from django.db import models
from django.db.models import Sum, F
from django.db.models.functions import Coalesce
from django.contrib.auth.models import User
from django.utils.functional import cached_property

from .managers import ProductManager, PurchaseOrderManager, PurchaseItemManager, SalesOrderManager, SalesItemManager, AdjustmentManager


class MyUser(models.Model):
    user = models.OneToOneField(User, on_delete=models.CASCADE)
    date_format = models.CharField(max_length=32, default='%m/%d/%Y')
    date_format_ym = models.CharField(max_length=32, default='%m/%Y')
    date_format_ym_short = models.CharField(max_length=32, default='%m/%y')
    date_format_md = models.CharField(max_length=32, default='%m/%d')


class Currency(models.Model):
    name = models.CharField(max_length=128, unique=True)
    symbol = models.CharField(max_length=8, unique=True)

    class Meta:
        verbose_name_plural = 'currencies'

    def __str__(self):
        return self.name


class Product(models.Model):
    sku = models.CharField(max_length=128, unique=True)
    name = models.CharField(max_length=255)
    price = models.DecimalField(max_digits=10, decimal_places=2)

    objects = ProductManager()

    @cached_property
    def purchased(self):
        return PurchaseItem.objects.filter(product=self).aggregate(value=Coalesce(Sum('quantity'), 0))['value']

    def get_sold(self, year=None, month=None):
        objects = SalesItem.objects.filter(product=self).exclude(sales_order__status='RE')

        if year is not None and month is not None:
            objects = objects.filter(sales_order__date__year=year, sales_order__date__month=month)

        return objects.aggregate(value=Coalesce(Sum('quantity'), 0))['value']

    sold = cached_property(get_sold)

    @cached_property
    def pending(self):
        return SalesItem.objects.filter(product=self, sales_order__status='PR').aggregate(value=Coalesce(Sum('quantity'), 0))['value']

    @cached_property
    def adjustment(self):
        return Adjustment.objects.filter(product=self).aggregate(value=Coalesce(Sum('quantity'), 0))['value']

    def available(self):
        return self.purchased - self.sold + self.adjustment

    def available_str(self):
        return str(self.available() + self.pending) + ('' if self.pending == 0 else ' (-' + str(self.pending) + ')')

    def avg_price(self):
        revenue = SalesItem.objects.filter(product=self) \
            .aggregate(value=Sum(F('price')*F('quantity')*(100-F('sales_order__discount'))/100, output_field=models.DecimalField()))['value']

        if revenue:
            return '${0:,.2f}'.format(revenue / self.sold)
        else:
            return "N/A"

    def last_sold(self):
        try:
            last_so = SalesItem.objects.filter(product=self).exclude(sales_order__status='RE').values('sales_order__date').latest('sales_order__date')
            return last_so['sales_order__date']
        except SalesItem.DoesNotExist:
            return None

    def __str__(self):
        return self.sku


class PurchaseOrder(models.Model):
    date = models.DateField()
    order_id = models.CharField(max_length=128, unique=True)
    currency = models.ForeignKey(Currency)

    objects = PurchaseOrderManager()

    def __str__(self):
        return str(self.id)


class PurchaseItem(models.Model):
    purchase_order = models.ForeignKey(PurchaseOrder)
    product = models.ForeignKey(Product)
    price = models.DecimalField(max_digits=10, decimal_places=2)
    quantity = models.IntegerField()

    objects = PurchaseItemManager()

    def __str__(self):
        return str(self.id)


class Platform(models.Model):
    name = models.CharField(max_length=255, unique=True)

    def __str__(self):
        return self.name


class SalesOrder(models.Model):
    STATUS_CHOICES = (
        ('PR', 'Processing'),
        ('CO', 'Complete'),
        ('RE', 'Returned'),
    )

    date = models.DateField()
    platform = models.ForeignKey(Platform)
    order_id = models.CharField(max_length=128)
    customer = models.CharField(max_length=255)
    currency = models.ForeignKey(Currency)
    discount = models.DecimalField(max_digits=10, decimal_places=2)
    tax = models.DecimalField(max_digits=10, decimal_places=3)
    status = models.CharField(max_length=2, choices=STATUS_CHOICES, default='PR', blank=False)

    objects = SalesOrderManager()

    def __str__(self):
        return str(self.id)


class SalesItem(models.Model):
    sales_order = models.ForeignKey(SalesOrder)
    product = models.ForeignKey(Product)
    price = models.DecimalField(max_digits=10, decimal_places=2)
    quantity = models.IntegerField()

    objects = SalesItemManager()

    def __str__(self):
        return str(self.id)


class Adjustment(models.Model):
    date = models.DateField()
    product = models.ForeignKey(Product)
    quantity = models.IntegerField()
    comment = models.CharField(max_length=255, blank=True)

    objects = AdjustmentManager()

    def __str__(self):
        return str(self.id)
