CREATE TABLE IF NOT EXISTS bot_templates (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 slug VARCHAR(80) NOT NULL UNIQUE,
 title VARCHAR(150) NOT NULL,
 description TEXT NULL,
 price BIGINT UNSIGNED NOT NULL DEFAULT 0,
 is_free TINYINT(1) NOT NULL DEFAULT 1,
 is_active TINYINT(1) NOT NULL DEFAULT 1,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS factory_users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 chat_id BIGINT NOT NULL UNIQUE,
 balance BIGINT UNSIGNED NOT NULL DEFAULT 0,
 step VARCHAR(100) NOT NULL DEFAULT 'home',
 step_data JSON NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS bots (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 owner_id BIGINT NOT NULL,
 template_id INT UNSIGNED NOT NULL,
 bot_token_enc TEXT NOT NULL,
 bot_username VARCHAR(255) NOT NULL,
 db_name VARCHAR(128) NOT NULL UNIQUE,
 status ENUM('provisioning','active','stopped','error') NOT NULL DEFAULT 'provisioning',
 webhook_url TEXT NULL,
 webhook_set TINYINT(1) NOT NULL DEFAULT 0,
 cron_mode ENUM('central','system','manual') NOT NULL DEFAULT 'central',
 last_error TEXT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 INDEX idx_owner(owner_id), INDEX idx_status(status),
 CONSTRAINT fk_bot_template FOREIGN KEY(template_id) REFERENCES bot_templates(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS bot_events (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 bot_id BIGINT UNSIGNED NOT NULL,
 event_type VARCHAR(60) NOT NULL,
 details TEXT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_bot_event(bot_id, created_at),
 CONSTRAINT fk_event_bot FOREIGN KEY(bot_id) REFERENCES bots(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO bot_templates(slug,title,description,price,is_free,is_active) VALUES
('daramad','💰 کسب درآمد','قالب کسب درآمد و زیرمجموعه‌گیری بر پایه سورس ارسالی شما.',0,1,1)
ON DUPLICATE KEY UPDATE title=VALUES(title), description=VALUES(description);