//! Raw SQL definitions for the fyi database schema.
//!
//! This module contains all SQL string constants used for:
//! - Table definitions
//! - Index definitions
//! - Migration queries
//!
//! Centralizing SQL here makes it easier to review schema changes
//! and ensures consistency across the codebase.

/// SQL schema for creating the unified events table.
/// This table contains both hook events and API requests (distinguished by event_type).
pub(crate) const EVENTS_TABLE: &str = r"
CREATE TABLE IF NOT EXISTS events (
    id INTEGER PRIMARY KEY,
    machine_id TEXT NOT NULL,
    timestamp INTEGER NOT NULL,
    event_type TEXT NOT NULL,
    session_id TEXT NOT NULL,
    framework TEXT,

    -- Correlation fields (hook events)
    tool_use_id TEXT,
    spawned_agent_id TEXT,

    -- Common denormalized fields (hook events)
    tool_name TEXT,
    subagent_type TEXT,
    permission_mode TEXT,
    transcript_path TEXT,

    -- Process context (hook events)
    pid INTEGER,
    cwd TEXT,

    -- API request fields (NULL for hook events)
    model TEXT,
    tokens_input INTEGER,
    tokens_output INTEGER,
    tokens_cache_read INTEGER,
    tokens_cache_creation INTEGER,
    cost_usd REAL,
    duration_ms INTEGER,

    -- Full payload + extensible metadata
    payload TEXT,
    metadata TEXT
);

CREATE INDEX IF NOT EXISTS idx_events_session_id ON events(session_id);
CREATE INDEX IF NOT EXISTS idx_events_timestamp ON events(timestamp);
CREATE INDEX IF NOT EXISTS idx_events_event_type ON events(event_type);
CREATE INDEX IF NOT EXISTS idx_events_machine_id ON events(machine_id);
";

/// Partial index for tool_use_id (only indexes non-NULL values).
pub(crate) const TOOL_USE_INDEX: &str = "CREATE INDEX IF NOT EXISTS idx_events_tool_use_id ON events(tool_use_id) WHERE tool_use_id IS NOT NULL";

/// Partial index for spawned_agent_id (only indexes non-NULL values).
pub(crate) const SPAWNED_AGENT_INDEX: &str = "CREATE INDEX IF NOT EXISTS idx_events_spawned_agent_id ON events(spawned_agent_id) WHERE spawned_agent_id IS NOT NULL";

/// Partial index for tool_name (only indexes non-NULL values).
pub(crate) const TOOL_NAME_INDEX: &str = "CREATE INDEX IF NOT EXISTS idx_events_tool_name ON events(tool_name) WHERE tool_name IS NOT NULL";

/// Partial index for subagent_type (only indexes non-NULL values).
pub(crate) const SUBAGENT_TYPE_INDEX: &str = "CREATE INDEX IF NOT EXISTS idx_events_subagent_type ON events(subagent_type) WHERE subagent_type IS NOT NULL";

/// Migration: Drop old indexes with inconsistent naming.
/// The new indexes will be created by the main schema.
pub(crate) const DROP_OLD_INDEXES: &str = r"
DROP INDEX IF EXISTS idx_session;
DROP INDEX IF EXISTS idx_timestamp;
DROP INDEX IF EXISTS idx_event_type;
DROP INDEX IF EXISTS idx_machine_id;
DROP INDEX IF EXISTS idx_tool_use;
DROP INDEX IF EXISTS idx_spawned_agent;
DROP INDEX IF EXISTS idx_tool_name;
DROP INDEX IF EXISTS idx_subagent_type;
DROP INDEX IF EXISTS idx_api_session;
DROP INDEX IF EXISTS idx_api_timestamp;
DROP INDEX IF EXISTS idx_api_machine_id;
DROP INDEX IF EXISTS idx_api_requests_session_id;
DROP INDEX IF EXISTS idx_api_requests_timestamp;
DROP INDEX IF EXISTS idx_api_requests_machine_id;
";

/// Migration v3->v4: Add API request columns to events table.
pub(crate) const MIGRATE_API_COLUMNS: &str = r"
ALTER TABLE events ADD COLUMN model TEXT;
ALTER TABLE events ADD COLUMN tokens_input INTEGER;
ALTER TABLE events ADD COLUMN tokens_output INTEGER;
ALTER TABLE events ADD COLUMN tokens_cache_read INTEGER;
ALTER TABLE events ADD COLUMN tokens_cache_creation INTEGER;
ALTER TABLE events ADD COLUMN cost_usd REAL;
ALTER TABLE events ADD COLUMN duration_ms INTEGER;
";

/// Migration v3->v4: Migrate data from api_requests to events.
pub(crate) const MIGRATE_API_REQUESTS: &str = r"
INSERT INTO events (machine_id, timestamp, event_type, session_id, framework, model, tokens_input, tokens_output, tokens_cache_read, tokens_cache_creation, cost_usd, duration_ms, metadata)
SELECT machine_id, timestamp, 'ApiRequest', session_id, framework, model, tokens_input, tokens_output, tokens_cache_read, tokens_cache_creation, cost_usd, duration_ms, metadata
FROM api_requests;
";
