pinakes/migrations/postgres/V9__fix_indexes_and_constraints.sql

-- Drop redundant indexes (already covered by UNIQUE constraints)
DROP INDEX IF EXISTS idx_users_username;
DROP INDEX IF EXISTS idx_user_libraries_user_id;

-- Add missing indexes for comments table
CREATE INDEX IF NOT EXISTS idx_comments_media ON comments(media_id);
CREATE INDEX IF NOT EXISTS idx_comments_parent ON comments(parent_comment_id);

-- Remove duplicates before adding unique constraint
DELETE FROM external_metadata e1
WHERE EXISTS (
    SELECT 1 FROM external_metadata e2
    WHERE e1.media_id = e2.media_id
    AND e1.source = e2.source
    AND e1.ctid < e2.ctid
);

-- Add unique constraint for external_metadata (idempotent)
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'uq_external_metadata_source'
    ) THEN
        ALTER TABLE external_metadata ADD CONSTRAINT uq_external_metadata_source UNIQUE(media_id, source);
    END IF;
END $$;