use common_contracts::dtos::{CompanyProfileDto, ProviderStatusDto};
use common_contracts::models::CompanyProfile;
use sqlx::PgPool;

pub async fn upsert_company(pool: &PgPool, payload: &CompanyProfileDto) -> Result<(), sqlx::Error> {
    sqlx::query(
        r#"
        INSERT INTO company_profiles (symbol, name, industry, list_date, additional_info, updated_at)
        VALUES ($1, $2, $3, $4, $5, NOW())
        ON CONFLICT (symbol) DO UPDATE SET
            name = EXCLUDED.name,
            industry = EXCLUDED.industry,
            list_date = EXCLUDED.list_date,
            additional_info = CASE 
                -- Preserve existing provider_status in additional_info if it exists
                WHEN company_profiles.additional_info ? 'provider_status' AND EXCLUDED.additional_info IS NOT NULL THEN 
                    jsonb_set(EXCLUDED.additional_info, '{provider_status}', company_profiles.additional_info->'provider_status')
                ELSE EXCLUDED.additional_info 
            END,
            updated_at = NOW()
        "#,
    )
    .bind(&payload.symbol)
    .bind(&payload.name)
    .bind(&payload.industry)
    .bind(&payload.list_date)
    .bind(&payload.additional_info)
    .execute(pool)
    .await?;
    Ok(())
}

pub async fn get_company_by_symbol(pool: &PgPool, symbol: &str) -> Result<Option<CompanyProfile>, sqlx::Error> {
    let rec = sqlx::query_as::<_, CompanyProfile>(
        r#"
        SELECT symbol, name, industry, list_date, additional_info, updated_at
        FROM company_profiles
        WHERE symbol = $1
"#,
    )
    .bind(symbol)
    .fetch_optional(pool)
    .await?;
    Ok(rec)
}

pub async fn update_provider_status(
    pool: &PgPool, 
    symbol: &str, 
    provider_id: &str, 
    status: &ProviderStatusDto
) -> Result<(), sqlx::Error> {
    // Ensure 'provider_status' key exists in additional_info, defaulting to {} if null
    // Then use jsonb_set to update the specific provider's status
    sqlx::query(
        r#"
        UPDATE company_profiles
        SET additional_info = jsonb_set(
            COALESCE(additional_info, '{}'::jsonb),
            '{provider_status}',
            COALESCE(additional_info->'provider_status', '{}'::jsonb) || jsonb_build_object($2::text, $3::jsonb)
        ),
        updated_at = NOW()
        WHERE symbol = $1
        "#
    )
    .bind(symbol)
    .bind(provider_id)
    .bind(serde_json::to_value(status).unwrap()) // Now serializes 'status' as string from enum
    .execute(pool)
    .await?;
    Ok(())
}

pub async fn get_provider_status(
    pool: &PgPool,
    symbol: &str,
    provider_id: &str
) -> Result<Option<ProviderStatusDto>, sqlx::Error> {
    // Use fetch_optional and decode the result manually to handle SQL NULL vs JSON null correctly.
    // The issue was that query_as expects non-null for Option<T> unless the column itself is nullable,
    // but here we are selecting a JSON path which returns SQL NULL if the path doesn't exist.
    // By using fetch_optional with a basic Row, we can inspect the value safely.
    
    let row = sqlx::query(
        r#"
        SELECT additional_info->'provider_status'->$2 as status
        FROM company_profiles
        WHERE symbol = $1
        "#
    )
    .bind(symbol)
    .bind(provider_id)
    .fetch_optional(pool)
    .await?;

    if let Some(row) = row {
        use sqlx::Row;
        // Get the value as Option<serde_json::Value> to handle both SQL NULL and JSON null
        let val: Option<serde_json::Value> = row.try_get("status")?;
        
        if let Some(json_val) = val {
            if !json_val.is_null() {
                let status: ProviderStatusDto = serde_json::from_value(json_val).map_err(|e| sqlx::Error::ColumnDecode { 
                    index: "status".to_string(), 
                    source: Box::new(e) 
                })?;
                return Ok(Some(status));
            }
        }
    }
    Ok(None)
}
