-- 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
);

-- Admin Users Table
CREATE TABLE admin_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    staff_email VARCHAR(100) UNIQUE NOT NULL,
    staff_id VARCHAR(50) UNIQUE NOT NULL,
    surname VARCHAR(50) NOT NULL,
    first_name VARCHAR(50) NOT NULL,
    other_name VARCHAR(50),
    role ENUM('bursary', 'imtc') NOT NULL,
    password VARCHAR(255) NOT NULL,
    staff_passport VARCHAR(255),
    is_active BOOLEAN DEFAULT TRUE,
    last_login TIMESTAMP NULL,
    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');

-- Insert Sample Admin User (password: admin123)
INSERT INTO admin_users (staff_email, staff_id, surname, first_name, role, password) VALUES
('admin@uniosun.edu.ng', 'STAFF001', 'Admin', 'System', 'imtc', '$2a$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi');

-- 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_admin_users_email ON admin_users(staff_email);
CREATE INDEX idx_payment_items_active ON payment_items(is_active);
