const pool = require("./database");

async function testExactServerQuery() {
  try {
    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, '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
      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
      WHERE LOWER(COALESCE(s.suborder_dept_flow_status, s.dept_status, 'todo')) NOT IN ('delivered', 'done', 'completed')
        AND s.actual_delivery_date IS NULL
      ORDER BY COALESCE(s.suborder_date, om.order_date) DESC NULLS LAST, s.sub_order_id DESC
    `);
    console.log("EXACT SERVER QUERY COUNT:", result.rows.length);
    if (result.rows.length > 0) {
      console.log("FIRST 3 ROWS:", result.rows.slice(0, 3));
    }
  } catch (err) {
    console.error("DB ERROR:", err);
  } finally {
    await pool.end();
  }
}

testExactServerQuery();
