--------------------------------------------------------------
-- createbase.sql
--------------------------------------------------------------

DROP TABLE Statistique;
DROP TABLE Match;
DROP TABLE Joueur;
DROP TABLE Tableau;

CREATE TABLE Tableau
(
 NT  CHAR(2),
 Lib VARCHAR(30),
 CONSTRAINT pk_tableau PRIMARY KEY (NT)
);

CREATE TABLE Joueur
(
 NJ NUMBER,
 Pre VARCHAR(30),
 Nom VARCHAR(30),
 Gen CHAR(1),
 Cla NUMBER(3),
 Nat CHAR(3),
 VNs VARCHAR(30),
 PNs CHAR(3),
 DNs DATE,
 Main CHAR(6),
 Taille NUMBER(3,2),
 CONSTRAINT pk_joueur PRIMARY KEY (NJ),
 CONSTRAINT ck_joueur_Gen CHECK (Gen IN ('H','F')),
 CONSTRAINT ck_joueur_Cla CHECK (Cla > 0),
 CONSTRAINT ck_joueur_Main CHECK (Main IN ('Droite','Gauche'))
);



CREATE TABLE Match
(
 NM NUMBER,
 NT CHAR(2),
 Tour VARCHAR(30),
 NJ1 NUMBER,
 NJ1b NUMBER,
 NJ2 NUMBER,
 NJ2b NUMBER,
 J1SSet1 NUMBER,
 J1STie1 NUMBER,
 J1SSet2 NUMBER,
 J1STie2 NUMBER,
 J1SSet3 NUMBER,
 J1STie3 NUMBER,
 J1SSet4 NUMBER,
 J1STie4 NUMBER,
 J1SSet5 NUMBER,
 J2SSet1 NUMBER,
 J2STie1 NUMBER,
 J2SSet2 NUMBER,
 J2STie2 NUMBER,
 J2SSet3 NUMBER,
 J2STie3 NUMBER,
 J2SSet4 NUMBER,
 J2STie4 NUMBER,
 J2SSet5 NUMBER,
 CONSTRAINT pk_match PRIMARY KEY (NM),
 CONSTRAINT fk_match_tableau FOREIGN KEY (NT) REFERENCES Tableau (NT),
 CONSTRAINT nn_nt CHECK (NT is not null),
 CONSTRAINT fk_match_NJ1 FOREIGN KEY (NJ1) REFERENCES Joueur (NJ),
 CONSTRAINT nn_nj1 CHECK (nj1 is not null),
 CONSTRAINT fk_match_NJ2 FOREIGN KEY (NJ2) REFERENCES Joueur (NJ),
 CONSTRAINT nn_nj2 CHECK (nj2 is not null),
 CONSTRAINT fk_match_NJ1b FOREIGN KEY (NJ1b) REFERENCES Joueur (NJ),
 CONSTRAINT fk_match_NJ2b FOREIGN KEY (NJ2b) REFERENCES Joueur (NJ),
 CONSTRAINT ck_match_tour CHECK (Tour IN ('1er tour', '2ème tour', '3ème tour', '1/8 de finale', '1/4 de finale', '1/2 finale', 'finale')),
 CONSTRAINT ck_J1SSet1 check (J1SSet1 between 0 and 7 or J1SSet1=-1),
 CONSTRAINT ck_J1STie1 CHECK (J1STie1 >=-1),
 CONSTRAINT ck_J1SSet2 CHECK (J1SSet2 between 0 and 7 or J1SSet1=-1),
 CONSTRAINT ck_J1STie2 CHECK (J1STie2 >=-1),
 CONSTRAINT ck_J1SSet3 CHECK (J1SSet3 >=-1),
 CONSTRAINT ck_J1STie3 CHECK (J1STie3 >=0),
 CONSTRAINT ck_J1SSet4 CHECK (J1SSet4 between 0 and 7 or J1SSet1=-1),
 CONSTRAINT ck_J1STie4 CHECK (J1STie4 >=-1),
 CONSTRAINT ck_J1SSet5 CHECK (J1SSet5 >=-1 ),
 CONSTRAINT ck_J2SSet1 CHECK (J2SSet1 between 0 and 7 or J1SSet1=-1),
 CONSTRAINT ck_J2STie1 CHECK (J2STie1 >=-1),
 CONSTRAINT ck_J2SSet2 CHECK (J2SSet2 between 0 and 7 or J1SSet1=-1),
 CONSTRAINT ck_J2STie2 CHECK (J2STie2 >=-1),
 CONSTRAINT ck_J2SSet3 CHECK (J2SSet3 >=-1),
 CONSTRAINT ck_J2STie3 CHECK (J2STie3 >=-1),
 CONSTRAINT ck_J2SSet4 CHECK (J2SSet4 between 0 and 7 or J1SSet1=-1),
 CONSTRAINT ck_J2STie4 CHECK (J2STie4 >=-1),
 CONSTRAINT ck_J2SSet5 CHECK (J2SSet5 >=-1 )
);






CREATE TABLE Statistique
(
 NM NUMBER,
 NJ NUMBER,
 NS VARCHAR(30),
 Val NUMBER,
 CONSTRAINT pk_statistique PRIMARY KEY (NM, NJ, NS),
 CONSTRAINT fk_statistique_match FOREIGN KEY (NM) REFERENCES Match (NM),
 CONSTRAINT fk_statistique_joueur FOREIGN KEY (NJ) REFERENCES Joueur (NJ)
);


alter table Match add constraint ck_joueurs_diff check (NJ1 <> NJ2 AND NJ1 <> NJ1B AND NJ1 <> NJ2B AND NJ2 <> NJ1B AND NJ2 <> NJ2B AND NJ1B <> NJ2B);

alter table Match add constraint ck_tie_S1J1 check ((J1STie1 is not null and J1SSet1 is not null) or (J1STie1 is null));
alter table Match add constraint ck_tie_S1J2 check ((J2STie1 is not null and J2SSet1 is not null) or (J2STie1 is null));

alter table Match add constraint ck_sets_dames check ((NT like '%D' and J1sset4 is null and J1sset5 is null and J2sset4 is null and J2sset5 is null) or (NT not like '%D'));