//! Postgres pool plus JSON <-> SQL value bridging.
//!
//! Postgres is the query side of the CQRS split (all reads) and the eventual
//! destination of staged writes applied by the sync worker.

use deadpool_postgres::{Manager, ManagerConfig, Pool, RecyclingMethod};
use roundabout_orm_schema::{EntityDef, FieldDef};
use serde_json::{Map, Value};
use std::error::Error;
use tokio_postgres::types::ToSql;
use tokio_postgres::{NoTls, Row};

/// Build a connection pool from a libpq-style connection string.
pub fn build_pool(conn_str: &str) -> Result<Pool, Box<dyn Error + Send + Sync>> {
    let pg_config: tokio_postgres::Config = conn_str.parse()?;
    let mgr_config = ManagerConfig {
        recycling_method: RecyclingMethod::Fast,
    };
    let mgr = Manager::from_config(pg_config, NoTls, mgr_config);
    let pool = Pool::builder(mgr).max_size(8).build()?;
    Ok(pool)
}

/// Render a column definition for `CREATE TABLE` / `ADD COLUMN`, e.g.
/// `price DOUBLE PRECISION` or `guid UUID PRIMARY KEY DEFAULT gen_random_uuid()`.
/// Shared with the migration generator so table creation and column additions agree.
pub fn column_ddl(field: &FieldDef) -> String {
    let pk = if field.primary_key { " PRIMARY KEY" } else { "" };
    let default = field
        .default_expr
        .as_deref()
        .map(|e| format!(" DEFAULT {e}"))
        .unwrap_or_default();
    format!("{} {}{}{}", field.name, field.sql_type, pk, default)
}

/// Cast suffix for a primary-key parameter bound as text. For a `UUID` pk we
/// use `::text::uuid` so the bind parameter is typed as text (matching the
/// `String` we pass) while the comparison still uses the indexed uuid column.
fn pk_param_cast(sql_type: &str) -> &'static str {
    if sql_type == "UUID" {
        "::text::uuid"
    } else {
        ""
    }
}

/// Column expression for `SELECT`: `UUID` columns are cast to text so they
/// decode as `String` (no `uuid` crate needed); others are selected as-is.
fn select_col(field: &FieldDef) -> String {
    if field.sql_type == "UUID" {
        format!("{0}::text AS {0}", field.name)
    } else {
        field.name.clone()
    }
}

/// Read readable columns of one entity row as a JSON object. `None` if missing.
pub async fn read_one(
    pool: &Pool,
    ent: &EntityDef,
    id: &str,
) -> Result<Option<Value>, Box<dyn Error + Send + Sync>> {
    let pk = ent.primary_key().ok_or("entity has no primary key")?;
    let pk_cast = pk_param_cast(&pk.sql_type);
    let cols: Vec<String> = ent.readable().map(select_col).collect();
    let sql = format!(
        "SELECT {} FROM {} WHERE {} = $1{}",
        cols.join(", "),
        ent.table,
        pk.name,
        pk_cast
    );
    let client = pool.get().await?;
    let row = client.query_opt(&sql, &[&id]).await?;
    Ok(row.map(|r| row_to_json(&r, ent)))
}

/// Read up to `limit` rows of an entity as a JSON array.
pub async fn read_many(
    pool: &Pool,
    ent: &EntityDef,
    limit: i64,
) -> Result<Value, Box<dyn Error + Send + Sync>> {
    let cols: Vec<String> = ent.readable().map(select_col).collect();
    let sql = format!("SELECT {} FROM {} LIMIT $1", cols.join(", "), ent.table);
    let client = pool.get().await?;
    let rows = client.query(&sql, &[&limit]).await?;
    Ok(Value::Array(rows.iter().map(|r| row_to_json(r, ent)).collect()))
}

/// Apply a staged write (a map of column -> value) as an UPDATE.
pub async fn apply_update(
    pool: &Pool,
    ent: &EntityDef,
    row_id: &str,
    changes: &Map<String, Value>,
) -> Result<(), Box<dyn Error + Send + Sync>> {
    let pk = ent.primary_key().ok_or("entity has no primary key")?;
    let pk_cast = pk_param_cast(&pk.sql_type);

    let mut set_clauses = Vec::new();
    let mut boxed: Vec<Box<dyn ToSql + Sync + Send>> = Vec::new();
    for (col, val) in changes {
        let Some(field) = ent.field(col) else { continue };
        set_clauses.push(format!("{} = ${}", col, boxed.len() + 1));
        boxed.push(json_to_sql(&field.rust_type, val));
    }
    if set_clauses.is_empty() {
        return Ok(());
    }
    // Primary key (the guid, as text → cast to uuid) is the final bound parameter.
    let pk_idx = boxed.len() + 1;
    boxed.push(Box::new(row_id.to_string()));

    let sql = format!(
        "UPDATE {} SET {} WHERE {} = ${}{}",
        ent.table,
        set_clauses.join(", "),
        pk.name,
        pk_idx,
        pk_cast
    );
    let params: Vec<&(dyn ToSql + Sync)> =
        boxed.iter().map(|b| b.as_ref() as &(dyn ToSql + Sync)).collect();
    let client = pool.get().await?;
    client.execute(&sql, &params).await?;
    Ok(())
}

/// Convert a row into a JSON object using the manifest's per-column Rust type.
fn row_to_json(row: &Row, ent: &EntityDef) -> Value {
    let mut obj = Map::new();
    for (idx, field) in ent.readable().enumerate() {
        obj.insert(field.name.clone(), pg_value_to_json(row, idx, &field.rust_type));
    }
    Value::Object(obj)
}

/// Pull column `idx` as the manifest's declared type, mapping NULL -> JSON null.
fn pg_value_to_json(row: &Row, idx: usize, rust_type: &str) -> Value {
    match rust_type {
        "i16" => opt_to_json(row.try_get::<_, Option<i16>>(idx).ok().flatten()),
        "i32" | "u32" => opt_to_json(row.try_get::<_, Option<i32>>(idx).ok().flatten()),
        "i64" | "u64" | "usize" | "isize" => {
            opt_to_json(row.try_get::<_, Option<i64>>(idx).ok().flatten())
        }
        "f32" => opt_to_json(row.try_get::<_, Option<f32>>(idx).ok().flatten()),
        "f64" => opt_to_json(row.try_get::<_, Option<f64>>(idx).ok().flatten()),
        "bool" => opt_to_json(row.try_get::<_, Option<bool>>(idx).ok().flatten()),
        _ => opt_to_json(row.try_get::<_, Option<String>>(idx).ok().flatten()),
    }
}

fn opt_to_json<T: Into<Value>>(v: Option<T>) -> Value {
    v.map(Into::into).unwrap_or(Value::Null)
}

/// Box a JSON value as a typed Postgres parameter based on the column's Rust type.
/// `Option<T>` is used throughout so JSON null maps to SQL NULL.
fn json_to_sql(rust_type: &str, val: &Value) -> Box<dyn ToSql + Sync + Send> {
    match rust_type {
        "i16" => Box::new(val.as_i64().map(|v| v as i16)),
        "i32" | "u32" => Box::new(val.as_i64().map(|v| v as i32)),
        "i64" | "u64" | "usize" | "isize" => Box::new(val.as_i64()),
        "f32" => Box::new(val.as_f64().map(|v| v as f32)),
        "f64" => Box::new(val.as_f64()),
        "bool" => Box::new(val.as_bool()),
        _ => Box::new(val.as_str().map(|s| s.to_string())),
    }
}
