package persistence

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

	"github.com/google/uuid"
	apperrors "github.com/nexuscrm/kernel/pkg/errors"
	"github.com/nexuscrm/kernel/pkg/ports"
	"github.com/nexuscrm/shared/pkg/constants"
)

// OAuthRepository is also the persistence adapter for the generic
// authorization-grant contract. _System_OAuthConsent is the existing physical
// OAuth grant table; its CRM-era column names do not escape this adapter.
var _ ports.AuthorizationGrantRepository = (*OAuthRepository)(nil)

// authorizationGrantTx verifies the generic lock-order contract before any
// grant mutation. The product-specific caller must acquire the client lock
// first; this adapter then acquires the subject lock and finally touches the
// grant row. Requiring the ambient transaction prevents a nested transaction
// from splitting credential issuance from grant creation.
func authorizationGrantTx(ctx context.Context, clientID string) (*sql.Tx, error) {
	if !ports.AuthorizationClientLockHeld(ctx, clientID) {
		return nil, apperrors.NewInternalError("authorization grant operation requires the caller-owned client lock", nil)
	}
	tx, ok := ctx.Value(txContextKey{}).(*sql.Tx)
	if !ok || tx == nil || !isPessimisticGrantTransaction(ctx) {
		return nil, apperrors.NewInternalError("authorization grant operation requires a caller-owned pessimistic transaction", nil)
	}
	return tx, nil
}

func isPessimisticGrantTransaction(ctx context.Context) bool {
	marked, ok := ctx.Value(pessimisticTxMarkerKey{}).(bool)
	return ok && marked
}

// lockOAuthSubjectRow is the per-subject serialization point for every OAuth
// credential issuance or revocation. Product callers acquire their Connected
// App lock first; the remaining OAuth operation then owns the subject lock
// before it locks or mutates a credential or grant row. Read-only preflight
// snapshots never establish a competing serialization path.
func lockOAuthSubjectRow(ctx context.Context, tx *sql.Tx, subjectID string) error {
	var scanned string
	switch err := tx.QueryRowContext(ctx,
		`SELECT Id FROM `+constants.TableUser+` WHERE Id = ? FOR UPDATE`, subjectID).Scan(&scanned); {
	case err == sql.ErrNoRows:
		return apperrors.NewNotFoundError("User", subjectID)
	case err != nil:
		return apperrors.NewInternalError("failed to lock authorization grant subject", err)
	}
	return nil
}

func validAuthorizationGrantRequest(request ports.AuthorizationGrantRequest) error {
	if strings.TrimSpace(request.SubjectID) == "" || strings.TrimSpace(request.ClientID) == "" {
		return apperrors.NewValidationError("authorization_grant", "subject and client are required")
	}
	if request.PolicyVersion < 1 {
		return apperrors.NewValidationError("authorization_grant", "policy version must be at least 1")
	}
	return nil
}

// EnsureAuthorizationGrant returns the live grant for the exact approved
// subject/client/scope/policy tuple, or opens a fresh immutable era. Scope is
// compared as an opaque, already-normalized policy value; L0 does not apply a
// product-specific scope hierarchy.
func (r *OAuthRepository) EnsureAuthorizationGrant(ctx context.Context, request ports.AuthorizationGrantRequest) (ports.AuthorizationGrant, error) {
	if err := validAuthorizationGrantRequest(request); err != nil {
		return ports.AuthorizationGrant{}, err
	}
	tx, err := authorizationGrantTx(ctx, request.ClientID)
	if err != nil {
		return ports.AuthorizationGrant{}, err
	}
	if err := lockOAuthSubjectRow(ctx, tx, request.SubjectID); err != nil {
		return ports.AuthorizationGrant{}, err
	}

	// Retire every live grant with a different policy version or scope before
	// looking for reuse. Scope is an opaque OAuth policy value, so compare it
	// bytewise rather than through the database's case-insensitive default
	// collation. This covers policy version rollback and scope A->B->A (including
	// a case-only scope change): an older era can never become live again merely
	// because a product restores a prior policy tuple.
	if _, err := tx.ExecContext(ctx,
		`UPDATE `+constants.TableOAuthConsent+`
			SET RevokedAt = NOW(6), RevokedBy = 'system:authorization-policy-change', LastModifiedDate = NOW()
			WHERE UserId = ? AND ConnectedAppId = ? AND RevokedAt IS NULL
			  AND (ConsentVersion <> ? OR BINARY Scope <> BINARY ?)`,
		request.SubjectID, request.ClientID, request.PolicyVersion, request.Scope); err != nil {
		return ports.AuthorizationGrant{}, apperrors.NewInternalError("failed to retire stale authorization grants", err)
	}

	var grant ports.AuthorizationGrant
	err = tx.QueryRowContext(ctx,
		`SELECT Id, Scope, ConsentVersion FROM `+constants.TableOAuthConsent+`
			WHERE UserId = ? AND ConnectedAppId = ? AND ConsentVersion = ?
			  AND BINARY Scope = BINARY ? AND RevokedAt IS NULL
			ORDER BY ApprovedAt DESC
			LIMIT 1`,
		request.SubjectID, request.ClientID, request.PolicyVersion, request.Scope,
	).Scan(&grant.ID, &grant.Scope, &grant.PolicyVersion)
	switch err {
	case nil:
		grant.SubjectID = request.SubjectID
		grant.ClientID = request.ClientID
		return grant, nil
	case sql.ErrNoRows:
		// A changed scope opens a fresh era rather than widening an existing
		// row. Credentials bound to the old ID therefore cannot inherit scope.
	default:
		return ports.AuthorizationGrant{}, apperrors.NewInternalError("failed to read authorization grant", err)
	}

	grant = ports.AuthorizationGrant{
		ID:            uuid.NewString(),
		SubjectID:     request.SubjectID,
		ClientID:      request.ClientID,
		Scope:         request.Scope,
		PolicyVersion: request.PolicyVersion,
	}
	if _, err := tx.ExecContext(ctx,
		`INSERT INTO `+constants.TableOAuthConsent+` (
			Id, UserId, ConnectedAppId, Scope, ConsentVersion, ApprovedAt, CreatedDate, LastModifiedDate
		) VALUES (?, ?, ?, ?, ?, NOW(6), NOW(), NOW())`,
		grant.ID, grant.SubjectID, grant.ClientID, grant.Scope, grant.PolicyVersion); err != nil {
		return ports.AuthorizationGrant{}, apperrors.NewInternalError("failed to create authorization grant", err)
	}
	return grant, nil
}

// AuthorizationGrantActive checks an immutable grant era under the same lock
// order used by issuance and disconnect. A live row must match its original
// subject, client, and current policy version exactly.
func (r *OAuthRepository) AuthorizationGrantActive(ctx context.Context, grantID, subjectID, clientID string, policyVersion int) (bool, error) {
	if strings.TrimSpace(grantID) == "" || strings.TrimSpace(subjectID) == "" || strings.TrimSpace(clientID) == "" || policyVersion < 1 {
		return false, apperrors.NewValidationError("authorization_grant", "grant, subject, client, and policy version are required")
	}
	tx, err := authorizationGrantTx(ctx, clientID)
	if err != nil {
		return false, err
	}
	if err := lockOAuthSubjectRow(ctx, tx, subjectID); err != nil {
		return false, err
	}
	var found int
	err = tx.QueryRowContext(ctx,
		`SELECT 1 FROM `+constants.TableOAuthConsent+`
			WHERE Id = ? AND UserId = ? AND ConnectedAppId = ? AND ConsentVersion = ? AND RevokedAt IS NULL
			LIMIT 1 FOR UPDATE`,
		grantID, subjectID, clientID, policyVersion,
	).Scan(&found)
	switch err {
	case nil:
		return true, nil
	case sql.ErrNoRows:
		return false, nil
	default:
		return false, apperrors.NewInternalError("failed to check authorization grant", err)
	}
}

// RevokeAuthorizationGrantsForSubjectAndClient retires all live eras for one
// pair. The caller must already hold the client lock; this adapter locks the
// subject row before the grant update so a concurrent credential issuance cannot
// re-establish an era after this operation returns.
func (r *OAuthRepository) RevokeAuthorizationGrantsForSubjectAndClient(ctx context.Context, subjectID, clientID, revokedBy string) (int, error) {
	if strings.TrimSpace(subjectID) == "" || strings.TrimSpace(clientID) == "" {
		return 0, apperrors.NewValidationError("authorization_grant", "subject and client are required")
	}
	tx, err := authorizationGrantTx(ctx, clientID)
	if err != nil {
		return 0, err
	}
	if err := lockOAuthSubjectRow(ctx, tx, subjectID); err != nil {
		return 0, err
	}
	result, err := tx.ExecContext(ctx,
		`UPDATE `+constants.TableOAuthConsent+`
			SET RevokedAt = NOW(6), RevokedBy = ?, LastModifiedDate = NOW()
			WHERE UserId = ? AND ConnectedAppId = ? AND RevokedAt IS NULL`,
		oauthNullableString(revokedBy), subjectID, clientID)
	if err != nil {
		return 0, apperrors.NewInternalError("failed to revoke authorization grants", err)
	}
	return authorizationGrantRowsAffected(result)
}

// ConsumeUnboundOAuthCodesForSubject is the one transitional operation for
// pre-grant authorization codes. New code issuance always records an immutable
// grant id, so only a historical code can match the NULL/empty binding. The
// caller's chosen client lock and this adapter's subject lock keep the sweep in
// the same client -> subject transaction as the disconnect that requested it.
func (r *OAuthRepository) ConsumeUnboundOAuthCodesForSubject(ctx context.Context, subjectID, lockingClientID string) (int, error) {
	if strings.TrimSpace(subjectID) == "" || strings.TrimSpace(lockingClientID) == "" {
		return 0, apperrors.NewValidationError("authorization_grant", "subject and locking client are required")
	}
	tx, err := authorizationGrantTx(ctx, lockingClientID)
	if err != nil {
		return 0, err
	}
	if err := lockOAuthSubjectRow(ctx, tx, subjectID); err != nil {
		return 0, err
	}
	result, err := tx.ExecContext(ctx,
		`UPDATE `+constants.TableOAuthCode+`
			SET Used = 1
			WHERE UserId = ? AND Used = 0 AND ExpiresAt > NOW(6)
			  AND (ConsentId IS NULL OR ConsentId = '')`,
		subjectID,
	)
	if err != nil {
		return 0, apperrors.NewInternalError("failed to consume unbound OAuth codes", err)
	}
	return authorizationGrantRowsAffected(result)
}

// RevokeAuthorizationGrantsForClient retires all live grants for a client. A
// product lifecycle hook calls this while it owns that client's record lock;
// no subject-row scan is required because the client lock serializes every
// issuer before this bulk update is made visible.
func (r *OAuthRepository) RevokeAuthorizationGrantsForClient(ctx context.Context, clientID, revokedBy string) (int, error) {
	if strings.TrimSpace(clientID) == "" {
		return 0, apperrors.NewValidationError("authorization_grant", "client is required")
	}
	tx, err := authorizationGrantTx(ctx, clientID)
	if err != nil {
		return 0, err
	}
	result, err := tx.ExecContext(ctx,
		`UPDATE `+constants.TableOAuthConsent+`
			SET RevokedAt = NOW(6), RevokedBy = ?, LastModifiedDate = NOW()
			WHERE ConnectedAppId = ? AND RevokedAt IS NULL`,
		oauthNullableString(revokedBy), clientID)
	if err != nil {
		return 0, apperrors.NewInternalError("failed to revoke authorization grants for client", err)
	}
	return authorizationGrantRowsAffected(result)
}

// ListLiveAuthorizationGrantConnections reads the self-service connection list
// directly from the live authorization-grant source of truth. It intentionally
// runs outside the issuance lock: this is a snapshot for display, while every
// credential mutation independently re-checks its immutable grant era.
func (r *OAuthRepository) ListLiveAuthorizationGrantConnections(ctx context.Context, subjectID string) ([]ports.AuthorizationGrantConnection, error) {
	if strings.TrimSpace(subjectID) == "" {
		return nil, apperrors.NewValidationError("authorization_grant", "subject is required")
	}
	rows, err := r.executor(ctx).QueryContext(ctx,
		`SELECT c.ConnectedAppId, a.Name, c.Scope, c.ApprovedAt, c.LastUsedAt, c.ConsentVersion
			FROM `+constants.TableOAuthConsent+` c
			JOIN `+constants.TableConnectedApp+` a ON a.Id = c.ConnectedAppId
			WHERE c.UserId = ? AND c.RevokedAt IS NULL
			ORDER BY a.Name, c.ApprovedAt`, subjectID)
	if err != nil {
		return nil, apperrors.NewInternalError("failed to list live authorization grant connections", err)
	}
	defer rows.Close() //nolint:errcheck

	connections := make([]ports.AuthorizationGrantConnection, 0)
	for rows.Next() {
		var (
			connection ports.AuthorizationGrantConnection
			scope      sql.NullString
			approvedAt sql.NullTime
			lastUsedAt sql.NullTime
		)
		if err := rows.Scan(&connection.ClientID, &connection.ClientName, &scope, &approvedAt, &lastUsedAt, &connection.PolicyVersion); err != nil {
			return nil, apperrors.NewInternalError("failed to scan live authorization grant connection", err)
		}
		connection.Scope = scope.String
		if approvedAt.Valid {
			connection.FirstConnectedAt = approvedAt.Time
		}
		if lastUsedAt.Valid {
			used := lastUsedAt.Time
			connection.LastUsedAt = &used
		}
		connections = append(connections, connection)
	}
	if err := rows.Err(); err != nil {
		return nil, apperrors.NewInternalError("failed to iterate live authorization grant connections", err)
	}
	return connections, nil
}

func authorizationGrantRowsAffected(result sql.Result) (int, error) {
	affected, err := result.RowsAffected()
	if err != nil {
		return 0, apperrors.NewInternalError("failed to count revoked authorization grants", err)
	}
	return int(affected), nil
}