-- Preserve one artifact row per immutable storage object identity.
-- PostgreSQL 15+. Migrations 008-010 remain immutable.

BEGIN;

DO $$
BEGIN
    IF current_database() <> 'booking_test' THEN
        RAISE EXCEPTION
            'ARR artifact identity migration is allowed only in booking_test';
    END IF;
    IF to_regclass('ingestion.artifacts') IS NULL
       OR to_regclass('ingestion.result_submissions') IS NULL THEN
        RAISE EXCEPTION
            'ARR migrations 008 through 010 must be applied first';
    END IF;
    IF NOT EXISTS (
        SELECT 1
        FROM pg_constraint
        WHERE conrelid = 'ingestion.artifacts'::regclass
          AND conname = 'artifacts_kind_hash_unique'
    ) THEN
        RAISE EXCEPTION
            'ARR artifact identity migration is already applied or the source schema is invalid';
    END IF;
    IF to_regclass('ingestion.artifacts_kind_hash_idx') IS NOT NULL THEN
        RAISE EXCEPTION
            'ARR artifact identity lookup index already exists';
    END IF;
END;
$$;

ALTER TABLE ingestion.artifacts
    DROP CONSTRAINT artifacts_kind_hash_unique;

CREATE INDEX artifacts_kind_hash_idx
    ON ingestion.artifacts (artifact_kind, sha256);

COMMENT ON INDEX ingestion.artifacts_kind_hash_idx IS
  'Non-unique content lookup. Equal bytes may belong to different immutable OSS object identities and processing runs.';

COMMIT;