package mysql

import (
	"fmt"
	"strings"

	"github.com/tidbcloud/pov-service/internal/common/options"
)

func column2Query(opt options.ColumnOption) ([]string, []any) {
	whereConditions := make([]string, 0)
	args := make([]any, 0)

	// =
	if opt.Value != nil {
		whereConditions = append(whereConditions, fmt.Sprintf("%s = ?", opt.Column))
		args = append(args, opt.Value)
	}

	// !=
	if opt.ValueNot != nil {
		whereConditions = append(whereConditions, fmt.Sprintf("%s != ?", opt.Column))
		args = append(args, opt.ValueNot)
	}
	// IN
	if len(opt.Values) > 0 {
		placeholders := strings.Repeat("?,", len(opt.Values))
		placeholders = placeholders[:len(placeholders)-1] // 去掉最后一个逗号
		whereConditions = append(whereConditions, fmt.Sprintf("%s IN (%s)", opt.Column, placeholders))
		args = append(args, opt.Values...)
	}
	// NOT IN
	if len(opt.ValuesNot) > 0 {
		placeholders := strings.Repeat("?,", len(opt.ValuesNot))
		placeholders = placeholders[:len(placeholders)-1]
		whereConditions = append(whereConditions, fmt.Sprintf("%s NOT IN (%s)", opt.Column, placeholders))
		args = append(args, opt.ValuesNot...)
	}
	// >
	if opt.Large != nil {
		whereConditions = append(whereConditions, fmt.Sprintf("%s > ?", opt.Column))
		args = append(args, opt.Large)
	}
	// >=
	if opt.LargeOrEqual != nil {
		whereConditions = append(whereConditions, fmt.Sprintf("%s >= ?", opt.Column))
		args = append(args, opt.LargeOrEqual)
	}
	// <
	if opt.Less != nil {
		whereConditions = append(whereConditions, fmt.Sprintf("%s < ?", opt.Column))
		args = append(args, opt.Less)
	}
	// <=
	if opt.LessOrEqual != nil {
		whereConditions = append(whereConditions, fmt.Sprintf("%s <= ?", opt.Column))
		args = append(args, opt.LessOrEqual)
	}
	// LIKE
	if opt.Like != "" {
		whereConditions = append(whereConditions, fmt.Sprintf("%s LIKE ?", opt.Column))
		args = append(args, opt.Like)
	}

	return whereConditions, args
}

// buildQueries constructs a SQL WHERE clause, ORDER BY, LIMIT, and OFFSET
// clauses based on the provided ListOption.
// It returns the constructed SQL query string and a slice of arguments
// to be used with a prepared statement.
//
// Example:
//
//	opts := options.ListOption{
//		Filters: []options.ColumnOption{
//			{Column: "name", Value: "test_name"},
//			{Column: "age", LargeOrEqual: 18},
//			{Column: "status", Values: []any{"active", "pending"}},
//		},
//		OrderBy: "created_at DESC",
//		Limit:   10,
//		Offset:  0,
//	}
//	query, args := buildQueries(opts)
//	// query will be: "WHERE name = ? AND age >= ? AND status IN (?,?) ORDER BY created_at DESC LIMIT ? OFFSET ?"
//	// args will be: ["test_name", 18, "active", "pending", 10, 0]
func buildQueries(options options.TableOption) (string, []any) {
	whereConditions := make([]string, 0)
	args := make([]any, 0)

	// Process filters to build WHERE conditions and arguments
	for _, filter := range options.Filters {
		conditions, conditionArgs := column2Query(filter)
		whereConditions = append(whereConditions, conditions...)
		args = append(args, conditionArgs...)
	}

	var queryBuilder strings.Builder

	// Add WHERE clause if there are conditions
	if len(whereConditions) > 0 {
		queryBuilder.WriteString("WHERE ")
		queryBuilder.WriteString(strings.Join(whereConditions, " AND "))
	}

	// Add ORDER BY clause if specified
	if options.OrderBy != "" {
		queryBuilder.WriteString(fmt.Sprintf(" ORDER BY %s", options.OrderBy))
	}

	// Add LIMIT clause if specified
	if options.Limit > 0 {
		queryBuilder.WriteString(" LIMIT ?")
		args = append(args, options.Limit)
	}

	// Add OFFSET clause if specified
	if options.Offset > 0 {
		// offset must be after limit
		// if options.Limit > 0 {
		// 	queryBuilder.WriteString(" LIMIT ?")
		// 	args = append(args, options.Limit)
		// }
		queryBuilder.WriteString(" OFFSET ?")
		args = append(args, options.Offset)
	}

	return queryBuilder.String(), args
}