use crate::registry::types::*;
use sqlx::PgPool;

// ===== EUA Minting Services =====

/// Inserts a new EUA minting log record into the database
pub async fn insert_into_eua_minting_log(
    pool: &PgPool,
    req: LogEuaMintingRequest,
) -> Result<EuaMintingLog, sqlx::Error> {
    sqlx::query_as::<_, EuaMintingLog>(
        r#"
        INSERT INTO eua_minting_log (
            company_pubkey, company_name, eua_vault, amount, minted_by, eua_mint
        )
        VALUES ($1, $2, $3, $4, $5, $6)
        RETURNING *
        "#,
    )
    .bind(&req.company_pubkey)
    .bind(&req.company_name)
    .bind(&req.eua_vault)
    .bind(req.amount)
    .bind(&req.minted_by)
    .bind(&req.eua_mint)
    .fetch_one(pool)
    .await
}

/// Selects all EUA minting logs for a specific company
pub async fn select_eua_minting_logs_by_company(
    pool: &PgPool,
    company_pubkey: &str,
) -> Result<Vec<EuaMintingLog>, sqlx::Error> {
    sqlx::query_as::<_, EuaMintingLog>(
        "SELECT * FROM eua_minting_log WHERE company_pubkey = $1 ORDER BY created_at DESC",
    )
    .bind(company_pubkey)
    .fetch_all(pool)
    .await
}

/// Selects a single EUA minting log by its ID
pub async fn select_eua_minting_log_by_id(
    pool: &PgPool,
    log_id: i32,
) -> Result<EuaMintingLog, sqlx::Error> {
    sqlx::query_as::<_, EuaMintingLog>("SELECT * FROM eua_minting_log WHERE id = $1")
        .bind(log_id)
        .fetch_one(pool)
        .await
}

/// Selects all EUA minting logs
pub async fn select_all_eua_minting_logs(pool: &PgPool) -> Result<Vec<EuaMintingLog>, sqlx::Error> {
    sqlx::query_as::<_, EuaMintingLog>("SELECT * FROM eua_minting_log ORDER BY created_at DESC")
        .fetch_all(pool)
        .await
}

// ===== EUA Transfer Services =====

/// Inserts a new EUA transfer log record into the database
pub async fn insert_into_eua_transfer_log(
    pool: &PgPool,
    req: LogEuaTransferRequest,
) -> Result<EuaTransferLog, sqlx::Error> {
    sqlx::query_as::<_, EuaTransferLog>(
        r#"
        INSERT INTO eua_transfer_log (
            from_company_pubkey, from_company_name, to_company_pubkey, to_company_name,
            from_vault, to_vault, amount, authorized_by
        )
        VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
        RETURNING *
        "#,
    )
    .bind(&req.from_company_pubkey)
    .bind(&req.from_company_name)
    .bind(&req.to_company_pubkey)
    .bind(&req.to_company_name)
    .bind(&req.from_vault)
    .bind(&req.to_vault)
    .bind(req.amount)
    .bind(&req.authorized_by)
    .fetch_one(pool)
    .await
}

/// Selects all EUA transfer logs where the company is either sender or receiver
pub async fn select_eua_transfer_logs_by_company(
    pool: &PgPool,
    company_pubkey: &str,
) -> Result<Vec<EuaTransferLog>, sqlx::Error> {
    sqlx::query_as::<_, EuaTransferLog>(
        r#"
        SELECT * FROM eua_transfer_log 
        WHERE from_company_pubkey = $1 OR to_company_pubkey = $1 
        ORDER BY created_at DESC
        "#,
    )
    .bind(company_pubkey)
    .fetch_all(pool)
    .await
}

/// Selects all EUA transfer logs where the company is the sender
pub async fn select_eua_transfer_logs_sent_by_company(
    pool: &PgPool,
    company_pubkey: &str,
) -> Result<Vec<EuaTransferLog>, sqlx::Error> {
    sqlx::query_as::<_, EuaTransferLog>(
        r#"
        SELECT * FROM eua_transfer_log 
        WHERE from_company_pubkey = $1 
        ORDER BY created_at DESC
        "#,
    )
    .bind(company_pubkey)
    .fetch_all(pool)
    .await
}

/// Selects all EUA transfer logs where the company is the receiver
pub async fn select_eua_transfer_logs_received_by_company(
    pool: &PgPool,
    company_pubkey: &str,
) -> Result<Vec<EuaTransferLog>, sqlx::Error> {
    sqlx::query_as::<_, EuaTransferLog>(
        r#"
        SELECT * FROM eua_transfer_log 
        WHERE to_company_pubkey = $1 
        ORDER BY created_at DESC
        "#,
    )
    .bind(company_pubkey)
    .fetch_all(pool)
    .await
}

/// Selects a single EUA transfer log by its ID
pub async fn select_eua_transfer_log_by_id(
    pool: &PgPool,
    log_id: i32,
) -> Result<EuaTransferLog, sqlx::Error> {
    sqlx::query_as::<_, EuaTransferLog>("SELECT * FROM eua_transfer_log WHERE id = $1")
        .bind(log_id)
        .fetch_one(pool)
        .await
}

/// Selects all EUA transfer logs
pub async fn select_all_eua_transfer_logs(
    pool: &PgPool,
) -> Result<Vec<EuaTransferLog>, sqlx::Error> {
    sqlx::query_as::<_, EuaTransferLog>("SELECT * FROM eua_transfer_log ORDER BY created_at DESC")
        .fetch_all(pool)
        .await
}