SET FOREIGN_KEY_CHECKS = 0;

--
-- Structure de la table `financial_ville`
--
DROP TABLE IF EXISTS financial_ville;
CREATE TABLE IF NOT EXISTS `financial_ville` (
  `id_ville` int(10) unsigned zerofill NOT NULL auto_increment,
  `id_departement` int(10) unsigned zerofill NOT NULL,
  `cp` varchar(10) NOT NULL,
  `libelle` varchar(255) NOT NULL,
  `libelle_clean` varchar(255) NULL,
  `lat` decimal(8,5) NULL,
  `lng` decimal(8,5) NULL,
  PRIMARY KEY  (`id_ville`),
  FOREIGN KEY (`id_departement`) references financial_departement(id_departement) ON DELETE CASCADE)
 ENGINE=InnoDB AUTO_INCREMENT=000;

 
--
-- Contenu de la table `financial_ville`
--
INSERT INTO `financial_ville` (`id_ville`, `id_departement`, `cp`, `libelle`, `libelle_clean`, `lat`, `lng`) VALUES
(0000000001, 0000000001, '11111', 'ville1', 'ville1', 1, 1),
(0000000002, 0000000002, '22222', 'ville2', 'ville2', 1, 1),
(0000000003, 0000000003, '33333', 'ville3', 'ville3', 1, 1),
(0000000004, 0000000004, '44444', 'ville4', 'ville4', 1, 1),
(0000000005, 0000000005, '55555', 'ville4', 'ville5', 1, 1);
 

--
-- Structure de la table `financial_departement`
--
DROP TABLE IF EXISTS financial_departement;
CREATE TABLE IF NOT EXISTS `financial_departement` (
  `id_departement` int(10) unsigned zerofill NOT NULL auto_increment,
  `id_region` int(10) unsigned zerofill NOT NULL,
  `num_departement` varchar(15) NOT NULL,
  `libelle` varchar(255) NOT NULL,
  `libelle_clean` varchar(255) NULL,
  PRIMARY KEY  (`id_departement`),
  FOREIGN KEY (`id_region`) references financial_region(id_region) ON DELETE CASCADE)
 ENGINE=InnoDB AUTO_INCREMENT=000;

 --
-- Contenu de la table `financial_departement`
--
INSERT INTO `financial_departement` (`id_departement`, `id_region`, `num_departement`, `libelle`, `libelle_clean`) VALUES
(0000000001, 0000000001, '1', 'departement1', 'departement1'),
(0000000002, 0000000002, '2', 'departement2', 'departement2'),
(0000000003, 0000000003, '3', 'departement3', 'departement3'),
(0000000004, 0000000004, '4', 'departement4', 'departement4'),
(0000000005, 0000000005, '5', 'departement5', 'departement5');

--
-- Structure de la table `financial_region`
--
DROP TABLE IF EXISTS financial_region;
CREATE TABLE IF NOT EXISTS `financial_region` (
  `id_region` int(10) unsigned zerofill NOT NULL auto_increment,
  `id_pays` SMALLINT unsigned zerofill NOT NULL,
  `libelle` varchar(255) NOT NULL,
  `libelle_clean` varchar(255) NOT NULL,
  PRIMARY KEY  (`id_region`),
  FOREIGN KEY (`id_pays`) references financial_pays(id_pays) ON DELETE CASCADE)
  ENGINE=InnoDB AUTO_INCREMENT=000;
  
  
--
-- Contenu de la table `financial_region`
--
INSERT INTO `financial_region` (`id_region`, `id_pays`, `libelle`, `libelle_clean`) VALUES
(0000000001, 00000001, 'region1', 'region1'),
(0000000002, 00000001, 'region2', 'region2'),
(0000000003, 00000001, 'region3', 'region3'),
(0000000004, 00000001, 'region4', 'region4'),
(0000000005, 00000001, 'region5', 'region5');
  

--
-- Structure de la table `financial_zone`
--
DROP TABLE IF EXISTS financial_zone;
CREATE TABLE IF NOT EXISTS `financial_zone` (
  `id_local` smallint unsigned zerofill NOT NULL,
  `id_ville` mediumint unsigned zerofill NOT NULL,
   PRIMARY KEY ( `id_local` , `id_ville` ),
   FOREIGN KEY (`id_local`) REFERENCES financial_local(id_local) ON DELETE CASCADE)
   ENGINE=InnoDB;
  
 
--
-- Structure de la table `financial_gestion_bfi`
--
DROP TABLE IF EXISTS financial_gestion_bfi;
CREATE TABLE IF NOT EXISTS `financial_gestion_bfi` (
`id_local` smallint UNSIGNED zerofill NOT NULL,
`date_commande`date NOT NULL,
`bfi_commandes` smallint UNSIGNED zerofill NOT NULL,
`bfi_consommes` smallint UNSIGNED zerofill NOT NULL,
`bfi_anticipation` SMALLINT UNSIGNED NOT NULL,
`bfi_wait` SMALLINT UNSIGNED NOT NULL,
`bfi_jour` smallint UNSIGNED zerofill NOT NULL,
`bfi_max_jour` smallint UNSIGNED zerofill NOT NULL,
`bfi_semaine` smallint UNSIGNED zerofill NOT NULL,
`bfi_max_semaine` smallint UNSIGNED zerofill NOT NULL,
`jour_semaine` varchar(255),
PRIMARY KEY (`id_local`, `date_commande`),
FOREIGN KEY (`id_local`) references financial_local(id_local) ON DELETE CASCADE)
ENGINE = InnoDB AUTO_INCREMENT=000;

INSERT INTO financial_parametre(variable,valeur,type) VALUES('PACKBFI_MAX_ZONE','200','INT');
INSERT INTO financial_parametre(variable,valeur,type) VALUES('PACKBFI_PAS_ZONE','20','INT');
INSERT INTO financial_parametre(variable,valeur,type) VALUES('PACKBFI_PRIX_UNITAIRE_HT','25','FLOAT');
INSERT INTO financial_parametre(variable,valeur,type) VALUES('PACKBFI_URL_VALIDATION','v_simu','String');
INSERT INTO financial_parametre(variable,valeur,type) VALUES('PACKBFI_VALIDATION_TIMEOUT','60','INT');
INSERT INTO financial_parametre(variable,valeur,type) VALUES('PACKBFI_LOCAL_TEMP_VALIDATION','7','INT');
INSERT INTO financial_parametre(variable,valeur,type) VALUES('PACKBFI_URL_PARTENAIRE_TRANSFERT','http://financial.centralfinances.fr/Services/Service_Financial.php','String');
INSERT INTO financial_parametre(variable,valeur,type) VALUES('PACKBFI_CNF_PARTENAIRE_TRANSFERT','centralfinances.fr','String'),('PACKBFI_TRANSFERT_PARTENAIRE', 'false', 'Boolean'),('PACKBFI_LOCAL_DEFAULT', '1', 'Int');

INSERT INTO financial_parametre(id_local,variable,valeur,type) (SELECT id_local,'PACKBFI_QUOTA_ANTICIPATION','30','INT' from financial_local WHERE id_local = id_local_direction);
INSERT INTO financial_parametre(id_local,variable,valeur,type) (SELECT id_local,'PACKBFI_ANTICIPATION','false','BOOLEAN' from financial_local WHERE id_local = id_local_direction);


DROP TABLE IF EXISTS financial_projet_immo;
CREATE TABLE financial_projet_immo (
  id_projet_immo MEDIUMINT UNSIGNED ZEROFILL NOT NULL,
  type_operation ENUM('renegociation','credit_immobilier','autre'),
  objet_financement ENUM('residence_principale','residence_secondaire','investissement_locatif','murs_commerciaux','rachat_pret','autre') NULL,
  etat_residence ENUM('etat_ancien','etat_recent') NULL, 
  type_residence ENUM('maison','appartement') NULL,
  surface SMALLINT UNSIGNED NULL,
  nb_pieces TINYINT UNSIGNED NULL,
  performance_energetique ENUM('A','B','C','D','E','F','G') NULL,
  adresse1 VARCHAR(255) NULL,
  adresse2 VARCHAR(255) NULL,
  ville varchar(255) NULL,
  code_postal varchar(255) NULL,
  pays SMALLINT(5) UNSIGNED NULL,
  type_signature ENUM('signature_acte_vente','signature_livraison') NULL,
  date_signature date NULL,
  remarque MEDIUMINT UNSIGNED ZEROFILL NULL,
  PRIMARY KEY (id_projet_immo),
  FOREIGN KEY (id_projet_immo) REFERENCES financial_dossier(id_dossier) ON DELETE CASCADE,
  FOREIGN KEY (remarque) REFERENCES financial_remarque(id_remarque) ON DELETE SET NULL,
  FOREIGN KEY (pays) REFERENCES financial_pays(id_pays) ON DELETE SET NULL
) ENGINE=InnoDB;

DROP TABLE IF EXISTS financial_commissionnement;
CREATE TABLE financial_commissionnement(
id_commissionnement MEDIUMINT UNSIGNED ZEROFILL NOT NULL,
date_reception_hib DATE NULL,
date_facturation_surcom DATE NULL,
date_facturation_com_assurance_emprunteur DATE NULL,
date_facturation_com_assurance_coemprunteur DATE NULL,
date_saisie_bordereau DATE NULL,
montant_hib_percu DOUBLE UNSIGNED NULL,
commission_banque DOUBLE UNSIGNED NULL,
surcommission_banque DOUBLE UNSIGNED NULL,
commission_assurance_emprunteur DOUBLE UNSIGNED NULL,
commission_assurance_coemprunteur DOUBLE UNSIGNED NULL,
montant_retrocession_hib DOUBLE UNSIGNED NULL,
montant_retrocession_banque DOUBLE UNSIGNED NULL,
montant_retrocession_assurance_emprunteur DOUBLE UNSIGNED NULL,
montant_retrocession_assurance_coemprunteur DOUBLE UNSIGNED NULL,
id_remarque MEDIUMINT UNSIGNED ZEROFILL NULL,
PRIMARY KEY (id_commissionnement),
FOREIGN KEY (id_commissionnement) REFERENCES financial_dossier(id_dossier) ON DELETE CASCADE,
FOREIGN KEY (id_remarque) REFERENCES financial_remarque(id_remarque) ON DELETE SET NULL
)ENGINE=InnoDB AUTO_INCREMENT=000;
  
DROP TABLE IF EXISTS financial_commissionnement_facture;
CREATE TABLE financial_commissionnement_facture (
  id_facture MEDIUMINT(8) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
  id_commissionnement MEDIUMINT(8) unsigned zerofill NOT NULL,
  date_facture DATE DEFAULT NULL,
  montant double UNSIGNED NULL,
  montant_assurance double UNSIGNED NULL,
  numero_facture MEDIUMINT(8) UNSIGNED ZEROFILL DEFAULT NULL,
  id_remarque MEDIUMINT UNSIGNED ZEROFILL NULL,
  PRIMARY KEY (id_facture),
  FOREIGN KEY (id_commissionnement) REFERENCES financial_dossier(id_dossier) ON DELETE CASCADE,
  FOREIGN KEY (id_remarque) REFERENCES financial_remarque(id_remarque) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=000 ;
  