--Suppression
DROP TABLE FERME CASCADE CONSTRAINTS;
DROP TABLE VOLAILLE CASCADE CONSTRAINTS;
DROP TABLE ARTICLE CASCADE CONSTRAINTS;
DROP TABLE MARCHE_VEND CASCADE CONSTRAINTS;
DROP TABLE ENTREPOSE CASCADE CONSTRAINTS;
DROP TABLE A_VENDU CASCADE CONSTRAINTS;

--Table ferme
CREATE TABLE FERME
(
    ID_FERMIER VARCHAR2(15)  NOT NULL,
    NOM VARCHAR2(50)  NOT NULL,
    MDP VARCHAR2(15)  NOT NULL,
    MAIL VARCHAR2(50)  NOT NULL,
    DATE_ENREGISTREMENT DATE  NOT NULL,
    DATE_CONNECTION DATE NOT NULL,
    ECUS INTEGER  DEFAULT 500 CHECK( ECUS >= 0),
    SCORE INTEGER  DEFAULT 0 CHECK( SCORE >= 0),
    CLASSEMENT INTEGER  NULL,
    HIBERNATION CHAR(1)  DEFAULT 'F' CHECK (HIBERNATION IN ('T','F')),
    NB_ACHATS_JOUR INTEGER  DEFAULT 0 CHECK (NB_ACHATS_JOUR  BETWEEN 0 AND 12),
    NB_MALE INTEGER  DEFAULT 0 CHECK ( NB_MALE >= 0),
    NB_FEMELLE INTEGER  DEFAULT 0 CHECK ( NB_FEMELLE >= 0),
    NB_GROS INTEGER  DEFAULT 0 CHECK ( NB_GROS >= 0),
    NB_MOYEN INTEGER  DEFAULT 0 CHECK ( NB_MOYEN >= 0),
    NB_PETIT INTEGER  DEFAULT 8 CHECK ( NB_PETIT >= 0),
    ABREUVAGE_L CHAR(1)  DEFAULT 'F' CHECK (ABREUVAGE_L IN ('T','F')),
    NB_ABREUVAGE_L INTEGER  CHECK (NB_ABREUVAGE_L  BETWEEN 0 AND 4),
    NOURRI_L CHAR(1)  DEFAULT 'F' CHECK (NOURRI_L IN ('T','F')),
    NB_JEUNE_L INTEGER  CHECK (NB_JEUNE_L  BETWEEN 0 AND 4),
    SALE_L CHAR(1)  DEFAULT 'F' CHECK (SALE_L IN ('T','F')),
    NB_MALADE_L INTEGER  CHECK(NB_MALADE_L  BETWEEN 0 AND 4),
    ABREUVAGE_J CHAR(1)  DEFAULT 'F' CHECK (ABREUVAGE_J IN ('T','F')),
    NB_ABREUVAGE_J INTEGER  CHECK (NB_ABREUVAGE_J  BETWEEN 0 AND 4),
    NOURRI_J CHAR(1)  DEFAULT 'F' CHECK (NOURRI_J IN ('T','F')),
    NB_JEUNE_J INTEGER  CHECK(NB_JEUNE_J  BETWEEN 0 AND 4),
    SALE_J CHAR(1)  DEFAULT 'F' CHECK ( SALE_J IN ('T','F')),
    NB_MALADE_J INTEGER  CHECK(NB_MALADE_J  BETWEEN 0 AND 4),
    ABREUVAGE_VACHE CHAR(1)  DEFAULT 'F' CHECK (ABREUVAGE_VACHE IN ('T','F')),
    NB_ABREUVAGE_VACHE INTEGER  CHECK(NB_ABREUVAGE_VACHE  BETWEEN 0 AND 4),
    NOURRI_VACHE CHAR(1)  DEFAULT 'F' CHECK (NOURRI_VACHE IN ('T','F')),
    NB_JEUNE_VACHE INTEGER  CHECK(NB_JEUNE_VACHE  BETWEEN 0 AND 4),
    SALE_VACHE CHAR(1)  DEFAULT 'F' CHECK (SALE_VACHE IN ('T','F')),
    NB_MALADE_VACHE INTEGER  CHECK (NB_MALADE_VACHE  BETWEEN 0 AND 4),
    POIDS_VACHE INTEGER  DEFAULT 0 CHECK ( POIDS_VACHE BETWEEN 0 AND 750),
    AGE_VACHE INTEGER  DEFAULT 0,
    NB_LAIT  INTEGER  DEFAULT 0 CHECK ( NB_LAIT BETWEEN 0 AND 16),
   CONSTRAINT PK_FERMIER PRIMARY KEY (ID_FERMIER)  
);

--Table volaille
CREATE TABLE VOLAILLE
(
    ID_VOLAILLE INTEGER  NOT NULL,
    ID_FERMIER VARCHAR2(15)  NOT NULL,
    ABREUVAGE_V CHAR(1)  DEFAULT 'F' CHECK (ABREUVAGE_V IN ('T','F')),
    NB_ABREUVAGE_V INTEGER  CHECK (NB_ABREUVAGE_V  BETWEEN 0 AND 4),
    NOURRI_V CHAR(1)  DEFAULT 'F' CHECK (NOURRI_V IN ('T','F')),
    NB_JEUNE_V INTEGER  CHECK (NB_JEUNE_V  BETWEEN 0 AND 4),
    SALE_V CHAR(1)  DEFAULT 'F' CHECK (SALE_V IN ('T','F')),
    NB_MALADE_V INTEGER CHECK (NB_MALADE_V  BETWEEN 0 AND 4),
    POIDS_V FLOAT DEFAULT 0.05 CHECK (POIDS_V BETWEEN 0 AND 3.5),
    AGE_V INTEGER  DEFAULT 0 CHECK (AGE_V >= 0),
    PRODUCTION_V INTEGER  DEFAULT 0,
   CONSTRAINT PK_VOLAILLES PRIMARY KEY (ID_VOLAILLE)  
);

--Table article
CREATE TABLE ARTICLE
(
    ID_ARTICLE INTEGER  NOT NULL,
    NOM VARCHAR2(25)  NOT NULL,
    DESCRIPTION VARCHAR2(500)  NOT NULL,
    COLLECTIONNABLE CHAR(1)  DEFAULT 'F' CHECK (COLLECTIONNABLE IN ('T','F')),
    QUANTITE INTEGER  DEFAULT 0 CHECK(QUANTITE >= 0),
    PRIX_VENTE INTEGER  DEFAULT 0 CHECK(PRIX_VENTE >= 0),
    PRIX_ACHAT INTEGER  DEFAULT 0 CHECK(PRIX_ACHAT >= 0),
   CONSTRAINT PK_ARTICLE PRIMARY KEY (ID_ARTICLE)  
);

--Table marche_vend
CREATE TABLE MARCHE_VEND
(
    ID_FERMIER VARCHAR2(15)  NOT NULL,
    ID_ARTICLE INTEGER  NOT NULL,
    NB_VENTE INTEGER  DEFAULT 0 CHECK (NB_VENTE >= 0),
    PRIX INTEGER  DEFAULT 0 CHECK (PRIX >= 0),
   CONSTRAINT PK_MARCHE_VEND PRIMARY KEY (ID_FERMIER, ID_ARTICLE)  
);

--Table entrepose
CREATE TABLE ENTREPOSE
(
    ID_FERMIER VARCHAR2(15)  NOT NULL,
    ID_ARTICLE INTEGER  NOT NULL,
    QUANTITE INTEGER  DEFAULT 0 CHECK (QUANTITE >= 0),
    COLLECTION CHAR(1)  DEFAULT 'F' CHECK (COLLECTION IN ('T','F')),
    CONSTRAINT PK_ENTREPOSE PRIMARY KEY (ID_FERMIER, ID_ARTICLE)  
);

--Table a_vendu
CREATE TABLE A_VENDU
(
    ID_FERMIER VARCHAR2(15)  NOT NULL,
    ID_ARTICLE INTEGER  NOT NULL,
    NB_VENDU INTEGER  DEFAULT 0 CHECK (NB_VENDU >= 0),
    DATE_VENTE DATE  NULL,
    CONSTRAINT PK_A_VENDU PRIMARY KEY (ID_FERMIER, ID_ARTICLE)  
);

--Alter table pour les cles etrangeres
ALTER TABLE VOLAILLE ADD (
     CONSTRAINT FK_VOLAILLE_FERMIER
          FOREIGN KEY (ID_FERMIER)
               REFERENCES FERME (ID_FERMIER))   ;

ALTER TABLE MARCHE_VEND ADD (
     CONSTRAINT FK_MARCHE_VEND_FERMIER
          FOREIGN KEY (ID_FERMIER)
               REFERENCES FERME (ID_FERMIER))   ;

ALTER TABLE MARCHE_VEND ADD (
     CONSTRAINT FK_MARCHE_VEND_ARTICLE
          FOREIGN KEY (ID_ARTICLE)
               REFERENCES ARTICLE (ID_ARTICLE))   ;

ALTER TABLE ENTREPOSE ADD (
     CONSTRAINT FK_ENTREPOSE_FERMIER
          FOREIGN KEY (ID_FERMIER)
               REFERENCES FERME (ID_FERMIER))   ;

ALTER TABLE ENTREPOSE ADD (
     CONSTRAINT FK_ENTREPOSE_ARTICLE
          FOREIGN KEY (ID_ARTICLE)
               REFERENCES ARTICLE (ID_ARTICLE))   ;

ALTER TABLE A_VENDU ADD (
     CONSTRAINT FK_A_VENDU_FERMIER
          FOREIGN KEY (ID_FERMIER)
               REFERENCES FERME (ID_FERMIER))   ;

ALTER TABLE A_VENDU ADD (
     CONSTRAINT FK_A_VENDU_ARTICLE
          FOREIGN KEY (ID_ARTICLE)
               REFERENCES ARTICLE (ID_ARTICLE))   ;


--  View : coop_stock
-- View : Clapier
-- View : Paturage