DROP TABLE Defense;
DROP TABLE Arene;
DROP TABLE Apparition ;
DROP TABLE Emplacement;
DROP TABLE Pokemon;
DROP TABLE Joueur ;
DROP TABLE Equipe;
CREATE TABLE Equipe(
	id INT CONSTRAINT pk_equipe PRIMARY KEY,
	nom VARCHAR2(9),
	couleur VARCHAR2(5));
CREATE TABLE Joueur(
	pseudonyme VARCHAR2(30) CONSTRAINT pk_joueur PRIMARY KEY,
	personnage VARCHAR2(30) NOT NULL UNIQUE,
	sexe CHAR(1),
	niveau SMALLINT DEFAULT 0 NOT NULL,
	idEquipe REFERENCES Equipe(id));
CREATE TABLE Pokemon(
	id INT CONSTRAINT pk_pokemon PRIMARY KEY,
	nom VARCHAR2(30),
	espece VARCHAR2(20),
	pointCombat INT,
	idJoueur REFERENCES Joueur(pseudonyme),
	CONSTRAINT check_pointCombat CHECK (pointCombat >= 0));	
CREATE TABLE Emplacement(
	id INT CONSTRAINT pk_Emplacement PRIMARY KEY,
	latitude NUMBER(12,10) NOT NULL,
	longitude NUMBER(13,10) NOT NULL,
	CONSTRAINT check_latitude CHECK (latitude BETWEEN -90 AND 90),
	CONSTRAINT check_longitude CHECK (longitude BETWEEN -180 AND 180));
CREATE TABLE Apparition(
	idPokemon REFERENCES Pokemon(id),
	idEmplacement REFERENCES Emplacement(id),
	horaire DATE DEFAULT SYSDATE,
	duree INT,
	CONSTRAINT pk_Apparition PRIMARY KEY (idPokemon, idEmplacement));
CREATE TABLE Arene(
	id INT CONSTRAINT pk_Arene PRIMARY KEY, 
	nom VARCHAR2(30), 
	idEmplacement REFERENCES Emplacement(id));	
CREATE TABLE Defense(
	idEquipe REFERENCES Equipe(id), 
	idArene REFERENCES Arene(id), 
	dateControle DATE,
	CONSTRAINT pk_Defense PRIMARY KEY (idEquipe, idArene, dateControle));
INSERT INTO Equipe VALUES (1, 'Intuition', 'Jaune');
INSERT INTO Equipe VALUES (2, 'Sagesse', 'Bleu');
INSERT INTO Equipe VALUES (3, 'Bravoure', 'Rouge');
INSERT INTO Joueur VALUES ('Shadow', 'Smith', 'F', 10, 1);
INSERT INTO Joueur VALUES ('Root', 'Alice', 'F', 20, 2);
INSERT INTO Joueur VALUES ('Admin', 'Bob', 'M', 1, 1);
INSERT INTO Pokemon VALUES (1, 'Bulbizarre', 'Graine', 1071, 'Shadow');
INSERT INTO Pokemon VALUES (25, 'Pikachu', 'Souris', 887, 'Root');
INSERT INTO Pokemon VALUES (107, 'Tygnon', 'Puncheur', 204, 'Shadow');
INSERT INTO Pokemon VALUES (103, 'Noadkoko', 'Fruitpalme', 190, 'Admin');
INSERT INTO Pokemon VALUES (150, 'Mewtwo', 'Génétique', 4144, 'Root');
INSERT INTO Emplacement VALUES (1, 49.0350369, 2.0696998);
INSERT INTO Emplacement VALUES (2, 48.857848, 2.295253);
INSERT INTO Emplacement VALUES (3, -74.0445, 40.6892);
alter session set NLS_DATE_FORMAT='DD/MM/YYYY';
INSERT INTO Apparition VALUES (1, 2, '09/10/2016', 10);
INSERT INTO Apparition VALUES (25, 1, '01/09/2016', 20);
INSERT INTO Apparition VALUES (107, 3, '02/10/2016', 5);
INSERT INTO Apparition VALUES (103, 1, '25/10/2016', 15);
INSERT INTO Apparition VALUES (25, 3, '25/10/2016', 3);
INSERT INTO Arene VALUES (1, 'Liberte', 3);
INSERT INTO Arene VALUES (2, 'Eisti', 1);
INSERT INTO Arene VALUES (3, 'Star', 2);
INSERT INTO Defense VALUES (1, 1, '10/10/2016');
INSERT INTO Defense VALUES (1, 2, '01/09/2016');