-- =====================================================================
-- Kapitano Logistic integration - Step 5 (webhook receiver, display only)
--
--   logistic_webhook_events   NEW. Every callback Logistic sends, once.
--                             event_id is UNIQUE: a retried delivery is
--                             recognised by the database and never applied twice.
--   logistic_order_links      three columns added (our own table, step 1):
--                             last_event, last_event_at, snapshot.
--
-- No existing shop table is altered.
-- Same statements as migration 2026_10_06_000010_create_logistic_webhook_tables.
-- Requires the Step 1 file (kapitano_logistic_integration_2026-10-05.sql) to have run first.
--
-- NOT idempotent for the ALTERs on MySQL < 8.0.29 (no ADD COLUMN IF NOT EXISTS):
-- run the ALTER block once, or use `php artisan migrate` which checks each column.
-- =====================================================================

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS `logistic_webhook_events` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `event_id` CHAR(36) NOT NULL,
    `event` VARCHAR(40) NOT NULL,
    `order_id` BIGINT UNSIGNED NULL,
    `logistic_uuid` CHAR(36) NULL,
    `sequence` INT UNSIGNED NULL,
    `attempt` TINYINT UNSIGNED NULL,
    `occurred_at` TIMESTAMP NULL,
    `outcome` VARCHAR(20) NOT NULL,
    `note` VARCHAR(255) NULL,
    `payload` LONGTEXT NOT NULL,
    `received_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `logistic_webhook_events_event_id_unique` (`event_id`),
    KEY `logistic_webhook_events_event_index` (`event`),
    KEY `logistic_webhook_events_order_id_index` (`order_id`),
    KEY `logistic_webhook_events_outcome_index` (`outcome`),
    KEY `logistic_webhook_events_received_at_index` (`received_at`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

ALTER TABLE `logistic_order_links`
    ADD COLUMN `last_event` VARCHAR(40) NULL,
    ADD COLUMN `last_event_at` TIMESTAMP NULL,
    ADD COLUMN `snapshot` LONGTEXT NULL;