import { pool } from "../db.js";

export async function findUserByEmail(email) {
  const { rows } = await pool.query(`
    SELECT id, email, password_hash, display_name, role, status, balance, last_login_at, created_at, updated_at
    FROM uno_bff.users WHERE lower(email) = lower($1) LIMIT 1
  `, [email]);
  return rows[0] || null;
}

export async function findUserByEmailAndPassword(email, password) {
  const { rows } = await pool.query(`
    SELECT id, email, password_hash, display_name, role, status, balance, last_login_at, created_at, updated_at
    FROM uno_bff.users
    WHERE lower(email) = lower($1) AND status = 'active'
      AND password_hash::text = sites.crypt($2::text, password_hash::text)
    LIMIT 1
  `, [email, password]);
  return rows[0] || null;
}

export async function findUserById(userId) {
  const { rows } = await pool.query(`
    SELECT id, email, display_name, role, status, balance, created_at
    FROM uno_bff.users WHERE id = $1 AND status = 'active'
  `, [userId]);
  return rows[0] || null;
}

export async function updateUserBalance(userId, delta) {
  const { rows } = await pool.query(`
    UPDATE uno_bff.users
    SET balance = balance + $2, updated_at = NOW()
    WHERE id = $1
    RETURNING balance
  `, [userId, delta]);
  return rows[0]?.balance ?? null;
}

export async function touchLastLogin(userId) {
  await pool.query(`UPDATE uno_bff.users SET last_login_at = now(), updated_at = now() WHERE id = $1`, [userId]);
}

export async function createUser({ email, password, displayName }) {
  const { rows } = await pool.query(`
    INSERT INTO uno_bff.users (email, password_hash, display_name, role, status)
    VALUES (lower($1), sites.crypt($2::text, sites.gen_salt('bf', 10)), $3, 'user', 'active')
    RETURNING id, email, display_name, role, status, balance, created_at
  `, [email, password, displayName || null]);
  return rows[0];
}

export async function userExistsByEmail(email) {
  const { rows } = await pool.query(`SELECT 1 FROM uno_bff.users WHERE lower(email) = lower($1) LIMIT 1`, [email]);
  return rows.length > 0;
}

export async function findUserByTelegramId(telegramId) {
  const { rows } = await pool.query(`SELECT * FROM uno_bff.users WHERE telegram_id = $1 AND status = 'active' LIMIT 1`, [telegramId]);
  return rows[0] || null;
}

export async function linkTelegramToUser(userId, telegramId) {
  const { rows } = await pool.query(`UPDATE uno_bff.users SET telegram_id = $2 WHERE id = $1 RETURNING *`, [userId, telegramId]);
  return rows[0] || null;
}