use rouille::{input::json_input, Request, Response};
use serde_json::json;
use std::sync::Arc;

use super::helpers::{get_conn, json_error, require_auth};
use super::AppState;

pub fn list_pricing(request: &Request, state: &Arc<AppState>) -> Response {
    let mut conn = match get_conn(state) {
        Ok(c) => c,
        Err(e) => return e,
    };
    if let Err(e) = require_auth(request, &mut conn) {
        return e;
    }

    match conn.query(
        "SELECT id, metric_type::text, unit, rate_per_unit::float8, is_active \
         FROM pricing_config ORDER BY metric_type",
        &[],
    ) {
        Ok(rows) => {
            let configs: Vec<_> = rows
                .into_iter()
                .map(|row| {
                    json!({
                        "id": row.get::<_, i32>(0),
                        "metric_type": row.get::<_, String>(1),
                        "unit": row.get::<_, String>(2),
                        "rate_per_unit": row.get::<_, f64>(3),
                        "is_active": row.get::<_, bool>(4),
                    })
                })
                .collect();
            Response::json(&configs)
        }
        Err(_) => json_error(500, "Database error"),
    }
}

pub fn create_pricing(request: &Request, state: &Arc<AppState>) -> Response {
    let config: sap_sync_backend::billing_system::PricingConfig = match json_input(request) {
        Ok(f) => f,
        Err(_) => return json_error(400, "Invalid JSON"),
    };

    let mut conn = match get_conn(state) {
        Ok(c) => c,
        Err(e) => return e,
    };
    if let Err(e) = require_auth(request, &mut conn) {
        return e;
    }

    if let Ok(Some(_)) = conn.query_opt(
        "SELECT id FROM pricing_config WHERE metric_type = $1::text::metric_type",
        &[&config.metric_type],
    ) {
        return json_error(400, "Pricing config already exists");
    }

    let rate_str = config.price_per_unit.to_string();
    match conn.query_one(
        "INSERT INTO pricing_config (metric_type, unit, rate_per_unit, is_active) \
         VALUES ($1::text::metric_type, $2, $3::text::numeric, $4) RETURNING id",
        &[&config.metric_type, &config.unit, &rate_str, &config.is_active],
    ) {
        Ok(r) => Response::json(&json!({
            "id": r.get::<_, i32>(0),
            "metric_type": config.metric_type,
            "unit": config.unit,
            "rate_per_unit": config.price_per_unit,
            "is_active": config.is_active,
        })),
        Err(e) => {
            log::error!("Database error: {}", e);
            json_error(500, "Database error")
        }
    }
}

pub fn list_records(request: &Request, state: &Arc<AppState>) -> Response {
    let mut conn = match get_conn(state) {
        Ok(c) => c,
        Err(e) => return e,
    };
    if let Err(e) = require_auth(request, &mut conn) {
        return e;
    }

    match conn.query(
        "SELECT b.id, b.customer_id, b.subscription_id, b.period_start::text, b.period_end::text, \
         b.calculated_amount::float8, b.currency, b.invoice_status, b.created_at::text, \
         b.sent_to_sap_at IS NOT NULL as sent_to_sap, \
         COALESCE(c.name, 'Customer ' || b.customer_id::text) as customer_name \
         FROM billing_records b \
         LEFT JOIN customers c ON c.id = b.customer_id \
         ORDER BY b.created_at DESC",
        &[],
    ) {
        Ok(rows) => {
            let records: Vec<_> = rows
                .into_iter()
                .map(|row| {
                    json!({
                        "id": row.get::<_, i32>(0),
                        "customer_id": row.get::<_, i32>(1),
                        "subscription_id": row.get::<_, i32>(2),
                        "period_start": row.get::<_, String>(3),
                        "period_end": row.get::<_, String>(4),
                        "calculated_amount": row.get::<_, f64>(5),
                        "currency": row.get::<_, String>(6),
                        "invoice_status": row.get::<_, String>(7),
                        "created_at": row.get::<_, String>(8),
                        "sent_to_sap": row.get::<_, bool>(9),
                        "customer_name": row.get::<_, String>(10),
                    })
                })
                .collect();
            Response::json(&records)
        }
        Err(_) => json_error(500, "Database error"),
    }
}

pub fn generate(request: &Request, state: &Arc<AppState>) -> Response {
    let data: serde_json::Value = match json_input(request) {
        Ok(f) => f,
        Err(_) => return json_error(400, "Invalid JSON"),
    };

    let mut conn = match get_conn(state) {
        Ok(c) => c,
        Err(e) => return e,
    };
    if let Err(e) = require_auth(request, &mut conn) {
        return e;
    }

    let customer_id = data.get("customer_id").and_then(|v| v.as_i64()).unwrap_or(0) as i32;
    let period_start = data.get("period_start").and_then(|v| v.as_str()).unwrap_or_default().to_string();
    let period_end = data.get("period_end").and_then(|v| v.as_str()).unwrap_or_default().to_string();
    let zero = "0".to_string();

    match conn.query_one(
        "INSERT INTO billing_records (customer_id, subscription_id, period_start, period_end, \
         usage_data, calculated_amount, currency, invoice_status) \
         VALUES ($1, $2, $3::date, $4::date, '{}'::jsonb, $5::text::numeric, $6, $7) RETURNING id",
        &[&customer_id, &None::<i32>, &period_start, &period_end, &zero, &"EUR", &"pending"],
    ) {
        Ok(r) => Response::json(&json!({"id": r.get::<_, i32>(0), "message": "Invoice generated successfully"})),
        Err(e) => {
            log::error!("Database error: {}", e);
            json_error(500, "Database error")
        }
    }
}

pub fn preview(request: &Request, state: &Arc<AppState>, id: i32) -> Response {
    let mut conn = match get_conn(state) {
        Ok(c) => c,
        Err(e) => return e,
    };
    if let Err(e) = require_auth(request, &mut conn) {
        return e;
    }

    let record = match conn.query_opt(
        "SELECT b.id, COALESCE(c.name, 'Customer ' || b.customer_id::text), \
         b.period_start::text, b.period_end::text, b.calculated_amount::float8, \
         b.currency, b.invoice_status, b.usage_data::text \
         FROM billing_records b \
         LEFT JOIN customers c ON c.id = b.customer_id \
         WHERE b.id = $1",
        &[&id],
    ) {
        Ok(Some(r)) => r,
        Ok(None) => return json_error(404, "Billing record not found"),
        Err(e) => {
            log::error!("Billing preview query error: {}", e);
            return json_error(500, "Database error");
        }
    };

    let amount: f64 = record.get(4);
    let usage_data_str: String = record.get(7);
    let usage_data: serde_json::Value =
        serde_json::from_str(&usage_data_str).unwrap_or(json!({}));

    // Build line items from usage_data if available
    let line_items: Vec<serde_json::Value> = if let Some(items) = usage_data.as_array() {
        items
            .iter()
            .map(|item| {
                json!({
                    "description": item.get("description").and_then(|v| v.as_str()).unwrap_or("Service"),
                    "quantity": item.get("quantity").and_then(|v| v.as_f64()).unwrap_or(1.0),
                    "unit": item.get("unit").and_then(|v| v.as_str()).unwrap_or("unit"),
                    "rate": item.get("rate").and_then(|v| v.as_f64()).unwrap_or(0.0),
                    "amount": item.get("amount").and_then(|v| v.as_f64()).unwrap_or(0.0),
                })
            })
            .collect()
    } else {
        vec![json!({
            "description": "Hosting Services",
            "quantity": 1,
            "unit": "month",
            "rate": amount,
            "amount": amount,
        })]
    };

    let subtotal = amount;
    let tax = (subtotal * 0.19 * 100.0).round() / 100.0; // 19% VAT
    let total = subtotal + tax;

    Response::json(&json!({
        "customer_name": record.get::<_, String>(1),
        "period_start": record.get::<_, String>(2),
        "period_end": record.get::<_, String>(3),
        "line_items": line_items,
        "subtotal": subtotal,
        "tax": tax,
        "total": total,
        "currency": record.get::<_, String>(5),
    }))
}

pub fn send_to_sap_by_id(request: &Request, state: &Arc<AppState>, id: i32) -> Response {
    let mut conn = match get_conn(state) {
        Ok(c) => c,
        Err(e) => return e,
    };
    if let Err(e) = require_auth(request, &mut conn) {
        return e;
    }

    let _ = conn.execute(
        "UPDATE billing_records SET sent_to_sap_at = NOW() WHERE id = $1",
        &[&id],
    );
    Response::json(&json!({"message": "Invoice sent to SAP successfully"}))
}

pub fn send_to_sap(request: &Request, state: &Arc<AppState>) -> Response {
    let form: sap_sync_backend::models::BillingRecordId = match json_input(request) {
        Ok(f) => f,
        Err(_) => return json_error(400, "Invalid JSON"),
    };

    let mut conn = match get_conn(state) {
        Ok(c) => c,
        Err(e) => return e,
    };
    if let Err(e) = require_auth(request, &mut conn) {
        return e;
    }

    let _ = conn.execute(
        "UPDATE billing_records SET sent_to_sap_at = NOW() WHERE id = $1",
        &[&form.id],
    );
    Response::json(&json!({"message": "Invoice sent to SAP successfully"}))
}
