<?php
use App\Core\Database as DB;

function runMigrations(): void
{
    $pdo = DB::pdo();

    $pdo->exec("CREATE TABLE IF NOT EXISTS _migrations (
        id     VARCHAR(100) PRIMARY KEY,
        ran_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $pdo->exec("CREATE TABLE IF NOT EXISTS events (
        id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        name         VARCHAR(200) NOT NULL,
        slug         VARCHAR(200) NOT NULL,
        description  TEXT,
        date_event   DATE,
        cover_photo  VARCHAR(255),
        order_prefix VARCHAR(20),
        status       ENUM('active','hidden') DEFAULT 'hidden',
        password     VARCHAR(255) DEFAULT NULL,
        created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY uq_slug (slug)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $pdo->exec("CREATE TABLE IF NOT EXISTS photos (
        id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        event_id      INT UNSIGNED NOT NULL,
        filename      VARCHAR(255) NOT NULL,
        original_path VARCHAR(255),
        preview_path  VARCHAR(255),
        thumb_path    VARCHAR(255),
        sort_order    INT DEFAULT 0,
        created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        KEY idx_event (event_id),
        CONSTRAINT fk_p_event FOREIGN KEY (event_id) REFERENCES events (id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $pdo->exec("CREATE TABLE IF NOT EXISTS orders (
        id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        event_id        INT UNSIGNED,
        event_name      VARCHAR(200),
        order_number    VARCHAR(30) NOT NULL,
        customer_name   VARCHAR(150) NOT NULL,
        phone           VARCHAR(30),
        email           VARCHAR(150),
        nif             VARCHAR(20),
        shipping_method ENUM('noivos','ctt','internacional') NOT NULL,
        address         TEXT,
        subtotal        DECIMAL(10,2) DEFAULT 0,
        shipping_cost   DECIMAL(10,2) DEFAULT 0,
        total           DECIMAL(10,2) DEFAULT 0,
        photos_free     INT DEFAULT 0,
        payment_method  ENUM('dinheiro','mbway') DEFAULT 'dinheiro',
        status          ENUM('pending','paid','sent','cancelled') DEFAULT 'pending',
        notes           TEXT,
        created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY uq_num (order_number),
        KEY idx_status (status),
        CONSTRAINT fk_o_event FOREIGN KEY (event_id) REFERENCES events (id) ON DELETE SET NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    $pdo->exec("CREATE TABLE IF NOT EXISTS order_items (
        id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        order_id   INT UNSIGNED NOT NULL,
        photo_id   INT UNSIGNED NOT NULL,
        filename   VARCHAR(255),
        quantity   INT DEFAULT 1,
        unit_price DECIMAL(10,2) DEFAULT 0,
        subtotal   DECIMAL(10,2) DEFAULT 0,
        KEY idx_order (order_id),
        CONSTRAINT fk_i_order FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    // Alterações incrementais — só correm uma vez
    runAlter('add_nif', function() use ($pdo) {
        $c = $pdo->query("SHOW COLUMNS FROM orders LIKE 'nif'")->fetchAll();
        if (empty($c)) $pdo->exec("ALTER TABLE orders ADD COLUMN nif VARCHAR(20) DEFAULT NULL AFTER email");
    });

    runAlter('email_nullable', function() use ($pdo) {
        $c = $pdo->query("SHOW COLUMNS FROM orders LIKE 'email'")->fetchAll();
        if (!empty($c) && ($c[0]['Null'] ?? '') === 'NO')
            $pdo->exec("ALTER TABLE orders MODIFY COLUMN email VARCHAR(150) DEFAULT NULL");
    });

    runAlter('event_password', function() use ($pdo) {
        $c = $pdo->query("SHOW COLUMNS FROM events LIKE 'password'")->fetchAll();
        if (empty($c)) $pdo->exec("ALTER TABLE events ADD COLUMN password VARCHAR(255) DEFAULT NULL AFTER status");
    });

    runAlter('payment_method', function() use ($pdo) {
        $c = $pdo->query("SHOW COLUMNS FROM orders LIKE 'payment_method'")->fetchAll();
        if (empty($c)) $pdo->exec("ALTER TABLE orders ADD COLUMN payment_method ENUM('dinheiro','mbway') DEFAULT 'dinheiro' AFTER status");
    });

    runAlter('phone_nullable', function() use ($pdo) {
        $c = $pdo->query("SHOW COLUMNS FROM orders LIKE 'phone'")->fetchAll();
        if (!empty($c) && ($c[0]['Null'] ?? '') === 'NO')
            $pdo->exec("ALTER TABLE orders MODIFY COLUMN phone VARCHAR(30) DEFAULT NULL");
    });

    // Tabela de rate limiting (mais robusta que sessão — persiste entre sessões)
    $pdo->exec("CREATE TABLE IF NOT EXISTS _rate_limits (
        rkey     VARCHAR(255) NOT NULL PRIMARY KEY,
        attempts INT          NOT NULL DEFAULT 1,
        first_at INT          NOT NULL,
        last_at  INT          NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

    // Limpeza periódica de entradas expiradas (> 1 hora)
    $pdo->exec("DELETE FROM _rate_limits WHERE last_at < " . (time() - 3600));
}

function runAlter(string $id, callable $fn): void
{
    $done = DB::one("SELECT id FROM _migrations WHERE id=?", [$id]);
    if ($done) return;
    $fn();
    DB::exec("INSERT IGNORE INTO _migrations (id) VALUES (?)", [$id]);
}
