-- =============================================
-- PUFII Management System - Database Schema
-- =============================================

CREATE DATABASE IF NOT EXISTS pufii_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE pufii_db;

-- ------------------------------------------------
-- Packages (Priced tiers per reseller)
-- ------------------------------------------------
CREATE TABLE IF NOT EXISTS packages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    reseller_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    duration_minutes INT NOT NULL,
    data_limit_mb INT DEFAULT NULL COMMENT 'NULL = unlimited',
    description TEXT DEFAULT NULL,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (reseller_id) REFERENCES resellers(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------
-- Vouchers
-- ------------------------------------------------
CREATE TABLE IF NOT EXISTS vouchers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    reseller_id INT NOT NULL,
    package_id INT NOT NULL,
    code VARCHAR(20) UNIQUE NOT NULL,
    status ENUM('unused','used','expired') DEFAULT 'unused',
    used_at TIMESTAMP NULL DEFAULT NULL,
    used_by_mac VARCHAR(20) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (reseller_id) REFERENCES resellers(id) ON DELETE CASCADE,
    FOREIGN KEY (package_id) REFERENCES packages(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------
-- Transactions
-- ------------------------------------------------
CREATE TABLE IF NOT EXISTS transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    reseller_id INT NOT NULL,
    package_id INT NOT NULL,
    user_mac VARCHAR(20) DEFAULT NULL,
    amount DECIMAL(10,2) NOT NULL,
    commission DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    reseller_earnings DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    payment_reference VARCHAR(100) UNIQUE,
    payment_method ENUM('gateway','voucher') DEFAULT 'gateway',
    status ENUM('pending','completed','failed') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (reseller_id) REFERENCES resellers(id) ON DELETE CASCADE,
    FOREIGN KEY (package_id) REFERENCES packages(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------
-- Settings (Global platform config)
-- ------------------------------------------------
CREATE TABLE IF NOT EXISTS settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ------------------------------------------------
-- Customers (Manual tracking per reseller)
-- ------------------------------------------------
CREATE TABLE IF NOT EXISTS customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    reseller_id INT NOT NULL,
    name VARCHAR(150) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    email VARCHAR(150) DEFAULT NULL,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (reseller_id) REFERENCES resellers(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------
-- Invoices (Manual billing)
-- ------------------------------------------------
CREATE TABLE IF NOT EXISTS invoices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    reseller_id INT NOT NULL,
    customer_id INT NOT NULL,
    package_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    status ENUM('unpaid','paid','cancelled') DEFAULT 'unpaid',
    due_date DATE DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (reseller_id) REFERENCES resellers(id) ON DELETE CASCADE,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    FOREIGN KEY (package_id) REFERENCES packages(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ------------------------------------------------

-- ------------------------------------------------
-- Withdrawal Phones (Whitelists)
-- ------------------------------------------------
CREATE TABLE IF NOT EXISTS withdrawal_phones (
    id INT AUTO_INCREMENT PRIMARY KEY,
    owner_type ENUM('admin','reseller') NOT NULL,
    owner_id INT NOT NULL COMMENT '0 if admin, reseller_id if reseller',
    phone_number VARCHAR(20) NOT NULL,
    provider ENUM('AIRTEL','MTN','OTHER') DEFAULT 'AIRTEL',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_phone (owner_type, owner_id, phone_number)
) ENGINE=InnoDB;

-- ------------------------------------------------
-- Withdrawals (Payout Requests)
-- ------------------------------------------------
CREATE TABLE IF NOT EXISTS withdrawals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    reseller_id INT NOT NULL DEFAULT 0 COMMENT '0 = Admin withdrawal',
    recipient_name VARCHAR(150) DEFAULT NULL,
    phone_number VARCHAR(20) NOT NULL,
    provider VARCHAR(20) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending','completed','failed') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;
-- Seed Data
-- ------------------------------------------------

-- Default commission = 10%
INSERT INTO settings (setting_key, setting_value) VALUES ('commission_percentage', '10')
ON DUPLICATE KEY UPDATE setting_value = setting_value;
