<?php
function qident(string $name): string {
    return '`' . str_replace('`', '``', $name) . '`';
}
function column_exists(PDO $pdo, string $table, string $column): bool {
    $stmt = $pdo->query('SHOW COLUMNS FROM ' . qident($table) . ' LIKE ' . $pdo->quote($column));
    return (bool)$stmt->fetch();
}
function index_exists(PDO $pdo, string $table, string $index): bool {
    $stmt = $pdo->query('SHOW INDEX FROM ' . qident($table) . ' WHERE Key_name = ' . $pdo->quote($index));
    return (bool)$stmt->fetch();
}
function foreign_key_exists(PDO $pdo, string $table, string $constraint): bool {
    $stmt = $pdo->prepare('SELECT CONSTRAINT_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND CONSTRAINT_NAME = ?');
    $stmt->execute([$table, $constraint]);
    return (bool)$stmt->fetch();
}
function run_migrations(PDO $pdo): void {
    $users = tbl('users');
    $qrCodes = tbl('qr_codes');
    $qrScans = tbl('qr_scans');
    $qrGroups = tbl('qr_groups');
    $qrGroupShares = tbl('qr_group_shares');
    $prefix = table_prefix($GLOBALS['config'] ?? []);
    if (!column_exists($pdo, $users, 'name')) $pdo->exec('ALTER TABLE `'.$users.'` ADD name VARCHAR(100) NULL AFTER id');
    if (!column_exists($pdo, $users, 'role')) {
        $pdo->exec("ALTER TABLE `".$users."` ADD role ENUM('user','superadmin') NOT NULL DEFAULT 'user' AFTER password_hash");
        $firstUser = $pdo->query('SELECT id FROM `'.$users.'` ORDER BY id ASC LIMIT 1')->fetchColumn();
        if ($firstUser) { $stmt = $pdo->prepare("UPDATE `".$users."` SET role = 'superadmin' WHERE id = ?"); $stmt->execute([(int)$firstUser]); }
    }
    if (!column_exists($pdo, $users, 'reset_token_hash')) $pdo->exec('ALTER TABLE `'.$users.'` ADD reset_token_hash CHAR(64) NULL AFTER password_hash');
    if (!column_exists($pdo, $users, 'reset_expires_at')) $pdo->exec('ALTER TABLE `'.$users.'` ADD reset_expires_at DATETIME NULL AFTER reset_token_hash');
    if (!column_exists($pdo, $users, 'reset_requested_at')) $pdo->exec('ALTER TABLE `'.$users.'` ADD reset_requested_at DATETIME NULL AFTER reset_expires_at');
    if (!column_exists($pdo, $users, 'email_verified_at')) { $pdo->exec('ALTER TABLE `'.$users.'` ADD email_verified_at DATETIME NULL AFTER reset_requested_at'); $pdo->exec('UPDATE `'.$users.'` SET email_verified_at = NOW() WHERE email_verified_at IS NULL'); }
    if (!column_exists($pdo, $users, 'verification_token_hash')) $pdo->exec('ALTER TABLE `'.$users.'` ADD verification_token_hash CHAR(64) NULL AFTER email_verified_at');
    if (!column_exists($pdo, $users, 'verification_expires_at')) $pdo->exec('ALTER TABLE `'.$users.'` ADD verification_expires_at DATETIME NULL AFTER verification_token_hash');
    if (!column_exists($pdo, $users, 'failed_login_count')) $pdo->exec('ALTER TABLE `'.$users.'` ADD failed_login_count INT UNSIGNED NOT NULL DEFAULT 0 AFTER verification_expires_at');
    if (!column_exists($pdo, $users, 'login_locked_until')) $pdo->exec('ALTER TABLE `'.$users.'` ADD login_locked_until DATETIME NULL AFTER failed_login_count');
    if (!column_exists($pdo, $users, 'updated_at')) $pdo->exec('ALTER TABLE `'.$users.'` ADD updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER created_at');
    $pdo->exec('CREATE TABLE IF NOT EXISTS `'.$qrGroups.'` (
      id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      user_id INT UNSIGNED NOT NULL,
      name VARCHAR(120) NOT NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      FOREIGN KEY (user_id) REFERENCES `'.$users.'`(id) ON DELETE CASCADE,
      UNIQUE KEY user_group_name (user_id, name),
      INDEX(user_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci');
    $pdo->exec("CREATE TABLE IF NOT EXISTS `".$qrGroupShares."` (
      id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      group_id INT UNSIGNED NOT NULL,
      user_id INT UNSIGNED NOT NULL,
      access_role ENUM('view','edit') NOT NULL DEFAULT 'view',
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      FOREIGN KEY (group_id) REFERENCES `".$qrGroups."`(id) ON DELETE CASCADE,
      FOREIGN KEY (user_id) REFERENCES `".$users."`(id) ON DELETE CASCADE,
      UNIQUE KEY group_user (group_id, user_id),
      INDEX(user_id),
      INDEX(group_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
    if (!column_exists($pdo, $qrCodes, 'user_id')) {
        $pdo->exec('ALTER TABLE `'.$qrCodes.'` ADD user_id INT UNSIGNED NULL AFTER id');
        $firstUser = $pdo->query('SELECT id FROM `'.$users.'` ORDER BY id ASC LIMIT 1')->fetchColumn();
        if ($firstUser) { $stmt = $pdo->prepare('UPDATE `'.$qrCodes.'` SET user_id = ? WHERE user_id IS NULL'); $stmt->execute([(int)$firstUser]); }
        $pdo->exec('ALTER TABLE `'.$qrCodes.'` MODIFY user_id INT UNSIGNED NOT NULL');
    }
    if (!column_exists($pdo, $qrCodes, 'group_id')) $pdo->exec('ALTER TABLE `'.$qrCodes.'` ADD group_id INT UNSIGNED NULL AFTER user_id');
    if (!column_exists($pdo, $qrCodes, 'logo_path')) $pdo->exec('ALTER TABLE `'.$qrCodes.'` ADD logo_path VARCHAR(255) NULL AFTER transparent_background');
    if (!column_exists($pdo, $qrCodes, 'utm_source')) $pdo->exec('ALTER TABLE `'.$qrCodes.'` ADD utm_source VARCHAR(120) NULL AFTER logo_path');
    if (!column_exists($pdo, $qrCodes, 'utm_medium')) $pdo->exec('ALTER TABLE `'.$qrCodes.'` ADD utm_medium VARCHAR(120) NULL AFTER utm_source');
    if (!column_exists($pdo, $qrCodes, 'utm_campaign')) $pdo->exec('ALTER TABLE `'.$qrCodes.'` ADD utm_campaign VARCHAR(190) NULL AFTER utm_medium');
    if (!column_exists($pdo, $qrCodes, 'utm_term')) $pdo->exec('ALTER TABLE `'.$qrCodes.'` ADD utm_term VARCHAR(190) NULL AFTER utm_campaign');
    if (!column_exists($pdo, $qrCodes, 'utm_content')) $pdo->exec('ALTER TABLE `'.$qrCodes.'` ADD utm_content VARCHAR(190) NULL AFTER utm_term');
    if (!index_exists($pdo, $qrCodes, 'user_id')) $pdo->exec('ALTER TABLE `'.$qrCodes.'` ADD INDEX user_id (user_id)');
    if (!index_exists($pdo, $qrCodes, 'group_id')) $pdo->exec('ALTER TABLE `'.$qrCodes.'` ADD INDEX group_id (group_id)');
    $fkUser = $prefix.'fk_qr_codes_user';
    if (!foreign_key_exists($pdo, $qrCodes, $fkUser)) { try { $pdo->exec('ALTER TABLE `'.$qrCodes.'` ADD CONSTRAINT `'.$fkUser.'` FOREIGN KEY (user_id) REFERENCES `'.$users.'`(id) ON DELETE CASCADE'); } catch (Throwable $e) {} }
    $fkGroup = $prefix.'fk_qr_codes_group';
    if (!foreign_key_exists($pdo, $qrCodes, $fkGroup)) { try { $pdo->exec('ALTER TABLE `'.$qrCodes.'` ADD CONSTRAINT `'.$fkGroup.'` FOREIGN KEY (group_id) REFERENCES `'.$qrGroups.'`(id) ON DELETE SET NULL'); } catch (Throwable $e) {} }
    $pdo->exec('CREATE TABLE IF NOT EXISTS `'.$qrScans.'` (
      id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      qr_code_id INT UNSIGNED NOT NULL,
      scanned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      ip_hash CHAR(64) NULL,
      user_agent VARCHAR(255) NULL,
      referer VARCHAR(500) NULL,
      country_code CHAR(2) NULL,
      country_name VARCHAR(120) NULL,
      region_name VARCHAR(120) NULL,
      city VARCHAR(120) NULL,
      FOREIGN KEY (qr_code_id) REFERENCES `'.$qrCodes.'`(id) ON DELETE CASCADE,
      INDEX(qr_code_id),
      INDEX(scanned_at),
      INDEX(country_code)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci');
    if (!column_exists($pdo, $qrScans, 'country_code')) $pdo->exec('ALTER TABLE `'.$qrScans.'` ADD country_code CHAR(2) NULL AFTER referer');
    if (!column_exists($pdo, $qrScans, 'country_name')) $pdo->exec('ALTER TABLE `'.$qrScans.'` ADD country_name VARCHAR(120) NULL AFTER country_code');
    if (!column_exists($pdo, $qrScans, 'region_name')) $pdo->exec('ALTER TABLE `'.$qrScans.'` ADD region_name VARCHAR(120) NULL AFTER country_name');
    if (!column_exists($pdo, $qrScans, 'city')) $pdo->exec('ALTER TABLE `'.$qrScans.'` ADD city VARCHAR(120) NULL AFTER region_name');
    if (!index_exists($pdo, $qrScans, 'country_code')) { try { $pdo->exec('ALTER TABLE `'.$qrScans.'` ADD INDEX country_code (country_code)'); } catch (Throwable $e) {} }
    $rateLimits = tbl('rate_limits');
    $pdo->exec('CREATE TABLE IF NOT EXISTS `'.$rateLimits.'` (
      id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
      endpoint VARCHAR(80) NOT NULL,
      key_hash CHAR(64) NOT NULL,
      attempts INT UNSIGNED NOT NULL DEFAULT 1,
      window_start DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      blocked_until DATETIME NULL,
      last_attempt_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      UNIQUE KEY endpoint_key (endpoint, key_hash),
      INDEX(blocked_until),
      INDEX(last_attempt_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci');
}
