Class: Heathrow::Database

Inherits:
Object
  • Object
show all
Defined in:
lib/heathrow/database.rb

Constant Summary collapse

SCHEMA_VERSION = 
1

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(db_path = HEATHROW_DB) ⇒ Database

Returns a new instance of Database.



11
12
13
14
15
16
17
18
19
20
 
# File 'lib/heathrow/database.rb', line 11

def initialize(db_path = HEATHROW_DB)
  @db_path = db_path
  @db = SQLite3::Database.new(@db_path)
  @db.results_as_hash = true
  @db.execute("PRAGMA journal_mode=WAL")
  @db.execute("PRAGMA busy_timeout=5000")
  @mutex = Mutex.new
  setup_schema
  run_migrations
end

Instance Attribute Details

#db ⇒ Object (readonly)

Returns the value of attribute db.



7
8
9
 
# File 'lib/heathrow/database.rb', line 7

def db
  @db
end

Instance Method Details

#add_source(name, plugin_type, config, capabilities = ["read"], enabled = true) ⇒ Object

Source operations



541
542
543
544
545
546
547
548
549
550
551
552
553
 
# File 'lib/heathrow/database.rb', line 541

def add_source(name, plugin_type, config, capabilities = ["read"], enabled = true)
  config_json = config.is_a?(Hash) ? config.to_json : config
  capabilities_json = capabilities.is_a?(Array) ? capabilities.to_json : capabilities
  now = Time.now.to_i

  @db.execute <<-SQL, [name, plugin_type, enabled ? 1 : 0, config_json, capabilities_json, 0, now, now]
    INSERT OR REPLACE INTO sources
    (name, plugin_type, enabled, config, capabilities, message_count, created_at, updated_at)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?)
  SQL

  @db.last_insert_row_id
end

#close ⇒ Object 



740
741
742
 
# File 'lib/heathrow/database.rb', line 740

def close
  @db.close if @db
end

#create_additional_tables ⇒ Object 



125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
 
# File 'lib/heathrow/database.rb', line 125

def create_additional_tables
  # Contacts table
  @db.execute <<-SQL
    CREATE TABLE IF NOT EXISTS contacts (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      primary_email TEXT,
      identities TEXT,
      phone TEXT,
      avatar_url TEXT,
      tags TEXT,
      notes TEXT,
      message_count INTEGER DEFAULT 0,
      last_contact INTEGER,
      created_at INTEGER NOT NULL,
      updated_at INTEGER NOT NULL
    )
  SQL

  @db.execute "CREATE INDEX IF NOT EXISTS idx_contacts_email ON contacts(primary_email)"
  @db.execute "CREATE INDEX IF NOT EXISTS idx_contacts_name ON contacts(name)"

  # Drafts table
  @db.execute <<-SQL
    CREATE TABLE IF NOT EXISTS drafts (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      source_id INTEGER,
      reply_to_id INTEGER,
      recipients TEXT NOT NULL,
      cc TEXT,
      bcc TEXT,
      subject TEXT,
      content TEXT NOT NULL,
      attachments TEXT,
      created_at INTEGER NOT NULL,
      updated_at INTEGER NOT NULL,
      FOREIGN KEY(source_id) REFERENCES sources(id) ON DELETE SET NULL,
      FOREIGN KEY(reply_to_id) REFERENCES messages(id) ON DELETE SET NULL
    )
  SQL

  @db.execute "CREATE INDEX IF NOT EXISTS idx_drafts_updated ON drafts(updated_at DESC)"

  # Filters table
  @db.execute <<-SQL
    CREATE TABLE IF NOT EXISTS filters (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      enabled INTEGER DEFAULT 1,
      priority INTEGER DEFAULT 0,
      conditions TEXT NOT NULL,
      actions TEXT NOT NULL,
      created_at INTEGER NOT NULL,
      updated_at INTEGER NOT NULL
    )
  SQL

  @db.execute "CREATE INDEX IF NOT EXISTS idx_filters_enabled ON filters(enabled)"
  @db.execute "CREATE INDEX IF NOT EXISTS idx_filters_priority ON filters(priority DESC)"

  # Settings table
  @db.execute <<-SQL
    CREATE TABLE IF NOT EXISTS settings (
      key TEXT PRIMARY KEY,
      value TEXT NOT NULL,
      updated_at INTEGER NOT NULL
    )
  SQL
end

#create_default_views ⇒ Object 



240
241
242
243
244
245
246
247
248
249
250
251
252
253
 
# File 'lib/heathrow/database.rb', line 240

def create_default_views
  # Check if default views exist
  count = @db.get_first_value("SELECT COUNT(*) FROM views")
  return if count && count > 0

  now = Time.now.to_i

  # Built-in views (A, N, * are hardcoded in the app, but stored for reference)
  @db.execute("INSERT OR IGNORE INTO views (name, key_binding, filters, is_remainder, created_at, updated_at) VALUES ('All', 'A', '{\"rules\": []}', 0, ?, ?)", [now, now])
  @db.execute("INSERT OR IGNORE INTO views (name, key_binding, filters, created_at, updated_at) VALUES ('Unread', 'N', '{\"rules\": [{\"field\": \"read\", \"op\": \"=\", \"value\": false}]}', ?, ?)", [now, now])
  @db.execute("INSERT OR IGNORE INTO views (name, key_binding, filters, created_at, updated_at) VALUES ('Starred', '*', '{\"rules\": [{\"field\": \"starred\", \"op\": \"=\", \"value\": true}]}', ?, ?)", [now, now])

  # User-configurable views are defined in heathrowrc via the `view` DSL
end

#delete_message(message_id) ⇒ Object 



514
515
516
 
# File 'lib/heathrow/database.rb', line 514

def delete_message(message_id)
  @db.execute("DELETE FROM messages WHERE id = ?", message_id)
end

#delete_messages_by_ids(ids) ⇒ Object

Bulk delete messages by their IDs



701
702
703
704
705
 
# File 'lib/heathrow/database.rb', line 701

def delete_messages_by_ids(ids)
  return if ids.empty?
  placeholders = ids.map { '?' }.join(',')
  @db.execute("DELETE FROM messages WHERE id IN (#{placeholders})", ids)
end

#delete_postponed(id) ⇒ Object 



532
533
534
 
# File 'lib/heathrow/database.rb', line 532

def delete_postponed(id)
  @db.execute("DELETE FROM postponed WHERE id = ?", [id])
end

#delete_view(view_id) ⇒ Object 



653
654
655
 
# File 'lib/heathrow/database.rb', line 653

def delete_view(view_id)
  @db.execute("DELETE FROM views WHERE id = ?", view_id)
end

#execute(query, *params) ⇒ Object 



732
733
734
 
# File 'lib/heathrow/database.rb', line 732

def execute(query, *params)
  @db.execute(query, params)
end

#get_all_sources ⇒ Object 



560
561
562
 
# File 'lib/heathrow/database.rb', line 560

def get_all_sources
  get_sources(false)
end

#get_all_views ⇒ Object 



665
666
667
668
669
670
671
 
# File 'lib/heathrow/database.rb', line 665

def get_all_views
  views = @db.execute("SELECT * FROM views ORDER BY id")
  views.each do |view|
    view['filters'] = JSON.parse(view['filters']) if view['filters']
  end
  views
end

#get_folder_index(source_id, folder_name) ⇒ Object

Returns hash of { base_id => external_id:, read:, starred: } for a folder



686
687
688
689
690
691
692
693
694
695
696
697
698
 
# File 'lib/heathrow/database.rb', line 686

def get_folder_index(source_id, folder_name)
  rows = @db.execute(
    "SELECT id, external_id, read, starred, replied FROM messages WHERE source_id = ? AND folder = ?",
    [source_id, folder_name]
  )
  index = {}
  rows.each do |row|
    base_id = row['external_id'].to_s.split(':2,', 2).first
    index[base_id] = { id: row['id'], external_id: row['external_id'],
                       read: row['read'], starred: row['starred'], replied: row['replied'] }
  end
  index
end

#get_message(id) ⇒ Object 



475
476
477
478
479
 
# File 'lib/heathrow/database.rb', line 475

def get_message(id)
  row = @db.execute("SELECT * FROM messages WHERE id = ?", [id]).first
  return nil unless row
  normalize_message_row(row)
end

#get_messages(filters = {}, limit = nil, offset = 0, light: false) ⇒ Object 



343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
 
# File 'lib/heathrow/database.rb', line 343

def get_messages(filters = {}, limit = nil, offset = 0, light: false)
  cols = if light
    "id, source_id, external_id, thread_id, parent_id, sender, sender_name, recipients, subject, substr(content, 1, 200) as content, timestamp, received_at, read, starred, archived, labels, metadata, attachments, folder, replied"
  else
    "*"
  end
  query = "SELECT #{cols} FROM messages WHERE 1=1"
  params = []

  # Exclude archived/deleted messages by default
  unless filters.key?(:archived)
    query += " AND (archived = 0 OR archived IS NULL)"
  end

  if filters[:source_id]
    query += " AND source_id = ?"
    params << filters[:source_id]
  end

  if filters[:source_ids].is_a?(Array) && !filters[:source_ids].empty?
    ph = filters[:source_ids].map { '?' }.join(',')
    query += " AND source_id IN (#{ph})"
    params += filters[:source_ids]
  end

  if filters[:source_name]
    patterns = filters[:source_name].split('|').map(&:strip)
    conditions = patterns.map { "name LIKE ?" }.join(' OR ')
    query += " AND source_id IN (SELECT id FROM sources WHERE #{conditions})"
    params += patterns.map { |p| "%#{p}%" }
  end
  
  # Handle sender pattern (supports regex via pipe separation)
  if filters[:sender_pattern]
    patterns = filters[:sender_pattern].split('|')
    conditions = patterns.map { "(sender LIKE ? OR sender_name LIKE ?)" }.join(' OR ')
    query += " AND (#{conditions})"
    patterns.each { |p| params += ["%#{p}%", "%#{p}%"] }
  end
  
  # Handle subject pattern
  if filters[:subject_pattern]
    patterns = filters[:subject_pattern].split('|')
    conditions = patterns.map { "subject LIKE ?" }.join(' OR ')
    query += " AND (#{conditions})"
    params += patterns.map { |p| "%#{p}%" }
  end
  
  # Handle content patterns (each can be a pattern with | for OR, separated by comma for AND)
  if filters[:content_patterns]
    filters[:content_patterns].each do |pattern_group|
      if pattern_group.include?('|')
        # OR logic within this group
        or_patterns = pattern_group.split('|').map(&:strip)
        conditions = or_patterns.map { "content LIKE ?" }.join(' OR ')
        query += " AND (#{conditions})"
        params += or_patterns.map { |p| "%#{p}%" }
      else
        # Simple keyword
        query += " AND content LIKE ?"
        params << "%#{pattern_group}%"
      end
    end
  end
  
  # Legacy support for old filter formats
  if filters[:content_keywords]
    filters[:content_keywords].each do |keyword|
      query += " AND content LIKE ?"
      params << "%#{keyword}%"
    end
  end
  
  if filters[:content_regex]
    query += " AND content LIKE ?"
    params << "%#{filters[:content_regex]}%"
  end
  
  # Search across sender, subject, content (supports | for OR)
  if filters[:search]
    terms = filters[:search].split('|').map(&:strip).reject(&:empty?)
    if terms.size == 1
      query += " AND (sender LIKE ? OR subject LIKE ? OR content LIKE ? OR recipients LIKE ?)"
      search_term = "%#{terms.first}%"
      params += [search_term, search_term, search_term, search_term]
    else
      conditions = terms.map { |_t|
        "(sender LIKE ? OR subject LIKE ? OR content LIKE ? OR recipients LIKE ?)"
      }.join(' OR ')
      query += " AND (#{conditions})"
      terms.each { |t| term = "%#{t}%"; params += [term, term, term, term] }
    end
  end
  
  # Support both old and new column names for read status
  if filters[:is_read] != nil || filters[:read] != nil
    query += " AND read = ?"
    params << ((filters[:read] || filters[:is_read]) ? 1 : 0)
  end

  if filters[:starred] != nil
    query += " AND starred = ?"
    params << (filters[:starred] ? 1 : 0)
  end

  if filters[:archived] != nil
    query += " AND archived = ?"
    params << (filters[:archived] ? 1 : 0)
  end

  if filters[:maildir_folder]
    query += " AND folder = ?"
    params << filters[:maildir_folder]
  end

  if filters[:label]
    # Match label anywhere in the JSON labels array
    query += " AND labels LIKE ?"
    params << "%\"#{filters[:label]}\"%"
  end

  query += " ORDER BY timestamp DESC"

  if limit
    query += " LIMIT ? OFFSET ?"
    params += [limit, offset]
  end

  results = @db.execute(query, params)
  results.map { |row| normalize_message_row(row) }
end

#get_postponed(id) ⇒ Object 



528
529
530
 
# File 'lib/heathrow/database.rb', line 528

def get_postponed(id)
  @db.get_first_row("SELECT * FROM postponed WHERE id = ?", [id])
end

#get_source_by_id(id) ⇒ Object 



586
587
588
589
 
# File 'lib/heathrow/database.rb', line 586

def get_source_by_id(id)
  source = @db.execute("SELECT * FROM sources WHERE id = ? LIMIT 1", id).first
  source ? normalize_source_row(source) : nil
end

#get_source_by_name(name) ⇒ Object 



581
582
583
584
 
# File 'lib/heathrow/database.rb', line 581

def get_source_by_name(name)
  source = @db.execute("SELECT * FROM sources WHERE name = ? LIMIT 1", name).first
  source ? normalize_source_row(source) : nil
end

#get_source_stats ⇒ Object

Batch query: returns { source_id => unread: }



565
566
567
568
569
570
571
572
573
 
# File 'lib/heathrow/database.rb', line 565

def get_source_stats
  rows = @db.execute(
    "SELECT source_id, COUNT(*) as cnt, SUM(CASE WHEN read = 0 THEN 1 ELSE 0 END) as unread
     FROM messages WHERE archived = 0 OR archived IS NULL GROUP BY source_id"
  )
  stats = {}
  rows.each { |r| stats[r['source_id']] = { count: r['cnt'], unread: r['unread'] } }
  stats
end

#get_source_type_map ⇒ Object

Returns { source_id => plugin_type } for all sources



576
577
578
579
 
# File 'lib/heathrow/database.rb', line 576

def get_source_type_map
  rows = @db.execute("SELECT id, plugin_type FROM sources")
  rows.each_with_object({}) { |r, h| h[r['id']] = r['plugin_type'] }
end

#get_sources(enabled_only = true) ⇒ Object 



555
556
557
558
 
# File 'lib/heathrow/database.rb', line 555

def get_sources(enabled_only = true)
  query = enabled_only ? "SELECT * FROM sources WHERE enabled = 1 ORDER BY id" : "SELECT * FROM sources ORDER BY id"
  @db.execute(query).each { |s| normalize_source_row(s) }
end

#get_stats ⇒ Object

Statistics



674
675
676
677
678
679
680
681
682
683
 
# File 'lib/heathrow/database.rb', line 674

def get_stats
  {
    total_messages: @db.get_first_value("SELECT COUNT(*) FROM messages"),
    unread_messages: @db.get_first_value("SELECT COUNT(*) FROM messages WHERE read = 0"),
    starred_messages: @db.get_first_value("SELECT COUNT(*) FROM messages WHERE starred = 1"),
    archived_messages: @db.get_first_value("SELECT COUNT(*) FROM messages WHERE archived = 1"),
    total_sources: @db.get_first_value("SELECT COUNT(*) FROM sources"),
    active_sources: @db.get_first_value("SELECT COUNT(*) FROM sources WHERE enabled = 1")
  }
end

#get_view(view_id) ⇒ Object 



657
658
659
660
661
662
663
 
# File 'lib/heathrow/database.rb', line 657

def get_view(view_id)
  result = @db.get_first_row("SELECT * FROM views WHERE id = ?", view_id)
  if result
    result['filters'] = JSON.parse(result['filters']) if result['filters']
  end
  result
end

#insert_message(data) ⇒ Object

Message operations



256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
 
# File 'lib/heathrow/database.rb', line 256

def insert_message(data)
  # Support both hash and array formats for backward compatibility
  if data.is_a?(Hash)
    now = Time.now.to_i
    folder = data[:labels].is_a?(Array) ? data[:labels].first : nil
    @db.execute(
      "INSERT INTO messages
      (source_id, external_id, thread_id, parent_id, sender, sender_name,
       recipients, cc, bcc, subject, content, html_content,
       timestamp, received_at, read, starred, archived,
       labels, attachments, metadata, folder, replied)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ON CONFLICT(source_id, external_id) DO UPDATE SET
        subject = excluded.subject,
        content = excluded.content,
        html_content = excluded.html_content,
        metadata = excluded.metadata,
        attachments = excluded.attachments
        -- Preserve read, starred, archived, replied (user modifications)",
      [
        data[:source_id],
        data[:external_id],
        data[:thread_id],
        data[:parent_id],
        data[:sender],
        data[:sender_name],
        data[:recipients].is_a?(Array) ? data[:recipients].to_json : data[:recipients],
        data[:cc]&.to_json,
        data[:bcc]&.to_json,
        data[:subject],
        data[:content],
        data[:html_content],
        data[:timestamp] || now,
        data[:received_at] || now,
        data[:read] ? 1 : 0,
        data[:starred] ? 1 : 0,
        data[:archived] ? 1 : 0,
        data[:labels]&.to_json,
        data[:attachments]&.to_json,
        data[:metadata]&.to_json,
        folder,
        data[:replied] ? 1 : 0
      ]
    )
  else
    # Legacy array format - convert to new schema as best we can
    source_id, source_type, external_id, sender, recipient, subject, content, raw_data, attachments, timestamp, is_read = data
    now = Time.now.to_i
    ts = timestamp.is_a?(Time) ? timestamp.to_i : (timestamp.is_a?(String) ? Time.parse(timestamp).to_i : timestamp)

    @db.execute(
      "INSERT INTO messages
      (source_id, external_id, thread_id, parent_id, sender, sender_name,
       recipients, cc, bcc, subject, content, html_content,
       timestamp, received_at, read, starred, archived,
       labels, attachments, metadata)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ON CONFLICT(source_id, external_id) DO UPDATE SET
        subject = excluded.subject,
        content = excluded.content,
        html_content = excluded.html_content,
        metadata = excluded.metadata,
        attachments = excluded.attachments
        -- Preserve read, starred, archived (user modifications)",
      [
        source_id,
        external_id || "legacy-#{now}",
        nil, nil,
        sender || "unknown",
        nil,
        [recipient].compact.to_json,
        nil, nil,
        subject,
        content || "",
        nil,
        ts || now,
        now,
        is_read ? 1 : 0,
        0, 0,
        nil,
        attachments,
        raw_data
      ]
    )
  end
end

#list_postponed ⇒ Object 



524
525
526
 
# File 'lib/heathrow/database.rb', line 524

def list_postponed
  @db.execute("SELECT * FROM postponed ORDER BY created_at DESC")
end

#mark_all_as_read(folder: nil) ⇒ Object

Bulk mark all unread messages as read, optionally filtered by folder. Returns array of [id, metadata_json] for maildir flag sync.



488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
 
# File 'lib/heathrow/database.rb', line 488

def mark_all_as_read(folder: nil)
  if folder
    rows = @db.execute(
      "SELECT id, metadata FROM messages WHERE read = 0 AND folder >= ? AND folder < ?",
      [folder, folder.chomp('.') + '/']
    )
    @db.execute(
      "UPDATE messages SET read = 1 WHERE read = 0 AND folder >= ? AND folder < ?",
      [folder, folder.chomp('.') + '/']
    )
  else
    rows = @db.execute("SELECT id, metadata FROM messages WHERE read = 0")
    @db.execute("UPDATE messages SET read = 1 WHERE read = 0")
  end
  rows
end

#mark_as_read(message_id) ⇒ Object 



481
482
483
484
 
# File 'lib/heathrow/database.rb', line 481

def mark_as_read(message_id)
  @db.execute("UPDATE messages SET read = 1 WHERE id = ?", message_id)
  @db.changes > 0
end

#mark_as_unread(message_id) ⇒ Object 



505
506
507
508
 
# File 'lib/heathrow/database.rb', line 505

def mark_as_unread(message_id)
  @db.execute("UPDATE messages SET read = 0 WHERE id = ?", message_id)
  @db.changes > 0
end

#postponed_count ⇒ Object 



536
537
538
 
# File 'lib/heathrow/database.rb', line 536

def postponed_count
  @db.get_first_value("SELECT COUNT(*) FROM postponed") || 0
end

#run_migrations ⇒ Object 



195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
 
# File 'lib/heathrow/database.rb', line 195

def run_migrations
  current_version = @db.get_first_value("SELECT MAX(version) FROM schema_version") || 0

  # Migration: add folder column for fast folder lookups
  # Migration: add poll_interval and color columns to sources
  source_cols = @db.execute("PRAGMA table_info(sources)").map { |c| c['name'] }
  unless source_cols.include?('poll_interval')
    @db.execute("ALTER TABLE sources ADD COLUMN poll_interval INTEGER DEFAULT 900")
    # Maildir is fast local scan, default to 30s
    @db.execute("UPDATE sources SET poll_interval = 30 WHERE plugin_type = 'maildir'")
  end
  unless source_cols.include?('color')
    @db.execute("ALTER TABLE sources ADD COLUMN color TEXT")
  end

  cols = @db.execute("PRAGMA table_info(messages)").map { |c| c['name'] }
  unless cols.include?('folder')
    @db.execute("ALTER TABLE messages ADD COLUMN folder TEXT")
    @db.execute("CREATE INDEX IF NOT EXISTS idx_messages_folder ON messages(folder)")
    # Populate from labels JSON (first element)
    @db.execute("UPDATE messages SET folder = json_extract(labels, '$[0]') WHERE labels IS NOT NULL AND labels != '[]'")
  end

  unless cols.include?('replied')
    @db.execute("ALTER TABLE messages ADD COLUMN replied INTEGER DEFAULT 0")
  end

  # Postponed messages (drafts)
  @db.execute <<-SQL
    CREATE TABLE IF NOT EXISTS postponed (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      source_id INTEGER,
      data TEXT NOT NULL,
      created_at INTEGER NOT NULL
    )
  SQL

  if current_version < SCHEMA_VERSION
    @db.transaction do
      @db.execute("INSERT INTO schema_version (version, applied_at) VALUES (?, ?)",
                 [SCHEMA_VERSION, Time.now.to_i])
    end
  end
end

#save_postponed(source_id, data) ⇒ Object

Postponed messages (drafts)



519
520
521
522
 
# File 'lib/heathrow/database.rb', line 519

def save_postponed(source_id, data)
  @db.execute("INSERT INTO postponed (source_id, data, created_at) VALUES (?, ?, ?)",
              [source_id, JSON.generate(data), Time.now.to_i])
end

#save_view(view_data) ⇒ Object

View operations



614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
 
# File 'lib/heathrow/database.rb', line 614

def save_view(view_data)
  now = Time.now.to_i
  filters_json = view_data[:filters].is_a?(Hash) ? view_data[:filters].to_json : view_data[:filters]

  if view_data[:id]
    # Update existing view by id
    @db.execute(
      "UPDATE views SET name = ?, key_binding = ?, filters = ?, sort_order = ?, updated_at = ? WHERE id = ?",
      [view_data[:name], view_data[:key_binding], filters_json,
       view_data[:sort_order] || 'timestamp DESC', now, view_data[:id]]
    )
  elsif view_data[:key_binding]
    # UPSERT by key_binding (for F1-F12 and 0-9)
    existing = @db.get_first_row("SELECT id FROM views WHERE key_binding = ?", view_data[:key_binding])
    if existing
      @db.execute(
        "UPDATE views SET name = ?, filters = ?, sort_order = ?, updated_at = ? WHERE key_binding = ?",
        [view_data[:name], filters_json, view_data[:sort_order] || 'timestamp DESC', now, view_data[:key_binding]]
      )
      existing['id']
    else
      @db.execute(
        "INSERT INTO views (name, key_binding, filters, sort_order, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?)",
        [view_data[:name], view_data[:key_binding], filters_json,
         view_data[:sort_order] || 'timestamp DESC', now, now]
      )
      @db.last_insert_row_id
    end
  else
    # Insert new view
    @db.execute(
      "INSERT INTO views (name, key_binding, filters, sort_order, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?)",
      [view_data[:name], view_data[:key_binding], filters_json,
       view_data[:sort_order] || 'timestamp DESC', now, now]
    )
    @db.last_insert_row_id
  end
end

#setup_schema ⇒ Object 



22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
 
# File 'lib/heathrow/database.rb', line 22

def setup_schema
  # Schema version tracking
  @db.execute <<-SQL
    CREATE TABLE IF NOT EXISTS schema_version (
      version INTEGER PRIMARY KEY,
      applied_at INTEGER NOT NULL
    )
  SQL
  # Main messages table (following DATABASE_SCHEMA.md spec)
  @db.execute <<-SQL
    CREATE TABLE IF NOT EXISTS messages (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      source_id INTEGER NOT NULL,
      external_id TEXT NOT NULL,
      thread_id TEXT,
      parent_id INTEGER,

      sender TEXT NOT NULL,
      sender_name TEXT,

      recipients TEXT NOT NULL,
      cc TEXT,
      bcc TEXT,

      subject TEXT,
      content TEXT NOT NULL,
      html_content TEXT,

      timestamp INTEGER NOT NULL,
      received_at INTEGER NOT NULL,
      read INTEGER DEFAULT 0,
      starred INTEGER DEFAULT 0,
      archived INTEGER DEFAULT 0,

      labels TEXT,
      attachments TEXT,
      metadata TEXT,

      UNIQUE(source_id, external_id),
      FOREIGN KEY(source_id) REFERENCES sources(id) ON DELETE CASCADE,
      FOREIGN KEY(parent_id) REFERENCES messages(id) ON DELETE SET NULL
    )
  SQL
  
  # Indexes for performance
  @db.execute "CREATE INDEX IF NOT EXISTS idx_messages_source ON messages(source_id)"
  @db.execute "CREATE INDEX IF NOT EXISTS idx_messages_timestamp ON messages(timestamp DESC)"
  @db.execute "CREATE INDEX IF NOT EXISTS idx_messages_thread ON messages(thread_id)"
  @db.execute "CREATE INDEX IF NOT EXISTS idx_messages_read ON messages(read)"
  @db.execute "CREATE INDEX IF NOT EXISTS idx_messages_read_timestamp ON messages(read, timestamp DESC)"
  @db.execute "CREATE INDEX IF NOT EXISTS idx_messages_sender ON messages(sender)"
  
  # Sources table (configured communication sources)
  @db.execute <<-SQL
    CREATE TABLE IF NOT EXISTS sources (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL UNIQUE,
      plugin_type TEXT NOT NULL,
      enabled INTEGER DEFAULT 1,

      config TEXT NOT NULL,
      capabilities TEXT NOT NULL,

      last_sync INTEGER,
      last_error TEXT,

      message_count INTEGER DEFAULT 0,
      created_at INTEGER NOT NULL,
      updated_at INTEGER NOT NULL
    )
  SQL

  @db.execute "CREATE INDEX IF NOT EXISTS idx_sources_enabled ON sources(enabled)"
  @db.execute "CREATE INDEX IF NOT EXISTS idx_sources_plugin_type ON sources(plugin_type)"
  
  # Views table (user-defined filtered views)
  @db.execute <<-SQL
    CREATE TABLE IF NOT EXISTS views (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL UNIQUE,
      key_binding TEXT UNIQUE,

      filters TEXT NOT NULL,

      sort_order TEXT DEFAULT 'timestamp DESC',
      is_remainder INTEGER DEFAULT 0,

      show_count INTEGER DEFAULT 1,
      color INTEGER,
      icon TEXT,

      created_at INTEGER NOT NULL,
      updated_at INTEGER NOT NULL
    )
  SQL

  @db.execute "CREATE INDEX IF NOT EXISTS idx_views_key_binding ON views(key_binding)"

  # Additional tables from spec
  create_additional_tables
  create_default_views
end

#toggle_star(message_id) ⇒ Object 



510
511
512
 
# File 'lib/heathrow/database.rb', line 510

def toggle_star(message_id)
  @db.execute("UPDATE messages SET starred = NOT starred WHERE id = ?", message_id)
end

#transaction(&block) ⇒ Object 



736
737
738
 
# File 'lib/heathrow/database.rb', line 736

def transaction(&block)
  @db.transaction(&block)
end

#update_source(source_id, updates = {}) ⇒ Object 



591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
 
# File 'lib/heathrow/database.rb', line 591

def update_source(source_id, updates = {})
  now = Time.now.to_i
  updates.each do |key, value|
    case key
    when :config
      value = value.to_json if value.is_a?(Hash)
      @db.execute("UPDATE sources SET config = ?, updated_at = ? WHERE id = ?", [value, now, source_id])
    when :last_sync
      @db.execute("UPDATE sources SET last_sync = ?, updated_at = ? WHERE id = ?", [value, now, source_id])
    when :last_error
      @db.execute("UPDATE sources SET last_error = ?, updated_at = ? WHERE id = ?", [value, now, source_id])
    when :enabled
      @db.execute("UPDATE sources SET enabled = ?, updated_at = ? WHERE id = ?", [value ? 1 : 0, now, source_id])
    end
  end
end

#update_source_poll_time(source_id) ⇒ Object 



608
609
610
611
 
# File 'lib/heathrow/database.rb', line 608

def update_source_poll_time(source_id)
  now = Time.now.to_i
  @db.execute("UPDATE sources SET last_sync = ?, updated_at = ? WHERE id = ?", [now, now, source_id])
end