from peewee import (
    IntegerField,
    CharField,
    DateTimeField,
    BooleanField,
    AutoField,
    FloatField,
    TextField,
    DateField
)
from peewee import SQL, DoesNotExist, fn
from playhouse.hybrid import hybrid_method
from ..base import InnodbModel, MedianLibException
from .stock import Stock
from .magasin import Zone, ZoneMag
from .dispensation import FItem, FListe
from .ucd import Ucd
from .cip import Gtin
from ..constant import DRUGS_ANOMALY, DRUGS_FRACTION
import datetime

# !! use DRUGS_FRACTION constants instead !!
# TODO: remvoe this in V2
FRACTION = DRUGS_FRACTION


class ProductDomain(InnodbModel):
    """Manage drugs domain"""
    pk = AutoField(
        column_name='x_pk', primary_key=True)
    domain = CharField(
        column_name='x_domaine', max_length=10, constraints=[SQL("DEFAULT ''")], default='')
    description = CharField(
        column_name='x_lib_domaine', max_length=160, constraints=[SQL("DEFAULT ''")], default='')
    invoice = CharField(
        column_name='x_fact', max_length=30, constraints=[SQL("DEFAULT ''")], default='')
    control = BooleanField(
        column_name='x_g_controle', constraints=[SQL("DEFAULT 0")], default=0)

    class Meta:
        table_name = 'f_domaine'
        indexes = (
            (('x_domaine',), True),
            (('x_lib_domaine',), False),
        )


class Product(InnodbModel):
    pk = AutoField(column_name="x_pk", primary_key=True)
    reference = CharField(
        column_name='x_ref', unique=True, null=True, max_length=35,
        constraints=[SQL("DEFAULT ''")], default='')
    designation = CharField(
        column_name='x_desig', null=True, max_length=100,
        constraints=[SQL("DEFAULT ''")], default='')
    tmodif = DateTimeField(
        column_name="x_tmodif",
        null=True,
        constraints=[SQL("DEFAULT '0000-00-00 00:00:00'")],
    )
    tsortie = DateTimeField(
        column_name="x_tsortie",
        null=True,
        constraints=[SQL("DEFAULT '0000-00-00 00:00:00'")],
    )
    tentree = DateTimeField(
        column_name="x_tentree",
        null=True,
        constraints=[SQL("DEFAULT '0000-00-00 00:00:00'")],
    )
    tinventaire = DateTimeField(
        column_name='x_tinvent',
        null=True,
        constraints=[SQL("DEFAULT '0000-00-00 00:00:00'")]
    )
    adr_pref = CharField(
        column_name='x_adr_pref', max_length=19, constraints=[SQL("DEFAULT ''")], default='')
    condi = FloatField(
        column_name='x_condi', null=True, constraints=[SQL("DEFAULT 0.000")], default=0.000)
    tcreation = DateField(
        column_name='x_dcreat', null=True, constraints=[SQL("DEFAULT '0000-00-00'")])
    qml_cde = IntegerField(
        column_name='x_qml_cde', null=True, constraints=[SQL("DEFAULT 0")], default=0)
    qml_serv = IntegerField(
        column_name='x_qml_serv', null=True, constraints=[SQL("DEFAULT 0")], default=0)
    actif = BooleanField(
        column_name='x_actif', null=True, constraints=[SQL("DEFAULT 0")], default=0)
    cip = CharField(
        column_name='x_cip', null=True, max_length=20, constraints=[SQL("DEFAULT ''")], default='')
    atc = CharField(
        column_name='x_atc', null=True, max_length=20, constraints=[SQL("DEFAULT ''")], default='')
    ucd = CharField(
        column_name='x_ucd', null=True, max_length=20, constraints=[SQL("DEFAULT ''")], default='')
    dci = CharField(
        column_name='x_dci', max_length=100, constraints=[SQL("DEFAULT ''")], default='')
    forme = CharField(
        column_name="x_forme", max_length=20,
        constraints=[SQL("DEFAULT ''")], default=""
    )
    desig_bis = CharField(
        column_name='x_desig_bis', max_length=100, constraints=[SQL("DEFAULT ''")], default='')
    adr_ext = CharField(
        column_name='x_adr_ext', max_length=19, constraints=[SQL("DEFAULT ''")], default='')
    volume = CharField(
        column_name='x_volume', max_length=20, constraints=[SQL("DEFAULT ''")], default='')
    robot = BooleanField(
        column_name='x_robot', constraints=[SQL("DEFAULT 0")], default=0)
    unit_gest = CharField(
        column_name='x_unit_gest', max_length=20, constraints=[SQL("DEFAULT ''")], default='')
    unit_admin = CharField(
        column_name='x_unit_admin', max_length=20, constraints=[SQL("DEFAULT ''")], default='')
    alpha_1 = CharField(
        column_name='x_alpha_1', null=True, max_length=100, constraints=[SQL("DEFAULT ''")], default='')
    alpha_2 = CharField(
        column_name='x_alpha_2', null=True, max_length=100, constraints=[SQL("DEFAULT ''")], default='')
    alpha_3 = CharField(
        column_name='x_alpha_3', null=True, max_length=100, constraints=[SQL("DEFAULT ''")], default='')
    alpha_4 = CharField(
        column_name='x_alpha_4', null=True, max_length=100, constraints=[SQL("DEFAULT ''")], default='')
    alpha_5 = CharField(
        column_name='x_alpha_5', null=True, max_length=100, constraints=[SQL("DEFAULT ''")], default='')
    alpha_6 = CharField(
        column_name='x_alpha_6', null=True, max_length=100, constraints=[SQL("DEFAULT ''")], default='')
    alpha_7 = CharField(
        column_name='x_alpha_7', null=True, max_length=100, constraints=[SQL("DEFAULT ''")], default='')
    alpha_8 = CharField(
        column_name='x_alpha_8', null=True, max_length=100, constraints=[SQL("DEFAULT ''")], default='')
    alpha_9 = CharField(
        column_name='x_alpha_9', null=True, max_length=100, constraints=[SQL("DEFAULT ''")], default='')
    alpha_10 = CharField(
        column_name='x_alpha_10', null=True, max_length=100, constraints=[SQL("DEFAULT ''")], default='')
    url = CharField(
        column_name='x_url', null=True, max_length=1024, constraints=[SQL("DEFAULT ''")], default='')
    num_1 = FloatField(
        column_name='x_num_1', null=True, constraints=[SQL("DEFAULT 0.000")], default=0.000)
    num_2 = FloatField(
        column_name='x_num_2', null=True, constraints=[SQL("DEFAULT 0.000")], default=0.000)
    reap_mode = CharField(
        column_name='x_reap_mode', max_length=1, constraints=[SQL("DEFAULT 'T'")], default='T')
    prix_unit = FloatField(
        column_name='x_prix_u', null=True, constraints=[SQL("DEFAULT 0.000")], default=0.000)
    coef_conv = FloatField(
        column_name="x_coef_conv", constraints=[SQL("DEFAULT 0.000")]
    )
    delai_peremp = IntegerField(
        column_name='x_delai_peremp', constraints=[SQL("DEFAULT 0")], default=0)
    externe = BooleanField(
        column_name='x_externe', null=True, constraints=[SQL("DEFAULT 0")], default=0)
    conversion_ucd = BooleanField(
        column_name='x_conversion_ucd', null=True, constraints=[SQL("DEFAULT 0")], default=0)
    multidose_par_uf = BooleanField(
        column_name='x_multidose_par_uf', constraints=[SQL("DEFAULT 0")], default=0)
    com_med = TextField(
        column_name='x_com_med', null=True, default="")
    bac = IntegerField(
        column_name='x_bac', constraints=[SQL("DEFAULT 0")], default=0)
    stup = BooleanField(
        column_name='x_stup', constraints=[SQL("DEFAULT 0")], default=0)
    risque = BooleanField(
        column_name='x_risque', constraints=[SQL("DEFAULT 0")], default=0)
    warn_pct = IntegerField(
        column_name='x_warn_pct', constraints=[SQL("DEFAULT 20")], default=20)
    tiroir_bas = IntegerField(
        column_name='x_tiroir_bas', null=True, constraints=[SQL("DEFAULT 0")], default=0)
    anomaly = CharField(
        column_name="x_anomalie", max_length=10,
        constraints=[SQL("DEFAULT ''")], default="")

    @hybrid_method
    def stock_liste(self, typemag, fraction):
        ret = 0
        try:
            stk = FItem().select(
                (fn.SUM(FItem.qte_dem) - fn.SUM(FItem.qte_serv)).alias("total")
            ).join(FListe, on=(FListe.liste == FItem.liste)).where(
                FListe.zone_fin == typemag, FListe.mode == 'E',
                FItem.reference == self.reference,
                FItem.fraction == fraction
            )
            if len(stk):
                ret = stk[0].total or 0
        except DoesNotExist:  # pragma: no cover
            pass
        return ret

    @hybrid_method
    def stock_magasin(self, magasin, fraction):
        """Retrieve the quantity in stock"""
        ret = 0
        try:
            stk = Stock().select(
                fn.SUM(Stock.quantite).alias("total")
            ).where(
                Stock.magasin == magasin, Stock.reference == self.reference,
                Stock.fraction == fraction,
                Stock.date_peremption > datetime.datetime.now()
            )
            if len(stk):
                ret = stk[0].total or 0
        except DoesNotExist:  # pragma: no cover
            ret = 0
        return ret

    @hybrid_method
    def stock_coupe(self, type_mag, fraction):
        """
        Retrieve the quantity in stock

        A cut-stock only exists in the older machines, that's why zone/zonemag is used.
        No cut-stock for newermachines : Aide -Pick, -Cut & ACCED_V2/3
        """
        ret = 0
        try:
            stk = Stock().select(fn.SUM(Stock.quantite).alias("total")).join(
                ZoneMag, on=(Stock.magasin == ZoneMag.mag)
            ).join(
                Zone, on=((Zone.zone == ZoneMag.zone)
                          & (Zone.appro == 1))
            ).where(
                Stock.zone_admin == type_mag,
                Stock.reference == self.reference,
                Stock.fraction == fraction
            )
            if len(stk):
                ret = stk[0].total or 0
        except DoesNotExist:  # pragma: no cover
            ret = 0
        return ret

    @hybrid_method
    def cdu_list(self,) -> list:
        """ List Common Dispensing Unit (CDU) for this reference

        :returns: List of Ucd ModelSelect
        """
        ret = []
        try:
            cdus = Ucd.select(Ucd).where(Ucd.reference == self.reference)
            ret = [u for u in cdus]
        except DoesNotExist:  # pragma: no cover
            ret = []
        return ret

    @hybrid_method
    def gtin_list(self,) -> list:
        ret = []
        try:
            cdus = Ucd.select(Ucd).where(Ucd.reference == self.reference)
            for u in cdus:
                gtins = Gtin.select(Gtin).where(Gtin.ucd == u.ucd)
                for g in gtins:
                    ret.append(g)
        except DoesNotExist:  # pragma: no cover
            ret = []
        return ret

    class Meta:
        table_name = "f_ref"
        indexes = (
            (('x_ref',), True),
        )

    def save(self, force_insert=False, only=None):
        """Enforce control on each information passed"""
        if self.anomaly and self.anomaly not in [da[0] for da in DRUGS_ANOMALY]:
            raise ValueError('Anomaly value is incorrect')
        return super().save(force_insert, only)


class Seuil(InnodbModel):
    pk = AutoField(
        column_name='x_pk', primary_key=True)
    reference = CharField(
        column_name='x_ref', constraints=[SQL("DEFAULT ''")])
    zone = CharField(
        column_name='x_zone', constraints=[SQL("DEFAULT ''")])
    stock_mini = FloatField(
        column_name='x_stk_mini', constraints=[SQL("DEFAULT 0.000")])
    stock_maxi = FloatField(
        column_name='x_stk_maxi', constraints=[SQL("DEFAULT 0.000")])
    fraction = IntegerField(
        column_name='x_fraction', constraints=[SQL("DEFAULT 100")])

    class Meta:
        table_name = 'f_seuil'
        indexes = (
            (('x_ref', 'x_zone'), True),
        )

    def save(self, force_insert=False, only=None):
        """Enforce control on each information passed"""
        if self.fraction not in DRUGS_FRACTION:
            raise ValueError('Fraction value incorrect')
        return super().save(force_insert, only)


class ContainerFormat(InnodbModel):
    """Blacklist batch lot from the supplier autorisation code"""
    pk = AutoField(
        column_name='x_pk', primary_key=True)
    code = CharField(
        column_name='x_format', max_length=10,
        constraints=[SQL("DEFAULT ''")], default="")
    dimension_x = IntegerField(
        column_name='x_dim_x', constraints=[SQL("DEFAULT 0")], default=0)
    dimension_y = IntegerField(
        column_name='x_dim_y', constraints=[SQL("DEFAULT 0")], default=0)
    dimension_z = IntegerField(
        column_name='x_dim_z', constraints=[SQL("DEFAULT 0")], default=0)
    orientation = CharField(
        column_name='x_orient', max_length=1,
        constraints=[SQL("DEFAULT ''")], default="Y")
    divide_number = IntegerField(
        column_name='x_nb_div', constraints=[SQL("DEFAULT 0")], default=0)
    color = IntegerField(
        column_name='x_couleur', constraints=[SQL("DEFAULT 0")], default=12632256)
    measurement_x_mm = IntegerField(
        column_name='x_mm_x', constraints=[SQL("DEFAULT 0")], default=0)
    measurement_y_mm = IntegerField(
        column_name='x_mm_y', constraints=[SQL("DEFAULT 0")], default=0)
    measurement_z_mm = IntegerField(
        column_name='x_mm_z', constraints=[SQL("DEFAULT 0")], default=0)
    type_tray = CharField(
        column_name='x_type_bac', max_length=10,
        constraints=[SQL("DEFAULT ''")], default="")

    @hybrid_method
    def volume(self,):
        """Compute the volume for this """
        return (self.measurement_x_mm * self.measurement_y_mm * self.measurement_z_mm) or 0

    def save(self, force_insert=False, only=None):
        """Enforce control on each information passed"""
        # We retrieve the liste
        if self.dimension_x < 0:
            raise MedianLibException("Width must greather or equal to 0")
        if self.dimension_y < 0:
            raise MedianLibException("Height must greather or equal to 0")
        if self.dimension_z < 0:
            raise MedianLibException("Depth must greather or equal to 0")
        return super().save(force_insert, only)

    class Meta:
        table_name = 'f_format'
        indexes = (
            (('x_format',), True),
        )


class LotRetire(InnodbModel):
    """Blacklist batch lot from the supplier autorisation code"""
    pk = AutoField(
        column_name='x_pk', primary_key=True)
    ucd = CharField(
        column_name='x_ucd', max_length=20,
        constraints=[SQL("DEFAULT ''")], default="")
    lot = CharField(
        column_name='x_lot', max_length=20,
        constraints=[SQL("DEFAULT ''")], default="")

    class Meta:
        table_name = 'f_lot_retire'
        indexes = (
            (('x_ucd', 'x_lot'), True),
            (('x_ucd',), False),
        )


class TopFive(InnodbModel):
    pk = AutoField(
        column_name='x_pk', primary_key=True)
    cle = CharField(
        column_name='x_cle', max_length=10,
        constraints=[SQL("DEFAULT ''")])
    val = CharField(
        column_name='x_val', max_length=20,
        constraints=[SQL("DEFAULT ''")])
    poste = CharField(
        column_name='x_poste', max_length=20,
        constraints=[SQL("DEFAULT ''")])
    user = CharField(
        column_name='x_user', max_length=60,
        constraints=[SQL("DEFAULT ''")])
    mode = BooleanField(
        column_name='x_mode', constraints=[SQL("DEFAULT 0")])
    nb = IntegerField(
        column_name='x_nb', null=True, constraints=[SQL("DEFAULT 0")])

    class Meta:
        table_name = 'f_top5'
        indexes = (
            (('x_cle', 'x_val', 'x_poste', 'x_mode'), True),
        )
