pagodera/lib/db/queries/users.ts

/**
 * Queries de usuários
 * CRUD completo para a tabela users
 */

import { query } from "../index";

export interface User {
  user_id: number;
  username: string;
  email: string;
  password_hash?: string; // Apenas para login, não retornar em outros lugares
  first_name: string;
  last_name: string;
  phone: string | null;
  cpf: string;
  account_level: "free" | "bronze" | "silver" | "gold";
  has_donated: boolean;
  whatsapp_notifications: boolean;
  created_at: Date;
  updated_at: Date;
  last_login: Date | null;
  is_active: boolean;
  is_verified: boolean;
}

export interface CreateUserData {
  username: string;
  email: string;
  password_hash: string;
  first_name: string;
  last_name: string;
  phone?: string;
  cpf?: string; // CPF agora é opcional
}

export interface UpdateUserData {
  first_name?: string;
  last_name?: string;
  phone?: string;
  whatsapp_notifications?: boolean;
}

/**
 * Cria um novo usuário
 */
export async function createUser(
  data: CreateUserData,
): Promise<{ userId: number; username: string; email: string }> {
  const result = await query<{
    user_id: number;
    username: string;
    email: string;
  }>(
    `
    INSERT INTO users (username, email, password_hash, first_name, last_name, phone, cpf)
    VALUES ($1, $2, $3, $4, $5, $6, $7)
    RETURNING user_id, username, email
    `,
    [
      data.username,
      data.email,
      data.password_hash,
      data.first_name,
      data.last_name,
      data.phone || null,
      data.cpf || null,
    ],
  );

  return {
    userId: result.rows[0].user_id,
    username: result.rows[0].username,
    email: result.rows[0].email,
  };
}

/**
 * Busca usuário por email (inclui password_hash para login)
 */
export async function getUserByEmail(email: string): Promise<User | null> {
  const result = await query<User>(
    `
    SELECT * FROM users WHERE email = $1
    `,
    [email],
  );

  return result.rows[0] || null;
}

/**
 * Busca usuário por username
 */
export async function getUserByUsername(
  username: string,
): Promise<User | null> {
  const result = await query<User>(
    `
    SELECT * FROM users WHERE username = $1
    `,
    [username],
  );

  return result.rows[0] || null;
}

/**
 * Busca usuário por ID (sem password_hash)
 */
export async function getUserById(userId: number): Promise<User | null> {
  const result = await query<User>(
    `
    SELECT 
      user_id, username, email, first_name, last_name, phone, cpf,
      account_level, has_donated, whatsapp_notifications,
      created_at, updated_at, last_login, is_active, is_verified
    FROM users 
    WHERE user_id = $1
    `,
    [userId],
  );

  return result.rows[0] || null;
}

/**
 * Busca usuário por CPF (verifica duplicidade)
 */
export async function getUserByCpf(cpf: string): Promise<User | null> {
  const result = await query<User>(
    `
    SELECT user_id, username, email FROM users WHERE cpf = $1
    `,
    [cpf],
  );

  return result.rows[0] || null;
}

/**
 * Atualiza dados do usuário
 */
export async function updateUser(
  userId: number,
  data: UpdateUserData,
): Promise<User> {
  const updates: string[] = [];
  const values: any[] = [];
  let paramIndex = 1;

  if (data.first_name !== undefined) {
    updates.push(`first_name = $${paramIndex++}`);
    values.push(data.first_name);
  }

  if (data.last_name !== undefined) {
    updates.push(`last_name = $${paramIndex++}`);
    values.push(data.last_name);
  }

  if (data.phone !== undefined) {
    updates.push(`phone = $${paramIndex++}`);
    values.push(data.phone || null);
  }

  if (data.whatsapp_notifications !== undefined) {
    updates.push(`whatsapp_notifications = $${paramIndex++}`);
    values.push(data.whatsapp_notifications);
  }

  if (updates.length === 0) {
    // Se não há updates, retorna usuário atual
    const user = await getUserById(userId);
    if (!user) {
      throw new Error("Usuário não encontrado");
    }
    return user;
  }

  updates.push(`updated_at = CURRENT_TIMESTAMP`);
  values.push(userId);

  const result = await query<User>(
    `
    UPDATE users 
    SET ${updates.join(", ")}
    WHERE user_id = $${paramIndex}
    RETURNING 
      user_id, username, email, first_name, last_name, phone, cpf,
      account_level, has_donated, whatsapp_notifications,
      created_at, updated_at, last_login, is_active, is_verified
    `,
    values,
  );

  if (result.rows.length === 0) {
    throw new Error("Usuário não encontrado");
  }

  return result.rows[0];
}

/**
 * Atualiza timestamp de último login
 */
export async function updateLastLogin(userId: number): Promise<void> {
  await query(
    `
    UPDATE users 
    SET last_login = CURRENT_TIMESTAMP 
    WHERE user_id = $1
    `,
    [userId],
  );
}