<?php

function schema_table_exists(PDO $pdo, string $table): bool {
    $stmt = $pdo->prepare('SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ?');
    $stmt->execute([$table]);
    return (int) $stmt->fetchColumn() > 0;
}

function schema_column_exists(PDO $pdo, string $table, string $column): bool {
    $stmt = $pdo->prepare('SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ?');
    $stmt->execute([$table, $column]);
    return (int) $stmt->fetchColumn() > 0;
}

function ensure_app_schema(PDO $pdo): void {
    static $hasRun = false;
    if ($hasRun) {
        return;
    }
    $hasRun = true;

    $pdo->exec('CREATE TABLE IF NOT EXISTS migrations (
        id INT AUTO_INCREMENT PRIMARY KEY,
        migration VARCHAR(255) NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4');

    $pdo->exec('CREATE TABLE IF NOT EXISTS webinars (
        id INT AUTO_INCREMENT PRIMARY KEY,
        title VARCHAR(255) NOT NULL,
        description TEXT,
        presenter VARCHAR(255),
        scheduled_at DATETIME NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4');

    $pdo->exec('CREATE TABLE IF NOT EXISTS admin_users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        email VARCHAR(255) NOT NULL,
        password_hash VARCHAR(255) NOT NULL,
        display_name VARCHAR(255) NOT NULL DEFAULT "Admin",
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY uniq_admin_users_email (email)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4');

    $pdo->exec('CREATE TABLE IF NOT EXISTS attendees (
        id INT AUTO_INCREMENT PRIMARY KEY,
        webinar_id INT NOT NULL,
        first_name VARCHAR(255) NOT NULL DEFAULT "",
        last_name VARCHAR(255) NOT NULL DEFAULT "",
        name VARCHAR(255) NULL,
        email VARCHAR(255) NOT NULL,
        company VARCHAR(255) DEFAULT NULL,
        timezone VARCHAR(255) DEFAULT NULL,
        how_did_you_hear VARCHAR(255) DEFAULT NULL,
        password VARCHAR(255) NOT NULL DEFAULT "",
        consented TINYINT(1) NOT NULL DEFAULT 0,
        deleted_at TIMESTAMP NULL DEFAULT NULL,
        created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
        CONSTRAINT fk_attendees_webinar FOREIGN KEY (webinar_id) REFERENCES webinars(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4');

    if (schema_table_exists($pdo, 'attendees')) {
        if (schema_column_exists($pdo, 'attendees', 'name')) {
            $pdo->exec('ALTER TABLE attendees MODIFY COLUMN name VARCHAR(255) NULL');
        }

        if (!schema_column_exists($pdo, 'attendees', 'first_name')) {
            $pdo->exec('ALTER TABLE attendees ADD COLUMN first_name VARCHAR(255) NOT NULL DEFAULT "" AFTER webinar_id');
        }

        if (!schema_column_exists($pdo, 'attendees', 'last_name')) {
            $pdo->exec('ALTER TABLE attendees ADD COLUMN last_name VARCHAR(255) NOT NULL DEFAULT "" AFTER first_name');
        }

        if (!schema_column_exists($pdo, 'attendees', 'company')) {
            $pdo->exec('ALTER TABLE attendees ADD COLUMN company VARCHAR(255) DEFAULT NULL AFTER email');
        }

        if (!schema_column_exists($pdo, 'attendees', 'timezone')) {
            $pdo->exec('ALTER TABLE attendees ADD COLUMN timezone VARCHAR(255) DEFAULT NULL AFTER company');
        }

        if (!schema_column_exists($pdo, 'attendees', 'how_did_you_hear')) {
            $pdo->exec('ALTER TABLE attendees ADD COLUMN how_did_you_hear VARCHAR(255) DEFAULT NULL AFTER timezone');
        }

        if (!schema_column_exists($pdo, 'attendees', 'password')) {
            $pdo->exec('ALTER TABLE attendees ADD COLUMN password VARCHAR(255) NOT NULL DEFAULT "" AFTER how_did_you_hear');
        }

        if (!schema_column_exists($pdo, 'attendees', 'consented')) {
            $pdo->exec('ALTER TABLE attendees ADD COLUMN consented TINYINT(1) NOT NULL DEFAULT 0 AFTER password');
        }

        if (!schema_column_exists($pdo, 'attendees', 'deleted_at')) {
            $pdo->exec('ALTER TABLE attendees ADD COLUMN deleted_at TIMESTAMP NULL DEFAULT NULL AFTER consented');
        }

        if (!schema_column_exists($pdo, 'attendees', 'created_at')) {
            $pdo->exec('ALTER TABLE attendees ADD COLUMN created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP AFTER company');
        }

        if (schema_column_exists($pdo, 'attendees', 'registered_at')) {
            $pdo->exec('UPDATE attendees SET created_at = COALESCE(created_at, registered_at)');
        }

        if (schema_column_exists($pdo, 'attendees', 'name')) {
            $pdo->exec("UPDATE attendees SET first_name = TRIM(SUBSTRING_INDEX(name, ' ', 1)) WHERE (first_name = '' OR first_name IS NULL) AND name IS NOT NULL AND name <> ''");
            $pdo->exec("UPDATE attendees SET last_name = TRIM(SUBSTRING(name, CHAR_LENGTH(SUBSTRING_INDEX(name, ' ', 1)) + 1)) WHERE (last_name = '' OR last_name IS NULL) AND name IS NOT NULL AND name LIKE '% %'");
        }
    }

    $desiredTitle = 'Building Scalable Apps with AppWizzy';
    $desiredDescription = 'The fastest way to go from an idea to a working app you own, running on your server, with your database, using real frameworks.';
    $desiredPresenter = 'AppWizzy Team';
    $desiredScheduledAt = '2026-03-25 18:00:00';

    $stmt = $pdo->prepare('SELECT COUNT(*) FROM webinars WHERE id = 1');
    $stmt->execute();
    $hasPrimaryWebinar = (int) $stmt->fetchColumn() > 0;

    if ($hasPrimaryWebinar) {
        $update = $pdo->prepare('UPDATE webinars SET title = ?, description = ?, presenter = ?, scheduled_at = ? WHERE id = 1');
        $update->execute([$desiredTitle, $desiredDescription, $desiredPresenter, $desiredScheduledAt]);
    } else {
        $insert = $pdo->prepare('INSERT INTO webinars (id, title, description, presenter, scheduled_at) VALUES (1, ?, ?, ?, ?)');
        $insert->execute([$desiredTitle, $desiredDescription, $desiredPresenter, $desiredScheduledAt]);
    }
}