-- Pickup Workflow Core Bootstrap (idempotent)
-- Purpose:
-- 1) Create/repair core workflow tables required by Pickup workflow.
-- 2) Normalize pickup state to current_state with English canonical values.
-- 3) Seed/refresh event_definition and tenant_workflow_rule for pickup.
--
-- Safe to run multiple times.
-- MySQL 8+ recommended.

SET @schema_name := DATABASE();
SET @now := NOW();

-- =========================================================
-- 0) Core tables (create if missing)
-- =========================================================

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,
  `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 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`),
  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`),
  PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

-- Ensure a strict unique key for tenant rules (required for deterministic upserts).
SET @has_tenant_unique := (
  SELECT COUNT(*)
  FROM information_schema.statistics
  WHERE table_schema = @schema_name
    AND table_name = 'tenant_workflow_rule'
    AND index_name = 'uniq_tenant_workflow_rule_maincompany_entity_event'
);
SET @sql := IF(
  @has_tenant_unique = 0,
  'ALTER TABLE `tenant_workflow_rule` ADD UNIQUE INDEX `uniq_tenant_workflow_rule_maincompany_entity_event` (`maincompany_id`, `entity_type`, `event_code`)',
  'SELECT "tenant_workflow_rule unique index already exists"'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- =========================================================
-- 1) Pickup state source normalization: status -> current_state
-- =========================================================

SET @has_pickup_table := (
  SELECT COUNT(*)
  FROM information_schema.tables
  WHERE table_schema = @schema_name
    AND table_name = 'pickup'
);

-- Add current_state column if missing.
SET @has_pickup_current_state := (
  SELECT COUNT(*)
  FROM information_schema.columns
  WHERE table_schema = @schema_name
    AND table_name = 'pickup'
    AND column_name = 'current_state'
);
SET @sql := IF(
  @has_pickup_table = 1 AND @has_pickup_current_state = 0,
  'ALTER TABLE `pickup` ADD COLUMN `current_state` VARCHAR(64) DEFAULT NULL',
  'SELECT "pickup.current_state already exists or pickup table missing"'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Copy from legacy status if status column exists.
SET @has_pickup_status := (
  SELECT COUNT(*)
  FROM information_schema.columns
  WHERE table_schema = @schema_name
    AND table_name = 'pickup'
    AND column_name = 'status'
);
SET @sql := IF(
  @has_pickup_table = 1 AND @has_pickup_status = 1,
  'UPDATE `pickup` SET `current_state` = CASE
      WHEN `status` = ''PENDIENTE'' THEN ''PENDING''
      WHEN `status` = ''PROGRAMADO'' THEN ''SCHEDULED''
      WHEN `status` = ''REPROGRAMADO'' THEN ''RESCHEDULED''
      WHEN `status` = ''RECOGIDO'' THEN ''PICKED_UP''
      WHEN `status` = ''EN_TRANSITO'' THEN ''IN_TRANSIT''
      WHEN `status` = ''RETARDADO'' THEN ''DELAYED''
      WHEN `status` = ''ENTREGADO'' THEN ''DELIVERED''
      WHEN `status` = ''ANULADO'' THEN ''REJECTED''
      WHEN `status` = ''PROCESADO'' THEN ''PROCESSED''
      ELSE `current_state`
    END
    WHERE `current_state` IS NULL AND `status` IS NOT NULL',
  'SELECT "pickup.status not found or pickup table missing; skipping legacy copy"'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Normalize any legacy values directly present in current_state.
SET @sql := IF(
  @has_pickup_table = 1,
  'UPDATE `pickup` SET `current_state` = CASE
      WHEN `current_state` = ''PENDIENTE'' THEN ''PENDING''
      WHEN `current_state` = ''PROGRAMADO'' THEN ''SCHEDULED''
      WHEN `current_state` = ''REPROGRAMADO'' THEN ''RESCHEDULED''
      WHEN `current_state` = ''RECOGIDO'' THEN ''PICKED_UP''
      WHEN `current_state` = ''EN_TRANSITO'' THEN ''IN_TRANSIT''
      WHEN `current_state` = ''RETARDADO'' THEN ''DELAYED''
      WHEN `current_state` = ''ENTREGADO'' THEN ''DELIVERED''
      WHEN `current_state` = ''ANULADO'' THEN ''REJECTED''
      WHEN `current_state` = ''PROCESADO'' THEN ''PROCESSED''
      ELSE `current_state`
    END',
  'SELECT "pickup table missing; skipping current_state normalization"'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Default pending when still null.
SET @sql := IF(
  @has_pickup_table = 1,
  'UPDATE `pickup` SET `current_state` = ''PENDING'' WHERE `current_state` IS NULL',
  'SELECT "pickup table missing; skipping default state fill"'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Consistency rule: legacy pickups already attached to a route must start as scheduled.
SET @has_pickup_listpickup_fk := (
  SELECT COUNT(*)
  FROM information_schema.columns
  WHERE table_schema = @schema_name
    AND table_name = 'pickup'
    AND column_name = 'listpickup_id'
);
SET @sql := IF(
  @has_pickup_table = 1 AND @has_pickup_listpickup_fk = 1,
  'UPDATE `pickup` SET `current_state` = ''SCHEDULED'' WHERE `current_state` = ''PENDING'' AND `listpickup_id` IS NOT NULL',
  'SELECT "pickup.listpickup_id missing or pickup missing; skipping route-scheduled normalization"'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Ensure index exists.
SET @has_pickup_current_state_idx := (
  SELECT COUNT(*)
  FROM information_schema.statistics
  WHERE table_schema = @schema_name
    AND table_name = 'pickup'
    AND index_name = 'IDX_PICKUP_CURRENT_STATE'
);
SET @sql := IF(
  @has_pickup_table = 1 AND @has_pickup_current_state_idx = 0,
  'ALTER TABLE `pickup` ADD INDEX `IDX_PICKUP_CURRENT_STATE` (`current_state`)',
  'SELECT "IDX_PICKUP_CURRENT_STATE already exists or pickup missing"'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Make current_state NOT NULL after backfill.
SET @sql := IF(
  @has_pickup_table = 1,
  'ALTER TABLE `pickup` MODIFY COLUMN `current_state` VARCHAR(64) NOT NULL',
  'SELECT "pickup missing; skipping NOT NULL change"'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- =========================================================
-- 2) Rebuild pickup workflow data (clean + insert)
-- =========================================================

DROP TEMPORARY TABLE IF EXISTS `tmp_pickup_event_seed`;
CREATE TEMPORARY TABLE `tmp_pickup_event_seed` (
  `code` VARCHAR(100) NOT NULL,
  `label` VARCHAR(255) NOT NULL,
  `description` LONGTEXT NULL,
  `target_state` VARCHAR(64) NULL,
  PRIMARY KEY (`code`)
) ENGINE = InnoDB;

INSERT INTO `tmp_pickup_event_seed` (`code`, `label`, `description`, `target_state`) VALUES
('pickup_created', 'Pickup created', 'Creates a pickup workflow entry and sets it as pending.', 'PENDING'),
('pickup_scheduled', 'Pickup scheduled', 'Schedules a pickup.', 'SCHEDULED'),
('pickup_rescheduled', 'Pickup rescheduled', 'Reschedules a pickup.', 'RESCHEDULED'),
('pickup_picked_up', 'Pickup picked up', 'Marks a pickup as picked up.', 'PICKED_UP'),
('pickup_in_transit', 'Pickup in transit', 'Marks a pickup as in transit.', 'IN_TRANSIT'),
('pickup_delivered', 'Pickup delivered', 'Marks a pickup as delivered.', 'DELIVERED'),
('pickup_rejected', 'Pickup rejected', 'Rejects a pickup.', 'REJECTED'),
('pickup_processed', 'Pickup processed', 'Marks a pickup as processed.', 'PROCESSED');

-- Clean pickup event definitions (global and company-specific) and recreate deterministically.
DELETE FROM `event_definition`
WHERE `entity_type` = 'pickup';

-- Insert canonical global 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',
  s.`code`,
  s.`label`,
  s.`description`,
  'system',
  'private',
  1,
  s.`target_state`,
  0,
  1,
  @now,
  @now
FROM `tmp_pickup_event_seed` s;

-- =========================================================
-- 3) Seed/refresh tenant pickup workflow rules (table-driven transitions)
-- =========================================================

DROP TEMPORARY TABLE IF EXISTS `tmp_pickup_rule_seed`;
CREATE TEMPORARY TABLE `tmp_pickup_rule_seed` (
  `event_code` VARCHAR(100) NOT NULL,
  `allowed_from_states` JSON NULL,
  PRIMARY KEY (`event_code`)
) ENGINE = InnoDB;

INSERT INTO `tmp_pickup_rule_seed` (`event_code`, `allowed_from_states`) VALUES
('pickup_created', JSON_ARRAY('PENDING')),
('pickup_scheduled', JSON_ARRAY('PENDING', 'RESCHEDULED')),
('pickup_rescheduled', JSON_ARRAY('PENDING', 'SCHEDULED', 'RESCHEDULED', 'IN_TRANSIT', 'REJECTED')),
('pickup_picked_up', JSON_ARRAY('IN_TRANSIT', 'REJECTED')),
('pickup_in_transit', JSON_ARRAY('PENDING', 'SCHEDULED', 'RESCHEDULED')),
('pickup_delivered', JSON_ARRAY('IN_TRANSIT', 'PICKED_UP', 'REJECTED')),
('pickup_rejected', JSON_ARRAY('IN_TRANSIT', 'PICKED_UP')),
('pickup_processed', JSON_ARRAY('PICKED_UP', 'DELIVERED'));

-- Clean pickup tenant rules and recreate deterministic data for every company.
DELETE FROM `tenant_workflow_rule`
WHERE `entity_type` = 'pickup';

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
  mc.`id`,
  'pickup',
  s.`event_code`,
  1,
  NULL,
  s.`allowed_from_states`,
  0,
  CASE WHEN s.`event_code` = 'pickup_picked_up' THEN 1 ELSE 0 END,
  0,
  0,
  1,
  @now,
  @now
FROM `maincompany` mc
JOIN `tmp_pickup_rule_seed` s;

-- =========================================================
-- 4) Rebuild listpickup workflow data (clean + insert)
-- =========================================================

DROP TEMPORARY TABLE IF EXISTS `tmp_listpickup_event_seed`;
CREATE TEMPORARY TABLE `tmp_listpickup_event_seed` (
  `code` VARCHAR(100) NOT NULL,
  `label` VARCHAR(255) NOT NULL,
  `description` LONGTEXT NULL,
  `affects_state` TINYINT(1) NOT NULL,
  `target_state` VARCHAR(64) NULL,
  PRIMARY KEY (`code`)
) ENGINE = InnoDB;

INSERT INTO `tmp_listpickup_event_seed` (`code`, `label`, `description`, `affects_state`, `target_state`) VALUES
('listpickup_created', 'Pickup route created', 'Creates a pickup route as draft.', 1, 'DRAFT'),
('listpickup_pickups_added', 'Pickups added to route', 'Registers pickups added to a draft pickup route.', 0, NULL),
('listpickup_pickups_removed', 'Pickups removed from route', 'Registers pickups removed from a draft pickup route.', 0, NULL),
('listpickup_ready', 'Pickup route ready', 'Marks a draft pickup route as ready.', 1, 'READY'),
('listpickup_started', 'Pickup route started', 'Starts route execution.', 1, 'IN_PROGRESS'),
('listpickup_completed', 'Pickup route completed', 'Completes route execution.', 1, 'COMPLETED'),
('listpickup_cancelled', 'Pickup route cancelled', 'Cancels the pickup route.', 1, 'CANCELLED');

DELETE FROM `event_definition`
WHERE `entity_type` = 'listpickup';

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',
  s.`code`,
  s.`label`,
  s.`description`,
  'system',
  'private',
  s.`affects_state`,
  s.`target_state`,
  0,
  1,
  @now,
  @now
FROM `tmp_listpickup_event_seed` s;

DROP TEMPORARY TABLE IF EXISTS `tmp_listpickup_rule_seed`;
CREATE TEMPORARY TABLE `tmp_listpickup_rule_seed` (
  `event_code` VARCHAR(100) NOT NULL,
  `allowed_from_states` JSON NULL,
  PRIMARY KEY (`event_code`)
) ENGINE = InnoDB;

INSERT INTO `tmp_listpickup_rule_seed` (`event_code`, `allowed_from_states`) VALUES
('listpickup_created', JSON_ARRAY('DRAFT')),
('listpickup_pickups_added', JSON_ARRAY('DRAFT')),
('listpickup_pickups_removed', JSON_ARRAY('DRAFT')),
('listpickup_ready', JSON_ARRAY('DRAFT')),
('listpickup_started', JSON_ARRAY('READY')),
('listpickup_completed', JSON_ARRAY('IN_PROGRESS')),
('listpickup_cancelled', JSON_ARRAY('DRAFT', 'READY', 'IN_PROGRESS'));

DELETE FROM `tenant_workflow_rule`
WHERE `entity_type` = 'listpickup';

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
  mc.`id`,
  'listpickup',
  s.`event_code`,
  1,
  NULL,
  s.`allowed_from_states`,
  0,
  0,
  0,
  0,
  1,
  @now,
  @now
FROM `maincompany` mc
JOIN `tmp_listpickup_rule_seed` s;

DROP TEMPORARY TABLE IF EXISTS `tmp_pickup_event_seed`;
DROP TEMPORARY TABLE IF EXISTS `tmp_pickup_rule_seed`;
DROP TEMPORARY TABLE IF EXISTS `tmp_listpickup_event_seed`;
DROP TEMPORARY TABLE IF EXISTS `tmp_listpickup_rule_seed`;

-- End of bootstrap.