<?php
/**
 * Upgrade script - adds role and must_change_password columns for existing installs
 * Run once if you installed before the clerk role was added.
 */

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

$upgrades = [
    ['role', "ALTER TABLE users ADD COLUMN role VARCHAR(20) DEFAULT 'admin'"],
    ['must_change_password', 'ALTER TABLE users ADD COLUMN must_change_password TINYINT(1) DEFAULT 0'],
    ['members.title', 'ALTER TABLE members ADD COLUMN title VARCHAR(20) AFTER id'],
    ['members.middle_name', 'ALTER TABLE members ADD COLUMN middle_name VARCHAR(100) AFTER first_name'],
    ['members.gender', 'ALTER TABLE members ADD COLUMN gender VARCHAR(20) AFTER last_name'],
    ['members.date_of_birth', 'ALTER TABLE members ADD COLUMN date_of_birth DATE AFTER gender'],
    ['members.occupation', 'ALTER TABLE members ADD COLUMN occupation VARCHAR(100) AFTER email'],
    ['members.marital_status', 'ALTER TABLE members ADD COLUMN marital_status VARCHAR(30) AFTER occupation'],
    ['members.baptism_date', 'ALTER TABLE members ADD COLUMN baptism_date DATE AFTER join_date'],
    ['members.ghana_post_address', 'ALTER TABLE members ADD COLUMN ghana_post_address VARCHAR(255) NULL AFTER address'],
    ['members.map_link', 'ALTER TABLE members ADD COLUMN map_link VARCHAR(500) NULL AFTER ghana_post_address'],
    ['members.emergency_contact', 'ALTER TABLE members ADD COLUMN emergency_contact VARCHAR(100) AFTER address'],
    ['members.emergency_phone', 'ALTER TABLE members ADD COLUMN emergency_phone VARCHAR(50) AFTER emergency_contact'],
    ['members.photo', 'ALTER TABLE members ADD COLUMN photo VARCHAR(255) NULL AFTER household_id'],
    ['ministries', 'CREATE TABLE IF NOT EXISTS ministries (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL UNIQUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)'],
    ['member_ministries', 'CREATE TABLE IF NOT EXISTS member_ministries (id INT AUTO_INCREMENT PRIMARY KEY, member_id INT NOT NULL, ministry_id INT NOT NULL, UNIQUE KEY (member_id, ministry_id), FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE, FOREIGN KEY (ministry_id) REFERENCES ministries(id) ON DELETE CASCADE)'],
    ['settings', 'CREATE TABLE IF NOT EXISTS settings (name VARCHAR(100) PRIMARY KEY, value TEXT)'],
    ['funds.is_personal', "ALTER TABLE funds ADD COLUMN is_personal TINYINT(1) DEFAULT 0 AFTER description"],
    ['transactions.member_id', 'ALTER TABLE transactions ADD COLUMN member_id INT NULL AFTER fund_id'],
    ['members.membership_number', 'ALTER TABLE members ADD COLUMN membership_number VARCHAR(50) NULL UNIQUE AFTER id'],
    ['membership_number_prefixes', "CREATE TABLE IF NOT EXISTS membership_number_prefixes (id INT AUTO_INCREMENT PRIMARY KEY, code VARCHAR(20) NOT NULL UNIQUE, description VARCHAR(255) NULL, display_order INT DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)"],
    ['members.is_aged', 'ALTER TABLE members ADD COLUMN is_aged TINYINT(1) DEFAULT 0 AFTER notes'],
    ['members.is_invalid', 'ALTER TABLE members ADD COLUMN is_invalid TINYINT(1) DEFAULT 0 AFTER is_aged'],
    ['fellowship_managers', 'CREATE TABLE IF NOT EXISTS fellowship_managers (user_id INT NOT NULL, fellowship_name VARCHAR(100) NOT NULL, PRIMARY KEY (user_id, fellowship_name), FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE)'],
    ['fellowship_subgroups', 'CREATE TABLE IF NOT EXISTS fellowship_subgroups (id INT AUTO_INCREMENT PRIMARY KEY, fellowship_name VARCHAR(100) NOT NULL, name VARCHAR(100) NOT NULL, display_order INT DEFAULT 0, UNIQUE KEY (fellowship_name, name))'],
    ['fellowship_subgroup_members', 'CREATE TABLE IF NOT EXISTS fellowship_subgroup_members (subgroup_id INT NOT NULL, member_id INT NOT NULL, PRIMARY KEY (subgroup_id, member_id), FOREIGN KEY (subgroup_id) REFERENCES fellowship_subgroups(id) ON DELETE CASCADE, FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE)'],
];

foreach ($upgrades as list($col, $sql)) {
    try {
        $pdo->exec($sql);
        echo "Added column: $col\n";
    } catch (PDOException $e) {
        if (strpos($e->getMessage(), 'Duplicate column') !== false || strpos($e->getMessage(), 'already exists') !== false) {
            echo "Column/constraint already exists: $col\n";
        } else {
            echo "Error ($col): " . $e->getMessage() . "\n";
        }
    }
}

$pdo->exec("UPDATE users SET role = 'admin' WHERE role IS NULL OR role = ''");

// Seed default ministries if table exists and is empty
try {
    $stmt = $pdo->query('SELECT COUNT(*) FROM ministries');
    if ($stmt && $stmt->fetchColumn() == 0) {
        $defaultMinistries = ['Church Choir', 'Singing Band', 'Praise Showers', 'Children\'s Ministry', 'Youth Ministry', 'Ushering'];
        $stmt = $pdo->prepare('INSERT INTO ministries (name) VALUES (?)');
        foreach ($defaultMinistries as $m) $stmt->execute([$m]);
        echo "\nSeeded default ministries.";
    }
} catch (PDOException $e) { /* ministries table may not exist yet */ }

echo "\nUpgrade complete.";
