-- Migration: Conversation System Improvements
-- Date: 2025-11-12
-- Description: Add tables for character memory and API usage tracking

-- Character Memory Table
-- Stores long-term memory facts for NPCs
CREATE TABLE IF NOT EXISTS character_memory (
    id INT AUTO_INCREMENT PRIMARY KEY,
    character_id VARCHAR(36) NOT NULL,
    player_id VARCHAR(36) NOT NULL,
    fact TEXT NOT NULL,
    learned_date DATETIME NOT NULL,
    importance TINYINT DEFAULT 5,
    conversation_id VARCHAR(36),
    INDEX idx_char_player (character_id, player_id),
    INDEX idx_importance (importance DESC),
    INDEX idx_learned_date (learned_date DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- API Usage Tracking Table
-- Tracks API costs and token usage for all OpenAI calls
CREATE TABLE IF NOT EXISTS api_usage (
    id INT AUTO_INCREMENT PRIMARY KEY,
    player_id VARCHAR(36) NOT NULL,
    conversation_id VARCHAR(36),
    endpoint VARCHAR(50) NOT NULL,
    model VARCHAR(50) NOT NULL,
    prompt_tokens INT NOT NULL,
    completion_tokens INT NOT NULL,
    total_tokens INT NOT NULL,
    cost_usd DECIMAL(10, 6) NOT NULL,
    created_date DATETIME NOT NULL,
    purpose VARCHAR(100),
    INDEX idx_player (player_id),
    INDEX idx_created (created_date),
    INDEX idx_conversation (conversation_id),
    INDEX idx_model (model)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Optional: Add sample queries for monitoring

-- Get player's API costs for last 30 days
-- SELECT player_id, SUM(cost_usd) as total_cost, SUM(total_tokens) as total_tokens
-- FROM api_usage
-- WHERE created_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)
-- GROUP BY player_id
-- ORDER BY total_cost DESC;

-- Get usage breakdown by purpose
-- SELECT purpose, COUNT(*) as calls, SUM(cost_usd) as total_cost, AVG(cost_usd) as avg_cost
-- FROM api_usage
-- WHERE created_date >= DATE_SUB(NOW(), INTERVAL 7 DAY)
-- GROUP BY purpose
-- ORDER BY total_cost DESC;

-- Get character memory for specific relationship
-- SELECT * FROM character_memory
-- WHERE character_id = 'CHARACTER_ID' AND player_id = 'PLAYER_ID'
-- ORDER BY importance DESC, learned_date DESC
-- LIMIT 10;
