package binding

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

	"github.com/tidbcloud/AgentTeam/internal/coredb"
)

type rowScanner interface {
	Scan(...any) error
}

type scannedBinding struct {
	value             Binding
	latestID          sql.NullString
	activeID          sql.NullString
	activeSourceID    sql.NullString
	archivedMemberID  sql.NullString
	archivedActorType sql.NullString
	archivedActorID   sql.NullString
	archivedAt        sql.NullTime
}

func bindingSelectSQL() string {
	return bindingSelectProjectionSQL(false)
}

func bindingListSelectSQL() string {
	return bindingSelectProjectionSQL(true)
}

func bindingSelectProjectionSQL(includePlanDisplayName bool) string {
	planColumn, planJoin := "", ""
	if includePlanDisplayName {
		planColumn = ", p.display_name"
		planJoin = `
JOIN workflow_plans p
  ON p.workspace_id = b.workspace_id
 AND p.id = b.plan_id`
	}
	return `
SELECT b.id, b.workspace_id, b.plan_id, b.binding_key,
       b.display_name, b.description_md, b.state, b.revision,
       b.latest_revision_id, latest.revision_number,
       b.active_revision_id, active.revision_number, b.active_source_id,
       b.owner_user_id, b.owner_member_id,
       b.create_idempotency_key, b.create_request_digest,
       b.created_by_member_id, b.created_by_actor_type, b.created_by_actor_id, b.created_at,
       b.updated_by_member_id, b.updated_by_actor_type, b.updated_by_actor_id, b.updated_at,
       b.archived_by_member_id, b.archived_by_actor_type, b.archived_by_actor_id, b.archived_at` + planColumn + `
FROM workflow_bindings b
LEFT JOIN workflow_binding_revisions latest
  ON latest.workspace_id = b.workspace_id
 AND latest.binding_id = b.id
 AND latest.id = b.latest_revision_id
LEFT JOIN workflow_binding_revisions active
  ON active.workspace_id = b.workspace_id
 AND active.binding_id = b.id
 AND active.id = b.active_revision_id` + planJoin
}

func scanBinding(scanner rowScanner) (Binding, error) {
	return scanBindingProjection(scanner, false)
}

func scanListedBinding(scanner rowScanner) (Binding, error) {
	return scanBindingProjection(scanner, true)
}

func scanBindingProjection(scanner rowScanner, includePlanDisplayName bool) (Binding, error) {
	var scanned scannedBinding
	var latestRevision sql.NullInt64
	var activeRevision sql.NullInt64
	destinations := []any{
		&scanned.value.ID, &scanned.value.WorkspaceID, &scanned.value.PlanID, &scanned.value.Key,
		&scanned.value.DisplayName, &scanned.value.DescriptionMD, &scanned.value.State, &scanned.value.Revision,
		&scanned.latestID, &latestRevision,
		&scanned.activeID, &activeRevision, &scanned.activeSourceID,
		&scanned.value.Owner.UserID, &scanned.value.Owner.MemberID,
		&scanned.value.CreateIdempotencyKey, &scanned.value.CreateRequestDigest,
		&scanned.value.CreatedBy.MemberID, &scanned.value.CreatedBy.Type, &scanned.value.CreatedBy.ID, &scanned.value.CreatedAt,
		&scanned.value.UpdatedBy.MemberID, &scanned.value.UpdatedBy.Type, &scanned.value.UpdatedBy.ID, &scanned.value.UpdatedAt,
		&scanned.archivedMemberID, &scanned.archivedActorType, &scanned.archivedActorID, &scanned.archivedAt,
	}
	if includePlanDisplayName {
		destinations = append(destinations, &scanned.value.PlanDisplayName)
	}
	if err := scanner.Scan(destinations...); err != nil {
		return Binding{}, err
	}
	return scanned.finish(latestRevision, activeRevision)
}

func bindingAggregateSelectSQL() string {
	return `
SELECT b.id, b.workspace_id, b.plan_id, b.binding_key,
       b.display_name, b.description_md, b.state, b.revision,
       b.latest_revision_id,
       b.active_revision_id, b.active_source_id,
       b.owner_user_id, b.owner_member_id,
       b.create_idempotency_key, b.create_request_digest,
       b.created_by_member_id, b.created_by_actor_type, b.created_by_actor_id, b.created_at,
       b.updated_by_member_id, b.updated_by_actor_type, b.updated_by_actor_id, b.updated_at,
       b.archived_by_member_id, b.archived_by_actor_type, b.archived_by_actor_id, b.archived_at
FROM workflow_bindings b`
}

func scanBindingAggregate(scanner rowScanner) (scannedBinding, error) {
	var scanned scannedBinding
	if err := scanner.Scan(
		&scanned.value.ID, &scanned.value.WorkspaceID, &scanned.value.PlanID, &scanned.value.Key,
		&scanned.value.DisplayName, &scanned.value.DescriptionMD, &scanned.value.State, &scanned.value.Revision,
		&scanned.latestID,
		&scanned.activeID, &scanned.activeSourceID,
		&scanned.value.Owner.UserID, &scanned.value.Owner.MemberID,
		&scanned.value.CreateIdempotencyKey, &scanned.value.CreateRequestDigest,
		&scanned.value.CreatedBy.MemberID, &scanned.value.CreatedBy.Type, &scanned.value.CreatedBy.ID, &scanned.value.CreatedAt,
		&scanned.value.UpdatedBy.MemberID, &scanned.value.UpdatedBy.Type, &scanned.value.UpdatedBy.ID, &scanned.value.UpdatedAt,
		&scanned.archivedMemberID, &scanned.archivedActorType, &scanned.archivedActorID, &scanned.archivedAt,
	); err != nil {
		return scannedBinding{}, err
	}
	return scanned, nil
}

func (scanned scannedBinding) finish(latestRevision sql.NullInt64, activeRevision sql.NullInt64) (Binding, error) {
	value := scanned.value
	if !scanned.latestID.Valid || !latestRevision.Valid {
		return Binding{}, fmt.Errorf("workflow binding %s has no latest revision", value.ID)
	}
	value.LatestRevision = RevisionRef{ID: scanned.latestID.String, Revision: latestRevision.Int64}
	if scanned.activeID.Valid || activeRevision.Valid || scanned.activeSourceID.Valid {
		if !scanned.activeID.Valid || !activeRevision.Valid || !scanned.activeSourceID.Valid {
			return Binding{}, fmt.Errorf("workflow binding %s has inconsistent active pointers", value.ID)
		}
		value.ActiveRevision = &RevisionRef{ID: scanned.activeID.String, Revision: activeRevision.Int64}
		value.ActiveSourceID = scanned.activeSourceID.String
	}
	value.CreatedAt = value.CreatedAt.UTC()
	value.UpdatedAt = value.UpdatedAt.UTC()
	value.ArchivedAt = ptrTime(scanned.archivedAt)
	if scanned.archivedMemberID.Valid || scanned.archivedActorType.Valid || scanned.archivedActorID.Valid {
		if !scanned.archivedMemberID.Valid || !scanned.archivedActorType.Valid || !scanned.archivedActorID.Valid {
			return Binding{}, fmt.Errorf("workflow binding %s has inconsistent archive provenance", value.ID)
		}
		value.ArchivedBy = &Actor{
			MemberID: scanned.archivedMemberID.String,
			Type:     scanned.archivedActorType.String,
			ID:       scanned.archivedActorID.String,
		}
	}
	return value, nil
}

func loadBinding(ctx context.Context, queryer coredb.QueryRower, workspaceID string, bindingID string, forUpdate bool) (Binding, error) {
	if forUpdate {
		return loadBindingForUpdate(ctx, queryer, workspaceID, bindingID)
	}
	query := bindingSelectSQL() + "\nWHERE b.workspace_id = $1 AND b.id = $2\n"
	value, err := scanBinding(queryer.QueryRow(ctx, query, workspaceID, bindingID))
	if err != nil {
		return Binding{}, mapWriteError(err)
	}
	return value, nil
}

// loadBindingForUpdate locks only the aggregate row first. The revision rows
// referenced by its now-stable pointers are then point-locked in that same
// Binding -> Revision order. Besides avoiding a multi-table JOIN FOR UPDATE,
// the locking reads are required under repeatable-read so a transaction that
// waited for the Binding row observes the winner's newly-created children.
func loadBindingForUpdate(ctx context.Context, queryer coredb.QueryRower, workspaceID string, bindingID string) (Binding, error) {
	query := bindingAggregateSelectSQL() + "\nWHERE b.workspace_id = $1 AND b.id = $2\nFOR UPDATE\n"
	scanned, err := scanBindingAggregate(queryer.QueryRow(ctx, query, workspaceID, bindingID))
	if err != nil {
		return Binding{}, mapWriteError(err)
	}

	latestRevision, err := lockBindingRevisionNumber(ctx, queryer, workspaceID, bindingID, scanned.latestID)
	if err != nil {
		return Binding{}, err
	}
	activeRevision, err := lockBindingRevisionNumber(ctx, queryer, workspaceID, bindingID, scanned.activeID)
	if err != nil {
		return Binding{}, err
	}
	return scanned.finish(latestRevision, activeRevision)
}

func lockBindingRevisionNumber(ctx context.Context, queryer coredb.QueryRower, workspaceID string, bindingID string, revisionID sql.NullString) (sql.NullInt64, error) {
	if !revisionID.Valid {
		return sql.NullInt64{}, nil
	}
	var revision int64
	err := queryer.QueryRow(ctx, `
SELECT revision_number
FROM workflow_binding_revisions
WHERE workspace_id = $1 AND binding_id = $2 AND id = $3
FOR UPDATE
`, workspaceID, bindingID, revisionID.String).Scan(&revision)
	if coredb.IsNotFound(err) {
		return sql.NullInt64{}, nil
	}
	if err != nil {
		return sql.NullInt64{}, fmt.Errorf("read workflow binding revision pointer: %w", mapWriteError(err))
	}
	return sql.NullInt64{Int64: revision, Valid: true}, nil
}

func revisionSelectSQL() string {
	return `
	SELECT id, workspace_id, binding_id, plan_id, revision_number,
	       plan_version_id, source_kind, config_json, digest,
	       create_idempotency_scope, create_idempotency_key, create_request_digest,
       created_by_member_id, created_by_actor_type, created_by_actor_id, created_at
FROM workflow_binding_revisions`
}

func scanRevision(scanner rowScanner) (Revision, error) {
	var value Revision
	if err := scanner.Scan(
		&value.ID, &value.WorkspaceID, &value.BindingID, &value.PlanID, &value.Revision,
		&value.PlanVersionID, &value.SourceKind, &value.ConfigJSON, &value.Digest,
		&value.CreateIdempotencyScope, &value.CreateIdempotencyKey, &value.CreateRequestDigest,
		&value.CreatedBy.MemberID, &value.CreatedBy.Type, &value.CreatedBy.ID, &value.CreatedAt,
	); err != nil {
		return Revision{}, err
	}
	value.CreatedAt = value.CreatedAt.UTC()
	return value, nil
}

func loadRevisionByNumber(ctx context.Context, queryer coredb.QueryRower, workspaceID string, bindingID string, revision int64, forUpdate bool) (Revision, error) {
	query := revisionSelectSQL() + "\nWHERE workspace_id = $1 AND binding_id = $2 AND revision_number = $3\n"
	if forUpdate {
		query += "FOR UPDATE\n"
	}
	value, err := scanRevision(queryer.QueryRow(ctx, query, workspaceID, bindingID, revision))
	if err != nil {
		return Revision{}, mapWriteError(err)
	}
	return value, nil
}

func loadRevisionByID(ctx context.Context, queryer coredb.QueryRower, workspaceID string, bindingID string, revisionID string) (Revision, error) {
	value, err := scanRevision(queryer.QueryRow(ctx, revisionSelectSQL()+`
WHERE workspace_id = $1 AND binding_id = $2 AND id = $3
`, workspaceID, bindingID, revisionID))
	if err != nil {
		return Revision{}, mapWriteError(err)
	}
	return value, nil
}

func sourceSelectSQL() string {
	return `
SELECT id, workspace_id, binding_id, binding_revision_id, activation_number,
       source_kind, status, revision,
	       next_occurrence_at,
	       activation_idempotency_scope, activation_idempotency_key, activation_request_digest,
       activated_by_member_id, activated_by_actor_type, activated_by_actor_id, activated_at,
       closed_at, closed_reason, created_at, updated_at
FROM workflow_binding_sources`
}

func scanSource(scanner rowScanner) (Source, error) {
	var value Source
	var nextOccurrence sql.NullTime
	var closedAt sql.NullTime
	if err := scanner.Scan(
		&value.ID, &value.WorkspaceID, &value.BindingID, &value.BindingRevisionID, &value.Activation,
		&value.SourceKind, &value.Status, &value.Revision,
		&nextOccurrence,
		&value.ActivationIdempotencyScope, &value.ActivationIdempotencyKey, &value.ActivationRequestDigest,
		&value.ActivatedBy.MemberID, &value.ActivatedBy.Type, &value.ActivatedBy.ID, &value.ActivatedAt,
		&closedAt, &value.ClosedReason, &value.CreatedAt, &value.UpdatedAt,
	); err != nil {
		return Source{}, err
	}
	value.NextOccurrenceAt = ptrTime(nextOccurrence)
	value.ClosedAt = ptrTime(closedAt)
	value.ActivatedAt = value.ActivatedAt.UTC()
	value.CreatedAt = value.CreatedAt.UTC()
	value.UpdatedAt = value.UpdatedAt.UTC()
	return value, nil
}

func loadSource(ctx context.Context, queryer coredb.QueryRower, workspaceID string, sourceID string, forUpdate bool) (Source, error) {
	query := sourceSelectSQL() + "\nWHERE workspace_id = $1 AND id = $2\n"
	if forUpdate {
		query += "FOR UPDATE\n"
	}
	value, err := scanSource(queryer.QueryRow(ctx, query, workspaceID, sourceID))
	if err != nil {
		return Source{}, mapWriteError(err)
	}
	return value, nil
}