package alert

import (
	"context"
	"fmt"
	"math"
	"sort"
	"strings"
	"time"
)

// ResolutionCounts is the handled/self_recovered/noise breakdown behind every
// resolution-mix row (daily and per-rule) and the window-wide total the Alert
// Quality KPI tiles are rated off. Total is carried alongside the three
// resolutions rather than left for the client to sum, so a resolution value
// this package does not know about is still counted somewhere instead of
// silently dropped.
type ResolutionCounts struct {
	Handled       int `json:"handled" validate:"required"`
	SelfRecovered int `json:"self_recovered" validate:"required"`
	Noise         int `json:"noise" validate:"required"`
	Expired       int `json:"expired" validate:"required"`
	Total         int `json:"total" validate:"required"`
}

// addResolution folds one (resolution, count) row into a running total.
func addResolution(c *ResolutionCounts, resolution string, count int) {
	switch resolution {
	case ResolutionHandled:
		c.Handled += count
	case ResolutionSelfRecovered:
		c.SelfRecovered += count
	case ResolutionNoise:
		c.Noise += count
	case ResolutionExpired:
		c.Expired += count
	}
	c.Total += count
}

// DailyResolutionMix is one UTC+8 calendar day's resolution mix — the input
// to both the "Daily resolution mix" chart and the "Noise rate trend" line,
// whose rate (Noise / Total) the client derives rather than the server
// shipping a precomputed percentage.
type DailyResolutionMix struct {
	Day string `json:"day" validate:"required"` // YYYY-MM-DD, UTC+8 calendar day
	ResolutionCounts
}

// RuleResolution is one alertname's resolution mix. Stats.RuleMix carries every
// rule in the window, busiest first; the page ranks and truncates client-side.
type RuleResolution struct {
	Alertname string `json:"alertname" validate:"required"`
	ResolutionCounts
}

// TeamLoad is one oncall team's alert count in the window, split the way the
// on-call load chart shows it: a page at night carries differently than a
// page in the day, and a ticket never pages anyone so it gets its own column.
//
// Grouping by oncall_team rather than by assignee is deliberate. The label is
// on every alert whether or not alertstore owns it (see docs/design.md "The
// Cutover"), whereas an assignee is resolved only for owned alerts — so a
// per-assignee split silently dropped every shadow alert and showed data only
// for the slice that had been cut over. Keying on the team the alert was routed
// to keeps this chart on the same full-feed population as Total alerts and
// Night pages beside it. Alerts whose rule sets no oncall_team fold into
// UnroutedTeam.
type TeamLoad struct {
	OncallTeam string `json:"oncall_team" validate:"required"`
	NightPage  int    `json:"night_page" validate:"required"`
	DayPage    int    `json:"day_page" validate:"required"`
	Ticket     int    `json:"ticket" validate:"required"`
	Total      int    `json:"total" validate:"required"`
}

// UnroutedTeam is the bucket for alerts whose rule sets no oncall_team label —
// kept as its own row rather than dropped, since "alerts nobody's rotation
// owns" is exactly the gap an on-call load view should surface.
const UnroutedTeam = "(unrouted)"

// HourlyBucket is the alert count for one UTC+8 hour of day.
type HourlyBucket struct {
	Hour  int `json:"hour" validate:"required"`
	Count int `json:"count" validate:"required"`
}

// Stats is the aggregated response behind the Dashboards page: every number
// on it is computed in SQL over the alert table for one time window
// (created_at > From && created_at <= To), never by loading rows into Go.
// Bucketing reads created_at in UTC+8 via DATE_ADD(created_at, INTERVAL 8
// HOUR) rather than CONVERT_TZ, since TiDB is not guaranteed to ship the
// timezone tables CONVERT_TZ needs — the same choice the rest of the service
// makes for "night".
type Stats struct {
	DailyResolutionMix []DailyResolutionMix `json:"daily_resolution_mix" validate:"required"`
	RuleMix            []RuleResolution     `json:"rule_mix" validate:"required"`
	TeamLoad           []TeamLoad           `json:"team_load" validate:"required"`
	HourlyHistogram    []HourlyBucket       `json:"hourly_histogram" validate:"required"`
	// Teams is every distinct non-empty oncall_team label seen in the window,
	// always computed unfiltered by the team the caller passed in — so the
	// dropdown that reads it keeps every option even once one of them is
	// selected, rather than collapsing to just the chosen team.
	Teams []string `json:"teams" validate:"required"`
	// ResolvedTotals is the window-wide sum of DailyResolutionMix, spelled out
	// so the Alert Quality KPI tiles (resolved count, noise rate,
	// self-recovered rate, handled rate) do not each re-sum the daily array.
	ResolvedTotals ResolutionCounts `json:"resolved_totals" validate:"required"`
	TotalAlerts    int              `json:"total_alerts" validate:"required"`
	NightPages     int              `json:"night_pages" validate:"required"`
	// SLA is the Response SLA section's numbers — see SLA's own doc comment.
	SLA SLA `json:"sla" validate:"required"`
}

// oncallTeamExpr reads the oncall_team label out of the JSON attrs column —
// the same label oncall routing and mute/subscription filters key on (see
// docs/design.md), matched raw and exactly, with no default-team fallback.
// No index backs this path (attrs is JSON; only the promoted columns are
// generated/indexed), so every query that filters or groups by it evaluates
// the expression per row rather than via an index. Acceptable here because
// the window is already bounded — this is a BFF endpoint the Dashboards page
// calls with at most a 90-day custom range — and not something the public
// /alerts API does.
const oncallTeamExpr = "JSON_UNQUOTE(JSON_EXTRACT(attrs, '$.labels.oncall_team'))"

// appendTeamFilter adds an oncall-team equality clause to a WHERE-complete
// query when team is non-empty, leaving q/args untouched otherwise. Split out
// so the six queries Stats runs apply the filter identically, and so the
// clause-building itself can be unit tested without a database.
func appendTeamFilter(q string, args []any, team string) (string, []any) {
	if team == "" {
		return q, args
	}
	return q + " AND " + oncallTeamExpr + " = ?", append(args, team)
}

// AppendTeamFilter is appendTeamFilter for other packages' windowed queries
// over the alert table (pkg/agent).
func AppendTeamFilter(q string, args []any, team string) (string, []any) {
	return appendTeamFilter(q, args, team)
}

// appendInFilter adds a `col IN (?, ...)` clause to a WHERE-complete query for
// the non-empty members of vals, leaving q/args untouched when vals is empty.
// col is spliced in literally, so it must be a trusted column name or SQL
// expression (never user input) — only the values are parameterised. This is
// the multi-select counterpart to appendTeamFilter: the alert list's class,
// state and team controls each accept a set, and one member matches.
func appendInFilter(q string, args []any, col string, vals []string) (string, []any) {
	if len(vals) == 0 {
		return q, args
	}
	placeholders := make([]string, len(vals))
	for i, v := range vals {
		placeholders[i] = "?"
		args = append(args, v)
	}
	return q + " AND " + col + " IN (" + strings.Join(placeholders, ", ") + ")", args
}

// Stats aggregates the Dashboards page's numbers for one window, optionally
// scoped to one oncall team. Every section is its own grouped query; nothing
// here scans a row of the alert table into Go. Teams (the dropdown's option
// list) is deliberately not scoped by team — see its doc comment.
func (s *Store) Stats(ctx context.Context, from, to time.Time, team string) (*Stats, error) {
	daily, err := s.dailyResolutionMix(ctx, from, to, team)
	if err != nil {
		return nil, err
	}
	rules, err := s.ruleResolutionMix(ctx, from, to, team)
	if err != nil {
		return nil, err
	}
	loads, err := s.teamLoad(ctx, from, to, team)
	if err != nil {
		return nil, err
	}
	hourly, err := s.hourlyHistogram(ctx, from, to, team)
	if err != nil {
		return nil, err
	}
	scalars, err := s.statsScalars(ctx, from, to, team)
	if err != nil {
		return nil, err
	}
	teams, err := s.oncallTeams(ctx, from, to)
	if err != nil {
		return nil, err
	}
	sla, err := s.sla(ctx, from, to, team)
	if err != nil {
		return nil, err
	}

	var resolvedTotals ResolutionCounts
	for _, d := range daily {
		resolvedTotals.Handled += d.Handled
		resolvedTotals.SelfRecovered += d.SelfRecovered
		resolvedTotals.Noise += d.Noise
		resolvedTotals.Expired += d.Expired
		resolvedTotals.Total += d.Total
	}

	return &Stats{
		DailyResolutionMix: daily,
		RuleMix:            rules,
		TeamLoad:           loads,
		HourlyHistogram:    hourly,
		Teams:              teams,
		ResolvedTotals:     resolvedTotals,
		TotalAlerts:        scalars.TotalAlerts,
		NightPages:         scalars.NightPages,
		SLA:                *sla,
	}, nil
}

// oncallTeams lists every distinct non-empty oncall_team label in the
// window, unfiltered by team — see Stats.Teams. `<> ”` also drops rows
// where the label is entirely absent: JSON_EXTRACT on a missing path is
// SQL NULL, and NULL <> ” is NULL (falsy), not true.
func (s *Store) oncallTeams(ctx context.Context, from, to time.Time) ([]string, error) {
	q := `
		SELECT DISTINCT ` + oncallTeamExpr + ` AS team
		FROM alert
		WHERE created_at > ? AND created_at <= ? AND ` + oncallTeamExpr + ` <> ''
		ORDER BY team
	`
	teams := []string{}
	if err := s.db.SelectContext(ctx, &teams, q, from, to); err != nil {
		return nil, fmt.Errorf("oncall teams: %w", err)
	}
	return teams, nil
}

// AllOncallTeams lists every distinct non-empty oncall_team label across the
// whole table, for the alert list's team-filter dropdown. Unlike oncallTeams
// above it is unbounded by time: the filter should offer every team that has
// ever paged, not just the ones seen in some window. Same per-row JSON scan
// caveat as Stats — acceptable for a UI-shaping BFF endpoint.
func (s *Store) AllOncallTeams(ctx context.Context) ([]string, error) {
	q := `
		SELECT DISTINCT ` + oncallTeamExpr + ` AS team
		FROM alert
		WHERE ` + oncallTeamExpr + ` <> ''
		ORDER BY team
	`
	teams := []string{}
	if err := s.db.SelectContext(ctx, &teams, q); err != nil {
		return nil, fmt.Errorf("all oncall teams: %w", err)
	}
	return teams, nil
}

type dayResolutionRow struct {
	Day        string `db:"day"`
	Resolution string `db:"resolution"`
	Count      int    `db:"count"`
}

// dailyResolutionMix groups resolved alerts by UTC+8 calendar day and
// resolution — one row per (day, resolution) pair — then pivots them into one
// entry per day. Days come out in the order SQL emitted them (ORDER BY day),
// which is chronological.
func (s *Store) dailyResolutionMix(ctx context.Context, from, to time.Time, team string) ([]DailyResolutionMix, error) {
	q, args := appendTeamFilter(
		`SELECT DATE(DATE_ADD(created_at, INTERVAL 8 HOUR)) AS day, resolution, COUNT(*) AS count
		 FROM alert
		 WHERE state = ? AND created_at > ? AND created_at <= ?`,
		[]any{StateResolved, from, to},
		team,
	)
	q += " GROUP BY day, resolution ORDER BY day"
	var rows []dayResolutionRow
	if err := s.db.SelectContext(ctx, &rows, q, args...); err != nil {
		return nil, fmt.Errorf("daily resolution mix: %w", err)
	}
	return pivotDailyRows(rows), nil
}

// pivotDailyRows folds (day, resolution, count) rows into one entry per day,
// in the order the days were first seen — chronological, since the caller's
// query is ORDER BY day. Split out from dailyResolutionMix so the pivot can
// be unit tested without a database.
func pivotDailyRows(rows []dayResolutionRow) []DailyResolutionMix {
	order := make([]string, 0, len(rows))
	byDay := make(map[string]*DailyResolutionMix, len(rows))
	for _, r := range rows {
		row, ok := byDay[r.Day]
		if !ok {
			row = &DailyResolutionMix{Day: r.Day}
			byDay[r.Day] = row
			order = append(order, r.Day)
		}
		addResolution(&row.ResolutionCounts, r.Resolution, r.Count)
	}

	mix := make([]DailyResolutionMix, len(order))
	for i, day := range order {
		mix[i] = *byDay[day]
	}
	return mix
}

type ruleResolutionRow struct {
	Alertname  string `db:"alertname"`
	Resolution string `db:"resolution"`
	Count      int    `db:"count"`
}

// ruleResolutionMix groups resolved alerts by alertname and resolution, one
// entry per rule.
func (s *Store) ruleResolutionMix(ctx context.Context, from, to time.Time, team string) ([]RuleResolution, error) {
	q, args := appendTeamFilter(
		`SELECT alertname, resolution, COUNT(*) AS count
		 FROM alert
		 WHERE state = ? AND created_at > ? AND created_at <= ?`,
		[]any{StateResolved, from, to},
		team,
	)
	q += " GROUP BY alertname, resolution"
	var rows []ruleResolutionRow
	if err := s.db.SelectContext(ctx, &rows, q, args...); err != nil {
		return nil, fmt.Errorf("rule resolution mix: %w", err)
	}
	return pivotRuleRows(rows), nil
}

// pivotRuleRows folds (alertname, resolution, count) rows into one entry per
// rule, busiest first, ties by name.
func pivotRuleRows(rows []ruleResolutionRow) []RuleResolution {
	order := make([]string, 0, len(rows))
	byName := make(map[string]*RuleResolution, len(rows))
	for _, r := range rows {
		row, ok := byName[r.Alertname]
		if !ok {
			row = &RuleResolution{Alertname: r.Alertname}
			byName[r.Alertname] = row
			order = append(order, r.Alertname)
		}
		addResolution(&row.ResolutionCounts, r.Resolution, r.Count)
	}

	all := make([]RuleResolution, len(order))
	for i, name := range order {
		all[i] = *byName[name]
	}
	sort.Slice(all, func(i, j int) bool {
		if all[i].Total != all[j].Total {
			return all[i].Total > all[j].Total
		}
		return all[i].Alertname < all[j].Alertname
	})
	return all
}

type teamRow struct {
	OncallTeam     string `db:"oncall_team"`
	Classification string `db:"classification"`
	Night          int    `db:"night"`
	Count          int    `db:"count"`
}

// teamLoad groups every alert in the window by oncall team, classification and
// a UTC+8 night flag, then pivots them into one entry per team, sorted by night
// pages descending. It deliberately does not filter on assigned_to the way the
// old per-assignee query did: the team label is present on shadow alerts too,
// so this counts the full feed rather than only the cut-over slice that has an
// assignee (see TeamLoad). A missing label reads as SQL NULL, which COALESCE
// folds to ” here so it scans into a string, and pivotTeamRows maps to
// UnroutedTeam.
func (s *Store) teamLoad(ctx context.Context, from, to time.Time, team string) ([]TeamLoad, error) {
	q, args := appendTeamFilter(
		`SELECT COALESCE(`+oncallTeamExpr+`, '') AS oncall_team, classification, (HOUR(DATE_ADD(created_at, INTERVAL 8 HOUR)) < 9) AS night, COUNT(*) AS count
		 FROM alert
		 WHERE created_at > ? AND created_at <= ?`,
		[]any{from, to},
		team,
	)
	q += " GROUP BY oncall_team, classification, night"
	var rows []teamRow
	if err := s.db.SelectContext(ctx, &rows, q, args...); err != nil {
		return nil, fmt.Errorf("team load: %w", err)
	}
	return pivotTeamRows(rows), nil
}

// pivotTeamRows folds (oncall_team, classification, night, count) rows into one
// entry per team, sorted by night pages descending. An empty team name — the
// COALESCE of a missing label — becomes UnroutedTeam so the row is labelled
// rather than blank. Split out from teamLoad so the pivot can be unit tested
// without a database.
func pivotTeamRows(rows []teamRow) []TeamLoad {
	order := make([]string, 0, len(rows))
	byTeam := make(map[string]*TeamLoad, len(rows))
	for _, r := range rows {
		name := r.OncallTeam
		if name == "" {
			name = UnroutedTeam
		}
		row, ok := byTeam[name]
		if !ok {
			row = &TeamLoad{OncallTeam: name}
			byTeam[name] = row
			order = append(order, name)
		}
		switch {
		case r.Classification == ClassPage && r.Night != 0:
			row.NightPage += r.Count
		case r.Classification == ClassPage:
			row.DayPage += r.Count
		case r.Classification == ClassTicket:
			row.Ticket += r.Count
		}
		row.Total += r.Count
	}

	all := make([]TeamLoad, len(order))
	for i, name := range order {
		all[i] = *byTeam[name]
	}
	sort.Slice(all, func(i, j int) bool { return all[i].NightPage > all[j].NightPage })
	return all
}

type hourRow struct {
	Hour  int `db:"hour"`
	Count int `db:"count"`
}

// hourlyHistogram groups every alert in the window (any state or
// classification) by UTC+8 hour of day, always returning all 24 hours so the
// chart never has to fill a gap.
func (s *Store) hourlyHistogram(ctx context.Context, from, to time.Time, team string) ([]HourlyBucket, error) {
	q, args := appendTeamFilter(
		`SELECT HOUR(DATE_ADD(created_at, INTERVAL 8 HOUR)) AS hour, COUNT(*) AS count
		 FROM alert
		 WHERE created_at > ? AND created_at <= ?`,
		[]any{from, to},
		team,
	)
	q += " GROUP BY hour"
	var rows []hourRow
	if err := s.db.SelectContext(ctx, &rows, q, args...); err != nil {
		return nil, fmt.Errorf("hourly histogram: %w", err)
	}
	return pivotHourRows(rows), nil
}

// pivotHourRows fills all 24 hours, zero where SQL had no row to group, so
// the chart never has to fill a gap itself.
func pivotHourRows(rows []hourRow) []HourlyBucket {
	counts := make([]int, 24)
	for _, r := range rows {
		if r.Hour >= 0 && r.Hour < 24 {
			counts[r.Hour] = r.Count
		}
	}
	buckets := make([]HourlyBucket, 24)
	for h := 0; h < 24; h++ {
		buckets[h] = HourlyBucket{Hour: h, Count: counts[h]}
	}
	return buckets
}

type statsScalars struct {
	TotalAlerts int `db:"total_alerts"`
	NightPages  int `db:"night_pages"`
}

// statsScalars answers the two window-wide counts that are not a breakdown:
// total alerts and night pages. COALESCE guards SUM() over an empty window,
// which MySQL/TiDB returns as NULL rather than 0 — a custom date range with no
// data in it is an ordinary input here, not an edge case.
func (s *Store) statsScalars(ctx context.Context, from, to time.Time, team string) (*statsScalars, error) {
	q, args := appendTeamFilter(
		`SELECT
			COUNT(*) AS total_alerts,
			COALESCE(SUM(classification = ? AND HOUR(DATE_ADD(created_at, INTERVAL 8 HOUR)) < 9), 0) AS night_pages
		 FROM alert
		 WHERE created_at > ? AND created_at <= ?`,
		[]any{ClassPage, from, to},
		team,
	)
	var sc statsScalars
	if err := s.db.GetContext(ctx, &sc, q, args...); err != nil {
		return nil, fmt.Errorf("stats scalars: %w", err)
	}
	return &sc, nil
}

// ── Response SLA ──
//
// MTTA/MTTR are read as percentiles, not means — a mean is one slow alert
// away from meaning nothing, and P50/P90 is what an on-call lead actually
// asks after: "how long does the middle case take, and how bad does the
// worst-of-the-normal case get." SQL has no percentile aggregate this
// codebase can rely on across MySQL/TiDB versions, so mtta/mttrDurations
// below pull the raw (day, duration_seconds) pairs — thousands of small
// integers for a 90-day window, not the full alert row — and percentile()
// sorts and picks in Go.

// DurationPercentiles is P50/P90 of a duration distribution, in seconds.
// Both are nil together when the window has no qualifying sample — a
// KPI tile with nothing to show, not a duration computed from zero rows.
type DurationPercentiles struct {
	P50Seconds *int64 `json:"p50_seconds"`
	P90Seconds *int64 `json:"p90_seconds"`
}

// SLADayPoint is one UTC+8 calendar day's P50/P90, for the MTTA/MTTR trend
// charts. Every entry here has at least one sample by construction (a day
// with none never gets an entry), so unlike DurationPercentiles above,
// P50/P90 are never nil for an entry that exists.
type SLADayPoint struct {
	Day        string `json:"day" validate:"required"`
	P50Seconds int64  `json:"p50_seconds" validate:"required"`
	P90Seconds int64  `json:"p90_seconds" validate:"required"`
}

// SLA is the Response SLA section's numbers for one window: how fast pages
// get acknowledged and how fast page+ticket alerts get resolved, plus the
// two daily trends the section charts. AckedWithinDeadlineTotal is the rate's
// denominator (pages with both a response_deadline and an ack in the
// window), so the client can tell "0%, out of zero" from "0%, out of forty."
type SLA struct {
	MTTA                     DurationPercentiles `json:"mtta" validate:"required"`
	MTTR                     DurationPercentiles `json:"mttr" validate:"required"`
	AckedWithinDeadlineRate  *float64            `json:"acked_within_deadline_rate"`
	AckedWithinDeadlineTotal int                 `json:"acked_within_deadline_total" validate:"required"`
	MTTATrend                []SLADayPoint       `json:"mtta_trend" validate:"required"`
	MTTRTrend                []SLADayPoint       `json:"mttr_trend" validate:"required"`
}

// percentile returns the p-th percentile (0 < p <= 100) of sorted, ascending
// values, by nearest rank: the smallest value covering at least p% of the
// samples. Callers guard the len(sorted) == 0 case themselves, since "no
// samples" and "the one sample is 0s" mean different things in the response
// (see DurationPercentiles).
func percentile(sorted []int64, p float64) int64 {
	n := len(sorted)
	if n == 0 {
		return 0
	}
	rank := int(math.Ceil(p / 100 * float64(n)))
	if rank < 1 {
		rank = 1
	}
	if rank > n {
		rank = n
	}
	return sorted[rank-1]
}

type durationRow struct {
	Day             string `db:"day"`
	DurationSeconds int64  `db:"duration_seconds"`
}

// pivotDurationRows computes the window-wide P50/P90 (overall) and the daily
// P50/P90 trend (daily) from raw (day, duration_seconds) pairs. Split out
// from the query functions so the percentile math is unit-testable without a
// database.
func pivotDurationRows(rows []durationRow) (overall DurationPercentiles, daily []SLADayPoint) {
	byDay := make(map[string][]int64, len(rows))
	dayOrder := make([]string, 0, len(rows))
	all := make([]int64, 0, len(rows))
	for _, r := range rows {
		if _, ok := byDay[r.Day]; !ok {
			dayOrder = append(dayOrder, r.Day)
		}
		byDay[r.Day] = append(byDay[r.Day], r.DurationSeconds)
		all = append(all, r.DurationSeconds)
	}
	sort.Strings(dayOrder) // chronological: YYYY-MM-DD sorts lexically the same as by date.

	daily = make([]SLADayPoint, 0, len(dayOrder))
	for _, day := range dayOrder {
		durs := byDay[day]
		sort.Slice(durs, func(i, j int) bool { return durs[i] < durs[j] })
		daily = append(daily, SLADayPoint{Day: day, P50Seconds: percentile(durs, 50), P90Seconds: percentile(durs, 90)})
	}

	if len(all) > 0 {
		sort.Slice(all, func(i, j int) bool { return all[i] < all[j] })
		p50, p90 := percentile(all, 50), percentile(all, 90)
		overall = DurationPercentiles{P50Seconds: &p50, P90Seconds: &p90}
	}
	return overall, daily
}

// mttaDurations pulls (day, seconds-to-ack) for every page acked in the
// window — the raw pull pivotDurationRows turns into MTTA and MTTATrend.
// Windowed on created_at like every other Stats query (not acked_at), so an
// alert appears in exactly the window its own row belongs to, consistent
// with how the resolution-mix queries above window on created_at while
// filtering state = 'resolved'.
func (s *Store) mttaDurations(ctx context.Context, from, to time.Time, team string) ([]durationRow, error) {
	q, args := appendTeamFilter(
		`SELECT DATE(DATE_ADD(created_at, INTERVAL 8 HOUR)) AS day, TIMESTAMPDIFF(SECOND, created_at, acked_at) AS duration_seconds
		 FROM alert
		 WHERE classification = ? AND acked_at IS NOT NULL AND created_at > ? AND created_at <= ?`,
		[]any{ClassPage, from, to},
		team,
	)
	var rows []durationRow
	if err := s.db.SelectContext(ctx, &rows, q, args...); err != nil {
		return nil, fmt.Errorf("mtta durations: %w", err)
	}
	return rows, nil
}

// mttrDurations pulls (day, seconds-to-resolve) for every page or ticket
// someone resolved in the window. Expired alerts are left out: their
// resolved_at is the sweep's clock, not a response.
func (s *Store) mttrDurations(ctx context.Context, from, to time.Time, team string) ([]durationRow, error) {
	q, args := appendTeamFilter(
		`SELECT DATE(DATE_ADD(created_at, INTERVAL 8 HOUR)) AS day, TIMESTAMPDIFF(SECOND, created_at, resolved_at) AS duration_seconds
		 FROM alert
		 WHERE state = ? AND resolution != ? AND resolved_at IS NOT NULL AND created_at > ? AND created_at <= ?`,
		[]any{StateResolved, ResolutionExpired, from, to},
		team,
	)
	var rows []durationRow
	if err := s.db.SelectContext(ctx, &rows, q, args...); err != nil {
		return nil, fmt.Errorf("mttr durations: %w", err)
	}
	return rows, nil
}

type ackedDeadlineRow struct {
	WithinDeadline int `db:"within_deadline"`
	WithDeadline   int `db:"with_deadline"`
}

// ackedWithinDeadline answers the rate's numerator/denominator directly in
// SQL (a count, not a percentile — no row pull needed): among pages in the
// window that had a response_deadline and were acked, how many were acked
// at or before it.
func (s *Store) ackedWithinDeadline(ctx context.Context, from, to time.Time, team string) (*ackedDeadlineRow, error) {
	q, args := appendTeamFilter(
		`SELECT
			COALESCE(SUM(acked_at <= response_deadline), 0) AS within_deadline,
			COUNT(*) AS with_deadline
		 FROM alert
		 WHERE classification = ? AND acked_at IS NOT NULL AND response_deadline IS NOT NULL AND created_at > ? AND created_at <= ?`,
		[]any{ClassPage, from, to},
		team,
	)
	var row ackedDeadlineRow
	if err := s.db.GetContext(ctx, &row, q, args...); err != nil {
		return nil, fmt.Errorf("acked within deadline: %w", err)
	}
	return &row, nil
}

// sla runs the three Response SLA queries and assembles SLA.
func (s *Store) sla(ctx context.Context, from, to time.Time, team string) (*SLA, error) {
	mttaRows, err := s.mttaDurations(ctx, from, to, team)
	if err != nil {
		return nil, err
	}
	mttrRows, err := s.mttrDurations(ctx, from, to, team)
	if err != nil {
		return nil, err
	}
	deadline, err := s.ackedWithinDeadline(ctx, from, to, team)
	if err != nil {
		return nil, err
	}

	mtta, mttaTrend := pivotDurationRows(mttaRows)
	mttr, mttrTrend := pivotDurationRows(mttrRows)

	var rate *float64
	if deadline.WithDeadline > 0 {
		r := float64(deadline.WithinDeadline) / float64(deadline.WithDeadline)
		rate = &r
	}

	return &SLA{
		MTTA:                     mtta,
		MTTR:                     mttr,
		AckedWithinDeadlineRate:  rate,
		AckedWithinDeadlineTotal: deadline.WithDeadline,
		MTTATrend:                mttaTrend,
		MTTRTrend:                mttrTrend,
	}, nil
}