-- ایجاد دیتابیس (در صورت نیاز)
-- CREATE DATABASE IF NOT EXISTS instagram_bot CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- USE instagram_bot;

-- جدول کاربران
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    telegram_id BIGINT NOT NULL UNIQUE,
    username VARCHAR(255),
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    is_admin TINYINT(1) DEFAULT 0,
    joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_activity TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    request_count INT DEFAULT 0,
    INDEX (telegram_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول درخواست‌های API
CREATE TABLE IF NOT EXISTS api_calls (
    id INT AUTO_INCREMENT PRIMARY KEY,
    telegram_id BIGINT NOT NULL,
    endpoint VARCHAR(50) NOT NULL,
    params TEXT,
    response_code INT,
    response_size INT,
    is_error TINYINT(1) DEFAULT 0,
    error_message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX (telegram_id),
    INDEX (endpoint),
    INDEX (is_error),
    INDEX (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول تنظیمات
CREATE TABLE IF NOT EXISTS settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX (setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول کانال‌های اجباری
CREATE TABLE IF NOT EXISTS channels_required (
    id INT AUTO_INCREMENT PRIMARY KEY,
    channel_username VARCHAR(255) NOT NULL UNIQUE,
    channel_id BIGINT,
    channel_name VARCHAR(255),
    invite_link TEXT,
    added_by BIGINT,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX (channel_username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول پیام‌های همگانی
CREATE TABLE IF NOT EXISTS broadcasts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    message TEXT NOT NULL,
    sent_by BIGINT,
    total_users INT DEFAULT 0,
    successful_sends INT DEFAULT 0,
    failed_sends INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP NULL,
    is_forward TINYINT(1) DEFAULT 0,
    message_id BIGINT DEFAULT NULL,
    INDEX (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول محدودیت نرخ درخواست
CREATE TABLE IF NOT EXISTS rate_limits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    telegram_id BIGINT NOT NULL,
    request_count INT DEFAULT 1,
    first_request_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_request_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX (telegram_id),
    INDEX (last_request_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- جدول مدیریت ادمین‌ها
CREATE TABLE IF NOT EXISTS admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    telegram_id BIGINT NOT NULL UNIQUE,
    username VARCHAR(255),
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    added_by BIGINT,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    permissions VARCHAR(255) DEFAULT 'full', -- full, limited, custom
    is_super_admin TINYINT(1) DEFAULT 0,
    INDEX (telegram_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- درج تنظیمات پیش‌فرض
INSERT INTO settings (setting_key, setting_value) VALUES
('telegram_bot_token', 'YOUR_BOT_TOKEN_HERE'),
('instagram_api_key', 'YOUR_INSTAGRAM_API_KEY_HERE'),
('instagram_api_url', 'https://api.fast-creat.ir/instagram'),
('admin_ids', '123456789,987654321'),
('rate_limit_count', '10'),
('rate_limit_period', '60'),
('webhook_url', 'https://your-domain.com/path/to/webhook.php');