<?php
/**
 * MSPE Database Seed Script
 * ============================================================
 * Imports all data/*.json files into MySQL on first deployment.
 *
 * !! RUN ONCE via browser or CLI, then DELETE THIS FILE !!
 *
 * Browser: https://mspe.pro/db_seed.php?token=YOUR_TOKEN
 * CLI:     php db_seed.php --token=YOUR_TOKEN
 *
 * Generate a token:  php -r "echo bin2hex(random_bytes(16));"
 * Paste it below, then delete this file after running.
 * ============================================================
 */

define('SEED_TOKEN', 'CHANGE_THIS_TO_A_RANDOM_STRING');

// ── Token gate ────────────────────────────────────────────────
$cliToken = '';
foreach ($argv ?? [] as $arg) {
    if (str_starts_with($arg, '--token=')) {
        $cliToken = substr($arg, 8);
    }
}
$providedToken = $_GET['token'] ?? $cliToken;

if (!hash_equals(SEED_TOKEN, (string)$providedToken)) {
    http_response_code(403);
    die('403 Forbidden — provide ?token=YOUR_TOKEN');
}

// ── Bootstrap ─────────────────────────────────────────────────
define('IS_SEED', true);
$seedStartTime = microtime(true);
ob_start();

function loadEnvSeed($path) {
    if (!file_exists($path)) return;
    $lines = file($path, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
    foreach ($lines as $line) {
        $line = trim($line);
        if ($line === '' || $line[0] === '#') continue;
        if (strpos($line, '=') === false) continue;
        [$key, $value] = explode('=', $line, 2);
        $key   = trim($key);
        $value = trim($value);
        if (strlen($value) > 1 && (
            ($value[0] === '"' && substr($value, -1) === '"') ||
            ($value[0] === "'" && substr($value, -1) === "'")
        )) {
            $value = substr($value, 1, -1);
        }
        $_ENV[$key] = $value;
        putenv("$key=$value");
    }
}

loadEnvSeed(__DIR__ . '/.env');
loadEnvSeed(dirname(__DIR__) . '/.env');

function envSeed($key, $default = '') {
    return $_ENV[$key] ?? getenv($key) ?: $default;
}

// ── Connect to MySQL ──────────────────────────────────────────
$dsn = 'mysql:host=' . envSeed('DB_HOST', 'localhost')
     . ';dbname=' . envSeed('DB_NAME')
     . ';charset=utf8mb4';

try {
    $pdo = new PDO($dsn, envSeed('DB_USER'), envSeed('DB_PASS'), [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false,
    ]);
} catch (Exception $e) {
    http_response_code(500);
    die('Database connection failed: ' . $e->getMessage());
}

// ── Helpers ───────────────────────────────────────────────────
$results = [];

function ensureTable(PDO $pdo, string $table): void {
    $safe = preg_replace('/[^a-zA-Z0-9_]/', '', $table);
    $pdo->exec("CREATE TABLE IF NOT EXISTS `{$safe}` (
        `id`         VARCHAR(64)  NOT NULL PRIMARY KEY,
        `data`       JSON         NOT NULL,
        `created_at` DATETIME     DEFAULT CURRENT_TIMESTAMP,
        `updated_at` DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
}

function importJsonFile(PDO $pdo, string $table, string $file): array {
    if (!file_exists($file)) {
        return ['table' => $table, 'status' => 'skipped', 'reason' => 'File not found'];
    }

    $records = json_decode(file_get_contents($file), true);
    if (!is_array($records)) {
        return ['table' => $table, 'status' => 'error', 'reason' => 'Invalid JSON'];
    }
    if (empty($records)) {
        return ['table' => $table, 'status' => 'skipped', 'reason' => 'Empty file — nothing to import'];
    }

    ensureTable($pdo, $table);
    $safe = preg_replace('/[^a-zA-Z0-9_]/', '', $table);

    $inserted = 0;
    $updated  = 0;
    $errors   = 0;

    foreach ($records as $record) {
        if (empty($record['id'])) {
            $record['id'] = bin2hex(random_bytes(16));
        }
        $record['created_at'] = $record['created_at'] ?? date('Y-m-d H:i:s');
        $record['updated_at'] = $record['updated_at'] ?? date('Y-m-d H:i:s');

        try {
            // REPLACE INTO = INSERT if new, UPDATE if id already exists (idempotent)
            $stmt = $pdo->prepare(
                "REPLACE INTO `{$safe}` (`id`, `data`, `created_at`, `updated_at`)
                 VALUES (?, ?, ?, ?)"
            );
            $stmt->execute([
                $record['id'],
                json_encode($record, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES),
                $record['created_at'],
                $record['updated_at'],
            ]);
            // REPLACE INTO returns 1 for insert, 2 for replace
            ($stmt->rowCount() === 1) ? $inserted++ : $updated++;
        } catch (Exception $e) {
            $errors++;
            error_log("Seed error [{$table}] id={$record['id']}: " . $e->getMessage());
        }
    }

    return [
        'table'    => $table,
        'status'   => $errors === 0 ? 'ok' : 'partial',
        'inserted' => $inserted,
        'updated'  => $updated,
        'errors'   => $errors,
        'total'    => count($records),
    ];
}

// ── Seed each table ───────────────────────────────────────────
$dataDir = __DIR__ . '/data/';

$tables = [
    'settings'           => 'settings.json',
    'services'           => 'services.json',
    'news'               => 'news.json',
    'portfolio'          => 'portfolio.json',
    'availability_slots' => 'availability_slots.json',
    'blocked_times'      => 'blocked_times.json',
    'bookings'           => 'bookings.json',
    'messages'           => 'messages.json',
    'subscribers'        => 'subscribers.json',
];

foreach ($tables as $table => $file) {
    $results[] = importJsonFile($pdo, $table, $dataDir . $file);
}

// ── Also ensure empty-but-needed tables exist ─────────────────
$ensureOnly = ['users', 'login_attempts', 'password_resets', 'admin_auth', 'testimonials', 'team'];
foreach ($ensureOnly as $table) {
    ensureTable($pdo, $table);
    $results[] = ['table' => $table, 'status' => 'table_created', 'reason' => 'Schema ensured'];
}

// ── Clear stats cache ─────────────────────────────────────────
$cacheDir = __DIR__ . '/data/cache/';
$cleared  = 0;
if (is_dir($cacheDir)) {
    foreach (glob($cacheDir . 'stats_*.json') as $cacheFile) {
        if (@unlink($cacheFile)) {
            $cleared++;
        }
    }
}

// ── Output ────────────────────────────────────────────────────
$isCli = PHP_SAPI === 'cli';
$elapsed = round(microtime(true) - $seedStartTime, 3);
$allOk = array_reduce($results, fn($c, $r) => $c && in_array($r['status'], ['ok', 'skipped', 'table_created']), true);

if ($isCli) {
    echo "\n=== MSPE Database Seed ===\n\n";
    foreach ($results as $r) {
        $icon = match($r['status']) { 'ok' => '✓', 'partial' => '⚠', 'error' => '✗', default => '–' };
        printf("  %s %-25s %s\n", $icon, $r['table'], json_encode(array_diff_key($r, array_flip(['table']))));
    }
    echo "\n  Cache files cleared: {$cleared}\n";
    echo "  Elapsed time:        {$elapsed}s\n";
    echo $allOk ? "\n  All done. DELETE THIS FILE NOW.\n\n" : "\n  Some errors occurred — check error log.\n\n";
} else {
    header('Content-Type: text/html; charset=UTF-8');
    $badge = fn($s) => match($s) {
        'ok'           => '<span style="color:#22c55e">✓ ok</span>',
        'skipped'      => '<span style="color:#94a3b8">– skipped</span>',
        'table_created'=> '<span style="color:#60a5fa">○ ensured</span>',
        'partial'      => '<span style="color:#f59e0b">⚠ partial</span>',
        default        => '<span style="color:#ef4444">✗ error</span>',
    };

    echo '<!doctype html><html><head><meta charset="utf-8">
<title>MSPE DB Seed</title>
<style>body{font-family:monospace;background:#0f172a;color:#e2e8f0;padding:2rem;max-width:780px;margin:0 auto}
h1{color:#38bdf8}table{width:100%;border-collapse:collapse;margin:1rem 0}
td,th{padding:.4rem .8rem;border:1px solid #1e293b;text-align:left}th{background:#1e293b}
.ok{color:#22c55e}.warn{color:#f59e0b}.err{color:#ef4444}.note{background:#1e293b;padding:1rem;border-left:4px solid #f59e0b;margin:1.5rem 0}
</style></head><body>';
    echo '<h1>MSPE Database Seed</h1>';
    echo '<table><tr><th>Table</th><th>Status</th><th>Details</th></tr>';
    foreach ($results as $r) {
        $detail = array_diff_key($r, array_flip(['table', 'status']));
        echo '<tr><td>' . htmlspecialchars($r['table']) . '</td><td>' . $badge($r['status']) . '</td><td>' . htmlspecialchars(json_encode($detail)) . '</td></tr>';
    }
    echo '</table>';
    echo '<p>Stats cache files cleared: <strong>' . $cleared . '</strong></p>';
    echo '<p>Elapsed time: <strong>' . $elapsed . 's</strong></p>';

    if ($allOk) {
        echo '<div class="note">✅ Seed complete. <strong style="color:#ef4444">DELETE db_seed.php from your server immediately!</strong></div>';
    } else {
        echo '<div class="note">⚠️ Some tables had errors. Check your Hostinger error logs.</div>';
    }
    echo '</body></html>';
    ob_end_flush();
}