<?php
namespace App;

use PDO;

function get_db(): PDO {
    static $pdo = null;
    if ($pdo instanceof PDO) {
        return $pdo;
    }

    $env = get_mysql_env_local();
    $dsn = sprintf(
        'mysql:host=%s;port=%s;dbname=%s;charset=utf8mb4',
        $env['MYSQL_HOST'],
        $env['MYSQL_PORT'],
        $env['MYSQL_DATABASE']
    );

    $pdo = new PDO($dsn, (string)$env['MYSQL_USER'], (string)$env['MYSQL_PASSWORD'], [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
    ]);

    migrate_db($pdo);
    return $pdo;
}

function get_mysql_env_local(): array {
    static $env = null;
    if ($env !== null) {
        return $env;
    }

    $fileEnv = @parse_ini_file(__DIR__ . '/../.env', INI_SCANNER_RAW) ?: [];
    $env = [
        'MYSQL_HOST' => getenv('MYSQL_HOST') ?: ($fileEnv['MYSQL_HOST'] ?? '127.0.0.1'),
        'MYSQL_PORT' => getenv('MYSQL_PORT') ?: ($fileEnv['MYSQL_PORT'] ?? '3306'),
        'MYSQL_DATABASE' => getenv('MYSQL_DATABASE') ?: ($fileEnv['MYSQL_DATABASE'] ?? 'event_tech'),
        'MYSQL_USER' => getenv('MYSQL_USER') ?: ($fileEnv['MYSQL_USER'] ?? 'root'),
        'MYSQL_PASSWORD' => getenv('MYSQL_PASSWORD') ?: ($fileEnv['MYSQL_PASSWORD'] ?? ''),
    ];

    return $env;
}

function migrate_db(PDO $pdo): void {
    $pdo->exec("CREATE TABLE IF NOT EXISTS users (
        id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(120) NOT NULL,
        email VARCHAR(190) NOT NULL UNIQUE,
        password_hash VARCHAR(255) NOT NULL,
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    $pdo->exec("CREATE TABLE IF NOT EXISTS products (
        id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
        title VARCHAR(180) NOT NULL,
        slug VARCHAR(180) NOT NULL UNIQUE,
        description TEXT NULL,
        price_cents INT UNSIGNED NOT NULL DEFAULT 0,
        template_file VARCHAR(255) NULL,
        image_url VARCHAR(500) NULL,
        event_type_code VARCHAR(50) NULL,
        is_active TINYINT(1) NOT NULL DEFAULT 1,
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_products_event_type_code (event_type_code),
        INDEX idx_products_active (is_active)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    $pdo->exec("CREATE TABLE IF NOT EXISTS orders (
        id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
        user_id BIGINT UNSIGNED NULL,
        stripe_session_id VARCHAR(255) NULL,
        amount_cents INT UNSIGNED NOT NULL DEFAULT 0,
        status VARCHAR(20) NOT NULL DEFAULT 'pending',
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_orders_user_id (user_id),
        INDEX idx_orders_status (status)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    $pdo->exec("CREATE TABLE IF NOT EXISTS event_types (
        id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
        code VARCHAR(50) NOT NULL UNIQUE,
        label VARCHAR(80) NOT NULL,
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    $pdo->exec("CREATE TABLE IF NOT EXISTS user_events (
        id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
        user_id BIGINT UNSIGNED NOT NULL,
        title VARCHAR(180) NOT NULL,
        event_date DATE NULL,
        venue VARCHAR(255) NULL,
        notes TEXT NULL,
        event_type_code VARCHAR(50) NULL,
        main_person_name VARCHAR(180) NULL,
        secondary_person_name VARCHAR(180) NULL,
        padrinos_names TEXT NULL,
        salon_name VARCHAR(255) NULL,
        event_time TIME NULL,
        ceremony_place VARCHAR(255) NULL,
        ceremony_time TIME NULL,
        event_address VARCHAR(255) NULL,
        maps_url VARCHAR(500) NULL,
        dress_code VARCHAR(120) NULL,
        rsvp_deadline DATE NULL,
        contact_whatsapp VARCHAR(60) NULL,
        main_message TEXT NULL,
        source_product_id BIGINT UNSIGNED NULL,
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_user_events_user (user_id),
        INDEX idx_user_events_type (event_type_code)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    $pdo->exec("CREATE TABLE IF NOT EXISTS user_guests (
        id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
        user_id BIGINT UNSIGNED NOT NULL,
        event_id BIGINT UNSIGNED NULL,
        name VARCHAR(180) NOT NULL,
        phone VARCHAR(30) NULL,
        email VARCHAR(190) NULL,
        adults TINYINT UNSIGNED NOT NULL DEFAULT 1,
        kids TINYINT UNSIGNED NOT NULL DEFAULT 0,
        pass_type VARCHAR(40) NULL,
        pass_count TINYINT UNSIGNED NOT NULL DEFAULT 1,
        rsvp_status VARCHAR(20) NOT NULL DEFAULT 'pending',
        invite_sent_at DATETIME NULL,
        table_name VARCHAR(120) NULL,
        dietary_notes VARCHAR(255) NULL,
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_user_guests_user (user_id),
        INDEX idx_user_guests_event (event_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    $pdo->exec("CREATE TABLE IF NOT EXISTS event_field_templates (
        id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
        event_type_id INT UNSIGNED NOT NULL,
        field_key VARCHAR(80) NOT NULL,
        field_label VARCHAR(120) NOT NULL,
        input_type VARCHAR(20) NOT NULL DEFAULT 'text',
        is_required TINYINT(1) NOT NULL DEFAULT 0,
        group_name VARCHAR(80) NULL,
        placeholder VARCHAR(255) NULL,
        sort_order INT NOT NULL DEFAULT 0,
        options_json JSON NULL,
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY uq_event_field_templates_type_key (event_type_id, field_key)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    $pdo->exec("CREATE TABLE IF NOT EXISTS user_event_details (
        id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
        event_id BIGINT UNSIGNED NOT NULL,
        field_key VARCHAR(80) NOT NULL,
        field_value TEXT NULL,
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY uq_user_event_details_event_key (event_id, field_key)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    $pdo->exec("CREATE TABLE IF NOT EXISTS user_event_timeline (
        id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
        event_id BIGINT UNSIGNED NOT NULL,
        item_time TIME NULL,
        item_title VARCHAR(180) NOT NULL,
        item_note TEXT NULL,
        sort_order INT NOT NULL DEFAULT 0,
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_user_event_timeline_event (event_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    add_column_if_missing($pdo, 'products', 'event_type_code', 'VARCHAR(50) NULL');
    add_column_if_missing($pdo, 'products', 'image_url', 'VARCHAR(500) NULL');
    add_column_if_missing($pdo, 'products', 'is_active', 'TINYINT(1) NOT NULL DEFAULT 1');
    add_column_if_missing($pdo, 'user_events', 'source_product_id', 'BIGINT UNSIGNED NULL');
    add_column_if_missing($pdo, 'user_guests', 'invite_sent_at', 'DATETIME NULL');

    seed_event_types_and_templates($pdo);
    seed_default_products($pdo);
}

function add_column_if_missing(PDO $pdo, string $table, string $column, string $definition): void {
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :table_name AND COLUMN_NAME = :column_name");
    $stmt->execute([
        ':table_name' => $table,
        ':column_name' => $column,
    ]);
    $exists = (int)$stmt->fetchColumn() > 0;
    if ($exists) {
        return;
    }
    $pdo->exec("ALTER TABLE {$table} ADD COLUMN {$column} {$definition}");
}

function seed_default_products(PDO $pdo): void {
    $stmt = $pdo->prepare("INSERT IGNORE INTO products (title, slug, description, price_cents, template_file, image_url, event_type_code) VALUES (:title, :slug, :description, :price_cents, :template_file, :image_url, :event_type_code)");
    $stmt->execute([
        ':title' => 'Ejemplo: Boda floral',
        ':slug' => 'boda-floral',
        ':description' => 'Invitacion clasica floral',
        ':price_cents' => 1500,
        ':template_file' => 'designs/boda-floral.pen',
        ':image_url' => '/assets/imagen1.png',
        ':event_type_code' => 'boda',
    ]);
    $pdo->exec("UPDATE products SET image_url = '/assets/imagen1.png' WHERE slug = 'boda-floral' AND (image_url IS NULL OR image_url = '')");
}

function seed_event_types_and_templates(PDO $pdo): void {
    $types = [
        ['boda', 'Boda'],
        ['quince', 'XV Anos'],
        ['bautizo', 'Bautizo'],
        ['comunion', 'Primera Comunion'],
        ['graduacion', 'Graduacion'],
        ['cumpleanos', 'Cumpleanos'],
        ['corporativo', 'Corporativo'],
    ];

    $insertType = $pdo->prepare("INSERT IGNORE INTO event_types (code, label) VALUES (:code, :label)");
    foreach ($types as $t) {
        $insertType->execute([':code' => $t[0], ':label' => $t[1]]);
    }

    $typeIdByCode = [];
    $rows = $pdo->query("SELECT id, code FROM event_types")->fetchAll(PDO::FETCH_ASSOC);
    foreach ($rows as $r) {
        $typeIdByCode[$r['code']] = (int)$r['id'];
    }

    $templates = [
        ['*', 'main_person_name', 'Nombre principal', 'text', 1, 'basico', 'Ej. Maria Fernanda', 10],
        ['*', 'event_date', 'Fecha del evento', 'date', 1, 'basico', '', 20],
        ['*', 'event_time', 'Hora del evento', 'time', 1, 'basico', '', 30],
        ['*', 'salon_name', 'Salon / Lugar', 'text', 1, 'ubicacion', 'Nombre del salon', 40],
        ['*', 'event_address', 'Direccion', 'text', 1, 'ubicacion', 'Calle, numero, colonia', 50],
        ['*', 'maps_url', 'Enlace Google Maps', 'url', 0, 'ubicacion', 'https://maps.app...', 60],
        ['*', 'padrinos_names', 'Nombre de padrinos', 'textarea', 0, 'protocolo', '', 70],
        ['*', 'dress_code', 'Codigo de vestimenta', 'text', 0, 'protocolo', 'Formal, etiqueta, etc.', 80],
        ['*', 'contact_whatsapp', 'WhatsApp de contacto', 'text', 1, 'rsvp', '+52...', 90],
        ['*', 'rsvp_deadline', 'Fecha limite RSVP', 'date', 0, 'rsvp', '', 100],
        ['*', 'main_message', 'Mensaje principal', 'textarea', 0, 'contenido', '', 110],
        ['boda', 'secondary_person_name', 'Nombre de la pareja', 'text', 1, 'boda', 'Nombre del novio / novia', 120],
        ['boda', 'ceremony_place', 'Iglesia / ceremonia', 'text', 0, 'boda', 'Parroquia o sede', 130],
        ['boda', 'ceremony_time', 'Hora de ceremonia', 'time', 0, 'boda', '', 140],
        ['boda', 'gift_table', 'Mesa de regalos', 'text', 0, 'boda', 'Liverpool, Amazon, etc.', 150],
        ['quince', 'vals_song', 'Cancion de vals', 'text', 0, 'quince', '', 120],
        ['quince', 'court_of_honor', 'Corte de honor', 'textarea', 0, 'quince', '', 130],
        ['bautizo', 'ceremony_place', 'Parroquia', 'text', 1, 'religioso', '', 120],
        ['bautizo', 'ceremony_time', 'Hora de misa', 'time', 1, 'religioso', '', 130],
        ['bautizo', 'verse_text', 'Versiculo o frase', 'textarea', 0, 'religioso', '', 140],
        ['comunion', 'ceremony_place', 'Parroquia', 'text', 1, 'religioso', '', 120],
        ['comunion', 'ceremony_time', 'Hora de misa', 'time', 1, 'religioso', '', 130],
        ['graduacion', 'school_name', 'Escuela / Universidad', 'text', 1, 'graduacion', '', 120],
        ['graduacion', 'degree_name', 'Carrera / Programa', 'text', 0, 'graduacion', '', 130],
        ['cumpleanos', 'age_number', 'Edad que cumple', 'number', 0, 'cumpleanos', 'Ej. 30', 120],
        ['cumpleanos', 'theme_name', 'Tematica', 'text', 0, 'cumpleanos', 'Neon, elegante, etc.', 130],
        ['corporativo', 'company_name', 'Empresa organizadora', 'text', 1, 'corporativo', '', 120],
        ['corporativo', 'agenda_summary', 'Agenda del evento', 'textarea', 0, 'corporativo', '', 130],
    ];

    $insertField = $pdo->prepare("INSERT IGNORE INTO event_field_templates
        (event_type_id, field_key, field_label, input_type, is_required, group_name, placeholder, sort_order)
        VALUES (:event_type_id, :field_key, :field_label, :input_type, :is_required, :group_name, :placeholder, :sort_order)");

    foreach ($templates as $tpl) {
        $code = $tpl[0];
        if ($code === '*') {
            foreach ($typeIdByCode as $typeId) {
                $insertField->execute([
                    ':event_type_id' => $typeId,
                    ':field_key' => $tpl[1],
                    ':field_label' => $tpl[2],
                    ':input_type' => $tpl[3],
                    ':is_required' => $tpl[4],
                    ':group_name' => $tpl[5],
                    ':placeholder' => $tpl[6],
                    ':sort_order' => $tpl[7],
                ]);
            }
            continue;
        }
        if (!isset($typeIdByCode[$code])) {
            continue;
        }
        $insertField->execute([
            ':event_type_id' => $typeIdByCode[$code],
            ':field_key' => $tpl[1],
            ':field_label' => $tpl[2],
            ':input_type' => $tpl[3],
            ':is_required' => $tpl[4],
            ':group_name' => $tpl[5],
            ':placeholder' => $tpl[6],
            ':sort_order' => $tpl[7],
        ]);
    }
}
