/**
 * Demo Data Import Script
 * Run with: node import_demo_data.js
 * 
 * Inserts customers, orders and suborders from the spreadsheet into the database.
 * Uses ON CONFLICT DO NOTHING so it's safe to run multiple times.
 */

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

// ─── Spreadsheet Data ─────────────────────────────────────────────────────────
// Columns: date | customer_name | dc_no | dc_date | style | print_type | design_no | ground | dia | roll | qty

const rows = [
  { date: '2026-01-02', customer: 'Create Knit Fashion',          dc_no: '190',  dc_date: '2025-12-31', style: 'LEMONE',                          print_type: 'pigment',    design_no: 'C/1556/2', ground: 'rfd l/j',                    dia: null, roll: null, qty: '27.100'   },
  { date: '2026-01-02', customer: 'AKR Industries (P)',            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: null, qty: '11.900'   },
  { date: '2026-01-02', customer: 'AKR Industries (P)',            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: null, qty: '11.300'   },
  { date: '2026-01-02', customer: 'Star Knit Wear',               dc_no: '141',  dc_date: '2026-01-02', style: '',                                print_type: 'pigment',    design_no: '',         ground: 'mixed fabric',              dia: null, roll: null, qty: '1406.000'  },
  { date: '2026-01-02', customer: 'Rhythm Knit India (Unit 2)',    dc_no: '396',  dc_date: '2026-01-02', style: 'RK/25-26/CDL/E/43-DEAR-TREE',    print_type: 'pigment',    design_no: 'C/1436/5', ground: 'white s/j',                 dia: '32', roll: '17', qty: '779.800'  },
  { date: '2026-01-02', customer: 'Rhythm Knit India (Unit 2)',    dc_no: '3824', dc_date: '2026-01-02', style: 'RK/25-26/PON/B/15-BIG HEART',    print_type: 'FL+PIG',     design_no: 'C/1522/1', ground: 'white s/j',                 dia: '30', roll: '14', qty: '328.800'  },
  { date: '2026-01-02', customer: 'Rhythm Knit India (Unit 2)',    dc_no: '3813', dc_date: '2026-01-02', style: 'RK/25-26/CDL/E/43-ADV-BLACK',   print_type: 'REFRACTIVE', design_no: 'C/8969/5', ground: 'white s/j',                 dia: '16', roll: '11', qty: '214.000'  },
  { date: '2026-01-03', customer: 'Revanth Impex',                dc_no: '2146', dc_date: '2026-01-02', style: 'CAMO-BLUE',                      print_type: 'pigment',    design_no: 'C/1778/4', ground: 'white s/j',                 dia: null, roll: null, qty: '38.200'   },
  { date: '2026-01-03', customer: 'Aasfeervatu Textile',          dc_no: '8',    dc_date: '2026-01-02', style: '',                                print_type: '',           design_no: '',         ground: 'RFD DOUBLE CLOTH-GADA',     dia: '78', roll: null, qty: '5185.000'  },
  { date: '2026-01-03', customer: 'Rhythm Knit India (Unit 2)',    dc_no: '3809', dc_date: '2026-01-01', style: 'RK/25-26/CDL/S/83',             print_type: 'pigment',    design_no: 'C/1760/5', ground: 'CREAM S/J',                 dia: '30', roll: '1',  qty: '14.400'   },
  { date: '2026-01-05', customer: 'Laxmi Garments',               dc_no: '2058', dc_date: '2026-01-03', style: 'sand-dl',                        print_type: 'pigment',    design_no: 'B/8072/5', ground: 'white I/I',                 dia: '28', roll: '9',  qty: '168.000'  },
  { date: '2026-01-05', customer: 'Laxmi Garments',               dc_no: '2059', dc_date: '2026-01-03', style: 'honey bee',                      print_type: 'pigment',    design_no: 'B/9779/3', ground: 'white I/I',                 dia: '28', roll: '9',  qty: '168.000'  },
  { date: '2026-01-05', customer: 'Laxmi Garments',               dc_no: '2060', dc_date: '2026-01-03', style: 'yellow-ice cream',               print_type: 'pigment',    design_no: 'B/8503/5', ground: 'white I/I',                 dia: '28', roll: '9',  qty: '169.200'  },
];

// ─── Helpers ──────────────────────────────────────────────────────────────────
function n(v) { return v === '' || v === undefined || v === null ? null : v; }

function makeOrderId(prefix, num) {
  return `ORD-DEMO-${String(num).padStart(4, '0')}`;
}
function makeSubOrderId(prefix, num) {
  return `SO-DEMO-${String(num).padStart(4, '0')}`;
}

// ─── Main ─────────────────────────────────────────────────────────────────────
async function run() {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');

    // 1. Upsert unique customers
    const uniqueCustomers = [...new Set(rows.map(r => r.customer))];
    const customerIdMap = {};

    for (const name of uniqueCustomers) {
      // Check if customer already exists by name
      const existing = await client.query(
        `SELECT cus_id FROM customer_master WHERE LOWER(customer_name) = LOWER($1) LIMIT 1`,
        [name]
      );
      if (existing.rows.length > 0) {
        customerIdMap[name] = existing.rows[0].cus_id;
        console.log(`  ✔ Customer exists: "${name}" → ${existing.rows[0].cus_id}`);
      } else {
        // Generate next customer ID
        const seqRes = await client.query(`SELECT COALESCE(MAX(CAST(REGEXP_REPLACE(cus_id, '[^0-9]', '', 'g') AS INTEGER)), 0) + 1 AS next_num FROM customer_master WHERE cus_id ~ '^C-[0-9]+'`);
        const nextNum = seqRes.rows[0].next_num;
        const newCusId = `C-${String(nextNum).padStart(4, '0')}`;

        await client.query(
          `INSERT INTO customer_master (cus_id, customer_name, mobile1) VALUES ($1, $2, $3) ON CONFLICT (cus_id) DO NOTHING`,
          [newCusId, name, `DEMO-${String(nextNum).padStart(6, '0')}`]
        );
        customerIdMap[name] = newCusId;
        console.log(`  ✚ Customer inserted: "${name}" → ${newCusId}`);
      }
    }

    // 2. Group rows into orders (one order per date+customer pair)
    const orderGroups = {};
    for (const row of rows) {
      const key = `${row.date}__${row.customer}`;
      if (!orderGroups[key]) orderGroups[key] = { date: row.date, customer: row.customer, subrows: [] };
      orderGroups[key].subrows.push(row);
    }

    // 3. Insert orders and suborders
    let orderSeq  = 900; // Start demo order IDs from 900 to avoid conflicts
    let subSeq    = 900;

    for (const key of Object.keys(orderGroups)) {
      const group     = orderGroups[key];
      const cus_id    = customerIdMap[group.customer];
      const order_id  = makeOrderId('ORD', ++orderSeq);

      // Check if this demo order already exists
      const existingOrder = await client.query(
        `SELECT order_id FROM order_master WHERE order_id = $1`,
        [order_id]
      );

      if (existingOrder.rows.length === 0) {
        await client.query(
          `INSERT INTO order_master (order_id, cus_id, order_date, order_status)
           VALUES ($1, $2, $3, 'todo')
           ON CONFLICT (order_id) DO NOTHING`,
          [order_id, cus_id, group.date]
        );
        console.log(`  ✚ Order inserted: ${order_id} (${group.customer} | ${group.date})`);
      } else {
        console.log(`  ✔ Order exists: ${order_id}`);
      }

      // 4. Insert suborders for this order
      for (const row of group.subrows) {
        const sub_order_id = makeSubOrderId('SO', ++subSeq);

        // Check if suborder with same dc_challan_no already exists under this order
        const existingSO = await client.query(
          `SELECT sub_order_id FROM sub_order WHERE order_id = $1 AND dc_challan_no = $2 LIMIT 1`,
          [order_id, row.dc_no]
        );

        if (existingSO.rows.length > 0) {
          console.log(`    ✔ Suborder exists (order=${order_id}, dc_no=${row.dc_no})`);
          continue;
        }

        await client.query(
          `INSERT INTO sub_order (
            sub_order_id, order_id, customer_name,
            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,$12,$13,'todo')
          ON CONFLICT (sub_order_id) DO NOTHING`,
          [
            sub_order_id,
            order_id,
            group.customer,
            n(row.dc_no),
            n(row.dc_date),
            n(row.style),
            n(row.print_type),
            n(row.design_no),
            n(row.ground),
            n(row.dia),
            n(row.roll),
            n(row.qty),
            row.date
          ]
        );
        console.log(`    ✚ Suborder inserted: ${sub_order_id} (dc_no=${row.dc_no}, style=${row.style || '-'})`);
      }
    }

    await client.query('COMMIT');
    console.log('\n✅ Demo data imported successfully!');
  } catch (err) {
    await client.query('ROLLBACK');
    console.error('\n❌ Import failed — rolled back:', err.message);
    console.error(err);
  } finally {
    client.release();
    await pool.end();
  }
}

run();
