DROP TABLE IF EXISTS Assure ;
CREATE TABLE Assure
(
    noassure INT PRIMARY KEY,
    nomassure text NOT NULL,
    adresse text NOT NULL,
    cp char(5) check (CP ~ '^\d{5}$') NOT NULL,
    ville text NOT NULL

);

DROP TABLE IF EXISTS Contrat ;
CREATE TABLE Contrat
(
    noPolice int primary key,
    dateSouscription date NOT NULL,
    noCarteGrise text NOT NULL,
    dateMEC date NOT NULL,
    immatriculation text UNIQUE NOT NULL,
    puissance int NOT NULL,
    noassure int references assure(noassure) NOT NULL
);

DROP TABLE IF EXISTS Expert;
CREATE TABLE Expert
(
    noexpert INT PRIMARY KEY,
    nom text NOT NULL,
    telephone text NOT NULL
);


DROP TABLE IF EXISTS Sinistre;
CREATE TABLE Sinistre
(
    noDossier INT PRIMARY KEY,
    datesurvenance date NOT NULL,
    circonstances text NOT NULL,
    lieu text NOT NULL,
    nature text NOT NULL,
    blesses bool NOT NULL,
    datedeclaration date NOT NULL CHECK (datedeclaration >= datesurvenance),
    dossiercloture bool NOT NULL,
    nopolice int references contrat(noPolice) NOT NULL,
    noexpert int references Expert(noexpert) NOT NULL
);

DROP TABLE IF EXISTS TypeGarantie;
CREATE TABLE TypeGarantie
(
    codegarantie INT PRIMARY KEY,
    libelle TEXT NOT NULL,
    franchise numeric(12, 2) CHECK (Franchise >= 0) NOT NULL,
    plafond numeric(12, 2) CHECK (Plafond >= 0) CHECK (Franchise < Plafond) NOT NULL

);

DROP TABLE IF EXISTS MiseEnJeu;
CREATE TABLE MiseEnJeu
(
    nodossier int references Sinistre(noDossier),
    codegarantie int references TypeGarantie(codegarantie),
    declare numeric(12, 2) CHECK (declare >= 0) NOT NULL,
    rembourse numeric(12, 2) CHECK (rembourse >= 0),
    PRIMARY KEY (nodossier, codegarantie)
);

DROP TABLE IF EXISTS Associative;
CREATE TABLE Associative
(
    nopolice int references Contrat(noPolice),
    codegarantie int references TypeGarantie(codegarantie),
    PRIMARY KEY (nopolice, codegarantie)
);

DROP TABLE IF EXISTS HistoriqueBonus;
CREATE TABLE HistoriqueBonus
(
    nopolice int references Contrat(noPolice),
    datebonus date,
    bonus numeric(3, 2) NOT NULL,
    PRIMARY KEY (nopolice, datebonus)
);