const Warehouse = require("../../models/securityops/warehouseModel");
const {pool} = require("../../config/db")

// const createWarehouse = async (req, res) => {
//   try {
//     const {
//       warehouseName,
//       warehouseCode,
//       // branch,
//       // address,
//       capacity,
//       incharge,
//       uomId,
//     } = req.body;
//     const existing = await Warehouse.findOne({
//       warehouseCode,
//       isDeleted: false,
//     });

//     if (existing) {
//       return res.status(400).json({
//         success: false,
//         message: "Warehouse code already exists",
//       });
//     }

//     const warehouse = await Warehouse.create({
//       warehouseName,
//       warehouseCode,
//       // branch,
//       // address,
//       capacity,
//       uomId,
//       incharge,
//       createdBy: req.session.user?.id,
//     });

//     res.status(201).json({
//       success: true,
//       message: "Warehouse created successfully",
//       data: warehouse,
//     });
//   } catch (error) {
//     console.error(error);
//     res.status(500).json({
//       success: false,
//       message: "Failed to create warehouse",
//     });
//   }
// };
// const createWarehouse = async (req, res) => {
//   const client = await pool.connect();

//   try {
//     const {
//       warehouseName,
//       warehouseCode,
//       capacity,
//       incharge,
//       uomId,
//       address, // optional JSON
//     } = req.body;

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

//     //  Validation
//     if (!warehouseName) {
//       return errorResponse(res, "Warehouse name is required", 400);
//     }

//     await client.query("BEGIN");

//     //  Check duplicate warehouse code (if provided)
//     if (warehouseCode) {
//       const existing = await client.query(
//         `
//         SELECT id FROM warehouses
//         WHERE warehouse_code = $1 AND is_deleted = FALSE
//         LIMIT 1;
//         `,
//         [warehouseCode.toUpperCase()]
//       );

//       if (existing.rowCount > 0) {
//         await client.query("ROLLBACK");
//         return res.status(400).json({
//           success: false,
//           message: "Warehouse code already exists",
//         });
//       }
//     }

//     //  Insert warehouse
//     const result = await client.query(
//       `
//       INSERT INTO warehouses
//         (warehouse_name, warehouse_code, address, capacity, uom_id, incharge, status, is_deleted, created_by, created_at)
//       VALUES
//         ($1, $2, $3, $4, $5, $6, 'ACTIVE', FALSE, $7, NOW())
//       RETURNING *;
//       `,
//       [
//         warehouseName.trim(),
//         warehouseCode ? warehouseCode.toUpperCase() : null,
//         address ? JSON.stringify(address) : null,
//         capacity || 0,
//         uomId || null,
//         incharge || null,
//         userId,
//       ]
//     );

//     const warehouse = result.rows[0];

//     await client.query("COMMIT");

//     return res.status(201).json({
//       success: true,
//       message: "Warehouse created successfully",
//       data: warehouse,
//     });
//   } catch (error) {
//     await client.query("ROLLBACK");
//     console.error("Create warehouse error:", error);

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


// const getWarehouses = async (req, res) => {
//   try {
//     const page = parseInt(req.query.page) || 1;
//     const limit = parseInt(req.query.limit) || 10;

//     const skip = (page - 1) * limit;

//     const warehouses = await Warehouse.find({ isDeleted: false })
//       .populate({
//         path: "incharge",
//         select: "firstName lastName",
//         options: { strictPopulate: false },
//       })
//       .populate({
//         path: "uomId",
//         select: "name symbol",
//         options: { strictPopulate: false },
//       })
//       .sort({ createdAt: -1 })
//       .skip(skip)
//       .limit(limit);

//     const total = await Warehouse.countDocuments({
//       isDeleted: false,
//     });

//     res.json({
//       success: true,
//       data: warehouses,
//       pagination: {
//         total,
//         page,
//         pages: Math.ceil(total / limit),
//       },
//     });
//   } catch (error) {
//     console.log("GET WAREHOUSE ERROR 👉", error);
//     res.status(500).json({
//       success: false,
//       message: error.message || "Failed to fetch warehouses",
//     });
//   }
// };
const createWarehouse = async (req, res) => {
  const client = await pool.connect();

  try {
    const {
      warehouseName,
      warehouseCode,
      capacity,
      incharge,
      uomId,
      address,
      items = [], // NEW
      groundstock,
      alertstock,
    } = req.body;

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

    /* ================= VALIDATIONS ================= */

    if (!warehouseName?.trim()) {
      return errorResponse(res, "Warehouse name is required", 400);
    }

    if (!uomId) {
      return errorResponse(res, "UOM is required", 400);
    }

    if (
      capacity &&
      Number(capacity) > 0 &&
      Number(groundstock || 0) > Number(capacity)
    ) {
      return errorResponse(
        res,
        "Dead stock cannot exceed capacity",
        400
      );
    }

    if (
      alertstock &&
      groundstock &&
      Number(alertstock) <= Number(groundstock)
    ) {
      return errorResponse(
        res,
        "Alert stock must be greater than dead stock",
        400
      );
    }

    /* ================= ITEMS VALIDATION ================= */

    const formattedItems = Array.isArray(items)
      ? items.map((item) => ({
          item_id: Number(item.item_id),
          item_name: item.item_name,
          item_code: item.item_code,
        }))
      : [];

    await client.query("BEGIN");

    /* ================= DUPLICATE CODE ================= */

    if (warehouseCode) {
      const existing = await client.query(
        `
        SELECT id
        FROM warehouses
        WHERE warehouse_code = $1
        AND is_deleted = FALSE
        LIMIT 1;
        `,
        [warehouseCode.toUpperCase()]
      );

      if (existing.rowCount > 0) {
        await client.query("ROLLBACK");

        return res.status(400).json({
          success: false,
          message: "Warehouse code already exists",
        });
      }
    }

    /* ================= INSERT ================= */

    const result = await client.query(
      `
      INSERT INTO warehouses (
        warehouse_name,
        warehouse_code,
        address,
        capacity,
        uom_id,
        incharge,
        items,
        ground_stock,
        alert_stock,
        status,
        is_deleted,
        created_by,
        created_at
      )
      VALUES (
        $1,
        $2,
        $3,
        $4,
        $5,
        $6,
        $7,
        $8,
        $9,
        'ACTIVE',
        FALSE,
        $10,
        NOW()
      )
      RETURNING *;
      `,
      [
        warehouseName.trim(),
        warehouseCode
          ? warehouseCode.toUpperCase()
          : null,

        address
          ? JSON.stringify(address)
          : null,

        capacity || 0,

        uomId || null,

        incharge || null,

        JSON.stringify(formattedItems), // NEW

        groundstock || 0,

        alertstock || 0,

        userId,
      ]
    );

    await client.query("COMMIT");

    return res.status(201).json({
      success: true,
      message: "Warehouse created successfully",
      data: result.rows[0],
    });
  } catch (error) {
    await client.query("ROLLBACK");

    console.error(
      "Create warehouse error:",
      error
    );

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

const getWarehouses = 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;

    //  Removed UOM JOIN
const dataQuery = `
  SELECT 
    w.id,
    w.warehouse_name AS "warehouseName",
    w.warehouse_code AS "warehouseCode",
    w.capacity,
    w.ground_stock AS "groundStock",
    w.alert_stock AS "alertStock",
    w.status,
    w.address,
    w.items,
    w.created_at AS "createdAt",
    w.updated_at AS "updatedAt",

    -- Incharge
    json_build_object(
      'id', u.id,
      'firstName', u.first_name,
      'lastName', u.last_name
    ) AS "incharge",

    -- UOM
    json_build_object(
      'id', um.id,
      'name', um.name,
      'symbol', um.symbol
    ) AS "uom"

  FROM warehouses w

  LEFT JOIN users u 
    ON w.incharge = u.id

  LEFT JOIN unit_of_measurements um
    ON w.uom_id = um.id

  WHERE w.is_deleted = FALSE

  GROUP BY 
    w.id,
    u.id,
    um.id

  ORDER BY w.created_at DESC

  LIMIT $1 OFFSET $2;
`;

    const countQuery = `
      SELECT COUNT(*) FROM warehouses 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);

    let warehouses = dataResult.rows;

    //  Parse address JSON
    warehouses = warehouses.map((w) => ({
      ...w,
      address:
        typeof w.address === "string"
          ? JSON.parse(w.address)
          : w.address,
    }));

    return res.json({
      success: true,
      data: warehouses,
      pagination: {
        total,
        page,
        pages: Math.ceil(total / limit),
      },
    });
  } catch (error) {
    console.log("GET WAREHOUSE ERROR 👉", error);
    return res.status(500).json({
      success: false,
      message: error.message || "Failed to fetch warehouses",
    });
  } finally {
    client.release();
  }
};



const getWarehouseById = async (req, res) => {
  try {
    const warehouse = await Warehouse.findOne({
      _id: req.params.id,
      isDeleted: false,
    }).populate("incharge", "fistName");
    if (!warehouse) {
      return res.status(404).json({
        success: false,
        message: "Warehouse not found",
      });
    }

    res.json({
      success: true,
      data: warehouse,
    });
  } catch (error) {
    res.status(500).json({
      success: false,
      message: "Failed to fetch warehouse",
    });
  }
};

// const updateWarehouse = async (req, res) => {
//   try {
//     // const warehouse = await Warehouse.findById(req.params.id);

//     // if (!warehouse || warehouse.isDeleted) {
//     //   return res.status(404).json({
//     //     success: false,
//     //     message: "Warehouse not found"
//     //   });
//     // }

//     // const updated = await Warehouse.findByIdAndUpdate(
//     //   req.params.id,
//     //   {
//     //     ...req.body,
//     //     updatedBy: req.user?.id
//     //   },
//     //   { new: true }
//     // );
//     const warehouse = await Warehouse.findById(req.params.id);

//     if (!warehouse || warehouse.isDeleted) {
//       return res.status(404).json({
//         success: false,
//         message: "Warehouse not found",
//       });
//     }

//     Object.assign(warehouse, req.body);
//     warehouse.updatedBy = req.user?.id;

//     await warehouse.save();

//     res.json({
//       success: true,
//       message: "Warehouse updated successfully",
//       data: updated,
//     });
//   } catch (error) {
//     res.status(500).json({
//       success: false,
//       message: "Failed to update warehouse",
//     });
//   }
// };

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

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

    // Validate warehouse ID
    if (!warehouseId || isNaN(warehouseId)) {
      return res.status(400).json({
        success: false,
        message: "Invalid warehouse ID",
      });
    }

    const updates = req.body || {};

    await client.query("BEGIN");

    // Check warehouse exists
    const existingWarehouse = await client.query(
      `
        SELECT id
        FROM warehouses
        WHERE id = $1
          AND is_deleted = FALSE
      `,
      [warehouseId]
    );

    if (existingWarehouse.rowCount === 0) {
      await client.query("ROLLBACK");

      return res.status(404).json({
        success: false,
        message: "Warehouse not found",
      });
    }

    // Allowed fields
    const allowedFields = {
      warehouseName: "warehouse_name",
      warehouseCode: "warehouse_code",
      capacity: "capacity",
      groundStock: "ground_stock",
      alertStock: "alert_stock",
      status: "status",
      incharge: "incharge",
      uomId: "uom_id",
      address: "address",
      items: "items",
    };

    const fields = [];
    const values = [];
    let index = 1;

    // Dynamic field builder
    for (const key in updates) {
      if (
        Object.prototype.hasOwnProperty.call(allowedFields, key) &&
        updates[key] !== undefined
      ) {
        let value = updates[key];

        // Convert dropdown objects -> IDs
        if (
          ["incharge", "uomId"].includes(key) &&
          typeof value === "object" &&
          value !== null
        ) {
          value = value.id || null;
        }

        // Numeric fields
        if (
          ["capacity", "groundStock", "alertStock"].includes(key)
        ) {
          value =
            value === "" || value === null
              ? 0
              : Number(value);

          if (isNaN(value)) {
            await client.query("ROLLBACK");

            return res.status(400).json({
              success: false,
              message: `${key} must be a valid number`,
            });
          }
        }

        // Nullable foreign keys
        if (
          ["incharge", "uomId"].includes(key) &&
          (value === "" || isNaN(value))
        ) {
          value = null;
        }

        // Address JSON
        if (key === "address") {
          value =
            value && typeof value === "object"
              ? JSON.stringify(value)
              : null;
        }

        // Items JSONB
        if (key === "items") {
          value = Array.isArray(value)
            ? JSON.stringify(value)
            : "[]";
        }

        fields.push(`${allowedFields[key]} = $${index}`);
        values.push(value);

        index++;
      }
    }

    // Nothing to update
    if (fields.length === 0) {
      await client.query("ROLLBACK");

      return res.status(400).json({
        success: false,
        message: "No valid fields provided for update",
      });
    }

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

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

    // Final WHERE id param
    values.push(warehouseId);

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

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

    await client.query("COMMIT");

    return res.status(200).json({
      success: true,
      message: "Warehouse updated successfully",
      data: result.rows[0],
    });
  } catch (error) {
    await client.query("ROLLBACK");

    console.error("UPDATE WAREHOUSE ERROR 👉", error);

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



// const deleteWarehouse = async (req, res) => {
//   try {
//     const warehouse = await Warehouse.findById(req.params.id);

//     if (!warehouse) {
//       return res.status(404).json({
//         success: false,
//         message: "Warehouse not found",
//       });
//     }

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

//     res.json({
//       success: true,
//       message: "Warehouse deleted successfully",
//     });
//   } catch (error) {
//     res.status(500).json({
//       success: false,
//       message: "Failed to delete warehouse",
//     });
//   }
// };
const deleteWarehouse = async (req, res) => {
  const client = await pool.connect();

  try {
    const warehouseId = parseInt(req.params.id, 10);

    // 🔹 Validate ID
    if (!warehouseId || isNaN(warehouseId)) {
      return res.status(400).json({
        success: false,
        message: "Invalid warehouse ID",
      });
    }

    await client.query("BEGIN");

    // 🔹 Soft delete
    const result = await client.query(
      `
      UPDATE warehouses
      SET is_deleted = TRUE,
          updated_at = NOW()
      WHERE id = $1 AND is_deleted = FALSE
      RETURNING id;
      `,
      [warehouseId]
    );

    if (result.rowCount === 0) {
      await client.query("ROLLBACK");
      return res.status(404).json({
        success: false,
        message: "Warehouse not found",
      });
    }

    await client.query("COMMIT");

    return res.json({
      success: true,
      message: "Warehouse deleted successfully",
    });
  } catch (error) {
    await client.query("ROLLBACK");

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

module.exports = {
  createWarehouse,
  getWarehouses,
  updateWarehouse,
  deleteWarehouse,
  getWarehouseById,
};
