`scripts/collect-amazon-orders.js`

74 lines
// One-off: collect parsed Amazon order confirmations (from info@ via George) into
// AbramsOS purchase. Honest: total_amount stays NULL (not in metadata); dedup on order#.

const db = require('../lib/db');
const { id } = require('../lib/ids');
const USER = 'user_steve';

// [internalDate ms, raw subject, order# or null, gmail msg id]
const O = [
 [1783713200000,'Ordered: "Nespresso Capsules Vertuo,..." and 1 more item',null,'19f4d977b22ca555'],
 [1782836677000,'Ordered: "Plantronics Voyager Focus..."',null,'19f1958caa9e4860'],
 [1782336230000,'Ordered: "Plantronics CS520 Binaural..." and 6 more items',null,'19efb8497c2a7049'],
 [1777400488000,'Your Amazon.com order of "4 Rolls 450 Labels 4x6...".',null,'19dd55322cfe38b3'],
 [1777400271000,'Your Amazon.com order of "Nespresso Capsules Vertuo..." and 2 more items.',null,'19dd54c45ae813be'],
 [1777062036000,'Your Amazon.com order of "JCLEAL Pool Float Lounger..." and 8 more items.',null,'19dc126557900b67'],
 [1777061709000,'Your Amazon.com order of "GoldFin Mens Wetsuit Top..." and 3 more items.',null,'19dc12188ee4a51d'],
 [1776976244000,'Your Amazon.com order of "GoldFin Mens Wetsuit Top..." and 3 more items.',null,'19dbc09b1ed10be5'],
 [1776899063000,'Your Amazon.com order of "2x JCLEAL Pool Float Lounger..." and 7 more items.',null,'19db76ffed03007e'],
 [1776898645000,'Your Amazon.com order of "2x Upholstered Stylish Pool..." and 7 more items.',null,'19db769a0272155c'],
 [1776897636000,'Your Amazon.com order of "2x Upholstered Stylish Pool..." and 15 more items.',null,'19db75a3903871ff'],
 [1774988145000,'Your Amazon.com order of "TN-730 TN730 MFC-L2710DW...".',null,'19d4589b6defcddd'],
 [1773162522000,'Your Amazon.com order of "Nespresso Capsules Vertuo..." and 1 more item.',null,'19cd8b8e92e71c2c'],
 [1772650098000,'Ordered: "Nespresso Capsules Vertuo,..." and 4 more items',null,'19cba2dffd291135'],
 [1772647844000,'Your Amazon.com order of "Samsung T9 Portable SSD...".',null,'19cba0b8cd428a30'],
 [1769552625000,'Ordered: "4 Rolls 450 Labels 4x6..."',null,'19c018e4fd40c4d8'],
 [1769121939000,'Your Amazon Fresh Receipt',null,'19be7e280c331bc3'],
 [1742344709000,'Your Amazon.com order of "CXEZSIK Garden Hose..." and 10 more items.',null,'195abd669c697871'],
 [1734645608000,'Your Amazon.com order of "Premium Super Trimmed..." and 3 more items.',null,'193e0ef72f669674'],
 [1726445765000,'Your Amazon.com order of "Hoover PowerScrub XL Pet..." and 10 more items.',null,'191f82fc78a61c00'],
 [1713892191000,'Your Amazon.com order of "FANHAO Upgrade Garden Hose...".',null,'18f0bef70eeaa68f'],
 [1713390964000,'Your Amazon.com order',null,'18eee0f50ca80373'],
 [1710480506000,'Your Amazon.com order of "SpaGuard Brominating..." and 19 more items.',null,'18e409540ead49bd'],
 [1704324428000,'Your Amazon.com order of "Nespresso Capsules...".','112-4338583-8108250','18cd1a6f5d4a3c31'],
 [1702930000000,'Your Amazon.com order of "Abbott Collection Ceramic...".','114-2436527-3315420','18c7e89abb40e864'],
 [1702922319000,'Your Amazon.com order of "4 Ramen Bowls, 12 pieces..." and 3 more items.','113-0020876-1931461','18c7e147a2b9b31b'],
 [1702744238000,'Your Amazon.com order of "Inside the White House..." and 9 more items.','113-9055697-2465069','18c73772b79592d1'],
 [1702581106000,'Your Amazon.com order of "7x Amazon.com Gift Card...".','114-3273948-2369002','18c69bdfd8aeeddd'],
 [1701973028000,'Your Amazon.com order of "International Delight...".','114-2702963-4247441','18c457f715c85af9'],
 [1696091563000,'Your Amazon.com order of "4x Maume Dog Bell for Door...".','114-3775698-0042618','18ae6ef666311007'],
 [1694974687000,'Your Amazon.com order of "USB C to HDMI Adapter...".','114-6961835-5017059','18aa45d3af3bf82a'],
 [1694035211000,'Your Amazon.com order of "Board2by 2-Pack Cork Board..." and 8 more items.','112-5833883-4990623','18a6c5df6429a906'],
 [1692670754000,'Your Amazon.com order of "Led Desk Lamp for Office..." and 5 more items.','112-6653619-1561035','18a1b0a02fb0ecee'],
 [1666330044000,'Your Amazon.com order of "Ring Stick Up Cam Battery..." and 13 more items.',null,'183f902a907018e3'],
 [1655832050000,'Your Amazon.com order of "King Kooker 5500 Stainless..." and 5 more items.','112-8815900-8787430','1818747ff88f1478'],
 [1654800585000,'Your Amazon.com order of "Ksunun Double Head LED Desk..." and 5 more items.','113-9761492-5829028','18149cd144c02ac8'],
 [1654530601000,'Your Amazon.com order of "John Timberland Three Tier..." and 9 more items.',null,'18139b57277ef2c0'],
 [1650824986000,'Your Amazon.com order of "Harssidanzar Women\'s..." and 15 more items.','112-7447440-9868261','1805cd642b1ddf64'],
];

function cleanItem(subj) {
  let s = subj.replace(/^Ordered:\s*/i, '').replace(/^Your Amazon\.com order of\s*/i, '').replace(/^Your\s+/i, '');
  s = s.replace(/^"|"\.?$/g, '').replace(/\.\.\.?"?\.?$/, '').replace(/"/g, '').trim();
  return s || 'Amazon order';
}

(async () => {
  let inserted = 0, dupe = 0;
  for (const [ms, subj, oid, mid] of O) {
    const item = cleanItem(subj);
    const orderNo = oid ? `amzn:${oid}` : `amzn-msg:${mid}`;
    const raw = { source: 'george-info-amazon', subject: subj, item, order_id: oid, gmail_id: mid };
    const ins = await db.query(
      `INSERT INTO purchase (id, user_id, source_message_id, merchant_name, merchant_domain, order_number, purchase_date, total_amount, currency, confidence, raw_extract)
       VALUES ($1,$2,NULL,'Amazon','amazon.com',$3,$4,NULL,'USD',0.9,$5)
       ON CONFLICT (user_id, order_number) WHERE order_number IS NOT NULL DO NOTHING
       RETURNING id`,
      [id('purchase'), USER, orderNo, new Date(ms), raw]
    );
    if (ins.rows.length) inserted++; else dupe++;
  }
  console.log(`inserted=${inserted} dupe=${dupe} of ${O.length}`);
  process.exit(0);
})().catch(e => { console.error('FAIL', e.message); process.exit(1); });