<?php
/**
 * Database Migration: Make Department Field Required
 * 
 * This script:
 * 1. Updates existing NULL department values to a default value
 * 2. Changes the department column from NULL to NOT NULL
 * 
 * Safe to run on production - includes checks and backups.
 * 
 * Created: 2025-10-24
 */

set_time_limit(300);
require_once 'db_config.php';

$isCLI = (php_sapi_name() === 'cli');
if (!$isCLI) {
    echo '<!DOCTYPE html>
    <html>
    <head>
        <meta charset="UTF-8">
        <title>Make Department Required</title>
        <style>
            body { font-family: Arial, sans-serif; max-width: 900px; margin: 50px auto; padding: 20px; background: #f5f5f5; }
            .container { background: white; padding: 30px; border-radius: 8px; box-shadow: 0 2px 4px rgba(0,0,0,0.1); }
            h1 { color: #2c3e50; border-bottom: 3px solid #3498db; padding-bottom: 10px; }
            .step { background: #ecf0f1; padding: 15px; margin: 15px 0; border-left: 4px solid #3498db; border-radius: 4px; }
            .success { background: #d4edda; border-left-color: #28a745; color: #155724; }
            .error { background: #f8d7da; border-left-color: #dc3545; color: #721c24; }
            .warning { background: #fff3cd; border-left-color: #ffc107; color: #856404; }
            .info { background: #d1ecf1; border-left-color: #17a2b8; color: #0c5460; }
            pre { background: #2c3e50; color: #ecf0f1; padding: 15px; border-radius: 4px; overflow-x: auto; }
        </style>
    </head>
    <body>
        <div class="container">';
}

function output($message, $type = 'info') {
    global $isCLI;
    $timestamp = date('Y-m-d H:i:s');
    
    if ($isCLI) {
        $prefix = match($type) {
            'success' => '✓',
            'error' => '✗',
            'warning' => '⚠',
            default => 'ℹ'
        };
        echo "[$timestamp] $prefix $message\n";
    } else {
        echo "<div class='step $type'>";
        echo "<span style='color: #7f8c8d; font-size: 0.9em;'>[$timestamp]</span><br>";
        echo $message;
        echo "</div>";
        flush();
    }
}

try {
    if (!$isCLI) {
        echo "<h1>🔄 Make Department Field Required</h1>";
    }
    
    // Connect to database
    output("Connecting to database...", 'info');
    $pdo = getDBConnection();
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    output("✅ Database connection established", 'success');
    
    // Check current column status
    output("<h2>Step 1: Checking Current Status</h2>", 'info');
    
    $columnInfo = $pdo->query("
        SELECT COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'delegates' 
        AND COLUMN_NAME = 'department'
    ")->fetch(PDO::FETCH_ASSOC);
    
    if (!$columnInfo) {
        throw new Exception("Department column does not exist! Run the department field migration first.");
    }
    
    output("Current column status: {$columnInfo['COLUMN_TYPE']}, Nullable: {$columnInfo['IS_NULLABLE']}", 'info');
    
    if ($columnInfo['IS_NULLABLE'] === 'NO') {
        output("⚠️ Department is already required (NOT NULL). No migration needed.", 'warning');
        if (!$isCLI) {
            echo "</div></body></html>";
        }
        exit(0);
    }
    
    // Check for NULL values
    output("<h2>Step 2: Checking for NULL Values</h2>", 'info');
    
    $nullCount = $pdo->query("
        SELECT COUNT(*) 
        FROM delegates 
        WHERE department IS NULL OR department = ''
    ")->fetchColumn();
    
    if ($nullCount > 0) {
        output("Found <strong>$nullCount</strong> delegate(s) with NULL or empty department", 'warning');
        
        // Show sample records
        $samples = $pdo->query("
            SELECT id, full_name, designation, department 
            FROM delegates 
            WHERE department IS NULL OR department = ''
            LIMIT 5
        ")->fetchAll(PDO::FETCH_ASSOC);
        
        if (!$isCLI) {
            echo "<div class='step warning'>";
            echo "<strong>Sample records that will be updated:</strong><br>";
            echo "<pre>";
            foreach ($samples as $sample) {
                echo "ID: {$sample['id']} - {$sample['full_name']} ({$sample['designation']}) - Department: " . ($sample['department'] ?: 'NULL') . "\n";
            }
            if ($nullCount > 5) {
                echo "... and " . ($nullCount - 5) . " more\n";
            }
            echo "</pre>";
            echo "</div>";
        }
    } else {
        output("✅ No NULL or empty department values found", 'success');
    }
    
    // Create backup
    output("<h2>Step 3: Creating Backup</h2>", 'info');
    
    $backupDir = __DIR__ . '/backups';
    if (!is_dir($backupDir)) {
        mkdir($backupDir, 0755, true);
    }
    
    $timestamp = date('Y_m_d_H_i_s');
    $backupFile = $backupDir . "/delegates_before_department_required_$timestamp.sql";
    
    $delegates = $pdo->query("SELECT * FROM delegates ORDER BY id")->fetchAll(PDO::FETCH_ASSOC);
    
    $backupContent = "-- Backup before making department required\n";
    $backupContent .= "-- Created: " . date('Y-m-d H:i:s') . "\n\n";
    
    foreach ($delegates as $delegate) {
        $values = array_map(function($val) use ($pdo) {
            return $val === null ? 'NULL' : $pdo->quote($val);
        }, array_values($delegate));
        $backupContent .= "INSERT INTO delegates VALUES (" . implode(', ', $values) . ");\n";
    }
    
    file_put_contents($backupFile, $backupContent);
    output("✅ Backup created: $backupFile", 'success');
    
    // Update NULL values
    if ($nullCount > 0) {
        output("<h2>Step 4: Updating NULL Values</h2>", 'info');
        
        // Set a default value for NULL departments
        $defaultDepartment = 'Not Specified';
        
        $updateStmt = $pdo->prepare("
            UPDATE delegates 
            SET department = ? 
            WHERE department IS NULL OR department = ''
        ");
        $updateStmt->execute([$defaultDepartment]);
        
        $updated = $updateStmt->rowCount();
        output("✅ Updated <strong>$updated</strong> record(s) with default value: '$defaultDepartment'", 'success');
    }
    
    // Alter column to NOT NULL
    output("<h2>Step 5: Making Column Required</h2>", 'info');
    
    $pdo->exec("
        ALTER TABLE delegates 
        MODIFY COLUMN department VARCHAR(255) NOT NULL 
        COMMENT 'Required department for delegate'
    ");
    
    output("✅ Column modified to NOT NULL", 'success');
    
    // Verify
    output("<h2>Step 6: Verifying Changes</h2>", 'info');
    
    $verifyColumn = $pdo->query("
        SELECT COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'delegates' 
        AND COLUMN_NAME = 'department'
    ")->fetch(PDO::FETCH_ASSOC);
    
    if ($verifyColumn['IS_NULLABLE'] === 'NO') {
        output("✅ Verified: Department is now required (NOT NULL)", 'success');
    } else {
        throw new Exception("Verification failed: Column is still nullable");
    }
    
    // Final check
    $finalNullCount = $pdo->query("
        SELECT COUNT(*) 
        FROM delegates 
        WHERE department IS NULL OR department = ''
    ")->fetchColumn();
    
    if ($finalNullCount === 0) {
        output("✅ Verified: No NULL or empty department values", 'success');
    } else {
        output("⚠️ Warning: Still found $finalNullCount NULL/empty values", 'warning');
    }
    
    // Success summary
    output("<h2>✅ Migration Completed Successfully!</h2>", 'success');
    
    if (!$isCLI) {
        echo "<div class='step success'>";
        echo "<h3>Summary:</h3>";
        echo "<ul>";
        echo "<li>✅ Department field is now REQUIRED</li>";
        if ($nullCount > 0) {
            echo "<li>✅ Updated $updated existing NULL values to 'Not Specified'</li>";
        }
        echo "<li>✅ All delegate records preserved</li>";
        echo "<li>✅ Backup created: <code>$backupFile</code></li>";
        echo "</ul>";
        echo "<h3>Next Steps:</h3>";
        echo "<ol>";
        echo "<li>Test the registration form - Department should be required</li>";
        echo "<li>Check admin panel - Department edit should be required</li>";
        echo "<li>Review delegates with 'Not Specified' and update if needed</li>";
        echo "<li>Delete this migration file for security</li>";
        echo "</ol>";
        echo "</div>";
    }
    
} catch (Exception $e) {
    output("❌ MIGRATION FAILED: " . $e->getMessage(), 'error');
    
    if (!$isCLI) {
        echo "<div class='step error'>";
        echo "<h3>Error Details:</h3>";
        echo "<pre>" . htmlspecialchars($e->getTraceAsString()) . "</pre>";
        echo "</div>";
    }
    
    exit(1);
}

if (!$isCLI) {
    echo '</div></body></html>';
}
?>

