package handlers
import (
"fmt"
"html/template"
"math"
"net/http"
"time"
"go-crm/internal/templates"
)
type serviceCount struct {
Name string
Count int
}
type reportData struct {
TotalLeads int
ServiceCounts []serviceCount
StatusCounts []serviceCount
TotalScheduled int
ConversionRate float64
TotalRevenue float64
TotalSales int
AverageTicket float64
TopServices []serviceCount
MonthLabel string
}
func (a *App) MonthlyReport(w http.ResponseWriter, r *http.Request) {
accountID, ok := a.requireAuth(w, r)
if !ok {
return
}
clientID := a.clientIDForAccount(accountID)
if clientID == 0 {
http.Error(w, "No client found for account", http.StatusBadRequest)
return
}
now := time.Now()
monthStart := time.Date(now.Year(), now.Month(), 1, 0, 0, 0, 0, now.Location()).Unix()
nextMonth := time.Date(now.Year(), now.Month()+1, 1, 0, 0, 0, 0, now.Location()).Unix()
rd := reportData{MonthLabel: now.Format("January 2006")}
a.DB.QueryRow(
"SELECT COUNT(*) FROM leads WHERE client_id = ? AND created_at >= ? AND created_at < ?",
clientID, monthStart, nextMonth,
).Scan(&rd.TotalLeads)
rows, err := a.DB.Query(
`SELECT service_interest, COUNT(*) as cnt FROM leads
WHERE client_id = ? AND created_at >= ? AND created_at < ?
GROUP BY service_interest ORDER BY cnt DESC`,
clientID, monthStart, nextMonth,
)
if err == nil {
defer rows.Close()
for rows.Next() {
var sc serviceCount
rows.Scan(&sc.Name, &sc.Count)
rd.ServiceCounts = append(rd.ServiceCounts, sc)
}
}
statusRows, err := a.DB.Query(
`SELECT status, COUNT(*) as cnt FROM leads
WHERE client_id = ? AND created_at >= ? AND created_at < ?
GROUP BY status ORDER BY cnt DESC`,
clientID, monthStart, nextMonth,
)
if err == nil {
defer statusRows.Close()
for statusRows.Next() {
var sc serviceCount
statusRows.Scan(&sc.Name, &sc.Count)
rd.StatusCounts = append(rd.StatusCounts, sc)
}
}
a.DB.QueryRow(
"SELECT COUNT(*) FROM leads WHERE client_id = ? AND status = 'Agendou' AND created_at >= ? AND created_at < ?",
clientID, monthStart, nextMonth,
).Scan(&rd.TotalScheduled)
if rd.TotalLeads > 0 {
rd.ConversionRate = math.Round(float64(rd.TotalScheduled)/float64(rd.TotalLeads)*100*10) / 10
}
a.DB.QueryRow(
`SELECT COALESCE(SUM(amount),0), COUNT(*) FROM payments
WHERE client_id = ? AND has_paid = 1 AND created_at >= ? AND created_at < ?`,
clientID, monthStart, nextMonth,
).Scan(&rd.TotalRevenue, &rd.TotalSales)
if rd.TotalSales > 0 {
rd.AverageTicket = math.Round(rd.TotalRevenue/float64(rd.TotalSales)*100) / 100
}
topRows, err := a.DB.Query(
`SELECT s.name, COUNT(*) as cnt
FROM payments p
JOIN scheduling sch ON p.schedule_id = sch.schedule_id
JOIN services s ON sch.service_id = s.service_id
WHERE p.client_id = ? AND p.has_paid = 1 AND p.created_at >= ? AND p.created_at < ?
GROUP BY s.name ORDER BY cnt DESC LIMIT 5`,
clientID, monthStart, nextMonth,
)
if err == nil {
defer topRows.Close()
for topRows.Next() {
var sc serviceCount
topRows.Scan(&sc.Name, &sc.Count)
rd.TopServices = append(rd.TopServices, sc)
}
}
var reviewCount int
a.DB.QueryRow("SELECT COUNT(*) FROM leads WHERE client_id = ? AND needs_review = 1", clientID).Scan(&reviewCount)
buf := templates.BufRender()
fmt.Fprint(buf, templates.PageHeader("Monthly Report", fmt.Sprintf("Performance for %s", rd.MonthLabel)))
// Overview KPIs
fmt.Fprintf(buf, ``)
fmt.Fprint(buf, templates.KPICard("Total Leads", fmt.Sprintf("%d", rd.TotalLeads), "", "amber", `
`, 1))
fmt.Fprint(buf, templates.KPICard("Scheduled", fmt.Sprintf("%d", rd.TotalScheduled), fmt.Sprintf("%.1f%% conversion", rd.ConversionRate), "emerald", `
`, 2))
fmt.Fprint(buf, templates.KPICard("Revenue", fmt.Sprintf("R$ %.2f", rd.TotalRevenue), fmt.Sprintf("%d sales", rd.TotalSales), "sky", `
`, 3))
fmt.Fprintf(buf, `
`)
miniCards := []struct{ label, value string }{
{"Total Sales", fmt.Sprintf("%d", rd.TotalSales)},
{"Average Ticket", fmt.Sprintf("R$ %.2f", rd.AverageTicket)},
{"Pending Review", fmt.Sprintf("%d", reviewCount)},
}
for _, mc := range miniCards {
fmt.Fprintf(buf, `
`, mc.label, mc.value)
}
fmt.Fprintf(buf, `
`)
// Service interest table
serviceTable := renderServiceTable(rd.ServiceCounts, rd.TotalLeads)
fmt.Fprint(buf, templates.SectionCard("Leads by Service Interest", "Distribution of service requests", template.HTML(serviceTable)))
// Status table
statusTable := renderServiceTable(rd.StatusCounts, rd.TotalLeads)
fmt.Fprint(buf, templates.SectionCard("Leads by Status", "Pipeline breakdown", template.HTML(statusTable)))
fmt.Fprintf(buf, `
`)
// Top services
if len(rd.TopServices) > 0 {
topTable := renderTopServicesTable(rd.TopServices)
fmt.Fprintf(buf, `%s
`, templates.SectionCard("Top 5 Services Sold", "Best performing services this month", template.HTML(topTable)))
}
w.Header().Set("Content-Type", "text/html; charset=utf-8")
templates.WritePage(w, buf, "Monthly Report", "report")
}
func renderServiceTable(counts []serviceCount, total int) template.HTML {
if len(counts) == 0 {
return template.HTML(`No data for this month.
`)
}
out := string(templates.TableStart([]string{"Name", "Quantity", "Percentage"}))
for _, sc := range counts {
pct := 0.0
if total > 0 {
pct = math.Round(float64(sc.Count)/float64(total)*100*10) / 10
}
barWidth := int(pct)
if barWidth > 100 {
barWidth = 100
}
out += fmt.Sprintf(`
| %s |
%d |
|
`, htmlEscape(sc.Name), sc.Count, barWidth, pct)
}
out += string(templates.TableEnd())
return template.HTML(out)
}
func renderTopServicesTable(counts []serviceCount) template.HTML {
if len(counts) == 0 {
return template.HTML(`No sales data for this month.
`)
}
out := string(templates.TableStart([]string{"Service", "Sales"}))
for _, sc := range counts {
out += fmt.Sprintf(`| %s | %d |
`, htmlEscape(sc.Name), sc.Count)
}
out += string(templates.TableEnd())
return template.HTML(out)
}
// MonthlyReport is the package-level shim.
func MonthlyReport(w http.ResponseWriter, r *http.Request) {
(&App{DB: DB, WAConnector: WAConnector}).MonthlyReport(w, r)
}