from django.db import models
from django.conf import settings
from django.utils import timezone
from organisations.models import Organisation, OrganisationBranch
from ledgers.models import SystemTransactions, OrganisationSubAccount, DebtorAccounts
from customers.models import Customer


class ProductCategory(models.Model):
    category_name = models.CharField(max_length=255)
    description = models.TextField(default='', null=True, blank=True)
    deleted = models.BooleanField(default=False)
    parent = models.ForeignKey('ProductCategory', null=True, blank=True, on_delete=models.CASCADE, related_name='category_parent')
    organisation = models.ForeignKey(Organisation, on_delete=models.CASCADE, related_name='category_organisation')
    date_added = models.DateTimeField(default=timezone.now)
    added_by = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE, related_name='cat_addedby')

    def __str__(self):
        return self.category_name

    class Meta:
        db_table = 'public"."product_category'


class Product(models.Model):
    product_name = models.CharField(max_length=255)
    description = models.TextField(default='', null=True, blank=True)

    # New fields that match your frontend
    purchase_unit_type = models.CharField(max_length=100, default='', null=True, blank=True)
    purchase_price = models.FloatField(default=0)

    sale_unit_type = models.CharField(max_length=100, default='', null=True, blank=True)
    sale_price = models.FloatField(default=0)

    # Keep your existing references
    category = models.ForeignKey(ProductCategory, on_delete=models.CASCADE, related_name='product_category', null=True, blank=True)
    organisation = models.ForeignKey(Organisation, on_delete=models.CASCADE, related_name='product_organisation')
    added_by = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE, related_name='product_addedby')

    # Keep useful meta info
    deleted = models.BooleanField(default=False)
    date_added = models.DateTimeField(default=timezone.now)
    income_chart = models.ForeignKey(OrganisationSubAccount, on_delete=models.CASCADE, related_name='income_chart', null=True, blank=True)
    stock_chart = models.ForeignKey(OrganisationSubAccount, on_delete=models.SET_NULL, related_name='stock_chart', null=True, blank=True)

    def __str__(self):
        return self.product_name

    class Meta:
        db_table = 'public"."product'


class Stock(models.Model):
    batch_number = models.CharField(max_length=255, default='', null=True, blank=True)
    quantity = models.FloatField(null=False, blank=False)
    purchase_price = models.FloatField(null=False, blank=False)
    sell_price = models.FloatField(null=False, blank=False)
    payment_method = models.TextField(default='', null=False, blank=False)
    transaction = models.ForeignKey(SystemTransactions, on_delete=models.CASCADE, related_name='stock_transaction')
    stock_branch = models.ForeignKey(OrganisationBranch, on_delete=models.CASCADE, related_name='stoock_btanch')
    product = models.ForeignKey(Product, on_delete=models.CASCADE, related_name='stock_product')
    added_by = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE, related_name='stock_addedby')

    class Meta:
        db_table = 'public"."stock'


class Order(models.Model):
    STATUSES = (
        ('Pending', 'Pending'),
        ('Failed', 'Failed'),
        ('Processed', 'Processed')
    )
    TRANSACTIONTYPE = (
        ('Payable', 'Payable'),
        ('Sale', 'Sale'),
        ('Lease', 'Lease'),
        ('Receivable', 'Receivable')
    )
    TRANSACTIONSTATUS = (
        ('normal', 'Normal'),
        ('reversed', 'Reversed')
    )
    order_number = models.CharField(max_length=255, default='', null=True, blank=True)
    status = models.CharField(max_length=50, default='Pending', choices=STATUSES)
    discount_coupon = models.CharField(max_length=255, default='', null=True, blank=True)
    customer = models.ForeignKey(Customer, on_delete=models.CASCADE, related_name='order_customer', null=True)
    debtor_account = models.ForeignKey('ledgers.DebtorAccounts', on_delete=models.SET_NULL, related_name='order_debtor', null=True, blank=True)
    payment_method = models.TextField(default='', null=False, blank=False)
    maturity_date = models.DateTimeField(null=True, blank=True)
    date_added = models.DateTimeField(default=timezone.now)
    record_date = models.DateTimeField(default=timezone.now)
    transaction_status = models.TextField(default='normal', choices=TRANSACTIONSTATUS)
    transaction_type = models.CharField(max_length=255, default='Sale', choices=TRANSACTIONTYPE)
    transaction = models.ForeignKey(SystemTransactions, on_delete=models.CASCADE, related_name='order_transaction')
    order_branch = models.ForeignKey(OrganisationBranch, on_delete=models.CASCADE, related_name='order_btanch')
    added_by = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE, related_name='order_addedby')

    class Meta:
        db_table = 'public"."order'


class OrderItems(models.Model):
    quantity = models.FloatField(null=False, blank=False)
    total_discount = models.FloatField(null=False, blank=False, default=0)
    total_cost = models.FloatField(null=False, blank=False)
    price = models.FloatField(null=False, blank=False, default=0)
    stock = models.ForeignKey(Stock, on_delete=models.CASCADE, related_name='item_stock', null=True, blank=True)
    order = models.ForeignKey(Order, on_delete=models.CASCADE, related_name='item_order')
    product = models.ForeignKey(Product, on_delete=models.CASCADE, related_name='order_stock_product')
    added_by = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE, related_name='order_item_addedby')

    class Meta:
        db_table = 'public"."order_items'


class OrganisationSuppliers(models.Model):
    customer = models.ForeignKey(
        Customer,
        on_delete=models.SET_NULL,
        related_name='supplier_customer',
        null=True,
        blank=True
    )
    name = models.CharField(max_length=255, blank=True, null=True)  # ✅ new field
    description = models.TextField(default='', null=True, blank=True)
    deleted = models.BooleanField(default=False)
    organisation = models.ForeignKey(
        Organisation,
        on_delete=models.CASCADE,
        related_name='supplier_organisation'
    )
    date_added = models.DateTimeField(default=timezone.now)
    last_updated_date = models.DateTimeField(default=timezone.now)
    added_by = models.ForeignKey(
        settings.AUTH_USER_MODEL,
        on_delete=models.CASCADE,
        related_name='supplier_addedby'
    )
    chart_account = models.ForeignKey(
        OrganisationSubAccount,
        on_delete=models.CASCADE,
        related_name='supplier_chart_account',
        null=True,
        blank=True
    )
    bank_name = models.CharField(max_length=100, default='', null=True, blank=True)
    bank_acc_no = models.CharField(max_length=100, default='', null=True, blank=True)
    gender = models.CharField(max_length=10, null=True, blank=True)
    telephone = models.CharField(max_length=50, null=True, blank=True)
    address = models.CharField(max_length=255, null=True, blank=True)

    def __str__(self):
        return self.customer.name if self.customer else self.name or "External Supplier"

    class Meta:
        db_table = 'public"."organisation_suppliers'


PAYABLE_METHODS = (
    ('cash', 'Cash'),
    ('cheque/bank', 'Cheque/Bank'),
    ('credit', 'On Credit'),
)

PAYMENT_METHODS = (
    ('cash', 'Cash'),
    ('bank', 'Cheque/Bank'),
    ('mobile_money', 'Mobile Money'),
)


class SupplierPayable(models.Model):
    supplier = models.ForeignKey(
        OrganisationSuppliers,
        on_delete=models.CASCADE,
        related_name="payables"
    )
    description = models.TextField(blank=True, null=True)
    amount = models.DecimalField(max_digits=15, decimal_places=2)
    received_method = models.CharField(
        max_length=20,
        choices=PAYABLE_METHODS,
        default='cash'  # sets a default for old records
    )
    transaction_date = models.DateField(default=timezone.localdate)
    maturity_date = models.DateField(null=True, blank=True)
    comment = models.TextField(blank=True, null=True)
    status = models.CharField(
        max_length=20,
        choices=[
            ('pending', 'Pending'),
            ('partially_paid', 'Partially Paid'),
            ('cleared', 'Cleared')
        ],
        default='pending'
    )
    added_by = models.ForeignKey(
        settings.AUTH_USER_MODEL,
        on_delete=models.CASCADE,
        related_name="inventory_payable_added_by"
    )
    organisation = models.ForeignKey(
        Organisation,
        on_delete=models.CASCADE,
        related_name="inventory_payable_organisation"
    )
    date_added = models.DateTimeField(default=timezone.now)

    class Meta:
        db_table = 'inventory_supplierpayable'
        verbose_name = 'Supplier Payable'
        verbose_name_plural = 'Supplier Payables'

    def __str__(self):
        return f"{self.supplier.customer.name} - {self.amount}"


class SupplierPayment(models.Model):
    payable = models.ForeignKey(
        SupplierPayable,
        on_delete=models.CASCADE,
        related_name="payments",
        null=True, blank=True
    )
    description = models.TextField(blank=True, null=True)
    amount = models.DecimalField(max_digits=15, decimal_places=2)
    payment_method = models.CharField(max_length=20, choices=PAYMENT_METHODS, default='cash')
    transaction_date = models.DateField(default=timezone.localdate)
    comment = models.TextField(blank=True, null=True)
    added_by = models.ForeignKey(
        settings.AUTH_USER_MODEL,
        on_delete=models.CASCADE,
        related_name="inventory_payment_added_by"
    )
    organisation = models.ForeignKey(
        Organisation,
        on_delete=models.CASCADE,
        related_name="inventory_payment_organisation"
    )
    date_added = models.DateTimeField(default=timezone.now)

    class Meta:
        db_table = 'inventory_supplierpayment'
        verbose_name = 'Supplier Payment'
        verbose_name_plural = 'Supplier Payments'

    def __str__(self):
        return f"Payment of {self.amount} for {self.payable}"


class OrganisationProductSupplier(models.Model):
    supplier = models.ForeignKey(OrganisationSuppliers, on_delete=models.CASCADE, related_name='organ_supplier')
    organisation = models.ForeignKey(Organisation, on_delete=models.CASCADE, related_name='supplier_organ')
    unit_cost = models.FloatField(null=False, blank=False, default=0)
    product = models.ForeignKey(Product, on_delete=models.CASCADE, related_name='supplier_organ_product')
    date_added = models.DateTimeField(default=timezone.now)
    last_updated_date = models.DateTimeField(default=timezone.now)
    added_by = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE, related_name='supplier_org_prd_addedby')

    class Meta:
        db_table = 'public"."organisation_product_supplier'


class OrganisationSeason(models.Model):
    organisation = models.ForeignKey(Organisation, on_delete=models.CASCADE, related_name='organ_season')
    year = models.TextField(null=False, blank=False)
    label = models.TextField(null=False, blank=False)
    start_date = models.DateTimeField(default=timezone.now)
    end_date = models.DateTimeField(default=timezone.now)
    deleted = models.BooleanField(default=False)
    added_by = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE, related_name='org_season_addedby')

    class Meta:
        db_table = 'public"."organisation_seasons'


class NonCustomerOrders(models.Model):
    GENDER = (
        ('M', 'Male'),
        ('F', 'Female'),
        ('O', 'Other')
    )
    name = models.CharField(max_length=255)
    phone_number = models.CharField(max_length=55, null=True, blank=True)
    gender = models.CharField(max_length=55, null=True, blank=True, choices=GENDER)
    nationality = models.CharField(max_length=255, default="Ugandan")
    location = models.CharField(max_length=255, null=True, blank=True)
    order = models.ForeignKey(Order, on_delete=models.CASCADE, related_name='non_customer_order')

    class Meta:
        db_table = 'public"."non_customer_orders'


class ProductIcomes(models.Model):
    transaction = models.ForeignKey(SystemTransactions, on_delete=models.CASCADE, related_name='product_transaction')
    product = models.ForeignKey(Product, on_delete=models.CASCADE, related_name='product_income')
    transaction_type = models.CharField(max_length=255, default="normal")

    class Meta:
        db_table = 'public"."product_sale_incomes'


class CustomerRequest(models.Model):
    STATUS_CHOICES = [
        ('pending', 'Pending'),
        ('aggregated', 'Aggregated'),
        ('fulfilled', 'Fulfilled'),
        ('cancelled', 'Cancelled'),
    ]
    
    request_number = models.CharField(max_length=50, unique=True)
    customer = models.ForeignKey(Customer, on_delete=models.CASCADE, related_name='customer_requests')
    product = models.ForeignKey(Product, on_delete=models.CASCADE, related_name='product_requests')
    quantity = models.DecimalField(max_digits=10, decimal_places=2)
    request_date = models.DateField()
    status = models.CharField(max_length=20, choices=STATUS_CHOICES, default='pending')
    aggregated_request = models.ForeignKey('AggregatedRequest', on_delete=models.SET_NULL, null=True, blank=True, related_name='customer_requests')
    notes = models.TextField(blank=True)
    created_at = models.DateTimeField(auto_now_add=True)
    
    class Meta:
        db_table = 'customer_requests'
        ordering = ['-created_at']


class AggregatedRequest(models.Model):
    STATUS_CHOICES = [
        ('pending', 'Pending'),        # saved, not yet sent
        ('sent', 'Sent'),              # sent to service provider
        ('approved', 'Approved'),      # service provider approved
        ('delivered', 'Delivered'),    # service provider fulfilled/delivered
        ('cancelled', 'Cancelled'),
    ]

    request_number = models.CharField(max_length=50, unique=True)
    organisation = models.ForeignKey(Organisation, on_delete=models.CASCADE, related_name='aggregated_requests')
    service_provider = models.ForeignKey(Organisation, on_delete=models.CASCADE, related_name='service_provider_requests', help_text='Service provider organization supplying the products')
    created_by = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.SET_NULL, null=True, related_name='created_aggregated_requests')
    title = models.CharField(max_length=255)
    description = models.TextField(blank=True)
    status = models.CharField(max_length=20, choices=STATUS_CHOICES, default='pending')
    request_date = models.DateField()
    expected_fulfillment_date = models.DateField(null=True, blank=True)
    total_estimated_cost = models.DecimalField(max_digits=15, decimal_places=2, default=0)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)
    
    class Meta:
        db_table = 'aggregated_requests'
        ordering = ['-created_at']


class AggregatedRequestItem(models.Model):
    PAYMENT_METHODS = [
        ('cash', 'Cash'),
        ('credit', 'Credit'),
        ('bank', 'Bank'),
        ('mobile_money', 'Mobile Money'),
        ('flexipay', 'Flexipay'),
    ]

    aggregated_request = models.ForeignKey(AggregatedRequest, on_delete=models.CASCADE, related_name='items')
    customer = models.ForeignKey(Customer, on_delete=models.CASCADE, related_name='aggregated_items')
    product = models.ForeignKey(Product, on_delete=models.CASCADE, related_name='aggregated_items')
    quantity_requested = models.DecimalField(max_digits=10, decimal_places=2)
    quantity_fulfilled = models.DecimalField(max_digits=10, decimal_places=2, default=0)
    unit_price = models.DecimalField(max_digits=15, decimal_places=2, null=True, blank=True)
    total_cost = models.DecimalField(max_digits=15, decimal_places=2, default=0)
    notes = models.TextField(blank=True)
    payment_method = models.CharField(max_length=20, choices=PAYMENT_METHODS, default='credit')
    cash_account = models.ForeignKey('ledgers.CashAccounts', on_delete=models.SET_NULL, null=True, blank=True, related_name='aggregated_item_cash_account')
    payment_transaction = models.ForeignKey('ledgers.SystemTransactions', on_delete=models.SET_NULL, null=True, blank=True, related_name='aggregated_item_payment')

    class Meta:
        db_table = 'aggregated_request_items'

    def save(self, *args, **kwargs):
        if self.unit_price and self.quantity_requested:
            self.total_cost = self.unit_price * self.quantity_requested
        super().save(*args, **kwargs)


class AggregatedStock(models.Model):
    aggregated_request = models.ForeignKey(AggregatedRequest, on_delete=models.CASCADE, related_name='stocks')
    stock = models.ForeignKey(Stock, on_delete=models.CASCADE, related_name='aggregated_stocks')
    quantity_allocated = models.DecimalField(max_digits=10, decimal_places=2)
    allocation_date = models.DateField()
    created_by = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.SET_NULL, null=True, related_name='allocated_stocks')
    
    class Meta:
        db_table = 'aggregated_stocks'