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

echo "<h2>fee_payments টেবিল তৈরি করা হচ্ছে...</h2>";

try {
    // Create fee_payments table
    $sql = "
    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 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,
        
        INDEX idx_fee_id (fee_id),
        INDEX idx_payment_date (payment_date),
        INDEX idx_payment_method (payment_method)
    )";
    
    $pdo->exec($sql);
    echo "<p>✅ fee_payments টেবিল তৈরি হয়েছে</p>";
    
    // Insert sample data
    $insert_sql = "
    INSERT IGNORE 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.5, 2)
        END,
        DATE_ADD(f.created_at, INTERVAL FLOOR(RAND() * 15) DAY),
        CASE 
            WHEN RAND() < 0.6 THEN 'cash'
            WHEN RAND() < 0.8 THEN 'bank_transfer'
            ELSE 'mobile_banking'
        END,
        CONCAT('TXN-', DATE_FORMAT(NOW(), '%Y%m%d'), '-', f.id),
        CASE 
            WHEN f.fee_type LIKE '%বেতন%' THEN 'মাসিক বেতন পরিশোধ'
            WHEN f.fee_type LIKE '%পরীক্ষা%' THEN 'পরীক্ষা ফি পরিশোধ'
            ELSE 'ফি পরিশোধ'
        END,
        1
    FROM fees f 
    WHERE f.amount > 0
    LIMIT 50";
    
    $stmt = $pdo->prepare($insert_sql);
    $stmt->execute();
    $inserted = $stmt->rowCount();
    echo "<p>✅ $inserted টি নমুনা পেমেন্ট রেকর্ড যোগ করা হয়েছে</p>";
    
    // Update transaction IDs
    $pdo->exec("
        UPDATE fee_payments 
        SET transaction_id = CONCAT('BANK-', DATE_FORMAT(payment_date, '%Y%m%d'), '-', id)
        WHERE payment_method = 'bank_transfer'
    ");
    
    $pdo->exec("
        UPDATE fee_payments 
        SET transaction_id = CONCAT('MOBILE-', DATE_FORMAT(payment_date, '%Y%m%d'), '-', id)
        WHERE payment_method = 'mobile_banking'
    ");
    
    echo "<p>✅ ট্রানজেকশন আইডি আপডেট করা হয়েছে</p>";
    
    // Show count
    $count = $pdo->query("SELECT COUNT(*) FROM fee_payments")->fetchColumn();
    echo "<p>মোট পেমেন্ট রেকর্ড: $count</p>";
    
    echo "<h3>✅ সেটআপ সম্পন্ন!</h3>";
    echo "<p><a href='invoice.php' style='background: #007bff; color: white; padding: 10px 20px; text-decoration: none; border-radius: 5px;'>ইনভয়েজ পেজে যান</a></p>";
    
} catch (Exception $e) {
    echo "<p style='color: red;'>❌ ত্রুটি: " . $e->getMessage() . "</p>";
    echo "<p>SQL: " . htmlspecialchars($sql) . "</p>";
}
?>