<?php
// Database fix script - Run this to fix missing tables and fields
echo "<h2>Database Fix Script</h2>";
echo "<style>body{font-family:Arial;margin:20px;} .ok{color:green;} .error{color:red;} .info{color:blue;}</style>";

try {
    // Connect to database
    $pdo = new PDO("mysql:host=localhost;dbname=student_management", "root", "");
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "<div class='ok'>✓ Database connection successful</div>";

    // Create invoice_settings table
    $create_invoice_settings = "
    CREATE TABLE IF NOT EXISTS invoice_settings (
        id INT AUTO_INCREMENT PRIMARY KEY,
        setting_key VARCHAR(100) UNIQUE NOT NULL,
        setting_value TEXT,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    )";
    
    $pdo->exec($create_invoice_settings);
    echo "<div class='ok'>✓ Invoice settings table created/verified</div>";

    // Insert default invoice settings
    $default_settings = [
        'school_name' => 'Student Management System',
        'school_address' => 'Your School Address, City, State, PIN',
        'school_contact' => 'Phone: +91-XXXXXXXXXX | Email: info@yourschool.com',
        'gstin' => '22AAAAA0000A1Z5',
        'bank_name' => 'Your Bank Name',
        'account_number' => 'XXXXXXXXXXXXXX',
        'ifsc_code' => 'XXXX0000000'
    ];

    foreach ($default_settings as $key => $value) {
        $query = "INSERT IGNORE INTO invoice_settings (setting_key, setting_value) VALUES (?, ?)";
        $stmt = $pdo->prepare($query);
        $stmt->execute([$key, $value]);
    }
    echo "<div class='ok'>✓ Default invoice settings inserted</div>";

    // Check and add missing columns to students table
    $columns_to_add = [
        'reference_name' => 'VARCHAR(100) NULL',
        'exit_date' => 'DATE NULL',
        'document_name' => "ENUM('10th Marksheet', '12th Marksheet', 'Aadhar Card', 'Pan Card', 'Voter ID') NULL"
    ];

    foreach ($columns_to_add as $column => $definition) {
        try {
            $pdo->exec("ALTER TABLE students ADD COLUMN $column $definition");
            echo "<div class='ok'>✓ Added column '$column' to students table</div>";
        } catch (PDOException $e) {
            if (strpos($e->getMessage(), 'Duplicate column name') !== false) {
                echo "<div class='info'>ℹ Column '$column' already exists in students table</div>";
            } else {
                echo "<div class='error'>✗ Error adding column '$column': " . $e->getMessage() . "</div>";
            }
        }
    }

    // Check and add next_payment_date to fee_history table
    try {
        $pdo->exec("ALTER TABLE fee_history ADD COLUMN next_payment_date DATE NULL");
        echo "<div class='ok'>✓ Added column 'next_payment_date' to fee_history table</div>";
    } catch (PDOException $e) {
        if (strpos($e->getMessage(), 'Duplicate column name') !== false) {
            echo "<div class='info'>ℹ Column 'next_payment_date' already exists in fee_history table</div>";
        } else {
            echo "<div class='error'>✗ Error adding column 'next_payment_date': " . $e->getMessage() . "</div>";
        }
    }

    // Create indexes
    $indexes = [
        'idx_students_reference' => 'CREATE INDEX IF NOT EXISTS idx_students_reference ON students(reference_name)',
        'idx_students_document' => 'CREATE INDEX IF NOT EXISTS idx_students_document ON students(document_name)',
        'idx_students_exit_date' => 'CREATE INDEX IF NOT EXISTS idx_students_exit_date ON students(exit_date)'
    ];

    foreach ($indexes as $index_name => $sql) {
        try {
            $pdo->exec($sql);
            echo "<div class='ok'>✓ Created index '$index_name'</div>";
        } catch (PDOException $e) {
            echo "<div class='info'>ℹ Index '$index_name' already exists or error: " . $e->getMessage() . "</div>";
        }
    }

    // Test invoice generation
    echo "<h3>Testing Invoice Generation:</h3>";
    
    // Check if we have any fee history records
    $stmt = $pdo->query("SELECT COUNT(*) as count FROM fee_history");
    $count = $stmt->fetch(PDO::FETCH_ASSOC)['count'];
    
    if ($count > 0) {
        echo "<div class='ok'>✓ Found $count fee history records</div>";
        
        // Get a sample record
        $stmt = $pdo->query("SELECT fh.id FROM fee_history fh LIMIT 1");
        $sample = $stmt->fetch(PDO::FETCH_ASSOC);
        
        if ($sample) {
            echo "<div class='ok'>✓ Sample record found (ID: {$sample['id']})</div>";
            echo "<div class='info'>ℹ You can now test invoice generation with: <a href='invoice_pdf.php?id={$sample['id']}' target='_blank'>invoice_pdf.php?id={$sample['id']}</a></div>";
        }
    } else {
        echo "<div class='info'>ℹ No fee history records found. Create a fee deposit first to test invoice generation.</div>";
    }

    echo "<hr>";
    echo "<h3>Database Fix Complete!</h3>";
    echo "<div class='ok'>✓ All required tables and fields are now available</div>";
    echo "<p><a href='fee_deposit.php'>Go to Fee Deposit</a> | <a href='invoice_settings.php'>Configure Invoice Settings</a> | <a href='index.php'>Dashboard</a></p>";

} catch (PDOException $e) {
    echo "<div class='error'>✗ Database error: " . $e->getMessage() . "</div>";
    echo "<p>Make sure:</p>";
    echo "<ul>";
    echo "<li>MySQL is running in XAMPP</li>";
    echo "<li>Database 'student_management' exists</li>";
    echo "<li>Database credentials are correct</li>";
    echo "</ul>";
}
?>
