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

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

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

    if (!orgId || !userId) {
      return res.status(401).json({ message: "Unauthorized" });
    }

    const {
      //   recipe_code,
      recipe_name,
      version,
      batch_base_qty,
      batch_uom,
      output,
      bom,
      process,
    } = req.body;

    //  Basic Validation
    if (!recipe_name || !bom || bom.length === 0) {
      return res.status(400).json({ message: "Missing required fields" });
    }

    // 🔥 Generate recipe_code (RCP-101 format)
    const codeRes = await client.query(`
  SELECT recipe_code 
  FROM production_recipe 
  ORDER BY id DESC 
  LIMIT 1
`);

    let newCode = "RCP-101";

    if (codeRes.rows.length > 0) {
      const lastCode = codeRes.rows[0].recipe_code; // e.g. RCP-105
      const lastNumber = parseInt(lastCode.split("-")[1], 10);
      const nextNumber = lastNumber + 1;
      newCode = `RCP-${nextNumber}`;
    }

    await client.query("BEGIN");
    //  1. Insert Recipe Header
    const recipeRes = await client.query(
      `
      INSERT INTO production_recipe
      (recipe_code, recipe_name, version, batch_base_qty, batch_uom,output, org_id, created_by)
      VALUES ($1,$2,$3,$4,$5,$6,$7,$8)
      RETURNING id
      `,
      [
        newCode,
        recipe_name,
        version || "v1.0",
        batch_base_qty,
        batch_uom,
        output || null,
        orgId,
        userId,
      ],
    );

    const recipeId = recipeRes.rows[0].id;
    //  1. Insert Version History (Initial)
    await client.query(
      `
  INSERT INTO production_recipe_versions
  (recipe_id, version, change_note, created_by)
  VALUES ($1, $2, $3, $4)
  `,
      [recipeId, version || "1.0", "Initial Release", userId],
    );

    //  2. Fetch item details (IMPORTANT)
    const itemIds = bom.map((b) => b.item_id);

    const itemRes = await client.query(
      `
      SELECT id, item_name, item_code, uom
      FROM procurement_itemmaster
      WHERE id = ANY($1)
      `,
      [itemIds],
    );

    const itemMap = {};
    itemRes.rows.forEach((item) => {
      itemMap[item.id] = item;
    });

    //  3. Insert BOM
    for (const b of bom) {
      const item = itemMap[b.item_id];

      if (!item) {
        throw new Error(`Invalid item_id: ${b.item_id}`);
      }

      await client.query(
        `
        INSERT INTO production_recipe_bom
        (recipe_id, item_id, item_name, item_code, qty,rate, uom)
        VALUES ($1,$2,$3,$4,$5,$6,$7)
        `,
        [
          recipeId,
          b.item_id,
          item.item_name, // snapshot
          item.item_code, // snapshot
          b.qty,
          b.rate,
          item.uom, // from master
        ],
      );
    }

    //  4. Insert Process Params
    if (process) {
      await client.query(
        `
        INSERT INTO production_recipe_process
        (recipe_id, temperature, ph)
        VALUES ($1,$2,$3)
        `,
        [recipeId, process.temperature, process.ph],
      );
    }

    await client.query("COMMIT");

    return res.status(201).json({
      success: true,
      message: "Recipe created successfully",
      data: { id: recipeId },
    });
  } catch (err) {
    await client.query("ROLLBACK");
    console.error(err);

    return res.status(500).json({
      success: false,
      message: err.message || "Something went wrong",
    });
  } finally {
    client.release();
  }
};

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

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

    if (!orgId) {
      return res.status(401).json({ message: "Unauthorized" });
    }

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

    const offset = (page - 1) * limit;

    //  MAIN QUERY
    const query = `
      SELECT 
        r.id,
        r.recipe_code,
        r.recipe_name,
        r.version,
        r.batch_base_qty,
        r.batch_uom,
        r.status,

       COALESCE(r.output, 'N/A') AS output,

  COALESCE(
    SUM(b.qty * COALESCE(b.rate, i.rate, 0)) 
    / NULLIF(r.batch_base_qty, 0),
    0
  ) AS cost_per_kl


      FROM production_recipe r

      LEFT JOIN production_recipe_bom b 
        ON b.recipe_id = r.id

      LEFT JOIN procurement_itemmaster i 
        ON i.id = b.item_id

      WHERE r.org_id = $1
        AND (
          r.recipe_code ILIKE $2 OR
          r.recipe_name ILIKE $2
        )

      GROUP BY r.id

      ORDER BY r.created_at DESC
      LIMIT $3 OFFSET $4
    `;

    const values = [orgId, `%${search}%`, limit, offset];

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

    //  COUNT QUERY (for pagination)
    const countRes = await client.query(
      `
      SELECT COUNT(*) FROM production_recipe
      WHERE org_id = $1
        AND (
          recipe_code ILIKE $2 OR
          recipe_name ILIKE $2
        )
      `,
      [orgId, `%${search}%`],
    );

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

    //  FORMAT RESPONSE (UI READY)
    const data = result.rows.map((r, index) => ({
      s_no: offset + index + 1,
      //   id: r.recipe_code,
      id: r.id, // numeric ID (important)
      recipe_code: r.recipe_code, // display value
      recipe_name: r.recipe_name,
      version: r.version || "v1.0",
      output: r.output || "N/A",

      cost_per_kl: Number(r.cost_per_kl || 0).toFixed(2),

      status: (r.status || "active").toUpperCase(),
    }));

    return res.status(200).json({
      success: true,
      data,
      pagination: {
        page: Number(page),
        limit: Number(limit),
        total,
      },
    });
  } catch (err) {
    console.error(err);
    return res.status(500).json({
      success: false,
      message: err.message || "Something went wrong",
    });
  } finally {
    client.release();
  }
};

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

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

    if (!orgId) {
      return res.status(401).json({ message: "Unauthorized" });
    }

    if (!id) {
      return res.status(400).json({ message: "Recipe ID is required" });
    }

    // 🔹 MAIN QUERY
    const query = `
      SELECT 
        r.id,
        r.recipe_code,
        r.recipe_name,
        r.batch_base_qty,
        r.batch_uom,
        r.output,

        b.item_id,
        b.item_name,
        b.qty,
        b.rate,
        b.uom,


  p.temperature,
  p.ph

      FROM production_recipe r

      LEFT JOIN production_recipe_bom b 
        ON b.recipe_id = r.id

        LEFT JOIN production_recipe_process p
  ON p.recipe_id = r.id

      WHERE r.id = $1
        AND r.org_id = $2
    `;

    const result = await client.query(query, [id, orgId]);

    // ❌ No data
    if (result.rows.length === 0) {
      return res.status(404).json({
        success: false,
        message: "Recipe not found",
      });
    }

    const rows = result.rows;

    // 🔹 HEADER (from first row)
    const recipe = {
      id: rows[0].id,
      recipe_code: rows[0].recipe_code,
      recipe_name: rows[0].recipe_name,
      batch_base_qty: Number(rows[0].batch_base_qty),
      batch_uom: rows[0].batch_uom,
      output: rows[0].output || "N/A",

      process: {
        temperature: rows[0].temperature || "",
        ph: rows[0].ph || "",
      },
    };

    // 🔹 ITEMS (BOM)
    const items = rows
      .filter((r) => r.item_id !== null)
      .map((r) => ({
        item_id: r.item_id,
        item_name: r.item_name,
        qty: Number(r.qty),
        rate: Number(r.rate || 0),
        uom: r.uom,
      }));

    return res.status(200).json({
      success: true,
      data: {
        ...recipe,
        items,
      },
    });
  } catch (err) {
    console.error(err);
    return res.status(500).json({
      success: false,
      message: err.message || "Something went wrong",
    });
  } finally {
    client.release();
  }
};

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

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

    if (!orgId || !userId) {
      return res.status(401).json({ message: "Unauthorized" });
    }

    if (!id) {
      return res.status(400).json({ message: "Recipe ID is required" });
    }

    const {
      recipe_name,
      version,
      batch_base_qty,
      batch_uom,
      output,
      bom,
      process,
      change_note, //  IMPORTANT
    } = req.body;

    if (!recipe_name || !bom || bom.length === 0) {
      return res.status(400).json({ message: "Missing required fields" });
    }

    await client.query("BEGIN");

    //  1. CHECK EXISTENCE
    const existing = await client.query(
      `SELECT * FROM production_recipe WHERE id = $1 AND org_id = $2`,
      [id, orgId],
    );

    if (existing.rows.length === 0) {
      throw new Error("Recipe not found");
    }

    //  2. UPDATE HEADER
    await client.query(
      `
      UPDATE production_recipe
      SET 
        recipe_name = $1,
        version = $2,
        batch_base_qty = $3,
        batch_uom = $4,
        output = $5,
        updated_at = NOW()
      WHERE id = $6 AND org_id = $7
      `,
      [
        recipe_name,
        version || existing.rows[0].version,
        batch_base_qty,
        batch_uom,
        output,
        id,
        orgId,
      ],
    );

    //  3. DELETE OLD BOM (REPLACE STRATEGY)
    await client.query(
      `DELETE FROM production_recipe_bom WHERE recipe_id = $1`,
      [id],
    );

    //  4. FETCH ITEM MASTER
    const itemIds = bom.map((b) => b.item_id);

    const itemRes = await client.query(
      `
      SELECT id, item_name, item_code, uom
      FROM procurement_itemmaster
      WHERE id = ANY($1)
      `,
      [itemIds],
    );

    const itemMap = {};
    itemRes.rows.forEach((item) => {
      itemMap[item.id] = item;
    });

    //  5. INSERT NEW BOM
    for (const b of bom) {
      const item = itemMap[b.item_id];

      if (!item) {
        throw new Error(`Invalid item_id: ${b.item_id}`);
      }

      await client.query(
        `
        INSERT INTO production_recipe_bom
        (recipe_id, item_id, item_name, item_code, qty, rate, uom)
        VALUES ($1,$2,$3,$4,$5,$6,$7)
        `,
        [
          id,
          b.item_id,
          item.item_name,
          item.item_code,
          b.qty,
          b.rate,
          item.uom,
        ],
      );
    }

    // 6. UPSERT PROCESS PARAMS
    await client.query(
      `DELETE FROM production_recipe_process WHERE recipe_id = $1`,
      [id],
    );

    if (process) {
      await client.query(
        `
        INSERT INTO production_recipe_process
        (recipe_id, temperature, ph)
        VALUES ($1,$2,$3)
        `,
        [id, process.temperature, process.ph],
      );
    }

    // 7. INSERT VERSION HISTORY (MOST IMPORTANT)
    await client.query(
      `
      INSERT INTO production_recipe_versions
      (recipe_id, version, change_note, created_by)
      VALUES ($1,$2,$3,$4)
      `,
      [
        id,
        version || existing.rows[0].version,
        change_note || "Recipe updated",
        userId,
      ],
    );

    await client.query("COMMIT");

    return res.status(200).json({
      success: true,
      message: "Recipe updated successfully",
    });
  } catch (err) {
    await client.query("ROLLBACK");
    console.error(err);

    return res.status(500).json({
      success: false,
      message: err.message || "Something went wrong",
    });
  } finally {
    client.release();
  }
};

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

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

    //  AUTH CHECK
    if (!orgId) {
      return res.status(401).json({
        success: false,
        message: "Unauthorized",
      });
    }

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

    const query = `
      SELECT 
        v.version,
        v.change_note,
        v.created_at,
        COALESCE(u.name, 'Unknown') AS created_by

      FROM production_recipe_versions v

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

      INNER JOIN production_recipe r 
        ON r.id = v.recipe_id

      WHERE v.recipe_id = $1
        AND r.org_id = $2

      ORDER BY v.created_at DESC
    `;

    const result = await client.query(query, [id, orgId]);

    //  HANDLE EMPTY
    if (result.rows.length === 0) {
      return res.status(200).json({
        success: true,
        data: [],
        message: "No version history found",
      });
    }

    //  FORMAT RESPONSE
    const data = result.rows.map((v) => ({
      version: v.version,
      change_note: v.change_note,
      created_by: v.created_by,
      created_at: v.created_at,
    }));

    return res.status(200).json({
      success: true,
      data,
    });
  } catch (err) {
    console.error("getRecipeVersions error:", err);

    return res.status(500).json({
      success: false,
      message: err.message || "Internal Server Error",
    });
  } finally {
    client.release();
  }
};

module.exports = {
  createRecipe,
  getRecipes,
  getRecipeById,
  getRecipeVersions,
  updateRecipe,
};
