SET NAMES utf8mb4;
SET time_zone = '+00:00';

CREATE TABLE IF NOT EXISTS roles (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(64) NOT NULL,
  permissions_json JSON NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_roles_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS admins (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  role_id INT UNSIGNED NOT NULL,
  username VARCHAR(64) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  telegram_user_id BIGINT SIGNED NULL DEFAULT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  last_login_at TIMESTAMP NULL DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_admins_username (username),
  KEY idx_admins_role_id (role_id),
  CONSTRAINT fk_admins_role_id FOREIGN KEY (role_id) REFERENCES roles(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS bot_settings (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `key` VARCHAR(128) NOT NULL,
  value_json JSON NOT NULL,
  updated_by_admin_id INT UNSIGNED NULL DEFAULT NULL,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_bot_settings_key (`key`),
  KEY idx_bot_settings_updated_by (updated_by_admin_id),
  CONSTRAINT fk_bot_settings_updated_by FOREIGN KEY (updated_by_admin_id) REFERENCES admins(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS telegram_bots (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(128) NOT NULL,
  username VARCHAR(64) NOT NULL DEFAULT '',
  bot_token TEXT NOT NULL,
  webhook_secret VARCHAR(128) NOT NULL DEFAULT '',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_telegram_bots_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS groups_channels (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  bot_id INT UNSIGNED NOT NULL,
  chat_id BIGINT SIGNED NOT NULL,
  title VARCHAR(255) NOT NULL,
  username VARCHAR(64) NULL DEFAULT NULL,
  chat_type ENUM('group','supergroup','channel') NOT NULL,
  status VARCHAR(32) NULL DEFAULT NULL,
  language_code ENUM('en','zh','ms') NOT NULL DEFAULT 'en',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  welcome_json JSON NULL DEFAULT NULL,
  rules_json JSON NULL DEFAULT NULL,
  moderation_json JSON NULL DEFAULT NULL,
  permissions_json JSON NULL DEFAULT NULL,
  settings_json JSON NULL DEFAULT NULL,
  tracking_json JSON NULL DEFAULT NULL,
  last_sync_at DATETIME NULL DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_groups_channels_bot_chat (bot_id, chat_id),
  KEY idx_groups_channels_type_active (chat_type, is_active),
  KEY idx_groups_channels_bot_active (bot_id, is_active),
  CONSTRAINT fk_groups_channels_bot_id FOREIGN KEY (bot_id) REFERENCES telegram_bots(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS menus (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  scope ENUM('global','chat') NOT NULL DEFAULT 'global',
  chat_id BIGINT SIGNED NULL DEFAULT NULL,
  language_code ENUM('en','zh','ms') NOT NULL,
  code VARCHAR(64) NOT NULL,
  title VARCHAR(128) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_menus_scope_lang_code_chat (scope, language_code, code, chat_id),
  KEY idx_menus_chat (chat_id),
  KEY idx_menus_active_sort (is_active, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS support_links (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  language_code ENUM('en','zh','ms') NOT NULL,
  language ENUM('en','zh','ms') NOT NULL DEFAULT 'en',
  telegram_cs_url VARCHAR(512) NULL DEFAULT NULL,
  livechat_url VARCHAR(512) NULL DEFAULT NULL,
  login_deposit_url VARCHAR(512) NULL DEFAULT NULL,
  promotions_url VARCHAR(512) NULL DEFAULT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_support_links_lang (language_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS media_library (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  original_name VARCHAR(255) NOT NULL,
  stored_name VARCHAR(255) NOT NULL,
  mime_type VARCHAR(128) NOT NULL,
  file_size INT UNSIGNED NOT NULL,
  file_ext VARCHAR(16) NOT NULL,
  sha256 CHAR(64) NOT NULL,
  uploaded_by_admin_id INT UNSIGNED NULL DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_media_sha256 (sha256),
  KEY idx_media_uploaded_by (uploaded_by_admin_id),
  CONSTRAINT fk_media_uploaded_by FOREIGN KEY (uploaded_by_admin_id) REFERENCES admins(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS buttons (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  menu_id INT UNSIGNED NOT NULL,
  parent_button_id INT UNSIGNED NULL DEFAULT NULL,
  language_code ENUM('en','zh','ms') NOT NULL,
  title VARCHAR(128) NOT NULL,
  icon VARCHAR(16) NOT NULL DEFAULT '',
  button_type ENUM('url','callback','submenu','media','message') NOT NULL,
  url VARCHAR(512) NULL DEFAULT NULL,
  callback_data VARCHAR(128) NULL DEFAULT NULL,
  message_text TEXT NULL DEFAULT NULL,
  media_id INT UNSIGNED NULL DEFAULT NULL,
  tracking_enabled TINYINT(1) NOT NULL DEFAULT 1,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_buttons_menu_parent_sort (menu_id, parent_button_id, sort_order),
  KEY idx_buttons_media_id (media_id),
  CONSTRAINT fk_buttons_menu_id FOREIGN KEY (menu_id) REFERENCES menus(id),
  CONSTRAINT fk_buttons_parent_id FOREIGN KEY (parent_button_id) REFERENCES buttons(id),
  CONSTRAINT fk_buttons_media_id FOREIGN KEY (media_id) REFERENCES media_library(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS promos (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  language_code ENUM('en','zh','ms') NOT NULL,
  title VARCHAR(255) NOT NULL,
  description TEXT NULL DEFAULT NULL,
  content_text TEXT NULL DEFAULT NULL,
  media_id INT UNSIGNED NULL DEFAULT NULL,
  button_text VARCHAR(64) NULL DEFAULT NULL,
  button_url VARCHAR(512) NULL DEFAULT NULL,
  tracking_enabled TINYINT(1) NOT NULL DEFAULT 1,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  starts_at DATETIME NULL DEFAULT NULL,
  ends_at DATETIME NULL DEFAULT NULL,
  created_by_admin_id INT UNSIGNED NULL DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_promos_active_lang (is_active, language_code),
  KEY idx_promos_media_id (media_id),
  CONSTRAINT fk_promos_media_id FOREIGN KEY (media_id) REFERENCES media_library(id),
  CONSTRAINT fk_promos_created_by FOREIGN KEY (created_by_admin_id) REFERENCES admins(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS telegram_users (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  telegram_user_id BIGINT SIGNED NOT NULL,
  username VARCHAR(64) NULL DEFAULT NULL,
  first_name VARCHAR(128) NULL DEFAULT NULL,
  last_name VARCHAR(128) NULL DEFAULT NULL,
  language_code VARCHAR(16) NULL DEFAULT NULL,
  is_bot TINYINT(1) NOT NULL DEFAULT 0,
  first_seen_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_seen_at TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uq_telegram_users_tg_id (telegram_user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS click_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  telegram_user_id BIGINT SIGNED NULL DEFAULT NULL,
  chat_id BIGINT SIGNED NULL DEFAULT NULL,
  event_type VARCHAR(64) NOT NULL,
  event_key VARCHAR(128) NULL DEFAULT NULL,
  meta_json JSON NULL DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_click_logs_time (created_at),
  KEY idx_click_logs_event (event_type, created_at),
  KEY idx_click_logs_chat (chat_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS moderation_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  chat_id BIGINT SIGNED NOT NULL,
  target_user_id BIGINT SIGNED NULL DEFAULT NULL,
  executor_user_id BIGINT SIGNED NULL DEFAULT NULL,
  action ENUM('warn','mute','ban','kick','delete') NOT NULL,
  reason VARCHAR(255) NULL DEFAULT NULL,
  meta_json JSON NULL DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_moderation_chat_time (chat_id, created_at),
  KEY idx_moderation_action_time (action, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS banned_words (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  bot_id INT UNSIGNED NOT NULL DEFAULT 1,
  chat_id BIGINT SIGNED NULL DEFAULT NULL,
  rule_type ENUM('word','phrase','regex') NOT NULL,
  pattern VARCHAR(255) NOT NULL,
  action ENUM('delete','warn','mute','ban') NOT NULL DEFAULT 'delete',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_by_admin_id INT UNSIGNED NULL DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_banned_words_bot_chat_active (bot_id, chat_id, is_active),
  KEY idx_banned_words_chat_active (chat_id, is_active),
  KEY idx_banned_words_type_active (rule_type, is_active),
  CONSTRAINT fk_banned_words_created_by FOREIGN KEY (created_by_admin_id) REFERENCES admins(id),
  CONSTRAINT fk_banned_words_bot_id FOREIGN KEY (bot_id) REFERENCES telegram_bots(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS warning_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  chat_id BIGINT SIGNED NOT NULL,
  target_user_id BIGINT SIGNED NOT NULL,
  executor_user_id BIGINT SIGNED NULL DEFAULT NULL,
  reason VARCHAR(255) NULL DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_warning_chat_target_time (chat_id, target_user_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS scheduled_messages (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  bot_id INT UNSIGNED NOT NULL DEFAULT 1,
  title VARCHAR(255) NOT NULL,
  payload_json JSON NOT NULL,
  schedule_type ENUM('once','daily','weekly','monthly','interval') NOT NULL DEFAULT 'once',
  next_run_at DATETIME NOT NULL,
  interval_seconds INT UNSIGNED NULL DEFAULT NULL,
  is_paused TINYINT(1) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_by_admin_id INT UNSIGNED NULL DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_scheduled_next (is_active, is_paused, next_run_at),
  KEY idx_scheduled_bot_next (bot_id, is_active, is_paused, next_run_at),
  CONSTRAINT fk_scheduled_created_by FOREIGN KEY (created_by_admin_id) REFERENCES admins(id),
  CONSTRAINT fk_scheduled_bot_id FOREIGN KEY (bot_id) REFERENCES telegram_bots(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS scheduled_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  scheduled_message_id BIGINT UNSIGNED NOT NULL,
  bot_id INT UNSIGNED NOT NULL,
  chat_id BIGINT SIGNED NOT NULL,
  status ENUM('queued','sent','failed') NOT NULL,
  response_json JSON NULL DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_scheduled_logs_msg (scheduled_message_id, created_at),
  KEY idx_scheduled_logs_chat_time (chat_id, created_at),
  KEY idx_scheduled_logs_bot_time (bot_id, created_at),
  CONSTRAINT fk_scheduled_logs_message_id FOREIGN KEY (scheduled_message_id) REFERENCES scheduled_messages(id),
  CONSTRAINT fk_scheduled_logs_bot_id FOREIGN KEY (bot_id) REFERENCES telegram_bots(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS scheduled_message_targets (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  scheduled_message_id BIGINT UNSIGNED NOT NULL,
  chat_id BIGINT SIGNED NOT NULL,
  chat_type ENUM('group','supergroup','channel') NULL DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_sch_target (scheduled_message_id, chat_id),
  KEY idx_sch_target_chat (chat_id),
  CONSTRAINT fk_sch_target_message_id FOREIGN KEY (scheduled_message_id) REFERENCES scheduled_messages(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS scheduled_message_buttons (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  scheduled_message_id BIGINT UNSIGNED NOT NULL,
  row_index INT UNSIGNED NOT NULL DEFAULT 0,
  col_index INT UNSIGNED NOT NULL DEFAULT 0,
  text VARCHAR(64) NOT NULL,
  url VARCHAR(512) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_sch_button_pos (scheduled_message_id, row_index, col_index),
  KEY idx_sch_button_message (scheduled_message_id),
  CONSTRAINT fk_sch_button_message_id FOREIGN KEY (scheduled_message_id) REFERENCES scheduled_messages(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS scheduled_message_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  scheduled_message_id BIGINT UNSIGNED NOT NULL,
  bot_id INT UNSIGNED NOT NULL,
  chat_id BIGINT SIGNED NOT NULL,
  run_key VARCHAR(32) NOT NULL,
  status ENUM('queued','sent','failed','skipped') NOT NULL,
  telegram_message_id BIGINT SIGNED NULL DEFAULT NULL,
  error_message VARCHAR(255) NULL DEFAULT NULL,
  delete_at DATETIME NULL DEFAULT NULL,
  deleted_at DATETIME NULL DEFAULT NULL,
  response_json JSON NULL DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_sch_msg_chat_run (scheduled_message_id, chat_id, run_key),
  KEY idx_sch_msg_time (scheduled_message_id, created_at),
  KEY idx_sch_chat_time (chat_id, created_at),
  KEY idx_sch_delete_due (delete_at, deleted_at),
  KEY idx_sch_bot_time (bot_id, created_at),
  CONSTRAINT fk_sch_logs_message_id FOREIGN KEY (scheduled_message_id) REFERENCES scheduled_messages(id),
  CONSTRAINT fk_sch_logs_bot_id FOREIGN KEY (bot_id) REFERENCES telegram_bots(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS analytics_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  chat_id BIGINT SIGNED NULL DEFAULT NULL,
  telegram_user_id BIGINT SIGNED NULL DEFAULT NULL,
  metric VARCHAR(64) NOT NULL,
  value_int BIGINT SIGNED NOT NULL DEFAULT 1,
  meta_json JSON NULL DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_analytics_metric_time (metric, created_at),
  KEY idx_analytics_chat_time (chat_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  admin_id INT UNSIGNED NULL DEFAULT NULL,
  event VARCHAR(64) NOT NULL,
  ip VARCHAR(64) NULL DEFAULT NULL,
  meta_json JSON NULL DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_audit_event_time (event, created_at),
  KEY idx_audit_admin_time (admin_id, created_at),
  CONSTRAINT fk_audit_admin_id FOREIGN KEY (admin_id) REFERENCES admins(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS error_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  context VARCHAR(64) NOT NULL,
  message TEXT NOT NULL,
  meta_json JSON NULL DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_error_context_time (context, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS chat_admin_cache (
  bot_id INT UNSIGNED NOT NULL,
  chat_id BIGINT SIGNED NOT NULL,
  telegram_user_id BIGINT SIGNED NOT NULL,
  status ENUM('member','restricted','left','kicked','administrator','creator') NOT NULL,
  expires_at DATETIME NOT NULL,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (bot_id, chat_id, telegram_user_id),
  KEY idx_chat_admin_cache_user_exp (telegram_user_id, expires_at),
  KEY idx_chat_admin_cache_exp (expires_at),
  CONSTRAINT fk_chat_admin_cache_bot_id FOREIGN KEY (bot_id) REFERENCES telegram_bots(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS flood_counters (
  bot_id INT UNSIGNED NOT NULL,
  chat_id BIGINT SIGNED NOT NULL,
  telegram_user_id BIGINT SIGNED NOT NULL,
  bucket INT UNSIGNED NOT NULL,
  msg_count INT UNSIGNED NOT NULL DEFAULT 1,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (bot_id, chat_id, telegram_user_id, bucket),
  KEY idx_flood_counters_chat_bucket (bot_id, chat_id, bucket),
  KEY idx_flood_counters_updated_at (updated_at),
  CONSTRAINT fk_flood_counters_bot_id FOREIGN KEY (bot_id) REFERENCES telegram_bots(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS message_fingerprints (
  bot_id INT UNSIGNED NOT NULL,
  chat_id BIGINT SIGNED NOT NULL,
  telegram_user_id BIGINT SIGNED NOT NULL,
  fingerprint CHAR(40) NOT NULL,
  count INT UNSIGNED NOT NULL DEFAULT 1,
  expires_at DATETIME NOT NULL,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (bot_id, chat_id, telegram_user_id, fingerprint),
  KEY idx_message_fp_exp (expires_at),
  KEY idx_message_fp_chat_user (bot_id, chat_id, telegram_user_id, updated_at),
  CONSTRAINT fk_message_fp_bot_id FOREIGN KEY (bot_id) REFERENCES telegram_bots(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS chat_fingerprints (
  bot_id INT UNSIGNED NOT NULL,
  chat_id BIGINT SIGNED NOT NULL,
  fingerprint CHAR(40) NOT NULL,
  count INT UNSIGNED NOT NULL DEFAULT 1,
  last_telegram_user_id BIGINT SIGNED NULL DEFAULT NULL,
  expires_at DATETIME NOT NULL,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (bot_id, chat_id, fingerprint),
  KEY idx_chat_fp_exp (expires_at),
  KEY idx_chat_fp_chat_updated (bot_id, chat_id, updated_at),
  CONSTRAINT fk_chat_fp_bot_id FOREIGN KEY (bot_id) REFERENCES telegram_bots(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS captcha_sessions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  bot_id INT UNSIGNED NOT NULL,
  chat_id BIGINT SIGNED NOT NULL,
  user_id BIGINT SIGNED NOT NULL,
  token CHAR(32) NOT NULL,
  status ENUM('pending','verified','expired','kicked') NOT NULL DEFAULT 'pending',
  attempts INT UNSIGNED NOT NULL DEFAULT 0,
  verify_message_id BIGINT SIGNED NULL DEFAULT NULL,
  expires_at DATETIME NOT NULL,
  meta_json JSON NULL DEFAULT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_captcha_bot_token (bot_id, token),
  KEY idx_captcha_pending (bot_id, status, expires_at),
  KEY idx_captcha_chat_user (bot_id, chat_id, user_id, status),
  CONSTRAINT fk_captcha_bot_id FOREIGN KEY (bot_id) REFERENCES telegram_bots(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO roles (id, name, permissions_json) VALUES
  (1, 'owner', '{"all":true}'),
  (2, 'admin', '{"all":false}');

INSERT IGNORE INTO telegram_bots (id, name, username, bot_token, webhook_secret, is_active) VALUES
  (1, 'Default Bot', '', '', '', 1);

SET @t := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'support_links');
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'support_links' AND COLUMN_NAME = 'login_deposit_url');
SET @sql := IF(@t > 0 AND @c = 0, 'ALTER TABLE support_links ADD COLUMN login_deposit_url VARCHAR(512) NULL DEFAULT NULL AFTER livechat_url', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'support_links' AND COLUMN_NAME = 'promotions_url');
SET @sql := IF(@t > 0 AND @c = 0, 'ALTER TABLE support_links ADD COLUMN promotions_url VARCHAR(512) NULL DEFAULT NULL AFTER login_deposit_url', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @t := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'support_links');
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'support_links' AND COLUMN_NAME = 'language');
SET @sql := IF(@t > 0 AND @c = 0, 'ALTER TABLE support_links ADD COLUMN language ENUM(''en'',''zh'',''ms'') NOT NULL DEFAULT ''en'' AFTER language_code', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'support_links' AND COLUMN_NAME = 'is_active');
SET @sql := IF(@t > 0 AND @c = 0, 'ALTER TABLE support_links ADD COLUMN is_active TINYINT(1) NOT NULL DEFAULT 1 AFTER promotions_url', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @t := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'promos');
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'promos' AND COLUMN_NAME = 'button_text');
SET @sql := IF(@t > 0 AND @c = 0, 'ALTER TABLE promos ADD COLUMN button_text VARCHAR(64) NULL DEFAULT NULL AFTER media_id', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'promos' AND COLUMN_NAME = 'button_url');
SET @sql := IF(@t > 0 AND @c = 0, 'ALTER TABLE promos ADD COLUMN button_url VARCHAR(512) NULL DEFAULT NULL AFTER button_text', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'promos' AND COLUMN_NAME = 'tracking_enabled');
SET @sql := IF(@t > 0 AND @c = 0, 'ALTER TABLE promos ADD COLUMN tracking_enabled TINYINT(1) NOT NULL DEFAULT 1 AFTER button_url', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'promos' AND COLUMN_NAME = 'description');
SET @sql := IF(@t > 0 AND @c = 0, 'ALTER TABLE promos ADD COLUMN description TEXT NULL DEFAULT NULL AFTER title', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'promos' AND COLUMN_NAME = 'starts_at');
SET @sql := IF(@t > 0 AND @c = 0, 'ALTER TABLE promos ADD COLUMN starts_at DATETIME NULL DEFAULT NULL AFTER tracking_enabled', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'promos' AND COLUMN_NAME = 'ends_at');
SET @sql := IF(@t > 0 AND @c = 0, 'ALTER TABLE promos ADD COLUMN ends_at DATETIME NULL DEFAULT NULL AFTER starts_at', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @t := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'groups_channels');
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'groups_channels' AND COLUMN_NAME = 'tracking_json');
SET @sql := IF(@t > 0 AND @c = 0, 'ALTER TABLE groups_channels ADD COLUMN tracking_json JSON NULL DEFAULT NULL AFTER moderation_json', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'groups_channels' AND COLUMN_NAME = 'username');
SET @sql := IF(@t > 0 AND @c = 0, 'ALTER TABLE groups_channels ADD COLUMN username VARCHAR(64) NULL DEFAULT NULL AFTER title', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'groups_channels' AND COLUMN_NAME = 'status');
SET @sql := IF(@t > 0 AND @c = 0, 'ALTER TABLE groups_channels ADD COLUMN status VARCHAR(32) NULL DEFAULT NULL AFTER username', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'groups_channels' AND COLUMN_NAME = 'permissions_json');
SET @sql := IF(@t > 0 AND @c = 0, 'ALTER TABLE groups_channels ADD COLUMN permissions_json JSON NULL DEFAULT NULL AFTER status', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'groups_channels' AND COLUMN_NAME = 'settings_json');
SET @sql := IF(@t > 0 AND @c = 0, 'ALTER TABLE groups_channels ADD COLUMN settings_json JSON NULL DEFAULT NULL AFTER permissions_json', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @c := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'groups_channels' AND COLUMN_NAME = 'last_sync_at');
SET @sql := IF(@t > 0 AND @c = 0, 'ALTER TABLE groups_channels ADD COLUMN last_sync_at DATETIME NULL DEFAULT NULL AFTER tracking_json', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;