-- Phase M-Slice2 — Automated Meteorological Manifest Framework.
--
-- Creates the `meteo_policy_overrides` table for per-DZ threshold
-- tuning of the deterministic eligibility subsystem. Every column
-- is nullable so the default policy (per license level) remains in
-- effect unless the DZ operator explicitly overrides it.
--
-- Idempotent: uses INFORMATION_SCHEMA to check before creating,
-- matching the pattern used by earlier SkyLara migrations.

DELIMITER $$

DROP PROCEDURE IF EXISTS create_meteo_policy_overrides $$
CREATE PROCEDURE create_meteo_policy_overrides()
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'meteo_policy_overrides'
  ) THEN
    CREATE TABLE `meteo_policy_overrides` (
      `id`                 INT NOT NULL AUTO_INCREMENT,
      `dropzoneId`         INT NOT NULL,
      `steadyWindHold`     INT NULL,
      `steadyWindCaution`  INT NULL,
      `gustPeakHold`       INT NULL,
      `gustPeakCaution`    INT NULL,
      `gustSpreadHold`     INT NULL,
      `gustSpreadCaution`  INT NULL,
      `headwindHold`       INT NULL,
      `headwindCaution`    INT NULL,
      `crosswindHold`      INT NULL,
      `crosswindCaution`   INT NULL,
      `updatedAt`          DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
      `updatedByUserId`    INT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `meteo_policy_overrides_dropzoneId_key` (`dropzoneId`),
      CONSTRAINT `meteo_policy_overrides_dropzoneId_fkey`
        FOREIGN KEY (`dropzoneId`) REFERENCES `dropzones`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  END IF;
END $$

CALL create_meteo_policy_overrides() $$
DROP PROCEDURE create_meteo_policy_overrides $$

DELIMITER ;