package db_utils

import (
	"fmt"
	"gitlab.bob.co.za/bob-public-utils/bobgroup-go-utils/date_utils"
	"gitlab.bob.co.za/bob-public-utils/bobgroup-go-utils/errors"
	"gitlab.bob.co.za/bob-public-utils/bobgroup-go-utils/number_utils"
	"gitlab.bob.co.za/bob-public-utils/bobgroup-go-utils/struct_utils"
	"regexp"
	"strings"
	"time"
)

const (
	TableIndexScanPercentageCheckHealthy    = "healthy"
	TableIndexScanPercentageCheckProblem    = "problem"
	TableIndexScanPercentageCheckEmptyTable = "empty_table"
)

const (
	IndexProblemReasonNeverUsedIndex              = "Never-used index"
	IndexProblemReasonLowScanHighWrite            = "Low scan, high write"
	IndexProblemReasonSeldomUsedLargeIndex        = "Seldom-used large index"
	IndexProblemReasonHighWriteLargeNonBTreeIndex = "High write, large non-BTree index"
)

type Index struct {
	IndexName string `json:"index_name"`
	TableName string `json:"table_name"`
}

type CorruptIndex struct {
	CheckResult  string `json:"check_result"` // If there are no errors, this should be empty.
	RelationName string `json:"relation_name"`
	RelationSize int64  `json:"relation_size"` // This size in measured in pages.
}

type LowIndexScanTable struct {
	TableName    string `json:"table_name"`
	Status       string `json:"index_name"`
	PercentUsage int64  `json:"percent_usage"`
	RowsInTable  int64  `json:"rows_in_table"`
}

type ProblemIndex struct {
	TableName           string  `json:"table_name"`
	IndexName           string  `json:"index_name"`
	Reason              string  `json:"reason"`
	IndexScanPercentage float64 `json:"index_scan_percentage"`
	IndexSize           string  `json:"index_size"`
	TableSize           string  `json:"table_size"`
}

type IndexHealthIssues struct {
	CorruptIndexes     []CorruptIndex      `json:"corrupt_indexes"`
	LowIndexScanTables []LowIndexScanTable `json:"low_index_scan_tables"`
	ProblemIndexes     []ProblemIndex      `json:"problem_indexes"`
}

type IndexError struct {
	TableName string `json:"table_name"`
	IndexName string `json:"index_name"`
	Reason    string `json:"reason"`
}

// Special query cases
const (
	LastXHours string = "^last \\d+ hour(s)?$"
	LastDay    string = "^last day$"
	LastXDays  string = "^last \\d+ day(s)?$"
	LastMonth  string = "^last month$"
	Yesterday  string = "^yesterday$"
)

func checkAbsoluteRegex(regex string, value string) bool {
	matchesRegex, err := regexp.Match(regex, []byte(value))
	if matchesRegex && err == nil {
		return true
	}

	return false
}

func pullAbsoluteValue(value string) int64 {
	reg, err := regexp.Compile("[a-zA-Z]+")
	if err != nil {
		return 0
	}
	value = reg.ReplaceAllString(value, "")
	value = strings.TrimSpace(value)

	val, _ := number_utils.StringToInt64(value)
	return val
}

func AbsoluteDateStringQuery(absolute string) string {
	var value int64
	timeNow := date_utils.CurrentDate()

	if checkAbsoluteRegex(LastXHours, absolute) {
		value = pullAbsoluteValue(absolute)
		since := timeNow.Add(time.Duration(-value) * time.Hour)

		return fmt.Sprintf(" >= '%v'", date_utils.DateDBFormattedString(since))
	}
	if checkAbsoluteRegex(LastDay, absolute) {
		startDay := time.Date(timeNow.Year(), timeNow.Month(), timeNow.Day(), 0, 0, 0, 0, date_utils.CurrentLocation())
		endDay := time.Date(timeNow.Year(), timeNow.Month(), timeNow.Day(), 23, 59, 59, 999999999, date_utils.CurrentLocation())

		return fmt.Sprintf(" BETWEEN '%v' AND '%v'", date_utils.DateDBFormattedString(startDay), date_utils.DateDBFormattedString(endDay))
	}
	if checkAbsoluteRegex(LastXDays, absolute) {
		value = pullAbsoluteValue(absolute)
		since := timeNow.AddDate(0, 0, -int(value))

		return fmt.Sprintf(" >= '%v'", date_utils.DateDBFormattedString(since))
	}
	if checkAbsoluteRegex(LastMonth, absolute) {
		firstOfMonth := time.Date(timeNow.Year(), timeNow.Month(), 1, 0, 0, 0, 0, date_utils.CurrentLocation())
		lastDay := firstOfMonth.AddDate(0, 1, -1)
		lastOfMonth := time.Date(lastDay.Year(), lastDay.Month(), lastDay.Day(), 23, 59, 59, 999999999, date_utils.CurrentLocation())

		return fmt.Sprintf(" BETWEEN '%v' AND '%v'", date_utils.DateDBFormattedString(firstOfMonth), date_utils.DateDBFormattedString(lastOfMonth))
	}
	if checkAbsoluteRegex(Yesterday, absolute) {
		yesterday := timeNow.AddDate(0, 0, -1)
		startDay := time.Date(yesterday.Year(), yesterday.Month(), yesterday.Day(), 0, 0, 0, 0, date_utils.CurrentLocation())
		endDay := time.Date(yesterday.Year(), yesterday.Month(), yesterday.Day(), 23, 59, 59, 999999999, date_utils.CurrentLocation())

		return fmt.Sprintf(" BETWEEN '%v' AND '%v'", date_utils.DateDBFormattedString(startDay), date_utils.DateDBFormattedString(endDay))
	}

	// Not matched, so try to match it literally (It'll probably break and return nothing, but that's good)
	return fmt.Sprintf(" = '%v'", absolute)
}

type PageParams struct {
	Limit  int64 `json:"limit,omitempty"`  // Limit number of items returned in list (default: 0 (no limit))
	Offset int64 `json:"offset,omitempty"` // Offset in list (default: 0 (no offset))
}

func (params *PageParams) Validate() error {
	if params.Limit < 0 {
		return errors.Errorf("limit=%d must be positive", params.Limit)
	}
	if params.Offset < 0 {
		return errors.Errorf("offset=%d must be >=0", params.Offset)
	}
	return nil
}

func (params *PageParams) EnforceLimit() {
	if params.Limit <= 0 {
		params.Limit = 20
	}
}

func ValidateDateFilters(params any, dateFiltersToValidate ...string) error {
	queryParams := struct_utils.MapParams(params)
	for _, filter := range dateFiltersToValidate {
		date, present := queryParams[filter]
		if present {
			_, err := date_utils.ParseTimeString(date)
			if err != nil {
				return errors.Errorf("invalid %s: %s", filter, date)
			}
		}
	}
	return nil
}

func FindCommonNeverUsedIndexes(list1, list2 []ProblemIndex) []ProblemIndex {
	// Create a map to store indexes from the first list
	indexMap := make(map[string]ProblemIndex)
	for _, index := range list1 {
		if index.Reason == IndexProblemReasonNeverUsedIndex {
			indexMap[index.IndexName] = index
		}
	}

	// Iterate through the second list and find common indexes
	var commonIndexes []ProblemIndex
	for _, index := range list2 {
		if index.Reason == IndexProblemReasonNeverUsedIndex {
			if _, exists := indexMap[index.IndexName]; exists {
				commonIndexes = append(commonIndexes, index)
			}
		}
	}

	return commonIndexes
}