-- Create fee_payments table for tracking individual payments
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,
    
    FOREIGN KEY (fee_id) REFERENCES fees(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    
    INDEX idx_fee_id (fee_id),
    INDEX idx_payment_date (payment_date),
    INDEX idx_payment_method (payment_method)
);

-- Insert sample payment data (if fees exist)
INSERT INTO fee_payments (fee_id, amount, payment_date, payment_method, transaction_id, notes, created_by)
SELECT 
    f.id,
    f.paid_amount,
    f.created_at,
    'cash',
    CONCAT('TXN-', DATE_FORMAT(f.created_at, '%Y%m%d'), '-', f.id),
    'Initial payment record',
    1
FROM fees f 
WHERE f.paid_amount > 0 
AND NOT EXISTS (
    SELECT 1 FROM fee_payments fp WHERE fp.fee_id = f.id
)
LIMIT 50;

-- Update some payment methods for variety
UPDATE fee_payments 
SET payment_method = 'bank_transfer', 
    transaction_id = CONCAT('BANK-', DATE_FORMAT(payment_date, '%Y%m%d'), '-', id)
WHERE id % 3 = 0;

UPDATE fee_payments 
SET payment_method = 'mobile_banking', 
    transaction_id = CONCAT('MOBILE-', DATE_FORMAT(payment_date, '%Y%m%d'), '-', id)
WHERE id % 4 = 0;

-- Add some notes
UPDATE fee_payments 
SET notes = 'Monthly tuition fee payment'
WHERE id % 2 = 0;

UPDATE fee_payments 
SET notes = 'Exam fee payment'
WHERE id % 5 = 0;