-- 03: CREATE ocr_customer_match_log (sin foreign keys; requiere 01 ejecutado)
SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS `ocr_customer_match_log` (
  `id` BIGINT AUTO_INCREMENT NOT NULL,
  `maincompany_id` INT NOT NULL,
  `matched_customer_id` INT DEFAULT NULL,
  `matched_agency_customer_id` INT DEFAULT NULL,
  `matched_carrier_customer_id` INT DEFAULT NULL,
  `matched_rule_id` BIGINT DEFAULT NULL,
  `raw_recipient_text` LONGTEXT DEFAULT NULL,
  `normalized_recipient_text` LONGTEXT DEFAULT NULL,
  `score` INT NOT NULL,
  `confidence` VARCHAR(20) NOT NULL,
  `extracted_locker` VARCHAR(50) DEFAULT NULL,
  `extracted_full_name` VARCHAR(150) DEFAULT NULL,
  `extracted_agency` VARCHAR(80) DEFAULT NULL,
  `extracted_shipping_type` VARCHAR(50) DEFAULT NULL,
  `extracted_carrier` VARCHAR(100) DEFAULT NULL,
  `status` VARCHAR(30) NOT NULL,
  `reason` LONGTEXT DEFAULT NULL,
  `used_agency_as_recipient` TINYINT(1) NOT NULL DEFAULT 0,
  `require_shipping_type` TINYINT(1) NOT NULL DEFAULT 0,
  `require_carrier_match` TINYINT(1) NOT NULL DEFAULT 0,
  `use_agency_as_shipper` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL COMMENT '(DC2Type:datetime_immutable)',
  INDEX `IDX_8D61DAD318C8CC62` (`maincompany_id`),
  INDEX `IDX_8D61DAD3598DEE3D` (`matched_customer_id`),
  INDEX `IDX_8D61DAD3295973C3` (`matched_agency_customer_id`),
  INDEX `IDX_8D61DAD373491C0A` (`matched_carrier_customer_id`),
  INDEX `IDX_8D61DAD372B5D9E2` (`matched_rule_id`),
  INDEX `idx_ocr_customer_match_log_company_created` (`maincompany_id`, `created_at`),
  INDEX `idx_ocr_customer_match_log_status_created` (`status`, `created_at`),
  PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;