-- Migration 078: Batch Dedup — Schema extensions for 85k→~18-25k reduction
-- Adds merged_into_uuid tracking, performance indexes for batch dedup,
-- and extends link_type CHECK to include 'cross_regulation'.

BEGIN;

-- =============================================================================
-- 1. merged_into_uuid: Track which master a duplicate was merged into
-- =============================================================================

ALTER TABLE canonical_controls
    ADD COLUMN IF NOT EXISTS merged_into_uuid UUID REFERENCES canonical_controls(id);

CREATE INDEX IF NOT EXISTS idx_cc_merged_into
    ON canonical_controls(merged_into_uuid) WHERE merged_into_uuid IS NOT NULL;

-- =============================================================================
-- 2. Performance indexes for batch dedup queries
-- =============================================================================

-- Index on merge_group_hint inside generation_metadata (for sub-grouping)
CREATE INDEX IF NOT EXISTS idx_cc_merge_group_hint
    ON canonical_controls ((generation_metadata->>'merge_group_hint'))
    WHERE decomposition_method = 'pass0b';

-- Composite index for pattern-based dedup loading
CREATE INDEX IF NOT EXISTS idx_cc_pattern_dedup
    ON canonical_controls (pattern_id, release_state)
    WHERE decomposition_method = 'pass0b';

-- =============================================================================
-- 3. Extend link_type CHECK to include 'cross_regulation'
-- =============================================================================

ALTER TABLE control_parent_links
    DROP CONSTRAINT IF EXISTS control_parent_links_link_type_check;

ALTER TABLE control_parent_links
    ADD CONSTRAINT control_parent_links_link_type_check
        CHECK (link_type IN ('decomposition', 'dedup_merge', 'manual', 'crosswalk', 'cross_regulation'));

COMMIT;