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

// drizzle-orm does not export a coalesce helper — use sql tagged template.
function coalesce<T>(a: SQL<T> | { getSQL(): SQL<T> }, b: SQL<T> | { getSQL(): SQL<T> }): SQL<T> {
  return sql<T>`COALESCE(${a}, ${b})`;
}

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

// findBySlug: matches on translation.slug for the given locale.
// Falls back to 'he' translation if requested locale is missing.
export async function findBySlug(
  db: DrizzleClient,
  slug: string,
  locale: string,
): 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(eq(supportKbArticles.isActive, true))
    .limit(1);

  const row = rows[0];
  if (!row) return null;
  return toView(row as Parameters<typeof toView>[0]);
}

export async function listByCategory(
  db: DrizzleClient,
  category: (typeof supportKbArticles.$inferSelect)['category'],
  locale: string = 'he',
): 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), eq(supportKbArticles.isActive, true)));

  return rows.map((r) => toView(r as Parameters<typeof toView>[0]));
}

export async function search(
  db: DrizzleClient,
  query: string,
  locale: string,
  visibility: (typeof supportKbArticles.$inferSelect)['visibility'],
): Promise<KbArticleView[]> {
  const tr = aliasedTable(supportKbArticleTranslations, 'tr');
  const fb = aliasedTable(supportKbArticleTranslations, 'fb');
  const pattern = `%${query}%`;

  const resolvedTitle = coalesce(tr.title, fb.title);
  const resolvedBodyMd = coalesce(tr.bodyMd, fb.bodyMd);

  const rows = await db
    .select({
      id: supportKbArticles.id,
      slug: coalesce(tr.slug, fb.slug),
      locale: coalesce(tr.locale, fb.locale),
      category: supportKbArticles.category,
      title: resolvedTitle,
      bodyMd: resolvedBodyMd,
      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.visibility, visibility),
        eq(supportKbArticles.isActive, true),
        sql`(${ilike(coalesce(tr.title, fb.title), pattern)} OR ${ilike(coalesce(tr.bodyMd, fb.bodyMd), pattern)})`,
      ),
    );

  return rows.map((r) => toView(r as Parameters<typeof toView>[0]));
}

export async function listAll(db: DrizzleClient, locale: string = 'he'): 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(eq(supportKbArticles.isActive, true));

  return rows.map((r) => toView(r as Parameters<typeof toView>[0]));
}

export async function findById(
  db: DrizzleClient,
  id: string,
): Promise<KbArticleWithTranslations | null> {
  const parent = await db
    .select()
    .from(supportKbArticles)
    .where(eq(supportKbArticles.id, id))
    .limit(1);
  if (!parent[0]) return null;

  const translations = await db
    .select()
    .from(supportKbArticleTranslations)
    .where(eq(supportKbArticleTranslations.articleId, id));

  return {
    id: parent[0].id,
    slug: parent[0].slug,
    category: parent[0].category,
    tags: parent[0].tags,
    visibility: parent[0].visibility as KbArticleWithTranslations['visibility'],
    isActive: parent[0].isActive,
    createdAt: parent[0].createdAt.toISOString(),
    updatedAt: parent[0].updatedAt.toISOString(),
    translations: translations.map((t) => ({
      id: t.id,
      articleId: t.articleId,
      locale: t.locale,
      slug: t.slug,
      title: t.title,
      bodyMd: t.bodyMd,
      status: t.status,
      createdAt: t.createdAt.toISOString(),
      updatedAt: t.updatedAt.toISOString(),
    })),
  };
}

export interface InsertInput {
  locale: 'he' | 'en';
  slug: string;
  title: string;
  bodyMd: string;
  category: (typeof supportKbArticles.$inferSelect)['category'];
  tags: string[];
  visibility: (typeof supportKbArticles.$inferSelect)['visibility'];
  isActive: boolean;
  updatedBy?: string | null;
}

// Transactional insert: parent row with canonical slug = payload.slug (first-locale-wins,
// locked at creation regardless of locale) + one translation row for the first locale.
export async function insert(
  db: TxDrizzleClient,
  values: InsertInput,
): Promise<KbArticleWithTranslations> {
  return db.transaction(async (tx) => {
    const now = new Date();
    const [parent] = await tx
      .insert(supportKbArticles)
      .values({
        slug: values.slug,
        category: values.category,
        tags: values.tags,
        visibility: values.visibility,
        isActive: values.isActive,
        createdAt: now,
        updatedAt: now,
        updatedBy: values.updatedBy ?? null,
      })
      .returning();

    const [translation] = await tx
      .insert(supportKbArticleTranslations)
      .values({
        articleId: parent!.id,
        locale: values.locale,
        slug: values.slug,
        title: values.title,
        bodyMd: values.bodyMd,
        createdAt: now,
        updatedAt: now,
      })
      .returning();

    return {
      id: parent!.id,
      slug: parent!.slug,
      category: parent!.category,
      tags: parent!.tags,
      visibility: parent!.visibility as KbArticleWithTranslations['visibility'],
      isActive: parent!.isActive,
      createdAt: parent!.createdAt.toISOString(),
      updatedAt: parent!.updatedAt.toISOString(),
      translations: [
        {
          id: translation!.id,
          articleId: translation!.articleId,
          locale: translation!.locale,
          slug: translation!.slug,
          title: translation!.title,
          bodyMd: translation!.bodyMd,
          status: translation!.status,
          createdAt: translation!.createdAt.toISOString(),
          updatedAt: translation!.updatedAt.toISOString(),
        },
      ],
    };
  });
}

export interface UpdateParentInput {
  category?: (typeof supportKbArticles.$inferSelect)['category'];
  tags?: string[];
  visibility?: (typeof supportKbArticles.$inferSelect)['visibility'];
  isActive?: boolean;
  updatedBy?: string | null;
}

export async function updateParent(
  db: DrizzleClient,
  id: string,
  values: UpdateParentInput,
): Promise<void> {
  await db
    .update(supportKbArticles)
    .set({ ...values, updatedAt: new Date() })
    .where(eq(supportKbArticles.id, id));
}

export interface UpsertTranslationInput {
  locale: string;
  slug?: string;
  title?: string;
  bodyMd?: string;
}

// Upsert translation row for a given locale. Parent canonical slug stays locked (first-locale-wins).
export async function upsertTranslation(
  db: DrizzleClient,
  articleId: string,
  values: UpsertTranslationInput,
): Promise<void> {
  const now = new Date();
  const existing = await db
    .select({ id: supportKbArticleTranslations.id })
    .from(supportKbArticleTranslations)
    .where(
      and(
        eq(supportKbArticleTranslations.articleId, articleId),
        eq(supportKbArticleTranslations.locale, values.locale),
      ),
    )
    .limit(1);

  if (existing[0]) {
    await db
      .update(supportKbArticleTranslations)
      .set({
        ...(values.slug !== undefined ? { slug: values.slug, slugManualOverride: true } : {}),
        ...(values.title !== undefined ? { title: values.title, titleManualOverride: true } : {}),
        ...(values.bodyMd !== undefined
          ? { bodyMd: values.bodyMd, bodyMdManualOverride: true }
          : {}),
        updatedAt: now,
      })
      .where(
        and(
          eq(supportKbArticleTranslations.articleId, articleId),
          eq(supportKbArticleTranslations.locale, values.locale),
        ),
      );
  } else {
    await db.insert(supportKbArticleTranslations).values({
      articleId,
      locale: values.locale,
      slug: values.slug ?? '',
      title: values.title ?? '',
      bodyMd: values.bodyMd ?? '',
      createdAt: now,
      updatedAt: now,
    });
  }
}

export async function softDelete(db: DrizzleClient, id: string): Promise<void> {
  await db
    .update(supportKbArticles)
    .set({ isActive: false, updatedAt: new Date() })
    .where(eq(supportKbArticles.id, id));
}