BEGIN;

CREATE INDEX IF NOT EXISTS "IDX_article_site_state_created"
  ON public.article ("siteId", state, created DESC);

CREATE INDEX IF NOT EXISTS "IDX_article_user_created"
  ON public.article ("userId", created DESC);

CREATE INDEX IF NOT EXISTS "IDX_article_user_article_active_level"
  ON public.article_user ("articleId", active, level);

CREATE INDEX IF NOT EXISTS "IDX_article_user_judge_status"
  ON public.article_user ("judgeId", "reviewStatus", active);

CREATE INDEX IF NOT EXISTS "IDX_notification_user_unread_created"
  ON public.notification ("userId", created DESC)
  WHERE "readAt" IS NULL;

-- auth_challenge uses `target`, not the obsolete `identifier` column.
CREATE INDEX IF NOT EXISTS "IDX_auth_challenge_target_created"
  ON public.auth_challenge (target, created DESC);

DO $$
BEGIN
  IF EXISTS (
    SELECT 1
    FROM information_schema.columns
    WHERE table_schema = 'public'
      AND table_name = 'judge'
      AND column_name = 'siteId'
  ) AND EXISTS (
    SELECT 1
    FROM information_schema.columns
    WHERE table_schema = 'public'
      AND table_name = 'judge'
      AND column_name = 'status'
  ) THEN
    EXECUTE 'CREATE INDEX IF NOT EXISTS "IDX_judge_site_status" ON public.judge ("siteId", status) WHERE status IS NOT NULL';
  END IF;
END $$;

COMMIT;