-- Migration: Add ABN uniqueness constraint to companies table
-- Created: 2026-02-08
-- Description: Ensures ABN values are unique across all companies
-- 
-- IMPORTANT: This migration requires manual intervention if duplicates exist.
-- The migration will FAIL if duplicate ABN values exist in the database.
-- 
-- BEFORE RUNNING:
-- 1. Execute the verification queries to check for duplicates
-- 2. If duplicates exist, resolve them (nullify or update manually)
-- 3. Then run this migration
--
-- AFTER RUNNING:
-- 1. Verify the constraint is working with the post-migration queries

-- ============================================
-- VERIFICATION QUERIES (Run these before migration)
-- ============================================

-- Check for existing duplicate ABN values
-- If duplicates exist, resolve them before running this migration
/*
SELECT abn, COUNT(*) as duplicate_count, array_agg(id) as company_ids
FROM companies 
WHERE abn IS NOT NULL 
GROUP BY abn 
HAVING COUNT(*) > 1;
*/

-- Check total companies with ABN values
/*
SELECT COUNT(*) as total_companies_with_abn
FROM companies 
WHERE abn IS NOT NULL;
*/

-- ============================================
-- UP MIGRATION
-- ============================================

-- Step 1: Handle existing duplicate ABN values
-- Option A: Nullify duplicate ABNs (safer than deletion)
-- This preserves all companies but removes ABN from duplicates
/*
UPDATE companies 
SET abn = NULL, updated_at = now()
WHERE id IN (
  SELECT id FROM (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY abn ORDER BY updated_at DESC) as rn
    FROM companies 
    WHERE abn IS NOT NULL
  ) ranked
  WHERE rn > 1
);
*/

-- Option B: Manually review and handle duplicates
-- Use the verification query above to identify duplicates
-- Then manually update them through the app or direct SQL

-- Step 2: Add unique constraint to ABN column
-- This will fail if duplicates exist - ensure Step 1 is completed first
ALTER TABLE companies 
ADD CONSTRAINT companies_abn_unique UNIQUE (abn);

-- Step 3: Create index on ABN for better query performance
-- This index already exists in the schema but we'll ensure it exists
CREATE INDEX IF NOT EXISTS idx_companies_abn ON companies (abn);

-- ============================================
-- DOWN MIGRATION
-- ============================================

-- To rollback this migration, run the following:

-- Remove the unique constraint
-- ALTER TABLE companies 
-- DROP CONSTRAINT IF EXISTS companies_abn_unique;

-- Note: The index idx_companies_abn is kept for performance even after rollback
-- To also remove the index (not recommended):
-- DROP INDEX IF EXISTS idx_companies_abn;

-- ============================================
-- POST-MIGRATION VERIFICATION
-- ============================================

-- Verify the constraint is working (should return 0)
/*
SELECT COUNT(*) as constraint_violations
FROM (
  SELECT abn, COUNT(*) as count
  FROM companies 
  WHERE abn IS NOT NULL 
  GROUP BY abn 
  HAVING COUNT(*) > 1
) as duplicates;
*/

-- Test inserting a duplicate ABN (should fail)
/*
-- This should fail with unique constraint violation
INSERT INTO companies (profile_id, name, abn) 
VALUES ('test-profile-id', 'Test Company', '12345678901');
*/

-- List all unique ABNs
/*
SELECT DISTINCT abn 
FROM companies 
WHERE abn IS NOT NULL 
ORDER BY abn;
*/