USE orford;

-- sequence
TRUNCATE TABLE `sequence`;
INSERT INTO `sequence` (`sequence_nomtable`, `sequence_dernierid`) VALUES 
('adresse',(SELECT MAX(`adresse_p_id`)+1 FROM `adresse`)),
('article',(SELECT MAX(`article_p_id`)+1 FROM `article`)),
('categorie_article',(SELECT MAX(`categorie_article_p_id`)+1 FROM `categorie_article`)),
('chambre',(SELECT MAX(`chambre_p_id`)+1 FROM `chambre`)),
('client',(SELECT MAX(`client_p_id`)+1 FROM `client`)),
('depot',(SELECT MAX(`depot_p_id`)+1 FROM `depot`)),
('employe',(SELECT MAX(`employe_p_id`)+1 FROM `employe`)),
('encaissement',(SELECT MAX(`encaissement_p_id`)+1 FROM `encaissement`)),
('facture',(SELECT MAX(`facture_p_id`)+1 FROM `facture`)),
('fermeture',(SELECT MAX(`fermeture_p_id`)+1 FROM `fermeture`)),
('groupe',(SELECT MAX(`groupe_p_id`)+1 FROM `groupe`)),
('groupe_utilisateur',(SELECT MAX(`groupe_utilisateur_p_id`)+1 FROM `groupe_utilisateur`)),
('location_chambre',(SELECT MAX(`location_chambre_p_fk_id`)+1 FROM `location_chambre`)),
('modedepaiement',(SELECT MAX(`modedepaiement_p_id`)+1 FROM `modedepaiement`)),
('reservation',(SELECT MAX(`reservation_p_id`)+1 FROM `reservation`)),
('reservation_groupe',(SELECT MAX(`reservation_groupe_p_fk_id`)+1 FROM `reservation_groupe`)),
('reservation_individu',(SELECT MAX(`reservation_individu_p_fk_id`)+1 FROM `reservation_individu`)),
('status_chambre',(SELECT MAX(`status_chambre_p_id`)+1 FROM `status_chambre`)),
('taxe',(SELECT MAX(`taxe_p_id`)+1 FROM `taxe`)),
('telephone',(SELECT MAX(`telephone_p_id`)+1 FROM `telephone`)),
('telephone_type',(SELECT MAX(`telephone_type_p_id`)+1 FROM `telephone_type`)),
('utilisateur',(SELECT MAX(`utilisateur_p_id`)+1 FROM `utilisateur`)),
('vente',(SELECT MAX(`vente_p_id`)+1 FROM `vente`)),
('vente_article',(SELECT MAX(`vente_article_p_fk_id`)+1 FROM `vente_article`)),
('ville',(SELECT MAX(`ville_p_id`)+1 FROM `ville`));

-- index
CREATE INDEX `index_telephone_id` ON `telephone`(`telephone_p_id`);
CREATE INDEX `index_telephone_fk_type` ON `telephone`(`telephone_type_fk_id`);
CREATE INDEX `index_telephone_numero` ON `telephone`(`telephone_numero`);
CREATE INDEX `index_reservation_id` ON `reservation`(`reservation_p_id`);
CREATE INDEX `index_reservation_fk_status` ON `reservation`(`status_fk_id`);
CREATE INDEX `index_reservation_fk_chambre` ON `reservation`(`chambre_fk_id`);
CREATE INDEX `index_reservation_groupe_id` ON `reservation_groupe`(`reservation_groupe_p_fk_id`);
CREATE INDEX `index_reservation_groupe_fk_groupe` ON `reservation_groupe`(`groupe_fk_id`);
CREATE INDEX `index_reservation_individu_id` ON `reservation_individu`(`reservation_individu_p_fk_id`);
CREATE INDEX `index_reservation_individu_fk_client` ON `reservation_individu`(`client_fk_id`);
CREATE INDEX `index_vente_id` ON `vente`(`vente_p_id`);
CREATE INDEX `index_vente_jour` ON `vente`(`vente_jour`);
CREATE INDEX `index_vente_fk_facture` ON `vente`(`facture_fk_id`);
CREATE INDEX `index_vente_fk_article` ON `vente`(`article_fk_id`);
CREATE INDEX `index_vente_article_id` ON `vente_article`(`vente_article_p_fk_id`);
CREATE INDEX `index_location_fk_chambre` ON `location_chambre`(`reservation_fk_id`);
CREATE INDEX `index_location_id` ON `location_chambre`(`location_chambre_p_fk_id`);
CREATE INDEX `index_groupe_id` ON `groupe`(`groupe_p_id`);
CREATE INDEX `index_groupe_nom` ON `groupe`(`groupe_nom`);
CREATE INDEX `index_groupe_fk_client` ON `groupe`(`client_fk_id`);
CREATE INDEX `index_fermeture_p_id` ON `fermeture`(`fermeture_p_id`);
CREATE INDEX `index_fermeture_jour` ON `fermeture`(`fermeture_jour`);
CREATE INDEX `index_fermeture_fk_employe` ON `fermeture`(`employe_fk_id`);
CREATE INDEX `index_fermeture_fk_depot` ON `fermeture`(`depot_fk_id`);
CREATE INDEX `index_facture_id` ON `facture`(`facture_p_id`);
CREATE INDEX `index_facture_jour` ON `facture`(`facture_jour`);
CREATE INDEX `index_facture_fk_client` ON `facture`(`client_fk_id`);
CREATE INDEX `index_facture_fk_fermeture` ON `facture`(`fermeture_fk_id`);
CREATE INDEX `index_encaissement_id` ON `encaissement`(`encaissement_p_id`);
CREATE INDEX `index_encaissement_fk_facture` ON `encaissement`(`facture_fk_id`);
CREATE INDEX `index_encaissement_fk_modedepaiement` ON `encaissement`(`modedepaiement_fk_id`);
CREATE INDEX `index_client_id` ON `client`(`client_p_id`);
CREATE INDEX `index_client_nom` ON `client`(`client_nom`);
CREATE INDEX `index_client_prenom` ON `client`(`client_prenom`);
CREATE INDEX `index_client_fk_adresse` ON `client`(`adresse_fk_id`);
CREATE INDEX `index_adresse_id` ON `adresse`(`adresse_p_id`);
CREATE INDEX `index_adresse_fk_ville` ON `adresse`(`ville_fk_id`);

insert into taxe values(15000,0,0,0,'1969-12-31');
insert into taxe values(15001,0.07,0,0,'1991-01-01');
insert into taxe values(15002,0.07,0.04,0,'1992-07-01');
insert into taxe values(15003,0.07,0.065,0,'1994-05-13');
insert into taxe values(15004,0.07,0.075,0,'1998-01-01');
insert into taxe values(15005,0.06,0.075,0,'2006-07-01');
insert into taxe values(15006,0.05,0.075,0,'2008-01-01');
insert into taxe values(15007,0.05,0.085,0,'2011-01-01');
insert into taxe values(15008,0.05,0.095,0,'2012-01-01');
insert into taxe values(15009,0.05,0.095,0,'2013-01-01');
update sequence set sequence_dernierid=15010 where sequence_nomtable='taxe';

update facture set taxe_fk_id=15009 where facture_jour >='2013-01-01' and taxe_fk_id<15000;
update facture set taxe_fk_id=15008 where facture_jour >='2012-01-01' and taxe_fk_id<15000;
update facture set taxe_fk_id=15007 where facture_jour >='2011-01-01' and taxe_fk_id<15000;
update facture set taxe_fk_id=15006 where facture_jour >='2008-01-01' and taxe_fk_id<15000;
update facture set taxe_fk_id=15005 where facture_jour >='2006-07-01' and taxe_fk_id<15000;
update facture set taxe_fk_id=15004 where facture_jour >='1998-01-01' and taxe_fk_id<15000;
update facture set taxe_fk_id=15003 where facture_jour >='1994-05-13' and taxe_fk_id<15000;
update facture set taxe_fk_id=15002 where facture_jour >='1992-07-01' and taxe_fk_id<15000;
update facture set taxe_fk_id=15001 where facture_jour >='1991-01-01' and taxe_fk_id<15000;
update facture set taxe_fk_id=15000 where facture_jour >='1969-12-31' and taxe_fk_id<15000;

delete from taxe where taxe_p_id<15000;

SET SQL_SAFE_UPDATES = 0;
update article set categorie_fk_id=5 where article_code='TAXE DHEBERGEMENT';
