-- Workflow tables with entity relations for HBL, AWB and Pickup.
-- This file can be executed on databases that already have part of the workflow schema.
-- The application writes entity references through entity_type + entity_id.
-- Generated columns below allow database-level foreign keys for each supported entity type.

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

CREATE TABLE IF NOT EXISTS `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`),
    CONSTRAINT `chk_event_definition_entity_type`
        CHECK (`entity_type` IN ('awb', 'hbl', 'pickup', 'listpickup')),
    CONSTRAINT `fk_event_definition_maincompany`
        FOREIGN KEY (`maincompany_id`) REFERENCES `maincompany` (`id`) ON DELETE CASCADE,
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `logistic_event` (
    `id` INT AUTO_INCREMENT NOT NULL,
    `maincompany_id` INT NOT NULL,
    `entity_type` VARCHAR(64) NOT NULL,
    `entity_id` INT NOT NULL,
    `hbl_id` INT GENERATED ALWAYS AS (CASE WHEN `entity_type` = 'hbl' THEN `entity_id` ELSE NULL END) STORED,
    `awb_id` INT GENERATED ALWAYS AS (CASE WHEN `entity_type` = 'awb' THEN `entity_id` ELSE NULL END) STORED,
    `pickup_id` INT GENERATED ALWAYS AS (CASE WHEN `entity_type` = 'pickup' THEN `entity_id` ELSE NULL END) STORED,
    `listpickup_id` INT GENERATED ALWAYS AS (CASE WHEN `entity_type` = 'listpickup' THEN `entity_id` ELSE NULL END) STORED,
    `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`),
    INDEX `idx_logistic_event_hbl_id` (`hbl_id`),
    INDEX `idx_logistic_event_awb_id` (`awb_id`),
    INDEX `idx_logistic_event_pickup_id` (`pickup_id`),
    INDEX `idx_logistic_event_listpickup_id` (`listpickup_id`),
    CONSTRAINT `chk_logistic_event_entity_type`
        CHECK (`entity_type` IN ('awb', 'hbl', 'pickup', 'listpickup')),
    CONSTRAINT `fk_logistic_event_maincompany`
        FOREIGN KEY (`maincompany_id`) REFERENCES `maincompany` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_logistic_event_created_by`
        FOREIGN KEY (`created_by_id`) REFERENCES `user` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_logistic_event_hbl`
        FOREIGN KEY (`hbl_id`) REFERENCES `hbl` (`id`),
    CONSTRAINT `fk_logistic_event_awb`
        FOREIGN KEY (`awb_id`) REFERENCES `awb` (`id`),
    CONSTRAINT `fk_logistic_event_pickup`
        FOREIGN KEY (`pickup_id`) REFERENCES `pickup` (`id`),
    CONSTRAINT `fk_logistic_event_listpickup`
        FOREIGN KEY (`listpickup_id`) REFERENCES `listpickup` (`id`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `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`),
    CONSTRAINT `chk_tenant_workflow_rule_entity_type`
        CHECK (`entity_type` IN ('awb', 'hbl', 'pickup', 'listpickup')),
    CONSTRAINT `fk_tenant_workflow_rule_maincompany`
        FOREIGN KEY (`maincompany_id`) REFERENCES `maincompany` (`id`) ON DELETE CASCADE,
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

SET @sql = IF(
    (SELECT COUNT(*) FROM `information_schema`.`columns` WHERE `table_schema` = DATABASE() AND `table_name` = 'logistic_event' AND `column_name` = 'hbl_id') = 0,
    'ALTER TABLE `logistic_event` ADD `hbl_id` INT GENERATED ALWAYS AS (CASE WHEN `entity_type` = ''hbl'' THEN `entity_id` ELSE NULL END) STORED, ADD INDEX `idx_logistic_event_hbl_id` (`hbl_id`), ADD CONSTRAINT `fk_logistic_event_hbl` FOREIGN KEY (`hbl_id`) REFERENCES `hbl` (`id`)',
    'SELECT ''logistic_event.hbl_id already exists'''
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(
    (SELECT COUNT(*) FROM `information_schema`.`columns` WHERE `table_schema` = DATABASE() AND `table_name` = 'logistic_event' AND `column_name` = 'awb_id') = 0,
    'ALTER TABLE `logistic_event` ADD `awb_id` INT GENERATED ALWAYS AS (CASE WHEN `entity_type` = ''awb'' THEN `entity_id` ELSE NULL END) STORED, ADD INDEX `idx_logistic_event_awb_id` (`awb_id`), ADD CONSTRAINT `fk_logistic_event_awb` FOREIGN KEY (`awb_id`) REFERENCES `awb` (`id`)',
    'SELECT ''logistic_event.awb_id already exists'''
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(
    (SELECT COUNT(*) FROM `information_schema`.`columns` WHERE `table_schema` = DATABASE() AND `table_name` = 'logistic_event' AND `column_name` = 'pickup_id') = 0,
    'ALTER TABLE `logistic_event` ADD `pickup_id` INT GENERATED ALWAYS AS (CASE WHEN `entity_type` = ''pickup'' THEN `entity_id` ELSE NULL END) STORED, ADD INDEX `idx_logistic_event_pickup_id` (`pickup_id`), ADD CONSTRAINT `fk_logistic_event_pickup` FOREIGN KEY (`pickup_id`) REFERENCES `pickup` (`id`)',
    'SELECT ''logistic_event.pickup_id already exists'''
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(
    (SELECT COUNT(*) FROM `information_schema`.`columns` WHERE `table_schema` = DATABASE() AND `table_name` = 'logistic_event' AND `column_name` = 'listpickup_id') = 0,
    'ALTER TABLE `logistic_event` ADD `listpickup_id` INT GENERATED ALWAYS AS (CASE WHEN `entity_type` = ''listpickup'' THEN `entity_id` ELSE NULL END) STORED, ADD INDEX `idx_logistic_event_listpickup_id` (`listpickup_id`), ADD CONSTRAINT `fk_logistic_event_listpickup` FOREIGN KEY (`listpickup_id`) REFERENCES `listpickup` (`id`)',
    'SELECT ''logistic_event.listpickup_id already exists'''
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(
    (SELECT COUNT(*) FROM `information_schema`.`columns` WHERE `table_schema` = DATABASE() AND `table_name` = 'hbl' AND `column_name` = 'current_state') = 0,
    'ALTER TABLE `hbl` ADD `current_state` VARCHAR(64) DEFAULT NULL',
    'SELECT ''hbl.current_state already exists'''
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(
    (SELECT COUNT(*) FROM `information_schema`.`columns` WHERE `table_schema` = DATABASE() AND `table_name` = 'awb' AND `column_name` = 'current_state') = 0,
    'ALTER TABLE `awb` ADD `current_state` VARCHAR(64) DEFAULT NULL',
    'SELECT ''awb.current_state already exists'''
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Pickup already stores its workflow state in the existing `pickup`.`status` column.
-- Listpickup keeps legacy `status` and adds `current_state` for workflow (do not rename).
SET @sql = IF(
    (SELECT COUNT(*) FROM `information_schema`.`columns` WHERE `table_schema` = DATABASE() AND `table_name` = 'listpickup' AND `column_name` = 'current_state') = 0,
    'ALTER TABLE `listpickup` ADD `current_state` VARCHAR(255) DEFAULT NULL',
    'SELECT ''listpickup.current_state already exists'''
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(
    (SELECT COUNT(*) FROM `information_schema`.`columns` WHERE `table_schema` = DATABASE() AND `table_name` = 'listpickup' AND `column_name` = 'current_state') = 1
    AND (SELECT COUNT(*) FROM `information_schema`.`columns` WHERE `table_schema` = DATABASE() AND `table_name` = 'listpickup' AND `column_name` = 'status') = 1,
    'UPDATE `listpickup` SET `current_state` = CASE
        WHEN `status` IN (''Abierta'', ''ABIERTA'', ''OPEN'', ''open'') THEN ''DRAFT''
        WHEN `status` IN (''Cerrada'', ''CERRADA'', ''CLOSED'', ''closed'') THEN ''COMPLETED''
        WHEN `status` IN (''DRAFT'', ''READY'', ''IN_PROGRESS'', ''COMPLETED'', ''CANCELLED'') THEN `status`
        ELSE ''DRAFT''
    END WHERE `current_state` IS NULL',
    'SELECT ''listpickup.current_state backfill skipped'''
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(
    (SELECT COUNT(*) FROM `information_schema`.`columns` WHERE `table_schema` = DATABASE() AND `table_name` = 'listpickup' AND `column_name` = 'current_state') = 1,
    'ALTER TABLE `listpickup` MODIFY `current_state` VARCHAR(255) NOT NULL',
    'SELECT ''listpickup.current_state missing; skip NOT NULL'''
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Listpickup route workflow event definitions.
SET @now = NOW();

INSERT INTO `event_definition` (
    `maincompany_id`,
    `entity_type`,
    `code`,
    `label`,
    `description`,
    `source_type`,
    `default_visibility`,
    `affects_state`,
    `target_state`,
    `is_user_creatable`,
    `is_active`,
    `created_at`,
    `updated_at`
)
SELECT
    NULL,
    'listpickup',
    seed.`code`,
    seed.`label`,
    seed.`description`,
    'system',
    'private',
    seed.`affects_state`,
    seed.`target_state`,
    0,
    1,
    @now,
    @now
FROM (
    SELECT 'listpickup_created' AS `code`, 'Pickup route created' AS `label`, 'Creates a pickup route as draft.' AS `description`, 1 AS `affects_state`, 'DRAFT' AS `target_state`
    UNION ALL SELECT 'listpickup_pickups_added', 'Pickups added to route', 'Registers pickups added to a draft pickup route.', 0, NULL
    UNION ALL SELECT 'listpickup_pickups_removed', 'Pickups removed from route', 'Registers pickups removed from a draft pickup route.', 0, NULL
    UNION ALL SELECT 'listpickup_ready', 'Pickup route ready', 'Marks a draft pickup route as ready.', 1, 'READY'
    UNION ALL SELECT 'listpickup_started', 'Pickup route started', 'Starts route execution.', 1, 'IN_PROGRESS'
    UNION ALL SELECT 'listpickup_completed', 'Pickup route completed', 'Completes route execution.', 1, 'COMPLETED'
    UNION ALL SELECT 'listpickup_cancelled', 'Pickup route cancelled', 'Cancels the pickup route.', 1, 'CANCELLED'
) AS seed;

-- Listpickup route tenant workflow rules.
INSERT INTO `tenant_workflow_rule` (
    `maincompany_id`,
    `entity_type`,
    `event_code`,
    `is_enabled`,
    `requires_previous_event_code`,
    `allowed_from_states`,
    `requires_release`,
    `requires_pod`,
    `requires_no_customs_hold`,
    `requires_documents_complete`,
    `is_active`,
    `created_at`,
    `updated_at`
)
SELECT
    maincompany.`id`,
    'listpickup',
    seed.`event_code`,
    1,
    NULL,
    seed.`allowed_from_states`,
    0,
    0,
    0,
    0,
    1,
    @now,
    @now
FROM `maincompany`
JOIN (
    SELECT 'listpickup_created' AS `event_code`, JSON_ARRAY('DRAFT') AS `allowed_from_states`
    UNION ALL SELECT 'listpickup_pickups_added', JSON_ARRAY('DRAFT')
    UNION ALL SELECT 'listpickup_pickups_removed', JSON_ARRAY('DRAFT')
    UNION ALL SELECT 'listpickup_ready', JSON_ARRAY('DRAFT')
    UNION ALL SELECT 'listpickup_started', JSON_ARRAY('READY')
    UNION ALL SELECT 'listpickup_completed', JSON_ARRAY('IN_PROGRESS')
    UNION ALL SELECT 'listpickup_cancelled', JSON_ARRAY('DRAFT', 'READY', 'IN_PROGRESS')
) AS seed;

-- Pickup workflow event definitions.
INSERT INTO `event_definition` (
    `maincompany_id`,
    `entity_type`,
    `code`,
    `label`,
    `description`,
    `source_type`,
    `default_visibility`,
    `affects_state`,
    `target_state`,
    `is_user_creatable`,
    `is_active`,
    `created_at`,
    `updated_at`
)
SELECT
    NULL,
    'pickup',
    seed.`code`,
    seed.`label`,
    seed.`description`,
    'system',
    'private',
    1,
    seed.`target_state`,
    0,
    1,
    @now,
    @now
FROM (
    SELECT 'pickup_created' AS `code`, 'Pickup created' AS `label`, 'Creates a pickup workflow entry and sets it as pending.' AS `description`, 'PENDING' AS `target_state`
    UNION ALL SELECT 'pickup_scheduled', 'Pickup scheduled', 'Schedules a pickup.', 'SCHEDULED'
    UNION ALL SELECT 'pickup_rescheduled', 'Pickup rescheduled', 'Reschedules a pickup.', 'RESCHEDULED'
    UNION ALL SELECT 'pickup_picked_up', 'Pickup picked up', 'Marks a pickup as picked up.', 'PICKED_UP'
    UNION ALL SELECT 'pickup_in_transit', 'Pickup in transit', 'Marks a pickup as in transit.', 'IN_TRANSIT'
    UNION ALL SELECT 'pickup_delivered', 'Pickup delivered', 'Marks a pickup as delivered.', 'DELIVERED'
    UNION ALL SELECT 'pickup_rejected', 'Pickup rejected', 'Rejects a pickup.', 'REJECTED'
    UNION ALL SELECT 'pickup_processed', 'Pickup processed', 'Marks a pickup as processed.', 'PROCESSED'

) AS seed;

-- Pickup workflow tenant rules.
-- These replace hardcoded pickup status transition maps with per-company workflow rules.
INSERT INTO `tenant_workflow_rule` (
    `maincompany_id`,
    `entity_type`,
    `event_code`,
    `is_enabled`,
    `requires_previous_event_code`,
    `allowed_from_states`,
    `requires_release`,
    `requires_pod`,
    `requires_no_customs_hold`,
    `requires_documents_complete`,
    `is_active`,
    `created_at`,
    `updated_at`
)
SELECT
    maincompany.`id`,
    'pickup',
    seed.`event_code`,
    1,
    NULL,
    seed.`allowed_from_states`,
    0,
    CASE WHEN seed.`event_code` = 'pickup_picked_up' THEN 1 ELSE 0 END,
    0,
    0,
    1,
    @now,
    @now
FROM `maincompany`
JOIN (
    SELECT 'pickup_created' AS `event_code`, JSON_ARRAY('PENDING') AS `allowed_from_states`
    UNION ALL SELECT 'pickup_scheduled', JSON_ARRAY('PENDING', 'RESCHEDULED')
    UNION ALL SELECT 'pickup_rescheduled', JSON_ARRAY('PENDING', 'SCHEDULED', 'RESCHEDULED', 'IN_TRANSIT', 'REJECTED')
    UNION ALL SELECT 'pickup_picked_up', JSON_ARRAY('IN_TRANSIT', 'REJECTED')
    UNION ALL SELECT 'pickup_in_transit', JSON_ARRAY('PENDING', 'SCHEDULED', 'RESCHEDULED')
    UNION ALL SELECT 'pickup_delivered', JSON_ARRAY('IN_TRANSIT', 'PICKED_UP', 'REJECTED')
    UNION ALL SELECT 'pickup_rejected', JSON_ARRAY('IN_TRANSIT', 'PICKED_UP')
    UNION ALL SELECT 'pickup_processed', JSON_ARRAY('PICKED_UP', 'DELIVERED')
) AS seed;

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

-- ALTER TABLE `awb` DROP `current_state`;
-- ALTER TABLE `hbl` DROP `current_state`;
-- DROP TABLE `tenant_workflow_rule`;
-- DROP TABLE `logistic_event`;
-- DROP TABLE `event_definition`;