import { Router, type Request, type Response } from "express";
import type { DB } from "../database.js";

export function analyticsRoutes(db: DB): Router {
  const router = Router();

  // KPIs gerais
  router.get("/summary", (_req: Request, res: Response) => {
    const total = db
      .prepare(`SELECT COUNT(*) as c FROM sessions`)
      .get() as { c: number };
    const completed = db
      .prepare(`SELECT COUNT(*) as c FROM sessions WHERE status = 'completed'`)
      .get() as { c: number };
    const today = db
      .prepare(
        `SELECT COUNT(*) as c FROM sessions WHERE date(started_at) = date('now')`,
      )
      .get() as { c: number };

    res.json({
      total_sessions: total.c,
      completed_sessions: completed.c,
      sessions_today: today.c,
      completion_rate:
        total.c > 0 ? Number(((completed.c / total.c) * 100).toFixed(1)) : 0,
    });
  });

  // Funil por bloco — drop-off
  router.get("/funnel", (_req: Request, res: Response) => {
    const rows = db
      .prepare(
        `SELECT block_id, COUNT(*) as views,
           SUM(CASE WHEN type='block_answered' THEN 1 ELSE 0 END) as answers
         FROM events
         WHERE block_id IS NOT NULL
         GROUP BY block_id
         ORDER BY views DESC`,
      )
      .all() as Array<{ block_id: string; views: number; answers: number }>;

    res.json(
      rows.map((r) => ({
        block_id: r.block_id,
        views: r.views,
        answers: r.answers,
        drop_off_rate:
          r.views > 0
            ? Number((((r.views - r.answers) / r.views) * 100).toFixed(1))
            : 0,
      })),
    );
  });

  // Sessoes recentes
  router.get("/sessions", (req: Request, res: Response) => {
    const limit = Math.min(Number(req.query.limit ?? 50), 200);
    const rows = db
      .prepare(
        `SELECT id, visitor_id, current_block_id, status, started_at, completed_at, utm_source, utm_campaign
         FROM sessions ORDER BY started_at DESC LIMIT ?`,
      )
      .all(limit);
    res.json(rows);
  });

  return router;
}
