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

export async function listModifierGroups(restaurantId: string, itemId?: string) {
  await initDatabase();
  const conditions: SQL[] = [sql`mg.restaurant_id = ${restaurantId}`];
  if (itemId) conditions.push(sql`mg.menu_item_id = ${itemId}`);
  const where = sql.join(conditions, sql` AND `);

  /* raw: SELECT mg.*, mi.name AS item_name, COALESCE(json_agg(...) FILTER (...), '[]'::json) AS options FROM menu_modifier_groups mg LEFT JOIN ... WHERE ... GROUP BY mg.id, mi.name ORDER BY mi.name, mg.display_order */
  const { rows } = await db.execute(sql`
    SELECT mg.*, mi.name AS item_name,
       COALESCE(
         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), '[]'::json
       ) AS options
     FROM menu_modifier_groups mg
     LEFT JOIN menu_items mi ON mg.menu_item_id = mi.id AND mi.restaurant_id = mg.restaurant_id
     LEFT JOIN menu_modifier_options mo ON mg.id = mo.modifier_group_id
     WHERE ${where}
     GROUP BY mg.id, mi.name
     ORDER BY mi.name, mg.display_order
  `);
  return rows;
}

export async function getModifierGroup(id: string, restaurantId: string) {
  await initDatabase();
  /* raw: SELECT mg.*, mi.name AS item_name, COALESCE(json_agg(...) FILTER (...), '[]'::json) AS options FROM menu_modifier_groups mg LEFT JOIN ... WHERE mg.id = $1 AND mg.restaurant_id = $2 GROUP BY mg.id, mi.name */
  const { rows } = await db.execute(sql`
    SELECT mg.*, mi.name AS item_name,
       COALESCE(
         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), '[]'::json
       ) AS options
     FROM menu_modifier_groups mg
     LEFT JOIN menu_items mi ON mg.menu_item_id = mi.id AND mi.restaurant_id = mg.restaurant_id
     LEFT JOIN menu_modifier_options mo ON mg.id = mo.modifier_group_id
     WHERE mg.id = ${id} AND mg.restaurant_id = ${restaurantId}
     GROUP BY mg.id, mi.name
  `);
  if (!rows[0]) throw new NotFoundError('Modifier group');
  return rows[0];
}

export async function createModifierGroup(restaurantId: string, data: Record<string, unknown>) {
  await initDatabase();
  if (!data.menu_item_id) throw new ValidationError('menu_item_id is required');
  /* raw: SELECT id FROM menu_items WHERE id = $1 AND restaurant_id = $2 */
  const { rows: itemRows } = await db.execute(sql`SELECT id FROM menu_items WHERE id = ${data.menu_item_id as string} AND restaurant_id = ${restaurantId}`);
  if (!itemRows[0]) throw new NotFoundError('Menu item');

  /* raw: INSERT INTO menu_modifier_groups (...) VALUES (...) RETURNING * */
  const { rows } = await db.execute(sql`
    INSERT INTO menu_modifier_groups
      (restaurant_id, branch_id, menu_item_id, name, type, is_required, min_selections, max_selections, display_order)
    VALUES (${restaurantId}, ${(data.branch_id as string) ?? null}, ${data.menu_item_id as string}, ${data.name as string},
            ${(data.type as string) ?? 'checkbox'}, ${data.is_required ?? false},
            ${(data.min_selections as number) ?? 0}, ${(data.max_selections as number) ?? null},
            ${(data.display_order as number) ?? 0})
    RETURNING *
  `);
  return rows[0];
}

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

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

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

export async function listModifierOptions(groupId: string, restaurantId: string) {
  await initDatabase();
  /* raw: SELECT mo.* FROM menu_modifier_options mo INNER JOIN menu_modifier_groups mg ON ... WHERE mo.modifier_group_id = $1 AND mg.restaurant_id = $2 ORDER BY mo.display_order, mo.name */
  const { rows } = await db.execute(sql`
    SELECT mo.* FROM menu_modifier_options mo
    INNER JOIN menu_modifier_groups mg ON mo.modifier_group_id = mg.id
    WHERE mo.modifier_group_id = ${groupId} AND mg.restaurant_id = ${restaurantId}
    ORDER BY mo.display_order, mo.name
  `);
  return rows;
}

export async function createModifierOption(groupId: string, restaurantId: string, data: Record<string, unknown>) {
  await initDatabase();
  /* raw: SELECT id FROM menu_modifier_groups WHERE id = $1 AND restaurant_id = $2 */
  const { rows: groupRows } = await db.execute(sql`SELECT id FROM menu_modifier_groups WHERE id = ${groupId} AND restaurant_id = ${restaurantId}`);
  if (!groupRows[0]) throw new NotFoundError('Modifier group');

  /* raw: INSERT INTO menu_modifier_options (...) VALUES (...) RETURNING * */
  const { rows } = await db.execute(sql`
    INSERT INTO menu_modifier_options (modifier_group_id, name, price_delta, is_default, is_available, display_order)
    VALUES (${groupId}, ${data.name as string}, ${(data.price_delta as number) ?? 0}, ${data.is_default ?? false}, ${data.is_available ?? true}, ${(data.display_order as number) ?? 0})
    RETURNING *
  `);
  return rows[0];
}

export async function updateModifierOption(optId: string, groupId: string, restaurantId: string, data: Record<string, unknown>) {
  await initDatabase();
  /* raw: SELECT id FROM menu_modifier_groups WHERE id = $1 AND restaurant_id = $2 */
  const { rows: groupRows } = await db.execute(sql`SELECT id FROM menu_modifier_groups WHERE id = ${groupId} AND restaurant_id = ${restaurantId}`);
  if (!groupRows[0]) throw new NotFoundError('Modifier group');

  const allowed = ['name', 'price_delta', 'is_default', 'is_available', 'display_order'];
  const sets: SQL[] = [];
  for (const [k, v] of Object.entries(data)) {
    if (!allowed.includes(k)) continue;
    sets.push(sql`${sql.raw(k)} = ${bindValue(v)}`);
  }
  if (sets.length === 0) throw new ValidationError('No valid fields to update');

  /* raw: UPDATE menu_modifier_options SET ... WHERE id = $N AND modifier_group_id = $N RETURNING * */
  const { rows } = await db.execute(sql`UPDATE menu_modifier_options SET ${sql.join(sets, sql`, `)} WHERE id = ${optId} AND modifier_group_id = ${groupId} RETURNING *`);
  if (!rows[0]) throw new NotFoundError('Modifier option');
  return rows[0];
}

export async function deleteModifierOption(optId: string, groupId: string, restaurantId: string) {
  await initDatabase();
  /* raw: SELECT id FROM menu_modifier_groups WHERE id = $1 AND restaurant_id = $2 */
  const { rows: groupRows } = await db.execute(sql`SELECT id FROM menu_modifier_groups WHERE id = ${groupId} AND restaurant_id = ${restaurantId}`);
  if (!groupRows[0]) throw new NotFoundError('Modifier group');

  /* raw: DELETE FROM menu_modifier_options WHERE id = $1 AND modifier_group_id = $2 */
  const result = await db.execute(sql`DELETE FROM menu_modifier_options WHERE id = ${optId} AND modifier_group_id = ${groupId}`);
  if ((result.rowCount ?? 0) === 0) throw new NotFoundError('Modifier option');
}