
const fs = require("fs");
const express = require("express");
const cors = require("cors");
const multer = require("multer");
const pool = require("./database");

const app = express();
const port = 5000;

app.use(cors());

app.use(express.json());



if (!fs.existsSync("./assets")) {
  fs.mkdirSync("./assets");
}


const storage = multer.diskStorage({
  destination: (req, file, cb) => {
    cb(null, "./assets");
  },
  filename: (req, file, cb) => {
    cb(null, `${Date.now()}-${file.originalname}`);
  },
});
const upload = multer({ storage });


app.use('/assets', express.static('assets'));
const normalize = (val) => {
  if (val === "" || val === "null" || val === undefined) return null;
  return val;
};

app.post("/submit-design", upload.single("image"), async (req, res) => {
  const { cus_id, customname, designId, designname, designdesc, active, activefrom, activetill } = req.body;
  const host = req.protocol + '://' + req.get('host');
  const image_path = req.file ? `${host}/assets/${req.file.filename}` : null;

  try {
    const query = `
      INSERT INTO design_master 
      (cus_id, customer_name, design_id, design_name, design_desc, image_path, is_active, active_from, active_till)
      VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
      RETURNING *;
    `;

    const values = [
      cus_id,
      customname,
      designId,
      designname,
      designdesc,
      image_path,
      active === "true",
      null, // normalize(activefrom),
      null, // normalize(activetill),
    ];

    const result = await pool.query(query, values);
    res.status(201).json({ message: "Design submitted successfully", data: result.rows[0] });
  } catch (error) {
    console.error("Error inserting data:", error.message);
    res.status(500).json({ message: "DesignId already exists" });
  }
});

app.put("/update-design", upload.single("image"), async (req, res) => {
  const { cus_id, customname, designId, designname, designdesc, active, activefrom, activetill } = req.body;
  const host = req.protocol + '://' + req.get('host');
  const imagePath = req.file ? `${host}/assets/${req.file.filename}` : null;

  try {
    let query = `
      UPDATE design_master
      SET design_name = $1, design_desc = $2, is_active = $3, active_from = $4, active_till = $5, cus_id = $6, customer_name = $7
    `;
    const values = [
      designname,
      designdesc,
      active === "true",
      null, // normalize(activefrom),
      null, // normalize(activetill),
      cus_id === "" ? null : cus_id,
      customname === "" ? null : customname
    ];

    if (imagePath) {
      query += `, image_path = $8 WHERE design_id = $9`;
      values.push(imagePath, designId);
    } else {
      query += ` WHERE design_id = $8`;
      values.push(designId);
    }

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

    if (result.rowCount === 0) {
      return res.status(404).json({ message: "Design not found." });
    }

    res.status(200).json({ message: "Design updated successfully!" });
  } catch (error) {
    console.error("Error updating design:", error);
    res.status(500).json({ message: "Failed to update design." });
  }
});

const path = require("path");

app.get("/download/:filename", (req, res) => {
  const filename = decodeURIComponent(req.params.filename);
  const filePath = path.join(__dirname, "assets", filename);

  res.download(filePath, filename, (err) => {
    if (err) {
      console.error("Error downloading file:", err);
      res.status(500).send("Failed to download the file.");
    }
  });
});




app.post("/filter-record", (req, res) => {
  const { filters } = req.body;

  
  if (!Array.isArray(filters) || filters.length === 0) {
    return res.status(400).json({ message: "No filters provided" });
  }

  const validColumns = [
    "design_id",
    "design_name",
    "design_desc",
    "is_active",
    "active_from",
    "active_till"
  ];

  const conditions = [];
  const params = [];
  let paramIndex = 1;

  filters.forEach(({ column, filterType, value, startDate, endDate }) => {
    if (!validColumns.includes(column)) {
      return res.status(400).json({ message: `Invalid column: ${column}` });
    }

    if (filterType === "equals") {
      conditions.push(`${column} = $${paramIndex}`);
      params.push(value);
      paramIndex++;
    } else if (filterType === "contains") {
      conditions.push(`${column} ILIKE '%' || $${paramIndex} || '%'`);
      params.push(value);
      paramIndex++;
    } else if (filterType === "between") {
      
      if (!startDate || !endDate) {
        return res.status(400).json({ message: "Both startDate and endDate are required for 'between' filter." });
      }

      
      conditions.push(`${column} BETWEEN $${paramIndex} AND $${paramIndex + 1}`);
      params.push(startDate, endDate); 
      paramIndex += 2;
    }
  });

  if (conditions.length === 0) {
    return res.status(400).json({ message: "No valid filters provided." });
  }

  const query = `SELECT * FROM design_master WHERE ${conditions.join(" AND ")}`;

  
  pool.query(query, params, (err, result) => {
    if (err) {
      console.error("Database error:", err.message);
      return res.status(500).json({ message: "Internal server error.", error: err.message });
    }

    if (result.rows.length === 0) {
      return res.status(404).json({ message: "No records found." });
    }

    
    res.json(result.rows);
  });
});


app.get("/get-designs", async (req, res) => {
  const { page, limit } = req.query;

  const pageNumber = parseInt(page) || 1;
  const limitNumber = parseInt(limit) || 50;
  const offset = (pageNumber - 1) * limitNumber;

  try {
    const query = `SELECT * FROM design_master ORDER BY created_at DESC, design_id DESC LIMIT $1 OFFSET $2;`;
    const result = await pool.query(query, [limitNumber, offset]);

    const countQuery = `SELECT COUNT(*) AS total FROM design_master;`;
    const countResult = await pool.query(countQuery);
    const totalRecords = parseInt(countResult.rows[0].total, 10);

    res.status(200).json({
      data: result.rows,
      total: totalRecords,
      currentPage: pageNumber,
      totalPages: Math.ceil(totalRecords / limitNumber),
    });
  } catch (error) {
    console.error("Error fetching paginated data:", error.message);
    res.status(500).json({ message: "Failed to fetch paginated data." });
  }
});

     


app.post('/filter', async (req, res) => {
  const { filters, page = 1, limit = 10 } = req.body;

  if (!Array.isArray(filters) || filters.length === 0) {
    return res.status(400).json({ message: 'Filters array required' });
  }

  try {
    let whereClauses = [];
    let values = [];

    filters.forEach((filter) => {
      const { column, logic, value, startDate, endDate } = filter;
      let clause = '';
      let paramIndex = values.length + 1;

      const allowedColumns = ['order_id', 'customer_name','order_price', 'order_datime', 'overall_status', 'order_delivery_date','active'];
      if (!allowedColumns.includes(column)) {
        throw new Error('Invalid column in filter');
      }
       if (column === 'active') {
  const activeBool = value === 'Yes';
  clause = `o.${column} = $${paramIndex}`;
  values.push(activeBool);
} else if (logic === 'equals') {
  clause = `o.${column} = $${paramIndex}`;
  values.push(value);
} else if (logic === 'contains') {
  clause = `o.${column} ILIKE $${paramIndex}`;
  values.push(`%${value}%`);
} else if (
  logic === 'between' &&
  (column === 'order_datime' || column === 'order_delivery_date')
) {
  clause = `o.${column} BETWEEN $${paramIndex} AND $${paramIndex + 1}`;
  values.push(startDate, endDate);
} else {
  throw new Error('Invalid filter logic');
}

      whereClauses.push(clause);
    });

    const whereQuery = whereClauses.length > 0 ? 'WHERE ' + whereClauses.join(' AND ') : '';
    const offset = (page - 1) * limit;

    const countQuery = `
      SELECT COUNT(*) AS total
      FROM textile_orders o
      LEFT JOIN design_master d ON o.design_id = d.design_id
      ${whereQuery}
    `;
    const countResult = await pool.query(countQuery, values);
    const total = parseInt(countResult.rows[0].total);
    const totalPages = Math.ceil(total / limit);

    const query = `
      SELECT o.*, d.design_name
      FROM textile_orders o
      LEFT JOIN design_master d ON o.design_id = d.design_id
      ${whereQuery}
      ORDER BY o.order_datime DESC
      LIMIT $${values.length + 1} OFFSET $${values.length + 2}
    `;
    values.push(limit, offset);

    const result = await pool.query(query, values);
    res.json({ orders: result.rows, totalPages });
  } catch (err) {
    res.status(500).json({ message: err.message });
  }
});





app.get("/global-search", async (req, res) => {
  const { id } = req.query;

  if (!id || id.trim() === "") {
    return res.status(400).json({ message: "Search term is required" });
  }

  try {
    const query = `
      SELECT * FROM (
        SELECT 
          'design_master' AS source, 
          design_id AS id, 
          design_name AS result, 
          design_desc AS description
        FROM design_master
        WHERE design_id::TEXT ILIKE $1

        UNION ALL

        SELECT 
          'order_master' AS source, 
          o.order_id AS id, 
          c.customer_name AS result, 
          o.order_status AS description
        FROM order_master o
        JOIN customer_master c ON o.cus_id = c.cus_id
        WHERE o.order_id::TEXT ILIKE $1

        UNION ALL

        SELECT 
          'lead' AS source,
          lead_id AS id,
          importance AS result,
          lead_status AS description
        FROM lead
        WHERE lead_id::TEXT ILIKE $1
      ) AS combined
      ORDER BY result;
    `;

    const values = [`%${id}%`];
    const results = await pool.query(query, values);

    res.json(results.rows || []);
  } catch (error) {
    console.error("Error fetching search results:", error);
    res.status(500).json({ message: "Internal server error" });
  }
});

app.get('/Order/:id', async (req, res) => {
  const { id } = req.params;

  try {
  
    const query = 'SELECT * FROM textile_orders WHERE order_id = $1';
    const result = await pool.query(query, [id]);

    if (result.rows.length === 0) {
      return res.status(404).json({ message: 'Order not found' });
    }

    res.json(result.rows[0]); 
  } catch (error) {
    console.error('Error fetching order:', error.message);
    res.status(500).json({ message: 'Internal server error' });
  }
});

/*
app.get('/get-design/:id', async (req, res) => {
  const { id } = req.params; 
  try {
    const result = await pool.query('SELECT * FROM design_master WHERE design_id = $1', [id]);
    if (result.rows.length === 0) {
      return res.status(404).json({ message: 'Design not found' });
    }
    res.json(result.rows[0]);
  } catch (error) {
    console.error(error);
    res.status(500).json({ message: 'Error fetching design' });
  }
});
*/
app.get('/get-designsid', async (req, res) => {
  const { designId } = req.query;

  if (!designId) {
    return res.status(400).json({ message: 'Design ID required' });
  }

  try {
    const result = await pool.query(
      'SELECT * FROM design_master WHERE design_id = $1',
      [designId]
    );

    if (result.rows.length === 0) {
      return res.status(404).json({ message: 'Design not found' });
    }

    res.json(result.rows[0]);
  } catch (error) {
    console.error(error);
    res.status(500).json({ message: 'Error fetching design' });
  }
});


app.post('/log-activity', async (req, res) => {
  const { sub_order_id, order_id, dept_id, dept_status, order_status } = req.body;

  try {
    await pool.query(`
      INSERT INTO orders1_auditlog 
      (sub_order_id, order_id, dept_id, dept_status, order_status, activity_date) 
      VALUES ($1, $2, $3, $4, $5, CURRENT_TIMESTAMP)
    `, [sub_order_id, order_id, dept_id, dept_status, order_status]);

    res.status(200).json({ message: "Activity logged successfully" });
  } catch (err) {
    console.error("Error logging activity:", err);
    res.status(500).json({ error: "Failed to log activity" });
  }
});


app.get('/api/all-orders', async (req, res) => {
  try {
    const result = await pool.query(`
      SELECT 
        a.sub_order_id,
        a.order_id,
        a.lead_id,
        a.design_id,
        a.suborder_date,
        a.actual_delivery_date,
        a.suborder_dept_flow_status,
        (EXTRACT(epoch FROM (a.actual_delivery_date - CURRENT_DATE)) / 86400)::int AS days_diff,
        CASE 
          WHEN (EXTRACT(epoch FROM (a.actual_delivery_date - CURRENT_DATE)) / 86400)::int < 0 THEN 'Y' 
          ELSE 'N' 
        END AS hasbreached
      FROM 
        public.sub_order a
      WHERE 
        a.suborder_dept_flow_status IN ('todo', 'inprogress')
      ORDER BY 
        ABS((EXTRACT(epoch FROM (a.actual_delivery_date - CURRENT_DATE)) / 86400)::int) Desc;
    `);

    res.json(result.rows);
  } catch (err) {
    console.error('Error fetching all orders:', err);
    res.status(500).json({ error: 'Failed to fetch orders' });
  }
});
app.get('/api/dept-status-summary', async (req, res) => {
  try {
    const result = await pool.query(`
      SELECT 
        so.sub_order_id,
        so.order_id,
        so.design_id,
        so.suborder_date,
        so.dept_id,
        so.dept_status,
        COUNT(*) AS total_count
      FROM sub_order so
      WHERE so.dept_status != 'done'
      GROUP BY so.sub_order_id, so.order_id, so.dept_id, so.dept_status
      ORDER BY so.sub_order_id;
    `);

    res.json(result.rows);
  } catch (err) {
    console.error('Error fetching dept summary:', err);
    res.status(500).json({ error: 'Failed to fetch dept summary' });
  }
});



app.get('/search-customers', async (req, res) => {
  const { query } = req.query;
  try {
    const result = await pool.query(
      `SELECT cus_id, customer_name, '' AS city FROM customer_master WHERE customer_name ILIKE $1 LIMIT 10`,
      [`%${query}%`]
    );
    res.json(result.rows);
  } catch (err) {
    console.error('Error searching customers', err);
    res.status(500).json({ message: 'Internal Server Error' });
  }
});
const safeValue = (val) => {
  return val === "" || val === undefined ? null : val;
};

app.post("/customermaster", async (req, res) => {
  const {
    cus_id,
    customer_name,
    mobile1,
    mobile2,
    email,
    gst_no,
  } = req.body;

  try {
    const query = `
      INSERT INTO customer_master (
        cus_id, customer_name, mobile1, mobile2, email, gst_no
      ) VALUES (
        $1, $2, $3, $4, $5, $6
      )
      RETURNING *;
    `;
    const values = [
      safeValue(cus_id),
      customer_name,
      safeValue(mobile1),
      safeValue(mobile2),
      email,
      safeValue(gst_no),
    ];

    const result = await pool.query(query, values);
    res.status(201).json({ message: "Customer added successfully", data: result.rows[0] });
  } catch (err) {
    console.error("Insert error:", err);

    if (err.code === "23505") {
      let field = "unknown";
      if (err.detail.includes("cus_id")) field = "Customer ID";
      else if (err.detail.includes("mobile1")) field = "Mobile1";

      return res.status(400).json({ message: `${field} already exists!` });
    }

    res.status(500).json({ message: "Error inserting customer" });
  }
});

app.get("/customers/:id", async (req, res) => {
  const { id } = req.params;

  try {
    const query = "SELECT * FROM customer_master WHERE cus_id = $1";
    const { rows } = await pool.query(query, [id]);

    if (rows.length > 0) {
      res.status(200).json(rows[0]);
    } else {
      res.status(404).json({ message: "Customer not found" });
    }
  } catch (error) {
    console.error(error.message);
    res.status(500).json({ message: "Error fetching customer details" });
  }
});

app.get("/next-customer-id", async (req, res) => {
  try {
    const checkCount = await pool.query("SELECT COUNT(*) AS count FROM customer_master");
    const count = parseInt(checkCount.rows[0].count, 10);
    
    if (count === 0) {
      await pool.query(`
        DO $$
        BEGIN
          IF EXISTS (SELECT 1 FROM pg_class WHERE relname = 'customer_seq') THEN
            ALTER SEQUENCE customer_seq RESTART WITH 1;
          ELSE
            CREATE SEQUENCE customer_seq START 1;
          END IF;
        END
        $$;
      `);
    } else {
      await pool.query(`
        DO $$
        BEGIN
          IF NOT EXISTS (SELECT 1 FROM pg_class WHERE relname = 'customer_seq') THEN
            CREATE SEQUENCE customer_seq START 1;
          END IF;
        END
        $$;
      `);
    }

    const result = await pool.query(`SELECT nextval('customer_seq') AS next_num`);
    const num = result.rows[0].next_num;

    const nextId = `C-${num.toString().padStart(4, "0")}`;

    res.json({ nextId });
  } catch (err) {
    console.error("Error generating next customer ID:", err);
    res.status(500).json({ message: "Error generating next customer ID" });
  }
});


app.put("/customers/:id", async (req, res) => {
  const { id } = req.params;
  const {
    customer_name,
    mobile1,
    mobile2,
    email,
    gst_no,
  } = req.body;

  try {
    const query = `
      UPDATE customer_master
      SET customer_name = $1,
          mobile1 = $2,
          mobile2 = $3,
          email = $4,
          gst_no = $5
      WHERE cus_id = $6
      RETURNING *;
    `;

    const values = [
      customer_name,
      safeValue(mobile1),
      safeValue(mobile2),
      email,
      safeValue(gst_no),
      id,
    ];

    const result = await pool.query(query, values);
    res.status(200).json({ message: "Customer updated successfully", data: result.rows[0] });
  } catch (err) {
    console.error("Update error:", err);

    if (err.code === "23505") {
      let field = "unknown";
      if (err.detail.includes("cus_id")) field = "Customer ID";
      else if (err.detail.includes("mobile1")) field = "Mobile1";

      return res.status(400).json({ message: `${field} already exists!` });
    }

    res.status(500).json({ message: "Error updating customer details" });
  }
});
app.put("/api/customers1/:id", async (req, res) => {
  const { id } = req.params;
  const {
    customer_name,
    mobile1,
    mobile2,
    email,
    gst_no,
  } = req.body;

  try {
    const query = `
      UPDATE customer_master
      SET customer_name = $1,
          mobile1 = $2,
          mobile2 = $3,
          email = $4,
          gst_no = $5
      WHERE cus_id = $6
    `;

    const values = [
      customer_name,
      safeValue(mobile1),
      safeValue(mobile2) || " ",
      email,
      safeValue(gst_no),
      id,
    ];

    await pool.query(query, values);
    res.status(200).json({ message: "Customer updated successfully" });
  } catch (err) {
    console.error("Update error:", err);
    res.status(500).json({ message: "Error updating customer details" });
  }
});


app.get('/api/customers3', async (req, res) => {
  const { search } = req.query;
  console.log("Customer search string received:", search);

  try {
    const searchPattern = `%${search.toLowerCase()}%`;
    const result = await pool.query(
      `SELECT * FROM customer_master 
       WHERE LOWER(customer_name) LIKE $1 
          OR LOWER(cus_id) LIKE $1 
          OR LOWER(mobile1) LIKE $1 
          OR LOWER(mobile2) LIKE $1
          OR LOWER(email) LIKE $1
       LIMIT 10`,
      [searchPattern]
    );

    res.json({ customers: result.rows });
  } catch (err) {
    console.error("Error:", err);
    res.status(500).json({ error: "Internal server error" });
  }
});




app.get("/api/customers/:cus_id/designs", async (req, res) => {
  const { cus_id } = req.params;
  try {
    const result = await pool.query(
      "SELECT design_id, design_name, design_desc, image_path, is_active FROM design_master WHERE cus_id = $1",
      [cus_id]
    );
    res.json(result.rows);
  } catch (err) {
    console.error("Error fetching customer designs:", err);
    res.status(500).json({ error: "Failed to fetch designs" });
  }
});

app.get("/customertable", async (req, res) => {
  const page = parseInt(req.query.page) || 1;
  const limit = parseInt(req.query.limit) || 10;
  const offset = (page - 1) * limit;
  const search = req.query.search || "";

  try {
    let totalQuery = "SELECT COUNT(*) FROM customer_master";
    let dataQuery = `
      SELECT cus_id, customer_name, mobile1, mobile2, email, gst_no
      FROM customer_master
    `;
    let queryParams = [];
    let countParams = [];

    if (search) {
      const searchPattern = `%${search}%`;
      const whereSQL = ` WHERE COALESCE(cus_id, '') ILIKE $1 
                           OR COALESCE(customer_name, '') ILIKE $1 
                           OR COALESCE(mobile1, '') ILIKE $1 
                           OR COALESCE(mobile2, '') ILIKE $1
                           OR COALESCE(email, '') ILIKE $1
                           OR COALESCE(gst_no, '') ILIKE $1`;
      totalQuery += whereSQL;
      countParams.push(searchPattern);

      dataQuery += whereSQL;
      dataQuery += ` ORDER BY cus_id LIMIT $2 OFFSET $3`;
      queryParams.push(searchPattern, limit, offset);
    } else {
      dataQuery += ` ORDER BY cus_id LIMIT $1 OFFSET $2`;
      queryParams.push(limit, offset);
    }

    const { rows: totalRows } = await pool.query(totalQuery, countParams);
    const totalCount = parseInt(totalRows[0].count);
    const totalPages = Math.ceil(totalCount / limit);

    const { rows } = await pool.query(dataQuery, queryParams);

    res.json({
      customers: rows,
      totalPages,
      currentPage: page,
    });
  } catch (err) {
    console.error("Error fetching customer data:", err);
    res.status(500).json({ message: "Error fetching customer data" });
  }
});



app.post("/filtercustomers", async (req, res) => {
  const { filters, page = 1, limit = 10 } = req.body;
  const offset = (page - 1) * limit;

  let whereClauses = [];
  let values = [];

  filters.forEach((filter, index) => {
    const { column, logic, value, from, to } = filter;
    const col = column;

    if (logic === "equals") {
      whereClauses.push(`${col} = $${values.length + 1}`);
      values.push(value);
    } else if (logic === "contains") {
      whereClauses.push(`${col} ILIKE $${values.length + 1}`);
      values.push(`%${value}%`);
    } else if (logic === "between") {
      whereClauses.push(`${col} BETWEEN $${values.length + 1} AND $${values.length + 2}`);
      values.push(from, to);
    }
  });

  const whereSQL = whereClauses.length > 0 ? `WHERE ${whereClauses.join(" AND ")}` : "";

  try {
    const countQuery = `SELECT COUNT(*) FROM customer_master ${whereSQL}`;
    const countResult = await pool.query(countQuery, values);
    const totalCount = parseInt(countResult.rows[0].count);
    const totalPages = Math.ceil(totalCount / limit);

    const dataQuery = `
      SELECT cus_id, customer_name, mobile1, mobile2, email, gst_no
      FROM customer_master
      ${whereSQL}
      ORDER BY cus_id
      LIMIT $${values.length + 1} OFFSET $${values.length + 2}
    `;
    values.push(limit, offset);

    const dataResult = await pool.query(dataQuery, values);

    res.json({
      customers: dataResult.rows,
      totalPages,
      currentPage: page,
    });
  } catch (error) {
    console.error("Error filtering customers:", error);
    res.status(500).json({ message: "Error filtering customers" });
  }
});



/*
app.post('/login', async (req, res) => {
  try {
    const { username, password } = req.body;
    console.log("Incoming login:", username, password); 

    if (!username || !password) {
      return res.status(400).json({ success: false, message: "Missing fields" });
    }

    const result = await pool.query(
      'SELECT * FROM user_access_control WHERE user_name = $1 AND user_password = $2',
      [username, password]
    );

   if (result.rows.length > 0) {
  const user = result.rows[0]; 
  res.json({
    success: true,
    message: 'Login successful',
    user_name: user.user_name,
     
  });
} else {
  res.json({ success: false, message: 'Invalid username or password' });
}

  } catch (err) {
    console.error(" Login Route Error:", err); 
    res.status(500).json({ success: false, message: 'Server error' });
  }
});
*/
const bcrypt = require('bcrypt');

app.post('/login', async (req, res) => {
  try {
    const username = (req.body.username || '').trim();
    const password = req.body.password || '';

    if (!username || !password) {
      return res.json({ success: false, message: "Invalid username or password" });
    }

    const result = await pool.query(
      'SELECT * FROM user_access_control WHERE LOWER(user_name) = LOWER($1)',
      [username]
    );

    if (result.rows.length === 0) {
      return res.json({ success: false, message: "Invalid username or password" });
    }

    const user = result.rows[0];
    const isHashed = user.user_password.startsWith('$2b$');
    let isMatch = false;

    if (isHashed) {
      isMatch = await bcrypt.compare(password, user.user_password);
    } else {
      isMatch = password === user.user_password;
      if (isMatch) {
        const hashed = await bcrypt.hash(password, 10);
        await pool.query(
          'UPDATE user_access_control SET user_password = $1 WHERE user_name = $2',
          [hashed, user.user_name]
        );
      }
    }

    if (!isMatch) {
      return res.json({ success: false, message: "Invalid username or password" });
    }

    return res.json({ success: true, user_name: user.user_name, user_role: user.user_role });
  } catch (err) {
    console.error('Login Route Error:', err);
    return res.status(500).json({ success: false, message: 'Server error' });
  }
});


app.put('/approve-strike-off-status', async (req, res) => {
  const { order_id } = req.body;

  try {
    await pool.query(
      `UPDATE textile_orders
       SET dept_strike_off_approval = 'approved'
       WHERE order_id = $1`,
      [order_id]
    );

    res.json({ message: 'Strike-off approval updated in textile_orders!' });
  } catch (err) {
    console.error('Approval update error:', err);
    res.status(500).json({ error: 'Database update failed' });
  }
});

app.get("/api/total-customers", async (req, res) => {
  try {
    const result = await pool.query(`
       SELECT COUNT(DISTINCT cus_id) AS total_customers
      FROM order_master;
    `);
    res.json(result.rows[0]);
  } catch (err) {
    console.error("Error fetching total customers:", err);
    res.status(500).send("Server Error");
  }
});

app.get("/api/todays-customers", async (req, res) => {
  try {
    const result = await pool.query(`
     SELECT COUNT(DISTINCT cus_id) AS count
      FROM order_master
      WHERE DATE(order_date) = CURRENT_DATE
        AND cus_id NOT IN (
          SELECT DISTINCT cus_id
          FROM order_master
          WHERE DATE(order_date) < CURRENT_DATE
        );
    `);
    res.json(result.rows[0]);
  } catch (err) {
    console.error("Error fetching today's customers:", err);
    res.status(500).send("Server Error");
  }
});

app.get("/api/customer-status-trend", async (req, res) => {
  try {
    const result = await pool.query(`
      WITH first_orders AS (
        SELECT 
          cus_id,
          MIN(order_date) AS first_order_date
        FROM order_master
        GROUP BY cus_id
      ),
      monthly_customers AS (
        SELECT
          TO_CHAR(order_date, 'Mon-YYYY') AS month_year,
          cus_id
        FROM order_master
WHERE DATE_TRUNC('month', order_date) >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '5 months')
      )
      SELECT
        mc.month_year,
        COUNT(DISTINCT mc.cus_id) AS total_customers,
        COUNT(DISTINCT CASE
          WHEN DATE_TRUNC('month', fo.first_order_date) = 
               DATE_TRUNC('month', TO_DATE(mc.month_year, 'Mon-YYYY'))
          THEN mc.cus_id
        END) AS new_customers,
        COUNT(DISTINCT CASE
          WHEN DATE_TRUNC('month', fo.first_order_date) < 
               DATE_TRUNC('month', TO_DATE(mc.month_year, 'Mon-YYYY'))
          THEN mc.cus_id
        END) AS returning_customers
      FROM monthly_customers mc
      JOIN first_orders fo
        ON mc.cus_id = fo.cus_id
      GROUP BY mc.month_year
      ORDER BY DATE_TRUNC('month', TO_DATE(mc.month_year, 'Mon-YYYY'));
    `);

    res.json(result.rows);
  } catch (err) {
    console.error("Error fetching customer status trend:", err);
    res.status(500).send("Server Error");
  }
});

app.get("/api/total-pending-customers", async (req, res) => {
  try {
    const result = await pool.query(`
     SELECT COUNT(DISTINCT cus_id) AS total_pending_customers
FROM order_master
WHERE LOWER(order_status) = 'todo'
   OR LOWER(order_status) = 'inprogress';

    `);

    res.json({
      total_pending_customers: parseInt(result.rows[0].total_pending_customers, 10)
    });
  } catch (err) {
    console.error("Error fetching total pending customers:", err);
    res.status(500).send("Server Error");
  }
});


app.get("/api/customer-trend", async (req, res) => {
  try {
    const result = await pool.query(`
      WITH months AS (
        SELECT TO_CHAR(DATE_TRUNC('month', NOW()) - (INTERVAL '1 month' * g), 'YYYY-MM') AS month_year
        FROM generate_series(0, 6) g
      )
      SELECT m.month_year,
             COALESCE(COUNT(DISTINCT o.cus_id), 0) AS customer_count
      FROM months m
      LEFT JOIN order_master o
        ON TO_CHAR(o.order_date, 'YYYY-MM') = m.month_year
      GROUP BY m.month_year
      ORDER BY m.month_year;
    `);

    res.json(result.rows);
  } catch (err) {
    console.error("Error fetching customer trend:", err);
    res.status(500).send("Error fetching customer trend");
  }
});

app.get("/api/customers-by-month/:month_year", async (req, res) => {
  const { month_year } = req.params;

  try {
    const result = await pool.query(
      `
      SELECT 
        c.customer_name,
        o.order_id,
        s.sub_order_id,
                o.order_price

      FROM order_master o
      LEFT JOIN customer_master c ON c.cus_id = o.cus_id
      LEFT JOIN sub_order s ON s.order_id = o.order_id
      WHERE TO_CHAR(o.order_date, 'YYYY-MM') = $1
      ORDER BY o.cus_id, o.order_id, s.sub_order_id;
      `,
      [month_year]
    );

    res.json(result.rows);
  } catch (err) {
    console.error("Error fetching customers by month:", err);
    res.status(500).send("Server Error");
  }
});

app.get('/api/total-vs-closed-orders', async (req, res) => {
  try {
    const result = await pool.query(`
      WITH months AS (
        SELECT TO_CHAR(DATE_TRUNC('month', NOW()) - (INTERVAL '1 month' * g), 'Mon-YYYY') AS month_year
        FROM generate_series(0, 5) g
      )
      SELECT 
        m.month_year,
        COALESCE(COUNT(o.sub_order_id), 0) AS total_orders,
        COALESCE(SUM(CASE WHEN o.suborder_dept_flow_status = 'delivered' THEN 1 ELSE 0 END), 0) AS closed_orders,
        COALESCE(SUM(CASE WHEN o.suborder_dept_flow_status!= 'delivered' THEN 1 ELSE 0 END), 0) AS pending_orders
      FROM months m
      LEFT JOIN sub_order o
        ON TO_CHAR(o.suborder_date, 'Mon-YYYY') = m.month_year
      GROUP BY m.month_year
      ORDER BY TO_DATE(m.month_year, 'Mon-YYYY');
    `);
    res.json(result.rows);
  } catch (err) {
    console.error(err);
    res.status(500).send("Server Error");
  }
});


app.get('/api/pending-orders', async (req, res) => {
  try {
    const result = await pool.query(`
      SELECT COUNT(*) AS pending_count
      FROM sub_order
      WHERE suborder_dept_flow_status = 'todo' OR suborder_dept_flow_status = 'inprogress'
    `);
    res.json({ pending_orders: parseInt(result.rows[0].pending_count) });
  } catch (err) {
    console.error("Error fetching pending orders:", err);
    res.status(500).send("Server Error");
  }
});

app.get("/api/completed-orders-30days-summary", async (req, res) => {
  const { fromDate, toDate, search } = req.query;
  try {
    let whereClauses = [
      "(s.suborder_dept_flow_status = 'delivered' OR s.dept_status = 'done' OR om.actual_delivery_date IS NOT NULL OR LOWER(om.order_status) = 'delivered' OR LOWER(om.order_status) = 'completed')"
    ];
    let params = [];

    if (fromDate && req.query.preset !== 'above15days' && req.query.preset !== 'over15days') {
      params.push(fromDate);
      whereClauses.push(`COALESCE(s.suborder_date, om.order_date, s.actual_delivery_date, om.actual_delivery_date)::date >= $${params.length}`);
    }

    if (toDate && req.query.preset !== 'above15days' && req.query.preset !== 'over15days') {
      params.push(toDate);
      whereClauses.push(`COALESCE(s.suborder_date, om.order_date, s.actual_delivery_date, om.actual_delivery_date)::date <= $${params.length}`);
    }

    if (search) {
      params.push(`%${search.trim()}%`);
      whereClauses.push(`(
        c.customer_name ILIKE $${params.length} OR
        s.design_id ILIKE $${params.length} OR
        s.design_style ILIKE $${params.length} OR
        s.sub_order_id ILIKE $${params.length} OR
        s.order_id ILIKE $${params.length}
      )`);
    }

    const whereSql = "WHERE " + whereClauses.join(" AND ");

    const result = await pool.query(`
      SELECT 
        COUNT(s.sub_order_id) as total_completed_orders,
        COUNT(s.sub_order_id) as total_completed_suborders,
        COALESCE(SUM(CASE WHEN s.delivered_weight::text ~ '^[0-9]+(\\.[0-9]+)?$' THEN s.delivered_weight::numeric ELSE 0 END), 0) as total_delivered_weight,
        COALESCE(SUM(CASE WHEN s.inward_weight::text ~ '^[0-9]+(\\.[0-9]+)?$' THEN s.inward_weight::numeric ELSE 0 END), 0) as total_inward_weight,
        COALESCE(SUM(s.suborder_price), 0) as total_completed_value,
        CASE 
          WHEN SUM(CASE WHEN s.inward_weight::text ~ '^[0-9]+(\\.[0-9]+)?$' THEN s.inward_weight::numeric ELSE 0 END) > 0
          THEN ROUND((
            (
              SUM(CASE WHEN s.inward_weight::text ~ '^[0-9]+(\\.[0-9]+)?$' THEN s.inward_weight::numeric ELSE 0 END) - 
              SUM(CASE WHEN s.delivered_weight::text ~ '^[0-9]+(\\.[0-9]+)?$' THEN s.delivered_weight::numeric ELSE 0 END)
            ) / 
            SUM(CASE WHEN s.inward_weight::text ~ '^[0-9]+(\\.[0-9]+)?$' THEN s.inward_weight::numeric ELSE 0 END)
          ) * 100, 2)
          ELSE ROUND(COALESCE(AVG(
            CASE 
              WHEN s.loss_percentage::text ~ '^[0-9]+(\\.[0-9]+)?$' THEN s.loss_percentage::numeric
              ELSE NULL
            END
          ), 0), 2)
        END as avg_loss_percentage,
        ROUND(AVG(
          CASE 
            WHEN s.actual_delivery_date IS NOT NULL AND om.order_date IS NOT NULL AND s.actual_delivery_date >= om.order_date
            THEN (EXTRACT(EPOCH FROM (s.actual_delivery_date - om.order_date)) / 86400)
            ELSE NULL
          END
        ), 1) as avg_completion_tat
      FROM sub_order s
      JOIN order_master om ON s.order_id = om.order_id
      LEFT JOIN customer_master c ON om.cus_id = c.cus_id
      ${whereSql}
    `, params);
    res.json(result.rows[0]);
  } catch (err) {
    console.error("Error fetching completed order summary:", err);
    res.status(500).json({ error: "Server Error" });
  }
});

app.get("/api/completed-orders-30days", async (req, res) => {
  const { fromDate, toDate, search } = req.query;
  try {
    let whereClauses = [
      "(s.suborder_dept_flow_status = 'delivered' OR s.dept_status = 'done' OR om.actual_delivery_date IS NOT NULL OR LOWER(om.order_status) = 'delivered' OR LOWER(om.order_status) = 'completed')"
    ];
    let params = [];

    if (fromDate) {
      params.push(fromDate);
      whereClauses.push(`COALESCE(s.suborder_date, om.order_date, s.actual_delivery_date, om.actual_delivery_date)::date >= $${params.length}`);
    }

    if (toDate) {
      params.push(toDate);
      whereClauses.push(`COALESCE(s.suborder_date, om.order_date, s.actual_delivery_date, om.actual_delivery_date)::date <= $${params.length}`);
    }

    if (search) {
      params.push(`%${search.trim()}%`);
      whereClauses.push(`(
        c.customer_name ILIKE $${params.length} OR
        s.design_id ILIKE $${params.length} OR
        s.design_style ILIKE $${params.length} OR
        s.sub_order_id ILIKE $${params.length} OR
        s.order_id ILIKE $${params.length}
      )`);
    }

    const whereSql = "WHERE " + whereClauses.join(" AND ");

    const result = await pool.query(`
      SELECT 
        s.sub_order_id,
        s.order_id,
        om.cus_id,
        COALESCE(c.customer_name, 'N/A') as customer_name,
        s.design_id,
        s.design_style,
        s.print_type,
        COALESCE(s.delivered_weight, 0) as delivered_weight,
        COALESCE(s.inward_weight, 0) as inward_weight,
        COALESCE(s.suborder_price, om.order_price, 0) as suborder_price,
        om.order_date,
        COALESCE(s.actual_delivery_date, om.actual_delivery_date) as actual_delivery_date,
        s.suborder_dept_flow_status,
        CASE 
          WHEN s.actual_delivery_date IS NOT NULL AND om.order_date IS NOT NULL AND s.actual_delivery_date >= om.order_date
          THEN ROUND(EXTRACT(EPOCH FROM (s.actual_delivery_date - om.order_date)) / 86400)::int
          ELSE NULL
        END as tat_days
      FROM sub_order s
      JOIN order_master om ON s.order_id = om.order_id
      LEFT JOIN customer_master c ON om.cus_id = c.cus_id
      ${whereSql}
      ORDER BY COALESCE(s.actual_delivery_date, om.actual_delivery_date) DESC NULLS LAST
    `, params);
    res.json(result.rows);
  } catch (err) {
    console.error("Error fetching completed orders:", err);
    res.status(500).json({ error: "Server Error" });
  }
});

app.get("/api/all-orders-list", async (req, res) => {
  const { fromDate, toDate, search } = req.query;
  try {
    let whereClauses = [
      "LOWER(COALESCE(s.suborder_dept_flow_status, s.dept_status, 'todo')) NOT IN ('delivered', 'done', 'completed')",
      "s.actual_delivery_date IS NULL"
    ];
    let params = [];

    if (fromDate) {
      params.push(fromDate);
      whereClauses.push(`COALESCE(s.suborder_date, om.order_date)::date >= $${params.length}`);
    }

    if (toDate) {
      params.push(toDate);
      whereClauses.push(`COALESCE(s.suborder_date, om.order_date)::date <= $${params.length}`);
    }

    if (search) {
      params.push(`%${search.trim()}%`);
      whereClauses.push(`(
        c.customer_name ILIKE $${params.length} OR
        s.design_id ILIKE $${params.length} OR
        s.design_style ILIKE $${params.length} OR
        s.sub_order_id ILIKE $${params.length} OR
        s.order_id ILIKE $${params.length}
      )`);
    }

    const whereSql = "WHERE " + whereClauses.join(" AND ");

    const result = await pool.query(`
      SELECT 
        s.sub_order_id,
        s.order_id,
        om.cus_id,
        COALESCE(c.customer_name, s.customer_name, 'N/A') as customer_name,
        COALESCE(s.design_id, 'N/A') as design_id,
        COALESCE(s.design_style, d.design_name, 'N/A') as design_style,
        COALESCE(s.print_type, 'N/A') as print_type,
        COALESCE(s.dc_challan_no::text, s.outward_dc_challan_no::text, '—') as dc_no,
        s.dc_date,
        COALESCE(s.ground, '—') as ground,
        COALESCE(s.diameter, '—') as diameter,
        COALESCE(s.roll_length, 0) as roll_length,
        COALESCE(s.inward_weight, 0) as inward_weight,
        COALESCE(s.delivered_weight, 0) as delivered_weight,
        COALESCE(s.suborder_price, om.order_price, 0) as suborder_price,
        COALESCE(s.suborder_date, om.order_date) as order_date,
        COALESCE(s.expected_delivery_date, om.expected_delivery_date) as expected_delivery_date,
        s.actual_delivery_date as actual_delivery_date,
        COALESCE(s.suborder_dept_flow_status, s.dept_status, 'todo') as status,
        CASE 
          WHEN s.suborder_date IS NOT NULL 
          THEN ROUND(EXTRACT(EPOCH FROM (NOW() - s.suborder_date)) / 86400)::int
          WHEN om.order_date IS NOT NULL 
          THEN ROUND(EXTRACT(EPOCH FROM (NOW() - om.order_date)) / 86400)::int
          ELSE NULL
        END as days_with_us,
        CASE 
          WHEN s.expected_delivery_date IS NOT NULL 
          THEN ROUND(EXTRACT(EPOCH FROM (s.expected_delivery_date - NOW())) / 86400)::int
          WHEN om.expected_delivery_date IS NOT NULL 
          THEN ROUND(EXTRACT(EPOCH FROM (om.expected_delivery_date - NOW())) / 86400)::int
          ELSE NULL
        END as remaining_days,
        CASE 
          WHEN s.actual_delivery_date IS NOT NULL AND om.order_date IS NOT NULL AND s.actual_delivery_date >= om.order_date
          THEN ROUND(EXTRACT(EPOCH FROM (s.actual_delivery_date - om.order_date)) / 86400)::int
          ELSE NULL
        END as tat_days
      FROM sub_order s
      LEFT JOIN order_master om ON s.order_id = om.order_id
      LEFT JOIN customer_master c ON om.cus_id = c.cus_id
      LEFT JOIN design_master d ON s.design_id = d.design_id
      ${whereSql}
      ORDER BY COALESCE(s.suborder_date, om.order_date) DESC NULLS LAST, s.sub_order_id DESC
    `, params);
    res.json(result.rows);
  } catch (err) {
    console.error("Error fetching undelivered orders list:", err);
    res.status(500).json({ error: "Server Error" });
  }
});



app.get("/api/order-trend", async (req, res) => {
  try {
    const result = await pool.query(`
      WITH months AS (
  SELECT TO_CHAR(DATE_TRUNC('month', NOW()) - (INTERVAL '1 month' * g), 'YYYY-MM') AS month_year
  FROM generate_series(0, 5) g
)
SELECT m.month_year,
       COALESCE(COUNT(o.sub_order_id), 0) AS order_count
FROM months m
LEFT JOIN sub_order o
  ON TO_CHAR(o.suborder_date, 'YYYY-MM') = m.month_year
GROUP BY m.month_year
ORDER BY m.month_year;

    `);
    res.json(result.rows);
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: "Failed to fetch order trend" });
  }
});app.get("/api/converted-leads-by-month/:month", async (req, res) => {
  try {
    const { month } = req.params;

    const query = `
      SELECT 
          l.lead_id,
          l.cus_id,
          c.customer_name,                    
          l.design_id,
          l.received_date,
          l.expected_delivery_date,
          l.quote_price,
          l.lead_status,
          s.sub_order_id,
          s.order_id,
          s.suborder_date,
          s.suborder_dept_flow_status,
          l.reject_reason,
          l.importance
      FROM lead l
      LEFT JOIN sub_order s
          ON s.lead_id = l.lead_id
      LEFT JOIN customer_master c                   
          ON l.cus_id = c.cus_id
      WHERE DATE_TRUNC('month', l.received_date) = TO_DATE($1, 'YYYY-MM')
        AND l.lead_status = 'accepted'
      ORDER BY l.lead_id;
    `;

    const { rows } = await pool.query(query, [month]);
    res.json(rows);

  } catch (error) {
    console.error("Error fetching accepted leads by month:", error);
    res.status(500).send("Server error");
  }
});




app.get("/api/leads-by-month/:month", async (req, res) => {
  try {
    const month = req.params.month; 
    
    const query = `
     SELECT 
    l.lead_id,
    to_char(l.received_date, 'YYYY-MM-DD') AS received_date,
    l.expected_delivery_date,
    l.quote_price,
    l.lead_status,
    l.design_id,
    c.customer_name AS customer_name,
    l.reject_reason
  FROM lead l
  JOIN customer_master c ON l.cus_id = c.cus_id
  WHERE to_char(l.received_date, 'YYYY-MM') = $1
  ORDER BY l.received_date DESC
    `;
    
    const result = await pool.query(query, [month]);
    res.json(result.rows);
  } catch (err) {
    console.error("Error fetching leads by month:", err);
    res.status(500).send("Error fetching leads");
  }
});




app.get("/api/suborders-by-month/:monthYear", async (req, res) => {
  const { monthYear } = req.params;

  try {
    const result = await pool.query(
      `
      SELECT 
        so.sub_order_id,
      
        so.order_id,
        so.design_id,
        so.suborder_date,
        so.suborder_dept_flow_status,
        o.order_status
      FROM sub_order AS so
      JOIN "order_master" AS o 
        ON so.order_id = o.order_id
      WHERE TO_CHAR(so.suborder_date, 'YYYY-MM') = $1
      ORDER BY so.suborder_date ASC
      `,
      [monthYear]
    );

    res.json(result.rows);
  } catch (err) {
    console.error("Error fetching orders by month:", err);
    res.status(500).json({ error: "Failed to fetch orders by month" });
  }
});app.get("/api/monthly-suborders-comparison", async (req, res) => {
  try {
    const result = await pool.query(`
      SELECT 
        TO_CHAR(so.suborder_date, 'YYYY-MM') AS month_year,
        EXTRACT(YEAR FROM so.suborder_date) AS year,
        COUNT(*) AS sub_order_count
      FROM sub_order AS so
      GROUP BY month_year, year
      ORDER BY month_year ASC;
    `);

    res.json(result.rows);
  } catch (err) {
    console.error("Error fetching suborder comparison:", err);
    res.status(500).json({ error: "Failed to fetch suborder comparison" });
  }
});


app.get('/api/todays-orders', async (req, res) => {
  try {
    const result = await pool.query(`
      SELECT COUNT(*) AS today_count
      FROM sub_order
      WHERE suborder_date::date = CURRENT_DATE
    `);

    console.log("Today's Orders Count:", result.rows[0].today_count);
    res.json({ count: parseInt(result.rows[0].today_count, 10) });
  } catch (err) {
    console.error(err);
    res.status(500).send("Server Error");
  }
});
app.get('/api/leads/next-id', async (req, res) => {
  try {
    const result = await pool.query(`
      SELECT 'LD-' || LPAD(nextval('sample_seq')::TEXT, 7, '0') AS next_lead_id
    `);
    const nextLeadId = result.rows[0].next_lead_id;
    console.log('Next Lead ID:', nextLeadId);
    res.json({ nextLeadId });
  } catch (error) {
    console.error('Error generating next lead ID:', error);
    res.status(500).json({ error: 'Error generating next lead ID' });
  }
});
app.put("/api/designfield", upload.single("image"), async (req, res) => {
  try {
    const {
      design_id,
      design_name,
      design_desc,
      is_active,
      active_from,
      active_till,
    } = req.body;

    let image_path = null;
    if (req.file) {
      const serverIP = "localhost";
      image_path = `http://${serverIP}:5000/assets/${req.file.filename}`;
    }

    if (image_path) {
      await pool.query(
        `UPDATE design_master
         SET design_name = $1,
             design_desc = $2,
             image_path = $3,
             is_active = $4,
             active_from = $5,
             active_till = $6
         WHERE design_id = $7`,
        [
          design_name,
          design_desc,
          image_path,
          is_active === "true" || is_active === true ? true : false,
          active_from || null,
          active_till || null,
          design_id,
        ]
      );
    } else {
      await pool.query(
        `UPDATE design_master
         SET design_name = $1,
             design_desc = $2,
             is_active = $3,
             active_from = $4,
             active_till = $5
         WHERE design_id = $6`,
        [
          design_name,
          design_desc,
          is_active === "true" || is_active === true ? true : false,
          active_from || null,
          active_till || null,
          design_id,
        ]
      );
    }

    const updatedDesign = await pool.query(
      "SELECT * FROM design_master WHERE design_id = $1",
      [design_id]
    );

    res.json({
      message: "Design updated successfully",
      updatedDesign: updatedDesign.rows[0],
    });
  } catch (err) {
    console.error("❌ Error updating design:", err);
    res.status(500).json({ message: "Failed to update design" });
  }
});

app.post("/api/leads", async (req, res) => {
  try {
    const {
      lead_id,
      received_date,
      expected_delivery_date,
      quote_price,
      lead_status,
      design_id,
      cus_id,
      reject_reason,
      importance 
    } = req.body;

    const toNullIfEmpty = (val) => (val === "" || val === undefined ? null : val);

    await pool.query(
      `INSERT INTO lead 
        (lead_id, received_date, expected_delivery_date, quote_price, lead_status, design_id, cus_id, reject_reason, importance)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)`,
      [
        toNullIfEmpty(lead_id),
        toNullIfEmpty(received_date),
        toNullIfEmpty(expected_delivery_date),
        toNullIfEmpty(quote_price),
        toNullIfEmpty(lead_status),
        toNullIfEmpty(design_id),
        toNullIfEmpty(cus_id),
        toNullIfEmpty(reject_reason),
        toNullIfEmpty(importance)  
      ]
    );

    res.status(201).json({ message: "Lead inserted successfully" });
  } catch (err) {
    console.error("DB Insert Error:", err);
    res.status(500).json({ error: "Failed to insert lead" });
  }
});
app.get("/api/critical-leads/monthly", async (req, res) => {
  try {
    const current = await pool.query(`
      SELECT COUNT(*) AS count
      FROM lead
      WHERE importance ILIKE 'high'
        AND lead_status ILIKE 'received'
        AND DATE_TRUNC('month', received_date) = DATE_TRUNC('month', NOW());
    `);

    const last = await pool.query(`
      SELECT COUNT(*) AS count
      FROM lead
      WHERE importance ILIKE 'high'
        AND lead_status ILIKE 'received'
        AND DATE_TRUNC('month', received_date) = DATE_TRUNC('month', NOW() - INTERVAL '1 month');
    `);

    res.json({
      currentMonthCritical: current.rows[0].count,
      lastMonthCritical: last.rows[0].count
    });
  } catch (err) {
    console.error(err);
    res.status(500).send("Server Error");
  }
});



app.get("/api/monthly-new-leads", async (req, res) => {
  try {
    const result = await pool.query(`
      SELECT 
        TO_CHAR(received_date, 'Mon YYYY') AS month,
        COUNT(*) AS new_leads
      FROM lead
      WHERE received_date >= (CURRENT_DATE - INTERVAL '6 months')
      GROUP BY TO_CHAR(received_date, 'Mon YYYY')
      ORDER BY MIN(received_date)
    `);
    res.json(result.rows);
  } catch (err) {
    console.error(err);
    res.status(500).send("Server Error");
  }
});
app.get("/api/quarterly-sales/:quarter", async (req, res) => {
  const { quarter } = req.params;

  const quarterMonths = {
    Q1: [4, 6],   
    Q2: [7, 9],   
    Q3: [10, 12], 
    Q4: [1, 3],   
  };

  if (!quarterMonths[quarter]) {
    return res.status(400).json({ message: "Invalid quarter value" });
  }

  const [startMonth, endMonth] = quarterMonths[quarter];
  console.log(`📅 Quarter received: ${quarter}, Months ${startMonth} → ${endMonth}`);

  try {
    const query = `
      SELECT
        COALESCE(SUM(CASE 
          WHEN (
            (EXTRACT(MONTH FROM order_date) BETWEEN $1 AND $2 AND $1 < $2)
            OR
            ($1 > $2 AND (EXTRACT(MONTH FROM order_date) >= $1 OR EXTRACT(MONTH FROM order_date) <= $2))
          )
          AND EXTRACT(YEAR FROM order_date) = EXTRACT(YEAR FROM CURRENT_DATE)
          THEN order_price
        END), 0) AS currentyear,

        COALESCE(SUM(CASE 
          WHEN (
            (EXTRACT(MONTH FROM order_date) BETWEEN $1 AND $2 AND $1 < $2)
            OR
            ($1 > $2 AND (EXTRACT(MONTH FROM order_date) >= $1 OR EXTRACT(MONTH FROM order_date) <= $2))
          )
          AND EXTRACT(YEAR FROM order_date) = EXTRACT(YEAR FROM CURRENT_DATE) - 1
          THEN order_price
        END), 0) AS lastyear
      FROM order_master;
    `;

    const result = await pool.query(query, [startMonth, endMonth]);
    console.log("✅ Query Result:", result.rows);

    res.json(result.rows[0] || { currentyear: 0, lastyear: 0 });

  } catch (err) {
    console.error("❌ Quarterly data fetch error:", err);
    res.status(500).json({ message: "Error fetching quarterly data" });
  }
});





app.get('/api/designs', async (req, res) => {
  try {
    const result = await pool.query(
      'SELECT design_id, design_name, design_desc, active_from, active_till, image_path FROM design_master ORDER BY created_at DESC, design_id DESC'
    );

    res.json(result.rows);
  } catch (err) {
    console.error("Error fetching designs:", err);
    res.status(500).json({ error: 'Failed to fetch designs' });
  }
});

app.post("/api/designs", upload.single("image"), async (req, res) => {
  try {
    const {
      design_id,
      design_name,
      design_desc,
is_active,      active_from,
      active_till,
    } = req.body;

    const toNullIfEmpty = (value) => {
      return value === "" || value === undefined ? null : value;
    };

    const serverIP = "localhost";
    const image_path = req.file
      ? `http://${serverIP}:5000/assets/${req.file.filename}`
      : null;

    await pool.query(
      `INSERT INTO design_master 
       (design_id, design_name, design_desc, image_path, is_active, active_from, active_till)
       VALUES ($1, $2, $3, $4, $5, $6, $7)`,
      [
        toNullIfEmpty(design_id),
        toNullIfEmpty(design_name),
        toNullIfEmpty(design_desc),
        toNullIfEmpty(image_path),
        is_active === "true" || is_active === true ? true : false, 
        toNullIfEmpty(active_from),
        toNullIfEmpty(active_till),
      ]
    );

    res.status(201).json({
      message: "Design added successfully",
      design_id,
      design_name,
      design_desc,
      image_path,
      is_active,
      active_from,
      active_till,
    });
  } catch (err) {
    console.error("❌ Error inserting design:", err);
    res.status(500).json({ error: "Failed to insert design" });
  }
});

app.get("/api/customers/next-id", async (req, res) => {
  try {
    const { rows } = await pool.query("SELECT MAX(cus_id) AS lastId FROM customer_master");
    const lastId = rows[0]?.lastid || "CUST0000";
    const nextNumber = parseInt(lastId.replace("CUST", "")) + 1;
    const nextCusId = "CUST" + nextNumber.toString().padStart(4, "0");
    res.json({ nextCusId });
  } catch (err) {
    console.error(err);
    res.status(500).json({ message: "Failed to generate customer ID" });
  }
});

app.get("/api/convertorder/:lead_id", async (req, res) => {
  const { lead_id } = req.params;
  try {
    const leadQuery = `
      SELECT l.lead_id, l.design_id, 
             c.cus_id, c.customer_name, c.mobile1, c.email, '' AS city
      FROM lead l
      JOIN customer_master c ON l.cus_id = c.cus_id
      WHERE l.lead_id = $1
    `;
    const result = await pool.query(leadQuery, [lead_id]);

    if (result.rows.length === 0) {
      return res.status(404).json({ error: "Lead not found" });
    }

    res.json(result.rows[0]);
  } catch (err) {
    console.error("Error fetching lead details:", err);
    res.status(500).json({ error: "Server error" });
  }
});

app.post("/api/convertorder/:lead_id", async (req, res) => {
  const { lead_id } = req.params;
  try {
    await pool.query(
      `UPDATE lead SET lead_status = $1 WHERE lead_id = $2`,
      ["accepted", lead_id]
    );

    res.json({ message: "Lead converted to order successfully!" });
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: "Failed to convert lead" });
  }
});

app.get('/api/customers', async (req, res) => {
  const { name } = req.query;
  try {
    const result = await pool.query(
      `
      SELECT 
        cus_id, 
        customer_name, 
        mobile1, 
        mobile2,
        address AS street, 
        address AS city
      FROM customer_master
      WHERE LOWER(customer_name) LIKE LOWER($1)
         OR LOWER(mobile1) LIKE LOWER($1)
   OR LOWER(mobile2) LIKE LOWER($1);
      `,
      [`%${name}%`] 
    );

    res.json(result.rows);
  } catch (err) {
    console.error("Error fetching customers:", err);
    res.status(500).json({ error: "Failed to fetch customers" });
  }
});
app.post("/api/customers1", async (req, res) => {
  try {
    const {
      cus_id,
      customer_name,
      address,
      mobile1,
      mobile2,
      landline1,
      fax,
      email,
      latitude,
      longitude,
      active,
      val_created_on,
      val_updated_on,
      remarks1,
      remarks2,
    } = req.body;

    const safe = (val) => (val === "" || val === undefined ? null : val);

    const result = await pool.query(
      `
      INSERT INTO customer_master 
        (
          cus_id, customer_name, address, mobile1, mobile2, landline1, fax, email,
          latitude, longitude, active, val_created_on, val_updated_on, remarks1, remarks2
        )
      VALUES 
        (
          $1, $2, $3, $4, $5, $6, $7, $8, 
          $9, $10, $11, $12, $13, $14, $15
        )
      RETURNING cus_id
      `,
      [
        safe(cus_id),
        safe(customer_name),
        safe(address),
        safe(mobile1),
        safe(mobile2),
        safe(landline1),
        safe(fax),
        safe(email),
        safe(latitude),
        safe(longitude),
        safe(active),
        safe(val_created_on),
        safe(val_updated_on),
        safe(remarks1),
        safe(remarks2),
      ]
    );

    res.status(201).json({
      cus_id: result.rows[0].cus_id,
      message: "✅ Customer added successfully",
    });
  } catch (err) {
    console.error("❌ Error inserting customer:", err);
    res.status(500).json({ message: "Failed to add customer" });
  }
});


app.get("/api/designs/search", async (req, res) => {
  const name = req.query.name;
  try {
    const designs = await pool.query(
      "SELECT * FROM design_master WHERE design_name ILIKE $1",
      [`%${name}%`]
    );
    res.json(designs.rows);
  } catch (err) {
    console.error(err);
    res.status(500).json({ message: "Server error" });
  }
});
/*
app.post("/filter-leads", async (req, res) => {
  try {
    const filters = req.body.filters || [];
    const page = parseInt(req.body.page) || 1;
    const limit = parseInt(req.body.limit) || 10;
    const offset = (page - 1) * limit;

    let query = `
      SELECT l.*, c.customer_name,c.mobile1,c.mobile2
      FROM lead l
      LEFT JOIN customer_master c ON l.cus_id = c.cus_id
      WHERE 1=1
    `;

    const values = [];
    let count = 1;

    const allowedColumns = ["lead_id", "cus_id", "design_id", "lead_status", "received_date","quote_price","importance"];

    filters.forEach(filter => {
      const { column, type, value } = filter;
      if (!allowedColumns.includes(column) || value === undefined || value === null) return;

      const colName = `l.${column}`;
      let valToUse = value;

      if (column === "cus_id" && !isNaN(value)) {
        valToUse = Number(value);
      }

      if (type === "equals") {
        query += ` AND ${colName} = $${count}`;
        values.push(valToUse);
        count++;
      } 
      else if (type === "contains") {
        query += ` AND ${colName}::text ILIKE $${count}`;
        values.push(`%${valToUse}%`);
        count++;
      } 
      else if (type === "between" && Array.isArray(valToUse) && valToUse.length === 2) {
        query += ` AND ${colName}::date BETWEEN $${count} AND $${count + 1}`;
        values.push(valToUse[0], valToUse[1]);
        count += 2;
      }
    });

    const countQuery = `SELECT COUNT(*) AS total FROM (${query}) subq`;
    const countResult = await pool.query(countQuery, values);
    const totalRecords = parseInt(countResult.rows[0].total, 10);
    const totalPages = Math.ceil(totalRecords / limit);
if (filters.length === 0) {

  query += `
    ORDER BY l.received_date DESC
    LIMIT 100
  `;

} else {

  query += `
    ORDER BY l.received_date DESC
    LIMIT $${count} OFFSET $${count + 1}
  `;
  values.push(limit, offset);

}
    const result = await pool.query(query, values);

    res.json({
      data: result.rows,
      pagination: {
        totalRecords,
        totalPages,
        currentPage: page,
        perPage: limit
      }
    });

  } catch (error) {
    console.error("Filter error:", error);
    res.status(500).json({ error: "Filter failed" });
  }
});
*/
app.post("/filter-leads", async (req, res) => {
  try {
    const filters = req.body.filters || [];
    const page = parseInt(req.body.page) || 1;
    const limit = parseInt(req.body.limit) || 10;
    const offset = (page - 1) * limit;

    let baseQuery = `
      SELECT l.*, c.customer_name, c.mobile1, c.mobile2
      FROM lead l
      LEFT JOIN customer_master c ON l.cus_id = c.cus_id
      WHERE 1=1
    `;

    const values = [];
    let count = 1;

    const allowedColumns = [
      "lead_id",
      "cus_id",
      "design_id",
      "lead_status",
      "received_date",
      "quote_price",
      "importance",
      "reject_reason"
    ];

    filters.forEach(filter => {
      const { column, type, value } = filter;

      if (!allowedColumns.includes(column)) return;
      if (type !== "in" && (value === undefined || value === null || value === "")) return;
      if (type === "in" && (!Array.isArray(value) || value.length === 0)) return;

      const colName = `l.${column}`;
      let valToUse = value;

      if (column === "cus_id" && !isNaN(value)) {
        valToUse = Number(value);
      }

      if (type === "equals") {
        baseQuery += ` AND ${colName} = $${count}`;
        values.push(valToUse);
        count++;
      } 
      else if (type === "in") {
        const placeholders = valToUse.map(() => `$${count++}`);
        baseQuery += ` AND ${colName} IN (${placeholders.join(",")})`;
        values.push(...valToUse);
      } 
      else if (type === "contains") {
        baseQuery += ` AND ${colName}::text ILIKE $${count}`;
        values.push(`%${valToUse}%`);
        count++;
      } 
      else if (type === "between" && Array.isArray(valToUse)) {
        baseQuery += ` AND ${colName}::date BETWEEN $${count} AND $${count + 1}`;
        values.push(valToUse[0], valToUse[1]);
        count += 2;
      }
    });

    const countQuery = `SELECT COUNT(*) AS total FROM (${baseQuery}) subq`;
    const countResult = await pool.query(countQuery, values);

    const totalRecords = parseInt(countResult.rows[0].total, 10);
    const totalPages = Math.ceil(totalRecords / limit);

    const finalQuery = baseQuery + `
      ORDER BY l.received_date DESC
      LIMIT $${count} OFFSET $${count + 1}
    `;

    const result = await pool.query(finalQuery, [...values, limit, offset]);

    res.json({
      data: result.rows,
      pagination: {
        totalRecords,
        totalPages,
        currentPage: page,
        perPage: limit
      }
    });

  } catch (error) {
    console.error("Filter error:", error);
    res.status(500).json({ error: "Filter failed" });
  }
});

app.get('/api/reject-reason-master', async (req, res) => {
    try {
        const result = await pool.query('SELECT * FROM reject_reason_master ORDER BY reason_name ASC');
        res.json(result.rows);
    } catch (err) {
        console.error("Error fetching reject reasons:", err.message);
        res.status(500).send("Server Error");
    }
});
app.get("/api/lead-status-master", async (req, res) => {
    try {
        const query = "SELECT lead_status_id, status_name FROM lead_status_master ORDER BY lead_status_id ASC";
        const result = await pool.query(query); 
        res.status(200).json(result.rows);
    } catch (err) {
        console.error("Error fetching status master:", err);
        res.status(500).json({ error: "Server error while fetching statuses" });
    }
});
app.get("/api/leads/:lead_id", async (req, res) => {
  const { lead_id } = req.params;
  console.log("Fetching lead with ID:", lead_id);

  try {
    const result = await pool.query(
      `SELECT l.*, 
              c.customer_name, c.mobile1,c.mobile2, '' AS street, '' AS city,
              d.design_name, d.design_desc, d.image_path
       FROM lead l
       LEFT JOIN customer_master c ON l.cus_id = c.cus_id
       LEFT JOIN design_master d ON l.design_id = d.design_id
       WHERE l.lead_id = $1`,
      [lead_id]
    );

    console.log("Lead fetch result:", result.rows);

    if (result.rows.length === 0) {
      return res.status(404).json({ error: "Failed to fetch lead details" });
    }

    res.json(result.rows[0]);
  } catch (err) {
    console.error("Database error:", err.message);
    res.status(500).json({ error: "Server error" });
  }
});
app.put("/api/leads/:lead_id", async (req, res) => {
  try {
    const leadId = req.params.lead_id; 
    let {
      received_date,
      expected_delivery_date,
      quote_price,
      lead_status,
      design_id,
      cus_id,
      reject_reason,
      importance  
    } = req.body;

    const toNull = (val) => (val === "" ? null : val);

    const updateQuery = `
      UPDATE lead
      SET 
        received_date = $1,
        expected_delivery_date = $2,
        quote_price = $3,
        lead_status = $4,
        design_id = $5,
        cus_id = $6,
        reject_reason = $7,
        importance = $8
      WHERE lead_id = $9
    `;

    await pool.query(updateQuery, [
      toNull(received_date),
      toNull(expected_delivery_date),
      toNull(quote_price),
      toNull(lead_status),
      toNull(design_id),
      toNull(cus_id),
      toNull(reject_reason),
      toNull(importance),  
      leadId
    ]);

    res.status(200).json({ message: "Lead updated successfully" });
  } catch (error) {
    console.error("Error updating lead:", error.message);
    res.status(500).json({ error: "Internal server error" });
  }
});

app.get('/api/next-order-id', async (req, res) => {
  try {
    const result = await pool.query(`SELECT 'ORD-' || LPAD(nextval('order_master_sequence')::TEXT, 7, '0') AS order_id`);
    res.json({ order_id: result.rows[0].order_id });
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: 'Failed to generate order ID' });
  }
});
const localToUTC = (localStr) => {
  if (!localStr) return null;
  const d = new Date(localStr);
  return new Date(d.getTime() - d.getTimezoneOffset() * 60000).toISOString();
};
(async () => {
  try {
    await pool.query(`
      ALTER TABLE order_master ADD COLUMN IF NOT EXISTS dc_challan_no VARCHAR(100);
      ALTER TABLE order_master ADD COLUMN IF NOT EXISTS dc_date DATE;
      ALTER TABLE order_master ADD COLUMN IF NOT EXISTS ground VARCHAR(255);
      ALTER TABLE order_master ADD COLUMN IF NOT EXISTS total_weight NUMERIC(10, 2);
      ALTER TABLE order_master ADD COLUMN IF NOT EXISTS received_weight NUMERIC(10, 2);
    `);
  } catch (e) {
    console.error("Error altering order_master table columns:", e);
  }
})();

(async () => {
  try {
    await pool.query(`CREATE SEQUENCE IF NOT EXISTS sample_seq START WITH 1 INCREMENT BY 1;`);
    await pool.query(`
      CREATE TABLE IF NOT EXISTS sample (
        sample_id VARCHAR(50) PRIMARY KEY,
        received_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        expected_delivery_date TIMESTAMP,
        cus_id VARCHAR(50),
        design_id VARCHAR(100),
        lead_status VARCHAR(50) DEFAULT 'received',
        is_sample BOOLEAN DEFAULT TRUE,
        weight NUMERIC(10, 2),
        ground VARCHAR(255),
        dc_challan_no VARCHAR(100),
        dc_date DATE,
        received_weight NUMERIC(10, 2),
        total_weight NUMERIC(10, 2),
        design_style VARCHAR(100),
        print_type VARCHAR(100),
        colour_fabric VARCHAR(100),
        diameter VARCHAR(255),
        roll_length VARCHAR(100),
        order_date TIMESTAMP,
        converted_order_id VARCHAR(50),
        quote_price NUMERIC(10, 2),
        importance VARCHAR(50),
        reject_reason TEXT
      );
    `);
    const maxRes = await pool.query(`SELECT MAX(CAST(SUBSTRING(sample_id FROM '[0-9]+') AS INTEGER)) AS max_num FROM sample WHERE sample_id ~ '^LD-[0-9]+$'`);
    const maxVal = maxRes.rows[0]?.max_num;
    if (maxVal && maxVal > 0) {
      await pool.query(`SELECT setval('sample_seq', ${maxVal});`);
    }
  } catch (e) {
    console.error("Error creating sample table:", e);
  }
})();

app.post("/api/samples", async (req, res) => {
  const {
    cus_id,
    design_id,
    order_date,
    ground,
    dc_challan_no,
    dc_date,
    total_weight,
    received_weight,
    weight,
    design_style,
    print_type,
    colour_fabric,
    diameter,
    roll_length
  } = req.body;

  try {
    const idResult = await pool.query("SELECT 'LD-' || LPAD(nextval('sample_seq')::TEXT, 7, '0') AS next_lead_id");
    const lead_id = idResult.rows[0].next_lead_id;

    await pool.query(
      `INSERT INTO sample (
        sample_id, received_date, cus_id, design_id, lead_status, is_sample,
        weight, ground, dc_challan_no, dc_date, total_weight, received_weight,
        design_style, print_type, colour_fabric, diameter, roll_length, order_date
      ) VALUES ($1, NOW(), $2, $3, 'received', true, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15)`,
      [
        lead_id,
        toNull(cus_id),
        toNull(design_id),
        toNull(weight),
        toNull(ground),
        toNull(dc_challan_no),
        toNullDate(dc_date),
        toNull(total_weight),
        toNull(received_weight),
        toNull(design_style),
        toNull(print_type),
        toNull(colour_fabric),
        toNull(diameter),
        toNull(roll_length),
        toNullDate(order_date)
      ]
    );

    const sampleW = parseFloat(weight);
    if (cus_id && !isNaN(sampleW) && sampleW > 0) {
      const yarnType = ground || colour_fabric || 'Sample Yarn';
      try {
        await pool.query(
          `INSERT INTO yarn_ledger (cus_id, yarn_type, quantity, transaction_type, order_id, created_at)
           VALUES ($1, $2, $3, 'OUTWARD', $4, NOW())`,
          [cus_id, yarnType, sampleW, `${lead_id} (Sample)`]
        );
        await pool.query(
          `INSERT INTO customer_yarn_stock (cus_id, yarn_type, total_inward, used_in_leads)
           VALUES ($1, $2, 0, $3)
           ON CONFLICT (cus_id, yarn_type) DO UPDATE
           SET used_in_leads = customer_yarn_stock.used_in_leads + EXCLUDED.used_in_leads`,
          [cus_id, yarnType, sampleW]
        );
      } catch (ledgerErr) {
        console.error("Error recording sample in yarn_ledger:", ledgerErr);
      }
    }

    res.json({ message: "Sample saved successfully", lead_id, sample_id: lead_id });
  } catch (err) {
    console.error("Error saving sample:", err);
    res.status(500).json({ error: "Failed to save sample" });
  }
});

app.put("/api/samples/:lead_id", async (req, res) => {
  const { lead_id } = req.params;
  const {
    cus_id,
    design_id,
    order_date,
    ground,
    dc_challan_no,
    dc_date,
    total_weight,
    received_weight,
    weight,
    design_style,
    print_type,
    colour_fabric,
    diameter,
    roll_length
  } = req.body;

  try {
    await pool.query(
      `UPDATE sample SET
        cus_id = COALESCE($1, cus_id),
        design_id = COALESCE($2, design_id),
        order_date = COALESCE($3, order_date),
        ground = COALESCE($4, ground),
        dc_challan_no = COALESCE($5, dc_challan_no),
        dc_date = COALESCE($6, dc_date),
        total_weight = COALESCE($7, total_weight),
        received_weight = COALESCE($8, received_weight),
        weight = COALESCE($9, weight),
        design_style = COALESCE($10, design_style),
        print_type = COALESCE($11, print_type),
        colour_fabric = COALESCE($12, colour_fabric),
        diameter = COALESCE($13, diameter),
        roll_length = COALESCE($14, roll_length)
      WHERE sample_id = $15`,
      [
        toNull(cus_id),
        toNull(design_id),
        toNullDate(order_date),
        toNull(ground),
        toNull(dc_challan_no),
        toNullDate(dc_date),
        toNull(total_weight),
        toNull(received_weight),
        toNull(weight),
        toNull(design_style),
        toNull(print_type),
        toNull(colour_fabric),
        toNull(diameter),
        toNull(roll_length),
        lead_id
      ]
    );

    const sampleW = parseFloat(weight);
    if (cus_id && !isNaN(sampleW) && sampleW > 0) {
      const yarnType = ground || colour_fabric || 'Sample Yarn';
      try {
        await pool.query(
          `INSERT INTO yarn_ledger (cus_id, yarn_type, quantity, transaction_type, order_id, created_at)
           VALUES ($1, $2, $3, 'OUTWARD', $4, NOW())`,
          [cus_id, yarnType, sampleW, `${lead_id} (Sample)`]
        );
        await pool.query(
          `INSERT INTO customer_yarn_stock (cus_id, yarn_type, total_inward, used_in_leads)
           VALUES ($1, $2, 0, $3)
           ON CONFLICT (cus_id, yarn_type) DO UPDATE
           SET used_in_leads = customer_yarn_stock.used_in_leads + EXCLUDED.used_in_leads`,
          [cus_id, yarnType, sampleW]
        );
      } catch (ledgerErr) {
        console.error("Error recording sample in yarn_ledger:", ledgerErr);
      }
    }

    res.json({ message: "Sample updated successfully", lead_id });
  } catch (err) {
    console.error("Error updating sample:", err);
    res.status(500).json({ error: "Failed to update sample" });
  }
});

app.get("/api/samples", async (req, res) => {
  try {
    const result = await pool.query(`
      SELECT 
        s.*, 
        s.sample_id AS lead_id, 
        COALESCE(c.customer_name, s.cus_id) AS customer_name, 
        d.design_name,
        (
          CASE 
            WHEN s.lead_status = 'accepted' THEN 'accepted'
            WHEN EXISTS (
              SELECT 1 FROM sub_order so 
              JOIN order_master om ON so.order_id = om.order_id 
              WHERE (om.cus_id = s.cus_id OR (s.cus_id ~ '^[0-9]+$' AND ('C-' || LPAD(s.cus_id, 4, '0')) = om.cus_id))
                AND so.design_id = s.design_id
                AND s.design_id IS NOT NULL AND s.design_id != ''
            ) THEN 'accepted'
            ELSE COALESCE(s.lead_status, 'received')
          END
        ) AS lead_status,
        COALESCE(
          s.converted_order_id,
          (
            SELECT so.order_id FROM sub_order so 
            JOIN order_master om ON so.order_id = om.order_id 
            WHERE (om.cus_id = s.cus_id OR (s.cus_id ~ '^[0-9]+$' AND ('C-' || LPAD(s.cus_id, 4, '0')) = om.cus_id))
              AND so.design_id = s.design_id
              AND s.design_id IS NOT NULL AND s.design_id != ''
            LIMIT 1
          )
        ) AS converted_order_id
      FROM sample s
      LEFT JOIN customer_master c ON (s.cus_id = c.cus_id OR (s.cus_id ~ '^[0-9]+$' AND ('C-' || LPAD(s.cus_id, 4, '0')) = c.cus_id))
      LEFT JOIN design_master d ON s.design_id = d.design_id
      ORDER BY s.received_date DESC NULLS LAST
    `);
    res.json(result.rows);
  } catch (err) {
    console.error("Error fetching samples:", err);
    res.status(500).json({ error: "Failed to fetch samples" });
  }
});

app.get("/api/suborders-by-dc/:dc_no", async (req, res) => {
  const { dc_no } = req.params;
  try {
    const result = await pool.query(
      `SELECT * FROM sub_order WHERE dc_challan_no = $1 ORDER BY sub_order_id ASC`,
      [dc_no]
    );
    res.json(result.rows);
  } catch (err) {
    console.error("Error fetching suborders by DC:", err);
    res.status(500).json({ error: "Failed to fetch suborders by DC number" });
  }
});

app.get("/api/samples-by-dc/:dc_no", async (req, res) => {
  const { dc_no } = req.params;
  try {
    const result = await pool.query(
      `SELECT 
         s.*, 
         s.sample_id AS lead_id, 
         COALESCE(c.customer_name, s.cus_id) AS customer_name, 
         d.design_name,
         (
           CASE 
             WHEN s.lead_status = 'accepted' THEN 'accepted'
             WHEN EXISTS (
               SELECT 1 FROM sub_order so 
               JOIN order_master om ON so.order_id = om.order_id 
               WHERE (om.cus_id = s.cus_id OR (s.cus_id ~ '^[0-9]+$' AND ('C-' || LPAD(s.cus_id, 4, '0')) = om.cus_id))
                 AND so.design_id = s.design_id
                 AND s.design_id IS NOT NULL AND s.design_id != ''
             ) THEN 'accepted'
             ELSE COALESCE(s.lead_status, 'received')
           END
         ) AS lead_status,
         COALESCE(
           s.converted_order_id,
           (
             SELECT so.order_id FROM sub_order so 
             JOIN order_master om ON so.order_id = om.order_id 
             WHERE (om.cus_id = s.cus_id OR (s.cus_id ~ '^[0-9]+$' AND ('C-' || LPAD(s.cus_id, 4, '0')) = om.cus_id))
               AND so.design_id = s.design_id
               AND s.design_id IS NOT NULL AND s.design_id != ''
             LIMIT 1
           )
         ) AS converted_order_id
       FROM sample s
       LEFT JOIN customer_master c ON (s.cus_id = c.cus_id OR (s.cus_id ~ '^[0-9]+$' AND ('C-' || LPAD(s.cus_id, 4, '0')) = c.cus_id))
       LEFT JOIN design_master d ON s.design_id = d.design_id
       WHERE s.dc_challan_no = $1
       ORDER BY s.received_date DESC`,
      [dc_no]
    );
    res.json(result.rows);
  } catch (err) {
    console.error("Error fetching samples by DC:", err);
    res.status(500).json({ error: "Failed to fetch samples by DC number" });
  }
});

app.post("/api/samples/:lead_id/convert-to-order", async (req, res) => {
  const { lead_id } = req.params;
  try {
    // 1. Fetch sample
    const sampleResult = await pool.query("SELECT * FROM sample WHERE sample_id = $1", [lead_id]);
    if (sampleResult.rowCount === 0) {
      return res.status(404).json({ error: "Sample not found" });
    }
    const sample = sampleResult.rows[0];

    // 2. Generate new order ID
    const orderIdResult = await pool.query("SELECT 'ORD-' || LPAD(nextval('order_master_sequence')::TEXT, 7, '0') AS order_id");
    const order_id = orderIdResult.rows[0].order_id;

    // 3. Create order
    await pool.query(
      `INSERT INTO order_master (
        order_id, order_date, cus_id, order_status, ground,
        dc_challan_no, dc_date, total_weight, received_weight
      ) VALUES ($1, $2, $3, 'todo', $4, $5, $6, $7, $8)`,
      [
        order_id,
        sample.order_date || new Date(),
        sample.cus_id,
        sample.ground,
        sample.dc_challan_no,
        sample.dc_date,
        sample.total_weight,
        sample.received_weight
      ]
    );

    // 4. Generate suborder ID and create suborder (without inward_weight)
    const subIdResult = await pool.query("SELECT 'SUB-' || LPAD(nextval('sub_order_sequence')::TEXT, 7, '0') AS sub_order_id");
    const sub_order_id = subIdResult.rows[0].sub_order_id;

    await pool.query(
      `INSERT INTO sub_order (
        sub_order_id, order_id, design_id, design_style, print_type,
        colour_fabric, diameter, roll_length, ground, dc_challan_no, dc_date,
        suborder_dept_flow_status
      ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, 'todo')`,
      [
        sub_order_id,
        order_id,
        sample.design_id,
        sample.design_style,
        sample.print_type,
        sample.colour_fabric,
        sample.diameter,
        sample.roll_length,
        sample.ground,
        sample.dc_challan_no,
        sample.dc_date
      ]
    );

    // 5. Mark sample as accepted and save converted_order_id
    await pool.query("UPDATE sample SET lead_status = 'accepted', converted_order_id = $1 WHERE sample_id = $2", [order_id, lead_id]);

    res.json({ message: "Sample converted to order successfully", order_id, sub_order_id });
  } catch (err) {
    console.error("Error converting sample to order:", err);
    res.status(500).json({ error: "Failed to convert sample to order" });
  }
});

app.post("/api/orders", async (req, res) => {
  const {
    order_id,
    order_date,
    cus_id,
    order_status,
    order_price,
    overall_state_tax,
    overall_central_tax,
    overall_igst,
    customer_rating,
    customer_feedback,
    expected_delivery_date,
    actual_delivery_date,
    dc_challan_no,
    dc_date,
    ground,
    total_weight,
    received_weight
  } = req.body;

  try {
    await pool.query(
      `
      INSERT INTO order_master (
        order_id,
        order_date,
        cus_id,
        order_status,
        order_price,
        overall_state_tax,
        overall_central_tax,
        overall_igst,
        customer_rating,
        customer_feedback,
        expected_delivery_date,
        actual_delivery_date,
        dc_challan_no,
        dc_date,
        ground,
        total_weight,
        received_weight
      )
      VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17)
      ON CONFLICT (order_id)
      DO UPDATE SET
        order_date = EXCLUDED.order_date,
        cus_id = EXCLUDED.cus_id,
        order_status = EXCLUDED.order_status,
        order_price = EXCLUDED.order_price,
        overall_state_tax = EXCLUDED.overall_state_tax,
        overall_central_tax = EXCLUDED.overall_central_tax,
        overall_igst=EXCLUDED.overall_igst,
        customer_rating = EXCLUDED.customer_rating,
        customer_feedback = EXCLUDED.customer_feedback,
        expected_delivery_date = EXCLUDED.expected_delivery_date,
        actual_delivery_date = EXCLUDED.actual_delivery_date,
        dc_challan_no = EXCLUDED.dc_challan_no,
        dc_date = EXCLUDED.dc_date,
        ground = EXCLUDED.ground,
        total_weight = EXCLUDED.total_weight,
        received_weight = EXCLUDED.received_weight
      `,
      [
        order_id,
        toNullDate(order_date),
        toNull(cus_id),
        toNull(order_status),
        toNull(order_price),
        toNull(overall_state_tax),
        toNull(overall_central_tax),
        toNull(overall_igst),
        toNull(customer_rating),
        toNull(customer_feedback),
        toNullDate(expected_delivery_date),
        toNullDate(actual_delivery_date),
        toNull(dc_challan_no),
        toNullDate(dc_date),
        toNull(ground),
        toNull(total_weight),
        toNull(received_weight)
      ]
    );

    const suborderDates = await pool.query(
      `SELECT 
         MAX(expected_delivery_date) AS max_expected,
         MAX(actual_delivery_date) AS max_actual
       FROM sub_order
       WHERE order_id = $1`,
      [order_id]
    );

    const maxExpected = suborderDates.rows[0].max_expected || null;
    const maxActual = suborderDates.rows[0].max_actual || null;

    await pool.query(
      `UPDATE order_master
       SET expected_delivery_date = COALESCE($1, expected_delivery_date),
           actual_delivery_date = COALESCE($2, actual_delivery_date)
       WHERE order_id = $3`,
      [maxExpected, maxActual, order_id]
    );

    res.json({ message: "Order inserted/updated successfully", order_id });
  } catch (err) {
    console.error("Error inserting/updating order:", err);
    res.status(500).json({ error: "Failed to insert or update order" });
  }
});

app.get("/api/order-status-master", async (req, res) => {
    try {
        const query = "SELECT status_id, status_name FROM order_status_master ORDER BY status_id";
        const result = await pool.query(query); 
        res.json(result.rows);
    } catch (err) {
        console.error(err);
        res.status(500).json({ error: "Server error fetching status master" });
    }
});

app.put('/api/orders/:orderId', async (req, res) => {
  const { orderId } = req.params;
  const {
    order_date,
    cus_id,
    order_status,
    order_price,
    overall_state_tax,
    overall_central_tax,
    overall_igst,
    customer_rating,
    customer_feedback,
    expected_delivery_date,
    actual_delivery_date,
    dc_challan_no,
    dc_date,
    ground,
    total_weight,
    received_weight
  } = req.body;

  try {
    console.log(" Updating order:", orderId);
    console.log(" Received data:", req.body);

    const result = await pool.query(
      `UPDATE order_master
       SET 
         order_date = $1, 
         cus_id = $2, 
         order_status = $3,
         order_price = $4,
         overall_state_tax = $5,
         overall_central_tax = $6,
         overall_igst=$7,
         customer_rating = $8,
         customer_feedback = $9,
         expected_delivery_date = $10,
         actual_delivery_date = $11,
         dc_challan_no = $12,
         dc_date = $13,
         ground = $14,
         total_weight = $15,
         received_weight = $16
       WHERE order_id = $17
       RETURNING *`,
      [
        toNullDate(order_date),
        toNull(cus_id),
        toNull(order_status),
        toNull(order_price),
        toNull(overall_state_tax),
        toNull(overall_central_tax),
        toNull(overall_igst),
        toNull(customer_rating),
        toNull(customer_feedback),
        toNullDate(expected_delivery_date),
        toNullDate(actual_delivery_date),
        toNull(dc_challan_no),
        toNullDate(dc_date),
        toNull(ground),
        toNull(total_weight),
        toNull(received_weight),
        orderId
      ]
    );

    if (result.rowCount === 0) {
      return res.status(404).json({ error: "Order not found" });
    }

    const suborderDates = await pool.query(
      `SELECT 
         MAX(expected_delivery_date) AS max_expected,
         MAX(actual_delivery_date) AS max_actual
       FROM sub_order
       WHERE order_id = $1`,
      [orderId]
    );

    const maxExpected = suborderDates.rows[0].max_expected || null;
    const maxActual = suborderDates.rows[0].max_actual || null;

    if (maxExpected || maxActual) {
      await pool.query(
        `UPDATE order_master
         SET expected_delivery_date = COALESCE($1, expected_delivery_date),
             actual_delivery_date = COALESCE($2, actual_delivery_date)
         WHERE order_id = $3`,
        [maxExpected, maxActual, orderId]
      );
      console.log("✅ Delivery dates updated in order_master");
    } else {
      console.log("⚠️ No suborder dates found, skipping delivery date update");
    }

    res.json({
      message: "Order updated successfully",
      updatedOrder: result.rows[0],
    });
  } catch (error) {
    console.error("Error updating order:", error);
    res.status(500).json({ error: "Failed to update order" });
  }
});



app.get("/api/next-suborder-id", async (req, res) => {
  try {
    const result = await pool.query("SELECT nextval('sub_order_id_seq') AS sub_order_id");
    const rawId = result.rows[0].sub_order_id;

 
    const formattedId = `SUB_${String(rawId).padStart(5, '0')}`;

    res.json({ sub_order_id: formattedId });
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: "Failed to get sub order ID" });
  }
});

app.get("/api/leads1", async (req, res) => {
  try {
    const result = await pool.query("SELECT lead_id, design_id FROM lead ");
    res.json(result.rows);
  } catch (err) {
    console.error("Error fetching leads", err);
    res.status(500).json({ error: "Internal server error" });
  }
});
// Removed duplicate customertable route
app.put('/update-order-status', async (req, res) => {
  const { order_id, new_status } = req.body;

  if (!order_id || !new_status) {
    return res.status(400).json({ error: 'order_id and new_status required' });
  }

  try {
    const { rows: flowCheck } = await pool.query(`
      SELECT 1 FROM order_status_flow_master
      WHERE current_status = (
        SELECT order_status FROM order_master WHERE order_id = $1
      ) AND next_status = $2
    `, [order_id, new_status]);

    if (flowCheck.length === 0) {
      return res.status(400).json({ error: 'Invalid status transition' });
    }

    await pool.query(`
      UPDATE order_master SET order_status = $1 WHERE order_id = $2
    `, [new_status, order_id]);

    res.json({ message: 'Order status updated to ' + new_status });
  } catch (error) {
    console.error('Error updating status:', error);
    res.status(500).json({ error: 'Internal server error' });
  }
});

app.get('/api/orders', async (req, res) => {
  try {
const result = await pool.query(`
  SELECT om.*, 
         c.customer_name, 
         '' AS city
  FROM order_master om
  JOIN customer_master c ON om.cus_id = c.cus_id
  ORDER BY om.order_id
`);
   res.json(result.rows);
  } catch (err) {
    console.error('Error fetching orders:', err);
    res.status(500).json({ error: 'Internal server error' });
  }
});
app.get('/orders/:order_id', async (req, res) => {
  const { order_id } = req.params;

  try {
    const orderResult = await pool.query(
      'SELECT * FROM order_master WHERE order_id = $1',
      [order_id]
    );

    if (orderResult.rows.length === 0) {
      return res.status(404).json({ error: 'Order not found' });
    }

    const subOrderResult = await pool.query(
      'SELECT * FROM sub_order WHERE order_id = $1',
      [order_id]
    );

    res.json({
      order: orderResult.rows[0],
      suborders: subOrderResult.rows,
    });
  } catch (err) {
    console.error('Error fetching order data:', err);
    res.status(500).json({ error: 'Internal server error' });
  }
});

app.get('/suborders/:order_id', async (req, res) => {
  const { order_id } = req.params;
  try {
    const result = await pool.query('SELECT * FROM sub_order WHERE order_id = $1 ORDER BY sub_order_id', [order_id]);
    res.json(result.rows);
  } catch (err) {
    console.error('Suborder fetch error:', err);
    res.status(500).json({ error: 'Database error' });
  }
});
app.get('/get-suborder/:sub_order_id', async (req, res) => {
  const { sub_order_id } = req.params;
  try {
    const result = await pool.query('SELECT * FROM sub_order WHERE sub_order_id = $1', [sub_order_id]);
    if (result.rows.length === 0) {
      return res.status(404).json({ message: 'Suborder not found' });
    }
    res.json(result.rows[0]);
  } catch (error) {
    console.error('Error fetching suborder:', error);
    res.status(500).json({ error: 'Internal server error' });
  }
});
app.post("/api/suborders", async (req, res) => {
  const suborders = req.body;

  try {
    for (let sub of suborders) {
      for (let field in sub) {
        if (sub[field] === "") sub[field] = null;
      }
      if (!sub.suborder_dept_flow_status) {
        sub.suborder_dept_flow_status = "todo";
      }

      await pool.query(
        `INSERT INTO sub_order (
          sub_order_id, order_id, design_id,
          design_style, print_type, colour_fabric, diameter, roll_length,
          inward_weight, delivered_weight, outward_dc_challan_no,
          loss, loss_percentage, bill_no, suborder_date, expected_delivery_date,
          suborder_remarks, suborder_dept_flow_status, actual_delivery_date, tax1, tax2, suborder_price, hsn_code, supply_type, igst, customer_name,
          dc_challan_no, dc_date, ground
        ) VALUES (
          $1, $2, $3,
          $4, $5, $6, $7, $8,
          $9, $10, $11, $12,
          $13, $14, $15, $16, $17,
          $18, $19, $20, $21, $22, $23, $24, $25, $26,
          $27, $28, $29
        )
        ON CONFLICT (sub_order_id)
        DO UPDATE SET
          order_id = EXCLUDED.order_id,
          design_id = EXCLUDED.design_id,
          design_style = EXCLUDED.design_style,
          print_type = EXCLUDED.print_type,
          colour_fabric = EXCLUDED.colour_fabric,
          diameter = EXCLUDED.diameter,
          roll_length = EXCLUDED.roll_length,
          inward_weight = EXCLUDED.inward_weight,
          delivered_weight = EXCLUDED.delivered_weight,
          outward_dc_challan_no = EXCLUDED.outward_dc_challan_no,
          loss = EXCLUDED.loss,
          loss_percentage = EXCLUDED.loss_percentage,
          bill_no = EXCLUDED.bill_no,
          suborder_date = EXCLUDED.suborder_date,
          expected_delivery_date = EXCLUDED.expected_delivery_date,
          suborder_remarks = EXCLUDED.suborder_remarks,
          suborder_dept_flow_status = EXCLUDED.suborder_dept_flow_status,
          actual_delivery_date = EXCLUDED.actual_delivery_date,
          tax1 = EXCLUDED.tax1,
          tax2 = EXCLUDED.tax2,
          suborder_price = EXCLUDED.suborder_price,
          hsn_code = EXCLUDED.hsn_code,
          supply_type = EXCLUDED.supply_type,
          igst = EXCLUDED.igst,
          customer_name = EXCLUDED.customer_name,
          dc_challan_no = EXCLUDED.dc_challan_no,
          dc_date = EXCLUDED.dc_date,
          ground = EXCLUDED.ground`,
        [
          sub.sub_order_id,
          sub.order_id,
          toNull(sub.design_id),
          toNull(sub.design_style),
          toNull(sub.print_type),
          toNull(sub.colour_fabric),
          toNull(sub.diameter),
          toNull(sub.roll_length),
          toNull(sub.inward_weight),
          toNull(sub.delivered_weight),
          toNull(sub.outward_dc_challan_no),
          toNull(sub.loss),
          toNull(sub.loss_percentage),
          toNull(sub.bill_no),
          toNullDate(sub.suborder_date),
          toNullDate(sub.expected_delivery_date),
          toNull(sub.suborder_remarks),
          toNull(sub.suborder_dept_flow_status),
          toNullDate(sub.actual_delivery_date),
          toNull(sub.tax1),
          toNull(sub.tax2),
          toNull(sub.suborder_price),
          toNull(sub.hsn_code),
          toNull(sub.supply_type),
          toNull(sub.igst),
          toNull(sub.customer_name),
          toNull(sub.dc_challan_no),
          toNullDate(sub.dc_date),
          toNull(sub.ground)
        ]
      );

      // Automatically allocate materials based on design BOM
      if (sub.design_id) {
        const bomItems = await pool.query(
          "SELECT material_id, required_qty FROM design_bom WHERE design_id = $1",
          [sub.design_id]
        );
        const subOrderQty = parseFloat(sub.inward_weight || sub.roll_length || sub.diameter || 1.0);
        
        for (const bomItem of bomItems.rows) {
          const matId = bomItem.material_id;
          const reqQty = parseFloat(bomItem.required_qty) * subOrderQty;
          
          // Ensure inventory row exists
          await pool.query(
            "INSERT INTO raw_material_inventory (material_id, quantity_on_hand, allocated_quantity) VALUES ($1, 0, 0) ON CONFLICT (material_id) DO NOTHING",
            [matId]
          );

          // Update allocated quantity
          await pool.query(
            `UPDATE raw_material_inventory 
             SET allocated_quantity = allocated_quantity + $1 
             WHERE material_id = $2`,
            [reqQty, matId]
          );
          
          // Log transaction
          await pool.query(
            `INSERT INTO inventory_transaction_log (material_id, transaction_type, quantity, reference_id, created_by)
             VALUES ($1, 'Allocation', $2, $3, 'Auto Allocator')`,
            [matId, reqQty, `Order Allocation: ${sub.order_id}`]
          );
        }
      }

      const suborderDates = await pool.query(
        `SELECT 
           MAX(expected_delivery_date) AS max_expected,
           MAX(actual_delivery_date) AS max_actual
         FROM sub_order
         WHERE order_id = $1`,
        [sub.order_id]
      );

      const maxExpected = suborderDates.rows[0].max_expected || null;
      const maxActual = suborderDates.rows[0].max_actual || null;

      await pool.query(
        `UPDATE order_master
         SET expected_delivery_date = $1,
             actual_delivery_date = $2
         WHERE order_id = $3`,
        [maxExpected, maxActual, sub.order_id]
      );
    }

    res.json({ message: "Suborders saved successfully and parent updated" });
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: "Failed to insert suborders" });
  }
});


app.put("/api/suborders/:sub_order_id", async (req, res) => {
  const { sub_order_id } = req.params;
  const form = req.body;

  for (let field in form) {
    if (form[field] === "") form[field] = null;
  }

  const suborder_date = toNullDate(form.suborder_date);
  const expected_delivery_date = toNullDate(form.expected_delivery_date);
  const actual_delivery_date = toNullDate(form.actual_delivery_date);
  const dept_received_date = toNullDate(form.dept_received_date);
  const dept_work_complete_date = toNullDate(form.dept_work_complete_date);

  try {
    const result = await pool.query(
      `UPDATE sub_order SET
        design_id = $1,
        design_style = $2,
        print_type = $3,
        colour_fabric = $4,
        diameter = $5,
        roll_length = $6,
        inward_weight = $7,
        delivered_weight = $8,
        outward_dc_challan_no = $9,
        loss = $10,
        loss_percentage = $11,  
        bill_no = $12,
        suborder_date = $13,
        expected_delivery_date = $14,
        suborder_remarks = $15,
        suborder_dept_flow_status = $16,
        actual_delivery_date = $17,
        dept_id = $18,
        dept_status = $19,
        dept_received_date = $20,
        dept_work_complete_date = $21,
        order_comments = $22,
        dept_comments = $23,
        tax1 = $24,
        tax2 = $25,
        suborder_price = $26,
        hsn_code=$27,
        supply_type=$28,igst=$29,
        customer_name=$30,
        dc_challan_no=$31,
        dc_date=$32,
        ground=$33
      WHERE sub_order_id = $34`,
      [
        toNull(form.design_id),
        toNull(form.design_style),
        toNull(form.print_type),
        toNull(form.colour_fabric),
        toNull(form.diameter),
        toNull(form.roll_length),
        toNull(form.inward_weight),
        toNull(form.delivered_weight),
        toNull(form.outward_dc_challan_no),
        toNull(form.loss),
        toNull(form.loss_percentage),
        toNull(form.bill_no),
        toNullDate(suborder_date),
        toNullDate(expected_delivery_date),
        toNull(form.suborder_remarks),
        toNull(form.suborder_dept_flow_status || "todo"),
        toNullDate(actual_delivery_date),
        toNull(form.dept_id),
        toNull(form.dept_status),
        toNullDate(dept_received_date),
        toNullDate(dept_work_complete_date),
        toNull(form.order_comments),
        toNull(form.dept_comments),
        toNull(form.tax1),
        toNull(form.tax2),
        toNull(form.suborder_price),
        toNull(form.hsn_code),
        toNull(form.supply_type),
        toNull(form.igst),
        toNull(form.customer_name),
        toNull(form.dc_challan_no),
        toNullDate(form.dc_date),
        toNull(form.ground),
        sub_order_id,
      ]
    );

    if (result.rowCount === 0) {
      return res.status(404).json({ error: "SubOrder not found" });
    }

    const suborderDates = await pool.query(
      `SELECT 
         MAX(expected_delivery_date) AS max_expected,
         MAX(actual_delivery_date) AS max_actual
       FROM sub_order
       WHERE order_id = $1`,
      [form.order_id]
    );

    const maxExpected = suborderDates.rows[0].max_expected || null;
    const maxActual = suborderDates.rows[0].max_actual || null;

    await pool.query(
      `UPDATE order_master
       SET expected_delivery_date = $1,
           actual_delivery_date = $2
       WHERE order_id = $3`,
      [maxExpected, maxActual, form.order_id]
    );

    res.status(200).json({
      message: "SubOrder and parent order updated successfully",
    });
  } catch (err) {
    console.error("SubOrder update error:", err);
    res.status(500).json({ error: "Failed to update SubOrder" });
  }
});

app.delete("/api/suborders/:sub_order_id", async (req, res) => {
  const { sub_order_id } = req.params;
  try {
    await pool.query("BEGIN");
    
    // Delete dependent records
    await pool.query("DELETE FROM sub_order_bom WHERE sub_order_id = $1", [sub_order_id]);
    await pool.query("DELETE FROM production_schedule WHERE sub_order_id = $1", [sub_order_id]);
    
    // Delete the suborder itself
    await pool.query("DELETE FROM sub_order WHERE sub_order_id = $1", [sub_order_id]);
    
    await pool.query("COMMIT");
    res.json({ message: "Suborder deleted successfully" });
  } catch (err) {
    await pool.query("ROLLBACK");
    console.error("Error deleting suborder:", err);
    res.status(500).json({ error: "Failed to delete suborder" });
  }
});

app.put('/update-dept-status', async (req, res) => {
  const { sub_order_id } = req.body;
  if (!sub_order_id) return res.status(400).json({ error: 'Sub Order ID is required' });

  const client = await pool.connect();

  try {
    const { rows: currentRows } = await client.query(`
      SELECT so.order_id, so.dept_id, so.dept_status, so.suborder_dept_flow_status, dm.dept_flow_seq 
      FROM sub_order so 
      LEFT JOIN dept_master dm ON so.dept_id = dm.dept_id 
      WHERE so.sub_order_id = $1
    `, [sub_order_id]);

    if (currentRows.length === 0) return res.status(404).json({ error: 'Sub order not found' });

    let { order_id, dept_id, dept_status, dept_flow_seq, suborder_dept_flow_status } = currentRows[0];
    
const now = new Date();
const istNow = new Date(now.getTime() + (5.5 * 60 * 60 * 1000)); 

await client.query(`
  UPDATE sub_order
  SET dept_work_complete_date = $1
  WHERE sub_order_id = $2 AND dept_status = $3
`, [istNow, sub_order_id, dept_status]);



    if (suborder_dept_flow_status === 'delivered') {
      console.log(" Already Delivered:", sub_order_id);
      return res.status(200).json({ message: 'Suborder already completed', suborder_dept_flow_status: 'delivered' });
    }

    if (!dept_id) {
      const { rows: deptRows } = await client.query(`SELECT dept_id FROM dept_master ORDER BY dept_flow_seq ASC LIMIT 1`);
      dept_id = deptRows[0].dept_id;

      const { rows: statusRows } = await client.query(`SELECT dept_status FROM dept_status_flow_master ORDER BY dept_status_seq ASC LIMIT 1`);
      dept_status = statusRows[0].dept_status;

      await client.query(`
        UPDATE sub_order 
        SET dept_id = $1, dept_status = $2, suborder_dept_flow_status = 'inprogress', dept_received_date = $3, dept_work_complete_date = NULL
        WHERE sub_order_id = $4
      `, [dept_id, dept_status, now, sub_order_id]);

      console.log(" Suborder Initialized:", {
        sub_order_id,
        dept_id,
        dept_status,
        dept_received_date: now.toISOString()
      });

      await updateOverallOrderStatus(client, order_id);
      return res.status(200).json({ message: 'Suborder initialized', dept_status });
    }

    const { rows: currentStatusSeqRows } = await client.query(`SELECT dept_status_seq FROM dept_status_flow_master WHERE dept_status = $1`, [dept_status]);
    const currentStatusSeq = currentStatusSeqRows[0]?.dept_status_seq;

    const { rows: nextStatusRows } = await client.query(`
      SELECT dept_status FROM dept_status_flow_master
      WHERE dept_status_seq > $1 ORDER BY dept_status_seq ASC LIMIT 1
    `, [currentStatusSeq]);

    if (nextStatusRows.length > 0) {
     
      const now = new Date();

      await client.query(`
        UPDATE sub_order
        SET dept_work_complete_date = $1
        WHERE sub_order_id = $2 AND dept_status = $3
      `, [now, sub_order_id, dept_status]);

      console.log(" Dept Work Completed:", {
        sub_order_id,
        dept_status,
        dept_work_complete_date: now.toISOString()
      });

      const nextDeptStatus = nextStatusRows[0].dept_status;

      await client.query(`
        UPDATE sub_order
        SET dept_status = $1, dept_received_date = $2, dept_work_complete_date = NULL, suborder_dept_flow_status = 'inprogress'
        WHERE sub_order_id = $3
      `, [nextDeptStatus, now, sub_order_id]);

      console.log(" Dept Status Progressed:", {
        sub_order_id,
        next_dept_status: nextDeptStatus,
        dept_received_date: now.toISOString()

      });

      await updateOverallOrderStatus(client, order_id);
      return res.status(200).json({ message: 'Dept status progressed', next_dept_status: nextDeptStatus,  
 });
    }

    const { rows: nextDeptRows } = await client.query(`SELECT * FROM dept_master WHERE dept_flow_seq > $1 ORDER BY dept_flow_seq ASC LIMIT 1`, [dept_flow_seq]);

    if (nextDeptRows.length > 0) {
  console.log('Updating dept_work_complete_date for sub_order:', sub_order_id);

  await client.query(`
    UPDATE sub_order
    SET dept_work_complete_date = $1
    WHERE sub_order_id = $2
  `, [now, sub_order_id]);




      console.log(" Dept Completed, Moving Next:", {
        sub_order_id,
        dept_status,
        dept_work_complete_date: now.toISOString()
      });

      const nextDept = nextDeptRows[0];
      const { rows: firstStatusRows } = await client.query(`SELECT dept_status FROM dept_status_flow_master ORDER BY dept_status_seq ASC LIMIT 1`);
      const firstStatus = firstStatusRows[0]?.dept_status;

      await client.query(`
        UPDATE sub_order
        SET dept_id = $1, dept_status = $2, dept_received_date = $3, dept_work_complete_date = NULL, suborder_dept_flow_status = 'inprogress'
        WHERE sub_order_id = $4
      `, [nextDept.dept_id, firstStatus, now, sub_order_id]);

      console.log(" Moved To Next Dept:", {
        sub_order_id,
        new_dept_id: nextDept.dept_id,
        new_dept_status: firstStatus,
        dept_work_complete_date: now.toISOString()
      });

      await updateOverallOrderStatus(client, order_id);
      return res.status(200).json({ message: 'Moved to next department', new_dept_id: nextDept.dept_id, new_dept_status: firstStatus,deptWorkCompleteDate: now.toISOString() 
 });
    }

    await client.query(`
      UPDATE sub_order
      SET suborder_dept_flow_status = 'delivered', dept_work_complete_date = $1
      WHERE sub_order_id = $2
    `, [now, sub_order_id]);

    console.log("Suborder Delivered:", {
      sub_order_id,
      dept_work_complete_date: now.toISOString()
    });

    const newStatus = await updateOverallOrderStatus(client, order_id);
    return res.status(200).json({ message: 'Suborder fully delivered', order_status: newStatus });

  } catch (err) {
    console.error(' Error in /update-dept-status:', err);
    return res.status(500).json({ error: 'Internal server error' });
  } finally {
    client.release();
  }
});


async function updateOverallOrderStatus(client, order_id) {
  const { rows: suborders } = await client.query(`
    SELECT suborder_dept_flow_status FROM sub_order WHERE order_id = $1
  `, [order_id]);

  const { rows: statusSeqRows } = await client.query(`
    SELECT order_status FROM order_status_flow_master ORDER BY order_status_seq ASC
  `);

  const getStatus = (name) => statusSeqRows.find(
    s => s.order_status.toLowerCase() === name
  )?.order_status;

  const statuses = suborders.map(s => s.suborder_dept_flow_status);

  let newStatus;
if (statuses.every(st => st === 'delivered')) {
  newStatus = getStatus('done');
} else if (statuses.some(st => st === 'inprogress')) {
  newStatus = getStatus('in_progress');
} else if (statuses.some(st => st === 'delivered') && statuses.some(st => st === 'todo')) {
  newStatus = getStatus('in_progress');
} else {
  newStatus = getStatus('todo');
}


  if (newStatus) {
    await client.query(`
      UPDATE order_master SET order_status = $1 WHERE order_id = $2
    `, [newStatus, order_id]);
  }

  return newStatus;   
}

app.get('/get-last-dept', async (req, res) => {
  const client = await pool.connect();
  try {
    const { rows } = await client.query(
      `SELECT MAX(dept_flow_seq) AS max_flow_seq FROM dept_master`
    );
    return res.status(200).json({ max_flow_seq: rows[0].max_flow_seq });
  } catch (err) {
    console.error(err);
    return res.status(500).json({ error: 'Internal server error' });
  } finally {
    client.release();
  }
});


app.get('/api/customers/:cus_id', async (req, res) => {
  try {
    const { cus_id } = req.params;
    const result = await pool.query(`SELECT * FROM customer_master WHERE cus_id = $1`, [cus_id]);
    if (result.rows.length === 0) {
      return res.status(404).json({ error: 'Customer not found' });
    }
    res.json(result.rows[0]);
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: 'Server error' });
  }
});

app.get('/api/customers/:cus_id/orders', async (req, res) => {
  const { cus_id } = req.params;
  try {
    const result = await pool.query('SELECT * FROM order_master WHERE cus_id = $1 ORDER BY order_date DESC', [cus_id]);
    res.json(result.rows);
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: 'Failed to fetch customer orders' });
  }
});

app.get('/api/customers/:cus_id/leads', async (req, res) => {
  const { cus_id } = req.params;
  try {
    const numId = cus_id.replace(/^C-0*/i, '');
    const result = await pool.query(
      `SELECT 
         s.*, 
         s.sample_id AS lead_id,
         (
           CASE 
             WHEN s.lead_status = 'accepted' THEN 'accepted'
             WHEN EXISTS (
               SELECT 1 FROM sub_order so 
               JOIN order_master om ON so.order_id = om.order_id 
               WHERE (om.cus_id = s.cus_id OR (s.cus_id ~ '^[0-9]+$' AND ('C-' || LPAD(s.cus_id, 4, '0')) = om.cus_id))
                 AND so.design_id = s.design_id
                 AND s.design_id IS NOT NULL AND s.design_id != ''
             ) THEN 'accepted'
             ELSE COALESCE(s.lead_status, 'received')
           END
         ) AS lead_status,
         COALESCE(
           s.converted_order_id,
           (
             SELECT so.order_id FROM sub_order so 
             JOIN order_master om ON so.order_id = om.order_id 
             WHERE (om.cus_id = s.cus_id OR (s.cus_id ~ '^[0-9]+$' AND ('C-' || LPAD(s.cus_id, 4, '0')) = om.cus_id))
               AND so.design_id = s.design_id
               AND s.design_id IS NOT NULL AND s.design_id != ''
             LIMIT 1
           )
         ) AS converted_order_id
       FROM sample s 
       WHERE s.cus_id = $1 OR s.cus_id = $2 OR (s.cus_id ~ '^[0-9]+$' AND 'C-' || LPAD(s.cus_id, 4, '0') = $1) 
       ORDER BY s.received_date DESC`,
      [cus_id, numId]
    );
    res.json(result.rows);
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: 'Failed to fetch customer leads' });
  }
});

app.get('/api/customers/:cus_id/suborders', async (req, res) => {
  const { cus_id } = req.params;
  try {
    const query = `
      SELECT 
        s.*,
        o.order_date,
        COALESCE(s.customer_name, c.customer_name) AS customer_name,
        COALESCE(SUM(l.processed_qty), 0) AS logged_weight
      FROM sub_order s
      JOIN order_master o ON s.order_id = o.order_id
      LEFT JOIN customer_master c ON o.cus_id = c.cus_id
      LEFT JOIN production_logs l ON s.sub_order_id = l.order_ref
      WHERE o.cus_id = $1
      GROUP BY s.sub_order_id, o.order_date, s.customer_name, c.customer_name
      ORDER BY s.sub_order_id ASC
    `;
    const result = await pool.query(query, [cus_id]);
    res.json(result.rows);
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: 'Failed to fetch customer suborders' });
  }
});

app.get('/api/order-print/:orderId', async (req, res) => {
  const { orderId } = req.params;

  try {
    const orderResult = await pool.query(
      `SELECT om.*, 
          c.customer_name AS company_name, 
              '' AS city,
              c.mobile1,
              c.email
       FROM order_master om
       JOIN customer_master c ON om.cus_id = c.cus_id
       WHERE om.order_id = $1`,
      [orderId]
    );
    if (orderResult.rows.length === 0) {
      return res.status(404).json({ error: 'Order not found' });
    }

    const subordersResult = await pool.query(
      `SELECT * FROM sub_order WHERE order_id = $1`,
      [orderId]
    );

    res.json({
      order: orderResult.rows[0],       
      suborders: subordersResult.rows  
    });

  } catch (err) {
    console.error('Error fetching print data:', err);
    res.status(500).json({ error: 'Database error' });
  }
});
/*
app.post("/api/orders/filter", async (req, res) => {
  const { filters, page = 1, limit = 10 } = req.body; 
  const offset = (page - 1) * limit;

  let query = `
    SELECT 
      o.order_id, 
      o.cus_id AS cus_id,     
      o.order_date, 
      o.order_status,
      c.customer_name,
      c.city
    FROM order_master o
    LEFT JOIN customer_master c ON o.cus_id = c.cus_id
  `;

  let whereClauses = [];
  let values = [];
  let count = 1;

  for (const filter of filters) {
    if (!filter.column) continue;

    let col = filter.column === "customer_name" || filter.column === "city"
      ? `c.${filter.column}`
      : `o.${filter.column}`;

    if (filter.filterType === "equals") {
      whereClauses.push(`${col} = $${count}`);
      values.push(filter.value);
      count++;
    } else if (filter.filterType === "contains") {
      whereClauses.push(`${col} ILIKE $${count}`);
      values.push(`%${filter.value}%`);
      count++;
    } else if (filter.filterType === "between" && filter.startDate && filter.endDate) {
      whereClauses.push(`${col} BETWEEN $${count} AND $${count + 1}`);
      values.push(filter.startDate, filter.endDate);
      count += 2;
    }
  }

  if (whereClauses.length > 0) {
  query += " WHERE " + whereClauses.join(" AND ");
}

if (!filters || filters.length === 0) {

  query += ` ORDER BY o.order_date DESC LIMIT 100`;

} else {

  query += ` ORDER BY o.order_date DESC LIMIT $${count} OFFSET $${count + 1}`;
  values.push(limit, offset);

}
  let countQuery = `
    SELECT COUNT(*) AS total
    FROM order_master o
    LEFT JOIN customer_master c ON o.cus_id = c.cus_id
  `;
  if (whereClauses.length > 0) {
    countQuery += " WHERE " + whereClauses.join(" AND ");
  }

  try {
    const result = await pool.query(query, values);
    const totalRes = await pool.query(countQuery, values.slice(0, values.length - 2)); 
    res.json({
      orders: result.rows,
      total: parseInt(totalRes.rows[0].total),
      page,
      limit
    });
  } catch (err) {
    console.error("Filter error:", err);
    res.status(500).json({ error: "Server error" });
  }
});
*/

app.post("/api/orders/filter", async (req, res) => {
  const { filters, page = 1, limit = 10 } = req.body;
  const offset = (page - 1) * limit;

  let query = `
    SELECT 
      o.order_id, 
      o.cus_id AS cus_id,     
      o.order_date, 
      o.order_status,
      c.customer_name,
      '' AS city
    FROM order_master o
    LEFT JOIN customer_master c ON o.cus_id = c.cus_id
  `;

  let whereClauses = [];
  let values = [];
  let count = 1;

  for (const filter of filters) {
    if (!filter.column) continue;

    let col = filter.column === "customer_name"
      ? `c.customer_name`
      : filter.column === "city"
        ? `''`
        : `o.${filter.column}`;

if (filter.filterType === "equals" || filter.filterType === "in") {

  if (Array.isArray(filter.value)) {

    const placeholders = filter.value.map(() => `$${count++}`);
    whereClauses.push(`${col} IN (${placeholders.join(",")})`);
    values.push(...filter.value);

  } else {

    whereClauses.push(`${col} = $${count}`);
    values.push(filter.value);
    count++;

  }
}
    
    else if (filter.filterType === "contains") {
      whereClauses.push(`${col} ILIKE $${count}`);
      values.push(`%${filter.value}%`);
      count++;
    }

  
    else if (
      filter.filterType === "between" &&
      filter.startDate &&
      filter.endDate
    ) {
      whereClauses.push(`${col} BETWEEN $${count} AND $${count + 1}`);
      values.push(filter.startDate, filter.endDate);
      count += 2;
    }
  }

  if (whereClauses.length > 0) {
    query += " WHERE " + whereClauses.join(" AND ");
  }

  query += ` ORDER BY o.order_date DESC LIMIT $${count} OFFSET $${count + 1}`;
  values.push(limit, offset);

  let countQuery = `
    SELECT COUNT(*) AS total
    FROM order_master o
    LEFT JOIN customer_master c ON o.cus_id = c.cus_id
  `;

  if (whereClauses.length > 0) {
    countQuery += " WHERE " + whereClauses.join(" AND ");
  }

  try {
    const result = await pool.query(query, values);

    const totalRes = await pool.query(
      countQuery,
      values.slice(0, values.length - 2)
    );

    res.json({
      orders: result.rows,
      total: parseInt(totalRes.rows[0].total),
      page,
      limit,
    });
  } catch (err) {
    console.error("Filter error:", err);
    res.status(500).json({ error: "Server error" });
  }
});
app.get("/api/reject-reason-summary", async (req, res) => {
  try {
    const result = await pool.query(`
      SELECT 
        TO_CHAR(received_date, 'YYYY-MM') AS month,
        LOWER(TRIM(reject_reason)) AS reject_reason,
        COUNT(*) AS count
      FROM lead
      WHERE 
        lead_status = 'rejected'               
        AND reject_reason IS NOT NULL         
        AND TRIM(reject_reason) <> ''       
      GROUP BY month, LOWER(TRIM(reject_reason))
      ORDER BY month, reject_reason;
    `);

    res.json(result.rows);
  } catch (err) {
    console.error("Reject reason query error:", err.message);
    res.status(500).send("Server Error");
  }
});
app.get("/api/reject-reason-details", async (req, res) => {
  try {
    const { reason, month } = req.query;
    console.log("Reason param:", reason, "Month param:", month);

    const result = await pool.query(
      `
      SELECT 
        l.lead_id, 
        l.reject_reason, 
        l.lead_status, 
        l.quote_price,
        l.design_id,
        c.customer_name,
        c.cus_id,
        TO_CHAR(l.received_date, 'DD-MM-YYYY') AS received_date,
        l.expected_delivery_date
      FROM lead l
      JOIN customer_master c ON l.cus_id = c.cus_id
      WHERE 
        l.lead_status = 'rejected'
        AND LOWER(TRIM(l.reject_reason)) LIKE '%' || LOWER(TRIM($1)) || '%'
        AND TO_CHAR(l.received_date, 'YYYY-MM') = $2
      ORDER BY l.received_date DESC;
      `,
      [reason, month]
    );

    res.json(result.rows);
  } catch (err) {
    console.error("Reject reason details query error:", err.message);
    res.status(500).send("Server Error");
  }
});



app.get("/api/monthly-leads", async (req, res) => {
  try {
    const query = `
      SELECT 
        TO_CHAR(received_date, 'YYYY-MM') AS month,
        COUNT(*) AS lead_count
      FROM lead
      GROUP BY TO_CHAR(received_date, 'YYYY-MM')
      ORDER BY month;
    `;
    const result = await pool.query(query);
    res.json(result.rows);
  } catch (err) {
    console.error("Monthly leads query error:", err);
    res.status(500).json({ 
      error: "Internal server error", 
      details: err.message  
    });
  }
});
app.get("/api/monthly-suborders", async (req, res) => {
  try {
    const result = await pool.query(`
      SELECT 
        TO_CHAR(months.month, 'YYYY-MM') AS month_year,
        COALESCE(COUNT(s.suborder_date), 0)::int AS sub_order_count
      FROM generate_series(
          DATE_TRUNC('month', NOW()) - INTERVAL '5 months',
          DATE_TRUNC('month', NOW()),
          interval '1 month'
      ) AS months(month)
      LEFT JOIN sub_order s 
        ON DATE_TRUNC('month', s.suborder_date) = months.month
      GROUP BY months.month
      ORDER BY months.month;
    `);

    res.json(result.rows);
  } catch (err) {
    console.error("Monthly sub-orders API error:", err); 
    res.status(500).send("Server Error");
  }
});

app.get("/api/monthly-leads-orders", async (req, res) => {
  try {
    const query = `
    WITH months AS (
    SELECT generate_series(
        DATE_TRUNC('month', NOW()) - INTERVAL '5 months',
        DATE_TRUNC('month', NOW()),
        interval '1 month'
    ) AS month
),
monthly_leads AS (
    SELECT 
        DATE_TRUNC('month', received_date) AS month,
        COUNT(*)::int AS total_leads
    FROM lead
    GROUP BY DATE_TRUNC('month', received_date)
),
monthly_converted AS (
    SELECT 
        DATE_TRUNC('month', received_date) AS month,
        COUNT(DISTINCT lead_id)::int AS converted_leads
    FROM lead
    WHERE lead_status = 'accepted'
    GROUP BY DATE_TRUNC('month', received_date)
)
SELECT 
    TO_CHAR(m.month, 'YYYY-MM') AS month_year,
    COALESCE(ml.total_leads, 0) AS total_leads,
    COALESCE(mc.converted_leads, 0) AS converted_leads,
    ROUND(
        CASE WHEN COALESCE(ml.total_leads, 0) > 0
             THEN (COALESCE(mc.converted_leads, 0)::decimal / ml.total_leads) * 100
             ELSE 0 END, 2
    ) AS conversion_percentage
FROM months m
LEFT JOIN monthly_leads ml ON m.month = ml.month
LEFT JOIN monthly_converted mc ON mc.month = m.month
ORDER BY m.month;

    `;

    const { rows } = await pool.query(query);
    res.json(rows);
  } catch (error) {
    console.error("Error fetching conversion data:", error);
    res.status(500).send("Server error");
  }
});

app.put("/update-design-popup", async (req, res) => {
  const { designId, active, activeFrom, activeTill } = req.body;

  try {
    const query = `
      UPDATE design_master
      SET is_active = $1,
          active_from = $2,
          active_till = $3
      WHERE design_id = $4
    `;

    const values = [active === true, activeFrom || null, activeTill || null, designId];

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

    if (result.rowCount === 0) {
      return res.status(404).json({ message: "Design not found." });
    }

    res.status(200).json({ message: "Design updated successfully!" });
  } catch (err) {
    console.error("Error updating design popup:", err);
    res.status(500).json({ message: "Failed to update design." });
  }
});
app.delete("/customerdelete/:cus_id", async (req, res) => {
  const { cus_id } = req.params;

  try {
    const result = await pool.query("DELETE FROM customer_master WHERE cus_id = $1", [cus_id]);
    if (result.rowCount === 0) {
      return res.status(404).json({ message: "Customer not found" });
    }
    res.json({ message: "Customer deleted successfully" });
  } catch (error) {
    console.error("Error deleting customer:", error);
    res.status(500).json({ message: "Error deleting customer" });
  }
});


app.post("/api/add-department", async (req, res) => {
  try {
    const {
      dept_id,
      dept_name,
      dept_flow_seq,
      primary_contact_name,
      
      primary_phone,
      secondary_contact,
      secondary_phone,
      dept_manager,
      manager_phone,
    } = req.body;

    if (!dept_id || !dept_name) {
      return res.status(400).json({ error: "Department ID and Name are required" });
    }

    const insertQuery = `
      INSERT INTO dept_master (
        dept_id, dept_name, dept_flow_Seq,primary_contact_name, 
        primary_phone, secondary_contact, secondary_phone,
        dept_manager, manager_phone
      )
      VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9)
    `;

    await pool.query(insertQuery, [
      dept_id,
      dept_name,
            dept_flow_seq,

      primary_contact_name,
      primary_phone,
      secondary_contact,
      secondary_phone,
      dept_manager,
      manager_phone,
    ]);

    res.json({ message: "Department added successfully!" });
  } catch (error) {
    console.error(" Error inserting department:", error);
    res.status(500).json({ error: error.message });
  }
});
app.get("/get-department", async (req, res) => {
  try {
    const result = await pool.query("SELECT * FROM dept_master");
    res.json({ departments: result.rows });
  } catch (err) {
    console.error(" Query error:", err);
    res.status(500).json({ error: err.message });
  }
});
app.get("/api/department/:dept_id", async (req, res) => {
  try {
    const { dept_id } = req.params;
    const result = await pool.query("SELECT * FROM dept_master WHERE dept_id = $1", [dept_id]);

    if (result.rows.length === 0) {
      return res.status(404).json({ error: "Department not found" });
    }

    res.json(result.rows[0]);
  } catch (err) {
    console.error("Error fetching department:", err);
    res.status(500).json({ error: err.message });
  }
});

app.put("/api/update-department/:dept_id", async (req, res) => {
  try {
    const { dept_id } = req.params;
    const {
      dept_name,
       dept_flow_seq,
      primary_contact_name,
     
      primary_phone,
      secondary_contact,
      secondary_phone,
      dept_manager,
      manager_phone,
    } = req.body;

    const result = await pool.query(
      `UPDATE dept_master 
       SET dept_name=$1, dept_flow_seq=$2,primary_contact_name=$3, primary_phone=$4,
           secondary_contact=$5, secondary_phone=$6, dept_manager=$7, manager_phone=$8
       WHERE dept_id=$9 RETURNING *`,
      [
        dept_name,
        dept_flow_seq,
                primary_contact_name,

        primary_phone,
        secondary_contact,
        secondary_phone,
        dept_manager,
        manager_phone,
        dept_id,
      ]
    );
    if (result.rowCount === 0) {
      return res.status(404).json({ error: "Department not found" });
    }
    res.json({ message: "Department updated successfully", department: result.rows[0] });
  } catch (err) {
    console.error("Error updating department:", err);
    res.status(500).json({ error: err.message });
  }
});
app.delete("/api/delete-department/:dept_id", async (req, res) => {
  try {
    const { dept_id } = req.params;
    const result = await pool.query("DELETE FROM dept_master WHERE dept_id = $1", [dept_id]);
    if (result.rowCount === 0) {
      return res.status(404).json({ error: "Department not found" });
    }
    res.json({ message: "Department deleted successfully" });
  } catch (err) {
    console.error("Delete error:", err);
    res.status(500).json({ error: err.message });
  }
});

app.get("/api/order-price-last-6-months", async (req, res) => {
  try {
    const result = await pool.query(`
      SELECT
        TO_CHAR(order_date, 'YYYY-MM') AS month,
        SUM(order_price) AS total_price
      FROM order_master
      WHERE order_date >= (CURRENT_DATE - INTERVAL '6 months')
      GROUP BY TO_CHAR(order_date, 'YYYY-MM')
      ORDER BY month ASC;
    `);

    console.log("📦 Raw Query Result:", result.rows);
    res.json(result.rows);
  } catch (err) {
    console.error("❌ Error fetching 6-month totals:", err);
    res.status(500).json({ error: "Internal Server Error" });
  }
});
app.get("/api/top-customers/:year", async (req, res) => {
  const { year } = req.params;
  console.log("Requested year:", year);

  try {
    const result = await pool.query(`
      SELECT 
        c.customer_name,
        COUNT(o.order_id) AS number_of_orders,
        COALESCE(SUM(o.order_price), 0) AS total_order_amount
      FROM customer_master AS c
      JOIN order_master AS o ON c.cus_id = o.cus_id
      WHERE EXTRACT(YEAR FROM o.order_date::date) = $1
      GROUP BY c.customer_name
      ORDER BY total_order_amount DESC
      LIMIT 15
    `, [year]);

    console.log("DB Result:", result.rows);
    res.json(result.rows);
  } catch (error) {
    console.error("Error fetching top customers:", error.message);
    res.status(500).json({ error: error.message });
  }
});
app.get("/api/customer-orders/:customerName/:year", async (req, res) => {
  const { customerName, year } = req.params;

  try {
    const result = await pool.query(
      `
      SELECT 
        o.order_id,
        TO_CHAR(o.order_date, 'DD-Mon-YYYY') AS order_date,
        COALESCE(o.order_price, 0) AS order_price,order_status
      FROM order_master AS o
      JOIN customer_master AS c ON o.cus_id = c.cus_id
      WHERE c.customer_name = $1 
        AND EXTRACT(YEAR FROM o.order_date) = $2
      ORDER BY o.order_date DESC
      `,
      [customerName, year]
    );
    res.json(result.rows);
  } catch (err) {
    console.error("Error fetching customer orders:", err);
    res.status(500).json({ error: "Failed to fetch customer orders" });
  }
});




app.get("/api/lead-status-summary1", async (req, res) => {
  try {
    const result = await pool.query(`
      SELECT lead_status, COUNT(*) AS count
      FROM lead
      GROUP BY lead_status
      ORDER BY lead_status;
    `);
    res.json(result.rows);
  } catch (err) {
    console.error("Error fetching lead status summary:", err);
    res.status(500).send("Server Error");
  }
});
app.get("/api/leads-by-status/:status", async (req, res) => {
  const { status } = req.params;
  try {
    const result = await pool.query(
      `SELECT lead_id, design_id, cus_id, received_date, expected_delivery_date, quote_price, lead_status, reject_reason
       FROM lead
       WHERE lead_status = $1
       ORDER BY received_date DESC`,
      [status]
    );
    res.json(result.rows);
  } catch (err) {
    console.error("Error fetching leads by status:", err);
    res.status(500).send("Server error");
  }
});app.get('/api/yearly-sales-comparison', async (req, res) => {
  const client = await pool.connect();

  try {
    const now = new Date();
    let currentYear = now.getFullYear();

    if (now.getMonth() + 1 < 4) {
      currentYear--;
    }

    const thisFYStart = `${currentYear}-04-01`;
    const thisFYEnd = `${currentYear + 1}-03-31`;

    const lastFYStart = `${currentYear - 1}-04-01`;
    const lastFYEnd = `${currentYear}-03-31`;

    console.log(`📅 Current FY: ${thisFYStart} → ${thisFYEnd}`);
    console.log(`📅 Last FY: ${lastFYStart} → ${lastFYEnd}`);

    const { rows: lastYearSales } = await client.query(
      `SELECT COALESCE(SUM(order_price), 0) AS total_sales
       FROM order_master
       WHERE order_date BETWEEN $1::date AND $2::date`,
      [lastFYStart, lastFYEnd]
    );

    const { rows: thisYearSales } = await client.query(
      `SELECT COALESCE(SUM(order_price), 0) AS total_sales
       FROM order_master
       WHERE order_date BETWEEN $1::date AND LEAST(CURRENT_DATE, $2::date)`,
      [thisFYStart, thisFYEnd]
    );

    res.json({
      lastYearTotal: parseFloat(lastYearSales[0].total_sales),
      thisYearTotal: parseFloat(thisYearSales[0].total_sales),
    });

  } catch (err) {
    console.error("❌ Error fetching yearly sales comparison:", err);
    res.status(500).json({ error: 'Error fetching yearly sales comparison' });
  } finally {
    client.release();
  }
});
app.get("/api/monthly-tax-comparison", async (req, res) => {
  const client = await pool.connect();
  try {
    const now = new Date();
    const currentYear = now.getFullYear();
    const lastYear = currentYear - 1;

    const query = `
      SELECT 
        TO_CHAR(tax_date, 'Mon') AS month,
        EXTRACT(YEAR FROM tax_date) AS year,
        SUM(state_tax) AS total_state_tax,
        SUM(central_tax) AS total_central_tax
      FROM tax_table
      WHERE EXTRACT(YEAR FROM tax_date) IN ($1, $2)
      GROUP BY 1, 2
      ORDER BY MIN(tax_date);
    `;

    const result = await client.query(query, [currentYear, lastYear]);
    const rows = result.rows;

    const months = [
      "Jan", "Feb", "Mar", "Apr", "May", "Jun",
      "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"
    ];

    const comparisonData = months.map((m) => {
      const current = rows.find(r => r.month.trim() === m && r.year === currentYear);
      const last = rows.find(r => r.month.trim() === m && r.year === lastYear);
      return {
        month: m,
        current_total:
          (current ? Number(current.total_state_tax) + Number(current.total_central_tax) : 0),
        last_total:
          (last ? Number(last.total_state_tax) + Number(last.total_central_tax) : 0),
      };
    });

    res.json(comparisonData);
  } catch (error) {
    console.error("Error fetching tax comparison:", error);
    res.status(500).json({ message: "Error fetching tax comparison" });
  } finally {
    client.release();
  }
});
app.get("/api/tax-summary", async (req, res) => {
  try {
    const query = `
      SELECT 
        SUM(current_state_tax) AS current_state_tax_total,
        SUM(current_central_tax) AS current_central_tax_total,
        SUM(last_state_tax) AS last_state_tax_total,
        SUM(last_central_tax) AS last_central_tax_total
      FROM order_master;
    `;
    const result = await pool.query(query);
    res.json(result.rows[0]);
  } catch (err) {
    console.error("Error fetching tax summary:", err);
    res.status(500).json({ error: "Internal Server Error" });
  }
});


app.get("/api/monthly-tax-summary", async (req, res) => {
  try {
    const now = new Date();
    let currentYear = now.getFullYear();

    if (now.getMonth() + 1 < 4) {
      currentYear--;
    }

    const thisFYStart = `${currentYear}-04-01`;
    const thisFYEnd = `${currentYear + 1}-03-31`;

    const lastFYStart = `${currentYear - 1}-04-01`;
    const lastFYEnd = `${currentYear}-03-31`;

    const query = `
      SELECT 
        TO_CHAR(order_date, 'Mon') AS month,
        EXTRACT(MONTH FROM order_date) AS month_num,
        CASE 
          WHEN order_date BETWEEN $1::date AND $2::date THEN 'current'
          WHEN order_date BETWEEN $3::date AND $4::date THEN 'last'
        END AS year_type,
        SUM(overall_state_tax) AS state_tax,
        SUM(overall_central_tax) AS central_tax
      FROM order_master
      WHERE order_date BETWEEN $3::date AND $2::date
      GROUP BY month, month_num, year_type
      ORDER BY month_num;
    `;

    const { rows } = await pool.query(query, [
      thisFYStart,
      thisFYEnd,
      lastFYStart,
      lastFYEnd,
    ]);

    const months = [
      "Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec","Jan","Feb","Mar"
    ];

    const combined = months.map((m) => {
      const current = rows.find((r) => r.month === m && r.year_type === "current");
      const last = rows.find((r) => r.month === m && r.year_type === "last");
      return {
        month: m,
        current_state_tax: parseFloat(current?.state_tax || 0),
        current_central_tax: parseFloat(current?.central_tax || 0),
        last_state_tax: parseFloat(last?.state_tax || 0),
        last_central_tax: parseFloat(last?.central_tax || 0),
      };
    });

    res.json(combined);
  } catch (err) {
    console.error("❌ Error fetching monthly tax summary:", err);
    res.status(500).json({ message: "Error fetching monthly tax data" });
  }
});


app.get("/api/yearly-tax-summary", async (req, res) => {
  try {
    const now = new Date();
    let currentYear = now.getFullYear();

    if (now.getMonth() + 1 < 4) {
      currentYear--;
    }

    const thisFYStart = `${currentYear}-04-01`;
    const thisFYEnd = `${currentYear + 1}-03-31`;

    const lastFYStart = `${currentYear - 1}-04-01`;
    const lastFYEnd = `${currentYear}-03-31`;

    const { rows: current } = await pool.query(
      `SELECT COALESCE(SUM(overall_state_tax + overall_central_tax), 0) AS total 
       FROM order_master 
       WHERE order_date BETWEEN $1::date AND $2::date`,
      [thisFYStart, thisFYEnd]
    );

    const { rows: last } = await pool.query(
      `SELECT COALESCE(SUM(overall_state_tax + overall_central_tax), 0) AS total 
       FROM order_master 
       WHERE order_date BETWEEN $1::date AND $2::date`,
      [lastFYStart, lastFYEnd]
    );

    res.json({
      currentYear: parseFloat(current[0].total),
      lastYear: parseFloat(last[0].total),
    });
  } catch (err) {
    console.error("❌ Error fetching yearly tax summary:", err);
    res.status(500).json({ message: "Error fetching yearly tax summary" });
  }
});


app.get("/export-all-customers", async (req, res) => {
  try {
    const result = await pool.query("SELECT * FROM customer_master ORDER BY customer_name");
    res.json(result.rows);
  } catch (error) {
    console.error("Error exporting customers:", error);
    res.status(500).json({ message: "Error exporting customers" });
  }
});
app.post("/export-customers", async (req, res) => {
  try {
    const { filters } = req.body;

    let query = "SELECT * FROM customer_master WHERE 1=1";
    const values = [];

    filters?.forEach((filter) => {
      if (!filter.column || !filter.logic) return;

      if (filter.logic === "contains" && filter.value) {
        query += ` AND ${filter.column} ILIKE $${values.length + 1}`;
        values.push(`%${filter.value}%`);
      } else if (filter.logic === "equals" && filter.value !== undefined) {
        query += ` AND ${filter.column} = $${values.length + 1}`;
        values.push(filter.value);
      } else if (filter.logic === "between" && filter.from && filter.to) {
        query += ` AND ${filter.column} BETWEEN $${values.length + 1} AND $${values.length + 2}`;
        values.push(filter.from, filter.to);
      }
    });

    query += " ORDER BY cus_id ASC";

    const result = await pool.query(query, values);
    res.json(result.rows);
  } catch (error) {
    console.error("❌ Error exporting customers:", error);
    res.status(500).json({ message: "Error exporting customers" });
  }
});

app.get("/export-all-leads", async (req, res) => {
  try {
    const result = await pool.query(`
      SELECT 
        lead.lead_id,
        lead.received_date,
        lead.expected_delivery_date,
        lead.quote_price,
        lead.lead_status,
        lead.design_id,
        lead.cus_id,
        lead.reject_reason,
        lead.importance,
        customer_master.customer_name,
        customer_master.email,
        customer_master.mobile1
      FROM lead
      LEFT JOIN customer_master 
        ON lead.cus_id = customer_master.cus_id
      ORDER BY lead.lead_id ASC
    `);
    res.json(result.rows);
  } catch (error) {
    console.error("Error exporting leads:", error);
    res.status(500).json({ message: "Error exporting leads" });
  }
});

app.post("/export-filtered-leads", async (req, res) => {
  try {
    const { filters = [] } = req.body;

    let query = `
      SELECT 
        lead.lead_id,
        lead.received_date,
        lead.expected_delivery_date,
        lead.quote_price,
        lead.lead_status,
        lead.design_id,
        lead.cus_id,
        lead.reject_reason,
        lead.importance,
        customer_master.customer_name,
        customer_master.email,
        customer_master.mobile1
      FROM lead
      LEFT JOIN customer_master 
        ON lead.cus_id = customer_master.cus_id
      WHERE 1=1
    `;

    const values = [];
    let count = 1;

    const allowedColumns = [
      "lead_id",
      "received_date",
      "expected_delivery_date",
      "quote_price",
      "lead_status",
      "design_id",
      "cus_id",
      "importance",
      "reject_reason"
    ];

    filters.forEach(filter => {
      const { column, type, value } = filter;

      if (!allowedColumns.includes(column)) return;

      const colName = `lead.${column}`;

      if (type === "equals") {
        query += ` AND ${colName} = $${count}`;
        values.push(value);
        count++;
      }

      else if (type === "contains") {
        query += ` AND ${colName}::text ILIKE $${count}`;
        values.push(`%${value}%`);
        count++;
      }

      else if (type === "in" && Array.isArray(value) && value.length > 0) {
        const placeholders = value.map(() => `$${count++}`);
        query += ` AND ${colName} IN (${placeholders.join(",")})`;
        values.push(...value);
      }

      else if (type === "between" && Array.isArray(value) && value.length === 2) {
        query += ` AND ${colName} BETWEEN $${count} AND $${count + 1}`;
        values.push(value[0], value[1]);
        count += 2;
      }
    });

    query += ` ORDER BY lead.lead_id ASC`;

    console.log("EXPORT QUERY:", query);
    console.log("EXPORT VALUES:", values);

    const result = await pool.query(query, values);
    res.json(result.rows);

  } catch (error) {
    console.error("❌ Error exporting filtered leads:", error);
    res.status(500).json({ message: "Error exporting filtered leads" });
  }
});

app.get("/export-all-orders", async (req, res) => {
  try {
    const result = await pool.query("SELECT * FROM order_master ORDER BY order_id DESC");
    res.json(result.rows);
  } catch (error) {
    console.error("Error exporting orders:", error);
    res.status(500).json({ message: "Error exporting orders" });
  }
});
app.post("/api/suborders/byOrderIds", async (req, res) => {
  try {
    const { orderIds } = req.body;
    if (!orderIds || orderIds.length === 0) {
      return res.status(400).json({ message: "No order IDs provided." });
    }

    const query = `
      SELECT 
        s.*,      
        o.cus_id,
        o.order_date,o.order_price,o.overall_state_tax,o.overall_central_tax
      FROM sub_order s
      JOIN order_master o ON s.order_id = o.order_id
      WHERE s.order_id = ANY($1)
      ORDER BY s.order_id;
    `;

    const { rows } = await pool.query(query, [orderIds]);
    res.json(rows);
  } catch (error) {
    console.error("Error fetching suborders:", error);
    res.status(500).json({ message: "Error fetching suborders", error });
  }
});


app.get("/export-all-suborders", async (req, res) => {
  try {
    const query = `
      SELECT 
        s.*,
        o.order_date,
        COALESCE(s.customer_name, c.customer_name) AS customer_name
      FROM sub_order s
      JOIN order_master o ON s.order_id = o.order_id
      LEFT JOIN customer_master c ON o.cus_id = c.cus_id
      ORDER BY s.sub_order_id ASC
    `;
    const result = await pool.query(query);
    res.json(result.rows);
  } catch (error) {
    console.error("Error exporting suborders:", error);
    res.status(500).json({ message: "Error exporting suborders" });
  }
});
app.get("/export-all-designs", async (req, res) => {
  try {
    const result = await pool.query(`
      SELECT * from design_master
      ORDER BY design_id ASC
    `);
    res.json(result.rows);
  } catch (error) {
    console.error("Error exporting designs:", error);
    res.status(500).json({ message: "Error exporting designs" });
  }
});
function toNull(value) {
  return value === "" || value === undefined ? null : value;
}
function toNullDate(value) {
  if (!value || value === "" || value === "Invalid Date") return null;
  const d = new Date(value);
  return isNaN(d.getTime()) ? null : d;
}
app.post("/api/config-items", async (req, res) => {
  let data = req.body;
  console.log("Received data:", data);

  try {
   const query = `
  INSERT INTO configuration_items (
    ci_id, ci_name, installed_date, installed_status, decommission_date,
    end_of_life, description, tier, vendor_id, vendor_name, maintenance_id,
    ci_class, location, warranty_start_date, warranty_end_date,
    warranty_period, customer_type
  )
  VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17)
  RETURNING *;
`;

const values = [
  toNull(data.ci_id),
  toNull(data.ci_name),
  toNull(data.installed_date),
  toNull(data.installed_status),
  toNull(data.decommission_date),
  toNull(data.end_of_life ? new Date(data.end_of_life).toISOString() : null),
  toNull(data.description),
  toNull(data.tier),
  toNull(data.vendor_id),
  toNull(data.vendor_name),
  toNull(data.maintenance_id),
  toNull(data.ci_class),
  toNull(data.location),
  toNull(data.warranty_start_date),
  toNull(data.warranty_end_date),
  toNull(data.warranty_period),
  toNull(data.customer_type),
];

    const result = await pool.query(query, values);
    res.status(201).json({
      message: "Configuration item added!",
      data: result.rows[0],
    });
  } catch (err) {
    console.error("Error inserting CI:", err);
    res.status(500).json({ message: "Database insert failed" });
  }
});
app.get('/api/next-contract-id', async (req, res) => {
  try {
    const result = await pool.query(
      `SELECT 'CON-' || LPAD(nextval('contract_sequence')::TEXT, 5, '0') AS contract_id`
    );

    res.json({ contract_id: result.rows[0].contract_id });
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: 'Failed to generate Contract ID' });
  }
});


app.post("/api/vendors", async (req, res) => {
  try {
    const v = req.body;

    const result = await pool.query(
      `INSERT INTO vendor (
        vendor_id, vendor_name, email, street, city, state, country, pincode,
        contact_phone, landline1, landline2, active, created_on, updated_on
      )
      VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14)
      RETURNING vendor_id, vendor_name`,
      [
        toNull(v.vendor_id),
        toNull(v.vendor_name),
        toNull(v.email),
        toNull(v.street),
        toNull(v.city),
        toNull(v.state),
        toNull(v.country),
        toNull(v.pincode),
        toNull(v.contact_phone),
        toNull(v.landline1),
        toNull(v.landline2),
        toNull(v.active),
        toNull(v.created_on),
        toNull(v.updated_on),
      ]
    );

    res.json(result.rows[0]);
  } catch (err) {
    console.error("Error adding vendor:", err);
    res.status(500).json({ error: "Failed to add vendor" });
  }
});

app.get("/api/config-items/filter", async (req, res) => {
  const { filters } = req.query;
  const page = parseInt(req.query.page) || 1;
  const limit = parseInt(req.query.limit) || 10;
  const offset = (page - 1) * limit;

  let parsedFilters = [];
  try {
    parsedFilters = filters ? JSON.parse(filters) : [];
  } catch (err) {
    return res.status(400).json({ error: "Invalid filters JSON" });
  }

  let conditions = [];
  let params = [];

  parsedFilters.forEach((f, idx) => {
    if (!f.column || !f.logic) return;

    if (f.logic === "contains") {
      conditions.push(`${f.column} ILIKE $${params.length + 1}`);
      params.push(`%${f.value1}%`);
    } else if (f.logic === "equals") {
      conditions.push(`${f.column} = $${params.length + 1}`);
      params.push(f.value1);
    } else if (f.logic === "between") {
      conditions.push(`${f.column} BETWEEN $${params.length + 1} AND $${params.length + 2}`);
      params.push(f.value1, f.value2);
    }
  });

  let baseQuery = `SELECT * FROM configuration_items`;
  if (conditions.length > 0) {
    baseQuery += " WHERE " + conditions.join(" AND "); 
  }

  try {
    const countQuery = `SELECT COUNT(*) FROM (${baseQuery}) AS total`;
    const countResult = await pool.query(countQuery, params);
    const totalCount = parseInt(countResult.rows[0].count, 10);

    const paginatedQuery = `${baseQuery} ORDER BY ci_id LIMIT $${params.length + 1} OFFSET $${params.length + 2}`;
    const paginatedParams = [...params, limit, offset];

    const { rows } = await pool.query(paginatedQuery, paginatedParams);
    res.json({ rows, totalCount });
  } catch (error) {
    console.error("Filter query failed:", error);
    res.status(500).json({ error: "Database error" });
  }
});



app.get("/api/config-items", async (req, res) => {
  const { page = 1, limit = 10 } = req.query; 
  const offset = (page - 1) * limit;

  try {
    const countResult = await pool.query("SELECT COUNT(*) FROM configuration_items");
    const totalCount = parseInt(countResult.rows[0].count, 10);

    const result = await pool.query(
      "SELECT * FROM configuration_items ORDER BY ci_id LIMIT $1 OFFSET $2",
      [limit, offset]
    );

    res.json({ rows: result.rows, totalCount });
  } catch (err) {
    console.error("Error fetching config items:", err);
    res.status(500).json({ error: "Failed to fetch configuration items" });
  }
});

app.get('/api/next-ci-id', async (req, res) => {
  try {
    const result = await pool.query(
      `SELECT 'CI-' || LPAD(nextval('ci_sequence')::TEXT, 5, '0') AS ci_id`
    );
    res.json({ ci_id: result.rows[0].ci_id });
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: 'Failed to generate CI ID' });
  }
});
app.get('/api/next-vendor-id', async (req, res) => {
  try {
    const result = await pool.query(
      `SELECT 'VEN-' || LPAD(nextval('vendor_sequence')::TEXT, 5, '0') AS vendor_id`
    );
    res.json({ vendor_id: result.rows[0].vendor_id });
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: 'Failed to generate Vendor ID' });
  }
});
app.get('/api/config-items/:ci_id', async (req, res) => {
  const { ci_id } = req.params;
  try {
    const query = `
      SELECT ci.*, v.vendor_name AS maintenance_name
      FROM configuration_items ci
      LEFT JOIN vendor v
      ON ci.maintenance_id = v.vendor_id
      WHERE ci.ci_id = $1
    `;
    const result = await pool.query(query, [ci_id]);

    if (result.rows.length === 0)
      return res.status(404).json({ error: "Configuration Item not found" });

    res.json(result.rows[0]);
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: "Failed to fetch Configuration Item" });
  }
});


app.put('/api/config-items/:ci_id', async (req, res) => {
  const { ci_id } = req.params;
  const data = req.body;

  try {
  const query = `
  UPDATE configuration_items
  SET ci_name=$1,
      installed_date=$2,
      installed_status=$3,
      decommission_date=$4,
      end_of_life=$5,
      description=$6,
      tier=$7,
      vendor_id=$8,
      vendor_name=$9,
      maintenance_id=$10,   -- ADD THIS
      ci_class=$11,
      location=$12,
      warranty_start_date=$13,
      warranty_end_date=$14,
      warranty_period=$15,
      customer_type=$16
  WHERE ci_id=$17
`;

const values = [
  toNull(data.ci_name),
  toNull(data.installed_date),
  toNull(data.installed_status),
  toNull(data.decommission_date),
  toNull(data.end_of_life ? new Date(data.end_of_life).toISOString() : null),
  toNull(data.description),
  toNull(data.tier),
  toNull(data.vendor_id),
  toNull(data.vendor_name),
  toNull(data.maintenance_id),  
  toNull(data.ci_class),
  toNull(data.location),
  toNull(data.warranty_start_date),
  toNull(data.warranty_end_date),
  toNull(data.warranty_period),
  toNull(data.customer_type),
  ci_id,
];


    await pool.query(query, values);
    res.json({ message: "Configuration Item updated successfully" });
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: "Failed to update Configuration Item" });
  }
});
app.get('/api/vendors/:vendor_id', async (req, res) => {
  const { vendor_id } = req.params;
  try {
    const result = await pool.query('SELECT * FROM vendor WHERE vendor_id = $1', [vendor_id]);
    res.json(result.rows[0]);
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: "Failed to fetch vendor" });
  }
});app.put("/api/vendors/:vendor_id", async (req, res) => {
  const { vendor_id } = req.params;
  const data = req.body;

  try {
    const query = `
      UPDATE vendor
      SET vendor_name=$1, email=$2, street=$3, city=$4, state=$5,
          country=$6, pincode=$7, contact_phone=$8, landline1=$9,
          landline2=$10, active=$11, created_on=$12, updated_on=$13
      WHERE vendor_id=$14
    `;

    const values = [
      toNull(data.vendor_name),
      toNull(data.email),
      toNull(data.street),
      toNull(data.city),
      toNull(data.state),
      toNull(data.country),
      toNull(data.pincode),
      toNull(data.contact_phone),
      toNull(data.landline1),
      toNull(data.landline2),
      toNull(data.active),
      toNull(data.created_on),
      toNull(data.updated_on),
      vendor_id,
    ];

    await pool.query(query, values);
    res.json({ message: "Vendor updated successfully" });
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: "Failed to update vendor" });
  }
});
app.get("/api/vendorslist", async (req, res) => {
  try {
    const result = await pool.query("SELECT * FROM vendor ORDER BY vendor_id ASC");
    res.json(result.rows);
  } catch (err) {
    console.error("Error fetching vendors:", err);
    res.status(500).json({ error: "Failed to fetch vendors" });
  }
});app.get("/api/vendors1/search", async (req, res) => {
  const { name } = req.query; 
  console.log("🔍 Vendor search API hit with:", name);

  try {
    const result = await pool.query(
      `SELECT vendor_id, vendor_name, email, city, country
       FROM vendor   
       WHERE LOWER(vendor_name) LIKE LOWER($1)
       LIMIT 10`,
      [`%${name}%`]
    );

    console.log("✅ Found vendors:", result.rows.length);
    res.json(result.rows);
  } catch (error) {
    console.error("❌ Error fetching vendors:", error);
    res.status(500).json({ error: "Server error" });
  }
});
app.get("/api/monthly-weight-difference", async (req, res) => {
  try {
    const query = `
      WITH months AS (
  SELECT generate_series(1,12) AS month_num
)
SELECT 
    EXTRACT(YEAR FROM CURRENT_DATE) AS year,
    TO_CHAR(to_date(months.month_num::text,'MM'), 'Mon') AS month,
    months.month_num,
    COALESCE(SUM(CASE WHEN EXTRACT(YEAR FROM sub_order.suborder_date) = EXTRACT(YEAR FROM CURRENT_DATE) THEN sub_order.inward_weight END),0) AS inward_current,
    COALESCE(SUM(CASE WHEN EXTRACT(YEAR FROM sub_order.suborder_date) = EXTRACT(YEAR FROM CURRENT_DATE) THEN sub_order.delivered_weight END),0) AS delivered_current,
    COALESCE(SUM(CASE WHEN EXTRACT(YEAR FROM sub_order.suborder_date) = EXTRACT(YEAR FROM CURRENT_DATE) - 1 THEN sub_order.inward_weight END),0) AS inward_last,
    COALESCE(SUM(CASE WHEN EXTRACT(YEAR FROM sub_order.suborder_date) = EXTRACT(YEAR FROM CURRENT_DATE) - 1 THEN sub_order.delivered_weight END),0) AS delivered_last
FROM months
LEFT JOIN sub_order 
  ON EXTRACT(MONTH FROM sub_order.suborder_date) = months.month_num
GROUP BY months.month_num
ORDER BY months.month_num;

    `;

    const result = await pool.query(query);
    res.json(result.rows);
  } catch (err) {
    console.error(err.message);
    res.status(500).send("Server Error");
  }
});
app.get("/api/avg-tat", async (req, res) => {
  try {
    const result = await pool.query(`
    WITH months AS (
  SELECT generate_series(1, 12) AS month_num
)
SELECT 
  TO_CHAR(to_date(m.month_num::text, 'MM'), 'Mon') AS month,
  COALESCE(ROUND(AVG(t.tat_days), 2), 0) AS avg_tat
FROM months m
LEFT JOIN (
    SELECT 
      EXTRACT(MONTH FROM order_date) AS order_month,
      (EXTRACT(EPOCH FROM (actual_delivery_date - order_date)) / 86400) AS tat_days
    FROM order_master
    WHERE 
      order_date IS NOT NULL
      AND actual_delivery_date IS NOT NULL
      AND EXTRACT(YEAR FROM order_date) = EXTRACT(YEAR FROM CURRENT_DATE)
) t
ON t.order_month = m.month_num
GROUP BY m.month_num
ORDER BY m.month_num;


    `);

    res.json(result.rows);
  } catch (error) {
    console.error("Error fetching avg TAT:", error);
    res.status(500).json({ error: error.message });
  }
});
app.get("/api/projected-orders", async (req, res) => {
  try {
    const query = `
      SELECT 
          DATE_TRUNC('month', order_date) AS month,
          TO_CHAR(order_date, 'Mon YYYY') AS month_name,
          COUNT(*) AS total_orders
      FROM order_master
      WHERE order_date >= date_trunc('month', CURRENT_DATE) - INTERVAL '3 months'
        AND order_date < date_trunc('month', CURRENT_DATE)
      GROUP BY 1,2
      ORDER BY month;
    `;

    const result = await pool.query(query);

    const total = result.rows.reduce(
      (sum, r) => sum + Number(r.total_orders),
      0
    );

    const projected = total / result.rows.length;

    res.json({
      last_3_months: result.rows,
      projected_orders: Math.round(projected),
    });
  } catch (error) {
    console.error("Error:", error);
    res.status(500).json({ error: "Internal server error" });
  }
});

app.get("/api/hsn", async (req, res) => {
  try {
    const result = await pool.query("SELECT hsn_code, hsn_desc, sgst, cgst, igst FROM gst_hsn");
    res.json(result.rows);
  } catch (err) {
    res.status(500).json({ error: err.message });
  }
});
app.get("/api/config-items-dashboard", async (req, res) => {
  try {
    const result = await pool.query("SELECT * FROM configuration_items ORDER BY ci_id");
    res.json(result.rows);
  } catch (err) {
    console.error("Error fetching config items:", err);
    res.status(500).json({ error: "Failed to fetch configuration items" });
  }
});

app.delete("/api/config-items/:ci_id", async (req, res) => {
  const { ci_id } = req.params;

  try {
    const query = "DELETE FROM configuration_items WHERE ci_id = $1";
    const result = await pool.query(query, [ci_id]);

    if (result.rowCount === 0) {
      return res.status(404).json({ message: "Configuration Item not found" });
    }

    res.json({ message: "Configuration Item deleted successfully" });
  } catch (error) {
    console.error("Error deleting CI:", error);
    res.status(500).json({ message: "Server Error" });
  }
});
app.get("/vendortable", async (req, res) => {
  const page = parseInt(req.query.page) || 1;
  const limit = parseInt(req.query.limit) || 10;
  const offset = (page - 1) * limit;

  try {
    const countResult = await pool.query("SELECT COUNT(*) FROM vendor");
    const totalCount = parseInt(countResult.rows[0].count);

    const result = await pool.query(
      `SELECT * FROM vendor ORDER BY vendor_id LIMIT $1 OFFSET $2`,
      [limit, offset]
    );

    res.json({ rows: result.rows, totalCount });
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: "Failed to fetch vendors" });
  }
});app.post("/vendor-filter", async (req, res) => {
  const { filters, page, limit } = req.body;

  const p = page || 1;
  const l = limit || 10;
  const offset = (p - 1) * l;

  const allowed = ["vendor_id", "vendor_name", "email", "city", "contact_phone", "created_on", "updated_on"];

  let whereParts = [];
  let values = [];
  let index = 1;

  filters.forEach((f) => {
    if (!allowed.includes(f.column)) return;

    if (f.logic === "equals") {
      whereParts.push(`${f.column}::text = $${index}`);
      values.push(f.value1);
      index++;
    }

    else if (f.logic === "contains") {
      whereParts.push(`${f.column}::text ILIKE $${index}`);
      values.push(`%${f.value1}%`);
      index++;
    }

    else if (f.logic === "between") {
      whereParts.push(`${f.column} BETWEEN $${index} AND $${index + 1}`);
      values.push(f.value1, f.value2);
      index += 2;
    }
  });

  const whereClause = whereParts.length > 0 ? "WHERE " + whereParts.join(" AND ") : "";

  try {
    const countQuery = `SELECT COUNT(*) FROM vendor ${whereClause}`;
    const countResult = await pool.query(countQuery, values);
    const totalCount = parseInt(countResult.rows[0].count);

    const dataQuery = `
      SELECT * FROM vendor
      ${whereClause}
      ORDER BY vendor_id
      LIMIT $${index} OFFSET $${index + 1}
    `;

    const result = await pool.query(dataQuery, [...values, l, offset]);

    res.json({ rows: result.rows, totalCount });
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: "Filter failed" });
  }
});



app.get("/vendor/:id", async (req, res) => {
  try {
    const { id } = req.params;
    const result = await pool.query(
      "SELECT * FROM vendor WHERE vendor_id = $1",
      [id]
    );
    if (result.rows.length === 0) return res.status(404).json({ error: "Not found" });
    res.json(result.rows[0]);
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: "Failed to fetch vendor" });
  }
});
app.put("/vendor/:id", async (req, res) => {
  try {
    const { id } = req.params;
    const data = req.body;
    const query = `
      UPDATE vendor SET
        vendor_name=$1, email=$2, street=$3, city=$4, state=$5, country=$6,
        pincode=$7, contact_phone=$8, landline1=$9, landline2=$10,
        active=$11, created_on=$12, updated_on=$13
      WHERE vendor_id=$14
      RETURNING *;
    `;
    const values = [
      data.vendor_name, data.email, data.street, data.city, data.state, data.country,
      data.pincode, data.contact_phone, data.landline1, data.landline2,
      data.active, data.created_on || null, data.updated_on || null,
      id
    ];
    const result = await pool.query(query, values);
    if (result.rows.length === 0) return res.status(404).json({ error: "Not found" });
    res.json(result.rows[0]);
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: "Failed to update vendor" });
  }
});
app.delete("/vendor/:id", async (req, res) => {
  const { id } = req.params;

  try {
    const existing = await pool.query("SELECT * FROM vendor WHERE vendor_id = $1", [id]);
    if (existing.rows.length === 0) {
      return res.status(404).json({ message: "Vendor not found" });
    }

    await pool.query("DELETE FROM vendor WHERE vendor_id = $1", [id]);
    res.status(200).json({ message: "Vendor deleted successfully" });
  } catch (err) {
    console.error("Delete vendor error:", err);
    res.status(500).json({ message: "Internal server error" });
  }
});app.get("/vendor-export", async (req, res) => {
  try {
    const query = `SELECT * FROM vendor ORDER BY vendor_id ASC`;
    const result = await pool.query(query);

    res.json({ rows: result.rows });
  } catch (err) {
    console.error("Export Error:", err);
    res.status(500).send("Server error");
  }
});app.post("/vendor-export-filter", async (req, res) => {
  const { filters } = req.body;

  let baseQuery = "SELECT * FROM vendor WHERE 1=1";
  let params = [];

  filters.forEach((f, index) => {
    if (f.logic === "equals") {
      baseQuery += ` AND ${f.column} = $${params.length + 1}`;
      params.push(f.value1);
    }
    if (f.logic === "contains") {
      baseQuery += ` AND ${f.column} ILIKE $${params.length + 1}`;
      params.push(`%${f.value1}%`);
    }
    if (f.logic === "between") {
      baseQuery += ` AND ${f.column} BETWEEN $${params.length + 1} AND $${params.length + 2}`;
      params.push(f.value1, f.value2);
    }
  });

  const result = await pool.query(baseQuery, params);
  res.json({ rows: result.rows });
});
app.get("/api/config-items1/export", async (req, res) => {
  try {
    const { filters } = req.query;
    const parsedFilters = filters ? JSON.parse(filters) : [];

    let query = `SELECT * FROM configuration_items`;
    const conditions = [];
    const params = [];

    parsedFilters.forEach((f, index) => {
      if (!f.column || !f.logic) return;

      if (f.logic === "contains") {
        conditions.push(`${f.column} ILIKE $${params.length + 1}`);
        params.push(`%${f.value1}%`);
      } else if (f.logic === "equals") {
        conditions.push(`${f.column} = $${params.length + 1}`);
        params.push(f.value1);
      } else if (f.logic === "between") {
        conditions.push(`${f.column} BETWEEN $${params.length + 1} AND $${params.length + 2}`);
        params.push(f.value1, f.value2);
      }
    });

    if (conditions.length) {
      query += " WHERE " + conditions.join(" AND ");
    }

    const result = await pool.query(query, params);
    res.json(result.rows);
  } catch (err) {
    console.error("EXPORT ERROR >>>", err);
    res.status(500).json({ message: "Export failed" });
  }
});

app.post("/api/contact", async (req, res) => {
  const { name, email, phone, address, message } = req.body;

  try {
    await pool.query(
      `INSERT INTO contact_us (name, email, phone, address, message) 
       VALUES ($1, $2, $3, $4, $5)`,
      [name, email, phone, address, message]
    );

    res.json({ success: true, message: "Message saved successfully!" });
  } catch (err) {
    console.error(err);
    res.status(500).json({ success: false, error: "DB Error" });
  }
});

// ============================================================================
// RAW MATERIAL INVENTORY & PRODUCTION SCHEDULING API ROUTES
// ============================================================================

// --- Raw Materials & Inventory API ---

// GET: Fetch all raw materials with stock levels
app.get("/api/raw-materials", async (req, res) => {
  try {
    const query = `
      SELECT r.*, COALESCE(i.quantity_on_hand, 0) as quantity_on_hand, COALESCE(i.allocated_quantity, 0) as allocated_quantity
      FROM raw_material_master r
      LEFT JOIN raw_material_inventory i ON r.material_id = i.material_id
      ORDER BY r.material_name ASC
    `;
    const result = await pool.query(query);
    res.json(result.rows);
  } catch (err) {
    console.error("Error fetching raw materials:", err.message);
    res.status(500).json({ error: "Database error fetching raw materials" });
  }
});

// POST: Create a new raw material
app.post("/api/raw-materials", async (req, res) => {
  const { material_name, material_category, unit_of_measure, min_stock_level, cost_per_unit } = req.body;
  if (!material_name || !material_category || !unit_of_measure) {
    return res.status(400).json({ error: "Name, category, and unit of measure are required" });
  }
  try {
    await pool.query("BEGIN");
    
    const insertMasterQuery = `
      INSERT INTO raw_material_master (material_name, material_category, unit_of_measure, min_stock_level, cost_per_unit)
      VALUES ($1, $2, $3, $4, $5)
      RETURNING *
    `;
    const masterResult = await pool.query(insertMasterQuery, [
      material_name,
      material_category,
      unit_of_measure,
      parseFloat(min_stock_level) || 0,
      parseFloat(cost_per_unit) || 0
    ]);
    
    const newMaterial = masterResult.rows[0];
    
    const insertInventoryQuery = `
      INSERT INTO raw_material_inventory (material_id, quantity_on_hand, allocated_quantity)
      VALUES ($1, 0.00, 0.00)
    `;
    await pool.query(insertInventoryQuery, [newMaterial.material_id]);
    
    await pool.query("COMMIT");
    res.status(201).json(newMaterial);
  } catch (err) {
    await pool.query("ROLLBACK");
    console.error("Error creating raw material:", err.message);
    res.status(500).json({ error: "Database error creating raw material" });
  }
});

// PUT: Update a raw material
app.put("/api/raw-materials/:id", async (req, res) => {
  const { id } = req.params;
  const { material_name, material_category, unit_of_measure, min_stock_level, cost_per_unit } = req.body;
  try {
    const updateQuery = `
      UPDATE raw_material_master
      SET material_name = $1, material_category = $2, unit_of_measure = $3, min_stock_level = $4, cost_per_unit = $5, updated_at = CURRENT_TIMESTAMP
      WHERE material_id = $6
      RETURNING *
    `;
    const result = await pool.query(updateQuery, [
      material_name,
      material_category,
      unit_of_measure,
      parseFloat(min_stock_level) || 0,
      parseFloat(cost_per_unit) || 0,
      id
    ]);
    
    if (result.rowCount === 0) {
      return res.status(404).json({ error: "Material not found" });
    }
    res.json(result.rows[0]);
  } catch (err) {
    console.error("Error updating raw material:", err.message);
    res.status(500).json({ error: "Database error updating raw material" });
  }
});

// DELETE: Delete a raw material
app.delete("/api/raw-materials/:id", async (req, res) => {
  const { id } = req.params;
  try {
    const deleteQuery = "DELETE FROM raw_material_master WHERE material_id = $1";
    const result = await pool.query(deleteQuery, [id]);
    if (result.rowCount === 0) {
      return res.status(404).json({ error: "Material not found" });
    }
    res.json({ message: "Material deleted successfully" });
  } catch (err) {
    console.error("Error deleting raw material:", err.message);
    res.status(500).json({ error: "Database error deleting raw material" });
  }
});

// POST: Adjust stock (manual check-in, check-out, purchase)
app.post("/api/inventory/adjust", async (req, res) => {
  const { material_id, transaction_type, quantity, reference_id, created_by } = req.body;
  if (!material_id || !transaction_type || quantity === undefined) {
    return res.status(400).json({ error: "Material ID, transaction type, and quantity are required" });
  }
  
  const qty = parseFloat(quantity);
  try {
    await pool.query("BEGIN");
    
    const logQuery = `
      INSERT INTO inventory_transaction_log (material_id, transaction_type, quantity, reference_id, created_by)
      VALUES ($1, $2, $3, $4, $5)
    `;
    await pool.query(logQuery, [material_id, transaction_type, qty, reference_id, created_by || "System"]);
    
    let updateStockQuery = "";
    if (transaction_type === "Stock In") {
      updateStockQuery = "UPDATE raw_material_inventory SET quantity_on_hand = quantity_on_hand + $1 WHERE material_id = $2";
    } else if (transaction_type === "Consumption" || transaction_type === "Stock Out") {
      updateStockQuery = "UPDATE raw_material_inventory SET quantity_on_hand = quantity_on_hand - $1 WHERE material_id = $2";
    } else if (transaction_type === "Adjustment") {
      updateStockQuery = "UPDATE raw_material_inventory SET quantity_on_hand = $1 WHERE material_id = $2";
    } else if (transaction_type === "Allocation") {
      updateStockQuery = "UPDATE raw_material_inventory SET allocated_quantity = allocated_quantity + $1 WHERE material_id = $2";
    } else {
      await pool.query("ROLLBACK");
      return res.status(400).json({ error: "Invalid transaction type" });
    }
    
    await pool.query(updateStockQuery, [qty, material_id]);
    
    await pool.query("COMMIT");
    res.json({ success: true, message: "Inventory adjusted successfully" });
  } catch (err) {
    await pool.query("ROLLBACK");
    console.error("Error adjusting inventory:", err.message);
    res.status(500).json({ error: "Database error adjusting inventory" });
  }
});

// GET: Fetch inventory transaction logs
app.get("/api/inventory/transactions", async (req, res) => {
  try {
    const query = `
      SELECT t.*, r.material_name, r.material_category, r.unit_of_measure
      FROM inventory_transaction_log t
      JOIN raw_material_master r ON t.material_id = r.material_id
      ORDER BY t.created_at DESC
      LIMIT 100
    `;
    const result = await pool.query(query);
    res.json(result.rows);
  } catch (err) {
    console.error("Error fetching transaction logs:", err.message);
    res.status(500).json({ error: "Database error fetching transaction logs" });
  }
});

// --- Bill of Materials (BOM) API ---

// GET: Fetch BOM for a specific design
app.get("/api/designs/:design_id/bom", async (req, res) => {
  const { design_id } = req.params;
  try {
    const query = `
      SELECT b.*, r.material_name, r.material_category, r.unit_of_measure, r.cost_per_unit
      FROM design_bom b
      JOIN raw_material_master r ON b.material_id = r.material_id
      WHERE b.design_id = $1
    `;
    const result = await pool.query(query, [design_id]);
    res.json(result.rows);
  } catch (err) {
    console.error("Error fetching design BOM:", err.message);
    res.status(500).json({ error: "Database error fetching design BOM" });
  }
});

// POST: Save or update BOM for a design
app.post("/api/designs/:design_id/bom", async (req, res) => {
  const { design_id } = req.params;
  const { bom_items } = req.body;
  
  if (!Array.isArray(bom_items)) {
    return res.status(400).json({ error: "BOM items must be an array" });
  }
  
  try {
    await pool.query("BEGIN");
    
    // Restore quantity for existing BOM items before replacing
    const existingBOM = await pool.query("SELECT material_id, required_qty FROM design_bom WHERE design_id = $1", [design_id]);
    for (const item of existingBOM.rows) {
      await pool.query(
        "UPDATE raw_material_inventory SET quantity_on_hand = quantity_on_hand + $1 WHERE material_id = $2",
        [parseFloat(item.required_qty), item.material_id]
      );
    }

    await pool.query("DELETE FROM design_bom WHERE design_id = $1", [design_id]);
    
    const insertQuery = `
      INSERT INTO design_bom (design_id, material_id, required_qty)
      VALUES ($1, $2, $3)
    `;
    
    for (const item of bom_items) {
      const reqQty = parseFloat(item.required_qty);
      await pool.query(insertQuery, [design_id, item.material_id, reqQty]);
      
      // Ensure inventory row exists
      await pool.query(
        "INSERT INTO raw_material_inventory (material_id, quantity_on_hand, allocated_quantity) VALUES ($1, 0, 0) ON CONFLICT (material_id) DO NOTHING",
        [item.material_id]
      );
      
      // Deduct the new quantity from inventory
      await pool.query(
        "UPDATE raw_material_inventory SET quantity_on_hand = quantity_on_hand - $1 WHERE material_id = $2",
        [reqQty, item.material_id]
      );
    }
    
    await pool.query("COMMIT");
    res.json({ success: true, message: "Bill of Materials saved successfully" });
  } catch (err) {
    await pool.query("ROLLBACK");
    console.error("Error saving design BOM:", err.message);
    res.status(500).json({ error: "Database error saving design BOM" });
  }
});

// --- Sub-Order Bill of Materials (BOM) API ---

// GET: Fetch BOM for a specific sub-order
app.get("/api/suborders/:sub_order_id/bom", async (req, res) => {
  const { sub_order_id } = req.params;
  try {
    const query = `
      SELECT b.*, r.material_name, r.material_category, r.unit_of_measure, r.cost_per_unit
      FROM sub_order_bom b
      JOIN raw_material_master r ON b.material_id = r.material_id
      WHERE b.sub_order_id = $1
    `;
    const result = await pool.query(query, [sub_order_id]);
    res.json(result.rows);
  } catch (err) {
    console.error("Error fetching sub-order BOM:", err.message);
    res.status(500).json({ error: "Database error fetching sub-order BOM" });
  }
});

// POST: Save or update BOM for a sub-order
app.post("/api/suborders/:sub_order_id/bom", async (req, res) => {
  const { sub_order_id } = req.params;
  const { bom_items } = req.body;
  
  if (!Array.isArray(bom_items)) {
    return res.status(400).json({ error: "BOM items must be an array" });
  }
  
  try {
    await pool.query("BEGIN");
    
    // Restore quantity for existing BOM items before replacing
    const existingBOM = await pool.query("SELECT material_id, required_qty FROM sub_order_bom WHERE sub_order_id = $1", [sub_order_id]);
    for (const item of existingBOM.rows) {
      const restoreQty = parseFloat(item.required_qty);
      const matId = item.material_id;
      
      // Increase quantity_on_hand (restore stock)
      await pool.query(
        "UPDATE raw_material_inventory SET quantity_on_hand = quantity_on_hand + $1 WHERE material_id = $2",
        [restoreQty, matId]
      );
      // Decrease allocated_quantity (restore allocation)
      await pool.query(
        "UPDATE raw_material_inventory SET allocated_quantity = allocated_quantity - $1 WHERE material_id = $2",
        [restoreQty, matId]
      );
      
      // Log restoration in transaction log
      await pool.query(
        `INSERT INTO inventory_transaction_log (material_id, transaction_type, quantity, reference_id, created_by)
         VALUES ($1, 'Stock In', $2, $3, 'SubOrder BOM System')`,
        [matId, restoreQty, `BOM Revert: ${sub_order_id}`]
      );
      await pool.query(
        `INSERT INTO inventory_transaction_log (material_id, transaction_type, quantity, reference_id, created_by)
         VALUES ($1, 'Allocation', $2, $3, 'SubOrder BOM System')`,
        [matId, -restoreQty, `BOM Revert: ${sub_order_id}`]
      );
    }

    await pool.query("DELETE FROM sub_order_bom WHERE sub_order_id = $1", [sub_order_id]);
    
    const insertQuery = `
      INSERT INTO sub_order_bom (sub_order_id, material_id, required_qty)
      VALUES ($1, $2, $3)
    `;
    
    for (const item of bom_items) {
      const reqQty = parseFloat(item.required_qty);
      const matId = item.material_id;
      await pool.query(insertQuery, [sub_order_id, matId, reqQty]);
      
      // Ensure inventory row exists
      await pool.query(
        "INSERT INTO raw_material_inventory (material_id, quantity_on_hand, allocated_quantity) VALUES ($1, 0, 0) ON CONFLICT (material_id) DO NOTHING",
        [matId]
      );
      
      // Deduct from quantity_on_hand (reduce stock) and add to allocated_quantity (add to allocation)
      await pool.query(
        "UPDATE raw_material_inventory SET quantity_on_hand = quantity_on_hand - $1, allocated_quantity = allocated_quantity + $2 WHERE material_id = $3",
        [reqQty, reqQty, matId]
      );

      // Log transaction
      await pool.query(
        `INSERT INTO inventory_transaction_log (material_id, transaction_type, quantity, reference_id, created_by)
         VALUES ($1, 'Stock Out', $2, $3, 'SubOrder BOM System')`,
        [matId, reqQty, `BOM Allocate: ${sub_order_id}`]
      );
      await pool.query(
        `INSERT INTO inventory_transaction_log (material_id, transaction_type, quantity, reference_id, created_by)
         VALUES ($1, 'Allocation', $2, $3, 'SubOrder BOM System')`,
        [matId, reqQty, `BOM Allocate: ${sub_order_id}`]
      );
    }
    
    await pool.query("COMMIT");
    res.json({ success: true, message: "Sub-order Bill of Materials saved successfully" });
  } catch (err) {
    await pool.query("ROLLBACK");
    console.error("Error saving sub-order BOM:", err.message);
    res.status(500).json({ error: "Database error saving sub-order BOM" });
  }
});

// --- Order Materials Check & Operations ---

// GET: Check materials availability for an entire order
app.get("/api/orders/:order_id/materials-check", async (req, res) => {
  const { order_id } = req.params;
  try {
    const subOrdersQuery = `
      SELECT sub_order_id, design_id, COALESCE(inward_weight, roll_length, diameter, 1) as qty, ground
      FROM sub_order
      WHERE order_id = $1
    `;
    const subOrders = await pool.query(subOrdersQuery, [order_id]);
    
    if (subOrders.rowCount === 0) {
      return res.json({ available: true, materials: [], message: "No sub-orders found for this order" });
    }
    
    const materialRequirements = {};
    
    for (const subOrder of subOrders.rows) {
      // 1. Try to fetch from sub_order_bom
      let bomQuery = `
        SELECT material_id, required_qty 
        FROM sub_order_bom 
        WHERE sub_order_id = $1
      `;
      let bomItems = await pool.query(bomQuery, [subOrder.sub_order_id]);
      let isSubBOM = bomItems.rowCount > 0;

      // 2. Fallback to design_bom if sub_order_bom is empty and design_id is set
      if (!isSubBOM && subOrder.design_id) {
        bomQuery = `
          SELECT material_id, required_qty 
          FROM design_bom 
          WHERE design_id = $1
        `;
        bomItems = await pool.query(bomQuery, [subOrder.design_id]);
      }
      
      const subOrderQty = parseFloat(subOrder.qty) || 1.0;
      for (const bomItem of bomItems.rows) {
        const matId = bomItem.material_id;
        // If it came from sub_order_bom, it's already the total qty; otherwise multiply by subOrderQty.
        const reqQty = isSubBOM 
          ? parseFloat(bomItem.required_qty)
          : parseFloat(bomItem.required_qty) * subOrderQty;
        
        if (materialRequirements[matId]) {
          materialRequirements[matId] += reqQty;
        } else {
          materialRequirements[matId] = reqQty;
        }
      }
    }
    
    const materialsCheckList = [];
    let allAvailable = true;
    
    for (const matId of Object.keys(materialRequirements)) {
      const required = materialRequirements[matId];
      
      const stockQuery = `
        SELECT r.material_name, r.unit_of_measure, COALESCE(i.quantity_on_hand, 0) as quantity_on_hand, COALESCE(i.allocated_quantity, 0) as allocated_quantity
        FROM raw_material_master r
        LEFT JOIN raw_material_inventory i ON r.material_id = i.material_id
        WHERE r.material_id = $1
      `;
      const stockResult = await pool.query(stockQuery, [matId]);
      
      if (stockResult.rowCount > 0) {
        const row = stockResult.rows[0];
        const onHand = parseFloat(row.quantity_on_hand);
        const available = onHand - parseFloat(row.allocated_quantity);
        const shortage = required > onHand ? required - onHand : 0;
        
        if (shortage > 0) {
          allAvailable = false;
        }
        
        materialsCheckList.push({
          material_id: parseInt(matId),
          material_name: row.material_name,
          unit_of_measure: row.unit_of_measure,
          required_qty: required,
          quantity_on_hand: onHand,
          allocated_quantity: parseFloat(row.allocated_quantity),
          available_qty: available,
          shortage: shortage,
          status: shortage > 0 ? "Shortage" : "Available"
        });
      }
    }
    
    // Check if allocation log exists for this order
    const checkAllocation = await pool.query(
      "SELECT 1 FROM inventory_transaction_log WHERE transaction_type = 'Allocation' AND reference_id = $1 LIMIT 1",
      [`Order Allocation: ${order_id}`]
    );
    const isAllocated = checkAllocation.rowCount > 0;
    
    res.json({
      order_id,
      available: allAvailable,
      allocated: isAllocated,
      materials: materialsCheckList
    });
  } catch (err) {
    console.error("Error checking materials for order:", err.message);
    res.status(500).json({ error: "Database error checking materials" });
  }
});

// POST: Allocate materials for an order
app.post("/api/orders/:order_id/allocate", async (req, res) => {
  const { order_id } = req.params;
  const { materials } = req.body;
  
  if (!Array.isArray(materials)) {
    return res.status(400).json({ error: "Materials allocation list must be an array" });
  }
  
  try {
    await pool.query("BEGIN");
    
    for (const item of materials) {
      const matId = item.material_id;
      const qty = parseFloat(item.qty);
      
      const updateQuery = `
        UPDATE raw_material_inventory 
        SET allocated_quantity = allocated_quantity + $1 
        WHERE material_id = $2
      `;
      await pool.query(updateQuery, [qty, matId]);
      
      const logQuery = `
        INSERT INTO inventory_transaction_log (material_id, transaction_type, quantity, reference_id, created_by)
        VALUES ($1, 'Allocation', $2, $3, 'Order Allocator')
      `;
      await pool.query(logQuery, [matId, qty, `Order Allocation: ${order_id}`]);
    }
    
    await pool.query("COMMIT");
    res.json({ success: true, message: "Materials allocated successfully" });
  } catch (err) {
    await pool.query("ROLLBACK");
    console.error("Error allocating materials for order:", err.message);
    res.status(500).json({ error: "Database error allocating materials" });
  }
});

// POST: Consume materials for an order (production complete)
app.post("/api/orders/:order_id/consume", async (req, res) => {
  const { order_id } = req.params;
  const { materials } = req.body;
  
  if (!Array.isArray(materials)) {
    return res.status(400).json({ error: "Materials consumption list must be an array" });
  }
  
  try {
    await pool.query("BEGIN");
    
    for (const item of materials) {
      const matId = item.material_id;
      const qty = parseFloat(item.qty);
      const wasAllocated = item.was_allocated === true;
      
      let updateQuery = "";
      if (wasAllocated) {
        updateQuery = `
          UPDATE raw_material_inventory 
          SET quantity_on_hand = quantity_on_hand - $1,
              allocated_quantity = GREATEST(0.00, allocated_quantity - $1)
          WHERE material_id = $2
        `;
      } else {
        updateQuery = `
          UPDATE raw_material_inventory 
          SET quantity_on_hand = quantity_on_hand - $1
          WHERE material_id = $2
        `;
      }
      
      await pool.query(updateQuery, [qty, matId]);
      
      const logQuery = `
        INSERT INTO inventory_transaction_log (material_id, transaction_type, quantity, reference_id, created_by)
        VALUES ($1, 'Consumption', $2, $3, 'Order Consumer')
      `;
      await pool.query(logQuery, [matId, qty, `Order Consumption: ${order_id}`]);
    }
    
    await pool.query("COMMIT");
    res.json({ success: true, message: "Materials consumed successfully" });
  } catch (err) {
    await pool.query("ROLLBACK");
    console.error("Error consuming materials for order:", err.message);
    res.status(500).json({ error: "Database error consuming materials" });
  }
});

// --- Machinery API ---

// GET: Fetch all machines
app.get("/api/machines", async (req, res) => {
  try {
    const result = await pool.query("SELECT * FROM machine_master ORDER BY machine_name ASC");
    res.json(result.rows);
  } catch (err) {
    console.error("Error fetching machines:", err.message);
    res.status(500).json({ error: "Database error fetching machines" });
  }
});

// POST: Register new machine
app.post("/api/machines", async (req, res) => {
  const { machine_name, machine_type, daily_capacity, capacity_unit, supported_materials } = req.body;
  if (!machine_name || !machine_type || !daily_capacity || !capacity_unit) {
    return res.status(400).json({ error: "All machine attributes are required" });
  }
  try {
    const query = `
      INSERT INTO machine_master (machine_name, machine_type, daily_capacity, capacity_unit, supported_materials)
      VALUES ($1, $2, $3, $4, $5)
      RETURNING *
    `;
    const result = await pool.query(query, [
      machine_name,
      machine_type,
      parseFloat(daily_capacity),
      capacity_unit,
      supported_materials || ""
    ]);
    res.status(201).json(result.rows[0]);
  } catch (err) {
    console.error("Error registering machine:", err.message);
    res.status(500).json({ error: "Database error registering machine" });
  }
});

// PUT: Update a machine
app.put("/api/machines/:id", async (req, res) => {
  const { id } = req.params;
  const { machine_name, machine_type, daily_capacity, capacity_unit, supported_materials, is_active } = req.body;
  try {
    const query = `
      UPDATE machine_master
      SET machine_name = $1, machine_type = $2, daily_capacity = $3, capacity_unit = $4, supported_materials = $5, is_active = $6
      WHERE machine_id = $7
      RETURNING *
    `;
    const result = await pool.query(query, [
      machine_name,
      machine_type,
      parseFloat(daily_capacity),
      capacity_unit,
      supported_materials,
      is_active === true,
      id
    ]);
    if (result.rowCount === 0) {
      return res.status(404).json({ error: "Machine not found" });
    }
    res.json(result.rows[0]);
  } catch (err) {
    console.error("Error updating machine:", err.message);
    res.status(500).json({ error: "Database error updating machine" });
  }
});
// --- Production Management & Machine Logs API ---

// Auto-create machine_daily_log table if not exists
pool.query(`
  CREATE TABLE IF NOT EXISTS machine_daily_log (
    log_id SERIAL PRIMARY KEY,
    machine_id INT REFERENCES machine_master(machine_id) ON DELETE CASCADE,
    record_date DATE NOT NULL,
    operator_name VARCHAR(100),
    processed_qty NUMERIC(12,2) NOT NULL,
    task_details VARCHAR(255) DEFAULT 'Daily Production Batch',
    created_at TIMESTAMP DEFAULT NOW()
  );
`).catch(err => console.error("Error creating machine_daily_log table:", err.message));

// GET: Fetch all machines with total processed quantity and history logs
app.get("/api/production-machines", async (req, res) => {
  try {
    const machinesRes = await pool.query("SELECT * FROM machine_master ORDER BY machine_id ASC");
    const logsRes = await pool.query(`
      SELECT l.*, m.machine_name 
      FROM machine_daily_log l
      JOIN machine_master m ON l.machine_id = m.machine_id
      ORDER BY l.record_date DESC, l.log_id DESC
    `);

    const machinesWithLogs = machinesRes.rows.map(mac => {
      const macLogs = logsRes.rows
        .filter(l => l.machine_id === mac.machine_id)
        .map(l => ({
          log_id: l.log_id,
          date: l.record_date ? new Date(l.record_date).toISOString().split('T')[0] : '',
          task: l.task_details || 'Daily Production Batch',
          operator: l.operator_name || 'Unassigned',
          processed: parseFloat(l.processed_qty) || 0
        }));

      const totalProcessed = macLogs.reduce((sum, item) => sum + item.processed, 0);

      return {
        id: `MAC-${String(mac.machine_id).padStart(2, '0')}`,
        machine_id: mac.machine_id,
        name: mac.machine_name,
        type: mac.machine_type,
        speed: mac.capacity_unit ? `${mac.daily_capacity} ${mac.capacity_unit}` : `${mac.daily_capacity}`,
        status: mac.is_active ? "Running" : "Maintenance",
        lastMaintenance: mac.created_at ? new Date(mac.created_at).toISOString().split('T')[0] : "2026-07-01",
        totalProcessed: totalProcessed,
        history: macLogs
      };
    });

    res.json(machinesWithLogs);
  } catch (err) {
    console.error("Error fetching production machines:", err.message);
    res.status(500).json({ error: "Database error fetching machines" });
  }
});

// POST: Add new machine to machine_master
app.post("/api/production-machines", async (req, res) => {
  const { name, speed, status } = req.body;
  if (!name) {
    return res.status(400).json({ error: "Machine name is required" });
  }
  try {
    const isActive = status !== "Maintenance";
    const query = `
      INSERT INTO machine_master (machine_name, machine_type, daily_capacity, capacity_unit, is_active)
      VALUES ($1, $2, $3, $4, $5)
      RETURNING *
    `;
    const result = await pool.query(query, [
      name.trim(),
      "General",
      100.00,
      speed || "m/min",
      isActive
    ]);

    const newMac = result.rows[0];
    res.status(201).json({
      id: `MAC-${String(newMac.machine_id).padStart(2, '0')}`,
      machine_id: newMac.machine_id,
      name: newMac.machine_name,
      type: newMac.machine_type,
      speed: speed || "100m/min",
      status: newMac.is_active ? "Running" : "Maintenance",
      lastMaintenance: new Date().toISOString().split('T')[0],
      totalProcessed: 0,
      history: []
    });
  } catch (err) {
    console.error("Error saving machine to DB:", err.message);
    res.status(500).json({ error: "Database error saving machine" });
  }
});

// POST: Log daily output record into machine_daily_log
app.post("/api/production-machines/daily-log", async (req, res) => {
  const { machine_id, record_date, operator_name, processed_qty, task_details } = req.body;
  if (!machine_id || !processed_qty) {
    return res.status(400).json({ error: "Machine ID and Processed Quantity are required" });
  }
  try {
    const query = `
      INSERT INTO machine_daily_log (machine_id, record_date, operator_name, processed_qty, task_details)
      VALUES ($1, $2, $3, $4, $5)
      RETURNING *
    `;
    const result = await pool.query(query, [
      parseInt(machine_id, 10),
      record_date || new Date().toISOString().split('T')[0],
      operator_name || "Unassigned",
      parseFloat(processed_qty),
      task_details || "Daily Production Batch"
    ]);

    res.status(201).json(result.rows[0]);
  } catch (err) {
    console.error("Error inserting machine daily log:", err.message);
    res.status(500).json({ error: "Database error inserting daily log" });
  }
});

// --- Production Scheduler API ---

// GET: Fetch production schedule board
app.get("/api/production-schedule", async (req, res) => {
  try {
    const query = `
      SELECT p.*, m.machine_name, m.machine_type, m.supported_materials, s.order_id, s.design_id, s.design_style, s.colour_fabric, s.ground, s.suborder_remarks,
             COALESCE(s.inward_weight, s.roll_length, s.diameter, 1) as qty,
             d.design_name, d.image_path
      FROM production_schedule p
      JOIN machine_master m ON p.machine_id = m.machine_id
      JOIN sub_order s ON p.sub_order_id = s.sub_order_id
      LEFT JOIN design_master d ON s.design_id = d.design_id
      ORDER BY p.start_date ASC
    `;
    const result = await pool.query(query);
    res.json(result.rows);
  } catch (err) {
    console.error("Error fetching production schedule:", err.message);
    res.status(500).json({ error: "Database error fetching production schedule" });
  }
});

// POST: Schedule a sub-order on a machine
app.post("/api/production-schedule", async (req, res) => {
  const { sub_order_id, machine_id, start_date, end_date, scheduled_qty } = req.body;
  if (!sub_order_id || !machine_id || !start_date || !end_date || scheduled_qty === undefined) {
    return res.status(400).json({ error: "Sub-order ID, machine ID, start/end dates, and quantity are required" });
  }
  try {
    const checkQuery = "SELECT * FROM production_schedule WHERE sub_order_id = $1";
    const checkResult = await pool.query(checkQuery, [sub_order_id]);
    if (checkResult.rowCount > 0) {
      return res.status(400).json({ error: "This sub-order is already scheduled on a machine" });
    }

    const query = `
      INSERT INTO production_schedule (sub_order_id, machine_id, start_date, end_date, scheduled_qty, status)
      VALUES ($1, $2, $3, $4, $5, 'Scheduled')
      RETURNING *
    `;
    const result = await pool.query(query, [
      sub_order_id,
      parseInt(machine_id),
      start_date,
      end_date,
      parseFloat(scheduled_qty)
    ]);
    
    const scheduleItem = result.rows[0];

    // Allocate materials based on design BOM and scheduled_qty
    const subRes = await pool.query("SELECT * FROM sub_order WHERE sub_order_id = $1", [sub_order_id]);
    if (subRes.rowCount > 0) {
      const sub = subRes.rows[0];
      if (sub.design_id) {
        const bomItems = await pool.query("SELECT * FROM design_bom WHERE design_id = $1", [sub.design_id]);
        const qty = parseFloat(scheduled_qty);

        for (const bomItem of bomItems.rows) {
          const matId = bomItem.material_id;
          const reqQty = parseFloat(bomItem.required_qty) * qty;

          // Ensure inventory row exists
          await pool.query(
            "INSERT INTO raw_material_inventory (material_id, quantity_on_hand, allocated_quantity) VALUES ($1, 0, 0) ON CONFLICT (material_id) DO NOTHING",
            [matId]
          );

          // Update allocated quantity
          await pool.query(
            `UPDATE raw_material_inventory 
             SET allocated_quantity = allocated_quantity + $1 
             WHERE material_id = $2`,
            [reqQty, matId]
          );

          // Log transaction
          await pool.query(
            `INSERT INTO inventory_transaction_log (material_id, transaction_type, quantity, reference_id, created_by)
             VALUES ($1, 'Allocation', $2, $3, 'Scheduler Allocator')`,
            [matId, reqQty, `Schedule Allocation: ${scheduleItem.schedule_id}`]
          );
        }
      }
    }

    res.status(201).json(scheduleItem);
  } catch (err) {
    console.error("Error scheduling production:", err.message);
    res.status(500).json({ error: "Database error scheduling production" });
  }
});

// PUT: Update scheduling details or status
app.put("/api/production-schedule/:id", async (req, res) => {
  const { id } = req.params;
  const { machine_id, start_date, end_date, scheduled_qty, status } = req.body;
  try {
    // Get previous status to check for transitions
    const currentSchedule = await pool.query("SELECT * FROM production_schedule WHERE schedule_id = $1", [id]);
    if (currentSchedule.rowCount === 0) {
      return res.status(404).json({ error: "Schedule entry not found" });
    }
    const oldStatus = currentSchedule.rows[0].status;

    const query = `
      UPDATE production_schedule
      SET machine_id = COALESCE($1, machine_id),
          start_date = COALESCE($2, start_date),
          end_date = COALESCE($3, end_date),
          scheduled_qty = COALESCE($4, scheduled_qty),
          status = COALESCE($5, status)
      WHERE schedule_id = $6
      RETURNING *
    `;
    const result = await pool.query(query, [
      machine_id ? parseInt(machine_id) : null,
      start_date,
      end_date,
      scheduled_qty ? parseFloat(scheduled_qty) : null,
      status,
      id
    ]);
    
    const updatedRow = result.rows[0];

    // Trigger auto-consumption when transitioning to Completed
    if (status === "Completed" && oldStatus !== "Completed") {
      // Get sub-order and design ID
      const subOrderRes = await pool.query("SELECT * FROM sub_order WHERE sub_order_id = $1", [updatedRow.sub_order_id]);
      if (subOrderRes.rowCount > 0) {
        const sub = subOrderRes.rows[0];
        if (sub.design_id) {
          const bomItems = await pool.query("SELECT * FROM design_bom WHERE design_id = $1", [sub.design_id]);
          const schedQty = parseFloat(updatedRow.scheduled_qty) || 1.0;

          for (const bomItem of bomItems.rows) {
            const matId = bomItem.material_id;
            const reqQty = parseFloat(bomItem.required_qty) * schedQty;

            // Update inventory levels (reduce on_hand and allocated_quantity)
            await pool.query(
              `UPDATE raw_material_inventory 
               SET quantity_on_hand = quantity_on_hand - $1,
                   allocated_quantity = GREATEST(0, allocated_quantity - $1)
               WHERE material_id = $2`,
              [reqQty, matId]
            );

            // Log consumption transaction
            await pool.query(
              `INSERT INTO inventory_transaction_log (material_id, transaction_type, quantity, reference_id, created_by)
               VALUES ($1, 'Consumption', $2, $3, 'Auto Completed Scheduler')`,
              [matId, reqQty, `Auto-Consumption (Sub-Order: ${updatedRow.sub_order_id})`]
            );
          }
        }
      }
    }

    res.json(updatedRow);
  } catch (err) {
    console.error("Error updating production schedule:", err.message);
    res.status(500).json({ error: "Database error updating production schedule" });
  }
});

// DELETE: Cancel/remove a schedule
app.delete("/api/production-schedule/:id", async (req, res) => {
  const { id } = req.params;
  try {
    // Get schedule details before deleting to check status and quantity
    const schedRes = await pool.query("SELECT * FROM production_schedule WHERE schedule_id = $1", [id]);
    if (schedRes.rowCount === 0) {
      return res.status(404).json({ error: "Schedule entry not found" });
    }
    const sched = schedRes.rows[0];

    // Only release allocation if the schedule was not already Completed (completed status consumes stock rather than holding allocation)
    if (sched.status !== "Completed") {
      const subRes = await pool.query("SELECT * FROM sub_order WHERE sub_order_id = $1", [sched.sub_order_id]);
      if (subRes.rowCount > 0 && subRes.rows[0].design_id) {
        const bomItems = await pool.query("SELECT * FROM design_bom WHERE design_id = $1", [subRes.rows[0].design_id]);
        const qty = parseFloat(sched.scheduled_qty) || 0.0;

        for (const bomItem of bomItems.rows) {
          const matId = bomItem.material_id;
          const reqQty = parseFloat(bomItem.required_qty) * qty;

          // Deduct from allocated_quantity
          await pool.query(
            `UPDATE raw_material_inventory 
             SET allocated_quantity = GREATEST(0, allocated_quantity - $1) 
             WHERE material_id = $2`,
            [reqQty, matId]
          );

          // Log transaction
          await pool.query(
            `INSERT INTO inventory_transaction_log (material_id, transaction_type, quantity, reference_id, created_by)
             VALUES ($1, 'Adjustment', $2, $3, 'Scheduler Release')`,
            [matId, -reqQty, `Schedule Cancelled: ${id}`]
          );
        }
      }
    }

    const query = "DELETE FROM production_schedule WHERE schedule_id = $1";
    const result = await pool.query(query, [id]);
    res.json({ message: "Schedule entry removed successfully" });
  } catch (err) {
    console.error("Error deleting production schedule:", err.message);
    res.status(500).json({ error: "Database error deleting production schedule" });
  }
});



// GET: Fetch active sub-orders with customer, DC number, design ID & logged weight for production logging
app.get("/api/production/active-suborders", async (req, res) => {
  try {
    const query = `
      SELECT 
        s.sub_order_id,
        s.order_id,
        om.cus_id,
        COALESCE(c.customer_name, s.customer_name, 'N/A') as customer_name,
        COALESCE(s.dc_challan_no::text, s.outward_dc_challan_no::text, '—') as dc_no,
        COALESCE(s.design_id, 'N/A') as design_id,
        COALESCE(s.ground, '—') as ground,
        COALESCE(s.inward_weight, 0) as inward_weight,
        COALESCE(SUM(l.processed_qty), 0) as logged_weight
      FROM sub_order s
      LEFT JOIN order_master om ON s.order_id = om.order_id
      LEFT JOIN customer_master c ON om.cus_id = c.cus_id
      LEFT JOIN production_logs l ON s.sub_order_id = l.order_ref
      WHERE s.suborder_dept_flow_status != 'delivered' OR s.suborder_dept_flow_status IS NULL
      GROUP BY s.sub_order_id, om.cus_id, c.customer_name, s.customer_name, s.dc_challan_no, s.outward_dc_challan_no, s.design_id, s.ground, s.inward_weight, s.suborder_date
      ORDER BY s.suborder_date DESC
    `;
    const result = await pool.query(query);
    res.json(result.rows);
  } catch (err) {
    console.error("Error fetching active suborders for production:", err);
    res.status(500).json({ error: "Server error" });
  }
});

// GET: Fetch all unscheduled sub-orders
app.get("/api/unscheduled-suborders", async (req, res) => {
  try {
    const query = `
      SELECT s.*, d.design_name, d.image_path,
             COALESCE(s.inward_weight, s.roll_length, s.diameter, 1) as qty
      FROM sub_order s
      LEFT JOIN design_master d ON s.design_id = d.design_id
      LEFT JOIN production_schedule p ON s.sub_order_id = p.sub_order_id
      WHERE p.schedule_id IS NULL
      ORDER BY s.suborder_date DESC
    `;
    const result = await pool.query(query);
    res.json(result.rows);
  } catch (err) {
    console.error("Error fetching unscheduled sub-orders:", err.message);
    res.status(500).json({ error: "Database error fetching unscheduled sub-orders" });
  }
});

// ==========================================
// PRODUCTION MANAGEMENT API ENDPOINTS & TABLES (PostgreSQL)
// ==========================================

// Ensure production tables exist & seed default values if empty
(async function initProductionTablesInServer() {
  try {
    await pool.query(`
      CREATE TABLE IF NOT EXISTS production_employees (
        id VARCHAR(50) PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        role VARCHAR(100) DEFAULT 'Operator',
        department VARCHAR(100) DEFAULT 'Digital Printing',
        shift VARCHAR(50) DEFAULT 'Day',
        mobile VARCHAR(50)
      );
      CREATE TABLE IF NOT EXISTS production_outsourced (
        id VARCHAR(50) PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        agency VARCHAR(255) DEFAULT 'Apex Labor Solutions',
        task VARCHAR(255) DEFAULT 'General Production',
        shift VARCHAR(50) DEFAULT 'Day'
      );
      CREATE TABLE IF NOT EXISTS production_machines (
        id VARCHAR(50) PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        status VARCHAR(50) DEFAULT 'Running',
        speed VARCHAR(100) DEFAULT '100m/min',
        last_maintenance DATE
      );
      CREATE TABLE IF NOT EXISTS production_logs (
        id SERIAL PRIMARY KEY,
        machine_id VARCHAR(50) REFERENCES production_machines(id) ON DELETE CASCADE,
        log_date DATE NOT NULL,
        worker_type VARCHAR(50),
        worker_id VARCHAR(50),
        processed_qty NUMERIC(10, 2) DEFAULT 0,
        order_ref VARCHAR(100)
      );
      CREATE TABLE IF NOT EXISTS production_attendance (
        worker_id VARCHAR(50) NOT NULL,
        worker_type VARCHAR(50),
        log_date DATE NOT NULL,
        status VARCHAR(20) DEFAULT 'Present',
        PRIMARY KEY (worker_id, log_date)
      );
    `);

    // Seed permanent employees if empty
    await pool.query(`
      INSERT INTO production_employees (id, name, role, department, shift, mobile) VALUES
      ('EMP001', 'Arun Kumar', 'Senior Printer', 'Digital Printing', 'Day', '9876543210'),
      ('EMP002', 'Priya Dharshini', 'QC Specialist', 'Quality Control', 'Day', '9876543211'),
      ('EMP003', 'Karthik Raja', 'Machine Operator', 'Digital Printing', 'Night', '9876543212'),
      ('EMP004', 'Anitha Ramesh', 'Dyeing Specialist', 'Chemical Dyeing', 'Day', '9876543213'),
      ('EMP005', 'Rajesh Kannan', 'Stenter Operator', 'Stenter & Drying', 'Night', '9876543214'),
      ('EMP006', 'Suresh Kumar', 'Operator', 'Calendering Press', 'Day', '9876543215')
      ON CONFLICT (id) DO NOTHING;
    `);

    // Seed outsourced workers if empty
    await pool.query(`
      INSERT INTO production_outsourced (id, name, agency, task, shift) VALUES
      ('OUT-001', 'Manoj S', 'Apex Labor Solutions', 'Yarn Sorting & Feeding', 'Day'),
      ('OUT-002', 'Vignesh M', 'Prime Staffing', 'Dye Mixing Assistant', 'Night'),
      ('OUT-003', 'Murugan K', 'Apex Labor Solutions', 'Fabric Press Helper', 'Day'),
      ('OUT-004', 'Lakshmi Narayanan', 'Global Workforce', 'Fabric Inspection', 'Day')
      ON CONFLICT (id) DO NOTHING;
    `);

    // Seed machines if empty
    await pool.query(`
      INSERT INTO production_machines (id, name, status, speed, last_maintenance) VALUES
      ('MAC-01', 'Kornit Allegro Digital Printer', 'Running', '150m/min', '2026-06-15'),
      ('MAC-02', 'Reggiani Renoir Superfast', 'Maintenance', '200m/min', '2026-07-01'),
      ('MAC-03', 'Mimaki Tiger-1800B', 'Running', '120m/min', '2026-05-20'),
      ('MAC-04', 'Zimmer Colaris Digital Printer', 'Running', '180m/min', '2026-06-30')
      ON CONFLICT (id) DO NOTHING;
    `);

    // Seed sample production logs if empty
    const logsCount = await pool.query("SELECT COUNT(*) FROM production_logs");
    if (parseInt(logsCount.rows[0].count) === 0) {
      const todayStr = new Date().toISOString().split("T")[0];
      await pool.query(`
        INSERT INTO production_logs (machine_id, log_date, worker_type, worker_id, processed_qty, order_ref) VALUES
        ('MAC-01', '${todayStr}', 'permanent', 'EMP001', 450, 'ORD-2026-881'),
        ('MAC-01', '${todayStr}', 'outsourced', 'OUT-001', 320, 'ORD-2026-882'),
        ('MAC-02', '${todayStr}', 'permanent', 'EMP003', 600, 'ORD-2026-790'),
        ('MAC-03', '${todayStr}', 'permanent', 'EMP002', 280, 'ORD-2026-905'),
        ('MAC-03', '${todayStr}', 'outsourced', 'OUT-002', 410, 'ORD-2026-910'),
        ('MAC-04', '${todayStr}', 'permanent', 'EMP004', 530, 'ORD-2026-944'),
        ('MAC-04', '${todayStr}', 'outsourced', 'OUT-003', 390, 'ORD-2026-950');
      `);
      await pool.query(`
        INSERT INTO production_attendance (worker_id, worker_type, log_date, status) VALUES
        ('EMP001', 'permanent', '${todayStr}', 'Present'),
        ('EMP002', 'permanent', '${todayStr}', 'Present'),
        ('EMP003', 'permanent', '${todayStr}', 'Present'),
        ('EMP004', 'permanent', '${todayStr}', 'Present'),
        ('EMP005', 'permanent', '${todayStr}', 'Present'),
        ('EMP006', 'permanent', '${todayStr}', 'Absent'),
        ('OUT-001', 'outsourced', '${todayStr}', 'Present'),
        ('OUT-002', 'outsourced', '${todayStr}', 'Present'),
        ('OUT-003', 'outsourced', '${todayStr}', 'Present'),
        ('OUT-004', 'outsourced', '${todayStr}', 'Absent')
        ON CONFLICT (worker_id, log_date) DO NOTHING;
      `);
    }
  } catch (err) {
    console.error("Error setting up production tables in server:", err.message);
  }
})();

// Helper to safely format dates as YYYY-MM-DD strings
function formatDateStr(val) {
  if (!val) return "";
  if (typeof val === 'string') return val.split("T")[0];
  if (val instanceof Date) {
    const y = val.getFullYear();
    const m = String(val.getMonth() + 1).padStart(2, '0');
    const d = String(val.getDate()).padStart(2, '0');
    return `${y}-${m}-${d}`;
  }
  return String(val).split("T")[0];
}

// Helper to fetch attendance object for workers
async function getAttendanceMapForWorker(workerId) {
  try {
    const res = await pool.query(
      "SELECT TO_CHAR(log_date, 'YYYY-MM-DD') as log_date, status FROM production_attendance WHERE worker_id = $1",
      [workerId]
    );
    const map = {};
    res.rows.forEach(r => {
      if (r.log_date) map[r.log_date] = r.status;
    });
    return map;
  } catch {
    return {};
  }
}

// 1. EMPLOYEES (Permanent Workers)
app.get("/api/production/employees", async (req, res) => {
  try {
    const result = await pool.query("SELECT * FROM production_employees ORDER BY id ASC");
    const attResult = await pool.query("SELECT worker_id, TO_CHAR(log_date, 'YYYY-MM-DD') as log_date, status FROM production_attendance WHERE worker_type = 'permanent' OR worker_type IS NULL");
    
    const attMap = {};
    attResult.rows.forEach(r => {
      if (!attMap[r.worker_id]) attMap[r.worker_id] = {};
      if (r.log_date) attMap[r.worker_id][r.log_date] = r.status;
    });

    const rows = result.rows.map(emp => ({
      ...emp,
      attendance: attMap[emp.id] || {}
    }));
    res.json(rows);
  } catch (err) {
    console.error("Error fetching employees:", err);
    res.status(500).json({ error: "Server error" });
  }
});

app.post("/api/production/employees", async (req, res) => {
  try {
    const { id, name, role, department, shift, mobile } = req.body;
    const result = await pool.query(
      "INSERT INTO production_employees (id, name, role, department, shift, mobile) VALUES ($1, $2, $3, $4, $5, $6) RETURNING *",
      [id, name, role, department, shift, mobile]
    );
    res.status(201).json({ ...result.rows[0], attendance: {} });
  } catch (err) {
    console.error("Error creating employee:", err);
    res.status(500).json({ error: "Server error" });
  }
});

app.put("/api/production/employees/:id", async (req, res) => {
  try {
    const { id } = req.params;
    const { name, role, department, shift, mobile } = req.body;
    const result = await pool.query(
      "UPDATE production_employees SET name = $1, role = $2, department = $3, shift = $4, mobile = $5 WHERE id = $6 RETURNING *",
      [name, role, department, shift, mobile, id]
    );
    const attendance = await getAttendanceMapForWorker(id);
    res.json({ ...result.rows[0], attendance });
  } catch (err) {
    console.error("Error updating employee:", err);
    res.status(500).json({ error: "Server error" });
  }
});

app.delete("/api/production/employees/:id", async (req, res) => {
  try {
    const { id } = req.params;
    await pool.query("DELETE FROM production_employees WHERE id = $1", [id]);
    await pool.query("DELETE FROM production_attendance WHERE worker_id = $1", [id]);
    res.json({ message: "Employee deleted successfully" });
  } catch (err) {
    console.error("Error deleting employee:", err);
    res.status(500).json({ error: "Server error" });
  }
});

// 2. OUTSOURCED WORKERS
app.get("/api/production/outsourced", async (req, res) => {
  try {
    const result = await pool.query("SELECT * FROM production_outsourced ORDER BY id ASC");
    const attResult = await pool.query("SELECT worker_id, TO_CHAR(log_date, 'YYYY-MM-DD') as log_date, status FROM production_attendance WHERE worker_type = 'outsourced' OR worker_type IS NULL");
    
    const attMap = {};
    attResult.rows.forEach(r => {
      if (!attMap[r.worker_id]) attMap[r.worker_id] = {};
      if (r.log_date) attMap[r.worker_id][r.log_date] = r.status;
    });

    const rows = result.rows.map(emp => ({
      ...emp,
      attendance: attMap[emp.id] || {}
    }));
    res.json(rows);
  } catch (err) {
    console.error("Error fetching outsourced workers:", err);
    res.status(500).json({ error: "Server error" });
  }
});

app.post("/api/production/outsourced", async (req, res) => {
  try {
    const { id, name, agency, task, shift } = req.body;
    const result = await pool.query(
      "INSERT INTO production_outsourced (id, name, agency, task, shift) VALUES ($1, $2, $3, $4, $5) RETURNING *",
      [id, name, agency, task, shift]
    );
    res.status(201).json({ ...result.rows[0], attendance: {} });
  } catch (err) {
    console.error("Error creating outsourced worker:", err);
    res.status(500).json({ error: "Server error" });
  }
});

app.put("/api/production/outsourced/:id", async (req, res) => {
  try {
    const { id } = req.params;
    const { name, agency, task, shift } = req.body;
    const result = await pool.query(
      "UPDATE production_outsourced SET name = $1, agency = $2, task = $3, shift = $4 WHERE id = $5 RETURNING *",
      [name, agency, task, shift, id]
    );
    const attendance = await getAttendanceMapForWorker(id);
    res.json({ ...result.rows[0], attendance });
  } catch (err) {
    console.error("Error updating outsourced worker:", err);
    res.status(500).json({ error: "Server error" });
  }
});

app.delete("/api/production/outsourced/:id", async (req, res) => {
  try {
    const { id } = req.params;
    await pool.query("DELETE FROM production_outsourced WHERE id = $1", [id]);
    await pool.query("DELETE FROM production_attendance WHERE worker_id = $1", [id]);
    res.json({ message: "Outsourced worker deleted successfully" });
  } catch (err) {
    console.error("Error deleting outsourced worker:", err);
    res.status(500).json({ error: "Server error" });
  }
});

// 3. MACHINES
app.get("/api/production/machines", async (req, res) => {
  try {
    const result = await pool.query("SELECT id, name, status, speed, TO_CHAR(last_maintenance, 'YYYY-MM-DD') as last_maintenance FROM production_machines ORDER BY id ASC");
    const rows = result.rows.map(m => ({
      ...m,
      lastMaintenance: m.last_maintenance || ""
    }));
    res.json(rows);
  } catch (err) {
    console.error("Error fetching machines:", err);
    res.status(500).json({ error: "Server error" });
  }
});

app.post("/api/production/machines", async (req, res) => {
  try {
    const { id, name, status, speed, lastMaintenance } = req.body;
    const result = await pool.query(
      "INSERT INTO production_machines (id, name, status, speed, last_maintenance) VALUES ($1, $2, $3, $4, $5) RETURNING id, name, status, speed, TO_CHAR(last_maintenance, 'YYYY-MM-DD') as last_maintenance",
      [id, name, status, speed, lastMaintenance || null]
    );
    const row = result.rows[0];
    res.status(201).json({
      ...row,
      lastMaintenance: row.last_maintenance || ""
    });
  } catch (err) {
    console.error("Error creating machine:", err);
    res.status(500).json({ error: "Server error" });
  }
});

app.put("/api/production/machines/:id", async (req, res) => {
  try {
    const { id } = req.params;
    const { name, status, speed, lastMaintenance } = req.body;
    const result = await pool.query(
      "UPDATE production_machines SET name = $1, status = $2, speed = $3, last_maintenance = $4 WHERE id = $5 RETURNING id, name, status, speed, TO_CHAR(last_maintenance, 'YYYY-MM-DD') as last_maintenance",
      [name, status, speed, lastMaintenance || null, id]
    );
    const row = result.rows[0];
    res.json({
      ...row,
      lastMaintenance: row.last_maintenance || ""
    });
  } catch (err) {
    console.error("Error updating machine:", err);
    res.status(500).json({ error: "Server error" });
  }
});

app.delete("/api/production/machines/:id", async (req, res) => {
  try {
    const { id } = req.params;
    await pool.query("DELETE FROM production_machines WHERE id = $1", [id]);
    res.json({ message: "Machine deleted successfully" });
  } catch (err) {
    console.error("Error deleting machine:", err);
    res.status(500).json({ error: "Server error" });
  }
});

// 4. PRODUCTION LOGS (Supports POST /api/production/logs & POST /api/production/machine-logs)
app.get("/api/production/logs", async (req, res) => {
  try {
    const { date } = req.query;
    let query = `
      SELECT l.id, l.machine_id, TO_CHAR(l.log_date, 'YYYY-MM-DD') as log_date, l.worker_type, l.worker_id, l.processed_qty, l.order_ref,
        m.name as machine_name,
        COALESCE(e.name, o.name, 'Unassigned') as worker_name,
        COALESCE(c.customer_name, s.customer_name, 'N/A') as customer_name,
        c.cus_id,
        COALESCE(s.dc_challan_no::text, s.outward_dc_challan_no::text, '—') as dc_no,
        COALESCE(s.design_id, 'N/A') as design_id,
        s.ground
      FROM production_logs l
      LEFT JOIN production_machines m ON l.machine_id = m.id
      LEFT JOIN production_employees e ON l.worker_id = e.id
      LEFT JOIN production_outsourced o ON l.worker_id = o.id
      LEFT JOIN sub_order s ON l.order_ref = s.sub_order_id
      LEFT JOIN order_master om ON s.order_id = om.order_id
      LEFT JOIN customer_master c ON om.cus_id = c.cus_id
    `;
    const params = [];
    if (date) {
      query += " WHERE l.log_date = $1";
      params.push(date);
    }
    query += " ORDER BY l.id DESC";
    const result = await pool.query(query, params);
    res.json(result.rows);
  } catch (err) {
    console.error("Error fetching production logs:", err);
    res.status(500).json({ error: "Server error" });
  }
});

const handleSaveProductionLog = async (req, res) => {
  try {
    const machineId = req.body.machineId || req.body.machine_id;
    const date = req.body.date || req.body.log_date;
    const workerType = req.body.workerType || req.body.operatorType;
    const workerId = req.body.workerId || req.body.operatorId;
    const processedQty = req.body.processedQty !== undefined ? req.body.processedQty : req.body.processed;
    const orderRef = req.body.orderRef || req.body.order_ref;

    const result = await pool.query(
      "INSERT INTO production_logs (machine_id, log_date, worker_type, worker_id, processed_qty, order_ref) VALUES ($1, $2, $3, $4, $5, $6) RETURNING *",
      [machineId, date, workerType, workerId, processedQty || 0, orderRef]
    );

    // Automatically set attendance to Present for worker on this log date
    if (workerId && date) {
      await pool.query(
        `INSERT INTO production_attendance (worker_id, worker_type, log_date, status)
         VALUES ($1, $2, $3, 'Present')
         ON CONFLICT (worker_id, log_date) DO UPDATE SET status = 'Present'`,
        [workerId, workerType || 'permanent', date]
      );
    }

    const insertedRow = result.rows[0];
    res.status(201).json({
      ...insertedRow,
      log_date: insertedRow.log_date ? new Date(insertedRow.log_date).toISOString().split("T")[0] : insertedRow.log_date
    });
  } catch (err) {
    console.error("Error creating production log:", err);
    res.status(500).json({ error: "Server error" });
  }
};

app.post("/api/production/logs", handleSaveProductionLog);
app.post("/api/production/machine-logs", handleSaveProductionLog);

// 5. ATTENDANCE TOGGLE ENDPOINT
app.post("/api/production/attendance/toggle", async (req, res) => {
  try {
    const { workerId, workerType, date, status } = req.body;
    if (!workerId || !date) {
      return res.status(400).json({ error: "workerId and date are required" });
    }
    const nextStatus = status || 'Present';
    const result = await pool.query(
      `INSERT INTO production_attendance (worker_id, worker_type, log_date, status)
       VALUES ($1, $2, $3, $4)
       ON CONFLICT (worker_id, log_date) DO UPDATE SET status = EXCLUDED.status
       RETURNING *`,
      [workerId, workerType || 'permanent', date, nextStatus]
    );
    res.json(result.rows[0]);
  } catch (err) {
    console.error("Error toggling attendance:", err);
    res.status(500).json({ error: "Server error" });
  }
});

// 6. WORKER PRODUCTION HISTORY
app.get("/api/production/worker-history/:workerId", async (req, res) => {
  try {
    const { workerId } = req.params;
    const result = await pool.query(
      `SELECT l.*, m.name as machine_name
       FROM production_logs l
       LEFT JOIN production_machines m ON l.machine_id = m.id
       WHERE l.worker_id = $1
       ORDER BY l.log_date DESC, l.id DESC`,
      [workerId]
    );
    const rows = result.rows.map(r => ({
      ...r,
      log_date: r.log_date ? new Date(r.log_date).toISOString().split("T")[0] : r.log_date
    }));
    res.json(rows);
  } catch (err) {
    console.error("Error fetching worker history:", err);
    res.status(500).json({ error: "Server error" });
  }
});

// ==========================================
// YARN LEDGER & STOCK API ENDPOINTS
// ==========================================

// Ensure yarn_ledger and customer_yarn_stock tables exist
pool.query(`
  CREATE TABLE IF NOT EXISTS yarn_ledger (
    id SERIAL PRIMARY KEY,
    cus_id VARCHAR(50) NOT NULL,
    yarn_type VARCHAR(100) NOT NULL,
    quantity NUMERIC(12,2) NOT NULL,
    transaction_type VARCHAR(20) NOT NULL, -- INWARD or OUTWARD
    order_id VARCHAR(50),
    created_at TIMESTAMP DEFAULT NOW()
  );
  CREATE TABLE IF NOT EXISTS customer_yarn_stock (
    cus_id VARCHAR(50) NOT NULL,
    yarn_type VARCHAR(100) NOT NULL,
    total_inward NUMERIC(12,2) DEFAULT 0,
    used_in_leads NUMERIC(12,2) DEFAULT 0,
    used_in_orders NUMERIC(12,2) DEFAULT 0,
    PRIMARY KEY (cus_id, yarn_type)
  );
`).catch(err => console.error("Error setting up yarn tables:", err.message));

// GET: Yarn stock balance summary for a customer
app.get("/api/yarn/stock/:cus_id", async (req, res) => {
  try {
    const { cus_id } = req.params;
    const result = await pool.query(
      "SELECT * FROM customer_yarn_stock WHERE cus_id = $1 ORDER BY yarn_type ASC",
      [cus_id]
    );
    res.json(result.rows);
  } catch (err) {
    console.error("Error fetching yarn stock:", err);
    res.status(500).json({ error: "Server error" });
  }
});

// GET: Yarn ledger transaction history for a customer
app.get("/api/yarn/ledger/:cus_id", async (req, res) => {
  try {
    const { cus_id } = req.params;
    const result = await pool.query(
      "SELECT * FROM yarn_ledger WHERE cus_id = $1 ORDER BY created_at DESC, id DESC",
      [cus_id]
    );
    res.json(result.rows);
  } catch (err) {
    console.error("Error fetching yarn ledger:", err);
    res.status(500).json({ error: "Server error" });
  }
});

// POST: Record Yarn Inward
app.post("/api/yarn/inward", async (req, res) => {
  const { cus_id, yarn_type, quantity, order_id } = req.body;
  if (!cus_id || !yarn_type || !quantity) {
    return res.status(400).json({ error: "cus_id, yarn_type, and quantity are required" });
  }
  try {
    const qty = parseFloat(quantity);
    await pool.query("BEGIN");
    
    await pool.query(
      `INSERT INTO yarn_ledger (cus_id, yarn_type, quantity, transaction_type, order_id, created_at)
       VALUES ($1, $2, $3, 'INWARD', $4, NOW())`,
      [cus_id, yarn_type, qty, order_id || null]
    );

    await pool.query(
      `INSERT INTO customer_yarn_stock (cus_id, yarn_type, total_inward)
       VALUES ($1, $2, $3)
       ON CONFLICT (cus_id, yarn_type) DO UPDATE
       SET total_inward = customer_yarn_stock.total_inward + EXCLUDED.total_inward`,
      [cus_id, yarn_type, qty]
    );

    await pool.query("COMMIT");
    res.status(201).json({ message: "Yarn inward recorded successfully" });
  } catch (err) {
    await pool.query("ROLLBACK");
    console.error("Error recording yarn inward:", err);
    res.status(500).json({ error: "Server error" });
  }
});

// POST: Record Yarn Outward
app.post("/api/yarn/outward", async (req, res) => {
  const { cus_id, yarn_type, quantity, order_id } = req.body;
  if (!cus_id || !yarn_type || !quantity) {
    return res.status(400).json({ error: "cus_id, yarn_type, and quantity are required" });
  }
  try {
    const qty = parseFloat(quantity);
    await pool.query("BEGIN");

    await pool.query(
      `INSERT INTO yarn_ledger (cus_id, yarn_type, quantity, transaction_type, order_id, created_at)
       VALUES ($1, $2, $3, 'OUTWARD', $4, NOW())`,
      [cus_id, yarn_type, qty, order_id || null]
    );

    await pool.query(
      `INSERT INTO customer_yarn_stock (cus_id, yarn_type, total_inward, used_in_orders)
       VALUES ($1, $2, 0, $3)
       ON CONFLICT (cus_id, yarn_type) DO UPDATE
       SET used_in_orders = customer_yarn_stock.used_in_orders + EXCLUDED.used_in_orders`,
      [cus_id, yarn_type, qty]
    );

    await pool.query("COMMIT");
    res.status(201).json({ message: "Yarn outward recorded successfully" });
  } catch (err) {
    await pool.query("ROLLBACK");
    console.error("Error recording yarn outward:", err);
    res.status(500).json({ error: "Server error" });
  }
});



// ==========================================================================
// ACCOUNTS MODULE API ROUTES
// ==========================================================================
// 1. Search orders by Customer Name, Design ID, or Order ID (COMPLETED/DELIVERED ORDERS ONLY)
app.get("/api/accounts/search-orders", async (req, res) => {
  try {
    const customerName = (req.query.customerName || req.query.query || "").trim();
    const designId = (req.query.designId || "").trim();
    const orderId = (req.query.orderId || "").trim();

    if (!customerName && !designId && !orderId) {
      return res.json([]);
    }

    let conditions = [];
    let params = [];

    if (customerName) {
      params.push(`%${customerName}%`);
      conditions.push(`(c.customer_name ILIKE $${params.length} OR om.cus_id ILIKE $${params.length})`);
    }
    if (designId) {
      params.push(`%${designId}%`);
      conditions.push(`(so.design_id ILIKE $${params.length} OR so.design_style ILIKE $${params.length})`);
    }
    if (orderId) {
      params.push(`%${orderId}%`);
      conditions.push(`(om.order_id ILIKE $${params.length})`);
    }

    // Restrict Accounts search ONLY to completed or delivered orders
    conditions.push(`(
      LOWER(COALESCE(om.order_status, '')) LIKE '%deliver%' OR 
      LOWER(COALESCE(om.order_status, '')) LIKE '%complete%' OR 
      LOWER(COALESCE(om.order_status, '')) LIKE '%dispatch%' OR 
      LOWER(COALESCE(om.order_status, '')) IN ('done', 'closed') OR 
      LOWER(COALESCE(so.suborder_dept_flow_status, '')) LIKE '%deliver%' OR 
      LOWER(COALESCE(so.suborder_dept_flow_status, '')) LIKE '%complete%' OR 
      LOWER(COALESCE(so.suborder_dept_flow_status, '')) LIKE '%dispatch%' OR 
      LOWER(COALESCE(so.suborder_dept_flow_status, '')) IN ('done', 'closed') OR 
      LOWER(COALESCE(so.dept_status, '')) LIKE '%deliver%' OR 
      LOWER(COALESCE(so.dept_status, '')) LIKE '%complete%' OR 
      LOWER(COALESCE(so.dept_status, '')) LIKE '%dispatch%' OR 
      LOWER(COALESCE(so.dept_status, '')) IN ('done', 'closed') OR 
      om.actual_delivery_date IS NOT NULL OR
      so.actual_delivery_date IS NOT NULL
    )`);

    // Exclude orders that ALREADY have an invoice generated in accounts_invoices
    conditions.push(`om.order_id NOT IN (SELECT DISTINCT order_id FROM accounts_invoices WHERE order_id IS NOT NULL)`);

    const query = `
      SELECT DISTINCT 
        om.order_id, 
        om.order_date, 
        om.cus_id, 
        COALESCE(c.customer_name, 'Unknown Customer') AS customer_name,
        COALESCE(STRING_AGG(DISTINCT so.design_id, ', '), 'N/A') AS design_ids,
        COALESCE(STRING_AGG(DISTINCT so.design_style, ', '), 'N/A') AS design_names,
        COALESCE(om.order_status, MAX(so.suborder_dept_flow_status), 'Delivered') AS order_status
      FROM order_master om
      LEFT JOIN customer_master c ON om.cus_id = c.cus_id
      LEFT JOIN sub_order so ON om.order_id = so.order_id
      WHERE ${conditions.join(" AND ")}
      GROUP BY om.order_id, om.order_date, om.cus_id, c.customer_name, om.order_status
      ORDER BY om.order_id DESC
      LIMIT 25
    `;

    const result = await pool.query(query, params);
    res.json(result.rows);
  } catch (err) {
    console.error("Accounts search-orders error:", err);
    res.status(500).json({ error: "Server error searching orders" });
  }
});

// Endpoint for recently delivered / completed orders (ONLY uninvoiced / no payment received)
app.get("/api/accounts/recently-delivered-orders", async (req, res) => {
  try {
    const query = `
      SELECT 
        om.order_id, 
        om.order_date, 
        om.actual_delivery_date,
        om.cus_id, 
        COALESCE(c.customer_name, 'Unknown Customer') AS customer_name,
        COALESCE(STRING_AGG(DISTINCT so.design_id, ', '), 'N/A') AS design_ids,
        COALESCE(STRING_AGG(DISTINCT so.design_style, ', '), 'N/A') AS design_names,
        COALESCE(SUM(so.suborder_price), 0) AS total_amount,
        COALESCE(om.order_status, MAX(so.suborder_dept_flow_status), 'Delivered') AS order_status,
        MAX(COALESCE(so.actual_delivery_date::text, om.actual_delivery_date::text, om.order_date::text)) AS latest_delivery_date
      FROM order_master om
      LEFT JOIN customer_master c ON om.cus_id = c.cus_id
      LEFT JOIN sub_order so ON om.order_id = so.order_id
      WHERE (
        LOWER(COALESCE(om.order_status, '')) LIKE '%deliver%' OR 
        LOWER(COALESCE(om.order_status, '')) LIKE '%complete%' OR 
        LOWER(COALESCE(om.order_status, '')) LIKE '%dispatch%' OR 
        LOWER(COALESCE(om.order_status, '')) IN ('done', 'closed') OR 
        LOWER(COALESCE(so.suborder_dept_flow_status, '')) LIKE '%deliver%' OR 
        LOWER(COALESCE(so.suborder_dept_flow_status, '')) LIKE '%complete%' OR 
        LOWER(COALESCE(so.suborder_dept_flow_status, '')) LIKE '%dispatch%' OR 
        LOWER(COALESCE(so.suborder_dept_flow_status, '')) IN ('done', 'closed') OR 
        LOWER(COALESCE(so.dept_status, '')) LIKE '%deliver%' OR 
        LOWER(COALESCE(so.dept_status, '')) LIKE '%complete%' OR 
        LOWER(COALESCE(so.dept_status, '')) LIKE '%dispatch%' OR 
        LOWER(COALESCE(so.dept_status, '')) IN ('done', 'closed') OR 
        om.actual_delivery_date IS NOT NULL OR
        so.actual_delivery_date IS NOT NULL
      )
      AND om.order_id NOT IN (SELECT DISTINCT order_id FROM accounts_invoices WHERE order_id IS NOT NULL)
      GROUP BY om.order_id, om.order_date, om.actual_delivery_date, om.cus_id, c.customer_name, om.order_status
      ORDER BY latest_delivery_date DESC, om.order_id DESC
      LIMIT 20
    `;
    const result = await pool.query(query);
    res.json(result.rows);
  } catch (err) {
    console.error("Recently delivered orders fetch error:", err);
    res.status(500).json({ error: "Server error fetching recently delivered orders" });
  }
});

// 2. Retrieve complete order details by order_id for invoice generation
app.get("/api/accounts/order-details/:order_id", async (req, res) => {
  try {
    const { order_id } = req.params;

    const orderQuery = `
      SELECT om.*, c.customer_name 
      FROM order_master om
      LEFT JOIN customer_master c ON om.cus_id = c.cus_id
      WHERE om.order_id = $1
    `;
    const orderRes = await pool.query(orderQuery, [order_id]);

    if (orderRes.rows.length === 0) {
      return res.status(404).json({ error: "Order not found" });
    }

    const subOrderQuery = `
      SELECT * FROM sub_order WHERE order_id = $1 ORDER BY sub_order_id
    `;
    const subRes = await pool.query(subOrderQuery, [order_id]);

    const orderObj = orderRes.rows[0];
    const items = subRes.rows.map((so) => {
      const qty = parseFloat(so.inward_weight || so.delivered_weight || so.roll_length || 100);
      const rate = parseFloat(so.suborder_price || 85);
      return {
        subOrderId: so.sub_order_id,
        designId: so.design_id || "N/A",
        desc: `${so.design_style || "Printing Work"} (${so.print_type || ""})`,
        qty: qty,
        rate: rate
      };
    });

    res.json({
      orderId: orderObj.order_id,
      customerName: orderObj.customer_name || "Customer",
      customerId: orderObj.cus_id || "C-0001",
      designId: items[0]?.designId || "DES-100",
      designName: items[0]?.desc || "Textile Printing",
      orderDate: orderObj.order_date ? new Date(orderObj.order_date).toISOString().split("T")[0] : new Date().toISOString().split("T")[0],
      items: items.length > 0 ? items : [{ subOrderId: `${order_id}_S1`, desc: "Standard Textile Printing", qty: 100, rate: 90 }]
    });
  } catch (err) {
    console.error("Accounts order-details error:", err);
    res.status(500).json({ error: "Server error fetching order details" });
  }
});

// 3. Fetch all invoices with payment history
app.get("/api/accounts/invoices", async (req, res) => {
  try {
    const invRes = await pool.query("SELECT * FROM accounts_invoices ORDER BY created_at DESC");
    const payRes = await pool.query("SELECT * FROM accounts_payments ORDER BY payment_date ASC");

    const paymentsByInv = {};
    payRes.rows.forEach(p => {
      if (!paymentsByInv[p.invoice_id]) paymentsByInv[p.invoice_id] = [];
      paymentsByInv[p.invoice_id].push({
        paymentId: p.payment_id,
        date: p.payment_date ? new Date(p.payment_date).toISOString().split("T")[0] : "",
        amount: parseFloat(p.amount),
        mode: p.payment_mode,
        ref: p.reference_no
      });
    });

    const formattedInvoices = invRes.rows.map(inv => ({
      invoiceId: inv.invoice_id,
      orderId: inv.order_id,
      customerId: inv.cus_id,
      customerName: inv.customer_name,
      invoiceDate: inv.invoice_date ? new Date(inv.invoice_date).toISOString().split("T")[0] : "",
      dueDate: inv.due_date ? new Date(inv.due_date).toISOString().split("T")[0] : "",
      subtotal: parseFloat(inv.subtotal),
      discount: parseFloat(inv.discount),
      gstPercent: parseFloat(inv.gst_percent),
      gstAmount: parseFloat(inv.gst_amount),
      netAmount: parseFloat(inv.net_amount),
      paidAmount: parseFloat(inv.paid_amount),
      balance: parseFloat(inv.balance),
      status: inv.status,
      items: inv.items || [],
      payments: paymentsByInv[inv.invoice_id] || []
    }));

    res.json(formattedInvoices);
  } catch (err) {
    console.error("Accounts get invoices error:", err);
    res.status(500).json({ error: "Server error fetching invoices" });
  }
});

// 4. Fetch single invoice by ID
app.get("/api/accounts/invoices/:invoiceId", async (req, res) => {
  try {
    const { invoiceId } = req.params;
    const invRes = await pool.query("SELECT * FROM accounts_invoices WHERE invoice_id = $1", [invoiceId]);
    if (invRes.rows.length === 0) {
      return res.status(404).json({ error: "Invoice not found" });
    }

    const payRes = await pool.query("SELECT * FROM accounts_payments WHERE invoice_id = $1 ORDER BY payment_date ASC", [invoiceId]);
    const inv = invRes.rows[0];

    res.json({
      invoiceId: inv.invoice_id,
      orderId: inv.order_id,
      customerId: inv.cus_id,
      customerName: inv.customer_name,
      invoiceDate: inv.invoice_date ? new Date(inv.invoice_date).toISOString().split("T")[0] : "",
      dueDate: inv.due_date ? new Date(inv.due_date).toISOString().split("T")[0] : "",
      subtotal: parseFloat(inv.subtotal),
      discount: parseFloat(inv.discount),
      gstPercent: parseFloat(inv.gst_percent),
      gstAmount: parseFloat(inv.gst_amount),
      netAmount: parseFloat(inv.net_amount),
      paidAmount: parseFloat(inv.paid_amount),
      balance: parseFloat(inv.balance),
      status: inv.status,
      items: inv.items || [],
      payments: payRes.rows.map(p => ({
        paymentId: p.payment_id,
        date: p.payment_date ? new Date(p.payment_date).toISOString().split("T")[0] : "",
        amount: parseFloat(p.amount),
        mode: p.payment_mode,
        ref: p.reference_no
      }))
    });
  } catch (err) {
    console.error("Accounts get invoice error:", err);
    res.status(500).json({ error: "Server error fetching invoice" });
  }
});

// 5. Create new Invoice
app.post("/api/accounts/invoices", async (req, res) => {
  const {
    orderId,
    customerId,
    customerName,
    dueDate,
    subtotal,
    discount,
    gstPercent,
    gstAmount,
    netAmount,
    paidAmount,
    balance,
    status,
    items,
    paymentMode,
    paymentRef
  } = req.body;

  try {
    const countRes = await pool.query("SELECT COUNT(*) FROM accounts_invoices");
    const nextNum = parseInt(countRes.rows[0].count) + 1;
    const year = new Date().getFullYear();
    const invoiceId = `INV-${year}-${String(nextNum).padStart(4, '0')}`;

    const insertInvQuery = `
      INSERT INTO accounts_invoices 
      (invoice_id, order_id, cus_id, customer_name, invoice_date, due_date, subtotal, discount, gst_percent, gst_amount, net_amount, paid_amount, balance, status, items)
      VALUES ($1, $2, $3, $4, CURRENT_DATE, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)
      RETURNING *
    `;

    await pool.query(insertInvQuery, [
      invoiceId,
      orderId,
      customerId,
      customerName,
      dueDate || null,
      subtotal,
      discount,
      gstPercent,
      gstAmount,
      netAmount,
      paidAmount,
      balance,
      status,
      JSON.stringify(items || [])
    ]);

    if (paidAmount > 0) {
      await pool.query(
        `INSERT INTO accounts_payments (invoice_id, payment_date, amount, payment_mode, reference_no)
         VALUES ($1, CURRENT_DATE, $2, $3, $4)`,
        [invoiceId, paidAmount, paymentMode || "Cash", paymentRef || "Initial Collection"]
      );
    }

    res.status(201).json({ message: "Invoice generated successfully", invoiceId });
  } catch (err) {
    console.error("Accounts create invoice error:", err);
    res.status(500).json({ error: "Failed to create invoice" });
  }
});

// 6. Record Payment for Invoice
app.post("/api/accounts/invoices/:invoiceId/payments", async (req, res) => {
  const { invoiceId } = req.params;
  const { amount, paymentMode, referenceNo } = req.body;

  try {
    const payAmt = parseFloat(amount);
    if (isNaN(payAmt) || payAmt <= 0) {
      return res.status(400).json({ error: "Invalid payment amount" });
    }

    const invRes = await pool.query("SELECT * FROM accounts_invoices WHERE invoice_id = $1", [invoiceId]);
    if (invRes.rows.length === 0) {
      return res.status(404).json({ error: "Invoice not found" });
    }

    const inv = invRes.rows[0];
    const newPaid = parseFloat(inv.paid_amount) + payAmt;
    const newBal = parseFloat(inv.net_amount) - newPaid;
    let newStatus = "Partially Paid";
    if (newBal <= 0) newStatus = "Paid";

    await pool.query("BEGIN");

    await pool.query(
      `INSERT INTO accounts_payments (invoice_id, payment_date, amount, payment_mode, reference_no)
       VALUES ($1, CURRENT_DATE, $2, $3, $4)`,
      [invoiceId, payAmt, paymentMode || "Bank Transfer", referenceNo || "N/A"]
    );

    await pool.query(
      `UPDATE accounts_invoices 
       SET paid_amount = $1, balance = $2, status = $3 
       WHERE invoice_id = $4`,
      [newPaid, Math.max(0, newBal), newStatus, invoiceId]
    );

    await pool.query("COMMIT");

    res.json({ message: "Payment recorded successfully", invoiceId, paidAmount: newPaid, balance: Math.max(0, newBal), status: newStatus });
  } catch (err) {
    await pool.query("ROLLBACK");
    console.error("Accounts record payment error:", err);
    res.status(500).json({ error: "Failed to record payment" });
  }
});


app.listen(port, () => {
  console.log(`Server running on http://localhost:${port}`);
});
