from django.db import models

class LoadPointMaster(models.Model):
    LoadPointMasterID  = models.AutoField(primary_key=True)
    LoadPointName  = models.CharField(max_length=100)
    LoadPointAddress  = models.CharField(max_length=255, null=True, blank=True)
    LoadPointCity  = models.CharField(max_length=50)
    AddressLine1 = models.CharField(max_length=255)
    AddressLine2 = models.CharField(max_length=255)
    AddressLine3 = models.CharField(max_length=255)
    CountryCode = models.CharField(max_length=50)
    Phone = models.CharField(max_length=250)
    Fax = models.CharField(max_length=250)
    Email = models.CharField(max_length=250)
    Code = models.CharField(max_length=50)
    PostalCode = models.CharField(max_length=50)
    RequiredReferenceNumber = models.BooleanField(default=False)
    IsDeleted = models.BooleanField(default=False)

    class Meta:
        db_table = 'LoadPointMaster'
        managed = False

    def __str__(self):
        return f"{self.LoadPointMasterID}, {self.LoadPointName}, {self.LoadPointCity}"

class IncoTerms(models.Model):
    Id = models.AutoField(primary_key=True)
    Code = models.CharField(max_length=10)  # Code for IncoTerms (varchar(10))
    Remarks = models.CharField(max_length=255, null=True, blank=True)  # Remarks or additional info
    TransportNeeded = models.BooleanField(null=True, blank=True)
    SortId = models.IntegerField()
    CreatedBy = models.IntegerField()  # User who created the record
    CreatedAt = models.DateTimeField(auto_now_add=True)  # Timestamp for record creation (auto-generated)
    UpdatedBy = models.IntegerField(null=True, blank=True)  # User who last updated the record (nullable)
    UpdatedAt = models.DateTimeField(null=True, blank=True)  # Timestamp for the last update (nullable)
    IsDeleted = models.BooleanField(default=False)  # Soft delete flag (default: active)

    class Meta:
        db_table = 'IncoTerms'  # Specify the table name explicitly if it doesn't follow Django's default naming convention

    def __str__(self):
        return self.code

class LoadingSlots(models.Model):
    Id = models.AutoField(primary_key=True)
    LoadPointId = models.IntegerField()
    Slot = models.CharField(max_length=50)
    TimeFrom = models.TextField()
    TimeTo = models.TextField()
    WareHouseCode = models.CharField(max_length=50)
    ProductCategory = models.TextField()
    RequiredReferenceNumber = models.BooleanField(default=False)
    IsDeleted = models.BooleanField(default=False)

    class Meta:
        db_table = 'LoadingSlots'

    def __str__(self):
        return f"LoadingSlot {self.Id} - {self.Slot}"

class ProductLoadPoints(models.Model):
    ID = models.AutoField(primary_key=True)
    ProductID = models.IntegerField()
    LoadSlotId = models.IntegerField()
    CreatedBy = models.IntegerField()
    CreatedOn = models.DateTimeField()
    UpdatedBy = models.IntegerField()
    UpdatedOn = models.DateTimeField()
    IsDeleted = models.BooleanField(default=False)

    class Meta:
        db_table = 'ProductLoadPoints'

    def __str__(self):
        return f"ProductLoadPoint {self.ID} - Product {self.ProductID}"


class TransportMaster(models.Model):
    ID = models.AutoField(primary_key=True)  # AutoField to match ID column with auto-increment behavior
    Transporter = models.CharField(max_length=100)  # Transporter field with max length 100
    Email = models.EmailField(max_length=1000)  # Email field with max length 100
    PhoneNumber = models.CharField(max_length=20)  # Phone number field with max length 20
    Code = models.CharField(max_length=50)

    AddressLine1 = models.CharField(max_length=255, null=True, blank=True)
    AddressLine2 = models.CharField(max_length=255, null=True, blank=True)
    PostalCode = models.CharField(max_length=20, null=True, blank=True)
    City = models.CharField(max_length=50, null=True, blank=True)
    Country = models.CharField(max_length=100, null=True, blank=True)
    CountryCode = models.CharField(max_length=50, null=True, blank=True)
    CreatedBy = models.IntegerField()  # Integer field for the user who created the record
    CreatedOn = models.DateTimeField(auto_now_add=True)  # DateTime field for creation timestamp
    UpdatedBy = models.IntegerField()  # Integer field for the user who last updated the record
    UpdatedOn = models.DateTimeField(auto_now=True)  # DateTime field for the last updated timestamp
    IsDeleted = models.BooleanField(default=False)  # Bit field to mark if the record is deleted (default is False)

    class Meta:
        db_table = 'TransportMaster'  # Ensure the table name matches the one in your SQL schema

    def __str__(self):
        return self.Transporter


class TransportPriceMaster(models.Model):
    Id = models.AutoField(primary_key=True)  # AutoField to match ID column with auto-increment behavior
    # TransporterId = models.IntegerField()  # Integer field for the Transporter (Foreign Key to TransportMaster)
    TransporterId = models.ForeignKey(TransportMaster, on_delete=models.CASCADE, db_column='TransporterId' )
    ProductId = models.IntegerField()  # Integer field for the Product (Foreign Key to Product table)
    Packaging = models.CharField(max_length=50)  # Packaging field with max length of 50 characters
    LoadPointId = models.IntegerField()  # Integer field for Load Point Id (Foreign Key to Loading Points)
    DestinationAddressId = models.IntegerField()  # Integer field for the destination address ID
    DestinationCity = models.CharField(max_length=100)  # City field with max length 100 characters
    DestinationCountry = models.CharField(max_length=50)  # Country field with max length 50 characters
    DestinationZIP = models.CharField(max_length=20)  # ZIP field with max length of 20 characters
    IncoTermId = models.IntegerField()  # Integer field to reference the Incoterm (Foreign Key)
    # PricePerUnit = models.CharField(max_length=50)  # Price per unit as a string (e.g., price)
    ChemicalName = models.CharField(max_length=100)  # Chemical name (varchar(100))
    # DilutionFrom = models.DecimalField(max_digits=10, decimal_places=2)  # Dilution from (DECIMAL(10, 2))
    # DilutionTo = models.DecimalField(max_digits=10, decimal_places=2)  # Dilution to (DECIMAL(10, 2))
    DilutionFrom = models.FloatField(null=True, blank=True)
    DilutionTo = models.FloatField(null=True, blank=True)
    Concentration = models.FloatField(null=True, blank=True)
    OriginCity = models.CharField(max_length=100)  # Origin city (varchar(100))
    CreatedBy = models.IntegerField()  # Integer field for the user who created the record
    CreatedOn = models.DateTimeField(auto_now_add=True)  # Automatically set the creation timestamp
    UpdatedBy = models.IntegerField()  # Integer field for the user who last updated the record
    UpdatedOn = models.DateTimeField(auto_now=True)  # Automatically update the timestamp on each save
    IsDeleted = models.BooleanField(default=False)  # Bit field to mark if the record is deleted (default is False)
    BasePrice = models.DecimalField(max_digits=10, decimal_places=2)
    EffectivePrice = models.DecimalField(max_digits=10, decimal_places=2)
    ExactERPAddressId = models.IntegerField()
    Grade = models.CharField(max_length=255, null=True, blank=True)
    Frequency = models.CharField(max_length=200, null=True, blank=True)
    RouteVia = models.CharField(max_length=500, null=True, blank=True)

    class Meta:
        db_table = 'TransportPriceMaster'  # Ensures the table name matches the one in your SQL schema

    def __str__(self):
        return f"Transport Price for Product {self.ProductId} by Transporter {self.TransporterId}, {self.EffectivePrice}"


class TransportDistanceMaster(models.Model):
    Id = models.AutoField(primary_key=True, db_column="Id")  # Auto-increment primary key
    CustomerId = models.IntegerField(null=True, blank=True, db_column="CustomerId")  # Nullable Customer ID
    LoadPointId = models.IntegerField(db_column="LoadPointId")  # Non-nullable Load Point ID
    DestinationId = models.IntegerField(db_column="DestinationId")  # Non-nullable Destination ID
    TransportTime = models.TimeField(db_column="TransportTime")  # Non-nullable time taken for transport
    PreferredLoadingDateOffset = models.IntegerField(null=False, db_column="PreferredLoadingDateOffset")  # Offset in days for the preferred loading date
    PreferredLoadingTime = models.CharField(max_length=20, null=False, db_column="PreferredLoadingTime")
    ExactERPAddressId = models.IntegerField(null=True)
    CreatedBy = models.IntegerField(db_column="CreatedBy")  # Non-nullable Created By field
    CreatedOn = models.DateTimeField(
        auto_now_add=True, db_column="CreatedOn"
    )  # Automatically sets creation timestamp
    UpdatedBy = models.IntegerField(null=True, blank=True, db_column="UpdatedBy")  # Nullable Updated By field
    UpdatedOn = models.DateTimeField(
        auto_now=True, null=True, blank=True, db_column="UpdatedOn"
    )  # Automatically updates timestamp on save
    IsDeleted = models.BooleanField(default=False, db_column="IsDeleted")  # Soft delete flag

    class Meta:
        db_table = "TransportDistanceMaster"  # Explicitly sets table name

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


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()
    DeliveryCertificateNeeded = models.IntegerField()
    CleaningCertificateNeeded = models.IntegerField()
    # Text fields for LoadListConditions and CMRConditions
    LoadListConditions = models.TextField(blank=True, null=True)
    TankType = models.TextField(blank=True, null=True)

    # Optionally, you can add meta information for table name and ordering
    class Meta:
        db_table = 'TermsAndConditions'  # This is the table name in the database
        ordering = ['Id']  # Optional: Default ordering by Id

class ShippingPortMaster(models.Model):
    """Ports a sea container is collected from and returned to.

    CountryCode is here because the CMR prints the unloading place as
    "<country> - <port>"; that mapping used to be hardcoded in the frontend.
    """
    Id = models.AutoField(primary_key=True)
    PortName = models.CharField(max_length=100)
    CountryCode = models.CharField(max_length=10, 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 = 'ShippingPortMaster'
        ordering = ['PortName']

    def __str__(self):
        return self.PortName


class ShippingAgencyMaster(models.Model):
    """Agencies handling the sea leg, and where to reach them.

    MailIds is comma separated, the same convention as
    TransporterContacts.MailIds, so the existing splitting code applies.
    """
    Id = models.AutoField(primary_key=True)
    AgencyName = models.CharField(max_length=100)
    MailIds = models.TextField(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 = 'ShippingAgencyMaster'
        ordering = ['AgencyName']

    def __str__(self):
        return self.AgencyName


class TransporterContacts(models.Model):
    Id = models.AutoField(primary_key=True)
    TransporterId = models.IntegerField()
    ProductCategory = models.CharField(max_length=255)
    MailIds = models.TextField(null=True, blank=True)

    class Meta:
        db_table = 'TransporterContacts'  # This is the table name in the database
        ordering = ['Id']