from django.db import models
from django_db_views.db_view import DBView


class Vlan(models.Model):
    host_vlan = models.PositiveSmallIntegerField()
    oam_vlan = models.PositiveSmallIntegerField()
    mgmt_vlan = models.PositiveSmallIntegerField()
    nfs_vlan = models.PositiveSmallIntegerField(blank=True, null=True)

    def __str__(self):
        return "pk: %s , host_vlan: %s , oam_vlan: %s , mgmt_vlan: %s , nfs_vlan: %s" % (self.pk, self.host_vlan, self.oam_vlan, self.mgmt_vlan, self.nfs_vlan)

    class Meta:
        constraints = [models.UniqueConstraint(fields=["host_vlan", "oam_vlan", "mgmt_vlan", "nfs_vlan"], name="unique_vlan")]


class Namespace(models.Model):
    namespace_name = models.CharField(max_length=255)

    def __str__(self):
        return "pk: %s , namespace_name: %s" % (self.pk, self.namespace_name)

    class Meta:
        constraints = [models.UniqueConstraint(fields=["namespace_name"], name="unique_namespace")]


class SapClli(models.Model):
    clli = models.CharField(max_length=255, blank=True, null=True)

    def __str__(self):
        return "pk: %s , clli: %s" % (self.pk, self.clli)

    class Meta:
        constraints = [models.UniqueConstraint(fields=["clli"], name="unique_sapclli")]


class Location(models.Model):
    fuze_spm_site_id = models.CharField(max_length=16)
    fuze_spm_site_name = models.CharField(max_length=255)
    fuze_spm_market = models.CharField(max_length=255)
    fuze_spm_submarket = models.CharField(max_length=255)
    fuze_spm_site_type = models.CharField(max_length=4, choices=[("SAP", "SAP"), ("DRAN", "DRAN"), ("CRAN", "CRAN")])
    latitude = models.DecimalField(max_digits=12, decimal_places=9, blank=True, null=True)
    longitude = models.DecimalField(max_digits=12, decimal_places=9, blank=True, null=True)
    maint_window_p = models.BooleanField(default=False, blank=True, null=True)

    def __str__(self):
        return "pk: %s , fuze_spm_site_id: %s , fuze_spm_site_name: %s , fuze_spm_market: %s , fuze_spm_submarket: %s , fuze_spm_site_type: %s" % (self.pk, self.fuze_spm_site_id, self.fuze_spm_site_name, self.fuze_spm_market, self.fuze_spm_submarket, self.fuze_spm_site_type)

    class Meta:
        constraints = [models.UniqueConstraint(fields=["fuze_spm_site_id",
                                                       "fuze_spm_site_name",
                                                       "fuze_spm_market",
                                                       "fuze_spm_submarket",
                                                       "fuze_spm_site_type"],
                                               name="unique_location")]


class CellSiteRouter(models.Model):
    csr_hostname = models.CharField(max_length=255, blank=True, null=True)
    oam_subnet = models.CharField(max_length=255, blank=True, null=True)
    oam_default_gateway = models.CharField(max_length=255, blank=True, null=True)
    ilo_subnet = models.CharField(max_length=255, blank=True, null=True)
    ilo_default_gateway = models.CharField(max_length=255, blank=True, null=True)

    def __str__(self):
        return "pk: %s , csr_hostname: %s" % (self.pk, self.csr_hostname)

    class Meta:
        constraints = [models.UniqueConstraint(fields=["csr_hostname"], name="unique_cellsiterouter")]


class Cluster(models.Model):
    cluster_name = models.CharField(max_length=255)
    oam_vip_hostname = models.CharField(max_length=255, blank=True, null=True)
    oam_vip_address = models.CharField(max_length=255, blank=True, null=True)
    mgmt_subnet = models.CharField(max_length=255, blank=True, null=True)
    mgmt_default_gateway = models.CharField(max_length=255, blank=True, null=True)
    mgmt_address_range_start = models.CharField(max_length=255, blank=True, null=True)
    mgmt_address_range_end = models.CharField(max_length=255, blank=True, null=True)
    is_central_controller = models.SmallIntegerField(default=0, blank=True, null=True)
    sap_clli = models.ForeignKey("SapClli", on_delete=models.SET_NULL, blank=True, null=True)
    parent_cluster = models.ForeignKey("Cluster", on_delete=models.SET_NULL, blank=True, null=True)
    location = models.ForeignKey("Location", on_delete=models.SET_NULL, blank=True, null=True)
    vlan = models.ForeignKey("Vlan", on_delete=models.SET_NULL, blank=True, null=True)
    csr = models.ForeignKey("CellSiteRouter", on_delete=models.SET_NULL, blank=True, null=True)
    namespace = models.ForeignKey("Namespace", on_delete=models.SET_NULL, blank=True, null=True)

    def __str__(self):
        return "pk: %s , cluster_name: %s" % (self.pk, self.cluster_name)

    class Meta:
        constraints = [models.UniqueConstraint(fields=["cluster_name"], name="unique_cluster")]


class Server(models.Model):
    server_number = models.PositiveSmallIntegerField(blank=True, null=True)
    oam_hostname = models.CharField(max_length=255, blank=True, null=True)
    oam_host_address = models.CharField(max_length=255, blank=True, null=True)
    ilo_hostname = models.CharField(max_length=255)
    ilo_host_address = models.CharField(max_length=255, blank=True, null=True)
    pxe_mac_address = models.CharField(max_length=255, blank=True, null=True)
    intel_nic_firmware_version = models.CharField(max_length=255, blank=True, null=True)
    cluster = models.ForeignKey("Cluster", on_delete=models.SET_NULL, blank=True, null=True)
    blade = models.ForeignKey("Blade", on_delete=models.SET_NULL, blank=True, null=True)

    def __str__(self):
        return "pk: %s , ilo_hostname: %s , cluster_id: %s" % (self.pk, self.ilo_hostname, self.cluster_id)

    class Meta:
        constraints = [models.UniqueConstraint(fields=["ilo_hostname"], name="unique_server_byhostname")]
        constraints = [models.UniqueConstraint(fields=["ilo_host_address"], name="unique_server_byiloaddress")]


class Credential(models.Model):
    bmc_password_admin_default = models.CharField(max_length=255, blank=True, null=True)
    bmc_password_admin = models.CharField(max_length=255, blank=True, null=True)
    bmc_username = models.CharField(max_length=255, blank=True, null=True)
    bmc_password = models.CharField(max_length=255, blank=True, null=True)

    def __str__(self):
        return "pk: %s , bmc_password_admin_default: %s , bmc_password_admin: %s , bmc_username: %s , bmc_password: %s" % (self.pk, self.bmc_password_admin_default, self.bmc_password_admin, self.bmc_username, self.bmc_password)

    class Meta:
        constraints = [models.UniqueConstraint(fields=["bmc_password_admin_default", "bmc_password_admin", "bmc_username", "bmc_password"], name="unique_credential")]


class Chassis(models.Model):
    serial_number = models.CharField(max_length=255, blank=True, null=True)

    def __str__(self):
        return "pk: %s , serial_number: %s" % (self.pk, self.serial_number)

    class Meta:
        constraints = [models.UniqueConstraint(fields=["serial_number"], name="unique_chassis")]


class Blade(models.Model):
    serial_number = models.CharField(max_length=255, blank=True, null=True)
    credential = models.ForeignKey("Credential", on_delete=models.SET_NULL, blank=True, null=True)
    chassis = models.ForeignKey("Chassis", on_delete=models.SET_NULL, blank=True, null=True)
    vendor = models.ForeignKey("Vendor", on_delete=models.SET_NULL, blank=True, null=True)

    def __str__(self):
        return "pk: %s , serial_number: %s , chassis_id: %s , vendor_id: %s" % (self.pk, self.serial_number, self.chassis_id, self.vendor_id)

    class Meta:
        constraints = [models.UniqueConstraint(fields=["serial_number"], name="unique_blade")]


class Vendor(models.Model):
    vendor_name = models.CharField(max_length=255, blank=True, null=True)

    def __str__(self):
        return "pk: %s , vendor_name: %s" % (self.pk, self.vendor_name)

    class Meta:
        constraints = [models.UniqueConstraint(fields=["vendor_name"], name="unique_vendor")]


class FirmwareUpgradeSchedule(models.Model):
    date_scheduled = models.DateTimeField(blank=True, null=True)
    date_completed = models.DateTimeField(blank=True, null=True)
    date_last_failed = models.DateTimeField(blank=True, null=True)
    kirke_ticket_number = models.CharField(max_length=255, blank=True, null=True)
    kirke_ticket_status = models.CharField(max_length=255, blank=True, null=True)
    kirke_ticket_completed = models.DateTimeField(blank=True, null=True)
    server = models.ForeignKey("Server", on_delete=models.SET_NULL, blank=True, null=True)

    def __str__(self):
        return "pk: %s , date_scheduled: %s , kirke_ticket_number: %s , server_id: %s" % (self.pk, self.date_scheduled, self.kirke_ticket_number, self.server_id)

    class Meta:
        constraints = [models.UniqueConstraint(fields=["server_id"], name="unique_firmwareupgradeschedule")]


class WrInstallSchedule(models.Model):
    date_scheduled = models.DateTimeField(blank=True, null=True)
    date_completed = models.DateTimeField(blank=True, null=True)
    date_last_failed = models.DateTimeField(blank=True, null=True)
    kirke_ticket_number = models.CharField(max_length=255, blank=True, null=True)
    kirke_ticket_status = models.CharField(max_length=255, blank=True, null=True)
    kirke_ticket_completed = models.DateTimeField(blank=True, null=True)
    cluster = models.ForeignKey("Cluster", on_delete=models.SET_NULL, blank=True, null=True)

    def __str__(self):
        return "pk: %s , date_scheduled: %s , kirke_ticket_number: %s , cluster_id: %s, date_completed: %s" % (self.pk, self.date_scheduled, self.kirke_ticket_number, self.cluster_id, self.date_completed)

    class Meta:
        constraints = [models.UniqueConstraint(fields=["cluster_id"], name="unique_wrinstallschedule")]


class PatchSchedule(models.Model):
    date_scheduled = models.DateTimeField(blank=True, null=True)
    date_completed = models.DateTimeField(blank=True, null=True)
    date_last_failed = models.DateTimeField(blank=True, null=True)
    kirke_ticket_number = models.CharField(max_length=255, blank=True, null=True)
    kirke_ticket_status = models.CharField(max_length=255, blank=True, null=True)
    kirke_ticket_completed = models.DateTimeField(blank=True, null=True)
    cluster = models.ForeignKey("Cluster", on_delete=models.SET_NULL, blank=True, null=True)

    def __str__(self):
        return "pk: %s , date_scheduled: %s , kirke_ticket_number: %s , cluster_id: %s" % (self.pk, self.date_scheduled, self.kirke_ticket_number, self.cluster_id)

    class Meta:
        constraints = [models.UniqueConstraint(fields=["cluster_id"], name="unique_patchschedule")]


class IcingaBmc(DBView):
    ilo_hostname = models.CharField(max_length=255, blank=True, null=True)
    ilo_host_address = models.CharField(max_length=255, blank=True, null=True)
    location = models.ForeignKey("Location", on_delete=models.DO_NOTHING, blank=True, null=True)
    csr_hostname = models.CharField(max_length=255, blank=True, null=True)
    oam_default_gateway = models.CharField(max_length=255, blank=True, null=True)
    latitude = models.CharField(max_length=255, blank=True, null=True)
    longitude = models.CharField(max_length=255, blank=True, null=True)
    serial_number = models.CharField(max_length=255, blank=True, null=True)
    vendor_name = models.CharField(max_length=255, blank=True, null=True)
    bmc_password_admin_default = models.CharField(max_length=255, blank=True, null=True)
    bmc_password_admin = models.CharField(max_length=255, blank=True, null=True)
    bmc_username = models.CharField(max_length=255, blank=True, null=True)
    bmc_password = models.CharField(max_length=255, blank=True, null=True)
    date_completed = models.DateTimeField(blank=True, null=True)
    view_definition = """
select s.id,
  s.ilo_hostname,
  s.ilo_host_address,
  l.id as location_id,
  csr.oam_default_gateway,
  l.latitude,
  l.longitude,
  csr.csr_hostname,
  b.serial_number,
  v.vendor_name,
  cr.bmc_password_admin_default,
  cr.bmc_password_admin,
  cr.bmc_username,
  cr.bmc_password,
  ws.date_completed
from caas_server s
inner join caas_cluster c
  on s.cluster_id = c.id
inner join caas_location l
  on c.location_id = l.id
inner join caas_cellsiterouter csr
  on c.csr_id = csr.id
left outer join caas_blade b
  on s.blade_id = b.id
left outer join caas_vendor v
  on b.vendor_id = v.id
left outer join caas_credential cr
  on b.credential_id = cr.id
left outer join caas_wrinstallschedule ws
  on c.id = ws.cluster_id
where c.is_central_controller = 0;
"""

    def __str__(self):
        return "pk: %s , ilo_hostname: %s , ilo_host_address: %s , csr_hostname: %s , oam_default_gateway: %s, latitude: %s , longitude: %s , bmc_password_admin_default: %s , bmc_password_admin: %s , bmc_username: %s , bmc_password: %s" % (self.pk, self.ilo_hostname, self.ilo_host_address, self.csr_hostname,  self.oam_default_gateway, self.latitude, self.longitude, self.bmc_password_admin_default, self.bmc_password_admin, self.bmc_username, self.bmc_password, self.date_completed)

    class Meta:
        managed = False
        db_table = "caas_icingabmc"


class IcingaRouter(DBView):
    csr_hostname = models.CharField(max_length=255, blank=True, null=True)
    oam_default_gateway = models.CharField(max_length=255, blank=True, null=True)
    view_definition = """
select l.id as id,
  csr.csr_hostname as csr_hostname,
  csr.oam_default_gateway as oam_default_gateway
from caas_server s
inner join caas_cluster c
  on s.cluster_id = c.id
inner join caas_location l
  on c.location_id = l.id
inner join caas_cellsiterouter csr
  on c.csr_id = csr.id
left outer join caas_blade b
  on s.blade_id = b.id
left outer join caas_vendor v
  on b.vendor_id = v.id
left outer join caas_credential cr
  on b.credential_id = cr.id
where c.is_central_controller=0
group by l.id,
  csr.csr_hostname,
  csr.oam_default_gateway;
"""

    def __str__(self):
        return "pk: %s , csr_hostname: %s , oam_default_gateway: %s" % (self.pk, self.csr_hostname, self.oam_default_gateway)

    class Meta:
        managed = False
        db_table = "caas_icingarouter"


class FirmwareBatch(DBView):
    ilo_host_address = models.CharField(max_length=255, blank=True, null=True)
    ilo_hostname = models.CharField(max_length=255, blank=True, null=True)
    intel_nic_firmware_version = models.CharField(max_length=255, blank=True, null=True)
    bmc_username = models.CharField(max_length=255, blank=True, null=True)
    bmc_password = models.CharField(max_length=255, blank=True, null=True)
    view_definition = """
select s.id,
  s.ilo_hostname,
  s.ilo_host_address,
  s.intel_nic_firmware_version,
  cr.bmc_username,
  cr.bmc_password
from caas_server s
inner join caas_cluster c
  on s.cluster_id = c.id
left outer join caas_blade b
  on s.blade_id = b.id
left outer join caas_credential cr
  on b.credential_id = cr.id
where c.is_central_controller=0;
"""

    def __str__(self):
        return "pk: %s , ilo_host_address: %s" % (self.pk, self.ilo_host_address)

    class Meta:
        managed = False
        db_table = "caas_firmwarebatch"


class WrBatch(DBView):
    cluster_id = models.IntegerField()
    fuze_spm_site_id = models.CharField(max_length=16)
    parent_oam_vip_hostname = models.CharField(max_length=255, blank=True, null=True)
    parent_oam_vip_address = models.CharField(max_length=255, blank=True, null=True)
    parent_mgmt_address_range_start = models.CharField(max_length=255, blank=True, null=True)
    parent_mgmt_address_range_end = models.CharField(max_length=255, blank=True, null=True)
    parent_mgmt_default_gateway = models.CharField(max_length=255, blank=True, null=True)
    parent_mgmt_subnet = models.CharField(max_length=255, blank=True, null=True)
    pxe_mac_address = models.CharField(max_length=255, blank=True, null=True)
    ilo_hostname = models.CharField(max_length=255, blank=True, null=True)
    ilo_host_address = models.CharField(max_length=255, blank=True, null=True)
    oam_hostname = models.CharField(max_length=255, blank=True, null=True)
    oam_host_address = models.CharField(max_length=255, blank=True, null=True)
    oam_vip_address = models.CharField(max_length=255, blank=True, null=True)
    oam_default_gateway = models.CharField(max_length=255, blank=True, null=True)
    mgmt_address_range_start = models.CharField(max_length=255, blank=True, null=True)
    mgmt_address_range_end = models.CharField(max_length=255, blank=True, null=True)
    mgmt_default_gateway = models.CharField(max_length=255, blank=True, null=True)
    mgmt_subnet = models.CharField(max_length=255, blank=True, null=True)
    host_vlan = models.PositiveSmallIntegerField()
    oam_vlan = models.PositiveSmallIntegerField()
    mgmt_vlan = models.PositiveSmallIntegerField()
    cluster_name = models.CharField(max_length=255, blank=True, null=True)
    parent_cluster_name = models.CharField(max_length=255, blank=True, null=True)
    vendor_name = models.CharField(max_length=255, blank=True, null=True)
    intel_nic_firmware_version = models.CharField(max_length=255, blank=True, null=True)
    maint_window_p = models.BooleanField(default=False, blank=True, null=True)
    namespace_id = models.IntegerField()
    namespace_name = models.CharField(max_length=255, blank=True, null=True)
    bmc_username = models.CharField(max_length=255, blank=True, null=True)
    bmc_password = models.CharField(max_length=255, blank=True, null=True)
    view_definition = """
select s.id,
  c.id as cluster_id,
  l.fuze_spm_site_id,
  cp.oam_vip_hostname as parent_oam_vip_hostname,
  cp.oam_vip_address as parent_oam_vip_address,
  cp.mgmt_address_range_start as parent_mgmt_address_range_start,
  cp.mgmt_address_range_end as parent_mgmt_address_range_end,
  cp.mgmt_default_gateway as parent_mgmt_default_gateway,
  cp.mgmt_subnet as parent_mgmt_subnet,
  s.pxe_mac_address,
  s.ilo_hostname,
  s.ilo_host_address,
  s.oam_hostname,
  s.oam_host_address,
  c.oam_vip_address,
  csr.oam_default_gateway,
  c.mgmt_address_range_start,
  c.mgmt_address_range_end,
  c.mgmt_default_gateway,
  c.mgmt_subnet,
  vl.host_vlan,
  vl.oam_vlan,
  vl.mgmt_vlan,
  c.cluster_name,
  cp.cluster_name as parent_cluster_name,
  v.vendor_name,
  s.intel_nic_firmware_version,
  l.maint_window_p,
  n.id as namespace_id,
  n.namespace_name,
  cr.bmc_username,
  cr.bmc_password
from caas_server s
inner join caas_cluster c
  on s.cluster_id = c.id
left outer join caas_cluster cp
  on c.parent_cluster_id = cp.id
inner join caas_cellsiterouter csr
  on c.csr_id = csr.id
inner join caas_cellsiterouter csrp
  on cp.csr_id = csrp.id
inner join caas_location l
  on c.location_id = l.id
inner join caas_vlan vl
  on c.vlan_id = vl.id
inner join caas_namespace n
  on c.namespace_id = n.id
left outer join caas_blade b
  on s.blade_id = b.id
left outer join caas_vendor v
  on b.vendor_id = v.id
left outer join caas_credential cr
  on b.credential_id = cr.id
where c.parent_cluster_id is not null;
"""

    def __str__(self):
        return "pk: %s , ilo_host_address: %s , cluster_name : %s" % (self.pk, self.ilo_host_address, self.cluster_name)

    class Meta:
        managed = False
        db_table = "caas_wrbatch"