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

const SAFE_COLS = `id, restaurant_id, name, address, phone, hours, timezone, is_active,
  smtp_host, smtp_port, smtp_user, smtp_from,
  CASE WHEN smtp_pass IS NOT NULL THEN 'configured' ELSE NULL END AS smtp_pass_status,
  slug, storefront_enabled, accepts_dine_in, accepts_takeaway, accepts_delivery,
  min_order_value, qr_style_json, storefront_message,
  quiet_hours_enabled, quiet_hours_start, quiet_hours_end,
  whatsapp_send_rate,
  name_customized,
  created_at, updated_at`;

export async function listBranches(
  restaurantId: string,
  activeOnly = false,
  /**
   * When supplied (i.e. the caller is a hard-pinned staff user), the result
   * collapses to that single branch even if it's inactive — keeps every
   * /api/branches consumer (topbar picker, QR Storefront hub, …) showing
   * exactly what the user is allowed to act on.
   */
  pinnedBranchId: string | null = null,
) {
  await initDatabase();
  /* raw: SELECT ${SAFE_COLS} FROM branches WHERE restaurant_id = $1 [AND id = $pinned] [AND is_active = true] ORDER BY name ASC */
  const conditions: SQL[] = [sql`restaurant_id = ${restaurantId}`];
  if (pinnedBranchId) conditions.push(sql`id = ${pinnedBranchId}`);
  if (activeOnly) conditions.push(sql`is_active = true`);
  const { rows } = await db.execute(sql`SELECT ${sql.raw(SAFE_COLS)} FROM branches WHERE ${sql.join(conditions, sql` AND `)} ORDER BY name ASC`);
  return rows;
}

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

async function ensureBranchSlug(restaurantId: string, base: string, attemptId?: string): Promise<string> {
  const root = base && base.length > 0 ? base : 'b';
  let candidate = root;
  let i = 1;
  // Try the base, then append -2, -3, ... until unique within this restaurant
  // Scoped to (restaurant_id, slug) per the unique index.
  // attemptId is excluded from the conflict check (used by update flow).
  while (true) {
    const { rows } = await db.execute(sql`
      SELECT 1 FROM branches WHERE restaurant_id = ${restaurantId} AND slug = ${candidate}
        ${attemptId ? sql`AND id <> ${attemptId}` : sql``}
      LIMIT 1
    `);
    if (rows.length === 0) return candidate;
    i += 1;
    candidate = `${root}-${i}`;
    if (i > 1000) return `${root}-${Date.now().toString(36)}`;
  }
}

export async function createBranch(restaurantId: string, data: Record<string, unknown>) {
  await initDatabase();
  const { slugify } = await import('@server/utils/slug');
  const requested = (data.slug as string | undefined)?.trim();
  const base = requested && requested.length > 0 ? slugify(requested) : slugify((data.name as string) ?? '');
  const slug = await ensureBranchSlug(restaurantId, base);
  /* raw: INSERT INTO branches (...) VALUES (...) RETURNING * */
  const { rows } = await db.execute(sql`
    INSERT INTO branches (restaurant_id, name, address, phone, hours, timezone, is_active, slug)
    VALUES (${restaurantId}, ${data.name as string}, ${(data.address as string) ?? null}, ${(data.phone as string) ?? null},
            ${JSON.stringify(data.hours ?? {})}::jsonb, ${(data.timezone as string) ?? 'UTC'}, ${data.is_active ?? true},
            ${slug})
    RETURNING *
  `);
  return rows[0];
}

export async function updateBranch(id: string, restaurantId: string, data: Record<string, unknown>) {
  await initDatabase();
  const allowed = [
    'name','address','phone','hours','timezone','is_active',
    'smtp_host','smtp_port','smtp_user','smtp_pass','smtp_from',
    'slug','storefront_enabled','accepts_dine_in','accepts_takeaway','accepts_delivery',
    'min_order_value','storefront_message','qr_style_json',
    'quiet_hours_enabled','quiet_hours_start','quiet_hours_end',
    'whatsapp_send_rate',
  ];
  const sets: SQL[] = [];
  // Normalize/validate slug if present
  if (typeof data.slug === 'string' && data.slug.trim().length > 0) {
    const { slugify } = await import('@server/utils/slug');
    const normalized = slugify(data.slug.trim());
    data.slug = await ensureBranchSlug(restaurantId, normalized, id);
  }
  // Normalize name upfront so comparison and persistence are consistent.
  // We must compare against the real DB value — not just presence in the payload —
  // because the edit form always sends `name` even when the user changed something
  // else. Only set name_customized when the trimmed value actually differs.
  let nameActuallyChanged = false;
  if ('name' in data && typeof data.name === 'string') {
    data.name = data.name.trim();
    const { rows: curRows } = await db.execute(sql`SELECT name FROM branches WHERE id = ${id} AND restaurant_id = ${restaurantId} LIMIT 1`);
    const currentName = (curRows[0] as { name: string } | undefined)?.name ?? '';
    nameActuallyChanged = data.name !== currentName;
  }
  for (const [k, v] of Object.entries(data)) {
    if (!allowed.includes(k)) continue;
    const val = (k === 'hours' || k === 'qr_style_json') ? JSON.stringify(v ?? {}) : v;
    sets.push(sql`${sql.raw(k)} = ${bindValue(val)}`);
  }
  if (nameActuallyChanged) {
    sets.push(sql`name_customized = true`);
  }
  if (sets.length === 0) throw new ValidationError('No valid fields to update');
  sets.push(sql`updated_at = NOW()`);

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

  /* raw: SELECT ${SAFE_COLS} FROM branches WHERE id = $1 AND restaurant_id = $2 */
  const { rows: resultRows } = await db.execute(sql`SELECT ${sql.raw(SAFE_COLS)} FROM branches WHERE id = ${id} AND restaurant_id = ${restaurantId}`);
  if (!resultRows[0]) throw new NotFoundError('Branch');
  return resultRows[0];
}

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