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

const SAFE_COLS = `id, restaurant_id, branch_id, table_number, label, capacity, zone, qr_style_json, is_active, created_at, updated_at`;

export async function listTables(restaurantId: string, branchId?: string) {
  await initDatabase();
  const conditions: SQL[] = [sql`restaurant_id = ${restaurantId}`];
  if (branchId) conditions.push(sql`branch_id = ${branchId}`);
  const { rows } = await db.execute(sql`
    SELECT ${sql.raw(SAFE_COLS)} FROM restaurant_tables
    WHERE ${sql.join(conditions, sql` AND `)}
    ORDER BY table_number ASC
  `);
  return rows;
}

export async function getTable(id: string, restaurantId: string) {
  await initDatabase();
  const { rows } = await db.execute(sql`
    SELECT ${sql.raw(SAFE_COLS)} FROM restaurant_tables
    WHERE id = ${id} AND restaurant_id = ${restaurantId}
  `);
  if (!rows[0]) throw new NotFoundError('Table');
  return rows[0];
}

export async function getTableByNumber(branchId: string, tableNumber: string) {
  await initDatabase();
  const { rows } = await db.execute(sql`
    SELECT ${sql.raw(SAFE_COLS)} FROM restaurant_tables
    WHERE branch_id = ${branchId} AND table_number = ${tableNumber} AND is_active = true
  `);
  return rows[0] ?? null;
}

export async function createTable(restaurantId: string, data: Record<string, unknown>) {
  await initDatabase();
  const branchId = data.branch_id as string;
  if (!branchId) throw new ValidationError('branch_id is required');
  const tableNumber = String(data.table_number ?? '').trim();
  if (!tableNumber) throw new ValidationError('table_number is required');

  // Confirm branch belongs to restaurant
  const { rows: branchRows } = await db.execute(sql`
    SELECT id FROM branches WHERE id = ${branchId} AND restaurant_id = ${restaurantId}
  `);
  if (!branchRows[0]) throw new NotFoundError('Branch');

  try {
    const { rows } = await db.execute(sql`
      INSERT INTO restaurant_tables (restaurant_id, branch_id, table_number, label, capacity, zone, qr_style_json, is_active)
      VALUES (${restaurantId}, ${branchId}, ${tableNumber}, ${(data.label as string) ?? null},
              ${(data.capacity as number) ?? 2},
              ${(data.zone as string) ?? null},
              ${JSON.stringify(data.qr_style_json ?? {})}::jsonb,
              ${(data.is_active as boolean) ?? true})
      RETURNING ${sql.raw(SAFE_COLS)}
    `);
    return rows[0];
  } catch (err: unknown) {
    if ((err as { code?: string })?.code === '23505') {
      throw new ConflictError(`Table number "${tableNumber}" already exists for this branch`);
    }
    throw err;
  }
}

export async function bulkCreateTables(
  restaurantId: string,
  branchId: string,
  numbers: string[],
  defaults: { capacity?: number; zone?: string | null; qr_style_json?: unknown } = {}
) {
  await initDatabase();
  const { rows: branchRows } = await db.execute(sql`
    SELECT id FROM branches WHERE id = ${branchId} AND restaurant_id = ${restaurantId}
  `);
  if (!branchRows[0]) throw new NotFoundError('Branch');

  const created: unknown[] = [];
  await db.transaction(async (tx) => {
    for (const numRaw of numbers) {
      const num = String(numRaw).trim();
      if (!num) continue;
      const { rows } = await tx.execute(sql`
        INSERT INTO restaurant_tables (restaurant_id, branch_id, table_number, capacity, zone, qr_style_json, is_active)
        VALUES (${restaurantId}, ${branchId}, ${num},
                ${defaults.capacity ?? 2},
                ${(defaults as { zone?: string }).zone ?? null},
                ${JSON.stringify(defaults.qr_style_json ?? {})}::jsonb, true)
        ON CONFLICT (branch_id, table_number) DO NOTHING
        RETURNING ${sql.raw(SAFE_COLS)}
      `);
      if (rows[0]) created.push(rows[0]);
    }
  });
  return created;
}

export async function updateTable(id: string, restaurantId: string, data: Record<string, unknown>) {
  await initDatabase();
  const allowed = ['table_number', 'label', 'capacity', 'zone', 'qr_style_json', 'is_active'];
  const sets: SQL[] = [];
  for (const [k, v] of Object.entries(data)) {
    if (!allowed.includes(k)) continue;
    if (k === 'qr_style_json') {
      sets.push(sql`qr_style_json = ${JSON.stringify(v ?? {})}::jsonb`);
    } else {
      sets.push(sql`${sql.raw(k)} = ${bindValue(v)}`);
    }
  }
  if (sets.length === 0) throw new ValidationError('No valid fields to update');
  sets.push(sql`updated_at = NOW()`);

  try {
    const { rows } = await db.execute(sql`
      UPDATE restaurant_tables
      SET ${sql.join(sets, sql`, `)}
      WHERE id = ${id} AND restaurant_id = ${restaurantId}
      RETURNING ${sql.raw(SAFE_COLS)}
    `);
    if (!rows[0]) throw new NotFoundError('Table');
    return rows[0];
  } catch (err: unknown) {
    if ((err as { code?: string })?.code === '23505') {
      throw new ConflictError('A table with that number already exists for this branch');
    }
    throw err;
  }
}

export async function deleteTable(id: string, restaurantId: string) {
  await initDatabase();
  const result = await db.execute(sql`
    DELETE FROM restaurant_tables WHERE id = ${id} AND restaurant_id = ${restaurantId}
  `);
  if ((result.rowCount ?? 0) === 0) throw new NotFoundError('Table');
}

export interface FindAvailableTableArgs {
  restaurantId: string;
  branchId: string;
  partySize: number;
  startAt: Date;
  endAt: Date;
  bufferMin: number;
}

/**
 * Find the smallest active table at this branch that fits the party and has
 * no overlapping booking in the requested window (taking each booking's own
 * buffer into account). Returns null when nothing fits — the caller should
 * still attempt the insert without a table_id (unassigned booking) and let
 * the staff resolve it manually.
 */
export async function findAvailableTable({
  restaurantId,
  branchId,
  partySize,
  startAt,
  endAt,
  bufferMin,
}: FindAvailableTableArgs) {
  await initDatabase();
  const startIso = startAt.toISOString();
  const endIso = endAt.toISOString();
  const { rows } = await db.execute(sql`
    SELECT ${sql.raw(SAFE_COLS.split(',').map((c) => `t.${c.trim()}`).join(', '))}
      FROM restaurant_tables t
     WHERE t.restaurant_id = ${restaurantId}
       AND t.branch_id     = ${branchId}
       AND t.is_active     = true
       AND t.capacity      >= ${partySize}
       AND NOT EXISTS (
         SELECT 1
           FROM bookings b
          WHERE b.table_id = t.id
            AND b.status NOT IN ('cancelled', 'noshow')
            AND b.start_at IS NOT NULL
            AND b.end_at   IS NOT NULL
            AND tstzrange(b.start_at, b.end_at + make_interval(mins => b.buffer_min), '[)')
                && tstzrange(${startIso}::timestamptz,
                             ${endIso}::timestamptz + make_interval(mins => ${bufferMin}::int),
                             '[)')
       )
     ORDER BY t.capacity ASC, t.table_number ASC
     LIMIT 1
  `);
  return rows[0] ?? null;
}