package utils

import (
	"fmt"
	"strings"
)

// MultiPicklistJSONContainsSQL emits the authoritative multi-picklist
// membership predicate. New writes use canonical JSON arrays, while upgraded
// databases can still contain legacy JSON strings such as "A;B"; both shapes
// must read through the same predicate so deploy does not depend on a full
// historical L3 data rewrite.
func MultiPicklistJSONContainsSQL(fieldSQL, valueSQL string, wantContains bool) string {
	readExpr := fmt.Sprintf(
		"(CASE WHEN JSON_TYPE(%s) = 'STRING' THEN CASE WHEN TRIM(JSON_UNQUOTE(%s)) = '' THEN JSON_ARRAY() ELSE CAST(CONCAT('[', REPLACE(JSON_QUOTE(JSON_UNQUOTE(%s)), ';', '\",\"'), ']') AS JSON) END ELSE %s END)",
		fieldSQL,
		fieldSQL,
		fieldSQL,
		fieldSQL,
	)
	want := 0
	if wantContains {
		want = 1
	}
	return fmt.Sprintf("COALESCE(JSON_CONTAINS(%s, JSON_QUOTE(%s)), 0) = %d", readExpr, valueSQL, want)
}

// MultiPicklistContainsAllTokens is the runtime counterpart to
// MultiPicklistJSONContainsAllSQL. A semicolon-bearing authored RHS literal is
// an all-token contract: every RHS token must be selected on the LHS.
func MultiPicklistContainsAllTokens(fieldItems, searchItems []string) bool {
	if len(fieldItems) == 0 || len(searchItems) == 0 {
		return false
	}
	fieldSet := make(map[string]struct{}, len(fieldItems))
	for _, item := range fieldItems {
		item = strings.TrimSpace(item)
		if item == "" {
			continue
		}
		fieldSet[item] = struct{}{}
	}
	if len(fieldSet) == 0 {
		return false
	}
	checked := false
	for _, searchItem := range searchItems {
		searchItem = strings.TrimSpace(searchItem)
		if searchItem == "" {
			continue
		}
		checked = true
		if _, exists := fieldSet[searchItem]; !exists {
			return false
		}
	}
	return checked
}

// MultiPicklistJSONContainsAllSQL emits the SQL form of the same all-token
// contract as MultiPicklistContainsAllTokens. valueSQLs must each be a SQL
// expression for one authored RHS token.
func MultiPicklistJSONContainsAllSQL(fieldSQL string, valueSQLs []string) string {
	if len(valueSQLs) == 0 {
		return "0 = 1"
	}
	predicates := make([]string, 0, len(valueSQLs))
	for _, valueSQL := range valueSQLs {
		predicates = append(predicates, MultiPicklistJSONContainsSQL(fieldSQL, valueSQL, true))
	}
	if len(predicates) == 1 {
		return predicates[0]
	}
	return "(" + strings.Join(predicates, " AND ") + ")"
}

// MultiPicklistJSONContainsAnySQL emits an any-token SQL membership predicate
// for callers that explicitly need at-least-one RHS token semantics.
func MultiPicklistJSONContainsAnySQL(fieldSQL string, valueSQLs []string) string {
	if len(valueSQLs) == 0 {
		return "0 = 1"
	}
	predicates := make([]string, 0, len(valueSQLs))
	for _, valueSQL := range valueSQLs {
		predicates = append(predicates, MultiPicklistJSONContainsSQL(fieldSQL, valueSQL, true))
	}
	if len(predicates) == 1 {
		return predicates[0]
	}
	return "(" + strings.Join(predicates, " OR ") + ")"
}

// MultiPicklistJSONContainsNoneSQL emits a none-of SQL predicate. Every RHS
// token must be absent from the selected values.
func MultiPicklistJSONContainsNoneSQL(fieldSQL string, valueSQLs []string) string {
	if len(valueSQLs) == 0 {
		return "1 = 1"
	}
	predicates := make([]string, 0, len(valueSQLs))
	for _, valueSQL := range valueSQLs {
		predicates = append(predicates, MultiPicklistJSONContainsSQL(fieldSQL, valueSQL, false))
	}
	if len(predicates) == 1 {
		return predicates[0]
	}
	return "(" + strings.Join(predicates, " AND ") + ")"
}