src/application/services/payout.rs

use crate::domain::error::{AppError, AppResult};
use crate::domain::models::{
    AddBankAccountRequest, InitiatePayoutRequest, MerchantBankAccount, NotificationLog, Payout,
    PayoutSummary,
};
use crate::infrastructure::db::DbPool;
use uuid::Uuid;

// ========================================================
// ADVANCED OOPS CONCEPTS: STRATEGY & FACTORY FEE SCHEDULES
// ========================================================

/// Strategy trait for calculating payouts transaction fees.
pub trait PayoutFeeStrategy: Send + Sync {
    fn calculate_fee(&self) -> f64;
}

pub struct ImpsPayoutFeeStrategy;
impl PayoutFeeStrategy for ImpsPayoutFeeStrategy {
    fn calculate_fee(&self) -> f64 {
        5.00
    }
}

pub struct RtgsPayoutFeeStrategy;
impl PayoutFeeStrategy for RtgsPayoutFeeStrategy {
    fn calculate_fee(&self) -> f64 {
        25.00
    }
}

pub struct UpiPayoutFeeStrategy;
impl PayoutFeeStrategy for UpiPayoutFeeStrategy {
    fn calculate_fee(&self) -> f64 {
        0.00
    }
}

pub struct NeftPayoutFeeStrategy;
impl PayoutFeeStrategy for NeftPayoutFeeStrategy {
    fn calculate_fee(&self) -> f64 {
        2.50
    }
}

/// Factory pattern to retrieve the registered payout fee strategy dynamically.
pub struct PayoutStrategyFactory;

impl PayoutStrategyFactory {
    pub fn get_strategy(mode: &str) -> Box<dyn PayoutFeeStrategy> {
        match mode {
            "IMPS" => Box::new(ImpsPayoutFeeStrategy),
            "RTGS" => Box::new(RtgsPayoutFeeStrategy),
            "UPI" => Box::new(UpiPayoutFeeStrategy),
            _ => Box::new(NeftPayoutFeeStrategy),
        }
    }
}

// ========================================================
// PAYOUT SERVICE IMPLEMENTATION
// ========================================================

pub struct PayoutService {
    pool: DbPool,
}

impl PayoutService {
    pub fn new(pool: DbPool) -> Self {
        Self { pool }
    }

    // ─── Bank Accounts ────────────────────────────────────────────────────────

    pub async fn add_bank_account(
        &self,
        merchant_id: &str,
        req: AddBankAccountRequest,
    ) -> AppResult<MerchantBankAccount> {
        // Validate IFSC format (11 chars: 4 alpha + 0 + 6 alphanumeric)
        if req.ifsc_code.len() != 11 {
            return Err(AppError::Validation(
                "IFSC code must be exactly 11 characters".into(),
            ));
        }
        let id = Uuid::new_v4().to_string();

        let account = sqlx::query_as::<_, MerchantBankAccount>(
            r#"INSERT INTO merchant_bank_accounts
               (id, merchant_id, account_holder, account_number, ifsc_code, bank_name)
               VALUES ($1, $2, $3, $4, $5, $6)
               ON CONFLICT (merchant_id, account_number)
               DO UPDATE SET bank_name = EXCLUDED.bank_name
               RETURNING *"#,
        )
        .bind(&id)
        .bind(merchant_id)
        .bind(&req.account_holder)
        .bind(&req.account_number)
        .bind(req.ifsc_code.to_uppercase())
        .bind(&req.bank_name)
        .fetch_one(&self.pool)
        .await
        .map_err(AppError::Database)?;

        tracing::info!(merchant_id, "Bank account added: {}", &req.account_holder);
        Ok(account)
    }

    pub async fn list_bank_accounts(
        &self,
        merchant_id: &str,
    ) -> AppResult<Vec<MerchantBankAccount>> {
        sqlx::query_as::<_, MerchantBankAccount>(
            "SELECT * FROM merchant_bank_accounts WHERE merchant_id = $1 ORDER BY is_primary DESC, created_at DESC",
        )
        .bind(merchant_id)
        .fetch_all(&self.pool)
        .await
        .map_err(AppError::Database)
    }

    // ─── Payouts ──────────────────────────────────────────────────────────────

    pub async fn initiate_payout(
        &self,
        merchant_id: &str,
        req: InitiatePayoutRequest,
    ) -> AppResult<Payout> {
        if req.amount_inr < 100.0 {
            return Err(AppError::Validation("Minimum payout amount is ₹100".into()));
        }

        // Verify bank account ownership
        let account_exists = sqlx::query_scalar::<_, bool>(
            "SELECT EXISTS(SELECT 1 FROM merchant_bank_accounts WHERE id = $1 AND merchant_id = $2)",
        )
        .bind(&req.bank_account_id)
        .bind(merchant_id)
        .fetch_one(&self.pool)
        .await
        .map_err(AppError::Database)?;

        if !account_exists {
            return Err(AppError::NotFound("Bank account not found".into()));
        }

        let mode = req.mode.as_deref().unwrap_or("NEFT").to_uppercase();
        let strategy = PayoutStrategyFactory::get_strategy(&mode);
        let fee = strategy.calculate_fee();

        // ─── Sovereign Ledger Balance Gating ─────────────────────────────────────
        // Guard: The merchant can only withdraw settled funds that are not already
        // committed in a prior pending or in-flight payout.
        //
        // available_balance = Σ(net_payout_inr for COMPLETED settlements)
        //                   - Σ(amount_inr + fee_inr for PENDING/PROCESSING/SUCCESS payouts)

        let settled_sum = sqlx::query_scalar::<_, Option<f64>>(
            "SELECT COALESCE(SUM(net_payout_inr), 0.0) FROM settlements WHERE merchant_id = $1 AND status = 'COMPLETED'"
        )
        .bind(merchant_id)
        .fetch_one(&self.pool)
        .await
        .map_err(AppError::Database)?
        .unwrap_or(0.0);

        let active_payouts_sum = sqlx::query_scalar::<_, Option<f64>>(
            "SELECT COALESCE(SUM(amount_inr + fee_inr), 0.0) FROM payouts WHERE merchant_id = $1 AND status IN ('PENDING', 'PROCESSING', 'SUCCESS')"
        )
        .bind(merchant_id)
        .fetch_one(&self.pool)
        .await
        .map_err(AppError::Database)?
        .unwrap_or(0.0);

        let available_balance = settled_sum - active_payouts_sum;
        let total_debit = req.amount_inr + fee;

        if total_debit > available_balance {
            tracing::warn!(
                merchant_id,
                requested = req.amount_inr,
                fee,
                available = available_balance,
                settled = settled_sum,
                committed = active_payouts_sum,
                "Payout rejected: insufficient settled balance"
            );
            return Err(AppError::Validation(format!(
                "Sovereign Ledger Error: Insufficient settled funds. \
                 Requested ₹{:.2} + fee ₹{:.2} = ₹{:.2}, but available settled balance is ₹{:.2}. \
                 Please wait for pending settlements to clear.",
                req.amount_inr, fee, total_debit, available_balance
            )));
        }

        tracing::info!(
            merchant_id,
            available_balance,
            total_debit,
            "Ledger balance check passed — proceeding with payout"
        );
        // ─────────────────────────────────────────────────────────────────────────

        let id = Uuid::new_v4().to_string();

        let payout = sqlx::query_as::<_, Payout>(
            r#"INSERT INTO payouts
               (id, merchant_id, bank_account_id, amount_inr, fee_inr, mode, notes)
               VALUES ($1, $2, $3, $4, $5, $6, $7)
               RETURNING *"#,
        )
        .bind(&id)
        .bind(merchant_id)
        .bind(&req.bank_account_id)
        .bind(req.amount_inr)
        .bind(fee)
        .bind(&mode)
        .bind(&req.notes)
        .fetch_one(&self.pool)
        .await
        .map_err(AppError::Database)?;

        tracing::info!(
            merchant_id, payout_id = %id,
            "Payout initiated: ₹{:.2} via {}", req.amount_inr, mode
        );
        Ok(payout)
    }

    pub async fn list_payouts(&self, merchant_id: &str, limit: i64) -> AppResult<Vec<Payout>> {
        sqlx::query_as::<_, Payout>(
            "SELECT * FROM payouts WHERE merchant_id = $1 ORDER BY initiated_at DESC LIMIT $2",
        )
        .bind(merchant_id)
        .bind(limit)
        .fetch_all(&self.pool)
        .await
        .map_err(AppError::Database)
    }

    pub async fn get_payout_summary(&self, merchant_id: &str) -> AppResult<PayoutSummary> {
        use sqlx::Row;

        let row = sqlx::query(
            r#"SELECT
                COALESCE(SUM(net_inr) FILTER (WHERE status = 'SUCCESS'), 0.0) as total_paid,
                COUNT(*) FILTER (WHERE status = 'PENDING') as pending_count,
                COUNT(*) FILTER (WHERE status = 'SUCCESS') as success_count,
                COUNT(*) FILTER (WHERE status = 'FAILED') as failed_count,
                MAX(processed_at) FILTER (WHERE status = 'SUCCESS') as last_payout_at
               FROM payouts WHERE merchant_id = $1"#,
        )
        .bind(merchant_id)
        .fetch_one(&self.pool)
        .await
        .map_err(AppError::Database)?;

        Ok(PayoutSummary {
            total_paid_inr: row.get::<Option<f64>, _>("total_paid").unwrap_or(0.0),
            pending_count: row.get("pending_count"),
            success_count: row.get("success_count"),
            failed_count: row.get("failed_count"),
            last_payout_at: row.get("last_payout_at"),
        })
    }

    /// Mark a payout as SUCCESS with UTR reference (called when bank confirms payment).
    pub async fn confirm_payout(
        &self,
        merchant_id: &str,
        payout_id: &str,
        utr_number: &str,
    ) -> AppResult<Payout> {
        let payout = sqlx::query_as::<_, Payout>(
            r#"UPDATE payouts
               SET status = 'SUCCESS', utr_number = $3, processed_at = NOW()
               WHERE id = $1 AND merchant_id = $2 AND status = 'PENDING'
               RETURNING *"#,
        )
        .bind(payout_id)
        .bind(merchant_id)
        .bind(utr_number)
        .fetch_optional(&self.pool)
        .await
        .map_err(AppError::Database)?
        .ok_or_else(|| AppError::NotFound("Payout not found or already processed".into()))?;

        tracing::info!(merchant_id, payout_id, utr = utr_number, "Payout confirmed");
        Ok(payout)
    }
}

// ========================================================
// ADVANCED OOPS CONCEPTS: POLYMORPHIC TEMPLATE STRATEGIES
// ========================================================

/// Polymorphic template strategy for rendering notification subjects and bodies.
pub trait NotificationTemplate: Send + Sync {
    fn event_type(&self) -> &'static str;
    fn subject(&self) -> String;
    fn html_body(&self) -> String;
}

pub struct OrderPlacedTemplate {
    pub merchant_name: String,
    pub buyer_name: String,
    pub transaction_id: String,
    pub amount_inr: f64,
}

impl NotificationTemplate for OrderPlacedTemplate {
    fn event_type(&self) -> &'static str {
        "ORDER_PLACED"
    }
    fn subject(&self) -> String {
        format!(
            "Order Confirmed – ₹{:.2} | {}",
            self.amount_inr, self.merchant_name
        )
    }
    fn html_body(&self) -> String {
        let frontend_url =
            std::env::var("FRONTEND_URL").unwrap_or_else(|_| "http://localhost:5173".to_string());
        format!(
            r#"<div style="font-family:Inter,sans-serif;max-width:560px;margin:auto;padding:40px 20px">
            <h2 style="color:#111;margin-bottom:8px">Order Confirmed ✓</h2>
            <p style="color:#555">Hi {buyer_name}, your order of <strong>₹{amount_inr:.2}</strong> from <strong>{merchant_name}</strong> has been placed successfully.</p>
            <p style="color:#888;font-size:13px">Transaction ID: <code>{transaction_id}</code></p>
            <div style="margin:24px 0">
                <a href="{frontend_url}/track/{transaction_id}" style="display:inline-block;background-color:#00E59B;color:#000000;padding:12px 24px;border-radius:8px;text-decoration:none;font-weight:bold;font-size:14px">Track Your Order</a>
            </div>
            <hr style="border:none;border-top:1px solid #eee;margin:24px 0">
            <p style="color:#aaa;font-size:12px">Powered by Rtix Secure Payments</p></div>"#,
            buyer_name = self.buyer_name,
            amount_inr = self.amount_inr,
            merchant_name = self.merchant_name,
            transaction_id = self.transaction_id,
            frontend_url = frontend_url,
        )
    }
}

pub struct PayoutInitiatedTemplate {
    pub merchant_name: String,
    pub amount_inr: f64,
    pub mode: String,
    pub payout_id: String,
}

impl NotificationTemplate for PayoutInitiatedTemplate {
    fn event_type(&self) -> &'static str {
        "PAYOUT_INITIATED"
    }
    fn subject(&self) -> String {
        format!(
            "Payout Initiated – ₹{:.2} via {}",
            self.amount_inr, self.mode
        )
    }
    fn html_body(&self) -> String {
        format!(
            r#"<div style="font-family:Inter,sans-serif;max-width:560px;margin:auto;padding:40px 20px">
            <h2 style="color:#111">Payout Initiated 🏦</h2>
            <p style="color:#555">Hi {merchant_name}, your payout of <strong>₹{amount_inr:.2}</strong> via <strong>{mode}</strong> has been initiated.</p>
            <p style="color:#888;font-size:13px">Payout ID: <code>{payout_id}</code></p>
            <p style="color:#555;font-size:14px">Funds will be credited within 1–2 business hours (IMPS) or next business day (NEFT).</p>
            <hr style="border:none;border-top:1px solid #eee;margin:24px 0">
            <p style="color:#aaa;font-size:12px">Powered by Rtix Secure Payments</p></div>"#,
            merchant_name = self.merchant_name,
            amount_inr = self.amount_inr,
            mode = self.mode,
            payout_id = self.payout_id,
        )
    }
}

pub struct PayoutSuccessTemplate {
    pub merchant_name: String,
    pub amount_inr: f64,
    pub utr_number: String,
}

impl NotificationTemplate for PayoutSuccessTemplate {
    fn event_type(&self) -> &'static str {
        "PAYOUT_SUCCESS"
    }
    fn subject(&self) -> String {
        format!("₹{:.2} Credited to Your Account | Rtix", self.amount_inr)
    }
    fn html_body(&self) -> String {
        format!(
            r#"<div style="font-family:Inter,sans-serif;max-width:560px;margin:auto;padding:40px 20px">
            <h2 style="color:#16a34a">Payment Successful ✓</h2>
            <p style="color:#555">Hi {merchant_name}, <strong>₹{amount_inr:.2}</strong> has been credited to your bank account.</p>
            <p style="color:#888;font-size:13px">UTR: <code>{utr_number}</code></p>
            <hr style="border:none;border-top:1px solid #eee;margin:24px 0">
            <p style="color:#aaa;font-size:12px">Powered by Rtix Secure Payments</p></div>"#,
            merchant_name = self.merchant_name,
            amount_inr = self.amount_inr,
            utr_number = self.utr_number,
        )
    }
}

pub struct SubscriptionRenewalTemplate {
    pub subscriber_name: String,
    pub plan_name: String,
    pub amount_inr: f64,
    pub next_billing_date: String,
}

impl NotificationTemplate for SubscriptionRenewalTemplate {
    fn event_type(&self) -> &'static str {
        "SUBSCRIPTION_RENEWAL"
    }
    fn subject(&self) -> String {
        format!(
            "Your {} subscription renews on {}",
            self.plan_name, self.next_billing_date
        )
    }
    fn html_body(&self) -> String {
        format!(
            r#"<div style="font-family:Inter,sans-serif;max-width:560px;margin:auto;padding:40px 20px">
            <h2 style="color:#111">Subscription Renewal Reminder</h2>
            <p style="color:#555">Hi {subscriber_name}, your <strong>{plan_name}</strong> subscription of ₹{amount_inr:.2} will renew on <strong>{next_billing_date}</strong>.</p>
            <hr style="border:none;border-top:1px solid #eee;margin:24px 0">
            <p style="color:#aaa;font-size:12px">Powered by Rtix Secure Payments</p></div>"#,
            subscriber_name = self.subscriber_name,
            plan_name = self.plan_name,
            amount_inr = self.amount_inr,
            next_billing_date = self.next_billing_date,
        )
    }
}

pub struct SubscriptionCancelledTemplate {
    pub subscriber_name: String,
    pub plan_name: String,
}

impl NotificationTemplate for SubscriptionCancelledTemplate {
    fn event_type(&self) -> &'static str {
        "SUBSCRIPTION_CANCELLED"
    }
    fn subject(&self) -> String {
        format!("Your {} subscription has been cancelled", self.plan_name)
    }
    fn html_body(&self) -> String {
        format!(
            r#"<div style="font-family:Inter,sans-serif;max-width:560px;margin:auto;padding:40px 20px">
            <h2 style="color:#dc2626">Subscription Cancelled</h2>
            <p style="color:#555">Hi {subscriber_name}, your <strong>{plan_name}</strong> subscription has been cancelled. You will retain access until the end of the current billing period.</p>
            <hr style="border:none;border-top:1px solid #eee;margin:24px 0">
            <p style="color:#aaa;font-size:12px">Powered by Rtix Secure Payments</p></div>"#,
            subscriber_name = self.subscriber_name,
            plan_name = self.plan_name,
        )
    }
}

// ─── Legacy Wrapper Enum for Backwards-Compatibility ──────────────────────

/// Transactional email templates (Legacy Enum wrapper, maintained for compatibility)
pub enum NotificationEvent<'a> {
    OrderPlaced {
        merchant_name: &'a str,
        buyer_name: &'a str,
        transaction_id: &'a str,
        amount_inr: f64,
    },
    PayoutInitiated {
        merchant_name: &'a str,
        amount_inr: f64,
        mode: &'a str,
        payout_id: &'a str,
    },
    PayoutSuccess {
        merchant_name: &'a str,
        amount_inr: f64,
        utr_number: &'a str,
    },
    SubscriptionRenewal {
        subscriber_name: &'a str,
        plan_name: &'a str,
        amount_inr: f64,
        next_billing_date: &'a str,
    },
    SubscriptionCancelled {
        subscriber_name: &'a str,
        plan_name: &'a str,
    },
}

impl<'a> NotificationEvent<'a> {
    /// OOPS Adapter: converts the legacy procedural enum into a dynamic template object.
    pub fn into_template(self) -> Box<dyn NotificationTemplate> {
        match self {
            NotificationEvent::OrderPlaced {
                merchant_name,
                buyer_name,
                transaction_id,
                amount_inr,
            } => Box::new(OrderPlacedTemplate {
                merchant_name: merchant_name.to_string(),
                buyer_name: buyer_name.to_string(),
                transaction_id: transaction_id.to_string(),
                amount_inr,
            }),
            NotificationEvent::PayoutInitiated {
                merchant_name,
                amount_inr,
                mode,
                payout_id,
            } => Box::new(PayoutInitiatedTemplate {
                merchant_name: merchant_name.to_string(),
                amount_inr,
                mode: mode.to_string(),
                payout_id: payout_id.to_string(),
            }),
            NotificationEvent::PayoutSuccess {
                merchant_name,
                amount_inr,
                utr_number,
            } => Box::new(PayoutSuccessTemplate {
                merchant_name: merchant_name.to_string(),
                amount_inr,
                utr_number: utr_number.to_string(),
            }),
            NotificationEvent::SubscriptionRenewal {
                subscriber_name,
                plan_name,
                amount_inr,
                next_billing_date,
            } => Box::new(SubscriptionRenewalTemplate {
                subscriber_name: subscriber_name.to_string(),
                plan_name: plan_name.to_string(),
                amount_inr,
                next_billing_date: next_billing_date.to_string(),
            }),
            NotificationEvent::SubscriptionCancelled {
                subscriber_name,
                plan_name,
            } => Box::new(SubscriptionCancelledTemplate {
                subscriber_name: subscriber_name.to_string(),
                plan_name: plan_name.to_string(),
            }),
        }
    }
}

// ─── Notification Service ─────────────────────────────────────────────────────

pub struct NotificationService {
    pool: DbPool,
    resend_api_key: Option<String>,
    sendgrid_api_key: Option<String>,
    sendgrid_endpoint: String,
    from_email: String,
}

impl NotificationService {
    pub fn new(pool: DbPool) -> Self {
        let resend_api_key = std::env::var("RESEND_API_KEY").ok();
        let sendgrid_api_key = std::env::var("SENDGRID_API_KEY")
            .or_else(|_| std::env::var("EMAIL_API_KEY"))
            .ok();
        let sendgrid_endpoint = std::env::var("EMAIL_ENDPOINT")
            .unwrap_or_else(|_| "https://api.sendgrid.com/v3/mail/send".to_string());
        let from_email = std::env::var("NOTIFICATION_FROM_EMAIL")
            .unwrap_or_else(|_| "notifications@rtix.in".to_string());

        if resend_api_key.is_none() && sendgrid_api_key.is_none() {
            tracing::warn!("Neither RESEND_API_KEY nor SENDGRID_API_KEY/EMAIL_API_KEY set — email notifications will be logged only.");
        }

        Self {
            pool,
            resend_api_key,
            sendgrid_api_key,
            sendgrid_endpoint,
            from_email,
        }
    }

    /// Dynamically send a notification rendered from any OOPS `NotificationTemplate`.
    pub async fn send(
        &self,
        recipient_email: &str,
        merchant_id: Option<&str>,
        template: &dyn NotificationTemplate,
    ) -> AppResult<()> {
        let subject = template.subject();
        let html_body = template.html_body();

        // Attempt to send via SendGrid or Resend API
        let provider_id = if let Some(api_key) = &self.sendgrid_api_key {
            self.send_via_sendgrid(
                api_key,
                &self.sendgrid_endpoint,
                recipient_email,
                &subject,
                &html_body,
            )
            .await
            .ok()
        } else if let Some(api_key) = &self.resend_api_key {
            self.send_via_resend(api_key, recipient_email, &subject, &html_body)
                .await
                .ok()
        } else {
            tracing::info!(
                recipient = recipient_email,
                subject = %subject,
                "[DRY RUN] Email notification — set SENDGRID_API_KEY/EMAIL_API_KEY or RESEND_API_KEY to enable delivery"
            );
            None
        };

        let event_type = template.event_type();

        let log_id = Uuid::new_v4().to_string();
        let is_configured = self.resend_api_key.is_some() || self.sendgrid_api_key.is_some();
        let status = if provider_id.is_some() || !is_configured {
            "SENT"
        } else {
            "FAILED"
        };

        sqlx::query(
            r#"INSERT INTO notification_log
               (id, merchant_id, recipient_email, event_type, subject, status, provider_id)
               VALUES ($1, $2, $3, $4, $5, $6, $7)"#,
        )
        .bind(&log_id)
        .bind(merchant_id)
        .bind(recipient_email)
        .bind(event_type)
        .bind(&subject)
        .bind(status)
        .bind(&provider_id)
        .execute(&self.pool)
        .await
        .map_err(AppError::Database)?;

        Ok(())
    }

    /// Legacy support method: converts older enum types and forwards it polmorphicly.
    pub async fn send_legacy(
        &self,
        recipient_email: &str,
        merchant_id: Option<&str>,
        event: NotificationEvent<'_>,
    ) -> AppResult<()> {
        let template = event.into_template();
        self.send(recipient_email, merchant_id, template.as_ref())
            .await
    }

    async fn send_via_resend(
        &self,
        api_key: &str,
        to: &str,
        subject: &str,
        html: &str,
    ) -> Result<String, String> {
        let client = reqwest::Client::new();
        let payload = serde_json::json!({
            "from": self.from_email,
            "to": [to],
            "subject": subject,
            "html": html
        });

        let resp = client
            .post("https://api.resend.com/emails")
            .bearer_auth(api_key)
            .json(&payload)
            .send()
            .await
            .map_err(|e| e.to_string())?;

        if resp.status().is_success() {
            let body: serde_json::Value = resp.json().await.map_err(|e| e.to_string())?;
            Ok(body["id"].as_str().unwrap_or("unknown").to_string())
        } else {
            let status = resp.status();
            let err_body = resp.text().await.unwrap_or_default();
            tracing::error!("Resend API error {}: {}", status, err_body);
            Err(format!("Resend API error: {}", status))
        }
    }

    async fn send_via_sendgrid(
        &self,
        api_key: &str,
        endpoint: &str,
        to: &str,
        subject: &str,
        html: &str,
    ) -> Result<String, String> {
        let client = reqwest::Client::new();
        let payload = serde_json::json!({
            "personalizations": [
                {
                    "to": [
                        {
                            "email": to
                        }
                    ]
                }
            ],
            "from": {
                "email": self.from_email
            },
            "subject": subject,
            "content": [
                {
                    "type": "text/html",
                    "value": html
                }
            ]
        });

        let resp = client
            .post(endpoint)
            .bearer_auth(api_key)
            .json(&payload)
            .send()
            .await
            .map_err(|e| e.to_string())?;

        if resp.status().is_success() {
            let msg_id = resp
                .headers()
                .get("x-message-id")
                .and_then(|h| h.to_str().ok())
                .unwrap_or("sendgrid-accepted")
                .to_string();
            Ok(msg_id)
        } else {
            let status = resp.status();
            let err_body = resp.text().await.unwrap_or_default();
            tracing::error!("SendGrid API error {}: {}", status, err_body);
            Err(format!("SendGrid API error: {}", status))
        }
    }

    pub async fn get_notification_log(
        &self,
        merchant_id: &str,
        limit: i64,
    ) -> AppResult<Vec<NotificationLog>> {
        sqlx::query_as::<_, NotificationLog>(
            r#"SELECT * FROM notification_log
               WHERE merchant_id = $1
               ORDER BY sent_at DESC LIMIT $2"#,
        )
        .bind(merchant_id)
        .bind(limit)
        .fetch_all(&self.pool)
        .await
        .map_err(AppError::Database)
    }
}