use anyhow::{Context, Result};
use rusqlite::Connection;

const COST_PER_GB: f64 = 0.005;

pub async fn show(database: &std::path::Path) -> Result<()> {
    let conn = Connection::open(database).context("Failed to open database")?;

    let developer_count: i64 = conn
        .query_row("SELECT COUNT(*) FROM developers", [], |row| row.get(0))
        .unwrap_or(0);

    let game_count: i64 = conn
        .query_row("SELECT COUNT(*) FROM games", [], |row| row.get(0))
        .unwrap_or(0);

    let gamer_count: i64 = conn
        .query_row("SELECT COUNT(*) FROM gamers", [], |row| row.get(0))
        .unwrap_or(0);

    let save_count: i64 = conn
        .query_row("SELECT COUNT(*) FROM saves", [], |row| row.get(0))
        .unwrap_or(0);

    let version_count: i64 = conn
        .query_row("SELECT COUNT(*) FROM save_versions", [], |row| row.get(0))
        .unwrap_or(0);

    let unused_invites: i64 = conn
        .query_row(
            "SELECT COUNT(*) FROM invites WHERE used_by IS NULL",
            [],
            |row| row.get(0),
        )
        .unwrap_or(0);

    let total_bytes: i64 = conn
        .query_row(
            "SELECT COALESCE(SUM(used_bytes), 0) FROM gamer_quotas",
            [],
            |row| row.get(0),
        )
        .unwrap_or(0);

    let total_gb = total_bytes as f64 / (1024.0 * 1024.0 * 1024.0);
    let total_cost = total_gb * COST_PER_GB;

    println!("Scratchback Statistics");
    println!("======================");
    println!("Developers: {}", developer_count);
    println!("Games: {}", game_count);
    println!("Gamers: {}", gamer_count);
    println!("Saves: {}", save_count);
    println!("Save versions: {}", version_count);
    println!("Unused invites: {}", unused_invites);
    println!();
    println!("Storage Cost (DO Spaces @ $0.005/GB)");
    println!("-------------------------------------");
    println!("Total storage: {:.6} GB", total_gb);
    println!("Monthly cost: ${:.6}", total_cost);

    Ok(())
}