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

// Create Designation
// const createDesignation = async (req, res) => {
//   try {
//     const { designationName, departmentId, orgId } = req.body;

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

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

//     // Check if designation with same name exists in the same department & org
//     const existingDesignation = await Designation.findOne({
//       designationName,
//       departmentId,
//       orgId,
//     });

//     if (existingDesignation) {
//       return errorResponse(
//         res,
//         "Designation name already exists in this department",
//         409,
//       ); // 409 = Conflict
//     }

//     const designation = await Designation.create({
//       designationName,
//       departmentId,
//       orgId,
//       createdBy: userId || null,
//       updatedBy: null,
//     });

//     // Log user action
//     await logUserAction({
//       userId,
//       orgId,
//       action: "CREATE",
//       model: "Designation",
//       details: { designationId: designation._id, designationName },
//       req,
//     });

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

  try {
    const { designationName, departmentId } = req.body;
    const userId = req.session?.user?.id || null;
    const orgId = req.session?.user.orgId || null;

    // const orgId = req.session?.user?.orgId;

    if (!orgId) {
      return errorResponse(res, "Organization ID missing in session", 400);
    }

    //  Validations
    if (!designationName)
      return errorResponse(res, "Designation name is required", 400);

    if (!departmentId || isNaN(parseInt(departmentId)))
      return errorResponse(res, "Valid Department ID is required", 400);

    // if (!orgId || isNaN(parseInt(orgId)))
    //   return errorResponse(res, "Valid Organization ID is required", 400);

    await client.query("BEGIN");

    //  Check duplicate (same name + department + org)
    const existing = await client.query(
      `
      SELECT id FROM designations
      WHERE designation_name = $1
        AND department_id = $2
        AND org_id = $3
        AND is_deleted = FALSE
      LIMIT 1;
      `,
      [designationName.trim(), departmentId, orgId],
    );

    if (existing.rowCount > 0) {
      await client.query("ROLLBACK");
      return errorResponse(
        res,
        "Designation already exists in this department",
        409,
      );
    }

    //  Insert designation
    const result = await client.query(
      `
      INSERT INTO designations
        (designation_name, department_id, org_id, status, is_deleted, created_by, updated_by, created_at)
      VALUES
        ($1, $2, $3, TRUE, FALSE, $4, NULL, NOW())
      RETURNING *;
      `,
      [designationName.trim(), departmentId, orgId, userId],
    );

    const designation = result.rows[0];

    //  Log action
    await logUserAction({
      userId,
      orgId,
      action: "CREATE",
      model: "Designation",
      details: {
        designationId: designation.id,
        designationName: designation.designation_name,
      },
      req,
    });

    await client.query("COMMIT");

    return res.status(201).json({
      success: true,
      message: "Designation created successfully",
      data: designation,
    });
  } catch (error) {
    await client.query("ROLLBACK");
    return errorResponse(res, "Failed to create designation", 500, error);
  } finally {
    client.release();
  }
};

// Get All Designations with Pagination (exclude soft deleted)
// const getDesignations = 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 [designations, total] = await Promise.all([
//       Designation.find(query)
//         .populate("createdBy", "firstName lastName email")
//         .populate("updatedBy", "firstName lastName email")
//         .populate("departmentId", "deptName")
//         .populate("orgId", "orgName")
//         .skip(skip)
//         .limit(limit)
//         .sort({ createdAt: -1 }),
//       Designation.countDocuments(query),
//     ]);

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

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

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

  try {
    let { page = 1, limit = 10 } = req.query;

    page = parseInt(page, 10);
    limit = parseInt(limit, 10);

    if (isNaN(page) || page < 1) page = 1;
    if (isNaN(limit) || limit < 1) limit = 10;

    const offset = (page - 1) * limit;

    //  Main Query (with JOINs like populate)
    const dataQuery = `
      SELECT 
        d.id,
        d.designation_name AS "designationName",
        d.status,
        d.created_at AS "createdAt",
        d.updated_at AS "updatedAt",

        -- Department
        json_build_object(
          'id', dept.id,
          'deptName', dept.dept_name
        ) AS "departmentId",

        -- Organization
        json_build_object(
          'id', org.id,
          'orgName', org.org_name
        ) AS "orgId",

        -- Created By
        json_build_object(
          'id', cu.id,
          'firstName', cu.first_name,
          'lastName', cu.last_name,
          'email', cu.email
        ) AS "createdBy",

        -- Updated By
        json_build_object(
          'id', uu.id,
          'firstName', uu.first_name,
          'lastName', uu.last_name,
          'email', uu.email
        ) AS "updatedBy"

      FROM designations d
      LEFT JOIN departments dept ON d.department_id = dept.id
      LEFT JOIN organizations org ON d.org_id = org.id
      LEFT JOIN users cu ON d.created_by = cu.id
      LEFT JOIN users uu ON d.updated_by = uu.id

      WHERE d.is_deleted = FALSE

      ORDER BY d.created_at DESC
      LIMIT $1 OFFSET $2;
    `;

    //  Count Query
    const countQuery = `
      SELECT COUNT(*) FROM designations WHERE is_deleted = FALSE;
    `;

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

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

    //  Log action
    await logUserAction({
      userId: req.session.user?.id || null,
      orgId: req.user?.orgId || null,
      action: "READ",
      model: "Designation",
      details: { page, limit, count: dataResult.rows.length },
      req,
    });

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

// Get Designation by ID
const getDesignationById = async (req, res) => {
  try {
    const designation = await Designation.findById(req.params.id)
      .populate("createdBy", "firstName lastName email")
      .populate("updatedBy", "firstName lastName email")
      .populate("departmentId", "deptName")
      .populate("orgId", "orgName");

    if (!designation) return errorResponse(res, "Designation not found", 404);

    // Log user action
    await logUserAction({
      userId: req.user?._id,
      orgId: designation.orgId?._id || null,
      action: "READ",
      model: "Designation",
      details: { designationId: designation._id },
      req,
    });

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

// const updateDesignation = async (req, res) => {
//   try {
//     const { status, designationName, departmentId, orgId } = req.body;
//     const userId = req.user?._id;

//     // 🔹 Fetch existing designation (SOURCE OF TRUTH)
//     const existing = await Designation.findById(req.params.id);
//     if (!existing) {
//       return errorResponse(res, "Designation not found", 404);
//     }

//     const finalDepartmentId = departmentId || existing.departmentId;
//     const finalOrgId = orgId || existing.orgId;

//     // 🔴 DUPLICATE CHECK (same designation + same department + same org)
//     if (designationName) {
//       const duplicate = await Designation.findOne({
//         _id: { $ne: req.params.id },
//         designationName: {
//           $regex: `^${designationName.trim()}$`,
//           $options: "i",
//         },
//         departmentId: finalDepartmentId,
//         orgId: finalOrgId,
//         isDeleted: false,
//       });

//       if (duplicate) {
//         return errorResponse(
//           res,
//           "Designation already exists in this department",
//           409,
//         );
//       }
//     }

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

//     if (status !== undefined) updateData.status = status;
//     if (designationName) updateData.designationName = designationName.trim();
//     if (departmentId) updateData.departmentId = departmentId;
//     if (orgId) updateData.orgId = orgId;

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

//     await logUserAction({
//       userId,
//       orgId: finalOrgId,
//       action: "UPDATE",
//       model: "Designation",
//       details: { designationId: designation._id, updatedFields: updateData },
//       req,
//     });

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

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

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

//     if (!designation) return errorResponse(res, "Designation not found", 404);

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

//     res.status(200).json({
//       success: true,
//       message: "Designation deleted successfully",
//       data: designation,
//     });
//   } catch (error) {
//     return errorResponse(res, "Failed to delete designation", 500, error);
//   }
// };
const updateDesignation = async (req, res) => {
  const client = await pool.connect();

  try {
    const { status, designationName, departmentId, } = req.body;
    const userId = req.session.user?.id || null;
    const orgId = req.session?.user?.orgId;;
    const designationId = parseInt(req.params.id, 10);

    if (!designationId) {
      return errorResponse(res, "Invalid designation ID", 400);
    }

    await client.query("BEGIN");

    // 🔹 1. Get existing designation (SOURCE OF TRUTH)
    const existingRes = await client.query(
      `SELECT * FROM designations WHERE id = $1 AND is_deleted = FALSE`,
      [designationId],
    );

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

    const existing = existingRes.rows[0];

    const finalDepartmentId = departmentId || existing.department_id;
    const finalOrgId = orgId || existing.org_id;

    // 2. Duplicate check
    if (designationName) {
      const duplicateRes = await client.query(
        `
        SELECT id FROM designations
        WHERE id != $1
          AND LOWER(designation_name) = LOWER($2)
          AND department_id = $3
          AND org_id = $4
          AND is_deleted = FALSE
        LIMIT 1;
        `,
        [designationId, designationName.trim(), finalDepartmentId, finalOrgId],
      );

      if (duplicateRes.rowCount > 0) {
        await client.query("ROLLBACK");
        return errorResponse(
          res,
          "Designation already exists in this department",
          409,
        );
      }
    }

    // 3. Build dynamic update query
    const fields = [];
    const values = [];
    let index = 1;

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

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

    if (departmentId) {
      fields.push(`department_id = $${index++}`);
      values.push(departmentId);
    }

    if (orgId) {
      fields.push(`org_id = $${index++}`);
      values.push(orgId);
    }

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

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

    // 🔹 4. Update query
    const updateQuery = `
      UPDATE designations
      SET ${fields.join(", ")}
      WHERE id = $${index} AND is_deleted = FALSE
      RETURNING *;
    `;

    values.push(designationId);

    const updateRes = await client.query(updateQuery, values);

    const updated = updateRes.rows[0];

    //  5. Fetch with JOIN (like populate)
    const finalRes = await client.query(
      `
      SELECT 
        d.id,
        d.designation_name AS "designationName",
        d.status,
        d.created_at AS "createdAt",
        d.updated_at AS "updatedAt",

        json_build_object(
          'id', dept.id,
          'deptName', dept.dept_name
        ) AS "departmentId",

        json_build_object(
          'id', org.id,
          'orgName', org.org_name
        ) AS "orgId",

        json_build_object(
          'id', cu.id,
          'firstName', cu.first_name,
          'lastName', cu.last_name,
          'email', cu.email
        ) AS "createdBy",

        json_build_object(
          'id', uu.id,
          'firstName', uu.first_name,
          'lastName', uu.last_name,
          'email', uu.email
        ) AS "updatedBy"

      FROM designations d
      LEFT JOIN departments dept ON d.department_id = dept.id
      LEFT JOIN organizations org ON d.org_id = org.id
      LEFT JOIN users cu ON d.created_by = cu.id
      LEFT JOIN users uu ON d.updated_by = uu.id

      WHERE d.id = $1;
      `,
      [designationId],
    );

    await client.query("COMMIT");

    //  Log
    await logUserAction({
      userId,
      orgId: finalOrgId,
      action: "UPDATE",
      model: "Designation",
      details: {
        designationId: designationId,
        updatedFields: req.body,
      },
      req,
    });

    return res.status(200).json({
      success: true,
      data: finalRes.rows[0],
    });
  } catch (error) {
    await client.query("ROLLBACK");
    return errorResponse(res, "Failed to update designation", 500, error);
  } finally {
    client.release();
  }
};

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

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

    if (!designationId) {
      return errorResponse(res, "Invalid designation ID", 400);
    }

    await client.query("BEGIN");

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

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

    const designation = result.rows[0];

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

    await client.query("COMMIT");

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

// const getDesignationsByDepartment = async (req, res) => {
//   try {
//     const { departmentId } = req.params;

//     if (!departmentId) {
//       return errorResponse(res, "Department id required", 400);
//     }

//     const list = await Designation.find({
//       departmentId,
//       isDeleted: false,
//       // status: "ACTIVE",
//     })
//       .select("_id designationName departmentId")
//       .sort({ designationName: 1 });

//     return res.status(200).json({
//       success: true,
//       count: list.length,
//       data: list,
//     });
//   } catch (error) {
//     return errorResponse(
//       res,
//       "Failed to fetch designations by department",
//       500,
//       error
//     );
//   }
// };

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

  try {
    const { departmentId } = req.params;

    if (!departmentId || isNaN(departmentId)) {
      return res.status(400).json({
        success: false,
        message: "Valid department id is required",
      });
    }

    const query = `
      SELECT 
        id, 
        designation_name AS "designationName", 
        department_id AS "departmentId"
      FROM designations
      WHERE department_id = $1
        AND is_deleted = FALSE
      ORDER BY designation_name ASC
    `;

    const { rows } = await client.query(query, [departmentId]);

    return res.status(200).json({
      success: true,
      count: rows.length,
      data: rows,
    });
  } catch (error) {
    console.error("Error fetching designations:", error);

    return res.status(500).json({
      success: false,
      message: "Failed to fetch designations by department",
    });
  } finally {
    client.release();
  }
};

module.exports = {
  createDesignation,
  getDesignations,
  getDesignationById,
  updateDesignation,
  deleteDesignation,
  getDesignationsByDepartment,
};
