const pool = require("../config/db");
const { ROLE_IDS } = require("../constants/hseRoles");

const getUserByEmail = async (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 };
};

/**
 * @param {object} filter - Optional { country_id, location_id, department_id } from master dashboard; filters by user's location/department and location's country.
 */
const getAllUsers = async (filter = null) => {
  let where = '';
  const params = [];
  if (filter && (filter.country_id != null || filter.location_id != null || filter.department_id != null)) {
    const cond = [];
    if (filter.country_id != null && filter.country_id !== '') {
      cond.push('l.country_id = ?');
      params.push(Number(filter.country_id));
    }
    if (filter.location_id != null && filter.location_id !== '') {
      cond.push('u.location_id = ?');
      params.push(Number(filter.location_id));
    }
    if (filter.department_id != null && filter.department_id !== '') {
      cond.push('u.department_id = ?');
      params.push(Number(filter.department_id));
    }
    if (cond.length) where = ' WHERE ' + cond.join(' AND ');
  }
  const [rows] = await pool.query(
    `SELECT u.*,
           s.status_name,
           d.department_name,
           l.location_name,
           (SELECT GROUP_CONCAT(DISTINCT r2.role_name ORDER BY r2.role_name)
            FROM user_role ur2
            INNER JOIN roles r2 ON ur2.role_id = r2.role_id
            WHERE ur2.user_id = u.user_id) AS roles
    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}`,
    params
  );
  return rows;
};

const getUserByEmployeeId = async (employeeId) => {
  const [rows] = await pool.query(
    'SELECT user_id FROM users WHERE employee_id = ?',
    [employeeId]
  );
  return rows.length > 0 ? rows[0] : null;
};

const getUserById = async (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 };
};

const createUser = async (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;
};

const updateUser = async (id, updateData, roleIds, connection = null) => {
  const db = connection || pool;
  const { status_id, employee_name, email_id, phonenumber, department_id, location_id, password } = updateData;
  
  const updateFields = [];
  const updateValues = [];
  
  if (status_id !== undefined) { updateFields.push('status_id = ?'); updateValues.push(status_id); }
  if (employee_name !== undefined) { updateFields.push('employee_name = ?'); updateValues.push(employee_name); }
  if (email_id !== undefined) { updateFields.push('email_id = ?'); updateValues.push(email_id); }
  if (phonenumber !== undefined) { updateFields.push('phonenumber = ?'); updateValues.push(phonenumber); }
  if (department_id !== undefined) { updateFields.push('department_id = ?'); updateValues.push(department_id); }
  if (location_id !== undefined) { updateFields.push('location_id = ?'); updateValues.push(location_id); }
  if (password !== undefined) { updateFields.push('password = ?'); updateValues.push(password); }
  
  if (updateFields.length > 0) {
    updateValues.push(id);
    const [result] = await db.query(
      `UPDATE users SET ${updateFields.join(', ')} WHERE user_id = ?`,
      updateValues
    );
    if (result.affectedRows === 0) return false;
  }
  
  if (roleIds && Array.isArray(roleIds)) {
    await db.query('DELETE FROM user_role WHERE user_id = ?', [id]);
    if (roleIds.length > 0) {
      const roleValues = roleIds.map(roleId => [id, roleId]);
      await db.query('INSERT INTO user_role (user_id, role_id) VALUES ?', [roleValues]);
    }
  }
  
  return true;
};

const deleteUser = async (id) => {
  const [result] = await pool.query('DELETE FROM users WHERE user_id = ?', [id]);
  return result.affectedRows > 0;
};

/**
 * Get user snapshot for logging (status_name, department_name, location_name, role names).
 * Uses the same connection so it runs inside the same transaction.
 */
const getUserSnapshotForLog = async (userId, connection = null) => {
  const db = connection || pool;
  const [rows] = await db.query(
    `SELECT u.employee_id, u.employee_name, u.phonenumber,
            s.status_name AS status, d.department_name AS department, l.location_name AS location,
            (SELECT GROUP_CONCAT(r.role_name) FROM user_role ur JOIN roles r ON ur.role_id = r.role_id WHERE ur.user_id = u.user_id) AS role
     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 = ?`,
    [userId]
  );
  if (rows.length === 0) return null;
  const r = rows[0];
  return {
    employee_id: r.employee_id,
    employee_name: r.employee_name,
    phonenumber: r.phonenumber,
    status: r.status,
    department: r.department,
    location: r.location,
    role: r.role
  };
};

/**
 * Build action string with who performed it (e.g. "CREATED by EMP001", "UPDATED by EMP002").
 */
const buildActionWithPerformer = async (db, actionType, performedByUserId) => {
  if (!performedByUserId) return actionType;
  const [rows] = await db.query(
    'SELECT employee_id FROM users WHERE user_id = ?',
    [performedByUserId]
  );
  const employeeId = rows[0]?.employee_id;
  return employeeId
    ? `${actionType} by ${employeeId}`
    : `${actionType} by (user_id: ${performedByUserId})`;
};

/**
 * Insert a user log row (creation or update). user_id in users_logs = the target user's user_id.
 * action column stores the operation and who did it, e.g. "CREATED by EMP001" or "UPDATED by EMP002".
 */
const insertUserLog = async (userId, performedByUserId, logData, actionType, connection = null) => {
  const db = connection || pool;
  const {
    employee_id,
    role,
    status,
    employee_name,
    phonenumber,
    department,
    location
  } = logData;

  const actionValue = await buildActionWithPerformer(db, actionType, performedByUserId);

  await db.query(
    `INSERT INTO users_logs (user_id, employee_id, role, status, employee_name, phonenumber, department, location, action, created_at)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())`,
    [
      userId,
      employee_id || null,
      role || null,
      status || null,
      employee_name || null,
      phonenumber || null,
      department || null,
      location || null,
      actionValue
    ]
  );
};

const insertUserCreationLog = async (createdUserId, createdByUserId, logData, connection = null) => {
  return insertUserLog(createdUserId, createdByUserId, logData, 'CREATED', connection);
};

/**
 * Insert a user update log (invoked when admin updates a user).
 */
const insertUserUpdateLog = async (updatedUserId, updatedByUserId, logData, connection = null) => {
  return insertUserLog(updatedUserId, updatedByUserId, logData, 'UPDATED', connection);
};

/**
 * Insert a user delete log (invoked when admin soft-deletes a user).
 */
const insertUserDeleteLog = async (deletedUserId, deletedByUserId, logData, connection = null) => {
  return insertUserLog(deletedUserId, deletedByUserId, logData, 'DELETED', connection);
};

/**
 * Get user logs from users_logs (user_id in logs maps to users.user_id).
 * Optional filter: action type ('CREATED', 'UPDATED', 'DELETED') to filter by action prefix.
 * If action not provided, returns all logs.
 */
const getUserLogs = async (options = {}) => {
  const { limit = 100, offset = 0, action } = options;
  const limitVal = Math.min(parseInt(limit, 10) || 100, 500);
  const offsetVal = Math.max(0, parseInt(offset, 10) || 0);

  let sql = `SELECT ul.user_id, ul.created_at, ul.employee_id, ul.role, ul.status, ul.employee_name,
             ul.phonenumber, ul.department, ul.location, ul.action
             FROM users_logs ul`;
  const params = [];

  if (action) {
    sql += ` WHERE ul.action LIKE ?`;
    params.push(`${action}%`);
  }
  sql += ` ORDER BY ul.created_at DESC LIMIT ? OFFSET ?`;
  params.push(limitVal, offsetVal);

  const [rows] = await pool.query(sql, params);
  return rows;
};

/**
 * Find or create user from Google OAuth profile
 * If user exists, return with roles
 * If user doesn't exist, create with email_id, employee_name, photo, status_id from Google credentials
 */
const findOrCreateUserByGoogle = async (profile) => {
  const email = profile.emails[0].value;
  const displayName = profile.displayName || profile.name?.givenName + ' ' + profile.name?.familyName || 'Google User';
  const googleId = profile.id;
  
  // Try to find existing user by email
  let user = await getUserByEmail(email);
  
  if (user) {
    // User exists, return with roles
    return user;
  }
  
  // User doesn't exist - create new user from Google credentials (email_id, employee_name, status_id; photo not stored when column is BLOB)
  const employeeId = `GOOGLE_${googleId.substring(0, 10)}`;
  const defaultStatusId = 1; // Active
  const defaultRoleId = ROLE_IDS.USER;
  
  const userData = {
    employee_id: employeeId,
    status_id: defaultStatusId,
    employee_name: displayName,
    email_id: email,
    phonenumber: null,
    department_id: null,
    location_id: null,
    password: null // OAuth users don't have password
  };
  
  const userId = await createUser(userData, [defaultRoleId]);
  
  // Fetch the newly created user with roles
  user = await getUserById(userId);
  
  return user;
};

module.exports = {
  getUserByEmail,
  getUserByEmployeeId,
  getAllUsers,
  getUserById,
  createUser,
  updateUser,
  deleteUser,
  getUserSnapshotForLog,
  insertUserCreationLog,
  insertUserUpdateLog,
  insertUserDeleteLog,
  getUserLogs,
  findOrCreateUserByGoogle
};
