package api

import (
	"database/sql"
	"net/http"
	"sort"
	"strconv"
	"strings"
	"time"

	"github.com/gin-gonic/gin"

	"github.com/rycroftapparel/workpulse-api/internal/httpapi"
)

// orgReportsTimeFilter returns a SQL boolean expression for reports.created_at (no user filter).
// reportsAlias must be the SQL alias for the reports table in the surrounding query (e.g. "r"),
// so the column is qualified and not ambiguous after JOIN users (both tables have created_at).
func orgReportsTimeFilter(period string, reportsAlias string) string {
	col := reportsAlias + ".created_at"
	switch strings.ToLower(strings.TrimSpace(period)) {
	case "7d":
		return col + ` >= NOW() - INTERVAL '7 days'`
	case "30d":
		return col + ` >= NOW() - INTERVAL '30 days'`
	case "90d":
		return col + ` >= NOW() - INTERVAL '90 days'`
	case "all", "":
		return `TRUE`
	default:
		return col + ` >= NOW() - INTERVAL '30 days'`
	}
}

func orgPeriodLabel(period string) string {
	p := strings.ToLower(strings.TrimSpace(period))
	if p == "" {
		return "30d"
	}
	if p == "7d" || p == "30d" || p == "90d" || p == "all" {
		return p
	}
	return "30d"
}

// analyticsOrgSummary returns org-wide KPIs (superadmin only). Division is taken from reports.division string.
func (s *Server) analyticsOrgSummary(c *gin.Context) {
	periodNorm := orgPeriodLabel(c.DefaultQuery("period", "30d"))
	tf := orgReportsTimeFilter(periodNorm, "r")

	ctx, cancel := s.ctx(c)
	defer cancel()

	var total, draft, nonDraft, distinctReporters int
	q := `SELECT 
		COUNT(*) AS total,
		COUNT(*) FILTER (WHERE r.status = 'draft') AS draft,
		COUNT(*) FILTER (WHERE r.status <> 'draft') AS nondraft,
		COUNT(DISTINCT r.user_id) AS reporters
		FROM reports r WHERE (` + tf + `)`
	err := s.DB.QueryRowContext(ctx, q).Scan(&total, &draft, &nonDraft, &distinctReporters)
	if err != nil {
		c.JSON(http.StatusInternalServerError, httpapi.Fail("db", err.Error()))
		return
	}

	completion := 0
	if total > 0 {
		completion = (nonDraft * 100) / total
	}

	var notifUnread int
	_ = s.DB.QueryRowContext(ctx, `SELECT COUNT(*) FROM notifications WHERE read_at IS NULL`).Scan(&notifUnread)

	c.JSON(http.StatusOK, httpapi.OK(gin.H{
		"period":                     periodNorm,
		"reportsTotal":               total,
		"reportsDraft":               draft,
		"reportsNonDraft":            nonDraft,
		"completionRatePercent":      completion,
		"blockerResolutionPercent":   0,
		"distinctReportersInPeriod":  distinctReporters,
		"notificationsUnreadOrgWide": notifUnread,
	}))
}

// analyticsOrgByDivision aggregates report counts by division. With no active org_divisions rows,
// behavior matches legacy (group by reports.division text, empty → "(none)"). With active masters,
// returns each active master (label from name) plus unmatched report buckets (label = raw key).
func (s *Server) analyticsOrgByDivision(c *gin.Context) {
	periodNorm := orgPeriodLabel(c.DefaultQuery("period", "30d"))
	tf := orgReportsTimeFilter(periodNorm, "r")

	ctx, cancel := s.ctx(c)
	defer cancel()
	accessAll, accessCodes, err := s.userDivisionVisibility(ctx, userID(c), roleFromContext(c))
	if err != nil {
		c.JSON(http.StatusInternalServerError, httpapi.Fail("db", err.Error()))
		return
	}

	var masterCount int
	err = s.DB.QueryRowContext(ctx, `SELECT COUNT(*) FROM org_divisions WHERE is_active = true`).Scan(&masterCount)
	if err != nil {
		c.JSON(http.StatusInternalServerError, httpapi.Fail("db", err.Error()))
		return
	}

	type reportAgg struct {
		raw string
		n   int
	}
	reportMap := map[string]reportAgg{}

	rRows, err := s.DB.QueryContext(ctx,
		`SELECT COALESCE(NULLIF(TRIM(r.division), ''), '(none)') AS code, COUNT(*) AS n
		 FROM reports r WHERE (`+tf+`) GROUP BY 1`,
	)
	if err != nil {
		c.JSON(http.StatusInternalServerError, httpapi.Fail("db", err.Error()))
		return
	}
	defer rRows.Close()
	for rRows.Next() {
		var code string
		var n int
		if err := rRows.Scan(&code, &n); err != nil {
			c.JSON(http.StatusInternalServerError, httpapi.Fail("db", err.Error()))
			return
		}
		k := strings.ToLower(strings.TrimSpace(code))
		reportMap[k] = reportAgg{raw: code, n: n}
	}
	if err := rRows.Err(); err != nil {
		c.JSON(http.StatusInternalServerError, httpapi.Fail("db", err.Error()))
		return
	}

	divs := []gin.H{}
	if masterCount == 0 {
		var flat []reportAgg
		for _, v := range reportMap {
			flat = append(flat, v)
		}
		sort.Slice(flat, func(i, j int) bool {
			if flat[i].n != flat[j].n {
				return flat[i].n > flat[j].n
			}
			return flat[i].raw < flat[j].raw
		})
		for _, e := range flat {
			if !divisionAllowedByVisibility(accessAll, accessCodes, e.raw) {
				continue
			}
			divs = append(divs, gin.H{
				"code":        e.raw,
				"label":       e.raw,
				"reportCount": e.n,
			})
		}
		c.JSON(http.StatusOK, httpapi.OK(gin.H{
			"period":    periodNorm,
			"divisions": divs,
		}))
		return
	}

	mRows, err := s.DB.QueryContext(ctx,
		`SELECT code, name, sort_order FROM org_divisions WHERE is_active = true ORDER BY sort_order ASC, name ASC`,
	)
	if err != nil {
		c.JSON(http.StatusInternalServerError, httpapi.Fail("db", err.Error()))
		return
	}
	defer mRows.Close()
	type masterRow struct {
		code, name string
	}
	var masters []masterRow
	for mRows.Next() {
		var code, name string
		var sortOrder int
		if err := mRows.Scan(&code, &name, &sortOrder); err != nil {
			c.JSON(http.StatusInternalServerError, httpapi.Fail("db", err.Error()))
			return
		}
		masters = append(masters, masterRow{code: code, name: name})
	}
	if err := mRows.Err(); err != nil {
		c.JSON(http.StatusInternalServerError, httpapi.Fail("db", err.Error()))
		return
	}

	masterKeys := map[string]struct{}{}
	for _, m := range masters {
		mk := strings.ToLower(strings.TrimSpace(m.code))
		masterKeys[mk] = struct{}{}
		if !divisionAllowedByVisibility(accessAll, accessCodes, m.code) {
			continue
		}
		n := 0
		if v, ok := reportMap[mk]; ok {
			n = v.n
		}
		divs = append(divs, gin.H{
			"code":        m.code,
			"label":       m.name,
			"reportCount": n,
		})
	}

	type orphan struct {
		code string
		n    int
	}
	var orphans []orphan
	for k, v := range reportMap {
		if _, ok := masterKeys[k]; ok {
			continue
		}
		orphans = append(orphans, orphan{code: v.raw, n: v.n})
	}
	sort.Slice(orphans, func(i, j int) bool {
		if orphans[i].n != orphans[j].n {
			return orphans[i].n > orphans[j].n
		}
		return orphans[i].code < orphans[j].code
	})
	for _, o := range orphans {
		if !divisionAllowedByVisibility(accessAll, accessCodes, o.code) {
			continue
		}
		divs = append(divs, gin.H{
			"code":        o.code,
			"label":       o.code,
			"reportCount": o.n,
		})
	}

	c.JSON(http.StatusOK, httpapi.OK(gin.H{
		"period":    periodNorm,
		"divisions": divs,
	}))
}

// analyticsOrgReportersByDivision lists users with report counts for a single division label (superadmin only).
func (s *Server) analyticsOrgReportersByDivision(c *gin.Context) {
	div := strings.TrimSpace(c.Query("division"))
	if div == "" {
		c.JSON(http.StatusBadRequest, httpapi.Fail("validation", "division query required"))
		return
	}
	periodNorm := orgPeriodLabel(c.DefaultQuery("period", "30d"))
	tf := orgReportsTimeFilter(periodNorm, "r")

	ctx, cancel := s.ctx(c)
	defer cancel()
	ok, err := s.requestCanAccessDivision(ctx, c, div)
	if err != nil {
		c.JSON(http.StatusInternalServerError, httpapi.Fail("db", err.Error()))
		return
	}
	if !ok {
		c.JSON(http.StatusForbidden, httpapi.Fail("forbidden", "Anda tidak memiliki akses ke divisi ini."))
		return
	}
	rows, err := s.DB.QueryContext(ctx,
		`SELECT r.user_id, u.name, u.email, u.is_active, COUNT(*) AS n
		 FROM reports r
		 JOIN users u ON u.id = r.user_id
		 WHERE (`+tf+`) AND (
		   ($1 = '(none)' AND NULLIF(TRIM(r.division), '') IS NULL)
		   OR ($1 <> '(none)' AND LOWER(TRIM(COALESCE(r.division, ''))) = LOWER(TRIM($1)))
		 )
		 GROUP BY r.user_id, u.name, u.email, u.is_active
		 ORDER BY n DESC, u.name ASC`,
		div,
	)
	if err != nil {
		c.JSON(http.StatusInternalServerError, httpapi.Fail("db", err.Error()))
		return
	}
	defer rows.Close()
	out := []gin.H{}
	for rows.Next() {
		var uid uint64
		var name, email string
		var isActive bool
		var n int
		if err := rows.Scan(&uid, &name, &email, &isActive, &n); err != nil {
			c.JSON(http.StatusInternalServerError, httpapi.Fail("db", err.Error()))
			return
		}
		out = append(out, gin.H{"userId": uid, "name": name, "email": email, "isActive": isActive, "reportCount": n})
	}
	if err := rows.Err(); err != nil {
		c.JSON(http.StatusInternalServerError, httpapi.Fail("db", err.Error()))
		return
	}
	c.JSON(http.StatusOK, httpapi.OK(gin.H{
		"period":    periodNorm,
		"division":  div,
		"reporters": out,
	}))
}

// analyticsOrgReportsByReporter lists report rows for one reporter in one division bucket,
// using the same created_at period and division rules as reporters-by-division (drill-down).
func (s *Server) analyticsOrgReportsByReporter(c *gin.Context) {
	div := strings.TrimSpace(c.Query("division"))
	if div == "" {
		c.JSON(http.StatusBadRequest, httpapi.Fail("validation", "division query required"))
		return
	}
	uidStr := strings.TrimSpace(c.Query("userId"))
	if uidStr == "" {
		c.JSON(http.StatusBadRequest, httpapi.Fail("validation", "userId query required"))
		return
	}
	targetUID, err := strconv.ParseUint(uidStr, 10, 64)
	if err != nil || targetUID == 0 {
		c.JSON(http.StatusBadRequest, httpapi.Fail("validation", "invalid userId"))
		return
	}

	periodNorm := orgPeriodLabel(c.DefaultQuery("period", "30d"))
	tf := orgReportsTimeFilter(periodNorm, "r")

	ctx, cancel := s.ctx(c)
	defer cancel()
	ok, err := s.requestCanAccessDivision(ctx, c, div)
	if err != nil {
		c.JSON(http.StatusInternalServerError, httpapi.Fail("db", err.Error()))
		return
	}
	if !ok {
		c.JSON(http.StatusForbidden, httpapi.Fail("forbidden", "Anda tidak memiliki akses ke divisi ini."))
		return
	}

	wantSummary := orgReportListIncludeSummary(c)

	selectCols := `r.id, r.title, r.status, r.report_date, r.updated_at`
	if wantSummary {
		selectCols += `, r.body`
	}
	q := `SELECT ` + selectCols + `
		FROM reports r
		WHERE r.user_id = $1 AND (` + tf + `) AND (
			($2 = '(none)' AND NULLIF(TRIM(r.division), '') IS NULL)
			OR ($2 <> '(none)' AND LOWER(TRIM(COALESCE(r.division, ''))) = LOWER(TRIM($2)))
		)
		ORDER BY r.updated_at DESC
		LIMIT 200`
	rows, err := s.DB.QueryContext(ctx, q, targetUID, div)
	if err != nil {
		c.JSON(http.StatusInternalServerError, httpapi.Fail("db", err.Error()))
		return
	}
	defer rows.Close()

	out := []gin.H{}
	for rows.Next() {
		var id uint64
		var title, status, body string
		var reportDate sql.NullString
		var updated time.Time
		var scanErr error
		if wantSummary {
			scanErr = rows.Scan(&id, &title, &status, &reportDate, &updated, &body)
		} else {
			scanErr = rows.Scan(&id, &title, &status, &reportDate, &updated)
		}
		if scanErr != nil {
			c.JSON(http.StatusInternalServerError, httpapi.Fail("db", scanErr.Error()))
			return
		}
		h := gin.H{"id": id, "title": title, "status": status, "updatedAt": updated}
		if reportDate.Valid {
			h["reportDate"] = reportDate.String
		}
		if wantSummary {
			h["summary"] = orgReportBodyListSummary(body)
		}
		out = append(out, h)
	}
	if err := rows.Err(); err != nil {
		c.JSON(http.StatusInternalServerError, httpapi.Fail("db", err.Error()))
		return
	}

	c.JSON(http.StatusOK, httpapi.OK(gin.H{
		"period":   periodNorm,
		"division": div,
		"userId":   targetUID,
		"reports":  out,
	}))
}

// analyticsOrgTopReporters lists users with the most reports in the period (superadmin only).
func (s *Server) analyticsOrgTopReporters(c *gin.Context) {
	periodNorm := orgPeriodLabel(c.DefaultQuery("period", "30d"))
	tf := orgReportsTimeFilter(periodNorm, "r")

	limit, _ := strconv.Atoi(c.DefaultQuery("limit", "10"))
	if limit < 1 {
		limit = 1
	}
	if limit > 50 {
		limit = 50
	}

	ctx, cancel := s.ctx(c)
	defer cancel()
	rows, err := s.DB.QueryContext(ctx,
		`SELECT r.user_id, u.name, u.email, COUNT(*) AS n
		 FROM reports r
		 JOIN users u ON u.id = r.user_id
		 WHERE (`+tf+`)
		 GROUP BY r.user_id, u.name, u.email
		 ORDER BY n DESC, u.name ASC
		 LIMIT $1`,
		limit,
	)
	if err != nil {
		c.JSON(http.StatusInternalServerError, httpapi.Fail("db", err.Error()))
		return
	}
	defer rows.Close()
	out := []gin.H{}
	for rows.Next() {
		var uid uint64
		var name, email string
		var n int
		if err := rows.Scan(&uid, &name, &email, &n); err != nil {
			c.JSON(http.StatusInternalServerError, httpapi.Fail("db", err.Error()))
			return
		}
		out = append(out, gin.H{"userId": uid, "name": name, "email": email, "reportCount": n})
	}
	if err := rows.Err(); err != nil {
		c.JSON(http.StatusInternalServerError, httpapi.Fail("db", err.Error()))
		return
	}
	c.JSON(http.StatusOK, httpapi.OK(gin.H{
		"period":    periodNorm,
		"reporters": out,
	}))
}