<?php
// migrations/install.php
// Ejecuta esto en: http://localhost/miportal_pro/migrations/install.php
// Crea tablas que falten y ajusta columnas requeridas sin borrar datos.

require_once __DIR__ . '/../includes/db.php';

function column_exists(PDO $pdo, $table, $column) {
  $sql = "SELECT COUNT(*) FROM information_schema.columns
          WHERE table_schema = DATABASE()
            AND table_name = ?
            AND column_name = ?";
  $st = $pdo->prepare($sql);
  $st->execute([$table, $column]);
  return (int)$st->fetchColumn() > 0;
}

function table_exists(PDO $pdo, $table) {
  $sql = "SELECT COUNT(*) FROM information_schema.tables
          WHERE table_schema = DATABASE()
            AND table_name = ?";
  $st = $pdo->prepare($sql);
  $st->execute([$table]);
  return (int)$st->fetchColumn() > 0;
}

try {
  $pdo = pdo();
  $pdo->beginTransaction();

  // USERS
  if (!table_exists($pdo, 'users')) {
    $pdo->exec("CREATE TABLE users (
      id INT AUTO_INCREMENT PRIMARY KEY,
      role ENUM('admin','client') NOT NULL,
      name VARCHAR(120) NOT NULL,
      email VARCHAR(160) NOT NULL UNIQUE,
      password_hash VARCHAR(255) NOT NULL,
      pin_hash VARCHAR(255) NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    echo "Tabla users: creada<br>";
  } else {
    if (!column_exists($pdo, 'users', 'pin_hash')) {
      $pdo->exec("ALTER TABLE users ADD COLUMN pin_hash VARCHAR(255) NULL AFTER password_hash");
      echo "Tabla users: columna pin_hash agregada<br>";
    }
  }

  // PLATFORMS
  if (!table_exists($pdo, 'platforms')) {
    $pdo->exec("CREATE TABLE platforms (
      id INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(100) NOT NULL UNIQUE,
      description VARCHAR(255) NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    echo "Tabla platforms: creada<br>";
  } else {
    if (!column_exists($pdo, 'platforms', 'description')) {
      $pdo->exec("ALTER TABLE platforms ADD COLUMN description VARCHAR(255) NULL AFTER name");
      echo "Tabla platforms: columna description agregada<br>";
    }
  }

  // IMAP ACCOUNTS
  if (!table_exists($pdo, 'imap_accounts')) {
    $pdo->exec("CREATE TABLE 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
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    echo "Tabla imap_accounts: creada<br>";
  }

  // IMAP KEYS
  if (!table_exists($pdo, 'imap_keys')) {
    $pdo->exec("CREATE TABLE 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
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    echo "Tabla imap_keys: creada<br>";
  }

  // ASSIGNED ACCOUNTS
  if (!table_exists($pdo, 'assigned_accounts')) {
    $pdo->exec("CREATE TABLE 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
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    echo "Tabla assigned_accounts: creada<br>";
  }

  // CODES LOG
  if (!table_exists($pdo, 'codes_log')) {
    $pdo->exec("CREATE TABLE 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
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");
    echo "Tabla codes_log: creada<br>";
  }

  $pdo->commit();
  echo "<hr>OK: Migración completada. Puedes ir al Panel Admin.";
} catch (Throwable $e) {
  if ($pdo && $pdo->inTransaction()) $pdo->rollBack();
  http_response_code(500);
  echo "ERROR: " . $e->getMessage();
}
