import uuid

from django.db import models

from core.models import Project, TimeStampedModel

UNIT_CHOICES = [
    ("bag", "Bag"), ("ton", "Ton"), ("kg", "Kg"), ("cft", "CFT"),
    ("piece", "Piece"), ("truck", "Truck"), ("sqft", "Sq. Ft"), ("litre", "Litre"),
    ("unit", "Unit"),
]


class Material(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    project = models.ForeignKey(
        Project, on_delete=models.DO_NOTHING, related_name="materials",
        db_constraint=False,  # Project lives in the shared platform DB, not this company DB
    )
    name = models.CharField(max_length=150)
    unit = models.CharField(max_length=20, choices=UNIT_CHOICES, default="unit")
    reorder_level = models.DecimalField(
        max_digits=12, decimal_places=2, default=0,
        help_text="Optional low-stock alert threshold",
    )

    class Meta:
        db_table = "materials_material"
        unique_together = ["project", "name"]
        ordering = ["name"]

    def __str__(self):
        return f"{self.name} ({self.get_unit_display()})"

    @property
    def total_purchased(self):
        return self.purchases.aggregate(t=models.Sum("quantity"))["t"] or 0

    @property
    def total_used(self):
        return self.usages.aggregate(t=models.Sum("quantity"))["t"] or 0

    @property
    def remaining_stock(self):
        return self.total_purchased - self.total_used

    @property
    def total_purchase_value(self):
        return self.purchases.aggregate(
            t=models.Sum(models.F("quantity") * models.F("unit_price"))
        )["t"] or 0

    @property
    def is_low_stock(self):
        return self.reorder_level > 0 and self.remaining_stock <= self.reorder_level


class MaterialPurchase(TimeStampedModel):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    project = models.ForeignKey(
        Project, on_delete=models.DO_NOTHING, related_name="material_purchases",
        db_constraint=False,  # Project lives in the shared platform DB, not this company DB
    )
    material = models.ForeignKey(Material, on_delete=models.CASCADE, related_name="purchases")
    date = models.DateField()
    quantity = models.DecimalField(max_digits=12, decimal_places=2)
    unit_price = models.DecimalField(max_digits=12, decimal_places=2, default=0)
    vendor = models.CharField(max_length=255, blank=True)
    invoice_no = models.CharField(max_length=100, blank=True)
    invoice_attachment = models.FileField(upload_to="material_invoices/", null=True, blank=True)
    remarks = models.TextField(blank=True)

    class Meta:
        db_table = "materials_purchase"
        ordering = ["-date", "-created_at"]

    def __str__(self):
        return f"Purchase {self.material.name} x{self.quantity} on {self.date}"

    @property
    def total_cost(self):
        return self.quantity * self.unit_price


class MaterialUsage(TimeStampedModel):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False)
    project = models.ForeignKey(
        Project, on_delete=models.DO_NOTHING, related_name="material_usages",
        db_constraint=False,  # Project lives in the shared platform DB, not this company DB
    )
    material = models.ForeignKey(Material, on_delete=models.CASCADE, related_name="usages")
    date = models.DateField()
    quantity = models.DecimalField(max_digits=12, decimal_places=2)
    used_for = models.CharField(max_length=255, blank=True, help_text="e.g. 3rd floor slab casting")
    remarks = models.TextField(blank=True)

    class Meta:
        db_table = "materials_usage"
        ordering = ["-date", "-created_at"]

    def __str__(self):
        return f"Used {self.material.name} x{self.quantity} on {self.date}"