--
-- Database: CARTRACK
--
-- --------------------------------------------------------

--
-- Delete tables if they exist
--
drop table if exists Installation;
drop table if exists MsgSysteme;
drop table if exists Systeme;
drop table if exists ClientVoiture;
drop table if exists Client;
drop table if exists Partenaire;
drop table if exists Voiture;
drop table if exists Adresse;

--
-- Table structure for table `Adresse`
--
CREATE TABLE if not exists Adresse (
  code int NOT NULL,
  numero int NOT NULL,
  compChar varchar(4) NOT NULL,
  nom varchar(20) NOT NULL,
  codePostal int NOT NULL,
  ville varchar(20) NOT NULL,
  primary key (code)
);

--
-- Table structure for table `Client`
--
CREATE TABLE if not exists Client (
  code int NOT NULL,
  nom varchar(20) NOT NULL,
  prenom varchar(20) NOT NULL,
  codeAdresse int NOT NULL,
  telephone varchar(8) NOT NULL,
  primary key (code),
  FOREIGN KEY (codeAdresse) REFERENCES Adresse(code)
);

--
-- Table structure for table `Voiture`
--
CREATE TABLE if not exists Voiture (
  plaque varchar(7) NOT NULL,
  marque varchar(20) NOT NULL,
  modele varchar(20) NOT NULL,
  couleur varchar(20) NOT NULL,
  dateImmatrigulation date NOT NULL,
  primary key (plaque)
);

--
-- Table structure for table `Partenaire`
--
CREATE TABLE if not exists Partenaire(
  code int NOT NULL,
  nom varchar(20) NOT NULL,
  codeAdresse int NOT NULL,
  telephone varchar(8) NOT NULL,
  primary key (code),
  FOREIGN KEY (codeAdresse) REFERENCES Adresse (code)  
);

--
-- Table structure for table `Systeme`
--
CREATE TABLE if not exists Systeme(
  code int NOT NULL,
  codeVoiture varchar(7),
  codePartenaire int,
  active int DEFAULT 0,
  primary key (code),
  FOREIGN KEY (codeVoiture) REFERENCES Voiture (plaque),
  FOREIGN KEY (codePartenaire) REFERENCES Partenaire (code)
);

--
-- Table structure for table `MsgSysteme`
--
CREATE TABLE if not exists MsgSysteme(
  dateMsg date NOT NULL,
  timeMsg time NOT NULL,
  codeSysteme int NOT NULL,
  latitude varchar(10) NOT NULL,
  logitude varchar(10) NOT NULL,
  alarme int NOT NULL,
  portes int NOT NULL,
  PRIMARY KEY (dateMsg, timeMsg, codeSysteme),
  FOREIGN KEY (codeSysteme) REFERENCES Systeme (code) 
);

--
-- Table structure for table `Installation`
--
CREATE TABLE if not exists Installation (
  codePartenaire int NOT NULL,
  codeSysteme int NOT NULL,
  codeVoiture VARCHAR(8) NOT NULL,
  testSysteme int NOT NULL,
    PRIMARY KEY (codeSysteme,codePartenaire),
    FOREIGN KEY (codeSysteme) 
  	REFERENCES Systeme (code),
    FOREIGN KEY (codeVoiture) 
  	REFERENCES Voiture (plaque),
    FOREIGN KEY (codePartenaire) 
  	REFERENCES Partenaire (code) 
);

--
-- Table structure for table `Client - Voiture`
--
CREATE TABLE if not exists ClientVoiture(
  codeClient int,
  plaque varchar(7),
    PRIMARY KEY (codeClient, plaque),
    FOREIGN KEY (codeClient) 
  	REFERENCES Client (code),
    FOREIGN KEY (plaque) 
  	REFERENCES Voiture (plaque)    
);

--
-- Insert records to "ADRESSE"
--
INSERT INTO ADRESSE VALUES (1, 3, 'b', 'Jean Medecin', '06000', 'Nice' );
INSERT INTO ADRESSE VALUES (2, 10, ' ', 'Barla', '06000', 'Nice');
INSERT INTO ADRESSE VALUES (3, 10, ' ','Jean Jaures', '06200', 'Cagnes');
INSERT INTO ADRESSE VALUES (4, 140,'b', 'Ave de la Californie', '06200', 'Nice');
INSERT INTO ADRESSE VALUES (5, 13,' ', 'Ave Max Maurey', '06160', 'Antibes');

--
-- Insert records to "CLIENT"
--
INSERT INTO CLIENT VALUES (1,'ACHARD', 'Anne-Laure', 3,'06125476');
INSERT INTO CLIENT VALUES (2,'AUBERT', 'Yoan', 1,'06456677');
INSERT INTO CLIENT VALUES (3,'BAKRETI', 'Amel', 5,'06234457');

--
-- Insert records to "VOITURE"
--
INSERT INTO VOITURE VALUES('CG792AV', 'Renault', 'Megane', 'Noire', '2005-04-05');
INSERT INTO VOITURE VALUES('AN154TS', 'Renault', 'Clio', 'Blanque', '2002-03-05');
INSERT INTO VOITURE VALUES('AB344CA', 'Volkswagen', 'Polo', 'Rouge', '2002-04-17');
INSERT INTO VOITURE VALUES('BJ543TX', 'Peugeot', '307', 'Verte', '2011-11-25');

--
-- Insert records to "CLIENTVOITURE"
--
INSERT INTO CLIENTVOITURE VALUES(1,'CG792AV');
INSERT INTO CLIENTVOITURE VALUES(1,'AB344CA');
INSERT INTO CLIENTVOITURE VALUES(3,'AN154TS');
INSERT INTO CLIENTVOITURE VALUES(2,'BJ543TX');

--
-- Insert records to "PARTENAIRE"
--
INSERT INTO PARTENAIRE VALUES(1, 'Californie Cars', 4, '04562344');

--
-- Insert records to "SYSTEME"
--
INSERT INTO SYSTEME VALUES(1,'AN154TS', 1, 0);
INSERT INTO SYSTEME VALUES(2,NULL, NULL, 0);
--
-- Insert records to "INSTALLATION"
--
INSERT INTO INSTALLATION VALUES(1, 1, 'AN154TS', 1);

-- --
-- -- Insert records to "MSGSYSTEME"
-- --