use anyhow::{anyhow, Result};
use athena_rs::client::AthenaClient;
use athena_rs::OrderDirection;
use serde::{Deserialize, Serialize};
use serde_json::Value;

const TABLE: &str = "data_source_provider_requests";

#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct DataSourceProviderRequest {
    pub id: i64,
    pub example_url: String,
    #[serde(default)]
    pub structure_notes: Option<String>,
    #[serde(default)]
    pub harvestable_fields: Option<Value>,
    #[serde(default)]
    pub reliability: Option<String>,
    pub status: String,
    #[serde(default)]
    pub screenshot_s3_key: Option<String>,
    #[serde(default)]
    pub implementation_notes: Option<String>,
    #[serde(default)]
    pub created_at: Option<String>,
    #[serde(default)]
    pub updated_at: Option<String>,
}

/// Payload for creating a row (no `id`).
#[derive(Debug, Clone, Serialize, Deserialize)]
pub struct DataSourceProviderRequestNew {
    pub example_url: String,
    #[serde(default)]
    pub structure_notes: Option<String>,
    #[serde(default)]
    pub harvestable_fields: Option<Value>,
    #[serde(default)]
    pub reliability: Option<String>,
    #[serde(default)]
    pub status: Option<String>,
    #[serde(default)]
    pub screenshot_s3_key: Option<String>,
    #[serde(default)]
    pub implementation_notes: Option<String>,
}

impl DataSourceProviderRequest {
    pub fn from_athena_row(row: &Value) -> Result<Self> {
        let o = row.as_object().ok_or_else(|| anyhow!("provider request row not an object"))?;
        let gv = |k: &str| o.get(k).cloned();
        let id = gv("id")
            .and_then(|v| v.as_i64().or_else(|| v.as_f64().map(|f| f as i64)))
            .or_else(|| gv("id").and_then(|v| v.as_str().and_then(|s| s.parse().ok())))
            .ok_or_else(|| anyhow!("missing id"))?;
        let example_url = gv("example_url")
            .and_then(|v| v.as_str().map(String::from))
            .ok_or_else(|| anyhow!("missing example_url"))?;
        let status = gv("status")
            .and_then(|v| v.as_str().map(String::from))
            .unwrap_or_else(|| "pending".to_string());
        let structure_notes = gv("structure_notes").and_then(|v| {
            if v.is_null() {
                None
            } else {
                v.as_str().map(String::from)
            }
        });
        let harvestable_fields = gv("harvestable_fields").and_then(|v| if v.is_null() { None } else { Some(v) });
        let reliability = gv("reliability").and_then(|v| {
            if v.is_null() {
                None
            } else {
                v.as_str().map(String::from)
            }
        });
        let screenshot_s3_key = gv("screenshot_s3_key").and_then(|v| {
            if v.is_null() {
                None
            } else {
                v.as_str().map(String::from)
            }
        });
        let implementation_notes = gv("implementation_notes").and_then(|v| {
            if v.is_null() {
                None
            } else {
                v.as_str().map(String::from)
            }
        });
        let created_at = gv("created_at").and_then(|v| v.as_str().map(String::from));
        let updated_at = gv("updated_at").and_then(|v| v.as_str().map(String::from));

        Ok(Self {
            id,
            example_url,
            structure_notes,
            harvestable_fields,
            reliability,
            status,
            screenshot_s3_key,
            implementation_notes,
            created_at,
            updated_at,
        })
    }
}

impl DataSourceProviderRequestNew {
    pub fn to_insert_payload(&self) -> Value {
        let status = self.status.clone().unwrap_or_else(|| "pending".to_string());
        let mut v = serde_json::json!({
            "example_url": self.example_url,
            "structure_notes": self.structure_notes,
            "harvestable_fields": self.harvestable_fields.clone(),
            "reliability": self.reliability,
            "status": status,
            "screenshot_s3_key": self.screenshot_s3_key,
            "implementation_notes": self.implementation_notes,
        });
        if let Value::Object(ref mut o) = v {
            o.insert("updated_at".into(), Value::String(chrono::Utc::now().to_rfc3339()));
        }
        v
    }
}

pub async fn list_requests(client: &AthenaClient) -> Result<Vec<DataSourceProviderRequest>> {
    let res = client
        .fetch(TABLE)
        .where_neq("example_url", "")
        .order_by("id", OrderDirection::Desc)
        .limit(1000)
        .execute()
        .await
        .map_err(|e| anyhow!("fetch {}: {}", TABLE, e))?;
    let mut out = Vec::new();
    for row in &res.rows {
        out.push(DataSourceProviderRequest::from_athena_row(row)?);
    }
    Ok(out)
}

pub async fn get_request_by_id(client: &AthenaClient, id: i64) -> Result<Option<DataSourceProviderRequest>> {
    let res = client
        .fetch(TABLE)
        .where_eq("id", id)
        .limit(1)
        .execute()
        .await
        .map_err(|e| anyhow!("fetch provider request: {}", e))?;
    let Some(row) = res.rows.first() else {
        return Ok(None);
    };
    Ok(Some(DataSourceProviderRequest::from_athena_row(row)?))
}

pub async fn insert_request(client: &AthenaClient, body: &DataSourceProviderRequestNew) -> Result<()> {
    if body.example_url.trim().is_empty() {
        return Err(anyhow!("example_url required"));
    }
    client
        .insert(TABLE)
        .payload(body.to_insert_payload())
        .execute()
        .await
        .map_err(|e| anyhow!("insert provider request: {}", e))?;
    Ok(())
}

pub async fn update_request(client: &AthenaClient, id: i64, body: &DataSourceProviderRequestNew) -> Result<()> {
    if body.example_url.trim().is_empty() {
        return Err(anyhow!("example_url required"));
    }
    let status = body.status.clone().unwrap_or_else(|| "pending".to_string());
    let payload = serde_json::json!({
        "example_url": body.example_url,
        "structure_notes": body.structure_notes,
        "harvestable_fields": body.harvestable_fields.clone(),
        "reliability": body.reliability,
        "status": status,
        "screenshot_s3_key": body.screenshot_s3_key,
        "implementation_notes": body.implementation_notes,
        "updated_at": chrono::Utc::now().to_rfc3339(),
    });
    client
        .update(TABLE)
        .where_eq("id", id)
        .payload(payload)
        .execute()
        .await
        .map_err(|e| anyhow!("update provider request: {}", e))?;
    Ok(())
}

pub async fn set_screenshot_key(client: &AthenaClient, id: i64, key: &str) -> Result<()> {
    let payload = serde_json::json!({
        "screenshot_s3_key": key,
        "updated_at": chrono::Utc::now().to_rfc3339(),
    });
    client
        .update(TABLE)
        .where_eq("id", id)
        .payload(payload)
        .execute()
        .await
        .map_err(|e| anyhow!("update screenshot key: {}", e))?;
    Ok(())
}

pub async fn delete_request(client: &AthenaClient, id: i64) -> Result<()> {
    client
        .delete(TABLE)
        .where_eq("id", id)
        .execute()
        .await
        .map_err(|e| anyhow!("delete provider request: {}", e))?;
    Ok(())
}
