-- Reports: saved filters, schedules, run history and uptime checks. Idempotent; run once against the application database.
CREATE TABLE IF NOT EXISTS `ReportFilter` (
  `id` VARCHAR(191) NOT NULL,
  `siteId` VARCHAR(191) NOT NULL,
  `entity` VARCHAR(80) NOT NULL,
  `name` VARCHAR(120) NOT NULL,
  `config` JSON NOT NULL,
  `createdById` VARCHAR(191) NOT NULL,
  `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  INDEX `ReportFilter_siteId_entity_idx` (`siteId`, `entity`),
  CONSTRAINT `ReportFilter_siteId_fkey` FOREIGN KEY (`siteId`) REFERENCES `Site`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ReportSchedule` (
  `id` VARCHAR(191) NOT NULL,
  `siteId` VARCHAR(191) NOT NULL,
  `filterId` VARCHAR(191) NULL,
  `entity` VARCHAR(80) NOT NULL,
  `name` VARCHAR(120) NOT NULL,
  `frequency` VARCHAR(10) NOT NULL,
  `recipients` JSON NOT NULL,
  `enabled` BOOLEAN NOT NULL DEFAULT true,
  `lastRanAt` DATETIME(3) NULL,
  `nextRunAt` DATETIME(3) NOT NULL,
  `createdById` VARCHAR(191) NOT NULL,
  `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  INDEX `ReportSchedule_siteId_idx` (`siteId`),
  INDEX `ReportSchedule_enabled_nextRunAt_idx` (`enabled`, `nextRunAt`),
  INDEX `ReportSchedule_filterId_fkey` (`filterId`),
  CONSTRAINT `ReportSchedule_siteId_fkey` FOREIGN KEY (`siteId`) REFERENCES `Site`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `ReportSchedule_filterId_fkey` FOREIGN KEY (`filterId`) REFERENCES `ReportFilter`(`id`) ON DELETE SET NULL ON UPDATE CASCADE
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ReportRun` (
  `id` VARCHAR(191) NOT NULL,
  `siteId` VARCHAR(191) NOT NULL,
  `scheduleId` VARCHAR(191) NOT NULL,
  `ranAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `status` VARCHAR(10) NOT NULL,
  `rowCount` INT NOT NULL DEFAULT 0,
  `csv` LONGTEXT NULL,
  `error` VARCHAR(500) NULL,
  PRIMARY KEY (`id`),
  INDEX `ReportRun_siteId_ranAt_idx` (`siteId`, `ranAt`),
  INDEX `ReportRun_scheduleId_ranAt_idx` (`scheduleId`, `ranAt`),
  CONSTRAINT `ReportRun_siteId_fkey` FOREIGN KEY (`siteId`) REFERENCES `Site`(`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `ReportRun_scheduleId_fkey` FOREIGN KEY (`scheduleId`) REFERENCES `ReportSchedule`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `UptimeCheck` (
  `id` VARCHAR(191) NOT NULL,
  `siteId` VARCHAR(191) NOT NULL,
  `checkedAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `ok` BOOLEAN NOT NULL,
  `statusCode` INT NULL,
  `ms` INT NULL,
  PRIMARY KEY (`id`),
  INDEX `UptimeCheck_siteId_checkedAt_idx` (`siteId`, `checkedAt`),
  CONSTRAINT `UptimeCheck_siteId_fkey` FOREIGN KEY (`siteId`) REFERENCES `Site`(`id`) ON DELETE CASCADE ON UPDATE CASCADE
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;