#!/usr/bin/env php
<?php

/**
 * Database Migration Script
 *
 * Usage: php scripts/migrate.php [--fresh]
 *
 * Options:
 *   --fresh    Drop and recreate the database
 */

declare(strict_types=1);

require_once __DIR__ . '/../src/database.php';

use Notifier\Database;

// Parse arguments
$fresh = in_array('--fresh', $argv);

// Load config
$config = require __DIR__ . '/../config.php';
$dbPath = $config['database']['path'];
$migrationsPath = __DIR__ . '/../migrations';

echo "Notifier Database Migration\n";
echo "============================\n\n";

// Handle --fresh flag
if ($fresh) {
    echo "Fresh install requested.\n";
    if (file_exists($dbPath)) {
        unlink($dbPath);
        // Also remove WAL files if they exist
        if (file_exists($dbPath . '-wal')) unlink($dbPath . '-wal');
        if (file_exists($dbPath . '-shm')) unlink($dbPath . '-shm');
        echo "Deleted existing database.\n";
    }
}

// Create database connection
try {
    $db = new Database($dbPath);
    echo "Database connection established.\n";
    echo "PRAGMAs: " . json_encode($db->getInfo()) . "\n\n";
} catch (Exception $e) {
    echo "ERROR: Failed to connect to database: " . $e->getMessage() . "\n";
    exit(1);
}

// Check if schema_migrations table exists
$hasMigrations = $db->fetchColumn(
    "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='schema_migrations'"
);

// Get applied migrations
$applied = [];
if ($hasMigrations) {
    $rows = $db->fetchAll("SELECT version FROM schema_migrations ORDER BY version");
    $applied = array_column($rows, 'version');
}

echo "Applied migrations: " . (empty($applied) ? "(none)" : implode(', ', $applied)) . "\n\n";

// Find migration files
$files = glob($migrationsPath . '/*.sql');
sort($files);

if (empty($files)) {
    echo "No migration files found in {$migrationsPath}\n";
    exit(0);
}

$pending = [];
foreach ($files as $file) {
    $version = basename($file, '.sql');
    if (!in_array($version, $applied)) {
        $pending[$version] = $file;
    }
}

if (empty($pending)) {
    echo "All migrations are up to date.\n";
    exit(0);
}

echo "Pending migrations: " . implode(', ', array_keys($pending)) . "\n\n";

// Run pending migrations
foreach ($pending as $version => $file) {
    echo "Running migration: {$version}...\n";

    $sql = file_get_contents($file);
    if ($sql === false) {
        echo "ERROR: Failed to read {$file}\n";
        exit(1);
    }

    try {
        // SQLite can execute multiple statements via exec()
        $pdo = $db->getPdo();
        $pdo->exec($sql);

        // Record migration (only if table exists after first migration)
        $hasMigrations = $db->fetchColumn(
            "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='schema_migrations'"
        );
        if ($hasMigrations) {
            $db->execute(
                "INSERT INTO schema_migrations (version) VALUES (?)",
                [$version]
            );
        }

        echo "  ✓ Applied {$version}\n";
    } catch (Exception $e) {
        echo "  ✗ FAILED: " . $e->getMessage() . "\n";
        exit(1);
    }
}

echo "\nMigration complete.\n";

// Verify tables were created
echo "\nVerifying schema...\n";
$tables = $db->fetchAll(
    "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name"
);
$tableNames = array_column($tables, 'name');
echo "Tables: " . implode(', ', $tableNames) . "\n";

$expectedTables = [
    'categories',
    'notification_dates',
    'notification_history',
    'notification_recurrence',
    'notification_subsequents',
    'notifications',
    'scheduler_state',
    'schema_migrations',
    'send_jobs',
];

$missing = array_diff($expectedTables, $tableNames);
if (!empty($missing)) {
    echo "ERROR: Missing tables: " . implode(', ', $missing) . "\n";
    exit(1);
}

echo "\n✓ All expected tables present.\n";

// Verify foreign keys work
echo "\nTesting foreign key enforcement...\n";
try {
    // This should fail because notification_id 99999 doesn't exist
    $db->execute(
        "INSERT INTO notification_dates (notification_id, scheduled_at) VALUES (99999, datetime('now'))"
    );
    echo "ERROR: Foreign key constraint was NOT enforced!\n";
    exit(1);
} catch (Exception $e) {
    if (strpos($e->getMessage(), 'FOREIGN KEY constraint failed') !== false) {
        echo "✓ Foreign key constraints working correctly.\n";
    } else {
        echo "ERROR: Unexpected error: " . $e->getMessage() . "\n";
        exit(1);
    }
}

// Verify CHECK constraints work
echo "\nTesting CHECK constraints...\n";
try {
    // This should fail because 'invalid' is not a valid status
    $db->execute(
        "INSERT INTO notifications (title, message, schedule_type, status) VALUES ('test', 'test', 'dates', 'invalid')"
    );
    echo "ERROR: CHECK constraint was NOT enforced!\n";
    exit(1);
} catch (Exception $e) {
    if (strpos($e->getMessage(), 'CHECK constraint failed') !== false) {
        echo "✓ CHECK constraints working correctly.\n";
    } else {
        echo "ERROR: Unexpected error: " . $e->getMessage() . "\n";
        exit(1);
    }
}

echo "\n============================\n";
echo "✓ Phase 1 Complete: Database ready.\n";
