<?php
/**
 * Upgrade: add election_voters (eligible voter list) and election_checkins (day-of check-in)
 * Run once after upgrade-voting.php and upgrade-elections-assignment.php
 */

require_once __DIR__ . '/../config/database.php';

try {
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS election_voters (
            id          INT AUTO_INCREMENT PRIMARY KEY,
            election_id INT NOT NULL,
            member_id   INT NOT NULL,
            added_by    INT NULL COMMENT 'user_id who added this voter',
            added_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY unique_voter (election_id, member_id),
            FOREIGN KEY (election_id) REFERENCES elections(id) ON DELETE CASCADE,
            FOREIGN KEY (member_id)   REFERENCES members(id)   ON DELETE CASCADE,
            INDEX idx_ev_election (election_id),
            INDEX idx_ev_member   (member_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");
    echo "OK: election_voters table created.\n";
} catch (PDOException $e) {
    echo "Error creating election_voters: " . $e->getMessage() . "\n";
}

try {
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS election_checkins (
            id             INT AUTO_INCREMENT PRIMARY KEY,
            election_id    INT NOT NULL,
            member_id      INT NOT NULL,
            checked_in_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            checked_in_by  INT NULL COMMENT 'user_id who performed the check-in',
            UNIQUE KEY unique_checkin (election_id, member_id),
            FOREIGN KEY (election_id) REFERENCES elections(id) ON DELETE CASCADE,
            FOREIGN KEY (member_id)   REFERENCES members(id)   ON DELETE CASCADE,
            INDEX idx_ec_election (election_id),
            INDEX idx_ec_member   (member_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");
    echo "OK: election_checkins table created.\n";
} catch (PDOException $e) {
    echo "Error creating election_checkins: " . $e->getMessage() . "\n";
}

echo "Voting check-in upgrade complete.\n";
