CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    telegram_id BIGINT UNIQUE,
    full_name VARCHAR(100),
    username VARCHAR(100),
    mobile VARCHAR(20),

    balance DECIMAL(12,2) DEFAULT 0,
    commission_balance DECIMAL(12,2) DEFAULT 0,
    total_recharge DECIMAL(12,2) DEFAULT 0,

    login_pin VARCHAR(255),
    transaction_pin VARCHAR(255),

    role ENUM(
        'USER',
        'RESELLER',
        'VIP',
        'DISTRIBUTOR'
    ) DEFAULT 'USER',

    membership_expire DATETIME NULL,

    ref_by BIGINT DEFAULT NULL,

    device_id VARCHAR(255),

    status ENUM(
        'ACTIVE',
        'BLOCKED'
    ) DEFAULT 'ACTIVE',

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE admins (

    id INT AUTO_INCREMENT PRIMARY KEY,

    telegram_id BIGINT UNIQUE,

    full_name VARCHAR(100),

    role VARCHAR(30) DEFAULT 'SUPER_ADMIN',

    permissions TEXT

);

CREATE TABLE settings (

    id INT PRIMARY KEY DEFAULT 1,

    bot_name VARCHAR(100),

    referral_bonus DECIMAL(10,2) DEFAULT 0,

    recharge_commission DECIMAL(10,2) DEFAULT 0,

    membership_price DECIMAL(10,2) DEFAULT 0,

    deposit_bonus DECIMAL(10,2) DEFAULT 0,

    min_deposit DECIMAL(10,2) DEFAULT 50,

    min_transfer DECIMAL(10,2) DEFAULT 10,

    auto_recharge ENUM('ON','OFF') DEFAULT 'OFF',

    manual_recharge ENUM('ON','OFF') DEFAULT 'ON'

);

INSERT INTO settings(
id,
bot_name
)

VALUES(

1,

'SHOMAROHO RECHARGE BOT'

);

CREATE TABLE user_states (

    telegram_id BIGINT PRIMARY KEY,

    step VARCHAR(100),

    value TEXT

);

CREATE TABLE deposits (

    id INT AUTO_INCREMENT PRIMARY KEY,

    telegram_id BIGINT,

    amount DECIMAL(10,2),

    payment_method VARCHAR(30),

    trxid VARCHAR(100),

    screenshot VARCHAR(255),

    admin_note TEXT,

    approved_by BIGINT,

    approved_at DATETIME NULL,

    status ENUM(
        'Pending',
        'Approved',
        'Rejected'
    ) DEFAULT 'Pending',

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);
CREATE TABLE transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    telegram_id BIGINT,
    type VARCHAR(50),
    amount DECIMAL(12,2),
    details TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE wallet_transfer (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sender BIGINT,
    receiver BIGINT,
    amount DECIMAL(12,2),
    status ENUM('Success','Failed') DEFAULT 'Success',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    telegram_id BIGINT,
    title VARCHAR(100),
    message TEXT,
    is_read TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE device_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    telegram_id BIGINT,
    device_id VARCHAR(255),
    ip_address VARCHAR(100),
    login_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE recharge_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    telegram_id BIGINT,
    operator VARCHAR(30),
    mobile VARCHAR(20),
    amount DECIMAL(10,2),
    commission DECIMAL(10,2) DEFAULT 0,
    status ENUM('Pending','Success','Failed') DEFAULT 'Pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE sim_packs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    operator VARCHAR(30),
    category VARCHAR(30),
    pack_name VARCHAR(100),
    regular_price DECIMAL(10,2),
    discount_price DECIMAL(10,2),
    status ENUM('ON','OFF') DEFAULT 'ON'
);

CREATE TABLE sim_orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    telegram_id BIGINT,
    pack_id INT,
    mobile VARCHAR(20),
    amount DECIMAL(10,2),
    status ENUM('Pending','Success','Failed') DEFAULT 'Pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE membership_plans (
    id INT AUTO_INCREMENT PRIMARY KEY,
    plan_name VARCHAR(50),
    price DECIMAL(10,2),
    duration INT,
    status ENUM('ON','OFF') DEFAULT 'ON'
);

CREATE TABLE referrals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    referrer BIGINT,
    referred BIGINT,
    bonus DECIMAL(10,2) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE recharge_api (

    id INT AUTO_INCREMENT PRIMARY KEY,

    provider_name VARCHAR(100),

    api_url TEXT,

    api_key TEXT,

    api_secret TEXT,

    status ENUM('ON','OFF') DEFAULT 'OFF'

);

CREATE TABLE billpay_api (

    id INT AUTO_INCREMENT PRIMARY KEY,

    provider_name VARCHAR(100),

    api_url TEXT,

    api_key TEXT,

    api_secret TEXT,

    status ENUM('ON','OFF') DEFAULT 'OFF'

);

CREATE TABLE api_logs (

    id INT AUTO_INCREMENT PRIMARY KEY,

    api_name VARCHAR(100),

    request_data LONGTEXT,

    response_data LONGTEXT,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);

CREATE TABLE admin_logs (

    id INT AUTO_INCREMENT PRIMARY KEY,

    admin_id BIGINT,

    action TEXT,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);

CREATE TABLE system_logs (

    id INT AUTO_INCREMENT PRIMARY KEY,

    log_type VARCHAR(50),

    description TEXT,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);