<?php
/**
 * Migration Script: Add Complimentary Payment Status
 * 
 * This script adds the 'complimentary' payment status for free registrations
 * and updates existing records accordingly.
 * 
 * Run this script once to apply the database changes.
 */

require_once 'db_config.php';

echo "<h1>Complimentary Payment Status Migration</h1>";
echo "<pre>";

try {
    $pdo = getDBConnection();
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    echo "========================================\n";
    echo "Starting Migration Process\n";
    echo "========================================\n\n";
    
    // Check current status distribution
    echo "Step 1: Analyzing current payment status distribution...\n";
    $stmt = $pdo->query("
        SELECT 
            payment_status,
            COUNT(*) as count,
            SUM(total_amount) as total_amount
        FROM registrations
        GROUP BY payment_status
    ");
    $before = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    echo "Current Distribution:\n";
    foreach ($before as $row) {
        echo "  - {$row['payment_status']}: {$row['count']} registrations (\${$row['total_amount']})\n";
    }
    echo "\n";
    
    // Check for free registrations marked as paid
    $stmt = $pdo->query("
        SELECT COUNT(*) as count
        FROM registrations
        WHERE total_amount = 0 AND payment_status = 'paid'
    ");
    $freeCount = $stmt->fetchColumn();
    echo "Found {$freeCount} free registrations currently marked as 'paid'\n\n";
    
    // Step 2: Alter table to add complimentary status
    echo "Step 2: Adding 'complimentary' to payment_status ENUM...\n";
    $pdo->exec("
        ALTER TABLE `registrations` 
        MODIFY COLUMN `payment_status` 
        ENUM('pending','paid','failed','expired','cancelled','complimentary') 
        DEFAULT 'pending'
        COMMENT 'Payment status: complimentary for free/zero-cost registrations'
    ");
    echo "  ✓ ENUM updated successfully\n\n";
    
    // Step 3: Add helper columns
    echo "Step 3: Adding registration composition tracking columns...\n";
    
    // Check if columns already exist
    $stmt = $pdo->query("SHOW COLUMNS FROM registrations LIKE 'has_complimentary_items'");
    if ($stmt->rowCount() == 0) {
        $pdo->exec("
            ALTER TABLE `registrations`
            ADD COLUMN `has_complimentary_items` BOOLEAN DEFAULT FALSE 
            COMMENT 'Registration includes \$0 products' AFTER `payment_status`
        ");
        echo "  ✓ Added has_complimentary_items column\n";
    } else {
        echo "  ℹ has_complimentary_items column already exists\n";
    }
    
    $stmt = $pdo->query("SHOW COLUMNS FROM registrations LIKE 'has_paid_items'");
    if ($stmt->rowCount() == 0) {
        $pdo->exec("
            ALTER TABLE `registrations`
            ADD COLUMN `has_paid_items` BOOLEAN DEFAULT FALSE 
            COMMENT 'Registration includes paid products' AFTER `has_complimentary_items`
        ");
        echo "  ✓ Added has_paid_items column\n";
    } else {
        echo "  ℹ has_paid_items column already exists\n";
    }
    echo "\n";
    
    // Step 4: Add indexes
    echo "Step 4: Adding indexes for performance...\n";
    try {
        $pdo->exec("
            ALTER TABLE `registrations`
            ADD INDEX `idx_complimentary_flags` (has_complimentary_items, has_paid_items)
        ");
        echo "  ✓ Added composite index\n";
    } catch (PDOException $e) {
        if (strpos($e->getMessage(), 'Duplicate key name') !== false) {
            echo "  ℹ Index already exists\n";
        } else {
            throw $e;
        }
    }
    echo "\n";
    
    // Step 5: Analyze and flag registration composition
    echo "Step 5: Analyzing registration composition...\n";
    $pdo->exec("
        UPDATE registrations r
        SET 
            has_complimentary_items = (
                SELECT COUNT(*) > 0
                FROM registration_products rp
                WHERE rp.registration_id = r.id AND rp.unit_price = 0
            ),
            has_paid_items = (
                SELECT COUNT(*) > 0
                FROM registration_products rp
                WHERE rp.registration_id = r.id AND rp.unit_price > 0
            )
    ");
    echo "  ✓ Registration composition flagged\n\n";
    
    // Step 6: Update free registrations to complimentary
    echo "Step 6: Updating free registrations to 'complimentary' status...\n";
    $stmt = $pdo->prepare("
        UPDATE registrations
        SET payment_status = 'complimentary'
        WHERE total_amount = 0 
          AND payment_status = 'paid'
          AND has_complimentary_items = TRUE
          AND has_paid_items = FALSE
    ");
    $stmt->execute();
    $updated = $stmt->rowCount();
    echo "  ✓ Updated {$updated} registrations to 'complimentary' status\n\n";
    
    // Step 7: Display results
    echo "Step 7: Verification - New payment status distribution:\n";
    $stmt = $pdo->query("
        SELECT 
            payment_status,
            COUNT(*) as count,
            SUM(total_amount) as total_amount,
            SUM(delegate_count) as total_delegates
        FROM registrations
        GROUP BY payment_status
        ORDER BY 
            CASE payment_status
                WHEN 'paid' THEN 1
                WHEN 'complimentary' THEN 2
                WHEN 'pending' THEN 3
                WHEN 'failed' THEN 4
                WHEN 'expired' THEN 5
                WHEN 'cancelled' THEN 6
            END
    ");
    $after = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    foreach ($after as $row) {
        $amount = number_format($row['total_amount'], 2);
        echo "  - {$row['payment_status']}: {$row['count']} registrations, {$row['total_delegates']} delegates (\${$amount})\n";
    }
    echo "\n";
    
    // Display sample complimentary registrations
    echo "Sample Complimentary Registrations:\n";
    $stmt = $pdo->query("
        SELECT 
            registration_number,
            contact_full_name,
            university_name,
            delegate_count,
            total_amount,
            created_at
        FROM registrations
        WHERE payment_status = 'complimentary'
        LIMIT 5
    ");
    $samples = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    if (count($samples) > 0) {
        foreach ($samples as $sample) {
            echo "  • {$sample['registration_number']} - {$sample['contact_full_name']} ({$sample['university_name']})\n";
            echo "    Delegates: {$sample['delegate_count']}, Amount: \${$sample['total_amount']}, Date: {$sample['created_at']}\n";
        }
    } else {
        echo "  No complimentary registrations found.\n";
    }
    echo "\n";
    
    echo "========================================\n";
    echo "✅ Migration Completed Successfully!\n";
    echo "========================================\n\n";
    
    echo "Next Steps:\n";
    echo "1. Update api.php to mark new free registrations as 'complimentary'\n";
    echo "2. Update admin pages to display and filter complimentary status\n";
    echo "3. Update CSS to style complimentary status badges\n";
    echo "4. Test the system with a new free registration\n\n";
    
} catch (Exception $e) {
    echo "\n❌ Migration Failed!\n";
    echo "Error: " . $e->getMessage() . "\n";
    echo "Trace: " . $e->getTraceAsString() . "\n";
    exit(1);
}

echo "</pre>";
?>

