-- UNIOSUN Payment Platform Database Schema
CREATE DATABASE IF NOT EXISTS uniosun_payment;
USE uniosun_payment;

-- Payment Items Table
CREATE TABLE payment_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    payment_item_id VARCHAR(50) UNIQUE NOT NULL,
    payment_item VARCHAR(100) NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    service_type_id VARCHAR(50) NOT NULL,
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Transactions Table
CREATE TABLE transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id VARCHAR(50) UNIQUE NOT NULL,
    rrr VARCHAR(50) UNIQUE NOT NULL,
    payer_name VARCHAR(100) NOT NULL,
    payer_email VARCHAR(100) NOT NULL,
    payer_phone VARCHAR(20) NOT NULL,
    payment_item_id VARCHAR(50) NOT NULL,
    payment_item VARCHAR(100) NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    service_type_id VARCHAR(50) NOT NULL,
    status ENUM('pending', 'completed', 'failed', 'cancelled') DEFAULT 'pending',
    remita_status VARCHAR(20),
    remita_message TEXT,
    transaction_date TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (payment_item_id) REFERENCES payment_items(payment_item_id)
);

-- Payment Logs Table (for debugging and audit)
CREATE TABLE payment_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    transaction_id INT,
    action VARCHAR(50) NOT NULL,
    details JSON,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (transaction_id) REFERENCES transactions(id)
);

-- Insert Sample Payment Items
INSERT INTO payment_items (payment_item_id, payment_item, amount, service_type_id, description) VALUES
('OSU/3080/001', 'Acceptance Fee', 40000.00, '4430731', 'Acceptance fee for new students'),
('OSU/3080/002', 'School Fees', 100000.00, '4430732', 'Annual school fees'),
('OSU/3080/003', 'Hostel Fee', 50000.00, '4430733', 'Accommodation fee'),
('OSU/3080/004', 'Library Fee', 5000.00, '4430734', 'Library access fee'),
('OSU/3080/005', 'Medical Fee', 10000.00, '4430735', 'Medical services fee');

-- Create Indexes for better performance
CREATE INDEX idx_transactions_rrr ON transactions(rrr);
CREATE INDEX idx_transactions_order_id ON transactions(order_id);
CREATE INDEX idx_transactions_status ON transactions(status);
CREATE INDEX idx_transactions_created_at ON transactions(created_at);
CREATE INDEX idx_payment_items_active ON payment_items(is_active);
