-- Migration A1: KB bilingual sidecar schema
-- Creates support_kb_article_translations sidecar table.
-- Adds created_at to parent. Updates parent indexes.
-- A1 preserves legacy locale/title/body_md columns on parent (NOT NULL) — A2 drops them.
-- Backfill: one parent per slug group (first-locale-wins: oldest row by id wins canonical),
--   one translation row per existing (slug, locale) pair.
-- Does NOT drop legacy columns (A2 commit will do that after queries verified live).

ALTER TABLE "support_kb_articles" ADD COLUMN "created_at" timestamp with time zone DEFAULT now() NOT NULL;--> statement-breakpoint

CREATE TABLE "support_kb_article_translations" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"article_id" uuid NOT NULL,
	"locale" text NOT NULL,
	"slug" text NOT NULL,
	"title" text NOT NULL,
	"body_md" text NOT NULL,
	"slug_manual_override" boolean DEFAULT true NOT NULL,
	"title_manual_override" boolean DEFAULT true NOT NULL,
	"body_md_manual_override" boolean DEFAULT true NOT NULL,
	"model_id" text,
	"status" text DEFAULT 'OK' NOT NULL,
	"translated_at" timestamp with time zone,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	"updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint

ALTER TABLE "support_kb_article_translations" ADD CONSTRAINT "support_kb_article_translations_article_id_support_kb_articles_id_fk" FOREIGN KEY ("article_id") REFERENCES "public"."support_kb_articles"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "support_kb_article_translations" ADD CONSTRAINT "support_kb_article_translations_locale_languages_code_fk" FOREIGN KEY ("locale") REFERENCES "public"."languages"("code") ON DELETE no action ON UPDATE no action;--> statement-breakpoint

CREATE UNIQUE INDEX "support_kb_article_translations_article_locale_idx" ON "support_kb_article_translations" USING btree ("article_id","locale");--> statement-breakpoint
CREATE UNIQUE INDEX "support_kb_article_translations_locale_slug_idx" ON "support_kb_article_translations" USING btree ("locale","slug");--> statement-breakpoint
CREATE INDEX "support_kb_article_translations_locale_status_idx" ON "support_kb_article_translations" USING btree ("locale","status");--> statement-breakpoint

-- BACKFILL: populate translations sidecar from existing parent rows.
-- Step B1: insert translation rows for ALL existing (slug, locale) pairs.
--   canonical parent id = id of the oldest row per slug group (first-locale-wins: ORDER BY id ASC
--   used as deterministic proxy for creation order since updated_at may be equal post-seed).
WITH canonical AS (
  SELECT DISTINCT ON (slug)
    id AS canonical_id,
    slug
  FROM support_kb_articles
  ORDER BY slug, id ASC  -- first-locale-wins: oldest id = first authored
)
INSERT INTO support_kb_article_translations
  (id, article_id, locale, slug, title, body_md,
   slug_manual_override, title_manual_override, body_md_manual_override,
   model_id, status, translated_at, created_at, updated_at)
SELECT
  gen_random_uuid(),
  c.canonical_id,
  a.locale,
  a.slug,
  a.title,
  a.body_md,
  true,
  true,
  true,
  NULL,
  'OK',
  NULL,
  now(),
  now()
FROM support_kb_articles a
JOIN canonical c ON c.slug = a.slug
ON CONFLICT (article_id, locale) DO NOTHING;
--> statement-breakpoint

-- Step B2: delete non-canonical parent rows (same slug, non-first locale).
--   These are EN-locale siblings whose content is now in the translations sidecar.
--   The canonical (oldest id per slug) parent row is kept.
--   MUST run before creating the new UNIQUE INDEX on slug.
DELETE FROM support_kb_articles
WHERE id NOT IN (
  SELECT DISTINCT ON (slug) id
  FROM support_kb_articles
  ORDER BY slug, id ASC
);
--> statement-breakpoint

-- Drop old (slug, locale) unique index; create new indexes on parent.
-- MUST run after DELETE above — at this point each slug has exactly one parent row.
DROP INDEX "support_kb_slug_locale_idx";--> statement-breakpoint
CREATE UNIQUE INDEX "support_kb_articles_slug_idx" ON "support_kb_articles" USING btree ("slug");--> statement-breakpoint
CREATE INDEX "support_kb_articles_category_idx" ON "support_kb_articles" USING btree ("category");--> statement-breakpoint
CREATE INDEX "support_kb_articles_active_idx" ON "support_kb_articles" USING btree ("is_active");
