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

echo "<h2>ডাটাবেস ফিক্স করা হচ্ছে...</h2>";

try {
    // Drop table if exists (to avoid conflicts)
    $pdo->exec("DROP TABLE IF EXISTS fee_payments");
    echo "<p>পুরানো টেবিল ডিলিট করা হয়েছে</p>";
    
    // Create fee_payments table
    $create_sql = "
    CREATE TABLE fee_payments (
        id INT AUTO_INCREMENT PRIMARY KEY,
        fee_id INT NOT NULL,
        amount DECIMAL(10,2) NOT NULL,
        payment_date DATETIME NOT NULL,
        payment_method VARCHAR(50) DEFAULT 'cash',
        transaction_id VARCHAR(100) NULL,
        notes TEXT NULL,
        created_by INT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    )";
    
    $pdo->exec($create_sql);
    echo "<p>✅ fee_payments টেবিল তৈরি হয়েছে</p>";
    
    // Insert sample data from existing fees
    $insert_sql = "
    INSERT INTO fee_payments (fee_id, amount, payment_date, payment_method, transaction_id, notes, created_by)
    SELECT 
        f.id,
        CASE 
            WHEN f.paid_amount > 0 THEN f.paid_amount
            ELSE ROUND(f.amount * 0.3, 2)
        END as amount,
        DATE_ADD(f.created_at, INTERVAL FLOOR(RAND() * 10) DAY) as payment_date,
        CASE 
            WHEN RAND() < 0.5 THEN 'cash'
            WHEN RAND() < 0.8 THEN 'bank_transfer'
            ELSE 'mobile_banking'
        END as payment_method,
        CONCAT('TXN-', DATE_FORMAT(NOW(), '%Y%m%d'), '-', LPAD(f.id, 4, '0')) as transaction_id,
        CASE 
            WHEN f.fee_type LIKE '%বেতন%' THEN 'মাসিক বেতন পরিশোধ'
            WHEN f.fee_type LIKE '%পরীক্ষা%' THEN 'পরীক্ষা ফি পরিশোধ'
            WHEN f.fee_type LIKE '%ভর্তি%' THEN 'ভর্তি ফি পরিশোধ'
            ELSE 'ফি পরিশোধ'
        END as notes,
        1 as created_by
    FROM fees f 
    WHERE f.amount > 0
    ORDER BY f.id
    LIMIT 100";
    
    $stmt = $pdo->prepare($insert_sql);
    $stmt->execute();
    $inserted = $stmt->rowCount();
    echo "<p>✅ $inserted টি পেমেন্ট রেকর্ড যোগ করা হয়েছে</p>";
    
    // Update transaction IDs for different payment methods
    $pdo->exec("
        UPDATE fee_payments 
        SET transaction_id = CONCAT('BANK-', DATE_FORMAT(payment_date, '%Y%m%d'), '-', LPAD(id, 4, '0'))
        WHERE payment_method = 'bank_transfer'
    ");
    
    $pdo->exec("
        UPDATE fee_payments 
        SET transaction_id = CONCAT('MOBILE-', DATE_FORMAT(payment_date, '%Y%m%d'), '-', LPAD(id, 4, '0'))
        WHERE payment_method = 'mobile_banking'
    ");
    
    echo "<p>✅ ট্রানজেকশন আইডি আপডেট করা হয়েছে</p>";
    
    // Show final count
    $count = $pdo->query("SELECT COUNT(*) FROM fee_payments")->fetchColumn();
    echo "<p><strong>মোট পেমেন্ট রেকর্ড: $count</strong></p>";
    
    // Show sample data
    echo "<h3>নমুনা ডাটা:</h3>";
    $samples = $pdo->query("
        SELECT 
            fp.*,
            f.fee_type,
            s.first_name,
            s.last_name,
            s.roll_number
        FROM fee_payments fp
        JOIN fees f ON fp.fee_id = f.id
        JOIN students s ON f.student_id = s.id
        ORDER BY fp.id DESC
        LIMIT 5
    ")->fetchAll();
    
    if (!empty($samples)) {
        echo "<table border='1' style='border-collapse: collapse; width: 100%; font-size: 12px;'>";
        echo "<tr style='background: #f0f0f0;'>";
        echo "<th>ID</th><th>শিক্ষার্থী</th><th>ফি টাইপ</th><th>পরিমাণ</th><th>তারিখ</th><th>পদ্ধতি</th><th>ট্রানজেকশন</th><th>নোট</th>";
        echo "</tr>";
        
        foreach ($samples as $payment) {
            echo "<tr>";
            echo "<td>" . $payment['id'] . "</td>";
            echo "<td>" . $payment['first_name'] . " " . $payment['last_name'] . " (" . $payment['roll_number'] . ")</td>";
            echo "<td>" . $payment['fee_type'] . "</td>";
            echo "<td>৳" . number_format($payment['amount'], 2) . "</td>";
            echo "<td>" . date('d/m/Y', strtotime($payment['payment_date'])) . "</td>";
            echo "<td>" . $payment['payment_method'] . "</td>";
            echo "<td>" . $payment['transaction_id'] . "</td>";
            echo "<td>" . $payment['notes'] . "</td>";
            echo "</tr>";
        }
        echo "</table>";
    }
    
    echo "<br><div style='background: #d4edda; padding: 15px; border-radius: 5px; border: 1px solid #c3e6cb;'>";
    echo "<h3 style='color: #155724; margin: 0;'>✅ সেটআপ সম্পন্ন!</h3>";
    echo "<p style='margin: 10px 0 0 0;'>এখন ইনভয়েজ সিস্টেম ব্যবহার করতে পারবেন।</p>";
    echo "</div>";
    
    echo "<br><p>";
    echo "<a href='invoice.php' style='background: #007bff; color: white; padding: 12px 24px; text-decoration: none; border-radius: 5px; margin-right: 10px;'>ইনভয়েজ পেজে যান</a>";
    echo "<a href='test_invoice.php' style='background: #28a745; color: white; padding: 12px 24px; text-decoration: none; border-radius: 5px;'>টেস্ট করুন</a>";
    echo "</p>";
    
} catch (Exception $e) {
    echo "<div style='background: #f8d7da; padding: 15px; border-radius: 5px; border: 1px solid #f5c6cb;'>";
    echo "<h3 style='color: #721c24; margin: 0;'>❌ ত্রুটি হয়েছে</h3>";
    echo "<p style='margin: 10px 0 0 0; color: #721c24;'>" . $e->getMessage() . "</p>";
    echo "</div>";
    
    // Show database connection info
    echo "<h4>ডাটাবেস তথ্য:</h4>";
    try {
        $db_name = $pdo->query("SELECT DATABASE()")->fetchColumn();
        echo "<p>ডাটাবেস: $db_name</p>";
        
        $tables = $pdo->query("SHOW TABLES")->fetchAll(PDO::FETCH_COLUMN);
        echo "<p>টেবিল সংখ্যা: " . count($tables) . "</p>";
        echo "<p>টেবিল তালিকা: " . implode(', ', array_slice($tables, 0, 10)) . "</p>";
        
    } catch (Exception $e2) {
        echo "<p>ডাটাবেস তথ্য পাওয়া যায়নি: " . $e2->getMessage() . "</p>";
    }
}
?>