<?php
/**
 * User Entity Linking Migration Script
 * GHAMECC Choir Management Platform
 * 
 * This script applies the SQL migration to add society_id, circuit_id, and diocese_id columns
 * to the users table for linking society-level, circuit-level, and diocesan-level roles
 * to their respective entities.
 */

// Include database configuration
require_once 'config/database.php';

// Display header
echo "===========================================\n";
echo "GHAMECC User Entity Linking Migration\n";
echo "===========================================\n\n";

// Function to execute SQL and handle errors
function executeSQLWithErrorHandling($sql, $description) {
    global $conn;
    
    echo "Executing: $description... ";
    
    if ($conn->query($sql) === TRUE) {
        echo "SUCCESS\n";
        return true;
    } else {
        echo "ERROR: " . $conn->error . "\n";
        return false;
    }
}

// Check if tables exist before adding foreign keys
function tableExists($tableName) {
    global $conn;
    $result = $conn->query("SHOW TABLES LIKE '$tableName'");
    return $result->num_rows > 0;
}

// Start migration
echo "Starting migration...\n\n";

// Add entity ID columns to users table
$addColumnsSQL = "ALTER TABLE users 
                 ADD COLUMN society_id INT NULL,
                 ADD COLUMN circuit_id INT NULL,
                 ADD COLUMN diocese_id INT NULL";

executeSQLWithErrorHandling($addColumnsSQL, "Adding entity ID columns to users table");

// Check if the required tables exist before adding foreign keys
$societiesExist = tableExists('societies');
$circuitsExist = tableExists('circuits');
$diocesesExist = tableExists('dioceses');

// Add foreign key constraints if tables exist
if ($societiesExist) {
    $societyFKSQL = "ALTER TABLE users
                    ADD CONSTRAINT fk_user_society FOREIGN KEY (society_id) REFERENCES societies(id) ON DELETE SET NULL";
    executeSQLWithErrorHandling($societyFKSQL, "Adding society foreign key constraint");
} else {
    echo "WARNING: 'societies' table does not exist. Foreign key constraint not added.\n";
}

if ($circuitsExist) {
    $circuitFKSQL = "ALTER TABLE users
                    ADD CONSTRAINT fk_user_circuit FOREIGN KEY (circuit_id) REFERENCES circuits(id) ON DELETE SET NULL";
    executeSQLWithErrorHandling($circuitFKSQL, "Adding circuit foreign key constraint");
} else {
    echo "WARNING: 'circuits' table does not exist. Foreign key constraint not added.\n";
}

if ($diocesesExist) {
    $dioceseFKSQL = "ALTER TABLE users
                    ADD CONSTRAINT fk_user_diocese FOREIGN KEY (diocese_id) REFERENCES dioceses(id) ON DELETE SET NULL";
    executeSQLWithErrorHandling($dioceseFKSQL, "Adding diocese foreign key constraint");
} else {
    echo "WARNING: 'dioceses' table does not exist. Foreign key constraint not added.\n";
}

// Add indexes for better query performance
$indexesSQL = "CREATE INDEX idx_users_society_id ON users(society_id);
              CREATE INDEX idx_users_circuit_id ON users(circuit_id);
              CREATE INDEX idx_users_diocese_id ON users(diocese_id)";

foreach (explode(';', $indexesSQL) as $indexSQL) {
    $indexSQL = trim($indexSQL);
    if (!empty($indexSQL)) {
        executeSQLWithErrorHandling($indexSQL, "Adding index");
    }
}

echo "\nMigration completed.\n";
echo "===========================================\n";
echo "Next steps:\n";
echo "1. Update user management forms to include entity selection\n";
echo "2. Update authentication system to handle entity-specific roles\n";
echo "3. Update access control checks to consider entity associations\n";
echo "===========================================\n";
?>
