use chrono::{DateTime, Utc};
use serde::Serialize;
use serde_json::Value;
use sqlx::postgres::{PgPool, PgRow};
use sqlx::{Row, types::Uuid};

#[derive(Debug, Clone, Serialize)]
pub struct PublicGatewayRouteRecord {
    pub id: String,
    pub route_key: String,
    pub client_name: String,
    pub allowed_ops: Vec<String>,
    pub is_active: bool,
    pub metadata: Value,
    pub created_at: DateTime<Utc>,
    pub updated_at: DateTime<Utc>,
    pub deleted_at: Option<DateTime<Utc>>,
}

#[derive(Debug, Clone)]
pub struct SavePublicGatewayRouteParams {
    pub route_key: String,
    pub client_name: String,
    pub allowed_ops: Vec<String>,
    pub is_active: bool,
    pub metadata: Value,
}

#[derive(Debug, Clone)]
pub struct PatchPublicGatewayRouteParams {
    pub client_name: Option<String>,
    pub allowed_ops: Option<Vec<String>>,
    pub is_active: Option<bool>,
    pub metadata: Option<Value>,
}

fn map_route_row(row: &PgRow) -> Result<PublicGatewayRouteRecord, sqlx::Error> {
    Ok(PublicGatewayRouteRecord {
        id: row.try_get::<Uuid, _>("id")?.to_string(),
        route_key: row.try_get("route_key")?,
        client_name: row.try_get("client_name")?,
        allowed_ops: row.try_get("allowed_ops")?,
        is_active: row.try_get("is_active")?,
        metadata: row.try_get("metadata")?,
        created_at: row.try_get("created_at")?,
        updated_at: row.try_get("updated_at")?,
        deleted_at: row.try_get("deleted_at")?,
    })
}

pub async fn list_public_gateway_routes(
    pool: &PgPool,
) -> Result<Vec<PublicGatewayRouteRecord>, sqlx::Error> {
    let rows: Vec<PgRow> = sqlx::query(
        r#"
        SELECT
            public_gateway_route_id AS id,
            route_key,
            client_name,
            allowed_ops,
            is_active,
            metadata,
            created_at,
            updated_at,
            deleted_at
        FROM public_gateway_routes
        WHERE deleted_at IS NULL
        ORDER BY lower(route_key)
        "#,
    )
    .fetch_all(pool)
    .await?;

    rows.iter().map(map_route_row).collect()
}

pub async fn get_public_gateway_route_by_key(
    pool: &PgPool,
    route_key: &str,
) -> Result<Option<PublicGatewayRouteRecord>, sqlx::Error> {
    let row: Option<PgRow> = sqlx::query(
        r#"
        SELECT
            public_gateway_route_id AS id,
            route_key,
            client_name,
            allowed_ops,
            is_active,
            metadata,
            created_at,
            updated_at,
            deleted_at
        FROM public_gateway_routes
        WHERE lower(route_key) = lower($1)
          AND deleted_at IS NULL
        LIMIT 1
        "#,
    )
    .bind(route_key)
    .fetch_optional(pool)
    .await?;

    row.as_ref().map(map_route_row).transpose()
}

pub async fn create_public_gateway_route(
    pool: &PgPool,
    params: SavePublicGatewayRouteParams,
) -> Result<PublicGatewayRouteRecord, sqlx::Error> {
    let row: PgRow = sqlx::query(
        r#"
        INSERT INTO public_gateway_routes (
            public_gateway_route_id,
            route_key,
            client_name,
            allowed_ops,
            is_active,
            metadata
        )
        VALUES ($1, $2, $3, $4, $5, $6)
        RETURNING
            public_gateway_route_id AS id,
            route_key,
            client_name,
            allowed_ops,
            is_active,
            metadata,
            created_at,
            updated_at,
            deleted_at
        "#,
    )
    .bind(Uuid::new_v4())
    .bind(&params.route_key)
    .bind(&params.client_name)
    .bind(&params.allowed_ops)
    .bind(params.is_active)
    .bind(&params.metadata)
    .fetch_one(pool)
    .await?;

    map_route_row(&row)
}

pub async fn patch_public_gateway_route(
    pool: &PgPool,
    route_key: &str,
    params: PatchPublicGatewayRouteParams,
) -> Result<Option<PublicGatewayRouteRecord>, sqlx::Error> {
    let row: Option<PgRow> = sqlx::query(
        r#"
        UPDATE public_gateway_routes
        SET
            client_name = COALESCE($2, client_name),
            allowed_ops = COALESCE($3, allowed_ops),
            is_active = COALESCE($4, is_active),
            metadata = COALESCE($5, metadata),
            updated_at = now()
        WHERE lower(route_key) = lower($1)
          AND deleted_at IS NULL
        RETURNING
            public_gateway_route_id AS id,
            route_key,
            client_name,
            allowed_ops,
            is_active,
            metadata,
            created_at,
            updated_at,
            deleted_at
        "#,
    )
    .bind(route_key)
    .bind(params.client_name)
    .bind(params.allowed_ops)
    .bind(params.is_active)
    .bind(params.metadata)
    .fetch_optional(pool)
    .await?;

    row.as_ref().map(map_route_row).transpose()
}

pub async fn soft_delete_public_gateway_route(
    pool: &PgPool,
    route_key: &str,
) -> Result<Option<PublicGatewayRouteRecord>, sqlx::Error> {
    let row: Option<PgRow> = sqlx::query(
        r#"
        UPDATE public_gateway_routes
        SET
            is_active = false,
            deleted_at = now(),
            updated_at = now()
        WHERE lower(route_key) = lower($1)
          AND deleted_at IS NULL
        RETURNING
            public_gateway_route_id AS id,
            route_key,
            client_name,
            allowed_ops,
            is_active,
            metadata,
            created_at,
            updated_at,
            deleted_at
        "#,
    )
    .bind(route_key)
    .fetch_optional(pool)
    .await?;

    row.as_ref().map(map_route_row).transpose()
}
