import sqlite3
import os

DB_PATH = os.path.join(os.path.dirname(__file__), "dashboard.db")


def get_db():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("PRAGMA foreign_keys=ON")
    return conn


def init_db():
    conn = get_db()
    conn.executescript("""
        CREATE TABLE IF NOT EXISTS projects (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            path TEXT UNIQUE NOT NULL,
            name TEXT NOT NULL
        );

        CREATE TABLE IF NOT EXISTS sessions (
            id TEXT PRIMARY KEY,
            project_id INTEGER REFERENCES projects(id),
            file_path TEXT NOT NULL,
            file_mtime REAL NOT NULL,
            first_msg_at TEXT,
            last_msg_at TEXT,
            wall_seconds REAL DEFAULT 0,
            active_seconds REAL DEFAULT 0,
            human_prompts INTEGER DEFAULT 0,
            api_calls INTEGER DEFAULT 0,
            subagent_api_calls INTEGER DEFAULT 0,
            input_tokens INTEGER DEFAULT 0,
            output_tokens INTEGER DEFAULT 0,
            cache_read_tokens INTEGER DEFAULT 0,
            cache_write_tokens INTEGER DEFAULT 0,
            lines_written INTEGER DEFAULT 0,
            cost REAL DEFAULT 0,
            model_usage TEXT DEFAULT '{}',
            parsed_at TEXT
        );

        CREATE TABLE IF NOT EXISTS prompts (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            session_id TEXT REFERENCES sessions(id),
            project_id INTEGER REFERENCES projects(id),
            timestamp TEXT NOT NULL,
            type TEXT NOT NULL,
            model TEXT,
            input_tokens INTEGER DEFAULT 0,
            output_tokens INTEGER DEFAULT 0,
            cache_read_tokens INTEGER DEFAULT 0,
            cache_write_tokens INTEGER DEFAULT 0,
            content_preview TEXT
        );

        CREATE INDEX IF NOT EXISTS idx_prompts_session ON prompts(session_id);
        CREATE INDEX IF NOT EXISTS idx_prompts_project ON prompts(project_id);
        CREATE INDEX IF NOT EXISTS idx_prompts_timestamp ON prompts(timestamp);
        CREATE INDEX IF NOT EXISTS idx_sessions_project ON sessions(project_id);

        CREATE TABLE IF NOT EXISTS players (
            name TEXT PRIMARY KEY,
            total_prompts INTEGER DEFAULT 0,
            total_api_calls INTEGER DEFAULT 0,
            total_active_hours REAL DEFAULT 0,
            total_input_tokens INTEGER DEFAULT 0,
            total_output_tokens INTEGER DEFAULT 0,
            total_cache_read INTEGER DEFAULT 0,
            total_cache_write INTEGER DEFAULT 0,
            total_lines_written INTEGER DEFAULT 0,
            total_cost REAL DEFAULT 0,
            total_sessions INTEGER DEFAULT 0,
            total_projects INTEGER DEFAULT 0,
            model_usage TEXT DEFAULT '{}',
            projects_data TEXT DEFAULT '[]',
            earliest_session TEXT,
            latest_session TEXT,
            last_seen TEXT
        );
    """)
    conn.commit()
    conn.close()