const ReturnableGoods = require("../../models/securityops/goodsModel");
const { logUserAction } = require("../../utils/userActionLogger");
const { errorResponse } = require("../../utils/errorResponseHandler");
const User = require("../../models/userModel");
const {pool} = require("../../config/db")



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

  try {
    const userId = req.user.id;     
    const orgId = req.user.orgId;   

    /* ===== GET NEXT SEQUENCE ===== */
    const seqResult = await client.query(
      `SELECT sequence_number 
       FROM returnable_goods
       WHERE org_id = $1
       ORDER BY sequence_number DESC
       LIMIT 1`,
      [orgId]
    );

    const lastSeq = seqResult.rows[0]?.sequence_number || 0;
    const nextSeq = lastSeq + 1;

    const itemId = `OUT-${String(nextSeq).padStart(3, "0")}`;

    /* ===== BODY ===== */
    const {
      itemName,
      quantity,
      condition,
      remarks,
      pickupDate,
      returnDate,
      referenceBy,
      vendorName,
      receiverName,
      receiverPhone,
    } = req.body;

    /* ===== VALIDATION ===== */
    if (!itemName) {
      return res.status(400).json({
        success: false,
        message: "Item name is required",
      });
    }

    if (returnDate && pickupDate && new Date(returnDate) < new Date(pickupDate)) {
      return res.status(400).json({
        success: false,
        message: "Return date cannot be before pickup date",
      });
    }

    /* ===== FILE ===== */
    let itemPhoto = null;

    if (req.file) {
      itemPhoto = req.file.path
        .replace(/\\/g, "/")
        .replace(/^.*uploads/, "uploads");
    }

    /* ===== INSERT ===== */
    const result = await client.query(
      `INSERT INTO returnable_goods (
        item_id,
        sequence_number,
        item_name,
        quantity,
        condition,
        remarks,
        pickup_date,
        return_date,
        reference_by,
        vendor_name,
        receiver_name,
        receiver_phone,
        item_photo,
        org_id,
        created_by
      )
      VALUES (
        $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15
      )
      RETURNING *`,
      [
        itemId,
        nextSeq,
        itemName,
        quantity || null,
        condition || "Good",
        remarks || "",
        pickupDate || null,
        returnDate || null,
        referenceBy ? Number(referenceBy) : null,
        vendorName,
        receiverName,
        receiverPhone || "",
        itemPhoto,
        orgId,
        userId,
      ]
    );

    /* ===== LOG ACTION (optional) ===== */
    // await client.query(
    //   `INSERT INTO user_action_logs (user_id, action, model, document_id, details)
    //    VALUES ($1,$2,$3,$4,$5)`,
    //   [
    //     userId,
    //     "CREATE",
    //     "returnable_goods",
    //     result.rows[0].id,
    //     JSON.stringify({ itemId }),
    //   ]
    // );

    return res.status(201).json({
      success: true,
      message: "Returnable goods created successfully",
      data: result.rows[0],
    });

  } catch (err) {
    console.error("Create Returnable Goods Error:", err);

    return res.status(500).json({
      success: false,
      message: "Failed to create returnable goods",
    });
  } finally {
    client.release();
  }
};


// const mongoose = require("mongoose");

// const getReturnableGoods = async (req, res) => {
//   try {
//     const orgId = req.user.orgId;

//     const {
//       page = 1,
//       limit = 10,
//       search = "",
//       status,
//       sortBy = "createdAt",
//       order = "desc",
//     } = req.query;

//     const pageNumber = parseInt(page);
//     const pageSize = parseInt(limit);
//     const sortOrder = order === "asc" ? 1 : -1;

//     let filter = { orgId, isDeleted: false };

//     if (status) filter.status = status;

//     if (search) {
//       filter.$or = [
//         { itemId: { $regex: search, $options: "i" } },
//         { itemName: { $regex: search, $options: "i" } },
//         { receiverName: { $regex: search, $options: "i" } },
//       ];
//     }

//     const total = await ReturnableGoods.countDocuments(filter);

//     /* ===== FETCH RAW DATA (NO POPULATE) ===== */

//     const raw = await ReturnableGoods.find(filter)
//       .sort({ [sortBy]: sortOrder })
//       .skip((pageNumber - 1) * pageSize)
//       .limit(pageSize)
//       .lean();

//     const BASE_URL = process.env.BASE_URL || "http://localhost:5000";

//     const rawWithImage = raw.map((item) => ({
//       ...item,
//       // itemPhoto: item.itemPhoto ? `${BASE_URL}/${item.itemPhoto}` : null,
//       itemPhoto: item.itemPhoto
//         ? item.itemPhoto.startsWith("http")
//           ? item.itemPhoto
//           : `${BASE_URL}/${item.itemPhoto}`
//         : null,
//     }));

//     /* ===== SAFE USER POPULATION ===== */

//     const validUserIds = raw
//       .flatMap((r) => [r.createdBy, r.referenceBy])
//       .filter((id) => mongoose.Types.ObjectId.isValid(id));

//     const users = await User.find(
//       { _id: { $in: validUserIds } },
//       "firstName lastName",
//     ).lean();

//     const userMap = {};
//     users.forEach((u) => {
//       userMap[u._id.toString()] = u;
//     });

//     const data = rawWithImage.map((item) => ({
//       ...item,
//       createdByUser: userMap[item.createdBy] || null,
//       referenceUser: userMap[item.referenceBy] || null,
//     }));

//     return res.json({
//       success: true,
//       data,
//       pagination: {
//         total,
//         page: pageNumber,
//         limit: pageSize,
//         totalPages: Math.ceil(total / pageSize),
//       },
//     });
//   } catch (err) {
//     console.error("Fetch Returnable Goods Error:", err);
//     return errorResponse(res, "Failed", 500);
//   }
// };

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

  try {
    const orgId = req.session.user.orgId;

    let {
      page = 1,
      limit = 10,
      search = "",
      status,
      sortBy = "created_at",
      order = "desc",
    } = req.query;

    const pageNumber = parseInt(page);
    const pageSize = parseInt(limit);
    const offset = (pageNumber - 1) * pageSize;

    const sortOrder = order === "asc" ? "ASC" : "DESC";

    /* ===== SAFE SORT COLUMN ===== */
    const allowedSort = [
      "created_at",
      "item_name",
      "status",
      "sequence_number",
    ];

    if (!allowedSort.includes(sortBy)) {
      sortBy = "created_at";
    }

    /* ===== WHERE CONDITIONS ===== */
    let where = `WHERE rg.org_id = $1 AND rg.is_deleted = false`;
    let values = [orgId];
    let idx = values.length;

    if (status) {
      idx++;
      where += ` AND rg.status = $${idx}`;
      values.push(status);
    }

    if (search) {
      idx++;
      where += ` AND (
        rg.item_id ILIKE $${idx}
        OR rg.item_name ILIKE $${idx}
        OR rg.receiver_name ILIKE $${idx}
      )`;
      values.push(`%${search}%`);
    }

    /* ===== TOTAL COUNT ===== */
    const countQuery = `
      SELECT COUNT(*) 
      FROM returnable_goods rg
      ${where}
    `;

    const countResult = await client.query(countQuery, values);
    const total = parseInt(countResult.rows[0].count);

    /* ===== MAIN QUERY (WITH USER JOIN) ===== */
    const dataQuery = `
      SELECT 
        rg.*,

        -- created by user
        cu.first_name AS created_by_first_name,
        cu.last_name AS created_by_last_name,

        -- reference user
        ru.first_name AS ref_first_name,
        ru.last_name AS ref_last_name

      FROM returnable_goods rg

      LEFT JOIN users cu ON rg.created_by = cu.id
      LEFT JOIN users ru ON rg.reference_by = ru.id

      ${where}

      ORDER BY rg.${sortBy} ${sortOrder}
      LIMIT $${idx + 1} OFFSET $${idx + 2}
    `;

    const dataResult = await client.query(dataQuery, [
      ...values,
      pageSize,
      offset,
    ]);

    const BASE_URL = process.env.BASE_URL || "http://localhost:5000";

    /* ===== FORMAT RESPONSE ===== */
    const data = dataResult.rows.map((item) => ({
      ...item,

      itemPhoto: item.item_photo
        ? item.item_photo.startsWith("http")
          ? item.item_photo
          : `${BASE_URL}/${item.item_photo}`
        : null,

      createdByUser:
        item.created_by_first_name || item.created_by_last_name
          ? {
              firstName: item.created_by_first_name,
              lastName: item.created_by_last_name,
            }
          : null,

      referenceUser:
        item.ref_first_name || item.ref_last_name
          ? {
              firstName: item.ref_first_name,
              lastName: item.ref_last_name,
            }
          : null,
    }));

    return res.json({
      success: true,
      data,
      pagination: {
        total,
        page: pageNumber,
        limit: pageSize,
        totalPages: Math.ceil(total / pageSize),
      },
    });

  } catch (err) {
    console.error("Fetch Returnable Goods Error:", err);

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

// const updateReturnableGoods = async (req, res) => {
//   try {
//     const userId = req.user._id;
//     const orgId = req.user.orgId;
//     const { id } = req.params;

//     if (!id) {
//       return errorResponse(res, "Returnable goods id is required", 400);
//     }

//     const doc = await ReturnableGoods.findOne({
//       _id: id,
//       orgId,
//       isDeleted: false,
//     });

//     if (!doc) {
//       return errorResponse(res, "Returnable goods not found", 404);
//     }

//     const {
//       itemId,
//       itemName,
//       quantity,
//       condition,
//       remarks,
//       pickupDate,
//       returnDate,
//       // referenceBy,
//       receiverName,
//       receiverPhone,
//       vendorName,
//       //   approvedBy,
//       status,
//       returnBy,
//       returnByPhone,
//     } = req.body;

//     /* ===== DATE VALIDATION ===== */
//     const newPickupDate = pickupDate || doc.pickupDate;
//     const newReturnDate = returnDate || doc.returnDate;

//     if (new Date(newReturnDate) < new Date(newPickupDate)) {
//       return errorResponse(
//         res,
//         "Return date cannot be before pickup date",
//         400,
//       );
//     }

//     /* ===== FILE UPDATE ===== */
//     // if (req.file) {
//     //   doc.itemPhoto = req.file.path;
//     // }

//     let itemPhoto = doc.itemPhoto; // ⭐ keep old image by default

//     if (req.file) {
//       itemPhoto = req.file.path
//         .replace(/\\/g, "/")
//         .replace(/^.*uploads/, "uploads");

//       doc.itemPhoto = itemPhoto; // ⭐ VERY IMPORTANT
//     }
//     /* ===== PARTIAL UPDATE ===== */
//     if (itemId) doc.itemId = itemId;
//     if (itemName) doc.itemName = itemName;
//     if (quantity) doc.quantity = quantity;
//     if (condition) doc.condition = condition;
//     if (remarks !== undefined) doc.remarks = remarks;
//     if (pickupDate) doc.pickupDate = pickupDate;
//     if (returnDate) doc.returnDate = returnDate;
//     // if (referenceBy) doc.referenceBy = referenceBy;
//     if (receiverName) doc.receiverName = receiverName;
//     if (receiverPhone !== undefined) doc.receiverPhone = receiverPhone;
//     if (vendorName) doc.vendorName = vendorName;
//     if (status) doc.status = status;
//     if (returnBy !== undefined) doc.returnBy = returnBy;

//     if (returnByPhone !== undefined) doc.returnByPhone = returnByPhone;
//     doc.updatedBy = userId;
//     await doc.save();

//     /* ===== AUDIT LOG ===== */
//     await logUserAction({
//       userId,
//       action: "UPDATE",
//       model: "ReturnableGoods",
//       documentId: doc._id,
//       details: {
//         itemId: doc.itemId,
//         quantity: doc.quantity,
//       },
//       req,
//     });

//     return res.json({
//       success: true,
//       message: "Returnable goods updated successfully",
//       data: doc,
//     });
//   } catch (err) {
//     console.error("Update Returnable Goods Error:", err);
//     return errorResponse(res, "Failed to update returnable goods", 500, err);
//   }
// };

// const deleteReturnableGoods = async (req, res) => {
//   try {
//     const userId = req.user._id;
//     const orgId = req.user.orgId;
//     const { id } = req.params;

//     if (!id) {
//       return errorResponse(res, "Returnable goods id is required", 400);
//     }

//     const doc = await ReturnableGoods.findOne({
//       _id: id,
//       orgId,
//       isDeleted: false,
//     });

//     if (!doc) {
//       return errorResponse(res, "Returnable goods not found", 404);
//     }

//     doc.isDeleted = true;
//     await doc.save();

//     /* ===== AUDIT LOG ===== */
//     await logUserAction({
//       userId,
//       action: "DELETE",
//       model: "ReturnableGoods",
//       documentId: doc._id,
//       details: {
//         itemId: doc.itemId,
//       },
//       req,
//     });

//     return res.json({
//       success: true,
//       message: "Returnable goods deleted successfully",
//     });
//   } catch (err) {
//     console.error("Delete Returnable Goods Error:", err);
//     return errorResponse(res, "Failed to delete returnable goods", 500, err);
//   }
// };
const updateReturnableGoods = async (req, res) => {
  const client = await pool.connect();

  try {
    const userId = req.user.id;
    const orgId = req.user.orgId;
    const { id } = req.params;

    if (!id) {
      return res.status(400).json({
        success: false,
        message: "Returnable goods id is required",
      });
    }

    /* ===== FETCH EXISTING ===== */
    const existingResult = await client.query(
      `SELECT * FROM returnable_goods
       WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [id, orgId]
    );

    const doc = existingResult.rows[0];

    if (!doc) {
      return res.status(404).json({
        success: false,
        message: "Returnable goods not found",
      });
    }

    const {
      itemId,
      itemName,
      quantity,
      condition,
      remarks,
      pickupDate,
      returnDate,
      receiverName,
      receiverPhone,
      vendorName,
      status,
      returnBy,
      returnByPhone,
    } = req.body;

    /* ===== DATE VALIDATION ===== */
    const newPickupDate = pickupDate || doc.pickup_date;
    const newReturnDate = returnDate || doc.return_date;

    if (newReturnDate && newPickupDate && new Date(newReturnDate) < new Date(newPickupDate)) {
      return res.status(400).json({
        success: false,
        message: "Return date cannot be before pickup date",
      });
    }

    /* ===== FILE ===== */
    let itemPhoto = doc.item_photo;

    if (req.file) {
      itemPhoto = req.file.path
        .replace(/\\/g, "/")
        .replace(/^.*uploads/, "uploads");
    }

    /* ===== UPDATE ===== */
    const result = await client.query(
      `UPDATE returnable_goods SET
        item_id = COALESCE($1, item_id),
        item_name = COALESCE($2, item_name),
        quantity = COALESCE($3, quantity),
        condition = COALESCE($4, condition),
        remarks = COALESCE($5, remarks),
        pickup_date = COALESCE($6, pickup_date),
        return_date = COALESCE($7, return_date),
        receiver_name = COALESCE($8, receiver_name),
        receiver_phone = COALESCE($9, receiver_phone),
        vendor_name = COALESCE($10, vendor_name),
        status = COALESCE($11, status),
        return_by = COALESCE($12, return_by),
        return_by_phone = COALESCE($13, return_by_phone),
        item_photo = COALESCE($14, item_photo),
        updated_by = $15,
        updated_at = NOW()
      WHERE id = $16 AND org_id = $17 AND is_deleted = false
      RETURNING *`,
      [
        itemId || null,
        itemName || null,
        quantity || null,
        condition || null,
        remarks !== undefined ? remarks : null,
        pickupDate || null,
        returnDate || null,
        receiverName || null,
        receiverPhone !== undefined ? receiverPhone : null,
        vendorName || null,
        status || null,
        returnBy !== undefined ? returnBy : null,
        returnByPhone !== undefined ? returnByPhone : null,
        itemPhoto,
        userId,
        id,
        orgId,
      ]
    );

    const updated = result.rows[0];

    /* ===== AUDIT LOG ===== */
    // await client.query(
    //   `INSERT INTO user_action_logs (user_id, action, model, document_id, details)
    //    VALUES ($1,$2,$3,$4,$5)`,
    //   [
    //     userId,
    //     "UPDATE",
    //     "returnable_goods",
    //     updated.id,
    //     JSON.stringify({
    //       itemId: updated.item_id,
    //       quantity: updated.quantity,
    //     }),
    //   ]
    // );

    return res.json({
      success: true,
      message: "Returnable goods updated successfully",
      data: updated,
    });

  } catch (err) {
    console.error("Update Returnable Goods Error:", err);

    return res.status(500).json({
      success: false,
      message: "Failed to update returnable goods",
    });
  } finally {
    client.release();
  }
};


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

  try {
    const userId = req.session.user.id;
    const orgId = req.session.user.orgId;
    const { id } = req.params;

    if (!id) {
      return errorResponse(res, "Returnable goods id is required", 400);
    }

    await client.query("BEGIN");

    /* ===== CHECK EXISTENCE ===== */
    const findQuery = `
      SELECT id, item_id
      FROM returnable_goods
      WHERE id = $1
        AND org_id = $2
        AND is_deleted = false
      LIMIT 1
    `;

    const { rows } = await client.query(findQuery, [id, orgId]);

    if (rows.length === 0) {
      await client.query("ROLLBACK");
      return errorResponse(res, "Returnable goods not found", 404);
    }

    const doc = rows[0];

    /* ===== SOFT DELETE ===== */
    const deleteQuery = `
      UPDATE returnable_goods
      SET is_deleted = true,
          updated_at = NOW()
      WHERE id = $1
        AND org_id = $2
    `;

    await client.query(deleteQuery, [id, orgId]);

    /* ===== AUDIT LOG ===== */
    await logUserAction({
      userId,
      action: "DELETE",
      model: "ReturnableGoods",
      documentId: doc.id,
      details: {
        itemId: doc.item_id,
      },
      req,
    });

    await client.query("COMMIT");

    return res.json({
      success: true,
      message: "Returnable goods deleted successfully",
    });
  } catch (err) {
    await client.query("ROLLBACK");
    console.error("Delete Returnable Goods Error:", err);
    return errorResponse(res, "Failed to delete returnable goods", 500, err);
  } finally {
    client.release();
  }
};


const closeReturnableGoods = async (req, res) => {
  try {
    const userId = req.user._id;
    const orgId = req.user.orgId;
    const { id } = req.params;

    if (!id) {
      return errorResponse(res, "Id is required", 400);
    }

    const doc = await ReturnableGoods.findOne({
      _id: id,
      // orgId,
      isDeleted: false,
    });

    if (!doc) {
      return errorResponse(res, "Returnable goods not found", 404);
    }

    /* ===== STATUS VALIDATION ===== */
    if (doc.status === "Closed") {
      return errorResponse(res, "Already closed", 400);
    }

    /* ===== UPDATE ===== */
    doc.status = "Closed";
    doc.actualReturnDate = new Date();
    doc.updatedBy = userId;

    await doc.save();

    /* ===== AUDIT ===== */
    await logUserAction({
      userId,
      action: "CLOSE_RETURN",
      model: "ReturnableGoods",
      documentId: doc._id,
      details: {
        itemName: doc.itemName,
      },
      req,
    });

    return res.json({
      success: true,
      message: "Return confirmed successfully",
      data: doc,
    });
  } catch (err) {
    console.log(err);
    return errorResponse(res, "Failed to close return", 500);
  }
};


// const getReturnableGoodsById = async (req, res) => {
//   try {
//     const { id } = req.params;
//     const orgId = req.user.orgId;

//     /* ===== FETCH GOODS ===== */
//     const doc = await ReturnableGoods.findOne({
//       _id: id,
//       // orgId,
//       isDeleted: false,
//     }).lean();

//     if (!doc) {
//       return errorResponse(res, "Returnable goods not found", 404);
//     }

//     /* ===== BUILD IMAGE URL ===== */
//     const BASE_URL = process.env.BASE_URL || "http://localhost:5000";

//     const data = {
//       ...doc,
//       itemPhoto: doc.itemPhoto
//         ? doc.itemPhoto.startsWith("http")
//           ? doc.itemPhoto
//           : `${BASE_URL}/${doc.itemPhoto}`
//         : null,
//     };

//     /* ===== SAFE USER POPULATION ===== */
//     const userIds = [doc.createdBy, doc.referenceBy].filter((id) =>
//       mongoose.Types.ObjectId.isValid(id),
//     );

//     const users = await User.find(
//       { _id: { $in: userIds } },
//       "firstName lastName",
//     ).lean();

//     const userMap = {};
//     users.forEach((u) => {
//       userMap[u._id.toString()] = u;
//     });

//     const finalData = {
//       ...data,
//       createdByUser: userMap[doc.createdBy] || null,
//       referenceUser: userMap[doc.referenceBy] || null,
//     };

//     return res.status(200).json({
//       success: true,
//       message: "Returnable goods fetched successfully",
//       data: finalData,
//     });
//   } catch (error) {
//     console.error("Get Goods By Id Error:", error);
//     return errorResponse(res, "Failed to fetch returnable goods", 500);
//   }
// };

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

  try {
    const { id } = req.params;
    const orgId = req.session.user.orgId;

    /* ===== FETCH GOODS WITH USER JOIN ===== */
    const result = await client.query(
      `SELECT 
        rg.*,

        -- created by
        cu.first_name AS created_by_first_name,
        cu.last_name AS created_by_last_name,

        -- reference user
        ru.first_name AS ref_first_name,
        ru.last_name AS ref_last_name

      FROM returnable_goods rg

      LEFT JOIN users cu ON rg.created_by = cu.id
      LEFT JOIN users ru ON rg.reference_by = ru.id

      WHERE rg.id = $1 
        AND rg.org_id = $2
        AND rg.is_deleted = false
      LIMIT 1`,
      [id, orgId]
    );

    const doc = result.rows[0];

    if (!doc) {
      return res.status(404).json({
        success: false,
        message: "Returnable goods not found",
      });
    }

    /* ===== BUILD IMAGE URL ===== */
    const BASE_URL = process.env.BASE_URL || "http://localhost:5000";

    const data = {
      ...doc,

      itemPhoto: doc.item_photo
        ? doc.item_photo.startsWith("http")
          ? doc.item_photo
          : `${BASE_URL}/${doc.item_photo}`
        : null,

      /* ===== USER MAPPING (same as Mongo populate) ===== */
      createdByUser:
        doc.created_by_first_name || doc.created_by_last_name
          ? {
              firstName: doc.created_by_first_name,
              lastName: doc.created_by_last_name,
            }
          : null,

      referenceUser:
        doc.ref_first_name || doc.ref_last_name
          ? {
              firstName: doc.ref_first_name,
              lastName: doc.ref_last_name,
            }
          : null,
    };

    return res.status(200).json({
      success: true,
      message: "Returnable goods fetched successfully",
      data,
    });

  } catch (error) {
    console.error("Get Goods By Id Error:", error);

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

module.exports = {
  createReturnableGoods,
  getReturnableGoods,
  deleteReturnableGoods,
  updateReturnableGoods,
  closeReturnableGoods,
  getReturnableGoodsById,
};
