-- 0026 — Manifest Print Profile framework (Phase 4 / 2026-04-17)
--
-- Adds dashboard-editable print + screen column configuration and an
-- immutable snapshot model for frozen final prints. Spec:
-- docs/SkyLara_Manifest_Print_Profile_Final.md.
--
-- PRINCIPLE: AI decides the safety-first exit order. DZ managers decide
-- the display. This migration does NOT modify any existing table,
-- enum, or index. No change to Load, Slot, LoadHold, or the exit-order
-- algorithm. Pure additive. Safe to re-run.

-- ManifestPrintProfile
CREATE TABLE IF NOT EXISTS `manifest_print_profiles` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `dropzoneId` INT NOT NULL,
  `name` VARCHAR(120) NOT NULL,
  `description` TEXT NULL,
  `appliesToScreen` BOOLEAN NOT NULL DEFAULT TRUE,
  `appliesToPrint` BOOLEAN NOT NULL DEFAULT TRUE,
  `isDefault` BOOLEAN NOT NULL DEFAULT FALSE,
  `paperSize` ENUM('A4', 'A3', 'LETTER', 'LEGAL') NOT NULL DEFAULT 'A4',
  `orientation` ENUM('PORTRAIT', 'LANDSCAPE') NOT NULL DEFAULT 'PORTRAIT',
  `fitMode` ENUM('FIT_WIDTH', 'FIT_PAGE', 'SCALE_PERCENT') NOT NULL DEFAULT 'FIT_WIDTH',
  `scalePercent` INT NULL,
  `fontSizePt` INT NOT NULL DEFAULT 10,
  `rowSpacingPt` INT NOT NULL DEFAULT 4,
  `colorMode` ENUM('FULL_COLOR', 'GRAYSCALE', 'BLACK_ONLY') NOT NULL DEFAULT 'FULL_COLOR',
  `includeLogo` BOOLEAN NOT NULL DEFAULT TRUE,
  `includeTimestamp` BOOLEAN NOT NULL DEFAULT TRUE,
  `includeLoadMetadata` BOOLEAN NOT NULL DEFAULT TRUE,
  `createdByUserId` INT NULL,
  `updatedByUserId` INT NULL,
  `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt` DATETIME(3) NOT NULL,
  `deletedAt` DATETIME(3) NULL,

  PRIMARY KEY (`id`),
  UNIQUE KEY `manifest_print_profiles_dz_name_unique` (`dropzoneId`, `name`),
  INDEX `manifest_print_profiles_dz_default_idx` (`dropzoneId`, `isDefault`),
  INDEX `manifest_print_profiles_dz_deleted_idx` (`dropzoneId`, `deletedAt`),
  CONSTRAINT `manifest_print_profiles_dz_fk` FOREIGN KEY (`dropzoneId`) REFERENCES `dropzones` (`id`) ON DELETE CASCADE,
  CONSTRAINT `manifest_print_profiles_created_by_fk` FOREIGN KEY (`createdByUserId`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `manifest_print_profiles_updated_by_fk` FOREIGN KEY (`updatedByUserId`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ManifestPrintColumnSetting
CREATE TABLE IF NOT EXISTS `manifest_print_column_settings` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `profileId` INT NOT NULL,
  `columnKey` ENUM(
    'EXIT_ORDER', 'JUMP_TYPE', 'GROUP_NAMES', 'CANOPY_SIZE', 'LICENSE_LEVEL',
    'RIG_NUMBER', 'BODY_WEIGHT', 'EXIT_WEIGHT', 'NOTES', 'WARNINGS'
  ) NOT NULL,
  `visibleOnScreen` BOOLEAN NOT NULL DEFAULT TRUE,
  `visibleOnPrint` BOOLEAN NOT NULL DEFAULT TRUE,
  `printOrder` INT NOT NULL DEFAULT 0,
  `widthPreset` ENUM('NARROW', 'NORMAL', 'WIDE') NOT NULL DEFAULT 'NORMAL',
  `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

  PRIMARY KEY (`id`),
  UNIQUE KEY `manifest_print_column_settings_profile_col_unique` (`profileId`, `columnKey`),
  INDEX `manifest_print_column_settings_profile_order_idx` (`profileId`, `printOrder`),
  CONSTRAINT `manifest_print_column_settings_profile_fk` FOREIGN KEY (`profileId`) REFERENCES `manifest_print_profiles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ManifestPrintHighlightRule
CREATE TABLE IF NOT EXISTS `manifest_print_highlight_rules` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `profileId` INT NOT NULL,
  `ruleKey` ENUM(
    'TRACKING_CAUTION', 'MISSING_DATA', 'STUDENT_ROW', 'TANDEM_ROW',
    'WINGSUIT_ROW', 'MANUAL_REVIEW', 'SPECIAL_LOAD'
  ) NOT NULL,
  `enabled` BOOLEAN NOT NULL DEFAULT TRUE,
  `colorKey` VARCHAR(40) NOT NULL,
  `appliesToScreen` BOOLEAN NOT NULL DEFAULT TRUE,
  `appliesToPrint` BOOLEAN NOT NULL DEFAULT TRUE,
  `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

  PRIMARY KEY (`id`),
  UNIQUE KEY `manifest_print_highlight_rules_profile_rule_unique` (`profileId`, `ruleKey`),
  CONSTRAINT `manifest_print_highlight_rules_profile_fk` FOREIGN KEY (`profileId`) REFERENCES `manifest_print_profiles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- FinalLoadPrintSnapshot
CREATE TABLE IF NOT EXISTS `final_load_print_snapshots` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `loadId` INT NOT NULL,
  `dropzoneId` INT NOT NULL,
  `printProfileId` INT NOT NULL,
  `versionNumber` INT NOT NULL,
  `manifestJsonSnapshot` JSON NOT NULL,
  `loadWeightSummaryJson` JSON NOT NULL,
  `safetyNotesJson` JSON NULL,
  `warningsJson` JSON NULL,
  `printReadinessAtGen` ENUM(
    'DRAFT', 'NEEDS_RECONCILIATION', 'MANIFEST_READY',
    'NOT_FINAL_FOR_PILOT_SIGN_OFF', 'FINAL_FROZEN'
  ) NOT NULL DEFAULT 'DRAFT',
  `generatedByUserId` INT NOT NULL,
  `generatedAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `isFrozen` BOOLEAN NOT NULL DEFAULT FALSE,
  `frozenAt` DATETIME(3) NULL,
  `frozenByUserId` INT NULL,
  `supersededBySnapshotId` INT NULL,
  `pdfS3Key` VARCHAR(500) NULL,

  PRIMARY KEY (`id`),
  UNIQUE KEY `final_load_print_snapshots_load_version_unique` (`loadId`, `versionNumber`),
  INDEX `final_load_print_snapshots_load_frozen_idx` (`loadId`, `isFrozen`),
  INDEX `final_load_print_snapshots_dz_idx` (`dropzoneId`),
  INDEX `final_load_print_snapshots_profile_idx` (`printProfileId`),
  INDEX `final_load_print_snapshots_generated_idx` (`generatedAt`),
  CONSTRAINT `final_load_print_snapshots_load_fk` FOREIGN KEY (`loadId`) REFERENCES `loads` (`id`) ON DELETE CASCADE,
  CONSTRAINT `final_load_print_snapshots_dz_fk` FOREIGN KEY (`dropzoneId`) REFERENCES `dropzones` (`id`) ON DELETE CASCADE,
  CONSTRAINT `final_load_print_snapshots_profile_fk` FOREIGN KEY (`printProfileId`) REFERENCES `manifest_print_profiles` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `final_load_print_snapshots_generated_by_fk` FOREIGN KEY (`generatedByUserId`) REFERENCES `users` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `final_load_print_snapshots_frozen_by_fk` FOREIGN KEY (`frozenByUserId`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `final_load_print_snapshots_superseded_by_fk` FOREIGN KEY (`supersededBySnapshotId`) REFERENCES `final_load_print_snapshots` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;