create table CLIENTS (
        ID bigint not null auto_increment,
        VERSION integer not null,
        TITRE varchar(5) not null,
        NOM varchar(30) not null,
        PRENOM varchar(30) not null,
        primary key (ID)
    ) ENGINE=InnoDB;

    create table CRENEAUX (
        ID bigint not null auto_increment,
        VERSION integer not null,
        HDEBUT integer not null,
        MDEBUT integer not null,
        HFIN integer not null,
        MFIN integer not null,
        ID_MEDECIN bigint not null,
        primary key (ID)
    ) ENGINE=InnoDB;

    create table MEDECINS (
        ID bigint not null auto_increment,
        VERSION integer not null,
        TITRE varchar(5) not null,
        NOM varchar(30) not null,
        PRENOM varchar(30) not null,
        primary key (ID)
    ) ENGINE=InnoDB;

    create table RV (
        ID bigint not null auto_increment,
        JOUR date not null,
        ID_CLIENT bigint not null,
        ID_CRENEAU bigint not null,
        primary key (ID)
    ) ENGINE=InnoDB;

    alter table CRENEAUX 
        add index FK9BD7A197FE16862 (ID_MEDECIN), 
        add constraint FK9BD7A197FE16862 
        foreign key (ID_MEDECIN) 
        references MEDECINS (ID);

    alter table RV 
        add index FKA4494D97AD2 (ID_CLIENT), 
        add constraint FKA4494D97AD2 
        foreign key (ID_CLIENT) 
        references CLIENTS (ID);

    alter table RV 
        add index FKA441A673246 (ID_CRENEAU), 
        add constraint FKA441A673246 
        foreign key (ID_CRENEAU) 
        references CRENEAUX (ID);

INSERT INTO CLIENTS ( VERSION, NOM, PRENOM, TITRE) VALUES (1, 'MARTIN', 'Jules', 'Mr');
INSERT INTO CLIENTS ( VERSION, NOM, PRENOM, TITRE) VALUES (1, 'GERMAN', 'Christine', 'Mme');
INSERT INTO CLIENTS ( VERSION, NOM, PRENOM, TITRE) VALUES (1, 'JACQUARD', 'Jules', 'Mr');
INSERT INTO CLIENTS ( VERSION, NOM, PRENOM, TITRE) VALUES (1, 'BISTROU', 'Brigitte', 'Melle');

COMMIT WORK;

INSERT INTO MEDECINS ( VERSION, NOM, PRENOM, TITRE) VALUES (1, 'PELISSIER', 'Marie', 'Mme');
INSERT INTO MEDECINS ( VERSION, NOM, PRENOM, TITRE) VALUES (1, 'BROMARD', 'Jacques', 'Mr');
INSERT INTO MEDECINS ( VERSION, NOM, PRENOM, TITRE) VALUES (1, 'JANDOT', 'Philippe', 'Mr');
INSERT INTO MEDECINS ( VERSION, NOM, PRENOM, TITRE) VALUES (1, 'JACQUEMOT', 'Justine', 'Melle');

COMMIT WORK;

INSERT INTO CRENEAUX ( VERSION, ID_MEDECIN, HDEBUT, MDEBUT, HFIN, MFIN) VALUES (1, 1, 8, 0, 8, 20);
INSERT INTO CRENEAUX ( VERSION, ID_MEDECIN, HDEBUT, MDEBUT, HFIN, MFIN) VALUES (1, 1, 8, 20, 8, 40);
INSERT INTO CRENEAUX ( VERSION, ID_MEDECIN, HDEBUT, MDEBUT, HFIN, MFIN) VALUES (1, 1, 8, 40, 9, 0);
INSERT INTO CRENEAUX ( VERSION, ID_MEDECIN, HDEBUT, MDEBUT, HFIN, MFIN) VALUES (1, 1, 9, 0, 9, 20);
INSERT INTO CRENEAUX ( VERSION, ID_MEDECIN, HDEBUT, MDEBUT, HFIN, MFIN) VALUES (1, 1, 9, 20, 9, 40);
INSERT INTO CRENEAUX ( VERSION, ID_MEDECIN, HDEBUT, MDEBUT, HFIN, MFIN) VALUES (1, 1, 9, 40, 10, 0);
INSERT INTO CRENEAUX ( VERSION, ID_MEDECIN, HDEBUT, MDEBUT, HFIN, MFIN) VALUES (1, 1, 10, 0, 10, 20);
INSERT INTO CRENEAUX ( VERSION, ID_MEDECIN, HDEBUT, MDEBUT, HFIN, MFIN) VALUES (1, 1, 10, 20, 10, 40);
INSERT INTO CRENEAUX ( VERSION, ID_MEDECIN, HDEBUT, MDEBUT, HFIN, MFIN) VALUES (1, 1, 10, 40, 11, 0);
INSERT INTO CRENEAUX ( VERSION, ID_MEDECIN, HDEBUT, MDEBUT, HFIN, MFIN) VALUES ( 1, 1, 11, 0, 11, 20);
INSERT INTO CRENEAUX ( VERSION, ID_MEDECIN, HDEBUT, MDEBUT, HFIN, MFIN) VALUES ( 1, 1, 11, 20, 11, 40);
INSERT INTO CRENEAUX ( VERSION, ID_MEDECIN, HDEBUT, MDEBUT, HFIN, MFIN) VALUES ( 1, 1, 11, 40, 12, 0);
INSERT INTO CRENEAUX ( VERSION, ID_MEDECIN, HDEBUT, MDEBUT, HFIN, MFIN) VALUES ( 1, 1, 14, 0, 14, 20);
INSERT INTO CRENEAUX ( VERSION, ID_MEDECIN, HDEBUT, MDEBUT, HFIN, MFIN) VALUES (1, 1, 14, 20, 14, 40);
INSERT INTO CRENEAUX ( VERSION, ID_MEDECIN, HDEBUT, MDEBUT, HFIN, MFIN) VALUES ( 1, 1, 14, 40, 15, 0);
INSERT INTO CRENEAUX ( VERSION, ID_MEDECIN, HDEBUT, MDEBUT, HFIN, MFIN) VALUES ( 1, 1, 15, 0, 15, 20);
INSERT INTO CRENEAUX ( VERSION, ID_MEDECIN, HDEBUT, MDEBUT, HFIN, MFIN) VALUES ( 1, 1, 15, 20, 15, 40);
INSERT INTO CRENEAUX ( VERSION, ID_MEDECIN, HDEBUT, MDEBUT, HFIN, MFIN) VALUES ( 1, 1, 15, 40, 16, 0);
INSERT INTO CRENEAUX ( VERSION, ID_MEDECIN, HDEBUT, MDEBUT, HFIN, MFIN) VALUES ( 1, 1, 16, 0, 16, 20);
INSERT INTO CRENEAUX ( VERSION, ID_MEDECIN, HDEBUT, MDEBUT, HFIN, MFIN) VALUES ( 1, 1, 16, 20, 16, 40);
INSERT INTO CRENEAUX ( VERSION, ID_MEDECIN, HDEBUT, MDEBUT, HFIN, MFIN) VALUES ( 1, 1, 16, 40, 17, 0);
INSERT INTO CRENEAUX ( VERSION, ID_MEDECIN, HDEBUT, MDEBUT, HFIN, MFIN) VALUES ( 1, 1, 17, 0, 17, 20);
INSERT INTO CRENEAUX ( VERSION, ID_MEDECIN, HDEBUT, MDEBUT, HFIN, MFIN) VALUES ( 1, 1, 17, 20, 17, 40);
INSERT INTO CRENEAUX ( VERSION, ID_MEDECIN, HDEBUT, MDEBUT, HFIN, MFIN) VALUES ( 1, 1, 17, 40, 18, 0);
INSERT INTO CRENEAUX ( VERSION, ID_MEDECIN, HDEBUT, MDEBUT, HFIN, MFIN) VALUES ( 1, 2, 8, 0, 8, 20);
INSERT INTO CRENEAUX ( VERSION, ID_MEDECIN, HDEBUT, MDEBUT, HFIN, MFIN) VALUES ( 1, 2, 8, 20, 8, 40);
INSERT INTO CRENEAUX ( VERSION, ID_MEDECIN, HDEBUT, MDEBUT, HFIN, MFIN) VALUES ( 1, 2, 8, 40, 9, 0);
INSERT INTO CRENEAUX ( VERSION, ID_MEDECIN, HDEBUT, MDEBUT, HFIN, MFIN) VALUES ( 1, 2, 9, 0, 9, 20);
INSERT INTO CRENEAUX ( VERSION, ID_MEDECIN, HDEBUT, MDEBUT, HFIN, MFIN) VALUES ( 1, 2, 9, 20, 9, 40);
INSERT INTO CRENEAUX ( VERSION, ID_MEDECIN, HDEBUT, MDEBUT, HFIN, MFIN) VALUES ( 1, 2, 9, 40, 10, 0);
INSERT INTO CRENEAUX ( VERSION, ID_MEDECIN, HDEBUT, MDEBUT, HFIN, MFIN) VALUES ( 1, 2, 10, 0, 10, 20);
INSERT INTO CRENEAUX ( VERSION, ID_MEDECIN, HDEBUT, MDEBUT, HFIN, MFIN) VALUES ( 1, 2, 10, 20, 10, 40);
INSERT INTO CRENEAUX ( VERSION, ID_MEDECIN, HDEBUT, MDEBUT, HFIN, MFIN) VALUES ( 1, 2, 10, 40, 12, 0);
INSERT INTO CRENEAUX ( VERSION, ID_MEDECIN, HDEBUT, MDEBUT, HFIN, MFIN) VALUES ( 1, 2, 12, 0, 12, 20);
INSERT INTO CRENEAUX ( VERSION, ID_MEDECIN, HDEBUT, MDEBUT, HFIN, MFIN) VALUES ( 1, 2, 12, 20, 12, 40);
INSERT INTO CRENEAUX ( VERSION, ID_MEDECIN, HDEBUT, MDEBUT, HFIN, MFIN) VALUES ( 1, 2, 12, 40, 12, 0);
INSERT INTO CRENEAUX ( VERSION, ID_MEDECIN, HDEBUT, MDEBUT, HFIN, MFIN) VALUES ( 1, 3, 8, 0, 8, 20);
INSERT INTO CRENEAUX ( VERSION, ID_MEDECIN, HDEBUT, MDEBUT, HFIN, MFIN) VALUES ( 1, 3, 8, 20, 8, 40);
INSERT INTO CRENEAUX ( VERSION, ID_MEDECIN, HDEBUT, MDEBUT, HFIN, MFIN) VALUES ( 1, 3, 8, 40, 9, 0);
INSERT INTO CRENEAUX ( VERSION, ID_MEDECIN, HDEBUT, MDEBUT, HFIN, MFIN) VALUES ( 1, 3, 9, 0, 9, 20);
INSERT INTO CRENEAUX ( VERSION, ID_MEDECIN, HDEBUT, MDEBUT, HFIN, MFIN) VALUES ( 1, 3, 9, 20, 9, 40);
INSERT INTO CRENEAUX ( VERSION, ID_MEDECIN, HDEBUT, MDEBUT, HFIN, MFIN) VALUES ( 1, 3, 9, 40, 10, 0);
INSERT INTO CRENEAUX ( VERSION, ID_MEDECIN, HDEBUT, MDEBUT, HFIN, MFIN) VALUES ( 1, 3, 10, 0, 10, 20);
INSERT INTO CRENEAUX ( VERSION, ID_MEDECIN, HDEBUT, MDEBUT, HFIN, MFIN) VALUES ( 1, 3, 10, 20, 10, 40);
INSERT INTO CRENEAUX ( VERSION, ID_MEDECIN, HDEBUT, MDEBUT, HFIN, MFIN) VALUES ( 1, 3, 10, 40, 12, 0);
INSERT INTO CRENEAUX ( VERSION, ID_MEDECIN, HDEBUT, MDEBUT, HFIN, MFIN) VALUES ( 1, 3, 12, 0, 12, 20);

COMMIT WORK;

INSERT INTO RV ( JOUR, ID_CRENEAU, ID_CLIENT) VALUES ('2006-08-22', 1, 2);
INSERT INTO RV ( JOUR, ID_CRENEAU, ID_CLIENT) VALUES ('2006-08-23', 5, 2);
INSERT INTO RV ( JOUR, ID_CRENEAU, ID_CLIENT) VALUES ('2006-08-23', 20, 4);
INSERT INTO RV ( JOUR, ID_CRENEAU, ID_CLIENT) VALUES ('2006-09-10', 10, 2);
INSERT INTO RV ( JOUR, ID_CRENEAU, ID_CLIENT) VALUES ('2006-08-23', 8, 4);
INSERT INTO RV ( JOUR, ID_CRENEAU, ID_CLIENT) VALUES ('2006-08-23', 7, 3);

COMMIT WORK;

ALTER TABLE RV ADD CONSTRAINT UNQ1_RV UNIQUE (JOUR, ID_CRENEAU);

COMMIT WORK;