-- Chery reports v1.1 - run in the existing bot database before replacing PHP files.
-- Additive and safe to run again. Existing bot tables and records are unchanged.
CREATE TABLE IF NOT EXISTS `moderation_reports` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `chat_id` BIGINT NOT NULL,
  `message_id` BIGINT NOT NULL,
  `thread_id` BIGINT DEFAULT NULL,
  `target_user_id` BIGINT DEFAULT NULL,
  `target_name` VARCHAR(255) NOT NULL,
  `reporter_user_id` BIGINT NOT NULL,
  `reporter_name` VARCHAR(255) NOT NULL,
  `excerpt` TEXT NOT NULL,
  `created_at` INT UNSIGNED NOT NULL,
  `status` VARCHAR(16) NOT NULL DEFAULT 'open',
  `admin_message_id` BIGINT DEFAULT NULL,
  `delivery_attempt_at` INT UNSIGNED NOT NULL DEFAULT 0,
  `forward_status` VARCHAR(16) NOT NULL DEFAULT 'pending',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_reported_message` (`chat_id`,`message_id`),
  KEY `ix_report_inbox` (`chat_id`,`status`,`id`),
  KEY `ix_report_delivery` (`admin_message_id`,`delivery_attempt_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `moderation_report_limits` (
  `chat_id` BIGINT NOT NULL,
  `user_id` BIGINT NOT NULL,
  `next_allowed_at` INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`chat_id`,`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `moderation_recent_messages` (
  `chat_id` BIGINT NOT NULL,
  `message_id` BIGINT NOT NULL,
  `user_id` BIGINT NOT NULL,
  `sent_at` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`chat_id`,`message_id`),
  KEY `ix_recent_user` (`chat_id`,`user_id`,`sent_at`),
  KEY `ix_recent_expiry` (`sent_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE IF NOT EXISTS `moderation_report_actions` (
  `report_id` BIGINT UNSIGNED NOT NULL,
  `action` VARCHAR(16) NOT NULL,
  `admin_id` BIGINT NOT NULL,
  `outcome` VARCHAR(16) NOT NULL,
  `detail` VARCHAR(255) NOT NULL DEFAULT '',
  `updated_at` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`report_id`,`action`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;