app/database.py

"""
SQLite Database Module
─────────────────────
Handles connection, initialization, and health checks.

Sources:
  - Python sqlite3 docs: https://docs.python.org/3/library/sqlite3.html
  - Litestream tips: https://litestream.io/tips/
  - SQLite WAL mode: https://www.sqlite.org/wal.html
  - SQLite PRAGMA: https://www.sqlite.org/pragma.html
"""

import sqlite3
import os
import threading
import time

DB_PATH = "/tmp/data/app.db"

# Thread-local storage for connections
_local = threading.local()


def get_db() -> sqlite3.Connection:
    """
    Get a thread-safe database connection.
    
    Each thread gets its own connection (SQLite requirement for WAL mode).
    Connections are cached per-thread to avoid overhead.
    
    PRAGMA explanations:
    - journal_mode=WAL    → Required for Litestream. Allows concurrent reads.
    - busy_timeout=5000   → Wait 5s if another thread is writing.
    - synchronous=NORMAL  → Safe with WAL, faster than FULL.
    - cache_size=-20000   → 20MB page cache in memory.
    - foreign_keys=ON     → Enforce relational integrity.
    - wal_autocheckpoint=1000 → Checkpoint every 1000 pages (~4MB).
    """
    if not hasattr(_local, 'conn') or _local.conn is None:
        os.makedirs(os.path.dirname(DB_PATH), exist_ok=True)
        
        conn = sqlite3.connect(DB_PATH, check_same_thread=False)
        conn.row_factory = sqlite3.Row
        
        # ── Critical PRAGMAs ──
        conn.execute("PRAGMA journal_mode=WAL")
        conn.execute("PRAGMA busy_timeout=5000")
        conn.execute("PRAGMA synchronous=NORMAL")
        conn.execute("PRAGMA cache_size=-20000")
        conn.execute("PRAGMA foreign_keys=ON")
        conn.execute("PRAGMA wal_autocheckpoint=1000")
        
        _local.conn = conn
    
    return _local.conn


def close_db():
    """Close the thread-local connection."""
    if hasattr(_local, 'conn') and _local.conn:
        _local.conn.close()
        _local.conn = None


def init_db():
    """
    Create tables on first run.
    Safe to call multiple times (IF NOT EXISTS).
    """
    conn = get_db()
    conn.executescript("""
        -- ═══════════════════════════════════════════
        --  USERS
        -- ═══════════════════════════════════════════
        CREATE TABLE IF NOT EXISTS users (
            id TEXT PRIMARY KEY,
            email TEXT UNIQUE NOT NULL,
            name TEXT,
            avatar_url TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );
        
        -- ═══════════════════════════════════════════
        --  PROJECTS
        -- ═══════════════════════════════════════════
        CREATE TABLE IF NOT EXISTS projects (
            id TEXT PRIMARY KEY,
            user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
            name TEXT NOT NULL,
            description TEXT,
            design_tokens JSON,
            status TEXT DEFAULT 'draft' CHECK(status IN ('draft','active','archived')),
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );
        
        -- ═══════════════════════════════════════════
        --  COLOR PALETTES
        -- ═══════════════════════════════════════════
        CREATE TABLE IF NOT EXISTS color_palettes (
            id TEXT PRIMARY KEY,
            project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
            name TEXT NOT NULL,
            colors JSON NOT NULL,
            mood TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );
        
        -- ═══════════════════════════════════════════
        --  DESIGN ASSETS (references to HF Bucket files)
        -- ═══════════════════════════════════════════
        CREATE TABLE IF NOT EXISTS design_assets (
            id TEXT PRIMARY KEY,
            project_id TEXT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
            asset_type TEXT NOT NULL,
            bucket_path TEXT NOT NULL,
            file_size INTEGER,
            metadata JSON,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );
        
        -- ═══════════════════════════════════════════
        --  BACKUP LOG (tracks backup history)
        -- ═══════════════════════════════════════════
        CREATE TABLE IF NOT EXISTS backup_log (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            backup_type TEXT NOT NULL,
            file_path TEXT,
            file_size INTEGER,
            status TEXT DEFAULT 'success',
            error_message TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );
        
        -- ═══════════════════════════════════════════
        --  INDEXES
        -- ═══════════════════════════════════════════
        CREATE INDEX IF NOT EXISTS idx_projects_user 
            ON projects(user_id);
        CREATE INDEX IF NOT EXISTS idx_palettes_project 
            ON color_palettes(project_id);
        CREATE INDEX IF NOT EXISTS idx_assets_project 
            ON design_assets(project_id);
        CREATE INDEX IF NOT EXISTS idx_backup_log_date 
            ON backup_log(created_at);
    """)
    conn.commit()


def integrity_check() -> dict:
    """
    Run SQLite integrity check.
    Returns health status of the database.
    
    Source: https://www.sqlite.org/pragma.html#pragma_integrity_check
    """
    conn = get_db()
    result = conn.execute("PRAGMA integrity_check").fetchone()
    wal_size = conn.execute("PRAGMA wal_checkpoint(PASSIVE)").fetchone()
    page_count = conn.execute("PRAGMA page_count").fetchone()[0]
    page_size = conn.execute("PRAGMA page_size").fetchone()[0]
    
    db_size = page_count * page_size
    
    return {
        "integrity": result[0],  # "ok" if healthy
        "db_size_bytes": db_size,
        "db_size_mb": round(db_size / (1024 * 1024), 2),
        "page_count": page_count,
        "page_size": page_size,
        "wal_pages": wal_size[1] if wal_size else 0,
        "journal_mode": conn.execute("PRAGMA journal_mode").fetchone()[0],
    }