<?php
// api/fix_database_schema.php
require_once __DIR__ . '/../config/database.php';

echo "<h1>Database Schema Fixer</h1>";
echo "<pre>";

try {
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // 1. Fix MEMBERS table
    echo "Checking 'members' table...\n";
    
    // is_approved
    try {
        $pdo->query("SELECT is_approved FROM members LIMIT 1");
        echo "- 'is_approved' exists.\n";
    } catch (Exception $e) {
        echo "- Adding 'is_approved'...\n";
        $pdo->exec("ALTER TABLE members ADD COLUMN is_approved TINYINT(1) DEFAULT 1");
    }

    // category
    try {
        $pdo->query("SELECT category FROM members LIMIT 1");
        echo "- 'category' exists.\n";
    } catch (Exception $e) {
        echo "- Adding 'category'...\n";
        $pdo->exec("ALTER TABLE members ADD COLUMN category VARCHAR(100) NULL");
    }

    // Extended Profile Fields
    $new_cols = [
        'whatsapp' => "VARCHAR(20) NULL",
        'dob' => "DATE NULL",
        'gender' => "VARCHAR(10) NULL",
        'state_origin' => "VARCHAR(50) NULL",
        'marital_status' => "VARCHAR(20) NULL",
        'wedding_anniversary' => "DATE NULL",
        'next_of_kin' => "JSON NULL",
        'employment' => "JSON NULL",
        'education' => "JSON NULL",
        'referees' => "JSON NULL",
        'past_assignments' => "JSON NULL"
    ];

    foreach ($new_cols as $col => $def) {
        try {
            $pdo->query("SELECT $col FROM members LIMIT 1");
            echo "- '$col' exists.\n";
        } catch (Exception $e) {
            echo "- Adding '$col'...\n";
            $pdo->exec("ALTER TABLE members ADD COLUMN $col $def");
        }
    }


    // 2. Fix ROSTER_ASSIGNMENTS table
    echo "\nChecking 'roster_assignments' table...\n";
    try {
        $pdo->query("SELECT member_id FROM roster_assignments LIMIT 1");
        echo "- 'member_id' exists.\n";
    } catch (Exception $e) {
        echo "- Adding 'member_id'...\n";
        $pdo->exec("ALTER TABLE roster_assignments ADD COLUMN member_id INT(11) NULL");
        echo "- 'member_id' added.\n";
    }


    // 3. Fix ROSTERS table
    echo "\nChecking 'rosters' table...\n";
    try {
        $pdo->query("SELECT checklist_json FROM rosters LIMIT 1");
        echo "- 'checklist_json' exists.\n";
    } catch (Exception $e) {
        echo "- Adding 'checklist_json'...\n";
        $pdo->exec("ALTER TABLE rosters ADD COLUMN checklist_json TEXT NULL AFTER status");
    }
    
    // 4. Create missing tables if needed
    echo "\nChecking 'member_registrations' table...\n";
    $pdo->exec("CREATE TABLE IF NOT EXISTS member_registrations (
        id INT AUTO_INCREMENT PRIMARY KEY,
        full_name VARCHAR(100) NOT NULL,
        email VARCHAR(100) NOT NULL,
        phone VARCHAR(20),
        token VARCHAR(64) NOT NULL,
        status ENUM('Pending','Approved','Rejected') DEFAULT 'Pending',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )");
    echo "- 'member_registrations' OK.\n";

    echo "\nChecking 'access_tokens' table...\n";
    $pdo->exec("CREATE TABLE IF NOT EXISTS access_tokens (
        id INT AUTO_INCREMENT PRIMARY KEY,
        token VARCHAR(64) NOT NULL,
        type VARCHAR(20) NOT NULL, -- 'registration', 'update'
        member_id INT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        expires_at TIMESTAMP NULL,
        is_used TINYINT(1) DEFAULT 0,
        KEY idx_token (token)
    )");
    echo "- 'access_tokens' OK.\n";


    echo "\n\nDONE! Database schema should now be up to date.";

} catch (PDOException $e) {
    echo "CRITICAL ERROR: " . $e->getMessage();
}

echo "</pre>";
?>
