`database/repositories/users.js`

159 lines
/**
 * Users Repository
 * Data access layer for user operations
 */

import pool from '../connection.js';

class UsersRepository {
  /**
   * Find all users (admin only)
   */
  async findAll(limit = 50, offset = 0) {
    const result = await pool.query(
      `SELECT id, email, name, avatar_url, role, provider, email_verified,
              is_active, created_at, last_login_at, login_count
       FROM users
       WHERE is_active = true
       ORDER BY created_at DESC
       LIMIT $1 OFFSET $2`,
      [limit, offset]
    );
    return result.rows;
  }

  /**
   * Find user by ID
   */
  async findById(id) {
    const result = await pool.query(
      `SELECT id, email, name, avatar_url, role, provider, email_verified,
              is_active, created_at, last_login_at, login_count
       FROM users WHERE id = $1`,
      [id]
    );
    return result.rows[0] || null;
  }

  /**
   * Find user by email
   */
  async findByEmail(email) {
    const result = await pool.query(
      `SELECT * FROM users WHERE email = $1`,
      [email.toLowerCase()]
    );
    return result.rows[0] || null;
  }

  /**
   * Find user by provider ID
   */
  async findByProviderId(provider, providerId) {
    const result = await pool.query(
      `SELECT * FROM users WHERE provider = $1 AND provider_id = $2`,
      [provider, providerId]
    );
    return result.rows[0] || null;
  }

  /**
   * Create new user
   */
  async create(userData) {
    const { email, name, avatar_url, provider, provider_id, password_hash, email_verified } = userData;

    const result = await pool.query(
      `INSERT INTO users (email, name, avatar_url, provider, provider_id, password_hash, email_verified)
       VALUES ($1, $2, $3, $4, $5, $6, $7)
       RETURNING *`,
      [email.toLowerCase(), name, avatar_url, provider || 'email', provider_id, password_hash, email_verified || false]
    );
    return result.rows[0];
  }

  /**
   * Update user
   */
  async update(id, updates) {
    const { name, avatar_url, email_verified, role, is_active } = updates;

    const result = await pool.query(
      `UPDATE users
       SET name = COALESCE($1, name),
           avatar_url = COALESCE($2, avatar_url),
           email_verified = COALESCE($3, email_verified),
           role = COALESCE($4, role),
           is_active = COALESCE($5, is_active),
           updated_at = NOW()
       WHERE id = $6
       RETURNING *`,
      [name, avatar_url, email_verified, role, is_active, id]
    );
    return result.rows[0] || null;
  }

  /**
   * Update login stats
   */
  async recordLogin(id) {
    await pool.query(
      `UPDATE users SET last_login_at = NOW(), login_count = login_count + 1 WHERE id = $1`,
      [id]
    );
  }

  /**
   * Soft delete user
   */
  async delete(id) {
    const result = await pool.query(
      `UPDATE users
       SET is_active = false, email = email || '_deleted_' || id, updated_at = NOW()
       WHERE id = $1
       RETURNING id`,
      [id]
    );
    return result.rows.length > 0;
  }

  /**
   * Hard delete user (use with caution)
   */
  async hardDelete(id) {
    const result = await pool.query(
      `DELETE FROM users WHERE id = $1 RETURNING id`,
      [id]
    );
    return result.rows.length > 0;
  }

  /**
   * Count total users
   */
  async count() {
    const result = await pool.query(
      `SELECT COUNT(*) as total FROM users WHERE is_active = true`
    );
    return parseInt(result.rows[0].total);
  }

  /**
   * Search users by name or email
   */
  async search(query, limit = 20) {
    const result = await pool.query(
      `SELECT id, email, name, avatar_url, role, created_at
       FROM users
       WHERE is_active = true
         AND (name ILIKE $1 OR email ILIKE $1)
       ORDER BY name
       LIMIT $2`,
      [`%${query}%`, limit]
    );
    return result.rows;
  }
}

export default new UsersRepository();