-- Listpickup route workflow lifecycle events and tenant rules.
-- Seeds only the current route lifecycle: DRAFT, READY, IN_PROGRESS, COMPLETED, CANCELLED.

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 `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 @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;

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;