package persistence

import (
	"context"
	"database/sql"
	"time"

	"github.com/google/uuid"
	"github.com/nexuscrm/backend/internal/domain/ports"
	apperrors "github.com/nexuscrm/backend/pkg/errors"
	"github.com/nexuscrm/shared/pkg/constants"
)

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

// NewOAuthRepository creates a new OAuthRepository
func NewOAuthRepository(db *sql.DB) *OAuthRepository {
	return &OAuthRepository{db: 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 {
	id := uuid.New().String()
	query := `INSERT INTO ` + constants.TableOAuthCode + ` (
		Id, Code, ClientId, RedirectUri, Scope, UserId, Nonce, ExpiresAt, Used, CreatedDate
	) VALUES (?, ?, ?, ?, ?, ?, ?, ?, 0, NOW())`

	_, err := r.db.ExecContext(ctx, query, id, code, clientID, redirectURI, scope, userID, nonce, expiresAt)
	if err != nil {
		return apperrors.NewInternalError("failed to store OAuth code", err)
	}
	return nil
}

// 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) {
	// 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

	// SELECT FOR UPDATE to lock the row
	query := `SELECT ClientId, RedirectUri, Scope, UserId, Nonce, ExpiresAt, Used 
		FROM ` + constants.TableOAuthCode + ` 
		WHERE Code = ? 
		FOR UPDATE`

	var data ports.OAuthCodeData
	var used bool
	var scope, nonce sql.NullString // Handle nullable fields
	err = tx.QueryRowContext(ctx, query, code).Scan(
		&data.ClientID, &data.RedirectURI, &scope, &data.UserID, &nonce, &data.ExpiresAt, &used,
	)
	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
	}

	// 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
	}

	// Mark as used
	updateQuery := `UPDATE ` + constants.TableOAuthCode + ` SET Used = 1 WHERE Code = ?`
	_, err = tx.ExecContext(ctx, updateQuery, code)
	if err != nil {
		return nil, apperrors.NewInternalError("failed to mark OAuth code as used", err)
	}

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

	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)
	}
	return result.RowsAffected()
}

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