use crate::models::AnalysisResult;
use common_contracts::dtos::NewAnalysisResult;
use sqlx::PgPool;
use uuid::Uuid;

pub async fn create_analysis_result(
    pool: &PgPool,
    payload: &NewAnalysisResult,
) -> Result<AnalysisResult, sqlx::Error> {
    sqlx::query_as::<_, AnalysisResult>(
        r#"
        INSERT INTO analysis_results (request_id, symbol, template_id, module_id, content, meta_data)
        VALUES ($1, $2, $3, $4, $5, $6)
        RETURNING id, request_id, symbol, template_id, module_id, content, meta_data, created_at
        "#
    )
    .bind(&payload.request_id)
    .bind(&payload.symbol)
    .bind(&payload.template_id)
    .bind(&payload.module_id)
    .bind(&payload.content)
    .bind(&payload.meta_data)
    .fetch_one(pool)
    .await
}

pub async fn get_analysis_results(
    pool: &PgPool,
    symbol: &str,
    module_id: Option<&str>,
) -> Result<Vec<AnalysisResult>, sqlx::Error> {
    if let Some(mid) = module_id {
        sqlx::query_as::<_, AnalysisResult>(
            r#"
            SELECT id, request_id, symbol, template_id, module_id, content, meta_data, created_at 
            FROM analysis_results 
            WHERE symbol = $1 AND module_id = $2
            ORDER BY created_at DESC
            "#
        )
        .bind(symbol)
        .bind(mid)
        .fetch_all(pool)
        .await
    } else {
        sqlx::query_as::<_, AnalysisResult>(
            r#"
            SELECT id, request_id, symbol, template_id, module_id, content, meta_data, created_at 
            FROM analysis_results 
            WHERE symbol = $1
            ORDER BY created_at DESC
            "#
        )
        .bind(symbol)
        .fetch_all(pool)
        .await
    }
}

pub async fn get_analysis_result_by_id(
    pool: &PgPool,
    id: Uuid,
) -> Result<Option<AnalysisResult>, sqlx::Error> {
    sqlx::query_as::<_, AnalysisResult>(
        r#"
        SELECT id, request_id, symbol, template_id, module_id, content, meta_data, created_at 
        FROM analysis_results
        WHERE id = $1
        "#
    )
    .bind(&id)
    .fetch_optional(pool)
    .await
}

