package libhelper

import (
	"fmt"
	"regexp"
	pl "simrs-vx/pkg/logger"
	"strings"
	"unicode"

	"github.com/jackc/pgx/v5/pgconn"
	"gorm.io/gorm"
)

func SearchCodeOrName(search string, tx *gorm.DB) *gorm.DB {
	if search != "" {
		tx = tx.Where("\"Code\" ILIKE ? OR \"Name\" ILIKE ?", "%"+search+"%", "%"+search+"%")
	}

	return tx
}

func Sort(sort string, tx *gorm.DB) *gorm.DB {
	if sort == "" {
		return tx
	}

	pairs := strings.Split(sort, ",")
	for _, pair := range pairs {
		parts := strings.Split(strings.TrimSpace(pair), ":")
		if len(parts) != 2 {
			continue // skip invalid format
		}

		field := strings.TrimSpace(parts[0])
		direction := strings.ToUpper(strings.TrimSpace(parts[1]))

		if direction != "ASC" && direction != "DESC" {
			continue
		}

		field = normalizeColumnName(field)

		tx = tx.Order(fmt.Sprintf("\"%s\" %s", field, direction))
	}

	return tx
}

func normalizeColumnName(input string) string {
	if input == "" {
		return input
	}

	input = strings.ReplaceAll(input, "-", "_")

	runes := []rune(input)
	var out []rune
	upperNext := true
	for _, r := range runes {
		if r == '_' {
			out = append(out, r)
			upperNext = true
			continue
		}
		if upperNext {
			out = append(out, unicode.ToUpper(r))
			upperNext = false
		} else {
			out = append(out, r)
		}
	}
	return string(out)
}

func HandleCreateError(input any, event *pl.Event, err error) error {
	e, ok := err.(*pgconn.PgError)
	if !ok {
		// fallback if it's not a pg error
		event.Status = "failed"
		event.ErrInfo = pl.ErrorInfo{
			Code:   "data-create-fail",
			Detail: fmt.Sprintf("Database insert failed: %s", err.Error()),
			Raw:    err,
		}
		return pl.SetLogError(event, input)
	}

	switch e.Code {
	case "23505": // unique_violation
		event.Status = "failed"
		event.ErrInfo = pl.ErrorInfo{
			Code:   "data-create-duplicate",
			Detail: fmt.Sprintf("Duplicate value violates unique constraint: %s", e.ConstraintName),
			Raw:    err,
		}
		return pl.SetLogError(event, input)

	case "23503": // foreign_key_violation
		event.Status = "failed"
		event.ErrInfo = pl.ErrorInfo{
			Code:   "data-create-fkViolation",
			Detail: fmt.Sprintf("Foreign key constraint '%s' violated. Please check related record exists.", e.ConstraintName),
			Raw:    err,
		}
		return pl.SetLogError(event, input)

	default:
		event.Status = "failed"
		event.ErrInfo = pl.ErrorInfo{
			Code:   "data-create-fail",
			Detail: fmt.Sprintf("Database insert failed: %s", e.Message),
			Raw:    err,
		}
		return pl.SetLogError(event, input)
	}
}

func HandleListError(input any, event *pl.Event, err error) error {
	if err == nil {
		return nil
	}

	e, ok := err.(*pgconn.PgError)
	if !ok {
		// fallback if it's not a pg error
		event.Status = "failed"
		event.ErrInfo = pl.ErrorInfo{
			Code:   "data-list-fail",
			Detail: fmt.Sprintf("database list failed: %s", err.Error()),
			Raw:    err,
		}
		return pl.SetLogError(event, input)
	}

	re := regexp.MustCompile(`^([^:]+):\s*unsupported relations for schema\s+(\S+)`)
	if matches := re.FindStringSubmatch(e.Message); len(matches) == 3 {
		relation := strings.TrimSpace(matches[1])
		schema := strings.TrimSpace(matches[2])

		event.Status = "failed"
		event.ErrInfo = pl.ErrorInfo{
			Code:   "invalid-relation",
			Detail: fmt.Sprintf("Invalid relation '%s' for schema '%s' — check the relation name.", relation, schema),
			Raw:    e,
		}
		return pl.SetLogError(event, input)
	}

	if strings.Contains(e.Message, "unsupported relations for schema") {
		event.Status = "failed"
		event.ErrInfo = pl.ErrorInfo{
			Code:   "invalid-relation",
			Detail: fmt.Sprintf("Unsupported relation detected: %s", e.Message),
			Raw:    e,
		}
		return pl.SetLogError(event, input)
	}

	// Generic pg error fallback
	event.Status = "failed"
	event.ErrInfo = pl.ErrorInfo{
		Code:   "pg-error",
		Detail: fmt.Sprintf("PostgreSQL error: %s", e.Message),
		Raw:    e,
	}
	return pl.SetLogError(event, input)
}