-- 0014 — Load hold overlay + operational controls
--
-- Phase A4 (2026-04-15): smart manifest operations slice.
--
-- Additive only. Does NOT touch the existing LoadStatus enum or the
-- load FSM transition table. Holds are a side-channel overlay that
-- sits next to status — a load can be ACTIVE-held (rows in load_holds
-- with releasedAt IS NULL) without changing its status. This preserves
-- every state-machine guarantee in @repo/config/LOAD_FSM_TRANSITIONS
-- while still giving operators real "hold/resume/delay" semantics.
--
-- Idempotent. Safe to re-run against local dev databases that may
-- already be partially migrated.

-- ─────────────────────────────────────────────────────────────────────
-- 1. Extend `loads` with operational-control columns.
-- ─────────────────────────────────────────────────────────────────────

-- Wrap ADD COLUMN calls in a stored procedure so re-runs don't fail.
DROP PROCEDURE IF EXISTS sky_add_col_if_missing_0014;
DELIMITER $$
CREATE PROCEDURE sky_add_col_if_missing_0014(
  IN t_name VARCHAR(64),
  IN c_name VARCHAR(64),
  IN c_def  VARCHAR(255)
)
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_schema = DATABASE()
      AND table_name   = t_name
      AND column_name  = c_name
  ) THEN
    SET @sql := CONCAT('ALTER TABLE `', t_name, '` ADD COLUMN `', c_name, '` ', c_def);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;
END$$
DELIMITER ;

CALL sky_add_col_if_missing_0014('loads', 'secondPilotId',        'INT NULL');
CALL sky_add_col_if_missing_0014('loads', 'crewMemberIds',        'JSON NULL');
CALL sky_add_col_if_missing_0014('loads', 'delayMinutes',         'INT NULL');
CALL sky_add_col_if_missing_0014('loads', 'delayReason',          'VARCHAR(120) NULL');
CALL sky_add_col_if_missing_0014('loads', 'closedForManifesting', 'TINYINT(1) NOT NULL DEFAULT 0');

DROP PROCEDURE IF EXISTS sky_add_col_if_missing_0014;

-- Index secondPilotId for fast lookups.
DROP PROCEDURE IF EXISTS sky_add_idx_if_missing_0014;
DELIMITER $$
CREATE PROCEDURE sky_add_idx_if_missing_0014(
  IN t_name VARCHAR(64),
  IN i_name VARCHAR(64),
  IN i_def  VARCHAR(255)
)
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name   = t_name
      AND index_name   = i_name
  ) THEN
    SET @sql := CONCAT('ALTER TABLE `', t_name, '` ADD INDEX `', i_name, '` ', i_def);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;
END$$
DELIMITER ;

CALL sky_add_idx_if_missing_0014('loads', 'loads_secondPilotId_idx', '(`secondPilotId`)');

DROP PROCEDURE IF EXISTS sky_add_idx_if_missing_0014;

-- Foreign key for secondPilotId → users.id, RESTRICT on delete to
-- match the existing pilotId FK semantics.
SET @fk_exists := (
  SELECT COUNT(*) FROM information_schema.table_constraints
  WHERE table_schema    = DATABASE()
    AND table_name      = 'loads'
    AND constraint_type = 'FOREIGN KEY'
    AND constraint_name = 'loads_secondPilotId_fkey'
);
SET @sql := IF(@fk_exists = 0,
  'ALTER TABLE `loads` ADD CONSTRAINT `loads_secondPilotId_fkey` FOREIGN KEY (`secondPilotId`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE',
  'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ─────────────────────────────────────────────────────────────────────
-- 2. `load_holds` overlay table.
-- ─────────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS `load_holds` (
  `id`                 INT NOT NULL AUTO_INCREMENT,
  `loadId`             INT NOT NULL,
  `reasonCode`         ENUM(
                         'WEATHER_HOLD',
                         'ATC_HOLD',
                         'AIRCRAFT_ISSUE',
                         'PILOT_UNAVAILABLE',
                         'REFUEL_DELAY',
                         'JUMPER_MISSING',
                         'GEAR_CHECK_ISSUE',
                         'SAFETY_REVIEW',
                         'RUNWAY_LANDING_AREA_ISSUE',
                         'OTHER'
                       ) NOT NULL,
  `reasonDetail`       TEXT NULL,
  `expectedMinutes`    INT NULL,
  `createdByUserId`    INT NOT NULL,
  `createdAt`          DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `releasedByUserId`   INT NULL,
  `releasedAt`         DATETIME(3) NULL,
  PRIMARY KEY (`id`),
  INDEX `load_holds_loadId_releasedAt_idx` (`loadId`, `releasedAt`),
  INDEX `load_holds_createdByUserId_idx`   (`createdByUserId`),
  INDEX `load_holds_createdAt_idx`         (`createdAt`),
  CONSTRAINT `load_holds_loadId_fkey`
    FOREIGN KEY (`loadId`)
    REFERENCES `loads`(`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `load_holds_createdByUserId_fkey`
    FOREIGN KEY (`createdByUserId`)
    REFERENCES `users`(`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE,
  CONSTRAINT `load_holds_releasedByUserId_fkey`
    FOREIGN KEY (`releasedByUserId`)
    REFERENCES `users`(`id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;