-- Migration 067b: Fix combined URLs that were migrated as single records
-- Split comma/space-separated URLs into individual rows
-- Date: 2026-02-17

-- Step 1: Insert split URLs as separate rows (non-primary)
-- Handles patterns like "https://a.pl, https://b.pl" or "https://a.pl https://b.pl"
INSERT INTO company_websites (company_id, url, is_primary, source, created_at)
SELECT cw.company_id,
       trim(both ' ' from unnest(
           string_to_array(
               regexp_replace(cw.url, ',\s*', ',', 'g'),
               ','
           )
       )) as split_url,
       FALSE,
       'migration_fix',
       NOW()
FROM company_websites cw
WHERE cw.url LIKE '%,%'
  AND cw.source = 'migration';

-- Step 2: Delete the original combined rows
DELETE FROM company_websites
WHERE url LIKE '%,%'
  AND source = 'migration';

-- Step 3: Mark the first URL per company as primary (if none is primary)
UPDATE company_websites cw
SET is_primary = TRUE
WHERE cw.id = (
    SELECT min(cw2.id)
    FROM company_websites cw2
    WHERE cw2.company_id = cw.company_id
)
AND NOT EXISTS (
    SELECT 1 FROM company_websites cw3
    WHERE cw3.company_id = cw.company_id AND cw3.is_primary = TRUE
);

-- Step 4: Sync companies.website with the primary URL
UPDATE companies c
SET website = (
    SELECT cw.url
    FROM company_websites cw
    WHERE cw.company_id = c.id AND cw.is_primary = TRUE
    LIMIT 1
)
WHERE EXISTS (
    SELECT 1 FROM company_websites cw
    WHERE cw.company_id = c.id AND cw.is_primary = TRUE
);