-- 0024 — Rule usage daily rollup table.
--
-- Purpose: pre-aggregate `rule_usage_events` into one row per
--   (dropzoneId, ruleKey, day). This lets us prune the raw events table
--   on a 30-day retention policy without losing long-horizon reporting.
--
-- Populated by a daily job (scripts/rule_usage_daily_rollup.ts) that
-- upserts per-day counts. Safe to re-run; uniqueness is enforced by the
-- (dropzoneId, ruleKey, day) composite key.

CREATE TABLE IF NOT EXISTS `rule_usage_daily` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `dropzoneId` INT NOT NULL,
  `ruleKey` VARCHAR(100) NOT NULL,
  `category` VARCHAR(30) NOT NULL,
  `day` DATE NOT NULL,
  `warningCount` INT NOT NULL DEFAULT 0,
  `blockCount` INT NOT NULL DEFAULT 0,
  `overrideCount` INT NOT NULL DEFAULT 0,
  `approvalCount` INT NOT NULL DEFAULT 0,
  `totalCount` INT NOT NULL DEFAULT 0,
  `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt` DATETIME(3) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `rule_usage_daily_dz_key_day` (`dropzoneId`, `ruleKey`, `day`),
  INDEX `rule_usage_daily_dz_day_idx` (`dropzoneId`, `day`),
  INDEX `rule_usage_daily_key_day_idx` (`ruleKey`, `day`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;