-- جداول سیستم کاربران ربات، کیف پول و پرداخت

CREATE TABLE IF NOT EXISTS bot_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    telegram_id BIGINT NOT NULL UNIQUE,
    username VARCHAR(100) DEFAULT NULL,
    first_name VARCHAR(150) DEFAULT NULL,
    last_name VARCHAR(150) DEFAULT NULL,
    phone VARCHAR(30) DEFAULT NULL,
    balance DECIMAL(18,0) NOT NULL DEFAULT 0,
    role ENUM('user','reseller','reseller_vip','vip') NOT NULL DEFAULT 'user',
    referrer_id BIGINT DEFAULT NULL,
    invite_code VARCHAR(32) NOT NULL,
    is_blocked TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_seen_at TIMESTAMP NULL DEFAULT NULL,
    INDEX idx_referrer (referrer_id),
    INDEX idx_role (role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_activity (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    telegram_id BIGINT NOT NULL,
    action VARCHAR(80) NOT NULL,
    detail TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_uid (telegram_id),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payment_methods (
    id INT AUTO_INCREMENT PRIMARY KEY,
    method_key VARCHAR(40) NOT NULL UNIQUE,
    title VARCHAR(100) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    config_json TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO payment_methods (method_key, title, is_active) VALUES
('card', 'کارت به کارت', 1),
('variza', 'پرداخت واریزا', 0);

CREATE TABLE IF NOT EXISTS charge_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    telegram_id BIGINT NOT NULL,
    amount DECIMAL(18,0) NOT NULL,
    method_key VARCHAR(40) NOT NULL DEFAULT 'card',
    card_id INT DEFAULT NULL,
    status ENUM('pending_payment','waiting_receipt','pending_review','approved','rejected','cancelled') NOT NULL DEFAULT 'pending_payment',
    receipt_file_id VARCHAR(255) DEFAULT NULL,
    admin_note TEXT,
    variza_slug VARCHAR(120) DEFAULT NULL,
    variza_amount DECIMAL(18,0) DEFAULT NULL,
    variza_attempt_code VARCHAR(120) DEFAULT NULL,
    variza_delivery_id VARCHAR(120) DEFAULT NULL,
    paid_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_tg (telegram_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gift_codes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(64) NOT NULL UNIQUE,
    amount DECIMAL(18,0) NOT NULL,
    max_uses INT NOT NULL DEFAULT 1,
    used_count INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    expires_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS gift_code_uses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code_id INT NOT NULL,
    telegram_id BIGINT NOT NULL,
    amount DECIMAL(18,0) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_code_user (code_id, telegram_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
