use actix_web::{
    HttpRequest, HttpResponse, Responder, delete, get, patch, post,
    web::{self, Data, Json, Path},
};
use serde::Deserialize;
use serde_json::json;
use sqlx::{Postgres, Row, Transaction};
use std::time::Instant;

use crate::AppState;
use crate::api::client_context::{
    auth_pool, logging_pool, pool_for_client, registered_client_for, required_client_name,
};
use crate::api::gateway::auth::authorize_gateway_request;
use crate::api::management::audit::{
    audit_table_exists, build_audit_entry, insert_database_audit_log,
};
use crate::api::management::sql::{
    ALLOWED_COLUMN_DATA_TYPES, ALLOWED_INDEX_METHODS, build_create_index_statement,
    build_create_table_statement, build_drop_column_statement, build_drop_index_statement,
    build_drop_table_statement, build_edit_table_statements,
};
use crate::api::management::types::{
    CapabilityRight, CreateIndexRequest, CreateTableRequest, DropColumnRequest, DropIndexRequest,
    DropTableRequest, EditTableRequest, ManagementCapabilitiesResponse,
};
use crate::api::response::{
    api_created, api_success, bad_request, conflict, forbidden, internal_error, not_found,
    processed_error, service_unavailable,
};
use crate::data::clients::{SaveAthenaClientParams, upsert_athena_client};
use crate::error::sqlx_parser::process_sqlx_error_with_context;
use crate::provisioning::{
    NeonConnectionParams, ProvisioningError, RailwayConnectionParams, fetch_neon_connection_uri,
    fetch_railway_connection_uri, run_provision_sql,
};
use crate::utils::request_logging::{LoggedRequest, log_operation_event, log_request};

pub mod audit;
pub mod sql;
pub mod types;

const MANAGEMENT_READ_RIGHT: &str = "management.read";
const MANAGEMENT_TABLES_WRITE_RIGHT: &str = "management.tables.write";
const MANAGEMENT_TABLES_DROP_RIGHT: &str = "management.tables.drop";
const MANAGEMENT_COLUMNS_DROP_RIGHT: &str = "management.columns.drop";
const MANAGEMENT_INDEXES_WRITE_RIGHT: &str = "management.indexes.write";
const MANAGEMENT_INDEXES_DROP_RIGHT: &str = "management.indexes.drop";
const MANAGEMENT_PROVISION_WRITE_RIGHT: &str = "management.provision.write";

async fn authorize_management_request(
    req: &HttpRequest,
    app_state: &AppState,
    client_name: &str,
    required_rights: Vec<String>,
) -> Result<crate::api::gateway::auth::GatewayAuthOutcome, HttpResponse> {
    auth_pool(app_state)?;
    let auth = authorize_gateway_request(req, app_state, Some(client_name), required_rights).await;
    if let Some(response) = auth.response {
        return Err(response);
    }
    if auth.bound_client_name.as_deref() != Some(client_name) {
        return Err(forbidden(
            "Management API requires a client-bound API key",
            format!(
                "The presented API key must be bound to client '{}'.",
                client_name
            ),
        ));
    }
    Ok(auth)
}

async fn table_exists(
    tx: &mut Transaction<'_, Postgres>,
    schema_name: &str,
    table_name: &str,
) -> Result<bool, sqlx::Error> {
    let row = sqlx::query(
        r#"
        SELECT EXISTS (
            SELECT 1
            FROM information_schema.tables
            WHERE table_schema = $1
              AND table_name = $2
        ) AS exists
        "#,
    )
    .bind(schema_name)
    .bind(table_name)
    .fetch_one(&mut **tx)
    .await?;

    row.try_get("exists")
}

async fn column_exists(
    tx: &mut Transaction<'_, Postgres>,
    schema_name: &str,
    table_name: &str,
    column_name: &str,
) -> Result<bool, sqlx::Error> {
    let row = sqlx::query(
        r#"
        SELECT EXISTS (
            SELECT 1
            FROM information_schema.columns
            WHERE table_schema = $1
              AND table_name = $2
              AND column_name = $3
        ) AS exists
        "#,
    )
    .bind(schema_name)
    .bind(table_name)
    .bind(column_name)
    .fetch_one(&mut **tx)
    .await?;

    row.try_get("exists")
}

async fn index_exists(
    tx: &mut Transaction<'_, Postgres>,
    schema_name: &str,
    index_name: &str,
) -> Result<bool, sqlx::Error> {
    let row = sqlx::query(
        r#"
        SELECT EXISTS (
            SELECT 1
            FROM pg_indexes
            WHERE schemaname = $1
              AND indexname = $2
        ) AS exists
        "#,
    )
    .bind(schema_name)
    .bind(index_name)
    .fetch_one(&mut **tx)
    .await?;

    row.try_get("exists")
}

async fn run_audited_statements(
    tx: &mut Transaction<'_, Postgres>,
    statements: &[String],
) -> Result<(), sqlx::Error> {
    sqlx::query("SAVEPOINT athena_management_mutation")
        .execute(&mut **tx)
        .await?;

    for statement in statements {
        sqlx::query(statement).execute(&mut **tx).await?;
    }

    Ok(())
}

async fn rollback_to_savepoint(tx: &mut Transaction<'_, Postgres>) {
    let _ = sqlx::query("ROLLBACK TO SAVEPOINT athena_management_mutation")
        .execute(&mut **tx)
        .await;
}

fn management_capabilities() -> ManagementCapabilitiesResponse {
    ManagementCapabilitiesResponse {
        backend: "postgresql",
        retention_mutation_supported: false,
        replication_mutation_supported: false,
        supported_operations: vec![
            "management.capabilities.read",
            "management.tables.create",
            "management.tables.edit",
            "management.tables.drop",
            "management.columns.drop",
            "management.indexes.create",
            "management.indexes.drop",
            "management.provision.providers.neon",
            "management.provision.providers.railway",
        ],
        required_rights: vec![
            CapabilityRight {
                operation: "GET /management/capabilities",
                required_right: MANAGEMENT_READ_RIGHT,
            },
            CapabilityRight {
                operation: "POST /management/tables",
                required_right: MANAGEMENT_TABLES_WRITE_RIGHT,
            },
            CapabilityRight {
                operation: "PATCH /management/tables/{table_name}",
                required_right: MANAGEMENT_TABLES_WRITE_RIGHT,
            },
            CapabilityRight {
                operation: "DELETE /management/tables/{table_name}",
                required_right: MANAGEMENT_TABLES_DROP_RIGHT,
            },
            CapabilityRight {
                operation: "DELETE /management/tables/{table_name}/columns/{column_name}",
                required_right: MANAGEMENT_COLUMNS_DROP_RIGHT,
            },
            CapabilityRight {
                operation: "POST /management/indexes",
                required_right: MANAGEMENT_INDEXES_WRITE_RIGHT,
            },
            CapabilityRight {
                operation: "DELETE /management/indexes/{index_name}",
                required_right: MANAGEMENT_INDEXES_DROP_RIGHT,
            },
            CapabilityRight {
                operation: "POST /management/provision/providers/neon",
                required_right: MANAGEMENT_PROVISION_WRITE_RIGHT,
            },
            CapabilityRight {
                operation: "POST /management/provision/providers/railway",
                required_right: MANAGEMENT_PROVISION_WRITE_RIGHT,
            },
        ],
        allowed_index_methods: ALLOWED_INDEX_METHODS.to_vec(),
        allowed_column_data_types: ALLOWED_COLUMN_DATA_TYPES.to_vec(),
    }
}

#[derive(Debug, Deserialize)]
#[serde(rename_all = "snake_case")]
struct ManagementNeonProvisionRequest {
    client_name: String,
    #[serde(default)]
    description: Option<String>,
    #[serde(default)]
    connection_uri: Option<String>,
    #[serde(default)]
    api_key: Option<String>,
    #[serde(default)]
    project_id: Option<String>,
    #[serde(default)]
    branch_id: Option<String>,
    #[serde(default)]
    database_name: Option<String>,
    #[serde(default)]
    role_name: Option<String>,
    #[serde(default)]
    endpoint_id: Option<String>,
    #[serde(default)]
    api_base_url: Option<String>,
    #[serde(default = "default_true")]
    provision_schema: bool,
    #[serde(default = "default_true")]
    register_runtime: bool,
    #[serde(default = "default_true")]
    register_catalog: bool,
}

#[derive(Debug, Deserialize)]
#[serde(rename_all = "snake_case")]
struct ManagementRailwayProvisionRequest {
    client_name: String,
    #[serde(default)]
    description: Option<String>,
    #[serde(default)]
    connection_uri: Option<String>,
    #[serde(default)]
    api_key: Option<String>,
    #[serde(default)]
    project_id: Option<String>,
    #[serde(default)]
    environment_id: Option<String>,
    #[serde(default)]
    service_id: Option<String>,
    #[serde(default)]
    plugin_id: Option<String>,
    #[serde(default)]
    graphql_url: Option<String>,
    #[serde(default = "default_true")]
    provision_schema: bool,
    #[serde(default = "default_true")]
    register_runtime: bool,
    #[serde(default = "default_true")]
    register_catalog: bool,
}

fn default_true() -> bool {
    true
}

fn provisioning_error_response(context: &str, err: ProvisioningError) -> HttpResponse {
    match err {
        ProvisioningError::InvalidInput(message) => bad_request(context, message),
        ProvisioningError::Conflict(message) => conflict(context, message),
        ProvisioningError::Unavailable(message) => service_unavailable(context, message),
        ProvisioningError::Execution(message) => internal_error(context, message),
    }
}

fn required_provider_field(name: &str, value: Option<String>) -> Result<String, HttpResponse> {
    let value = value.unwrap_or_default();
    if value.trim().is_empty() {
        return Err(bad_request(
            "Missing required field",
            format!(
                "Provide '{}' or set 'connection_uri' to bypass provider API lookup.",
                name
            ),
        ));
    }
    Ok(value)
}

async fn register_provider_client(
    app_state: &AppState,
    client_name: &str,
    description: Option<String>,
    pg_uri: &str,
    metadata: serde_json::Value,
    register_runtime: bool,
    register_catalog: bool,
) -> Result<(bool, bool), HttpResponse> {
    let mut runtime_registered = false;
    let mut catalog_registered = false;

    if register_runtime {
        let target = crate::drivers::postgresql::sqlx_driver::ClientConnectionTarget {
            client_name: client_name.to_string(),
            source: "database".to_string(),
            description: description.clone(),
            pg_uri: Some(pg_uri.to_string()),
            pg_uri_env_var: None,
            config_uri_template: None,
            is_active: true,
            is_frozen: false,
        };
        if let Err(err) = app_state.pg_registry.upsert_client(target).await {
            return Err(internal_error(
                "Failed to register runtime client",
                format!("Runtime client registration failed: {}", err),
            ));
        }
        runtime_registered = true;
    }

    if register_catalog {
        let pool = logging_pool(app_state)?;
        if let Err(err) = upsert_athena_client(
            &pool,
            SaveAthenaClientParams {
                client_name: client_name.to_string(),
                description,
                pg_uri: Some(pg_uri.to_string()),
                pg_uri_env_var: None,
                config_uri_template: None,
                source: "database".to_string(),
                is_active: true,
                is_frozen: false,
                metadata,
            },
        )
        .await
        {
            return Err(internal_error(
                "Failed to register client in catalog",
                err.to_string(),
            ));
        }
        catalog_registered = true;
    }

    Ok((runtime_registered, catalog_registered))
}

#[get("/management/capabilities")]
async fn management_capabilities_route(
    req: HttpRequest,
    app_state: Data<AppState>,
) -> impl Responder {
    let client_name = match required_client_name(&req) {
        Ok(value) => value,
        Err(resp) => return resp,
    };

    if let Err(resp) = registered_client_for(app_state.get_ref(), &client_name) {
        return resp;
    }

    let auth = match authorize_management_request(
        &req,
        app_state.get_ref(),
        &client_name,
        vec![MANAGEMENT_READ_RIGHT.to_string()],
    )
    .await
    {
        Ok(auth) => auth,
        Err(resp) => return resp,
    };

    let logged_request = log_request(
        req.clone(),
        Some(app_state.get_ref()),
        Some(auth.request_id.clone()),
        Some(&auth.log_context),
    );
    log_operation_event(
        Some(app_state.get_ref()),
        &logged_request,
        "management_capabilities",
        None,
        0,
        actix_web::http::StatusCode::OK,
        Some(json!({ "client_name": client_name })),
    );

    api_success(
        "Loaded management capabilities",
        json!(management_capabilities()),
    )
}

#[post("/management/provision/providers/neon")]
async fn management_provision_neon(
    req: HttpRequest,
    body: Json<ManagementNeonProvisionRequest>,
    app_state: Data<AppState>,
) -> impl Responder {
    let caller_client = match required_client_name(&req) {
        Ok(value) => value,
        Err(resp) => return resp,
    };
    if let Err(resp) = registered_client_for(app_state.get_ref(), &caller_client) {
        return resp;
    }
    let auth = match authorize_management_request(
        &req,
        app_state.get_ref(),
        &caller_client,
        vec![MANAGEMENT_PROVISION_WRITE_RIGHT.to_string()],
    )
    .await
    {
        Ok(auth) => auth,
        Err(resp) => return resp,
    };

    let pg_uri = if let Some(uri) = body
        .connection_uri
        .as_ref()
        .filter(|value| !value.trim().is_empty())
    {
        uri.to_string()
    } else {
        let api_key = match required_provider_field("api_key", body.api_key.clone()) {
            Ok(value) => value,
            Err(resp) => return resp,
        };
        let project_id = match required_provider_field("project_id", body.project_id.clone()) {
            Ok(value) => value,
            Err(resp) => return resp,
        };
        match fetch_neon_connection_uri(NeonConnectionParams {
            api_key,
            project_id,
            branch_id: body.branch_id.clone(),
            database_name: body.database_name.clone(),
            role_name: body.role_name.clone(),
            endpoint_id: body.endpoint_id.clone(),
            api_base_url: body.api_base_url.clone(),
        })
        .await
        {
            Ok(uri) => uri,
            Err(err) => {
                return provisioning_error_response("Failed to fetch Neon connection URI", err);
            }
        }
    };

    let statements_executed = if body.provision_schema {
        Some(match run_provision_sql(&pg_uri).await {
            Ok(total) => total,
            Err(err) => {
                return provisioning_error_response("Failed to provision Neon database", err);
            }
        })
    } else {
        None
    };

    let description = body
        .description
        .clone()
        .or_else(|| Some("Neon database managed via Athena management API".to_string()));
    let (runtime_registered, catalog_registered) = match register_provider_client(
        app_state.get_ref(),
        &body.client_name,
        description,
        &pg_uri,
        json!({
            "managed_by": "management_api",
            "provider": "neon",
            "project_id": body.project_id,
            "branch_id": body.branch_id,
            "database_name": body.database_name,
            "role_name": body.role_name,
            "endpoint_id": body.endpoint_id,
            "requested_by_client": caller_client,
        }),
        body.register_runtime,
        body.register_catalog,
    )
    .await
    {
        Ok(value) => value,
        Err(resp) => return resp,
    };

    let logged_request = log_request(
        req.clone(),
        Some(app_state.get_ref()),
        Some(auth.request_id.clone()),
        Some(&auth.log_context),
    );
    log_operation_event(
        Some(app_state.get_ref()),
        &logged_request,
        "management_provision_neon",
        Some(&body.client_name),
        0,
        actix_web::http::StatusCode::OK,
        Some(json!({
            "provider": "neon",
            "caller_client": caller_client,
            "target_client": body.client_name,
            "register_runtime": runtime_registered,
            "register_catalog": catalog_registered,
        })),
    );

    api_success(
        "Provisioned Neon database",
        json!({
            "provider": "neon",
            "client_name": body.client_name,
            "pg_uri": pg_uri,
            "pipeline": {
                "provision_schema": body.provision_schema,
                "statements_executed": statements_executed,
                "register_runtime": runtime_registered,
                "register_catalog": catalog_registered,
            }
        }),
    )
}

#[post("/management/provision/providers/railway")]
async fn management_provision_railway(
    req: HttpRequest,
    body: Json<ManagementRailwayProvisionRequest>,
    app_state: Data<AppState>,
) -> impl Responder {
    let caller_client = match required_client_name(&req) {
        Ok(value) => value,
        Err(resp) => return resp,
    };
    if let Err(resp) = registered_client_for(app_state.get_ref(), &caller_client) {
        return resp;
    }
    let auth = match authorize_management_request(
        &req,
        app_state.get_ref(),
        &caller_client,
        vec![MANAGEMENT_PROVISION_WRITE_RIGHT.to_string()],
    )
    .await
    {
        Ok(auth) => auth,
        Err(resp) => return resp,
    };

    let pg_uri = if let Some(uri) = body
        .connection_uri
        .as_ref()
        .filter(|value| !value.trim().is_empty())
    {
        uri.to_string()
    } else {
        let api_key = match required_provider_field("api_key", body.api_key.clone()) {
            Ok(value) => value,
            Err(resp) => return resp,
        };
        let project_id = match required_provider_field("project_id", body.project_id.clone()) {
            Ok(value) => value,
            Err(resp) => return resp,
        };
        let environment_id =
            match required_provider_field("environment_id", body.environment_id.clone()) {
                Ok(value) => value,
                Err(resp) => return resp,
            };
        match fetch_railway_connection_uri(RailwayConnectionParams {
            api_key,
            project_id,
            environment_id,
            service_id: body.service_id.clone(),
            plugin_id: body.plugin_id.clone(),
            graphql_url: body.graphql_url.clone(),
        })
        .await
        {
            Ok(uri) => uri,
            Err(err) => {
                return provisioning_error_response("Failed to fetch Railway connection URI", err);
            }
        }
    };

    let statements_executed = if body.provision_schema {
        Some(match run_provision_sql(&pg_uri).await {
            Ok(total) => total,
            Err(err) => {
                return provisioning_error_response("Failed to provision Railway database", err);
            }
        })
    } else {
        None
    };

    let description = body
        .description
        .clone()
        .or_else(|| Some("Railway database managed via Athena management API".to_string()));
    let (runtime_registered, catalog_registered) = match register_provider_client(
        app_state.get_ref(),
        &body.client_name,
        description,
        &pg_uri,
        json!({
            "managed_by": "management_api",
            "provider": "railway",
            "project_id": body.project_id,
            "environment_id": body.environment_id,
            "service_id": body.service_id,
            "plugin_id": body.plugin_id,
            "requested_by_client": caller_client,
        }),
        body.register_runtime,
        body.register_catalog,
    )
    .await
    {
        Ok(value) => value,
        Err(resp) => return resp,
    };

    let logged_request = log_request(
        req.clone(),
        Some(app_state.get_ref()),
        Some(auth.request_id.clone()),
        Some(&auth.log_context),
    );
    log_operation_event(
        Some(app_state.get_ref()),
        &logged_request,
        "management_provision_railway",
        Some(&body.client_name),
        0,
        actix_web::http::StatusCode::OK,
        Some(json!({
            "provider": "railway",
            "caller_client": caller_client,
            "target_client": body.client_name,
            "register_runtime": runtime_registered,
            "register_catalog": catalog_registered,
        })),
    );

    api_success(
        "Provisioned Railway database",
        json!({
            "provider": "railway",
            "client_name": body.client_name,
            "pg_uri": pg_uri,
            "pipeline": {
                "provision_schema": body.provision_schema,
                "statements_executed": statements_executed,
                "register_runtime": runtime_registered,
                "register_catalog": catalog_registered,
            }
        }),
    )
}

#[post("/management/tables")]
async fn management_create_table(
    req: HttpRequest,
    body: Json<CreateTableRequest>,
    app_state: Data<AppState>,
) -> impl Responder {
    let started = Instant::now();
    let request_payload = body.0.clone();
    let client_name = match required_client_name(&req) {
        Ok(value) => value,
        Err(resp) => return resp,
    };

    if let Err(resp) = registered_client_for(app_state.get_ref(), &client_name) {
        return resp;
    }
    let pool = match pool_for_client(app_state.get_ref(), &client_name) {
        Ok(pool) => pool,
        Err(resp) => return resp,
    };
    let auth = match authorize_management_request(
        &req,
        app_state.get_ref(),
        &client_name,
        vec![MANAGEMENT_TABLES_WRITE_RIGHT.to_string()],
    )
    .await
    {
        Ok(auth) => auth,
        Err(resp) => return resp,
    };
    let logged_request: LoggedRequest = log_request(
        req.clone(),
        Some(app_state.get_ref()),
        Some(auth.request_id.clone()),
        Some(&auth.log_context),
    );

    let statements = match build_create_table_statement(&body) {
        Ok(statement) => vec![statement],
        Err(err) => return bad_request("Invalid create table request", err),
    };

    let mut tx = match pool.begin().await {
        Ok(tx) => tx,
        Err(err) => return internal_error("Failed to open database transaction", err.to_string()),
    };

    if !matches!(audit_table_exists(&mut tx).await, Ok(true)) {
        return service_unavailable(
            "Database audit log unavailable",
            "The target database must contain public.database_audit_log before management mutations can run.",
        );
    }

    match table_exists(&mut tx, &body.schema_name, &body.table_name).await {
        Ok(true) => {
            return conflict(
                "Table already exists",
                format!(
                    "Table '{}.{}' already exists.",
                    body.schema_name, body.table_name
                ),
            );
        }
        Ok(false) => {}
        Err(err) => {
            return processed_error(process_sqlx_error_with_context(
                &err,
                Some(&body.table_name),
            ));
        }
    }

    let result = run_audited_statements(&mut tx, &statements).await;
    if let Err(err) = result {
        rollback_to_savepoint(&mut tx).await;
        let audit_entry = build_audit_entry(
            &req,
            &auth.log_context,
            &client_name,
            &body.schema_name,
            Some(&body.table_name),
            "table",
            "create_table",
            "failed",
            json!(request_payload.clone()),
            statements.clone(),
            json!({}),
            Some(err.to_string()),
            started.elapsed().as_millis() as i64,
            &auth.request_id,
        );
        let _ = insert_database_audit_log(&mut tx, &audit_entry).await;
        let _ = tx.commit().await;
        log_operation_event(
            Some(app_state.get_ref()),
            &logged_request,
            "management_create_table",
            Some(&body.table_name),
            started.elapsed().as_millis(),
            actix_web::http::StatusCode::BAD_REQUEST,
            Some(json!({ "error": err.to_string() })),
        );
        app_state.metrics_state.record_management_mutation(
            "create_table",
            "error",
            started.elapsed().as_secs_f64(),
        );
        return processed_error(process_sqlx_error_with_context(
            &err,
            Some(&body.table_name),
        ));
    }

    let audit_entry = build_audit_entry(
        &req,
        &auth.log_context,
        &client_name,
        &body.schema_name,
        Some(&body.table_name),
        "table",
        "create_table",
        "success",
        json!(request_payload.clone()),
        statements.clone(),
        json!({ "applied": true }),
        None,
        started.elapsed().as_millis() as i64,
        &auth.request_id,
    );
    if let Err(err) = insert_database_audit_log(&mut tx, &audit_entry).await {
        let _ = tx.rollback().await;
        return service_unavailable(
            "Database audit log write failed",
            format!(
                "The table was not created because the audit log write failed: {}",
                err
            ),
        );
    }
    if let Err(err) = tx.commit().await {
        return internal_error("Failed to commit database transaction", err.to_string());
    }

    log_operation_event(
        Some(app_state.get_ref()),
        &logged_request,
        "management_create_table",
        Some(&body.table_name),
        started.elapsed().as_millis(),
        actix_web::http::StatusCode::CREATED,
        Some(json!({ "schema_name": body.schema_name, "table_name": body.table_name })),
    );
    app_state.metrics_state.record_management_mutation(
        "create_table",
        "success",
        started.elapsed().as_secs_f64(),
    );

    api_created(
        "Created table",
        json!({
            "schema_name": body.schema_name,
            "table_name": body.table_name,
            "statements": statements,
        }),
    )
}

#[patch("/management/tables/{table_name}")]
async fn management_edit_table(
    req: HttpRequest,
    path: Path<String>,
    body: Json<EditTableRequest>,
    app_state: Data<AppState>,
) -> impl Responder {
    let started = Instant::now();
    let request_payload = body.0.clone();
    let client_name = match required_client_name(&req) {
        Ok(value) => value,
        Err(resp) => return resp,
    };
    let table_name = path.into_inner();

    if let Err(resp) = registered_client_for(app_state.get_ref(), &client_name) {
        return resp;
    }
    let pool = match pool_for_client(app_state.get_ref(), &client_name) {
        Ok(pool) => pool,
        Err(resp) => return resp,
    };
    let auth = match authorize_management_request(
        &req,
        app_state.get_ref(),
        &client_name,
        vec![MANAGEMENT_TABLES_WRITE_RIGHT.to_string()],
    )
    .await
    {
        Ok(auth) => auth,
        Err(resp) => return resp,
    };
    let logged_request = log_request(
        req.clone(),
        Some(app_state.get_ref()),
        Some(auth.request_id.clone()),
        Some(&auth.log_context),
    );

    let statements =
        match build_edit_table_statements(&body.schema_name, &table_name, &body.operations) {
            Ok(statements) => statements,
            Err(err) => return bad_request("Invalid edit table request", err),
        };

    let mut tx = match pool.begin().await {
        Ok(tx) => tx,
        Err(err) => return internal_error("Failed to open database transaction", err.to_string()),
    };

    if !matches!(audit_table_exists(&mut tx).await, Ok(true)) {
        return service_unavailable(
            "Database audit log unavailable",
            "The target database must contain public.database_audit_log before management mutations can run.",
        );
    }

    match table_exists(&mut tx, &body.schema_name, &table_name).await {
        Ok(false) => {
            return not_found(
                "Table not found",
                format!(
                    "Table '{}.{}' does not exist.",
                    body.schema_name, table_name
                ),
            );
        }
        Ok(true) => {}
        Err(err) => {
            return processed_error(process_sqlx_error_with_context(&err, Some(&table_name)));
        }
    }

    let result = run_audited_statements(&mut tx, &statements).await;
    if let Err(err) = result {
        rollback_to_savepoint(&mut tx).await;
        let audit_entry = build_audit_entry(
            &req,
            &auth.log_context,
            &client_name,
            &body.schema_name,
            Some(&table_name),
            "table",
            "edit_table",
            "failed",
            json!(request_payload.clone()),
            statements.clone(),
            json!({}),
            Some(err.to_string()),
            started.elapsed().as_millis() as i64,
            &auth.request_id,
        );
        let _ = insert_database_audit_log(&mut tx, &audit_entry).await;
        let _ = tx.commit().await;
        log_operation_event(
            Some(app_state.get_ref()),
            &logged_request,
            "management_edit_table",
            Some(&table_name),
            started.elapsed().as_millis(),
            actix_web::http::StatusCode::BAD_REQUEST,
            Some(json!({ "error": err.to_string() })),
        );
        app_state.metrics_state.record_management_mutation(
            "edit_table",
            "error",
            started.elapsed().as_secs_f64(),
        );
        return processed_error(process_sqlx_error_with_context(&err, Some(&table_name)));
    }

    let audit_entry = build_audit_entry(
        &req,
        &auth.log_context,
        &client_name,
        &body.schema_name,
        Some(&table_name),
        "table",
        "edit_table",
        "success",
        json!(request_payload.clone()),
        statements.clone(),
        json!({ "applied": true }),
        None,
        started.elapsed().as_millis() as i64,
        &auth.request_id,
    );
    if let Err(err) = insert_database_audit_log(&mut tx, &audit_entry).await {
        let _ = tx.rollback().await;
        return service_unavailable(
            "Database audit log write failed",
            format!(
                "The table was not altered because the audit log write failed: {}",
                err
            ),
        );
    }
    if let Err(err) = tx.commit().await {
        return internal_error("Failed to commit database transaction", err.to_string());
    }

    log_operation_event(
        Some(app_state.get_ref()),
        &logged_request,
        "management_edit_table",
        Some(&table_name),
        started.elapsed().as_millis(),
        actix_web::http::StatusCode::OK,
        Some(json!({ "schema_name": body.schema_name, "table_name": table_name })),
    );
    app_state.metrics_state.record_management_mutation(
        "edit_table",
        "success",
        started.elapsed().as_secs_f64(),
    );
    api_success(
        "Updated table",
        json!({ "table_name": table_name, "statements": statements }),
    )
}

#[delete("/management/tables/{table_name}")]
async fn management_drop_table(
    req: HttpRequest,
    path: Path<String>,
    body: Json<DropTableRequest>,
    app_state: Data<AppState>,
) -> impl Responder {
    let started = Instant::now();
    let request_payload = body.0.clone();
    let client_name = match required_client_name(&req) {
        Ok(value) => value,
        Err(resp) => return resp,
    };
    let table_name = path.into_inner();
    if let Err(resp) = registered_client_for(app_state.get_ref(), &client_name) {
        return resp;
    }
    let pool = match pool_for_client(app_state.get_ref(), &client_name) {
        Ok(pool) => pool,
        Err(resp) => return resp,
    };
    let auth = match authorize_management_request(
        &req,
        app_state.get_ref(),
        &client_name,
        vec![MANAGEMENT_TABLES_DROP_RIGHT.to_string()],
    )
    .await
    {
        Ok(auth) => auth,
        Err(resp) => return resp,
    };
    let logged_request = log_request(
        req.clone(),
        Some(app_state.get_ref()),
        Some(auth.request_id.clone()),
        Some(&auth.log_context),
    );

    let statements = match build_drop_table_statement(&body.schema_name, &table_name, body.cascade)
    {
        Ok(statement) => vec![statement],
        Err(err) => return bad_request("Invalid drop table request", err),
    };

    let mut tx = match pool.begin().await {
        Ok(tx) => tx,
        Err(err) => return internal_error("Failed to open database transaction", err.to_string()),
    };

    if !matches!(audit_table_exists(&mut tx).await, Ok(true)) {
        return service_unavailable(
            "Database audit log unavailable",
            "The target database must contain public.database_audit_log before management mutations can run.",
        );
    }
    match table_exists(&mut tx, &body.schema_name, &table_name).await {
        Ok(false) => {
            return not_found(
                "Table not found",
                format!(
                    "Table '{}.{}' does not exist.",
                    body.schema_name, table_name
                ),
            );
        }
        Ok(true) => {}
        Err(err) => {
            return processed_error(process_sqlx_error_with_context(&err, Some(&table_name)));
        }
    }

    let result = run_audited_statements(&mut tx, &statements).await;
    if let Err(err) = result {
        rollback_to_savepoint(&mut tx).await;
        let audit_entry = build_audit_entry(
            &req,
            &auth.log_context,
            &client_name,
            &body.schema_name,
            Some(&table_name),
            "table",
            "drop_table",
            "failed",
            json!(request_payload.clone()),
            statements.clone(),
            json!({}),
            Some(err.to_string()),
            started.elapsed().as_millis() as i64,
            &auth.request_id,
        );
        let _ = insert_database_audit_log(&mut tx, &audit_entry).await;
        let _ = tx.commit().await;
        log_operation_event(
            Some(app_state.get_ref()),
            &logged_request,
            "management_drop_table",
            Some(&table_name),
            started.elapsed().as_millis(),
            actix_web::http::StatusCode::BAD_REQUEST,
            Some(json!({ "error": err.to_string() })),
        );
        app_state.metrics_state.record_management_mutation(
            "drop_table",
            "error",
            started.elapsed().as_secs_f64(),
        );
        return processed_error(process_sqlx_error_with_context(&err, Some(&table_name)));
    }

    let audit_entry = build_audit_entry(
        &req,
        &auth.log_context,
        &client_name,
        &body.schema_name,
        Some(&table_name),
        "table",
        "drop_table",
        "success",
        json!(request_payload.clone()),
        statements.clone(),
        json!({ "dropped": true }),
        None,
        started.elapsed().as_millis() as i64,
        &auth.request_id,
    );
    if let Err(err) = insert_database_audit_log(&mut tx, &audit_entry).await {
        let _ = tx.rollback().await;
        return service_unavailable(
            "Database audit log write failed",
            format!(
                "The table was not dropped because the audit log write failed: {}",
                err
            ),
        );
    }
    if let Err(err) = tx.commit().await {
        return internal_error("Failed to commit database transaction", err.to_string());
    }

    log_operation_event(
        Some(app_state.get_ref()),
        &logged_request,
        "management_drop_table",
        Some(&table_name),
        started.elapsed().as_millis(),
        actix_web::http::StatusCode::OK,
        Some(json!({ "schema_name": body.schema_name, "table_name": table_name })),
    );
    app_state.metrics_state.record_management_mutation(
        "drop_table",
        "success",
        started.elapsed().as_secs_f64(),
    );
    api_success(
        "Dropped table",
        json!({ "table_name": table_name, "statements": statements }),
    )
}

#[delete("/management/tables/{table_name}/columns/{column_name}")]
async fn management_drop_column(
    req: HttpRequest,
    path: Path<(String, String)>,
    body: Json<DropColumnRequest>,
    app_state: Data<AppState>,
) -> impl Responder {
    let started = Instant::now();
    let request_payload = body.0.clone();
    let client_name = match required_client_name(&req) {
        Ok(value) => value,
        Err(resp) => return resp,
    };
    let (table_name, column_name) = path.into_inner();
    if let Err(resp) = registered_client_for(app_state.get_ref(), &client_name) {
        return resp;
    }
    let pool = match pool_for_client(app_state.get_ref(), &client_name) {
        Ok(pool) => pool,
        Err(resp) => return resp,
    };
    let auth = match authorize_management_request(
        &req,
        app_state.get_ref(),
        &client_name,
        vec![MANAGEMENT_COLUMNS_DROP_RIGHT.to_string()],
    )
    .await
    {
        Ok(auth) => auth,
        Err(resp) => return resp,
    };
    let logged_request = log_request(
        req.clone(),
        Some(app_state.get_ref()),
        Some(auth.request_id.clone()),
        Some(&auth.log_context),
    );

    let statements = match build_drop_column_statement(
        &body.schema_name,
        &table_name,
        &column_name,
        body.cascade,
    ) {
        Ok(statement) => vec![statement],
        Err(err) => return bad_request("Invalid drop column request", err),
    };

    let mut tx = match pool.begin().await {
        Ok(tx) => tx,
        Err(err) => return internal_error("Failed to open database transaction", err.to_string()),
    };
    if !matches!(audit_table_exists(&mut tx).await, Ok(true)) {
        return service_unavailable(
            "Database audit log unavailable",
            "The target database must contain public.database_audit_log before management mutations can run.",
        );
    }
    match column_exists(&mut tx, &body.schema_name, &table_name, &column_name).await {
        Ok(false) => {
            return not_found(
                "Column not found",
                format!(
                    "Column '{}.{}.{}' does not exist.",
                    body.schema_name, table_name, column_name
                ),
            );
        }
        Ok(true) => {}
        Err(err) => {
            return processed_error(process_sqlx_error_with_context(&err, Some(&table_name)));
        }
    }

    let result = run_audited_statements(&mut tx, &statements).await;
    if let Err(err) = result {
        rollback_to_savepoint(&mut tx).await;
        let audit_entry = build_audit_entry(
            &req,
            &auth.log_context,
            &client_name,
            &body.schema_name,
            Some(&table_name),
            "column",
            "drop_column",
            "failed",
            json!(request_payload.clone()),
            statements.clone(),
            json!({}),
            Some(err.to_string()),
            started.elapsed().as_millis() as i64,
            &auth.request_id,
        );
        let _ = insert_database_audit_log(&mut tx, &audit_entry).await;
        let _ = tx.commit().await;
        log_operation_event(
            Some(app_state.get_ref()),
            &logged_request,
            "management_drop_column",
            Some(&table_name),
            started.elapsed().as_millis(),
            actix_web::http::StatusCode::BAD_REQUEST,
            Some(json!({ "error": err.to_string() })),
        );
        app_state.metrics_state.record_management_mutation(
            "drop_column",
            "error",
            started.elapsed().as_secs_f64(),
        );
        return processed_error(process_sqlx_error_with_context(&err, Some(&table_name)));
    }

    let audit_entry = build_audit_entry(
        &req,
        &auth.log_context,
        &client_name,
        &body.schema_name,
        Some(&table_name),
        "column",
        "drop_column",
        "success",
        json!(request_payload.clone()),
        statements.clone(),
        json!({ "dropped": true, "column_name": column_name }),
        None,
        started.elapsed().as_millis() as i64,
        &auth.request_id,
    );
    if let Err(err) = insert_database_audit_log(&mut tx, &audit_entry).await {
        let _ = tx.rollback().await;
        return service_unavailable(
            "Database audit log write failed",
            format!(
                "The column was not dropped because the audit log write failed: {}",
                err
            ),
        );
    }
    if let Err(err) = tx.commit().await {
        return internal_error("Failed to commit database transaction", err.to_string());
    }

    log_operation_event(
        Some(app_state.get_ref()),
        &logged_request,
        "management_drop_column",
        Some(&table_name),
        started.elapsed().as_millis(),
        actix_web::http::StatusCode::OK,
        Some(
            json!({ "schema_name": body.schema_name, "table_name": table_name, "column_name": column_name }),
        ),
    );
    app_state.metrics_state.record_management_mutation(
        "drop_column",
        "success",
        started.elapsed().as_secs_f64(),
    );
    api_success(
        "Dropped column",
        json!({ "table_name": table_name, "column_name": column_name, "statements": statements }),
    )
}

#[post("/management/indexes")]
async fn management_create_index(
    req: HttpRequest,
    body: Json<CreateIndexRequest>,
    app_state: Data<AppState>,
) -> impl Responder {
    let started = Instant::now();
    let request_payload = body.0.clone();
    let client_name = match required_client_name(&req) {
        Ok(value) => value,
        Err(resp) => return resp,
    };
    if let Err(resp) = registered_client_for(app_state.get_ref(), &client_name) {
        return resp;
    }
    let pool = match pool_for_client(app_state.get_ref(), &client_name) {
        Ok(pool) => pool,
        Err(resp) => return resp,
    };
    let auth = match authorize_management_request(
        &req,
        app_state.get_ref(),
        &client_name,
        vec![MANAGEMENT_INDEXES_WRITE_RIGHT.to_string()],
    )
    .await
    {
        Ok(auth) => auth,
        Err(resp) => return resp,
    };
    let logged_request = log_request(
        req.clone(),
        Some(app_state.get_ref()),
        Some(auth.request_id.clone()),
        Some(&auth.log_context),
    );

    let (index_name, statement) = match build_create_index_statement(&body) {
        Ok(value) => value,
        Err(err) => return bad_request("Invalid create index request", err),
    };
    let statements = vec![statement];

    let mut tx = match pool.begin().await {
        Ok(tx) => tx,
        Err(err) => return internal_error("Failed to open database transaction", err.to_string()),
    };
    if !matches!(audit_table_exists(&mut tx).await, Ok(true)) {
        return service_unavailable(
            "Database audit log unavailable",
            "The target database must contain public.database_audit_log before management mutations can run.",
        );
    }
    match table_exists(&mut tx, &body.schema_name, &body.table_name).await {
        Ok(false) => {
            return not_found(
                "Table not found",
                format!(
                    "Table '{}.{}' does not exist.",
                    body.schema_name, body.table_name
                ),
            );
        }
        Ok(true) => {}
        Err(err) => {
            return processed_error(process_sqlx_error_with_context(
                &err,
                Some(&body.table_name),
            ));
        }
    }
    match index_exists(&mut tx, &body.schema_name, &index_name).await {
        Ok(true) => {
            return conflict(
                "Index already exists",
                format!(
                    "Index '{}.{}' already exists.",
                    body.schema_name, index_name
                ),
            );
        }
        Ok(false) => {}
        Err(err) => {
            return processed_error(process_sqlx_error_with_context(
                &err,
                Some(&body.table_name),
            ));
        }
    }

    let result = run_audited_statements(&mut tx, &statements).await;
    if let Err(err) = result {
        rollback_to_savepoint(&mut tx).await;
        let audit_entry = build_audit_entry(
            &req,
            &auth.log_context,
            &client_name,
            &body.schema_name,
            Some(&body.table_name),
            "index",
            "create_index",
            "failed",
            json!(request_payload.clone()),
            statements.clone(),
            json!({}),
            Some(err.to_string()),
            started.elapsed().as_millis() as i64,
            &auth.request_id,
        );
        let _ = insert_database_audit_log(&mut tx, &audit_entry).await;
        let _ = tx.commit().await;
        log_operation_event(
            Some(app_state.get_ref()),
            &logged_request,
            "management_create_index",
            Some(&body.table_name),
            started.elapsed().as_millis(),
            actix_web::http::StatusCode::BAD_REQUEST,
            Some(json!({ "error": err.to_string(), "index_name": index_name })),
        );
        app_state.metrics_state.record_management_mutation(
            "create_index",
            "error",
            started.elapsed().as_secs_f64(),
        );
        return processed_error(process_sqlx_error_with_context(
            &err,
            Some(&body.table_name),
        ));
    }

    let audit_entry = build_audit_entry(
        &req,
        &auth.log_context,
        &client_name,
        &body.schema_name,
        Some(&body.table_name),
        "index",
        "create_index",
        "success",
        json!(request_payload.clone()),
        statements.clone(),
        json!({ "created": true, "index_name": index_name }),
        None,
        started.elapsed().as_millis() as i64,
        &auth.request_id,
    );
    if let Err(err) = insert_database_audit_log(&mut tx, &audit_entry).await {
        let _ = tx.rollback().await;
        return service_unavailable(
            "Database audit log write failed",
            format!(
                "The index was not created because the audit log write failed: {}",
                err
            ),
        );
    }
    if let Err(err) = tx.commit().await {
        return internal_error("Failed to commit database transaction", err.to_string());
    }

    log_operation_event(
        Some(app_state.get_ref()),
        &logged_request,
        "management_create_index",
        Some(&body.table_name),
        started.elapsed().as_millis(),
        actix_web::http::StatusCode::CREATED,
        Some(
            json!({ "schema_name": body.schema_name, "table_name": body.table_name, "index_name": index_name }),
        ),
    );
    app_state.metrics_state.record_management_mutation(
        "create_index",
        "success",
        started.elapsed().as_secs_f64(),
    );
    api_created(
        "Created index",
        json!({ "table_name": body.table_name, "index_name": index_name, "statements": statements }),
    )
}

#[delete("/management/indexes/{index_name}")]
async fn management_drop_index(
    req: HttpRequest,
    path: Path<String>,
    body: Json<DropIndexRequest>,
    app_state: Data<AppState>,
) -> impl Responder {
    let started = Instant::now();
    let request_payload = body.0.clone();
    let client_name = match required_client_name(&req) {
        Ok(value) => value,
        Err(resp) => return resp,
    };
    let index_name = path.into_inner();
    if let Err(resp) = registered_client_for(app_state.get_ref(), &client_name) {
        return resp;
    }
    let pool = match pool_for_client(app_state.get_ref(), &client_name) {
        Ok(pool) => pool,
        Err(resp) => return resp,
    };
    let auth = match authorize_management_request(
        &req,
        app_state.get_ref(),
        &client_name,
        vec![MANAGEMENT_INDEXES_DROP_RIGHT.to_string()],
    )
    .await
    {
        Ok(auth) => auth,
        Err(resp) => return resp,
    };
    let logged_request = log_request(
        req.clone(),
        Some(app_state.get_ref()),
        Some(auth.request_id.clone()),
        Some(&auth.log_context),
    );

    let statements = match build_drop_index_statement(&body.schema_name, &index_name) {
        Ok(statement) => vec![statement],
        Err(err) => return bad_request("Invalid drop index request", err),
    };

    let mut tx = match pool.begin().await {
        Ok(tx) => tx,
        Err(err) => return internal_error("Failed to open database transaction", err.to_string()),
    };
    if !matches!(audit_table_exists(&mut tx).await, Ok(true)) {
        return service_unavailable(
            "Database audit log unavailable",
            "The target database must contain public.database_audit_log before management mutations can run.",
        );
    }
    match index_exists(&mut tx, &body.schema_name, &index_name).await {
        Ok(false) => {
            return not_found(
                "Index not found",
                format!(
                    "Index '{}.{}' does not exist.",
                    body.schema_name, index_name
                ),
            );
        }
        Ok(true) => {}
        Err(err) => {
            return processed_error(process_sqlx_error_with_context(&err, Some(&index_name)));
        }
    }

    let result = run_audited_statements(&mut tx, &statements).await;
    if let Err(err) = result {
        rollback_to_savepoint(&mut tx).await;
        let audit_entry = build_audit_entry(
            &req,
            &auth.log_context,
            &client_name,
            &body.schema_name,
            None,
            "index",
            "drop_index",
            "failed",
            json!(request_payload.clone()),
            statements.clone(),
            json!({}),
            Some(err.to_string()),
            started.elapsed().as_millis() as i64,
            &auth.request_id,
        );
        let _ = insert_database_audit_log(&mut tx, &audit_entry).await;
        let _ = tx.commit().await;
        log_operation_event(
            Some(app_state.get_ref()),
            &logged_request,
            "management_drop_index",
            None,
            started.elapsed().as_millis(),
            actix_web::http::StatusCode::BAD_REQUEST,
            Some(json!({ "error": err.to_string(), "index_name": index_name })),
        );
        app_state.metrics_state.record_management_mutation(
            "drop_index",
            "error",
            started.elapsed().as_secs_f64(),
        );
        return processed_error(process_sqlx_error_with_context(&err, Some(&index_name)));
    }

    let audit_entry = build_audit_entry(
        &req,
        &auth.log_context,
        &client_name,
        &body.schema_name,
        None,
        "index",
        "drop_index",
        "success",
        json!(request_payload.clone()),
        statements.clone(),
        json!({ "dropped": true, "index_name": index_name }),
        None,
        started.elapsed().as_millis() as i64,
        &auth.request_id,
    );
    if let Err(err) = insert_database_audit_log(&mut tx, &audit_entry).await {
        let _ = tx.rollback().await;
        return service_unavailable(
            "Database audit log write failed",
            format!(
                "The index was not dropped because the audit log write failed: {}",
                err
            ),
        );
    }
    if let Err(err) = tx.commit().await {
        return internal_error("Failed to commit database transaction", err.to_string());
    }

    log_operation_event(
        Some(app_state.get_ref()),
        &logged_request,
        "management_drop_index",
        None,
        started.elapsed().as_millis(),
        actix_web::http::StatusCode::OK,
        Some(json!({ "schema_name": body.schema_name, "index_name": index_name })),
    );
    app_state.metrics_state.record_management_mutation(
        "drop_index",
        "success",
        started.elapsed().as_secs_f64(),
    );
    api_success(
        "Dropped index",
        json!({ "index_name": index_name, "statements": statements }),
    )
}

pub fn services(cfg: &mut web::ServiceConfig) {
    cfg.service(management_capabilities_route)
        .service(management_provision_neon)
        .service(management_provision_railway)
        .service(management_create_table)
        .service(management_edit_table)
        .service(management_drop_table)
        .service(management_drop_column)
        .service(management_create_index)
        .service(management_drop_index);
}
