spider/internal/handler/analytics.go

package handler

import (
	"encoding/csv"
	"fmt"
	"time"

	"spider/internal/model"
	"spider/internal/store"

	"github.com/gin-gonic/gin"
)

// AnalyticsHandler handles analytics endpoints.
type AnalyticsHandler struct {
	store *store.Store
}

// Funnel returns conversion funnel data.
func (h *AnalyticsHandler) Funnel(c *gin.Context) {
	db := h.store.DB

	var rawTotal, cleanTotal, validTotal int64
	db.Model(&model.MerchantRaw{}).Count(&rawTotal)
	db.Model(&model.MerchantClean{}).Count(&cleanTotal)
	db.Model(&model.MerchantClean{}).Where("status = ?", "valid").Count(&validTotal)

	type kv struct {
		Key   string `gorm:"column:key"`
		Count int64  `gorm:"column:count"`
	}

	var followRows []kv
	db.Model(&model.MerchantClean{}).
		Select("follow_status as `key`, count(*) as `count`").
		Where("status = ?", "valid").
		Group("follow_status").
		Find(&followRows)

	followMap := map[string]int64{}
	for _, r := range followRows {
		followMap[r.Key] = r.Count
	}

	contacted := followMap["contacted"] + followMap["cooperating"] + followMap["rejected"]
	cooperating := followMap["cooperating"]
	rejected := followMap["rejected"]

	safeDiv := func(a, b int64) float64 {
		if b == 0 {
			return 0
		}
		return float64(a) / float64(b)
	}

	OK(c, gin.H{
		"raw_total":   rawTotal,
		"clean_total": cleanTotal,
		"valid_total": validTotal,
		"contacted":   contacted,
		"cooperating": cooperating,
		"rejected":    rejected,
		"conversion_rates": gin.H{
			"raw_to_clean":          safeDiv(cleanTotal, rawTotal),
			"clean_to_valid":        safeDiv(validTotal, cleanTotal),
			"valid_to_contacted":    safeDiv(contacted, validTotal),
			"contacted_to_cooperating": safeDiv(cooperating, contacted),
		},
	})
}

// SourceEfficiency returns source-level performance metrics.
func (h *AnalyticsHandler) SourceEfficiency(c *gin.Context) {
	db := h.store.DB

	// By source type
	type sourceMetric struct {
		SourceType string `gorm:"column:source_type" json:"source_type"`
		RawCount   int64  `gorm:"column:raw_count" json:"raw_count"`
	}

	var rawBySource []sourceMetric
	db.Model(&model.MerchantRaw{}).
		Select("source_type, count(*) as raw_count").
		Group("source_type").
		Find(&rawBySource)

	// Count clean/hot merchants per source type in a single query using JSON LIKE
	type cleanBySource struct {
		SourceType string `gorm:"column:source_type"`
		CleanCount int64  `gorm:"column:clean_count"`
		HotCount   int64  `gorm:"column:hot_count"`
	}

	// Build a single query: count all and count hot per source type
	sourceTypes := make([]string, 0, len(rawBySource))
	rawCountMap := map[string]int64{}
	for _, rs := range rawBySource {
		if rs.SourceType != "" {
			sourceTypes = append(sourceTypes, rs.SourceType)
			rawCountMap[rs.SourceType] = rs.RawCount
		}
	}

	// Single batch query for clean counts per source type
	cleanCounts := map[string]int64{}
	hotCounts := map[string]int64{}
	for _, st := range sourceTypes {
		pattern := fmt.Sprintf("%%\"%s\"%%", st)
		var cc int64
		db.Model(&model.MerchantClean{}).Where("all_sources LIKE ?", pattern).Count(&cc)
		cleanCounts[st] = cc
		var hc int64
		db.Model(&model.MerchantClean{}).Where("all_sources LIKE ? AND level = ?", pattern, "Hot").Count(&hc)
		hotCounts[st] = hc
	}

	type sourceEff struct {
		SourceType string  `json:"source_type"`
		RawCount   int64   `json:"raw_count"`
		CleanCount int64   `json:"clean_count"`
		HotCount   int64   `json:"hot_count"`
		Efficiency float64 `json:"efficiency"`
	}

	results := make([]sourceEff, 0, len(sourceTypes))
	for _, st := range sourceTypes {
		eff := float64(0)
		if rawCountMap[st] > 0 {
			eff = float64(hotCounts[st]) / float64(rawCountMap[st])
		}
		results = append(results, sourceEff{
			SourceType: st,
			RawCount:   rawCountMap[st],
			CleanCount: cleanCounts[st],
			HotCount:   hotCounts[st],
			Efficiency: eff,
		})
	}

	// Top keywords
	type kwMetric struct {
		Keyword        string `gorm:"column:keyword" json:"keyword"`
		MerchantsFound int64  `gorm:"column:merchants_found" json:"merchants_found"`
	}

	var topKeywords []kwMetric
	db.Model(&model.MerchantRaw{}).
		Select("source_name as keyword, count(*) as merchants_found").
		Where("source_type = ? AND source_name != ''", "web").
		Group("source_name").
		Order("merchants_found DESC").
		Limit(10).
		Find(&topKeywords)

	// Top groups
	type groupMetric struct {
		GroupUsername string `gorm:"column:group_username" json:"group_username"`
		MembersFound int64  `gorm:"column:members_found" json:"members_found"`
	}

	var topGroups []groupMetric
	db.Model(&model.GroupMember{}).
		Select("group_username, count(distinct member_username) as members_found").
		Group("group_username").
		Order("members_found DESC").
		Limit(10).
		Find(&topGroups)

	OK(c, gin.H{
		"by_source_type": results,
		"top_keywords":   topKeywords,
		"top_groups":     topGroups,
	})
}

// Trends returns time-series data for reporting.
func (h *AnalyticsHandler) Trends(c *gin.Context) {
	db := h.store.DB

	period := c.DefaultQuery("period", "week")
	rangeStr := c.DefaultQuery("range", "90")
	rangeDays := parseInt(rangeStr, 90)

	startDate := time.Now().AddDate(0, 0, -rangeDays)

	var dateFormat, groupExpr string
	switch period {
	case "month":
		dateFormat = "%Y-%m"
		groupExpr = "DATE_FORMAT(created_at, '%Y-%m')"
	default: // week
		dateFormat = "%x-W%v"
		groupExpr = "DATE_FORMAT(created_at, '%x-W%v')"
	}
	_ = dateFormat // used implicitly via groupExpr

	type trendRow struct {
		PeriodLabel string `gorm:"column:period_label" json:"period_label"`
		Count       int64  `gorm:"column:count" json:"count"`
	}

	// Raw added per period
	var rawTrend []trendRow
	db.Model(&model.MerchantRaw{}).
		Select(groupExpr+" as period_label, count(*) as `count`").
		Where("created_at >= ?", startDate).
		Group("period_label").
		Order("period_label ASC").
		Find(&rawTrend)

	// Clean added per period
	var cleanTrend []trendRow
	db.Model(&model.MerchantClean{}).
		Select(groupExpr+" as period_label, count(*) as `count`").
		Where("created_at >= ?", startDate).
		Group("period_label").
		Order("period_label ASC").
		Find(&cleanTrend)

	// Build merged result
	type periodData struct {
		PeriodLabel string `json:"period_label"`
		RawAdded    int64  `json:"raw_added"`
		CleanAdded  int64  `json:"clean_added"`
	}

	periodMap := map[string]*periodData{}
	for _, r := range rawTrend {
		if _, ok := periodMap[r.PeriodLabel]; !ok {
			periodMap[r.PeriodLabel] = &periodData{PeriodLabel: r.PeriodLabel}
		}
		periodMap[r.PeriodLabel].RawAdded = r.Count
	}
	for _, r := range cleanTrend {
		if _, ok := periodMap[r.PeriodLabel]; !ok {
			periodMap[r.PeriodLabel] = &periodData{PeriodLabel: r.PeriodLabel}
		}
		periodMap[r.PeriodLabel].CleanAdded = r.Count
	}

	// Sort by period
	data := make([]periodData, 0, len(periodMap))
	for _, v := range periodMap {
		data = append(data, *v)
	}
	// Simple sort
	for i := 0; i < len(data); i++ {
		for j := i + 1; j < len(data); j++ {
			if data[i].PeriodLabel > data[j].PeriodLabel {
				data[i], data[j] = data[j], data[i]
			}
		}
	}

	OK(c, gin.H{
		"period": period,
		"data":   data,
	})
}

// ExportTrends exports trend data as CSV.
func (h *AnalyticsHandler) ExportTrends(c *gin.Context) {
	db := h.store.DB
	period := c.DefaultQuery("period", "week")
	rangeStr := c.DefaultQuery("range", "90")
	rangeDays := parseInt(rangeStr, 90)
	startDate := time.Now().AddDate(0, 0, -rangeDays)

	var groupExpr string
	switch period {
	case "month":
		groupExpr = "DATE_FORMAT(created_at, '%Y-%m')"
	default:
		groupExpr = "DATE_FORMAT(created_at, '%x-W%v')"
	}

	type trendRow struct {
		PeriodLabel string `gorm:"column:period_label"`
		Count       int64  `gorm:"column:count"`
	}

	var rawTrend []trendRow
	db.Model(&model.MerchantRaw{}).
		Select(groupExpr+" as period_label, count(*) as `count`").
		Where("created_at >= ?", startDate).Group("period_label").Order("period_label ASC").Find(&rawTrend)

	var cleanTrend []trendRow
	db.Model(&model.MerchantClean{}).
		Select(groupExpr+" as period_label, count(*) as `count`").
		Where("created_at >= ?", startDate).Group("period_label").Order("period_label ASC").Find(&cleanTrend)

	type periodData struct {
		PeriodLabel string
		RawAdded    int64
		CleanAdded  int64
	}

	periodMap := map[string]*periodData{}
	for _, r := range rawTrend {
		if _, ok := periodMap[r.PeriodLabel]; !ok {
			periodMap[r.PeriodLabel] = &periodData{PeriodLabel: r.PeriodLabel}
		}
		periodMap[r.PeriodLabel].RawAdded = r.Count
	}
	for _, r := range cleanTrend {
		if _, ok := periodMap[r.PeriodLabel]; !ok {
			periodMap[r.PeriodLabel] = &periodData{PeriodLabel: r.PeriodLabel}
		}
		periodMap[r.PeriodLabel].CleanAdded = r.Count
	}

	data := make([]periodData, 0, len(periodMap))
	for _, v := range periodMap {
		data = append(data, *v)
	}
	for i := 0; i < len(data); i++ {
		for j := i + 1; j < len(data); j++ {
			if data[i].PeriodLabel > data[j].PeriodLabel {
				data[i], data[j] = data[j], data[i]
			}
		}
	}

	c.Header("Content-Type", "text/csv; charset=utf-8")
	c.Header("Content-Disposition", "attachment; filename=trends.csv")
	c.Writer.Write([]byte{0xEF, 0xBB, 0xBF})

	w := csv.NewWriter(c.Writer)
	w.Write([]string{"时间段", "原始新增", "清洗新增"})
	for _, d := range data {
		w.Write([]string{d.PeriodLabel, fmt.Sprintf("%d", d.RawAdded), fmt.Sprintf("%d", d.CleanAdded)})
	}
	w.Flush()
}