<?php
/**
 * Upgrade: create committees and committee_members tables.
 * Run once from the browser or CLI.
 */

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

$sqls = [
    'committees' => "CREATE TABLE IF NOT EXISTS committees (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(150) NOT NULL UNIQUE,
        description TEXT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )",
    'committee_members' => "CREATE TABLE IF NOT EXISTS committee_members (
        id INT AUTO_INCREMENT PRIMARY KEY,
        committee_id INT NOT NULL,
        member_id INT NOT NULL,
        role VARCHAR(60) NOT NULL DEFAULT 'Member',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY (committee_id, member_id),
        FOREIGN KEY (committee_id) REFERENCES committees(id) ON DELETE CASCADE,
        FOREIGN KEY (member_id)    REFERENCES members(id)    ON DELETE CASCADE
    )",
];

foreach ($sqls as $table => $sql) {
    try {
        $pdo->exec($sql);
        echo "Created table: $table\n";
    } catch (PDOException $e) {
        echo "Error ($table): " . $e->getMessage() . "\n";
    }
}

echo "\nCommittees upgrade complete.";
