<?php

declare(strict_types=1);

define('ROOT_PATH', __DIR__ . '/..');

require_once ROOT_PATH . '/app/Core/Autoloader.php';
\App\Core\Autoloader::register();
require_once ROOT_PATH . '/app/helpers/env.php';
require_once ROOT_PATH . '/app/helpers/config.php';

$envFile = ROOT_PATH . '/.env';
if (file_exists($envFile)) {
    foreach (file($envFile, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES) as $line) {
        $line = trim($line);
        if ($line === '' || $line[0] === '#' || strpos($line, '=') === false) {
            continue;
        }
        list($k, $v) = explode('=', $line, 2);
        $_ENV[trim($k)] = trim($v);
    }
}

use App\Core\Database;

try {
    $db = Database::getConnection();
    echo "Running Event Session Phase 1 DDL Migration...\n";

    // 1. Create event_sessions table
    $db->exec("
        CREATE TABLE IF NOT EXISTS `event_sessions` (
            `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            `wedding_event_id` BIGINT UNSIGNED NOT NULL COMMENT '→ wedding_events.id',
            `workspace_id` BIGINT UNSIGNED NOT NULL COMMENT '→ workspaces.id',
            `session_name` VARCHAR(100) NOT NULL DEFAULT 'Default Session' COMMENT 'e.g. Session 1, Session 2, Default Session',
            `start_time` DATETIME NOT NULL COMMENT 'Session start timestamp',
            `end_time` DATETIME NULL DEFAULT NULL COMMENT 'Optional session end timestamp',
            `venue_name` VARCHAR(150) NULL DEFAULT NULL COMMENT 'Optional room/venue override',
            `venue_address` TEXT NULL DEFAULT NULL COMMENT 'Optional address override',
            `description` VARCHAR(500) NULL DEFAULT NULL COMMENT 'Optional notes for guest',
            `sort_order` SMALLINT UNSIGNED NOT NULL DEFAULT 1,
            `is_default` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1 = default session for this wedding event',
            `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            `updated_at` DATETIME NULL DEFAULT NULL,
            PRIMARY KEY (`id`),
            KEY `idx_event_sessions_event` (`wedding_event_id`),
            KEY `idx_event_sessions_workspace` (`workspace_id`),
            CONSTRAINT `fk_event_sessions_wedding_event_id` FOREIGN KEY (`wedding_event_id`) REFERENCES `wedding_events` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
            CONSTRAINT `fk_event_sessions_workspace_id` FOREIGN KEY (`workspace_id`) REFERENCES `workspaces` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
    ");
    echo "[OK] Table 'event_sessions' created or already exists.\n";

    // 2. Add event_session_id column to guest_events if not exists
    $cols = $db->query("SHOW COLUMNS FROM guest_events LIKE 'event_session_id'")->fetchAll();
    if (empty($cols)) {
        $db->exec("
            ALTER TABLE `guest_events` 
            ADD COLUMN `event_session_id` BIGINT UNSIGNED NULL DEFAULT NULL AFTER `wedding_event_id`,
            ADD KEY `idx_guest_events_session` (`event_session_id`),
            ADD CONSTRAINT `fk_guest_events_event_session_id` 
                FOREIGN KEY (`event_session_id`) REFERENCES `event_sessions` (`id`) 
                ON DELETE SET NULL ON UPDATE CASCADE;
        ");
        echo "[OK] Column 'event_session_id' added to table 'guest_events'.\n";
    } else {
        echo "[INFO] Column 'event_session_id' already exists in 'guest_events'.\n";
    }

    echo "Migration completed successfully.\n";
} catch (Exception $e) {
    echo "ERROR: " . $e->getMessage() . "\n";
    exit(1);
}
