from peewee import (
    CharField,
    FloatField,
    DateTimeField,
    IntegerField
)
from peewee import SQL
from ..base import BaseModelSqlView, mysql_db


class WebviewEmplacement(BaseModelSqlView):

    pk = IntegerField(
        column_name='x_pk', constraints=[SQL("DEFAULT 0")])
    adr = CharField(
        column_name='x_adr', null=True, constraints=[SQL("DEFAULT ''")])
    ref = CharField(
        column_name='x_ref', null=True, constraints=[SQL("DEFAULT ''")])
    bloque = CharField(
        column_name='x_bloque', null=True, constraints=[SQL("DEFAULT ''")])
    libelle = CharField(
        column_name='x_libelle', null=True, constraints=[SQL("DEFAULT ''")])
    etat = CharField(
        column_name='x_etat', null=True, constraints=[SQL("DEFAULT ''")])
    x_format = CharField(
        column_name='x_format', null=True, constraints=[SQL("DEFAULT ''")])
    qte = FloatField(
        column_name='x_qte', null=True, constraints=[SQL("DEFAULT 0")])
    fraction = FloatField(
        column_name='x_fraction', null=True, constraints=[SQL("DEFAULT ''")])
    ucd = CharField(
        column_name='x_ucd', null=True, constraints=[SQL("DEFAULT ''")])
    sortie = DateTimeField(
        column_name='x_tsortie', null=True, constraints=[SQL("DEFAULT ''")])
    entree = DateTimeField(
        column_name='x_tentree', constraints=[SQL("DEFAULT ''")])
    lot = CharField(
        column_name='x_lot', constraints=[SQL("DEFAULT ''")])
    peremp = DateTimeField(
        column_name='x_tperemp', constraints=[SQL("DEFAULT 0")])
    contenant = DateTimeField(
        column_name='x_contenant', constraints=[SQL("DEFAULT '0000-00-00 00:00:00'")])
    designation = CharField(
        column_name='x_desig', null=True, constraints=[SQL("DEFAULT ''")])
    magasin = CharField(
        column_name='x_mag', null=True, constraints=[SQL("DEFAULT ''")])
    taquin = CharField(
        column_name='x_taquin', null=True, constraints=[SQL("DEFAULT ''")])
    demi_taquin = CharField(
        column_name='x_demi_taquin', null=True, constraints=[SQL("DEFAULT ''")])

    def create_view(self):
        """Create the view in the database"""
        query = """CREATE OR REPLACE VIEW webview_emplacement AS
                    SELECT s.x_pk, `a`.`x_adr` AS `x_adr`, `d`.`x_libelle` AS `x_libelle`,
                        IF((isnull(`s`.`x_contenant`) AND (`a`.`x_contenant` = '')), 'Sans contenant',
                            IF(isnull(`s`.`x_ref`), 'Vide', 'Avec produits')) AS `x_etat`,
                        `a`.`x_format` AS `x_format`, `s`.`x_ref` AS `x_ref`,
                        IF(isnull(`s`.`x_bloque`), `a`.`x_bloque`, `s`.`x_bloque`) AS `x_bloque`,
                        `s`.`x_qte` AS `x_qte`, `s`.`x_fraction` AS `x_fraction`, `s`.`x_ucd` AS `x_ucd`,
                        `s`.`x_tsortie` AS `x_tsortie`, `s`.`x_tentree` AS `x_tentree`, `s`.`x_lot` AS `x_lot`,
                        `s`.`x_tperemp` AS `x_tperemp`, `a`.`x_contenant` AS `x_contenant`,
                        `r`.`x_desig` AS `x_desig`, `a`.`x_mag` AS `x_mag`,
                        `ta`.`x_taquin` AS `x_taquin`, `de`.`x_demi_taquin` AS `x_demi_taquin`
                    FROM `f_adr` `a`
                    LEFT JOIN `f_stock` `s` ON ((`a`.`x_adr` = `s`.`x_adr`))
                    LEFT JOIN `f_ref` `r` ON ((`r`.`x_ref` = `s`.`x_ref`))
                    LEFT JOIN `webview_taquin` `ta` ON ((LEFT(`a`.`x_adr`,(LENGTH(`ta`.`x_adr`) - 4)) = LEFT(`ta`.`x_adr`,(LENGTH(`ta`.`x_adr`) - 4))))
                    LEFT JOIN `webview_demi_taquin` `de` ON ((LEFT(`a`.`x_adr`,(LENGTH(`de`.`x_adr`) - 4)) = LEFT(`de`.`x_adr`,(LENGTH(`de`.`x_adr`) - 4))))
                    LEFT JOIN `f_defaut` `d` ON ((`d`.`x_valeur` = IF(isnull(`s`.`x_bloque`), `a`.`x_bloque`, `s`.`x_bloque`)))"""  # noqa
        mysql_db.execute_sql(query)

    class Meta:
        table_name = 'webview_emplacement'


Emplacements = WebviewEmplacement
