-- Cache statistics table
CREATE TABLE IF NOT EXISTS cache_stats (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    cache_key TEXT NOT NULL,
    cache_type TEXT NOT NULL,  -- 'file_tree'
    event_type TEXT NOT NULL,  -- 'hit', 'miss', 'invalidate'
    user_id INTEGER,
    response_time_ms INTEGER,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- Index for efficient queries
CREATE INDEX IF NOT EXISTS idx_cache_stats_created_at ON cache_stats(created_at);
CREATE INDEX IF NOT EXISTS idx_cache_stats_type_event ON cache_stats(cache_type, event_type);

-- Auto-cleanup trigger: delete logs older than 7 days
CREATE TRIGGER IF NOT EXISTS cleanup_old_cache_stats
AFTER INSERT ON cache_stats
BEGIN
    DELETE FROM cache_stats
    WHERE created_at < datetime('now', '-7 days');
END;