<?php
/**
 * Upgrade script - creates voting system tables
 * Run once to add voting functionality
 */

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

try {
    // Elections table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS elections (
            id INT AUTO_INCREMENT PRIMARY KEY,
            title VARCHAR(200) NOT NULL,
            type ENUM('fellowship', 'presbyters') NOT NULL DEFAULT 'fellowship',
            status ENUM('draft', 'active', 'closed') NOT NULL DEFAULT 'draft',
            start_date DATETIME NULL,
            end_date DATETIME NULL,
            number_of_females INT NULL DEFAULT NULL COMMENT 'For presbyters elections',
            number_of_males INT NULL DEFAULT NULL COMMENT 'For presbyters elections',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    // Positions table (for fellowship elections)
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS election_positions (
            id INT AUTO_INCREMENT PRIMARY KEY,
            election_id INT NOT NULL,
            name VARCHAR(200) NOT NULL,
            position_order INT NOT NULL DEFAULT 0,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (election_id) REFERENCES elections(id) ON DELETE CASCADE,
            INDEX idx_election_id (election_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    // Candidates table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS election_candidates (
            id INT AUTO_INCREMENT PRIMARY KEY,
            position_id INT NULL COMMENT 'For fellowship elections',
            election_id INT NOT NULL COMMENT 'For presbyters elections',
            member_id INT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (position_id) REFERENCES election_positions(id) ON DELETE CASCADE,
            FOREIGN KEY (election_id) REFERENCES elections(id) ON DELETE CASCADE,
            FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE,
            UNIQUE KEY unique_fellowship_candidate (position_id, member_id),
            UNIQUE KEY unique_presbyters_candidate (election_id, member_id),
            INDEX idx_election_id (election_id),
            INDEX idx_position_id (position_id),
            INDEX idx_member_id (member_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    // Votes table
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS election_votes (
            id INT AUTO_INCREMENT PRIMARY KEY,
            election_id INT NOT NULL,
            voter_member_id INT NOT NULL COMMENT 'Member who cast the vote',
            position_id INT NULL COMMENT 'For fellowship elections',
            candidate_id INT NOT NULL COMMENT 'Member who was voted for',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (election_id) REFERENCES elections(id) ON DELETE CASCADE,
            FOREIGN KEY (position_id) REFERENCES election_positions(id) ON DELETE CASCADE,
            FOREIGN KEY (candidate_id) REFERENCES election_candidates(id) ON DELETE CASCADE,
            FOREIGN KEY (voter_member_id) REFERENCES members(id) ON DELETE CASCADE,
            UNIQUE KEY unique_fellowship_vote (election_id, voter_member_id, position_id),
            UNIQUE KEY unique_presbyters_vote (election_id, voter_member_id, candidate_id),
            INDEX idx_election_id (election_id),
            INDEX idx_voter_member_id (voter_member_id),
            INDEX idx_candidate_id (candidate_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    ");

    echo "Voting system tables created successfully.\n";
} catch (PDOException $e) {
    die("Error creating voting tables: " . $e->getMessage() . "\n");
}
