<?php
/**
 * Migration: Add learner promotion workflow support
 * 
 * Adds approval workflow fields to learner_promotions table:
 * - status (pending, approved, rejected)
 * - balance fields for tracking financial carryover
 * - audit fields (created_by, approved_by, timestamps)
 */

error_reporting(E_ALL);
ini_set('display_errors', 1);

$configPath = __DIR__ . '/../Config.php';
if (!file_exists($configPath)) {
    die("Config.php not found at: $configPath\n");
}
require_once $configPath;

try {
    $pdo = new PDO(
        'mysql:host=' . $host . ';dbname=' . $db . ';charset=utf8mb4',
        $user,
        $pass,
        [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
    );

    $statements = [
        // Check if columns exist before adding them
        "ALTER TABLE learner_promotions 
         ADD COLUMN IF NOT EXISTS status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending' AFTER note",
        
        "ALTER TABLE learner_promotions 
         ADD COLUMN IF NOT EXISTS balance_before DECIMAL(10,2) NOT NULL DEFAULT 0.00 AFTER status",
        
        "ALTER TABLE learner_promotions 
         ADD COLUMN IF NOT EXISTS balance_carried DECIMAL(10,2) NOT NULL DEFAULT 0.00 AFTER balance_before",
        
        "ALTER TABLE learner_promotions 
         ADD COLUMN IF NOT EXISTS created_by INT(11) DEFAULT NULL AFTER balance_carried",
        
        "ALTER TABLE learner_promotions 
         ADD COLUMN IF NOT EXISTS approved_by INT(11) DEFAULT NULL AFTER created_by",
        
        "ALTER TABLE learner_promotions 
         ADD COLUMN IF NOT EXISTS created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER approved_by",
        
        "ALTER TABLE learner_promotions 
         ADD COLUMN IF NOT EXISTS approved_at DATETIME DEFAULT NULL AFTER created_at",
        
        // Add indexes for better query performance
        "ALTER TABLE learner_promotions ADD INDEX IF NOT EXISTS idx_status (status)",
        "ALTER TABLE learner_promotions ADD INDEX IF NOT EXISTS idx_learner_year (learner_id, promotion_year)",
    ];

    foreach ($statements as $sql) {
        try {
            $pdo->exec($sql);
            echo "✓ Executed: " . substr($sql, 0, 50) . "...\n";
        } catch (Exception $e) {
            echo "! Statement skipped (already exists or error): " . substr($sql, 0, 50) . "\n";
        }
    }

    echo "\n✓ Migration completed successfully!\n";
    echo "Learner promotions table is now configured for approval workflow.\n";

} catch (Exception $e) {
    echo "✗ Migration failed: " . $e->getMessage() . "\n";
    exit(1);
}
?>
