package db

import (
	"context"
	"database/sql"
	"errors"
)

type Passkey struct {
	ID           int64
	UserID       int64
	CredentialID string
	PublicKey    string
	Counter      int64
	CreatedAt    string
}

func (d *DB) ListPasskeys(ctx context.Context, userID int64) ([]Passkey, error) {
	rows, err := d.QueryContext(ctx,
		`SELECT id, user_id, credential_id, public_key, counter, created_at
		 FROM passkeys WHERE user_id = ?`, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Passkey
	for rows.Next() {
		var p Passkey
		if err := rows.Scan(&p.ID, &p.UserID, &p.CredentialID, &p.PublicKey, &p.Counter, &p.CreatedAt); err != nil {
			return nil, err
		}
		out = append(out, p)
	}
	return out, rows.Err()
}

func (d *DB) CreatePasskey(ctx context.Context, userID int64, credentialID, publicKey string,
	counter int64, createdAt string,
) error {
	_, err := d.ExecContext(ctx,
		`INSERT INTO passkeys (user_id, credential_id, public_key, counter, created_at)
		 VALUES (?, ?, ?, ?, ?)`,
		userID, credentialID, publicKey, counter, createdAt)
	return err
}

// PasskeyWithUser is a stored credential plus its owner's name and approval
// state.
type PasskeyWithUser struct {
	Passkey
	Username  string
	IsPending bool
}

func (d *DB) PasskeyByCredentialID(ctx context.Context, credentialID string) (*PasskeyWithUser, error) {
	var p PasskeyWithUser
	err := d.QueryRowContext(ctx,
		`SELECT passkeys.id, passkeys.user_id, passkeys.credential_id, passkeys.public_key,
		        passkeys.counter, passkeys.created_at, users.username, users.is_pending
		 FROM passkeys
		 JOIN users ON users.id = passkeys.user_id
		 WHERE passkeys.credential_id = ?`, credentialID).
		Scan(&p.ID, &p.UserID, &p.CredentialID, &p.PublicKey, &p.Counter, &p.CreatedAt,
			&p.Username, &p.IsPending)
	if errors.Is(err, sql.ErrNoRows) {
		return nil, nil
	}
	if err != nil {
		return nil, err
	}
	return &p, nil
}

// UpdatePasskeyCounter advances the signature counter only if it still holds
// the expected value, which rejects replayed assertions.
func (d *DB) UpdatePasskeyCounter(ctx context.Context, id, expected, next int64) (bool, error) {
	res, err := d.ExecContext(ctx,
		`UPDATE passkeys SET counter = ? WHERE id = ? AND counter = ?`, next, id, expected)
	if err != nil {
		return false, err
	}
	n, err := res.RowsAffected()
	return n > 0, err
}

// RevokePasskey deletes one passkey, unless it is the user's last way to log
// in. The count check and the delete share a transaction so two concurrent
// revocations cannot both pass the guard.
func (d *DB) RevokePasskey(ctx context.Context, userID, passkeyID int64) (revoked bool, err error) {
	tx, err := d.BeginTx(ctx, nil)
	if err != nil {
		return false, err
	}
	defer tx.Rollback()

	var remaining int
	if err = tx.QueryRowContext(ctx,
		`SELECT COUNT(*) FROM passkeys WHERE user_id = ? AND id != ?`,
		userID, passkeyID).Scan(&remaining); err != nil {
		return false, err
	}
	// The password is read inside the transaction, so a concurrent password
	// removal cannot leave the account with no login method.
	var hasPassword bool
	if err = tx.QueryRowContext(ctx,
		`SELECT password_hash IS NOT NULL FROM users WHERE id = ?`, userID).Scan(&hasPassword); err != nil {
		return false, err
	}
	if !hasPassword && remaining == 0 {
		return false, nil
	}
	if _, err = tx.ExecContext(ctx, `DELETE FROM passkeys WHERE id = ?`, passkeyID); err != nil {
		return false, err
	}
	return true, tx.Commit()
}

// PasskeyOwner returns the owner of a passkey, used to check ownership before
// a revoke.
func (d *DB) PasskeyOwner(ctx context.Context, id int64) (int64, bool, error) {
	var userID int64
	err := d.QueryRowContext(ctx, `SELECT user_id FROM passkeys WHERE id = ?`, id).Scan(&userID)
	if errors.Is(err, sql.ErrNoRows) {
		return 0, false, nil
	}
	if err != nil {
		return 0, false, err
	}
	return userID, true, nil
}