<?php
	// check this file's MD5 to make sure it wasn't called before
	$prevMD5=@implode('', @file(dirname(__FILE__).'/setup.md5'));
	$thisMD5=md5(@implode('', @file("./updateDB.php")));
	if($thisMD5==$prevMD5) {
		$setupAlreadyRun=true;
	}else{
		// set up tables
		if(!isset($silent)) {
			$silent=true;
		}

		// set up tables
		setupTable('main', "create table if not exists `main` (   `id` INT unsigned not null auto_increment , primary key (`id`), `main` VARCHAR(255) null ) CHARSET utf8", $silent);
		setupTable('clients', "create table if not exists `clients` (   `matricule_client` INT unsigned not null , primary key (`matricule_client`), `date_inscription` DATE null , `type` INT unsigned null , `nom_prenom` VARCHAR(255) not null , `nationalite` INT unsigned not null , `sexe` VARCHAR(255) not null , `date_naissance` DATE not null , `age` INT null , `nni` VARCHAR(255) not null , unique `nni_unique` (`nni`), `fonction` INT unsigned not null , `passport` VARCHAR(80) null , `lieu` INT unsigned null , `date_emis` DATE null , `date_exp` DATE null , `validite` VARCHAR(80) null , `adresse` VARCHAR(255) not null , `telephone` VARCHAR(255) not null , `email` VARCHAR(80) null , `banque` INT unsigned null , `compte` VARCHAR(255) null , `carte_fidelite` INT unsigned null , `numero_cf` VARCHAR(40) null , unique `numero_cf_unique` (`numero_cf`), `total_entree` DECIMAL(10,0) null default '0' , `total_sortie` DECIMAL(10,0) null default '0' , `solde` DECIMAL(10,0) null default '0' , `photo` VARCHAR(40) null , `signature` VARCHAR(40) null , `agence` INT unsigned null , `dossier` VARCHAR(255) null , `urgence` VARCHAR(255) null , `observation` TEXT null ) CHARSET utf8", $silent);
		setupIndexes('clients', array('type','nationalite','fonction','lieu','banque','carte_fidelite','agence'));
		setupTable('gestionnaires', "create table if not exists `gestionnaires` (   `matricule_gestionnaire` INT unsigned not null , primary key (`matricule_gestionnaire`), `date_recrutement` DATE null , `nom_prenom` VARCHAR(255) null , `telephone` VARCHAR(255) null , `email` VARCHAR(80) null , `adresse` VARCHAR(255) null , `fonction` INT unsigned null , `agence` INT unsigned null , `photo` VARCHAR(40) null , `dossier` VARCHAR(80) null , `observation` TEXT null ) CHARSET utf8", $silent);
		setupIndexes('gestionnaires', array('fonction','agence'));
		setupTable('partenaires', "create table if not exists `partenaires` (   `matricule_partenaire` INT unsigned not null , primary key (`matricule_partenaire`), `raison_sociale` INT unsigned null , `contact` VARCHAR(80) null , `fonction` INT unsigned null , `adresse` INT unsigned null , `telephone` INT unsigned null , `email` INT unsigned null , `commission` DECIMAL(10,0) null , `banque` INT unsigned null , `compte` INT unsigned null , `observation` TEXT null ) CHARSET utf8", $silent);
		setupIndexes('partenaires', array('raison_sociale'));
		setupTable('paiements', "create table if not exists `paiements` (   `idpaiement` INT(3) unsigned zerofill not null auto_increment , primary key (`idpaiement`), `code` VARCHAR(255) null , unique `code_unique` (`code`), `matricule` INT unsigned null , `nom_prenom` INT unsigned null , `banque` INT unsigned null , `compte` INT unsigned null , `type_paiement` INT unsigned null , `date_paiement` DATE null , `num_facture` VARCHAR(255) null , `montant` DECIMAL(10,0) null , `beneficiaire` INT unsigned null , `rs` INT unsigned null , `banque_beneficiaire` INT unsigned null , `compte_beneficiaire` INT unsigned null , `statut` VARCHAR(80) not null default 'En cours' , `observation` TEXT null ) CHARSET utf8", $silent);
		setupIndexes('paiements', array('matricule','type_paiement','beneficiaire'));
		setupTable('emetteurs', "create table if not exists `emetteurs` (   `idemetteur` INT unsigned not null auto_increment , primary key (`idemetteur`), `emetteur` INT unsigned null , `adresse` INT unsigned null , `email` INT unsigned null , `telephone` INT unsigned null , `observation` TEXT null ) CHARSET utf8", $silent);
		setupIndexes('emetteurs', array('emetteur'));
		setupTable('bieleteries', "create table if not exists `bieleteries` (   `idbieleterie` INT(3) unsigned zerofill not null auto_increment , primary key (`idbieleterie`), `link_tarif` VARCHAR(255) null , `num_facture` VARCHAR(255) null , unique `num_facture_unique` (`num_facture`), `date` DATE null , `client` INT unsigned null , `statut` VARCHAR(80) not null ) CHARSET utf8", $silent);
		setupIndexes('bieleteries', array('client'));
		setupTable('tarifs', "create table if not exists `tarifs` (   `idtarif` INT unsigned not null auto_increment , primary key (`idtarif`), `link_bieleterie` INT(3) unsigned zerofill null , `passager` INT unsigned null , `parcours` INT unsigned null , `compagnie` INT unsigned null , `provenance` INT unsigned null , `emetteur` INT unsigned null , `pnr` VARCHAR(255) null , `numero` VARCHAR(80) null , unique `numero_unique` (`numero`), `prix_achat` DECIMAL(10,0) null default '0' , `commission` DECIMAL(10,0) null default '0' , `frais_service` DECIMAL(10,0) null default '0' , `total` DECIMAL(10,0) null ) CHARSET utf8", $silent);
		setupIndexes('tarifs', array('link_bieleterie','passager','parcours','compagnie','provenance','emetteur'));
		setupTable('transferts_argent', "create table if not exists `transferts_argent` (   `idtransfert` INT(3) unsigned zerofill not null auto_increment , primary key (`idtransfert`), `num_facture` VARCHAR(255) null , unique `num_facture_unique` (`num_facture`), `code_transaction` INT null , unique `code_transaction_unique` (`code_transaction`), `date` DATE null , `emetteur` INT unsigned null , `nni_emetteur` INT unsigned null , `phone_emetteur` INT unsigned null , `adresse_emetteur` INT unsigned null , `beneficiaire` VARCHAR(255) null , `nni_beneficiaire` VARCHAR(255) null , `phone_beneficiaire` VARCHAR(255) null , `adresse_beneficiaire` VARCHAR(255) null , `prix_achat` DECIMAL(10,0) null default '0' , `commission` DECIMAL(10,0) null default '0' , `total` DECIMAL(10,0) null , `statut` VARCHAR(255) not null default 'En cours' , `observation` TEXT null ) CHARSET utf8", $silent);
		setupIndexes('transferts_argent', array('emetteur'));
		setupTable('colis', "create table if not exists `colis` (   `idcoli` INT(3) unsigned zerofill not null auto_increment , primary key (`idcoli`), `num_facture` VARCHAR(255) null , unique `num_facture_unique` (`num_facture`), `link_envoi_colis` VARCHAR(255) null , `code_transaction` VARCHAR(255) null , unique `code_transaction_unique` (`code_transaction`), `date_envoi` DATE null , `emetteur` INT unsigned null , `phone_emetteur` INT unsigned null ) CHARSET utf8", $silent);
		setupIndexes('colis', array('emetteur'));
		setupTable('envois_colis', "create table if not exists `envois_colis` (   `idenvois_coli` INT unsigned not null auto_increment , primary key (`idenvois_coli`), `link_colis` INT unsigned null , `description` VARCHAR(255) null , `destination` INT unsigned null , `agence` INT unsigned null , `ville` INT unsigned null , `prix_unitaire` DECIMAL(10,0) null default '0' , `qts` DECIMAL(10,0) null default '0' , `prix_total` DECIMAL(10,0) null default '0' , `beneficiaire` INT unsigned null , `telephone_beneficiaire` INT unsigned null , `nni_beneficiaire` INT unsigned null , `date_reception` DATE null , `statut` VARCHAR(255) not null , `observation` TEXT null ) CHARSET utf8", $silent);
		setupIndexes('envois_colis', array('link_colis','destination','agence','ville','beneficiaire'));
		setupTable('parc_auto', "create table if not exists `parc_auto` (   `idparc` INT unsigned not null auto_increment , primary key (`idparc`), `type` VARCHAR(255) null , `marque` INT unsigned null , `model` INT unsigned null , `immatriculation` VARCHAR(255) null , `places` VARCHAR(255) null , `observation` TEXT null ) CHARSET utf8", $silent);
		setupIndexes('parc_auto', array('marque'));
		setupTable('location_vh', "create table if not exists `location_vh` (   `idlocation` INT(3) unsigned zerofill not null auto_increment , primary key (`idlocation`), `link_liste` VARCHAR(255) null , `num_facture` VARCHAR(255) null , `date_saisie` DATE null , `client` INT unsigned null ) CHARSET utf8", $silent);
		setupIndexes('location_vh', array('client'));
		setupTable('liste_location', "create table if not exists `liste_location` (   `idlocations` INT(3) unsigned zerofill not null auto_increment , primary key (`idlocations`), `link_location` INT(3) unsigned zerofill null , `date_location` DATE null , `date_fin_location` DATE null , `vehicule` INT unsigned null , `model` INT unsigned null , `type` VARCHAR(255) null , `prix_unitaire` DECIMAL(10,0) null default '0' , `qts` DECIMAL(10,0) null default '0' , `prix_total` DECIMAL(10,0) null , `observation` VARCHAR(255) null ) CHARSET utf8", $silent);
		setupIndexes('liste_location', array('link_location','vehicule','model'));
		setupTable('fonctions', "create table if not exists `fonctions` (   `idfonction` INT unsigned not null auto_increment , primary key (`idfonction`), `fonction` VARCHAR(255) null , `observation` TEXT null ) CHARSET utf8", $silent);
		setupTable('agences', "create table if not exists `agences` (   `idagence` INT unsigned not null auto_increment , primary key (`idagence`), `agence` VARCHAR(255) null , `adresse` VARCHAR(255) null , `ville` INT unsigned null , `observation` TEXT null ) CHARSET utf8", $silent);
		setupIndexes('agences', array('ville'));
		setupTable('villes', "create table if not exists `villes` (   `idville` INT unsigned not null auto_increment , primary key (`idville`), `ville` VARCHAR(255) null , `observation` TEXT null ) CHARSET utf8", $silent);
		setupTable('banques', "create table if not exists `banques` (   `idbanque` INT unsigned not null auto_increment , primary key (`idbanque`), `banque` VARCHAR(255) null , `adresse` VARCHAR(255) null , `telephone` VARCHAR(255) null , `email` VARCHAR(80) null , `contact` VARCHAR(255) null , `observation` TEXT null ) CHARSET utf8", $silent);
		setupTable('type_paiements', "create table if not exists `type_paiements` (   `idtype_payment` INT unsigned not null auto_increment , primary key (`idtype_payment`), `type_paiement` VARCHAR(255) null , `observation` TEXT null ) CHARSET utf8", $silent);
		setupTable('compagnies', "create table if not exists `compagnies` (   `idcompagnie` INT unsigned not null auto_increment , primary key (`idcompagnie`), `compagnie` VARCHAR(255) null , `observation` TEXT null ) CHARSET utf8", $silent);
		setupTable('statuts', "create table if not exists `statuts` (   `idstatut` INT unsigned not null auto_increment , primary key (`idstatut`), `statut` VARCHAR(255) null ) CHARSET utf8", $silent);
		setupTable('visas', "create table if not exists `visas` (   `idvisa` INT unsigned not null auto_increment , primary key (`idvisa`), `date_depot` DATE null , `client` INT unsigned null , `num_passport` INT unsigned null , `type_visa` INT unsigned null , `emetteur` INT unsigned null , `prix_achat` DECIMAL(10,0) null default '0' , `commission` DECIMAL(10,0) null default '0' , `total` DECIMAL(10,0) null , `statut` VARCHAR(40) not null default 'En cours' , `dossier` VARCHAR(80) null , `obs` TEXT null ) CHARSET utf8", $silent);
		setupIndexes('visas', array('client','type_visa','emetteur'));
		setupTable('oumras', "create table if not exists `oumras` (   `idoumra` INT unsigned not null auto_increment , primary key (`idoumra`), `date` DATE null , `client` INT unsigned null , `adresse` INT unsigned null , `passeport` INT unsigned null , `date_depart` DATE null , `date_retour` DATE null , `compagnie` INT unsigned null , `billet` VARCHAR(80) null , `programme` INT unsigned null , `debut_visa` DATE null , `fin_visa` DATE null , `duree_visa` VARCHAR(80) null , `statut` VARCHAR(80) not null default 'En cours' , `dossier` VARCHAR(80) null , `obs` TEXT null ) CHARSET utf8", $silent);
		setupIndexes('oumras', array('client','compagnie','programme'));
		setupTable('assurances', "create table if not exists `assurances` (   `idassurance` INT unsigned not null auto_increment , primary key (`idassurance`), `date` DATE null , `nom_prenom` INT unsigned null , `intermediare` INT unsigned null , `pays_residence` INT unsigned null , `adresse` INT unsigned null , `telephone` INT unsigned null , `email` INT unsigned null , `destination` INT unsigned null , `formule` INT unsigned null , `certificat` VARCHAR(80) null , unique `certificat_unique` (`certificat`), `date_depart` DATE null , `date_retour` DATE null , `passport` INT unsigned null , `beneficiaire` VARCHAR(80) null , `nom_prenomb` INT unsigned null , `date_de_naissance` INT unsigned null , `dossier_pdf` VARCHAR(80) null , `dossier_zippe` VARCHAR(80) null , `statut` VARCHAR(80) not null default 'En cours' , `questionnaire_medical` VARCHAR(80) null , `question1` VARCHAR(80) null , `question2` VARCHAR(80) null , `question2a` VARCHAR(255) null , `question3` VARCHAR(80) null , `question4` VARCHAR(80) null , `question5` VARCHAR(80) null , `prime_total` VARCHAR(80) null ) CHARSET utf8", $silent, array( " ALTER TABLE `assurances` CHANGE `date_de_naissance` `date_de_naissance` VARCHAR(40) null "));
		setupIndexes('assurances', array('nom_prenom','intermediare','pays_residence','adresse','destination','formule','nom_prenomb'));
		setupTable('type_visas', "create table if not exists `type_visas` (   `idtype_visa` INT unsigned not null auto_increment , primary key (`idtype_visa`), `type_visa` VARCHAR(255) null ) CHARSET utf8", $silent);
		setupTable('facture', "create table if not exists `facture` (   `idfacture` INT(3) unsigned zerofill not null auto_increment , primary key (`idfacture`), `num_facture` VARCHAR(255) null , unique `num_facture_unique` (`num_facture`), `type` VARCHAR(255) not null , `date` DATE null , `client` INT unsigned null , `societe` INT unsigned null , `travaux` INT unsigned null , `statut` VARCHAR(80) not null default 'En cours' ) CHARSET utf8", $silent);
		setupIndexes('facture', array('client','travaux'));
		setupTable('detail_facture', "create table if not exists `detail_facture` (   `dfid` INT unsigned not null auto_increment , primary key (`dfid`), `factureid` INT unsigned null , `designation` INT unsigned null , `quantite` DECIMAL(10,0) null default '1' , `prixunitaire` DECIMAL(10,0) null default '0' , `total` DECIMAL(10,0) null ) CHARSET utf8", $silent);
		setupIndexes('detail_facture', array('factureid','designation'));
		setupTable('recettes', "create table if not exists `recettes` (   `idrecette` INT unsigned not null auto_increment , primary key (`idrecette`), `date` DATE null , `num_piece` VARCHAR(80) not null , unique `num_piece_unique` (`num_piece`), `agence` INT unsigned null , `service` INT unsigned null , `nature` INT unsigned null , `montant` DECIMAL(10,0) null default '0' , `obs` TEXT null ) CHARSET utf8", $silent);
		setupIndexes('recettes', array('agence','service','nature'));
		setupTable('depenses', "create table if not exists `depenses` (   `iddepense` INT unsigned not null auto_increment , primary key (`iddepense`), `date` DATE null , `nature` INT unsigned null , `justificatif` INT unsigned null , `agence` INT unsigned null , `service` INT unsigned null , `banque` INT unsigned null , `montant` DECIMAL(10,0) null default '0' , `compte` VARCHAR(80) null , `obs` TEXT null ) CHARSET utf8", $silent);
		setupIndexes('depenses', array('nature','justificatif','agence','service','banque'));
		setupTable('caisse', "create table if not exists `caisse` (   `idcaisse` INT unsigned not null auto_increment , primary key (`idcaisse`), `link_recettes` INT unsigned null , `link_depenses` INT unsigned null , `date` DATE null , `recettes` DECIMAL(10,0) null , `depenses` DECIMAL(10,0) null , `caisse` DECIMAL(10,0) null , `obs` TEXT null ) CHARSET utf8", $silent);
		setupIndexes('caisse', array('link_recettes','link_depenses'));
		setupTable('tresorerie_caisse', "create table if not exists `tresorerie_caisse` (   `idtresorerie_caisse` INT(7) unsigned zerofill not null auto_increment , primary key (`idtresorerie_caisse`), `date` DATE null , `journee_du` VARCHAR(255) null , `link_in` VARCHAR(255) null ) CHARSET utf8", $silent);
		setupTable('tresorerie_items', "create table if not exists `tresorerie_items` (   `idtresorerie_item` INT(7) unsigned zerofill not null auto_increment , primary key (`idtresorerie_item`), `link_out` INT(7) unsigned zerofill null , `justificatif` VARCHAR(255) null , `libelles` VARCHAR(255) null , `montant` DECIMAL(10,0) null default '0' , `commission_agence` DECIMAL(10,0) null , `net_compagnie` DECIMAL(10,0) null , `observations` TEXT null ) CHARSET utf8", $silent);
		setupIndexes('tresorerie_items', array('link_out'));
		setupTable('piece_reglements', "create table if not exists `piece_reglements` (   `idpiece` INT(7) unsigned zerofill not null auto_increment , primary key (`idpiece`), `date` DATE null , `beneficiaire` INT unsigned null , `operation` INT unsigned null , `montant` DECIMAL(10,0) null default '0' , `banque` INT unsigned null , `compte` INT unsigned null , `num_cheque` VARCHAR(255) not null , unique `num_cheque_unique` (`num_cheque`), `date_cheque` DATE not null , `imputation` VARCHAR(255) null , `statut` VARCHAR(80) not null default 'En cours' , `observations` TEXT null ) CHARSET utf8", $silent);
		setupIndexes('piece_reglements', array('beneficiaire','operation'));
		setupTable('autorisation_change', "create table if not exists `autorisation_change` (   `idautorisation` INT(7) unsigned zerofill not null auto_increment , primary key (`idautorisation`), `date` DATE null , `nom_prenom` INT unsigned null , `nationalite` INT unsigned null , `profession` INT unsigned null , `adresse` INT unsigned null , `num_compte` INT unsigned null , `nature_operation` INT unsigned null , `piece_justificative` VARCHAR(255) null , `nature_devises` INT unsigned null , `montant_chiffre` DECIMAL(10,0) null default '0' , `cours` FLOAT(10,0) null default '0' , `montant_fois_cours` DECIMAL(10,0) null , `commission` FLOAT(10,0) null default '0' , `contre_valeur` DECIMAL(10,0) null default '0' , `total` VARCHAR(255) null , `statut` VARCHAR(80) not null default 'En cours' ) CHARSET utf8", $silent);
		setupIndexes('autorisation_change', array('nom_prenom','nature_operation','nature_devises'));
		setupTable('bon_passage', "create table if not exists `bon_passage` (   `idbon` INT(7) unsigned zerofill not null auto_increment , primary key (`idbon`), `date` DATE null , `pnr` VARCHAR(255) null , `nous_prions` VARCHAR(255) null , `au_nom_de` VARCHAR(255) null , `parcours` VARCHAR(255) null , `depart` DATE null , `retour` DATE null , `total_ht` DECIMAL(10,0) null default '0' , `commissions` FLOAT(10,0) null default '0' , `total_ttc` DECIMAL(10,0) null , `frais_pta` DECIMAL(10,0) null , `net_payer` DECIMAL(10,0) null , `num_billet` VARCHAR(255) null , `statut` VARCHAR(80) not null default 'En cours' , `observations` TEXT null ) CHARSET utf8", $silent);
		setupTable('fiches_changes', "create table if not exists `fiches_changes` (   `idfichechange` INT(7) unsigned zerofill not null auto_increment , primary key (`idfichechange`), `date` DATE null , `journee_du` VARCHAR(255) null , `link_in` VARCHAR(255) null , `statut` VARCHAR(80) not null default 'En cours' ) CHARSET utf8", $silent);
		setupTable('fiches_changes_items', "create table if not exists `fiches_changes_items` (   `idfiche_change_item` INT(7) unsigned zerofill not null auto_increment , primary key (`idfiche_change_item`), `link_out` INT(7) unsigned zerofill null , `nom_prenom` INT unsigned not null , `num_identite` INT unsigned null , `montant` DECIMAL(10,0) null default '0' , `devises` INT unsigned null , `achats` DECIMAL(10,0) null default '0' , `ventes` DECIMAL(10,0) null default '0' , `observations` TEXT null ) CHARSET utf8", $silent);
		setupIndexes('fiches_changes_items', array('link_out','nom_prenom','devises'));
		setupTable('caisse_journaliere', "create table if not exists `caisse_journaliere` (   `idcaisse_journaliere` INT(7) unsigned zerofill not null auto_increment , primary key (`idcaisse_journaliere`), `date` DATE null , `link_in` VARCHAR(255) null ) CHARSET utf8", $silent);
		setupTable('item_caisse_journaliere', "create table if not exists `item_caisse_journaliere` (   `link_out` INT(7) unsigned zerofill null , `iditem_caisse_journaliere` INT unsigned not null auto_increment , primary key (`iditem_caisse_journaliere`), `justificatif` VARCHAR(255) null , `nom_prenom` VARCHAR(255) null , `destination` VARCHAR(255) null , `compagnie` VARCHAR(255) null , `prix_ht` DECIMAL(10,0) null default '0' , `commission` FLOAT(10,0) null default '0' , `prix_ttc` DECIMAL(10,0) null default '0' , `observations` VARCHAR(255) null ) CHARSET utf8", $silent);
		setupIndexes('item_caisse_journaliere', array('link_out'));
		setupTable('models', "create table if not exists `models` (   `idmodel` INT unsigned not null auto_increment , primary key (`idmodel`), `marque` VARCHAR(255) not null , `model` VARCHAR(255) not null ) CHARSET utf8", $silent);
		setupTable('natures', "create table if not exists `natures` (   `idnature` INT unsigned not null auto_increment , primary key (`idnature`), `nature` VARCHAR(80) null , `obs` TEXT null ) CHARSET utf8", $silent);
		setupTable('services', "create table if not exists `services` (   `idservice` INT unsigned not null auto_increment , primary key (`idservice`), `service` VARCHAR(80) null , `obs` TEXT null ) CHARSET utf8", $silent);
		setupTable('liens', "create table if not exists `liens` (   `id` INT unsigned not null auto_increment , primary key (`id`), `logo` VARCHAR(80) null , `nom` VARCHAR(80) null , `lien` VARCHAR(255) null ) CHARSET utf8", $silent);
		setupTable('types_client', "create table if not exists `types_client` (   `idtype_client` INT unsigned not null auto_increment , primary key (`idtype_client`), `type_client` VARCHAR(80) null , `obs` TEXT null ) CHARSET utf8", $silent);
		setupTable('cartes_fidelite', "create table if not exists `cartes_fidelite` (   `idcartecf` INT unsigned not null auto_increment , primary key (`idcartecf`), `carte_fidelite` VARCHAR(80) null , `obs` TEXT null ) CHARSET utf8", $silent);
		setupTable('designations', "create table if not exists `designations` (   `iddesignation` INT unsigned not null auto_increment , primary key (`iddesignation`), `designation` VARCHAR(80) null , `obs` TEXT null ) CHARSET utf8", $silent);
		setupTable('parcours', "create table if not exists `parcours` (   `idparcour` INT unsigned not null auto_increment , primary key (`idparcour`), `parcour` VARCHAR(80) null , `obs` TEXT null ) CHARSET utf8", $silent);
		setupTable('pays', "create table if not exists `pays` (   `idpays` INT unsigned not null auto_increment , primary key (`idpays`), `pays` VARCHAR(80) null , `obs` TEXT null ) CHARSET utf8", $silent);
		setupTable('provenance', "create table if not exists `provenance` (   `idprovenance` INT unsigned not null auto_increment , primary key (`idprovenance`), `provenance` VARCHAR(80) null , `obs` TEXT null ) CHARSET utf8", $silent);
		setupTable('programmes', "create table if not exists `programmes` (   `idprogramme` INT unsigned not null auto_increment , primary key (`idprogramme`), `programme` VARCHAR(80) null , `obs` VARCHAR(80) null ) CHARSET utf8", $silent);
		setupTable('justificatifs', "create table if not exists `justificatifs` (   `idjustificatif` INT unsigned not null auto_increment , primary key (`idjustificatif`), `justificatif` VARCHAR(80) null , `obs` TEXT null ) CHARSET utf8", $silent);
		setupTable('devise', "create table if not exists `devise` (   `iddevise` INT unsigned not null auto_increment , primary key (`iddevise`), `devise` VARCHAR(80) null ) CHARSET utf8", $silent);
		setupTable('intermediares', "create table if not exists `intermediares` (   `idintermediaire` INT unsigned not null auto_increment , primary key (`idintermediaire`), `intermediare` VARCHAR(80) null , `obs` TEXT null ) CHARSET utf8", $silent);
		setupTable('formule', "create table if not exists `formule` (   `idformule` INT unsigned not null auto_increment , primary key (`idformule`), `formule` VARCHAR(80) null , `obs` TEXT null ) CHARSET utf8", $silent);
		setupTable('operations', "create table if not exists `operations` (   `idoperation` INT unsigned not null auto_increment , primary key (`idoperation`), `operation` VARCHAR(80) null ) CHARSET utf8", $silent);
		setupTable('pointages', "create table if not exists `pointages` (   `idpointage` INT unsigned not null auto_increment , primary key (`idpointage`), `date` DATE null , `nom_prenom` INT unsigned null , `agence` INT unsigned null , `fonction` INT unsigned null , `arrivee` TIME null , `depart` TIME null , `obs` TEXT null ) CHARSET utf8", $silent);
		setupIndexes('pointages', array('nom_prenom'));
		setupTable('fichiers_partages', "create table if not exists `fichiers_partages` (   `idfichier_partage` INT unsigned not null auto_increment , primary key (`idfichier_partage`), `date` DATE null , `agent` INT unsigned null , `agence` INT unsigned null , `service` INT unsigned null , `nom_du_fichier` VARCHAR(80) null , `pdf` VARCHAR(80) null , `word` VARCHAR(80) null , `excel` VARCHAR(80) null , `powerpoint` VARCHAR(80) null , `compresse` VARCHAR(80) null , `note` TEXT null ) CHARSET utf8", $silent);
		setupIndexes('fichiers_partages', array('agent','service'));
		setupTable('note_de_service', "create table if not exists `note_de_service` (   `idnote_service` INT unsigned not null auto_increment , primary key (`idnote_service`), `date` DATE null , `agence` INT unsigned null , `service` INT unsigned null , `objet` VARCHAR(80) not null , `texte` TEXT not null , `ampliation` VARCHAR(80) not null ) CHARSET utf8", $silent);
		setupIndexes('note_de_service', array('agence','service'));


		// save MD5
		if($fp=@fopen(dirname(__FILE__).'/setup.md5', 'w')) {
			fwrite($fp, $thisMD5);
			fclose($fp);
		}
	}


	function setupIndexes($tableName, $arrFields) {
		if(!is_array($arrFields)) {
			return false;
		}

		foreach($arrFields as $fieldName) {
			if(!$res=@db_query("SHOW COLUMNS FROM `$tableName` like '$fieldName'")) {
				continue;
			}
			if(!$row=@db_fetch_assoc($res)) {
				continue;
			}
			if($row['Key']=='') {
				@db_query("ALTER TABLE `$tableName` ADD INDEX `$fieldName` (`$fieldName`)");
			}
		}
	}


	function setupTable($tableName, $createSQL='', $silent=true, $arrAlter='') {
		global $Translation;
		ob_start();

		echo '<div style="padding: 5px; border-bottom:solid 1px silver; font-family: verdana, arial; font-size: 10px;">';

		// is there a table rename query?
		if(is_array($arrAlter)) {
			$matches=array();
			if(preg_match("/ALTER TABLE `(.*)` RENAME `$tableName`/", $arrAlter[0], $matches)) {
				$oldTableName=$matches[1];
			}
		}

		if($res=@db_query("select count(1) from `$tableName`")) { // table already exists
			if($row = @db_fetch_array($res)) {
				echo str_replace("<TableName>", $tableName, str_replace("<NumRecords>", $row[0],$Translation["table exists"]));
				if(is_array($arrAlter)) {
					echo '<br>';
					foreach($arrAlter as $alter) {
						if($alter!='') {
							echo "$alter ... ";
							if(!@db_query($alter)) {
								echo '<span class="label label-danger">' . $Translation['failed'] . '</span>';
								echo '<div class="text-danger">' . $Translation['mysql said'] . ' ' . db_error(db_link()) . '</div>';
							}else{
								echo '<span class="label label-success">' . $Translation['ok'] . '</span>';
							}
						}
					}
				}else{
					echo $Translation["table uptodate"];
				}
			}else{
				echo str_replace("<TableName>", $tableName, $Translation["couldnt count"]);
			}
		}else{ // given tableName doesn't exist

			if($oldTableName!='') { // if we have a table rename query
				if($ro=@db_query("select count(1) from `$oldTableName`")) { // if old table exists, rename it.
					$renameQuery=array_shift($arrAlter); // get and remove rename query

					echo "$renameQuery ... ";
					if(!@db_query($renameQuery)) {
						echo '<span class="label label-danger">' . $Translation['failed'] . '</span>';
						echo '<div class="text-danger">' . $Translation['mysql said'] . ' ' . db_error(db_link()) . '</div>';
					}else{
						echo '<span class="label label-success">' . $Translation['ok'] . '</span>';
					}

					if(is_array($arrAlter)) setupTable($tableName, $createSQL, false, $arrAlter); // execute Alter queries on renamed table ...
				}else{ // if old tableName doesn't exist (nor the new one since we're here), then just create the table.
					setupTable($tableName, $createSQL, false); // no Alter queries passed ...
				}
			}else{ // tableName doesn't exist and no rename, so just create the table
				echo str_replace("<TableName>", $tableName, $Translation["creating table"]);
				if(!@db_query($createSQL)) {
					echo '<span class="label label-danger">' . $Translation['failed'] . '</span>';
					echo '<div class="text-danger">' . $Translation['mysql said'] . db_error(db_link()) . '</div>';
				}else{
					echo '<span class="label label-success">' . $Translation['ok'] . '</span>';
				}
			}
		}

		echo "</div>";

		$out=ob_get_contents();
		ob_end_clean();
		if(!$silent) {
			echo $out;
		}
	}
?>