package dialectsfcrm

import (
	"encoding/json"
	"fmt"
	"regexp"
	"strconv"
	"strings"

	"github.com/nexuscrm/shared/pkg/contract"
	"github.com/nexuscrm/shared/pkg/models"
)

type reportDefinitionDialect struct{}

func NewReportDefinitionDialect() contract.ReportDefinitionDialect {
	return reportDefinitionDialect{}
}

func (reportDefinitionDialect) ParseStoredDefinition(row contract.Record) (*models.ReportDefinition, error) {
	sourceObject := reportRecordString(row, "SourceObject")
	sourceRef := reportRecordString(row, "SourceRef")
	reportType := reportRecordString(row, "ReportType")
	if sourceRef == "" {
		sourceRef = salesforceSourceRefFromLegacyReportType(reportType)
	}
	if sourceRef == "" && sourceObject != "" {
		resolvedSourceObject := reportDefinitionDialect{}.ResolveSourceObject(sourceObject, nil)
		if resolvedSourceObject != "" && !strings.EqualFold(resolvedSourceObject, sourceObject) {
			sourceRef = sourceObject
			sourceObject = resolvedSourceObject
		}
	}
	displayMode := reportRecordString(row, "DisplayMode")
	if displayMode == "" {
		displayMode = reportRecordString(row, "Format")
	}
	if displayMode == "" {
		displayMode = salesforceDisplayModeFromLegacyReportType(reportType)
	}
	filterLogic := reportRecordString(row, "FilterLogic")
	if filterLogic == "" {
		filterLogic = reportRecordString(row, "BooleanFilter")
	}

	def := &models.ReportDefinition{
		ID:           reportRecordString(row, "Id"),
		Name:         reportRecordString(row, "Name"),
		SourceObject: sourceObject,
		SourceRef:    sourceRef,
		DisplayMode:  displayMode,
		FilterLogic:  filterLogic,
		Scope:        reportRecordString(row, "Scope"),
	}

	if colStr := getReportJSONString(row, "Columns"); colStr != "" {
		if err := json.Unmarshal([]byte(colStr), &def.Columns); err != nil {
			return nil, fmt.Errorf("failed to parse columns (val=%s): %w", colStr, err)
		}
	}
	if filterStr := getReportJSONString(row, "Filters"); filterStr != "" {
		if err := parseSalesforceFilters(filterStr, &def.Filters); err != nil {
			def.Filters = nil
		}
	}
	if groupStr := getReportJSONString(row, "Grouping"); groupStr != "" {
		if err := parseSalesforceGrouping(groupStr, &def.Grouping); err != nil {
			return nil, fmt.Errorf("failed to parse grouping (val=%s): %w", groupStr, err)
		}
	}
	if aggStr := getReportJSONString(row, "Aggregates"); aggStr != "" {
		if err := parseSalesforceAggregates(aggStr, &def.Aggregates); err != nil {
			return nil, fmt.Errorf("failed to parse aggregates (val=%s): %w", aggStr, err)
		}
	}
	if sdfStr := getReportJSONString(row, "StandardDateFilter"); sdfStr != "" {
		var dto salesforceStandardDateFilterWire
		if err := json.Unmarshal([]byte(sdfStr), &dto); err == nil && dto.Column != "" {
			def.DateFilter = salesforceStandardDateFilterToModel(&dto)
		}
	}

	if def.DisplayMode == "" {
		switch {
		case strings.EqualFold(def.EffectiveSourceRef(), "matrix"):
			def.DisplayMode = "MATRIX"
		case strings.EqualFold(def.EffectiveSourceRef(), "summary"), len(def.Grouping) > 0:
			def.DisplayMode = "SUMMARY"
		default:
			def.DisplayMode = "TABULAR"
		}
	}
	def.NormalizeBoundaryAliases()

	return def, nil
}

func (reportDefinitionDialect) ResolveSourceObject(sourceObject string, lookup contract.ReportSourceObjectLookup) string {
	if lookup != nil {
		if canonical := lookup(sourceObject); canonical != "" {
			return canonical
		}
	}
	if strings.Contains(sourceObject, "CustomEntity") {
		if dollar := strings.Index(sourceObject, "$"); dollar >= 0 {
			rest := sourceObject[dollar+1:]
			if at := strings.Index(rest, "@"); at >= 0 {
				rest = rest[:at]
			}
			return rest
		}
	}
	if i := strings.Index(sourceObject, "With"); i > 0 {
		sourceObject = sourceObject[:i]
	}
	if strings.HasSuffix(sourceObject, "Report") && len(sourceObject) > len("Report") {
		sourceObject = strings.TrimSuffix(sourceObject, "Report")
	}
	for _, suffix := range []string{"FieldAuditHistory", "AuditHistory", "Activity", "List"} {
		if strings.HasSuffix(sourceObject, suffix) {
			sourceObject = strings.TrimSuffix(sourceObject, suffix)
			break
		}
	}
	return sourceObject
}

func (reportDefinitionDialect) NormalizeColumn(column string) string {
	if dot := strings.LastIndex(column, "."); dot >= 0 {
		return column[dot+1:]
	}
	if strings.Contains(column, "_") {
		return screamingSnakeToPascal(column)
	}
	return column
}

func (reportDefinitionDialect) SupplementalStoredDefinitionFields() []string {
	return []string{"BooleanFilter"}
}

func salesforceSourceRefFromLegacyReportType(reportType string) string {
	if isSalesforceReportDisplayModeLiteral(reportType) {
		return ""
	}
	return reportType
}

func salesforceDisplayModeFromLegacyReportType(reportType string) string {
	if !isSalesforceReportDisplayModeLiteral(reportType) {
		return ""
	}
	return strings.ToUpper(strings.TrimSpace(reportType))
}

func isSalesforceReportDisplayModeLiteral(value string) bool {
	switch strings.ToUpper(strings.TrimSpace(value)) {
	case "TABULAR", "SUMMARY", "MATRIX":
		return true
	default:
		return false
	}
}

func (reportDefinitionDialect) BuildFilterExpression(req contract.ReportFilterExpressionRequest) (contract.ReportFilterExpression, bool) {
	expr := strings.TrimSpace(req.FilterLogic)
	if expr == "" {
		expr = strings.TrimSpace(req.BooleanFilter)
	}
	if expr == "" {
		if req.Language != contract.ReportFilterExpressionLanguageFormula || !filtersNeedFormulaCompilation(req.Filters) {
			return contract.ReportFilterExpression{}, false
		}
		if len(req.Filters) == 0 {
			return contract.ReportFilterExpression{}, false
		}
		tokens := make([]string, len(req.Filters))
		for i := range req.Filters {
			tokens[i] = strconv.Itoa(i + 1)
		}
		expr = strings.Join(tokens, " AND ")
	} else {
		hasOR := containsWordOR(expr)
		hasNOT := containsWordNOT(expr)
		switch req.Language {
		case contract.ReportFilterExpressionLanguageSQL:
			if !hasOR && !hasNOT {
				return contract.ReportFilterExpression{}, false
			}
		case contract.ReportFilterExpressionLanguageFormula:
			if !hasOR && !hasNOT && !filtersNeedFormulaCompilation(req.Filters) {
				return contract.ReportFilterExpression{}, false
			}
		default:
			return contract.ReportFilterExpression{}, false
		}
	}

	if !validReportBooleanFilter(expr) {
		return contract.ReportFilterExpression{}, false
	}
	return buildNumberedFilterExpression(req.Language, expr, req.Fragments)
}

func getReportJSONString(row contract.Record, key string) string {
	val, ok := row[key]
	if !ok || val == nil {
		return ""
	}
	if s, ok := val.(string); ok {
		return s
	}
	b, err := json.Marshal(val)
	if err != nil {
		return ""
	}
	return string(b)
}

func reportRecordString(row contract.Record, key string) string {
	if row == nil {
		return ""
	}
	value, ok := row[key]
	if !ok || value == nil {
		return ""
	}
	if s, ok := value.(string); ok {
		return s
	}
	return fmt.Sprintf("%v", value)
}

func parseSalesforceGrouping(raw string, out *[]string) error {
	var simple []string
	if err := json.Unmarshal([]byte(raw), &simple); err == nil {
		*out = simple
		return nil
	}
	var objects []map[string]interface{}
	if err := json.Unmarshal([]byte(raw), &objects); err != nil {
		return err
	}
	names := make([]string, 0, len(objects))
	for _, obj := range objects {
		if n, ok := obj["name"].(string); ok && n != "" {
			names = append(names, n)
		}
	}
	*out = names
	return nil
}

func parseSalesforceFilters(raw string, out *[]models.ReportFilter) error {
	var structured []salesforceReportFilterWire
	if err := json.Unmarshal([]byte(raw), &structured); err != nil {
		return err
	}
	*out = salesforceReportFiltersToModel(structured)
	return nil
}

func parseSalesforceAggregates(raw string, out *[]models.ReportAggregate) error {
	var structured []salesforceReportAggregateWire
	if err := json.Unmarshal([]byte(raw), &structured); err == nil {
		if len(structured) > 0 && (structured[0].Field != "" || structured[0].Type != "") {
			*out = salesforceReportAggregatesToModel(structured)
			return nil
		}
	}
	var keys []string
	if err := json.Unmarshal([]byte(raw), &keys); err != nil {
		return err
	}
	aggs := make([]models.ReportAggregate, len(keys))
	for i, k := range keys {
		if strings.HasPrefix(k, "s!") {
			aggs[i] = models.ReportAggregate{Field: k[2:], Type: "SUM"}
		} else if strings.HasPrefix(k, "a!") {
			aggs[i] = models.ReportAggregate{Field: k[2:], Type: "AVG"}
		} else if strings.HasPrefix(k, "mx!") {
			aggs[i] = models.ReportAggregate{Field: k[3:], Type: "MAX"}
		} else if strings.HasPrefix(k, "mn!") {
			aggs[i] = models.ReportAggregate{Field: k[3:], Type: "MIN"}
		} else {
			aggs[i] = models.ReportAggregate{Field: "", Type: k}
		}
	}
	*out = aggs
	return nil
}

func screamingSnakeToPascal(s string) string {
	parts := strings.FieldsFunc(strings.ToLower(s), func(r rune) bool {
		return r == '_' || r == ' ' || r == '-'
	})
	if len(parts) == 0 {
		return s
	}
	var b strings.Builder
	for _, part := range parts {
		if part == "" {
			continue
		}
		b.WriteString(strings.ToUpper(part[:1]))
		if len(part) > 1 {
			b.WriteString(part[1:])
		}
	}
	if b.Len() == 0 {
		return s
	}
	return b.String()
}

func filtersNeedFormulaCompilation(filters []models.ReportFilter) bool {
	for _, f := range filters {
		op := strings.ToLower(f.Operator)
		if op != "contains" && op != "notcontain" && op != "doesnotcontain" {
			continue
		}
		if !strings.Contains(f.Value, ",") {
			continue
		}
		if len(splitAndTrimMulti(f.Value)) > 1 {
			return true
		}
	}
	return false
}

func splitAndTrimMulti(s string) []string {
	raw := strings.Split(s, ",")
	out := make([]string, 0, len(raw))
	for _, p := range raw {
		p = strings.TrimSpace(p)
		if p != "" {
			out = append(out, p)
		}
	}
	return out
}

var reportFilterTokenRe = regexp.MustCompile(`\b\d+\b`)

type reportBoolTokenKind int

const (
	reportBoolTokenNumber reportBoolTokenKind = iota
	reportBoolTokenAnd
	reportBoolTokenOr
	reportBoolTokenNot
	reportBoolTokenLParen
	reportBoolTokenRParen
)

type reportBoolToken struct {
	kind reportBoolTokenKind
}

func validReportBooleanFilter(expr string) bool {
	tokens, ok := tokenizeReportBooleanFilter(expr)
	if !ok || len(tokens) == 0 {
		return false
	}
	p := reportBoolParser{tokens: tokens}
	if !p.parseExpression() {
		return false
	}
	return p.pos == len(tokens)
}

func tokenizeReportBooleanFilter(expr string) ([]reportBoolToken, bool) {
	tokens := make([]reportBoolToken, 0, len(expr)/2)
	for i := 0; i < len(expr); {
		ch := expr[i]
		switch {
		case ch == ' ' || ch == '\t' || ch == '\n' || ch == '\r':
			i++
		case ch == '(':
			tokens = append(tokens, reportBoolToken{kind: reportBoolTokenLParen})
			i++
		case ch == ')':
			tokens = append(tokens, reportBoolToken{kind: reportBoolTokenRParen})
			i++
		case ch >= '0' && ch <= '9':
			start := i
			for i < len(expr) && expr[i] >= '0' && expr[i] <= '9' {
				i++
			}
			if i < len(expr) && isIdentByte(expr[i]) {
				return nil, false
			}
			if start == i {
				return nil, false
			}
			tokens = append(tokens, reportBoolToken{kind: reportBoolTokenNumber})
		case (ch >= 'a' && ch <= 'z') || (ch >= 'A' && ch <= 'Z'):
			start := i
			for i < len(expr) && ((expr[i] >= 'a' && expr[i] <= 'z') || (expr[i] >= 'A' && expr[i] <= 'Z')) {
				i++
			}
			if i < len(expr) && isIdentByte(expr[i]) {
				return nil, false
			}
			word := strings.ToUpper(expr[start:i])
			switch word {
			case "AND":
				tokens = append(tokens, reportBoolToken{kind: reportBoolTokenAnd})
			case "OR":
				tokens = append(tokens, reportBoolToken{kind: reportBoolTokenOr})
			case "NOT":
				tokens = append(tokens, reportBoolToken{kind: reportBoolTokenNot})
			default:
				return nil, false
			}
		default:
			return nil, false
		}
	}
	return tokens, true
}

type reportBoolParser struct {
	tokens []reportBoolToken
	pos    int
}

func (p *reportBoolParser) parseExpression() bool {
	if !p.parseTerm() {
		return false
	}
	for p.match(reportBoolTokenOr) {
		if !p.parseTerm() {
			return false
		}
	}
	return true
}

func (p *reportBoolParser) parseTerm() bool {
	if !p.parseFactor() {
		return false
	}
	for p.match(reportBoolTokenAnd) {
		if !p.parseFactor() {
			return false
		}
	}
	return true
}

func (p *reportBoolParser) parseFactor() bool {
	if p.match(reportBoolTokenNot) {
		return p.parseFactor()
	}
	if p.match(reportBoolTokenNumber) {
		return true
	}
	if p.match(reportBoolTokenLParen) {
		if !p.parseExpression() {
			return false
		}
		return p.match(reportBoolTokenRParen)
	}
	return false
}

func (p *reportBoolParser) match(kind reportBoolTokenKind) bool {
	if p.pos >= len(p.tokens) || p.tokens[p.pos].kind != kind {
		return false
	}
	p.pos++
	return true
}

func buildNumberedFilterExpression(language contract.ReportFilterExpressionLanguage, expr string, fragments []contract.ReportFilterExpressionFragment) (contract.ReportFilterExpression, bool) {
	fragmentByIndex := make(map[int]contract.ReportFilterExpressionFragment, len(fragments))
	for _, fragment := range fragments {
		if fragment.Index > 0 && fragment.Expression != "" {
			fragmentByIndex[fragment.Index] = fragment
		}
	}

	matches := reportFilterTokenRe.FindAllStringIndex(expr, -1)
	if len(matches) == 0 {
		return contract.ReportFilterExpression{}, false
	}

	var out strings.Builder
	var params []any
	last := 0
	for _, match := range matches {
		idx, err := strconv.Atoi(expr[match[0]:match[1]])
		if err != nil {
			return contract.ReportFilterExpression{}, false
		}
		fragment, ok := fragmentByIndex[idx]
		if !ok {
			return contract.ReportFilterExpression{}, false
		}
		out.WriteString(expr[last:match[0]])
		out.WriteString("(")
		out.WriteString(fragment.Expression)
		out.WriteString(")")
		params = append(params, fragment.Params...)
		last = match[1]
	}
	out.WriteString(expr[last:])

	replaced := replaceReportBoolOperatorsOutsideLiterals(out.String(), language)
	if replaced == "" {
		return contract.ReportFilterExpression{}, false
	}
	return contract.ReportFilterExpression{Expression: replaced, Params: params}, true
}

func replaceReportBoolOperatorsOutsideLiterals(s string, language contract.ReportFilterExpressionLanguage) string {
	andToken, orToken, notToken := "", "", ""
	switch language {
	case contract.ReportFilterExpressionLanguageFormula:
		andToken, orToken, notToken = "&&", "||", "!"
	case contract.ReportFilterExpressionLanguageSQL:
		andToken, orToken, notToken = "AND", "OR", "NOT"
	default:
		return ""
	}

	var b strings.Builder
	inSingle, inDouble := false, false
	i := 0
	for i < len(s) {
		ch := s[i]
		if ch == '\'' && !inDouble {
			inSingle = !inSingle
			b.WriteByte(ch)
			i++
			continue
		}
		if ch == '"' && !inSingle {
			inDouble = !inDouble
			b.WriteByte(ch)
			i++
			continue
		}
		if inSingle || inDouble {
			b.WriteByte(ch)
			i++
			continue
		}
		prevIsIdent := i > 0 && isIdentByte(s[i-1])
		if !prevIsIdent && i+2 <= len(s) && strings.EqualFold(s[i:i+2], "OR") && (i+2 == len(s) || !isIdentByte(s[i+2])) {
			b.WriteString(orToken)
			i += 2
			continue
		}
		if !prevIsIdent && i+3 <= len(s) && strings.EqualFold(s[i:i+3], "AND") && (i+3 == len(s) || !isIdentByte(s[i+3])) {
			b.WriteString(andToken)
			i += 3
			continue
		}
		if !prevIsIdent && i+3 <= len(s) && strings.EqualFold(s[i:i+3], "NOT") && (i+3 == len(s) || !isIdentByte(s[i+3])) {
			b.WriteString(notToken)
			i += 3
			continue
		}
		b.WriteByte(ch)
		i++
	}
	return b.String()
}

func containsWordOR(s string) bool {
	return containsReportBoolWord(s, "OR")
}

func containsWordNOT(s string) bool {
	return containsReportBoolWord(s, "NOT")
}

func containsReportBoolWord(s string, word string) bool {
	upper := strings.ToUpper(s)
	word = strings.ToUpper(word)
	for i := 0; i <= len(upper)-len(word); i++ {
		if upper[i:i+len(word)] != word {
			continue
		}
		prevOk := i == 0 || !isIdentByte(upper[i-1])
		nextOk := i+len(word) == len(upper) || !isIdentByte(upper[i+len(word)])
		if prevOk && nextOk {
			return true
		}
	}
	return false
}

func isIdentByte(c byte) bool {
	return (c >= 'a' && c <= 'z') || (c >= 'A' && c <= 'Z') || (c >= '0' && c <= '9') || c == '_'
}