// One-off data reset: wipes every business table and reseeds a small, // internally-consistent, realistic dataset — 2 pending orders, 1 completed, // 1 partially dispatched, 1 cancelled — built through the same tables the // live app itself writes to (stage history, dispatch history, cancellation // fields), so the Logs page's derived event feed comes out correct for free. // Keeps only the two named employees (Arun Palanisamy, Priya) and all roles. import { getConnection, executeWithConn } from '../config/database.js'; const ADMIN_PWH = '$2b$12$Vm8zfW/wxdjgqcvQpnNc2uIs0ZL4EjVzBIaDdum5WeJlIhzJykjbG'; // Admin@123 const ROLE_ADMIN = 'ROLE-1'; const ROLE_MANAGER = 'ROLE-2'; const EMP_ARUN = 'KCEMP-1'; const EMP_PRIYA = 'KCEMP-2'; const S = ['metal_issue', 'wiring', 'cm', 'bm', 'pol', 'cu', 'ass', 'qc', 'hm']; function dt(iso: string): Date { return new Date(iso); } async function main() { const conn = await getConnection(); try { await conn.beginTransaction(); // ── 1. Clear every business table, children first ── const clearOrder = [ 'order_dispatch_history', 'order_stage_history', 'order_comments', 'notifications', 'order_products', 'orders', 'seals', 'clients', 'products', 'product_categories', 'purities', 'metal_types', 'sizes', 'weights', 'activity_logs', ]; for (const table of clearOrder) { await executeWithConn(`DELETE FROM \`${table}\``, undefined, conn); } await executeWithConn(`DELETE FROM employees WHERE id NOT IN (?, ?)`, [EMP_ARUN, EMP_PRIYA], conn); // ── 2. Reset the two kept employees to clean canonical values ── await executeWithConn( `UPDATE employees SET code=?, name=?, mobile=?, email=?, role_id=?, auth_enabled=TRUE, user_id=?, password_hash=?, deleted_at=NULL, deleted_by=NULL WHERE id=?`, ['KCE-1', 'Arun Palanisamy', '9843011111', 'arun@karurchains.com', ROLE_ADMIN, 'admin@karurchains.com', ADMIN_PWH, EMP_ARUN], conn, ); await executeWithConn( `UPDATE employees SET code=?, name=?, mobile=?, email=?, role_id=?, auth_enabled=TRUE, user_id=?, password_hash=?, deleted_at=NULL, deleted_by=NULL WHERE id=?`, ['KCE-2', 'Priya', '9843022222', 'priya@karurchains.com', ROLE_MANAGER, 'manager@karurchains.com', ADMIN_PWH, EMP_PRIYA], conn, ); // ── 3. Master data ── await executeWithConn( `INSERT INTO product_categories (id, name, created_by) VALUES ('CAT-1','Chain',?), ('CAT-2','Ring',?), ('CAT-3','Bangle',?), ('CAT-4','Necklace',?), ('CAT-5','Earring',?)`, [EMP_ARUN, EMP_ARUN, EMP_ARUN, EMP_ARUN, EMP_ARUN], conn, ); await executeWithConn( `INSERT INTO metal_types (id, name, created_by) VALUES ('MT-1','Gold',?), ('MT-2','Silver',?), ('MT-3','Platinum',?), ('MT-4','Other',?)`, [EMP_ARUN, EMP_ARUN, EMP_ARUN, EMP_ARUN], conn, ); await executeWithConn( `INSERT INTO purities (id, name, created_by) VALUES ('PUR-1','24K',?), ('PUR-2','22K',?), ('PUR-3','18K',?), ('PUR-4','916',?), ('PUR-5','750',?), ('PUR-6','925',?)`, [EMP_ARUN, EMP_ARUN, EMP_ARUN, EMP_ARUN, EMP_ARUN, EMP_ARUN], conn, ); await executeWithConn( `INSERT INTO sizes (id, name, created_by) VALUES ('SZ-1','16 inch',?), ('SZ-2','18 inch',?), ('SZ-3','20 inch',?), ('SZ-4','2.4',?), ('SZ-5','2.6',?), ('SZ-6','2.8',?)`, [EMP_ARUN, EMP_ARUN, EMP_ARUN, EMP_ARUN, EMP_ARUN, EMP_ARUN], conn, ); await executeWithConn( `INSERT INTO weights (id, name, created_by) VALUES ('WT-1','2.5',?), ('WT-2','5',?), ('WT-3','10',?), ('WT-4','12.4',?), ('WT-5','24.8',?), ('WT-6','38.2',?)`, [EMP_ARUN, EMP_ARUN, EMP_ARUN, EMP_ARUN, EMP_ARUN, EMP_ARUN], conn, ); await executeWithConn( `INSERT INTO products (id, name, metal_type_id, purity_id, category_id, size_id, weight_id, huid, created_by) VALUES ('PROD-1','Gold Chain 22K','MT-1','PUR-2','CAT-1','SZ-2','WT-4','HU12345A',?), ('PROD-2','Antique Bangles','MT-1','PUR-4','CAT-3','SZ-5','WT-5','HU12346B',?), ('PROD-3','Temple Necklace','MT-1','PUR-2','CAT-4','SZ-1','WT-6',NULL,?)`, [EMP_ARUN, EMP_ARUN, EMP_ARUN], conn, ); // ── 4. Clients + seals ── await executeWithConn( `INSERT INTO clients (id, name, mobile, alternate_mobile, email, send_sms, created_by) VALUES ('CLI-1','Sri Lakshmi Jewellers','9843012345','9843012346','contact@srilakshmijewellers.in',TRUE,?), ('CLI-2','Meenakshi Gold Palace','9843098765',NULL,'info@meenakshigold.in',FALSE,?), ('CLI-3','Kumaran Chains Co.','9843055512',NULL,NULL,TRUE,?)`, [EMP_ARUN, EMP_ARUN, EMP_ARUN], conn, ); await executeWithConn( `INSERT INTO seals (id, client_id, name, created_by) VALUES ('SEAL-1','CLI-1','SKV Gold Seal',?), ('SEAL-2','CLI-1','SKV Silver Seal',?), ('SEAL-3','CLI-2','Lakshmi Hallmark',?)`, [EMP_ARUN, EMP_ARUN, EMP_ARUN], conn, ); // ── 5. Orders ── const orders: { id: string; order_no: string; order_date: string; client_id: string; taken_by: string; received_through: string; delivery_date: string; priority: string; contact: string | null; po_number: string | null; po_date: string | null; remarks: string | null; seal_id: string | null; lifecycle: string; created_at: string; }[] = [ { id: 'ORD-1', order_no: 'ORD-3001', order_date: '2026-07-12', client_id: 'CLI-1', taken_by: EMP_PRIYA, received_through: 'Phone', delivery_date: '2026-07-28', priority: 'Normal', contact: 'Mr. Ganesan', po_number: 'PO-2201', po_date: '2026-07-11', remarks: null, seal_id: 'SEAL-1', lifecycle: 'active', created_at: '2026-07-12 10:15:00' }, { id: 'ORD-2', order_no: 'ORD-3002', order_date: '2026-07-15', client_id: 'CLI-2', taken_by: EMP_ARUN, received_through: 'WhatsApp', delivery_date: '2026-08-02', priority: 'Normal', contact: null, po_number: null, po_date: null, remarks: null, seal_id: 'SEAL-3', lifecycle: 'active', created_at: '2026-07-15 11:40:00' }, { id: 'ORD-3', order_no: 'ORD-3003', order_date: '2026-07-08', client_id: 'CLI-3', taken_by: EMP_PRIYA, received_through: 'Direct Visit', delivery_date: '2026-07-20', priority: 'Urgent', contact: 'Mr. Kumaran', po_number: 'PO-2150', po_date: '2026-07-07', remarks: 'Wedding order — priority finish.', seal_id: null, lifecycle: 'active', created_at: '2026-07-08 09:20:00' }, { id: 'ORD-4', order_no: 'ORD-3004', order_date: '2026-07-10', client_id: 'CLI-1', taken_by: EMP_ARUN, received_through: 'Email', delivery_date: '2026-07-25', priority: 'Normal', contact: null, po_number: null, po_date: null, remarks: null, seal_id: 'SEAL-2', lifecycle: 'active', created_at: '2026-07-10 14:05:00' }, { id: 'ORD-5', order_no: 'ORD-3005', order_date: '2026-07-09', client_id: 'CLI-2', taken_by: EMP_PRIYA, received_through: 'Phone', delivery_date: '2026-07-22', priority: 'Normal', contact: null, po_number: null, po_date: null, remarks: 'Customer changed requirements — order called off.', seal_id: null, lifecycle: 'cancelled', created_at: '2026-07-09 16:30:00' }, ]; for (const o of orders) { await executeWithConn( `INSERT INTO orders (id, order_no, order_date, client_id, taken_by_employee_id, received_through, delivery_date, priority, received_by_client_contact, po_number, po_date, seal_id, remarks, lifecycle, created_by, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`, [o.id, o.order_no, o.order_date, o.client_id, o.taken_by, o.received_through, o.delivery_date, o.priority, o.contact, o.po_number, o.po_date, o.seal_id, o.remarks, o.lifecycle, o.taken_by, dt(o.created_at.replace(' ', 'T')), dt(o.created_at.replace(' ', 'T'))], conn, ); } // ── 6. Order lines ── interface LineDef { id: string; order_id: string; product_id: string; quantity: number; weight: number; size: string; jo_no: string; completed_stages: number; dispatched: boolean; dispatched_quantity: number | null; dispatched_weight: number | null; dispatch_date: string | null; dispatched_by: string | null; cancelled: boolean; cancelled_quantity: number | null; cancelled_weight: number | null; cancel_date: string | null; cancelled_by: string | null; cancel_reason: string | null; } const lines: LineDef[] = [ // Order 1 — pending, qty 5, mid-production (ASS = 7th stage) { id: 'OPRD-1', order_id: 'ORD-1', product_id: 'PROD-1', quantity: 5, weight: 12.4, size: '18 inch', jo_no: 'JO-2001', completed_stages: 7, dispatched: false, dispatched_quantity: null, dispatched_weight: null, dispatch_date: null, dispatched_by: null, cancelled: false, cancelled_quantity: null, cancelled_weight: null, cancel_date: null, cancelled_by: null, cancel_reason: null }, // Order 2 — pending, qty 5, early production (CM = 3rd stage) { id: 'OPRD-2', order_id: 'ORD-2', product_id: 'PROD-2', quantity: 5, weight: 24.8, size: '2.6', jo_no: 'JO-2002', completed_stages: 3, dispatched: false, dispatched_quantity: null, dispatched_weight: null, dispatch_date: null, dispatched_by: null, cancelled: false, cancelled_quantity: null, cancelled_weight: null, cancel_date: null, cancelled_by: null, cancel_reason: null }, // Order 3 — completed, qty 3, fully staged + fully dispatched in one batch { id: 'OPRD-3', order_id: 'ORD-3', product_id: 'PROD-3', quantity: 3, weight: 38.2, size: '16 inch', jo_no: 'JO-2003', completed_stages: 9, dispatched: true, dispatched_quantity: 3, dispatched_weight: 114.6, dispatch_date: '2026-07-17 15:30:00', dispatched_by: EMP_ARUN, cancelled: false, cancelled_quantity: null, cancelled_weight: null, cancel_date: null, cancelled_by: null, cancel_reason: null }, // Order 4 — partially dispatched, qty 4, fully staged, 2 of 4 dispatched across 2 batches { id: 'OPRD-4', order_id: 'ORD-4', product_id: 'PROD-1', quantity: 4, weight: 12.4, size: '18 inch', jo_no: 'JO-2004', completed_stages: 9, dispatched: false, dispatched_quantity: 2, dispatched_weight: 24.8, dispatch_date: '2026-07-20 12:00:00', dispatched_by: EMP_PRIYA, cancelled: false, cancelled_quantity: null, cancelled_weight: null, cancel_date: null, cancelled_by: null, cancel_reason: null }, // Order 5 — cancelled mid-production (CM = 3rd stage reached, then called off) { id: 'OPRD-5', order_id: 'ORD-5', product_id: 'PROD-2', quantity: 2, weight: 24.8, size: '2.6', jo_no: 'JO-2005', completed_stages: 3, dispatched: false, dispatched_quantity: null, dispatched_weight: null, dispatch_date: null, dispatched_by: null, cancelled: true, cancelled_quantity: 2, cancelled_weight: 49.6, cancel_date: '2026-07-12 11:00:00', cancelled_by: EMP_ARUN, cancel_reason: 'Customer changed requirements before production could continue.' }, ]; for (const l of lines) { await executeWithConn( `INSERT INTO order_products (id, order_id, product_id, quantity, weight, size, jo_no, completed_stages, dispatched, dispatched_quantity, dispatched_weight, dispatch_date, dispatched_by_employee_id, cancelled, cancelled_quantity, cancelled_weight, cancel_date, cancelled_by_employee_id, cancel_reason, created_by) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`, [ l.id, l.order_id, l.product_id, l.quantity, l.weight, l.size, l.jo_no, l.completed_stages, l.dispatched, l.dispatched_quantity, l.dispatched_weight, l.dispatch_date ? dt(l.dispatch_date.replace(' ', 'T')) : null, l.dispatched_by, l.cancelled, l.cancelled_quantity, l.cancelled_weight, l.cancel_date ? dt(l.cancel_date.replace(' ', 'T')) : null, l.cancelled_by, l.cancel_reason, orders.find((o) => o.id === l.order_id)!.taken_by, ], conn, ); } // ── 7. Stage history — one row per stage reached, chronological, with // completed_at set for every stage except the line's current/last one ── interface StagePlan { lineId: string; startDate: string; employees: string[] } const stagePlans: StagePlan[] = [ { lineId: 'OPRD-1', startDate: '2026-07-12', employees: [EMP_PRIYA, EMP_ARUN] }, { lineId: 'OPRD-2', startDate: '2026-07-15', employees: [EMP_ARUN, EMP_PRIYA] }, { lineId: 'OPRD-3', startDate: '2026-07-08', employees: [EMP_PRIYA, EMP_ARUN] }, { lineId: 'OPRD-4', startDate: '2026-07-10', employees: [EMP_ARUN, EMP_PRIYA] }, { lineId: 'OPRD-5', startDate: '2026-07-09', employees: [EMP_PRIYA, EMP_ARUN] }, ]; let stageSeq = 1; for (const plan of stagePlans) { const line = lines.find((l) => l.id === plan.lineId)!; const base = dt(`${plan.startDate}T09:30:00`); for (let i = 0; i < line.completed_stages; i++) { const enteredAt = new Date(base.getTime() + i * 86_400_000); const isLast = i === line.completed_stages - 1; // The cancelled line's final reached stage never finished — cancelled instead. const completedAt = isLast ? null : new Date(base.getTime() + (i + 1) * 86_400_000); await executeWithConn( `INSERT INTO order_stage_history (id, order_product_id, stage_id, entered_at, completed_at, employee_id) VALUES (?, ?, ?, ?, ?, ?)`, [`STG-${stageSeq}`, line.id, S[i], enteredAt, completedAt, plan.employees[i % plan.employees.length]], conn, ); stageSeq += 1; } } // ── 8. Dispatch history — one row per real dispatch batch ── await executeWithConn( `INSERT INTO order_dispatch_history (id, order_product_id, quantity, weight, dispatch_date, dispatched_by_employee_id) VALUES ('DSH-1', 'OPRD-3', 3, 114.6, ?, ?)`, [dt('2026-07-17T15:30:00'), EMP_ARUN], conn, ); await executeWithConn( `INSERT INTO order_dispatch_history (id, order_product_id, quantity, weight, dispatch_date, dispatched_by_employee_id) VALUES ('DSH-2', 'OPRD-4', 1, 12.4, ?, ?), ('DSH-3', 'OPRD-4', 1, 12.4, ?, ?)`, [dt('2026-07-19T10:00:00'), EMP_PRIYA, dt('2026-07-20T12:00:00'), EMP_PRIYA], conn, ); await conn.commit(); console.log('Reset + seed complete.'); } catch (err) { await conn.rollback(); console.error('Reset failed, rolled back:', err); throw err; } finally { conn.release(); } } main() .then(() => process.exit(0)) .catch(() => process.exit(1));