use loco_rs::prelude::*;
use sea_orm::entity::prelude::*;
use sea_orm::{Set, TransactionTrait};
use uuid::Uuid;

use crate::models::_entities::{order_items, product_variants, products, shipping_methods};
use crate::models::users;
use crate::shared::pricing;
pub use crate::models::_entities::orders::{ActiveModel, Column, Entity, Model};
pub type Orders = Entity;

/// The customer-supplied and carrier details needed to place an order. Prices
/// and product names are never taken from here — they are snapshotted from the
/// database inside [`place`] so the customer cannot influence what they pay.
pub struct Checkout {
    pub email: String,
    pub phone: String,
    pub customer_name: Option<String>,
    /// The account that owns this order, if any (a logged-in buyer or a guest
    /// who created an account during checkout). `None` for pure guest orders.
    pub user_id: Option<i32>,
    pub account_type: String,
    pub company_name: Option<String>,
    pub company_id: Option<String>,
    pub tax_id: Option<String>,
    pub vat_id: Option<String>,
    pub residence_address: Option<String>,
    pub residence_city: Option<String>,
    pub residence_zip: Option<String>,
    pub residence_country: Option<String>,
    pub address: Option<String>,
    pub city: Option<String>,
    pub zip: Option<String>,
    pub country: Option<String>,
    pub note: Option<String>,
    pub payment_method: String,
    pub method: shipping_methods::Model,
    pub pickup_point_id: Option<String>,
    pub pickup_point_name: Option<String>,
}

fn generate_order_number() -> String {
    let suffix = Uuid::new_v4().simple().to_string()[..8].to_uppercase();
    format!("ORD-{suffix}")
}

/// Atomically place an order for the given `(variant_id, quantity)` lines:
/// snapshot each variant's price/name/label, decrement its stock (re-checking
/// inside the transaction so an item can't oversell between cart and pay), then
/// write the order and its line items.
///
/// `expected_total_cents` is the order total (items + shipping) the buyer
/// confirmed on the payment page. When set, the total this call is about to
/// charge is compared against it *using the same prices that build the order*;
/// if they differ (an item or shipping price changed since the buyer looked),
/// the transaction is rolled back and `Ok(None)` is returned so the caller can
/// send the buyer back to re-confirm — they are never charged a stale or a
/// surprise price. `Ok(Some(order))` is the placed order.
pub async fn place(
    ctx: &AppContext,
    items: &[(i32, i32)],
    details: Checkout,
    user: Option<&users::Model>,
    expected_total_cents: Option<i64>,
) -> Result<Option<Model>> {
    // Resolve the price of every line *before* opening the transaction. Pricing
    // loads its context from the connection pool; doing it while the order
    // transaction holds a connection would acquire a second one and can exhaust
    // the pool (it times out under contention or a small pool). Prices are stable
    // within a request, so this snapshot is what we charge; stock is still
    // re-validated against the transaction below.
    let line_variants = product_variants::Entity::find()
        .filter(product_variants::Column::Id.is_in(items.iter().map(|(id, _)| *id)))
        .all(&ctx.db)
        .await?;
    let priced = pricing::price_variants(ctx, &line_variants, user).await?;
    let price_by_variant: std::collections::HashMap<i32, i64> = line_variants
        .iter()
        .zip(priced)
        .map(|(v, p)| (v.id, p.price_cents))
        .collect();

    let txn = ctx.db.begin().await?;

    let mut subtotal: i64 = 0;
    let mut snapshots = Vec::new();
    for (variant_id, qty) in items {
        let variant = product_variants::Entity::find_by_id(*variant_id)
            .one(&txn)
            .await?
            .ok_or_else(|| Error::BadRequest("an item is no longer available".to_string()))?;
        let product = products::Entity::find_by_id(variant.product_id)
            .filter(products::Column::Published.eq(true))
            .one(&txn)
            .await?
            .ok_or_else(|| Error::BadRequest("a product is no longer available".to_string()))?;
        // Tracked variants can't oversell; untracked ones (stock = None) are
        // always available and never decremented.
        if let Some(on_hand) = variant.stock {
            if on_hand < *qty {
                return Err(Error::BadRequest(format!(
                    "not enough stock for {}",
                    product.name
                )));
            }
        }
        // The price the buyer actually pays — public sale or, for a business
        // account, their negotiated/lowest price (resolved above, outside the
        // transaction, with the same resolver the cart and storefront use).
        let unit_price_cents = *price_by_variant
            .get(&variant.id)
            .ok_or_else(|| Error::BadRequest("an item is no longer available".to_string()))?;
        subtotal += unit_price_cents * i64::from(*qty);

        if let Some(on_hand) = variant.stock {
            let mut active = variant.clone().into_active_model();
            active.stock = Set(Some(on_hand - *qty));
            active.update(&txn).await?;
        }

        snapshots.push((product.id, variant.id, product.name, variant.label, unit_price_cents, *qty));
    }

    let charged_total = subtotal + details.method.price_cents;

    // If the buyer confirmed a total that no longer matches what we'd charge (an
    // item or shipping price moved in the meantime), abort without placing. The
    // transaction is dropped without committing, rolling back the stock
    // decrements above.
    if let Some(expected) = expected_total_cents {
        if expected != charged_total {
            return Ok(None);
        }
    }

    let order = ActiveModel {
        order_number: Set(generate_order_number()),
        email: Set(details.email),
        phone: Set(Some(details.phone)),
        customer_name: Set(details.customer_name),
        status: Set("pending".to_string()),
        total_cents: Set(charged_total),
        user_id: Set(details.user_id),
        account_type: Set(details.account_type),
        company_name: Set(details.company_name),
        company_id: Set(details.company_id),
        tax_id: Set(details.tax_id),
        vat_id: Set(details.vat_id),
        residence_address: Set(details.residence_address),
        residence_city: Set(details.residence_city),
        residence_zip: Set(details.residence_zip),
        residence_country: Set(details.residence_country),
        address: Set(details.address),
        city: Set(details.city),
        zip: Set(details.zip),
        country: Set(details.country),
        note: Set(details.note),
        payment_method: Set(Some(details.payment_method)),
        carrier_code: Set(Some(details.method.code)),
        carrier_name: Set(Some(details.method.name)),
        shipping_cents: Set(details.method.price_cents),
        pickup_point_id: Set(details.pickup_point_id),
        pickup_point_name: Set(details.pickup_point_name),
        ..Default::default()
    }
    .insert(&txn)
    .await?;

    for (product_id, variant_id, name, variant_label, unit_price_cents, qty) in snapshots {
        order_items::ActiveModel {
            order_id: Set(order.id),
            product_id: Set(Some(product_id)),
            variant_id: Set(Some(variant_id)),
            product_name: Set(name),
            variant_label: Set(variant_label),
            unit_price_cents: Set(unit_price_cents),
            quantity: Set(qty),
            ..Default::default()
        }
        .insert(&txn)
        .await?;
    }

    txn.commit().await?;
    Ok(Some(order))
}

#[async_trait::async_trait]
impl ActiveModelBehavior for ActiveModel {
    async fn before_save<C>(self, _db: &C, insert: bool) -> std::result::Result<Self, DbErr>
    where
        C: ConnectionTrait,
    {
        if !insert && self.updated_at.is_unchanged() {
            let mut this = self;
            this.updated_at = sea_orm::ActiveValue::Set(chrono::Utc::now().into());
            Ok(this)
        } else {
            Ok(self)
        }
    }
}

// implement your read-oriented logic here
impl Model {}

// implement your write-oriented logic here
impl ActiveModel {}

// implement your custom finders, selectors oriented logic here
impl Entity {
    /// Admin order search: a diacritic- and case-insensitive substring match over
    /// the free-text order fields an admin would actually type — order number,
    /// email, customer name, company name, phone and tracking number. Backed by
    /// the trigram indexes from the `order_search_indexes` migration. Newest
    /// first, capped at `limit`. A blank query returns nothing (callers fall back
    /// to the full list).
    pub async fn search<C: sea_orm::ConnectionTrait>(
        db: &C,
        query: &str,
        limit: u64,
    ) -> Result<Vec<Model>, DbErr> {
        let q = query.trim();
        if q.is_empty() {
            return Ok(Vec::new());
        }
        // Treat the query literally: escape LIKE wildcards, then wrap in %…%.
        let escaped = q.replace('\\', "\\\\").replace('%', "\\%").replace('_', "\\_");
        let pattern = format!("%{escaped}%");

        let sql = r#"
            SELECT * FROM orders o
            WHERE f_unaccent(o.order_number)               ILIKE f_unaccent($1)
               OR f_unaccent(o.email)                      ILIKE f_unaccent($1)
               OR f_unaccent(COALESCE(o.customer_name,'')) ILIKE f_unaccent($1)
               OR f_unaccent(COALESCE(o.company_name,''))  ILIKE f_unaccent($1)
               OR f_unaccent(COALESCE(o.phone,''))         ILIKE f_unaccent($1)
               OR f_unaccent(COALESCE(o.tracking_number,'')) ILIKE f_unaccent($1)
            ORDER BY o.created_at DESC
            LIMIT $2
        "#;

        Entity::find()
            .from_raw_sql(sea_orm::Statement::from_sql_and_values(
                db.get_database_backend(),
                sql,
                [pattern.into(), (limit as i64).into()],
            ))
            .all(db)
            .await
    }
}
