package service

import (
	"fmt"
	"gorm.io/gorm"
	"psi/database"
	"psi/models"
	systemReq "psi/models/request"
	systemRes "psi/models/response"
	"sync"
	"time"
)

type StatistService struct{}

// DashboardStatist 获取仪表盘统计数据
/*func (s *StatistService) DashboardStatist(req systemReq.DashboardStatistRequest, db ...*gorm.DB) (*systemRes.DashboardStatistResponse, error) {
	databaseConn := database.OptionalDB(db...)

	now := time.Now()
	startOfDay := time.Date(now.Year(), now.Month(), now.Day(), 0, 0, 0, 0, now.Location()).Unix()
	endOfDay := time.Date(now.Year(), now.Month(), now.Day(), 23, 59, 59, 0, now.Location()).Unix()

	if req.StartDate == 0 {
		req.StartDate = startOfDay
	}
	if req.EndDate == 0 {
		req.EndDate = endOfDay
	}

	statDateStr := now.Format("20060102")
	var statDate int64
	if _, err := fmt.Sscanf(statDateStr, "%d", &statDate); err != nil {
		return s.getDashboardStatRealtime(databaseConn, req.StartDate, req.EndDate)
	}

	var dashboardStat models.DashboardDailyStat
	err := databaseConn.Where("stat_date = ? AND is_del = ?", statDate, 0).First(&dashboardStat).Error

	if err != nil {
		return s.getDashboardStatRealtime(databaseConn, req.StartDate, req.EndDate)
	}

	var userStats []systemRes.UserStatItem
	var userDailyStats []models.UserDailyStat
	if err := databaseConn.Where("stat_date = ? AND is_del = ?", statDate, 0).Find(&userDailyStats).Error; err == nil {
		for _, userStat := range userDailyStats {
			userStats = append(userStats, systemRes.UserStatItem{
				UserID:         userStat.UserID,
				UserName:       userStat.UserName,
				ReceivingCount: userStat.ReceivingNum,
				OutboundCount:  userStat.OutboundNum,
			})
		}
	}

	return &systemRes.DashboardStatistResponse{
		TotalReceivingCount: dashboardStat.TotalReceivingNum,
		TotalOutboundCount:  dashboardStat.TotalOutboundNum,
		TotalSaleCount:      dashboardStat.TodaySalesCount,
		UserStats:           userStats,
		ProductTotal:        dashboardStat.ProductTotal,
		InventoryTotal:      dashboardStat.InventoryTotal,
		TodayInbound:        dashboardStat.TodayInbound,
		TodayOutbound:       dashboardStat.TodayOutbound,
		YesterdayInbound:    dashboardStat.YesterdayInbound,
		YesterdayOutbound:   dashboardStat.YesterdayOutbound,
	}, nil
}*/

// DashboardStatist 获取仪表盘统计数据
/*func (s *StatistService) DashboardStatist(req systemReq.DashboardStatistRequest, db ...*gorm.DB) (*systemRes.DashboardStatistResponse, error) {
	databaseConn := database.OptionalDB(db...)

	// 计算今日的时间范围（如果未指定）
	now := time.Now()
	startOfDay := time.Date(now.Year(), now.Month(), now.Day(), 0, 0, 0, 0, now.Location()).Unix()
	endOfDay := time.Date(now.Year(), now.Month(), now.Day(), 23, 59, 59, 0, now.Location()).Unix()

	if req.StartDate == 0 {
		req.StartDate = startOfDay
	}
	if req.EndDate == 0 {
		req.EndDate = endOfDay
	}

	// 查询今日销售订单总数
	var totalSaleCount int64
	saleOrderQuery := databaseConn.Model(&models.SalesOrder{}).
		Where("created_at >= ? AND created_at <= ? AND is_del = ?", req.StartDate, req.EndDate, 0)
	saleOrderQuery.Count(&totalSaleCount)

	// 从 statist 表查询总入库和出库次数
	var totalReceivingCount, totalOutboundCount int64
	statistQuery := databaseConn.Model(&models.Statist{}).
		Where("stat_date >= ? AND stat_date <= ? AND is_del = ?", req.StartDate, req.EndDate, 0)

	var statistList []models.Statist
	if err := statistQuery.Find(&statistList).Error; err != nil {
		return nil, utils.NewError("查询统计数据失败")
	}

	if len(statistList) == 0 {
		return &systemRes.DashboardStatistResponse{
			TotalReceivingCount: 0,
			TotalOutboundCount:  0,
			TotalSaleCount:      totalSaleCount,
			UserStats:           []systemRes.UserStatItem{},
		}, nil
	}

	for _, stat := range statistList {
		totalReceivingCount += stat.ReceivingNum
		totalOutboundCount += stat.OutboundNum
	}

	// 查询个人统计数据
	var userStats []systemRes.UserStatItem

	// 按用户分组统计
	type UserStatGroup struct {
		CreateBy       int64 `gorm:"column:create_by"`
		TotalReceiving int64 `gorm:"column:total_receiving"`
		TotalOutbound  int64 `gorm:"column:total_outbound"`
	}

	var userStatGroups []UserStatGroup
	databaseConn.Model(&models.Statist{}).
		Select("create_by, SUM(receiving_num) as total_receiving, SUM(outbound_num) as total_outbound").
		Where("stat_date >= ? AND stat_date <= ? AND is_del = ?", req.StartDate, req.EndDate, 0).
		Group("create_by").
		Scan(&userStatGroups)

	// 获取用户信息并构建返回数据
	for _, group := range userStatGroups {
		var employee models.Employee
		if err := database.DB.Where("id = ?", group.CreateBy).First(&employee).Error; err == nil {
			userStats = append(userStats, systemRes.UserStatItem{
				UserID:         employee.ID,
				UserName:       employee.Username,
				ReceivingCount: group.TotalReceiving,
				OutboundCount:  group.TotalOutbound,
			})
		}
	}

	return &systemRes.DashboardStatistResponse{
		TotalReceivingCount: totalReceivingCount,
		TotalOutboundCount:  totalOutboundCount,
		TotalSaleCount:      totalSaleCount,
		UserStats:           userStats,
	}, nil
}*/
// DashboardStatist 获取仪表盘统计数据
// 统一口径：所有统计字段改为实时查原始单据表，不再依赖 dashboard_daily_stat 预计算表
func (s *StatistService) DashboardStatist(req systemReq.DashboardStatistRequest, db ...*gorm.DB) (*systemRes.DashboardStatistResponse, error) {
	databaseConn := database.OptionalDB(db...)

	now := time.Now()
	startOfDay := time.Date(now.Year(), now.Month(), now.Day(), 0, 0, 0, 0, now.Location()).Unix()
	endOfDay := time.Date(now.Year(), now.Month(), now.Day(), 23, 59, 59, 0, now.Location()).Unix()

	if req.StartDate == 0 {
		req.StartDate = startOfDay
	}
	if req.EndDate == 0 {
		req.EndDate = endOfDay
	}

	return s.getDashboardStatRealtime(databaseConn, req.StartDate, req.EndDate)
}

// getDashboardStatRealtime 实时查询仪表盘统计数据
// 统一口径：所有统计字段直接查原始单据表（sales_order / receiving_order / outbound_order）
// 所有独立查询通过 goroutine 并发执行，总耗时 = max(各查询耗时)
func (s *StatistService) getDashboardStatRealtime(databaseConn *gorm.DB, startDate, endDate int64) (*systemRes.DashboardStatistResponse, error) {
	endTime := time.Unix(endDate, 0)
	yesterdayTime := endTime.AddDate(0, 0, -1)

	todayStart := time.Date(endTime.Year(), endTime.Month(), endTime.Day(), 0, 0, 0, 0, endTime.Location()).Unix()
	todayEnd := time.Date(endTime.Year(), endTime.Month(), endTime.Day(), 23, 59, 59, 0, endTime.Location()).Unix()
	yesterdayStart := time.Date(yesterdayTime.Year(), yesterdayTime.Month(), yesterdayTime.Day(), 0, 0, 0, 0, yesterdayTime.Location()).Unix()
	yesterdayEnd := time.Date(yesterdayTime.Year(), yesterdayTime.Month(), yesterdayTime.Day(), 23, 59, 59, 0, yesterdayTime.Location()).Unix()

	// 结果结构体
	type salesTodayYesterday struct {
		TodayCount     int64 `gorm:"column:today_count"`
		YesterdayCount int64 `gorm:"column:yesterday_count"`
	}
	type receivingTodayYesterday struct {
		TodayCount     int64 `gorm:"column:today_count"`
		YesterdayCount int64 `gorm:"column:yesterday_count"`
	}
	type outboundTodayYesterday struct {
		TodayCount     int64 `gorm:"column:today_count"`
		YesterdayCount int64 `gorm:"column:yesterday_count"`
	}
	type UserStatGroup struct {
		CreateBy       int64 `gorm:"column:create_by"`
		TotalReceiving int64 `gorm:"column:total_receiving"`
		TotalOutbound  int64 `gorm:"column:total_outbound"`
	}

	var (
		wg             sync.WaitGroup
		salesStat      salesTodayYesterday
		receivingStat  receivingTodayYesterday
		outboundStat   outboundTodayYesterday
		userStatGroups []UserStatGroup
		productTotal   int64
		inventoryTotal int64
		statErr        error
		receivingErr   error
		outboundErr    error
		userErr        error
		productErr     error
		inventoryErr   error
	)

	// 1. 销售订单统计（并发）
	wg.Add(1)
	go func() {
		defer wg.Done()
		statErr = databaseConn.Model(&models.SalesOrder{}).
			Select(`
				COALESCE(SUM(CASE WHEN created_at >= ? AND created_at <= ? THEN total_amount ELSE 0 END), 0) as today_count,
				COALESCE(SUM(CASE WHEN created_at >= ? AND created_at <= ? THEN total_amount ELSE 0 END), 0) as yesterday_count
			`, todayStart, todayEnd, yesterdayStart, yesterdayEnd).
			Where("is_del = ?", 0).
			Scan(&salesStat).Error
	}()

	// 2. 入库单统计（并发）
	wg.Add(1)
	go func() {
		defer wg.Done()
		receivingErr = databaseConn.Model(&models.ReceivingOrder{}).
			Select(`
				COALESCE(SUM(CASE WHEN created_at >= ? AND created_at <= ? THEN 1 ELSE 0 END), 0) as today_count,
				COALESCE(SUM(CASE WHEN created_at >= ? AND created_at <= ? THEN 1 ELSE 0 END), 0) as yesterday_count
			`, todayStart, todayEnd, yesterdayStart, yesterdayEnd).
			Where("is_del = ?", 0).
			Scan(&receivingStat).Error
	}()

	// 3. 出库单统计（并发）
	wg.Add(1)
	go func() {
		defer wg.Done()
		outboundErr = databaseConn.Model(&models.OutboundOrder{}).
			Select(`
				COALESCE(SUM(CASE WHEN created_at >= ? AND created_at <= ? THEN 1 ELSE 0 END), 0) as today_count,
				COALESCE(SUM(CASE WHEN created_at >= ? AND created_at <= ? THEN 1 ELSE 0 END), 0) as yesterday_count
			`, todayStart, todayEnd, yesterdayStart, yesterdayEnd).
			Where("is_del = ?", 0).
			Scan(&outboundStat).Error
	}()

	// 4. 用户个人统计（并发）
	wg.Add(1)
	go func() {
		defer wg.Done()
		singleDayYYYYMMDD := fmt.Sprintf("%04d%02d%02d", endTime.Year(), endTime.Month(), endTime.Day())
		var singleDayStatDate int64
		fmt.Sscanf(singleDayYYYYMMDD, "%d", &singleDayStatDate)
		userErr = databaseConn.Model(&models.Statist{}).
			Select("create_by, SUM(receiving_num) as total_receiving, SUM(outbound_num) as total_outbound").
			Where("stat_date = ? AND is_del = ?", singleDayStatDate, 0).
			Group("create_by").
			Scan(&userStatGroups).Error
	}()

	// 5. 商品总数（并发）
	wg.Add(1)
	go func() {
		defer wg.Done()
		productErr = databaseConn.Model(&models.Product{}).Where("is_del = ?", 0).Count(&productTotal).Error
	}()

	// 6. 库存总量（并发）
	wg.Add(1)
	go func() {
		defer wg.Done()
		inventoryErr = databaseConn.Model(&models.Inventory{}).Where("is_del = ?", 0).Select("COALESCE(SUM(quantity), 0)").Row().Scan(&inventoryTotal)
	}()

	// 等待所有 goroutine 完成
	wg.Wait()

	// 检查关键查询错误（订单/入库/出库为核心统计，出错则返回）
	if statErr != nil {
		return nil, statErr
	}
	if receivingErr != nil {
		return nil, receivingErr
	}
	if outboundErr != nil {
		return nil, outboundErr
	}
	// 用户统计、商品、库存出错不阻塞，仅打印并置零
	if userErr != nil {
		userStatGroups = nil
	}
	if productErr != nil {
		productTotal = 0
	}
	if inventoryErr != nil {
		inventoryTotal = 0
	}

	// 根据用户统计结果批量查询员工信息
	var userStats []systemRes.UserStatItem
	if len(userStatGroups) > 0 {
		userIDs := make([]int64, len(userStatGroups))
		for i, group := range userStatGroups {
			userIDs[i] = group.CreateBy
		}
		var employees []models.Employee
		if err := database.DB.Where("id IN ?", userIDs).Find(&employees).Error; err == nil {
			empMap := make(map[int64]models.Employee, len(employees))
			for _, emp := range employees {
				empMap[emp.ID] = emp
			}
			for _, group := range userStatGroups {
				if emp, ok := empMap[group.CreateBy]; ok {
					userStats = append(userStats, systemRes.UserStatItem{
						UserID:         emp.ID,
						UserName:       emp.Username,
						ReceivingCount: group.TotalReceiving,
						OutboundCount:  group.TotalOutbound,
					})
				}
			}
		}
	}

	return &systemRes.DashboardStatistResponse{
		TotalOrderCount:         salesStat.TodayCount,
		TotalReceivingCount:     receivingStat.TodayCount,
		TotalOutboundCount:      outboundStat.TodayCount,
		TodaySaleAmount:         salesStat.TodayCount,
		YesterdayOrderCount:     salesStat.YesterdayCount,
		YesterdayReceivingCount: receivingStat.YesterdayCount,
		YesterdayOutboundCount:  outboundStat.YesterdayCount,
		YesterdaySaleAmount:     salesStat.YesterdayCount,
		UserStats:               userStats,
		ProductTotal:            productTotal,
		InventoryTotal:          inventoryTotal,
	}, nil
}

// GetWarehouseStatist 获取仓库统计数据
func (s *StatistService) GetWarehouseStatist(req systemReq.WarehouseStatistRequest, db ...*gorm.DB) (*systemRes.WarehouseStatistResponse, error) {
	databaseConn := database.OptionalDB(db...)
	now := time.Now()
	yesterdayTime := now.AddDate(0, 0, -1)

	// statist.stat_date 存储 YYYYMMDD 格式 int64，需转换为 YYYYMMDD 再比较
	todayYYYYMMDD := fmt.Sprintf("%04d%02d%02d", now.Year(), now.Month(), now.Day())
	yesterdayYYYYMMDD := fmt.Sprintf("%04d%02d%02d", yesterdayTime.Year(), yesterdayTime.Month(), yesterdayTime.Day())
	var todayStatDate, yesterdayStatDate int64
	fmt.Sscanf(todayYYYYMMDD, "%d", &todayStatDate)
	fmt.Sscanf(yesterdayYYYYMMDD, "%d", &yesterdayStatDate)

	// 若用户传了日期范围，从 req.EndDate 反算 YYYYMMDD；否则默认今天
	var reqEndStatDate int64
	if req.EndDate != 0 {
		reqEndTime := time.Unix(req.EndDate, 0)
		reqEndYYYYMMDD := fmt.Sprintf("%04d%02d%02d", reqEndTime.Year(), reqEndTime.Month(), reqEndTime.Day())
		fmt.Sscanf(reqEndYYYYMMDD, "%d", &reqEndStatDate)
	} else {
		reqEndStatDate = todayStatDate
	}

	var productTotal int64
	databaseConn.Model(&models.Product{}).Where("is_del = ?", 0).Count(&productTotal)

	var inventoryTotal int64
	databaseConn.Model(&models.Inventory{}).Where("is_del = ?", 0).Select("COALESCE(SUM(quantity), 0)").Row().Scan(&inventoryTotal)

	type DailyStat struct {
		TodayInbound      int64 `gorm:"column:today_inbound"`
		TodayOutbound     int64 `gorm:"column:today_outbound"`
		YesterdayInbound  int64 `gorm:"column:yesterday_inbound"`
		YesterdayOutbound int64 `gorm:"column:yesterday_outbound"`
	}

	// 计算昨天对应的 YYYYMMDD（基于 reqEndDate 或今天）
	var yesterdayReqStatDate int64
	if req.EndDate != 0 {
		yesterdayReqTime := time.Unix(req.EndDate, 0).AddDate(0, 0, -1)
		yesterdayReqYYYYMMDD := fmt.Sprintf("%04d%02d%02d", yesterdayReqTime.Year(), yesterdayReqTime.Month(), yesterdayReqTime.Day())
		fmt.Sscanf(yesterdayReqYYYYMMDD, "%d", &yesterdayReqStatDate)
	} else {
		yesterdayReqStatDate = yesterdayStatDate
	}

	var dailyStat DailyStat
	databaseConn.Model(&models.Statist{}).
		Select(`
			COALESCE(SUM(CASE WHEN stat_date = ? THEN receiving_num ELSE 0 END), 0) as today_inbound,
			COALESCE(SUM(CASE WHEN stat_date = ? THEN outbound_num ELSE 0 END), 0) as today_outbound,
			COALESCE(SUM(CASE WHEN stat_date = ? THEN receiving_num ELSE 0 END), 0) as yesterday_inbound,
			COALESCE(SUM(CASE WHEN stat_date = ? THEN outbound_num ELSE 0 END), 0) as yesterday_outbound
		`, reqEndStatDate, reqEndStatDate, yesterdayReqStatDate, yesterdayReqStatDate).
		Where("is_del = ?", 0).
		Scan(&dailyStat)

	return &systemRes.WarehouseStatistResponse{
		ProductTotal:      productTotal,
		InventoryTotal:    inventoryTotal,
		TodayInbound:      dailyStat.TodayInbound,
		TodayOutbound:     dailyStat.TodayOutbound,
		YesterdayInbound:  dailyStat.YesterdayInbound,
		YesterdayOutbound: dailyStat.YesterdayOutbound,
	}, nil
}

// GetOrderStatist 获取订单统计数据
func (s *StatistService) GetOrderStatist(req systemReq.OrderStatistRequest, db ...*gorm.DB) (*systemRes.OrderStatistResponse, error) {
	databaseConn := database.OptionalDB(db...)

	now := time.Now()
	startOfDay := time.Date(now.Year(), now.Month(), now.Day(), 0, 0, 0, 0, now.Location()).Unix()
	endOfDay := time.Date(now.Year(), now.Month(), now.Day(), 23, 59, 59, 0, now.Location()).Unix()
	yesterdayStart := time.Date(now.Year(), now.Month(), now.Day()-1, 0, 0, 0, 0, now.Location()).Unix()
	yesterdayEnd := time.Date(now.Year(), now.Month(), now.Day()-1, 23, 59, 59, 0, now.Location()).Unix()

	if req.StartDate == 0 {
		req.StartDate = startOfDay
	}
	if req.EndDate == 0 {
		req.EndDate = endOfDay
	}

	const (
		StatusDraft     = 1
		StatusConfirmed = 2
		StatusShipped   = 5
		StatusCancelled = 6
	)

	type OrderDailyStat struct {
		TodayQuantity      int64 `gorm:"column:today_quantity"`
		TodayAmount        int64 `gorm:"column:today_amount"`
		TodayShipped       int64 `gorm:"column:today_shipped"`
		TodayUnshipped     int64 `gorm:"column:today_unshipped"`
		TodayPending       int64 `gorm:"column:today_pending"`
		YesterdayQuantity  int64 `gorm:"column:yesterday_quantity"`
		YesterdayAmount    int64 `gorm:"column:yesterday_amount"`
		YesterdayShipped   int64 `gorm:"column:yesterday_shipped"`
		YesterdayUnshipped int64 `gorm:"column:yesterday_unshipped"`
		YesterdayPending   int64 `gorm:"column:yesterday_pending"`
	}

	var orderStat OrderDailyStat
	databaseConn.Model(&models.SalesOrder{}).
		Select(`
			COALESCE(SUM(CASE WHEN created_at >= ? AND created_at <= ? THEN 1 ELSE 0 END), 0) as today_quantity,
			COALESCE(SUM(CASE WHEN created_at >= ? AND created_at <= ? THEN total_amount ELSE 0 END), 0) as today_amount,
			COALESCE(SUM(CASE WHEN created_at >= ? AND created_at <= ? AND status = ? THEN 1 ELSE 0 END), 0) as today_shipped,
			COALESCE(SUM(CASE WHEN created_at >= ? AND created_at <= ? AND status NOT IN (?, ?) THEN 1 ELSE 0 END), 0) as today_unshipped,
			COALESCE(SUM(CASE WHEN created_at >= ? AND created_at <= ? AND status IN (?, ?) THEN 1 ELSE 0 END), 0) as today_pending,
			COALESCE(SUM(CASE WHEN created_at >= ? AND created_at <= ? THEN 1 ELSE 0 END), 0) as yesterday_quantity,
			COALESCE(SUM(CASE WHEN created_at >= ? AND created_at <= ? THEN total_amount ELSE 0 END), 0) as yesterday_amount,
			COALESCE(SUM(CASE WHEN created_at >= ? AND created_at <= ? AND status = ? THEN 1 ELSE 0 END), 0) as yesterday_shipped,
			COALESCE(SUM(CASE WHEN created_at >= ? AND created_at <= ? AND status NOT IN (?, ?) THEN 1 ELSE 0 END), 0) as yesterday_unshipped,
			COALESCE(SUM(CASE WHEN created_at >= ? AND created_at <= ? AND status IN (?, ?) THEN 1 ELSE 0 END), 0) as yesterday_pending
		`, req.StartDate, req.EndDate, req.StartDate, req.EndDate, req.StartDate, req.EndDate, StatusShipped, req.StartDate, req.EndDate, StatusShipped, StatusCancelled, req.StartDate, req.EndDate, StatusDraft, StatusConfirmed,
			yesterdayStart, yesterdayEnd, yesterdayStart, yesterdayEnd, yesterdayStart, yesterdayEnd, StatusShipped, yesterdayStart, yesterdayEnd, StatusShipped, StatusCancelled, yesterdayStart, yesterdayEnd, StatusDraft, StatusConfirmed).
		Where("is_del = ? AND created_at >= ? AND created_at <= ?", 0, yesterdayStart, req.EndDate).
		Scan(&orderStat)

	return &systemRes.OrderStatistResponse{
		Today: systemRes.OrderStatItem{
			Quantity:  orderStat.TodayQuantity,
			Amount:    orderStat.TodayAmount,
			Shipped:   orderStat.TodayShipped,
			Unshipped: orderStat.TodayUnshipped,
			Pending:   orderStat.TodayPending,
		},
		Yesterday: systemRes.OrderStatItem{
			Quantity:  orderStat.YesterdayQuantity,
			Amount:    orderStat.YesterdayAmount,
			Shipped:   orderStat.YesterdayShipped,
			Unshipped: orderStat.YesterdayUnshipped,
			Pending:   orderStat.YesterdayPending,
		},
	}, nil
}