-- Main log entries table with comprehensive structured fields
CREATE TABLE IF NOT EXISTS logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    
    -- Core fields
    timestamp REAL NOT NULL,              -- Unix timestamp with microseconds
    level INTEGER NOT NULL,               -- 0=App, 1=Error, 2=Warning, 3=Info, 4=Debug
    source TEXT NOT NULL,                 -- Module/source name (e.g., 'myapp.db.connection')
    message TEXT NOT NULL,                -- Full log message, no truncation
    
    -- Error classification
    error_type TEXT,                      -- Exception name or error classification
    error_code TEXT,                      -- Specific error code if applicable
    error_hash TEXT,                      -- Hash for grouping similar errors
    stack_trace TEXT,                     -- Full stack trace
    
    -- Execution context
    pid INTEGER,                          -- Process ID
    ppid INTEGER,                         -- Parent process ID
    thread_id TEXT,                       -- Thread/fiber ID for Eio
    fiber_id TEXT,                        -- Specific Eio fiber ID
    domain_id INTEGER,                    -- OCaml domain ID for multicore
    
    -- Request/Session tracking
    session_id TEXT,                      -- Session identifier
    request_id TEXT,                      -- Request identifier
    trace_id TEXT,                        -- Distributed tracing ID
    span_id TEXT,                         -- Span ID for tracing
    parent_span_id TEXT,                  -- Parent span for trace hierarchy
    
    -- Source location
    file_path TEXT,                       -- Full file path
    file_name TEXT,                       -- Just the filename
    line_number INTEGER,                  -- Line number in source
    column_number INTEGER,                -- Column number if available
    function_name TEXT,                   -- Function/method name
    module_path TEXT,                     -- Full module path (e.g., 'Myapp.Db.Connection')
    
    -- Performance metrics
    duration_ms REAL,                     -- Operation duration in milliseconds
    memory_before INTEGER,                -- Memory usage before operation (bytes)
    memory_after INTEGER,                 -- Memory usage after operation (bytes)
    memory_delta INTEGER,                 -- Memory change
    cpu_time_ms REAL,                     -- CPU time consumed
    
    -- Network context
    client_ip TEXT,                       -- Client IP address
    server_ip TEXT,                       -- Server IP address
    host_name TEXT,                       -- Hostname
    port INTEGER,                         -- Port number
    protocol TEXT,                        -- Protocol (HTTP, gRPC, etc.)
    method TEXT,                          -- HTTP method or RPC method
    path TEXT,                            -- Request path or endpoint
    status_code INTEGER,                  -- Response status code
    bytes_sent INTEGER,                   -- Bytes sent
    bytes_received INTEGER,               -- Bytes received
    
    -- User context
    user_id TEXT,                         -- User identifier
    tenant_id TEXT,                       -- Tenant ID for multi-tenant apps
    organization_id TEXT,                 -- Organization ID
    
    -- System context
    os_name TEXT,                         -- Operating system name
    os_version TEXT,                      -- OS version
    ocaml_version TEXT,                   -- OCaml compiler version
    app_version TEXT,                     -- Application version
    environment TEXT,                     -- Environment (dev, staging, prod)
    region TEXT,                          -- Deployment region
    container_id TEXT,                    -- Docker/container ID
    k8s_pod TEXT,                         -- Kubernetes pod name
    k8s_namespace TEXT,                   -- Kubernetes namespace
    
    -- Metadata fields (JSON for flexibility)
    tags TEXT,                            -- JSON array of tags
    labels TEXT,                          -- JSON object of key-value labels
    context TEXT,                         -- JSON object with arbitrary context
    custom_fields TEXT,                   -- JSON object for app-specific fields
    
    -- Audit fields
    created_at REAL DEFAULT (julianday('now')),
    indexed_at REAL                       -- When added to FTS index
);

-- Full-text search index on all text fields
CREATE VIRTUAL TABLE IF NOT EXISTS logs_fts USING fts5(
    message,
    error_type,
    error_code,
    source,
    file_path,
    function_name,
    module_path,
    path,
    method,
    stack_trace,
    tags,
    labels,
    context,
    custom_fields,
    content=logs,
    content_rowid=id,
    tokenize='porter unicode61'
);

-- Pattern tracking for error analysis
CREATE TABLE IF NOT EXISTS patterns (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    pattern_hash TEXT UNIQUE NOT NULL,    -- Hash of normalized pattern
    pattern TEXT NOT NULL,                -- Normalized pattern template
    pattern_type TEXT,                    -- Type of pattern (error, warning, anomaly)
    
    -- Occurrence tracking
    first_seen REAL NOT NULL,
    last_seen REAL NOT NULL,
    occurrence_count INTEGER DEFAULT 1,
    unique_sources INTEGER DEFAULT 1,     -- Number of unique sources
    unique_sessions INTEGER DEFAULT 1,    -- Number of unique sessions affected
    
    -- Statistical analysis
    avg_duration_ms REAL,                 -- Average duration when this occurs
    max_duration_ms REAL,                 -- Maximum duration observed
    min_duration_ms REAL,                 -- Minimum duration observed
    
    -- Trend analysis
    hourly_rate REAL,                     -- Occurrences per hour
    daily_rate REAL,                      -- Occurrences per day
    acceleration_rate REAL,               -- Rate of increase/decrease
    
    -- Claude consultation tracking
    severity_score REAL,                  -- Calculated severity (0-10)
    impact_score REAL,                    -- Business impact score
    last_consultation REAL,               -- When Claude last reviewed
    consultation_count INTEGER DEFAULT 0, -- Number of consultations
    fix_attempted BOOLEAN DEFAULT 0,      -- Whether a fix was attempted
    fix_successful BOOLEAN DEFAULT 0,     -- Whether the fix worked
    
    -- Metadata
    sample_log_ids TEXT,                  -- JSON array of example log IDs
    related_patterns TEXT,                -- JSON array of related pattern IDs
    notes TEXT,                           -- Human or Claude notes
    
    created_at REAL DEFAULT (julianday('now')),
    updated_at REAL DEFAULT (julianday('now'))
);

-- Time-series data for pattern analysis
CREATE TABLE IF NOT EXISTS pattern_metrics (
    pattern_id INTEGER REFERENCES patterns(id) ON DELETE CASCADE,
    timestamp REAL NOT NULL,              -- Bucket timestamp (e.g., hour boundary)
    bucket_size INTEGER NOT NULL,         -- Bucket size in seconds (3600 for hourly)
    
    -- Metrics for this time bucket
    occurrence_count INTEGER DEFAULT 0,
    unique_sources INTEGER DEFAULT 0,
    unique_sessions INTEGER DEFAULT 0,
    avg_duration_ms REAL,
    max_duration_ms REAL,
    error_rate REAL,                      -- Errors per second in this bucket
    
    PRIMARY KEY (pattern_id, timestamp, bucket_size)
);

-- Session tracking for correlation
CREATE TABLE IF NOT EXISTS sessions (
    session_id TEXT PRIMARY KEY,
    start_time REAL NOT NULL,
    end_time REAL,
    duration_ms REAL,
    
    -- Session metadata
    user_id TEXT,
    tenant_id TEXT,
    client_ip TEXT,
    user_agent TEXT,
    
    -- Session metrics
    log_count INTEGER DEFAULT 0,
    error_count INTEGER DEFAULT 0,
    warning_count INTEGER DEFAULT 0,
    
    -- Performance metrics
    total_duration_ms REAL,
    total_memory_bytes INTEGER,
    
    created_at REAL DEFAULT (julianday('now'))
);

-- System metrics snapshot table
CREATE TABLE IF NOT EXISTS system_metrics (
    timestamp REAL PRIMARY KEY,
    
    -- CPU metrics
    cpu_usage_percent REAL,
    cpu_user_percent REAL,
    cpu_system_percent REAL,
    cpu_idle_percent REAL,
    load_avg_1m REAL,
    load_avg_5m REAL,
    load_avg_15m REAL,
    
    -- Memory metrics
    memory_total_bytes INTEGER,
    memory_used_bytes INTEGER,
    memory_free_bytes INTEGER,
    memory_available_bytes INTEGER,
    swap_used_bytes INTEGER,
    swap_free_bytes INTEGER,
    
    -- Disk metrics
    disk_read_bytes_per_sec REAL,
    disk_write_bytes_per_sec REAL,
    disk_usage_percent REAL,
    
    -- Network metrics
    network_rx_bytes_per_sec REAL,
    network_tx_bytes_per_sec REAL,
    network_connections INTEGER,
    
    -- Process metrics
    process_count INTEGER,
    thread_count INTEGER,
    fiber_count INTEGER,
    
    -- GC metrics (OCaml specific)
    gc_minor_collections INTEGER,
    gc_major_collections INTEGER,
    gc_heap_words INTEGER,
    gc_live_words INTEGER,
    
    created_at REAL DEFAULT (julianday('now'))
);

-- Comprehensive indexes for efficient querying
CREATE INDEX IF NOT EXISTS idx_logs_timestamp ON logs(timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_logs_level_timestamp ON logs(level, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_logs_source_timestamp ON logs(source, timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_logs_error_hash ON logs(error_hash) WHERE error_hash IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_logs_session_id ON logs(session_id) WHERE session_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_logs_request_id ON logs(request_id) WHERE request_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_logs_trace_id ON logs(trace_id) WHERE trace_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_logs_user_id ON logs(user_id) WHERE user_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_logs_error_type ON logs(error_type) WHERE error_type IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_logs_duration ON logs(duration_ms) WHERE duration_ms IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_logs_status_code ON logs(status_code) WHERE status_code IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_patterns_last_seen ON patterns(last_seen DESC);
CREATE INDEX IF NOT EXISTS idx_patterns_severity ON patterns(severity_score DESC) WHERE severity_score IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_patterns_count ON patterns(occurrence_count DESC);
CREATE INDEX IF NOT EXISTS idx_pattern_metrics_timestamp ON pattern_metrics(timestamp DESC);

-- Triggers to maintain FTS and update timestamps
CREATE TRIGGER IF NOT EXISTS logs_fts_insert AFTER INSERT ON logs
BEGIN
    INSERT INTO logs_fts(rowid, message, error_type, error_code, source, 
                        file_path, function_name, module_path, path, method,
                        stack_trace, tags, labels, context, custom_fields)
    VALUES (new.id, new.message, new.error_type, new.error_code, new.source,
            new.file_path, new.function_name, new.module_path, new.path, new.method,
            new.stack_trace, new.tags, new.labels, new.context, new.custom_fields);
    
    UPDATE logs SET indexed_at = julianday('now') WHERE id = new.id;
END;

CREATE TRIGGER IF NOT EXISTS patterns_update_timestamp AFTER UPDATE ON patterns
BEGIN
    UPDATE patterns SET updated_at = julianday('now') WHERE id = new.id;
END;

-- Views for common queries
CREATE VIEW IF NOT EXISTS recent_errors AS
SELECT 
    error_hash,
    error_type,
    error_code,
    COUNT(*) as count,
    COUNT(DISTINCT session_id) as affected_sessions,
    COUNT(DISTINCT user_id) as affected_users,
    MAX(timestamp) as last_seen,
    MIN(timestamp) as first_seen,
    GROUP_CONCAT(DISTINCT source) as sources,
    AVG(duration_ms) as avg_duration_ms,
    message as sample_message
FROM logs
WHERE level <= 2  -- Error and Warning
    AND timestamp > julianday('now') - 1  -- Last 24 hours
GROUP BY error_hash
ORDER BY count DESC;

CREATE VIEW IF NOT EXISTS error_trends AS
SELECT 
    date(timestamp) as day,
    level,
    COUNT(*) as count,
    COUNT(DISTINCT error_hash) as unique_errors,
    COUNT(DISTINCT session_id) as affected_sessions,
    AVG(duration_ms) as avg_duration_ms
FROM logs
WHERE level <= 2
GROUP BY date(timestamp), level
ORDER BY day DESC, level;

CREATE VIEW IF NOT EXISTS slow_operations AS
SELECT 
    source,
    function_name,
    COUNT(*) as count,
    AVG(duration_ms) as avg_duration_ms,
    MAX(duration_ms) as max_duration_ms,
    MIN(duration_ms) as min_duration_ms,
    SUM(duration_ms) as total_duration_ms
FROM logs
WHERE duration_ms IS NOT NULL
GROUP BY source, function_name
HAVING avg_duration_ms > 100  -- Operations slower than 100ms
ORDER BY avg_duration_ms DESC;