DROP DATABASE lra_api;

CREATE DATABASE lra_api;

use lra_api;

# START

create table annexe
(
  id_annexe int auto_increment
    primary key,
  ordre int not null,
  titre varchar(255) not null,
  nomInitial varchar(255) not null,
  semaine int not null,
  nomFinal varchar(255) null,
  constraint annexe_nomFinal_uindex
  unique (nomFinal)
)
  engine=InnoDB
;

create table biotypologie
(
  id_biotypologie int auto_increment
    primary key,
  nom varchar(255) not null,
  actif tinyint(1) not null
)
  engine=InnoDB
;

create table commande
(
  id_commande int auto_increment
    primary key,
  id_pro int not null,
  id_credit int not null,
  etat int not null,
  date datetime not null,
  description text null,
  factureURL varchar(255) not null,
  constraint COMMANDE_factureURL_uindex
  unique (factureURL)
)
  engine=InnoDB
;

create index COMMANDE_professionnel_id_pro_fk
  on commande (id_pro)
;

create index COMMANDE_credit_id_credit_fk
  on commande (id_credit)
;

create table credit
(
  id_credit int auto_increment
    primary key,
  libelle varchar(255) not null,
  montant float not null,
  credit int not null
)
  engine=InnoDB
;

alter table commande
  add constraint COMMANDE_credit_id_credit_fk
foreign key (id_credit) references credit (id_credit)
;

create table divers
(
  id_divers int auto_increment
    primary key,
  clef varchar(255) not null,
  valeur varchar(255) not null
)
  engine=InnoDB
;

create table jour
(
  id_jour int auto_increment
    primary key,
  nom varchar(255) not null
)
  engine=InnoDB
;

create table menu
(
  id_menu int auto_increment
    primary key,
  nom varchar(255) not null,
  score int not null,
  priorite int not null,
  type int not null,
  compositions text not null,
  recette text null,
  actif tinyint(1) not null
)
  engine=InnoDB
;

create table menu_jour
(
  id_menu int not null,
  id_jour int not null,
  primary key (id_menu, id_jour),
  constraint MENU_JOUR_menu_id_menu_fk
  foreign key (id_menu) references menu (id_menu),
  constraint MENU_JOUR_jour_id_jour_fk
  foreign key (id_jour) references jour (id_jour)
)
  engine=InnoDB
;

create index MENU_JOUR_jour_id_jour_fk
  on menu_jour (id_jour)
;

create table menu_probleme
(
  id_menu int not null,
  id_probleme int not null,
  priorite int default '1' not null,
  primary key (id_menu, id_probleme),
  constraint MENU_PROBLEME_menu_id_menu_fk
  foreign key (id_menu) references menu (id_menu)
)
  engine=InnoDB
;

create index MENU_PROBLEME_probleme_id_probleme_fk
  on menu_probleme (id_probleme)
;

create table probleme
(
  id_probleme int auto_increment
    primary key,
  nom varchar(255) not null,
  actif tinyint(1) not null
)
  engine=InnoDB
;

alter table menu_probleme
  add constraint MENU_PROBLEME_probleme_id_probleme_fk
foreign key (id_probleme) references probleme (id_probleme)
;

create table professionnel
(
  id_pro int auto_increment
    primary key,
  email varchar(255) not null,
  password varchar(255) not null,
  etablissement varchar(255) not null,
  genre varchar(10) not null,
  nom varchar(255) not null,
  prenom varchar(255) not null,
  adresse varchar(255) not null,
  postal varchar(5) not null,
  ville varchar(255) not null,
  credit int not null,
  telephone varchar(255) not null,
  actif tinyint(1) not null,
  siret varchar(255) not null
)
  engine=InnoDB
;

alter table commande
  add constraint COMMANDE_professionnel_id_pro_fk
foreign key (id_pro) references professionnel (id_pro)
;

create table prog_menu
(
  id_menu int not null,
  id_programme int not null,
  ordre int not null,
  primary key (id_menu, id_programme, ordre),
  constraint PROG_MENU_menu_id_menu_fk
  foreign key (id_menu) references menu (id_menu)
)
  engine=InnoDB
;

create index PROG_MENU_programme_id_programme_fk
  on prog_menu (id_programme)
;

create table programme
(
  id_programme int auto_increment
    primary key,
  id_pro int not null,
  id_probleme1 int not null,
  id_probleme2 int not null,
  id_biotypologie int not null,
  date datetime not null,
  genre varchar(255) not null,
  email varchar(255) null,
  nom varchar(255) not null,
  prenom varchar(255) not null,
  age int not null,
  taille int not null,
  duree int not null,
  poids int not null,
  nomFichier varchar(255) not null,
  constraint PROGRAMME_professionnel_id_pro_fk
  foreign key (id_pro) references professionnel (id_pro),
  constraint PROGRAMME_probleme1_id_probleme_fk
  foreign key (id_probleme1) references probleme (id_probleme),
  constraint PROGRAMME_probleme2_id_probleme_fk
  foreign key (id_probleme2) references probleme (id_probleme),
  constraint programme_biotypologie_id_biotypologie_fk
  foreign key (id_biotypologie) references biotypologie (id_biotypologie)
)
  engine=InnoDB
;

create index PROGRAMME_professionnel_id_pro_fk
  on programme (id_pro)
;

create index PROGRAMME_probleme1_id_probleme_fk
  on programme (id_probleme1)
;

create index PROGRAMME_probleme2_id_probleme_fk
  on programme (id_probleme2)
;

create index programme_biotypologie_id_biotypologie_fk
  on programme (id_biotypologie)
;

create table reinit
(
  id_reinit int auto_increment
    primary key,
  id_pro int not null,
  hash varchar(255) not null,
  peremption datetime not null,
  constraint REINIT_professionnel_id_pro_fk
  foreign key (id_pro) references professionnel (id_pro)
)
  engine=InnoDB
;

create index REINIT_professionnel_id_pro_fk
  on reinit (id_pro)
;

create table tokens
(
  id_token int auto_increment
    primary key,
  value varchar(255) not null,
  id_pro int null,
  expiration datetime null,
  admin tinyint(1) default '0' not null,
  constraint tokens_professionnel_id_pro_fk
  foreign key (id_pro) references professionnel (id_pro)
)
  engine=InnoDB
;

create index tokens_professionnel_id_pro_fk
  on tokens (id_pro)
;

INSERT INTO lra_api.annexe (ordre, titre, nomInitial, semaine, nomFinal)
VALUES (1, 'Résumé information clientèle', '', -1, NULL);
INSERT INTO lra_api.annexe (ordre, titre, nomInitial, semaine, nomFinal)
VALUES (2, '1ere semaine : détox', '', 1, NULL);
INSERT INTO lra_api.annexe (ordre, titre, nomInitial, semaine, nomFinal) VALUES (3, '2eme semaine', '', 2, NULL);
INSERT INTO lra_api.annexe (ordre, titre, nomInitial, semaine, nomFinal) VALUES (4, '3eme semaine', '', 3, NULL);
INSERT INTO lra_api.annexe (ordre, titre, nomInitial, semaine, nomFinal) VALUES (5, '4eme semaine', '', 4, NULL);

INSERT INTO lra_api.probleme (nom, actif) VALUES ('Surcharge globale', 1);
INSERT INTO lra_api.probleme (nom, actif) VALUES ('Surcharge androïde', 1);
INSERT INTO lra_api.probleme (nom, actif) VALUES ('Cellulite gynoïde', 1);
INSERT INTO lra_api.probleme (nom, actif) VALUES ('Cellulite métabolique', 1);
INSERT INTO lra_api.probleme (nom, actif) VALUES ('Cellulite fibreuse', 1);
INSERT INTO lra_api.probleme (nom, actif) VALUES ('Relâchement cutané', 1);
INSERT INTO lra_api.probleme (nom, actif) VALUES ('Peau grasse / acné', 1);
INSERT INTO lra_api.probleme (nom, actif) VALUES ('Rides', 1);

INSERT INTO lra_api.credit (libelle, montant, credit) VALUES ('10 crédits', 10, 10);
INSERT INTO lra_api.credit (libelle, montant, credit) VALUES ('20 crédits (-10%)', 19, 20);
INSERT INTO lra_api.credit (libelle, montant, credit) VALUES ('30 crédits (-30%)', 17, 30);

INSERT INTO lra_api.divers (clef, valeur) VALUES ('admin_id', 'admin');
INSERT INTO lra_api.divers (clef, valeur) VALUES ('admin_pass', 'admin');
INSERT INTO lra_api.divers (clef, valeur) VALUES ('PDF_path', 'C:\\xampp\\htdocs\\lra_api\\public\\pdf');
INSERT INTO lra_api.divers (clef, valeur) VALUES ('PDF_URL', '/programme');
INSERT INTO lra_api.divers (clef, valeur) VALUES ('facture_path', '/temp/facture');
INSERT INTO lra_api.divers (clef, valeur) VALUES ('facture_URL', '/facture');
INSERT INTO lra_api.divers (clef, valeur) VALUES ('annexe_path', 'C:\\xampp\\htdocs\\lra_api\\public\\annexe');
INSERT INTO lra_api.divers (clef, valeur) VALUES ('annexe_URL', '/annexe');
INSERT INTO lra_api.divers (clef, valeur) VALUES ('migrations', '1');
INSERT INTO lra_api.divers (clef, valeur) VALUES ('mailer_smtpDebug', '2');
INSERT INTO lra_api.divers (clef, valeur) VALUES ('mailer_host', 'smtp.infomaniak.com');
INSERT INTO lra_api.divers (clef, valeur) VALUES ('mailer_smptAuth', 'true');
INSERT INTO lra_api.divers (clef, valeur) VALUES ('mailer_username', 'facteur@nextsourcia.com');
INSERT INTO lra_api.divers (clef, valeur) VALUES ('mailer_password', 'aqw12ZSX');
INSERT INTO lra_api.divers (clef, valeur) VALUES ('mailer_smtpSecure', 'tls');
INSERT INTO lra_api.divers (clef, valeur) VALUES ('mailer_port', '587');
INSERT INTO lra_api.divers (clef, valeur) VALUES ('mailer_senderEmail', 'facteur@nextsourcia.com');
INSERT INTO lra_api.divers (clef, valeur) VALUES ('mailer_senderName', 'Facteur nextSourcia');
INSERT INTO lra_api.divers (clef, valeur) VALUES ('website_URL', 'http://localhost:3000');
INSERT INTO lra_api.divers (clef, valeur) VALUES ('api_URL', 'http://localhost');
INSERT INTO lra_api.divers (clef, valeur) VALUES ('mailTemplate_path', 'C:\\xampp\\htdocs\\lra_api\\public\\templates');
INSERT INTO lra_api.divers (clef, valeur) VALUES ('proforma_email', 'comptable@lucia-rapetti.com');

INSERT INTO lra_api.jour (nom) VALUES ('Lundi');
INSERT INTO lra_api.jour (nom) VALUES ('Mardi');
INSERT INTO lra_api.jour (nom) VALUES ('Mercredi');
INSERT INTO lra_api.jour (nom) VALUES ('Jeudi');
INSERT INTO lra_api.jour (nom) VALUES ('Vendredi');
INSERT INTO lra_api.jour (nom) VALUES ('Samedi');

INSERT INTO lra_api.biotypologie (nom, actif) VALUES ('Lymphatique', 1);
INSERT INTO lra_api.biotypologie (nom, actif) VALUES ('Sanguine', 1);
INSERT INTO lra_api.biotypologie (nom, actif) VALUES ('Bilieuse', 1);
INSERT INTO lra_api.biotypologie (nom, actif) VALUES ('Nerveuse', 1);
