-- =============================================
-- PRIYO TELECOM - Complete Database Schema
-- =============================================

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

-- Users Table
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    phone VARCHAR(15) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    balance DECIMAL(12,2) DEFAULT 0.00,
    due_balance DECIMAL(12,2) DEFAULT 0.00,
    role ENUM('user','reseller','admin') DEFAULT 'user',
    reseller_id INT NULL,
    status ENUM('active','suspended','pending') DEFAULT 'pending',
    profile_pic VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (reseller_id) REFERENCES users(id) ON DELETE SET NULL
);

-- Operators Table
CREATE TABLE operators (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    code VARCHAR(20) NOT NULL,
    logo VARCHAR(255),
    prefix JSON,
    status ENUM('active','inactive') DEFAULT 'active',
    api_endpoint VARCHAR(255),
    api_key VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

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

-- Settings Table
CREATE TABLE settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    `key` VARCHAR(100) UNIQUE NOT NULL,
    `value` TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- =============================================
-- Default Data
-- =============================================

-- Admin User (password: admin123)
INSERT INTO users (name, username, email, phone, password, balance, role, status) VALUES
('Super Admin', 'admin', 'admin@priyotelecom.com', '01700000000', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 99999.00, 'admin', 'active');

-- Operators
INSERT INTO operators (name, code, prefix, status) VALUES
('Grameenphone', 'GP', '["017","013"]', 'active'),
('Robi', 'ROBI', '["018"]', 'active'),
('Banglalink', 'BL', '["019","014"]', 'active'),
('Teletalk', 'TT', '["015"]', 'active');

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

-- Default Settings
INSERT INTO settings (`key`, `value`) VALUES
('site_name', 'Priyo Telecom'),
('site_logo', ''),
('currency', '৳'),
('min_deposit', '50'),
('max_topup', '1000'),
('maintenance_mode', '0'),
('registration_open', '1'),
('sms_api_key', ''),
('welcome_bonus', '0'),
('referral_bonus', '0');
