const { query } = require('../config/database');

async function findByUsername(username) {
  const res = await query('SELECT * FROM users WHERE username = $1', [username]);
  return res.rows[0] || null;
}

async function findById(id) {
  const res = await query('SELECT id,name,username,role,is_active,created_at FROM users WHERE id=$1', [id]);
  return res.rows[0] || null;
}

async function findAll({ limit, offset, search, role }) {
  const params = [];
  const conds = ['1=1'];
  if (role)   { params.push(role);          conds.push(`role=$${params.length}`); }
  if (search) { params.push(`%${search}%`); conds.push(`(name ILIKE $${params.length} OR username ILIKE $${params.length})`); }
  const where = conds.join(' AND ');
  const res = await query(
    `SELECT id,name,username,role,is_active,created_at FROM users WHERE ${where} ORDER BY id LIMIT $${params.length+1} OFFSET $${params.length+2}`,
    [...params, limit, offset]
  );
  const count = await query(`SELECT COUNT(*) FROM users WHERE ${where}`, params);
  return { rows: res.rows, total: parseInt(count.rows[0].count) };
}

async function create({ name, username, password, role }) {
  const res = await query(
    'INSERT INTO users (name,username,password,role) VALUES ($1,$2,$3,$4) RETURNING id,name,username,role,is_active,created_at',
    [name, username, password, role]
  );
  return res.rows[0];
}

async function update(id, fields) {
  const sets = Object.keys(fields).map((k, i) => `${k}=$${i + 2}`).join(',');
  const res = await query(
    `UPDATE users SET ${sets} WHERE id=$1 RETURNING id,name,username,role,is_active,created_at`,
    [id, ...Object.values(fields)]
  );
  return res.rows[0] || null;
}

async function remove(id) {
  await query('DELETE FROM users WHERE id=$1', [id]);
}

module.exports = { findByUsername, findById, findAll, create, update, remove };
