START TRANSACTION;

CREATE TABLE IF NOT EXISTS bank_code_aliases (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    bank_id INT UNSIGNED NOT NULL,
    alias_code VARCHAR(20) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_bank_code_alias_bank FOREIGN KEY (bank_id) REFERENCES bank_catalog (id) ON DELETE CASCADE,
    UNIQUE KEY uq_bank_alias_code (alias_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Preserve Finanto entry but release the numeric code so it can be used by Banco CBSS
SET @finanto_entry_id := (
    SELECT id
    FROM bank_catalog
    WHERE code = '954' AND (name LIKE '%Finanto%' OR name LIKE '%FINANTO%')
    LIMIT 1
);

UPDATE bank_catalog
SET code = 'FINANTO', name = 'Finanto Hub'
WHERE id = @finanto_entry_id;

-- Ensure Banco CBSS S/A exists with the numeric code 954
INSERT INTO bank_catalog (code, name, is_active, created_at)
SELECT '954', 'Banco CBSS S/A', 1, NOW()
WHERE NOT EXISTS (
    SELECT 1 FROM bank_catalog WHERE code = '954'
);

SET @cbss_entry_id := (
    SELECT id FROM bank_catalog WHERE code = '954' LIMIT 1
);

-- Add aliases so future imports can resolve both entries even if names vary
INSERT INTO bank_code_aliases (bank_id, alias_code)
SELECT @cbss_entry_id, 'CBSS'
WHERE @cbss_entry_id IS NOT NULL
  AND NOT EXISTS (
      SELECT 1 FROM bank_code_aliases WHERE alias_code = 'CBSS'
  );

INSERT INTO bank_code_aliases (bank_id, alias_code)
SELECT @finanto_entry_id, 'FINANTO'
WHERE @finanto_entry_id IS NOT NULL
  AND NOT EXISTS (
      SELECT 1 FROM bank_code_aliases WHERE alias_code = 'FINANTO'
  );

-- Re-link existing contracts that should point to Banco CBSS
UPDATE lead_contract_data
SET original_bank_id = @cbss_entry_id
WHERE @cbss_entry_id IS NOT NULL
  AND original_bank_id = @finanto_entry_id
  AND JSON_UNQUOTE(JSON_EXTRACT(raw_payload, '$."__normalized"."bank_code"')) = '954'
  AND JSON_UNQUOTE(JSON_EXTRACT(raw_payload, '$."__normalized"."bank_name"')) LIKE '%CBSS%';

COMMIT;