package soqlexpr_test

import (
	"context"
	"reflect"
	"strings"
	"testing"

	soql "github.com/nexuscrm/dialect-sf-crm-soql"
	"github.com/nexuscrm/kernel/pkg/formula"
	"github.com/nexuscrm/shared/pkg/models"
	"github.com/nexuscrm/shared/pkg/queryspec"
)

// End-to-end SOQL-parser → semi-join-resolver → formula SQL compiler contract matrix.
//
// This test exercises the seam between the standalone SOQL dialect module
// (github.com/nexuscrm/dialect-sf-crm-soql) and the backend formula SQL compiler
// (backend/pkg/formula). It lives in the distribution-owned module — not in the
// seal-clean SOQL module or the app-blind backend module — because it imports
// both backend/pkg/formula and the app-owned SOQL parser. It is a
// black-box test of the SOQL module's PUBLIC surface (ParseSOQLWith +
// ResolveSemiJoins), so it does not reach into any unexported SOQL helper.
//
// Why this exists: PR #543's review history shows the failure mode is always a
// contract mismatch BETWEEN layers (parser ↔ resolver ↔ formula compiler ↔ SQL walker).
// Six review rounds each found a different mismatch by reading code; nobody had
// been running the full pipeline end-to-end and asserting the final SQL string.
// This matrix runs ParseSOQLWith → ResolveSemiJoins → formula.ToSQL for every shape
// combination so any future drift in any layer fails a test instead of slipping
// through to production.
//
// The matrix covers:
//   - IN vs NOT IN, with non-empty and empty inner sets
//   - Multi-clause queries (IN + IN, IN + NOT IN)
//   - Composition with AND
//   - Quote / backslash escaping in returned IDs

// seqResolver returns different IDs per call so multi-clause queries can be
// resolved deterministically in parser-clause order. It satisfies
// soql.SemiJoinResolver.
type seqResolver struct {
	results [][]string
	idx     int
}

func (s *seqResolver) ResolveSemiJoinIDs(ctx context.Context, inner *models.QueryRequest) ([]string, error) {
	r := s.results[s.idx]
	s.idx++
	return r, nil
}

func resolvedFilterExpression(t *testing.T, req *models.QueryRequest) string {
	t.Helper()
	if strings.TrimSpace(req.FilterExpr) != "" {
		return req.FilterExpr
	}
	lowering, err := queryspec.LowerPredicate(req.Filter, queryspec.PredicateLoweringExpressionOnly)
	if err != nil {
		t.Fatalf("LowerPredicate: %v", err)
	}
	expr := lowering.Expression
	if strings.TrimSpace(expr) == "" {
		t.Fatalf("resolved filter expression is empty")
	}
	return expr
}

func TestSOQLParentFieldWhereSmokeParsesIssue3036Probes(t *testing.T) {
	cases := []struct {
		name            string
		soql            string
		wantObject      string
		wantSelect      []string
		wantFilterParts []string
	}{
		{
			name:       "select traversal control",
			soql:       `SELECT Id, Account.Industry FROM Opportunity LIMIT 1`,
			wantObject: "Opportunity",
			wantSelect: []string{"Id", "Account.Industry"},
		},
		{
			name:            "one-hop parent-field where",
			soql:            `SELECT Id FROM Opportunity WHERE Account.Industry = 'DNB-Software' LIMIT 1`,
			wantObject:      "Opportunity",
			wantSelect:      []string{"Id"},
			wantFilterParts: []string{"Account.Industry", "DNB-Software"},
		},
		{
			name:            "multi-hop parent-field where",
			soql:            `SELECT Id FROM Contact WHERE Account.Owner.Name = 'x' LIMIT 1`,
			wantObject:      "Contact",
			wantSelect:      []string{"Id"},
			wantFilterParts: []string{"Account.Owner.Name", "x"},
		},
	}

	for _, tc := range cases {
		t.Run(tc.name, func(t *testing.T) {
			req, err := soql.ParseSOQLWith(tc.soql, soql.ParseOpts{AllowSemiJoin: true})
			if err != nil {
				t.Fatalf("ParseSOQLWith: %v", err)
			}
			if req.ObjectAPIName != tc.wantObject {
				t.Fatalf("ObjectAPIName = %q, want %q", req.ObjectAPIName, tc.wantObject)
			}
			if !reflect.DeepEqual(req.SelectFields, tc.wantSelect) {
				t.Fatalf("SelectFields = %#v, want %#v", req.SelectFields, tc.wantSelect)
			}
			if len(tc.wantFilterParts) == 0 {
				if strings.TrimSpace(req.FilterExpr) != "" {
					t.Fatalf("FilterExpr = %q, want empty", req.FilterExpr)
				}
				return
			}
			filterExpr := resolvedFilterExpression(t, req)
			for _, part := range tc.wantFilterParts {
				if !strings.Contains(filterExpr, part) {
					t.Fatalf("FilterExpr = %q, want to contain %q", filterExpr, part)
				}
			}
		})
	}
}

func TestSemiJoinE2E_ResolvedFilterCompilesToSQL(t *testing.T) {
	cases := []struct {
		name        string
		soql        string
		innerIDs    [][]string // one entry per SemiJoinClause, in parser order
		wantSQLPart string     // a substring that must appear in the final SQL
		wantArgs    []interface{}
		wantErrSub  string // if non-empty, ToSQL must error and msg contain this
	}{
		{
			name:        "IN with non-empty inner → IN array SQL",
			soql:        `SELECT Id FROM Quote WHERE OpportunityId IN (SELECT Id FROM Opportunity WHERE Name = 'Acme')`,
			innerIDs:    [][]string{{"006A", "006B"}},
			wantSQLPart: "OpportunityId IN (?, ?)",
			wantArgs:    []interface{}{"006A", "006B"},
		},
		{
			name:        "NOT IN with non-empty inner → NOT (… IN array …)",
			soql:        `SELECT Id FROM Account WHERE Id NOT IN (SELECT AccountId FROM Contact WHERE Active__c = true)`,
			innerIDs:    [][]string{{"001A", "001B", "001C"}},
			wantSQLPart: "NOT ((Id IN (?, ?, ?)))",
			wantArgs:    []interface{}{"001A", "001B", "001C"},
		},
		{
			// expr-lang treats bare `false` as a BoolNode; walker emits it
			// as a parameterized `?` with args=[false]. TiDB short-circuits
			// `WHERE ? (false)` to no rows — which is exactly the IN-empty
			// semi-join semantic.
			name:        "IN with empty inner → false constant short-circuits",
			soql:        `SELECT Id FROM Quote WHERE OpportunityId IN (SELECT Id FROM Opportunity WHERE Name = 'NoMatch')`,
			innerIDs:    [][]string{{}}, // empty
			wantSQLPart: "?",
			wantArgs:    []interface{}{false},
		},
		{
			name:        "NOT IN with empty inner → true constant",
			soql:        `SELECT Id FROM Quote WHERE OpportunityId NOT IN (SELECT Id FROM Opportunity WHERE Name = 'NoMatch')`,
			innerIDs:    [][]string{{}},
			wantSQLPart: "?",
			wantArgs:    []interface{}{true},
		},
		{
			name:        "IN composed with AND",
			soql:        `SELECT Id FROM Quote WHERE Status = 'Approved' AND OpportunityId IN (SELECT Id FROM Opportunity)`,
			innerIDs:    [][]string{{"006A"}},
			wantSQLPart: "OpportunityId IN (?)",
			wantArgs:    []interface{}{"Approved", "006A"},
		},
		{
			name:        "two IN clauses substituted independently",
			soql:        `SELECT Id FROM Quote WHERE OpportunityId IN (SELECT Id FROM Opportunity) AND AccountId IN (SELECT Id FROM Account)`,
			innerIDs:    [][]string{{"006A"}, {"001X", "001Y"}},
			wantSQLPart: "AccountId IN (?, ?)",
			wantArgs:    []interface{}{"006A", "001X", "001Y"},
		},
		{
			name:        "IN + NOT IN composed",
			soql:        `SELECT Id FROM Quote WHERE OpportunityId IN (SELECT Id FROM Opportunity) AND AccountId NOT IN (SELECT Id FROM Account)`,
			innerIDs:    [][]string{{"006A"}, {"001X"}},
			wantSQLPart: "NOT ((AccountId IN (?)))",
			wantArgs:    []interface{}{"006A", "001X"},
		},
		{
			name:        "ID containing single quote round-trips through escape",
			soql:        `SELECT Id FROM Quote WHERE OpportunityId IN (SELECT Id FROM Opportunity)`,
			innerIDs:    [][]string{{"a'b"}},
			wantSQLPart: "OpportunityId IN (?)",
			wantArgs:    []interface{}{"a'b"},
		},
		{
			name:        "ID containing backslash round-trips through escape",
			soql:        `SELECT Id FROM Quote WHERE OpportunityId IN (SELECT Id FROM Opportunity)`,
			innerIDs:    [][]string{{`a\b`}},
			wantSQLPart: "OpportunityId IN (?)",
			wantArgs:    []interface{}{`a\b`},
		},
	}

	for _, tc := range cases {
		t.Run(tc.name, func(t *testing.T) {
			req, err := soql.ParseSOQLWith(tc.soql, soql.ParseOpts{AllowSemiJoin: true})
			if err != nil {
				t.Fatalf("ParseSOQLWith: %v", err)
			}
			res := &seqResolver{results: tc.innerIDs}
			if err := soql.ResolveSemiJoins(context.Background(), req, res); err != nil {
				t.Fatalf("ResolveSemiJoins: %v", err)
			}

			filterExpr := resolvedFilterExpression(t, req)
			sql, args, err := formula.ToSQL(filterExpr)
			t.Logf("filter expression post-resolve: %q", filterExpr)
			t.Logf("SQL=%q args=%v err=%v", sql, args, err)

			if tc.wantErrSub != "" {
				if err == nil {
					t.Fatalf("want ToSQL error containing %q, got nil", tc.wantErrSub)
				}
				if !strings.Contains(err.Error(), tc.wantErrSub) {
					t.Fatalf("want ToSQL error %q, got %v", tc.wantErrSub, err)
				}
				return
			}
			if err != nil {
				t.Fatalf("ToSQL failed end-to-end — this is the failure mode reviewers have been guessing at: %v", err)
			}
			if !strings.Contains(sql, tc.wantSQLPart) {
				t.Fatalf("SQL missing expected substring %q\n  got SQL: %q", tc.wantSQLPart, sql)
			}
			if !sameArgs(args, tc.wantArgs) {
				t.Fatalf("args mismatch:\n  got:  %v\n  want: %v", args, tc.wantArgs)
			}
		})
	}
}

func sameArgs(a, b []interface{}) bool {
	if len(a) != len(b) {
		return false
	}
	for i := range a {
		if a[i] != b[i] {
			return false
		}
	}
	return true
}

// Sanity: the original PR review claim that NOT IN with array works
// end-to-end, recorded as a stable test instead of a one-off probe.
func TestSemiJoinE2E_NotInWithArrayProducesValidSQL(t *testing.T) {
	expressions := []string{
		`Id in ['a','b']`,
		`Id not in ['a','b']`,
		`!(Id in ['a','b'])`,
	}
	for _, e := range expressions {
		t.Run(e, func(t *testing.T) {
			sql, _, err := formula.ToSQL(e)
			if err != nil {
				t.Fatalf("ToSQL(%q): %v", e, err)
			}
			if sql == "" {
				t.Fatalf("ToSQL(%q) returned empty SQL", e)
			}
			if !strings.Contains(sql, "IN (") {
				t.Fatalf("ToSQL(%q) = %q; want contains IN clause", e, sql)
			}
		})
	}
}

// Round-trip: every shape the resolver can emit must compile via ToSQL without
// error. This is the one-line backstop against future shape drift in either the
// resolver's placeholder substitution or the formula SQL compiler.
//
// The original in-package test reached the unexported substituteSemiJoinPlaceholder
// directly; as a black-box test against the SOQL module's public surface this
// drives the SAME substitution through ResolveSemiJoins (which calls it
// internally) for each shape, then asserts the resolved predicate compiles.
func TestSemiJoinE2E_AllResolverOutputsAreWalkerParseable(t *testing.T) {
	type shape struct {
		soql string // a single-clause semi-join query; OpportunityId IN/NOT IN (...)
		ids  []string
	}
	const inQuery = `SELECT Id FROM Quote WHERE OpportunityId IN (SELECT Id FROM Opportunity)`
	const notInQuery = `SELECT Id FROM Quote WHERE OpportunityId NOT IN (SELECT Id FROM Opportunity)`
	shapes := []shape{
		{inQuery, []string{"x"}},
		{inQuery, []string{"x", "y"}},
		{inQuery, []string{"x", "y", "z"}},
		{notInQuery, []string{"x"}},
		{notInQuery, []string{"x", "y"}},
		{inQuery, []string{}},    // empty IN → "false"
		{notInQuery, []string{}}, // empty NOT IN → "true"
	}
	for _, s := range shapes {
		t.Run(s.soql, func(t *testing.T) {
			req, err := soql.ParseSOQLWith(s.soql, soql.ParseOpts{AllowSemiJoin: true})
			if err != nil {
				t.Fatalf("ParseSOQLWith: %v", err)
			}
			res := &seqResolver{results: [][]string{s.ids}}
			if err := soql.ResolveSemiJoins(context.Background(), req, res); err != nil {
				t.Fatalf("ResolveSemiJoins: %v", err)
			}
			filterExpr := resolvedFilterExpression(t, req)
			t.Logf("ids=%v → %q", s.ids, filterExpr)
			if _, _, err := formula.ToSQL(filterExpr); err != nil {
				t.Fatalf("resolver emitted unparseable expression %q: %v", filterExpr, err)
			}
		})
	}
}