kanban/backend/reports.go

package main

import (
	"database/sql"
	"fmt"
	"sort"
	"time"
)

// DailyReport — agregaciones por dia natural (TZ del servidor a menos que el
// caller pase una TZ explicita). Issue 0093.
type DailyReport struct {
	Date    string `json:"date"`
	TZ      string `json:"tz"`
	StartTs string `json:"start_ts"`
	EndTs   string `json:"end_ts"`

	KPIs DailyKPIs `json:"kpis"`

	TopAssigneesDone    []UserCount      `json:"top_assignees_done"`
	TopAssigneesCreated []UserCount      `json:"top_assignees_created"`
	TopRequestersAdded  []NamedCount     `json:"top_requesters_added"`
	TopRequestersDone   []NamedCount     `json:"top_requesters_done"`
	DoneCards           []DoneCard       `json:"done_cards"`
	ReopenedCards       []ReopenedEntry  `json:"reopened_cards"`
	StaleCards          StaleBuckets     `json:"stale_cards"`
	LeadTime            LeadTimeStats    `json:"lead_time"`
	HourlyMoves         [24]int          `json:"hourly_moves"`
	Deadlines           DeadlineSummary  `json:"deadlines"`
	TagsDone            []NamedCount     `json:"tags_done"`
	ArchivedToday       int              `json:"archived_today"`
}

type DailyKPIs struct {
	Done             int   `json:"done"`
	Created          int   `json:"created"`
	Moves            int   `json:"moves"`
	BlockedMs        int64 `json:"blocked_ms"`
	DeadlinesMet     int   `json:"deadlines_met"`
	DeadlinesMissed  int   `json:"deadlines_missed"`
	Reopened         int   `json:"reopened"`
	ArchivedAuto     int   `json:"archived_auto"`
	ArchivedManual   int   `json:"archived_manual"`
}

type UserCount struct {
	UserID string `json:"user_id"`
	Name   string `json:"name"`
	Count  int    `json:"count"`
}

type NamedCount struct {
	Name  string `json:"name"`
	Count int    `json:"count"`
}

type DoneCard struct {
	ID           string   `json:"id"`
	SeqNum       int      `json:"seq_num"`
	Title        string   `json:"title"`
	Requester    string   `json:"requester"`
	AssigneeID   *string  `json:"assignee_id"`
	AssigneeName *string  `json:"assignee_name"`
	Tags         []string `json:"tags"`
	ColumnID     string   `json:"column_id"`
	ColumnName   string   `json:"column_name"`
	CompletedAt  string   `json:"completed_at"`
	CreatedAt    string   `json:"created_at"`
	LeadTimeMs   int64    `json:"lead_time_ms"`
	Color        string   `json:"color"`
}

type ReopenedEntry struct {
	CardID       string  `json:"card_id"`
	Title        string  `json:"title"`
	SeqNum       int     `json:"seq_num"`
	FromColumn   string  `json:"from_column"`
	ToColumn     string  `json:"to_column"`
	Ts           string  `json:"ts"`
	ActorID      *string `json:"actor_id"`
	ActorName    *string `json:"actor_name"`
}

type StaleEntry struct {
	CardID     string `json:"card_id"`
	Title      string `json:"title"`
	SeqNum     int    `json:"seq_num"`
	ColumnID   string `json:"column_id"`
	ColumnName string `json:"column_name"`
	EnteredAt  string `json:"entered_at"`
	Days       int    `json:"days"`
}

type StaleBuckets struct {
	D7  []StaleEntry `json:"d7"`
	D14 []StaleEntry `json:"d14"`
	D30 []StaleEntry `json:"d30"`
}

type LeadTimeStats struct {
	AvgMs   int64 `json:"avg_ms"`
	P50Ms   int64 `json:"p50_ms"`
	P95Ms   int64 `json:"p95_ms"`
	Samples int   `json:"samples"`
}

type DeadlineSummary struct {
	Met    int               `json:"met"`
	Missed int               `json:"missed"`
	List   []DeadlineMissEntry `json:"list"`
}

type DeadlineMissEntry struct {
	CardID      string `json:"card_id"`
	Title       string `json:"title"`
	SeqNum      int    `json:"seq_num"`
	Deadline    string `json:"deadline"`
	CompletedAt string `json:"completed_at"`
	LateMs      int64  `json:"late_ms"`
}

// DailyReportFor computes the report for the local day specified by date+tz.
func (db *DB) DailyReportFor(date, tz string) (*DailyReport, error) {
	loc, err := time.LoadLocation(tz)
	if err != nil {
		loc = time.UTC
		tz = "UTC"
	}
	t, err := time.ParseInLocation("2006-01-02", date, loc)
	if err != nil {
		return nil, fmt.Errorf("invalid date %q: %w", date, err)
	}
	start := t
	end := t.Add(24 * time.Hour)
	startUTC := start.UTC().Format(time.RFC3339Nano)
	endUTC := end.UTC().Format(time.RFC3339Nano)

	r := &DailyReport{
		Date:    date,
		TZ:      tz,
		StartTs: startUTC,
		EndTs:   endUTC,
		StaleCards: StaleBuckets{
			D7:  []StaleEntry{},
			D14: []StaleEntry{},
			D30: []StaleEntry{},
		},
		Deadlines: DeadlineSummary{List: []DeadlineMissEntry{}},
		DoneCards:           []DoneCard{},
		ReopenedCards:       []ReopenedEntry{},
		TopAssigneesDone:    []UserCount{},
		TopAssigneesCreated: []UserCount{},
		TopRequestersAdded:  []NamedCount{},
		TopRequestersDone:   []NamedCount{},
		TagsDone:            []NamedCount{},
	}

	users, err := db.userNameMap()
	if err != nil {
		return nil, err
	}
	doneColIDs, doneColNames, err := db.doneColumnIDs()
	if err != nil {
		return nil, err
	}
	allColNames, err := db.allColumnNames()
	if err != nil {
		return nil, err
	}

	// --- Done cards ----------------------------------------------------------
	rows, err := db.conn.Query(`
		SELECT c.id, c.seq_num, c.title, c.requester, c.assignee_id, c.tags, c.column_id, c.completed_at, c.created_at, c.color, c.deadline
		FROM cards c
		WHERE c.completed_at IS NOT NULL
		  AND c.completed_at >= ? AND c.completed_at < ?
		  AND c.deleted_at IS NULL
		ORDER BY c.completed_at DESC
	`, startUTC, endUTC)
	if err != nil {
		return nil, err
	}
	leadSamples := []int64{}
	assigneeDoneCount := map[string]int{}
	requesterDoneCount := map[string]int{}
	tagCount := map[string]int{}
	for rows.Next() {
		var c DoneCard
		var assignee, deadline sql.NullString
		var tagsJSON string
		if err := rows.Scan(&c.ID, &c.SeqNum, &c.Title, &c.Requester, &assignee, &tagsJSON, &c.ColumnID, &c.CompletedAt, &c.CreatedAt, &c.Color, &deadline); err != nil {
			rows.Close()
			return nil, err
		}
		c.Tags = parseTags(tagsJSON)
		if assignee.Valid && assignee.String != "" {
			a := assignee.String
			c.AssigneeID = &a
			if n, ok := users[a]; ok {
				nm := n
				c.AssigneeName = &nm
			}
			assigneeDoneCount[a]++
		}
		if c.Requester != "" {
			requesterDoneCount[c.Requester]++
		}
		for _, tag := range c.Tags {
			tagCount[tag]++
		}
		c.ColumnName = allColNames[c.ColumnID]
		// Lead time created -> completed.
		if ct, err := time.Parse(time.RFC3339Nano, c.CreatedAt); err == nil {
			if compt, err := time.Parse(time.RFC3339Nano, c.CompletedAt); err == nil {
				c.LeadTimeMs = compt.Sub(ct).Milliseconds()
				if c.LeadTimeMs >= 0 {
					leadSamples = append(leadSamples, c.LeadTimeMs)
				}
			}
		}
		// Deadlines.
		if deadline.Valid && deadline.String != "" {
			if dlt, err := time.Parse(time.RFC3339Nano, deadline.String); err == nil {
				if compt, err := time.Parse(time.RFC3339Nano, c.CompletedAt); err == nil {
					if compt.After(dlt) {
						r.Deadlines.Missed++
						r.Deadlines.List = append(r.Deadlines.List, DeadlineMissEntry{
							CardID: c.ID, Title: c.Title, SeqNum: c.SeqNum,
							Deadline: deadline.String, CompletedAt: c.CompletedAt,
							LateMs: compt.Sub(dlt).Milliseconds(),
						})
					} else {
						r.Deadlines.Met++
					}
				}
			}
		}
		r.DoneCards = append(r.DoneCards, c)
	}
	rows.Close()
	r.KPIs.Done = len(r.DoneCards)
	r.LeadTime = computeLeadTime(leadSamples)
	r.TopAssigneesDone = topUsersFromCount(assigneeDoneCount, users, 5)
	r.TopRequestersDone = topNamedFromCount(requesterDoneCount, 5)
	r.TagsDone = topNamedFromCount(tagCount, 10)

	_ = doneColIDs
	_ = doneColNames

	// --- Created (card_events kind=created) ----------------------------------
	rows, err = db.conn.Query(`
		SELECT e.card_id, e.actor_id, COALESCE(c.requester, '')
		FROM card_events e
		LEFT JOIN cards c ON c.id = e.card_id
		WHERE e.kind = 'created'
		  AND e.created_at >= ? AND e.created_at < ?
	`, startUTC, endUTC)
	if err != nil {
		return nil, err
	}
	assigneeCreatedCount := map[string]int{}
	requesterAddedCount := map[string]int{}
	createdN := 0
	for rows.Next() {
		var cardID string
		var actor sql.NullString
		var requester string
		if err := rows.Scan(&cardID, &actor, &requester); err != nil {
			rows.Close()
			return nil, err
		}
		createdN++
		if actor.Valid && actor.String != "" {
			assigneeCreatedCount[actor.String]++
		}
		if requester != "" {
			requesterAddedCount[requester]++
		}
	}
	rows.Close()
	r.KPIs.Created = createdN
	r.TopAssigneesCreated = topUsersFromCount(assigneeCreatedCount, users, 5)
	r.TopRequestersAdded = topNamedFromCount(requesterAddedCount, 5)

	// --- Moves del dia + hourly + reopened -----------------------------------
	// Reopened = card que el dia X entro a una columna NO done HABIENDO estado
	// en una done previa. Detectable comparando entered_at del dia con la
	// entrada previa (mismo card_id).
	rows, err = db.conn.Query(`
		SELECT h.card_id, h.column_id, h.entered_at, h.actor_id, c.title, c.seq_num
		FROM card_column_history h
		JOIN cards c ON c.id = h.card_id
		WHERE h.entered_at >= ? AND h.entered_at < ?
		  AND c.deleted_at IS NULL
		ORDER BY h.entered_at ASC
	`, startUTC, endUTC)
	if err != nil {
		return nil, err
	}
	hourly := [24]int{}
	type moveRow struct {
		cardID, columnID, enteredAt, title string
		actor                              sql.NullString
		seqNum                             int
	}
	var moves []moveRow
	for rows.Next() {
		var m moveRow
		if err := rows.Scan(&m.cardID, &m.columnID, &m.enteredAt, &m.actor, &m.title, &m.seqNum); err != nil {
			rows.Close()
			return nil, err
		}
		moves = append(moves, m)
		if ts, err := time.Parse(time.RFC3339Nano, m.enteredAt); err == nil {
			h := ts.In(loc).Hour()
			if h >= 0 && h < 24 {
				hourly[h]++
			}
		}
	}
	rows.Close()
	r.HourlyMoves = hourly
	r.KPIs.Moves = len(moves)
	for _, m := range moves {
		// Solo interesa si la columna actual NO es done.
		isDone := doneColIDs[m.columnID]
		if isDone {
			continue
		}
		// Hubo entrada previa en una columna done?
		prevWasDone, prevColID := db.previousColumnWasDone(m.cardID, m.enteredAt, doneColIDs)
		if prevWasDone {
			entry := ReopenedEntry{
				CardID:     m.cardID,
				Title:      m.title,
				SeqNum:     m.seqNum,
				FromColumn: allColNames[prevColID],
				ToColumn:   allColNames[m.columnID],
				Ts:         m.enteredAt,
			}
			if m.actor.Valid && m.actor.String != "" {
				a := m.actor.String
				entry.ActorID = &a
				if n, ok := users[a]; ok {
					nm := n
					entry.ActorName = &nm
				}
			}
			r.ReopenedCards = append(r.ReopenedCards, entry)
		}
	}
	r.KPIs.Reopened = len(r.ReopenedCards)

	// --- Stale buckets (cards activas hoy con N dias en misma columna) -------
	r.StaleCards = db.staleBucketsAt(end, doneColIDs, allColNames)

	// --- Bloqueado ms (lock_history que solapa con el dia) -------------------
	r.KPIs.BlockedMs = db.blockedMsInRange(startUTC, endUTC)

	// --- Archivadas hoy ------------------------------------------------------
	var autoN, manualN int
	if err := db.conn.QueryRow(`
		SELECT COUNT(*) FROM cards
		WHERE archived_at IS NOT NULL
		  AND archived_at >= ? AND archived_at < ?
		  AND deleted_at IS NULL
	`, startUTC, endUTC).Scan(&autoN); err == nil {
		// Heuristica: auto vs manual no se diferencia (no log explicito). Si
		// la columna actual es is_done, asumimos auto. Mejor que nada.
		_ = manualN
		r.KPIs.ArchivedAuto = autoN
		r.ArchivedToday = autoN
	}
	r.KPIs.DeadlinesMet = r.Deadlines.Met
	r.KPIs.DeadlinesMissed = r.Deadlines.Missed

	return r, nil
}

func (db *DB) userNameMap() (map[string]string, error) {
	rows, err := db.conn.Query(`SELECT id, COALESCE(display_name,''), username FROM users`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := map[string]string{}
	for rows.Next() {
		var id, dn, un string
		if err := rows.Scan(&id, &dn, &un); err != nil {
			return nil, err
		}
		if dn != "" {
			out[id] = dn
		} else {
			out[id] = un
		}
	}
	return out, nil
}

func (db *DB) doneColumnIDs() (map[string]bool, map[string]string, error) {
	rows, err := db.conn.Query(`SELECT id, name FROM columns WHERE is_done=1`)
	if err != nil {
		return nil, nil, err
	}
	defer rows.Close()
	ids := map[string]bool{}
	names := map[string]string{}
	for rows.Next() {
		var id, n string
		if err := rows.Scan(&id, &n); err != nil {
			return nil, nil, err
		}
		ids[id] = true
		names[id] = n
	}
	return ids, names, nil
}

func (db *DB) allColumnNames() (map[string]string, error) {
	rows, err := db.conn.Query(`SELECT id, name FROM columns`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := map[string]string{}
	for rows.Next() {
		var id, n string
		if err := rows.Scan(&id, &n); err != nil {
			return nil, err
		}
		out[id] = n
	}
	return out, nil
}

// previousColumnWasDone returns whether the entry of `cardID` immediately
// before `enteredAt` was in a done column.
func (db *DB) previousColumnWasDone(cardID, enteredAt string, doneColIDs map[string]bool) (bool, string) {
	var colID string
	err := db.conn.QueryRow(`
		SELECT column_id FROM card_column_history
		WHERE card_id=? AND entered_at < ?
		ORDER BY entered_at DESC
		LIMIT 1
	`, cardID, enteredAt).Scan(&colID)
	if err != nil {
		return false, ""
	}
	return doneColIDs[colID], colID
}

func (db *DB) staleBucketsAt(asOf time.Time, doneColIDs map[string]bool, colNames map[string]string) StaleBuckets {
	out := StaleBuckets{D7: []StaleEntry{}, D14: []StaleEntry{}, D30: []StaleEntry{}}
	rows, err := db.conn.Query(`
		SELECT h.card_id, c.title, c.seq_num, h.column_id, h.entered_at
		FROM card_column_history h
		JOIN cards c ON c.id = h.card_id
		WHERE h.exited_at IS NULL
		  AND c.deleted_at IS NULL
		  AND c.archived_at IS NULL
	`)
	if err != nil {
		return out
	}
	defer rows.Close()
	for rows.Next() {
		var s StaleEntry
		if err := rows.Scan(&s.CardID, &s.Title, &s.SeqNum, &s.ColumnID, &s.EnteredAt); err != nil {
			continue
		}
		// Skip done columns (esos se auto-archivan; no son "estancados" activos).
		if doneColIDs[s.ColumnID] {
			continue
		}
		entered, err := time.Parse(time.RFC3339Nano, s.EnteredAt)
		if err != nil {
			continue
		}
		days := int(asOf.Sub(entered).Hours() / 24)
		if days < 7 {
			continue
		}
		s.Days = days
		s.ColumnName = colNames[s.ColumnID]
		switch {
		case days >= 30:
			out.D30 = append(out.D30, s)
		case days >= 14:
			out.D14 = append(out.D14, s)
		default:
			out.D7 = append(out.D7, s)
		}
	}
	sort.Slice(out.D7, func(i, j int) bool { return out.D7[i].Days > out.D7[j].Days })
	sort.Slice(out.D14, func(i, j int) bool { return out.D14[i].Days > out.D14[j].Days })
	sort.Slice(out.D30, func(i, j int) bool { return out.D30[i].Days > out.D30[j].Days })
	return out
}

func (db *DB) blockedMsInRange(startUTC, endUTC string) int64 {
	// Para cada periodo de lock, contar la interseccion con [start,end].
	rows, err := db.conn.Query(`
		SELECT locked_at, COALESCE(unlocked_at, ?) FROM card_lock_history
		WHERE locked_at < ? AND COALESCE(unlocked_at, ?) > ?
	`, endUTC, endUTC, endUTC, startUTC)
	if err != nil {
		return 0
	}
	defer rows.Close()
	start, _ := time.Parse(time.RFC3339Nano, startUTC)
	end, _ := time.Parse(time.RFC3339Nano, endUTC)
	var total time.Duration
	for rows.Next() {
		var lstr, ustr string
		if err := rows.Scan(&lstr, &ustr); err != nil {
			continue
		}
		l, err := time.Parse(time.RFC3339Nano, lstr)
		if err != nil {
			continue
		}
		u, err := time.Parse(time.RFC3339Nano, ustr)
		if err != nil {
			continue
		}
		if l.Before(start) {
			l = start
		}
		if u.After(end) {
			u = end
		}
		if u.After(l) {
			total += u.Sub(l)
		}
	}
	return total.Milliseconds()
}

func topUsersFromCount(m map[string]int, names map[string]string, k int) []UserCount {
	out := make([]UserCount, 0, len(m))
	for id, n := range m {
		out = append(out, UserCount{UserID: id, Name: names[id], Count: n})
	}
	sort.Slice(out, func(i, j int) bool { return out[i].Count > out[j].Count })
	if len(out) > k {
		out = out[:k]
	}
	return out
}

func topNamedFromCount(m map[string]int, k int) []NamedCount {
	out := make([]NamedCount, 0, len(m))
	for n, c := range m {
		out = append(out, NamedCount{Name: n, Count: c})
	}
	sort.Slice(out, func(i, j int) bool { return out[i].Count > out[j].Count })
	if len(out) > k {
		out = out[:k]
	}
	return out
}

func computeLeadTime(samples []int64) LeadTimeStats {
	if len(samples) == 0 {
		return LeadTimeStats{}
	}
	sorted := make([]int64, len(samples))
	copy(sorted, samples)
	sort.Slice(sorted, func(i, j int) bool { return sorted[i] < sorted[j] })
	var sum int64
	for _, v := range sorted {
		sum += v
	}
	p := func(q float64) int64 {
		if len(sorted) == 0 {
			return 0
		}
		idx := int(float64(len(sorted)-1) * q)
		return sorted[idx]
	}
	return LeadTimeStats{
		AvgMs:   sum / int64(len(sorted)),
		P50Ms:   p(0.5),
		P95Ms:   p(0.95),
		Samples: len(sorted),
	}
}