//! Database summary queries via docker exec + psql.
//! RGS schema: user=rgs, db=ln_graph_sync
//! Tables: node_announcements, channel_announcements, channel_updates, config

use super::ops::postgres_container_name;
use super::InfraContext;
use anyhow::{bail, Context, Result};
use std::process::Command;

const DB_USER: &str = "rgs";
const DB_NAME: &str = "ln_graph_sync";

#[derive(Debug, Clone, Default)]
pub struct DbSummary {
    pub node_announcements: u64,
    pub channel_announcements: u64,
    pub channel_updates: u64,
    pub config_rows: u64,
}

pub fn query_summary(ctx: &InfraContext) -> Result<DbSummary> {
    let container = postgres_container_name(ctx);
    let sql = "SELECT \
        (SELECT COUNT(*) FROM node_announcements), \
        (SELECT COUNT(*) FROM channel_announcements), \
        (SELECT COUNT(*) FROM channel_updates), \
        (SELECT COUNT(*) FROM config);";

    let out = Command::new("docker")
        .args(["exec", &container, "psql", "-U", DB_USER, "-d", DB_NAME, "-t", "-A", "-F", "|", "-c", sql])
        .output()
        .context("docker exec psql")?;

    if !out.status.success() {
        bail!("psql error: {}", String::from_utf8_lossy(&out.stderr).trim());
    }

    let stdout = String::from_utf8_lossy(&out.stdout);
    let nums: Vec<u64> = stdout
        .trim()
        .split('|')
        .map(|s| s.trim().parse::<u64>().unwrap_or(0))
        .collect();

    Ok(DbSummary {
        node_announcements:    nums.first().copied().unwrap_or(0),
        channel_announcements: nums.get(1).copied().unwrap_or(0),
        channel_updates:       nums.get(2).copied().unwrap_or(0),
        config_rows:           nums.get(3).copied().unwrap_or(0),
    })
}

/// Returns (scid_formatted, capacity_str) pairs.
/// Schema: channel_announcements has short_channel_id (bigint), funding_amount_sats (bigint).
pub fn query_channel_list(ctx: &InfraContext, limit: u64) -> Vec<(String, String)> {
    let container = postgres_container_name(ctx);
    let sql = format!(
        "SELECT short_channel_id, funding_amount_sats \
         FROM channel_announcements ORDER BY short_channel_id LIMIT {limit};"
    );
    let out = Command::new("docker")
        .args(["exec", &container, "psql", "-U", DB_USER, "-d", DB_NAME, "-t", "-A", "-F", "|", "-c", &sql])
        .output();

    let Ok(out) = out else { return vec![] };
    if !out.status.success() { return vec![]; }

    String::from_utf8_lossy(&out.stdout)
        .lines()
        .filter_map(|line| {
            let mut parts = line.split('|');
            let raw_scid: u64 = parts.next()?.trim().parse().ok()?;
            let sats: u64 = parts.next()?.trim().parse().unwrap_or(0);
            Some((format_scid(raw_scid), format_capacity(sats)))
        })
        .collect()
}

fn format_scid(scid: u64) -> String {
    let block = scid >> 40;
    let tx    = (scid >> 16) & 0xFF_FFFF;
    let out   = scid & 0xFFFF;
    format!("{block}×{tx}×{out}")
}

fn format_capacity(sats: u64) -> String {
    if sats >= 1_000_000 { format!("{:.1}M sat", sats as f64 / 1_000_000.0) }
    else if sats >= 1_000 { format!("{:.0}k sat", sats as f64 / 1_000.0) }
    else { format!("{sats} sat") }
}

pub fn print_summary(ctx: &InfraContext) -> Result<()> {
    let s = query_summary(ctx)?;
    println!("{:<28} Rows", "Table");
    println!("{}", "─".repeat(36));
    println!("{:<28} {}", "node_announcements",    s.node_announcements);
    println!("{:<28} {}", "channel_announcements", s.channel_announcements);
    println!("{:<28} {}", "channel_updates",       s.channel_updates);
    println!("{:<28} {}", "config",                s.config_rows);
    Ok(())
}
