const { getDb } = require('../dbPool');

// ==================== 카테고리(공장) 관련 ====================

const createCategory = async (category) => {
  const db = await getDb();
  const {
    category_name,
    description = null,
    display_order = 0,
    is_active = true
  } = category;

  const [result] = await db.query(
    `INSERT INTO workplace_categories
     (category_name, description, display_order, is_active)
     VALUES (?, ?, ?, ?)`,
    [category_name, description, display_order, is_active]
  );

  return result.insertId;
};

const getAllCategories = async () => {
  const db = await getDb();
  const [rows] = await db.query(
    `SELECT category_id, category_name, description, display_order, is_active, layout_image, created_at, updated_at
     FROM workplace_categories
     ORDER BY display_order ASC, category_id ASC`
  );
  return rows;
};

const getActiveCategories = async () => {
  const db = await getDb();
  const [rows] = await db.query(
    `SELECT category_id, category_name, description, display_order, is_active, layout_image, created_at, updated_at
     FROM workplace_categories
     WHERE is_active = TRUE
     ORDER BY display_order ASC, category_id ASC`
  );
  return rows;
};

const getCategoryById = async (categoryId) => {
  const db = await getDb();
  const [rows] = await db.query(
    `SELECT category_id, category_name, description, display_order, is_active, layout_image, created_at, updated_at
     FROM workplace_categories
     WHERE category_id = ?`,
    [categoryId]
  );
  return rows[0];
};

const updateCategory = async (categoryId, category) => {
  const db = await getDb();
  const {
    category_name,
    description,
    display_order,
    is_active,
    layout_image
  } = category;

  const [result] = await db.query(
    `UPDATE workplace_categories
     SET category_name = ?, description = ?, display_order = ?, is_active = ?, layout_image = ?, updated_at = NOW()
     WHERE category_id = ?`,
    [category_name, description, display_order, is_active, layout_image, categoryId]
  );

  return result;
};

const deleteCategory = async (categoryId) => {
  const db = await getDb();
  const [result] = await db.query(
    `DELETE FROM workplace_categories WHERE category_id = ?`,
    [categoryId]
  );
  return result;
};

// ==================== 작업장 관련 ====================

const createWorkplace = async (workplace) => {
  const db = await getDb();
  const {
    category_id = null,
    workplace_name,
    description = null,
    is_active = true,
    workplace_purpose = null,
    display_priority = 0
  } = workplace;

  const [result] = await db.query(
    `INSERT INTO workplaces
     (category_id, workplace_name, description, is_active, workplace_purpose, display_priority)
     VALUES (?, ?, ?, ?, ?, ?)`,
    [category_id, workplace_name, description, is_active, workplace_purpose, display_priority]
  );

  return result.insertId;
};

const getAllWorkplaces = async () => {
  const db = await getDb();
  const [rows] = await db.query(
    `SELECT w.workplace_id, w.category_id, w.workplace_name, w.description, w.is_active, w.workplace_purpose, w.display_priority,
            w.layout_image, w.created_at, w.updated_at,
            wc.category_name
     FROM workplaces w
     LEFT JOIN workplace_categories wc ON w.category_id = wc.category_id
     ORDER BY wc.display_order ASC, w.display_priority ASC, w.workplace_id DESC`
  );
  return rows;
};

const getActiveWorkplaces = async () => {
  const db = await getDb();
  const [rows] = await db.query(
    `SELECT w.workplace_id, w.category_id, w.workplace_name, w.description, w.is_active, w.workplace_purpose, w.display_priority,
            w.layout_image, w.created_at, w.updated_at,
            wc.category_name
     FROM workplaces w
     LEFT JOIN workplace_categories wc ON w.category_id = wc.category_id
     WHERE w.is_active = TRUE
     ORDER BY wc.display_order ASC, w.workplace_id DESC`
  );
  return rows;
};

const getWorkplacesByCategory = async (categoryId) => {
  const db = await getDb();
  const [rows] = await db.query(
    `SELECT w.workplace_id, w.category_id, w.workplace_name, w.description, w.is_active, w.workplace_purpose, w.display_priority,
            w.layout_image, w.created_at, w.updated_at,
            wc.category_name
     FROM workplaces w
     LEFT JOIN workplace_categories wc ON w.category_id = wc.category_id
     WHERE w.category_id = ?
     ORDER BY w.workplace_id DESC`,
    [categoryId]
  );
  return rows;
};

const getWorkplaceById = async (workplaceId) => {
  const db = await getDb();
  const [rows] = await db.query(
    `SELECT w.workplace_id, w.category_id, w.workplace_name, w.description, w.is_active, w.workplace_purpose, w.display_priority,
            w.layout_image, w.created_at, w.updated_at,
            wc.category_name
     FROM workplaces w
     LEFT JOIN workplace_categories wc ON w.category_id = wc.category_id
     WHERE w.workplace_id = ?`,
    [workplaceId]
  );
  return rows[0];
};

const updateWorkplace = async (workplaceId, workplace) => {
  const db = await getDb();
  const {
    category_id,
    workplace_name,
    description,
    is_active,
    workplace_purpose,
    display_priority,
    layout_image
  } = workplace;

  const [result] = await db.query(
    `UPDATE workplaces
     SET category_id = ?, workplace_name = ?, description = ?, is_active = ?,
         workplace_purpose = ?, display_priority = ?, layout_image = ?, updated_at = NOW()
     WHERE workplace_id = ?`,
    [category_id, workplace_name, description, is_active, workplace_purpose, display_priority, layout_image, workplaceId]
  );

  return result;
};

const deleteWorkplace = async (workplaceId) => {
  const db = await getDb();
  const [result] = await db.query(
    `DELETE FROM workplaces WHERE workplace_id = ?`,
    [workplaceId]
  );
  return result;
};

// ==================== 작업장 지도 영역 관련 ====================

const createMapRegion = async (region) => {
  const db = await getDb();
  const {
    workplace_id,
    category_id,
    x_start,
    y_start,
    x_end,
    y_end,
    shape = 'rect',
    polygon_points = null
  } = region;

  const [result] = await db.query(
    `INSERT INTO workplace_map_regions
     (workplace_id, category_id, x_start, y_start, x_end, y_end, shape, polygon_points)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
    [workplace_id, category_id, x_start, y_start, x_end, y_end, shape, polygon_points]
  );

  return result.insertId;
};

const getMapRegionsByCategory = async (categoryId) => {
  const db = await getDb();
  const [rows] = await db.query(
    `SELECT mr.*, w.workplace_name, w.description
     FROM workplace_map_regions mr
     INNER JOIN workplaces w ON mr.workplace_id = w.workplace_id
     WHERE mr.category_id = ? AND w.is_active = TRUE
     ORDER BY mr.region_id ASC`,
    [categoryId]
  );
  return rows;
};

const getMapRegionByWorkplace = async (workplaceId) => {
  const db = await getDb();
  const [rows] = await db.query(
    `SELECT * FROM workplace_map_regions WHERE workplace_id = ?`,
    [workplaceId]
  );
  return rows[0];
};

const updateMapRegion = async (regionId, region) => {
  const db = await getDb();
  const {
    x_start,
    y_start,
    x_end,
    y_end,
    shape,
    polygon_points
  } = region;

  const [result] = await db.query(
    `UPDATE workplace_map_regions
     SET x_start = ?, y_start = ?, x_end = ?, y_end = ?, shape = ?, polygon_points = ?, updated_at = NOW()
     WHERE region_id = ?`,
    [x_start, y_start, x_end, y_end, shape, polygon_points, regionId]
  );

  return result;
};

const deleteMapRegion = async (regionId) => {
  const db = await getDb();
  const [result] = await db.query(
    `DELETE FROM workplace_map_regions WHERE region_id = ?`,
    [regionId]
  );
  return result;
};

const deleteMapRegionsByCategory = async (categoryId) => {
  const db = await getDb();
  const [result] = await db.query(
    `DELETE FROM workplace_map_regions WHERE category_id = ?`,
    [categoryId]
  );
  return result;
};

module.exports = {
  createCategory,
  getAllCategories,
  getActiveCategories,
  getCategoryById,
  updateCategory,
  deleteCategory,
  createWorkplace,
  getAllWorkplaces,
  getActiveWorkplaces,
  getWorkplacesByCategory,
  getWorkplaceById,
  updateWorkplace,
  deleteWorkplace,
  createMapRegion,
  getMapRegionsByCategory,
  getMapRegionByWorkplace,
  updateMapRegion,
  deleteMapRegion,
  deleteMapRegionsByCategory
};