// Package audit implements the neutral audit-history query plan and executor.
//
// Design (§5.1): public Salesforce-compatible `<Object>History` queries are
// parsed by the L1 Salesforce dialect and lowered to contract.HistoryQueryRequest
// before reaching this package. L0 receives the already-resolved parent object
// name and renders a neutral view over _System_AuditLog:
//
//	SELECT Id, FieldName, OldValue, NewValue, ChangedAt, ChangedById
//	FROM _System_AuditLog
//	WHERE ObjectApiName = '<ParentObject>' AND RecordId = '<parent id>'
//	  AND FieldName <> '__record__'            -- hide head rows
//	  AND <FLS suppression predicates>
//	ORDER BY ChangedAt DESC
//
// This file owns only the L0-neutral pieces:
//  1. Building a HistoryQueryPlan from a parent object and selected history
//     columns supplied by the contract request.
//  2. Rendering the post-FLS plan to a SQL string + bind args.
//
// FLS gate (§4.1 CH-6): public <Object>History callers must derive
// HistoryQueryPlan.AllowedFields from metadata/FLS before rendering. Unknown,
// deleted, untracked, or FLS-denied fields then fail closed through a positive
// `FieldName IN (...)` predicate. The only non-metadata exceptions are the
// shared audit pseudo-field constants emitted for state transitions. A predicate
// on a denied field's value column surfaces as *FieldDeniedByFLSError (HTTP 400).
//
// This file is not a SOQL parser and owns no Salesforce pseudo-object naming.
package audit

import (
	"context"
	"database/sql"
	"fmt"
	"strings"

	"github.com/nexuscrm/shared/pkg/queryspec"
)

// HistoryRow is one row returned by the audit-history contract.
type HistoryRow struct {
	ID          string
	ParentID    string
	Field       string
	OldValue    *string
	NewValue    *string
	DataType    string // stubbed as "string" in Phase 2A
	CreatedDate string // ISO8601
	CreatedByID string
}

// BuildHistoryQueryPlan converts the neutral history request shape into a
// HistoryQueryPlan ready for RedactFieldHistoryPlan. Projection names use the
// public history contract columns (Field, OldValue, NewValue, CreatedDate,
// CreatedById, Id, parent id, and DataType); unknown names pass through as
// metadata references so the redactor can gate on them.
func BuildHistoryQueryPlan(parent string, selectFields []string) (HistoryQueryPlan, error) {
	parent = strings.TrimSpace(parent)
	if parent == "" {
		return HistoryQueryPlan{}, fmt.Errorf("audit: history parent object is required")
	}
	plan := HistoryQueryPlan{
		ObjectApiName: parent,
		Projections:   make([]FieldRef, 0, len(selectFields)),
	}
	for _, sel := range selectFields {
		ref := projectionRef(sel)
		plan.Projections = append(plan.Projections, ref)
	}
	return plan, nil
}

type historyQueryFilter struct {
	column   string
	operator string
	value    string
	values   []string
}

// applyHistoryQueryFilter lowers the existing neutral query predicate into the
// closed history metadata subset. Only AND-combined field comparisons are
// accepted. OldValue/NewValue predicates would require a field-specific FLS
// dependency and therefore fail closed.
func applyHistoryQueryFilter(plan HistoryQueryPlan, filter queryspec.Predicate) (HistoryQueryPlan, error) {
	plan.filters = nil
	if err := appendHistoryQueryFilters(&plan, filter); err != nil {
		return HistoryQueryPlan{}, err
	}
	return plan, nil
}

func appendHistoryQueryFilters(plan *HistoryQueryPlan, filter queryspec.Predicate) error {
	switch filter.Kind {
	case queryspec.PredicateNone:
		return nil
	case queryspec.PredicateAnd:
		for _, child := range filter.Children {
			if err := appendHistoryQueryFilters(plan, child); err != nil {
				return err
			}
		}
		return nil
	case queryspec.PredicateFieldComparison:
		break
	default:
		return fmt.Errorf("audit: unsupported history filter predicate %q", filter.Kind)
	}

	column, ok := historyFilterColumn(filter.Field)
	if !ok {
		return fmt.Errorf("audit: unsupported history filter column %q", strings.TrimSpace(filter.Field))
	}
	operator, ok := historyFilterOperator(filter.Op)
	if !ok {
		return fmt.Errorf("audit: unsupported history filter operator for %q", strings.TrimSpace(filter.Field))
	}
	lowered := historyQueryFilter{column: column, operator: operator}
	if operator == "IN" || operator == "NOT IN" {
		values, ok := filter.Value.([]string)
		if !ok || len(values) == 0 {
			return fmt.Errorf("audit: history filter operator for %q requires string values", strings.TrimSpace(filter.Field))
		}
		lowered.values = append([]string(nil), values...)
	} else {
		value, ok := filter.Value.(string)
		if !ok {
			return fmt.Errorf("audit: history filter operator for %q requires a string value", strings.TrimSpace(filter.Field))
		}
		lowered.value = value
	}
	plan.filters = append(plan.filters, lowered)
	return nil
}

func historyFilterColumn(column string) (string, bool) {
	switch strings.ToLower(strings.TrimSpace(column)) {
	case "id":
		return "Id", true
	case "field":
		return "FieldName", true
	case "createddate":
		return "ChangedAt", true
	case "createdbyid":
		return "ChangedById", true
	default:
		return "", false
	}
}

func historyFilterOperator(operator string) (string, bool) {
	switch strings.ToUpper(strings.TrimSpace(operator)) {
	case "=", "==":
		// queryv1 ComparisonEqual is lowered to the query engine's canonical
		// equality operator (==). History renders SQL directly, so normalize
		// both accepted engine spellings to SQL equality at this boundary.
		return "=", true
	case "!=", "<>", ">", ">=", "<", "<=", "LIKE", "NOT LIKE", "IN", "NOT IN":
		return strings.ToUpper(strings.TrimSpace(operator)), true
	default:
		return "", false
	}
}

// projectionRef maps a SF history column name to a FieldRef the redactor
// can reason about. Metadata columns (Field, Id, ParentId, DataType,
// CreatedDate, CreatedById) produce FieldRef with an empty FieldName so
// the redactor treats them as metadata and never drops them. Value
// columns (OldValue, NewValue) preserve an empty FieldName here because
// the SF history contract does not name the field in the projection —
// the FieldName column IS what identifies which field each row is for,
// so the redactor's FLS gate is enforced via the FieldName suppression
// predicate rather than the projection.
func projectionRef(sel string) FieldRef {
	switch strings.ToLower(strings.TrimSpace(sel)) {
	case "oldvalue":
		return FieldRef{Column: ColumnOldValue}
	case "newvalue":
		return FieldRef{Column: ColumnNewValue}
	case "field", "fieldname":
		return FieldRef{Column: ColumnFieldName}
	case "createddate":
		return FieldRef{Column: ColumnChangedAt}
	case "createdbyid":
		return FieldRef{Column: ColumnChangedById}
	default:
		// Id / ParentId / DataType and anything else — treat as metadata
		// projection so it survives redaction. The SQL renderer maps the
		// SF name to its AuditLog column.
		return FieldRef{Column: Column(sel)}
	}
}

// ExecuteHistoryPlan renders the post-redaction plan to SQL and executes
// it against _System_AuditLog via the provided *sql.DB. Returns the rows
// as typed HistoryRow values.
//
// The head-row sentinel ("__record__") is always filtered out so callers
// only see field-level diffs. If plan.AllowedFields is non-nil, we inject a
// positive FieldName allow-list so deleted/unknown/untracked fields fail
// closed. If len(plan.DeniedFields) > 0 we also inject the FieldName
// suppression predicates generated by RedactFieldHistoryPlan.
//
// Error messages MUST NOT echo the user-supplied predicate value (§5.1 +
// fls_plan rule) — we only surface the field name, never the filter value.
func ExecuteHistoryPlan(
	ctx context.Context,
	db *sql.DB,
	plan HistoryQueryPlan,
	parentRecordID string,
	limit, offset int,
	sortDesc bool,
	parentVisibilityWhere string,
	parentReadTable string,
) ([]HistoryRow, error) {
	if db == nil {
		return nil, fmt.Errorf("audit: ExecuteHistoryPlan requires a non-nil *sql.DB")
	}
	if plan.ObjectApiName == "" {
		return nil, fmt.Errorf("audit: plan.ObjectApiName is required")
	}

	where, args := buildHistoryWhere(plan, parentRecordID, parentVisibilityWhere, parentReadTable)

	orderDir := "DESC"
	if !sortDesc {
		orderDir = "ASC"
	}

	q := fmt.Sprintf(
		`SELECT Id, RecordId, FieldName, OldValue, NewValue, ChangedAt, ChangedById
		 FROM _System_AuditLog
		 WHERE %s
		 ORDER BY ChangedAt %s`,
		strings.Join(where, " AND "), orderDir,
	)
	if limit > 0 {
		q += fmt.Sprintf(" LIMIT %d", limit)
		if offset > 0 {
			q += fmt.Sprintf(" OFFSET %d", offset)
		}
	}

	rows, err := db.QueryContext(ctx, q, args...)
	if err != nil {
		// Do NOT wrap with args — any user-supplied predicate literal
		// would leak through error logs otherwise.
		return nil, fmt.Errorf("audit: history query: %w", err)
	}
	defer rows.Close()

	out := make([]HistoryRow, 0, 32)
	for rows.Next() {
		var (
			r               HistoryRow
			old, nw         sql.NullString
			changedAt       sql.NullTime
			changedByID     sql.NullString
			recordID, field sql.NullString
			idCol           sql.NullString
		)
		if err := rows.Scan(&idCol, &recordID, &field, &old, &nw, &changedAt, &changedByID); err != nil {
			return nil, fmt.Errorf("audit: history scan: %w", err)
		}
		r.ID = idCol.String
		r.ParentID = recordID.String
		r.Field = field.String
		if old.Valid {
			v := old.String
			r.OldValue = &v
		}
		if nw.Valid {
			v := nw.String
			r.NewValue = &v
		}
		if changedAt.Valid {
			r.CreatedDate = changedAt.Time.UTC().Format("2006-01-02T15:04:05.000Z")
		}
		r.CreatedByID = changedByID.String
		r.DataType = "string" // Phase 2A stub per loop-prompt §5.1
		out = append(out, r)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("audit: history rows: %w", err)
	}
	return out, nil
}

// CountHistoryPlan returns the total rows matching the same post-redaction
// history plan. This keeps the SOQL envelope's totalSize aligned with the full
// result set even when ExecuteHistoryPlan fetches only one page.
func CountHistoryPlan(
	ctx context.Context,
	db *sql.DB,
	plan HistoryQueryPlan,
	parentRecordID string,
	parentVisibilityWhere string,
	parentReadTable string,
) (int, error) {
	if db == nil {
		return 0, fmt.Errorf("audit: CountHistoryPlan requires a non-nil *sql.DB")
	}
	if plan.ObjectApiName == "" {
		return 0, fmt.Errorf("audit: plan.ObjectApiName is required")
	}
	where, args := buildHistoryWhere(plan, parentRecordID, parentVisibilityWhere, parentReadTable)
	q := fmt.Sprintf(
		`SELECT COUNT(*)
		 FROM _System_AuditLog
		 WHERE %s`,
		strings.Join(where, " AND "),
	)
	var count int
	if err := db.QueryRowContext(ctx, q, args...).Scan(&count); err != nil {
		return 0, fmt.Errorf("audit: history count: %w", err)
	}
	return count, nil
}

func buildHistoryWhere(plan HistoryQueryPlan, parentRecordID string, parentVisibilityWhere string, parentReadTable string) ([]string, []interface{}) {
	where := []string{
		"ObjectApiName = ?",
		"FieldName <> ?",
	}
	args := []interface{}{plan.ObjectApiName, headFieldNameSentinel}
	if parentRecordID != "" {
		where = append(where, "RecordId = ?")
		args = append(args, parentRecordID)
	}
	for _, filter := range plan.filters {
		if filter.operator == "IN" || filter.operator == "NOT IN" {
			placeholders := make([]string, len(filter.values))
			for i, value := range filter.values {
				placeholders[i] = "?"
				args = append(args, value)
			}
			where = append(where, fmt.Sprintf("`%s` %s (%s)", filter.column, filter.operator, strings.Join(placeholders, ", ")))
			continue
		}
		where = append(where, fmt.Sprintf("`%s` %s ?", filter.column, filter.operator))
		args = append(args, filter.value)
	}
	if plan.AllowedFields != nil {
		if len(plan.AllowedFields) == 0 {
			where = append(where, "1 = 0")
		} else {
			placeholders := make([]string, len(plan.AllowedFields))
			for i, fieldName := range plan.AllowedFields {
				placeholders[i] = "?"
				args = append(args, fieldName)
			}
			where = append(where, fmt.Sprintf("FieldName IN (%s)", strings.Join(placeholders, ", ")))
		}
	}
	// During the bounded mixed-version window, the producer-stamped boolean is
	// authoritative whenever present. Only unstamped legacy rows may fall back
	// to the strict sequence/interval predicate. This covers both rolling
	// directions: an old producer with a new reader, and a new producer whose
	// row is observed by an old reader while sequence reservation remains on.
	// A stamped false must never be widened by a legacy eligible interval.
	// Pseudo rows retain their existing explicit semantics.
	pseudoFields := []string{
		FieldNameCreated,
		FieldNameDeleted,
		FieldNameUndeleted,
		FieldNameLocked,
		FieldNameUnlocked,
		FieldNameOwner,
	}
	pseudoLiterals := make([]string, len(pseudoFields))
	for i, fieldName := range pseudoFields {
		pseudoLiterals[i] = "'" + strings.ReplaceAll(fieldName, "'", "''") + "'"
	}
	where = append(where, fmt.Sprintf(`(
		FieldName IN (%s)
		OR (
			FieldHistoryEligible = TRUE
			OR (
				FieldHistoryEligible IS NULL
				AND HistorySequence IS NOT NULL
				AND EXISTS (
					SELECT 1
					FROM _System_Field history_field
					JOIN _System_Object history_object ON history_object.Id = history_field.ObjectId
					JOIN _System_FieldHistoryTrackingInterval history_interval ON history_interval.FieldId = history_field.Id
					WHERE history_object.ApiName = _System_AuditLog.ObjectApiName
					  AND history_field.ApiName = _System_AuditLog.FieldName
					  AND history_object.IsDeleted = 0
					  AND history_field.IsDeleted = 0
					  AND _System_AuditLog.HistorySequence > history_interval.StartedSequence
					  AND (history_interval.EndedSequence IS NULL OR _System_AuditLog.HistorySequence < history_interval.EndedSequence)
				)
			)
		)
	)`, strings.Join(pseudoLiterals, ", ")))

	// FLS suppression: for every denied field, add `FieldName <> ?` so no
	// rows for that field surface.
	for _, p := range plan.Predicates {
		if p.Kind != PredicateSuppressField {
			continue
		}
		where = append(where, "FieldName <> ?")
		args = append(args, p.FieldName)
	}

	// Parent visibility constraint: subquery on the parent object to restrict
	// _System_AuditLog.RecordId to records normal SOQL can actually see. This
	// combines RLS and the /query deleted-row exclusion when present. Use the
	// same read target as normal SOQL; if formula views are active, parentReadTable
	// is v_<object>, otherwise it is the base object table.
	if parentVisibilityWhere != "" {
		readTable := strings.TrimSpace(parentReadTable)
		if readTable == "" {
			readTable = plan.ObjectApiName
		}
		where = append(where, fmt.Sprintf(
			"RecordId IN (SELECT Id FROM `%s` WHERE %s)",
			strings.ReplaceAll(readTable, "`", "``"),
			parentVisibilityWhere,
		))
	}
	return where, args
}