AgendaPro/platform/routes/clients.ts

import { Router } from "express";
import { pool, withTx } from "../db/pool.ts";
import { normalizePhone } from "../lib/phone.ts";
import { writeAudit } from "../lib/audit.ts";
import { err, h, type AuthedRequest } from "../lib/auth.ts";

export const clientsRouter = Router();

const COLS = `id, business_id, name, email, phone, phone_e164, contactable,
              birth_date, notes, tags, source_channel, created_at,
              crm_contact_id, crm_synced_at, crm_source, crm_tags,
              attr_session_source, attr_medium, attr_campaign, attr_campaign_id,
              attr_utm_source, attr_utm_medium, attr_utm_content, attr_ad_id,
              attr_referrer`;

clientsRouter.get(
  "/",
  h(async (req: AuthedRequest, res) => {
    const q = (req.query.q as string | undefined)?.trim();
    const bid = req.user!.business_id;

    if (!q) {
      const { rows } = await pool.query(
        `SELECT ${COLS} FROM clients
          WHERE business_id = $1 AND deleted_at IS NULL
          ORDER BY name LIMIT 50`,
        [bid]
      );
      res.json({ clients: rows });
      return;
    }

    // Se busca por tres vías a la vez: el nombre, el teléfono tal cual se guardó,
    // y el normalizado. La tercera es la que hace que teclear "5588887777"
    // encuentre a quien está guardada como "+52 55 8888 7777".
    const like = `%${q}%`;
    const e164 = normalizePhone(q);
    const { rows } = await pool.query(
      `SELECT ${COLS} FROM clients
        WHERE business_id = $1 AND deleted_at IS NULL
          AND (name ILIKE $2 OR phone ILIKE $2 OR email ILIKE $2
               OR ($3::text IS NOT NULL AND phone_e164 = $3))
        ORDER BY name LIMIT 50`,
      [bid, like, e164]
    );
    res.json({ clients: rows });
  })
);

clientsRouter.get(
  "/:id",
  h(async (req: AuthedRequest, res) => {
    const id = Number(req.params.id);
    const bid = req.user!.business_id;
    const { rows } = await pool.query(
      `SELECT ${COLS} FROM clients
        WHERE id = $1 AND business_id = $2 AND deleted_at IS NULL`,
      [id, bid]
    );
    if (!rows[0]) {
      err(res, 404, "Clienta no encontrada");
      return;
    }

    // Las visitas salen de `visits`, no de las citas: una cita es una
    // intención y una visita es el hecho consumado. Contar citas como visitas
    // infla el historial con gente que no vino.
    const v = await pool.query(
      `SELECT count(*)::int AS visits,
              COALESCE(sum(total_charged),0)::float AS total_spent,
              max(occurred_at) AS last_visit
         FROM visits WHERE business_id = $1 AND client_id = $2`,
      [bid, id]
    );
    const ns = await pool.query(
      `SELECT count(*)::int AS c FROM appointments
        WHERE business_id = $1 AND client_id = $2 AND status = 'no_show'`,
      [bid, id]
    );
    const visits = v.rows[0].visits as number;
    const total = v.rows[0].total_spent as number;

    res.json({
      client: {
        ...rows[0],
        stats: {
          visits,
          total_spent: Math.round(total * 100) / 100,
          last_visit: v.rows[0].last_visit,
          avg_ticket: visits ? Math.round((total / visits) * 100) / 100 : 0,
          no_show_count: ns.rows[0].c,
        },
      },
    });
  })
);

clientsRouter.post(
  "/",
  h(async (req: AuthedRequest, res) => {
    const { name, phone, email, notes, source_channel } = req.body ?? {};
    if (!name || typeof name !== "string" || !name.trim()) {
      err(res, 400, "El nombre es obligatorio");
      return;
    }
    const bid = req.user!.business_id;
    const e164 = normalizePhone(phone);

    // Se pregunta antes de insertar para poder devolver la ficha existente. El
    // índice único sigue siendo la garantía real: entre esta consulta y el
    // INSERT cabe otra alta, y por eso abajo también se atrapa el 23505.
    if (e164) {
      const dup = await pool.query(
        `SELECT ${COLS} FROM clients
          WHERE business_id = $1 AND phone_e164 = $2 AND deleted_at IS NULL`,
        [bid, e164]
      );
      if (dup.rows[0]) {
        res.status(409).json({
          error: "Ya existe una clienta con ese teléfono",
          existing: dup.rows[0],
        });
        return;
      }
    }

    try {
      const client = await withTx(async (c) => {
        const { rows } = await c.query(
          `INSERT INTO clients
             (business_id, name, email, phone, phone_e164, notes, source_channel)
           VALUES ($1,$2,$3,$4,$5,$6,$7) RETURNING ${COLS}`,
          [
            bid,
            name.trim(),
            email || null,
            phone || null,
            e164,
            notes || null,
            source_channel || null,
          ]
        );
        await writeAudit(c, {
          businessId: bid,
          actorUserId: req.user!.id,
          entity: "clients",
          entityId: rows[0].id,
          action: "create",
          after: rows[0],
          ip: req.ip ?? null,
        });
        return rows[0];
      });
      res.status(201).json({ client });
    } catch (e: any) {
      if (e.code === "23505") {
        const dup = await pool.query(
          `SELECT ${COLS} FROM clients WHERE business_id = $1 AND phone_e164 = $2`,
          [bid, e164]
        );
        res.status(409).json({
          error: "Ya existe una clienta con ese teléfono",
          existing: dup.rows[0] ?? null,
        });
        return;
      }
      throw e;
    }
  })
);