/**
 * Public KB fetch helpers — read-only, visibility='public' + isActive only.
 * Admin CRUD lives under /api/admin/support/kb (phase 6).
 *
 * A1 columns (locale, title, body_md) have been dropped from support_kb_articles.
 * All content is now read exclusively from support_kb_article_translations.
 */

import { and, eq, desc, sql } from 'drizzle-orm';
import { aliasedTable } from 'drizzle-orm';
import type { SQL } from 'drizzle-orm';
import { supportKbArticles, supportKbArticleTranslations } from '@/server/db/schema';
import type { DrizzleClient } from '@/server/db/client';
import type { KbArticleView } from '@/server/support/types';

function coalesce<T>(a: SQL<T> | { getSQL(): SQL<T> }, b: SQL<T> | { getSQL(): SQL<T> }): SQL<T> {
  return sql<T>`COALESCE(${a}, ${b})`;
}

export async function getPublicKbArticle(
  db: DrizzleClient,
  slug: string,
  locale: 'he' | 'en',
): Promise<KbArticleView | null> {
  const tr = aliasedTable(supportKbArticleTranslations, 'tr');
  const fb = aliasedTable(supportKbArticleTranslations, 'fb');

  const rows = await db
    .select({
      id: supportKbArticles.id,
      slug: coalesce(tr.slug, fb.slug),
      locale: coalesce(tr.locale, fb.locale),
      category: supportKbArticles.category,
      title: coalesce(tr.title, fb.title),
      bodyMd: coalesce(tr.bodyMd, fb.bodyMd),
      tags: supportKbArticles.tags,
      visibility: supportKbArticles.visibility,
      isActive: supportKbArticles.isActive,
      createdAt: supportKbArticles.createdAt,
      updatedAt: supportKbArticles.updatedAt,
    })
    .from(supportKbArticles)
    .innerJoin(
      tr,
      and(eq(tr.articleId, supportKbArticles.id), eq(tr.slug, slug), eq(tr.locale, locale)),
    )
    .leftJoin(fb, and(eq(fb.articleId, supportKbArticles.id), eq(fb.locale, 'he')))
    .where(and(eq(supportKbArticles.visibility, 'public'), eq(supportKbArticles.isActive, true)))
    .limit(1);

  const row = rows[0];
  if (!row) return null;
  return toView(row);
}

export async function listPublicKbByCategory(
  db: DrizzleClient,
  category: string,
  locale: 'he' | 'en',
): Promise<KbArticleView[]> {
  const tr = aliasedTable(supportKbArticleTranslations, 'tr');
  const fb = aliasedTable(supportKbArticleTranslations, 'fb');

  const rows = await db
    .select({
      id: supportKbArticles.id,
      slug: coalesce(tr.slug, fb.slug),
      locale: coalesce(tr.locale, fb.locale),
      category: supportKbArticles.category,
      title: coalesce(tr.title, fb.title),
      bodyMd: coalesce(tr.bodyMd, fb.bodyMd),
      tags: supportKbArticles.tags,
      visibility: supportKbArticles.visibility,
      isActive: supportKbArticles.isActive,
      createdAt: supportKbArticles.createdAt,
      updatedAt: supportKbArticles.updatedAt,
    })
    .from(supportKbArticles)
    .leftJoin(tr, and(eq(tr.articleId, supportKbArticles.id), eq(tr.locale, locale)))
    .leftJoin(fb, and(eq(fb.articleId, supportKbArticles.id), eq(fb.locale, 'he')))
    .where(
      and(
        eq(supportKbArticles.category, category as never),
        eq(supportKbArticles.visibility, 'public'),
        eq(supportKbArticles.isActive, true),
      ),
    )
    .orderBy(desc(supportKbArticles.updatedAt));

  return rows.map(toView);
}

function toView(row: {
  id: string;
  slug: unknown;
  locale: unknown;
  category: string;
  title: unknown;
  bodyMd: unknown;
  tags: string[];
  visibility: string;
  isActive: boolean;
  createdAt: Date;
  updatedAt: Date;
}): KbArticleView {
  return {
    id: row.id,
    slug: (row.slug as string | null) ?? '',
    locale: (row.locale as string | null) ?? 'he',
    category: row.category,
    title: (row.title as string | null) ?? '',
    bodyMd: (row.bodyMd as string | null) ?? '',
    tags: row.tags,
    visibility: row.visibility as KbArticleView['visibility'],
    isActive: row.isActive,
    createdAt: row.createdAt.toISOString(),
    updatedAt: row.updatedAt.toISOString(),
  };
}