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

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(120) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    balance DECIMAL(10,2) DEFAULT 0.00,
    role ENUM('user','admin') DEFAULT 'user',
    is_active TINYINT(1) DEFAULT 1,
    api_key VARCHAR(64) UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_login DATETIME NULL
);

CREATE TABLE bot_accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    uid VARCHAR(50) NULL,
    password VARCHAR(120) NULL,
    access_token TEXT NULL,
    region VARCHAR(10) DEFAULT 'BD',
    is_active TINYINT(1) DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE bot_stats (
    id INT AUTO_INCREMENT PRIMARY KEY,
    bot_account_id INT NOT NULL UNIQUE,
    nickname VARCHAR(80) DEFAULT '',
    level INT DEFAULT 1,
    initial_exp BIGINT DEFAULT 0,
    current_exp BIGINT DEFAULT 0,
    gained_exp BIGINT DEFAULT 0,
    likes INT DEFAULT 0,
    status VARCHAR(30) DEFAULT 'OFFLINE',
    matches_played INT DEFAULT 0,
    active_matches INT DEFAULT 0,
    last_match_time VARCHAR(20) NULL,
    last_updated DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (bot_account_id) REFERENCES bot_accounts(id) ON DELETE CASCADE
);

CREATE TABLE bot_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    bot_account_id INT NULL,
    level VARCHAR(20) DEFAULT 'info',
    message TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_time (user_id, created_at),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    transaction_id VARCHAR(120) UNIQUE,
    amount DECIMAL(10,2) NOT NULL,
    method VARCHAR(30),
    status ENUM('pending','completed','failed') DEFAULT 'pending',
    purpose VARCHAR(50) DEFAULT 'wallet_topup',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE site_settings (
    k VARCHAR(50) PRIMARY KEY,
    v TEXT
);

INSERT INTO site_settings (k,v) VALUES
('bot_price', '100'),
('delivery_charge_dhaka', '60'),
('delivery_charge_outside', '120'),
('delivery_dhaka_city', 'Dhaka'),
('cod_enabled', '1'),
('adv_enabled', '1'),
('7hpay_gateway_url', 'pay.7hsiam.top'),
('7hpay_brand_key', '0a6VCKqT6oQkm3NUAKFkwiX0d5hLI5hxOPodqkVDddDFujdUUb'),
('site_name', '7H SIAM Bot'),
('min_topup', '50');

-- Default admin (password: admin123 — change it!)
INSERT INTO users (username, email, password_hash, role, balance, api_key)
VALUES ('admin', 'admin@local', '$2y$10$e0MYzXyjpJS7Pd0RVvHwHe1HcQhVJqTsZqWqN1wE7u1H8tK9VQm8a', 'admin', 999999, 'ADMIN_RAILWAY_KEY_XYZ');