BEGIN;
ALTER TABLE "VideoTranscript"
  DROP COLUMN IF EXISTS speech_detected,
  DROP COLUMN IF EXISTS language,
  DROP COLUMN IF EXISTS audio_type,
  DROP COLUMN IF EXISTS non_speech_summary,
  DROP COLUMN IF EXISTS content_tags;

CREATE TABLE IF NOT EXISTS "TranscriptionTask" (
  "videoId" TEXT PRIMARY KEY REFERENCES "Video"(aweme_id) ON DELETE CASCADE ON UPDATE CASCADE,
  status TEXT NOT NULL DEFAULT 'QUEUED' CHECK (status IN ('QUEUED', 'PROCESSING', 'SUCCEEDED', 'FAILED')),
  "providerTaskId" TEXT,
  "submittingAt" TIMESTAMP(3),
  "submittedAt" TIMESTAMP(3),
  "nextPollAt" TIMESTAMP(3) NOT NULL DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'),
  "leaseUntil" TIMESTAMP(3),
  "leaseToken" TEXT,
  attempts INTEGER NOT NULL DEFAULT 0,
  "lastError" TEXT,
  "createdAt" TIMESTAMP(3) NOT NULL DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC'),
  "updatedAt" TIMESTAMP(3) NOT NULL DEFAULT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC')
);
CREATE INDEX IF NOT EXISTS "TranscriptionTask_status_nextPollAt_idx" ON "TranscriptionTask"(status, "nextPollAt");

-- Preserve an existing Chinese parser configuration. Fresh databases use simple
-- until the operator installs a Chinese parser and configures public.zhcfg.
DO $$ BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_ts_config c JOIN pg_namespace n ON n.oid = c.cfgnamespace
                 WHERE n.nspname = 'public' AND c.cfgname = 'zhcfg') THEN
    CREATE TEXT SEARCH CONFIGURATION public.zhcfg (COPY = pg_catalog.simple);
  END IF;
END $$;
ALTER TABLE "VideoTranscript" ADD COLUMN IF NOT EXISTS transcript_zh_tsv tsvector;
CREATE OR REPLACE FUNCTION public.refresh_video_transcript_search() RETURNS trigger AS $$
BEGIN
  NEW.transcript_zh_tsv := to_tsvector('public.zhcfg'::regconfig, COALESCE(array_to_string(NEW.transcript, ' '), ''));
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Replace the legacy live trigger as well as our own trigger on repeat deployment.
DROP TRIGGER IF EXISTS trg_vt_transcript_zh_tsv ON "VideoTranscript";
DROP TRIGGER IF EXISTS video_transcript_search_update ON "VideoTranscript";
CREATE TRIGGER video_transcript_search_update BEFORE INSERT OR UPDATE OF transcript
  ON "VideoTranscript" FOR EACH ROW EXECUTE FUNCTION public.refresh_video_transcript_search();
UPDATE "VideoTranscript" SET transcript_zh_tsv = to_tsvector('public.zhcfg'::regconfig, COALESCE(array_to_string(transcript, ' '), ''));
CREATE INDEX IF NOT EXISTS video_transcript_zh_idx ON "VideoTranscript" USING GIN (transcript_zh_tsv);
COMMIT;