-- Phase 4 — Weather alerts (2026-04-15)
-- Strictly additive. Idempotent. MySQL 8 procedure-wrapped.
--
-- Adds the `weather_alerts` table so:
--   1. Auto-evaluator (driven by /weather/alerts reads) can persist
--      generated alerts and dedupe by triggerKey.
--   2. Manual alerts can be raised by ops staff with the same lifecycle
--      (acknowledged → resolved) as auto alerts.
--   3. Audit trail records who created/acknowledged/resolved each alert.

DROP PROCEDURE IF EXISTS create_weather_alerts_if_missing;
DELIMITER $$
CREATE PROCEDURE create_weather_alerts_if_missing()
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME = 'weather_alerts'
  ) THEN
    CREATE TABLE `weather_alerts` (
      `id` INT NOT NULL AUTO_INCREMENT,
      `dropzoneId` INT NOT NULL,
      `type` VARCHAR(48) NOT NULL,
      `severity` VARCHAR(16) NOT NULL,
      `source` VARCHAR(8) NOT NULL,
      `title` VARCHAR(255) NOT NULL,
      `message` TEXT NOT NULL,
      `triggerKey` VARCHAR(96) NOT NULL,
      `status` VARCHAR(16) NOT NULL,
      `snapshotAtTrigger` JSON NULL,
      `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
      `createdById` INT NULL,
      `acknowledgedAt` DATETIME(3) NULL,
      `acknowledgedById` INT NULL,
      `resolvedAt` DATETIME(3) NULL,
      `resolvedById` INT NULL,
      `resolvedReason` VARCHAR(255) NULL,
      PRIMARY KEY (`id`),
      INDEX `weather_alerts_dropzoneId_status_idx` (`dropzoneId`, `status`),
      INDEX `weather_alerts_dropzoneId_createdAt_idx` (`dropzoneId`, `createdAt`),
      INDEX `weather_alerts_dropzoneId_triggerKey_status_idx`
        (`dropzoneId`, `triggerKey`, `status`),
      CONSTRAINT `weather_alerts_dropzoneId_fkey`
        FOREIGN KEY (`dropzoneId`) REFERENCES `dropzones`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
      CONSTRAINT `weather_alerts_createdById_fkey`
        FOREIGN KEY (`createdById`) REFERENCES `users`(`id`)
        ON DELETE SET NULL ON UPDATE CASCADE,
      CONSTRAINT `weather_alerts_acknowledgedById_fkey`
        FOREIGN KEY (`acknowledgedById`) REFERENCES `users`(`id`)
        ON DELETE SET NULL ON UPDATE CASCADE,
      CONSTRAINT `weather_alerts_resolvedById_fkey`
        FOREIGN KEY (`resolvedById`) REFERENCES `users`(`id`)
        ON DELETE SET NULL ON UPDATE CASCADE
    );
  END IF;
END$$
DELIMITER ;
CALL create_weather_alerts_if_missing();
DROP PROCEDURE IF EXISTS create_weather_alerts_if_missing;