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

async function testApiAllOrdersList() {
  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,
        COALESCE(s.actual_delivery_date, om.actual_delivery_date) as actual_delivery_date,
        COALESCE(s.suborder_dept_flow_status, s.dept_status, om.order_status, 'pending') 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
      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, om.order_status, 'pending')) NOT IN ('delivered', 'done', 'completed')
      ORDER BY COALESCE(s.suborder_date, om.order_date) DESC NULLS LAST, s.sub_order_id DESC
    `);
    console.log("UNDELIVERED ORDERS COUNT:", result.rows.length);
    console.log("FIRST 5 ROWS:", result.rows.slice(0, 5));
  } catch (err) {
    console.error(err);
  } finally {
    await pool.end();
  }
}

testApiAllOrdersList();
