AgendaPro/server/routes/booking.ts

import { Router } from "express";
import { db } from "../db.ts";
import {
  parseWorkingHours, getWorkingHoursForDate, getCandidates, getExistingBusy,
  pickBestSlotEmployee, autoAssign, isAvailable, runInTransaction,
  type WorkingHoursMap,
} from "../lib/scheduling.ts";
import { DEFAULT_TZ } from "../lib/time.ts";

export const bookingRouter = Router();

function publicBusiness(slug: string) {
  return db
    .prepare(
      `SELECT id, name, industry, currency, currency_symbol, phone, address, timezone,
              booking_enabled, cancel_window_hours, cancel_penalty_pct, require_deposit, deposit_pct,
              auto_assign_specialist, working_hours
       FROM businesses WHERE slug = ? AND status = 'active'`
    )
    .get(slug) as any;
}

// Public business info + services + employees
bookingRouter.get("/:slug", (req, res) => {
  const biz = publicBusiness(req.params.slug);
  if (!biz) return res.status(404).json({ error: "Negocio no encontrado" });
  if (!biz.booking_enabled) return res.status(403).json({ error: "Las reservas online están desactivadas" });
  const services = db
    .prepare(`SELECT id, name, description, category, duration_min, price, color FROM services WHERE business_id = ? AND active = 1 ORDER BY category, name`)
    .all(biz.id);
  const employeeRows = db
    .prepare(`SELECT id, name, role, color FROM employees WHERE business_id = ? AND active = 1 ORDER BY name`)
    .all(biz.id) as { id: number; name: string; role: string; color: string }[];
  // Qué servicios ofrece cada especialista. Sin esto el cliente no puede filtrar el
  // paso "Elige un especialista" y deja escoger a alguien que no da ese servicio: el
  // siguiente paso responde 400 y la UI lo muestra como "no pudimos cargar los
  // horarios, intenta otra fecha", que nunca lo arregla.
  // El JOIN contra employees filtra por negocio: employee_services no tiene
  // business_id propio, así que es la única forma de no cruzar tenants aquí.
  const links = db
    .prepare(
      `SELECT es.employee_id, es.service_id
         FROM employee_services es
         JOIN employees e ON e.id = es.employee_id
        WHERE e.business_id = ? AND e.active = 1`
    )
    .all(biz.id) as { employee_id: number; service_id: number }[];
  const byEmployee = new Map<number, number[]>();
  for (const l of links) {
    const arr = byEmployee.get(l.employee_id) ?? [];
    arr.push(l.service_id);
    byEmployee.set(l.employee_id, arr);
  }
  const employees = employeeRows.map((e) => ({ ...e, service_ids: byEmployee.get(e.id) ?? [] }));
  res.json({ business: { ...biz, currency_symbol: biz.currency_symbol || "$" }, services, employees });
});

// Available slots for a service + employee on a given date
bookingRouter.get("/:slug/slots", (req, res) => {
  const biz = publicBusiness(req.params.slug);
  if (!biz || !biz.booking_enabled) return res.status(404).json({ error: "No disponible" });
  const serviceId = Number(req.query.service_id);
  const employeeId = req.query.employee_id ? Number(req.query.employee_id) : null;
  const date = req.query.date as string; // YYYY-MM-DD, en la tz del negocio
  if (!serviceId || !date) return res.status(400).json({ error: "service_id y date son obligatorios" });
  if (!/^\d{4}-\d{2}-\d{2}$/.test(date)) return res.status(400).json({ error: "Fecha inválida (formato AAAA-MM-DD)" });
  const tz = biz.timezone || DEFAULT_TZ;
  const service = db.prepare(`SELECT * FROM services WHERE id = ? AND business_id = ? AND active = 1`).get(serviceId, biz.id) as any;
  if (!service) return res.status(400).json({ error: "Servicio no válido" });

  // candidate employees
  let candidates: number[];
  if (employeeId) {
    const ok = db.prepare(`SELECT 1 FROM employee_services WHERE service_id = ? AND employee_id = ?`).get(serviceId, employeeId);
    if (!ok) return res.status(400).json({ error: "El especialista no ofrece este servicio" });
    candidates = [employeeId];
  } else {
    candidates = db.prepare(`SELECT employee_id id FROM employee_services WHERE service_id = ?`).all(serviceId).map((r: any) => r.id);
    if (candidates.length === 0) {
      const anyEmp = db.prepare(`SELECT id FROM employees WHERE business_id = ? AND active = 1 LIMIT 1`).get(biz.id) as any;
      candidates = anyEmp ? [anyEmp.id] : [];
    }
  }
  if (candidates.length === 0) return res.json({ slots: [] });

  const bizWh = parseWorkingHours(biz.working_hours) as WorkingHoursMap | null;

  // Si el cliente no eligió especialista, usar el ranking para mostrar el mejor disponible por slot.
  const useRanking = !employeeId;
  const candInfos = useRanking ? getCandidates(db, biz.id, serviceId) : [];
  const candBusyMap = useRanking ? getExistingBusy(db, candInfos.map((c) => c.id), date, tz) : new Map();

  // Para cuando sí se eligió especialista: ventana = horario de ese empleado (o negocio).
  const singleEmpRow = employeeId
    ? (db.prepare(`SELECT working_hours wh FROM employees WHERE id = ?`).get(employeeId) as any)
    : null;
  const singleEmpWh = singleEmpRow ? parseWorkingHours(singleEmpRow.wh) : null;
  const window = employeeId
    ? (getWorkingHoursForDate(singleEmpWh as WorkingHoursMap | null, bizWh, date, tz) ?? getWorkingHoursForDate(null, bizWh, date, tz))
    : getWorkingHoursForDate(null, bizWh, date, tz);

  const now = new Date();
  const slots: { time: string; iso: string; employee_id: number; employee_name: string }[] = [];
  const dur = service.duration_min;

  if (!window) {
    return res.json({ slots, service: { id: service.id, name: service.name, price: service.price, duration_min: service.duration_min } });
  }
  const dayStart = window.startMs;
  const dayEnd = window.endMs;

  // citas existentes del día para el caso de especialista fijo
  const existing = employeeId
    ? (getExistingBusy(db, [employeeId], date, tz).get(employeeId) ?? [])
    : [];

  for (let t = dayStart; t + dur * 60000 <= dayEnd; t += 30 * 60000) {
    if (t < now.getTime() + 30 * 60000) continue; // pasado / demasiado pronto
    const slotStart = t;
    const slotEnd = t + dur * 60000;
    let chosen: { id: number; name: string } | null = null;
    if (useRanking) {
      chosen = pickBestSlotEmployee(candInfos, candBusyMap, bizWh, { name: service.name, category: service.category }, slotStart, slotEnd, tz);
    } else {
      const busy = existing.some((b) => overlapsRange(slotStart, slotEnd, b.startMs, b.endMs));
      if (!busy) {
        const emp = db.prepare(`SELECT name FROM employees WHERE id = ?`).get(employeeId) as any;
        chosen = { id: employeeId, name: emp?.name ?? "" };
      }
    }
    if (chosen) {
      slots.push({
        // La etiqueta se rinde en la tz del NEGOCIO: sin `timeZone` salía en la del
        // proceso (UTC en producción) y no coincidía con la hora real del `iso`.
        time: new Date(t).toLocaleTimeString("es-MX", { hour: "2-digit", minute: "2-digit", hour12: true, timeZone: tz }),
        iso: new Date(t).toISOString(),
        employee_id: chosen.id,
        employee_name: chosen.name,
      });
    }
    if (slots.length >= 40) break;
  }
  res.json({ slots, service: { id: service.id, name: service.name, price: service.price, duration_min: service.duration_min } });
});

function overlapsRange(aStart: number, aEnd: number, bStart: number, bEnd: number): boolean {
  return aStart < bEnd && aEnd > bStart;
}

// Create a booking from the public site (no auth): creates/finds client + appointment
bookingRouter.post("/:slug/book", (req, res) => {
  const biz = publicBusiness(req.params.slug);
  if (!biz || !biz.booking_enabled) return res.status(404).json({ error: "No disponible" });
  const tz = biz.timezone || DEFAULT_TZ;
  const { service_id, employee_id, start_at, client } = req.body ?? {};
  if (!service_id || !start_at || !client?.name) return res.status(400).json({ error: "Faltan datos (servicio, hora o nombre)" });
  const service = db.prepare(`SELECT * FROM services WHERE id = ? AND business_id = ? AND active = 1`).get(Number(service_id), biz.id) as any;
  if (!service) return res.status(400).json({ error: "Servicio no válido" });
  const start = new Date(start_at);
  if (isNaN(start.getTime()) || start.getTime() < Date.now()) return res.status(400).json({ error: "Fecha inválida" });

  const startMs = start.getTime();
  const endMs = startMs + service.duration_min * 60000;

  // ¿El cliente forzó especialista? Si auto_assign_specialist=1, se ignora al cliente y se auto-asigna.
  const autoAssignOn = !!biz.auto_assign_specialist;
  const clientChose = !autoAssignOn && employee_id ? Number(employee_id) : null;

  try {
    const result = runInTransaction(db, () => {
      // 1) Resolver especialista
      let empId: number | null = clientChose;
      let reasons: string[] = [];
      if (!empId) {
        const bizWh = parseWorkingHours(biz.working_hours) as any;
        const aa = autoAssign(db, {
          businessId: biz.id, serviceId: service.id, serviceName: service.name, serviceCategory: service.category,
          startMs, endMs, bizWh, tz,
        });
        if (!aa) throw { status: 409, error: "ESE_HORARIO_OCUPADO" };
        empId = aa.employeeId;
        reasons = aa.reasons;
      } else {
        // validar que ofrece el servicio
        const ok = db.prepare(`SELECT 1 FROM employee_services WHERE service_id = ? AND employee_id = ?`).get(service.id, empId);
        if (!ok) throw { status: 400, error: "El especialista no ofrece este servicio" };
        // guard anti doble reserva
        if (!isAvailable(db, empId, startMs, endMs, tz)) throw { status: 409, error: "ESE_HORARIO_OCUPADO" };
        reasons = ["Tu especialista elegido"];
      }

      // 2) Resolver cliente (por phone/email o crear)
      let clientId: number | null = null;
      const match = client.phone
        ? (db.prepare(`SELECT id FROM clients WHERE business_id = ? AND phone = ?`).get(biz.id, client.phone) as any)
        : client.email
        ? (db.prepare(`SELECT id FROM clients WHERE business_id = ? AND email = ?`).get(biz.id, client.email) as any)
        : null;
      if (match) clientId = match.id;
      else {
        const created = db
          .prepare(`INSERT INTO clients (business_id, name, email, phone, tags) VALUES (?, ?, ?, ?, 'Online') RETURNING id`)
          .get(biz.id, client.name, client.email || null, client.phone || null) as any;
        clientId = created.id;
      }

      // F18: soft no-show enforcement (nunca bloquea la reserva)
      const nsCount = (db.prepare("SELECT COUNT(*) c FROM appointments WHERE client_id = ? AND status = 'no_show'").get(clientId) as { c: number })?.c ?? 0;
      const ns = typeof nsCount === "number" ? nsCount : Number(nsCount) || 0;
      const riskFlag = ns >= 2;
      let notesBase = client.notes || "Reserva online";
      if (ns >= 3) {
        notesBase = `[Riesgo de no-show] ${notesBase}`;
        db.prepare(`UPDATE clients SET tags = 'Riesgo' WHERE id = ? AND (tags IS NULL OR tags NOT LIKE '%Riesgo%')`).run(clientId);
      }

      const endIso = new Date(endMs).toISOString().replace(/\.\d{3}Z$/, "Z");
      const startIso = new Date(startMs).toISOString().replace(/\.\d{3}Z$/, "Z");
      const appt = db
        .prepare(
          `INSERT INTO appointments (business_id, service_id, employee_id, client_id, start_at, end_at, status, price, notes)
           VALUES (?, ?, ?, ?, ?, ?, 'scheduled', ?, ?) RETURNING *`
        )
        .get(biz.id, Number(service_id), empId, clientId, startIso, endIso, service.price, notesBase) as any;

      const empName = (db.prepare(`SELECT name FROM employees WHERE id = ?`).get(empId) as any)?.name;
      return { appt, empId: empId as number, empName, reasons, clientCreated: !match, noShowCount: ns, riskFlag };
    });

    res.status(201).json({
      appointment: {
        id: result.appt.id,
        start_at: result.appt.start_at,
        end_at: result.appt.end_at,
        price: result.appt.price,
        service_name: service.name,
        employee_id: result.empId,
        employee_name: result.empName,
        reasons: result.reasons,
      },
      business: { name: biz.name, currency_symbol: biz.currency_symbol },
      client_created: result.clientCreated,
      no_show_count: result.noShowCount,
      risk_flag: result.riskFlag,
    });
  } catch (e: any) {
    if (e && typeof e === "object" && e.status) {
      return res.status(e.status).json({ error: e.error === "ESE_HORARIO_OCUPADO" ? "Esa hora acaba de ocuparse, elige otra." : e.error });
    }
    throw e;
  }
});