<?php
declare(strict_types=1);

/**
 * Adds Phase 3 setup tables/columns to an existing crm database.
 * Fresh installs already include this in schema.sql.
 * Run: C:\xampp\php\php.exe sql/migrate_phase3.php
 */
$base = dirname(__DIR__);
require $base . '/app/init.php';

$db = App\Core\Database::instance();
$pdo = $db->pdo();

function phase3ColumnExists(PDO $pdo, string $table, string $column): bool
{
    $stmt = $pdo->prepare(
        'SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t AND COLUMN_NAME = :c'
    );
    $stmt->execute(['t' => $table, 'c' => $column]);
    return (int) $stmt->fetchColumn() > 0;
}

function phase3TableExists(PDO $pdo, string $table): bool
{
    $stmt = $pdo->prepare(
        'SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :t'
    );
    $stmt->execute(['t' => $table]);
    return (int) $stmt->fetchColumn() > 0;
}

if (!phase3ColumnExists($pdo, 'leads', 'attrs')) {
    $pdo->exec('ALTER TABLE leads ADD COLUMN attrs JSON NULL AFTER notes');
}

if (!phase3TableExists($pdo, 'setup_states')) {
    $pdo->exec("CREATE TABLE setup_states (
      tenant_id INT UNSIGNED NOT NULL,
      draft JSON NOT NULL,
      published JSON NULL,
      previous JSON NULL,
      draft_updated_at DATETIME NULL,
      published_at DATETIME NULL,
      published_by INT UNSIGNED NULL,
      PRIMARY KEY (tenant_id),
      CONSTRAINT fk_setup_tenant FOREIGN KEY (tenant_id) REFERENCES tenants (id),
      CONSTRAINT fk_setup_user FOREIGN KEY (published_by) REFERENCES users (id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
}

echo "Phase 3 migrate finished.\n";
