-- Add has_files column to avoid correlated subquery per row
ALTER TABLE media ADD COLUMN IF NOT EXISTS has_files BOOLEAN NOT NULL DEFAULT false;

-- Backfill from existing data
UPDATE media SET has_files = EXISTS (SELECT 1 FROM media_files mf WHERE mf.media_id = media.id AND mf.deleted_at IS NULL);

-- Add index for the upgrade detection query
CREATE INDEX IF NOT EXISTS idx_media_upgrade_candidates
    ON media (media_type) WHERE monitored = true AND has_files = true
    AND current_quality IS NOT NULL AND desired_quality IS NOT NULL
    AND current_quality::text != desired_quality::text;

-- Add trigram index for substring title search
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX IF NOT EXISTS idx_media_title_trgm ON media USING gin (title gin_trgm_ops);