<?php
/**
 * Upgrade: add missing indexes for performance.
 *
 * Safe to run multiple times: catches "duplicate key name" errors.
 */
require_once __DIR__ . '/../config/database.php';

function tryExec(PDO $pdo, string $label, string $sql): void {
    try {
        $pdo->exec($sql);
        echo "OK: $label\n";
    } catch (PDOException $e) {
        // 1061 duplicate key name, 1060 duplicate column, 1831/1832 index exists (varies)
        $code = (int)($e->errorInfo[1] ?? 0);
        if (in_array($code, [1061, 1060, 1831, 1832], true)) {
            echo "SKIP: $label (already exists)\n";
            return;
        }
        echo "ERROR: $label: " . $e->getMessage() . "\n";
    }
}

// Members: common sort/search
tryExec($pdo, 'members idx_last_first', "ALTER TABLE members ADD INDEX idx_last_first (last_name, first_name)");
tryExec($pdo, 'members idx_phone', "ALTER TABLE members ADD INDEX idx_phone (phone)");
tryExec($pdo, 'members idx_email', "ALTER TABLE members ADD INDEX idx_email (email)");
tryExec($pdo, 'members idx_status_dod', "ALTER TABLE members ADD INDEX idx_status_dod (status, date_of_death)");
tryExec($pdo, 'members idx_household', "ALTER TABLE members ADD INDEX idx_household (household_id)");
tryExec($pdo, 'members idx_dob', "ALTER TABLE members ADD INDEX idx_dob (date_of_birth)");

// Attendance
tryExec($pdo, 'attendance_records idx_date', "ALTER TABLE attendance_records ADD INDEX idx_att_date (date)");
tryExec($pdo, 'attendance_records idx_service_date', "ALTER TABLE attendance_records ADD INDEX idx_att_service_date (service_type_id, date)");

// Receipts
tryExec($pdo, 'receipts idx_transaction_date', "ALTER TABLE receipts ADD INDEX idx_transaction_date (transaction_date)");
tryExec($pdo, 'receipts idx_issued_at', "ALTER TABLE receipts ADD INDEX idx_issued_at2 (issued_at)");

// Transactions (finance reports often filter by date and fund)
tryExec($pdo, 'transactions idx_date', "ALTER TABLE transactions ADD INDEX idx_transaction_date (transaction_date)");
tryExec($pdo, 'transactions idx_fund_date', "ALTER TABLE transactions ADD INDEX idx_fund_date (fund_id, transaction_date)");
tryExec($pdo, 'transactions idx_member_date', "ALTER TABLE transactions ADD INDEX idx_member_date (member_id, transaction_date)");

echo "\nPerformance indexes upgrade complete.\n";

