-- Migration: Add unique constraint on companies.abn
-- Created: 2026-02-09

-- Step 1: Check for existing duplicate ABNs
-- This query will show all companies that have duplicate ABN values
-- (excluding NULL and empty string ABNs)
SELECT 
    abn,
    COUNT(*) as duplicate_count,
    array_agg(
        json_build_object(
            'id', id,
            'name', name,
            'profile_id', profile_id
        )
    ) as companies
FROM companies
WHERE abn IS NOT NULL 
  AND abn != ''
GROUP BY abn
HAVING COUNT(*) > 1
ORDER BY duplicate_count DESC, abn;

-- Step 2: Migration to add UNIQUE constraint
-- Note: This migration uses a partial unique index to exclude NULL and empty ABNs
BEGIN;

-- Drop existing constraint/index if present (from prior migration 20260208000001)
ALTER TABLE companies DROP CONSTRAINT IF EXISTS companies_abn_unique;
DROP INDEX IF EXISTS companies_abn_unique;

-- Create a partial unique index on ABN
-- This allows multiple NULL values and multiple empty strings, but enforces uniqueness on actual ABN values
CREATE UNIQUE INDEX companies_abn_unique
ON companies (abn)
WHERE abn IS NOT NULL AND abn != '';

-- Add a comment to document the constraint
COMMENT ON INDEX companies_abn_unique IS 'Ensures ABN values are unique across companies, excluding NULL and empty values';

COMMIT;

-- Notes on handling duplicates:
-- If the duplicate check query returns results, you'll need to resolve these before running the migration.
-- Options for resolving duplicates:
-- 1. Update duplicate ABNs to NULL or empty string for companies that don't need an ABN
-- 2. Update ABNs to correct unique values where data entry errors occurred
-- 3. Merge duplicate company records if they represent the same business
-- 4. Add a suffix to make ABNs unique temporarily (e.g., '12345678901_OLD')
--
-- Example resolution queries:
-- UPDATE companies SET abn = NULL WHERE id = <duplicate_id>;
-- UPDATE companies SET abn = <correct_abn> WHERE id = <company_id>;