-- NGBVD API Clients Outbound Configuration Patch
-- Run this only if you prefer SQL instead of Laravel migration.

ALTER TABLE `api_clients`
  ADD COLUMN `integration_code` VARCHAR(60) NULL AFTER `system_name`,
  ADD COLUMN `outbound_enabled` TINYINT(1) NOT NULL DEFAULT 0 AFTER `is_active`,
  ADD COLUMN `outbound_base_url` VARCHAR(500) NULL AFTER `outbound_enabled`,
  ADD COLUMN `outbound_push_endpoint` VARCHAR(255) NULL AFTER `outbound_base_url`,
  ADD COLUMN `outbound_timeout` SMALLINT UNSIGNED NOT NULL DEFAULT 30 AFTER `outbound_push_endpoint`,
  ADD COLUMN `outbound_verify_ssl` TINYINT(1) NOT NULL DEFAULT 1 AFTER `outbound_timeout`,
  ADD COLUMN `outbound_api_version` VARCHAR(20) NULL AFTER `outbound_verify_ssl`,
  ADD COLUMN `outbound_notes` TEXT NULL AFTER `outbound_api_version`;

UPDATE `api_clients`
SET `integration_code` = CASE
    WHEN LOWER(CONCAT(IFNULL(`system_name`, ''), ' ', IFNULL(`allowed_scopes`, ''))) LIKE '%ubos%' THEN 'UBOS_GBV_PORTAL'
    WHEN LOWER(CONCAT(IFNULL(`system_name`, ''), ' ', IFNULL(`allowed_scopes`, ''))) LIKE '%nsr%' THEN 'NSR'
    WHEN LOWER(CONCAT(IFNULL(`system_name`, ''), ' ', IFNULL(`allowed_scopes`, ''))) LIKE '%sauti%' THEN 'SAUTI'
    ELSE 'PARTNER_SYSTEM'
END
WHERE `integration_code` IS NULL;

UPDATE `api_clients`
SET `outbound_push_endpoint` = '/api/v2/incidents',
    `outbound_timeout` = 30,
    `outbound_verify_ssl` = 1,
    `outbound_api_version` = 'v2'
WHERE `outbound_push_endpoint` IS NULL;
