1  #!/usr/bin/env node
  2  /**
  3   * migrate-messages-to-shared.js
  4   *
  5   * One-shot migration: copies 333Method's messages, opt_outs, and
  6   * unsubscribed_emails from sites.db into mmo-platform/db/messages.db.
  7   *
  8   * Safe to re-run — all INSERTs use INSERT OR IGNORE.
  9   *
 10   * Column mapping notes (333Method → shared):
 11   *   - project              ← hardcoded '333method'
 12   *   - approval_status      ← 'gdpr_blocked' mapped to 'parked'
 13   *                             (gdpr_blocked is not in shared CHECK constraint)
 14   *   - message_type         ← 'reply_skipped' mapped to 'reply'
 15   *                             (reply_skipped is not in shared CHECK constraint)
 16   *   - video_url            ← NULL (333method has no videos)
 17   *   - pricing_id           ← NULL (333method uses pricing_variant free-text)
 18   *   - price_discount_pct   ← NULL
 19   *
 20   * Columns present in 333Method but not in shared schema (dropped silently):
 21   *   tracking_clicked_at, read_at, payment_id, payment_amount_local,
 22   *   payment_amount_usd, exchange_rate, pricing_variant, report_url,
 23   *   our_account, zb_status, retry_count, reworded_at
 24   *
 25   * Usage:
 26   *   node db/migrations/migrate-messages-to-shared.js [--dry-run]
 27   */
 28  
 29  import Database from 'better-sqlite3';
 30  import { existsSync } from 'fs';
 31  import path from 'path';
 32  import { fileURLToPath } from 'url';
 33  
 34  const __dirname = path.dirname(fileURLToPath(import.meta.url));
 35  
 36  const DRY_RUN = process.argv.includes('--dry-run');
 37  
 38  // ---------------------------------------------------------------------------
 39  // Paths
 40  // ---------------------------------------------------------------------------
 41  
 42  const sitesDbPath = process.env.DATABASE_PATH
 43    || path.resolve(__dirname, '../../db/sites.db');
 44  
 45  const messagesDbPath = process.env.MESSAGES_DB_PATH
 46    || path.resolve(__dirname, '../../../mmo-platform/db/messages.db');
 47  
 48  // ---------------------------------------------------------------------------
 49  // Guard: both files must exist before we start
 50  // ---------------------------------------------------------------------------
 51  
 52  if (!existsSync(sitesDbPath)) {
 53    console.error(`ERROR: sites.db not found at: ${sitesDbPath}`);
 54    process.exit(1);
 55  }
 56  
 57  if (!existsSync(messagesDbPath)) {
 58    console.error(`ERROR: messages.db not found at: ${messagesDbPath}`);
 59    console.error('Run mmo-platform/scripts/init-messages-db.js first.');
 60    process.exit(1);
 61  }
 62  
 63  // ---------------------------------------------------------------------------
 64  // Open databases
 65  // ---------------------------------------------------------------------------
 66  
 67  const src = new Database(sitesDbPath, { readonly: true });
 68  const dst = new Database(messagesDbPath);
 69  
 70  dst.pragma('journal_mode = WAL');
 71  dst.pragma('busy_timeout = 30000');
 72  
 73  // ATTACH source so we can query both in a single connection for counts
 74  dst.exec(`ATTACH DATABASE '${sitesDbPath}' AS src`);
 75  
 76  // ---------------------------------------------------------------------------
 77  // Pre-migration counts
 78  // ---------------------------------------------------------------------------
 79  
 80  const srcMessageCount       = src.prepare('SELECT COUNT(*) AS n FROM messages').get().n;
 81  const srcOptOutCount        = src.prepare('SELECT COUNT(*) AS n FROM opt_outs').get().n;
 82  const srcUnsubCount         = src.prepare('SELECT COUNT(*) AS n FROM unsubscribed_emails').get().n;
 83  
 84  const dstMessagesBefore     = dst.prepare("SELECT COUNT(*) AS n FROM messages WHERE project='333method'").get().n;
 85  const dstOptOutsBefore      = dst.prepare('SELECT COUNT(*) AS n FROM opt_outs').get().n;
 86  const dstUnsubBefore        = dst.prepare('SELECT COUNT(*) AS n FROM unsubscribed_emails').get().n;
 87  
 88  console.log('=== 333Method → messages.db migration ===');
 89  console.log(`Source:      ${sitesDbPath}`);
 90  console.log(`Destination: ${messagesDbPath}`);
 91  if (DRY_RUN) console.log('DRY RUN — no writes will be committed');
 92  console.log('');
 93  console.log('Pre-migration counts:');
 94  console.log(`  sites.db   messages:             ${srcMessageCount.toLocaleString()}`);
 95  console.log(`  sites.db   opt_outs:             ${srcOptOutCount.toLocaleString()}`);
 96  console.log(`  sites.db   unsubscribed_emails:  ${srcUnsubCount.toLocaleString()}`);
 97  console.log(`  messages.db messages (333method): ${dstMessagesBefore.toLocaleString()}`);
 98  console.log(`  messages.db opt_outs:            ${dstOptOutsBefore.toLocaleString()}`);
 99  console.log(`  messages.db unsubscribed_emails: ${dstUnsubBefore.toLocaleString()}`);
100  console.log('');
101  
102  // ---------------------------------------------------------------------------
103  // Migration helpers
104  // ---------------------------------------------------------------------------
105  
106  /**
107   * Map approval_status values that exist in sites.db but not in the shared
108   * schema's CHECK constraint.
109   *   gdpr_blocked → parked  (compliance-parked, semantically equivalent)
110   */
111  function mapApprovalStatus(v) {
112    if (v === 'gdpr_blocked') return 'parked';
113    return v;
114  }
115  
116  /**
117   * Map message_type values that exist in sites.db but not in the shared
118   * schema's CHECK constraint.
119   *   reply_skipped → reply  (it is a reply-type message, just skipped for send)
120   */
121  function mapMessageType(v) {
122    if (v === 'reply_skipped') return 'reply';
123    return v || 'outreach';
124  }
125  
126  // ---------------------------------------------------------------------------
127  // Build the INSERT statement
128  // Columns in shared messages that we can populate from 333Method:
129  //   id, project, site_id, direction, contact_method, contact_uri,
130  //   message_body, subject_line, approval_status, rework_instructions,
131  //   exported_at, delivery_status, error_message, retry_at,
132  //   sent_at, delivered_at, email_id, opened_at, sentiment, intent,
133  //   message_type, raw_payload, is_read, processed_at,
134  //   payment_link, payment_amount, payment_currency, template_id,
135  //   created_at, updated_at
136  //
137  // Populated as NULL / default:
138  //   video_url, pricing_id, price_discount_pct
139  // ---------------------------------------------------------------------------
140  
141  const insertMessage = dst.prepare(`
142    INSERT OR IGNORE INTO messages (
143      id,
144      project,
145      site_id,
146      direction,
147      contact_method,
148      contact_uri,
149      message_body,
150      subject_line,
151      video_url,
152      approval_status,
153      rework_instructions,
154      exported_at,
155      delivery_status,
156      error_message,
157      retry_at,
158      sent_at,
159      delivered_at,
160      email_id,
161      opened_at,
162      sentiment,
163      intent,
164      message_type,
165      raw_payload,
166      is_read,
167      processed_at,
168      payment_link,
169      payment_amount,
170      payment_currency,
171      pricing_id,
172      price_discount_pct,
173      template_id,
174      created_at,
175      updated_at
176    ) VALUES (
177      @id,
178      '333method',
179      @site_id,
180      @direction,
181      @contact_method,
182      @contact_uri,
183      @message_body,
184      @subject_line,
185      NULL,
186      @approval_status,
187      @rework_instructions,
188      @exported_at,
189      @delivery_status,
190      @error_message,
191      @retry_at,
192      @sent_at,
193      @delivered_at,
194      @email_id,
195      @opened_at,
196      @sentiment,
197      @intent,
198      @message_type,
199      @raw_payload,
200      @is_read,
201      @processed_at,
202      @payment_link,
203      @payment_amount,
204      @payment_currency,
205      NULL,
206      NULL,
207      @template_id,
208      @created_at,
209      @updated_at
210    )
211  `);
212  
213  const insertOptOut = dst.prepare(`
214    INSERT OR IGNORE INTO opt_outs (
215      id, phone, email, method, project, opted_out_at, source, created_at
216    ) VALUES (
217      @id, @phone, @email, @method, '333method', @opted_out_at, @source, @created_at
218    )
219  `);
220  
221  const insertUnsub = dst.prepare(`
222    INSERT OR IGNORE INTO unsubscribed_emails (
223      id, email, message_id, project, unsubscribed_at, source, created_at
224    ) VALUES (
225      @id, @email, NULL, '333method', @unsubscribed_at, @source, @created_at
226    )
227  `);
228  
229  // ---------------------------------------------------------------------------
230  // Execute migration in a single transaction
231  // ---------------------------------------------------------------------------
232  
233  const migrate = dst.transaction(() => {
234    // --- messages ---
235    const allMessages = src.prepare(`
236      SELECT
237        id, site_id, direction, contact_method, contact_uri,
238        message_body, subject_line, approval_status, rework_instructions,
239        exported_at, delivery_status, error_message, retry_at,
240        sent_at, delivered_at, email_id, opened_at, sentiment, intent,
241        raw_payload, is_read, processed_at, payment_link, payment_amount,
242        payment_currency, template_id, created_at, updated_at, message_type
243      FROM messages
244      ORDER BY id
245    `).all();
246  
247    let msgInserted = 0;
248    let msgSkipped  = 0;
249  
250    for (const row of allMessages) {
251      const result = insertMessage.run({
252        ...row,
253        approval_status: mapApprovalStatus(row.approval_status),
254        message_type:    mapMessageType(row.message_type),
255      });
256      if (result.changes > 0) {
257        msgInserted++;
258      } else {
259        msgSkipped++;
260      }
261    }
262  
263    // --- opt_outs ---
264    const allOptOuts = src.prepare('SELECT * FROM opt_outs').all();
265    let optOutInserted = 0;
266    let optOutSkipped  = 0;
267  
268    for (const row of allOptOuts) {
269      const result = insertOptOut.run(row);
270      if (result.changes > 0) {
271        optOutInserted++;
272      } else {
273        optOutSkipped++;
274      }
275    }
276  
277    // --- unsubscribed_emails ---
278    const allUnsubs = src.prepare('SELECT * FROM unsubscribed_emails').all();
279    let unsubInserted = 0;
280    let unsubSkipped  = 0;
281  
282    for (const row of allUnsubs) {
283      const result = insertUnsub.run(row);
284      if (result.changes > 0) {
285        unsubInserted++;
286      } else {
287        unsubSkipped++;
288      }
289    }
290  
291    return { msgInserted, msgSkipped, optOutInserted, optOutSkipped, unsubInserted, unsubSkipped };
292  });
293  
294  let stats;
295  if (DRY_RUN) {
296    // Run inside a transaction that we immediately roll back
297    const dryRunTx = dst.transaction(() => {
298      stats = migrate();
299      // Force rollback by throwing after we have the stats
300      throw new Error('DRY_RUN_ROLLBACK');
301    });
302    try {
303      dryRunTx();
304    } catch (e) {
305      if (e.message !== 'DRY_RUN_ROLLBACK') throw e;
306    }
307  } else {
308    stats = migrate();
309  }
310  
311  // ---------------------------------------------------------------------------
312  // Post-migration counts
313  // ---------------------------------------------------------------------------
314  
315  const dstMessagesAfter = dst.prepare("SELECT COUNT(*) AS n FROM messages WHERE project='333method'").get().n;
316  const dstOptOutsAfter  = dst.prepare('SELECT COUNT(*) AS n FROM opt_outs').get().n;
317  const dstUnsubAfter    = dst.prepare('SELECT COUNT(*) AS n FROM unsubscribed_emails').get().n;
318  
319  console.log(`Migration results${DRY_RUN ? ' (DRY RUN — rolled back)' : ''}:`);
320  console.log(`  messages:            ${stats.msgInserted.toLocaleString()} inserted, ${stats.msgSkipped.toLocaleString()} skipped (already existed)`);
321  console.log(`  opt_outs:            ${stats.optOutInserted.toLocaleString()} inserted, ${stats.optOutSkipped.toLocaleString()} skipped`);
322  console.log(`  unsubscribed_emails: ${stats.unsubInserted.toLocaleString()} inserted, ${stats.unsubSkipped.toLocaleString()} skipped`);
323  console.log('');
324  console.log('Post-migration counts in messages.db:');
325  console.log(`  messages (333method): ${dstMessagesAfter.toLocaleString()} (was ${dstMessagesBefore.toLocaleString()})`);
326  console.log(`  opt_outs:            ${dstOptOutsAfter.toLocaleString()} (was ${dstOptOutsBefore.toLocaleString()})`);
327  console.log(`  unsubscribed_emails: ${dstUnsubAfter.toLocaleString()} (was ${dstUnsubBefore.toLocaleString()})`);
328  console.log('');
329  
330  // Sanity check
331  if (!DRY_RUN) {
332    const expectedMessages = dstMessagesBefore + stats.msgInserted;
333    if (dstMessagesAfter !== expectedMessages) {
334      console.error(`WARNING: count mismatch — expected ${expectedMessages}, got ${dstMessagesAfter}`);
335      process.exit(1);
336    }
337    console.log('Sanity check: PASSED');
338    console.log('Migration complete.');
339  } else {
340    console.log('Dry run complete — no changes committed.');
341  }
342  
343  src.close();
344  dst.close();