from django.db.models.sql.constants import ORDER_DIR
from django.db import models
from django.template.defaultfilters import default
from django.utils.timezone import now
from django.db import connections
from rest_framework.exceptions import ValidationError


class BaseModel(models.Model):
    """models to store details of base model for other models"""
    created_by = models.IntegerField( null=True, blank=True)
    created_on = models.DateTimeField(auto_now_add=True, null=True)
    updated_by = models.IntegerField(null=True, blank=True)
    updated_on = models.DateTimeField(auto_now=True, null=True)
    is_deleted = models.BooleanField(default=False)
    deleted_by = models.IntegerField(null=True, blank=True)
    deleted_on = models.DateTimeField(null=True, blank=True)

    class Meta:
        """Meta class for the above model."""

        abstract = True
        ordering = ('-created_on',)


class OrderRequests(models.Model):
    """Model to store details fo order requests"""
    Id = models.AutoField(primary_key=True, db_column="Id")  # Primary key
    ExactOrderNo = models.CharField(max_length=50, db_column="ExactOrderNo")
    ProductId = models.IntegerField(db_column="ProductId")
    Quantity = models.DecimalField(max_digits=10, decimal_places=4, null=True, blank=True)
    DeliveryDate = models.DateField(db_column="DeliveryDate")   
    FillingDate = models.DateField(db_column="FillingDate")   
    PurchaseOrderNo = models.CharField(max_length=50, db_column="PurchaseOrderNo", null=True, blank=True)
    OrderedBy = models.IntegerField(db_column="OrderedBy", null=True, blank=True)  # Address ID
    DeliverTo = models.IntegerField(db_column="DeliverTo", null=True, blank=True)  # Address ID
    BillTo = models.IntegerField(db_column="BillTo", null=True, blank=True)
    IncoTermsId = models.IntegerField(db_column="IncoTermsId", null=True, blank=True)
    LoadPointId = models.IntegerField(db_column="LoadPointId", null=True, blank=True)
    LoadReference = models.CharField(max_length=50, db_column="LoadReference", null=True, blank=True)
    DeliveryTime = models.TimeField(db_column="DeliveryTime", null=True, blank=True)
    DeliveryTimeInterval = models.CharField(max_length=20, db_column="DeliveryTimeInterval", null=True, blank=True)
    Product = models.CharField(max_length=250)
    OrderedCompany = models.CharField(max_length=100)
    OrderedAddress = models.TextField()
    DeliveryCustomer = models.CharField(max_length=100)
    DeliveryAddress = models.TextField()
    BillToCustomer = models.CharField(max_length=100)
    BillToAddress = models.TextField()
    LoadPoint = models.CharField(max_length=100)
    IncoTerm = models.CharField(max_length=50)
    IsOrder = models.BooleanField(default=False, db_column="IsOrder")
    LoadingDate = models.DateField()
    CreatedBy = models.IntegerField(db_column="CreatedBy")
    CreatedAt = models.DateTimeField(auto_now_add=True, db_column="CreatedAt")
    UpdatedBy = models.IntegerField(db_column="UpdatedBy")
    UpdatedAt = models.DateTimeField(auto_now=True, db_column="UpdatedAt")
    IsDeleted = models.BooleanField(default=False, db_column="IsDeleted")
    IsDelivered = models.BooleanField(default=False, db_column="IsDelivered")
    Information = models.TextField(null=True, blank=True)
    ContainerNo = models.CharField(max_length=200, default='', null=True, blank=True)
    no_of_packings = models.IntegerField(default=None)
    kg_per_packaging = models.IntegerField(default=None)
    ParentOrderId = models.IntegerField(default=None)
    # A packed order leaves either by road or in a container by sea. The mode
    # drives which fields, documents and emails apply.
    transport_mode = models.CharField(max_length=20, null=True, blank=True)
    transport_delivery_location = models.CharField(max_length=250, null=True, blank=True)

    class Meta:
        db_table = "OrderRequests"

    def __str__(self):
        return f"Order {self.Id}"
    

class PackingDetail(models.Model):
    """Models to store packing details of packaged goods"""
    order_request = models.ForeignKey(
        OrderRequests,  on_delete=models.SET_NULL, null=True, blank=True, related_name='packing_details')
    kg_per_packaging = models.IntegerField(null=True, blank=True)
    no_of_packings = models.IntegerField(default=None, null=True, blank=True)
    pack_tare_weight = models.IntegerField(null=True, blank=True)         # in Kg
    no_of_pallet = models.IntegerField(null=True, blank=True)
    pallet_weight = models.IntegerField(null=True, blank=True)
    dimension = models.CharField(max_length=300, default='', null=True, blank=True) 

    CreatedBy = models.IntegerField(null=True, blank=True)
    CreatedAt = models.DateTimeField(auto_now_add=True)
    UpdatedBy = models.IntegerField(null=True, blank=True)
    UpdatedAt = models.DateTimeField(null=True, blank=True)
    IsDeleted = models.BooleanField(default=False)

    class Meta:
        db_table = "PackingDetail"

    def __str__(self):
        return f"Order {self.order_request.Id}"
    
    def get_net_weight(self):
        """function to get total net weight"""
        if self.no_of_packings and self.kg_per_packaging:
            net_weight = float(self.no_of_packings) * float(self.kg_per_packaging)
            return net_weight
        else:
            return None


class OrderRequestFiles(models.Model):
    Id = models.AutoField(primary_key=True, db_column="Id")  # Primary key
    OrderId = models.IntegerField(db_column="OrderId")
    FileName = models.CharField(max_length=255, db_column="FileName")
    IsDeleted = models.BooleanField(default=False, db_column="IsDeleted")

    class Meta:
        db_table = "OrderRequestFiles"  # Matches the table name in SQL

    def __str__(self):
        return f"File: {self.FileName} (Order ID: {self.OrderId})"


class OrderHistory(models.Model):
    objects = None
    Id = models.AutoField(primary_key=True, db_column="Id")
    OrderRequestId = models.IntegerField(db_column="OrderRequestId")
    Updation = models.CharField(max_length=500, db_column="Updation")
    UpdatedBy = models.IntegerField(db_column="UpdatedBy")
    UpdatedOn = models.DateTimeField(auto_now_add=True, db_column="UpdatedOn")
    IsDeleted = models.BooleanField(default=False, db_column="IsDeleted")
    history_type = models.CharField(max_length=500, default=None, null=True, blank=True)

    class Meta:
        db_table = "OrderHistory"


class BookedSlots(models.Model):   
    Id = models.AutoField(primary_key=True, db_column="Id")  # Auto-increment primary key
    SlotId = models.IntegerField(db_column="SlotId")  # Reference to the Slot entity (if applicable)
    Date = models.DateField(db_column="Date")  # Date of the booking
    Time = models.TextField(db_column="Time")
    OrderId = models.IntegerField(db_column="OrderId")  # Reference to the associated order Request Id
    IsDeleted = models.BooleanField(default=False,
                                    db_column="IsDeleted")
    CreatedOn = models.DateTimeField(auto_now_add=True, db_column="CreatedOn")  # Timestamp of creation
    UpdatedOn = models.DateTimeField(auto_now=True, db_column="UpdatedOn", null=True,
                                     blank=True)

    class Meta:
        db_table = "BookedSlots"


class PriceMaster(models.Model):
    Id = models.AutoField(primary_key=True)  # Primary key
    ProductID = models.IntegerField(db_column="ProductID")
    IncotermID = models.IntegerField(db_column="IncotermID")
    QuarterName = models.CharField(max_length=500)
    ValidityStartDate = models.DateField()
    ValidityEndDate = models.DateField()
    Packaging = models.CharField(max_length=500)
    CustomerAddressId = models.IntegerField(db_column="CustomerAddressId")
    DestinationCountry = models.CharField(max_length=50)
    DestinationZIP = models.CharField(max_length=50)
    PaymentTermID = models.IntegerField()  # Foreign key to PaymentTerm (if applicable)
    MinQuantity = models.DecimalField(db_column="MinQuantity", max_digits=9, decimal_places=2, null=True)
    MinQtyOperator = models.CharField(max_length=50, default='=', null=True, blank=True)
    Price = models.DecimalField(db_column="Price", max_digits=9, decimal_places=2, null=True)
    ExactERPAddressId = models.IntegerField(null=True)
    OrderedAddressId = models.IntegerField()
    ArticleCode = models.CharField(max_length=100)
    PaymentTerm = models.CharField(max_length=500)
    OrderedByCustomer = models.CharField(max_length=150)
    ProductName = models.CharField(max_length=150)
    OfferRef = models.CharField(max_length=150)
    IncoTerm = models.CharField(max_length=50)
    # IncoTermID = models.IntegerField()
    DeliveryDestination = models.CharField(max_length=500)
    PriceConfirmation = models.CharField(max_length=500)
    IsConfirmed = models.IntegerField()   
    Remarks = models.TextField()
    IsFromSheet = models.IntegerField(default=1)
    no_of_packings = models.IntegerField(default=None)
    kg_per_packaging = models.IntegerField(default=None)
    packing_remarks = models.TextField(default=None)
    EmailForOffer = models.CharField(max_length=1000, null=True, blank=True)  
    BillTo = models.IntegerField(null=True, blank=True)
    ParentShipTo = models.IntegerField(null=True, blank=True)

    DatePriceOfferRequest = models.DateField(null=True, blank=True)
    OfferDate = models.DateField(null=True, blank=True)
    Responsivnes = models.CharField(max_length=50)
    LeadTimeDays = models.CharField(max_length=50)

    CreatedBy = models.IntegerField()
    CreatedOn = models.DateTimeField(auto_now_add=True)
    UpdatedBy = models.IntegerField(null=True, blank=True)
    UpdatedOn = models.DateTimeField(auto_now=True)
    IsDeleted = models.SmallIntegerField(db_column="IsDeleted", default=0, null=False)

    class Meta:
        db_table = "PriceMaster"
        verbose_name = "Price Master"
        verbose_name_plural = "Price Masters"

    def __str__(self):
        return f"- {self.Id}-{self.QuarterName} - {self.Price}"


class CostCenter(models.Model):
    Id = models.AutoField(primary_key=True)
    Code = models.CharField(max_length=20)
    Description = models.CharField(max_length=255)
    CreatedBy = models.IntegerField()
    CreatedOn = models.DateTimeField(auto_now_add=True)
    UpdatedBy = models.IntegerField(null=True, blank=True)
    UpdatedOn = models.DateTimeField(null=True, blank=True)
    IsDeleted = models.BooleanField(default=False)

    class Meta:
        db_table = "CostCenter"


class OrderStatus(models.Model):
    OrderStatusId = models.AutoField(primary_key=True)
    Status = models.CharField(max_length=50)
    Remarks = models.TextField(null=True, blank=True)
    IsDeleted = models.BooleanField(default=False)

    class Meta:
        db_table = 'OrderStatus'


class Orders(models.Model):
    Id = models.AutoField(primary_key=True)
    OrderNo = models.IntegerField()  # Order request ID
    LoadingDate = models.DateField()
    FillingDate = models.DateField()
    LoadingTime = models.CharField(max_length=50)
    LoadingSlotId = models.IntegerField()
    LoadingSlot = models.CharField(max_length=100)
    TransporterId = models.IntegerField()
    CostCenterId = models.IntegerField()
    CostCenter = models.CharField(max_length=50)
    Price = models.DecimalField(max_digits=10, decimal_places=3)
    Quarter = models.CharField(max_length=50)
    TotalPrice = models.DecimalField(max_digits=10, decimal_places=3)
    Remarks = models.TextField(null=True, blank=True)
    Quantity = models.DecimalField(max_digits=10, decimal_places=4, null=True, blank=True)
    LoadPoint = models.CharField(max_length=100)
    IncoTerm = models.CharField(max_length=50)
    LoadReference = models.CharField(max_length=50)
    StationReference = models.CharField(max_length=250, default='', null=True, blank=True)
    ExtraCostBorneBy  = models.CharField(max_length=200, default='', null=True, blank=True)
    StatusId = models.IntegerField()
    CreatedBy = models.IntegerField()
    CreatedAt = models.DateTimeField(auto_now_add=True)
    UpdatedBy = models.IntegerField(null=True, blank=True)
    UpdatedAt = models.DateTimeField(auto_now=True)
    IsDeleted = models.BooleanField(default=False)

    ExactOrderNo = models.CharField(max_length=50)
    # Product details
    ProductId = models.IntegerField()
    ProductName = models.CharField(max_length=500)
    Packaging = models.CharField(max_length=100, null=True, blank=True)

    # Delivery details
    DeliveryDate = models.DateField()
    DeliveryCustomerId = models.IntegerField()
    DeliveryCustomerName = models.CharField(max_length=500)
    DeliveryAddress = models.TextField()

    # Billing details
    BillToCustomerId = models.IntegerField()
    BillToCustomerName = models.CharField(max_length=500)
    BillToAddress = models.TextField()

    # Ordered customer details
    OrderedCustomerId = models.IntegerField()
    OrderedCustomerName = models.CharField(max_length=500)
    OrderedAddress = models.TextField()

    PaymentTerm = models.CharField(max_length=500)

    TransportPrice = models.DecimalField(max_digits=10, decimal_places=2)
    InvoiceCode = models.CharField(max_length=100)

    no_of_packings = models.IntegerField(default=None)
    kg_per_packaging = models.IntegerField(default=None)

    NeedSample = models.CharField(max_length=500, default='NOT_APPLICABLE')  # Chioices will be YES, NO, NOT_APPLICABLE
    SampleReason = models.CharField(max_length=1000, default='')
    # choices will be - taken_from_same_batch, analyzed_by_customer, option_1, option_2
    CourierDate = models.DateField()
    CourierTime = models.CharField(max_length=500, default='',null=True, blank=True)
    CourierPickupDate = models.DateField()
    CourierPickupTime = models.CharField(max_length=500, default='',null=True, blank=True)
    SampleOrderRef = models.CharField(max_length=500, default='', null=True, blank=True)

 
    class Meta:
        db_table = 'Orders'

    def get_order_request_obj(self):
        """function to get order request"""
        order_request = None
        try:
            order_request = OrderRequests.objects.get(Id=self.OrderNo, IsDeleted=False)
        except Exception as e:
            raise ValidationError({
                "status_code": 500,
                "status": "error",
                "message": f"Something went wrong {e}",
                "data": None
            })
        return order_request 


# class Holidays(models.Model):
#     Id = models.AutoField(primary_key=True)
#     Date = models.DateField()  # Date field for the holiday
#     Remarks = models.CharField(max_length=500)  # Remarks column with a maximum length of 500 characters
#     IsDeleted = models.BooleanField(default=False)  # IsDeleted as a boolean (0 or 1)

#     class Meta:
#         db_table = 'HoliDays'

class OffDays(models.Model):
    Id = models.AutoField(primary_key=True)
    Date = models.DateField()
    Remarks = models.CharField(max_length=500, blank=True, null=True)
    IsDeleted = models.BooleanField(default=False)

    class Meta:
        db_table = 'OffDays'  # This specifies the table name in the database

    def __str__(self):
        return f"OffDay {self.date} - {self.remarks}"


class ExtraCharges(models.Model):
    Id = models.AutoField(primary_key=True)
    Name = models.CharField(max_length=500)
    Amount = models.CharField(max_length=100)
    IsExxon = models.IntegerField(default=0)
    IsDeleted = models.BooleanField(default=False)
    ArticleCode = models.CharField(max_length=500)
    OrderIndex = models.IntegerField()

    class Meta:
        db_table = 'ExtraCharges'


class OrderCharges(models.Model):
    """Model to store extra charges related to order"""
    Id = models.AutoField(primary_key=True)
    OrderNo = models.IntegerField()  # OrderRequest_Id
    ChargeName = models.CharField(max_length=100)
    ExtraChargeId = models.IntegerField(null=False)
    Rate = models.DecimalField(max_digits=10, decimal_places=2)
    IsDeleted = models.BooleanField(default=False, db_column="IsDeleted")
    ArticleCode = models.CharField(max_length=500)

    class Meta:
        db_table = 'OrderCharges'

    @property
    def get_is_exxon(self):
        if self.ExtraChargeId:
            try:
                extra_charge = ExtraCharges.objects.get(Id=self.ExtraChargeId)
                return extra_charge.IsExxon
            except:
                return None
        else:
            return 0

    @property
    def get_charge_article_code(self):
        if self.ExtraChargeId:
            try:
                extra_charge = ExtraCharges.objects.get(Id=self.ExtraChargeId)
                return extra_charge.ArticleCode
            except:
                return None
        else:
            return self.ArticleCode if self.ArticleCode else None


class TermsAndConditions(models.Model):
    Id = models.AutoField(primary_key=True)
    ArticleCode = models.CharField(max_length=50)
    ShipToCode = models.CharField(max_length=50)
    IncoTerm = models.CharField(max_length=50, blank=True, null=True)
    DriverLanguage = models.CharField(max_length=50, blank=True, null=True)

    # Integer fields for the various conditions
    LoadReferenceNeeded = models.IntegerField()
    WeighingTicketNeeded = models.IntegerField()
    COCNeeded = models.IntegerField()
    COANeeded = models.IntegerField()
    SealingReportNeeded = models.IntegerField()

    # Text fields for LoadListConditions and CMRConditions
    LoadListConditions = models.TextField(blank=True, null=True)
    CMRConditions = models.TextField(blank=True, null=True)

    # Optionally, you can add meta information for table name and ordering
    class Meta:
        db_table = 'TermsAndConditions'
        ordering = ['Id']


class EmailLog(models.Model):
    id = models.AutoField(primary_key=True)
    sender = models.CharField(max_length=255, null=True, blank=True)
    recipient = models.TextField()
    cc = models.TextField(blank=True, null=True)
    # Carries the transporter availability and price request, where every
    # carrier is addressed privately so none of them sees who else was asked.
    bcc = models.TextField(blank=True, null=True)
    subject = models.TextField(default='')
    body = models.TextField()
    attachment = models.TextField()
    sent_at = models.DateTimeField(default=now)
    status = models.CharField(max_length=20, choices=[
        ("success", "Success"),
        ("failed", "Failed"),
        ("error", "Error"),
        ("NA","No orders found"),
    ])
    error_message = models.TextField(blank=True, null=True)
    triggered_by = models.TextField()
    order_id = models.CharField(max_length=100, null=True, blank=True)
    type = models.CharField(max_length=500, null=True, blank=True)
    receipient_id = models.IntegerField()
    email_response = models.CharField(max_length=300, null=True, blank=True)

    class Meta:
        db_table = 'EmailLog'


class LoadListConditions(models.Model):
    Id = models.AutoField(primary_key=True, db_column="Id")  # Primary key
    ShipToCode = models.CharField(max_length=100, db_column="ShipToCode")
    Instruction1 = models.TextField()
    Instruction2 = models.TextField()
    Instruction3 = models.TextField()
    CreatedBy = models.IntegerField()
    CreatedDate = models.DateTimeField(auto_now_add=True)
    IsDeleted = models.BooleanField(default=False, db_column="IsDeleted")

    class Meta:
        db_table = "LoadListConditions"

class IncoTerms(models.Model):
    Id = models.AutoField(primary_key=True)
    Code = models.CharField(max_length=10)
    Remarks = models.CharField(max_length=255, null=True, blank=True)
    TransportNeeded = models.BooleanField(null=True, blank=True)
    SortId = models.IntegerField()
    CreatedBy = models.IntegerField()
    CreatedAt = models.DateTimeField(auto_now_add=True)
    UpdatedBy = models.IntegerField(null=True, blank=True)
    UpdatedAt = models.DateTimeField(null=True, blank=True)
    IsDeleted = models.BooleanField(default=False)

    class Meta:
        db_table = 'IncoTerms'

    def __str__(self):
        return self.code

class UnloadInstructions(models.Model):
    Id = models.AutoField(primary_key=True)
    ArticleCode = models.CharField(max_length=50)
    ExactERPAddressId = models.IntegerField()
    BillTo = models.IntegerField()
    IncoTerm = models.CharField(max_length=50)
    LoadPointId = models.IntegerField()
    Color = models.CharField(max_length=100)
    Instructions = models.TextField(blank=True, null=True)
    IsImageReq = models.IntegerField(blank=True, null=True)
    IsDeleted = models.BooleanField(default=False)

    class Meta:
        db_table = 'UnloadInstructions'

class OrderFiles(models.Model):
    Id = models.AutoField(primary_key=True, db_column="Id")
    OrderId = models.IntegerField(db_column="OrderId")
    Type = models.CharField(max_length=100, db_column="Type")
    FileName = models.CharField(max_length=255, db_column="FileName")
    IsDeleted = models.BooleanField(default=False, db_column="IsDeleted")

    class Meta:
        db_table = "OrderFiles"
        managed = True

    def __str__(self):
        return f"{self.Type} File: {self.FileName} (Order ID: {self.OrderId})"


class CustomerContacts(models.Model):
    """Model to store email of customers to which email is sent"""
    Id = models.AutoField(primary_key=True)
    ExactERPAddressId = models.IntegerField()
    BillTo = models.IntegerField()
    Email = models.EmailField(max_length=254, null=True, blank=True)
    Type = models.CharField(max_length=50)
    product_article = models.CharField(max_length=100, null=True, blank=True)
    product_id = models.IntegerField(null=True, blank=True)
    CreatedAt = models.DateTimeField(auto_now_add=True, db_column="CreatedAt")
    IsDeleted = models.BooleanField(default=False)

    class Meta:
        db_table = 'CustomerContacts'


class TransporterContact(BaseModel):   
    """Model to store email of transporters to which email is sent"""
    Id = models.AutoField(primary_key=True)
    transporter_id = models.IntegerField()
    ship_to = models.IntegerField()
    product_category = models.EmailField(max_length=254, null=True, blank=True)
    recipient_type = models.CharField(max_length=50)
    email = models.EmailField(max_length=254, null=True, blank=True)

    class Meta:
        db_table = 'TransporterContact'

    def __str__(self):
        return f'{self.id}: {self.ship_to}'


class OrderPriceLink(models.Model):
    OrderId = models.IntegerField()
    PriceRowId = models.IntegerField()
    IsDeleted = models.BooleanField(default=False)

    class Meta:
        db_table = 'OrderPriceLink'


class CustomerRequirement(models.Model):
    """Model to store specific requirements related to customer """
    ShipTo = models.IntegerField()
    AddressId = models.IntegerField()
    IsCMR = models.BooleanField(default=False)
    CustomText = models.TextField(max_length=500)
    CreatedDate = models.DateTimeField(auto_now_add=True)
    IsSample = models.BooleanField(default=False)
    IsContainerRequired = models.BooleanField(default=False)
    IsOutSideEU = models.BooleanField(default=False)
    NoCustomerEmailRequired = models.BooleanField(default=False)
    NeedReturnMDGD = models.BooleanField(default=False)
    NeedVGM = models.BooleanField(default=False)
    SendLoadReferenceToTransporter = models.BooleanField(default=False)
    product_article = models.CharField(max_length=100, null=True, blank=True)
    product_id = models.IntegerField(null=True, blank=True)
    

    class Meta:
        db_table = "CustomerRequirements"
        verbose_name = "Customer Requirement"

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


class EmailAttachment(BaseModel):
    """Model to store default attachments that needs to be sent with emails depending on customer """
    customer_id = models.IntegerField(null=True)
    email_type = models.CharField(max_length=200, default='', null=True, blank=True)
    file_path = models.FileField(upload_to='email_attachments/', blank=True, null=True)
    file_name = models.CharField(max_length=200, default='', null=True, blank=True)
    product = models.CharField(max_length=200, default='', null=True, blank=True)
    
    class Meta:
        db_table = "EmailAttachment"

    def __str__(self):
        return f"ShipTo: {self.customer_id},  {self.email_type} {self.file_name}"


class LoadListRemark(BaseModel):
    """Model to store remarks for load list"""
    ship_to = models.IntegerField()
    remark_type = models.CharField(max_length=200, default='', null=True, blank=True)
    remarks = models.TextField(blank=True, null=True)
    
    class Meta:
        db_table = "LoadListRemark"

    def __str__(self):
        return f"LoadList: {self.ship_to}, Remarks: {self.remarks}"


class OrderContainerDetails(models.Model):
    """The sea leg of a packed order: the booking, and the transporter's three
    appointments.

    One row per order rather than one per appointment. The three legs look
    alike, but they are a fixed set of three with different meanings, and the
    screen reads them as named fields.

    Leg 2 - be at the warehouse to load - has no date or time here on purpose.
    That appointment IS the order's own loading slot, so storing it again would
    let the two drift apart. Only its location is kept.
    """
    order_request = models.ForeignKey(
        OrderRequests, on_delete=models.SET_NULL, null=True, blank=True,
        related_name='container_details')

    # the sea leg
    # The chosen rows in ShippingPortMaster / ShippingAgencyMaster. The names
    # below stay as the snapshot of what was picked, so renaming a master row
    # does not rewrite what an old order says.
    port_of_loading_id = models.IntegerField(null=True, blank=True)
    shipping_agency_id = models.IntegerField(null=True, blank=True)
    port_of_loading = models.CharField(max_length=100, null=True, blank=True)
    shipping_agency = models.CharField(max_length=100, null=True, blank=True)
    est_ship_departure = models.DateField(null=True, blank=True)

    # leg 1: collect the empty container at the port
    container_pickup_date = models.DateField(null=True, blank=True)
    container_pickup_time = models.CharField(max_length=50, null=True, blank=True)
    container_pickup_point = models.CharField(max_length=250, null=True, blank=True)

    # leg 2: be at the warehouse to load
    container_load_location = models.CharField(max_length=250, null=True, blank=True)

    # leg 3: deliver the loaded container back to the port
    container_dropoff_date = models.DateField(null=True, blank=True)
    container_dropoff_time = models.CharField(max_length=50, null=True, blank=True)
    container_dropoff_reference = models.CharField(max_length=250, null=True, blank=True)

    CreatedBy = models.IntegerField(null=True, blank=True)
    CreatedAt = models.DateTimeField(auto_now_add=True)
    UpdatedBy = models.IntegerField(null=True, blank=True)
    UpdatedAt = models.DateTimeField(null=True, blank=True)
    IsDeleted = models.BooleanField(default=False)

    class Meta:
        db_table = "OrderContainerDetails"

    def __str__(self):
        return f"Container schedule for order {self.order_request_id}"


class OrderTransporterRequests(BaseModel):
    """Which transporters have been sent an availability and price request.

    Replies are not tracked here. They go to the sales mailbox; the planner
    reads them, picks a carrier and types the price into the order by hand.
    This table exists so a second round of requests can show who was already
    asked.
    """
    order_request = models.ForeignKey(
        OrderRequests, on_delete=models.SET_NULL, null=True, blank=True,
        related_name='transporter_requests')
    transporter_id = models.IntegerField()
    # Snapshot of the addresses used, because TransporterContacts changes and
    # otherwise there is no way to tell later who was actually emailed.
    mail_ids_used = models.TextField(null=True, blank=True)
    request_sent_at = models.DateTimeField(null=True, blank=True)

    class Meta:
        db_table = "OrderTransporterRequests"
        # One row per carrier per order: a re-send updates request_sent_at.
        unique_together = ("order_request", "transporter_id")


class PayloadLog(BaseModel):
    """Model to store payloads and responses"""
    order_id = models.IntegerField(null=True, blank=True)
    request_payload = models.TextField(null=True, blank=True)
    response_payload = models.TextField(null=True, blank=True)
    status = models.CharField(max_length=50, null=True, blank=True) 
    payload_type = models.CharField(max_length=50, null=True, blank=True)

    class Meta:
        db_table = "PayloadLog"


class EmailDraft(BaseModel):
    """model to store email drafts and there will be multiple attachments for every emails"""
    order_request_id = models.IntegerField()
    email_type = models.CharField(max_length=200, default='', null=True, blank=True)
    subject = models.CharField(max_length=1000, default='', null=True, blank=True)
    body = models.TextField(null=True, blank=True)
    
    class Meta:
        db_table = "EmailDraft"

    def __str__(self):
        return f"EmailDraft: {self.email_type}, {self.subject}"

class EmailDraftAttachment(BaseModel):
    """model to store multiple attachments for every emails"""
    email_draft_id = models.IntegerField()
    attachment = models.FileField(upload_to='drafts/', blank=True, null=True)
    file_name = models.CharField(max_length=200, default='', null=True, blank=True)
    
    class Meta:
        db_table = "EmailDraftAttachment"

    def __str__(self):
        return f"EmailDraftAttachment: {self.email_draft_id}"


    from django.db import models


class LinkedPrice(BaseModel):

    order_price = models.ForeignKey(
        OrderPriceLink,
        on_delete=models.CASCADE,
        null=True,
        blank=True,
        related_name='linked_prices'
    )

    article_code = models.CharField(max_length=50, null=True, blank=True)
    product_name = models.CharField(max_length=255, null=True, blank=True)

    bill_to = models.IntegerField(null=True, blank=True)
    ordered_by_customer = models.CharField(max_length=255, null=True, blank=True)

    currency = models.CharField(max_length=10, null=True, blank=True)
    currency_id = models.IntegerField(null=True, blank=True)

    delivery_destination = models.CharField(max_length=255, null=True, blank=True)

    exact_erp_address_id = models.IntegerField(null=True, blank=True)

    incoterm = models.CharField(max_length=50, null=True, blank=True)

    # lead_time_days = models.IntegerField(null=True, blank=True)

    min_quantity = models.FloatField(null=True, blank=True)
    min_quantity_operator = models.CharField(max_length=50, null=True, blank=True)

    packaging = models.CharField(max_length=100, null=True, blank=True)

    kg_per_packaging = models.FloatField(null=True, blank=True)
    no_of_packings = models.FloatField(null=True, blank=True)

    packing_remarks = models.BooleanField(null=True, blank=True)

    payment_term = models.CharField(max_length=100, null=True, blank=True)
    payment_term_id = models.IntegerField(null=True, blank=True)

    price = models.FloatField(null=True, blank=True)
    price_confirmation = models.BooleanField(null=True, blank=True)

    offer_ref = models.CharField(max_length=100, null=True, blank=True)
    remarks = models.TextField(null=True, blank=True)

    quarter_name = models.CharField(max_length=255, null=True, blank=True)

    validity_start_date = models.DateField(null=True, blank=True)
    validity_end_date = models.DateField(null=True, blank=True)

    pricelist_id = models.IntegerField(null=True, blank=True)

    emails = models.TextField(null=True, blank=True)

    class Meta:
        db_table = "LinkedPrice"


    def __str__(self):
        return f"{self.article_code} - {self.product_name}"    


class ShiptoMapping(models.Model):
    """modal to store shipto, Parent shipto- product mappings"""
    shipto_code = models.IntegerField(null=True, blank=True)
    ship_to_id = models.IntegerField(null=True, blank=True)
    parent_shipto_code = models.IntegerField(null=True, blank=True)
    parent_shipto_id = models.IntegerField(null=True, blank=True)
    product_article = models.CharField(max_length=100, null=True, blank=True)
    product_id = models.IntegerField(null=True, blank=True)
    bill_to_code = models.IntegerField(null=True, blank=True)
    bill_to_id = models.IntegerField(null=True, blank=True)
    parent_bill_to_code = models.IntegerField(null=True, blank=True)

    class Meta:
        db_table = "ShiptoMapping"


    def __str__(self):
        return f"{self.parent_shipto_code} - {self.shipto_code} - {self.product_article}"