-- ========================================
--   TG TOOLBOX — DATABASE SCHEMA
--   phpMyAdmin / mysql client দিয়ে import করো
-- ========================================

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

USE tgtoolbox;

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    telegram_id BIGINT UNSIGNED NOT NULL UNIQUE,
    username VARCHAR(64) DEFAULT NULL,
    first_name VARCHAR(128) DEFAULT NULL,
    is_premium TINYINT(1) NOT NULL DEFAULT 0,
    premium_until DATETIME DEFAULT NULL,
    referral_code VARCHAR(20) NOT NULL UNIQUE,
    referred_by INT DEFAULT NULL,
    referral_count INT NOT NULL DEFAULT 0,
    daily_usage INT NOT NULL DEFAULT 0,
    daily_ai_usage INT NOT NULL DEFAULT 0,
    usage_date DATE DEFAULT NULL,
    state TEXT DEFAULT NULL,
    is_banned TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_active DATETIME DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_referred_by FOREIGN KEY (referred_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS usage_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    tool_key VARCHAR(50) NOT NULL,
    used_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_usage_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS referrals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    referrer_id INT NOT NULL,
    referred_id INT NOT NULL UNIQUE,
    rewarded TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_ref_referrer FOREIGN KEY (referrer_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_ref_referred FOREIGN KEY (referred_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE INDEX idx_usage_user_tool ON usage_logs(user_id, tool_key);
CREATE INDEX idx_usage_used_at ON usage_logs(used_at);