-- 0015 — Slot operational weight (body + equipment)
--
-- Phase A5 (2026-04-15): live manifest operational slice.
--
-- Canonical rule from the Live Manifest + Aircraft Weight Workflow
-- spec: `operational weight = body weight + equipment weight`,
-- with a default equipment weight of 12 kg per jumper.
-- Applies to every slot (jumpers, tandems, videographers, coaches,
-- pilots/crew if they become slot participants). Tandem / special
-- category overrides are deferred to a later slice.
--
-- Additive only. Existing `weight` column (historically in lbs in
-- seeded data) is preserved for backwards compatibility until all
-- consumers have migrated.
--
-- Idempotent.

DROP PROCEDURE IF EXISTS sky_add_col_if_missing_0015;
DELIMITER $$
CREATE PROCEDURE sky_add_col_if_missing_0015(
  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_0015('slots', 'bodyWeightKg',        'DECIMAL(5,2) NULL');
CALL sky_add_col_if_missing_0015('slots', 'equipmentWeightKg',   'DECIMAL(5,2) NOT NULL DEFAULT 12.00');
CALL sky_add_col_if_missing_0015('slots', 'operationalWeightKg', 'DECIMAL(5,2) NULL');

DROP PROCEDURE IF EXISTS sky_add_col_if_missing_0015;