package persistence

import (
	"context"
	"crypto/sha256"
	"database/sql"
	"encoding/base64"
	"strings"
	"time"

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

// OAuthRepository implements ports.OAuthRepository for DB-backed OAuth code storage
type OAuthRepository struct {
	db *sql.DB
}

type oauthExecContext interface {
	ExecContext(context.Context, string, ...interface{}) (sql.Result, error)
}

type oauthQueryRowContext interface {
	QueryRowContext(context.Context, string, ...interface{}) *sql.Row
}

// NewOAuthRepository creates a new OAuthRepository
func NewOAuthRepository(db *sql.DB) *OAuthRepository {
	return &OAuthRepository{db: db}
}

func (r *OAuthRepository) executor(ctx context.Context) Executor {
	if tx, ok := ctx.Value(txContextKey{}).(*sql.Tx); ok && tx != nil {
		return tx
	}
	return r.db
}

// StoreAuthCode saves an authorization code with expiration
func (r *OAuthRepository) StoreAuthCode(ctx context.Context, code, clientID, redirectURI, scope, userID, nonce string, expiresAt time.Time) error {
	return r.StoreAuthCodeRequest(ctx, ports.OAuthCodeCreateRequest{
		Code:        code,
		CodeHash:    hashOAuthCode(code),
		ClientID:    clientID,
		RedirectURI: redirectURI,
		Scope:       scope,
		UserID:      userID,
		Nonce:       nonce,
		ExpiresAt:   expiresAt,
	})
}

// StoreAuthCodeRequest saves an authorization code request snapshot. New rows
// are keyed by CodeHash; the legacy plaintext Code column is intentionally left
// null so a database leak cannot directly redeem outstanding codes.
func (r *OAuthRepository) StoreAuthCodeRequest(ctx context.Context, req ports.OAuthCodeCreateRequest) error {
	return storeAuthCodeRequest(ctx, r.executor(ctx), req)
}

func storeAuthCodeRequest(ctx context.Context, exec oauthExecContext, req ports.OAuthCodeCreateRequest) error {
	id := uuid.New().String()
	query := `INSERT INTO ` + constants.TableOAuthCode + ` (
		Id, Code, CodeHash, ClientId, RedirectUri, Scope, UserId, Nonce, ExpiresAt, Used,
		CodeChallenge, CodeChallengeMethod, AuthTime, Issuer, ConsentId, ClientTypeAtIssue, Resource, CreatedDate
	) VALUES (?, NULL, ?, ?, ?, ?, ?, ?, ?, 0, ?, ?, ?, ?, ?, ?, ?, NOW())`

	codeHash := req.CodeHash
	if codeHash == "" && req.Code != "" {
		codeHash = hashOAuthCode(req.Code)
	}
	var authTime interface{}
	if !req.AuthTime.IsZero() {
		authTime = req.AuthTime
	}
	_, err := exec.ExecContext(
		ctx,
		query,
		id,
		codeHash,
		req.ClientID,
		req.RedirectURI,
		oauthNullableString(req.Scope),
		req.UserID,
		oauthNullableString(req.Nonce),
		req.ExpiresAt,
		oauthNullableString(req.CodeChallenge),
		oauthNullableString(req.CodeChallengeMethod),
		authTime,
		oauthNullableString(req.Issuer),
		oauthNullableString(req.ConsentID),
		oauthNullableString(req.ClientTypeAtIssue),
		oauthNullableString(req.Resource),
	)
	if err != nil {
		return apperrors.NewInternalError("failed to store OAuth code", err)
	}
	return nil
}

// StoreAuthorizeRequest saves a pre-login authorize request snapshot. Rows are
// keyed by HandleHash. The raw handle is never stored.
func (r *OAuthRepository) StoreAuthorizeRequest(ctx context.Context, req ports.OAuthAuthorizeRequestCreateRequest) error {
	id := uuid.New().String()
	query := `INSERT INTO ` + constants.TableOAuthAuthorizeRequest + ` (
		Id, HandleHash, ClientId, RedirectUri, Scope, State, CodeChallenge,
		CodeChallengeMethod, Issuer, Resource, ExpiresAt, Used, CreatedDate
	) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, NOW())`

	handleHash := req.HandleHash
	if handleHash == "" && req.Handle != "" {
		handleHash = hashOAuthCode(req.Handle)
	}
	_, err := r.db.ExecContext(
		ctx,
		query,
		id,
		handleHash,
		req.ClientID,
		req.RedirectURI,
		oauthNullableString(req.Scope),
		oauthNullableString(req.State),
		req.CodeChallenge,
		req.CodeChallengeMethod,
		req.Issuer,
		oauthNullableString(req.Resource),
		req.ExpiresAt,
	)
	if err != nil {
		return apperrors.NewInternalError("failed to store OAuth authorize request", err)
	}
	return nil
}

// GetAuthCode retrieves a usable code without consuming it.
// Returns nil if code not found, expired, or already used.
func (r *OAuthRepository) GetAuthCode(ctx context.Context, code string) (*ports.OAuthCodeData, error) {
	return queryOAuthCode(ctx, r.executor(ctx), code, false)
}

// ConsumeAuthCode retrieves and marks a code as used (one-time use).
// Returns nil if code not found, expired, or already used.
func (r *OAuthRepository) ConsumeAuthCode(ctx context.Context, code string) (*ports.OAuthCodeData, error) {
	if tx, ok := ctx.Value(txContextKey{}).(*sql.Tx); ok && tx != nil {
		return consumeAuthCode(ctx, tx, code)
	}
	// Use transaction to ensure atomicity of read-then-update
	tx, err := r.db.BeginTx(ctx, nil)
	if err != nil {
		return nil, apperrors.NewInternalError("failed to begin transaction", err)
	}
	defer tx.Rollback() //nolint:errcheck

	data, err := consumeAuthCode(ctx, tx, code)
	if err != nil || data == nil {
		return data, err
	}

	err = tx.Commit()
	if err != nil {
		return nil, apperrors.NewInternalError("failed to commit transaction", err)
	}

	return data, nil
}

func consumeAuthCode(ctx context.Context, tx *sql.Tx, code string) (*ports.OAuthCodeData, error) {
	// Read the immutable code snapshot without taking its row lock. In the
	// credential-issuance path the caller already owns the Connected App lock;
	// acquire the subject lock next so a disconnect sweep and a redemption of a
	// historical unbound code serialize on the same app -> subject order.
	data, err := queryOAuthCode(ctx, tx, code, false)
	if err != nil || data == nil {
		return data, err
	}
	if err := lockOAuthSubjectRow(ctx, tx, data.UserID); err != nil {
		return nil, err
	}
	// Recheck the one-time and expiry gates in the write itself. A concurrent
	// redeem or disconnect that completed while this transaction waited for the
	// subject lock therefore wins without issuing a session.
	updateQuery := `UPDATE ` + constants.TableOAuthCode + `
		SET Used = 1
		WHERE CodeHash = ? AND Used = 0 AND ExpiresAt > NOW(6)`
	result, err := tx.ExecContext(ctx, updateQuery, hashOAuthCode(code))
	if err != nil {
		return nil, apperrors.NewInternalError("failed to mark OAuth code as used", err)
	}
	updated, err := result.RowsAffected()
	if err != nil {
		return nil, apperrors.NewInternalError("failed to confirm OAuth code consumption", err)
	}
	if updated == 0 {
		return nil, nil
	}
	return data, nil
}

func queryOAuthCode(ctx context.Context, q oauthQueryRowContext, code string, forUpdate bool) (*ports.OAuthCodeData, error) {
	query := `SELECT ClientId, RedirectUri, Scope, UserId, Nonce, ExpiresAt, Used,
				CodeChallenge, CodeChallengeMethod, AuthTime, Issuer, ConsentId, ClientTypeAtIssue, Resource
			FROM ` + constants.TableOAuthCode + `
			WHERE CodeHash = ?`
	if forUpdate {
		query += `
			FOR UPDATE`
	}

	var data ports.OAuthCodeData
	var used bool
	var scope, nonce, codeChallenge, codeChallengeMethod, issuer, consentID, clientTypeAtIssue, resource sql.NullString
	var authTime sql.NullTime
	err := q.QueryRowContext(ctx, query, hashOAuthCode(code)).Scan(
		&data.ClientID,
		&data.RedirectURI,
		&scope,
		&data.UserID,
		&nonce,
		&data.ExpiresAt,
		&used,
		&codeChallenge,
		&codeChallengeMethod,
		&authTime,
		&issuer,
		&consentID,
		&clientTypeAtIssue,
		&resource,
	)
	if err == sql.ErrNoRows {
		return nil, nil // Code not found
	}
	if err != nil {
		return nil, apperrors.NewInternalError("failed to query OAuth code", err)
	}

	// Assign nullable fields
	if scope.Valid {
		data.Scope = scope.String
	}
	if nonce.Valid {
		data.Nonce = nonce.String
	}
	if codeChallenge.Valid {
		data.CodeChallenge = codeChallenge.String
	}
	if codeChallengeMethod.Valid {
		data.CodeChallengeMethod = codeChallengeMethod.String
	}
	if authTime.Valid {
		data.AuthTime = authTime.Time
	}
	if issuer.Valid {
		data.Issuer = issuer.String
	}
	if consentID.Valid {
		data.ConsentID = consentID.String
	}
	if clientTypeAtIssue.Valid {
		data.ClientTypeAtIssue = clientTypeAtIssue.String
	}
	if resource.Valid {
		data.Resource = resource.String
	}

	// Check if already used
	if used {
		return nil, nil // Code already consumed
	}

	// Check expiration
	if time.Now().After(data.ExpiresAt) {
		return nil, nil // Code expired
	}

	return &data, nil
}

// GetAuthorizeRequest retrieves a usable pre-login authorize request without
// consuming it. The caller must later use a consume method for terminal resume.
func (r *OAuthRepository) GetAuthorizeRequest(ctx context.Context, handle string) (*ports.OAuthAuthorizeRequestData, error) {
	query := `SELECT ClientId, RedirectUri, Scope, State, CodeChallenge,
			CodeChallengeMethod, Issuer, Resource, ExpiresAt, Used
		FROM ` + constants.TableOAuthAuthorizeRequest + `
		WHERE HandleHash = ?`

	return queryOAuthAuthorizeRequest(ctx, r.db, query, hashOAuthCode(handle))
}

// ConsumeAuthorizeRequest retrieves and marks a pre-login authorize request
// used without issuing a code. It returns nil for missing, expired, or used
// handles and is used for terminal policy-denial resumes.
func (r *OAuthRepository) ConsumeAuthorizeRequest(ctx context.Context, handle string) (*ports.OAuthAuthorizeRequestData, error) {
	tx, err := r.db.BeginTx(ctx, nil)
	if err != nil {
		return nil, apperrors.NewInternalError("failed to begin transaction", err)
	}
	defer tx.Rollback() //nolint:errcheck

	query := `SELECT ClientId, RedirectUri, Scope, State, CodeChallenge,
			CodeChallengeMethod, Issuer, Resource, ExpiresAt, Used
		FROM ` + constants.TableOAuthAuthorizeRequest + `
		WHERE HandleHash = ?
		FOR UPDATE`

	data, err := queryOAuthAuthorizeRequest(ctx, tx, query, hashOAuthCode(handle))
	if err != nil {
		return nil, err
	}
	if data == nil {
		return nil, nil
	}

	updateQuery := `UPDATE ` + constants.TableOAuthAuthorizeRequest + ` SET Used = 1 WHERE HandleHash = ?`
	result, err := tx.ExecContext(ctx, updateQuery, hashOAuthCode(handle))
	if err != nil {
		return nil, apperrors.NewInternalError("failed to mark OAuth authorize request as used", err)
	}
	if rows, rowErr := result.RowsAffected(); rowErr == nil && rows != 1 {
		return nil, apperrors.NewInternalError("failed to mark OAuth authorize request as used", sql.ErrNoRows)
	}

	if err := tx.Commit(); err != nil {
		return nil, apperrors.NewInternalError("failed to commit transaction", err)
	}

	return data, nil
}

// ConsumeAuthorizeRequestAndStoreAuthCode stores the final authorization code
// and marks the pre-login authorize request used in the same transaction. It
// returns nil when the handle is missing, expired, or already used.
func (r *OAuthRepository) ConsumeAuthorizeRequestAndStoreAuthCode(ctx context.Context, handle string, req ports.OAuthCodeCreateRequest) (*ports.OAuthAuthorizeRequestData, error) {
	if tx, ok := ctx.Value(txContextKey{}).(*sql.Tx); ok && tx != nil {
		return consumeAuthorizeRequestAndStoreAuthCodeTx(ctx, tx, handle, req)
	}
	tx, err := r.db.BeginTx(ctx, nil)
	if err != nil {
		return nil, apperrors.NewInternalError("failed to begin transaction", err)
	}
	defer tx.Rollback() //nolint:errcheck

	data, err := consumeAuthorizeRequestAndStoreAuthCodeTx(ctx, tx, handle, req)
	if err != nil || data == nil {
		return data, err
	}
	if err := tx.Commit(); err != nil {
		return nil, apperrors.NewInternalError("failed to commit transaction", err)
	}
	return data, nil
}

// consumeAuthorizeRequestAndStoreAuthCodeTx never commits or rolls back. It
// lets code issuance compose the grant, pre-login handle, and code row in one
// caller-owned credential transaction when the context already carries one.
func consumeAuthorizeRequestAndStoreAuthCodeTx(ctx context.Context, tx *sql.Tx, handle string, req ports.OAuthCodeCreateRequest) (*ports.OAuthAuthorizeRequestData, error) {

	query := `SELECT ClientId, RedirectUri, Scope, State, CodeChallenge,
			CodeChallengeMethod, Issuer, Resource, ExpiresAt, Used
		FROM ` + constants.TableOAuthAuthorizeRequest + `
		WHERE HandleHash = ?
		FOR UPDATE`

	data, err := queryOAuthAuthorizeRequest(ctx, tx, query, hashOAuthCode(handle))
	if err != nil {
		return nil, err
	}
	if data == nil {
		return nil, nil
	}

	if err := storeAuthCodeRequest(ctx, tx, req); err != nil {
		return nil, err
	}

	updateQuery := `UPDATE ` + constants.TableOAuthAuthorizeRequest + ` SET Used = 1 WHERE HandleHash = ?`
	result, err := tx.ExecContext(ctx, updateQuery, hashOAuthCode(handle))
	if err != nil {
		return nil, apperrors.NewInternalError("failed to mark OAuth authorize request as used", err)
	}
	if rows, rowErr := result.RowsAffected(); rowErr == nil && rows != 1 {
		return nil, apperrors.NewInternalError("failed to mark OAuth authorize request as used", sql.ErrNoRows)
	}

	return data, nil
}

// CleanupExpiredCodes removes expired codes (for background cleanup)
func (r *OAuthRepository) CleanupExpiredCodes(ctx context.Context) (int64, error) {
	query := `DELETE FROM ` + constants.TableOAuthCode + ` WHERE ExpiresAt < NOW() OR Used = 1`
	result, err := r.db.ExecContext(ctx, query)
	if err != nil {
		return 0, apperrors.NewInternalError("failed to cleanup expired OAuth codes", err)
	}
	deletedCodes, _ := result.RowsAffected()

	query = `DELETE FROM ` + constants.TableOAuthAuthorizeRequest + ` WHERE ExpiresAt < NOW() OR Used = 1`
	result, err = r.db.ExecContext(ctx, query)
	if err != nil {
		return deletedCodes, apperrors.NewInternalError("failed to cleanup expired OAuth authorize requests", err)
	}
	deletedRequests, _ := result.RowsAffected()
	return deletedCodes + deletedRequests, nil
}

func hashOAuthCode(code string) string {
	sum := sha256.Sum256([]byte(code))
	return base64.RawURLEncoding.EncodeToString(sum[:])
}

func oauthNullableString(value string) interface{} {
	if value == "" {
		return nil
	}
	return value
}

func queryOAuthAuthorizeRequest(ctx context.Context, q oauthQueryRowContext, query string, args ...interface{}) (*ports.OAuthAuthorizeRequestData, error) {
	var data ports.OAuthAuthorizeRequestData
	var used bool
	var scope, state, resource sql.NullString
	err := q.QueryRowContext(ctx, query, args...).Scan(
		&data.ClientID,
		&data.RedirectURI,
		&scope,
		&state,
		&data.CodeChallenge,
		&data.CodeChallengeMethod,
		&data.Issuer,
		&resource,
		&data.ExpiresAt,
		&used,
	)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, apperrors.NewInternalError("failed to query OAuth authorize request", err)
	}

	if scope.Valid {
		data.Scope = scope.String
	}
	if state.Valid {
		data.State = state.String
	}
	if resource.Valid {
		data.Resource = resource.String
	}
	if used {
		return nil, nil
	}
	if time.Now().After(data.ExpiresAt) {
		return nil, nil
	}
	return &data, nil
}

// StoreRefreshToken persists a hashed, rotating OAuth refresh token. Only the
// hash is stored; the plaintext is returned to the client once at issuance.
func (r *OAuthRepository) StoreRefreshToken(ctx context.Context, token *models.SystemOAuthRefreshToken) error {
	query := `INSERT INTO ` + constants.TableOAuthRefreshToken + ` (
		Id, TokenHash, UserId, ConnectedAppId, FamilyId, ConsentId, Scope, ExpiresAt, IsRevoked, CreatedDate, LastModifiedDate
	) VALUES (?, ?, ?, ?, ?, ?, ?, ?, 0, NOW(), NOW())`
	_, err := r.executor(ctx).ExecContext(ctx, query,
		token.ID,
		token.TokenHash,
		token.UserID,
		token.ConnectedAppID,
		token.FamilyID,
		token.ConsentID,
		token.Scope,
		token.ExpiresAt,
	)
	if err != nil {
		return apperrors.NewInternalError("failed to store OAuth refresh token", err)
	}
	return nil
}

// GetRefreshTokenByHash returns the refresh token row for a hash, or (nil, nil)
// when none exists. Revocation/expiry are validated by the caller.
func (r *OAuthRepository) GetRefreshTokenByHash(ctx context.Context, tokenHash string) (*models.SystemOAuthRefreshToken, error) {
	query := `SELECT Id, TokenHash, UserId, ConnectedAppId, FamilyId, ConsentId, Scope, ExpiresAt, IsRevoked, CreatedDate, LastModifiedDate
		FROM ` + constants.TableOAuthRefreshToken + ` WHERE TokenHash = ? LIMIT 1`

	var t models.SystemOAuthRefreshToken
	var appID, familyID, consentID, scope sql.NullString
	var expiresAtRaw, createdRaw, modifiedRaw []byte

	err := r.db.QueryRowContext(ctx, query, tokenHash).Scan(
		&t.ID, &t.TokenHash, &t.UserID, &appID, &familyID, &consentID, &scope, &expiresAtRaw, &t.IsRevoked, &createdRaw, &modifiedRaw,
	)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, apperrors.NewInternalError("failed to query OAuth refresh token", err)
	}

	if appID.Valid {
		v := appID.String
		t.ConnectedAppID = &v
	}
	if familyID.Valid {
		v := familyID.String
		t.FamilyID = &v
	}
	if consentID.Valid {
		v := consentID.String
		t.ConsentID = &v
	}
	if scope.Valid {
		v := scope.String
		t.Scope = &v
	}
	t.ExpiresAt = parseTime(expiresAtRaw)
	t.CreatedDate = parseTime(createdRaw)
	t.LastModifiedDate = parseTime(modifiedRaw)
	return &t, nil
}

// RevokeRefreshToken revokes one refresh token by id. Returns true when a
// still-active row was revoked — the rotate-on-use path relies on this.
func (r *OAuthRepository) RevokeRefreshToken(ctx context.Context, id string) (bool, error) {
	query := `UPDATE ` + constants.TableOAuthRefreshToken + ` SET IsRevoked = 1, LastModifiedDate = NOW() WHERE Id = ? AND IsRevoked = 0`
	result, err := r.db.ExecContext(ctx, query, id)
	if err != nil {
		return false, apperrors.NewInternalError("failed to revoke OAuth refresh token", err)
	}
	affected, err := result.RowsAffected()
	if err != nil {
		return false, err
	}
	return affected == 1, nil
}

// RevokeRefreshTokenFamily revokes every still-active refresh token sharing a
// family id. It backs replay defense: presenting an already-rotated token means
// the chain may be compromised, so the whole family is killed and the legitimate
// holder is forced to re-authenticate. Returns the number of rows revoked.
//
// It runs in a transaction that first locks the family rows FOR UPDATE, so it
// serializes against RotateRefreshToken on the same family: a concurrent
// rotation cannot insert an active successor "after" this sweep has run without
// first blocking on the same family lock.
func (r *OAuthRepository) RevokeRefreshTokenFamily(ctx context.Context, familyID string) (int, error) {
	if strings.TrimSpace(familyID) == "" {
		return 0, nil
	}
	tx, err := r.db.BeginTx(ctx, nil)
	if err != nil {
		return 0, apperrors.NewInternalError("failed to begin transaction", err)
	}
	defer tx.Rollback() //nolint:errcheck

	n, err := revokeRefreshFamilyLocked(ctx, tx, familyID)
	if err != nil {
		return 0, err
	}
	if err := tx.Commit(); err != nil {
		return 0, apperrors.NewInternalError("failed to commit transaction", err)
	}
	return n, nil
}

// lockRefreshFamily takes a row lock on every member of a refresh-token family
// so concurrent rotations/sweeps on the family serialize. It is a SELECT ... FOR
// UPDATE, which the MySQL driver requires be issued via Query (not Exec, which
// rejects row-returning statements); the rows themselves are discarded — only
// the lock matters.
func lockRefreshFamily(ctx context.Context, tx *sql.Tx, familyID string) error {
	rows, err := tx.QueryContext(ctx,
		`SELECT Id FROM `+constants.TableOAuthRefreshToken+` WHERE FamilyId = ? FOR UPDATE`, familyID)
	if err != nil {
		return apperrors.NewInternalError("failed to lock OAuth refresh token family", err)
	}
	defer rows.Close()
	for rows.Next() { //nolint:revive // draining the result set is what releases the read side
	}
	if err := rows.Err(); err != nil {
		return apperrors.NewInternalError("failed to lock OAuth refresh token family", err)
	}
	return nil
}

// revokeRefreshFamilyLocked locks the family rows FOR UPDATE and revokes every
// still-active member. It must be called inside a transaction; callers rely on
// the FOR UPDATE to serialize the whole family.
func revokeRefreshFamilyLocked(ctx context.Context, tx *sql.Tx, familyID string) (int, error) {
	if err := lockRefreshFamily(ctx, tx, familyID); err != nil {
		return 0, err
	}
	result, err := tx.ExecContext(ctx,
		`UPDATE `+constants.TableOAuthRefreshToken+` SET IsRevoked = 1, LastModifiedDate = NOW() WHERE FamilyId = ? AND IsRevoked = 0`, familyID)
	if err != nil {
		return 0, apperrors.NewInternalError("failed to revoke OAuth refresh token family", err)
	}
	affected, err := result.RowsAffected()
	if err != nil {
		return 0, err
	}
	return int(affected), nil
}

// RevokeRefreshTokensForUser revokes every still-active refresh token owned by a
// user (credential-revocation integration). Returns the number of rows revoked.
func (r *OAuthRepository) RevokeRefreshTokensForUser(ctx context.Context, userID string) (int, error) {
	if strings.TrimSpace(userID) == "" {
		return 0, nil
	}
	result, err := r.db.ExecContext(ctx,
		`UPDATE `+constants.TableOAuthRefreshToken+` SET IsRevoked = 1, LastModifiedDate = NOW() WHERE UserId = ? AND IsRevoked = 0`, userID)
	if err != nil {
		return 0, apperrors.NewInternalError("failed to revoke OAuth refresh tokens for user", err)
	}
	affected, err := result.RowsAffected()
	if err != nil {
		return 0, err
	}
	return int(affected), nil
}

// refreshTokenExecutor returns the transactional executor if the context carries
// one, else the DB connection — so a refresh-token revoke can run inside the same
// transaction as the Connected App change that triggered it.
func (r *OAuthRepository) refreshTokenExecutor(ctx context.Context) oauthExecContext {
	if tx, ok := ctx.Value(txContextKey{}).(*sql.Tx); ok && tx != nil {
		return tx
	}
	return r.db
}

// RevokeRefreshTokensForConnectedApp revokes every still-active refresh token
// issued for a Connected App (honoring a context-carried transaction). Returns
// the number of rows revoked.
func (r *OAuthRepository) RevokeRefreshTokensForConnectedApp(ctx context.Context, appID string) (int, error) {
	if strings.TrimSpace(appID) == "" {
		return 0, nil
	}
	result, err := r.refreshTokenExecutor(ctx).ExecContext(ctx,
		`UPDATE `+constants.TableOAuthRefreshToken+` SET IsRevoked = 1, LastModifiedDate = NOW() WHERE ConnectedAppId = ? AND IsRevoked = 0`, appID)
	if err != nil {
		return 0, apperrors.NewInternalError("failed to revoke OAuth refresh tokens for connected app", err)
	}
	affected, err := result.RowsAffected()
	if err != nil {
		return 0, err
	}
	return int(affected), nil
}

// RevokeRefreshTokensForUserAndConnectedApp eagerly retires only the refresh
// credentials covered by a user's disconnect. The authorization-grant revoke
// is the authority; this prevents an already-issued token from rotating before
// its next grant check. The context executor preserves the surrounding app ->
// user -> grant transaction.
func (r *OAuthRepository) RevokeRefreshTokensForUserAndConnectedApp(ctx context.Context, userID, appID string) (int, error) {
	if strings.TrimSpace(userID) == "" || strings.TrimSpace(appID) == "" {
		return 0, nil
	}
	result, err := r.refreshTokenExecutor(ctx).ExecContext(ctx,
		`UPDATE `+constants.TableOAuthRefreshToken+`
			SET IsRevoked = 1, LastModifiedDate = NOW()
			WHERE UserId = ? AND ConnectedAppId = ? AND IsRevoked = 0`, userID, appID)
	if err != nil {
		return 0, apperrors.NewInternalError("failed to revoke OAuth refresh tokens for user and connected app", err)
	}
	affected, err := result.RowsAffected()
	if err != nil {
		return 0, err
	}
	return int(affected), nil
}

// RotateRefreshToken atomically consumes a presented refresh token and inserts
// its successor in one transaction, serialized per family via FOR UPDATE. See
// the ports.OAuthRepository contract for the (rotated, reuseDetected) semantics.
func (r *OAuthRepository) RotateRefreshToken(ctx context.Context, presentedHash string, successor *models.SystemOAuthRefreshToken) (bool, bool, error) {
	if successor == nil {
		return false, false, apperrors.NewInternalError("nil refresh token successor", nil)
	}
	if tx, ok := ctx.Value(txContextKey{}).(*sql.Tx); ok && tx != nil {
		return r.rotateRefreshTokenOnTx(ctx, tx, presentedHash, successor)
	}

	tx, err := r.db.BeginTx(ctx, nil)
	if err != nil {
		return false, false, apperrors.NewInternalError("failed to begin transaction", err)
	}
	defer tx.Rollback() //nolint:errcheck
	rotated, reuseDetected, err := r.rotateRefreshTokenOnTx(ctx, tx, presentedHash, successor)
	if err != nil {
		return false, false, err
	}
	if err := tx.Commit(); err != nil {
		return false, false, apperrors.NewInternalError("failed to commit transaction", err)
	}
	return rotated, reuseDetected, nil
}

// rotateRefreshTokenOnTx performs the single-use family rotation in a
// caller-owned transaction. It never commits or rolls back: a credential issuer
// may compose the rotation with its grant check and session persistence.
func (r *OAuthRepository) rotateRefreshTokenOnTx(ctx context.Context, tx *sql.Tx, presentedHash string, successor *models.SystemOAuthRefreshToken) (bool, bool, error) {
	familyID := ""
	if successor.FamilyID != nil {
		familyID = strings.TrimSpace(*successor.FamilyID)
	}

	// Acquire the per-family lock up front so any concurrent rotation or family
	// sweep on this family serializes behind us. Legacy tokens (no family) fall
	// back to locking only the presented row, below.
	if familyID != "" {
		if err := lockRefreshFamily(ctx, tx, familyID); err != nil {
			return false, false, err
		}
	}

	// Re-read the presented token under the lock. For a family-less legacy token
	// the family lock above is a no-op, so lock the row itself here.
	presentedQuery := `SELECT Id, IsRevoked FROM ` + constants.TableOAuthRefreshToken + ` WHERE TokenHash = ?`
	if familyID == "" {
		presentedQuery += ` FOR UPDATE`
	}
	var presentedID string
	var isRevoked bool
	switch err := tx.QueryRowContext(ctx, presentedQuery, presentedHash).Scan(&presentedID, &isRevoked); {
	case err == sql.ErrNoRows:
		// The presented token vanished between the service read and here; change
		// nothing and report neither rotation nor reuse.
		return false, false, nil
	case err != nil:
		return false, false, apperrors.NewInternalError("failed to load presented refresh token", err)
	}

	if isRevoked {
		// Replay of an already-rotated token: revoke the whole family.
		if err := r.reuseWithinTx(ctx, tx, familyID, presentedID); err != nil {
			return false, false, err
		}
		return false, true, nil
	}

	// Single-use CAS on the presented token.
	res, err := tx.ExecContext(ctx,
		`UPDATE `+constants.TableOAuthRefreshToken+` SET IsRevoked = 1, LastModifiedDate = NOW() WHERE Id = ? AND IsRevoked = 0`, presentedID)
	if err != nil {
		return false, false, apperrors.NewInternalError("failed to revoke presented refresh token", err)
	}
	affected, err := res.RowsAffected()
	if err != nil {
		return false, false, err
	}
	if affected != 1 {
		// Lost the CAS (a concurrent consumer beat us): treat as reuse.
		if err := r.reuseWithinTx(ctx, tx, familyID, presentedID); err != nil {
			return false, false, err
		}
		return false, true, nil
	}

	if err := insertRefreshTokenTx(ctx, tx, successor); err != nil {
		return false, false, err
	}
	return true, false, nil
}

// reuseWithinTx revokes the presented token's whole family (or just the token,
// for a legacy family-less token) inside an existing transaction.
func (r *OAuthRepository) reuseWithinTx(ctx context.Context, tx *sql.Tx, familyID, presentedID string) error {
	if familyID != "" {
		_, err := revokeRefreshFamilyLocked(ctx, tx, familyID)
		return err
	}
	if _, err := tx.ExecContext(ctx,
		`UPDATE `+constants.TableOAuthRefreshToken+` SET IsRevoked = 1, LastModifiedDate = NOW() WHERE Id = ? AND IsRevoked = 0`, presentedID); err != nil {
		return apperrors.NewInternalError("failed to revoke presented refresh token", err)
	}
	return nil
}

// insertRefreshTokenTx inserts a refresh token row using the given executor.
func insertRefreshTokenTx(ctx context.Context, tx *sql.Tx, token *models.SystemOAuthRefreshToken) error {
	query := `INSERT INTO ` + constants.TableOAuthRefreshToken + ` (
		Id, TokenHash, UserId, ConnectedAppId, FamilyId, ConsentId, Scope, ExpiresAt, IsRevoked, CreatedDate, LastModifiedDate
	) VALUES (?, ?, ?, ?, ?, ?, ?, ?, 0, NOW(), NOW())`
	if _, err := tx.ExecContext(ctx, query,
		token.ID, token.TokenHash, token.UserID, token.ConnectedAppID, token.FamilyID, token.ConsentID, token.Scope, token.ExpiresAt,
	); err != nil {
		return apperrors.NewInternalError("failed to store OAuth refresh token", err)
	}
	return nil
}

// Ensure interface compliance
var _ ports.OAuthRepository = (*OAuthRepository)(nil)