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

export interface CustomerFilters {
  restaurantId: string;
  /**
   * When set, restrict the list to customers who have at least one order or
   * booking at the given branch. The customer record itself stays
   * restaurant-scoped (one record per phone number across the brand) — this is
   * a view filter, not a hard scope. When null/undefined (e.g. an owner
   * viewing "All branches"), the list is the full restaurant set.
   */
  branchId?: string | null;
  search?: string;
  page?: number;
  limit?: number;
}

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

  if (branchId) {
    conditions.push(sql`(
      EXISTS (
        SELECT 1 FROM orders o2
         WHERE o2.restaurant_id = ${restaurantId}
           AND o2.branch_id = ${branchId}
           AND o2.customer_id = c.id
      )
      OR EXISTS (
        SELECT 1 FROM bookings b2
         WHERE b2.restaurant_id = ${restaurantId}
           AND b2.branch_id = ${branchId}
           AND b2.customer_id = c.id
      )
    )`);
  }

  if (search) {
    const pattern = `%${search}%`;
    conditions.push(sql`(
      c.name ILIKE ${pattern}
      OR c.email ILIKE ${pattern}
      OR c.phone ILIKE ${pattern}
      OR EXISTS (
        SELECT 1
          FROM customer_points cp2
          JOIN loyalty_tiers lt2
            ON lt2.id = cp2.tier_id
           AND lt2.restaurant_id = ${restaurantId}
         WHERE cp2.restaurant_id = ${restaurantId}
           AND cp2.customer_id = c.id
           AND lt2.name ILIKE ${pattern}
      )
    )`);
  }

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

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

  /* raw: SELECT c.*, COALESCE(o.order_count, 0) AS total_orders, ... FROM customers c LEFT JOIN ... */
  const { rows } = await db.execute(sql`
    SELECT c.*,
      COALESCE(o.order_count, 0) AS total_orders,
      COALESCE(o.order_total, 0) AS computed_spend,
      COALESCE(b.booking_count, 0) AS total_reservations,
      GREATEST(
        COALESCE(o.last_order_at, c.created_at),
        COALESCE(b.last_booking_at, c.created_at),
        c.created_at
      ) AS last_visit,
      cp.balance AS loyalty_balance,
      cp.lifetime_points AS loyalty_lifetime_points,
      lt.name AS loyalty_tier_name,
      lt.badge_color AS loyalty_tier_color
     FROM customers c
     LEFT JOIN (
       SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS order_total, MAX(created_at) AS last_order_at
       FROM orders WHERE restaurant_id = ${restaurantId} GROUP BY customer_id
     ) o ON o.customer_id = c.id
     LEFT JOIN (
       SELECT customer_id, COUNT(*) AS booking_count, MAX(created_at) AS last_booking_at
       FROM bookings WHERE restaurant_id = ${restaurantId} GROUP BY customer_id
     ) b ON b.customer_id = c.id
     LEFT JOIN customer_points cp
       ON cp.restaurant_id = ${restaurantId} AND cp.customer_id = c.id
     LEFT JOIN loyalty_tiers lt
       ON lt.id = cp.tier_id AND lt.restaurant_id = ${restaurantId}
     WHERE ${where}
     ORDER BY COALESCE(o.order_total, 0) DESC, c.created_at DESC
     LIMIT ${limit} OFFSET ${offset}
  `);

  /* raw: SELECT enabled FROM loyalty_settings WHERE restaurant_id = $1 */
  const { rows: settingsRows } = await db.execute(sql`
    SELECT enabled FROM loyalty_settings WHERE restaurant_id = ${restaurantId}
  `);
  const loyaltyEnabled = (settingsRows[0] as { enabled?: boolean } | undefined)?.enabled === true;

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

export async function getCustomer(id: string, restaurantId: string) {
  await initDatabase();
  /* raw: SELECT c.*, ... FROM customers c LEFT JOIN ... WHERE c.id = $1 AND c.restaurant_id = $2 */
  const { rows } = await db.execute(sql`
    SELECT c.*,
      COALESCE(o.order_count, 0) AS total_orders,
      COALESCE(o.order_total, 0) AS computed_spend,
      COALESCE(b.booking_count, 0) AS total_reservations,
      GREATEST(
        COALESCE(o.last_order_at, c.created_at),
        COALESCE(b.last_booking_at, c.created_at),
        c.created_at
      ) AS last_visit
     FROM customers c
     LEFT JOIN (
       SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS order_total, MAX(created_at) AS last_order_at
       FROM orders WHERE restaurant_id = ${restaurantId} GROUP BY customer_id
     ) o ON o.customer_id = c.id
     LEFT JOIN (
       SELECT customer_id, COUNT(*) AS booking_count, MAX(created_at) AS last_booking_at
       FROM bookings WHERE restaurant_id = ${restaurantId} GROUP BY customer_id
     ) b ON b.customer_id = c.id
     WHERE c.id = ${id} AND c.restaurant_id = ${restaurantId}
  `);
  if (!rows[0]) throw new NotFoundError('Customer');
  return rows[0];
}

export async function createCustomer(restaurantId: string, data: Record<string, unknown>) {
  await initDatabase();
  if (data.email) {
    /* raw: SELECT id FROM customers WHERE restaurant_id = $1 AND email = $2 */
    const { rows: existing } = await db.execute(sql`SELECT id FROM customers WHERE restaurant_id = ${restaurantId} AND email = ${data.email as string}`);
    if (existing[0]) throw new ConflictError('A customer with this email already exists');
  }
  /* raw: INSERT INTO customers (...) VALUES (...) RETURNING * */
  const { rows } = await db.execute(sql`
    INSERT INTO customers (restaurant_id, name, email, phone, preferences, tags, notes)
    VALUES (${restaurantId}, ${data.name as string}, ${(data.email as string) ?? null}, ${(data.phone as string) ?? null},
            ${JSON.stringify(data.preferences ?? [])}::jsonb, ${JSON.stringify(data.tags ?? [])}::jsonb,
            ${(data.notes as string) ?? null})
    RETURNING *
  `);
  const row = rows[0];
  if (row) dispatchWebhook(restaurantId, 'customer.created', row as Record<string, unknown>);
  return row;
}

export async function updateCustomer(id: string, restaurantId: string, data: Record<string, unknown>) {
  await initDatabase();
  const allowed = ['name','email','phone','preferences','tags','notes','total_visits','total_spend'];
  const sets: SQL[] = [];
  for (const [k, v] of Object.entries(data)) {
    if (!allowed.includes(k)) continue;
    const val = (k === 'preferences' || k === 'tags') ? JSON.stringify(v) : v;
    sets.push(sql`${sql.raw(k)} = ${bindValue(val)}`);
  }
  if (sets.length === 0) throw new ValidationError('No valid fields to update');
  sets.push(sql`updated_at = NOW()`);

  /* raw: UPDATE customers SET ... WHERE id = $N AND restaurant_id = $N RETURNING * */
  const { rows } = await db.execute(sql`UPDATE customers SET ${sql.join(sets, sql`, `)} WHERE id = ${id} AND restaurant_id = ${restaurantId} RETURNING *`);
  if (!rows[0]) throw new NotFoundError('Customer');
  dispatchWebhook(restaurantId, 'customer.updated', rows[0] as Record<string, unknown>);
  return rows[0];
}

/**
 * Recompute total_visits / total_spend / last_order_at for a single customer
 * from the orders table. Idempotent and safe to call after every order
 * status change — runs in the caller's transaction so an aggregate write
 * failure rolls back the order status that triggered it.
 */
export async function recomputeCustomerAggregates(
  tx: { execute: typeof db.execute },
  customerId: string,
  restaurantId: string,
): Promise<void> {
  await tx.execute(sql`
    UPDATE customers c SET
      total_visits  = COALESCE((SELECT COUNT(*)::int FROM orders o
                                WHERE o.customer_id = c.id AND o.restaurant_id = c.restaurant_id
                                  AND o.status IN ('completed','delivered')), 0),
      total_spend   = COALESCE((SELECT SUM(total) FROM orders o
                                WHERE o.customer_id = c.id AND o.restaurant_id = c.restaurant_id
                                  AND o.status IN ('completed','delivered')), 0),
      last_order_at = (SELECT MAX(created_at) FROM orders o
                       WHERE o.customer_id = c.id AND o.restaurant_id = c.restaurant_id
                         AND o.status IN ('completed','delivered')),
      updated_at = NOW()
    WHERE c.id = ${customerId} AND c.restaurant_id = ${restaurantId}
  `);
}

/**
 * Normalize an inbound caller phone to E.164-ish form so the same number
 * matches across voice (Twilio gives "+1234..."), chat ("+1234..."), and
 * legacy entries that may be stored without the leading "+".
 */
export function normalizeCallerPhone(raw: string | null | undefined): string | null {
  if (!raw) return null;
  const trimmed = String(raw).trim();
  if (!trimmed || trimmed.toLowerCase() === 'anonymous' || trimmed.toLowerCase() === 'unknown') {
    return null;
  }
  // Keep leading "+" if present; strip everything else non-digit.
  const hasPlus = trimmed.startsWith('+');
  const digits = trimmed.replace(/\D+/g, '');
  if (!digits) return null;
  return hasPlus ? `+${digits}` : `+${digits}`;
}

export interface FindOrCreateCustomerResult {
  customer: {
    id: string;
    name: string | null;
    phone: string | null;
    total_visits: number;
    last_seen_at: Date | null;
    last_order_at: Date | null;
    preferred_branch_id: string | null;
  };
  isNew: boolean;
}

/**
 * Look up a customer by (restaurant_id, normalized phone), or create a
 * minimal row when none exists. On every match, bumps `total_visits` and
 * stamps `last_seen_at` so the personalization layer (voice greeting,
 * chat handoff, etc) can tell new vs returning callers apart.
 *
 * Centralizes the previous WhatsApp-only inline helper so voice + chat
 * share one implementation. Safe to call from a webhook hot path —
 * callers should still wrap it in their own try/catch if a lookup
 * failure should never break the call.
 */
export async function findOrCreateCustomerByPhone(
  restaurantId: string,
  rawPhone: string | null | undefined,
  opts?: { sourceLabel?: string; profileName?: string | null },
): Promise<FindOrCreateCustomerResult | null> {
  await initDatabase();
  const phone = normalizeCallerPhone(rawPhone);
  if (!phone) return null;

  const { rows: existing } = await db.execute(sql`
    SELECT id, name, phone, total_visits, last_seen_at, last_order_at, preferred_branch_id FROM customers
    WHERE restaurant_id = ${restaurantId} AND phone = ${phone}
    LIMIT 1
  `);
  const found = existing[0] as {
    id: string;
    name: string | null;
    phone: string | null;
    total_visits: number | null;
    last_seen_at: Date | string | null;
    last_order_at: Date | string | null;
    preferred_branch_id: string | null;
  } | undefined;
  if (found) {
    const { rows: updated } = await db.execute(sql`
      UPDATE customers
         SET total_visits = COALESCE(total_visits, 0) + 1,
             last_seen_at = NOW(),
             updated_at = NOW()
       WHERE id = ${found.id} AND restaurant_id = ${restaurantId}
       RETURNING total_visits, last_seen_at
    `);
    const fresh = updated[0] as { total_visits: number | null; last_seen_at: Date | string | null } | undefined;
    return {
      customer: {
        id: found.id,
        name: found.name,
        phone: found.phone,
        total_visits: Number(fresh?.total_visits ?? found.total_visits ?? 1),
        // Return the PREVIOUS last_seen_at so callers can say "last time we
        // spoke was X ago" — not the timestamp we just stamped on this call.
        last_seen_at: found.last_seen_at ? new Date(found.last_seen_at) : null,
        last_order_at: found.last_order_at ? new Date(found.last_order_at) : null,
        preferred_branch_id: found.preferred_branch_id ?? null,
      },
      isNew: false,
    };
  }

  const fallbackName = opts?.profileName?.trim() || `Caller ${phone.slice(-4)}`;
  const sourceNote = opts?.sourceLabel ? `Auto-created from ${opts.sourceLabel}` : null;
  const { rows: ins } = await db.execute(sql`
    INSERT INTO customers (restaurant_id, name, phone, notes, total_visits, last_seen_at)
    VALUES (${restaurantId}, ${fallbackName}, ${phone}, ${sourceNote}, 1, NOW())
    RETURNING id, name, phone, total_visits, last_seen_at, last_order_at
  `);
  const row = ins[0] as {
    id: string;
    name: string | null;
    phone: string | null;
    total_visits: number | null;
    last_seen_at: Date | string | null;
    last_order_at: Date | string | null;
  } | undefined;
  if (!row) return null;
  try { dispatchWebhook(restaurantId, 'customer.created', row as unknown as Record<string, unknown>); } catch { /* never break the caller flow */ }
  return {
    customer: {
      id: row.id,
      name: row.name,
      phone: row.phone,
      total_visits: Number(row.total_visits ?? 1),
      last_seen_at: row.last_seen_at ? new Date(row.last_seen_at) : null,
      last_order_at: row.last_order_at ? new Date(row.last_order_at) : null,
      preferred_branch_id: null,
    },
    isNew: true,
  };
}

/**
 * Persist a customer name supplied mid-conversation (e.g. when the voice
 * agent asks an unknown caller "may I know your name?"). Only overwrites
 * placeholder names ("Caller 1234", "WhatsApp 1234", or empty) so an
 * existing real name is never clobbered.
 */
export async function setCustomerName(
  restaurantId: string,
  customerId: string,
  name: string,
): Promise<boolean> {
  const trimmed = name.trim();
  if (!trimmed) return false;
  await initDatabase();
  const { rowCount } = await db.execute(sql`
    UPDATE customers
       SET name = ${trimmed}, updated_at = NOW()
     WHERE id = ${customerId}
       AND restaurant_id = ${restaurantId}
       AND (name IS NULL OR name = '' OR name ILIKE 'Caller %' OR name ILIKE 'WhatsApp %')
  `);
  return (rowCount ?? 0) > 0;
}

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