-- Phase 2 Slice 1 — Jump Run / Drift / Canopy read-only logic (2026-04-15)
-- Strictly additive. Idempotent. MySQL 8 procedure-wrapped (matches 0009/0010).
--
-- Adds the `winds_aloft` table so:
--   1. /weather/drift can persist real provider readings (Open-Meteo
--      pressure-level API) and serve them on subsequent calls.
--   2. The estimated-multiplier fallback can be persisted with an
--      explicit confidence tag so the UI knows to show "ESTIMATED" vs
--      "LIVE" — same trust pattern as Slice 1's WeatherData.source.
--   3. A future pre-warm extension can pre-fetch winds aloft for warm
--      dropzones without changing the table shape.
--
-- Each row is one altitude layer. Multiple rows sharing the same
-- (dropzoneId, fetchedAt) form one full vertical profile.

DROP PROCEDURE IF EXISTS create_winds_aloft_if_missing;
DELIMITER $$
CREATE PROCEDURE create_winds_aloft_if_missing()
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME = 'winds_aloft'
  ) THEN
    CREATE TABLE `winds_aloft` (
      `id` INT NOT NULL AUTO_INCREMENT,
      `dropzoneId` INT NOT NULL,
      `fetchedAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
      `altitudeFt` INT NOT NULL,
      `windSpeedKts` INT NOT NULL,
      `windDirectionDeg` INT NOT NULL,
      `source` VARCHAR(32) NOT NULL,
      `confidence` VARCHAR(16) NOT NULL,
      `pressureLevelHpa` INT NULL,
      PRIMARY KEY (`id`),
      INDEX `winds_aloft_dropzoneId_fetchedAt_idx` (`dropzoneId`, `fetchedAt`),
      INDEX `winds_aloft_dropzoneId_altitudeFt_fetchedAt_idx`
        (`dropzoneId`, `altitudeFt`, `fetchedAt`),
      CONSTRAINT `winds_aloft_dropzoneId_fkey`
        FOREIGN KEY (`dropzoneId`) REFERENCES `dropzones`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
    );
  END IF;
END$$
DELIMITER ;
CALL create_winds_aloft_if_missing();
DROP PROCEDURE IF EXISTS create_winds_aloft_if_missing;