<?php
// Quick database fix script
require_once 'config/database.php';

echo "<h1>Creating Missing Database Tables</h1>";

$tables_sql = [
    "CREATE TABLE IF NOT EXISTS academic_sessions (
        id INT AUTO_INCREMENT PRIMARY KEY,
        session_name VARCHAR(20) NOT NULL UNIQUE,
        start_date DATE,
        end_date DATE,
        status ENUM('active', 'inactive') DEFAULT 'active',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    )",
    
    "CREATE TABLE IF NOT EXISTS student_groups (
        id INT AUTO_INCREMENT PRIMARY KEY,
        group_name VARCHAR(100) NOT NULL,
        group_code VARCHAR(20) NOT NULL UNIQUE,
        description TEXT,
        status ENUM('active', 'inactive') DEFAULT 'active',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    )",
    
    "CREATE TABLE IF NOT EXISTS fees (
        id INT AUTO_INCREMENT PRIMARY KEY,
        student_id INT NOT NULL,
        fee_type VARCHAR(100) NOT NULL,
        amount DECIMAL(10,2) NOT NULL,
        paid_amount DECIMAL(10,2) DEFAULT 0,
        due_date DATE,
        paid_date DATE,
        description TEXT,
        month VARCHAR(20),
        academic_year VARCHAR(20),
        status ENUM('pending', 'partial', 'paid', 'overdue') DEFAULT 'pending',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
    )",
    
    "CREATE TABLE IF NOT EXISTS fee_payments (
        id INT AUTO_INCREMENT PRIMARY KEY,
        fee_id INT NOT NULL,
        amount DECIMAL(10,2) NOT NULL,
        payment_date DATE NOT NULL,
        payment_method VARCHAR(50),
        transaction_id VARCHAR(100),
        notes TEXT,
        created_by INT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (fee_id) REFERENCES fees(id) ON DELETE CASCADE,
        FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
    )"
];

$default_data = [
    "INSERT IGNORE INTO academic_sessions (session_name) VALUES ('2023-2024'), ('2024-2025'), ('2025-2026')",
    "INSERT IGNORE INTO student_groups (group_name, group_code) VALUES ('বিজ্ঞান', 'SCI'), ('মানবিক', 'HUM'), ('বাণিজ্য', 'BUS')"
];

// Create tables
echo "<h2>Creating tables...</h2>";
foreach ($tables_sql as $index => $sql) {
    try {
        $pdo->exec($sql);
        echo "<p style='color: green;'>✓ Table " . ($index + 1) . " created successfully</p>";
    } catch (Exception $e) {
        echo "<p style='color: red;'>✗ Error creating table: " . $e->getMessage() . "</p>";
    }
}

// Insert default data
echo "<h2>Inserting default data...</h2>";
foreach ($default_data as $sql) {
    try {
        $pdo->exec($sql);
        echo "<p style='color: green;'>✓ Default data inserted</p>";
    } catch (Exception $e) {
        echo "<p style='color: red;'>✗ Error inserting data: " . $e->getMessage() . "</p>";
    }
}

echo "<h2 style='color: green;'>✓ Database setup complete!</h2>";
echo "<p><a href='fees/bill_submission.php'><strong>Try accessing the bill submission page now</strong></a></p>";
?>