use serde_json::Value;
use sqlx::PgPool;
use uuid::Uuid;

/// One pipeline step-log row emitted for the `source`, `transform`, or `sink` stage.
#[derive(Debug, Clone, PartialEq)]
pub struct PipelineStepLogEntry {
    pub pipeline_run_id: Uuid,
    pub request_id: Option<String>,
    pub pipeline_name: Option<String>,
    pub step: String,
    pub athena_client: Option<String>,
    pub table_name: Option<String>,
    pub status_code: Option<i32>,
    pub duration_ms: Option<i64>,
    pub error_message: Option<String>,
    pub details: Option<Value>,
}

/// Ensures the pipeline step-log table exists on the logging Postgres database.
pub async fn ensure_pipeline_step_log_table(pool: &PgPool) -> Result<(), sqlx::Error> {
    sqlx::query(
        r#"
        CREATE TABLE IF NOT EXISTS pipeline_step_log (
            id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
            created_at timestamptz NOT NULL DEFAULT now(),
            pipeline_run_id uuid NOT NULL,
            request_id text,
            pipeline_name text,
            step text NOT NULL,
            athena_client text,
            table_name text,
            status_code integer,
            duration_ms bigint,
            error_message text,
            details jsonb,
            CONSTRAINT pipeline_step_log_step_check CHECK (step IN ('source', 'transform', 'sink'))
        )
        "#,
    )
    .execute(pool)
    .await?;

    sqlx::query(
        r#"
        CREATE INDEX IF NOT EXISTS idx_pipeline_step_log_pipeline_run_id
            ON pipeline_step_log (pipeline_run_id)
        "#,
    )
    .execute(pool)
    .await?;

    sqlx::query(
        r#"
        CREATE INDEX IF NOT EXISTS idx_pipeline_step_log_created_at
            ON pipeline_step_log (created_at DESC)
        "#,
    )
    .execute(pool)
    .await?;

    sqlx::query(
        r#"
        CREATE INDEX IF NOT EXISTS idx_pipeline_step_log_pipeline_name_created_at
            ON pipeline_step_log (pipeline_name, created_at DESC)
            WHERE pipeline_name IS NOT NULL
        "#,
    )
    .execute(pool)
    .await?;

    Ok(())
}

/// Persists a pipeline step-log row. Callers typically spawn this asynchronously from the route.
pub async fn insert_pipeline_step_log(
    pool: &PgPool,
    entry: PipelineStepLogEntry,
) -> Result<(), sqlx::Error> {
    sqlx::query(
        r#"
        INSERT INTO pipeline_step_log (
            pipeline_run_id, request_id, pipeline_name, step, athena_client, table_name,
            status_code, duration_ms, error_message, details
        )
        VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
        "#,
    )
    .bind(entry.pipeline_run_id)
    .bind(entry.request_id.as_deref())
    .bind(entry.pipeline_name.as_deref())
    .bind(&entry.step)
    .bind(entry.athena_client.as_deref())
    .bind(entry.table_name.as_deref())
    .bind(entry.status_code)
    .bind(entry.duration_ms)
    .bind(entry.error_message.as_deref())
    .bind(entry.details)
    .execute(pool)
    .await?;

    Ok(())
}
