<?php
require_once 'config/database.php';

try {
    // Check if customer_no column exists in admission_applications table
    $stmt = $pdo->query("SHOW COLUMNS FROM admission_applications LIKE 'customer_no'");
    $column_exists = $stmt->fetch();

    if ($column_exists) {
        echo "Column customer_no already exists in admission_applications table.\n";
    } else {
        echo "Adding customer_no column to admission_applications table...\n";
        
        // Add the customer_no column to the table
        $pdo->exec("ALTER TABLE admission_applications ADD COLUMN customer_no VARCHAR(20) UNIQUE AFTER application_no");
        
        echo "Column customer_no added successfully to admission_applications table.\n";
        
        // Update existing records with customer numbers based on their IDs
        $stmt = $pdo->query("SELECT id, application_no FROM admission_applications WHERE customer_no IS NULL OR customer_no = ''");
        $applications = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        foreach ($applications as $application) {
            $customer_no = 'CUST' . date('Y') . str_pad($application['id'], 5, '0', STR_PAD_LEFT);
            $update_stmt = $pdo->prepare("UPDATE admission_applications SET customer_no = ? WHERE id = ?");
            $update_stmt->execute([$customer_no, $application['id']]);
        }
        
        echo "Existing records updated with customer numbers.\n";
    }
    
} catch (Exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
}
?>