const pool = require("../config/db");

const getAllLocations = async () => {
  const [rows] = await pool.query(`
    SELECT l.*, c.country_name 
    FROM locations l 
    LEFT JOIN country c ON l.country_id = c.country_id
  `);
  return rows;
};

const getLocationById = async (id) => {
  const [rows] = await pool.query(`
    SELECT l.*, c.country_name 
    FROM locations l 
    LEFT JOIN country c ON l.country_id = c.country_id
    WHERE l.location_id = ?
  `, [id]);
  return rows.length ? rows[0] : null;
};

const getLocationsByCountryId = async (country_id) => {
  const [rows] = await pool.query(`
    SELECT l.*, c.country_name 
    FROM locations l 
    LEFT JOIN country c ON l.country_id = c.country_id
    WHERE l.country_id = ?
  `, [country_id]);
  return rows;
};

const createLocation = async (country_id, location_name, location_id = null) => {
  let id = location_id;
  if (id == null || id === '') {
    const [maxRow] = await pool.query('SELECT COALESCE(MAX(location_id), 0) + 1 AS next_id FROM locations');
    id = maxRow[0].next_id;
  }
  await pool.query('INSERT INTO locations (location_id, country_id, location_name) VALUES (?, ?, ?)',
    [id, country_id, location_name]);
  return id;
};

const updateLocation = async (id, country_id, location_name) => {
  const [result] = await pool.query(
    'UPDATE locations SET country_id = ?, location_name = ? WHERE location_id = ?',
    [country_id, location_name, id]
  );
  return result.affectedRows > 0;
};

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

const getLocationDepartments = async (id) => {
  const [rows] = await pool.query(`
    SELECT d.* 
    FROM departments d
    INNER JOIN location_departments ld ON d.department_id = ld.department_id
    WHERE ld.location_id = ?
  `, [id]);
  return rows;
};

const addDepartmentToLocation = async (location_id, department_id) => {
  await pool.query('INSERT INTO location_departments (department_id, location_id) VALUES (?, ?)', [department_id, location_id]);
};

const removeDepartmentFromLocation = async (location_id, department_id) => {
  const [result] = await pool.query('DELETE FROM location_departments WHERE location_id = ? AND department_id = ?', [location_id, department_id]);
  return result.affectedRows > 0;
};

const getAllLocationDepartmentPairs = async () => {
  const [rows] = await pool.query(`
    SELECT ld.location_id, ld.department_id,
           l.location_name, l.country_id,
           c.country_name,
           d.department_name
    FROM location_departments ld
    INNER JOIN locations l ON ld.location_id = l.location_id
    LEFT JOIN country c ON l.country_id = c.country_id
    INNER JOIN departments d ON ld.department_id = d.department_id
    ORDER BY l.location_name, d.department_name
  `);
  return rows;
};

module.exports = {
  getAllLocations,
  getLocationById,
  getLocationsByCountryId,
  createLocation,
  updateLocation,
  deleteLocation,
  getLocationDepartments,
  addDepartmentToLocation,
  removeDepartmentFromLocation,
  getAllLocationDepartmentPairs
};

