package coredb

import (
	"fmt"
	"strconv"
	"strings"
)

func BuildStringValuesCTE(name string, column string, values []string, startParam int) (string, []any, int, error) {
	trimmedName := strings.TrimSpace(name)
	trimmedColumn := strings.TrimSpace(column)
	if trimmedName == "" || trimmedColumn == "" {
		return "", nil, startParam, fmt.Errorf("cte name and column are required")
	}
	if len(values) == 0 {
		return "", nil, startParam, fmt.Errorf("values are empty")
	}
	if err := validateStartParam(startParam); err != nil {
		return "", nil, startParam, err
	}

	var builder strings.Builder
	builder.WriteString(trimmedName)
	builder.WriteString(" AS (\n")

	args := make([]any, 0, len(values))
	param := startParam
	for i, value := range values {
		if i > 0 {
			builder.WriteString("\n    UNION ALL\n")
		}
		builder.WriteString("    SELECT $")
		builder.WriteString(strconv.Itoa(param))
		builder.WriteString(" AS ")
		builder.WriteString(trimmedColumn)
		args = append(args, value)
		param++
	}

	builder.WriteString("\n)")
	return builder.String(), args, param, nil
}

func BuildStringPairsCTE(name string, firstColumn string, secondColumn string, firstValues []string, secondValues []string, startParam int) (string, []any, int, error) {
	trimmedName := strings.TrimSpace(name)
	unionSelect, args, param, err := BuildStringPairsUnionSelect(firstColumn, secondColumn, firstValues, secondValues, startParam)
	if err != nil {
		return "", nil, startParam, err
	}
	if trimmedName == "" {
		return "", nil, startParam, fmt.Errorf("cte name and columns are required")
	}

	var builder strings.Builder
	builder.WriteString(trimmedName)
	builder.WriteString(" AS (\n")
	builder.WriteString(unionSelect)
	builder.WriteString("\n)")
	return builder.String(), args, param, nil
}

func BuildStringPairsUnionSelect(firstColumn string, secondColumn string, firstValues []string, secondValues []string, startParam int) (string, []any, int, error) {
	trimmedFirstColumn := strings.TrimSpace(firstColumn)
	trimmedSecondColumn := strings.TrimSpace(secondColumn)
	if trimmedFirstColumn == "" || trimmedSecondColumn == "" {
		return "", nil, startParam, fmt.Errorf("columns are required")
	}
	if len(firstValues) != len(secondValues) {
		return "", nil, startParam, fmt.Errorf("value pairs are inconsistent")
	}
	if len(firstValues) == 0 {
		return "", nil, startParam, fmt.Errorf("value pairs are empty")
	}
	if err := validateStartParam(startParam); err != nil {
		return "", nil, startParam, err
	}

	var builder strings.Builder
	args := make([]any, 0, len(firstValues)*2)
	param := startParam
	for i := range firstValues {
		if i > 0 {
			builder.WriteString("\nUNION ALL\n")
		}
		builder.WriteString("SELECT $")
		builder.WriteString(strconv.Itoa(param))
		builder.WriteString(" AS ")
		builder.WriteString(trimmedFirstColumn)
		builder.WriteString(", $")
		builder.WriteString(strconv.Itoa(param + 1))
		builder.WriteString(" AS ")
		builder.WriteString(trimmedSecondColumn)
		args = append(args, firstValues[i], secondValues[i])
		param += 2
	}

	return builder.String(), args, param, nil
}

func BuildIndexedStringValuesCTE(name string, valueColumn string, indexColumn string, values []string, startParam int) (string, []any, int, error) {
	trimmedName := strings.TrimSpace(name)
	trimmedValueColumn := strings.TrimSpace(valueColumn)
	trimmedIndexColumn := strings.TrimSpace(indexColumn)
	if trimmedName == "" || trimmedValueColumn == "" || trimmedIndexColumn == "" {
		return "", nil, startParam, fmt.Errorf("cte name and columns are required")
	}
	if len(values) == 0 {
		return "", nil, startParam, fmt.Errorf("values are empty")
	}
	if err := validateStartParam(startParam); err != nil {
		return "", nil, startParam, err
	}

	var builder strings.Builder
	builder.WriteString(trimmedName)
	builder.WriteString(" AS (\n")

	args := make([]any, 0, len(values))
	param := startParam
	for i, value := range values {
		if i > 0 {
			builder.WriteString("\n    UNION ALL\n")
		}
		builder.WriteString("    SELECT $")
		builder.WriteString(strconv.Itoa(param))
		builder.WriteString(" AS ")
		builder.WriteString(trimmedValueColumn)
		builder.WriteString(", ")
		builder.WriteString(strconv.Itoa(i + 1))
		builder.WriteString(" AS ")
		builder.WriteString(trimmedIndexColumn)
		args = append(args, value)
		param++
	}

	builder.WriteString("\n)")
	return builder.String(), args, param, nil
}

func BuildInt64ValuesCTE(name string, column string, values []int64, startParam int) (string, []any, int, error) {
	trimmedName := strings.TrimSpace(name)
	trimmedColumn := strings.TrimSpace(column)
	if trimmedName == "" || trimmedColumn == "" {
		return "", nil, startParam, fmt.Errorf("cte name and column are required")
	}
	if len(values) == 0 {
		return "", nil, startParam, fmt.Errorf("values are empty")
	}
	if err := validateStartParam(startParam); err != nil {
		return "", nil, startParam, err
	}

	var builder strings.Builder
	builder.WriteString(trimmedName)
	builder.WriteString(" AS (\n")

	args := make([]any, 0, len(values))
	param := startParam
	for i, value := range values {
		if i > 0 {
			builder.WriteString("\n    UNION ALL\n")
		}
		builder.WriteString("    SELECT CAST($")
		builder.WriteString(strconv.Itoa(param))
		builder.WriteString(" AS DECIMAL(20,0)) AS ")
		builder.WriteString(trimmedColumn)
		args = append(args, value)
		param++
	}

	builder.WriteString("\n)")
	return builder.String(), args, param, nil
}

func validateStartParam(startParam int) error {
	if startParam < 1 {
		return fmt.Errorf("startParam must be at least 1")
	}
	return nil
}