#!/usr/bin/env node
// scripts/migrate-msale.js — one-time migration from the old msale portal
// (MySQL u709187725_msale_db) into the ERP's Sales Order module (app DB, ZSO_*).
//
// Scope (agreed): masters + OPEN orders/samples + customer balances.
//   • product_type            → ZSO_DIVISIONS (names/codes)
//   • product + price_list     → ZSO_PRODUCTS (catalog 'domestic'); product_export → catalog 'export'
//   • dealer_master            → ZSO_CUSTOMERS (portal logins; msale MD5 passwords kept as "md5:" —
//                                upgraded to bcrypt on first login). Addresses are NOT copied:
//                                the ERP reads them live from SAP CRD1.
//   • employee_master          → ZSO_USER_PROFILES for ERP users matched by email (user type,
//                                divisions, emp code); --apply-templates also writes the user
//                                type's default Sales steps/pages onto the ERP user.
//   • sales_person_master + dealer_sales_person_mapping → ZSO_SALES_PERSONS / ZSO_SP_CUSTOMER_MAP
//   • sales_order status 1–8   → ZSO_ORDERS (same IDs, so SAP ORDR.U_WEB_SO_NO keeps matching)
//                                + items, consignees, documents (files copied), pending payments
//   • sample_request (not rejected/completed) and sample_request_export (not dispatched) → ZSO_SAMPLES / ZSO_EXPORT_SAMPLES
//   • customer_wallet.balance  → one 'opening' wallet transaction per customer
//
// Usage (run against a FRESH COPY of the LIVE database at cutover, with msale stopped):
//   node scripts/migrate-msale.js                 # dry run — reads & reports only
//   node scripts/migrate-msale.js --verify-sql    # also execute the order/sample INSERTs, then roll back
//   node scripts/migrate-msale.js --commit        # write
//   options: --apply-templates   apply user-type permission templates to matched ERP users
//            --create-users      create an ERP login (role "user") for every active msale employee
//                                with no ERP account — username = email name part; they log in
//                                with their CURRENT msale password (re-hashed at first login), and
//                                get their user type's Sales permissions/pages automatically
//            --docs-dir=<path>   msale "documents" folder to copy order attachments from
//                                (default C:\xampp80\htdocs\msale\documents)
// MySQL connection: .env MSALE_MYSQL_HOST / _PORT / _USER / _PASSWORD / _DATABASE
'use strict';
process.chdir(require('path').join(__dirname, '..'));
require('dotenv').config();
const fs = require('fs');
const path = require('path');
const mysql = require('mysql2/promise');

const ARGS = process.argv.slice(2);
const COMMIT = ARGS.includes('--commit');
const APPLY = ARGS.includes('--apply-templates');
const CREATE_USERS = ARGS.includes('--create-users');
// --verify-sql: run every order/sample INSERT for real inside a transaction, then ROLL BACK (proves the SQL against the live schema without writing).
const VERIFY = ARGS.includes('--verify-sql') && !COMMIT;
const ROLLBACK = new Error('verify-sql rollback');
const txv = async fn => { try { await tx(async r => { await fn(r); if (!COMMIT) throw ROLLBACK; }); } catch (e) { if (e !== ROLLBACK) throw e; bump('verify_sql_rolled_back'); } };
const DOCS_DIR = (ARGS.find(a => a.startsWith('--docs-dir=')) || '').slice(11) || 'C:\\xampp80\\htdocs\\msale\\documents';

const { query, one, tx } = require('../services/sales/db');
const schema = require('../services/sales/schema');
const masters = require('../services/sales/masters');
const { refNo } = require('../services/sales/orders');
const DOC_DIR = path.join(__dirname, '..', 'storage', 'sales');

const report = { warnings: [] };
const bump = (k, n = 1) => { report[k] = (report[k] || 0) + n; };
const warn = m => { report.warnings.push(m); };
const s = v => (v == null ? null : String(v));
const d = v => (v && !String(v).startsWith('0000') ? v : null);
const n = v => (v == null || v === '' ? 0 : Number(v));

async function main() {
  console.log(`\n=== msale → ERP Sales migration (${COMMIT ? 'COMMIT' : 'DRY RUN — nothing will be written'}) ===\n`);
  const my = await mysql.createConnection({
    host: process.env.MSALE_MYSQL_HOST || 'localhost', port: parseInt(process.env.MSALE_MYSQL_PORT) || 3306,
    user: process.env.MSALE_MYSQL_USER || 'root', password: process.env.MSALE_MYSQL_PASSWORD || '',
    database: process.env.MSALE_MYSQL_DATABASE || 'u709187725_msale_db', dateStrings: true,
  });
  const M = async (sql, p = []) => (await my.query(sql, p))[0];
  await require('../services/approvalStepsStore').bootstrap();
  await schema.bootstrap();
  const W = async (sql, p = [], r) => (COMMIT ? query(sql, p, r) : []);

  // ── Divisions ──
  for (const t of await M(`SELECT * FROM product_type`)) {
    await W(`MERGE dbo.ZSO_DIVISIONS AS t USING (SELECT ? AS ID) x ON t.ID=x.ID WHEN MATCHED THEN UPDATE SET NAME=?, CODE=?, ACTIVE=?
      WHEN NOT MATCHED THEN INSERT (ID,NAME,CODE,ITEM_GROUPS,WAREHOUSE,SORT,ACTIVE) VALUES (?,?,?,'','01',99,?);`,
    [t.id, t.product_type_name, t.product_type_code, !!t.status, t.id, t.product_type_name, t.product_type_code, !!t.status]);
    bump('divisions');
  }

  // ── Products & prices ──
  const prices = {};
  for (const p of await M(`SELECT item_id, unit_price FROM price_list WHERE status=1 ORDER BY id`)) prices[p.item_id] = Number(p.unit_price);
  const prodMap = {};   // legacy product id → ERP product id (domestic)
  for (const [table, catalog] of [['product', 'domestic'], ['product_export', 'export']]) {
    for (const p of await M(`SELECT * FROM ${table}`)) {
      const ex = await one(`SELECT ID FROM dbo.ZSO_PRODUCTS WHERE CATALOG=? AND LEGACY_ID=?`, [catalog, p.id]);
      const vals = [catalog, p.so_type_id, p.item_code_old || p.item_code, p.item_code, p.item_description, p.short_item_description || '',
        parseInt(p.no_of_packages) || 1, p.hsn_code || '', catalog === 'domestic' ? (prices[p.id] || 0) : 0, !!p.is_instrument, !!p.status, p.id];
      if (catalog === 'domestic' && !prices[p.id] && p.status) warn(`Product ${p.item_code} has no active price`);
      if (ex) { await W(`UPDATE dbo.ZSO_PRODUCTS SET CATALOG=?, DIVISION_ID=?, ITEM_CODE=?, DISPLAY_CODE=?, DESCRIPTION=?, SHORT_DESC=?, PACK_SIZE=?, HSN=?, UNIT_PRICE=?, IS_INSTRUMENT=?, ACTIVE=?, LEGACY_ID=?, UPDATED_AT=SYSDATETIME(), UPDATED_BY='msale migration' WHERE ID=?`, [...vals, ex.ID]); if (catalog === 'domestic') prodMap[p.id] = ex.ID; }
      else if (COMMIT) { const r = await one(`INSERT INTO dbo.ZSO_PRODUCTS (CATALOG,DIVISION_ID,ITEM_CODE,DISPLAY_CODE,DESCRIPTION,SHORT_DESC,PACK_SIZE,HSN,UNIT_PRICE,IS_INSTRUMENT,ACTIVE,LEGACY_ID,UPDATED_BY) OUTPUT INSERTED.ID VALUES (?,?,?,?,?,?,?,?,?,?,?,?,'msale migration')`, vals); if (catalog === 'domestic') prodMap[p.id] = r.ID; }
      bump(`products_${catalog}`);
    }
  }

  // ── Customers (portal logins) ──
  for (const c of await M(`SELECT * FROM dealer_master`)) {
    const canLogin = c.status == 1 && c.is_login == 1 && c.territory && c.territory !== '0';
    const ex = await one(`SELECT PASSWORD_HASH FROM dbo.ZSO_CUSTOMERS WHERE CARD_CODE=?`, [c.cardcode]);
    if (ex && ex.PASSWORD_HASH && !ex.PASSWORD_HASH.startsWith('md5:')) { bump('customers_skipped_already_active_in_erp'); continue; }
    const vals = [c.cardname, c.e_mail || '', c.password ? `md5:${String(c.password).toLowerCase()}` : null, !!canLogin, !!c.is_lock, n(c.login_attempt), d(c.last_login), n(c.credit_limit), c.id];
    if (ex) await W(`UPDATE dbo.ZSO_CUSTOMERS SET CARD_NAME=?, EMAIL=?, PASSWORD_HASH=?, ACTIVE=?, LOCKED=?, LOGIN_ATTEMPTS=?, LAST_LOGIN=?, CREDIT_LIMIT=?, LEGACY_ID=?, UPDATED_AT=SYSDATETIME(), UPDATED_BY='msale migration' WHERE CARD_CODE=?`, [...vals, c.cardcode]);
    else await W(`INSERT INTO dbo.ZSO_CUSTOMERS (CARD_NAME,EMAIL,PASSWORD_HASH,ACTIVE,LOCKED,LOGIN_ATTEMPTS,LAST_LOGIN,CREDIT_LIMIT,LEGACY_ID,CARD_CODE,UPDATED_BY) VALUES (?,?,?,?,?,?,?,?,?,?,'msale migration')`, [...vals, c.cardcode]);
    bump(canLogin ? 'customers_login_enabled' : 'customers_login_disabled');
  }

  // ── User types (names only — permission templates stay as seeded/edited) ──
  for (const t of await M(`SELECT * FROM user_type`)) {
    await W(`UPDATE dbo.ZSO_USER_TYPES SET NAME=? WHERE ID=?`, [t.user_type_name, t.id]);
  }

  // ── Employees → ERP users (matched by email) ──
  const erpUsers = await query(`SELECT ID, USERNAME, EMAIL FROM dbo.ZCUST_USERS`);
  const byEmail = new Map(erpUsers.filter(u => u.EMAIL).map(u => [String(u.EMAIL).trim().toLowerCase(), u]));
  const unmatched = [], created = [], newEmails = new Set();
  const divisionIds = (await query(`SELECT ID FROM dbo.ZSO_DIVISIONS`)).map(r => r.ID);
  const usernames = new Set(erpUsers.map(u => String(u.USERNAME).toLowerCase()));
  for (const e of await M(`SELECT * FROM employee_master WHERE user_status=1`)) {
    const email = String(e.email_id || '').trim().toLowerCase();
    let u = byEmail.get(email);
    if (!u && CREATE_USERS && email.includes('@')) {
      // New ERP login for this employee, keeping their msale password
      // (stored "md5:" — re-hashed to bcrypt at their first ERP login).
      let un = email.split('@')[0].replace(/[^a-z0-9._-]/g, '') || `emp${e.id}`; const baseUn = un; let k = 1;
      while (usernames.has(un)) un = `${baseUn}${++k}`;
      usernames.add(un);
      if (COMMIT) {
        const hu = require('../services/hanaUsers');
        const id = await hu.createUser({ username: un, password: require('crypto').randomBytes(18).toString('hex'), fullName: e.name, email: e.email_id.trim(), role: 'user', modules: [], approvalSteps: [] });
        if (e.password) await query(`UPDATE dbo.ZCUST_USERS SET PASSWORD=? WHERE ID=?`, [`md5:${String(e.password).toLowerCase()}`, id]);
        u = { ID: id, USERNAME: un, EMAIL: e.email_id };
        byEmail.set(email, u);
      } else u = { ID: 0, USERNAME: un };
      created.push(`${e.name} → username "${un}"`);
      newEmails.add(email);
    }
    if (!u) { unmatched.push(`${e.name} <${e.email_id}> (type ${e.user_type})`); continue; }
    const isNewUser = newEmails.has(email);
    const divs = String(e.permission || '').split(',').map(Number).filter(x => divisionIds.includes(x));
    if (COMMIT) await masters.saveProfile({ userId: u.ID, userTypeId: parseInt(e.user_type), divisions: divs, empCode: e.emp_code || '', territory: e.territory || '', ccEmails: e.cc_mark_email_id || '', applyTemplate: APPLY || isNewUser }, 'msale migration');
    if (COMMIT) await query(`UPDATE dbo.ZSO_USER_PROFILES SET LEGACY_EMP_ID=? WHERE USER_ID=?`, [e.id, u.ID]);
    bump('employees_matched');
  }
  report.employees_unmatched = unmatched;
  report.erp_users_created = created;

  // ── Sales persons & mapping ──
  const spMap = {};
  for (const p of await M(`SELECT * FROM sales_person_master`)) {
    const u = byEmail.get(String(p.email_id || '').trim().toLowerCase());
    const ex = await one(`SELECT ID FROM dbo.ZSO_SALES_PERSONS WHERE LEGACY_ID=?`, [p.id]);
    if (ex) { spMap[p.id] = ex.ID; await W(`UPDATE dbo.ZSO_SALES_PERSONS SET NAME=?, EMAIL=?, DESIGNATION=?, DIVISIONS=?, USER_ID=?, ACTIVE=? WHERE ID=?`, [p.name, p.email_id, p.designation, p.devision, u ? u.ID : null, !!p.status, ex.ID]); }
    else if (COMMIT) { const r = await one(`INSERT INTO dbo.ZSO_SALES_PERSONS (NAME,EMAIL,DESIGNATION,DIVISIONS,USER_ID,ACTIVE,LEGACY_ID) OUTPUT INSERTED.ID VALUES (?,?,?,?,?,?,?)`, [p.name, p.email_id, p.designation, p.devision, u ? u.ID : null, !!p.status, p.id]); spMap[p.id] = r.ID; }
    bump('sales_persons');
  }
  for (const m of await M(`SELECT * FROM dealer_sales_person_mapping WHERE status=1`)) {
    if (COMMIT && spMap[m.sales_person_id]) await masters.mapCustomers(spMap[m.sales_person_id], [m.card_code], 'msale migration');
    bump('customer_mappings');
  }

  // ── Open orders (same IDs) ──
  const states = {};
  for (const a of await M(`SELECT id, state FROM dealer_address`)) states[a.id] = a.state;
  for (const odd of await M(`SELECT id, order_ref_no, status FROM sales_order WHERE status NOT BETWEEN 1 AND 11`)) warn(`Order ${odd.id} (${odd.order_ref_no}) has invalid msale status ${odd.status} — not migrated, check it manually`);
  const openOrders = await M(`SELECT * FROM sales_order WHERE status BETWEEN 1 AND 8 ORDER BY id`);
  for (const o of openOrders) {
    if (o.id >= 50001) { warn(`Order ${o.id} id ≥ 50001 would collide with ERP numbering — skipped`); continue; }
    if (await one(`SELECT ID FROM dbo.ZSO_ORDERS WHERE ID=?`, [o.id])) { bump('orders_already_migrated'); continue; }
    const items = await M(`SELECT * FROM sales_order_items WHERE sales_order_id=? AND status=1 ORDER BY id`, [o.id]);
    const cons = o.is_multi_consignee ? await M(`SELECT * FROM sales_order_consignee WHERE so_id=? AND status=1`, [o.id]) : [];
    const docs = await M(`SELECT * FROM sales_order_documents WHERE so_id=? AND status=1`, [o.id]);
    const pend = await M(`SELECT * FROM wallet_transactions WHERE ref_id=? AND status='pending' AND is_active=1`, [o.id]).catch(() => []);
    bump(`orders_status_${o.status}`);
    if (!COMMIT && !VERIFY) continue;
    await txv(async (r) => {
      await query(`SET IDENTITY_INSERT dbo.ZSO_ORDERS ON;
        INSERT INTO dbo.ZSO_ORDERS (ID,REF_NO,COMPANY,SO_TYPE,ORDER_TYPE,CARD_CODE,CARD_NAME,DIVISION_ID,CUST_ORDER_NO,ORDER_DATE,DELIVERY_DATE,CONTACT_PERSON,SALES_EMPLOYEE,
          SHIP_TO_CODE,SHIP_TO_TEXT,SHIP_STATE,BILL_TO_CODE,BILL_TO_TEXT,TAX_CODE,IGST_RATE,CGST_RATE,SGST_RATE,TCS_RATE,IGST_VAL,CGST_VAL,SGST_VAL,TCS_VAL,SUB_TOTAL,GRAND_TOTAL,
          CREDIT_DAYS,PAYMENT_MODE,PAYMENT_REF,PAYMENT_DATE,PAYMENT_DETAIL,PAID_AMOUNT,TDS_VAL,REBATE,PAID_STATUS,CUSTOMER_REMARKS,EMPLOYEE_REMARKS,INTERNAL_REMARKS,STATUS,
          IS_MULTI_CONSIGNEE,SAP_DOC_NUM,SAP_ORDER_DATE,INVOICE_JSON,CREATED_BY_TYPE,CREATED_BY,CREATED_BY_NAME,CREATED_AT,UPDATED_AT,LEGACY)
        VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,1);
        SET IDENTITY_INSERT dbo.ZSO_ORDERS OFF;`,
      [o.id, o.order_ref_no || refNo(o.id, o.created_date), (await masters.getSettings()).company, o.sales_order_type_id || 1, o.order_type || null, o.cust_code, o.cust_name,
        o.product_type_id, o.cust_order_no_old || o.cust_order_no || '', d(o.order_int_date), d(o.delivery_date), o.contact_person, o.sales_employee,
        o.shiptocode, o.ship_to, states[o.ship_to_id] || null, o.paytocode, o.bill_to, o.tax_code, n(o.igst_rate), n(o.cgst_rate), n(o.sgst_rate), n(o.tcs_rate),
        n(o.igst_value), n(o.cgst_value), n(o.sgst_value), n(o.tcs_value), n(o.sub_total), n(o.grand_total), o.credit_days || null, o.payment_mode, o.payment_ref_no,
        d(o.payment_date), o.payment_detail, o.paid_amount == null ? null : n(o.paid_amount), n(o.tds_val), o.general_rebate == null ? null : Math.abs(n(o.general_rebate)),
        o.paid_status, o.customer_remarks, o.employee_remarks, o.employee_remarks_internal, o.status, !!o.is_multi_consignee, o.sap_order_no ? parseInt(o.sap_order_no) || null : null,
        d(o.sap_order_date), o.invoice_details, o.order_type === 'DIRECT' ? 'user' : 'customer', String(o.created_by || ''), o.order_type === 'DIRECT' ? 'msale employee' : o.cust_name,
        o.created_date || new Date(), o.updated_date], r);
      for (const [i, it] of items.entries()) {
        await query(`INSERT INTO dbo.ZSO_ORDER_ITEMS (ORDER_ID,LINE_NO,PRODUCT_ID,ITEM_CODE,DISPLAY_CODE,DESCRIPTION,QTY,NOP,PRICE,SPECIAL_PRICE,COMMISSION,HSN,LINE_TOTAL) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)`,
          [o.id, i + 1, prodMap[it.item_id] || null, it.item_name_old || it.item_name, it.item_name, it.item_desc, n(it.item_qty), it.item_nop, n(it.item_price), n(it.item_s_price), n(it.commission), it.item_hsn, n(it.sub_total)], r);
      }
      const sapCode = Object.fromEntries(items.map(it => [it.item_name, it.item_name_old || it.item_name]));
      for (const c of cons) {
        let list = []; try { list = JSON.parse(c.item_list || '[]'); } catch (_e) {}
        await query(`INSERT INTO dbo.ZSO_ORDER_CONSIGNEES (ORDER_ID,SHIP_TO_CODE,SHIP_TO_TEXT,SE_NAME,SE_EMAIL,ITEMS_JSON) VALUES (?,?,?,?,?,?)`,
          [o.id, c.st_name, c.st_add, c.se_name, c.se_email, JSON.stringify(list.map(x => ({ itemCode: sapCode[x.item_code] || x.item_code, qty: n(x.item_qty) })))], r);
      }
      for (const dc of docs) {
        const src = path.join(DOCS_DIR, dc.document_name);
        if (!fs.existsSync(src)) { warn(`Order ${o.id}: document file missing — ${dc.document_name}`); continue; }
        const dest = `msale_${dc.document_name}`;
        if (COMMIT) fs.copyFileSync(src, path.join(DOC_DIR, dest));
        await query(`INSERT INTO dbo.ZSO_DOCS (ENTITY,ENTITY_ID,DOC_TYPE,TITLE,FILE_NAME,ORIG_NAME,CREATED_AT,CREATED_BY_NAME) VALUES ('order',?,?,?,?,?,?,'msale migration')`,
          [o.id, dc.document_type == 1 ? 'payment' : 'po_copy', dc.document_title, dest, dc.document_name, dc.created_date || new Date()], r);
        bump('documents_copied');
      }
      // Payment awaiting Accounts (status 6): carry the pending money over so it can be verified in the ERP.
      if (o.status == 6) {
        if (pend.length) {
          for (const t of pend) await query(`INSERT INTO dbo.ZSO_WALLET_TXN (CARD_CODE,TXN_TYPE,AMOUNT,TDS,STATUS,REF_TYPE,ORDER_ID,PAYMENT_MODE,PAYMENT_REF,PAYMENT_DATE,PAYMENT_DETAIL,REMARKS,CREATED_AT,CREATED_BY_NAME)
            VALUES (?,?,?,?,'pending',?,?,?,?,?,?,?,?,'msale migration')`, [t.cust_code, t.txn_type, t.txn_type === 'debit' ? n(t.amount) - n(t.tds_val) : n(t.amount), n(t.tds_val),
            t.ref_type === 'order' ? 'order' : 'topup', o.id, t.payment_mode, t.payment_ref_no, d(t.payment_date), t.payment_detail, t.remarks, t.created_date || new Date()], r);
        } else {
          if (n(o.paid_amount) > 0) await query(`INSERT INTO dbo.ZSO_WALLET_TXN (CARD_CODE,TXN_TYPE,AMOUNT,STATUS,REF_TYPE,ORDER_ID,PAYMENT_MODE,PAYMENT_REF,PAYMENT_DATE,PAYMENT_DETAIL,CREATED_BY_NAME)
            VALUES (?,'credit',?,'pending','topup',?,?,?,?,?,'msale migration')`, [o.cust_code, n(o.paid_amount), o.id, o.payment_mode, o.payment_ref_no, d(o.payment_date), o.payment_detail], r);
          await query(`INSERT INTO dbo.ZSO_WALLET_TXN (CARD_CODE,TXN_TYPE,AMOUNT,TDS,STATUS,REF_TYPE,ORDER_ID,CREATED_BY_NAME) VALUES (?,'debit',?,?,'pending','order',?,'msale migration')`,
            [o.cust_code, n(o.grand_total) - n(o.tds_val), n(o.tds_val), o.id], r);
        }
      }
      await query(`INSERT INTO dbo.ZSO_LOG (ENTITY,ENTITY_ID,ACTION,FROM_STATUS,TO_STATUS,REMARKS,ACTOR_TYPE,ACTOR,ACTOR_NAME) VALUES ('order',?,'migrated',NULL,?,?,'user','0','msale migration')`,
        [o.id, o.status, `Migrated from msale (history: ${o.chandan_status || '—'})`], r);
    });
  }

  // ── Open samples ──
  for (const sr of await M(`SELECT * FROM sample_request WHERE status NOT IN (4,9)`)) {
    if (await one(`SELECT ID FROM dbo.ZSO_SAMPLES WHERE ID=?`, [sr.id])) { bump('samples_already_migrated'); continue; }
    bump('samples_open');
    if (!COMMIT && !VERIFY) continue;
    const its = await M(`SELECT * FROM sales_order_items_sr WHERE sample_req__id=? AND status=1`, [sr.id]);
    const creator = (await M(`SELECT name, email_id FROM employee_master WHERE id=?`, [sr.created_by]))[0] || {};
    const cu = byEmail.get(String(creator.email_id || '').toLowerCase());
    await txv(async (r) => {
      await query(`SET IDENTITY_INSERT dbo.ZSO_SAMPLES ON;
        INSERT INTO dbo.ZSO_SAMPLES (ID,REF_NO,CARD_CODE,CARD_NAME,ADDRESS,BB_LICENCE_NO,BB_NAME,DIVISION_ID,REQ_DATE,DELIVERY_DAYS,STATUS,INTERNAL_REMARKS,MODIFICATION_REMARKS,REJECTION_REMARKS,
          STOCK_AVAILABLE,QA_DUE_DATE,QA_VERIFIED,OUT_OF_STOCK_DATE,CHALLAN_NO,CHALLAN_DATE,COURIER_NO,DISPATCH_INFO,FEEDBACK,CREATED_BY,CREATED_BY_NAME,CREATED_AT,LEGACY)
        VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,1); SET IDENTITY_INSERT dbo.ZSO_SAMPLES OFF;`,
      [sr.id, sr.sample_req_no, sr.cust_code, sr.cust_name, sr.cust_address, sr.bb_licence_no, sr.bb_name, sr.product_type_id, d(sr.sample_req_date), sr.sample_delivery_require_in_days,
        sr.status, sr.employee_remarks_internal, sr.modification_remarks, sr.rejection_remarks, sr.stock_radio == null ? null : !!sr.stock_radio, d(sr.qa_varification_due_date),
        sr.qa_verification_radio == null ? null : !!sr.qa_verification_radio, d(sr.out_of_stock_date), sr.challan_no, d(sr.date_of_challan), sr.courier_no, sr.dispatched_qty_and_batch_no,
        sr.feedback_sm, cu ? cu.ID : null, creator.name || 'msale', sr.created_date || new Date()], r);
      for (const it of its) await query(`INSERT INTO dbo.ZSO_SAMPLE_ITEMS (SAMPLE_ID,KIND,PRODUCT_ID,ITEM_CODE,DISPLAY_CODE,DESCRIPTION,QTY,NOP,BATCH_NO) VALUES (?,'domestic',?,?,?,?,?,?,?)`,
        [sr.id, prodMap[it.item_id] || null, it.item_name_old || it.item_name, it.item_name, it.item_desc, n(it.item_qty), it.item_nop, it.batch_no], r);
    });
  }
  for (const er of await M(`SELECT * FROM sample_request_export WHERE sample_status<>4 OR sample_status IS NULL`)) {
    if (await one(`SELECT ID FROM dbo.ZSO_EXPORT_SAMPLES WHERE ID=?`, [er.id])) { bump('export_samples_already_migrated'); continue; }
    bump('export_samples_open');
    if (!COMMIT && !VERIFY) continue;
    const its = await M(`SELECT * FROM sample_request_items_export WHERE sample_req__id=? AND status=1`, [er.id]);
    const creator = (await M(`SELECT name, email_id FROM employee_master WHERE id=?`, [er.created_by]))[0] || {};
    const cu = byEmail.get(String(creator.email_id || '').toLowerCase());
    const expMap = {};
    for (const p of await query(`SELECT ID, LEGACY_ID FROM dbo.ZSO_PRODUCTS WHERE CATALOG='export'`)) expMap[p.LEGACY_ID] = p.ID;
    await txv(async (r) => {
      await query(`SET IDENTITY_INSERT dbo.ZSO_EXPORT_SAMPLES ON;
        INSERT INTO dbo.ZSO_EXPORT_SAMPLES (ID,REF_NO,REGION,SAMPLE_AGAINST,CUST_NAME,COUNTRY,SHIPPING_ADDRESS,CONTACT_PERSON,CONTACT_EMAIL,CONTACT_NO,ACCOUNT_DETAILS,COURIER_BY_CUSTOMER,
          DIVISION_ID,REQ_DATE,DELIVERY_DATE,DELIVERY_DAYS,REMARKS_QA,REMARKS_LOGISTICS,STATUS,STOCK_AVAILABLE,QA_DUE_DATE,QA_VERIFIED,CHALLAN_NO,CHALLAN_DATE,COURIER_NO,DISPATCH_DETAILS,
          CREATED_BY,CREATED_BY_NAME,CREATED_AT,LEGACY) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,1); SET IDENTITY_INSERT dbo.ZSO_EXPORT_SAMPLES OFF;`,
      [er.id, er.sample_req_no, er.region, er.sample_against, er.cust_name, er.country, er.shipping_address, er.contact_person, er.contact_email, er.contact_no, er.account_details,
        !!er.sample_courier_exp, er.product_type_id || null, d(er.sample_req_date), d(er.delivery_date), er.sample_delivery_require_in_days, er.remarks_for_qa, er.remarks_for_logistics,
        er.sample_status || 1, er.stock_radio == null ? null : !!er.stock_radio, d(er.qa_varification_due_date), er.qa_verification_radio == null ? null : !!er.qa_verification_radio,
        er.challan_no, d(er.date_of_challan), er.courier_no, er.dispatch_details, cu ? cu.ID : null, creator.name || 'msale', er.created_date || new Date()], r);
      for (const it of its) await query(`INSERT INTO dbo.ZSO_SAMPLE_ITEMS (SAMPLE_ID,KIND,PRODUCT_ID,ITEM_CODE,DISPLAY_CODE,DESCRIPTION,QTY,NOP,BATCH_NO) VALUES (?,'export',?,?,?,?,?,?,?)`,
        [er.id, expMap[it.item_id] || null, it.item_name_old || it.item_name, it.item_name, it.item_desc, n(it.item_qty), it.item_nop, it.batch_no], r);
    });
  }

  // ── Wallet opening balances ──
  for (const w of await M(`SELECT * FROM customer_wallet WHERE balance<>0`).catch(() => [])) {
    if (await one(`SELECT ID FROM dbo.ZSO_WALLET_TXN WHERE CARD_CODE=? AND REF_TYPE='opening'`, [w.cust_code])) { bump('wallet_opening_already'); continue; }
    await W(`INSERT INTO dbo.ZSO_WALLET_TXN (CARD_CODE,TXN_TYPE,AMOUNT,STATUS,REF_TYPE,REMARKS,CREATED_BY_NAME,DECIDED_AT,DECIDED_BY_NAME) VALUES (?,?,?,'approved','opening','Opening balance migrated from msale','msale migration',SYSDATETIME(),'msale migration')`,
      [w.cust_code, Number(w.balance) > 0 ? 'credit' : 'debit', Math.abs(Number(w.balance))]);
    bump('wallet_opening_balances');
  }

  await my.end();
  console.log(JSON.stringify({ ...report, warnings: report.warnings.length }, null, 2));
  if (report.warnings.length) { console.log('\nWarnings:'); report.warnings.slice(0, 200).forEach(w => console.log('  - ' + w)); }
  if (unmatched.length) console.log(`\n${unmatched.length} active msale employees have no ERP user with the same email — create them in Admin → Users, then re-run (the run is idempotent).`);
  console.log(COMMIT ? '\n✅ Migration committed.' : '\nDry run finished — re-run with --commit to write.');
}

main().then(() => setTimeout(() => process.exit(0), 300)).catch(e => { console.error('\n❌ Migration failed:', e.stack || e.message); process.exit(1); });