-- 028: Import-Batches + Datei-Tracking für Multi-Datei-Anläufe (AIDA).
--
-- AIDA liefert pro Anlauf 5 Excel-Dateien (eine je Produktcode). Bisher hat die Storno-
-- Logik nach jeder Einzeldatei ausgelöst und damit fast den ganzen Bestand storniert.
-- Eine "Batch" gruppiert alle Dateien eines Anlaufs; cancelMissingBookings darf nur
-- einmal am Ende des Batches laufen (und nur, wenn list_type = 'final').

CREATE TABLE IF NOT EXISTS `import_batches` (
    `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `source`          VARCHAR(50) NOT NULL,
    `cruise`          VARCHAR(32) NOT NULL,
    `event_id`        INT UNSIGNED DEFAULT NULL,
    `ship_name`       VARCHAR(191) DEFAULT NULL,
    `departure_date`  DATE DEFAULT NULL,
    `arrival_date`    DATE DEFAULT NULL,
    `file_date`       DATE DEFAULT NULL,
    `list_type`       VARCHAR(16) DEFAULT NULL,
    `status`          VARCHAR(50) NOT NULL DEFAULT 'running',
    `started_at`      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `finished_at`     DATETIME DEFAULT NULL,
    `summary`         JSON DEFAULT NULL,
    KEY `idx_batch_source_event` (`source`, `event_id`),
    KEY `idx_batch_file_date`    (`file_date`),
    KEY `idx_batch_cruise`       (`cruise`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `import_files` (
    `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `import_batch_id` INT UNSIGNED DEFAULT NULL,
    `source`          VARCHAR(50) NOT NULL,
    `filename`        VARCHAR(255) NOT NULL,
    `file_hash`       VARCHAR(64) DEFAULT NULL,
    `file_date`       DATE DEFAULT NULL,
    `list_type`       VARCHAR(16) DEFAULT NULL,
    `cruise`          VARCHAR(32) DEFAULT NULL,
    `event_id`        INT UNSIGNED DEFAULT NULL,
    `product_code`    VARCHAR(16) DEFAULT NULL,
    `imported_at`     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `status`          VARCHAR(50) NOT NULL DEFAULT 'pending',
    `summary`         JSON DEFAULT NULL,
    UNIQUE KEY `ux_import_files_source_filename` (`source`, `filename`),
    KEY `idx_import_files_batch`   (`import_batch_id`),
    KEY `idx_import_files_status`  (`status`),
    KEY `idx_import_files_event`   (`event_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;