package dashboard

import (
	"context"
	"fmt"
	"math"
	"strconv"
	"strings"
	"time"

	"github.com/tidbcloud/devops/entity"
	tntent "github.com/tidbcloud/devops/entity/tenant"
	"github.com/tidbcloud/devops/pkg/infra/clients/cliniccli"
	"github.com/tidbcloud/devops/pkg/infra/config"
	gormutil "github.com/tidbcloud/devops/pkg/infra/utils/gorm"
	"github.com/tidbcloud/devops/pkg/service"
	tenantsvc "github.com/tidbcloud/devops/pkg/service/tenant"

	"github.com/go-logr/logr"
	promapi "github.com/prometheus/client_golang/api"
	promv1 "github.com/prometheus/client_golang/api/prometheus/v1"
	prommodel "github.com/prometheus/common/model"
	"github.com/tidbcloud/pkgv2/db"
	"go.uber.org/dig"
	"gorm.io/gorm"
)

var (
	_ DashboardService = &svc{}
)

type ServiceParam struct {
	dig.In

	Conf          *config.RootConfig
	Logger        logr.Logger
	DBProvider    db.TransProvider
	TenantService tenantsvc.TenantServiceAPI
	ClinicClient  cliniccli.Client
}

type ServiceResult struct {
	dig.Out

	API     DashboardServiceAPI
	Service service.Service `group:"service"`
}

type svc struct {
	*service.BaseService

	slaDB                 db.TransProvider
	serverlessSlaDB       db.TransProvider
	devopsDB              db.TransProvider
	tenantService         tenantsvc.TenantServiceAPI
	eePrometheusPrefix    string
	clinicClient          cliniccli.Client
	sloCache              *SLOCache
	slaCache              slaCacheAPI
	dedicatedClusterCache *ClusterCache
	nextGenClusterCache   *ClusterCache
}

func NewService(p ServiceParam) (ServiceResult, error) {
	base := service.NewBaseService("DashboardService", service.WithDummy(p.Conf.Services.Dashboard.Dummy))
	if p.Conf.Services.Dashboard.Dummy {
		return ServiceResult{
			API:     &DummyDashboardServiceAPI{},
			Service: base,
		}, nil
	}

	s := &svc{
		BaseService:        base,
		tenantService:      p.TenantService,
		eePrometheusPrefix: p.Conf.Services.Dashboard.EEPrometheusEndpoint,
		clinicClient:       p.ClinicClient,
		devopsDB:           p.DBProvider,
	}
	handle, err := gormutil.Connect(*p.Conf.Services.Dashboard.SlaDB, "DashboardSlaDB")
	if err != nil {
		return ServiceResult{}, err
	}
	s.slaDB = db.NewProvider(handle)
	// serverless sla db
	handle, err = gormutil.Connect(*p.Conf.Services.Dashboard.ServerlessSlaDB, "DashboardServerlessSlaDB")
	if err != nil {
		return ServiceResult{}, err
	}
	s.serverlessSlaDB = db.NewProvider(handle)

	s.sloCache = newSLOCache(p.Logger,
		p.Conf.Services.Dashboard.BasePrometheusEndpoint,
		p.Conf.Services.Dashboard.SyncSLOInterval,
	)
	s.dedicatedClusterCache = newDedicatedClusterCache(p.Logger,
		p.Conf.Services.Dashboard.SyncDedicatedClusterInterval,
		p.ClinicClient,
	)
	s.nextGenClusterCache = newNextGenClusterCache(p.Logger,
		p.Conf.Services.Dashboard.SyncDedicatedClusterInterval,
		p.ClinicClient,
	)
	s.slaCache = newSLACache(p.Logger,
		p.Conf.Services.Dashboard.SyncSLAInterval,
		s.slaDB,
		s.serverlessSlaDB,
		nil,
	)
	s.startSLAWarmup()

	return ServiceResult{
		API:     s,
		Service: s,
	}, nil
}

func (s *svc) GetSLAInfo(ctx context.Context, startTs, endTs int64) (*entity.SLAInfo, error) {
	months := getMonthsFromTimestamp(startTs, endTs, slaCacheMonthFormat)
	return s.slaCache.Get(ctx, months)
}

func (s *svc) startSLAWarmup() {
	go func() {
		ctx := s.WrapContext(context.Background())
		if err := s.slaCache.Warmup(ctx); err != nil {
			s.Logger(ctx).Error(err, "warm up SLA cache failed")
		}
	}()
}

type SLADetail struct {
	Month             string
	ClusterID         string `gorm:"column:cluster_id"`
	ClusterName       string `gorm:"column:cluster_name"`
	ClusterDeployType string `gorm:"column:cluster_deploy_type"`
	Status            entity.SLAStatus
	Value             float64
	TiDBInstanceCount int    `gorm:"column:tidb_instance_count"`
	TenantID          string `gorm:"column:tenant_id"`
	TenantName        string `gorm:"column:tenant_name"`
	IssueKey          string `gorm:"column:issue_key"`
	IssueStatus       string `gorm:"column:issue_status"`
}

func (SLADetail) TableName() string {
	return "sla"
}

func (s *svc) GetMonthlySummaryOfDedicatedSLA(ctx context.Context,
	month string,
	filter entity.DedicatedSLAFilter,
) (*entity.DedicatedClusterSLABasicList, error) {
	var data []SLADetail
	rdb := s.generateSQLForGetMonthlySummaryOfDedicatedSLA(
		s.slaDB.ReadDB(ctx),
		month, filter)
	err := rdb.Find(&data).Error
	if err != nil {
		return nil, fmt.Errorf("get sla detail failed: %w", err)
	}
	s.Logger(ctx).Info("find data", "data", len(data))

	aliveDedicatedClustersMap, err := s.dedicatedClusterCache.Get()
	if err != nil {
		return nil, err
	}
	result := make([]entity.DedicatedClusterSLABasic, 0, len(data))
	for _, item := range data {
		cluster := entity.DedicatedClusterSLABasic{
			ClusterID:    item.ClusterID,
			ClusterName:  item.ClusterName,
			TenantID:     item.TenantID,
			TenantName:   item.TenantName,
			TiDBInstance: item.TiDBInstanceCount,
			Deleted:      false,
			SLAValue:     item.Value,
			SLAStatus:    item.Status,
			IssueKey:     item.IssueKey,
			IssueStatus:  item.IssueStatus,
		}
		if _, ok := aliveDedicatedClustersMap[cluster.ClusterID]; !ok {
			if !filter.IncludeDeleted {
				continue
			}
			cluster.Deleted = true
		}
		result = append(result, cluster)
	}
	return &entity.DedicatedClusterSLABasicList{Items: result}, nil
}

func (s *svc) generateSQLForGetMonthlySummaryOfDedicatedSLA(
	rdb *gorm.DB,
	month string, filter entity.DedicatedSLAFilter,
) *gorm.DB {
	rdb = rdb.Model(&SLADetail{}).
		Where("month = ? AND tenant_kind='external'", month)
	if filter.Search != "" {
		search := strings.ToLower(strings.TrimSpace(filter.Search))
		if _, err := strconv.ParseInt(search, 10, 64); err == nil {
			rdb = rdb.Where("cluster_id = ? OR tenant_id = ?", search, search)
		} else {
			rdb = rdb.Where(
				`LOWER(cluster_name) LIKE ? OR LOWER(tenant_name) LIKE ?`,
				"%"+search+"%", "%"+search+"%")
		}
	}
	if filter.TiDBInstanceMode == entity.TiDBInstanceSingle {
		rdb = rdb.Where("tidb_instance_count = 1")
	} else {
		rdb = rdb.Where("tidb_instance_count > 1")
	}
	if len(filter.Status) > 0 {
		rdb = rdb.Where("status IN ?", filter.Status)
	}

	return rdb.Order("month ASC, value ASC, cluster_id ASC")
}

func (s *svc) GetMonthlySummaryOfNextGenSLA(ctx context.Context,
	month string,
	filter entity.NextGenSLAFilter,
) (*entity.NextGenClusterSLABasicList, error) {
	var data []SLADetail
	rdb := s.generateSQLForGetMonthlySummaryOfNextGenSLA(
		s.slaDB.ReadDB(ctx),
		month, filter)
	err := rdb.Find(&data).Error
	if err != nil {
		return nil, fmt.Errorf("get next-gen sla detail failed: %w", err)
	}
	s.Logger(ctx).Info("find next-gen sla data", "data", len(data))

	aliveNextGenClustersMap, err := s.nextGenClusterCache.Get()
	if err != nil {
		return nil, err
	}
	result := make([]entity.NextGenClusterSLABasic, 0, len(data))
	for _, item := range data {
		cluster := entity.NextGenClusterSLABasic{
			ClusterID:         item.ClusterID,
			ClusterName:       item.ClusterName,
			ClusterDeployType: item.ClusterDeployType,
			TenantID:          item.TenantID,
			TenantName:        item.TenantName,
			Deleted:           false,
			SLAValue:          item.Value,
			SLAStatus:         item.Status,
			IssueKey:          item.IssueKey,
			IssueStatus:       item.IssueStatus,
		}
		if _, ok := aliveNextGenClustersMap[cluster.ClusterID]; !ok {
			if !filter.IncludeDeleted {
				continue
			}
			cluster.Deleted = true
		}
		result = append(result, cluster)
	}
	return &entity.NextGenClusterSLABasicList{Items: result}, nil
}

func (s *svc) generateSQLForGetMonthlySummaryOfNextGenSLA(
	rdb *gorm.DB,
	month string, filter entity.NextGenSLAFilter,
) *gorm.DB {
	rdb = rdb.Model(&SLADetail{}).
		Where("month = ? AND tenant_kind='external'", month)
	if filter.Search != "" {
		search := strings.ToLower(strings.TrimSpace(filter.Search))
		if _, err := strconv.ParseInt(search, 10, 64); err == nil {
			rdb = rdb.Where("cluster_id = ? OR tenant_id = ?", search, search)
		} else {
			rdb = rdb.Where(
				`LOWER(cluster_name) LIKE ? OR LOWER(tenant_name) LIKE ?`,
				"%"+search+"%", "%"+search+"%")
		}
	}

	if filter.ClusterDeployType != "" {
		rdb = rdb.Where("cluster_deploy_type = ?", filter.ClusterDeployType)
	}

	if len(filter.Status) > 0 {
		rdb = rdb.Where("status IN ?", filter.Status)
	}

	return rdb.Order("month ASC, value ASC, cluster_id ASC")
}

type SLADowntime struct {
	Timestamp   int64  `gorm:"column:timestamp"`
	Fake        bool   `gorm:"column:fake"`
	FakeComment string `gorm:"column:comment"`
}

func (SLADowntime) TableName() string {
	return "sli_dedicated_cluster_downtime"
}

func (s *svc) GetClusterDetailOfDedicatedSLA(ctx context.Context,
	month string,
	clusterID string,
	includeFake bool,
) (*entity.DedicatedClusterSLADetail, error) {
	rdb := s.slaDB.ReadDB(ctx)
	rdb = rdb.Model(&SLADetail{}).
		Where("month = ?", month).
		Where("cluster_id = ?", clusterID)
	var basic SLADetail
	err := rdb.Model(&SLADetail{}).
		Where("month = ?", month).
		Where("cluster_id = ?", clusterID).
		First(&basic).Error
	if err != nil {
		return nil, fmt.Errorf("get sla detail failed: %w", err)
	}
	aliveDedicatedClustersMap, err := s.dedicatedClusterCache.Get()
	if err != nil {
		return nil, err
	}
	clusterIsDeleted := true
	if _, ok := aliveDedicatedClustersMap[clusterID]; ok {
		clusterIsDeleted = false
	}

	startTs, endTs, err := getBeginAndEndTsFromMonth(month, "2006/1")
	if err != nil {
		return nil, err
	}
	rdb1 := s.slaDB.ReadDB(ctx)
	rdb1 = rdb1.Model(&SLADowntime{}).
		Where("timestamp BETWEEN ? AND ?", startTs, endTs).
		Where("cluster_id = ?", clusterID)
	if !includeFake {
		rdb1 = rdb1.Where("fake = 0")
	}
	var downtimes []SLADowntime
	err = rdb1.Find(&downtimes).Error
	if err != nil {
		return nil, fmt.Errorf("get sla downtime failed: %w", err)
	}
	downtimeList := make([]entity.ClusterDowntime, 0, len(downtimes))
	for _, item := range downtimes {
		downtimeList = append(downtimeList, entity.ClusterDowntime{
			Timestamp: fmt.Sprintf("%d", item.Timestamp),
			Fake:      item.Fake,
			Comment:   item.FakeComment,
		})
	}

	return &entity.DedicatedClusterSLADetail{
		DedicatedClusterSLABasic: entity.DedicatedClusterSLABasic{
			ClusterID:    basic.ClusterID,
			ClusterName:  basic.ClusterName,
			TenantID:     basic.TenantID,
			TenantName:   basic.TenantName,
			TiDBInstance: basic.TiDBInstanceCount,
			Deleted:      clusterIsDeleted,
			SLAValue:     basic.Value,
			SLAStatus:    basic.Status,
			IssueKey:     basic.IssueKey,
			IssueStatus:  basic.IssueStatus,
		},
		DowntimeList: downtimeList,
	}, nil
}

func (s *svc) GetClusterDetailOfNextGenSLA(ctx context.Context,
	month string,
	clusterID string,
	includeFake bool,
) (*entity.NextGenClusterSLADetail, error) {
	rdb := s.slaDB.ReadDB(ctx)
	rdb = rdb.Model(&SLADetail{}).
		Where("month = ?", month).
		Where("cluster_id = ?", clusterID)
	var basic SLADetail
	err := rdb.First(&basic).Error
	if err != nil {
		return nil, fmt.Errorf("get next-gen cluster sla detail failed: %w", err)
	}
	aliveNextGenClustersMap, err := s.nextGenClusterCache.Get()
	if err != nil {
		return nil, err
	}
	clusterIsDeleted := true
	if _, ok := aliveNextGenClustersMap[clusterID]; ok {
		clusterIsDeleted = false
	}

	startTs, endTs, err := getBeginAndEndTsFromMonth(month, "2006/1")
	if err != nil {
		return nil, err
	}
	rdb1 := s.slaDB.ReadDB(ctx)
	rdb1 = rdb1.Model(&SLADowntime{}).
		Where("timestamp BETWEEN ? AND ?", startTs, endTs).
		Where("cluster_id = ?", clusterID)
	if !includeFake {
		rdb1 = rdb1.Where("fake = 0")
	}
	var downtimes []SLADowntime
	err = rdb1.Find(&downtimes).Error
	if err != nil {
		return nil, fmt.Errorf("get next-gen cluster sla downtime failed: %w", err)
	}
	downtimeList := make([]entity.ClusterDowntime, 0, len(downtimes))
	for _, item := range downtimes {
		downtimeList = append(downtimeList, entity.ClusterDowntime{
			Timestamp: fmt.Sprintf("%d", item.Timestamp),
			Fake:      item.Fake,
			Comment:   item.FakeComment,
		})
	}

	return &entity.NextGenClusterSLADetail{
		NextGenClusterSLABasic: entity.NextGenClusterSLABasic{
			ClusterID:         basic.ClusterID,
			ClusterName:       basic.ClusterName,
			ClusterDeployType: basic.ClusterDeployType,
			TenantID:          basic.TenantID,
			TenantName:        basic.TenantName,
			Deleted:           clusterIsDeleted,
			SLAValue:          basic.Value,
			SLAStatus:         basic.Status,
			IssueKey:          basic.IssueKey,
			IssueStatus:       basic.IssueStatus,
		},
		DowntimeList: downtimeList,
	}, nil
}

func (s *svc) GetSLOInfo(ctx context.Context) (*entity.SLOInfo, error) {
	slos, err := s.sloCache.GetSLOCache()
	if err != nil {
		return nil, err
	}
	return &entity.SLOInfo{CurrentSummary: slos}, nil
}

func (s *svc) GetIncidentInfo(ctx context.Context,
	startTs, endTs int64,
) (*entity.IncidentInfo, error) {
	var total int
	rdb := s.devopsDB.ReadDB(ctx)
	err := rdb.Raw(
		`SELECT count(1) AS total 
				FROM incidents 
				WHERE created_at BETWEEN FROM_UNIXTIME(?) AND FROM_UNIXTIME(?) 
				AND incident_start_time is not null
			`, startTs, endTs).Scan(&total).Error
	if err != nil {
		return nil, fmt.Errorf("get incident total failed: %w", err)
	}

	var mttr float64
	err = rdb.Raw(
		`SELECT
			COALESCE(AVG(i.ttr),0) as mttr 
		FROM(
			SELECT 
				t2.total as total,
				t1.row_number as rownumber,
				t1.ttr as ttr
			FROM(
				SELECT 
						ROW_NUMBER() OVER w AS 'row_number',
						TIMESTAMPDIFF(MINUTE, incident_start_time,incident_resolve_time) AS ttr
				FROM incidents
				WHERE created_at BETWEEN FROM_UNIXTIME(?) AND FROM_UNIXTIME(?) 
					AND incident_resolve_time is not null 
					AND incident_start_time is not null
				WINDOW w AS (ORDER BY TIMESTAMPDIFF(MINUTE, incident_start_time,incident_resolve_time))
			) AS t1,
			(
				SELECT count(1) as total
				FROM incidents
				WHERE created_at BETWEEN FROM_UNIXTIME(?) AND FROM_UNIXTIME(?) 
					AND incident_resolve_time is not null 
					AND incident_start_time is not null
			) AS t2
		) AS i
		WHERE
			i.rownumber IN (FLOOR((i.total+1) / 2), CEIL((i.total+1) / 2));
			`, startTs, endTs, startTs, endTs).Scan(&mttr).Error
	if err != nil {
		return nil, fmt.Errorf("get incident avg ttr failed: %w", err)
	}

	var data []entity.MonthlyTrend
	err = rdb.Raw(
		`SELECT
			i.month AS month,
			COALESCE(AVG(i.ttr),0) as value 
		FROM(
			SELECT 
				t1.month as month,
				t2.total as total,
				t1.row_num as row_num,
				t1.ttr as ttr
			FROM(
				SELECT 
					DATE_FORMAT(incident_start_time, '%Y-%m') AS month,
					ROW_NUMBER() OVER w AS row_num,
					TIMESTAMPDIFF(MINUTE, incident_start_time,incident_resolve_time) AS ttr
				FROM incidents 
				WHERE created_at BETWEEN FROM_UNIXTIME(?) AND FROM_UNIXTIME(?) 
					AND incident_resolve_time is not null 
					AND incident_start_time is not null
				WINDOW w AS (PARTITION BY DATE_FORMAT(incident_start_time, '%Y-%m') ORDER BY TIMESTAMPDIFF(MINUTE, incident_start_time,incident_resolve_time))
			) AS t1,
			(
				SELECT 
					DATE_FORMAT(incident_start_time, '%Y-%m') AS month,
					count(1) as total
				FROM incidents
				WHERE created_at BETWEEN FROM_UNIXTIME(?) AND FROM_UNIXTIME(?) 
					AND incident_resolve_time is not null 
					AND incident_start_time is not null
				GROUP BY month
			) AS t2
			WHERE t1.month=t2.month
		) AS i
		WHERE i.row_num IN (FLOOR((i.total+1) / 2), CEIL((i.total+1) / 2))
		GROUP BY month
		ORDER BY month ASC;
			`, startTs, endTs, startTs, endTs).Scan(&data).Error
	if err != nil {
		return nil, fmt.Errorf("get incident ttr trend failed: %w", err)
	}
	return &entity.IncidentInfo{
		Total:             total,
		MTTRInMinutes:     mttr,
		MTTRMonthlyTrends: data,
	}, nil
}

func (s *svc) GetCloudOncallInfo(ctx context.Context, startTs, endTs int64) (*entity.CloudOncallInfo, error) {
	var total int
	rdb := s.devopsDB.ReadDB(ctx)
	err := rdb.Raw(
		`SELECT 
				count(id) 
				FROM jirainterval.jira_issueview 
				WHERE project_id in (10417,10021)
				AND created_time BETWEEN FROM_UNIXTIME(?) AND FROM_UNIXTIME(?) 
			`, startTs, endTs).Scan(&total).Error
	if err != nil {
		return nil, fmt.Errorf("get cloud oncall total failed: %w", err)
	}

	var backlogTotal int
	err = rdb.Raw(
		`
			SELECT 
				count(id) 
				FROM jirainterval.jira_issueview 
				WHERE project_id in (10417,10021)
				AND ttr is null
				AND created_time BETWEEN FROM_UNIXTIME(?) AND FROM_UNIXTIME(?) 
			`, startTs, endTs).Scan(&backlogTotal).Error
	if err != nil {
		return nil, fmt.Errorf("get cloud oncall backlog total failed: %w", err)
	}

	var monthlyTrends []entity.MonthlyTrend
	err = rdb.Raw(
		`SELECT
				DATE_FORMAT(created_time, '%Y-%m') AS month,
				COUNT(id) AS value
				FROM jirainterval.jira_issueview
				WHERE project_id in (10417,10021)
				AND created_time BETWEEN FROM_UNIXTIME(?) AND FROM_UNIXTIME(?)
				GROUP BY month
				ORDER BY month ASC
			`, startTs, endTs).Scan(&monthlyTrends).Error
	if err != nil {
		return nil, fmt.Errorf("get cloud oncall monthly trends failed: %w", err)
	}

	var ttrInDays float64
	err = rdb.Raw(
		`SELECT COALESCE(AVG(ttr),0)
				FROM jirainterval.jira_issueview 
				WHERE project_id in (10417,10021)
				AND ttr is not null
				AND created_time BETWEEN FROM_UNIXTIME(?) AND FROM_UNIXTIME(?)
			`, startTs, endTs).Scan(&ttrInDays).Error
	if err != nil {
		return nil, fmt.Errorf("get cloud oncall ttr failed: %w", err)
	}

	var componentMonthlyTrends []entity.SeriesMonthlyTrend
	err = rdb.Raw(
		`SELECT 
				DATE_FORMAT(created_time, '%Y-%m') AS month,
				component AS series,
				count(id) as value
				FROM jirainterval.jira_issueview 
				WHERE project_id in (10417,10021)
				AND created_time BETWEEN FROM_UNIXTIME(?) AND FROM_UNIXTIME(?)
				GROUP BY month, component
				ORDER BY month ASC, value DESC, component ASC
			`, startTs, endTs).Scan(&componentMonthlyTrends).Error
	if err != nil {
		return nil, fmt.Errorf("get cloud oncall component monthly trends failed: %w", err)
	}

	var priorityMonthlyTrends []entity.SeriesMonthlyTrend
	err = rdb.Raw(
		`SELECT 
				DATE_FORMAT(created_time, '%Y-%m') AS month,
				priority AS series,
				count(id) as value
				FROM jirainterval.jira_issueview 
				WHERE project_id in (10417,10021)
				AND created_time BETWEEN FROM_UNIXTIME(?) AND FROM_UNIXTIME(?)
				GROUP BY month, priority
				ORDER BY month ASC, value DESC, priority ASC
			`, startTs, endTs).Scan(&priorityMonthlyTrends).Error
	if err != nil {
		return nil, fmt.Errorf("get cloud oncall priority monthly trends failed: %w", err)
	}

	var data []entity.TopTenant
	err = rdb.Raw(
		`SELECT 
				  TRIM(account_code) AS id, 
				  count(id) AS value
				FROM jirainterval.jira_issueview
				WHERE project_id in (10417,10021)
				AND account_code IS NOT NULL AND account_code != '' AND account_code != 'NA'
				AND created_time BETWEEN FROM_UNIXTIME(?) AND FROM_UNIXTIME(?) 
				GROUP BY account_code
				ORDER BY value DESC, account_code ASC
				LIMIT 10
			`, startTs, endTs).Scan(&data).Error
	if err != nil {
		return nil, fmt.Errorf("get cloud oncall top tenants failed: %w", err)
	}
	tenantIds := make([]uint64, 0, len(data))
	for _, item := range data {
		tenantIds = append(tenantIds, item.ID)
	}
	tenants, _, err := s.tenantService.ListTenants(ctx,
		tenantsvc.ListTenantsParam{
			Filter: tenantsvc.ListTenantsFilter{ID: tenantIds},
		},
	)
	if err != nil {
		return nil, err
	}
	for i, item := range data {
		tenant := buildTopTenant(tenants, item)
		data[i] = tenant
	}
	return &entity.CloudOncallInfo{
		Total:                  total,
		BacklogTotal:           backlogTotal,
		TotalMonthlyTrends:     monthlyTrends,
		TTRInDays:              ttrInDays,
		ComponentMonthlyTrends: componentMonthlyTrends,
		PriorityMonthlyTrends:  priorityMonthlyTrends,
		TopTenants:             data,
	}, nil
}

func (s *svc) GetAlertInfo(ctx context.Context, startTs, endTs int64) (*entity.CriticalAlertInfo, error) {
	var total int
	rdb := s.devopsDB.ReadDB(ctx)
	err := rdb.Raw(
		`SELECT 
				count(id) 
				FROM jirainterval.jira_issueview 
				WHERE project_id in (10415,10422,10048)
				AND priority = 'Critical'
				AND issue_type='Task'
				AND created_time BETWEEN FROM_UNIXTIME(?) AND FROM_UNIXTIME(?) 
			`, startTs, endTs).Scan(&total).Error
	if err != nil {
		return nil, fmt.Errorf("get alert total failed: %w", err)
	}

	var backlogTotal int
	err = rdb.Raw(
		`
			SELECT 
				count(id) 
				FROM jirainterval.jira_issueview 
				WHERE project_id in (10415,10422,10048)
				AND priority = 'Critical'
				AND issue_type='Task'
				AND ttr is null
				AND created_time BETWEEN FROM_UNIXTIME(?) AND FROM_UNIXTIME(?) 
			`, startTs, endTs).Scan(&backlogTotal).Error
	if err != nil {
		return nil, fmt.Errorf("get alert backlog failed: %w", err)
	}

	var ttrInDays float64
	err = rdb.Raw(
		`SELECT COALESCE(AVG(ttr),0)
				FROM jirainterval.jira_issueview 
				WHERE project_id in (10415,10422,10048)
				AND priority = 'Critical'
				AND issue_type='Task'
				AND ttr is not null
				AND created_time BETWEEN FROM_UNIXTIME(?) AND FROM_UNIXTIME(?)
			`, startTs, endTs).Scan(&ttrInDays).Error
	if err != nil {
		return nil, fmt.Errorf("get alert ttr failed: %w", err)
	}

	var data []entity.TopTenant
	err = rdb.Raw(
		`SELECT 
			TRIM(tenant_id) AS id, 
			count(id) AS value
		FROM jirainterval.jira_issueview
		WHERE 
		project_id in (10415,10422,10048)
		AND priority= 'Critical'
		AND tenant_id IS NOT NULL AND TRIM(tenant_id) REGEXP '^[0-9]+$'
		AND created_time BETWEEN FROM_UNIXTIME(?) AND FROM_UNIXTIME(?) 
		AND issue_type='Task'
		GROUP BY tenant_id
		ORDER BY value DESC, tenant_id ASC
		LIMIT 10
			`, startTs, endTs).Scan(&data).Error
	if err != nil {
		return nil, fmt.Errorf("get alert top tenants failed: %w", err)
	}
	tenantIds := make([]uint64, 0, len(data))
	for _, item := range data {
		tenantIds = append(tenantIds, item.ID)
	}
	tenants, _, err := s.tenantService.ListTenants(ctx,
		tenantsvc.ListTenantsParam{
			Filter: tenantsvc.ListTenantsFilter{ID: tenantIds},
		},
	)
	if err != nil {
		return nil, err
	}
	for i, item := range data {
		tenant := buildTopTenant(tenants, item)
		data[i] = tenant
	}

	var alertTrend []entity.SeriesMonthlyTrend
	err = rdb.Raw(
		`SELECT
				month,
				summary AS series,
				value
			FROM (
				SELECT
					DATE_FORMAT(created_time, '%Y-%m') AS month,
					summary,
					COUNT(1) AS value,
					ROW_NUMBER() OVER w AS row_num
				FROM jirainterval.jira_issueview
				WHERE project_id in (10415,10422,10048)
				  AND priority = 'Critical'
				  AND issue_type='Task'
				  AND created_time BETWEEN FROM_UNIXTIME(?) AND FROM_UNIXTIME(?) 
				group by DATE_FORMAT(created_time, '%Y-%m'), summary
				WINDOW w AS (PARTITION BY DATE_FORMAT(created_time, '%Y-%m') ORDER BY count(1) DESC)
				ORDER BY month, value DESC
			) AS ranked_data
			WHERE row_num <= 10
			ORDER BY month, value DESC;
			`, startTs, endTs).Scan(&alertTrend).Error
	if err != nil {
		return nil, fmt.Errorf("get alert monthly trend failed: %w", err)
	}

	var majorAlertTrend []entity.SeriesMonthlyTrend
	err = rdb.Raw(
		`SELECT
				month,
				summary AS series,
				value
			FROM (
				SELECT
					DATE_FORMAT(created_time, '%Y-%m') AS month,
					summary,
					COUNT(1) AS value,
					ROW_NUMBER() OVER w AS row_num
				FROM jirainterval.jira_issueview
				WHERE project_id in (10415,10422,10048)
				  AND priority = 'Major'
				  AND issue_type='Task'
				  AND created_time BETWEEN FROM_UNIXTIME(?) AND FROM_UNIXTIME(?) 
				group by DATE_FORMAT(created_time, '%Y-%m'), summary
				WINDOW w AS (PARTITION BY DATE_FORMAT(created_time, '%Y-%m') ORDER BY count(1) DESC)
				ORDER BY month, value DESC
			) AS ranked_data
			WHERE row_num <= 10
			ORDER BY month, value DESC;
			`, startTs, endTs).Scan(&majorAlertTrend).Error
	if err != nil {
		return nil, fmt.Errorf("get alert monthly trend failed: %w", err)
	}
	return &entity.CriticalAlertInfo{
		Total:                  total,
		BacklogTotal:           backlogTotal,
		TTRInDays:              ttrInDays,
		TopTenants:             data,
		AlertMonthlyTrend:      alertTrend,
		MajorAlertMonthlyTrend: majorAlertTrend,
	}, nil
}

func (s *svc) GetReleaseInfo(ctx context.Context, startTs, endTs int64) (*entity.ReleaseInfo, error) {
	var sprintTotal int
	rdb := s.devopsDB.ReadDB(ctx)
	err := rdb.Raw(
		`SELECT count(1)
				FROM release_changes
				WHERE type = 'sprint'
				AND window_start_at BETWEEN FROM_UNIXTIME(?) AND FROM_UNIXTIME(?) 
			`, startTs, endTs).Scan(&sprintTotal).Error
	if err != nil {
		return nil, fmt.Errorf("get release sprint total failed: %w", err)
	}

	var hotfixTotal int
	err = rdb.Raw(
		`SELECT count(1)
				FROM release_changes
				WHERE type = 'hotfix'
				AND window_start_at BETWEEN FROM_UNIXTIME(?) AND FROM_UNIXTIME(?) 
			`, startTs, endTs).Scan(&hotfixTotal).Error
	if err != nil {
		return nil, fmt.Errorf("get release hotfix total failed: %w", err)
	}

	var sprintApplicationMonthlyTrends []entity.SeriesMonthlyTrend
	err = rdb.Raw(
		`SELECT 
				DATE_FORMAT(window_start_at, '%Y-%m') AS month,
				application_name AS series,
				count(1) as value
				FROM release_changes
				WHERE type = 'sprint'
				AND window_start_at BETWEEN FROM_UNIXTIME(?) AND FROM_UNIXTIME(?)
				GROUP BY month, application_name
				ORDER BY month ASC, value DESC, application_name ASC
			`, startTs, endTs).Scan(&sprintApplicationMonthlyTrends).Error
	if err != nil {
		return nil, fmt.Errorf("get release sprint application monthly trends failed: %w", err)
	}

	var hotfixApplicationMonthlyTrends []entity.SeriesMonthlyTrend
	err = rdb.Raw(
		`SELECT 
				DATE_FORMAT(window_start_at, '%Y-%m') AS month,
				application_name AS series,
				count(1) as value
				FROM release_changes
				WHERE type = 'hotfix'
				AND window_start_at BETWEEN FROM_UNIXTIME(?) AND FROM_UNIXTIME(?)
				GROUP BY month, application_name
				ORDER BY month ASC, value DESC, application_name ASC
			`, startTs, endTs).Scan(&hotfixApplicationMonthlyTrends).Error
	if err != nil {
		return nil, fmt.Errorf("get release hotfix application monthly trends failed: %w", err)
	}
	return &entity.ReleaseInfo{
		SprintTotal:                    sprintTotal,
		SprintApplicationMonthlyTrends: sprintApplicationMonthlyTrends,
		HotfixTotal:                    hotfixTotal,
		HotfixApplicationMonthlyTrends: hotfixApplicationMonthlyTrends,
	}, nil
}

func (s *svc) GetTop10QPSTenants(ctx context.Context) (*entity.TopTenants, error) {
	return s.getTopTenants(ctx, "topk(10, sum by (tenant) (rate(tidb_executor_statement_total[24h])))")
}

func (s *svc) GetTop10TiKVStorageTenants(ctx context.Context) (*entity.TopTenants, error) {
	return s.getTopTenants(ctx, "topk(10, sum by (tenant) (avg_over_time(tikv_engine_size_bytes[24h])))")
}

func (s *svc) GetTop10TiFlashStorageTenants(ctx context.Context) (*entity.TopTenants, error) {
	return s.getTopTenants(ctx, "topk(10, sum by (tenant) (avg_over_time(tiflash_system_current_metric_StoreSizeCapacity[24h])))")
}

func (s *svc) getTopTenants(ctx context.Context, query string) (*entity.TopTenants, error) {
	client, err := promapi.NewClient(promapi.Config{
		Address: s.eePrometheusPrefix,
	})
	if err != nil {
		return nil, fmt.Errorf("error creating client: %v", err)
	}
	v1api := promv1.NewAPI(client)
	result, warnings, err := v1api.Query(ctx,
		query,
		time.Now(),
		promv1.WithTimeout(5*time.Second),
	)
	if err != nil {
		return nil, fmt.Errorf("error querying Prometheus: %v", err)
	}
	if len(warnings) > 0 {
		s.Logger(ctx).Info("top qps query warnings", "warnings", warnings)
	}
	vector, ok := result.(prommodel.Vector)
	if !ok {
		return nil, fmt.Errorf("prometheus query result is not vector")
	}
	topTenants := make([]entity.TopTenant, 0)
	tenantIds := make([]uint64, 0)
	for _, sample := range vector {
		tenantIdStr, ok := sample.Metric["tenant"]
		if !ok {
			return nil, fmt.Errorf("tenant not found in metrics")
		}
		tenantId, err := strconv.ParseUint(string(tenantIdStr), 10, 64)
		if err != nil {
			return nil, fmt.Errorf("tenant id is not uint64")
		}
		tenantIds = append(tenantIds, tenantId)
		topTenants = append(topTenants, entity.TopTenant{
			ID:    tenantId,
			Value: int64(math.Round(float64(sample.Value))),
		})
	}
	tenants, _, err := s.tenantService.ListTenants(ctx,
		tenantsvc.ListTenantsParam{
			Filter: tenantsvc.ListTenantsFilter{ID: tenantIds},
		},
	)
	if err != nil {
		return nil, err
	}
	for i, item := range topTenants {
		tenant := buildTopTenant(tenants, item)
		topTenants[i] = tenant
	}
	return &entity.TopTenants{Items: topTenants}, nil
}

func buildTopTenant(tenants []*tntent.Tenant, topTenant entity.TopTenant) entity.TopTenant {
	var tenant *tntent.Tenant
	for _, item := range tenants {
		if item.Id == topTenant.ID {
			tenant = item
			break
		}
	}
	if tenant == nil {
		return topTenant
	}

	tenantAlias := tenant.Alias
	if tenantAlias == "" {
		tenantAlias = tenant.Name
	}
	tenantRegion := tenant.GetTenantRegion()
	topTenant.Name = tenant.Name
	topTenant.Alias = tenantAlias
	topTenant.Region = tenantRegion
	return topTenant
}

func getMonthsFromTimestamp(startTs, endTs int64, format string) []string {
	months := make([]string, 0)
	start := time.Unix(startTs, 0).UTC()
	startMonth, err := time.Parse(format, start.Format(format))
	if err != nil {
		return months
	}
	end := time.Unix(endTs, 0).UTC()
	tmp := startMonth
	for tmp.Compare(end) <= 0 {
		months = append(months, tmp.Format(format))
		tmp = tmp.AddDate(0, 1, 0)
	}
	return months
}

func getBeginAndEndTsFromMonth(month string, format string) (int64, int64, error) {
	start, err := time.ParseInLocation(format, month, time.UTC)
	if err != nil {
		return 0, 0, fmt.Errorf("parse month(%s) failed: %w", month, err)
	}
	end := start.AddDate(0, 1, 0).Add(-time.Second)
	return start.Unix(), end.Unix(), nil
}

func convertDateFormat(date string, oldFormat string, newFormat string) (string, error) {
	time, err := time.Parse(oldFormat, date)
	if err != nil {
		return "", err
	}
	newDate := time.Format(newFormat)
	return newDate, nil
}