-- Phase 3 — Landing direction + override (2026-04-15)
-- Strictly additive. Idempotent. MySQL 8 procedure-wrapped.
--
-- Adds the `landing_overrides` table so:
--   1. /weather/landing can return the active override (or fall back to
--      the live recommendation when none exists).
--   2. POST /weather/landing/override and clear-override have a real
--      audit trail with who/when/why.
--   3. Override events feed the operational audit log alongside the
--      existing AuditLog entries (defense in depth).

DROP PROCEDURE IF EXISTS create_landing_overrides_if_missing;
DELIMITER $$
CREATE PROCEDURE create_landing_overrides_if_missing()
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME = 'landing_overrides'
  ) THEN
    CREATE TABLE `landing_overrides` (
      `id` INT NOT NULL AUTO_INCREMENT,
      `dropzoneId` INT NOT NULL,
      `headingDeg` INT NOT NULL,
      `patternHand` VARCHAR(8) NOT NULL,
      `reason` VARCHAR(255) NOT NULL,
      `setById` INT NOT NULL,
      `setAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
      `expiresAt` DATETIME(3) NULL,
      `clearedAt` DATETIME(3) NULL,
      `clearedById` INT NULL,
      `clearedReason` VARCHAR(255) NULL,
      PRIMARY KEY (`id`),
      INDEX `landing_overrides_dropzoneId_clearedAt_idx` (`dropzoneId`, `clearedAt`),
      INDEX `landing_overrides_dropzoneId_setAt_idx` (`dropzoneId`, `setAt`),
      CONSTRAINT `landing_overrides_dropzoneId_fkey`
        FOREIGN KEY (`dropzoneId`) REFERENCES `dropzones`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
      CONSTRAINT `landing_overrides_setById_fkey`
        FOREIGN KEY (`setById`) REFERENCES `users`(`id`)
        ON DELETE RESTRICT ON UPDATE CASCADE,
      CONSTRAINT `landing_overrides_clearedById_fkey`
        FOREIGN KEY (`clearedById`) REFERENCES `users`(`id`)
        ON DELETE SET NULL ON UPDATE CASCADE
    );
  END IF;
END$$
DELIMITER ;
CALL create_landing_overrides_if_missing();
DROP PROCEDURE IF EXISTS create_landing_overrides_if_missing;