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';

export async function listCategories(restaurantId: string, branchId?: string) {
  await initDatabase();
  const conditions: SQL[] = [sql`restaurant_id = ${restaurantId}`];
  if (branchId) conditions.push(sql`(branch_id = ${branchId} OR branch_id IS NULL)`);
  const where = sql.join(conditions, sql` AND `);

  /* raw: SELECT * FROM menu_categories WHERE restaurant_id = $1 [AND (branch_id = $2 OR branch_id IS NULL)] ORDER BY display_order ASC, name ASC */
  const { rows } = await db.execute(sql`SELECT * FROM menu_categories WHERE ${where} ORDER BY display_order ASC, name ASC`);
  return rows;
}

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

export async function createCategory(restaurantId: string, data: Record<string, unknown>) {
  await initDatabase();
  /* raw: INSERT INTO menu_categories (...) VALUES (...) RETURNING * */
  const { rows } = await db.execute(sql`
    INSERT INTO menu_categories (restaurant_id, branch_id, name, display_order, is_active)
    VALUES (${restaurantId}, ${(data.branch_id as string) ?? null}, ${data.name as string}, ${(data.display_order as number) ?? 0}, ${data.is_active ?? true})
    RETURNING *
  `);
  return rows[0];
}

export async function updateCategory(id: string, restaurantId: string, data: Record<string, unknown>) {
  await initDatabase();
  const allowed = ['name', 'display_order', 'is_active'];
  const sets: SQL[] = [];
  for (const [k, v] of Object.entries(data)) {
    if (!allowed.includes(k)) continue;
    sets.push(sql`${sql.raw(k)} = ${v as string | number | boolean | null}`);
  }
  if (sets.length === 0) throw new ValidationError('No valid fields to update');

  /* raw: UPDATE menu_categories SET ... WHERE id = $N AND restaurant_id = $N RETURNING * */
  const { rows } = await db.execute(sql`UPDATE menu_categories SET ${sql.join(sets, sql`, `)} WHERE id = ${id} AND restaurant_id = ${restaurantId} RETURNING *`);
  if (!rows[0]) throw new NotFoundError('Menu category');
  return rows[0];
}

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

export interface MenuItemFilters {
  restaurantId: string;
  branchId?: string;
  categoryId?: string;
  available?: boolean;
  search?: string;
  page?: number;
  limit?: number;
}

export async function listItems(filters: MenuItemFilters) {
  await initDatabase();
  const { restaurantId, branchId, categoryId, available, search, page = 1, limit = 50 } = filters;
  const offset = (page - 1) * limit;
  const conditions: SQL[] = [sql`mi.restaurant_id = ${restaurantId}`];

  if (branchId) conditions.push(sql`(mi.branch_id = ${branchId} OR mi.branch_id IS NULL)`);
  if (categoryId) conditions.push(sql`mi.category_id = ${categoryId}`);
  if (available !== undefined) conditions.push(sql`mi.is_available = ${available}`);
  if (search) conditions.push(sql`mi.name ILIKE ${`%${search}%`}`);

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

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

  /* raw: SELECT mi.*, mc.name as category_name FROM menu_items mi LEFT JOIN menu_categories mc ... WHERE ... ORDER BY ... LIMIT ... OFFSET ... */
  const { rows } = await db.execute(sql`
    SELECT mi.*, mc.name as category_name FROM menu_items mi
    LEFT JOIN menu_categories mc ON mi.category_id = mc.id
    WHERE ${where}
    ORDER BY mc.display_order ASC, mi.sort_order ASC, mi.name ASC
    LIMIT ${limit} OFFSET ${offset}
  `);

  if (rows.length > 0) {
    const itemIds = rows.map((r: Record<string, unknown>) => r.id);
    /* raw: SELECT mg.*, json_agg(...) FILTER (...) AS options FROM menu_modifier_groups mg LEFT JOIN menu_modifier_options mo ... WHERE mg.menu_item_id IN (...) ... GROUP BY mg.id ORDER BY mg.display_order */
    const { rows: groups } = await db.execute(sql`
      SELECT mg.*, json_agg(
         json_build_object('id', mo.id, 'name', mo.name, 'price_delta', mo.price_delta,
           'is_default', mo.is_default, 'is_available', mo.is_available, 'display_order', mo.display_order)
         ORDER BY mo.display_order, mo.name
       ) FILTER (WHERE mo.id IS NOT NULL) AS options
       FROM menu_modifier_groups mg
       LEFT JOIN menu_modifier_options mo ON mg.id = mo.modifier_group_id
       WHERE mg.menu_item_id IN (${sql.join((itemIds as string[]).map(id => sql`${id}::uuid`), sql`, `)}) AND mg.restaurant_id = ${restaurantId}
       GROUP BY mg.id
       ORDER BY mg.display_order
    `);
    const groupsByItem: Record<string, unknown[]> = {};
    for (const g of groups as Record<string, unknown>[]) {
      const itemId = g.menu_item_id as string;
      if (!groupsByItem[itemId]) groupsByItem[itemId] = [];
      groupsByItem[itemId].push(g);
    }
    for (const row of rows as Record<string, unknown>[]) {
      (row as Record<string, unknown>).modifier_groups = groupsByItem[row.id as string] ?? [];
    }
  }

  return { data: rows, total, page, limit, pages: Math.ceil(total / limit) };
}

export async function getItem(id: string, restaurantId: string) {
  await initDatabase();
  /* raw: SELECT mi.*, mc.name as category_name FROM menu_items mi LEFT JOIN menu_categories mc ... WHERE mi.id = $1 AND mi.restaurant_id = $2 */
  const { rows } = await db.execute(sql`
    SELECT mi.*, mc.name as category_name FROM menu_items mi
    LEFT JOIN menu_categories mc ON mi.category_id = mc.id
    WHERE mi.id = ${id} AND mi.restaurant_id = ${restaurantId}
  `);
  const row = rows[0] as Record<string, unknown> | undefined;
  if (!row) throw new NotFoundError('Menu item');

  /* raw: SELECT mg.*, json_agg(...) FILTER (...) AS options FROM menu_modifier_groups mg LEFT JOIN menu_modifier_options mo ... WHERE mg.menu_item_id = $1 AND mg.restaurant_id = $2 GROUP BY mg.id ORDER BY mg.display_order */
  const { rows: groups } = await db.execute(sql`
    SELECT mg.*, json_agg(
       json_build_object('id', mo.id, 'name', mo.name, 'price_delta', mo.price_delta,
         'is_default', mo.is_default, 'is_available', mo.is_available, 'display_order', mo.display_order)
       ORDER BY mo.display_order, mo.name
     ) FILTER (WHERE mo.id IS NOT NULL) AS options
     FROM menu_modifier_groups mg
     LEFT JOIN menu_modifier_options mo ON mg.id = mo.modifier_group_id
     WHERE mg.menu_item_id = ${id} AND mg.restaurant_id = ${restaurantId}
     GROUP BY mg.id
     ORDER BY mg.display_order
  `);
  row.modifier_groups = groups;
  return row;
}

export async function createItem(restaurantId: string, data: Record<string, unknown>) {
  await initDatabase();
  /* raw: INSERT INTO menu_items (...) VALUES (...) RETURNING * */
  const { rows } = await db.execute(sql`
    INSERT INTO menu_items (restaurant_id, branch_id, category_id, name, description, price,
       modifiers, dietary_tags, image_url, is_available, sort_order, spice_level, is_veg,
       prep_time_minutes, calories)
    VALUES (${restaurantId}, ${(data.branch_id as string) ?? null}, ${(data.category_id as string) ?? null},
            ${data.name as string}, ${(data.description as string) ?? null}, ${(data.price as number) ?? 0},
            ${JSON.stringify(data.modifiers ?? [])}::jsonb, ${JSON.stringify(data.dietary_tags ?? [])}::jsonb,
            ${(data.image_url as string) ?? null}, ${data.is_available ?? true},
            ${(data.sort_order as number) ?? 0}, ${(data.spice_level as number) ?? 0}, ${data.is_veg ?? false},
            ${(data.prep_time_minutes as number) ?? 0}, ${(data.calories as number) ?? 0})
    RETURNING *
  `);
  return rows[0];
}

export async function updateItem(id: string, restaurantId: string, data: Record<string, unknown>) {
  await initDatabase();
  const allowed = [
    'category_id', 'name', 'description', 'price', 'modifiers', 'dietary_tags',
    'image_url', 'is_available', 'sort_order', 'spice_level', 'is_veg',
    'prep_time_minutes', 'calories',
  ];
  const sets: SQL[] = [];
  for (const [k, v] of Object.entries(data)) {
    if (!allowed.includes(k)) continue;
    const val = (k === 'modifiers' || k === 'dietary_tags') ? JSON.stringify(v) : v;
    sets.push(sql`${sql.raw(k)} = ${val as string | number | boolean | null}`);
  }
  if (sets.length === 0) throw new ValidationError('No valid fields to update');
  sets.push(sql`updated_at = NOW()`);

  /* raw: UPDATE menu_items SET ... WHERE id = $N AND restaurant_id = $N RETURNING * */
  const { rows } = await db.execute(sql`UPDATE menu_items SET ${sql.join(sets, sql`, `)} WHERE id = ${id} AND restaurant_id = ${restaurantId} RETURNING *`);
  if (!rows[0]) throw new NotFoundError('Menu item');
  return rows[0];
}

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