doormilxpress_astryx/scripts/makeLogisticsTestSheet.cjs

/**
 * Build the logistics bulk-upload test sheet.
 *
 *   node scripts/makeLogisticsTestSheet.cjs
 *
 * The tenant's own export (tests/fixtures/jayanthi-kitchen-route.xls) is 13
 * lunch drops inside Coimbatore. Every one of them is hyperlocal, so uploading
 * it exercises none of the base-handover path — no parcel in that file is ever
 * routed through a base, and `next_action` never leaves `deliver`.
 *
 * This sheet keeps that file's exact column titles, so it travels through the
 * same header map and the same page code, and changes only the one thing that
 * decides routing: WHERE THE PARCEL IS GOING.
 *
 * The rule it is built against (backend, isHyperlocalBooking):
 *
 *   • both pincodes present  → same first 3 digits is hyperlocal, different is
 *                              routed through a base. The prefix decides
 *                              outright; distance does not get a vote.
 *   • a pincode missing      → fall back to straight-line distance, ≤30 km is
 *                              hyperlocal.
 *   • neither resolvable     → routed through a base, which is the safe answer.
 *
 * Two columns are added that the tenant's file does not carry — Receiver
 * Pincode and Receiver City. Both are already in SHEET_HEADER_MAP
 * (receiverpincode → postcode, receivercity → city), so this is not a new
 * format: it is the same format with columns the map already knows filled in.
 * Without a pincode every row would land on the distance fallback, and the
 * prefix rule — the one that actually decides intercity — would never run.
 */
const fs = require('node:fs');
const path = require('node:path');
const XLSX = require('xlsx');

const SENDER = {
  name: 'Jayanthi',
  phone: 7708733769,
  address: 'Edayarpalayam, Coimbatore, Tamil Nadu 641025'
};

const PICKUP_DATE = 'Sep 02, 2026';

/**
 * expect: what the backend should decide for the row, and why. Not a column —
 * it is carried here so the fixture and the test assertions cannot drift, and
 * it is written into a "Notes" column on the sheet so an operator running this
 * by hand can see what they are meant to be looking at.
 */
const ROWS = [
  // ---- Customer → Base. Different postal area, so the rider collects and
  //      carries it to a base instead of to the receiver. This is the whole
  //      point of the file.
  {
    name: 'Suresh Kumar', phone: 9840112233, alt: 9840112234,
    address: 'Flat 3B, 21 NSC Bose Road, Parrys, Chennai', city: 'Chennai', pincode: '600001',
    lat: 13.091, lng: 80.285, item: 'Documents envelope', qty: 1, cash: 0,
    expect: 'base handover — Coimbatore 641 to Chennai 600'
  },
  {
    name: 'Priya Raghavan', phone: 9840223344, alt: 9840223345,
    address: '14 Cheran Street, Raja Annamalai Puram, Chennai', city: 'Chennai', pincode: '600028',
    lat: 13.018, lng: 80.256, item: 'Spare parts carton', qty: 2, cash: 0,
    expect: 'base handover — Coimbatore 641 to Chennai 600'
  },
  {
    name: 'Anil Reddy', phone: 9848334455, alt: 9848334456,
    address: 'Plot 42, Gachibowli, Hyderabad', city: 'Hyderabad', pincode: '500081',
    lat: 17.44, lng: 78.3489, item: 'Textile samples', qty: 3, cash: 0,
    expect: 'base handover — interstate, Coimbatore to Hyderabad'
  },
  {
    name: 'Meera Krishnan', phone: 9848445566, alt: 9848445567,
    address: 'Tower B, Financial District, Nanakramguda, Hyderabad', city: 'Hyderabad', pincode: '500032',
    lat: 17.4156, lng: 78.3378, item: 'Legal documents', qty: 1, cash: 450,
    expect: 'base handover with COD — cash must survive the base leg'
  },
  {
    name: 'Rahul Menon', phone: 9880556677, alt: 9880556678,
    address: '9 Brigade Road, MG Road, Bengaluru', city: 'Bengaluru', pincode: '560001',
    lat: 12.975, lng: 77.606, item: 'Electronics accessories', qty: 2, cash: 0,
    expect: 'base handover — interstate, Coimbatore to Bengaluru'
  },
  {
    name: 'Divya Nair', phone: 9880667788, alt: 9880667789,
    address: 'Prestige Shantiniketan, Whitefield, Bengaluru', city: 'Bengaluru', pincode: '560066',
    lat: 12.9698, lng: 77.75, item: 'Gift hamper', qty: 1, cash: 1200,
    expect: 'base handover with COD'
  },
  {
    name: 'Karthik Subramani', phone: 9843778899, alt: 9843778890,
    address: '5 East Veli Street, Madurai', city: 'Madurai', pincode: '625001',
    lat: 9.9195, lng: 78.119, item: 'Temple prasadam box', qty: 1, cash: 0,
    expect: 'base handover — same state, different postal area still routes via a base'
  },
  {
    name: 'Lakshmi Devi', phone: 9842889900, alt: 9842889901,
    address: '18 Cherry Road, Salem', city: 'Salem', pincode: '636001',
    lat: 11.664, lng: 78.146, item: 'Handloom bundle', qty: 2, cash: 0,
    expect: 'base handover — same state, different postal area'
  },

  // ---- Customer → Customer. Same postal area, so the collecting rider carries
  //      it straight to the receiver and no base is involved. These rows are
  //      the control group: if one of them routes to a base, the prefix rule
  //      has broken.
  {
    name: 'Ganesh Iyer', phone: 9994001122, alt: 9994001123,
    address: '22 Avinashi Road, Peelamedu, Coimbatore', city: 'Coimbatore', pincode: '641004',
    lat: 11.029, lng: 76.993, item: 'Lunch box', qty: 1, cash: 0,
    expect: 'direct to customer — same 641 area'
  },
  {
    name: 'Revathi Balaji', phone: 9994112233, alt: 9994112234,
    address: '7 Cross Cut Road, Gandhipuram, Coimbatore', city: 'Coimbatore', pincode: '641012',
    lat: 11.018, lng: 76.966, item: 'Lunch box', qty: 2, cash: 260,
    expect: 'direct to customer with COD — same 641 area'
  },
  {
    name: 'Vignesh Murugan', phone: 9994223344, alt: 9994223345,
    address: '3/74 Nethaji Street, Edayarpalayam, Coimbatore', city: 'Coimbatore', pincode: '641025',
    lat: 11.008, lng: 76.928, item: 'Lunch box', qty: 1, cash: 0,
    expect: 'direct to customer — receiver shares the sender pincode'
  },
  {
    name: 'Anitha Selvam', phone: 9994334455, alt: 9994334456,
    address: '11 DB Road, Saibaba Colony, Coimbatore', city: 'Coimbatore', pincode: '641038',
    lat: 11.023, lng: 76.945, item: 'Bakery order', qty: 3, cash: 0,
    expect: 'direct to customer — same 641 area'
  },

  // ---- The fallback rule. A pincode is missing, so the decision falls to the
  //      straight-line distance instead. Both outcomes are represented, because
  //      a fallback that only ever answers one way is not being tested.
  {
    name: 'Mohan Das', phone: 9995445566, alt: 9995445567,
    address: 'Near Hope College signal, Coimbatore', city: 'Coimbatore', pincode: '',
    lat: 11.05, lng: 77.01, item: 'Stationery pack', qty: 1, cash: 0,
    expect: 'direct to customer — no pincode, but ~8 km falls inside the 30 km fallback'
  },
  {
    name: 'Sridhar Venkat', phone: 9995556677, alt: 9995556678,
    address: 'Opposite Central Station, Chennai', city: 'Chennai', pincode: '',
    lat: 13.0827, lng: 80.2707, item: 'Machine component', qty: 1, cash: 0,
    expect: 'base handover — no pincode, but ~430 km is far outside the fallback'
  },
  {
    name: 'Bhavani Shankar', phone: 9995667788, alt: 9995667789,
    address: 'T Nagar, Chennai', city: 'Chennai', pincode: '64',
    lat: 13.06, lng: 80.24, item: 'Fabric roll', qty: 1, cash: 0,
    expect: 'base handover — a 2-digit pincode is unusable, so distance decides'
  },

  // ---- The case that proves the prefix rule outranks distance. Pollachi is
  //      ~40 km from Coimbatore — closer than plenty of runs the console treats
  //      as local — but 642 is a different postal area, so it goes via a base.
  {
    name: 'Ramesh Palanisamy', phone: 9995778899, alt: 9995778890,
    address: '2 Mahalingapuram Road, Pollachi', city: 'Pollachi', pincode: '642001',
    lat: 10.658, lng: 77.008, item: 'Agricultural samples', qty: 2, cash: 0,
    expect: 'base handover — nearby, but 642 is a different area and the prefix decides'
  }
];

const HEADERS = [
  'Pickup Date (YYYY-MMM-DD )',
  'Sender Name*',
  'Sender Phone*',
  'Sender Address*',
  'Receiver Name*',
  'Receiver Phone',
  'Receiver Alternate Phone*',
  'Receiver Full Address',
  'Receiver City',
  'Receiver Pincode',
  'Receiver Latitude',
  'Receiver Longitude',
  'Item Description*',
  'Quantity',
  'Collect Cash',
  'Expected Routing (not read by the app)'
];

const sheetRows = ROWS.map((r) => ({
  'Pickup Date (YYYY-MMM-DD )': PICKUP_DATE,
  'Sender Name*': SENDER.name,
  'Sender Phone*': SENDER.phone,
  'Sender Address*': SENDER.address,
  'Receiver Name*': r.name,
  'Receiver Phone': r.phone,
  'Receiver Alternate Phone*': r.alt,
  'Receiver Full Address': r.address,
  'Receiver City': r.city,
  'Receiver Pincode': r.pincode,
  'Receiver Latitude': r.lat,
  'Receiver Longitude': r.lng,
  'Item Description*': r.item,
  Quantity: r.qty,
  'Collect Cash': r.cash,
  'Expected Routing (not read by the app)': r.expect
}));

const ws = XLSX.utils.json_to_sheet(sheetRows, { header: HEADERS });
ws['!cols'] = HEADERS.map((h) => ({ wch: Math.min(46, Math.max(14, h.length + 2)) }));

const wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws, 'Doormile_Logistics_Test');

const targets = process.argv.slice(2);
if (targets.length === 0) targets.push(path.join(__dirname, '..', 'tests', 'fixtures', 'doormile-logistics-test.xlsx'));

targets.forEach((out) => {
  fs.mkdirSync(path.dirname(out), { recursive: true });
  XLSX.writeFile(wb, out);
  console.log(`wrote ${sheetRows.length} rows → ${out}`);
});