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

echo "Updating database structure...\n";

try {
    // Update fee_payments table to add missing columns
    
    // Add payment_method column if it doesn't exist
    $stmt = $pdo->query("SHOW COLUMNS FROM fee_payments LIKE 'payment_method'");
    $column_exists = $stmt->rowCount() > 0;
    if (!$column_exists) {
        $pdo->exec("ALTER TABLE fee_payments ADD COLUMN payment_method ENUM('cash', 'bank', 'online', 'cheque') DEFAULT 'cash'");
    }
    
    // Add transaction_id column if it doesn't exist
    $stmt = $pdo->query("SHOW COLUMNS FROM fee_payments LIKE 'transaction_id'");
    $column_exists = $stmt->rowCount() > 0;
    if (!$column_exists) {
        $pdo->exec("ALTER TABLE fee_payments ADD COLUMN transaction_id VARCHAR(100)");
    }
    
    // Add payment_date column if it doesn't exist
    $stmt = $pdo->query("SHOW COLUMNS FROM fee_payments LIKE 'payment_date'");
    $column_exists = $stmt->rowCount() > 0;
    if (!$column_exists) {
        $pdo->exec("ALTER TABLE fee_payments ADD COLUMN payment_date DATE NOT NULL");
    }
    
    // Add created_by column if it doesn't exist
    $stmt = $pdo->query("SHOW COLUMNS FROM fee_payments LIKE 'created_by'");
    $column_exists = $stmt->rowCount() > 0;
    if (!$column_exists) {
        $pdo->exec("ALTER TABLE fee_payments ADD COLUMN created_by INT");
    }
    
    // Add indexes if they don't exist
    $pdo->exec("CREATE INDEX IF NOT EXISTS idx_payment_date ON fee_payments(payment_date)");
    $pdo->exec("CREATE INDEX IF NOT EXISTS idx_payment_method ON fee_payments(payment_method)");
    
    echo "Database structure updated successfully.\n";
} catch (PDOException $e) {
    echo "Error updating database: " . $e->getMessage() . "\n";
}
?>