<?php
/**
 * Apply pending SQL migrations in /migrations (idempotent where possible).
 * Open: http://localhost/barbershop/migrate.php
 */
declare(strict_types=1);

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

header('Content-Type: text/html; charset=utf-8');

$files = glob(__DIR__ . '/migrations/*.sql') ?: [];
sort($files);
$log = [];

try {
    $pdo = db();
    $pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
    $pdo->exec(
        'CREATE TABLE IF NOT EXISTS schema_migrations (
            filename VARCHAR(255) PRIMARY KEY,
            applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4'
    );

    foreach ($files as $file) {
        $name = basename($file);
        if ($name === 'fix_admin_user.sql' || str_starts_with($name, '001_')) {
            // 001 applied via setup; skip re-run of full seed wipe patterns
            $chk = $pdo->prepare('SELECT 1 FROM schema_migrations WHERE filename = ?');
            $chk->execute([$name]);
            if ($chk->fetch()) {
                $log[] = "Skip (already applied): {$name}";
                continue;
            }
            if (str_starts_with($name, '001_')) {
                $pdo->prepare('INSERT IGNORE INTO schema_migrations (filename) VALUES (?)')->execute([$name]);
                $log[] = "Marked baseline: {$name}";
                continue;
            }
        }

        $chk = $pdo->prepare('SELECT 1 FROM schema_migrations WHERE filename = ?');
        $chk->execute([$name]);
        $already = $chk->fetchColumn();
        $chk->closeCursor();
        if ($already) {
            $log[] = "Skip (already applied): {$name}";
            if ($name === '008_staff_time_off.sql') {
                $log = array_merge($log, migrate_after_staff_time_off($pdo));
            }
            if ($name === '010_user_roles.sql') {
                $log = array_merge($log, migrate_after_user_roles($pdo));
            }
            continue;
        }

        $sql = file_get_contents($file);
        if ($sql === false) {
            throw new RuntimeException("Cannot read {$name}");
        }
        $sql = preg_replace('/^USE\s+\w+\s*;/mi', '', $sql) ?? $sql;
        $parts = array_filter(array_map('trim', explode(';', $sql)));
        foreach ($parts as $stmt) {
            $clean = trim(preg_replace('/^\s*--.*$/m', '', $stmt) ?? $stmt);
            if ($clean === '') {
                continue;
            }
            $pdo->exec($clean);
        }
        $pdo->prepare('INSERT INTO schema_migrations (filename) VALUES (?)')->execute([$name]);
        $log[] = "Applied: {$name}";

        if ($name === '003_users_reminders.sql') {
            $log = array_merge($log, migrate_after_users_reminders($pdo));
        }
        if ($name === '005_sms_carriers.sql') {
            $log = array_merge($log, migrate_after_sms_carriers($pdo));
        }
        if ($name === '006_client_portal.sql') {
            $log = array_merge($log, migrate_after_client_portal($pdo));
        }
        if ($name === '007_payments.sql') {
            $log = array_merge($log, migrate_after_payments($pdo));
        }
        if ($name === '008_staff_time_off.sql') {
            $log = array_merge($log, migrate_after_staff_time_off($pdo));
        }
        if ($name === '010_user_roles.sql') {
            $log = array_merge($log, migrate_after_user_roles($pdo));
        }
    }
    $log[] = 'Done.';
} catch (Throwable $e) {
    $log[] = 'Error: ' . $e->getMessage();
}

function migrate_after_user_roles(PDO $pdo): array
{
    $log = [];
    $alters = [
        'ALTER TABLE users ADD COLUMN username VARCHAR(80) NULL AFTER email',
        "ALTER TABLE users ADD COLUMN role VARCHAR(20) NOT NULL DEFAULT 'customer' AFTER username",
    ];
    foreach ($alters as $sql) {
        try {
            $pdo->exec($sql);
            $log[] = 'OK: ' . $sql;
        } catch (Throwable $e) {
            if (!str_contains($e->getMessage(), 'Duplicate column')) {
                throw $e;
            }
            $log[] = 'Skip duplicate column';
        }
    }
    try {
        $pdo->exec('ALTER TABLE users ADD UNIQUE KEY uq_users_tenant_username (tenant_id, username)');
    } catch (Throwable $e) {
        // index may exist
    }

    try {
        $hasOwner = (int) $pdo->query("SELECT COUNT(*) FROM users WHERE role IN ('owner', 'admin')")->fetchColumn();
        if ($hasOwner === 0) {
            $tid = (int) $pdo->query('SELECT id FROM tenants ORDER BY id ASC LIMIT 1')->fetchColumn();
            if ($tid > 0) {
                $user = defined('ADMIN_USER') ? (string) ADMIN_USER : 'admin';
                $pass = defined('ADMIN_PASS') ? (string) ADMIN_PASS : 'password32';
                $hash = password_hash($pass, PASSWORD_DEFAULT);
                $pdo->prepare(
                    "INSERT INTO users (tenant_id, name, email, username, role, password_hash, sms_opt_in, email_opt_in, notes)
                     VALUES (?, 'Shop Owner', ?, ?, 'owner', ?, 0, 1, 'Seeded owner')"
                )->execute([$tid, $user . '@localhost', $user, $hash]);
                $log[] = 'Seeded owner account: ' . $user;
            }
        }
    } catch (Throwable $e) {
        $log[] = 'Owner seed skipped: ' . $e->getMessage();
    }
    return $log;
}

/**
 * @return list<string>
 */
function migrate_after_staff_time_off(PDO $pdo): array
{
    $log = [];
    $alters = [
        'ALTER TABLE staff_time_off ADD COLUMN is_day_off TINYINT(1) NOT NULL DEFAULT 1 AFTER off_date',
        'ALTER TABLE staff_time_off ADD COLUMN open_time TIME NULL AFTER is_day_off',
        'ALTER TABLE staff_time_off ADD COLUMN close_time TIME NULL AFTER open_time',
    ];
    foreach ($alters as $sql) {
        try {
            $pdo->exec($sql);
            $log[] = 'OK: ' . $sql;
        } catch (Throwable $e) {
            if (!str_contains($e->getMessage(), 'Duplicate column')) {
                throw $e;
            }
            $log[] = 'Skip duplicate column';
        }
    }
    return $log;
}

/**
 * @return list<string>
 */
function migrate_after_payments(PDO $pdo): array
{
    $log = [];
    $alters = [
        'ALTER TABLE shop_orders ADD COLUMN payment_status VARCHAR(30) NOT NULL DEFAULT \'unpaid\' AFTER notes',
        'ALTER TABLE shop_orders ADD COLUMN payment_provider VARCHAR(30) NULL AFTER payment_status',
        'ALTER TABLE bookings ADD COLUMN payment_status VARCHAR(30) NOT NULL DEFAULT \'unpaid\' AFTER notes',
        'ALTER TABLE bookings ADD COLUMN deposit_paid DECIMAL(10,2) NOT NULL DEFAULT 0 AFTER payment_status',
        'ALTER TABLE services ADD COLUMN deposit_mode VARCHAR(20) NOT NULL DEFAULT \'none\' AFTER price',
        'ALTER TABLE services ADD COLUMN deposit_value DECIMAL(10,2) NOT NULL DEFAULT 0 AFTER deposit_mode',
    ];
    foreach ($alters as $sql) {
        try {
            $pdo->exec($sql);
            $log[] = 'OK: ' . $sql;
        } catch (Throwable $e) {
            if (!str_contains($e->getMessage(), 'Duplicate column')) {
                throw $e;
            }
            $log[] = 'Skip duplicate column';
        }
    }
    return $log;
}

/**
 * @return list<string>
 */
function migrate_after_client_portal(PDO $pdo): array
{
    $log = [];
    $alters = [
        'ALTER TABLE users ADD COLUMN password_hash VARCHAR(255) NULL AFTER custom_sms_domain',
        'ALTER TABLE users ADD COLUMN login_token VARCHAR(64) NULL AFTER password_hash',
        'ALTER TABLE users ADD COLUMN login_token_expires DATETIME NULL AFTER login_token',
    ];
    foreach ($alters as $sql) {
        try {
            $pdo->exec($sql);
            $log[] = 'OK: ' . $sql;
        } catch (Throwable $e) {
            if (!str_contains($e->getMessage(), 'Duplicate column')) {
                throw $e;
            }
            $log[] = 'Skip duplicate column';
        }
    }
    try {
        $pdo->exec('ALTER TABLE users ADD INDEX idx_users_login_token (login_token)');
    } catch (Throwable $e) {
        // ignore
    }
    return $log;
}

/**
 * @return list<string>
 */
function migrate_after_sms_carriers(PDO $pdo): array
{
    $log = [];
    $alters = [
        'ALTER TABLE users ADD COLUMN carrier_id INT NULL AFTER email',
        'ALTER TABLE users ADD COLUMN custom_sms_domain VARCHAR(150) NULL AFTER carrier_id',
    ];
    foreach ($alters as $sql) {
        try {
            $pdo->exec($sql);
            $log[] = 'OK: ' . $sql;
        } catch (Throwable $e) {
            if (!str_contains($e->getMessage(), 'Duplicate column')) {
                throw $e;
            }
            $log[] = 'Skip duplicate column';
        }
    }
    return $log;
}

/**
 * @return list<string>
 */
function migrate_after_users_reminders(PDO $pdo): array
{
    $log = [];
    try {
        $pdo->exec('ALTER TABLE bookings ADD COLUMN user_id INT NULL AFTER staff_id');
        $log[] = 'Added bookings.user_id';
    } catch (Throwable $e) {
        if (!str_contains($e->getMessage(), 'Duplicate column')) {
            throw $e;
        }
        $log[] = 'bookings.user_id already exists';
    }
    try {
        $pdo->exec('ALTER TABLE bookings ADD INDEX idx_bookings_user (user_id)');
    } catch (Throwable $e) {
        // index may exist
    }

    require_once __DIR__ . '/lib/users.php';
    $rows = $pdo->query(
        'SELECT id, tenant_id, customer_name, customer_phone
         FROM bookings
         WHERE customer_name IS NOT NULL AND customer_name != \'\''
    )->fetchAll();
    $upd = $pdo->prepare('UPDATE bookings SET user_id = ? WHERE id = ? AND (user_id IS NULL OR user_id = 0)');
    foreach ($rows as $r) {
        $user = user_upsert((int) $r['tenant_id'], (string) $r['customer_name'], $r['customer_phone'] ?? null, null);
        $upd->execute([(int) $user['id'], (int) $r['id']]);
    }
    $linked = (int) $pdo->query('SELECT COUNT(*) FROM bookings WHERE user_id IS NOT NULL')->fetchColumn();
    $users = (int) $pdo->query('SELECT COUNT(*) FROM users')->fetchColumn();
    $log[] = "Backfill complete; {$users} users, {$linked} bookings linked.";
    return $log;
}
?>
<!DOCTYPE html>
<html lang="en"><head><meta charset="UTF-8"><title>Migrate</title>
<style>body{font-family:system-ui;max-width:720px;margin:2rem auto;padding:0 1rem}pre{background:#111;color:#eee;padding:1rem;border-radius:8px;white-space:pre-wrap}</style>
</head><body>
<h1>Migrations</h1>
<pre><?php foreach ($log as $l) echo htmlspecialchars($l) . "\n"; ?></pre>
<p><a href="<?= htmlspecialchars(APP_BASE) ?>/admin/calendar.php">Open calendar</a></p>
</body></html>
