CREATE DATABASE IF NOT EXISTS vipbot
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;


USE vipbot;



/*
=========================
Users
=========================
*/

CREATE TABLE users (

    id BIGINT AUTO_INCREMENT PRIMARY KEY,


    telegram_id BIGINT NOT NULL UNIQUE,


    username VARCHAR(255) NULL,


    first_name VARCHAR(255) NULL,


    last_name VARCHAR(255) NULL,


    phone VARCHAR(50) NULL,


    exchange VARCHAR(50) NULL,


    exchange_uid VARCHAR(100) NULL,


    vip_status ENUM(
        'pending',
        'approved',
        'expired',
        'blocked'
    )
    DEFAULT 'pending',


    last_balance DECIMAL(12,2)
    DEFAULT 0,


    warning_sent_at DATETIME NULL,


    grace_period_until DATETIME NULL,


    joined_at DATETIME NULL,


    channel_status VARCHAR(50)
    DEFAULT 'not_joined',


    admin_note TEXT NULL,


    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,


    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
    ON UPDATE CURRENT_TIMESTAMP


)ENGINE=InnoDB;



/*
=========================
Settings
=========================
*/

CREATE TABLE settings (

    id INT AUTO_INCREMENT PRIMARY KEY,


    setting_key VARCHAR(100)
    UNIQUE NOT NULL,


    setting_value TEXT NULL,


    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
    ON UPDATE CURRENT_TIMESTAMP


)ENGINE=InnoDB;



/*
=========================
Invite Links
=========================
*/

CREATE TABLE invite_links (

    id BIGINT AUTO_INCREMENT PRIMARY KEY,


    telegram_id BIGINT NOT NULL,


    invite_link TEXT NOT NULL,


    used TINYINT DEFAULT 0,


    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,


    used_at DATETIME NULL


)ENGINE=InnoDB;



/*
=========================
Broadcast Logs
=========================
*/

CREATE TABLE broadcasts (

    id BIGINT AUTO_INCREMENT PRIMARY KEY,


    message TEXT NOT NULL,


    target VARCHAR(50),


    sent_count INT DEFAULT 0,


    failed_count INT DEFAULT 0,


    created_at DATETIME DEFAULT CURRENT_TIMESTAMP


)ENGINE=InnoDB;



/*
=========================
System Logs
=========================
*/

CREATE TABLE logs (

    id BIGINT AUTO_INCREMENT PRIMARY KEY,


    type VARCHAR(50),


    message TEXT,


    created_at DATETIME DEFAULT CURRENT_TIMESTAMP


)ENGINE=InnoDB;



/*
=========================
Exchange Config
=========================
*/

CREATE TABLE exchanges (

    id INT AUTO_INCREMENT PRIMARY KEY,


    name VARCHAR(50) UNIQUE NOT NULL,


    enabled TINYINT DEFAULT 1,


    api_key TEXT NULL,


    secret_key TEXT NULL,


    created_at DATETIME DEFAULT CURRENT_TIMESTAMP


)ENGINE=InnoDB;





/*
=========================
Default Settings
=========================
*/


INSERT INTO settings
(setting_key,setting_value)

VALUES


(
'welcome_text',
'سلام، به ربات VIP خوش آمدید'
),


(
'minimum_balance',
'50'
),


(
'lbank_register_link',
'https://www.lbkpro.net/ref/5WW03'
),


(
'ourbit_register_link',
''
),


(
'button_register',
'📈 ثبت نام در تالار معاملات'
),


(
'button_education',
'🎓 آموزش های مورد نیاز'
),


(
'button_support',
'📞 ارتباط با پشتیبانی'
),


(
'support_username',
''
);



/*
=========================
Default Exchanges
=========================
*/


INSERT INTO exchanges
(name,enabled)

VALUES

('lbank',1),

('ourbit',0);