-- Migration 185: Prevent duplicate active location-contact relations
-- Date: 2026-05-05
-- Purpose: Ensure one active relation per (location_id, related_contact_id)

-- Soft-delete duplicate active rows, keep best candidate (primary first, then oldest)
WITH ranked AS (
    SELECT
        id,
        ROW_NUMBER() OVER (
            PARTITION BY location_id, related_contact_id
            ORDER BY is_primary DESC, created_at ASC, id ASC
        ) AS rn
    FROM locations_contacts
    WHERE deleted_at IS NULL
      AND related_contact_id IS NOT NULL
)
UPDATE locations_contacts lc
SET deleted_at = NOW()
FROM ranked r
WHERE lc.id = r.id
  AND r.rn > 1;

-- Enforce uniqueness for active linked contacts
CREATE UNIQUE INDEX IF NOT EXISTS uq_locations_contacts_location_related_active
ON locations_contacts(location_id, related_contact_id)
WHERE deleted_at IS NULL AND related_contact_id IS NOT NULL;