src/persistence.py

"""
================================================================================
PERSISTENCE LAYER - Alert State & STR Reference Management
================================================================================

PURPOSE:
  Local SQLite database for:
    1. Alert metadata (seen/dismissed/confirmed state)
    2. STR reference numbering (sequential counter)
    3. Alert suppression (avoid duplicate notifications)

  Complements the case management database (Supabase) by storing transient
  operational state (alert lifecycle, STR numbering) that doesn't need
  cloud persistence.

KEY RESPONSIBILITIES:
  1. Store alert acknowledgment state (seen, dismissed, confirmed)
  2. Track alert-investigator assignment
  3. Generate sequential STR references (STR-YYYYMMDD-NNNN)
  4. Suppress duplicate alerts within time window
  5. Provide O(1) lookup of alert states

TABLES:

  alert_state
    - alert_id: TEXT PRIMARY KEY (unique alert identifier)
    - seen: INTEGER (0/1) - Has investigator viewed this?
    - dismissed: INTEGER (0/1) - Explicitly dismissed?
    - confirmed: INTEGER (0/1) - Confirmed as suspicious/clean?
    - assigned_to: TEXT - Investigator ID (optional)
    - last_updated: TEXT - ISO timestamp of last change
    - notes: TEXT - Investigator notes on this alert

  str_counter
    - id: INTEGER PRIMARY KEY AUTOINCREMENT
    - date: TEXT - Date in YYYYMMDD format
    - counter: INTEGER - Daily sequential counter

MAIN FUNCTIONS:

  init_db()
    - Create tables if they don't exist
    - Initialize directory structure
    - Safe to call multiple times (idempotent)
    - Called once at application startup

  get_alert_state(alert_id) → dict
    - Fetch alert metadata by ID
    - Returns: {alert_id, seen, dismissed, confirmed, assigned_to, last_updated, notes}
    - If not found: returns empty state with defaults
    - Used for: checking alert lifecycle, displaying in UI

  update_alert_state(alert_id, **kwargs) → None
    - Update one or more alert fields
    - Auto-updates last_updated timestamp
    - Inserts row if doesn't exist (upsert pattern)
    - Used for: marking seen, confirming alerts, assigning to investigator

  get_all_alert_states() → dict
    - Fetch all alert states at once
    - Returns: {alert_id: state_dict, ...}
    - Enables O(1) lookup of alert state in memory
    - Used for: initialization, bulk state checks

  next_str_reference() → str
    - Generate sequential STR reference number
    - Format: STR-YYYYMMDD-NNNN
    - Daily counter resets each day
    - Thread-safe: uses database lock (sequential)
    - Used for: PDF report naming, STR filing identification

  was_recently_suppressed(account, typology, suppress_hours) → bool
    - Check if alert for this account+typology was seen recently
    - Returns: True if found within suppress_hours
    - Simple implementation: pattern-match on alert_id
    - Production: would use proper alert_id lookup
    - Used for: duplicate suppression, notification throttling

STATE MACHINE (Alert Lifecycle):

  New Alert (entry)
    seen=0, dismissed=0, confirmed=0

  Investigator Views Alert
    → seen=1

  Investigator Dismisses (benign)
    → dismissed=1, confirmed=0 (optional)

  Investigator Confirms (suspicious)
    → confirmed=1, dismissed=0 (optional)

  Assigned to Investigator
    → assigned_to="inv_001"

DATABASE OPERATIONS:

  Connections:
    - sqlite3.connect(_get_db_path()) with row_factory = sqlite3.Row
    - Enables dict-like row access: row['alert_id']

  Transactions:
    - Explicit conn.commit() after INSERT/UPDATE
    - Connection auto-closes with context manager

  Upsert Pattern (update_alert_state):
    1. INSERT OR IGNORE: Create row if missing
    2. UPDATE: Modify fields
    3. COMMIT: Persist changes

DATABASE LOCATION:
  - Path: config['data']['alerts_db_path']
  - Typically: data/alerts.db
  - Relative paths resolved to absolute by config_loader

PERFORMANCE CHARACTERISTICS:

  get_alert_state(id):
    - Single indexed lookup
    - O(log n) via PRIMARY KEY index
    - Negligible latency

  update_alert_state(id, **kwargs):
    - Lookup + update
    - O(log n) lookup + O(1) update
    - Database lock during write
    - Suitable for interactive operations

  get_all_alert_states():
    - Full table scan
    - O(n) where n = number of alerts
    - Returns all states for memory-backed lookup
    - Called sparingly (on startup or manual refresh)

  next_str_reference():
    - Lookup today's counter
    - Increment and commit
    - Database lock during write
    - Sequential (no gaps)
    - Suitable for reference generation

STR REFERENCE NUMBERING:

  Format: STR-YYYYMMDD-NNNN
    - STR prefix: Suspicious Transaction Report identifier
    - YYYYMMDD: Date of generation (resets daily)
    - NNNN: 4-digit sequential counter (0001, 0002, ..., 9999)

  Examples:
    - STR-20260531-0001 (May 31, 2026, first report)
    - STR-20260531-0042 (May 31, 2026, 42nd report)
    - STR-20260601-0001 (June 1, 2026, counter resets)

SUPPRESSION LOGIC:

  Alert Suppression:
    - Prevents flooding with duplicate alerts
    - suppress_hours: grace period (default 24)
    - Pattern: Check if alert for same account+typology in recent period
    - Implementation: Basic pattern-match (would improve in production)

DEPENDENCIES:
  - sqlite3: Standard library
  - os: Path creation
  - datetime: Timestamps and time windows
  - src.config_loader: get_config() for database path

USAGE EXAMPLE:

  # Initialize on startup
  from src.persistence import init_db, next_str_reference, update_alert_state
  init_db()

  # Mark alert as seen
  update_alert_state('alert_123', seen=1)

  # Assign to investigator
  update_alert_state('alert_123', assigned_to='inv_001', confirmed=1)

  # Generate STR reference
  str_ref = next_str_reference()  # "STR-20260531-0042"

  # Check suppression
  from src.persistence import was_recently_suppressed
  if not was_recently_suppressed('ACC_123', 'RoundTripping', 24):
    # Send alert to investigator
    pass

NOTES:

  - SQLite is sufficient for this operational state
  - Production might migrate to Redis for speed
  - No schema migrations needed (simple tables)
  - Thread-safe for concurrent reads, serialized writes
  - Backup: include data/alerts.db in regular backups

================================================================================
"""
import sqlite3
import os
from datetime import datetime

from src.config_loader import get_config

_db_path: str = None


def _get_db_path() -> str:
    global _db_path
    if _db_path is None:
        _db_path = get_config()['data']['alerts_db_path']
    return _db_path


def _get_conn() -> sqlite3.Connection:
    conn = sqlite3.connect(_get_db_path())
    conn.row_factory = sqlite3.Row
    return conn


def init_db() -> None:
    """Create tables if they don't exist."""
    os.makedirs(os.path.dirname(_get_db_path()), exist_ok=True)
    with _get_conn() as conn:
        conn.execute("""
            CREATE TABLE IF NOT EXISTS alert_state (
                alert_id TEXT PRIMARY KEY,
                seen INTEGER DEFAULT 0,
                dismissed INTEGER DEFAULT 0,
                confirmed INTEGER DEFAULT 0,
                assigned_to TEXT DEFAULT NULL,
                last_updated TEXT,
                notes TEXT DEFAULT ''
            )
        """)
        conn.execute("""
            CREATE TABLE IF NOT EXISTS str_counter (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                date TEXT NOT NULL,
                counter INTEGER NOT NULL DEFAULT 1
            )
        """)
        conn.commit()


def get_alert_state(alert_id: str) -> dict:
    with _get_conn() as conn:
        row = conn.execute(
            "SELECT * FROM alert_state WHERE alert_id = ?", (alert_id,)
        ).fetchone()
    if row is None:
        return {'alert_id': alert_id, 'seen': False, 'dismissed': False, 'confirmed': False}
    return dict(row)


def update_alert_state(alert_id: str, **kwargs) -> None:
    kwargs['last_updated'] = datetime.now().isoformat()
    fields = ', '.join(f"{k} = ?" for k in kwargs)
    values = list(kwargs.values()) + [alert_id]
    with _get_conn() as conn:
        conn.execute(
            "INSERT OR IGNORE INTO alert_state (alert_id, last_updated) VALUES (?, ?)",
            (alert_id, kwargs['last_updated'])
        )
        conn.execute(f"UPDATE alert_state SET {fields} WHERE alert_id = ?", values)
        conn.commit()


def get_all_alert_states() -> dict:
    """Returns a dict of alert_id -> state dict for O(1) lookup."""
    with _get_conn() as conn:
        rows = conn.execute("SELECT * FROM alert_state").fetchall()
    return {row['alert_id']: dict(row) for row in rows}


def next_str_reference() -> str:
    """Generate a sequential STR reference: STR-YYYYMMDD-NNNN."""
    today = datetime.now().strftime('%Y%m%d')
    with _get_conn() as conn:
        row = conn.execute(
            "SELECT counter FROM str_counter WHERE date = ?", (today,)
        ).fetchone()
        if row is None:
            conn.execute("INSERT INTO str_counter (date, counter) VALUES (?, 1)", (today,))
            counter = 1
        else:
            counter = row['counter'] + 1
            conn.execute(
                "UPDATE str_counter SET counter = ? WHERE date = ?", (counter, today)
            )
        conn.commit()
    return f"STR-{today}-{counter:04d}"


def was_recently_suppressed(account: str, typology: str, suppress_hours: int) -> bool:
    """Return True if this account+typology was seen within suppress_hours."""
    from datetime import timedelta
    cutoff = (datetime.now() - timedelta(hours=suppress_hours)).isoformat()
    with _get_conn() as conn:
        row = conn.execute("""
            SELECT 1 FROM alert_state
            WHERE alert_id LIKE ? AND last_updated > ? AND dismissed = 0
        """, (f"%{account}%", cutoff)).fetchone()
    # Basic suppression via alert_id pattern — production would use a proper lookup
    return row is not None