const pool = require('./database');
async function run() {
  const client = await pool.connect();
  try {
    // Check existing records
    const check = await client.query(
      "SELECT sub_order_id, order_id, dc_challan_no, design_style, print_type, design_id, ground, roll_length, inward_weight FROM sub_order WHERE dc_challan_no IN ('6679','6680')"
    );
    console.log('Existing records:', JSON.stringify(check.rows, null, 2));

    if (check.rows.length > 0) {
      // Update roll_length to 1 for both
      const upd = await client.query(
        "UPDATE sub_order SET roll_length = '1' WHERE dc_challan_no IN ('6679','6680') RETURNING sub_order_id, dc_challan_no, roll_length"
      );
      console.log('Updated roll_length to 1 for:', upd.rows.map(r => r.dc_challan_no));
    } else {
      console.log('Records not found — inserting now...');

      // Find the order for AKR Industries on 2026-01-02
      const orderRes = await client.query(
        "SELECT o.order_id FROM order_master o JOIN customer_master c ON o.cus_id = c.cus_id WHERE LOWER(c.customer_name) LIKE '%akr%' AND DATE(o.order_date) = '2026-01-02' LIMIT 1"
      );

      if (orderRes.rows.length === 0) {
        console.log('ERROR: No AKR order found for 2026-01-02');
        return;
      }

      const order_id = orderRes.rows[0].order_id;
      console.log('Using order_id:', order_id);

      const rows = [
        { dc_no: '6679', dc_date: '2025-12-31', style: 'MICKEY',      print_type: 'Discharge', design_no: 'C/1740/4', ground: 'pinkesque terry (open)',    dia: null, roll: '1', qty: '11.900', sub_order_id: 'SO-FIX-6679' },
        { dc_no: '6680', dc_date: '2025-12-31', style: 'MICKEY-PINK', print_type: 'pigment',   design_no: 'C/1741/4', ground: 'white crossloopknit(open)', dia: null, roll: '1', qty: '11.300', sub_order_id: 'SO-FIX-6680' },
      ];

      for (const r of rows) {
        await client.query(
          `INSERT INTO sub_order (sub_order_id, order_id, dc_challan_no, dc_date, design_style, print_type, design_id, ground, diameter, roll_length, inward_weight, suborder_date, suborder_dept_flow_status)
           VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,'2026-01-02','todo') ON CONFLICT (sub_order_id) DO NOTHING`,
          [r.sub_order_id, order_id, r.dc_no, r.dc_date, r.style, r.print_type, r.design_no, r.ground, r.dia, r.roll, r.qty]
        );
        console.log('Inserted:', r.dc_no, r.style);
      }
    }

    console.log('\nDone!');
  } catch (err) {
    console.error('Error:', err.message);
  } finally {
    client.release();
    await pool.end();
  }
}
run();
