-- +goose Up
-- Migration: Create flight_prep_items table
-- Date: 2026-04-09
-- Description: Master data table for flight preparation checklist items

CREATE TABLE flight_prep_items (
    id BINARY(16) PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    section ENUM('FLIGHT_PREPARATION', 'WEIGHT_AND_BALANCE') NOT NULL,
    sort_order INT NOT NULL DEFAULT 0 CHECK (sort_order >= 0),
    is_required TINYINT(1) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by BINARY(16),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    updated_by BINARY(16),

    INDEX idx_flight_prep_items_section_active (section, is_active),
    INDEX idx_flight_prep_items_sort_order (sort_order),
    INDEX idx_flight_prep_items_created_at (created_at),
    UNIQUE KEY uk_flight_prep_items_title_section_active (title, section, is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- +goose Down
DROP TABLE IF EXISTS flight_prep_items;