from peewee import DoesNotExist, OperationalError

from .models.log import LogDefaut, DefDefaut
from .utils import logger
from .models import (
    PrescriptionItem, Prescription, MagasinService, LogAstus, TopFive,
    HistoriqueMultidoses, MessageAstus, FListeError, Historique,
    Patient, Sejour, Service, WebviewTaquin, WebviewDemiTaquin,
    WebviewEmplacement, WebviewPatientHisto, LogInfo, LogScan, LotRetire,
    GtinAlias, GtinDimension, User, CatalogType, CatalogVersion, CatalogOption,
    CatalogLines, CatalogUnitStock, CatalogColumns, EquipmentUnitStock, EquipmentColumns,
    EquipmentLines, LogEchangeRiedl, ListeValide, ItemValide, Label,
    UnitDose, MagasinFormat, MagLogDefault, Dpm, DpmCheck, DpmAction, DpmComment,
    WsclientError, MagasinGroupe, NonConformity, NonConformityTestLine,
    NonConformityTest, NonConformityErrorType, NonConformityError,
    HistoriqueSachet, NonConformityReport, HistoriquePrise, PrinterCommand, Printer,
    PrinterCommandLabel, PrinterLabel, PrinterMag, PrinterType, PrinterLog,
    ReferencePerService, ProductDomain, LdapServer, LdapServerProfil, DataExtract
)
from .constant import PatientGlobal


class DbMigrator:

    def __init__(self, db_instance):
        """Initilise the migraotr class with the database selected"""
        self.db = db_instance
        logger.debug('Initilize migrator instance')

    def migrate(self, force=None):
        """Launch the migration script
            force argument, drop  some viewe
        """
        if force:
            logger.info('Migrate force activate')
            self.drop_sql_view('webview_patient_histo', WebviewPatientHisto)
            self.drop_sql_view('webview_emplacement', WebviewEmplacement)
            self.drop_sql_view('webview_demi_taquin', WebviewDemiTaquin)
            self.drop_sql_view('webview_taquin', WebviewTaquin)

        self.check_model('f_prescription_item', PrescriptionItem)
        self.check_model('f_prescription', Prescription)
        self.check_field('f_prescription', 'x_demande',
                         "ALTER TABLE f_prescription ADD COLUMN x_demande INT(11) NULL DEFAULT '0';")
        self.check_model('f_mag_dest', MagasinService)
        self.check_model('f_log_astus', LogAstus)
        self.check_model('f_top5', TopFive)
        self.check_model('f_lot_retire', LotRetire)
        self.check_model('f_histo_multidoses', HistoriqueMultidoses)
        self.check_model('f_histo_prise', HistoriquePrise)
        self.check_model('f_message_astus', MessageAstus)
        self.check_model('f_liste_error', FListeError)
        self.check_model('f_log_info', LogInfo)
        self.check_model('f_cip_alias', GtinAlias)
        self.check_model('f_log_echange_riedl', LogEchangeRiedl)
        self.check_model('f_mag_format', MagasinFormat)
        self.check_model('f_mag_log_defaut', MagLogDefault)
        self.check_model('f_mag_groupe', MagasinGroupe)
        self.check_model('f_log_scan', LogScan)
        self.check_field('f_mag_log_defaut', 'x_message',
                         "ALTER TABLE f_mag_log_defaut ADD COLUMN x_message VARCHAR(100) DEFAULT '';")
        self.check_fields(
            "f_user",
            {
                "x_astup": "tinyint(1) DEFAULT '0'",
                "x_inventaire": "tinyint(1) DEFAULT '0'",
                "x_login": "VARCHAR(30) DEFAULT ''",
                "x_url": "VARCHAR(250) DEFAULT ''",
                "x_badge": "varchar(16) DEFAULT ''",
                "x_email": "VARCHAR(320) DEFAULT ''",
                "x_lang": "VARCHAR(10) DEFAULT ''",
                "x_import_flag": "TINYINT(1) NULL DEFAULT 0",
                "x_maintenance": "TINYINT(1) NULL DEFAULT 0",
                "x_retry": "INT(11) NULL DEFAULT 5",
                "x_passwd_web": "VARCHAR(320) DEFAULT ''",
                "x_is_enabled": "TINYINT(1) NULL DEFAULT 1",
                "x_is_temporary": "TINYINT(1) NULL DEFAULT 1",
                "x_ad_auth": "TINYINT(1) NULL DEFAULT 0",
                "x_texpire": "DATETIME DEFAULT '0000-00-00 00:00:00'",
                "x_token": "VARCHAR(320) DEFAULT NULL",
                "x_token_expires": "DATETIME DEFAULT NULL",
            },
        )

        self.check_field('f_profil', 'x_visu', "ALTER TABLE f_profil ADD COLUMN x_visu TINYINT(1) NULL DEFAULT 1;")
        self.check_field('f_profil', 'x_edit', "ALTER TABLE f_profil ADD COLUMN x_edit TINYINT(1) NULL DEFAULT 0;")
        self.check_field('f_profil', 'x_cree', "ALTER TABLE f_profil ADD COLUMN x_cree TINYINT(1) NULL DEFAULT 0;")
        self.check_field('f_profil', 'x_supp', "ALTER TABLE f_profil ADD COLUMN x_supp TINYINT(1) NULL DEFAULT 0;")
        self.check_model('f_domaine', ProductDomain)
        self.check_field('f_ref', 'x_com_med', "ALTER TABLE f_ref ADD COLUMN x_com_med VARCHAR(100) DEFAULT '';")
        self.check_field('f_ref', 'x_bac', "ALTER TABLE f_ref ADD COLUMN x_bac INT(11) NULL DEFAULT 0;")
        self.check_field('f_ref', 'x_stup', "ALTER TABLE f_ref ADD x_stup TINYINT(1) NOT NULL DEFAULT 0;")
        self.check_field('f_ref', 'x_risque', "ALTER TABLE f_ref ADD x_risque TINYINT(1) NOT NULL DEFAULT 0;")
        self.check_field('f_ref', 'x_warn_pct', "ALTER TABLE f_ref ADD x_warn_pct INT NOT NULL DEFAULT 20;")
        self.check_field('f_ref', 'x_anomalie', "ALTER TABLE f_ref ADD COLUMN x_anomalie VARCHAR(10) DEFAULT '';")
        self.check_field('f_ref', 'x_alpha_1', "ALTER TABLE f_ref ADD COLUMN x_alpha_1 VARCHAR(100) DEFAULT '';")
        self.check_field('f_ref', 'x_alpha_2', "ALTER TABLE f_ref ADD COLUMN x_alpha_2 VARCHAR(100) DEFAULT '';")
        self.check_field('f_ref', 'x_alpha_3', "ALTER TABLE f_ref ADD COLUMN x_alpha_3 VARCHAR(100) DEFAULT '';")
        self.check_field('f_ref', 'x_alpha_4', "ALTER TABLE f_ref ADD COLUMN x_alpha_4 VARCHAR(100) DEFAULT '';")
        self.check_field('f_ref', 'x_alpha_5', "ALTER TABLE f_ref ADD COLUMN x_alpha_5 VARCHAR(100) DEFAULT '';")
        self.check_field('f_ref', 'x_alpha_6', "ALTER TABLE f_ref ADD COLUMN x_alpha_6 VARCHAR(100) DEFAULT '';")
        self.check_field('f_ref', 'x_alpha_7', "ALTER TABLE f_ref ADD COLUMN x_alpha_7 VARCHAR(100) DEFAULT '';")
        self.check_field('f_ref', 'x_alpha_8', "ALTER TABLE f_ref ADD COLUMN x_alpha_8 VARCHAR(100) DEFAULT '';")
        self.check_field('f_ref', 'x_alpha_9', "ALTER TABLE f_ref ADD COLUMN x_alpha_9 VARCHAR(100) DEFAULT '';")
        self.check_field('f_ref', 'x_alpha_10', "ALTER TABLE f_ref ADD COLUMN x_alpha_10 VARCHAR(100) DEFAULT '';")
        self.check_field('f_ref', 'x_url', "ALTER TABLE f_ref ADD COLUMN x_url VARCHAR(1024) DEFAULT '';")
        self.check_field('f_ref', 'x_reap_mode', "ALTER TABLE f_ref ADD COLUMN x_reap_mode VARCHAR(1) DEFAULT 'T';")
        self.check_field('f_ref', 'x_multidose_par_uf',
                         "ALTER TABLE f_ref ADD COLUMN x_multidose_par_uf TINYINT(1) NOT NULL DEFAULT 0;")
        self.check_field('f_ref', 'x_conversion_ucd',
                         "ALTER TABLE f_ref ADD COLUMN x_conversion_ucd TINYINT(1) NOT NULL DEFAULT 0;")
        self.check_field('f_reform', 'x_ordre', "ALTER TABLE f_reform ADD COLUMN x_ordre INT(11) NULL DEFAULT 0;")

        self.check_field('f_ref_ucd', 'x_alpha_1_bis',
                         "ALTER TABLE f_ref_ucd ADD COLUMN x_alpha_1_bis VARCHAR(100) DEFAULT '';")
        self.check_field('f_ref_ucd', 'x_alpha_2_bis',
                         "ALTER TABLE f_ref_ucd ADD COLUMN x_alpha_2_bis VARCHAR(100) DEFAULT '';")
        self.check_field('f_ref_ucd', 'x_alpha_3_bis',
                         "ALTER TABLE f_ref_ucd ADD COLUMN x_alpha_3_bis VARCHAR(100) DEFAULT '';")
        self.check_field('f_ref_ucd', 'x_alpha_4_bis',
                         "ALTER TABLE f_ref_ucd ADD COLUMN x_alpha_4_bis VARCHAR(100) DEFAULT '';")
        self.check_field('f_ref_ucd', 'x_alpha_5_bis',
                         "ALTER TABLE f_ref_ucd ADD COLUMN x_alpha_5_bis VARCHAR(100) DEFAULT '';")
        self.check_field('f_ref_ucd', 'x_alpha_6_bis',
                         "ALTER TABLE f_ref_ucd ADD COLUMN x_alpha_6_bis VARCHAR(100) DEFAULT '';")
        self.check_field('f_ref_ucd', 'x_alpha_7_bis',
                         "ALTER TABLE f_ref_ucd ADD COLUMN x_alpha_7_bis VARCHAR(100) DEFAULT '';")
        self.check_field('f_ref_ucd', 'x_alpha_8_bis',
                         "ALTER TABLE f_ref_ucd ADD COLUMN x_alpha_8_bis VARCHAR(100) DEFAULT '';")
        self.check_field('f_ref_ucd', 'x_alpha_9_bis',
                         "ALTER TABLE f_ref_ucd ADD COLUMN x_alpha_9_bis VARCHAR(100) DEFAULT '';")
        self.check_field('f_ref_ucd', 'x_alpha_10_bis',
                         "ALTER TABLE f_ref_ucd ADD COLUMN x_alpha_10_bis VARCHAR(100) DEFAULT '';")

        self.check_field('f_ucd_cip', 'x_chrono',
                         "ALTER TABLE f_ucd_cip ADD COLUMN x_chrono DATETIME DEFAULT '0000-00-00 00:00:00';")
        self.check_field('f_ucd_cip', 'x_update',
                         "ALTER TABLE f_ucd_cip ADD COLUMN x_update DATETIME DEFAULT '0000-00-00 00:00:00';")
        self.check_field('f_ucd_cip', 'x_last_user',
                         "ALTER TABLE f_ucd_cip ADD COLUMN x_last_user VARCHAR(35) DEFAULT '';")
        self.check_field('f_ucd_cip', 'x_type_dpm',
                         "ALTER TABLE f_ucd_cip ADD COLUMN x_type_dpm VARCHAR(1) DEFAULT '';")
        self.check_field('f_mag', 'x_last_reap',
                         "ALTER TABLE f_mag ADD COLUMN x_last_reap DATETIME DEFAULT '0000-00-00 00:00:00';")
        self.check_field('f_mag', 'x_libelle',
                         "ALTER TABLE f_mag ADD COLUMN x_libelle VARCHAR(35) DEFAULT '';")
        self.check_field('f_mag', 'x_type_machine',
                         "ALTER TABLE f_mag ADD COLUMN x_type_machine VARCHAR(20) DEFAULT '';")
        self.check_field('f_mag', 'x_type_dpm',
                         "ALTER TABLE f_mag ADD COLUMN x_type_dpm VARCHAR(1) DEFAULT '';")
        self.check_field('f_mag', 'x_groupe',
                         "ALTER TABLE f_mag ADD COLUMN x_groupe VARCHAR(3) DEFAULT '';")
        self.check_field('f_mag', 'x_option_machine',
                         "ALTER TABLE f_mag ADD x_option_machine TINYINT(1) NOT NULL DEFAULT 0;")
        self.check_field('f_mag', 'x_compteur_coupe',
                         "ALTER TABLE f_mag ADD x_compteur_coupe TINYINT(1) NOT NULL DEFAULT 0;")
        self.check_field('f_mag', 'x_avatar',
                         "ALTER TABLE f_mag ADD COLUMN x_avatar VARCHAR(50) DEFAULT '';")
        self.check_field('f_mag', 'x_lettre',
                         "ALTER TABLE f_mag ADD COLUMN x_lettre VARCHAR(1) DEFAULT '-';")
        self.check_field('f_stock', 'x_qte_blister_bac',
                         "ALTER TABLE f_stock ADD COLUMN x_qte_blister_bac INT(11) NULL DEFAULT 0;")
        self.check_field('f_stock', 'x_nb_dose_cut',
                         "ALTER TABLE f_stock ADD COLUMN x_nb_dose_cut INT(11) NULL DEFAULT 0;")
        self.check_field('f_stock', 'x_version', "ALTER TABLE f_stock ADD COLUMN x_version INT(11) NULL DEFAULT 0;")
        self.check_field('f_stock', 'x_DU', "ALTER TABLE f_stock ADD COLUMN x_DU INT(11) NULL DEFAULT 0;")
        self.check_field('f_stock', 'x_qte_prod_blister',
                         "ALTER TABLE f_stock ADD COLUMN x_qte_prod_blister INT(11) NULL DEFAULT 0;")
        self.check_field('f_ref', 'x_tiroir_bas',
                         "ALTER TABLE f_ref ADD COLUMN x_tiroir_bas TINYINT(1) NULL DEFAULT 0;")
        self.check_field('f_adr', 'x_qte_posage',
                         "ALTER TABLE f_adr ADD COLUMN x_qte_posage INT(11) NULL DEFAULT 0;")
        self.check_field('f_adr', 'x_nouv_qte_posage',
                         "ALTER TABLE f_adr ADD COLUMN x_nouv_qte_posage INT(11) NULL DEFAULT 0;")
        self.check_field('f_adr', 'x_emplacement',
                         "ALTER TABLE f_adr ADD COLUMN x_emplacement VARCHAR(20) NULL DEFAULT '';")
        self.check_field('f_adr', 'x_nouv_emplacement',
                         "ALTER TABLE f_adr ADD COLUMN x_nouv_emplacement varchar(20) NULL DEFAULT '';")
        self.check_field('f_adr', 'x_stup',
                         "ALTER TABLE f_adr ADD COLUMN x_stup TINYINT(1) NULL DEFAULT 0;")

        self.check_field('f_adr', 'x_bloque_reappro',
                         "ALTER TABLE f_adr ADD COLUMN x_bloque_reappro TINYINT(1) NOT NULL DEFAULT 0;")
        self.check_field('f_adr', 'x_service',
                         "ALTER TABLE f_adr ADD COLUMN x_service TINYINT(1) NULL DEFAULT 0;")
        self.check_field('f_adr', 'x_bloque_msg',
                         "ALTER TABLE f_adr ADD COLUMN x_bloque_msg varchar(100) NULL DEFAULT '';")
        # Complete f_format table to implement volume for RIEDL, ASTUS, AIDE-Cut
        # These fields is for theorical dimensions
        self.check_field('f_format', 'x_dim_x',
                         "ALTER TABLE f_format ADD COLUMN x_dim_x INT(11) NULL DEFAULT 0;")
        self.check_field('f_format', 'x_dim_y',
                         "ALTER TABLE f_format ADD COLUMN x_dim_y INT(11) NULL DEFAULT 0 AFTER x_dim_x;")
        self.check_field('f_format', 'x_dim_z',
                         "ALTER TABLE f_format ADD COLUMN x_dim_z INT(11) NULL DEFAULT 0 AFTER x_dim_y;")
        # These fields is for real dimensions
        self.check_field('f_format', 'x_mm_x',
                         "ALTER TABLE f_format ADD COLUMN x_mm_x INT(11) NULL DEFAULT 0;")
        self.check_field('f_format', 'x_mm_y',
                         "ALTER TABLE f_format ADD COLUMN x_mm_y INT(11) NULL DEFAULT 0 AFTER x_mm_x;")
        self.check_field('f_format', 'x_mm_z',
                         "ALTER TABLE f_format ADD COLUMN x_mm_z INT(11) NULL DEFAULT 0 AFTER x_mm_y;")
        # use by ASTUS and RIEDL
        self.check_field('f_format', 'x_type_bac',
                         "ALTER TABLE f_format ADD COLUMN x_type_bac varchar(10) NULL DEFAULT '';")

        # Table f_dest for Carnet and Riedl Exit
        self.check_field('f_dest', 'x_type_carnet',
                         "ALTER TABLE f_dest ADD COLUMN x_type_carnet INT(11) NULL DEFAULT '1'")
        self.check_field('f_dest', 'x_secteur', "ALTER TABLE f_dest ADD COLUMN x_secteur VARCHAR(40) NULL DEFAULT ''")
        self.check_field('f_dest', 'x_adr1', "ALTER TABLE f_dest ADD COLUMN x_adr1 VARCHAR(40) NULL DEFAULT ''")
        self.check_field('f_dest', 'x_adr2', "ALTER TABLE f_dest ADD COLUMN x_adr2 VARCHAR(40) NULL DEFAULT ''")
        self.check_field('f_dest', 'x_adr3', "ALTER TABLE f_dest ADD COLUMN x_adr3 VARCHAR(40) NULL DEFAULT ''")
        self.check_field('f_dest', 'x_adr4', "ALTER TABLE f_dest ADD COLUMN x_adr4 VARCHAR(40) NULL DEFAULT ''")
        self.check_field('f_dest', 'x_tk_mode',
                         "ALTER TABLE f_dest ADD COLUMN x_tk_mode VARCHAR(2) NOT NULL DEFAULT 'NO'")
        self.check_field('f_dest', 'x_rdl_unload_prt_auto',
                         "ALTER TABLE f_dest ADD COLUMN x_rdl_unload_prt_auto TINYINT(1) NOT NULL DEFAULT 0;")
        self.check_field('f_dest', 'x_plus_completion',
                         "ALTER TABLE f_dest ADD COLUMN x_plus_completion TINYINT(1) NOT NULL DEFAULT 0;")
        self.check_model('f_ref_dest', ReferencePerService)
        self.check_model_index(ReferencePerService)
        self.check_field('f_ref_dest', 'x_dist_type',
                         "ALTER TABLE f_ref_dest ADD COLUMN x_dist_type VARCHAR(1) NULL DEFAULT 'N'")
        self.check_field('f_ref_dest', 'x_eco_type',
                         "ALTER TABLE f_ref_dest ADD COLUMN x_eco_type VARCHAR(1) NULL DEFAULT 'E'")
        self.check_field('f_peigne', 'x_libelle',
                         "ALTER TABLE f_peigne ADD COLUMN x_libelle VARCHAR(50) NULL DEFAULT '';")
        self.check_field('f_peigne', 'x_mode_type',
                         "ALTER TABLE f_peigne ADD COLUMN x_mode_type VARCHAR(1) NULL DEFAULT '';")
        self.check_field('f_mag', 'x_split_liste',
                         "ALTER TABLE f_mag ADD COLUMN x_split_liste TINYINT(1) NULL DEFAULT 0;")
        self.check_field('f_mag', 'x_libelle',
                         "ALTER TABLE f_mag ADD COLUMN x_libelle varchar(35) NOT NULL DEFAULT '';")
        self.check_fields(
            "f_poste",
            {
                "x_proc_pid": "INT(11) NULL DEFAULT 0",
                "x_session": "VARCHAR(40) NOT NULL DEFAULT ''",
                "x_code": "VARCHAR(64) NULL DEFAULT NULL UNIQUE",
            },
        )
        self.check_field('f_stock', 'x_capa', "ALTER TABLE f_stock ADD COLUMN x_capa INT(11) NULL DEFAULT 0;")
        self.check_field('f_stock', 'x_cycleVie', "ALTER TABLE f_stock ADD COLUMN x_cycleVie INT(11) DEFAULT 0;")
        self.check_field('f_stock', 'x_indexprise',
                         "ALTER TABLE f_stock ADD COLUMN x_indexprise SMALLINT(6) NULL DEFAULT 0;")
        self.check_field('f_stock', 'x_cip',
                         "ALTER TABLE f_stock ADD COLUMN x_cip varchar(13) NOT NULL DEFAULT '';")
        self.check_field('f_item', 'x_liste_orig',
                         "ALTER TABLE f_item ADD COLUMN x_liste_orig VARCHAR(60) NULL DEFAULT ''")
        self.check_field('f_item', 'x_posologie', "ALTER TABLE f_item ADD COLUMN x_posologie TEXT NULL DEFAULT ''")
        self.check_field('f_item', 'x_id_ucd',
                         "ALTER TABLE f_item ADD COLUMN x_id_ucd VARCHAR(20) NULL DEFAULT ''")
        self.check_field('f_item', 'x_cip',
                         "ALTER TABLE f_item ADD COLUMN x_cip VARCHAR(20) NULL DEFAULT ''")
        self.check_fields(
            "f_liste",
            {
                "x_sequence": "VARCHAR(25) NULL DEFAULT ''",
                "x_interface": "VARCHAR(15) NULL DEFAULT ''",
                "x_liste_orig": "VARCHAR(60) NULL DEFAULT ''",
                "x_num_face": "INT(11) NULL DEFAULT 0",
                "x_createur": "VARCHAR(20) NULL DEFAULT ''",
                'x_adr_carnet': "INT(11) NULL DEFAULT 0",
                'x_with_note': "TINYINT(1) DEFAULT '0'",
            },
        )
        self.check_field('f_item_w', 'x_cip',
                         "ALTER TABLE f_item_w ADD COLUMN x_cip VARCHAR(20) NULL DEFAULT ''")
        self.check_field('f_item_w', 'x_serial',
                         "ALTER TABLE f_item_w ADD COLUMN x_serial VARCHAR(20) NULL DEFAULT ''")
        self.check_field('f_item_w', 'x_capa',
                         "ALTER TABLE f_item_w ADD COLUMN x_capa INT(11) NULL DEFAULT 0;")
        self.check_field('f_liste_error', 'x_message',
                         "ALTER TABLE f_liste_error ADD COLUMN x_message TEXT NULL DEFAULT ''")
        self.check_field('f_ucd_cip', 'x_DU_boite_pass',
                         "ALTER TABLE f_ucd_cip ADD COLUMN `x_DU_boite_pass` TINYINT(1) NOT NULL DEFAULT 0;")
        self.check_field('f_histo_blocage', 'x_dfnum',
                         "ALTER TABLE f_histo_blocage ADD COLUMN x_dfnum SMALLINT(6) NULL DEFAULT 0;")
        # If pk not exists, we drop the table to recreate it
        self.check_field('f_liste_error', 'x_pk',
                         "DROP TABLE IF EXISTS f_liste_error;")
        self.check_field('f_defaut', 'x_manuel',
                         "ALTER TABLE f_defaut ADD COLUMN `x_manuel` TINYINT(1) NOT NULL DEFAULT 0;")
        self.check_field('f_defaut', 'x_color',
                         "ALTER TABLE f_defaut ADD x_color varchar(32) DEFAULT '' NOT NULL;")
        self.check_field('f_defaut', 'x_icon',
                         "ALTER TABLE f_defaut ADD x_icon varchar(32) DEFAULT '' NOT NULL;")
        self.check_field('f_histo_prise', 'x_info1',
                         "ALTER TABLE f_histo_prise ADD x_info1 varchar(50) DEFAULT '' NOT NULL;")

        self.check_model('f_liste_error', FListeError)
        self.check_model('f_gtin_dimension', GtinDimension)
        self.check_model('f_def_defaut', DefDefaut)
        self.check_model('f_log_defaut', LogDefaut)
        self.check_model('f_annuaire', LdapServer)
        self.check_field('f_annuaire', 'x_secure',
                         "ALTER TABLE f_annuaire ADD COLUMN `x_secure` TINYINT(1) NOT NULL DEFAULT 0;")
        self.check_field('f_annuaire', 'x_search_base',
                         "ALTER TABLE f_annuaire ADD COLUMN `x_search_base` VARCHAR(256) NULL;")
        self.check_model('f_user_ad', LdapServerProfil)
        self.check_field('f_user_ad', 'x_filter',
                         "ALTER TABLE f_user_ad ADD COLUMN `x_filter` VARCHAR(256) DEFAULT '' NOT NULL;")
        self.check_field('f_user_ad', 'x_subtree',
                         "ALTER TABLE f_user_ad ADD COLUMN `x_subtree` TINYINT(1) NOT NULL DEFAULT 0;")

        self.check_model('f_user', User)
        self.check_field('f_user', 'x_avatar',
                         "ALTER TABLE f_user ADD COLUMN `x_avatar` VARCHAR(50) NULL ")
        self.check_field('f_resrc', 'x_group',
                         "ALTER TABLE f_resrc ADD COLUMN `x_group` VARCHAR(75) NULL ")
        self.check_model('f_catalog_type', CatalogType)
        self.check_model('f_catalog_version', CatalogVersion)
        self.check_model('f_catalog_option', CatalogOption)
        self.check_field('f_mag', 'x_avatar',
                         "ALTER TABLE f_mag ADD COLUMN `x_avatar` VARCHAR(50) NULL ")
        self.check_model('f_catalog_unitstock', CatalogUnitStock)
        self.check_model('f_catalog_lines', CatalogLines)
        self.check_model('f_catalog_columns', CatalogColumns)
        self.check_field('f_catalog_version', 'sub_version',
                         "ALTER TABLE f_catalog_version ADD COLUMN sub_version VARCHAR(20) NULL DEFAULT ''")
        self.check_model('f_equipment_unitstock', EquipmentUnitStock)
        self.check_model('f_equipment_lines', EquipmentLines)
        self.check_model('f_equipment_columns', EquipmentColumns)
        self.check_model('f_liste_valide', ListeValide)
        self.check_model_index(ListeValide)
        self.check_fields(
            'f_liste_valide',
            {
                'x_chambre': "VARCHAR(30) NULL DEFAULT ''",
                'x_adr_carnet': "INT(11) NULL DEFAULT 0 ",
            }
        )
        self.check_model('f_item_valide', ItemValide)
        self.check_fields(
            'f_item_valide',
            {
                'x_serial_doublon': "VARCHAR(20) NULL DEFAULT '' ",
                'x_nom': "VARCHAR(35) NULL DEFAULT '' ",
                'x_prenom': "VARCHAR(35) NULL DEFAULT '' ",
                'x_nom_jf': "VARCHAR(35) NULL DEFAULT '' ",
                'x_num_ipp': "VARCHAR(35) NULL DEFAULT '' ",
                'x_sejour': "VARCHAR(35) NULL DEFAULT '' ",
                'x_dtnaiss': "DATETIME DEFAULT NULL ",
                'x_sexe': "VARCHAR(1) NULL DEFAULT '' ",
                'x_chambre': "VARCHAR(30) NULL DEFAULT '' ",
                'x_lit': "VARCHAR(30) NULL DEFAULT '' ",
                'x_id_presc': "INT(11) NULL DEFAULT 0 ",
                'x_info': "VARCHAR(80) NULL DEFAULT '' ",
                'x_pk_doublon':	"INT(11) NULL DEFAULT 0 ",
                'x_liste':	"VARCHAR(60) NULL DEFAULT '' ",
                'x_mode':	"VARCHAR(1) NULL DEFAULT '' ",
                'x_etat':	"VARCHAR(1) NULL DEFAULT '' ",
            }
        )
        self.check_model_index(LotRetire)
        self.check_model('f_histo', Historique)
        self.check_fields(
            'f_histo',
            {
                'xx_id_plateau': "VARCHAR(40) NULL DEFAULT '' ",
                'xx_no_pilulier': "INT(11) NULL DEFAULT 0 ",
                'xx_id_peigne': "INT(11) NULL DEFAULT 0 "
            })
        self.check_field('f_libelles', 'x_readonly',
                         "ALTER TABLE f_libelles ADD COLUMN `x_readonly` INT(11) NULL DEFAULT 1;")
        self.check_field('f_libelles', 'x_max_size',
                         "ALTER TABLE f_libelles ADD COLUMN `x_max_size` INT(11) NULL DEFAULT 1;")
        # self.check_model_index(Historique)
        self.check_model('f_sachet', UnitDose)
        self.check_model('f_wsclient_erreur', WsclientError)
        self.check_model_index(WsclientError)
        self.check_field('f_def_defaut', 'x_duration',
                         "ALTER TABLE f_def_defaut ADD COLUMN `x_duration` INT(11) NOT NULL DEFAULT 60;")
        self.check_model_index(DefDefaut)
        # Printer management
        self.check_model('f_printer_model', PrinterType)
        self.check_field('f_printer_model', 'x_density',
                         "ALTER TABLE f_printer_model ADD COLUMN `x_density` INT(11) NULL DEFAULT 0 ")
        self.check_field('f_printer_model', 'x_quality',
                         "ALTER TABLE f_printer_model ADD COLUMN `x_quality` VARCHAR(50) NULL DEFAULT '' ")
        self.check_field('f_printer_model', 'x_unit',
                         "ALTER TABLE f_printer_model ADD COLUMN `x_unit` VARCHAR(10) NULL DEFAULT '' ")
        self.check_model('f_printer_label', PrinterLabel)
        self.check_model('f_printer', Printer)
        self.check_model('f_mag_printer', PrinterMag)
        self.check_model('f_printer_cmd', PrinterCommand)
        self.check_model('f_printer_cmd_label', PrinterCommandLabel)
        self.check_model('f_log_printer', PrinterLog)
        self.check_field('f_printer_cmd_label', 'x_enabled',
                         "ALTER TABLE f_printer_cmd_label ADD COLUMN `x_enabled` INT(11) NOT NULL DEFAULT 1 ")
        self.check_field('f_printer_cmd_label', 'x_density',
                         "ALTER TABLE f_printer_cmd_label ADD COLUMN `x_density` INT(11) NOT NULL DEFAULT 300 ")
        self.check_field('f_printer_cmd_label', 'x_unit',
                         "ALTER TABLE f_printer_cmd_label ADD COLUMN `x_unit` VARCHAR(20) NOT NULL DEFAULT '' ")
        self.check_field('f_log_printer', 'x_label_id',
                         """ALTER TABLE f_log_printer ADD COLUMN `x_label_id` INT(11) NULL;
                         ALTER TABLE f_log_printer ADD CONSTRAINT f_log_printer_ibfk_3 FOREIGN KEY (x_label_id)
                         REFERENCES f_printer_label(x_pk) ON DELETE CASCADE ON UPDATE CASCADE;
                         """)
        self.check_field('f_log_printer', 'x_detail',
                         "ALTER TABLE f_log_printer ADD COLUMN `x_detail` VARCHAR(100) NOT NULL DEFAULT '' ")
        self.check_model('f_data_extract', DataExtract)

        # long index name for LogDefaut
        idx = (LogDefaut
               .index(LogDefaut.chrono,
                      LogDefaut.poste,
                      LogDefaut.num_defaut,
                      LogDefaut.valeur,
                      LogDefaut.message,
                      name='idx_f_log_defaut_all',
                      unique=False))
        try:
            self.db.execute(LogDefaut._schema._create_index(idx))
        except OperationalError:
            logger.warning("f_log_defaut : index  %s exists" % idx._name)

        # self.check_model_index(LogDefaut)
        self.check_model('f_dpm', Dpm)
        self.check_model('f_dpm_check', DpmCheck)
        self.check_model('f_dpm_action', DpmAction)
        self.check_model('f_dpm_comment', DpmComment)
        self.check_model_index(Dpm)
        self.check_model_index(DpmCheck)
        self.check_model_index(DpmAction)
        self.check_model_index(DpmComment)
        self.check_field('f_dpm', 'x_modification_date',
                         "ALTER TABLE f_dpm ADD COLUMN `x_modification_date` DATETIME DEFAULT NULL ")
        self.check_field('f_dpm', 'x_modification_pk',
                         "ALTER TABLE f_dpm ADD COLUMN `x_modification_pk` INT(11) NULL DEFAULT NULL ")
        self.check_field('f_dpm', 'x_ucd',
                         "ALTER TABLE f_dpm ADD COLUMN `x_ucd` VARCHAR(20) NULL DEFAULT '' ")
        self.check_field('f_dpm', 'x_qt_blister',
                         "ALTER TABLE f_dpm ADD COLUMN `x_qt_blister` INT(11) NULL DEFAULT 0")
        self.check_field('f_dpm', 'x_qt_boite',
                         "ALTER TABLE f_dpm ADD COLUMN `x_qt_boite` INT(11) NULL DEFAULT 0")
        self.check_field('f_dpm', 'x_qt_pass',
                         "ALTER TABLE f_dpm ADD COLUMN `x_qt_pass` INT(11) NULL DEFAULT 0")
        self.check_field('f_dpm', 'x_du_boite_pass',
                         "ALTER TABLE f_dpm ADD COLUMN `x_du_boite_pass` INT(11) NULL DEFAULT 0")
        self.check_model('f_non_conformity', NonConformity)
        self.check_model('f_nc_error_type', NonConformityErrorType)
        self.check_model('f_non_conformity_test', NonConformityTest)
        self.check_model('f_non_conformity_test_line', NonConformityTestLine)
        self.check_model('f_non_conformity_error', NonConformityError)

        NonConformityReportError = NonConformityReport.errors.get_through_model()
        self.check_model('f_nc_report', NonConformityReport)
        self.check_model('f_report_error', NonConformityReportError)

        self.check_model_index(NonConformityTestLine)
        self.check_model_index(HistoriqueSachet)

        self.check_field('f_def_defaut', 'x_duration',
                         "ALTER TABLE f_def_defaut ADD COLUMN `x_duration` INT(11) NOT NULL DEFAULT 60;")
        self.check_model_index(DefDefaut)
        # long index name for LogDefaut
        idx = (LogDefaut
               .index(LogDefaut.chrono,
                      LogDefaut.poste,
                      LogDefaut.num_defaut,
                      LogDefaut.valeur,
                      LogDefaut.message,
                      name='idx_f_log_defaut_all',
                      unique=False))
        try:
            self.db.execute(LogDefaut._schema._create_index(idx))
        except OperationalError:
            logger.warning("f_log_defaut : index  %s exists" % idx._name)

        self.add_service('DEENOVA', 'Service de test')
        self.add_global_ipp_episode()
        self.add_custom_label('ref_alpha_1', 'Champ libre 1', 1)
        self.add_custom_label('ref_alpha_2', 'Champ libre 2', 1)
        self.add_custom_label('ref_alpha_3', 'Champ libre 3', 1)
        self.add_custom_label('ref_alpha_4', 'Champ libre 4', 1)
        self.add_custom_label('ref_alpha_5', 'Champ libre 5', 1)
        self.add_custom_label('ref_alpha_6', 'Champ libre 6', 2)
        self.add_custom_label('ref_alpha_7', 'Champ libre 7', 2)
        self.add_custom_label('ref_alpha_8', 'Champ libre 8', 2)
        self.add_custom_label('ref_alpha_9', 'Champ libre 9', 2)
        self.add_custom_label('ref_alpha_10', 'Champ libre 10', 2)
        self.add_custom_label('ref_num_1', 'Numerique 1', 1)
        self.add_custom_label('ref_num_2', 'Numerique 2', 1)
        self.add_custom_label('ref_url', 'URL', 1, 1024)
        # Free fields on f_ref_ucd
        self.add_custom_label('ucd_alpha_1', 'Champ libre 1', 2)
        self.add_custom_label('ucd_alpha_2', 'Champ libre 2', 2)
        self.add_custom_label('ucd_alpha_3', 'Champ libre 3', 2)
        self.add_custom_label('ucd_alpha_4', 'Champ libre 4', 2)
        self.add_custom_label('ucd_alpha_5', 'Champ libre 5', 2)
        self.add_custom_label('ucd_alpha_6', 'Champ libre 6', 2)
        self.add_custom_label('ucd_alpha_7', 'Champ libre 7', 2)
        self.add_custom_label('ucd_alpha_8', 'Champ libre 8', 2)
        self.add_custom_label('ucd_alpha_9', 'Champ libre 9', 2)
        self.add_custom_label('ucd_alpha_10', 'Champ libre 10', 2)

        # TODO: Drop all view before restore (force mode ?)
        logger.info('Check views')
        self.check_sql_view('webview_taquin', WebviewTaquin)
        self.check_sql_view('webview_demi_taquin', WebviewDemiTaquin)
        self.check_sql_view('webview_emplacement', WebviewEmplacement)
        self.check_sql_view('webview_patient_histo', WebviewPatientHisto)
        logger.info('End check views')

    def check_model(self, table_name, model_class):
        """Check if table exists, and create it if missing"""
        try:
            if not self.db.table_exists(table_name):
                logger.info('Table %s does not exists, we create it from %s' % (table_name, model_class))
                self.db.create_tables([model_class])
            else:
                logger.info('Table %s exists' % (table_name,))
            return True

        except OperationalError as e:
            error_msg = 'Error creating table %s: %s' % (table_name, str(e))
            logger.error(error_msg)
            print('\033[91m' + error_msg + '\033[0m')
            return False
        except Exception as e:
            error_msg = 'Unexpected error checking/creating table %s: %s' % (table_name, str(e))
            logger.error(error_msg)
            print('\033[91m' + error_msg + '\033[0m')
            return False

    def check_model_index(self, model_class):
        """Check if index exists, creat it if missing"""
        unique_template = """ALTER TABLE %s ADD CONSTRAINT %s UNIQUE KEY (%s);"""
        index_template = """CREATE INDEX %s USING BTREE ON %s (%s);"""
        table_name = model_class._meta.table_name
        for idx in model_class._meta.fields_to_index():
            exp = idx._expressions
            fld_list = [x.column.name for x in exp]

            if idx._unique:
                ddl = unique_template % (table_name, "_".join(fld_list), ", ".join(fld_list))
            else:
                ddl = index_template % ("_".join(fld_list), table_name, ", ".join(fld_list))

            try:
                logger.info('Create index %s' % idx._name)
                self.db.execute_sql(ddl)
            except OperationalError:
                logger.info("index %s exists, do nothing" % idx._name)

    def check_sql_view(self, view_name, model_class):
        """Check if view exists, if not create it"""
        vws = [v.name for v in self.db.get_views()]
        if view_name not in vws:
            logger.info(' -> View %s is missing, we create it' % view_name)
            kls = model_class()
            kls.create_view()

    def drop_sql_view(self, view_name, model_class):
        """Check if view exists, if exists drop it"""
        vws = [v.name for v in self.db.get_views()]
        if view_name in vws:
            logger.info(' -> View %s exists, we delete it' % view_name)
            kls = model_class()
            kls.delete_view(True)

    def check_field(self, table_name, field_name, ddl_str=None):
        """check if the field exists"""
        try:
            field_found = False
            for col in self.db.get_columns(table_name):
                if col.name == field_name:
                    field_found = True
                    break

            if field_found:
                logger.info('Column %s exists in table %s' % (field_name, table_name))
            else:
                logger.warning('Column %s does not exists in table %s' % (field_name, table_name))
                if ddl_str is None:
                    return False
                try:
                    self.db.execute_sql(ddl_str)
                except OperationalError as e:
                    logger.error('Error executing DDL for field %s: %s' % (field_name, str(e)))
                    return False
                except Exception as e:
                    logger.error('Unexpected error executing DDL for field %s: %s' % (field_name, str(e)))
                    return False

            return True

        except OperationalError as e:
            logger.error('Error checking field %s in table %s: %s' % (field_name, table_name, str(e)))
            return False
        except Exception as e:
            logger.error('Unexpected error checking field %s in table %s: %s' % (field_name, table_name, str(e)))
            return False

    def check_fields(self, table_name, fields_data):
        '''Check if multiple fields exists, build only one query for multiple missing fields'''

        if type(fields_data) is not dict:
            logger.error('Error while using check_fieldS, a dict of field/ddl was expected: %s' % table_name)
            return False

        field_ddls = []
        for field_name in fields_data:
            field_exists = self.check_field(table_name, field_name)
            if not field_exists:
                field_ddl = fields_data[field_name]
                field_ddls.append(f"ADD COLUMN {field_name} {field_ddl}")

        if len(field_ddls) > 0:
            try:
                sql_statement = f"ALTER TABLE {table_name} {', '.join(field_ddls)}"
                logger.info(f"Executing multi-field update on {table_name}: {sql_statement}")
                print(f"Executing multi-field update on {table_name}")  # Blue text for info
                self.db.execute_sql(sql_statement)
            except OperationalError as e:
                error_msg = f"SQL error updating multiple fields in table {table_name}: {str(e)}"
                logger.error(error_msg)
                print(f"\033[91m{error_msg}\033[0m")  # Red text for errors
                return False
            except Exception as e:
                error_msg = f"Unexpected error updating multiple fields in table {table_name}: {str(e)}"
                logger.error(error_msg)
                print(f"\033[91m{error_msg}\033[0m")  # Red text for errors
                return False
        return True

    def delete_model(self, table_name, model_class):
        """Delete the table relate to the model"""
        if self.db.table_exists(table_name):
            self.db.drop_tables([model_class])
        else:
            logger.warning('Table %s not found, cannot delete it' % (table_name,))

    def delete_field(self, table_name, field_name):
        """Delete a field on a database"""
        query = "ALTER TABLE %s DROP COLUMN %s;" % (table_name, field_name)
        self.db.execute_sql(query)
        return True

    def add_service(self, wardcode, wardname):
        """Check if DEENOVA appear on f_dest"""
        try:
            Service.get(code=wardcode)
        except DoesNotExist:
            ser = Service()
            ser.code = wardcode
            ser.libelle = wardname
            ser.save()
        return True

    def add_custom_label(self, code: str, libelle: str, readonly: bool = 1, size: int = 100) -> None:
        Label.get_or_create(code=code, defaults={
            'libelle': libelle,
            'readonly': readonly,
            'max_size': size,
        })

    def add_global_ipp_episode(self):
        """
        If Global IPP and Episode not exists, we create it
        """
        try:
            Patient.get(ipp='GLOBAL')
        except DoesNotExist:
            logger.info('Global patient not exists, we create it')
            p = Patient()
            p.ipp = PatientGlobal.Ipp.value
            p.prenom = 'GL'
            p.nom = 'GL'
            p.sexe = 'H'
            p.save()

        try:
            Sejour.get(ipp='GLOBAL', sejour='GLOBAL')
        except DoesNotExist:
            logger.info('Global episode not exists, we create it')
            s = Sejour()
            s.ipp = PatientGlobal.Ipp.value
            s.sejour = PatientGlobal.Sejour.value
            s.uf_alit = '-'
            s.uf_hosp = '-'
            s.save()
        return True
