

-- Packages Table
CREATE TABLE packages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    operator_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    type ENUM('topup','data','voice','sms','combo') NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    validity VARCHAR(50),
    description TEXT,
    cost_price DECIMAL(10,2) NOT NULL,
    sell_price DECIMAL(10,2) NOT NULL,
    reseller_price DECIMAL(10,2),
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (operator_id) REFERENCES operators(id)
);

-- Transactions Table
CREATE TABLE transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    type ENUM('topup','drive','mbanking','deposit','withdrawal','commission','refund') NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    charge DECIMAL(10,2) DEFAULT 0.00,
    net_amount DECIMAL(12,2) NOT NULL,
    balance_before DECIMAL(12,2) NOT NULL,
    balance_after DECIMAL(12,2) NOT NULL,
    operator_id INT,
    package_id INT,
    phone_number VARCHAR(15),
    reference VARCHAR(100),
    api_response TEXT,
    status ENUM('pending','processing','success','failed','refunded') DEFAULT 'pending',
    note TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (operator_id) REFERENCES operators(id),
    FOREIGN KEY (package_id) REFERENCES packages(id)
);

-- Drive (Auto Recharge) Plans
CREATE TABLE drive_plans (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    phone_number VARCHAR(15) NOT NULL,
    operator_id INT NOT NULL,
    package_id INT NOT NULL,
    schedule_type ENUM('daily','weekly','monthly','custom') NOT NULL,
    schedule_time TIME,
    schedule_days JSON,
    next_run DATETIME,
    last_run DATETIME,
    total_runs INT DEFAULT 0,
    max_runs INT DEFAULT 0,
    status ENUM('active','paused','completed','cancelled') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (operator_id) REFERENCES operators(id),
    FOREIGN KEY (package_id) REFERENCES packages(id)
);

-- Mobile Banking Table
CREATE TABLE mbanking (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    provider ENUM('bkash','nagad','rocket','upay') NOT NULL,
    transaction_type ENUM('send','receive','cashout','paybill') NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    charge DECIMAL(10,2) DEFAULT 0.00,
    sender_number VARCHAR(15),
    receiver_number VARCHAR(15),
    reference VARCHAR(100),
    status ENUM('pending','success','failed') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Reseller Commission Table
CREATE TABLE reseller_commissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    reseller_id INT NOT NULL,
    user_id INT NOT NULL,
    transaction_id INT NOT NULL,
    commission_percent DECIMAL(5,2) NOT NULL,
    commission_amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending','paid','cancelled') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (reseller_id) REFERENCES users(id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (transaction_id) REFERENCES transactions(id)
);

-- Reseller Packages (custom pricing)
CREATE TABLE reseller_packages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    reseller_id INT NOT NULL,
    package_id INT NOT NULL,
    custom_price DECIMAL(10,2) NOT NULL,
    commission_percent DECIMAL(5,2) DEFAULT 0.00,
    status ENUM('active','inactive') DEFAULT 'active',
    FOREIGN KEY (reseller_id) REFERENCES users(id),
    FOREIGN KEY (package_id) REFERENCES packages(id)
);

-- Deposits Table
CREATE TABLE deposits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    method ENUM('bkash','nagad','rocket','bank','agent') NOT NULL,
    sender_number VARCHAR(15),
    transaction_ref VARCHAR(100),
    proof_image VARCHAR(255),
    status ENUM('pending','approved','rejected') DEFAULT 'pending',
    admin_note TEXT,
    approved_by INT,
    approved_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Notifications Table
CREATE TABLE notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NULL,
    title VARCHAR(200) NOT NULL,
    message TEXT NOT NULL,
    type ENUM('info','success','warning','danger') DEFAULT 'info',
    is_global BOOLEAN DEFAULT FALSE,
    is_read BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- API Logs
CREATE TABLE api_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    transaction_id INT,
    operator VARCHAR(50),
    request_data TEXT,
    response_data TEXT,
    status_code INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Sample Packages (GP)
INSERT INTO packages (operator_id, name, type, amount, validity, cost_price, sell_price, reseller_price) VALUES
(1, '৳10 Topup', 'topup', 10.00, 'N/A', 9.50, 10.00, 9.70),
(1, '৳20 Topup', 'topup', 20.00, 'N/A', 19.00, 20.00, 19.50),
(1, '৳50 Topup', 'topup', 50.00, 'N/A', 47.50, 50.00, 48.50),
(1, '৳100 Topup', 'topup', 100.00, 'N/A', 95.00, 100.00, 97.00),
(1, '1GB Data 3 Days', 'data', 19.00, '3 Days', 17.00, 19.00, 18.00),
(1, '2GB Data 7 Days', 'data', 38.00, '7 Days', 34.00, 38.00, 36.00),
(2, '৳10 Topup', 'topup', 10.00, 'N/A', 9.50, 10.00, 9.70),
(2, '1GB Data 3 Days', 'data', 18.00, '3 Days', 16.00, 18.00, 17.00),
(3, '৳10 Topup', 'topup', 10.00, 'N/A', 9.50, 10.00, 9.70),
(3, '1GB Data 3 Days', 'data', 17.00, '3 Days', 15.00, 17.00, 16.00),
(4, '৳10 Topup', 'topup', 10.00, 'N/A', 9.50, 10.00, 9.70);
