-- Payment System Database Migration
-- Run this SQL to create the payment tables

-- Payment Methods table (stores QR codes and payment method details)
CREATE TABLE IF NOT EXISTS payment_methods (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    type ENUM('esewa', 'khalti', 'bank', 'ime_pay', 'connect_ips', 'other') NOT NULL,
    account_name VARCHAR(255),
    account_number VARCHAR(100),
    qr_code_url VARCHAR(500),
    instructions TEXT,
    is_active TINYINT(1) DEFAULT 1,
    display_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Payment Submissions table (tracks user payment screenshots and verification)
-- Note: Foreign keys removed to avoid data type mismatch issues
-- Referential integrity is enforced at the application level
CREATE TABLE IF NOT EXISTS payment_submissions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    enrollment_id INT NOT NULL,
    payment_method_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    screenshot_url VARCHAR(500) NOT NULL,
    transaction_id VARCHAR(100),
    notes TEXT,
    status ENUM('pending', 'verified', 'rejected') DEFAULT 'pending',
    rejection_reason TEXT,
    submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    verified_at TIMESTAMP NULL,
    verified_by INT NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_enrollment_id (enrollment_id),
    INDEX idx_status (status),
    INDEX idx_payment_method_id (payment_method_id)
);

-- Insert sample payment methods (you can update QR code URLs via admin panel)
INSERT INTO payment_methods (name, type, account_name, account_number, instructions, display_order) VALUES
('eSewa', 'esewa', 'Ashlya Academy', '9800000000', 'Scan QR code or send payment to the eSewa ID shown. Include course name in remarks.', 1),
('Khalti', 'khalti', 'Ashlya Academy', '9800000000', 'Scan QR code or send payment to the Khalti ID shown. Include course name in remarks.', 2),
('Bank Transfer', 'bank', 'Ashlya Academy Pvt Ltd', '1234567890123456', 'Transfer to the bank account shown. Include enrollment ID in remarks.', 3),
('IME Pay', 'ime_pay', 'Ashlya Academy', '9800000000', 'Scan QR code or send via IME Pay. Include course name in remarks.', 4);
