<?php
function 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 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 index_exists(PDO $pdo, string $table, string $index): bool {
    $stmt = $pdo->prepare(
        'SELECT COUNT(*) FROM information_schema.STATISTICS '
        .'WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=? AND INDEX_NAME=?'
    );
    $stmt->execute([$table, $index]);
    return (int)$stmt->fetchColumn() > 0;
}

function ensure_auth_schema(PDO $pdo): void {
    $pdo->exec("CREATE TABLE IF NOT EXISTS users (
      id INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(160) NOT NULL,
      email VARCHAR(190) NULL UNIQUE,
      phone VARCHAR(40) NOT NULL UNIQUE,
      password_hash VARCHAR(255) NOT NULL,
      blood_group VARCHAR(5) NULL,
      district VARCHAR(100) NULL,
      photo_url TEXT NULL,
      status ENUM('active','blocked') NOT NULL DEFAULT 'active',
      verified TINYINT(1) NOT NULL DEFAULT 0,
      token VARCHAR(128) NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      last_login_at DATETIME NULL,
      INDEX idx_users_status(status),
      INDEX idx_users_verified(verified),
      INDEX idx_users_created(created_at)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    if (table_exists($pdo, 'users') && !column_exists($pdo, 'users', 'photo_url')) {
        $pdo->exec('ALTER TABLE users ADD COLUMN photo_url TEXT NULL AFTER district');
    }
    if (table_exists($pdo, 'donors') && !column_exists($pdo, 'donors', 'photo_url')) {
        $pdo->exec('ALTER TABLE donors ADD COLUMN photo_url TEXT NULL AFTER address');
    }

    $pdo->exec("CREATE TABLE IF NOT EXISTS admins (
      id INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(120) NOT NULL,
      email VARCHAR(190) UNIQUE NOT NULL,
      password_hash VARCHAR(255) NOT NULL,
      token VARCHAR(128) NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");

    if (table_exists($pdo, 'blood_requests') && !column_exists($pdo, 'blood_requests', 'user_id')) {
        $pdo->exec('ALTER TABLE blood_requests ADD COLUMN user_id INT NULL AFTER id');
        $pdo->exec('ALTER TABLE blood_requests ADD INDEX idx_request_user (user_id)');
    }

    $goodHash = '$2y$12$yyHZ0alntw9ppaySWtWo.enrQcLtzHBAxPkWTYyK8VKKCnt9UkF4C'; // admin123
    $oldBrokenHash = '$2y$10$Pim.7RrD6.mS5Xr79gVw3uz1KXKJ9CczhWyFpOxopfxM4xoOCrmda';
    $fix = $pdo->prepare('UPDATE admins SET password_hash=? WHERE email=? AND password_hash=?');
    $fix->execute([$goodHash, 'admin@sadaka.local', $oldBrokenHash]);

    $count = (int)$pdo->query('SELECT COUNT(*) FROM admins')->fetchColumn();
    if ($count === 0) {
        $stmt = $pdo->prepare('INSERT INTO admins(name,email,password_hash) VALUES(?,?,?)');
        $stmt->execute(['Administrator','admin@sadaka.local',$goodHash]);
    }
}

function ensure_donor_application_schema(PDO $pdo): void {
    ensure_auth_schema($pdo);

    if (table_exists($pdo, 'donors') && !column_exists($pdo, 'donors', 'user_id')) {
        $pdo->exec('ALTER TABLE donors ADD COLUMN user_id INT NULL AFTER id');
    }
    if (table_exists($pdo, 'donors') && !index_exists($pdo, 'donors', 'idx_donors_user')) {
        $pdo->exec('ALTER TABLE donors ADD INDEX idx_donors_user (user_id)');
    }

    $pdo->exec("CREATE TABLE IF NOT EXISTS donor_applications (
      id INT AUTO_INCREMENT PRIMARY KEY,
      user_id INT NOT NULL,
      name VARCHAR(160) NOT NULL,
      phone VARCHAR(40) NOT NULL,
      blood_group VARCHAR(5) NOT NULL,
      district_id INT NULL,
      district VARCHAR(100) NOT NULL,
      thana_id INT NULL,
      upazila VARCHAR(100) NOT NULL,
      address VARCHAR(255) NULL,
      last_donation_date DATE NULL,
      available TINYINT(1) NOT NULL DEFAULT 1,
      status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
      admin_note VARCHAR(500) NULL,
      reviewed_at DATETIME NULL,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      UNIQUE KEY uq_donor_application_user (user_id),
      INDEX idx_donor_application_status (status, created_at),
      INDEX idx_donor_application_location (district_id, thana_id),
      INDEX idx_donor_application_group (blood_group)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
}
