purchases.test.js
1 /** 2 * Purchases Unit Tests 3 * Tests purchase CRUD operations, status transitions, and validation 4 * using the production schema 5 */ 6 7 import { describe, test, beforeEach } from 'node:test'; 8 import assert from 'node:assert/strict'; 9 import { initTestDb } from '../../src/utils/test-db.js'; 10 11 describe('Purchases Table', () => { 12 let db; 13 14 beforeEach(() => { 15 db = initTestDb(); 16 }); 17 18 test('purchases table exists in schema', () => { 19 const table = db 20 .prepare("SELECT name FROM sqlite_master WHERE type='table' AND name='purchases'") 21 .get(); 22 assert.ok(table, 'purchases table should exist'); 23 }); 24 25 test('inserts a purchase with all fields', () => { 26 const result = db 27 .prepare( 28 `INSERT INTO purchases 29 (email, landing_page_url, phone, paypal_order_id, paypal_payer_id, 30 paypal_capture_id, amount, currency, amount_usd, country_code, 31 ip_address, user_agent, status) 32 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)` 33 ) 34 .run( 35 'customer@example.com', 36 'https://example-business.com', 37 '+61412345678', 38 'ORDER_ABC123', 39 'PAYER_XYZ', 40 'CAPTURE_789', 41 29700, 42 'USD', 43 29700, 44 'US', 45 '1.2.3.4', 46 'Mozilla/5.0', 47 'paid' 48 ); 49 50 assert.ok(result.lastInsertRowid); 51 52 const purchase = db.prepare('SELECT * FROM purchases WHERE id = ?').get(result.lastInsertRowid); 53 assert.equal(purchase.email, 'customer@example.com'); 54 assert.equal(purchase.landing_page_url, 'https://example-business.com'); 55 assert.equal(purchase.amount, 29700); 56 assert.equal(purchase.currency, 'USD'); 57 assert.equal(purchase.status, 'paid'); 58 assert.equal(purchase.country_code, 'US'); 59 assert.equal(purchase.retry_count, 0); 60 assert.ok(purchase.created_at); 61 assert.ok(purchase.updated_at); 62 }); 63 64 test('enforces paypal_order_id uniqueness', () => { 65 db.prepare( 66 `INSERT INTO purchases (email, landing_page_url, paypal_order_id, amount, currency, amount_usd, status) 67 VALUES (?, ?, ?, ?, ?, ?, ?)` 68 ).run('a@test.com', 'https://a.com', 'UNIQUE_ORDER', 100, 'USD', 100, 'paid'); 69 70 assert.throws( 71 () => 72 db 73 .prepare( 74 `INSERT INTO purchases (email, landing_page_url, paypal_order_id, amount, currency, amount_usd, status) 75 VALUES (?, ?, ?, ?, ?, ?, ?)` 76 ) 77 .run('b@test.com', 'https://b.com', 'UNIQUE_ORDER', 200, 'USD', 200, 'paid'), 78 /UNIQUE constraint/ 79 ); 80 }); 81 82 test('INSERT OR IGNORE skips duplicate paypal_order_id', () => { 83 db.prepare( 84 `INSERT INTO purchases (email, landing_page_url, paypal_order_id, amount, currency, amount_usd, status) 85 VALUES (?, ?, ?, ?, ?, ?, ?)` 86 ).run('first@test.com', 'https://first.com', 'ORDER_DUP', 100, 'USD', 100, 'paid'); 87 88 const result = db 89 .prepare( 90 `INSERT OR IGNORE INTO purchases (email, landing_page_url, paypal_order_id, amount, currency, amount_usd, status) 91 VALUES (?, ?, ?, ?, ?, ?, ?)` 92 ) 93 .run('second@test.com', 'https://second.com', 'ORDER_DUP', 200, 'USD', 200, 'paid'); 94 95 assert.equal(result.changes, 0); 96 97 const count = db 98 .prepare('SELECT COUNT(*) as c FROM purchases WHERE paypal_order_id = ?') 99 .get('ORDER_DUP').c; 100 assert.equal(count, 1); 101 }); 102 103 test('enforces valid status via CHECK constraint', () => { 104 assert.throws( 105 () => 106 db 107 .prepare( 108 `INSERT INTO purchases (email, landing_page_url, paypal_order_id, amount, currency, amount_usd, status) 109 VALUES (?, ?, ?, ?, ?, ?, ?)` 110 ) 111 .run('test@test.com', 'https://test.com', 'ORDER_BAD', 100, 'USD', 100, 'invalid_status'), 112 /CHECK constraint/ 113 ); 114 }); 115 116 test('allows all valid statuses', () => { 117 const validStatuses = [ 118 'paid', 119 'processing', 120 'report_generated', 121 'delivered', 122 'failed', 123 'refunded', 124 ]; 125 126 for (const status of validStatuses) { 127 const result = db 128 .prepare( 129 `INSERT INTO purchases (email, landing_page_url, paypal_order_id, amount, currency, amount_usd, status) 130 VALUES (?, ?, ?, ?, ?, ?, ?)` 131 ) 132 .run( 133 `${status}@test.com`, 134 `https://${status}.com`, 135 `ORDER_${status.toUpperCase()}`, 136 100, 137 'USD', 138 100, 139 status 140 ); 141 142 assert.ok(result.lastInsertRowid, `Should accept status: ${status}`); 143 } 144 }); 145 146 test('status transitions: paid → processing → report_generated → delivered', () => { 147 db.prepare( 148 `INSERT INTO purchases (email, landing_page_url, paypal_order_id, amount, currency, amount_usd, status) 149 VALUES (?, ?, ?, ?, ?, ?, ?)` 150 ).run('flow@test.com', 'https://flow.com', 'ORDER_FLOW', 29700, 'USD', 29700, 'paid'); 151 152 const { id } = db 153 .prepare('SELECT id FROM purchases WHERE paypal_order_id = ?') 154 .get('ORDER_FLOW'); 155 156 // paid → processing 157 db.prepare("UPDATE purchases SET status = 'processing' WHERE id = ?").run(id); 158 assert.equal( 159 db.prepare('SELECT status FROM purchases WHERE id = ?').get(id).status, 160 'processing' 161 ); 162 163 // processing → report_generated 164 db.prepare( 165 "UPDATE purchases SET status = 'report_generated', report_score = 62, report_grade = 'C' WHERE id = ?" 166 ).run(id); 167 assert.equal( 168 db.prepare('SELECT status FROM purchases WHERE id = ?').get(id).status, 169 'report_generated' 170 ); 171 172 // report_generated → delivered 173 db.prepare( 174 "UPDATE purchases SET status = 'delivered', delivered_at = CURRENT_TIMESTAMP WHERE id = ?" 175 ).run(id); 176 const purchase = db.prepare('SELECT * FROM purchases WHERE id = ?').get(id); 177 assert.equal(purchase.status, 'delivered'); 178 assert.ok(purchase.delivered_at); 179 }); 180 181 test('refund updates status and timestamps', () => { 182 db.prepare( 183 `INSERT INTO purchases (email, landing_page_url, paypal_order_id, amount, currency, amount_usd, status) 184 VALUES (?, ?, ?, ?, ?, ?, ?)` 185 ).run( 186 'refund@test.com', 187 'https://refund.com', 188 'ORDER_REFUND', 189 29700, 190 'USD', 191 29700, 192 'delivered' 193 ); 194 195 const { id } = db 196 .prepare('SELECT id FROM purchases WHERE paypal_order_id = ?') 197 .get('ORDER_REFUND'); 198 199 db.prepare( 200 `UPDATE purchases 201 SET status = 'refunded', refunded_at = CURRENT_TIMESTAMP, refund_reason = ? 202 WHERE id = ?` 203 ).run('Customer requested', id); 204 205 const purchase = db.prepare('SELECT * FROM purchases WHERE id = ?').get(id); 206 assert.equal(purchase.status, 'refunded'); 207 assert.ok(purchase.refunded_at); 208 assert.equal(purchase.refund_reason, 'Customer requested'); 209 }); 210 211 test('retry_count increments on failure', () => { 212 db.prepare( 213 `INSERT INTO purchases (email, landing_page_url, paypal_order_id, amount, currency, amount_usd, status) 214 VALUES (?, ?, ?, ?, ?, ?, ?)` 215 ).run('retry@test.com', 'https://retry.com', 'ORDER_RETRY', 29700, 'USD', 29700, 'processing'); 216 217 const { id } = db 218 .prepare('SELECT id FROM purchases WHERE paypal_order_id = ?') 219 .get('ORDER_RETRY'); 220 221 // Simulate 3 retries 222 for (let i = 1; i <= 3; i++) { 223 db.prepare( 224 'UPDATE purchases SET retry_count = retry_count + 1, error_message = ? WHERE id = ?' 225 ).run(`Attempt ${i} failed`, id); 226 } 227 228 const purchase = db.prepare('SELECT * FROM purchases WHERE id = ?').get(id); 229 assert.equal(purchase.retry_count, 3); 230 assert.equal(purchase.error_message, 'Attempt 3 failed'); 231 }); 232 233 test('queries purchases by status', () => { 234 const statuses = ['paid', 'paid', 'delivered', 'failed']; 235 statuses.forEach((status, i) => { 236 db.prepare( 237 `INSERT INTO purchases (email, landing_page_url, paypal_order_id, amount, currency, amount_usd, status) 238 VALUES (?, ?, ?, ?, ?, ?, ?)` 239 ).run(`${i}@test.com`, `https://${i}.com`, `ORDER_QUERY_${i}`, 29700, 'USD', 29700, status); 240 }); 241 242 const paidCount = db 243 .prepare("SELECT COUNT(*) as c FROM purchases WHERE status = 'paid'") 244 .get().c; 245 assert.equal(paidCount, 2); 246 247 const failedCount = db 248 .prepare("SELECT COUNT(*) as c FROM purchases WHERE status = 'failed'") 249 .get().c; 250 assert.equal(failedCount, 1); 251 }); 252 253 test('indexes exist for common queries', () => { 254 const indexes = db 255 .prepare("SELECT name FROM sqlite_master WHERE type='index' AND tbl_name='purchases'") 256 .all() 257 .map(i => i.name); 258 259 // Check at least some key indexes exist 260 const hasStatusIndex = indexes.some(n => n.includes('status')); 261 const hasEmailIndex = indexes.some(n => n.includes('email')); 262 const hasOrderIndex = indexes.some(n => n.includes('paypal_order') || n.includes('order')); 263 264 assert.ok( 265 hasStatusIndex || hasEmailIndex || hasOrderIndex, 266 `Should have indexes. Found: ${indexes.join(', ')}` 267 ); 268 }); 269 270 test('site_id foreign key reference works', () => { 271 // Insert a site first 272 db.prepare( 273 'INSERT INTO sites (landing_page_url, domain, keyword, status) VALUES (?, ?, ?, ?)' 274 ).run('https://site.com', 'site.com', 'test', 'found'); 275 276 const siteId = db.prepare('SELECT id FROM sites ORDER BY id DESC LIMIT 1').get().id; 277 278 db.prepare( 279 `INSERT INTO purchases (email, landing_page_url, paypal_order_id, amount, currency, amount_usd, status, site_id) 280 VALUES (?, ?, ?, ?, ?, ?, ?, ?)` 281 ).run('fk@test.com', 'https://site.com', 'ORDER_FK', 29700, 'USD', 29700, 'paid', siteId); 282 283 const purchase = db 284 .prepare('SELECT site_id FROM purchases WHERE paypal_order_id = ?') 285 .get('ORDER_FK'); 286 assert.equal(purchase.site_id, siteId); 287 }); 288 });