1  #!/usr/bin/env python3
  2  """
  3  Reconcile sent history from Resend/Twilio APIs against the messages table.
  4  
  5  After restoring from the Mar-7-2026 backup, messages sent between Mar 7-20
  6  have delivery_status=NULL in the DB but were already sent. This script:
  7    1. Fetches all Twilio messages sent Mar 7-20 (by to-number)
  8    2. Fetches all Resend emails sent Mar 7-20 (by to-address)
  9    3. Marks matching messages in the DB as delivery_status='sent' with
 10       a note that they were reconciled from API history
 11  
 12  Usage:
 13      cd /home/jason/code/333Method
 14      python3 scripts/reconcile-sent-history.py [--dry-run]
 15  
 16  Flags:
 17      --dry-run   Show what would be updated without writing to DB
 18  """
 19  
 20  import sqlite3
 21  import sys
 22  import os
 23  import json
 24  import time
 25  from datetime import datetime, timezone
 26  from urllib.request import Request, urlopen
 27  from urllib.error import HTTPError
 28  from urllib.parse import urlencode
 29  from base64 import b64encode
 30  
 31  DRY_RUN = '--dry-run' in sys.argv
 32  DB_PATH = os.environ.get('DATABASE_PATH', 'db/sites.db')
 33  
 34  # Date range: Mar 7 (backup date) to Mar 20 (wipe date)
 35  FROM_DATE = '2026-03-07'
 36  TO_DATE = '2026-03-21'  # exclusive upper bound
 37  
 38  # Load env
 39  env = {}
 40  env_path = '.env'
 41  if os.path.exists(env_path):
 42      for line in open(env_path):
 43          line = line.strip()
 44          if line and not line.startswith('#') and '=' in line:
 45              k, _, v = line.partition('=')
 46              env[k.strip()] = v.strip().strip('"').strip("'")
 47  
 48  RESEND_KEY = env.get('RESEND_API_KEY', '')
 49  TWILIO_SID = env.get('TWILIO_ACCOUNT_SID', '')
 50  TWILIO_TOKEN = env.get('TWILIO_AUTH_TOKEN', '')
 51  
 52  print(f"DB: {DB_PATH}")
 53  print(f"Date range: {FROM_DATE} to {TO_DATE}")
 54  print(f"Mode: {'DRY RUN' if DRY_RUN else 'LIVE'}")
 55  print(f"Resend key: {'set' if RESEND_KEY else 'MISSING'}")
 56  print(f"Twilio SID: {'set' if TWILIO_SID else 'MISSING'}")
 57  print()
 58  
 59  conn = sqlite3.connect(DB_PATH)
 60  conn.execute("PRAGMA journal_mode=WAL")
 61  conn.execute("PRAGMA busy_timeout=30000")
 62  
 63  
 64  def http_get(url, headers=None):
 65      req = Request(url, headers=headers or {})
 66      with urlopen(req, timeout=30) as r:
 67          return json.loads(r.read())
 68  
 69  
 70  def http_get_basic(url, user, password):
 71      creds = b64encode(f"{user}:{password}".encode()).decode()
 72      return http_get(url, {'Authorization': f'Basic {creds}'})
 73  
 74  
 75  # ── Fetch Twilio SMS history ─────────────────────────────────────────────────
 76  
 77  print("=== Fetching Twilio SMS history (Mar 7-20) ===")
 78  twilio_sent = {}  # phone_number -> list of {sid, date_sent, body}
 79  
 80  page_url = (
 81      f"https://api.twilio.com/2010-04-01/Accounts/{TWILIO_SID}/Messages.json"
 82      f"?DateSent%3E={FROM_DATE}&DateSent%3C={TO_DATE}&PageSize=1000"
 83  )
 84  
 85  page_count = 0
 86  while page_url:
 87      try:
 88          data = http_get_basic(page_url, TWILIO_SID, TWILIO_TOKEN)
 89      except HTTPError as e:
 90          print(f"  Twilio HTTP error: {e.code} {e.reason}")
 91          break
 92  
 93      msgs = data.get('messages', [])
 94      for m in msgs:
 95          to_num = m.get('to', '').strip()
 96          if not to_num:
 97              continue
 98          if to_num not in twilio_sent:
 99              twilio_sent[to_num] = []
100          twilio_sent[to_num].append({
101              'sid': m.get('sid'),
102              'date_sent': m.get('date_sent'),
103              'status': m.get('status'),
104          })
105  
106      page_count += 1
107      next_page = data.get('next_page_uri')
108      page_url = f"https://api.twilio.com{next_page}" if next_page else None
109      if page_url:
110          time.sleep(0.1)  # rate limit
111  
112  total_twilio = sum(len(v) for v in twilio_sent.values())
113  print(f"  Pages fetched: {page_count}")
114  print(f"  Total SMS: {total_twilio} to {len(twilio_sent)} unique numbers")
115  
116  # ── Fetch Resend email history ───────────────────────────────────────────────
117  
118  print("\n=== Fetching Resend email history (Mar 7-20) ===")
119  resend_sent = {}  # email_address -> list of {id, created_at, subject}
120  
121  cursor = None
122  page_count = 0
123  while True:
124      url = f"https://api.resend.com/emails?limit=100"
125      if cursor:
126          url += f"&cursor={cursor}"
127  
128      try:
129          data = http_get(url, {'Authorization': f'Bearer {RESEND_KEY}'})
130      except HTTPError as e:
131          print(f"  Resend HTTP error: {e.code} {e.reason}")
132          break
133  
134      emails = data.get('data', [])
135      stop = False
136      for e in emails:
137          created = e.get('created_at', '')[:10]
138          if created < FROM_DATE:
139              stop = True
140              break
141          if created >= TO_DATE:
142              continue  # skip today's emails
143          to_list = e.get('to', [])
144          for addr in to_list:
145              addr = addr.lower().strip()
146              if addr not in resend_sent:
147                  resend_sent[addr] = []
148              resend_sent[addr].append({
149                  'id': e.get('id'),
150                  'created_at': e.get('created_at'),
151                  'subject': e.get('subject', ''),
152                  'last_event': e.get('last_event'),
153              })
154  
155      page_count += 1
156      has_more = data.get('has_more', False)
157      if stop or not has_more or not emails:
158          break
159  
160      # Get cursor for next page (last item's id)
161      cursor = emails[-1].get('id')
162      time.sleep(0.05)
163  
164  total_resend = sum(len(v) for v in resend_sent.values())
165  print(f"  Pages fetched: {page_count}")
166  print(f"  Total emails: {total_resend} to {len(resend_sent)} unique addresses")
167  
168  # ── Match against messages table ─────────────────────────────────────────────
169  
170  print("\n=== Matching against messages table ===")
171  
172  # Get all eligible (approved, null delivery) outbound messages
173  rows = conn.execute("""
174      SELECT id, contact_method, contact_uri, site_id
175      FROM messages
176      WHERE direction='outbound'
177        AND approval_status='approved'
178        AND delivery_status IS NULL
179        AND contact_uri IS NOT NULL
180  """).fetchall()
181  print(f"  Eligible messages: {len(rows):,}")
182  
183  sms_matched = []
184  email_matched = []
185  
186  for (msg_id, method, uri, site_id) in rows:
187      if method == 'sms':
188          # Normalize: Twilio returns E.164 (+14155551234)
189          normalized = uri.strip()
190          if normalized in twilio_sent:
191              sms_matched.append((msg_id, site_id, normalized, twilio_sent[normalized][0]['date_sent']))
192      elif method == 'email':
193          addr = uri.lower().strip()
194          if addr in resend_sent:
195              email_matched.append((msg_id, site_id, addr, resend_sent[addr][0]['created_at']))
196  
197  print(f"  SMS matches (already sent Mar 7-20): {len(sms_matched)}")
198  print(f"  Email matches (already sent Mar 7-20): {len(email_matched)}")
199  
200  # ── Apply updates ────────────────────────────────────────────────────────────
201  
202  print(f"\n=== {'Would update' if DRY_RUN else 'Updating'} matched messages ===")
203  
204  reconcile_note = 'Reconciled from API history (sent Mar 7-20, lost in DB wipe 2026-03-20)'
205  
206  if not DRY_RUN:
207      # Mark SMS as sent
208      for (msg_id, site_id, uri, date_sent) in sms_matched:
209          conn.execute("""
210              UPDATE messages
211              SET delivery_status='sent',
212                  sent_at=?,
213                  error_message=?
214              WHERE id=?
215          """, (date_sent, reconcile_note, msg_id))
216  
217      # Mark emails as sent
218      for (msg_id, site_id, addr, created_at) in email_matched:
219          conn.execute("""
220              UPDATE messages
221              SET delivery_status='sent',
222                  sent_at=?,
223                  error_message=?
224              WHERE id=?
225          """, (created_at, reconcile_note, msg_id))
226  
227      conn.commit()
228      print(f"  Marked {len(sms_matched)} SMS as sent")
229      print(f"  Marked {len(email_matched)} emails as sent")
230  else:
231      print(f"  Would mark {len(sms_matched)} SMS as sent")
232      print(f"  Would mark {len(email_matched)} emails as sent")
233      if sms_matched[:3]:
234          print("  SMS sample:")
235          for row in sms_matched[:3]:
236              print(f"    msg_id={row[0]} phone={row[2]} sent={row[3]}")
237      if email_matched[:3]:
238          print("  Email sample:")
239          for row in email_matched[:3]:
240              print(f"    msg_id={row[0]} addr={row[2]} sent={row[3]}")
241  
242  # ── Summary ──────────────────────────────────────────────────────────────────
243  
244  print("\n=== Summary ===")
245  remaining_sms = conn.execute("""
246      SELECT COUNT(*) FROM messages
247      WHERE direction='outbound' AND approval_status='approved'
248        AND delivery_status IS NULL AND contact_method='sms'
249  """).fetchone()[0]
250  remaining_email = conn.execute("""
251      SELECT COUNT(*) FROM messages
252      WHERE direction='outbound' AND approval_status='approved'
253        AND delivery_status IS NULL AND contact_method='email'
254  """).fetchone()[0]
255  print(f"  Remaining eligible SMS:   {remaining_sms:,}")
256  print(f"  Remaining eligible email: {remaining_email:,}")
257  
258  conn.close()
259  print(f"\n{'✅ Done (dry run)' if DRY_RUN else '✅ Done'}")