use chrono::NaiveDate;
use common_contracts::dtos::{DailyMarketDataDto, RealtimeQuoteDto, TimeSeriesFinancialDto};
use common_contracts::models::{DailyMarketData, RealtimeQuote, TimeSeriesFinancial};
use sqlx::PgPool;

pub async fn batch_insert_financials(pool: &PgPool, records: &[TimeSeriesFinancialDto]) -> Result<(), sqlx::Error> {
    let mut tx = pool.begin().await?;
    for r in records {
        sqlx::query(
            r#"
            INSERT INTO time_series_financials (symbol, metric_name, period_date, value, source)
            VALUES ($1, $2, $3, $4, $5)
            ON CONFLICT (symbol, metric_name, period_date) DO UPDATE SET
                value = EXCLUDED.value,
                source = EXCLUDED.source
            "#,
        )
        .bind(&r.symbol)
        .bind(&r.metric_name)
        .bind(&r.period_date)
        .bind(&r.value)
        .bind(&r.source)
        .execute(&mut *tx)
        .await?;
    }
    tx.commit().await?;
    Ok(())
}

pub async fn get_financials_by_symbol(
    pool: &PgPool,
    symbol: &str,
    metrics: Option<Vec<String>>,
) -> Result<Vec<TimeSeriesFinancial>, sqlx::Error> {
    if let Some(metrics) = metrics {
        let recs = sqlx::query_as::<_, TimeSeriesFinancial>(
            r#"
            SELECT symbol, metric_name, period_date, value, source
            FROM time_series_financials
            WHERE symbol = $1 AND metric_name = ANY($2)
            ORDER BY period_date DESC
            "#,
        )
        .bind(symbol)
        .bind(&metrics)
        .fetch_all(pool)
        .await?;
        Ok(recs)
    } else {
        let recs = sqlx::query_as::<_, TimeSeriesFinancial>(
            r#"
            SELECT symbol, metric_name, period_date, value, source
            FROM time_series_financials
            WHERE symbol = $1
            ORDER BY period_date DESC
            "#,
        )
        .bind(symbol)
        .fetch_all(pool)
        .await?;
        Ok(recs)
    }
}

pub async fn insert_realtime_quote(pool: &PgPool, q: &RealtimeQuoteDto) -> Result<(), sqlx::Error> {
    sqlx::query(
        r#"
        INSERT INTO realtime_quotes (
            symbol, market, ts, price, open_price, high_price, low_price,
            prev_close, change, change_percent, volume, source, updated_at
        )
        VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, NOW())
        ON CONFLICT (symbol, market, ts) DO UPDATE SET
            price = EXCLUDED.price,
            open_price = EXCLUDED.open_price,
            high_price = EXCLUDED.high_price,
            low_price = EXCLUDED.low_price,
            prev_close = EXCLUDED.prev_close,
            change = EXCLUDED.change,
            change_percent = EXCLUDED.change_percent,
            volume = EXCLUDED.volume,
            source = EXCLUDED.source,
            updated_at = NOW()
        "#,
    )
    .bind(&q.symbol)
    .bind(&q.market)
    .bind(&q.ts)
    .bind(&q.price)
    .bind(&q.open_price)
    .bind(&q.high_price)
    .bind(&q.low_price)
    .bind(&q.prev_close)
    .bind(&q.change)
    .bind(&q.change_percent)
    .bind(&q.volume)
    .bind(&q.source)
    .execute(pool)
    .await?;
    Ok(())
}

pub async fn get_latest_realtime_quote(
    pool: &PgPool,
    market: &str,
    symbol: &str,
) -> Result<Option<RealtimeQuote>, sqlx::Error> {
    let rec = sqlx::query_as::<_, RealtimeQuote>(
        r#"
        SELECT symbol, market, ts, price, open_price, high_price, low_price,
               prev_close, change, change_percent, volume, source, updated_at
        FROM realtime_quotes
        WHERE market = $1 AND symbol = $2
        ORDER BY ts DESC
        LIMIT 1
        "#,
    )
    .bind(market)
    .bind(symbol)
    .fetch_optional(pool)
    .await?;
    Ok(rec)
}

pub async fn batch_insert_daily_data(pool: &PgPool, records: &[DailyMarketDataDto]) -> Result<(), sqlx::Error> {
    let mut tx = pool.begin().await?;
    for r in records {
        sqlx::query(
            r#"
            INSERT INTO daily_market_data (
                symbol, trade_date, open_price, high_price, low_price, close_price,
                volume, pe, pb, total_mv
            )
            VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
            ON CONFLICT (symbol, trade_date) DO UPDATE SET
                open_price = EXCLUDED.open_price,
                high_price = EXCLUDED.high_price,
                low_price = EXCLUDED.low_price,
                close_price = EXCLUDED.close_price,
                volume = EXCLUDED.volume,
                pe = EXCLUDED.pe,
                pb = EXCLUDED.pb,
                total_mv = EXCLUDED.total_mv
            "#,
        )
        .bind(&r.symbol)
        .bind(&r.trade_date)
        .bind(&r.open_price)
        .bind(&r.high_price)
        .bind(&r.low_price)
        .bind(&r.close_price)
        .bind(&r.volume)
        .bind(&r.pe)
        .bind(&r.pb)
        .bind(&r.total_mv)
        .execute(&mut *tx)
        .await?;
    }
    tx.commit().await?;
    Ok(())
}

pub async fn get_daily_data_by_symbol(
    pool: &PgPool,
    symbol: &str,
    start_date: Option<NaiveDate>,
    end_date: Option<NaiveDate>,
) -> Result<Vec<DailyMarketData>, sqlx::Error> {
    match (start_date, end_date) {
        (Some(start), Some(end)) => {
            let recs = sqlx::query_as::<_, DailyMarketData>(
                r#"
                SELECT symbol, trade_date, open_price, high_price, low_price, close_price,
                       volume, pe, pb, total_mv
                FROM daily_market_data
                WHERE symbol = $1 AND trade_date BETWEEN $2 AND $3
                ORDER BY trade_date DESC
                "#,
            )
            .bind(symbol)
            .bind(start)
            .bind(end)
            .fetch_all(pool)
            .await?;
            Ok(recs)
        }
        (Some(start), None) => {
            let recs = sqlx::query_as::<_, DailyMarketData>(
                r#"
                SELECT symbol, trade_date, open_price, high_price, low_price, close_price,
                       volume, pe, pb, total_mv
                FROM daily_market_data
                WHERE symbol = $1 AND trade_date >= $2
                ORDER BY trade_date DESC
                "#,
            )
            .bind(symbol)
            .bind(start)
            .fetch_all(pool)
            .await?;
            Ok(recs)
        }
        (None, Some(end)) => {
            let recs = sqlx::query_as::<_, DailyMarketData>(
                r#"
                SELECT symbol, trade_date, open_price, high_price, low_price, close_price,
                       volume, pe, pb, total_mv
                FROM daily_market_data
                WHERE symbol = $1 AND trade_date <= $2
                ORDER BY trade_date DESC
                "#,
            )
            .bind(symbol)
            .bind(end)
            .fetch_all(pool)
            .await?;
            Ok(recs)
        }
        (None, None) => {
            let recs = sqlx::query_as::<_, DailyMarketData>(
                r#"
                SELECT symbol, trade_date, open_price, high_price, low_price, close_price,
                       volume, pe, pb, total_mv
                FROM daily_market_data
                WHERE symbol = $1
                ORDER BY trade_date DESC
                "#,
            )
            .bind(symbol)
            .fetch_all(pool)
            .await?;
            Ok(recs)
        }
    }
}


