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

// Add customer_number column to students table if it doesn't exist
try {
    // Check if the customer_number column exists in the students table
    $checkQuery = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
                   WHERE TABLE_NAME = 'students' 
                   AND COLUMN_NAME = 'customer_number'";
    $result = $pdo->query($checkQuery);
    
    if ($result->rowCount() == 0) {
        // Column doesn't exist, so add it
        $alterQuery = "ALTER TABLE students 
                       ADD COLUMN customer_number VARCHAR(20) UNIQUE AFTER application_no";
        $pdo->exec($alterQuery);
        echo "SUCCESS: customer_number column added to students table.";
        
        // Now update existing records with customer numbers based on their IDs
        $stmt = $pdo->query("SELECT id, student_id FROM students WHERE customer_number IS NULL OR customer_number = ''");
        $students = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        foreach ($students as $student) {
            // Generate customer number in format CUST + current year + padded ID
            $customer_number = 'CUST' . date('Y') . str_pad($student['id'], 5, '0', STR_PAD_LEFT);
            $update_stmt = $pdo->prepare("UPDATE students SET customer_number = ? WHERE id = ?");
            $update_stmt->execute([$customer_number, $student['id']]);
        }
        
        echo "\nExisting records updated with customer numbers.";
    } else {
        echo "INFO: customer_number column already exists in students table.";
        
        // Update any records that might have empty customer_number values
        $stmt = $pdo->query("SELECT id, student_id FROM students WHERE customer_number IS NULL OR customer_number = ''");
        $students = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        if (count($students) > 0) {
            echo "\nUpdating " . count($students) . " records with empty customer numbers...";
            
            foreach ($students as $student) {
                // Generate customer number in format CUST + current year + padded ID
                $customer_number = 'CUST' . date('Y') . str_pad($student['id'], 5, '0', STR_PAD_LEFT);
                $update_stmt = $pdo->prepare("UPDATE students SET customer_number = ? WHERE id = ?");
                $update_stmt->execute([$customer_number, $student['id']]);
            }
            
            echo "\nRecords updated successfully.";
        } else {
            echo "\nAll records already have customer numbers.";
        }
    }
} catch (Exception $e) {
    echo "ERROR: " . $e->getMessage();
}
?>