<?php
declare(strict_types=1);

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

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

function columnExists(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 tableExists(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 (!columnExists($pdo, 'tenants', 'industry_code')) {
    $pdo->exec("ALTER TABLE tenants ADD COLUMN industry_code VARCHAR(40) NOT NULL DEFAULT 'distribution' AFTER slug");
}
if (!columnExists($pdo, 'pipeline_stages', 'code')) {
    $pdo->exec('ALTER TABLE pipeline_stages ADD COLUMN code VARCHAR(40) NULL AFTER name');
}
if (!columnExists($pdo, 'accounts', 'attrs')) {
    $pdo->exec('ALTER TABLE accounts ADD COLUMN next_visit_at DATETIME NULL, ADD COLUMN last_visit_at DATETIME NULL, ADD COLUMN service_assignee_id INT UNSIGNED NULL, ADD COLUMN attrs JSON NULL');
}
if (!columnExists($pdo, 'opportunities', 'attrs')) {
    $pdo->exec('ALTER TABLE opportunities ADD COLUMN attrs JSON NULL');
}
if (!columnExists($pdo, 'contacts', 'email_status')) {
    $pdo->exec("ALTER TABLE contacts ADD COLUMN email_status ENUM('ok','bounced') NOT NULL DEFAULT 'ok'");
}
$pdo->exec("ALTER TABLE activities MODIFY type ENUM('call','email','meeting','visit','note','task','stage','campaign') NOT NULL");

$sql = file_get_contents(__DIR__ . '/schema.sql');
foreach (['marketing_connections', 'email_templates', 'marketing_lists', 'campaigns', 'campaign_recipients', 'unsubscribes'] as $table) {
    if (!tableExists($pdo, $table)) {
        echo "Create missing table $table via a full reinstall (install.php --force) if this migrate cannot parse schema chunks.\n";
    }
}

echo "Phase 2 migrate finished.\n";
