momentry_core/release/migrate_cleanup_inactive_identities.sql

-- Identity Inactive Cleanup Migration
-- Date: 2026-05-14
-- Purpose: Mark auto-generated identities with no face_detection references as 'inactive'
-- Run after pipeline completes to clean up orphaned auto identities

BEGIN;

-- Mark auto identities as inactive if they have zero face_detection references
UPDATE identities
SET status = 'inactive',
    metadata = metadata || jsonb_build_object('inactivated_at', NOW()::text, 'reason', 'no_face_references')
WHERE source = 'auto'
  AND status != 'inactive'
  AND id NOT IN (
    SELECT DISTINCT identity_id FROM face_detections WHERE identity_id IS NOT NULL
  );

-- Delete identity_bindings pointing to newly inactive identities
DELETE FROM identity_bindings
WHERE identity_id IN (
    SELECT id FROM identities WHERE status = 'inactive'
);

-- Log
DO $$
DECLARE
    marked INT;
BEGIN
    SELECT COUNT(*) INTO marked FROM identities WHERE status = 'inactive' AND metadata->>'reason' = 'no_face_references';
    RAISE NOTICE 'Marked % auto identities as inactive (no face references)', marked;
END $$;

COMMIT;