CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    phone VARCHAR(20) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('user','admin') DEFAULT 'user',
    status ENUM('pending','approved','banned') DEFAULT 'pending',
    balance DECIMAL(10,2) DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS bundles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    network ENUM('MTN','TELECEL','AIRTELTIGO') NOT NULL,
    name VARCHAR(100) NOT NULL,
    volume_mb INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS topups (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    method VARCHAR(50) DEFAULT 'manual',
    reference VARCHAR(100),
    status ENUM('pending','approved','rejected') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    approved_at TIMESTAMP NULL DEFAULT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    bundle_id INT NOT NULL,
    network VARCHAR(30) NOT NULL,
    beneficiary_phone VARCHAR(20) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending','processing','completed','failed','refunded') DEFAULT 'pending',
    admin_note TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (bundle_id) REFERENCES bundles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO bundles (network, name, volume_mb, price, status) VALUES
('MTN', '1GB MTN Data', 1024, 4.40, 'active'),
('MTN', '2GB MTN Data', 2048, 8.50, 'active'),
('MTN', '5GB MTN Data', 5120, 19.00, 'active'),
('MTN', '10GB MTN Data', 10240, 35.00, 'active'),
('MTN', '20GB MTN Data', 20480, 65.00, 'active'),
('TELECEL', '1GB Telecel Data', 1024, 4.80, 'active'),
('TELECEL', '5GB Telecel Data', 5120, 22.00, 'active'),
('TELECEL', '10GB Telecel Data', 10240, 42.00, 'active'),
('AIRTELTIGO', '1GB AirtelTigo Data', 1024, 4.70, 'active'),
('AIRTELTIGO', '5GB AirtelTigo Data', 5120, 21.00, 'active'),
('AIRTELTIGO', '10GB AirtelTigo Data', 10240, 40.00, 'active');