-- Phase 1 — Weather Ops Foundation (2026-04-15)
-- Strictly additive. No drops. No renames.
--
-- Adds provenance + audit completeness to the weather module so:
--   1. /weather and /weather/summary can fall back to the snapshot store
--      with honest freshness tagging when Open-Meteo is unreachable,
--      instead of returning silent YELLOW/78 mock data.
--   2. /weather/observation can legitimately write `source`, `observedById`,
--      and `notes` (previously cast `as any` against non-existent fields).
--   3. /weather/holds/:id/clear can populate `releasedBy` in responses
--      and audit-log beforeState/afterState.
--
-- MySQL 8.0 does not support ADD COLUMN IF NOT EXISTS, so each step is
-- wrapped in a procedure for idempotency — same pattern as 0009.

-- ---------------------------------------------------------------------------
-- WeatherData.source
-- ---------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS add_weather_data_source_if_missing;
DELIMITER $$
CREATE PROCEDURE add_weather_data_source_if_missing()
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'weather_data' AND COLUMN_NAME = 'source'
  ) THEN
    ALTER TABLE `weather_data`
      ADD COLUMN `source` VARCHAR(32) NULL DEFAULT 'OPEN_METEO';
  END IF;
END$$
DELIMITER ;
CALL add_weather_data_source_if_missing();
DROP PROCEDURE IF EXISTS add_weather_data_source_if_missing;

-- ---------------------------------------------------------------------------
-- WeatherData.observedById  (nullable; SetNull on User delete)
-- ---------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS add_weather_data_observed_by_if_missing;
DELIMITER $$
CREATE PROCEDURE add_weather_data_observed_by_if_missing()
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'weather_data' AND COLUMN_NAME = 'observedById'
  ) THEN
    ALTER TABLE `weather_data`
      ADD COLUMN `observedById` INT NULL;
  END IF;
END$$
DELIMITER ;
CALL add_weather_data_observed_by_if_missing();
DROP PROCEDURE IF EXISTS add_weather_data_observed_by_if_missing;

-- ---------------------------------------------------------------------------
-- WeatherData.notes
-- ---------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS add_weather_data_notes_if_missing;
DELIMITER $$
CREATE PROCEDURE add_weather_data_notes_if_missing()
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'weather_data' AND COLUMN_NAME = 'notes'
  ) THEN
    ALTER TABLE `weather_data`
      ADD COLUMN `notes` TEXT NULL;
  END IF;
END$$
DELIMITER ;
CALL add_weather_data_notes_if_missing();
DROP PROCEDURE IF EXISTS add_weather_data_notes_if_missing;

-- ---------------------------------------------------------------------------
-- WeatherData (dropzoneId, fetchedAt) composite index for snapshot-store lookups
-- ---------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS add_weather_data_snapshot_index_if_missing;
DELIMITER $$
CREATE PROCEDURE add_weather_data_snapshot_index_if_missing()
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
    WHERE TABLE_NAME = 'weather_data'
      AND INDEX_NAME = 'weather_data_dropzoneId_fetchedAt_idx'
  ) THEN
    CREATE INDEX `weather_data_dropzoneId_fetchedAt_idx`
      ON `weather_data` (`dropzoneId`, `fetchedAt`);
  END IF;
END$$
DELIMITER ;
CALL add_weather_data_snapshot_index_if_missing();
DROP PROCEDURE IF EXISTS add_weather_data_snapshot_index_if_missing;

-- ---------------------------------------------------------------------------
-- WeatherData FK to users.id for observedById
-- ---------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS add_weather_data_observed_by_fk_if_missing;
DELIMITER $$
CREATE PROCEDURE add_weather_data_observed_by_fk_if_missing()
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE TABLE_NAME = 'weather_data'
      AND COLUMN_NAME = 'observedById'
      AND REFERENCED_TABLE_NAME = 'users'
  ) THEN
    ALTER TABLE `weather_data`
      ADD CONSTRAINT `weather_data_observedById_fkey`
      FOREIGN KEY (`observedById`) REFERENCES `users`(`id`)
      ON DELETE SET NULL ON UPDATE CASCADE;
  END IF;
END$$
DELIMITER ;
CALL add_weather_data_observed_by_fk_if_missing();
DROP PROCEDURE IF EXISTS add_weather_data_observed_by_fk_if_missing;

-- ---------------------------------------------------------------------------
-- WeatherHold FK to users.id for releasedById (column already exists)
-- ---------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS add_weather_hold_released_by_fk_if_missing;
DELIMITER $$
CREATE PROCEDURE add_weather_hold_released_by_fk_if_missing()
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE TABLE_NAME = 'weather_holds'
      AND COLUMN_NAME = 'releasedById'
      AND REFERENCED_TABLE_NAME = 'users'
  ) THEN
    ALTER TABLE `weather_holds`
      ADD CONSTRAINT `weather_holds_releasedById_fkey`
      FOREIGN KEY (`releasedById`) REFERENCES `users`(`id`)
      ON DELETE SET NULL ON UPDATE CASCADE;
  END IF;
END$$
DELIMITER ;
CALL add_weather_hold_released_by_fk_if_missing();
DROP PROCEDURE IF EXISTS add_weather_hold_released_by_fk_if_missing;

-- ---------------------------------------------------------------------------
-- WeatherHold (dropzoneId, releasedAt) composite index for active-hold lookups
-- ---------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS add_weather_hold_active_index_if_missing;
DELIMITER $$
CREATE PROCEDURE add_weather_hold_active_index_if_missing()
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
    WHERE TABLE_NAME = 'weather_holds'
      AND INDEX_NAME = 'weather_holds_dropzoneId_releasedAt_idx'
  ) THEN
    CREATE INDEX `weather_holds_dropzoneId_releasedAt_idx`
      ON `weather_holds` (`dropzoneId`, `releasedAt`);
  END IF;
END$$
DELIMITER ;
CALL add_weather_hold_active_index_if_missing();
DROP PROCEDURE IF EXISTS add_weather_hold_active_index_if_missing;