package connector_inbound_routing

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

// loadConnectorInboundRouteFlowName looks up the FlowDeveloperName for the
// given (packInstallID, eventKind) by returning the latest (highest Version)
// row that is both non-deleted AND currently declared in the manifest
// (IsCurrent=1).
//
// Design note (option 3 / codex-R6 fix): the query is now scoped by
// InstalledPackId (not Namespace) so that the caller — the Dispatcher — can
// resolve the active pack incarnation ONCE and pass that same packInstallID
// into both this lookup and Enqueue, binding the route snapshot to the same
// pack incarnation used for enqueue.  Scoping by Namespace instead would allow
// a route resolved for pack-A to be enqueued against pack-B when a rebirth
// occurs between the two calls (the impossible-tuple race that codex BLOCKed
// on in rounds 5 and 6).
//
// IsCurrent=1 filter (B19 — codex R16 BLOCK): the sweeper preserves dropped-
// eventKind rows (IsDeleted=0) while backlog queue rows drain, so filtering by
// IsDeleted=0 alone would return preserved-for-backlog rows to new traffic.
// IsCurrent=1 is only set by Apply (manifest-declared rows); the sweeper clears
// it to 0 when preserving for backlog, making this filter the authoritative
// "live route" signal.
//
// Returns (flowDeveloperName, version, true, nil) on success.
// Returns ("", 0, false, nil) when no matching live row is found.
// Returns ("", 0, false, err) on DB errors.
func loadConnectorInboundRouteFlowName(ctx context.Context, db *sql.DB, packInstallID, eventKind string) (string, int64, bool, error) {
	packInstallID = strings.TrimSpace(packInstallID)
	eventKind = strings.ToLower(strings.TrimSpace(eventKind))
	if packInstallID == "" || eventKind == "" {
		return "", 0, false, nil
	}

	var flowDeveloperName string
	var version int64
	err := db.QueryRowContext(ctx, `
SELECT FlowDeveloperName, Version
  FROM _System_ConnectorInboundRoute
 WHERE InstalledPackId = ?
   AND EventKind = ?
   AND COALESCE(IsDeleted, 0) = 0
   AND IsCurrent = 1
 ORDER BY Version DESC
 LIMIT 1`,
		packInstallID, eventKind,
	).Scan(&flowDeveloperName, &version)
	if err == sql.ErrNoRows {
		return "", 0, false, nil
	}
	if err != nil {
		return "", 0, false, fmt.Errorf("query connector inbound route (packInstallID=%s, eventKind=%s): %w", packInstallID, eventKind, err)
	}
	return flowDeveloperName, version, true, nil
}

// loadConnectorInboundRouteByVersion looks up the execution spec (and IsDeleted
// flag) for the exact (installedPackId, eventKind, version) triple.
// Used by the Worker when processing a queue row that carries a snapshotted
// RouteVersion.
//
// Returns (spec, isDeleted=false, true, nil) for a live row.
// Returns (spec, isDeleted=true,  true, nil) for a soft-deleted row.
// Returns (zero, false, false, nil) when no row with that exact version exists.
// Returns (zero, false, false, err) on DB errors.
func loadConnectorInboundRouteByVersion(ctx context.Context, db *sql.DB, installedPackID, eventKind string, version int64) (RouteExecutionSpec, bool, bool, error) {
	installedPackID = strings.TrimSpace(installedPackID)
	eventKind = strings.ToLower(strings.TrimSpace(eventKind))
	if installedPackID == "" || eventKind == "" || version < 1 {
		return RouteExecutionSpec{}, false, false, nil
	}

	var spec RouteExecutionSpec
	var isDeletedInt int64
	var filterJSON string
	err := db.QueryRowContext(ctx, `
SELECT FlowDeveloperName,
       COALESCE(InvocationMode, ''), COALESCE(BatchInputName, ''), COALESCE(BatchResultOutputName, ''), COALESCE(MaxBatchSize, 0),
       COALESCE(QueueClass, ''), COALESCE(FlowExecutionFilter, ''),
       COALESCE(IsDeleted, 0)
  FROM _System_ConnectorInboundRoute
 WHERE InstalledPackId = ?
   AND EventKind = ?
   AND Version = ?
 LIMIT 1`,
		installedPackID, eventKind, version,
	).Scan(&spec.FlowDeveloperName, &spec.InvocationMode, &spec.BatchInputName, &spec.BatchResultOutputName, &spec.MaxBatchSize, &spec.QueueClass, &filterJSON, &isDeletedInt)
	if err == sql.ErrNoRows {
		return RouteExecutionSpec{}, false, false, nil
	}
	if err != nil {
		return RouteExecutionSpec{}, false, false, fmt.Errorf("query connector inbound route by version (pack=%s, eventKind=%s, version=%d): %w", installedPackID, eventKind, version, err)
	}
	filter, err := ParsePayloadValueFilterJSON(filterJSON)
	if err != nil {
		return RouteExecutionSpec{}, false, false, fmt.Errorf("parse connector inbound route flowExecutionFilter (pack=%s, eventKind=%s, version=%d): %w", installedPackID, eventKind, version, err)
	}
	spec.FlowExecutionFilter = filter
	return spec, isDeletedInt != 0, true, nil
}