import { db } from '@server/db/drizzle';
import { sql, type SQL } from 'drizzle-orm';
import { initDatabase } from '@server/db/init';
import { NotFoundError, ValidationError } from '@server/errors';
import { bindValue } from '@server/db/bind';
import { generateKbEmbedding } from './kb-embeddings.service';

export interface KbFilters {
  restaurantId: string;
  branchId?: string;
  type?: string;
  status?: string;
  search?: string;
  page?: number;
  limit?: number;
}

export async function listKbEntries(filters: KbFilters) {
  await initDatabase();
  const { restaurantId, branchId, type, status, search, page = 1, limit = 20 } = filters;
  const offset = (page - 1) * limit;
  const conditions: SQL[] = [sql`restaurant_id = ${restaurantId}`];

  if (branchId) conditions.push(sql`(branch_id = ${branchId} OR branch_id IS NULL)`);
  if (type) conditions.push(sql`type = ${type}`);
  if (status) conditions.push(sql`status = ${status}`);
  if (search) {
    const pattern = `%${search}%`;
    conditions.push(sql`(title ILIKE ${pattern} OR content ILIKE ${pattern})`);
  }

  const where = sql.join(conditions, sql` AND `);

  /* raw: SELECT COUNT(*) FROM knowledge_base WHERE ... */
  const { rows: countRows } = await db.execute(sql`SELECT COUNT(*) FROM knowledge_base WHERE ${where}`);
  const total = parseInt((countRows[0] as { count: string })?.count ?? '0', 10);

  /* raw: SELECT * FROM knowledge_base WHERE ... ORDER BY created_at DESC LIMIT ... OFFSET ... */
  const { rows } = await db.execute(sql`SELECT * FROM knowledge_base WHERE ${where} ORDER BY created_at DESC LIMIT ${limit} OFFSET ${offset}`);
  return { data: rows, total, page, limit, pages: Math.ceil(total / limit) };
}

export async function getKbEntry(id: string, restaurantId: string) {
  await initDatabase();
  /* raw: SELECT * FROM knowledge_base WHERE id = $1 AND restaurant_id = $2 */
  const { rows } = await db.execute(sql`SELECT * FROM knowledge_base WHERE id = ${id} AND restaurant_id = ${restaurantId}`);
  if (!rows[0]) throw new NotFoundError('Knowledge base entry');
  return rows[0];
}

export async function createKbEntry(restaurantId: string, data: Record<string, unknown>) {
  await initDatabase();
  /* raw: INSERT INTO knowledge_base (...) VALUES (...) RETURNING * */
  const { rows } = await db.execute(sql`
    INSERT INTO knowledge_base (restaurant_id, branch_id, type, title, content, url, question, answer,
       file_size, file_type, pages, status, is_active, embedding_status)
    VALUES (${restaurantId}, ${(data.branch_id as string) ?? null}, ${(data.type as string) ?? 'doc'}, ${data.title as string},
            ${(data.content as string) ?? null}, ${(data.url as string) ?? null}, ${(data.question as string) ?? null}, ${(data.answer as string) ?? null},
            ${(data.file_size as number) ?? null}, ${(data.file_type as string) ?? null}, ${(data.pages as number) ?? null},
            ${(data.status as string) ?? 'active'}, ${data.is_active ?? true}, ${'pending'})
    RETURNING *
  `);
  const entry = rows[0];
  if (entry) {
    generateKbEmbedding((entry as Record<string, unknown>).id as string, restaurantId).catch(() => {});
  }
  return entry;
}

export async function updateKbEntry(id: string, restaurantId: string, data: Record<string, unknown>) {
  await initDatabase();
  const allowed = ['title','content','url','question','answer','status','is_active','type'];
  const filteredEntries = Object.entries(data).filter(([k]) => allowed.includes(k));
  if (filteredEntries.length === 0) throw new ValidationError('No valid fields to update');

  const contentFields = ['title','content','question','answer','url'];
  const needsReEmbed = filteredEntries.some(([k]) => contentFields.includes(k));

  const sets: SQL[] = [];
  for (const [k, v] of filteredEntries) {
    sets.push(sql`${sql.raw(k)} = ${bindValue(v)}`);
  }
  if (needsReEmbed) sets.push(sql`embedding_status = 'pending'`);
  sets.push(sql`updated_at = NOW()`);

  /* raw: UPDATE knowledge_base SET ... WHERE id = $N AND restaurant_id = $N RETURNING * */
  const { rows } = await db.execute(sql`UPDATE knowledge_base SET ${sql.join(sets, sql`, `)} WHERE id = ${id} AND restaurant_id = ${restaurantId} RETURNING *`);
  if (!rows[0]) throw new NotFoundError('Knowledge base entry');
  if (needsReEmbed) {
    generateKbEmbedding(id, restaurantId).catch(() => {});
  }
  return rows[0];
}

export async function deleteKbEntry(id: string, restaurantId: string) {
  await initDatabase();
  /* raw: DELETE FROM knowledge_base WHERE id = $1 AND restaurant_id = $2 */
  const result = await db.execute(sql`DELETE FROM knowledge_base WHERE id = ${id} AND restaurant_id = ${restaurantId}`);
  if ((result.rowCount ?? 0) === 0) throw new NotFoundError('Knowledge base entry');
}