-- Pickup workflow lifecycle events and tenant rules.
-- Seeds only the current pickup lifecycle into empty workflow tables.

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,
  '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;

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;