from email_campaigns.adapters.db import Repository
from email_campaigns.emails.models import ShopifyEmailOrdersDbt
from email_campaigns.emails.types import (
    EmailSalesAnalytics,
    EmailSalesAnalyticsAdapter,
    EmailSalesLocationAnalytics,
    EmailSalesLocationAnalyticsList,
    EmailSalesProductAnalytics,
    EmailSalesProductAnalyticsList,
    EmailSalesShopAnalytics,
    EmailSalesShopAnalyticsList,
)


class EmailSalesAnalyticsRepository(Repository[ShopifyEmailOrdersDbt]):
    def find_by_email_ids(self, email_ids: list[str]) -> EmailSalesAnalytics:
        if not email_ids:
            return EmailSalesAnalyticsAdapter.validate_python(
                {
                    "orders_count": 0,
                    "items_sold": 0,
                    "net_sales_usd": 0.0,
                    "gross_sales_usd": 0.0,
                    "discounts_usd": 0.0,
                    "taxes_usd": 0.0,
                    "shipping_usd": 0.0,
                    "refunds_usd": 0.0,
                    "average_units_per_order": 0.0,
                    "average_order_amount": 0.0,
                    "first_time_buyer_rate": 0.0,
                }
            )
        query = self.db.query_from_template(
            "email-analytics/find-sales-by-email-ids.sql",
            context={"email_ids": email_ids},
        )
        row = self.db.session.execute(query).mappings().one()
        return EmailSalesAnalyticsAdapter.validate_python(dict(row))

    def find_product_sales_by_email_ids(
        self, email_ids: list[str]
    ) -> list[EmailSalesProductAnalytics]:
        if not email_ids:
            return []
        query = self.db.query_from_template(
            "email-analytics/find-product-sales-by-email-ids.sql",
            context={"email_ids": email_ids},
        )
        result = self.db.session.execute(query).mappings()
        return EmailSalesProductAnalyticsList.validate_python(result)

    def find_location_sales_by_email_ids(
        self, email_ids: list[str]
    ) -> list[EmailSalesLocationAnalytics]:
        if not email_ids:
            return []
        query = self.db.query_from_template(
            "email-analytics/find-location-sales-by-email-ids.sql",
            context={"email_ids": email_ids},
        )
        result = self.db.session.execute(query).mappings()
        return EmailSalesLocationAnalyticsList.validate_python(result)

    def find_shop_sales_by_email_ids(
        self, email_ids: list[str]
    ) -> list[EmailSalesShopAnalytics]:
        if not email_ids:
            return []
        query = self.db.query_from_template(
            "email-analytics/find-shop-sales-by-email-ids.sql",
            context={"email_ids": email_ids},
        )
        result = self.db.session.execute(query).mappings()
        return EmailSalesShopAnalyticsList.validate_python(result)