-- Migration 002: API Bot MVP tables and indexes
-- اجرا: mysql -u USER -p DB < worker/migrations/002_api_bot_schema.sql

ALTER TABLE `SendDetails`
    ADD COLUMN IF NOT EXISTS `bot_index` TINYINT UNSIGNED NULL DEFAULT NULL AFTER `acount_id`;

ALTER TABLE `SendReport`
    ADD COLUMN IF NOT EXISTS `bot_index` TINYINT UNSIGNED NULL DEFAULT NULL AFTER `channel_id`;

CREATE TABLE IF NOT EXISTS `api_bot_rate_buckets` (
    `bucket_key` varchar(80) NOT NULL,
    `count` int(11) NOT NULL DEFAULT 0,
    `expires_at` int(11) NOT NULL,
    PRIMARY KEY (`bucket_key`),
    KEY `expires_at` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `api_bot_claims` (
    `channel_id` bigint(20) NOT NULL,
    `group_id` bigint(20) NOT NULL,
    `message_id` int(11) NOT NULL,
    `claimed_until` int(11) NOT NULL,
    PRIMARY KEY (`channel_id`,`group_id`,`message_id`),
    KEY `claimed_until` (`claimed_until`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- MariaDB قدیمی IF NOT EXISTS برای ADD COLUMN ندارد — ensureSchema در PHP خودکار اجرا می‌کند