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

// ===== Company Services =====
pub async fn create_company(
    pool: &PgPool,
    req: CreateCompanyRequest,
) -> Result<Company, sqlx::Error> {
    // Start transaction
    let mut tx = pool.begin().await?;

    // Insert company with new fields initialized to defaults
    let company = sqlx::query_as::<_, Company>(
        r#"
        INSERT INTO companies (
            company_pubkey, company_name, company_admin_pubkey, local_admin_pubkey,
            eua_vault, ghg_vault, usdc_vault, registry_pubkey,
            is_active, locked_eua_amount, locked_usdc_amount,
            in_auction, placed_bid, auction_id,
            usdc_balance, eua_balance, ghg_balance
        )
        VALUES ($1, $2, $3, $4, $5, $6, $7, $8, true, 0, 0, false, false, NULL, 0, 0, 0)
        RETURNING *
        "#,
    )
    .bind(&req.company_pubkey)
    .bind(&req.company_name)
    .bind(&req.company_admin_pubkey)
    .bind(&req.local_admin_pubkey)
    .bind(&req.eua_vault)
    .bind(&req.ghg_vault)
    .bind(&req.usdc_vault) //we dont add any balances i guess we dont have any
    .bind(&req.registry_pubkey)
    .fetch_one(&mut *tx)
    .await?;

    // Increment companies_managed count for local admin
    sqlx::query(
        r#"
        UPDATE local_admins
        SET companies_managed = companies_managed + 1, updated_at = NOW()
        WHERE local_admin_pubkey = $1
        "#,
    )
    .bind(&req.local_admin_pubkey)
    .execute(&mut *tx)
    .await?;

    tx.commit().await?;
    Ok(company)
}

pub async fn update_company_status(
    pool: &PgPool,
    req: UpdateCompanyStatusRequest,
) -> Result<Company, sqlx::Error> {
    let result = sqlx::query_as::<_, Company>(
        r#"
        UPDATE companies
        SET is_active = $2, updated_at = NOW()
        WHERE company_pubkey = $1
        RETURNING *
        "#,
    )
    .bind(&req.company_pubkey)
    .bind(req.is_active)
    .fetch_one(pool)
    .await?;

    Ok(result)
}

pub async fn get_company(pool: &PgPool, company_pubkey: &str) -> Result<Company, sqlx::Error> {
    sqlx::query_as::<_, Company>("SELECT * FROM companies WHERE company_pubkey = $1")
        .bind(company_pubkey)
        .fetch_one(pool)
        .await
}

pub async fn get_company_by_admin(
    pool: &PgPool,
    company_admin_pubkey: &str,
) -> Result<Company, sqlx::Error> {
    sqlx::query_as::<_, Company>(
        "SELECT * FROM companies WHERE company_admin_pubkey = $1"
    )
    .bind(company_admin_pubkey)
    .fetch_one(pool)
    .await
}

pub async fn list_companies(pool: &PgPool) -> Result<Vec<Company>, sqlx::Error> {
    sqlx::query_as::<_, Company>("SELECT * FROM companies ORDER BY created_at DESC")
        .fetch_all(pool)
        .await
}

pub async fn list_companies_by_local_admin(
    pool: &PgPool,
    local_admin_pubkey: &str,
) -> Result<Vec<Company>, sqlx::Error> {
    sqlx::query_as::<_, Company>(
        "SELECT * FROM companies WHERE local_admin_pubkey = $1 ORDER BY created_at DESC",
    )
    .bind(local_admin_pubkey)
    .fetch_all(pool)
    .await
}

// ===== Balance Management Services =====
pub async fn update_usdc_balance(
    pool: &PgPool,
    req: UpdateUsdcBalanceRequest,
) -> Result<Company, sqlx::Error> {
    tracing::info!(
        "updating USDC balance: company_pubkey={}, new_balance={}",
        req.company_pubkey,
        req.usdc_balance
    );

    sqlx::query_as::<_, Company>(
        r#"
        UPDATE companies
        SET usdc_balance = $2, updated_at = NOW()
        WHERE company_pubkey = $1
        RETURNING *
        "#,
    )
    .bind(&req.company_pubkey)
    .bind(req.usdc_balance)
    .fetch_one(pool)
    .await
}

pub async fn update_eua_balance(
    pool: &PgPool,
    req: UpdateEuaBalanceRequest,
) -> Result<Company, sqlx::Error> {
    tracing::info!(
        "updating EUA balance: company_pubkey={}, new_balance={}",
        req.company_pubkey,
        req.eua_balance
    );

    sqlx::query_as::<_, Company>(
        r#"
        UPDATE companies
        SET eua_balance = $2, updated_at = NOW()
        WHERE company_pubkey = $1
        RETURNING *
        "#,
    )
    .bind(&req.company_pubkey)
    .bind(req.eua_balance)
    .fetch_one(pool)
    .await
}

pub async fn update_ghg_balance(
    pool: &PgPool,
    req: UpdateGhgBalanceRequest,
) -> Result<Company, sqlx::Error> {
    tracing::info!(
        "updating GHG balance: company_pubkey={}, new_balance={}",
        req.company_pubkey,
        req.ghg_balance
    );

    sqlx::query_as::<_, Company>(
        r#"
        UPDATE companies
        SET ghg_balance = $2, updated_at = NOW()
        WHERE company_pubkey = $1
        RETURNING *
        "#,
    )
    .bind(&req.company_pubkey)
    .bind(req.ghg_balance)
    .fetch_one(pool)
    .await
}

// ===== Auction Management Services =====
pub async fn update_auction_status(
    pool: &PgPool,
    req: UpdateAuctionStatusRequest,
) -> Result<Company, sqlx::Error> {
    sqlx::query_as::<_, Company>(
        r#"
        UPDATE companies
        SET in_auction = $2, auction_id = $3, updated_at = NOW()
        WHERE company_pubkey = $1
        RETURNING *
        "#,
    )
    .bind(&req.company_pubkey)
    .bind(req.in_auction)
    .bind(req.auction_id)
    .fetch_one(pool)
    .await
}

pub async fn update_bid_status(
    pool: &PgPool,
    req: UpdateBidStatusRequest,
) -> Result<Company, sqlx::Error> {
    sqlx::query_as::<_, Company>(
        r#"
        UPDATE companies
        SET placed_bid = $2, updated_at = NOW()
        WHERE company_pubkey = $1
        RETURNING *
        "#,
    )
    .bind(&req.company_pubkey)
    .bind(req.placed_bid)
    .fetch_one(pool)
    .await
}

pub async fn list_companies_in_auction(
    pool: &PgPool,
    auction_id: i64,
) -> Result<Vec<Company>, sqlx::Error> {
    sqlx::query_as::<_, Company>(
        "SELECT * FROM companies WHERE auction_id = $1 AND in_auction = true ORDER BY created_at DESC",
    )
    .bind(auction_id)
    .fetch_all(pool)
    .await
}

// ===== Locking Services =====
pub async fn lock_eua(
    pool: &PgPool,
    req: LockEuaRequest,
) -> Result<Company, sqlx::Error> {
    sqlx::query_as::<_, Company>(
        r#"
        UPDATE companies
        SET locked_eua_amount = locked_eua_amount + $2, updated_at = NOW()
        WHERE company_pubkey = $1
        RETURNING *
        "#,
    )
    .bind(&req.company_pubkey)
    .bind(req.amount)
    .fetch_one(pool)
    .await
}

pub async fn unlock_eua(
    pool: &PgPool,
    req: UnlockEuaRequest,
) -> Result<Company, sqlx::Error> {
    sqlx::query_as::<_, Company>(
        r#"
        UPDATE companies
        SET locked_eua_amount = CASE 
            WHEN locked_eua_amount >= $2 THEN locked_eua_amount - $2
            ELSE 0
        END,
        updated_at = NOW()
        WHERE company_pubkey = $1
        RETURNING *
        "#,
    )
    .bind(&req.company_pubkey)
    .bind(req.amount)
    .fetch_one(pool)
    .await
}

pub async fn lock_usdc(
    pool: &PgPool,
    req: LockUsdcRequest,
) -> Result<Company, sqlx::Error> {
    sqlx::query_as::<_, Company>(
        r#"
        UPDATE companies
        SET locked_usdc_amount = locked_usdc_amount + $2, updated_at = NOW()
        WHERE company_pubkey = $1
        RETURNING *
        "#,
    )
    .bind(&req.company_pubkey)
    .bind(req.amount)
    .fetch_one(pool)
    .await
}

pub async fn unlock_usdc(
    pool: &PgPool,
    req: UnlockUsdcRequest,
) -> Result<Company, sqlx::Error> {
    sqlx::query_as::<_, Company>(
        r#"
        UPDATE companies
        SET locked_usdc_amount = CASE 
            WHEN locked_usdc_amount >= $2 THEN locked_usdc_amount - $2
            ELSE 0
        END,
        updated_at = NOW()
        WHERE company_pubkey = $1
        RETURNING *
        "#,
    )
    .bind(&req.company_pubkey)
    .bind(req.amount)
    .fetch_one(pool)
    .await
}