import { pool } from "../config/db.js";
import { ROLE_IDS } from "../constants/hseRoles.js";

export async function getUserByEmail(email) {
  const [userRows] = await pool.execute(
    `SELECT user_id, employee_name, email_id, password, status_id
     FROM users
     WHERE email_id = ?`,
    [email]
  );

  if (userRows.length === 0) return null;

  const userId = userRows[0].user_id;
  const [roleRows] = await pool.query(
    `SELECT r.*
     FROM roles r
     INNER JOIN user_role ur ON r.role_id = ur.role_id
     WHERE ur.user_id = ?`,
    [userId]
  );

  return { ...userRows[0], roles: roleRows };
}

export async function getUserById(id) {
  const [userRows] = await pool.query(
    `SELECT u.*,
            s.status_name,
            d.department_name,
            l.location_name
     FROM users u
     LEFT JOIN account_status s ON u.status_id = s.status_id
     LEFT JOIN departments d ON u.department_id = d.department_id
     LEFT JOIN locations l ON u.location_id = l.location_id
     WHERE u.user_id = ?`,
    [id]
  );

  if (userRows.length === 0) return null;

  const [roleRows] = await pool.query(
    `SELECT r.*
     FROM roles r
     INNER JOIN user_role ur ON r.role_id = ur.role_id
     WHERE ur.user_id = ?`,
    [id]
  );

  return { ...userRows[0], roles: roleRows };
}

export async function createUser(userData, roleIds, connection = null) {
  const db = connection || pool;
  const {
    employee_id,
    status_id,
    employee_name,
    email_id,
    phonenumber,
    department_id,
    location_id,
    password,
    photo,
  } = userData;

  const [result] = await db.query(
    `INSERT INTO users (employee_id, status_id, employee_name, email_id, phonenumber, department_id, location_id, password, photo)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
    [
      employee_id,
      status_id,
      employee_name,
      email_id,
      phonenumber || null,
      department_id || null,
      location_id || null,
      password,
      photo || null,
    ]
  );

  const userId = result.insertId;

  if (roleIds && Array.isArray(roleIds) && roleIds.length > 0) {
    const roleValues = roleIds.map((roleId) => [userId, roleId]);
    await db.query("INSERT INTO user_role (user_id, role_id) VALUES ?", [
      roleValues,
    ]);
  }

  return userId;
}

export async function findOrCreateUserByGoogle(profile) {
  const email = profile.emails[0].value;
  const displayName =
    profile.displayName ||
    `${profile.name?.givenName || ""} ${profile.name?.familyName || ""}`.trim() ||
    "Google User";
  const googleId = profile.id;

  let user = await getUserByEmail(email);
  if (user) {
    return user;
  }

  const employeeId = `GOOGLE_${googleId.substring(0, 10)}`;
  const userData = {
    employee_id: employeeId,
    status_id: 1,
    employee_name: displayName,
    email_id: email,
    phonenumber: null,
    department_id: null,
    location_id: null,
    password: null,
  };

  const userId = await createUser(userData, [ROLE_IDS.USER]);
  return getUserById(userId);
}
