-- Corre isto no phpMyAdmin (separador "SQL"), na tua base de dados MySQL/MariaDB.
-- (a sintaxe é igual nos dois motores)

CREATE TABLE IF NOT EXISTS clients (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(255) NOT NULL,
  prioridade VARCHAR(10) NOT NULL DEFAULT 'media',
  criado_em BIGINT NOT NULL,
  morada VARCHAR(255) DEFAULT NULL,
  nif VARCHAR(30) DEFAULT NULL,
  website VARCHAR(255) DEFAULT NULL,
  setor VARCHAR(100) DEFAULT NULL,
  notas LONGTEXT DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS client_attachments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  client_id INT NOT NULL,
  original_name VARCHAR(255) NOT NULL,
  stored_name VARCHAR(255) NOT NULL,
  mime_type VARCHAR(150) DEFAULT '',
  size_bytes INT DEFAULT 0,
  uploaded_at BIGINT NOT NULL,
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS asset_brands (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(100) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS asset_types (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(100) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS client_assets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  cliente_id INT NOT NULL,
  categoria VARCHAR(20) NOT NULL DEFAULT 'outro',
  nome VARCHAR(255) NOT NULL,
  marca_id INT NULL DEFAULT NULL,
  tipo_id INT NULL DEFAULT NULL,
  referencia VARCHAR(255) DEFAULT NULL,
  data_aquisicao BIGINT NULL DEFAULT NULL,
  garantia_anos INT NULL DEFAULT NULL,
  data_validade BIGINT NULL DEFAULT NULL,
  notas LONGTEXT DEFAULT NULL,
  criado_em BIGINT NOT NULL,
  FOREIGN KEY (cliente_id) REFERENCES clients(id) ON DELETE CASCADE,
  FOREIGN KEY (marca_id) REFERENCES asset_brands(id) ON DELETE SET NULL,
  FOREIGN KEY (tipo_id) REFERENCES asset_types(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS client_asset_attachments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  asset_id INT NOT NULL,
  original_name VARCHAR(255) NOT NULL,
  stored_name VARCHAR(255) NOT NULL,
  mime_type VARCHAR(150) DEFAULT '',
  size_bytes INT DEFAULT 0,
  uploaded_at BIGINT NOT NULL,
  FOREIGN KEY (asset_id) REFERENCES client_assets(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS client_domains (
  id INT AUTO_INCREMENT PRIMARY KEY,
  client_id INT NOT NULL,
  domain VARCHAR(255) NOT NULL UNIQUE,
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS client_emails (
  id INT AUTO_INCREMENT PRIMARY KEY,
  client_id INT NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS contacts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nome VARCHAR(150) NOT NULL,
  email VARCHAR(255) DEFAULT NULL,
  telefone VARCHAR(30) DEFAULT NULL,
  funcao VARCHAR(50) NOT NULL DEFAULT 'outro',
  cliente_id INT NULL DEFAULT NULL,
  criado_em BIGINT NOT NULL,
  notas LONGTEXT DEFAULT NULL,
  FOREIGN KEY (cliente_id) REFERENCES clients(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS contact_attachments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  contact_id INT NOT NULL,
  original_name VARCHAR(255) NOT NULL,
  stored_name VARCHAR(255) NOT NULL,
  mime_type VARCHAR(150) DEFAULT '',
  size_bytes INT DEFAULT 0,
  uploaded_at BIGINT NOT NULL,
  FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE clients
  ADD COLUMN contacto_principal_id INT NULL DEFAULT NULL,
  ADD FOREIGN KEY (contacto_principal_id) REFERENCES contacts(id) ON DELETE SET NULL;

CREATE TABLE IF NOT EXISTS app_users (
  username VARCHAR(100) PRIMARY KEY,
  display_name VARCHAR(150) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role VARCHAR(30) NOT NULL DEFAULT 'utilizador',
  dominio_associado VARCHAR(255) DEFAULT NULL,
  email_associado VARCHAR(255) DEFAULT NULL,
  assinatura LONGTEXT DEFAULT NULL,
  nome_real VARCHAR(150) DEFAULT NULL,
  email VARCHAR(255) DEFAULT NULL,
  telefone VARCHAR(30) DEFAULT NULL,
  cliente_id INT NULL DEFAULT NULL,
  FOREIGN KEY (cliente_id) REFERENCES clients(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE contacts
  ADD COLUMN username VARCHAR(100) NULL DEFAULT NULL,
  ADD FOREIGN KEY (username) REFERENCES app_users(username) ON DELETE SET NULL;

CREATE TABLE IF NOT EXISTS tickets (
  id VARCHAR(20) PRIMARY KEY,
  titulo VARCHAR(255) NOT NULL,
  descricao TEXT,
  prioridade VARCHAR(20) NOT NULL DEFAULT 'media',
  estado VARCHAR(20) NOT NULL DEFAULT 'aberto',
  criado_por VARCHAR(150) NOT NULL,
  criado_em BIGINT NOT NULL,
  iniciado_em BIGINT NULL DEFAULT NULL,
  concluido_em BIGINT NULL DEFAULT NULL,
  dominio VARCHAR(255) DEFAULT '',
  notas LONGTEXT DEFAULT NULL,
  respondido_em BIGINT NULL DEFAULT NULL,
  atribuido_a VARCHAR(150) DEFAULT NULL,
  apagado_em BIGINT NULL DEFAULT NULL,
  cliente_id INT NULL DEFAULT NULL,
  email_contacto VARCHAR(255) DEFAULT NULL,
  FOREIGN KEY (cliente_id) REFERENCES clients(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS login_attempts (
  username VARCHAR(100) PRIMARY KEY,
  attempts INT NOT NULL DEFAULT 0,
  locked_until BIGINT NULL DEFAULT NULL,
  last_attempt BIGINT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS audit_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  autor VARCHAR(150) NOT NULL,
  acao VARCHAR(100) NOT NULL,
  detalhes TEXT,
  criado_em BIGINT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS canned_responses (
  id INT AUTO_INCREMENT PRIMARY KEY,
  titulo VARCHAR(150) NOT NULL,
  conteudo LONGTEXT NOT NULL,
  autor VARCHAR(150) NOT NULL,
  criado_em BIGINT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ticket_views (
  ticket_id VARCHAR(20) NOT NULL,
  username VARCHAR(100) NOT NULL,
  last_viewed_em BIGINT NOT NULL,
  PRIMARY KEY (ticket_id, username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ticket_interactions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ticket_id VARCHAR(20) NOT NULL,
  tipo VARCHAR(30) NOT NULL,
  autor VARCHAR(150) NOT NULL,
  conteudo LONGTEXT,
  criado_em BIGINT NOT NULL,
  FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS attachments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ticket_id VARCHAR(20) NOT NULL,
  original_name VARCHAR(255) NOT NULL,
  stored_name VARCHAR(255) NOT NULL,
  mime_type VARCHAR(150) DEFAULT '',
  size_bytes INT DEFAULT 0,
  uploaded_at BIGINT NOT NULL,
  interaction_id INT NULL DEFAULT NULL,
  FOREIGN KEY (ticket_id) REFERENCES tickets(id) ON DELETE CASCADE,
  FOREIGN KEY (interaction_id) REFERENCES ticket_interactions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS theme_settings (
  id INT PRIMARY KEY,
  ink VARCHAR(7) NOT NULL DEFAULT '#10142A',
  panel VARCHAR(7) NOT NULL DEFAULT '#191F3D',
  panel2 VARCHAR(7) NOT NULL DEFAULT '#212952',
  line_color VARCHAR(7) NOT NULL DEFAULT '#33396A',
  text_color VARCHAR(7) NOT NULL DEFAULT '#EDEFF7',
  text_dim VARCHAR(7) NOT NULL DEFAULT '#9AA1C7',
  amber VARCHAR(7) NOT NULL DEFAULT '#F2A73B',
  blue VARCHAR(7) NOT NULL DEFAULT '#6C8CFF',
  green VARCHAR(7) NOT NULL DEFAULT '#4CAF7D',
  red VARCHAR(7) NOT NULL DEFAULT '#E85C4A'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO theme_settings (id) VALUES (1);

CREATE TABLE IF NOT EXISTS processed_emails (
  id INT AUTO_INCREMENT PRIMARY KEY,
  message_id VARCHAR(255) NOT NULL UNIQUE,
  processed_at BIGINT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS allowed_emails (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  priority VARCHAR(10) NOT NULL DEFAULT 'media'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS blocked_emails (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS allowed_domains (
  id INT AUTO_INCREMENT PRIMARY KEY,
  domain VARCHAR(255) NOT NULL UNIQUE,
  priority VARCHAR(10) NOT NULL DEFAULT 'media'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS app_settings (
  id INT PRIMARY KEY,
  to_email VARCHAR(255) DEFAULT '',
  from_email VARCHAR(255) DEFAULT '',
  from_name VARCHAR(150) DEFAULT '',
  smtp_host VARCHAR(255) DEFAULT '',
  smtp_port INT DEFAULT 587,
  smtp_user VARCHAR(255) DEFAULT '',
  smtp_pass VARCHAR(255) DEFAULT '',
  smtp_secure VARCHAR(10) DEFAULT 'tls',
  imap_host VARCHAR(255) DEFAULT '',
  imap_port INT DEFAULT 993,
  imap_user VARCHAR(255) DEFAULT '',
  imap_pass VARCHAR(255) DEFAULT '',
  imap_secure VARCHAR(10) DEFAULT 'ssl',
  imap_days_lookback INT NOT NULL DEFAULT 1,
  last_cron_run BIGINT NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO app_settings (id, to_email, from_name) VALUES (1, 'paulo.ad@gmail.com', 'Ticket Factory');