-- =====================================================================
-- Kapitano Logistic integration - Step 1 (foundation)
--
-- Two NEW tables - no existing table is altered, and neither has a
-- foreign key to `orders`, so nothing about orders can be affected:
--
--   logistic_order_links   which shop order became which Logistic order
--   logistic_outbox        what still has to be sent to Logistic
--
-- Both stay empty, and nothing in the shop reads them, until
-- LOGISTIC_ENABLED=true is set in .env.
--
-- Same statements as migration 2026_10_05_000010_create_logistic_integration_tables.
-- Re-running the file changes nothing.
-- =====================================================================

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS `logistic_order_links` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `order_id` BIGINT UNSIGNED NOT NULL,
    `logistic_uuid` CHAR(36) NULL,
    `logistic_order_number` VARCHAR(40) NULL,
    `state` VARCHAR(20) NOT NULL DEFAULT 'pending',
    `last_status` VARCHAR(30) NULL,
    `last_sequence` INT UNSIGNED NULL,
    `last_error` TEXT NULL,
    `linked_at` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `logistic_order_links_order_id_unique` (`order_id`),
    UNIQUE KEY `logistic_order_links_logistic_uuid_unique` (`logistic_uuid`),
    KEY `logistic_order_links_state_index` (`state`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `logistic_outbox` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `order_id` BIGINT UNSIGNED NOT NULL,
    `operation` VARCHAR(20) NOT NULL,
    `idempotency_key` VARCHAR(128) NOT NULL,
    `payload` LONGTEXT NULL,
    `status` VARCHAR(20) NOT NULL DEFAULT 'pending',
    `attempts` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    `next_attempt_at` TIMESTAMP NULL DEFAULT NULL,
    `claimed_at` TIMESTAMP NULL DEFAULT NULL,
    `claim_token` CHAR(36) NULL,
    `last_http_status` SMALLINT UNSIGNED NULL,
    `last_error` TEXT NULL,
    `processed_at` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `logistic_outbox_idempotency_key_unique` (`idempotency_key`),
    KEY `logistic_outbox_order_id_index` (`order_id`),
    KEY `logistic_outbox_due_index` (`status`, `next_attempt_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;