package persistence

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

	"github.com/nexuscrm/kernel/pkg/ports"
	"github.com/nexuscrm/kernel/pkg/query"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

var _ ports.ApiTokenRepository = (*ApiTokenRepository)(nil)

// ApiTokenRepository handles database operations for self-service personal
// access tokens (_System_ApiToken). Only the token hash is stored; the
// plaintext value is returned to the owner once at creation time.
type ApiTokenRepository struct {
	db *sql.DB
}

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

// getExecutor returns the transactional executor if present in context, or the
// DB connection, mirroring SessionRepository so token writes join the caller's
// transaction when one is carried on the context.
func (r *ApiTokenRepository) getExecutor(ctx context.Context) Executor {
	if tx, ok := ctx.Value(txContextKey{}).(*sql.Tx); ok && tx != nil {
		return tx
	}
	return r.db
}

// Insert creates a new personal access token row.
func (r *ApiTokenRepository) Insert(ctx context.Context, token *models.SystemApiToken) error {
	now := time.Now()
	values := map[string]interface{}{
		constants.FieldID:                       token.ID,
		constants.FieldSysApiToken_OwnerID:      token.OwnerID,
		constants.FieldSysApiToken_Name:         token.Name,
		constants.FieldSysApiToken_TokenHash:    token.TokenHash,
		constants.FieldSysApiToken_TokenPrefix:  token.TokenPrefix,
		constants.FieldSysApiToken_Scopes:       token.Scopes,
		constants.FieldSysApiToken_ExpiresAt:    token.ExpiresAt,
		constants.FieldSysApiToken_LastUsedDate: token.LastUsedDate,
		constants.FieldSysApiToken_IsRevoked:    token.IsRevoked,
		constants.FieldCreatedDate:              now,
		constants.FieldLastModifiedDate:         now,
	}

	qr := query.Insert(constants.TableApiToken, values).Build()

	_, err := r.getExecutor(ctx).ExecContext(ctx, qr.SQL, qr.Params...)
	return err
}

// GetByHash returns the token row whose TokenHash matches, or (nil, nil) when
// no row exists. Revocation and expiry are intentionally not filtered here.
func (r *ApiTokenRepository) GetByHash(ctx context.Context, tokenHash string) (*models.SystemApiToken, error) {
	qr := query.From(constants.TableApiToken).
		Select([]string{
			constants.FieldID,
			constants.FieldSysApiToken_OwnerID,
			constants.FieldSysApiToken_Name,
			constants.FieldSysApiToken_TokenHash,
			constants.FieldSysApiToken_TokenPrefix,
			constants.FieldSysApiToken_Scopes,
			constants.FieldSysApiToken_ExpiresAt,
			constants.FieldSysApiToken_LastUsedDate,
			constants.FieldSysApiToken_IsRevoked,
			constants.FieldCreatedDate,
			constants.FieldLastModifiedDate,
		}).
		Where(constants.FieldSysApiToken_TokenHash, tokenHash).
		Limit(1).
		Build()

	row := r.getExecutor(ctx).QueryRowContext(ctx, qr.SQL, qr.Params...)
	token, err := scanApiToken(row)
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, nil
		}
		return nil, err
	}
	return token, nil
}

// ListByOwner returns all tokens owned by ownerID, newest first.
func (r *ApiTokenRepository) ListByOwner(ctx context.Context, ownerID string) ([]models.SystemApiToken, error) {
	sqlStr := fmt.Sprintf(
		"SELECT `%s`, `%s`, `%s`, `%s`, `%s`, `%s`, `%s`, `%s`, `%s`, `%s`, `%s` "+
			"FROM `%s` WHERE `%s` = ? ORDER BY `%s` DESC",
		constants.FieldID,
		constants.FieldSysApiToken_OwnerID,
		constants.FieldSysApiToken_Name,
		constants.FieldSysApiToken_TokenHash,
		constants.FieldSysApiToken_TokenPrefix,
		constants.FieldSysApiToken_Scopes,
		constants.FieldSysApiToken_ExpiresAt,
		constants.FieldSysApiToken_LastUsedDate,
		constants.FieldSysApiToken_IsRevoked,
		constants.FieldCreatedDate,
		constants.FieldLastModifiedDate,
		constants.TableApiToken,
		constants.FieldSysApiToken_OwnerID,
		constants.FieldCreatedDate,
	)

	rows, err := r.getExecutor(ctx).QueryContext(ctx, sqlStr, ownerID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var tokens []models.SystemApiToken
	for rows.Next() {
		token, err := scanApiToken(rows)
		if err != nil {
			return nil, err
		}
		tokens = append(tokens, *token)
	}
	return tokens, rows.Err()
}

// Revoke marks one token revoked, scoped to its owner so a user can only revoke
// their own tokens. Returns true when a previously-active row was revoked.
func (r *ApiTokenRepository) Revoke(ctx context.Context, id, ownerID string) (bool, error) {
	sqlStr := fmt.Sprintf(
		"UPDATE `%s` SET `%s` = 1, `%s` = CURRENT_TIMESTAMP "+
			"WHERE `%s` = ? AND `%s` = ? AND `%s` = 0",
		constants.TableApiToken,
		constants.FieldSysApiToken_IsRevoked,
		constants.FieldLastModifiedDate,
		constants.FieldID,
		constants.FieldSysApiToken_OwnerID,
		constants.FieldSysApiToken_IsRevoked,
	)
	result, err := r.getExecutor(ctx).ExecContext(ctx, sqlStr, id, ownerID)
	if err != nil {
		return false, err
	}
	affected, err := result.RowsAffected()
	if err != nil {
		return false, err
	}
	return affected == 1, nil
}

// RevokeAllNamedForOwnerExcept marks every still-active token of ownerID whose
// Name equals name revoked in one atomic UPDATE, keeping keepID (pass "" to
// revoke all matching). Returns the number of rows revoked. Rotation callers
// rely on the single-statement form: it either revokes every matching prior
// token or none, never a partial subset — and the name filter scopes rotation
// to one credential family, so rotating it can never revoke the owner's
// unrelated tokens.
func (r *ApiTokenRepository) RevokeAllNamedForOwnerExcept(ctx context.Context, ownerID, name, keepID string) (int64, error) {
	sqlStr := fmt.Sprintf(
		"UPDATE `%s` SET `%s` = 1, `%s` = CURRENT_TIMESTAMP "+
			"WHERE `%s` = ? AND `%s` = ? AND `%s` = 0 AND `%s` <> ?",
		constants.TableApiToken,
		constants.FieldSysApiToken_IsRevoked,
		constants.FieldLastModifiedDate,
		constants.FieldSysApiToken_OwnerID,
		constants.FieldSysApiToken_Name,
		constants.FieldSysApiToken_IsRevoked,
		constants.FieldID,
	)
	result, err := r.getExecutor(ctx).ExecContext(ctx, sqlStr, ownerID, name, keepID)
	if err != nil {
		return 0, err
	}
	return result.RowsAffected()
}

// TouchLastUsed records the most recent time a token authenticated a request.
// Best-effort: a failure here must not fail the authenticated request.
func (r *ApiTokenRepository) TouchLastUsed(ctx context.Context, id string) error {
	sqlStr := fmt.Sprintf(
		"UPDATE `%s` SET `%s` = CURRENT_TIMESTAMP WHERE `%s` = ?",
		constants.TableApiToken,
		constants.FieldSysApiToken_LastUsedDate,
		constants.FieldID,
	)
	_, err := r.getExecutor(ctx).ExecContext(ctx, sqlStr, id)
	return err
}

// rowScanner is satisfied by both *sql.Row and *sql.Rows.
type rowScanner interface {
	Scan(dest ...interface{}) error
}

func scanApiToken(s rowScanner) (*models.SystemApiToken, error) {
	var t models.SystemApiToken
	var scopesRaw sql.NullString
	var expiresAtRaw, lastUsedRaw, createdRaw, lastModifiedRaw []byte

	if err := s.Scan(
		&t.ID,
		&t.OwnerID,
		&t.Name,
		&t.TokenHash,
		&t.TokenPrefix,
		&scopesRaw,
		&expiresAtRaw,
		&lastUsedRaw,
		&t.IsRevoked,
		&createdRaw,
		&lastModifiedRaw,
	); err != nil {
		return nil, err
	}

	if scopesRaw.Valid {
		scope := scopesRaw.String
		t.Scopes = &scope
	}
	if len(expiresAtRaw) > 0 {
		expires := parseTime(expiresAtRaw)
		t.ExpiresAt = &expires
	}
	if len(lastUsedRaw) > 0 {
		lastUsed := parseTime(lastUsedRaw)
		t.LastUsedDate = &lastUsed
	}
	t.CreatedDate = parseTime(createdRaw)
	t.LastModifiedDate = parseTime(lastModifiedRaw)

	return &t, nil
}