const Department = require("../../models/admin/departmentModal");
const { errorResponse } = require("../../utils/errorResponseHandler");
const { logUserAction } = require("../../utils/userActionLogger");
const {pool} = require("../../config/db")

// Create Department
// const createDept = async (req, res) => {
//   try {
//     const { deptName, orgId } = req.body;

//     if (!deptName) return errorResponse(res, "Department name is required", 400);
//     if (!orgId) return errorResponse(res, "Organization ID is required", 400);

//     const userId = req.user?._id;

//     // Check if department with same name exists in the org
//     const existingDept = await Department.findOne({ deptName, orgId });
//     if (existingDept) {
//       return errorResponse(res, "Department name already exists in this organization", 409); // 409 = Conflict
//     }

//     const dept = await Department.create({
//       deptName,
//       orgId,
//       // createdBy: userId || null,
//       createdBy: req.session.user.id,
//       updatedBy: null,
//       updatedAt: null, 
//     });

//     // Log user action
//     await logUserAction({
//       userId,
//       orgId,
//       action: "CREATE",
//       model: "Department",
//       details: { deptId: dept._id, deptName },
//       req,
//     });

//     res.status(201).json({ success: true, data: dept });
//   } catch (error) {
//     return errorResponse(res, "Failed to create department", 400, error);
//   }
// };
const createDept = async (req, res) => {
  const client = await pool.connect();

  try {
    const { deptName } = req.body;

    if (!deptName) {
      return errorResponse(res, "Department name is required", 400);
    }

    const userId = req.session?.user?.id || null;
    const orgId = req.session.user.orgId;

    /* ===== 1. CHECK EXISTING ===== */
    const checkQuery = `
      SELECT id FROM departments 
      WHERE LOWER(dept_name) = LOWER($1)
      ${orgId ? "AND org_id = $2" : ""}
      LIMIT 1
    `;

    const checkValues = orgId
      ? [deptName, Number(orgId)]
      : [deptName];

    const existingDept = await client.query(checkQuery, checkValues);

    if (existingDept.rowCount > 0) {
      return errorResponse(
        res,
        "Department name already exists in this organization",
        409
      );
    }

    /* ===== 2. INSERT ===== */
    const insertQuery = `
      INSERT INTO departments (
        dept_name,
        org_id,
        created_by,
        updated_by,
        created_at
      )
      VALUES ($1, $2, $3, $4, NOW())
      RETURNING *
    `;

    const values = [
      deptName,
      orgId && !isNaN(Number(orgId)) ? Number(orgId) : null, // optional
      userId,
      null,
    ];

    const result = await client.query(insertQuery, values);
    const dept = result.rows[0];

    /* ===== 3. LOG ===== */
    await logUserAction({
      userId,
      orgId: dept.org_id || null,
      action: "CREATE",
      model: "Department",
      details: { deptId: dept.id, deptName },
      req,
    });

    /* ===== 4. RESPONSE ===== */
    return res.status(201).json({
      success: true,
      data: dept,
    });
  } catch (error) {
    console.error("Create dept error:", error);
    return errorResponse(res, "Failed to create department", 500);
  } finally {
    client.release();
  }
};

// Get All Departments with Pagination (exclude soft deleted)
// const getDepts = async (req, res) => {
//   try {
//     let { page = 1, limit = 10 } = req.query;
//     page = parseInt(page);
//     limit = parseInt(limit);
//     const skip = (page - 1) * limit;

//     const query = { isDeleted: false };

//     const [depts, total] = await Promise.all([
//       Department.find(query)
//         .populate("createdBy", "firstName lastName email")
//         .populate("updatedBy", "firstName lastName email")
//         .populate("orgId", "orgName")
//         .skip(skip)
//         .limit(limit)
//         .sort({ createdAt: -1 }),
//       Department.countDocuments(query),
//     ]);

//     // Log user action
//     await logUserAction({
//       userId: req.user?._id,
//       orgId: req.user?.orgId || null,
//       action: "READ",
//       model: "Department",
//       details: { page, limit, count: depts.length },
//       req,
//     });

//     res.status(200).json({
//       success: true,
//       page,
//       totalPages: Math.ceil(total / limit),
//       totalRecords: total,
//       data: depts,
//     });
//   } catch (error) {
//     return errorResponse(res, "Failed to fetch departments", 500, error);
//   }
// };

const getDepts = async (req, res) => {
  try {
    let { page = 1, limit = 10 } = req.query;

    page = parseInt(page) || 1;
    limit = parseInt(limit) || 10;

    const offset = (page - 1) * limit;

    //  Main Query (LEFT JOIN because optional fields)
    const dataQuery = `
      SELECT 
        d.id,
        d.dept_name,
        d.status,
        d.org_id,
        o.org_name,

        d.created_by,
        cb.first_name AS created_by_first_name,
        cb.last_name AS created_by_last_name,
        cb.email AS created_by_email,

        d.updated_by,
        ub.first_name AS updated_by_first_name,
        ub.last_name AS updated_by_last_name,
        ub.email AS updated_by_email,

        d.created_at,
        d.updated_at
      FROM departments d
      LEFT JOIN organizations o ON d.org_id = o.id
      LEFT JOIN users cb ON d.created_by = cb.id
      LEFT JOIN users ub ON d.updated_by = ub.id
      WHERE d.is_deleted = false
      ORDER BY d.created_at DESC
      LIMIT $1 OFFSET $2
    `;

    const countQuery = `
      SELECT COUNT(*) FROM departments
      WHERE is_deleted = false
    `;

    const [dataResult, countResult] = await Promise.all([
      pool.query(dataQuery, [limit, offset]),
      pool.query(countQuery),
    ]);

    const total = parseInt(countResult.rows[0].count);

    //  Format response (like Mongo populate)
    const depts = dataResult.rows.map((row) => ({
      id: row.id,
      deptName: row.dept_name,
      status : row.status,

      org: row.org_id
        ? {
            id: row.org_id,
            orgName: row.org_name,
          }
        : null,

      createdBy: row.created_by
        ? {
            id: row.created_by,
            firstName: row.created_by_first_name,
            lastName: row.created_by_last_name,
            email: row.created_by_email,
          }
        : null,

      updatedBy: row.updated_by
        ? {
            id: row.updated_by,
            firstName: row.updated_by_first_name,
            lastName: row.updated_by_last_name,
            email: row.updated_by_email,
          }
        : null,

      createdAt: row.created_at,
      updatedAt: row.updated_at,
    }));

    //  Logging (optional safe handling)
    await logUserAction({
      userId: req.user?.id || null,
      orgId: req.user?.orgId || null,
      action: "READ",
      model: "Department",
      details: { page, limit, count: depts.length },
      req,
    });

    return res.status(200).json({
      success: true,
      page,
      totalPages: Math.ceil(total / limit),
      totalRecords: total,
      data: depts,
    });
  } catch (error) {
    console.error("GET DEPTS ERROR:", error);
    return errorResponse(res, "Failed to fetch departments", 500, error);
  }
};

// Get Department by ID
const getDeptById = async (req, res) => {
  try {
    const dept = await Department.findById(req.params.id)
      .populate("createdBy", "firstName lastName email")
      .populate("updatedBy", "firstName lastName email")
      .populate("orgId", "orgName");

    if (!dept) return errorResponse(res, "Department not found", 404);

    // Log user action
    await logUserAction({
      userId: req.session.user?._id,
      orgId: dept.orgId?._id || null,
      action: "READ",
      model: "Department",
      details: { deptId: dept._id },
      req,
    });

    res.status(200).json({ success: true, data: dept });
  } catch (error) {
    return errorResponse(res, "Error fetching department", 500, error);
  }
};

// Update Department
// const updateDept = async (req, res) => {
//   try {
//     const { status, deptName } = req.body; // Removed orgId
//     const userId = req.user?._id;

//     //  updatedAt: new Date()
//     const updateData = { updatedBy: userId || null, updatedAt: new Date(),  };
//     if (status !== undefined) updateData.status = status;
//     if (deptName) updateData.deptName = deptName;

//     const dept = await Department.findByIdAndUpdate(
//       req.params.id,
//       updateData,
//       { new: true, runValidators: true }
//     )
//       .populate("createdBy", "firstName lastName email")
//       .populate("updatedBy", "firstName lastName email")
//       .populate("orgId", "orgName");

//     if (!dept) return errorResponse(res, "Department not found", 404);

//     // Log user action
//     await logUserAction({
//       userId,
//       orgId: dept.orgId?._id || null,
//       action: "UPDATE",
//       model: "Department",
//       details: { deptId: dept._id, updatedFields: updateData },
//       req,
//     });

//     res.status(200).json({ success: true, data: dept });
//   } catch (error) {
//     return errorResponse(res, "Failed to update department", 400, error);
//   }
// };


// const updateDept = async (req, res) => {
//   try {
//     const { status, deptName } = req.body;
//     // const userId = req.user?._id;
//     console.log("SESSION:", req.session);
//     const userId = req.session?.user?.id;

// if (!userId) {
//   return errorResponse(res, "Unauthorized: Session expired or missing", 401);
// }

//     const deptId = req.params.id;

//     //  DUPLICATE CHECK (ONLY IF deptName IS SENT)
//     if (deptName) {
//       const existingDept = await Department.findOne({
//         deptName: deptName.trim(),
//         // orgId: req.session.user.orgId,
//         _id: { $ne: deptId }, //  exclude current department
//         isDeleted: false,
//       });

//       if (existingDept) {
//         return errorResponse(
//           res,
//           "Department name already exists. Cannot update.",
//           409
//         );
//       }
//     }

//     const updateData = {
//       updatedBy: userId || null,
//       updatedAt: new Date(),
//     };

//     if (status !== undefined) updateData.status = status;
//     if (deptName) updateData.deptName = deptName.trim();

//     const dept = await Department.findByIdAndUpdate(
//       deptId,
//       updateData,
//       { new: true, runValidators: true }
//     )
//       .populate("createdBy", "firstName lastName email")
//       .populate("updatedBy", "firstName lastName email")
//       .populate("orgId", "orgName");

//     if (!dept) return errorResponse(res, "Department not found", 404);

//     await logUserAction({
//       userId :req.session.user.id,
//       orgId: dept.orgId?._id || null,
//       action: "UPDATE",
//       model: "Department",
//       details: { deptId: dept._id, updatedFields: updateData },
//       req,
//     });

//     return res.status(200).json({
//       success: true,
//       message: "Department updated successfully",
//       data: dept,
//     });
//   } catch (error) {
//     console.error("Update department error:", error);
//     return errorResponse(res, "Failed to update department", 500, error);
//   }
// };


const updateDept = async (req, res) => {
  try {
    const { status, deptName } = req.body;
    const deptId = Number(req.params.id);

    // 🔐 Session check
    const userId = req.session?.user?.id;
    if (!userId) {
      return errorResponse(res, "Unauthorized: Session expired or missing", 401);
    }

    // ❌ Invalid ID check
    if (!deptId) {
      return errorResponse(res, "Invalid department ID", 400);
    }

    // 🔍 Check if department exists
    const existingDeptQuery = `
      SELECT id FROM departments
      WHERE id = $1 AND is_deleted = false
    `;
    const existingDept = await pool.query(existingDeptQuery, [deptId]);

    if (existingDept.rowCount === 0) {
      return errorResponse(res, "Department not found", 404);
    }

    // 🔁 Duplicate check (if deptName provided)
    if (deptName) {
      const duplicateQuery = `
        SELECT id FROM departments
        WHERE LOWER(dept_name) = LOWER($1)
        AND id != $2
        AND is_deleted = false
      `;
      const duplicate = await pool.query(duplicateQuery, [
        deptName.trim(),
        deptId,
      ]);

      if (duplicate.rowCount > 0) {
        return errorResponse(
          res,
          "Department name already exists. Cannot update.",
          409
        );
      }
    }

    // 🛠️ Dynamic update fields
    const fields = [];
    const values = [];
    let index = 1;

    if (status !== undefined) {
      fields.push(`status = $${index++}`);
      values.push(status);
    }

    if (deptName) {
      fields.push(`dept_name = $${index++}`);
      values.push(deptName.trim());
    }

    // always update audit fields
    fields.push(`updated_by = $${index++}`);
    values.push(userId);

    fields.push(`updated_at = NOW()`);

    values.push(deptId);

    const updateQuery = `
      UPDATE departments
      SET ${fields.join(", ")}
      WHERE id = $${index}
      RETURNING *;
    `;

    const result = await pool.query(updateQuery, values);
    const dept = result.rows[0];

    // 📘 Fetch related data (like populate)
    const finalQuery = `
      SELECT 
        d.id,
        d.dept_name,
        d.status,
        d.created_at,
        d.updated_at,

        o.id AS org_id,
        o.org_name,

        cb.id AS created_by,
        cb.first_name,
        cb.last_name,
        cb.email,

        ub.id AS updated_by,
        ub.first_name AS updated_first_name,
        ub.last_name AS updated_last_name,
        ub.email AS updated_email

      FROM departments d
      LEFT JOIN organizations o ON d.org_id = o.id
      LEFT JOIN users cb ON d.created_by = cb.id
      LEFT JOIN users ub ON d.updated_by = ub.id
      WHERE d.id = $1
    `;

    const finalResult = await pool.query(finalQuery, [deptId]);
    const row = finalResult.rows[0];

    // 🎯 Format response (Mongo-like)
    const formatted = {
      id: row.id,
      deptName: row.dept_name,
      status: row.status,

      org: row.org_id
        ? {
            id: row.org_id,
            orgName: row.org_name,
          }
        : null,

      createdBy: row.created_by
        ? {
            id: row.created_by,
            firstName: row.first_name,
            lastName: row.last_name,
            email: row.email,
          }
        : null,

      updatedBy: row.updated_by
        ? {
            id: row.updated_by,
            firstName: row.updated_first_name,
            lastName: row.updated_last_name,
            email: row.updated_email,
          }
        : null,

      createdAt: row.created_at,
      updatedAt: row.updated_at,
    };

    // 🧾 Log action
    await logUserAction({
      userId: userId,
      orgId: row.org_id || null,
      action: "UPDATE",
      model: "Department",
      details: { deptId, updatedFields: req.body },
      req,
    });

    return res.status(200).json({
      success: true,
      message: "Department updated successfully",
      data: formatted,
    });
  } catch (error) {
    console.error("UPDATE DEPT ERROR:", error);
    return errorResponse(res, "Failed to update department", 500, error);
  }
};



// Soft Delete Department
// const deleteDept = async (req, res) => {
//   try {
//     const userId = req.user?._id;

//     const dept = await Department.findByIdAndUpdate(
//       req.params.id,
//       { isDeleted: true, updatedBy: userId || null },
//       { new: true }
//     );

//     if (!dept) return errorResponse(res, "Department not found", 404);

//     // Log user action
//     await logUserAction({
//       userId,
//       orgId: dept.orgId?._id || null,
//       action: "DELETE",
//       model: "Department",
//       details: { deptId: dept._id },
//       req,
//     });

//     res.status(200).json({
//       success: true,
//       message: "Department deleted successfully",
//       data: dept,
//     });
//   } catch (error) {
//     return errorResponse(res, "Failed to delete department", 500, error);
//   }
// };

const deleteDept = async (req, res) => {
  const client = await pool.connect();

  try {
    const userId = req.session.user?.id || null; // assuming integer user id
    const deptId = parseInt(req.params.id, 10);

    if (!deptId) {
      return errorResponse(res, "Invalid department ID", 400);
    }

    await client.query("BEGIN");

    // Soft delete department
    const result = await client.query(
      `
      UPDATE departments
      SET is_deleted = TRUE,
          updated_by = $1,
          updated_at = NOW()
      WHERE id = $2 AND is_deleted = FALSE
      RETURNING *;
      `,
      [userId, deptId]
    );

    if (result.rowCount === 0) {
      await client.query("ROLLBACK");
      return errorResponse(res, "Department not found", 404);
    }

    const dept = result.rows[0];

    // Log user action
    await logUserAction({
      userId,
      orgId: dept.org_id || null,
      action: "DELETE",
      model: "Department",
      details: { deptId: dept.id },
      req,
    });

    await client.query("COMMIT");

    return res.status(200).json({
      success: true,
      message: "Department deleted successfully",
      data: dept,
    });
  } catch (error) {
    await client.query("ROLLBACK");
    return errorResponse(res, "Failed to delete department", 500, error);
  } finally {
    client.release();
  }
};

module.exports = {
  createDept,
  getDepts,
  getDeptById,
  updateDept,
  deleteDept,
};
