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

/**
 * 1. 여러 건 등록 (트랜잭션 사용)
 */
const createBatch = async (reports) => {
  const db = await getDb();
  const conn = await db.getConnection();

  try {
    await conn.beginTransaction();

    const sql = `
      INSERT INTO WorkReports
        (\`date\`, user_id, project_id, task_id, overtime_hours, work_details, memo)
      VALUES (?, ?, ?, ?, ?, ?, ?)
    `;

    for (const rpt of reports) {
      const params = [
        rpt.date,
        rpt.user_id,
        rpt.project_id,
        rpt.task_id || null,
        rpt.overtime_hours || null,
        rpt.work_details || null,
        rpt.memo || null
      ];
      await conn.query(sql, params);
    }

    await conn.commit();
  } catch (err) {
    await conn.rollback();
    throw err;
  } finally {
    conn.release();
  }
};

/**
 * 2. 단일 등록
 */
const create = async (report) => {
  const db = await getDb();
  const {
    date, user_id, project_id,
    task_id, overtime_hours,
    work_details, memo
  } = report;

  const [result] = await db.query(
    `INSERT INTO WorkReports
     (\`date\`, user_id, project_id, task_id, overtime_hours, work_details, memo)
     VALUES (?, ?, ?, ?, ?, ?, ?)`,
    [
      date,
      user_id,
      project_id,
      task_id || null,
      overtime_hours || null,
      work_details || null,
      memo || null
    ]
  );

  return result.insertId;
};

/**
 * 3. 날짜별 조회
 */
const getAllByDate = async (date) => {
  const db = await getDb();
  const sql = `
    SELECT
      wr.user_id,
      wr.id,
      wr.\`date\`,
      w.worker_name,
      p.project_name,
      CONCAT(t.category, ':', t.subcategory) AS task_name,
      wr.overtime_hours,
      wr.work_details,
      wr.memo
    FROM WorkReports wr
    LEFT JOIN workers w  ON wr.user_id  = w.user_id
    LEFT JOIN projects p ON wr.project_id = p.project_id
    LEFT JOIN Tasks t    ON wr.task_id    = t.task_id
    WHERE wr.\`date\` = ?
    ORDER BY w.worker_name ASC
  `;
  const [rows] = await db.query(sql, [date]);
  return rows;
};

/**
 * 4. 기간 조회
 */
const getByRange = async (start, end) => {
  const db = await getDb();
  const [rows] = await db.query(
    `SELECT id, \`date\`, user_id, project_id, morning_task_id, afternoon_task_id, overtime_hours, overtime_task_id, work_details, note, memo, created_at, updated_at, morning_project_id, afternoon_project_id, overtime_project_id, task_id FROM WorkReports
     WHERE \`date\` BETWEEN ? AND ?
     ORDER BY \`date\` ASC`,
    [start, end]
  );
  return rows;
};

/**
 * 5. ID로 조회
 */
const getById = async (id) => {
  const db = await getDb();
  const [rows] = await db.query(
    `SELECT id, \`date\`, user_id, project_id, morning_task_id, afternoon_task_id, overtime_hours, overtime_task_id, work_details, note, memo, created_at, updated_at, morning_project_id, afternoon_project_id, overtime_project_id, task_id FROM WorkReports WHERE id = ?`,
    [id]
  );
  return rows[0];
};

/**
 * 6. 수정
 */
const update = async (id, report) => {
  const db = await getDb();
  const {
    date, user_id, project_id,
    task_id, overtime_hours,
    work_details, memo
  } = report;

  const [result] = await db.query(
    `UPDATE WorkReports
     SET \`date\`         = ?,
         user_id         = ?,
         project_id      = ?,
         task_id         = ?,
         overtime_hours  = ?,
         work_details    = ?,
         memo            = ?,
         updated_at      = CURRENT_TIMESTAMP
     WHERE id = ?`,
    [
      date,
      user_id,
      project_id,
      task_id || null,
      overtime_hours || null,
      work_details || null,
      memo || null,
      id
    ]
  );

  return result.affectedRows;
};

/**
 * 7. 삭제
 */
const remove = async (id) => {
  const db = await getDb();
  const [result] = await db.query(
    `DELETE FROM WorkReports WHERE id = ?`,
    [id]
  );
  return result.affectedRows;
};

/**
 * 8. 중복 확인
 */
const existsByDateAndWorker = async (date, user_id) => {
  const db = await getDb();
  const [rows] = await db.query(
    `SELECT 1 FROM WorkReports WHERE \`date\` = ? AND user_id = ? LIMIT 1`,
    [date, user_id]
  );
  return rows.length > 0;
};

module.exports = {
  create,
  createBatch,
  getAllByDate,
  getByRange,
  getById,
  update,
  remove,
  existsByDateAndWorker
};