
const {pool} = require("../../config/db")
const { errorResponse } = require("../../utils/errorResponseHandler");

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

  try {
    await client.query("BEGIN");

    const {
      recipe_id,
      production_date,
      batch_number,
      planned_input_qty,
      expected_output_qty,
      production_notes,

      ethanol_qty,
      ddgs_qty,
      co2_qty,
    } = req.body;

    const orgId = req.session.user.orgId;
    const createdBy = req.session.user.id;

    // =========================================
    // 1. CREATE PRODUCTION HEADER
    // =========================================

    const productionResult = await client.query(
      `
      INSERT INTO production_operations (
        org_id,
        recipe_id,
        production_date,
        batch_number,
        planned_input_qty,
        expected_output_qty,
        production_notes,
        created_by
      )
      VALUES ($1,$2,$3,$4,$5,$6,$7,$8)
      RETURNING *
      `,
      [
        orgId,
        recipe_id,
        production_date,
        batch_number,
        planned_input_qty,
        expected_output_qty,
        production_notes,
        createdBy,
      ]
    );

    const production = productionResult.rows[0];

    // =========================================
    // 2. GET PRODUCT IDS
    // =========================================

    const itemResult = await client.query(
      `
      SELECT id, item_name
      FROM procurement_itemmaster
      WHERE LOWER(item_name) IN ('ethanol', 'ddgs', 'co2')
      `
    );

    const itemMap = {};

    itemResult.rows.forEach((item) => {
      itemMap[item.item_name.toLowerCase()] = item.id;
    });

    // =========================================
    // 3. PREPARE ITEMS
    // =========================================

    const items = [
      {
        product_id: itemMap["ethanol"],
        qty: ethanol_qty,
        uom: "KL",
        item_type: "ethanol",
      },
      {
        product_id: itemMap["ddgs"],
        qty: ddgs_qty,
        uom: "T",
        item_type: "ddgs",
      },
      {
        product_id: itemMap["co2"],
        qty: co2_qty,
        uom: "KG",
        item_type: "co2",
      },
    ];

    // =========================================
    // 4. INSERT ITEMS
    // =========================================

    for (const item of items) {

      if (!item.qty || item.qty <= 0) continue;

      await client.query(
        `
        INSERT INTO production_operation_items (
          production_operation_id,
          product_id,
          qty,
          uom,
          item_type
        )
        VALUES ($1,$2,$3,$4,$5)
        `,
        [
          production.id,
          item.product_id,
          item.qty,
          item.uom,
          item.item_type,
        ]
      );

      // =========================================
      // 5. UPDATE INVENTORY STOCK
      // =========================================

      const stockResult = await client.query(
        `
        SELECT id
        FROM inventory_stock
        WHERE org_id = $1
        AND product_id = $2
        `,
        [orgId, item.product_id]
      );

      if (stockResult.rows.length > 0) {

        // UPDATE EXISTING STOCK

        await client.query(
          `
          UPDATE inventory_stock
          SET available_qty = available_qty + $1,
              updated_at = NOW()
          WHERE org_id = $2
          AND product_id = $3
          `,
          [item.qty, orgId, item.product_id]
        );

      } else {

        // CREATE STOCK ROW

        await client.query(
          `
          INSERT INTO inventory_stock (
            org_id,
            product_id,
            available_qty,
            reserved_qty,
            updated_at
          )
          VALUES ($1,$2,$3,0,NOW())
          `,
          [orgId, item.product_id, item.qty]
        );
      }
    }

    await client.query("COMMIT");

    return res.status(201).json({
      success: true,
      message: "Production created successfully",
      data: production,
    });

  } catch (error) {

    await client.query("ROLLBACK");

    console.error(error);

    return res.status(500).json({
      success: false,
      message: error.message,
    });

  } finally {
    client.release();
  }
};


const getProductions = async (req, res) => {

  const client = await pool.connect();

  try {

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

    page = parseInt(page);
    limit = parseInt(limit);

    const offset =
      (page - 1) * limit;

    // ======================
    // COUNT QUERY
    // ======================

    const countQuery = `
      SELECT COUNT(*) AS total

      FROM production_operations po

      LEFT JOIN production_recipe pr
        ON pr.id = po.recipe_id

      LEFT JOIN users u
        ON u.id = po.created_by

      WHERE
        COALESCE(po.is_deleted, false) = false

        AND (
          po.batch_number ILIKE $1
          OR pr.recipe_name ILIKE $1
          OR po.production_notes ILIKE $1
        )
    `;

    const countResult =
      await client.query(
        countQuery,
        [`%${search}%`]
      );

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

    // ======================
    // MAIN QUERY
    // ======================

    const query = `
      SELECT

        po.id,

        po.org_id,

        po.recipe_id,

        pr.recipe_name AS recipe,

        po.production_date,

        po.batch_number,

        po.planned_input_qty,

        po.expected_output_qty,

        po.production_notes,

        po.created_at,

        u.name AS created_by,

        -- ETHANOL

        MAX(
          CASE
            WHEN poi.item_type = 'ethanol'
            THEN poi.qty
          END
        ) AS ethanol_qty,

        MAX(
          CASE
            WHEN poi.item_type = 'ethanol'
            THEN poi.uom
          END
        ) AS ethanol_uom,

        -- DDGS

        MAX(
          CASE
            WHEN poi.item_type = 'ddgs'
            THEN poi.qty
          END
        ) AS ddgs_qty,

        MAX(
          CASE
            WHEN poi.item_type = 'ddgs'
            THEN poi.uom
          END
        ) AS ddgs_uom,

        -- CO2

        MAX(
          CASE
            WHEN poi.item_type = 'co2'
            THEN poi.qty
          END
        ) AS co2_qty,

        MAX(
          CASE
            WHEN poi.item_type = 'co2'
            THEN poi.uom
          END
        ) AS co2_uom,

        -- LOSS %

        ROUND(
          (
            (
              po.planned_input_qty
              - po.expected_output_qty
            )
            / NULLIF(
                po.planned_input_qty,
                0
              )
          ) * 100,
          2
        ) AS loss_percentage,

        -- STATUS

        CASE

          WHEN po.expected_output_qty = 0
          THEN 'Pending'

          WHEN po.expected_output_qty
            < po.planned_input_qty
          THEN 'Running'

          ELSE 'Completed'

        END AS status

      FROM production_operations po

      LEFT JOIN production_recipe pr
        ON pr.id = po.recipe_id

      LEFT JOIN users u
        ON u.id = po.created_by

      LEFT JOIN production_operation_items poi
        ON poi.production_operation_id = po.id

      WHERE
        COALESCE(po.is_deleted, false) = false

        AND (
          po.batch_number ILIKE $1
          OR pr.recipe_name ILIKE $1
          OR po.production_notes ILIKE $1
        )

      GROUP BY
        po.id,
        pr.recipe_name,
        u.name

      ORDER BY po.id DESC

      LIMIT $2 OFFSET $3
    `;

    const result =
      await client.query(
        query,
        [
          `%${search}%`,
          limit,
          offset,
        ]
      );

    return res.status(200).json({
      success: true,

      message:
        "Production list fetched successfully",

      data: result.rows,

      pagination: {
        totalRecords,
        currentPage: page,
        totalPages: Math.ceil(
          totalRecords / limit
        ),
        limit,
      },
    });

  } catch (error) {

    console.log(
      "GET PRODUCTION ERROR:",
      error
    );

    return res.status(500).json({
      success: false,

      message:
        error.message ||
        "Failed to fetch productions",
    });

  } finally {

    client.release();

  }
};


const deleteProduction = async (
  req,
  res
) => {

  const client = await pool.connect();

  try {

    const { id } = req.params;

    // CHECK EXISTING

    const checkQuery = `
      SELECT id
      FROM production_operations
      WHERE id = $1
      AND COALESCE(is_deleted, false) = false
    `;

    const checkResult =
      await client.query(
        checkQuery,
        [id]
      );

    if (
      checkResult.rows.length === 0
    ) {

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

    }

    // SOFT DELETE

    const deleteQuery = `
      UPDATE production_operations

      SET
        is_deleted = true

      WHERE id = $1

      RETURNING id
    `;

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

    return res.status(200).json({
      success: true,

      message:
        "Production deleted successfully",
    });

  } catch (error) {

    console.log(
      "DELETE PRODUCTION ERROR:",
      error
    );

    return res.status(500).json({
      success: false,

      message:
        error.message ||
        "Failed to delete production",
    });

  } finally {

    client.release();

  }
};


// const updateProduction = async (
//   req,
//   res
// ) => {

//   const client = await pool.connect();

//   try {

//     const { id } = req.params;

//     const {
//       recipe_id,
//       production_date,
//       batch_number,
//       planned_input_qty,
//       expected_output_qty,
//       production_notes,
//       ethanol_qty,
//       ddgs_qty,
//       co2_qty,
//     } = req.body;

//     // =========================
//     // CHECK EXISTING
//     // =========================

//     const checkQuery = `
//       SELECT id
//       FROM production_operations
//       WHERE id = $1
//       AND COALESCE(is_deleted, false) = false
//     `;

//     const checkResult =
//       await client.query(
//         checkQuery,
//         [id]
//       );

//     if (
//       checkResult.rows.length === 0
//     ) {

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

//     }

//     // =========================
//     // UPDATE PRODUCTION
//     // =========================

//     const updateQuery = `
//       UPDATE production_operations

//       SET
//         recipe_id = $1,

//         production_date = $2,

//         batch_number = $3,

//         planned_input_qty = $4,

//         expected_output_qty = $5,

//         production_notes = $6,

//         updated_at = NOW()

//       WHERE id = $7

//       RETURNING *
//     `;

//     const updateResult =
//       await client.query(
//         updateQuery,
//         [
//           recipe_id,
//           production_date,
//           batch_number,
//           planned_input_qty,
//           expected_output_qty,
//           production_notes,
//           id,
//         ]
//       );

//     // =========================
//     // UPDATE ETHANOL
//     // =========================

//     await client.query(
//       `
//       UPDATE production_operation_items

//       SET
//         qty = $1

//       WHERE
//         production_operation_id = $2
//         AND item_type = 'ethanol'
//       `,
//       [
//         ethanol_qty,
//         id,
//       ]
//     );

//     // =========================
//     // UPDATE DDGS
//     // =========================

//     await client.query(
//       `
//       UPDATE production_operation_items

//       SET
//         qty = $1

//       WHERE
//         production_operation_id = $2
//         AND item_type = 'ddgs'
//       `,
//       [
//         ddgs_qty,
//         id,
//       ]
//     );

//     // =========================
//     // UPDATE CO2
//     // =========================

//     await client.query(
//       `
//       UPDATE production_operation_items

//       SET
//         qty = $1

//       WHERE
//         production_operation_id = $2
//         AND item_type = 'co2'
//       `,
//       [
//         co2_qty,
//         id,
//       ]
//     );

//     return res.status(200).json({
//       success: true,

//       message:
//         "Production updated successfully",

//       data:
//         updateResult.rows[0],
//     });

//   } catch (error) {

//     console.log(
//       "UPDATE PRODUCTION ERROR:",
//       error
//     );

//     return res.status(500).json({
//       success: false,

//       message:
//         error.message ||
//         "Failed to update production",
//     });

//   } finally {

//     client.release();

//   }
// };


const updateProduction = async (req, res) => {

  const client = await pool.connect();

  try {

    await client.query("BEGIN");

    const { id } = req.params;

    const {
      recipe_id,
      production_date,
      batch_number,
      planned_input_qty,
      expected_output_qty,
      production_notes,
      ethanol_qty,
      ddgs_qty,
      co2_qty,
    } = req.body;

    const orgId = req.session.user.orgId;

    // =========================================
    // CHECK EXISTING PRODUCTION
    // =========================================

    const checkResult = await client.query(
      `
      SELECT *
      FROM production_operations
      WHERE id = $1
      AND COALESCE(is_deleted,false) = false
      `,
      [id]
    );

    if (checkResult.rows.length === 0) {

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

    }

    // =========================================
    // GET OLD ITEM QTY
    // =========================================

    const oldItemsResult = await client.query(
      `
      SELECT
        poi.product_id,
        poi.qty,
        poi.item_type
      FROM production_operation_items poi
      WHERE poi.production_operation_id = $1
      `,
      [id]
    );

    const oldMap = {};

    oldItemsResult.rows.forEach((item) => {
      oldMap[item.item_type] = {
        qty: Number(item.qty),
        product_id: item.product_id,
      };
    });

    // =========================================
    // UPDATE HEADER
    // =========================================

    const updateResult = await client.query(
      `
      UPDATE production_operations
      SET
        recipe_id = $1,
        production_date = $2,
        batch_number = $3,
        planned_input_qty = $4,
        expected_output_qty = $5,
        production_notes = $6,
        updated_at = NOW()
      WHERE id = $7
      RETURNING *
      `,
      [
        recipe_id,
        production_date,
        batch_number,
        planned_input_qty,
        expected_output_qty,
        production_notes,
        id,
      ]
    );

    // =========================================
    // NEW ITEMS
    // =========================================

    const newItems = [
      {
        item_type: "ethanol",
        qty: Number(ethanol_qty || 0),
      },
      {
        item_type: "ddgs",
        qty: Number(ddgs_qty || 0),
      },
      {
        item_type: "co2",
        qty: Number(co2_qty || 0),
      },
    ];

    // =========================================
    // UPDATE ITEMS + INVENTORY
    // =========================================

    for (const item of newItems) {

      const oldQty =
        oldMap[item.item_type]?.qty || 0;

      const productId =
        oldMap[item.item_type]?.product_id;

      const diffQty =
        item.qty - oldQty;

      // -----------------------------
      // UPDATE production items
      // -----------------------------

      await client.query(
        `
        UPDATE production_operation_items
        SET qty = $1
        WHERE
          production_operation_id = $2
          AND item_type = $3
        `,
        [
          item.qty,
          id,
          item.item_type,
        ]
      );

      // -----------------------------
      // UPDATE inventory stock
      // -----------------------------

      if (productId && diffQty !== 0) {

        await client.query(
          `
          UPDATE inventory_stock
          SET
            available_qty = available_qty + $1,
            updated_at = NOW()
          WHERE
            org_id = $2
            AND product_id = $3
          `,
          [
            diffQty,
            orgId,
            productId,
          ]
        );

      }

    }

    await client.query("COMMIT");

    return res.status(200).json({
      success: true,
      message: "Production updated successfully",
      data: updateResult.rows[0],
    });

  } catch (error) {

    await client.query("ROLLBACK");

    console.log(
      "UPDATE PRODUCTION ERROR:",
      error
    );

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

  } finally {

    client.release();

  }

};



module.exports = {
    createProduction,
    getProductions,
    deleteProduction,
    updateProduction,
    
}