//! SQLite storage backend for fyi events.

// Allow stderr for warning messages in schema initialization
#![allow(clippy::print_stderr)]

mod migrations;
mod query_builder;
mod row_parsing;
mod schema;
mod sql;

use std::path::Path;
use std::time::Duration;

use chrono::Utc;
use fyi_core::{ApiStats, Event, EventQuery, Order, Storage, StorageError, parse_timestamp_millis};
use rusqlite::{Connection, params};

use query_builder::QueryBuilder;
use row_parsing::row_to_event;

/// SQLite-backed storage for fyi events
pub struct SqliteStorage {
    conn: Connection,
}

impl SqliteStorage {
    /// Open or create a database at the given path
    pub fn open(path: &Path) -> Result<Self, StorageError> {
        // Create parent directory if needed
        if let Some(parent) = path.parent() {
            std::fs::create_dir_all(parent)?;
        }

        let conn = Connection::open(path).map_err(|e| StorageError::Connection(Box::new(e)))?;

        // Enable WAL mode for better concurrent access
        conn.pragma_update(None, "journal_mode", "WAL")
            .map_err(|e| StorageError::Connection(Box::new(e)))?;

        // Set busy timeout to handle concurrent writes gracefully
        conn.busy_timeout(std::time::Duration::from_millis(5000))
            .map_err(|e| StorageError::Connection(Box::new(e)))?;

        schema::init_schema(&conn)?;

        Ok(Self { conn })
    }
}

impl Storage for SqliteStorage {
    fn insert(&self, event: &Event) -> Result<i64, StorageError> {
        self.conn
            .execute(
                r"
                INSERT INTO events (machine_id, timestamp, event_type, session_id, framework,
                    tool_use_id, spawned_agent_id, tool_name, subagent_type, permission_mode,
                    transcript_path, pid, cwd, model, tokens_input, tokens_output,
                    tokens_cache_read, tokens_cache_creation, cost_usd, duration_ms,
                    payload, metadata)
                VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14, ?15, ?16, ?17, ?18, ?19, ?20, ?21, ?22)
                ",
                params![
                    event.machine_id,
                    event.timestamp.timestamp_millis(),
                    event.event_type.to_string(),
                    event.session_id,
                    event.framework,
                    event.tool_use_id,
                    event.spawned_agent_id,
                    event.tool_name,
                    event.subagent_type,
                    event.permission_mode,
                    event.transcript_path,
                    event.pid,
                    event.cwd,
                    event.model,
                    event.tokens_input,
                    event.tokens_output,
                    event.tokens_cache_read,
                    event.tokens_cache_creation,
                    event.cost_usd,
                    event.duration_ms,
                    event.payload,
                    event.metadata,
                ],
            )
            .map_err(|e| StorageError::Query(Box::new(e)))?;

        Ok(self.conn.last_insert_rowid())
    }

    fn query(&self, query: &EventQuery) -> Result<Vec<Event>, StorageError> {
        let mut qb = QueryBuilder::new(
            "SELECT id, machine_id, timestamp, event_type, session_id, framework, tool_use_id, spawned_agent_id, tool_name, subagent_type, permission_mode, transcript_path, pid, cwd, model, tokens_input, tokens_output, tokens_cache_read, tokens_cache_creation, cost_usd, duration_ms, payload, metadata FROM events",
        );

        // Apply filters
        if let Some(ref session_id) = query.session_id {
            qb.and_eq("session_id", session_id.clone());
        }
        if let Some(ref event_type) = query.event_type {
            qb.and_eq_upper("event_type", event_type.clone());
        }
        if let Some(ref permission_mode) = query.permission_mode {
            qb.and_eq("permission_mode", permission_mode.clone());
        }
        if let Some(ref framework) = query.framework {
            qb.and_eq("framework", framework.clone());
        }
        if let Some(after_ts) = query.after_ts {
            qb.and_gt("timestamp", after_ts.timestamp_millis());
        }
        if let Some(before_ts) = query.before_ts {
            qb.and_lt("timestamp", before_ts.timestamp_millis());
        }
        if let Some(after_id) = query.after_id {
            qb.and_gt("id", after_id);
        }

        // Filter for API requests only or exclude them
        if query.api_requests_only {
            qb.and_eq_upper("event_type", "ApiRequest".to_string());
        } else if query.exclude_api_requests {
            qb.and_neq_upper("event_type", "ApiRequest".to_string());
        }

        // Use EventQuery helper methods for ordering
        let orders_by_id = query.orders_by_id();
        let direction = query.effective_direction();
        let order_clause = match (orders_by_id, direction) {
            (true, Order::Asc) => "id ASC",
            (true, Order::Desc) => "id DESC",
            (false, Order::Asc) => "timestamp ASC",
            (false, Order::Desc) => "timestamp DESC",
        };
        qb.order_by(order_clause);

        // Apply limit if specified
        if let Some(limit) = query.limit {
            qb.limit(limit);
        }

        let (sql, params) = qb.build();

        let mut stmt = self
            .conn
            .prepare(&sql)
            .map_err(|e| StorageError::Query(Box::new(e)))?;

        let events = stmt
            .query_map(params.as_slice(), row_to_event)
            .map_err(|e| StorageError::Query(Box::new(e)))?
            .collect::<Result<Vec<_>, _>>()
            .map_err(|e| StorageError::Query(Box::new(e)))?;

        Ok(events)
    }

    fn gc(&self, retention: Duration) -> Result<usize, StorageError> {
        let chrono_retention =
            chrono::Duration::from_std(retention).map_err(|e| StorageError::Query(Box::new(e)))?;
        let cutoff = (Utc::now() - chrono_retention).timestamp_millis();

        // Delete events older than retention period (includes API requests now)
        let deleted = self
            .conn
            .execute("DELETE FROM events WHERE timestamp < ?1", [cutoff])
            .map_err(|e| StorageError::Query(Box::new(e)))?;

        Ok(deleted)
    }

    fn count_expired(&self, retention: Duration) -> Result<usize, StorageError> {
        let chrono_retention =
            chrono::Duration::from_std(retention).map_err(|e| StorageError::Query(Box::new(e)))?;
        let cutoff = (Utc::now() - chrono_retention).timestamp_millis();

        let count: usize = self
            .conn
            .query_row(
                "SELECT COUNT(*) FROM events WHERE timestamp < ?1",
                [cutoff],
                |row| row.get(0),
            )
            .map_err(|e| StorageError::Query(Box::new(e)))?;

        Ok(count)
    }

    fn count(&self) -> Result<usize, StorageError> {
        let count: usize = self
            .conn
            .query_row("SELECT COUNT(*) FROM events", [], |row| row.get(0))
            .map_err(|e| StorageError::Query(Box::new(e)))?;

        Ok(count)
    }

    fn api_stats(&self, session_id: &str) -> Result<Option<ApiStats>, StorageError> {
        // Query API request events (event_type = 'ApiRequest') for the session
        let result = self.conn.query_row(
            r"
            SELECT
                COUNT(*) as request_count,
                COALESCE(SUM(tokens_input), 0) as tokens_input,
                COALESCE(SUM(tokens_output), 0) as tokens_output,
                COALESCE(SUM(tokens_cache_read), 0) as tokens_cache_read,
                COALESCE(SUM(tokens_cache_creation), 0) as tokens_cache_creation,
                COALESCE(SUM(cost_usd), 0.0) as cost_usd,
                MIN(timestamp) as first_request_ts,
                MAX(timestamp) as last_request_ts,
                -- Context size = input + cache_read + cache_creation (input side of each request)
                COALESCE(MAX(COALESCE(tokens_input, 0) + COALESCE(tokens_cache_read, 0) + COALESCE(tokens_cache_creation, 0)), 0) as context_peak,
                -- Latest context from most recent request
                COALESCE((
                    SELECT COALESCE(tokens_input, 0) + COALESCE(tokens_cache_read, 0) + COALESCE(tokens_cache_creation, 0)
                    FROM events
                    WHERE session_id = ?1 AND UPPER(event_type) = 'APIREQUEST'
                    ORDER BY timestamp DESC
                    LIMIT 1
                ), 0) as context_latest
            FROM events
            WHERE session_id = ?1 AND UPPER(event_type) = 'APIREQUEST'
            ",
            [session_id],
            |row| {
                let first_ts_millis: Option<i64> = row.get(6)?;
                let last_ts_millis: Option<i64> = row.get(7)?;

                Ok(ApiStats {
                    session_id: session_id.to_string(),
                    request_count: row.get(0)?,
                    tokens_input: row.get(1)?,
                    tokens_output: row.get(2)?,
                    tokens_cache_read: row.get(3)?,
                    tokens_cache_creation: row.get(4)?,
                    cost_usd: row.get(5)?,
                    first_request_ts: first_ts_millis.and_then(parse_timestamp_millis),
                    last_request_ts: last_ts_millis.and_then(parse_timestamp_millis),
                    context_peak: row.get(8)?,
                    context_latest: row.get(9)?,
                })
            },
        );

        match result {
            Ok(stats) if stats.request_count > 0 => Ok(Some(stats)),
            Ok(_) => Ok(None), // No requests found
            Err(rusqlite::Error::QueryReturnedNoRows) => Ok(None),
            Err(e) => Err(StorageError::Query(Box::new(e))),
        }
    }
}

#[cfg(test)]
mod tests;
