-- ============================================================
-- No-delivery reasons catalog
-- scripts/Version20261015120000_nodeliveryreason_up.sql
--
-- Equivalente a: migrations/Version20261015120000.php
-- Catálogo de motivos de no entrega por compañía.
-- ============================================================

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS `nodeliveryreason` (
    `id` INT AUTO_INCREMENT NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `maincompany_id` INT NOT NULL,
    `active` TINYINT(1) NOT NULL,
    `creationdate` DATETIME NOT NULL,
    INDEX `IDX_NODELIVERYREASON_MAINCOMPANY` (`maincompany_id`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

SET @db_name := DATABASE();

SET @fk_exists := (
  SELECT COUNT(*)
  FROM information_schema.TABLE_CONSTRAINTS
  WHERE TABLE_SCHEMA = @db_name
    AND TABLE_NAME = 'nodeliveryreason'
    AND CONSTRAINT_NAME = 'FK_NODELIVERYREASON_MAINCOMPANY'
    AND CONSTRAINT_TYPE = 'FOREIGN KEY'
);

SET @sql := IF(
  @fk_exists = 0,
  'ALTER TABLE `nodeliveryreason` ADD CONSTRAINT `FK_NODELIVERYREASON_MAINCOMPANY` FOREIGN KEY (`maincompany_id`) REFERENCES `maincompany` (`id`)',
  'SELECT ''Constraint FK_NODELIVERYREASON_MAINCOMPANY already exists'' AS info'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;