mini-prog/backend/internal/store/postgres/ops_auth_store.go

package postgres

import (
	"context"
	"errors"
	"fmt"
	"time"

	"github.com/jackc/pgx/v5"
)

type OpsUser struct {
	ID          string
	PublicID    string
	CountryCode string
	Mobile      string
	DisplayName string
	Status      string
	LastLoginAt *time.Time
}

type OpsRole struct {
	ID          string
	RoleCode    string
	DisplayName string
	RoleRank    int
}

type CreateOpsUserParams struct {
	PublicID    string
	CountryCode string
	Mobile      string
	DisplayName string
	Status      string
}

type CreateOpsRefreshTokenParams struct {
	OpsUserID  string
	DeviceKey  string
	TokenHash  string
	ExpiresAt  time.Time
}

type OpsRefreshTokenRecord struct {
	ID        string
	OpsUserID string
	DeviceKey *string
	ExpiresAt time.Time
	IsRevoked bool
}

func (s *Store) CountOpsUsers(ctx context.Context) (int, error) {
	row := s.pool.QueryRow(ctx, `SELECT COUNT(*) FROM ops_users WHERE status <> 'deleted'`)
	var count int
	if err := row.Scan(&count); err != nil {
		return 0, fmt.Errorf("count ops users: %w", err)
	}
	return count, nil
}

func (s *Store) GetOpsUserByMobile(ctx context.Context, db queryRower, countryCode, mobile string) (*OpsUser, error) {
	row := db.QueryRow(ctx, `
		SELECT id, ops_user_public_id, country_code, mobile, display_name, status, last_login_at
		FROM ops_users
		WHERE country_code = $1 AND mobile = $2
		LIMIT 1
	`, countryCode, mobile)
	return scanOpsUser(row)
}

func (s *Store) GetOpsUserByID(ctx context.Context, db queryRower, opsUserID string) (*OpsUser, error) {
	row := db.QueryRow(ctx, `
		SELECT id, ops_user_public_id, country_code, mobile, display_name, status, last_login_at
		FROM ops_users
		WHERE id = $1
	`, opsUserID)
	return scanOpsUser(row)
}

func (s *Store) CreateOpsUser(ctx context.Context, tx Tx, params CreateOpsUserParams) (*OpsUser, error) {
	row := tx.QueryRow(ctx, `
		INSERT INTO ops_users (ops_user_public_id, country_code, mobile, display_name, status)
		VALUES ($1, $2, $3, $4, $5)
		RETURNING id, ops_user_public_id, country_code, mobile, display_name, status, last_login_at
	`, params.PublicID, params.CountryCode, params.Mobile, params.DisplayName, params.Status)
	return scanOpsUser(row)
}

func (s *Store) TouchOpsUserLogin(ctx context.Context, tx Tx, opsUserID string) error {
	_, err := tx.Exec(ctx, `
		UPDATE ops_users
		SET last_login_at = NOW()
		WHERE id = $1
	`, opsUserID)
	if err != nil {
		return fmt.Errorf("touch ops user last login: %w", err)
	}
	return nil
}

func (s *Store) GetOpsRoleByCode(ctx context.Context, tx Tx, roleCode string) (*OpsRole, error) {
	row := tx.QueryRow(ctx, `
		SELECT id, role_code, display_name, role_rank
		FROM ops_roles
		WHERE role_code = $1
		  AND status = 'active'
		LIMIT 1
	`, roleCode)
	return scanOpsRole(row)
}

func (s *Store) AssignOpsRole(ctx context.Context, tx Tx, opsUserID, roleID string) error {
	_, err := tx.Exec(ctx, `
		INSERT INTO ops_user_roles (ops_user_id, ops_role_id, status)
		VALUES ($1, $2, 'active')
		ON CONFLICT (ops_user_id, ops_role_id) DO NOTHING
	`, opsUserID, roleID)
	if err != nil {
		return fmt.Errorf("assign ops role: %w", err)
	}
	return nil
}

func (s *Store) GetPrimaryOpsRole(ctx context.Context, db queryRower, opsUserID string) (*OpsRole, error) {
	row := db.QueryRow(ctx, `
		SELECT r.id, r.role_code, r.display_name, r.role_rank
		FROM ops_user_roles ur
		JOIN ops_roles r ON r.id = ur.ops_role_id
		WHERE ur.ops_user_id = $1
		  AND ur.status = 'active'
		  AND r.status = 'active'
		ORDER BY r.role_rank DESC, r.created_at ASC
		LIMIT 1
	`, opsUserID)
	return scanOpsRole(row)
}

func (s *Store) CreateOpsRefreshToken(ctx context.Context, tx Tx, params CreateOpsRefreshTokenParams) (string, error) {
	row := tx.QueryRow(ctx, `
		INSERT INTO ops_refresh_tokens (ops_user_id, device_key, token_hash, expires_at)
		VALUES ($1, NULLIF($2, ''), $3, $4)
		RETURNING id
	`, params.OpsUserID, params.DeviceKey, params.TokenHash, params.ExpiresAt)

	var id string
	if err := row.Scan(&id); err != nil {
		return "", fmt.Errorf("create ops refresh token: %w", err)
	}
	return id, nil
}

func (s *Store) GetOpsRefreshTokenForUpdate(ctx context.Context, tx Tx, tokenHash string) (*OpsRefreshTokenRecord, error) {
	row := tx.QueryRow(ctx, `
		SELECT id, ops_user_id, device_key, expires_at, revoked_at IS NOT NULL
		FROM ops_refresh_tokens
		WHERE token_hash = $1
		FOR UPDATE
	`, tokenHash)

	var record OpsRefreshTokenRecord
	err := row.Scan(&record.ID, &record.OpsUserID, &record.DeviceKey, &record.ExpiresAt, &record.IsRevoked)
	if errors.Is(err, pgx.ErrNoRows) {
		return nil, nil
	}
	if err != nil {
		return nil, fmt.Errorf("query ops refresh token for update: %w", err)
	}
	return &record, nil
}

func (s *Store) RotateOpsRefreshToken(ctx context.Context, tx Tx, oldTokenID, newTokenID string) error {
	_, err := tx.Exec(ctx, `
		UPDATE ops_refresh_tokens
		SET revoked_at = NOW(), replaced_by_token_id = $2
		WHERE id = $1
	`, oldTokenID, newTokenID)
	if err != nil {
		return fmt.Errorf("rotate ops refresh token: %w", err)
	}
	return nil
}

func (s *Store) RevokeOpsRefreshToken(ctx context.Context, tokenHash string) error {
	_, err := s.pool.Exec(ctx, `
		UPDATE ops_refresh_tokens
		SET revoked_at = COALESCE(revoked_at, NOW())
		WHERE token_hash = $1
	`, tokenHash)
	if err != nil {
		return fmt.Errorf("revoke ops refresh token: %w", err)
	}
	return nil
}

func scanOpsUser(row pgx.Row) (*OpsUser, error) {
	var item OpsUser
	err := row.Scan(&item.ID, &item.PublicID, &item.CountryCode, &item.Mobile, &item.DisplayName, &item.Status, &item.LastLoginAt)
	if errors.Is(err, pgx.ErrNoRows) {
		return nil, nil
	}
	if err != nil {
		return nil, fmt.Errorf("scan ops user: %w", err)
	}
	return &item, nil
}

func scanOpsRole(row pgx.Row) (*OpsRole, error) {
	var item OpsRole
	err := row.Scan(&item.ID, &item.RoleCode, &item.DisplayName, &item.RoleRank)
	if errors.Is(err, pgx.ErrNoRows) {
		return nil, nil
	}
	if err != nil {
		return nil, fmt.Errorf("scan ops role: %w", err)
	}
	return &item, nil
}