package serviceaccount

import (
	"context"
	"crypto/sha256"
	"database/sql"
	"encoding/hex"
	"errors"
	"fmt"
	"strings"
	"time"

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

func normalizeAuthorizationPath(value string) (string, error) {
	switch value = trim(value); value {
	case AuthorizationPathDirectCreate, AuthorizationPathApprovalRequired:
		return value, nil
	default:
		return "", badRequest("authorizationPath must be direct_create or approval_required")
	}
}

// InsertCreateRequest inserts an immutable submission or returns the canonical
// existing request for the same requester-scoped client key. A different digest
// is an idempotency conflict.
func (store *TxStore) InsertCreateRequest(ctx context.Context, params CreateRequestInsert) (CreateRequest, error) {
	execer, err := txExecer(store)
	if err != nil {
		return CreateRequest{}, err
	}
	params.ID = trim(params.ID)
	params.WorkspaceID = trim(params.WorkspaceID)
	params.RequestedByMemberID = trim(params.RequestedByMemberID)
	params.ClientRequestID = trim(params.ClientRequestID)
	params.PayloadDigest = strings.ToLower(trim(params.PayloadDigest))
	if params.ID == "" || params.WorkspaceID == "" || params.RequestedByMemberID == "" || params.ClientRequestID == "" || len(params.PayloadSnapshot) == 0 || !isSHA256Digest(params.PayloadDigest) {
		return CreateRequest{}, badRequest("create request identity, payload, and sha256 digest are required")
	}
	if !model.IsValidJSON(params.PayloadSnapshot) {
		return CreateRequest{}, badRequest("create request payload must be valid JSON")
	}
	payloadDigest := sha256.Sum256(params.PayloadSnapshot)
	if hex.EncodeToString(payloadDigest[:]) != params.PayloadDigest {
		return CreateRequest{}, badRequest("create request payload digest does not match its JSON snapshot")
	}
	var requesterMatches bool
	if err := execer.QueryRow(ctx, `
SELECT EXISTS (
    SELECT 1
    FROM members
    WHERE id = $1
      AND workspace_id = $2
      AND type = 'human'
      AND status = 'active'
)
`, params.RequestedByMemberID, params.WorkspaceID).Scan(&requesterMatches); err != nil {
		return CreateRequest{}, fmt.Errorf("validate service account create requester: %w", err)
	}
	if !requesterMatches {
		return CreateRequest{}, conflict("service account create requester does not belong to the workspace")
	}
	path, err := normalizeAuthorizationPath(params.AuthorizationPath)
	if err != nil {
		return CreateRequest{}, err
	}
	if trim(params.BusinessState) != "" {
		return CreateRequest{}, badRequest("create request businessState is assigned by the service account domain")
	}
	state := RequestStateAwaitingAuthorization
	if path == AuthorizationPathDirectCreate {
		state = RequestStateAuthorized
	}
	now := utcOrNow(params.Now)
	if _, err := execer.Exec(ctx, `
INSERT INTO service_account_create_requests (
    id, workspace_id, requested_by_member_id, request_version,
    client_request_id, payload_snapshot, payload_digest, authorization_path,
    business_state, approval_instance_id, consumed_decision_version,
    cancel_client_mutation_id, cancel_payload_digest,
    cancel_approval_receipt_ref, cancel_result_version,
    created_service_account_id, version, created_at, updated_at,
    cancelled_at, fulfilled_at
) VALUES (
    $1, $2, $3, 1,
    $4, $5, $6, $7,
    $8, NULL, 0,
    NULL, '', '', 0,
    NULL, 1, $9, $9,
    NULL, NULL
)
ON DUPLICATE KEY UPDATE updated_at = updated_at
`, params.ID, params.WorkspaceID, params.RequestedByMemberID, params.ClientRequestID,
		params.PayloadSnapshot, params.PayloadDigest, path, state, now); err != nil {
		return CreateRequest{}, fmt.Errorf("insert service account create request: %w", err)
	}
	request, err := loadCreateRequestByClientKey(ctx, execer, params.WorkspaceID, params.RequestedByMemberID, params.ClientRequestID, "")
	if err != nil {
		if errors.Is(err, ErrNotFound) {
			return CreateRequest{}, conflict("service account create request id is already assigned to a different request")
		}
		return CreateRequest{}, err
	}
	// JSON columns may normalize the stored representation, so the canonical
	// payload digest is the idempotency equality contract.
	if request.PayloadDigest != params.PayloadDigest {
		return CreateRequest{}, conflict("service account create request idempotency key was reused with a different payload")
	}
	if request.AuthorizationPath != path {
		return CreateRequest{}, conflict("service account create request idempotency key was reused with a different authorization path")
	}
	return request, nil
}

func isSHA256Digest(value string) bool {
	if len(value) != 64 {
		return false
	}
	decoded, err := hex.DecodeString(value)
	return err == nil && len(decoded) == sha256.Size
}

func (store *Store) LoadCreateRequest(ctx context.Context, workspaceID string, requestID string) (CreateRequest, error) {
	q, err := queryer(store)
	if err != nil {
		return CreateRequest{}, err
	}
	return loadCreateRequest(ctx, q, workspaceID, requestID, "")
}

func (store *TxStore) LoadCreateRequestForUpdate(ctx context.Context, workspaceID string, requestID string) (CreateRequest, error) {
	q, err := txExecer(store)
	if err != nil {
		return CreateRequest{}, err
	}
	return loadCreateRequest(ctx, q, workspaceID, requestID, "FOR UPDATE")
}

// LoadCreateRequestByClientKeyForUpdate resolves the immutable request behind
// a requester-scoped idempotency key. Callers use this before reevaluating
// mutable authorization policy so a replay follows the originally committed
// path even if workspace roles have since changed.
func (store *TxStore) LoadCreateRequestByClientKeyForUpdate(ctx context.Context, workspaceID string, requesterMemberID string, clientRequestID string) (CreateRequest, error) {
	q, err := txExecer(store)
	if err != nil {
		return CreateRequest{}, err
	}
	return loadCreateRequestByClientKey(ctx, q, workspaceID, requesterMemberID, clientRequestID, "FOR UPDATE")
}

// LoadCreateRequestByCreatedAccountForUpdate resolves the unique provisioning
// request attached to a materialized workspace account.
func (store *TxStore) LoadCreateRequestByCreatedAccountForUpdate(ctx context.Context, workspaceID string, accountID string) (CreateRequest, error) {
	q, err := txExecer(store)
	if err != nil {
		return CreateRequest{}, err
	}
	request, err := scanCreateRequest(q.QueryRow(ctx, createRequestSelectSQL()+`
WHERE workspace_id = $1 AND created_service_account_id = $2
FOR UPDATE
`, trim(workspaceID), trim(accountID)))
	if err != nil {
		if coredb.IsNotFound(err) {
			return CreateRequest{}, notFound("service account create request not found")
		}
		return CreateRequest{}, fmt.Errorf("load service account create request by created account: %w", err)
	}
	return request, nil
}

func loadCreateRequest(ctx context.Context, q coredb.QueryRower, workspaceID string, requestID string, suffix string) (CreateRequest, error) {
	request, err := scanCreateRequest(q.QueryRow(ctx, createRequestSelectSQL()+` WHERE workspace_id = $1 AND id = $2 `+suffix, trim(workspaceID), trim(requestID)))
	if err != nil {
		if coredb.IsNotFound(err) {
			return CreateRequest{}, notFound("service account create request not found")
		}
		return CreateRequest{}, fmt.Errorf("load service account create request: %w", err)
	}
	return request, nil
}

func loadCreateRequestByClientKey(ctx context.Context, q coredb.QueryRower, workspaceID string, requesterMemberID string, clientRequestID string, suffix string) (CreateRequest, error) {
	request, err := scanCreateRequest(q.QueryRow(ctx, createRequestSelectSQL()+`
WHERE workspace_id = $1 AND requested_by_member_id = $2 AND client_request_id = $3 `+suffix,
		trim(workspaceID), trim(requesterMemberID), trim(clientRequestID)))
	if err != nil {
		if coredb.IsNotFound(err) {
			return CreateRequest{}, notFound("service account create request not found")
		}
		return CreateRequest{}, fmt.Errorf("load service account create request by client key: %w", err)
	}
	return request, nil
}

func (store *Store) ListCreateRequests(ctx context.Context, workspaceID string, requesterMemberID string, state string, limit int) ([]CreateRequest, error) {
	q, err := queryer(store)
	if err != nil {
		return nil, err
	}
	if limit <= 0 || limit > 100 {
		return nil, badRequest("limit must be between 1 and 100")
	}
	query := createRequestSelectSQL() + ` WHERE workspace_id = $1`
	args := []any{trim(workspaceID)}
	if requesterMemberID = trim(requesterMemberID); requesterMemberID != "" {
		args = append(args, requesterMemberID)
		query += fmt.Sprintf(" AND requested_by_member_id = $%d", len(args))
	}
	if state = trim(state); state != "" {
		if _, err := normalizeRequestState(state); err != nil {
			return nil, err
		}
		args = append(args, state)
		query += fmt.Sprintf(" AND business_state = $%d", len(args))
	}
	args = append(args, limit)
	query += fmt.Sprintf(" ORDER BY created_at DESC, id DESC LIMIT $%d", len(args))
	rows, err := q.Query(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("list service account create requests: %w", err)
	}
	defer rows.Close()
	requests := make([]CreateRequest, 0)
	for rows.Next() {
		request, err := scanCreateRequest(rows)
		if err != nil {
			return nil, fmt.Errorf("scan service account create request: %w", err)
		}
		requests = append(requests, request)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate service account create requests: %w", err)
	}
	return requests, nil
}

// TransitionCreateRequest advances the authoritative business state with an
// optimistic version fence and optional approval/account refs.
func (store *TxStore) TransitionCreateRequest(ctx context.Context, workspaceID string, requestID string, expectedVersion int64, nextState string, approvalInstanceID string, decisionVersion int64, createdAccountID string, now time.Time) (CreateRequest, error) {
	execer, err := txExecer(store)
	if err != nil {
		return CreateRequest{}, err
	}
	nextState, err = normalizeRequestState(nextState)
	if err != nil {
		return CreateRequest{}, err
	}
	workspaceID = trim(workspaceID)
	requestID = trim(requestID)
	approvalInstanceID = trim(approvalInstanceID)
	createdAccountID = trim(createdAccountID)
	if workspaceID == "" || requestID == "" || expectedVersion <= 0 {
		return CreateRequest{}, badRequest("workspaceId, requestId, and a positive expectedVersion are required")
	}
	if len(approvalInstanceID) > 64 || len(createdAccountID) > 64 {
		return CreateRequest{}, badRequest("approval and created account refs must be at most 64 bytes")
	}
	if decisionVersion < 0 {
		return CreateRequest{}, badRequest("decisionVersion cannot be negative")
	}
	current, err := loadCreateRequest(ctx, execer, workspaceID, requestID, "FOR UPDATE")
	if err != nil {
		return CreateRequest{}, err
	}
	if current.Version != expectedVersion {
		return CreateRequest{}, conflict("service account create request version changed")
	}
	if current.BusinessState == RequestStateDeclined || current.BusinessState == RequestStateCancelled || current.BusinessState == RequestStateFulfilled {
		return CreateRequest{}, conflict("terminal service account create requests cannot transition")
	}
	effectiveApprovalID, effectiveDecisionVersion, err := validateApprovalTransition(current, nextState, approvalInstanceID, decisionVersion)
	if err != nil {
		return CreateRequest{}, err
	}
	effectiveAccountID, err := validateCreatedAccountTransition(ctx, execer, current, nextState, createdAccountID)
	if err != nil {
		return CreateRequest{}, err
	}
	if err := validateCreateRequestTransition(current, nextState, effectiveAccountID, effectiveApprovalID); err != nil {
		return CreateRequest{}, err
	}
	if err := validateApprovalInstanceBinding(ctx, execer, current, effectiveApprovalID); err != nil {
		return CreateRequest{}, err
	}
	now = utcOrNow(now)
	var cancelledAt any
	var fulfilledAt any
	if nextState == RequestStateCancelled {
		cancelledAt = now
	}
	if nextState == RequestStateFulfilled {
		fulfilledAt = now
	}
	result, err := execer.Exec(ctx, `
UPDATE service_account_create_requests
SET business_state = $4,
    approval_instance_id = NULLIF($5, ''),
    consumed_decision_version = $6,
    created_service_account_id = NULLIF($7, ''),
    version = version + 1,
    updated_at = $8,
    cancelled_at = COALESCE($9, cancelled_at),
    fulfilled_at = COALESCE($10, fulfilled_at)
WHERE workspace_id = $1 AND id = $2 AND version = $3
	`, workspaceID, requestID, expectedVersion, nextState, effectiveApprovalID, effectiveDecisionVersion, effectiveAccountID, now, cancelledAt, fulfilledAt)
	if err != nil {
		if constraint, ok := coredb.UniqueViolationConstraint(err); ok {
			switch constraint {
			case "service_account_create_requests_approval_key":
				return CreateRequest{}, conflict("Approval instance is already assigned to another service account create request")
			case "service_account_create_requests_account_key":
				return CreateRequest{}, conflict("created service account is already assigned to another create request")
			}
		}
		return CreateRequest{}, fmt.Errorf("transition service account create request: %w", err)
	}
	if result.RowsAffected() == 0 {
		return CreateRequest{}, conflict("service account create request version changed")
	}
	return loadCreateRequest(ctx, execer, workspaceID, requestID, "")
}

func validateApprovalInstanceBinding(ctx context.Context, queryer coredb.QueryRower, current CreateRequest, effectiveApprovalID string) error {
	effectiveApprovalID = trim(effectiveApprovalID)
	if effectiveApprovalID == "" || effectiveApprovalID == trim(current.ApprovalInstanceID) {
		return nil
	}
	var existingRequestID string
	err := queryer.QueryRow(ctx, `
SELECT id
FROM service_account_create_requests
WHERE approval_instance_id = $1
  AND id <> $2
LIMIT 1
FOR UPDATE
`, effectiveApprovalID, current.ID).Scan(&existingRequestID)
	if err != nil {
		if coredb.IsNotFound(err) {
			return nil
		}
		return fmt.Errorf("validate service account create request Approval binding: %w", err)
	}
	return conflict("Approval instance is already assigned to another service account create request")
}

func validateApprovalTransition(current CreateRequest, nextState string, suppliedApprovalID string, suppliedDecisionVersion int64) (string, int64, error) {
	currentApprovalID := trim(current.ApprovalInstanceID)
	if current.AuthorizationPath == AuthorizationPathDirectCreate {
		if currentApprovalID != "" || suppliedApprovalID != "" || current.ConsumedDecisionVersion != 0 || suppliedDecisionVersion != 0 {
			return "", 0, badRequest("direct-create requests cannot carry Approval refs or decision versions")
		}
		return "", 0, nil
	}
	if current.AuthorizationPath != AuthorizationPathApprovalRequired {
		return "", 0, conflict("service account create request authorization path is invalid")
	}
	if suppliedApprovalID != "" && currentApprovalID != "" && suppliedApprovalID != currentApprovalID {
		return "", 0, conflict("service account create request Approval ref is immutable")
	}
	effectiveApprovalID := currentApprovalID
	if effectiveApprovalID == "" {
		effectiveApprovalID = suppliedApprovalID
	}

	isDecision := current.BusinessState == RequestStateAwaitingAuthorization &&
		(nextState == RequestStateAuthorized || nextState == RequestStateDeclined || nextState == RequestStateActionRequired)
	if isDecision {
		if effectiveApprovalID == "" {
			return "", 0, badRequest("Approval decision transitions require an approvalInstanceId")
		}
		if suppliedDecisionVersion <= current.ConsumedDecisionVersion {
			if suppliedDecisionVersion == 0 {
				return "", 0, badRequest("Approval decision transitions require a positive decisionVersion")
			}
			return "", 0, conflict("Approval decision version was already consumed")
		}
		return effectiveApprovalID, suppliedDecisionVersion, nil
	}

	if suppliedDecisionVersion != 0 && suppliedDecisionVersion != current.ConsumedDecisionVersion {
		return "", 0, badRequest("decisionVersion can only advance while consuming an Approval decision")
	}
	if nextState == current.BusinessState {
		if current.BusinessState != RequestStateAwaitingAuthorization || currentApprovalID != "" || suppliedApprovalID == "" || suppliedDecisionVersion != 0 {
			return "", 0, conflict("service account create request transition does not change authoritative state")
		}
	}
	return effectiveApprovalID, current.ConsumedDecisionVersion, nil
}

func validateCreatedAccountTransition(ctx context.Context, queryer coredb.QueryRower, current CreateRequest, nextState string, suppliedAccountID string) (string, error) {
	currentAccountID := trim(current.CreatedServiceAccountID)
	if suppliedAccountID != "" && currentAccountID != "" && suppliedAccountID != currentAccountID {
		return "", conflict("service account create request account ref is immutable")
	}
	effectiveAccountID := currentAccountID
	if effectiveAccountID == "" {
		effectiveAccountID = suppliedAccountID
	}
	if suppliedAccountID != "" {
		switch nextState {
		case RequestStateProvisioning, RequestStateFulfilled, RequestStateActionRequired:
		default:
			return "", badRequest("createdServiceAccountId can only be attached to a provisioning outcome")
		}
		var managementKind string
		if err := queryer.QueryRow(ctx, `
SELECT management_kind
FROM service_accounts
WHERE id = $1 AND workspace_id = $2
FOR UPDATE
`, suppliedAccountID, current.WorkspaceID).Scan(&managementKind); err != nil {
			if coredb.IsNotFound(err) {
				return "", conflict("created service account does not belong to the request workspace")
			}
			return "", fmt.Errorf("validate created service account: %w", err)
		}
		if trim(managementKind) != ManagementKindWorkspace {
			return "", conflict("create request cannot resolve to a platform-managed service account")
		}
	}
	if (nextState == RequestStateProvisioning || nextState == RequestStateFulfilled) && effectiveAccountID == "" {
		return "", badRequest("provisioning and fulfilled requests require a createdServiceAccountId")
	}
	if (nextState == RequestStateDeclined || nextState == RequestStateCancelled) && effectiveAccountID != "" {
		return "", conflict("materialized service account requests use account lifecycle management")
	}
	return effectiveAccountID, nil
}

func validateCreateRequestTransition(current CreateRequest, nextState string, effectiveAccountID string, effectiveApprovalID string) error {
	allowed := false
	switch current.BusinessState {
	case RequestStateSubmitted:
		allowed = nextState == RequestStateCancelled ||
			(current.AuthorizationPath == AuthorizationPathDirectCreate && nextState == RequestStateAuthorized) ||
			(current.AuthorizationPath == AuthorizationPathApprovalRequired && nextState == RequestStateAwaitingAuthorization)
	case RequestStateAwaitingAuthorization:
		allowed = nextState == RequestStateAwaitingAuthorization || nextState == RequestStateAuthorized ||
			nextState == RequestStateDeclined || nextState == RequestStateCancelled || nextState == RequestStateActionRequired
	case RequestStateAuthorized:
		allowed = nextState == RequestStateProvisioning || nextState == RequestStateActionRequired
	case RequestStateProvisioning:
		allowed = nextState == RequestStateFulfilled || nextState == RequestStateActionRequired
	case RequestStateActionRequired:
		if effectiveAccountID == "" {
			allowed = nextState == RequestStateAuthorized || nextState == RequestStateProvisioning || nextState == RequestStateCancelled
		} else {
			allowed = nextState == RequestStateProvisioning || nextState == RequestStateFulfilled
		}
	}
	if !allowed {
		return conflict("illegal service account create request state transition")
	}
	if current.AuthorizationPath == AuthorizationPathApprovalRequired &&
		(nextState == RequestStateAuthorized || nextState == RequestStateDeclined) && effectiveApprovalID == "" {
		return badRequest("Approval-authorized outcomes require an approvalInstanceId")
	}
	return nil
}

func createRequestSelectSQL() string {
	return `
SELECT id, workspace_id, requested_by_member_id, request_version,
       client_request_id, payload_snapshot, payload_digest, authorization_path,
       business_state, COALESCE(approval_instance_id, ''),
       consumed_decision_version, COALESCE(cancel_client_mutation_id, ''),
       cancel_payload_digest, cancel_approval_receipt_ref, cancel_result_version,
       COALESCE(created_service_account_id, ''), version, created_at, updated_at,
       cancelled_at, fulfilled_at
FROM service_account_create_requests`
}

func scanCreateRequest(scanner interface{ Scan(...any) error }) (CreateRequest, error) {
	var request CreateRequest
	var cancelledAt, fulfilledAt sql.NullTime
	if err := scanner.Scan(
		&request.ID,
		&request.WorkspaceID,
		&request.RequestedByMemberID,
		&request.RequestVersion,
		&request.ClientRequestID,
		&request.PayloadSnapshot,
		&request.PayloadDigest,
		&request.AuthorizationPath,
		&request.BusinessState,
		&request.ApprovalInstanceID,
		&request.ConsumedDecisionVersion,
		&request.CancelClientMutationID,
		&request.CancelPayloadDigest,
		&request.CancelApprovalReceiptRef,
		&request.CancelResultVersion,
		&request.CreatedServiceAccountID,
		&request.Version,
		&request.CreatedAt,
		&request.UpdatedAt,
		&cancelledAt,
		&fulfilledAt,
	); err != nil {
		return CreateRequest{}, err
	}
	if cancelledAt.Valid {
		value := cancelledAt.Time
		request.CancelledAt = &value
	}
	if fulfilledAt.Valid {
		value := fulfilledAt.Time
		request.FulfilledAt = &value
	}
	return request, nil
}