const Stock = require("../../models/securityops/stockModel");
// const { errorResponse } = require("../../utils/errorResponseHandler");
// const { logUserAction } = require("../../utils/userActionLogger");
// const authenticate = require("../../middlewares/authenticate")
// const authorize = require("../../middlewares/authorize")
const Product = require("../../models/admin/productModel");
const { pool } = require("../../config/db");

// const getStockDashboard = async (req, res) => {
//   try {
//     /* OPTIONAL FILTER (Future Safe) */
//     const { warehouseId } = req.query;

//     const stockMatch = {
//       isDeleted: false,
//       productId: { $ne: null },
//     };

//     if (warehouseId) {
//       stockMatch.warehouseId = new mongoose.Types.ObjectId(warehouseId);
//     }

//     /*  AGGREGATE STOCK */
//     const stockAgg = await Stock.aggregate([
//       { $match: stockMatch },

//       {
//         $group: {
//           _id: "$productId",
//           totalQty: { $sum: "$netQuantity" },
//         },
//       },
//     ]);

//     /*  GET ALL PRODUCTS */
//     // const products = await Product.find(
//     //   { isDeleted: false },
//     //   { name: 1 }
//     // ).lean();
//     const requiredProducts = ["Molasses", "Grains", "Corn", "Husky"];

//     const products = await Product.find(
//       {
//         isDeleted: false,
//         productName: { $in: requiredProducts },
//       },
//       { productName: 1 },
//     ).lean();

//     /*  MAP RESULT (VERY IMPORTANT) */
//     const stats = products.map((p) => {
//       const found = stockAgg.find((s) => s._id.toString() === p._id.toString());

//       return {
//         productId: p._id,
//         productName: p.productName,
//         quantityKg: found ? found.totalQty : 0,
//         quantityTon: Number((found ? found.totalQty : 0) / 1000).toFixed(2),
//       };
//     });

//     /*  SORT (Optional UX Improvement) */
//     stats.sort((a, b) => b.quantityKg - a.quantityKg);

//     return res.status(200).json({
//       success: true,
//       message: "Stock dashboard fetched",
//       data: stats,
//     });
//   } catch (error) {
//     console.error("Stock Dashboard Error:", error);
//     return res.status(500).json({
//       success: false,
//       message: "Failed to fetch stock dashboard",
//     });
//   }
// };

// const getStockDashboard = async (req, res) => {
//   try {
//     const requiredProducts = ["Molasses", "Grains", "Corn", "Husky"];

//     const stockAgg = await Stock.aggregate([
//       {
//         $match: {
//           isDeleted: false,
//           "materialDetails.materialType": { $in: requiredProducts },
//         },
//       },
//       {
//         $group: {
//           _id: "$materialDetails.materialType",
//           totalQty: { $sum: "$netQuantity" },
//         },
//       },
//     ]);

//     const stats = requiredProducts.map((name) => {
//       const found = stockAgg.find((s) => s._id === name);

//       return {
//         productName: name,
//         quantityKg: found ? found.totalQty : 0,
//         quantityTon: Number((found ? found.totalQty : 0) / 1000).toFixed(2),
//       };
//     });

//     return res.status(200).json({
//       success: true,
//       data: stats,
//     });
//   } catch (error) {
//     console.error(error);
//     return res.status(500).json({
//       success: false,
//       message: "Dashboard failed",
//     });
//   }
// };

const getStockDashboard = async (req, res) => {
  try {
    const requiredProducts = ["Molasses", "Grains", "Corn", "Husky"];

    const query = `
  SELECT 
    md->>'materialType' AS product,
    SUM(s.net_quantity) AS total_qty
  FROM stocks s,
       jsonb_array_elements(
         CASE 
           WHEN jsonb_typeof(s.material_details) = 'array' 
           THEN s.material_details
           ELSE '[]'::jsonb
         END
       ) AS md
  WHERE s.is_deleted = false
    AND md->>'materialType' = ANY($1)
  GROUP BY md->>'materialType'
`;

    const result = await pool.query(query, [requiredProducts]);

    const stockMap = {};
    result.rows.forEach((row) => {
      stockMap[row.product] = Number(row.total_qty);
    });

    const stats = requiredProducts.map((name) => {
      const qty = stockMap[name] || 0;

      return {
        productName: name,
        quantityKg: qty,
        quantityTon: (qty / 1000).toFixed(2),
      };
    });

    return res.status(200).json({
      success: true,
      data: stats,
    });
  } catch (error) {
    console.error(error);
    return res.status(500).json({
      success: false,
      message: "Dashboard failed",
    });
  }
};

// const getStockLedgerByProduct = async (req, res) => {
//   try {

//     const { materialType, page = 1, limit = 5, warehouseId } = req.query;

//     if (!materialType) {
//       return res.status(400).json({
//         success: false,
//         message: "materialType is required",
//       });
//     }
//     const match = {
//       isDeleted: false,
//       "materialDetails.materialType": materialType,
//     };

//     if (warehouseId) {
//       match.warehouseId = new mongoose.Types.ObjectId(warehouseId);
//     }

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

//     const [rows, total] = await Promise.all([
//       Stock.find(match)
//         .populate("receivedBy", "firstName")
//         .populate("qcBy", "firstName")
//         .populate("warehouseId", "name")
//         .sort({ createdAt: -1 })
//         .skip(skip)
//         .limit(Number(limit))
//         .lean(),

//       Stock.countDocuments(match),
//     ]);

//     /*  Format Response For UI */
//     const formatted = rows.map((r, i) => ({
//       slNo: skip + i + 1,
//       productName: r.materialDetails?.materialType,
//       vehicleNumber: r.vehicleNumber,
//       vendor: r.materialDetails?.supplierName,
//       date: r.receivedDate,
//       netQuantity: r.netQuantity,
//       grossWeight: r.grossWeight,
//       receivedBy: r.receivedBy?.firstName || "-",
//       qcBy: r.qcBy?.firstName || "-",
//     }));

//     return res.status(200).json({
//       success: true,
//       data: formatted,
//       pagination: {
//         total,
//         page: Number(page),
//         pages: Math.ceil(total / limit),
//       },
//     });
//   } catch (error) {
//     console.error("Stock Ledger Error:", error);
//     return res.status(500).json({
//       success: false,
//       message: "Failed to fetch stock ledger",
//     });
//   }
// };

const getStockLedgerByProduct = async (req, res) => {
  try {
    const { materialType, page = 1, limit = 5, warehouseId } = req.query;

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

    const offset = (page - 1) * limit;

    /* 🔍 MAIN QUERY */
    const query = `
      SELECT 
        s.id,
        s.vehicle_number,
        s.net_quantity,
        s.gross_weight,
        s.created_at AS received_date,

        u1.first_name AS received_by,
        u2.first_name AS qc_by,
        w.warehouse_name AS warehouse_name,

        md->>'materialType' AS material_type,
        md->>'supplierName' AS supplier_name

      FROM stocks s

      LEFT JOIN users u1 ON u1.id = s.received_by
      LEFT JOIN users u2 ON u2.id = s.qc_by
      LEFT JOIN warehouses w ON w.id = s.warehouse_id,

      jsonb_array_elements(
        CASE 
          WHEN jsonb_typeof(s.material_details) = 'array'
          THEN s.material_details
          ELSE '[]'::jsonb
        END
      ) AS md

      WHERE s.is_deleted = false
        AND md->>'materialType' = $1
        ${warehouseId ? "AND s.warehouse_id = $2" : ""}

      ORDER BY s.created_at DESC
      LIMIT $${warehouseId ? 3 : 2}
      OFFSET $${warehouseId ? 4 : 3}
    `;

    const values = warehouseId
      ? [materialType, warehouseId, limit, offset]
      : [materialType, limit, offset];

    /* 🔢 COUNT QUERY */
    const countQuery = `
      SELECT COUNT(*) 
      FROM stocks s,
      jsonb_array_elements(
        CASE 
          WHEN jsonb_typeof(s.material_details) = 'array'
          THEN s.material_details
          ELSE '[]'::jsonb
        END
      ) AS md
      WHERE s.is_deleted = false
        AND md->>'materialType' = $1
        ${warehouseId ? "AND s.warehouse_id = $2" : ""}
    `;

    const countValues = warehouseId
      ? [materialType, warehouseId]
      : [materialType];

    const [rowsResult, countResult] = await Promise.all([
      pool.query(query, values),
      pool.query(countQuery, countValues),
    ]);

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

    /* 🎯 FORMAT RESPONSE */
    const formatted = rows.map((r, i) => ({
      slNo: offset + i + 1,
      productName: r.material_type,
      vehicleNumber: r.vehicle_number,
      vendor: r.supplier_name || "-",
      date: r.received_date,
      netQuantity: r.net_quantity,
      grossWeight: r.gross_weight,
      receivedBy: r.received_by || "-",
      qcBy: r.qc_by || "-",
    }));

    return res.status(200).json({
      success: true,
      data: formatted,
      pagination: {
        total,
        page: Number(page),
        pages: Math.ceil(total / limit),
      },
    });
  } catch (error) {
    console.error("Stock Ledger Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to fetch stock ledger",
    });
  }
};

module.exports = {
  getStockDashboard,
  getStockLedgerByProduct,
};
