-- migrations/2025_08_add_missing_tables.sql

-- Crear tabla imap_accounts si no existe
CREATE TABLE IF NOT EXISTS imap_accounts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  label VARCHAR(120) NOT NULL,
  provider ENUM('gmail','namecheap','otro') DEFAULT 'gmail',
  host VARCHAR(160) NOT NULL,
  port INT NOT NULL DEFAULT 993,
  encryption ENUM('ssl','tls','none') DEFAULT 'ssl',
  username VARCHAR(160) NOT NULL,
  password_enc TEXT NOT NULL,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Crear tabla imap_keys si no existe
CREATE TABLE IF NOT EXISTS imap_keys (
  id INT AUTO_INCREMENT PRIMARY KEY,
  platform_id INT NOT NULL,
  phrase_key VARCHAR(255) NOT NULL,
  regex VARCHAR(255) NULL,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (platform_id) REFERENCES platforms(id) ON DELETE CASCADE
);

-- Crear tabla assigned_accounts si no existe
CREATE TABLE IF NOT EXISTS assigned_accounts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  client_id INT NOT NULL,
  platform_id INT NOT NULL,
  email VARCHAR(160) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_email_platform (email, platform_id),
  FOREIGN KEY (client_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (platform_id) REFERENCES platforms(id) ON DELETE CASCADE
);

-- Crear tabla codes_log si no existe
CREATE TABLE IF NOT EXISTS codes_log (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  client_id INT NOT NULL,
  platform_id INT NOT NULL,
  account_email VARCHAR(160) NOT NULL,
  code VARCHAR(50) NOT NULL,
  subject VARCHAR(255) NULL,
  from_email VARCHAR(255) NULL,
  uid BIGINT NULL,
  msg_date DATETIME NULL,
  imap_account_id INT NULL,
  fetched_at DATETIME NOT NULL,
  INDEX idx_codes (client_id, platform_id, account_email, fetched_at),
  FOREIGN KEY (client_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (platform_id) REFERENCES platforms(id) ON DELETE CASCADE
);
