package postgres

import (
	"context"
	"time"

	"botpontos/internal/domain"
)

func accountCols(p string) string {
	return p + "id, " + p + "username::text, " + cols(p, "password_hash", "role", "user_id", "failed_login_count",
		"locked_until", "disabled_at", "last_login_at", "created_at")
}

type accountScan struct {
	a    domain.WebAccount
	role string
}

func (s *accountScan) dest() []any {
	return []any{&s.a.ID, &s.a.Username, &s.a.PasswordHash, &s.role, &s.a.UserID, &s.a.FailedLoginCount,
		&s.a.LockedUntil, &s.a.DisabledAt, &s.a.LastLoginAt, &s.a.CreatedAt}
}

func (s *accountScan) result() domain.WebAccount {
	a := s.a
	a.Role = domain.Role(s.role)
	return a
}

func readAccount(row scanner) (domain.WebAccount, error) {
	var as accountScan
	if err := row.Scan(as.dest()...); err != nil {
		return domain.WebAccount{}, mapErr(err)
	}
	return as.result(), nil
}

func (s *Store) CreateWebAccount(ctx context.Context, username, passwordHash string, role domain.Role, userID *int64) (domain.WebAccount, error) {
	return readAccount(s.pool.QueryRow(ctx, `
		INSERT INTO web_accounts (username, password_hash, role, user_id)
		VALUES ($1::text::citext, $2, $3::text::web_role, $4)
		RETURNING `+accountCols(""), username, passwordHash, string(role), userID))
}

func (s *Store) GetWebAccount(ctx context.Context, id int64) (domain.WebAccount, error) {
	return readAccount(s.pool.QueryRow(ctx, `SELECT `+accountCols("")+` FROM web_accounts WHERE id = $1`, id))
}

func (s *Store) GetWebAccountByUsername(ctx context.Context, username string) (domain.WebAccount, error) {
	return readAccount(s.pool.QueryRow(ctx, `SELECT `+accountCols("")+` FROM web_accounts WHERE username = $1::text::citext`, username))
}

func (s *Store) ListWebAccounts(ctx context.Context) ([]domain.WebAccount, error) {
	rows, err := s.pool.Query(ctx, `SELECT `+accountCols("")+` FROM web_accounts ORDER BY username`)
	return collect(rows, err, readAccount)
}

// UpdateWebAccount altera papel, situação e/ou vínculo com o usuário do Telegram;
// nil mantém o valor atual. Em setUser, userID nil remove o vínculo.
func (s *Store) UpdateWebAccount(ctx context.Context, id int64, role *domain.Role, disabled *bool, setUser bool, userID *int64) error {
	var roleText *string
	if role != nil {
		v := string(*role)
		roleText = &v
	}
	return expectOne(s.pool.Exec(ctx, `
		UPDATE web_accounts SET
			role = COALESCE($2::text::web_role, role),
			disabled_at = CASE
				WHEN $3::boolean IS NULL THEN disabled_at
				WHEN $3::boolean THEN COALESCE(disabled_at, now())
				ELSE NULL
			END,
			user_id = CASE WHEN $4::boolean THEN $5::bigint ELSE user_id END
		WHERE id = $1`, id, roleText, disabled, setUser, userID))
}

func (s *Store) SetWebAccountPassword(ctx context.Context, id int64, passwordHash string) error {
	return expectOne(s.pool.Exec(ctx, `
		UPDATE web_accounts SET password_hash = $2, failed_login_count = 0, locked_until = NULL
		WHERE id = $1`, id, passwordHash))
}

// DeleteWebAccount apaga a conta; as sessões vão junto e a auditoria mantém o nome (migration 00003).
func (s *Store) DeleteWebAccount(ctx context.Context, id int64) error {
	return expectOne(s.pool.Exec(ctx, `DELETE FROM web_accounts WHERE id = $1`, id))
}

func (s *Store) CountActiveOwners(ctx context.Context) (int, error) {
	var n int
	err := s.pool.QueryRow(ctx, `SELECT count(*) FROM web_accounts WHERE role = 'owner' AND disabled_at IS NULL`).Scan(&n)
	return n, err
}

// RecordLoginFailure soma uma falha; ao atingir o limite, bloqueia até lockUntil e zera o contador.
func (s *Store) RecordLoginFailure(ctx context.Context, id int64, maxFailures int, lockUntil time.Time) (locked bool, err error) {
	err = s.pool.QueryRow(ctx, `
		UPDATE web_accounts SET
			failed_login_count = CASE WHEN failed_login_count + 1 >= $2 THEN 0 ELSE failed_login_count + 1 END,
			locked_until = CASE WHEN failed_login_count + 1 >= $2 THEN $3 ELSE locked_until END
		WHERE id = $1
		RETURNING locked_until IS NOT NULL AND locked_until > now()`, id, maxFailures, lockUntil).Scan(&locked)
	return locked, mapErr(err)
}

func (s *Store) RecordLoginSuccess(ctx context.Context, id int64, at time.Time) error {
	return expectOne(s.pool.Exec(ctx, `
		UPDATE web_accounts SET failed_login_count = 0, locked_until = NULL, last_login_at = $2 WHERE id = $1`, id, at))
}

type NewSession struct {
	AccountID     int64
	TokenHash     []byte
	CSRFTokenHash []byte
	ExpiresAt     time.Time
	UserAgent     *string
	IP            *string
}

func (s *Store) CreateSession(ctx context.Context, n NewSession) error {
	_, err := s.pool.Exec(ctx, `
		INSERT INTO web_sessions (account_id, token_hash, csrf_token_hash, expires_at, last_seen_at, user_agent, ip)
		VALUES ($1, $2, $3, $4, now(), $5, $6::text::inet)`,
		n.AccountID, n.TokenHash, n.CSRFTokenHash, n.ExpiresAt, n.UserAgent, n.IP)
	return mapErr(err)
}

func (s *Store) GetSessionByTokenHash(ctx context.Context, tokenHash []byte) (domain.WebSession, error) {
	var ws domain.WebSession
	var as accountScan
	dest := append([]any{&ws.ID, &ws.AccountID, &ws.CSRFTokenHash, &ws.ExpiresAt, &ws.LastSeenAt, &ws.RevokedAt}, as.dest()...)
	err := s.pool.QueryRow(ctx, `
		SELECT s.id, s.account_id, s.csrf_token_hash, s.expires_at, s.last_seen_at, s.revoked_at, `+accountCols("a.")+`
		FROM web_sessions s JOIN web_accounts a ON a.id = s.account_id
		WHERE s.token_hash = $1`, tokenHash).Scan(dest...)
	if err != nil {
		return ws, mapErr(err)
	}
	ws.Account = as.result()
	return ws, nil
}

func (s *Store) TouchSession(ctx context.Context, id int64, at time.Time) error {
	_, err := s.pool.Exec(ctx, `UPDATE web_sessions SET last_seen_at = $2 WHERE id = $1`, id, at)
	return err
}

func (s *Store) RevokeSession(ctx context.Context, id int64) error {
	_, err := s.pool.Exec(ctx, `UPDATE web_sessions SET revoked_at = now() WHERE id = $1 AND revoked_at IS NULL`, id)
	return err
}

func (s *Store) RevokeAccountSessions(ctx context.Context, accountID int64) error {
	_, err := s.pool.Exec(ctx, `UPDATE web_sessions SET revoked_at = now() WHERE account_id = $1 AND revoked_at IS NULL`, accountID)
	return err
}

func (s *Store) DeleteSessionsEndedBefore(ctx context.Context, before time.Time) (int64, error) {
	tag, err := s.pool.Exec(ctx, `DELETE FROM web_sessions WHERE COALESCE(revoked_at, expires_at) < $1`, before)
	return tag.RowsAffected(), err
}