-- Version20260508120000
-- Create logistic event persistence tables and add current_state to core logistic entities

-- =========================
-- UP
-- =========================

CREATE TABLE `logistic_event` (
    `id` INT AUTO_INCREMENT NOT NULL,
    `maincompany_id` INT NOT NULL,
    `entity_type` VARCHAR(64) NOT NULL,
    `entity_id` INT NOT NULL,
    `event_code` VARCHAR(100) NOT NULL,
    `event_label` VARCHAR(255) NOT NULL,
    `source_type` VARCHAR(32) NOT NULL,
    `visibility` VARCHAR(32) NOT NULL,
    `description` LONGTEXT DEFAULT NULL,
    `metadata` JSON DEFAULT NULL,
    `created_by_id` INT DEFAULT NULL,
    `occurred_at` DATETIME NOT NULL,
    `created_at` DATETIME NOT NULL,
    INDEX `idx_logistic_event_maincompany_id` (`maincompany_id`),
    INDEX `idx_logistic_event_entity_type` (`entity_type`),
    INDEX `idx_logistic_event_event_code` (`event_code`),
    INDEX `idx_logistic_event_maincompany_entity_type` (`maincompany_id`, `entity_type`),
    INDEX `idx_logistic_event_maincompany_event_code` (`maincompany_id`, `event_code`),
    INDEX `idx_logistic_event_maincompany_entity_entity_id` (`maincompany_id`, `entity_type`, `entity_id`),
    PRIMARY KEY(`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

CREATE TABLE `event_definition` (
    `id` INT AUTO_INCREMENT NOT NULL,
    `maincompany_id` INT DEFAULT NULL,
    `entity_type` VARCHAR(64) NOT NULL,
    `code` VARCHAR(100) NOT NULL,
    `label` VARCHAR(255) NOT NULL,
    `description` LONGTEXT DEFAULT NULL,
    `source_type` VARCHAR(32) NOT NULL,
    `default_visibility` VARCHAR(32) NOT NULL,
    `affects_state` TINYINT(1) NOT NULL,
    `target_state` VARCHAR(64) DEFAULT NULL,
    `is_user_creatable` TINYINT(1) NOT NULL,
    `is_active` TINYINT(1) NOT NULL,
    `created_at` DATETIME NOT NULL,
    `updated_at` DATETIME NOT NULL,
    INDEX `idx_event_definition_maincompany_id` (`maincompany_id`),
    INDEX `idx_event_definition_entity_type` (`entity_type`),
    INDEX `idx_event_definition_is_active` (`is_active`),
    INDEX `idx_event_definition_maincompany_entity_type` (`maincompany_id`, `entity_type`),
    INDEX `idx_event_definition_maincompany_code` (`maincompany_id`, `code`),
    UNIQUE INDEX `uniq_event_definition_maincompany_entity_code` (`maincompany_id`, `entity_type`, `code`),
    PRIMARY KEY(`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

CREATE TABLE `tenant_workflow_rule` (
    `id` INT AUTO_INCREMENT NOT NULL,
    `maincompany_id` INT NOT NULL,
    `entity_type` VARCHAR(64) NOT NULL,
    `event_code` VARCHAR(100) NOT NULL,
    `is_enabled` TINYINT(1) NOT NULL,
    `requires_previous_event_code` VARCHAR(100) DEFAULT NULL,
    `allowed_from_states` JSON DEFAULT NULL,
    `requires_release` TINYINT(1) NOT NULL,
    `requires_pod` TINYINT(1) NOT NULL,
    `requires_no_customs_hold` TINYINT(1) NOT NULL,
    `requires_documents_complete` TINYINT(1) NOT NULL,
    `is_active` TINYINT(1) NOT NULL,
    `created_at` DATETIME NOT NULL,
    `updated_at` DATETIME NOT NULL,
    INDEX `idx_tenant_workflow_rule_maincompany_id` (`maincompany_id`),
    INDEX `idx_tenant_workflow_rule_entity_type` (`entity_type`),
    INDEX `idx_tenant_workflow_rule_event_code` (`event_code`),
    INDEX `idx_tenant_workflow_rule_is_enabled` (`is_enabled`),
    INDEX `idx_tenant_workflow_rule_is_active` (`is_active`),
    INDEX `idx_tenant_workflow_rule_maincompany_entity_type` (`maincompany_id`, `entity_type`),
    INDEX `idx_tenant_workflow_rule_maincompany_event_code` (`maincompany_id`, `event_code`),
    PRIMARY KEY(`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

ALTER TABLE `hbl` ADD `current_state` VARCHAR(64) DEFAULT NULL;
ALTER TABLE `awb` ADD `current_state` VARCHAR(64) DEFAULT NULL;

-- =========================
-- DOWN
-- =========================

ALTER TABLE `hbl` DROP `current_state`;
ALTER TABLE `awb` DROP `current_state`;

DROP TABLE `tenant_workflow_rule`;
DROP TABLE `event_definition`;
DROP TABLE `logistic_event`;