package db

import (
	"log"
	"poc-daily-report/pkg/model"
	"time"
)

const rawQuery = `SELECT 
	t.id id,
	t.alias alias,
	t.region region,
	p.poc_status poc_status,
	c.id cluster_id,
	c.status cluster_status,
	ifnull(w1.date,'2022-08-31') detect_date,
	w1.QPS_gt_500_minutes qps_gt_500_minutes,
	w1.Max_QPS max_qps,
	t.plan tenant_plan,
	p.poc_starttime poc_starttime,
	p.poc_endtime poc_endtime,
	p.poc_presales poc_presales,
	p.poc_ee poc_ee
FROM
	tmp_poc_progress p
JOIN
	tmp_curr_tenants t ON p.tenant_id = t.id
LEFT JOIN
	(SELECT 
		*
	FROM
		tmp_curr_clusters c
	WHERE
		c.status NOT IN ('deleted' , 'cleared', 'deletecomplete') and c.free = 0
	) c 
ON 
	p.tenant_id = c.tenant_id
LEFT JOIN
	(SELECT 
		w.tenant_id,
		w.cluster_id,
		DATE(starttime) date,
		SUM(TIMESTAMPDIFF(MINUTE, starttime, endtime) + 1) QPS_gt_500_minutes,
		MAX(max_qps) Max_QPS
	FROM
		tmp_workload w
	GROUP BY 
		w.tenant_id , w.cluster_id , DATE(starttime)
	HAVING 
		date = ?
	ORDER BY 
		date , QPS_gt_500_minutes
		) w1 
	ON 
		w1.cluster_id = c.id
	WHERE
		p.poc_status IN ('in_progress' , 'paused')
	ORDER BY 
		t.region , p.poc_status , t.id`
const rawQuery2 = `SELECT 
t.id id,
t.alias alias,
t.region region,
p.poc_status poc_status,
c.id cluster_id,
c.status cluster_status,
IFNULL(w1.date, '2022-09-01') detect_date,
w1.QPM_60 qpm_60,
w1.Max_QPS max_qps, 
p.poc_presales poc_presales,
p.poc_ee poc_ee,
p.poc_starttime poc_starttime,
t.plan tenant_plan
FROM
tmp_poc_progress p
	JOIN
tmp_curr_tenants t ON p.tenant_id = t.id
	LEFT JOIN
(SELECT 
	*
FROM
	tmp_curr_clusters c
WHERE
	c.status NOT IN ('deleted' , 'cleared', 'deletecomplete')
		AND c.free = 0) c ON p.tenant_id = c.tenant_id
	LEFT JOIN
(SELECT 
		w.cluster_id,
		DATE(starttime) date,
		SUM(TIMESTAMPDIFF(MINUTE, starttime, endtime) + 1) QPM_60,
		MAX(max_qps) Max_QPS
FROM
	tmp_workload w
GROUP BY w.cluster_id , DATE(starttime)
HAVING date = ?
ORDER BY date , QPM_60) w1 ON w1.cluster_id = c.id
WHERE
p.poc_status IN ('in_progress' , 'paused')
ORDER BY t.region , p.poc_status , t.id`

const rawQuery3 = `SELECT 
	t.id,
	t.alias,
	t.region,
	p.poc_status,
	c.id cluster_id,
	c.status cluster_status,
	IFNULL(w1.date, ?) detect_date,
	ph.paused_hour paused_hour,
	w1.QPM_60 qpm_60,
	w1.Max_QPS max_qps,
	p.poc_presales poc_presales,
	p.poc_ee poc_ee,
	p.poc_starttime poc_starttime,
	t.plan tenant_plan
FROM
	tmp_poc_progress p
JOIN
	tmp_curr_tenants t 
ON 
	p.tenant_id = t.id
LEFT JOIN
	(SELECT 
		*
	FROM
		tmp_curr_clusters c
	WHERE
		c.status NOT IN ('deleted' , 'cleared', 'deletecomplete') AND c.free = 0
	) c 
ON 
	p.tenant_id = c.tenant_id
LEFT JOIN
	(SELECT 
		w.cluster_id,
		DATE(starttime) date,
		SUM(TIMESTAMPDIFF(MINUTE, starttime, endtime) + 1) QPM_60,
		MAX(max_qps) Max_QPS
	FROM
		tmp_workload w
	GROUP BY 
		w.cluster_id , DATE(starttime)
	HAVING 
		date = ?
	ORDER BY 
		date , QPM_60
	) w1 
ON 
	w1.cluster_id = c.id
LEFT JOIN
	(SELECT 
		c.id, COUNT(*) * 15 / 60 paused_hour
	FROM
		tmp_clusters c
	WHERE
		DATE(c.fetch_time) = ? 
		AND 
		c.status = 'paused' 
		AND 
		c.id IN (SELECT DISTINCT
					w.cluster_id
				FROM
					tmp_workload w
				WHERE
					DATE(starttime) = ?
				)
		GROUP BY 
			c.id) ph 
ON 
	c.id = ph.id
WHERE
	p.poc_status IN ('in_progress' , 'paused')
ORDER BY 
	t.region , p.poc_status , t.id`

const rawQuery4 = `SELECT 
t.id id,
t.alias alias,
t.region region,
p.poc_status poc_status,
c.id cluster_id,
c.status cluster_status,
IFNULL(w1.date, '2022-09-02') detect_date,
ph.paused_hour paused_hour,
w1.QPM_60 qpm_60,
w1.Max_QPS max_qps,
p.poc_presales poc_presales,
p.poc_ee poc_ee,
p.poc_starttime poc_starttime,
t.plan tenant_plan
FROM
tmp_poc_progress p
	JOIN
tmp_curr_tenants t ON p.tenant_id = t.id
	LEFT JOIN
(SELECT 
	*
FROM
	tmp_curr_clusters c
WHERE
	c.status NOT IN ('deleted' , 'cleared', 'deletecomplete')
		AND c.free = 0) c ON p.tenant_id = c.tenant_id
	LEFT JOIN
(SELECT 
	w.cluster_id,
		DATE(starttime) date,
		SUM(TIMESTAMPDIFF(MINUTE, starttime, endtime) + 1) qpm_60,
		MAX(max_qps) Max_QPS
FROM
	tmp_workload w
GROUP BY w.cluster_id , DATE(starttime)
HAVING date = ?
ORDER BY date , qpm_60) w1 ON w1.cluster_id = c.id
	LEFT JOIN
(SELECT 
	c.id, COUNT(*) * 15 / 60 paused_hour
FROM
	tmp_clusters c
WHERE
	DATE(c.fetch_time) = ?
		AND c.status = 'paused'
		AND c.tenant_id IN (SELECT pg.tenant_id
		FROM
			tmp_poc_progress pg
	   )
GROUP BY c.id) ph ON c.id = ph.id
WHERE
p.poc_status IN ('in_progress' , 'paused')
ORDER BY t.region , p.poc_status , t.id`

func GetNewestTimestamp() (time.Time, error) {
	var ts model.Timestamp
	db, err := getDB()
	if err != nil {
		return ts.Timestamp, err
	}
	result := db.Last(&ts)
	if result.Error != nil {
		log.Println(result.Error)
		return ts.Timestamp, result.Error
	}
	return ts.Timestamp, nil
}

func GetNewStartBackups(params model.Params) ([]model.Backup, error) {
	//rows, err := getDB().Query("")
	var backups []model.Backup
	db, err := getDB()
	if err != nil {
		return backups, err
	}
	result := db.Where("fetch_time = ? and (created_at between ? and ?) and (schedule_type = 'manual' or status = 'failed')", params.CurrentTimestamp, params.LastTimestamp, params.CurrentTimestamp).Find(&backups)
	if result.Error != nil {
		log.Println(result.Error)
		return backups, result.Error
	}
	return backups, nil
}

func GetNewFinishedBackups(params model.Params) ([]model.Backup, error) {
	var backups []model.Backup
	db, err := getDB()
	if err != nil {
		return backups, err
	}
	result := db.Where("fetch_time = ? and (finished_at between ? and ?) and (schedule_type = 'manual' or status = 'failed')", params.CurrentTimestamp, params.LastTimestamp, params.CurrentTimestamp).Find(&backups)
	if result.Error != nil {
		log.Println(result.Error)
		return backups, result.Error
	}
	return backups, err
}

func GetLastTooLongBackups(params model.Params) ([]model.Backup, error) {
	var backups []model.Backup
	db, err := getDB()
	if err != nil {
		return backups, err
	}
	result := db.Where("fetch_time = ? and cluster_id <> '1311285575352848384' and deleted_at is null and status = 'running' and created_at < date_add(now(), interval - 10 hour) and (schedule_type = 'manual' or status = 'failed')", params.CurrentTimestamp).Find(&backups)
	if result.Error != nil {
		log.Println(result.Error)
		return backups, result.Error
	}
	return backups, nil

}

func GetNewStartRestores(params model.Params) ([]model.Restore, error) {
	var restores []model.Restore
	db, err := getDB()
	if err != nil {
		return restores, err
	}
	result := db.Where("fetch_time = ? and (created_at between ? and ?)", params.CurrentTimestamp, params.LastTimestamp, params.CurrentTimestamp).Find(&restores)
	if result.Error != nil {
		log.Println(result.Error)
		return restores, result.Error
	}
	return restores, nil
}

func GetNewFinishedRestores(params model.Params) ([]model.Restore, error) {
	var restores []model.Restore
	db, err := getDB()
	if err != nil {
		return restores, err
	}
	result := db.Where("fetch_time = ? and (finished_at between ? and ?)", params.CurrentTimestamp, params.LastTimestamp, params.CurrentTimestamp).Find(&restores)
	if result.Error != nil {
		log.Println(result.Error)
		return restores, result.Error
	}
	return restores, nil
}

func GetLastTooLongRestores(params model.Params) ([]model.Restore, error) {
	var restores []model.Restore
	db, err := getDB()
	if err != nil {
		return restores, err
	}
	result := db.Where("fetch_time = ? and status = 'running' and created_at < date_add(now(), interval - 3 hour)", params.CurrentTimestamp).Find(&restores)
	if result.Error != nil {
		log.Println(result.Error)
		return restores, result.Error
	}
	return restores, nil
}

func GetNewStartImports(params model.Params) ([]model.Import, error) {
	var imports []model.Import
	db, err := getDB()
	if err != nil {
		return imports, err
	}
	result := db.Where("fetch_time = ? and (created_at between ? and ?)", params.CurrentTimestamp, params.LastTimestamp, params.CurrentTimestamp).Find(&imports)
	if result.Error != nil {
		log.Println(result.Error)
		return imports, result.Error
	}
	return imports, nil
}

func GetNewFinishedImports(params model.Params) ([]model.Import, error) {
	var imports []model.Import
	db, err := getDB()
	if err != nil {
		return imports, err
	}
	result := db.Where("fetch_time = ? and (finished_at between ? and ?)", params.CurrentTimestamp, params.LastTimestamp, params.CurrentTimestamp).Find(&imports)
	if result.Error != nil {
		log.Println(result.Error)
		return imports, result.Error
	}
	return imports, nil

}

func GetLastTooLongImports(params model.Params) ([]model.Import, error) {
	var imports []model.Import
	db, err := getDB()
	if err != nil {
		return imports, err
	}
	result := db.Where("fetch_time = ? and status = 'running' and created_at < date_add(now(), interval - 3 hour)", params.CurrentTimestamp).Find(&imports)
	if result.Error != nil {
		log.Println(result.Error)
		return imports, result.Error
	}
	return imports, nil
}

func GetNewUsers(params model.Params) ([]model.Tenant, error) {
	var tenants []model.Tenant
	db, err := getDB()
	if err != nil {
		return tenants, err
	}
	result := db.Where("fetch_time = ? and status = 'active' and (updated_at between ? and ?)", params.CurrentTimestamp, params.LastTimestamp, params.CurrentTimestamp).Find(&tenants)
	if result.Error != nil {
		log.Println(result.Error)
		return tenants, result.Error
	}
	return tenants, nil
}

func GetActiveUsers(params model.Params) ([]model.Tenant, error) {
	var tenants []model.Tenant
	db, err := getDB()
	if err != nil {
		return tenants, err
	}
	result := db.Where("fetch_time = ? and status = 'active'", params.CurrentTimestamp).Find(&tenants)
	if result.Error != nil {
		log.Println(result.Error)
		return tenants, result.Error
	}
	return tenants, nil
}

func GetClusterCreationStarts() []model.Cluster {
	var clusters []model.Cluster
	return clusters
}

func GetClusterCreationFinishes() []model.Cluster {
	var clusters []model.Cluster
	return clusters
}

func GetClusterCreationLastsTooLong() []model.Cluster {
	var clusters []model.Cluster
	return clusters
}

func GetAllClusters(params model.Params) ([]model.Cluster, error) {
	var clusters []model.Cluster
	db, err := getDB()
	if err != nil {
		return clusters, err
	}
	result := db.Where("fetch_time = ?", params.CurrentTimestamp).Find(&clusters)
	if result.Error != nil {
		log.Println(result.Error)
		return clusters, result.Error
	}
	return clusters, nil
}

func GetClusterScalingStarts(params model.Params) ([]model.ClusterScaling, error) {
	var scalings []model.ClusterScaling
	db, err := getDB()
	if err != nil {
		return scalings, err
	}
	result := db.Where("fetch_time = ? and (created_at between ? and ?)", params.CurrentTimestamp, params.LastTimestamp, params.CurrentTimestamp).Find(&scalings)
	if result.Error != nil {
		log.Println(result.Error)
		return scalings, result.Error
	}
	return scalings, nil
}

func GetClusterScalingFinishes(params model.Params) ([]model.ClusterScaling, error) {
	var scalings []model.ClusterScaling
	db, err := getDB()
	if err != nil {
		return scalings, err
	}
	result := db.Where("fetch_time = ? and (completed_at between ? and ?)", params.CurrentTimestamp, params.LastTimestamp, params.CurrentTimestamp).Find(&scalings)
	if result.Error != nil {
		log.Println(result.Error)
		return scalings, result.Error
	}
	return scalings, nil
}

func GetClusterScalingLastsTooLong(params model.Params) ([]model.ClusterScaling, error) {
	var scalings []model.ClusterScaling
	db, err := getDB()
	if err != nil {
		return scalings, err
	}
	result := db.Where("fetch_time = ? and created_at < date_add(now(), interval - 3 hour)", params.CurrentTimestamp).Find(&scalings)
	if result.Error != nil {
		log.Println(result.Error)
		return scalings, result.Error
	}
	return scalings, nil
}

/*func GetFocusedClusters(dateString string) ([]model.FocusedCluster, error) {
	var clusters []model.FocusedCluster
	db, err := getDB()
	if err != nil {
		return clusters, err
	}
	result := db.Raw(rawQuery, dateString).Scan(&clusters)
	if result.Error != nil {
		log.Println(result.Error)
		return clusters, result.Error
	}
	return clusters, nil
}*/

func GetFocusedClusters(date time.Time) ([]model.FocusedCluster, error) {
	var clusters []model.FocusedCluster
	db, err := getDB()
	if err != nil {
		return clusters, err
	}
	dateString := date.Format("2006-01-02")
	log.Println("dataString: ", dateString)
	result := db.Raw(rawQuery4, dateString, dateString).Scan(&clusters)
	if result.Error != nil {
		log.Println(result.Error)
		return clusters, result.Error
	}
	var regionOverides []model.RegionOveride
	result = db.Find(&regionOverides)
	if result.Error != nil {
		log.Println(result.Error)
		return clusters, result.Error
	}
	// Overide
	overideMap := make(map[uint64]string)
	for _, ro := range regionOverides {
		overideMap[ro.TenantId] = ro.Region
	}
	for i, cluster := range clusters {
		region, ok := overideMap[cluster.TenantId]
		if ok {
			clusters[i].Region = region
		}
	}
	return clusters, nil
}