orderPRO/database/migrations/20260308_000037_unify_integrations_base_links.sql

CREATE TABLE IF NOT EXISTS integrations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    type VARCHAR(32) NOT NULL,
    name VARCHAR(128) NOT NULL,
    base_url VARCHAR(255) NOT NULL,
    api_key_encrypted TEXT NULL,
    timeout_seconds SMALLINT UNSIGNED NOT NULL DEFAULT 10,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    last_test_status VARCHAR(16) NULL,
    last_test_http_code SMALLINT UNSIGNED NULL,
    last_test_message VARCHAR(255) NULL,
    last_test_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY integrations_type_name_unique (type, name),
    KEY integrations_type_active_idx (type, is_active),
    KEY integrations_last_test_at_idx (last_test_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS integration_test_logs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    integration_id INT UNSIGNED NOT NULL,
    status VARCHAR(16) NOT NULL,
    http_code SMALLINT UNSIGNED NULL,
    message VARCHAR(255) NOT NULL,
    endpoint_url VARCHAR(255) NULL,
    tested_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY integration_test_logs_integration_idx (integration_id),
    KEY integration_test_logs_tested_at_idx (tested_at),
    CONSTRAINT integration_test_logs_integration_fk
        FOREIGN KEY (integration_id) REFERENCES integrations(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `allegro_integration_settings`
    ADD COLUMN IF NOT EXISTS `integration_id` INT UNSIGNED NULL AFTER `id`;

ALTER TABLE `apaczka_integration_settings`
    ADD COLUMN IF NOT EXISTS `integration_id` INT UNSIGNED NULL AFTER `id`;

ALTER TABLE `inpost_integration_settings`
    ADD COLUMN IF NOT EXISTS `integration_id` INT UNSIGNED NULL AFTER `id`;

INSERT INTO `integrations` (`type`, `name`, `base_url`, `timeout_seconds`, `is_active`, `created_at`, `updated_at`)
VALUES
    ('allegro', 'Allegro Sandbox', 'https://api.allegro.pl.allegrosandbox.pl', 20, 1, NOW(), NOW()),
    ('allegro', 'Allegro Production', 'https://api.allegro.pl', 20, 1, NOW(), NOW()),
    ('apaczka', 'Apaczka', 'https://www.apaczka.pl', 20, 1, NOW(), NOW()),
    ('inpost', 'InPost ShipX', 'https://api-shipx-pl.easypack24.net', 20, 1, NOW(), NOW())
ON DUPLICATE KEY UPDATE
    `updated_at` = VALUES(`updated_at`);

UPDATE `allegro_integration_settings` `s`
INNER JOIN `integrations` `i`
    ON `i`.`type` = 'allegro'
   AND `i`.`name` = CASE
        WHEN `s`.`environment` = 'production' THEN 'Allegro Production'
        ELSE 'Allegro Sandbox'
    END
SET `s`.`integration_id` = `i`.`id`
WHERE `s`.`integration_id` IS NULL OR `s`.`integration_id` = 0;

UPDATE `apaczka_integration_settings` `s`
INNER JOIN `integrations` `i`
    ON `i`.`type` = 'apaczka'
   AND `i`.`name` = 'Apaczka'
SET `s`.`integration_id` = `i`.`id`
WHERE `s`.`id` = 1
  AND (`s`.`integration_id` IS NULL OR `s`.`integration_id` = 0);

UPDATE `inpost_integration_settings` `s`
INNER JOIN `integrations` `i`
    ON `i`.`type` = 'inpost'
   AND `i`.`name` = 'InPost ShipX'
SET `s`.`integration_id` = `i`.`id`
WHERE `s`.`id` = 1
  AND (`s`.`integration_id` IS NULL OR `s`.`integration_id` = 0);

UPDATE `integrations` `i`
INNER JOIN `apaczka_integration_settings` `s`
    ON `s`.`integration_id` = `i`.`id`
SET `i`.`api_key_encrypted` = `s`.`api_key_encrypted`,
    `i`.`updated_at` = NOW()
WHERE `i`.`type` = 'apaczka'
  AND (`i`.`api_key_encrypted` IS NULL OR `i`.`api_key_encrypted` = '')
  AND `s`.`api_key_encrypted` IS NOT NULL
  AND `s`.`api_key_encrypted` <> '';

UPDATE `integrations` `i`
INNER JOIN `inpost_integration_settings` `s`
    ON `s`.`integration_id` = `i`.`id`
SET `i`.`api_key_encrypted` = `s`.`api_token_encrypted`,
    `i`.`updated_at` = NOW()
WHERE `i`.`type` = 'inpost'
  AND (`i`.`api_key_encrypted` IS NULL OR `i`.`api_key_encrypted` = '')
  AND `s`.`api_token_encrypted` IS NOT NULL
  AND `s`.`api_token_encrypted` <> '';

SET @sql := (
    SELECT IF(COUNT(*) = 0,
        'ALTER TABLE `allegro_integration_settings` ADD UNIQUE KEY `allegro_integration_settings_integration_unique` (`integration_id`)',
        'SELECT 1')
    FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'allegro_integration_settings'
      AND index_name = 'allegro_integration_settings_integration_unique'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := (
    SELECT IF(COUNT(*) = 0,
        'ALTER TABLE `apaczka_integration_settings` ADD UNIQUE KEY `apaczka_integration_settings_integration_unique` (`integration_id`)',
        'SELECT 1')
    FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'apaczka_integration_settings'
      AND index_name = 'apaczka_integration_settings_integration_unique'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := (
    SELECT IF(COUNT(*) = 0,
        'ALTER TABLE `inpost_integration_settings` ADD UNIQUE KEY `inpost_integration_settings_integration_unique` (`integration_id`)',
        'SELECT 1')
    FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'inpost_integration_settings'
      AND index_name = 'inpost_integration_settings_integration_unique'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := (
    SELECT IF(COUNT(*) = 0,
        'ALTER TABLE `allegro_integration_settings` ADD CONSTRAINT `allegro_integration_settings_integration_fk` FOREIGN KEY (`integration_id`) REFERENCES `integrations`(`id`) ON DELETE CASCADE ON UPDATE CASCADE',
        'SELECT 1')
    FROM information_schema.table_constraints
    WHERE constraint_schema = DATABASE()
      AND table_name = 'allegro_integration_settings'
      AND constraint_name = 'allegro_integration_settings_integration_fk'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := (
    SELECT IF(COUNT(*) = 0,
        'ALTER TABLE `apaczka_integration_settings` ADD CONSTRAINT `apaczka_integration_settings_integration_fk` FOREIGN KEY (`integration_id`) REFERENCES `integrations`(`id`) ON DELETE CASCADE ON UPDATE CASCADE',
        'SELECT 1')
    FROM information_schema.table_constraints
    WHERE constraint_schema = DATABASE()
      AND table_name = 'apaczka_integration_settings'
      AND constraint_name = 'apaczka_integration_settings_integration_fk'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := (
    SELECT IF(COUNT(*) = 0,
        'ALTER TABLE `inpost_integration_settings` ADD CONSTRAINT `inpost_integration_settings_integration_fk` FOREIGN KEY (`integration_id`) REFERENCES `integrations`(`id`) ON DELETE CASCADE ON UPDATE CASCADE',
        'SELECT 1')
    FROM information_schema.table_constraints
    WHERE constraint_schema = DATABASE()
      AND table_name = 'inpost_integration_settings'
      AND constraint_name = 'inpost_integration_settings_integration_fk'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;