-- ======================================================================
-- ===   Sql Script for Database : cave
-- ===
-- === Build : 15
-- ======================================================================

CREATE TABLE recoltant
  (
    id       serial,
    nom      text,
    adresse  text,

    primary key(id)
  );

-- ======================================================================

CREATE TABLE type_vin
  (
    id       serial,
    libelle  text     not null,

    primary key(id)
  );

-- ======================================================================

CREATE TABLE region
  (
    id       serial   unique not null,
    libelle  text     not null,

    primary key(id)
  );

-- ======================================================================

CREATE TABLE contenant
  (
    id          serial   unique not null,
    contenance  float4   not null,
    libelle     text,

    primary key(id)
  );

-- ======================================================================

CREATE TABLE appellation
  (
    id         serial,
    libelle    text     unique not null,
    region_id  int,

    primary key(id),

    foreign key(region_id) references region(id) on delete cascade
  );

-- ======================================================================

CREATE TABLE vin
  (
    id              serial   unique not null,
    recoltant_id    int,
    appellation_id  int      not null,
    type_vin_id     int      not null,

    primary key(id),

    foreign key(recoltant_id) references recoltant(id) on delete cascade,
    foreign key(appellation_id) references appellation(id) on delete cascade,
    foreign key(type_vin_id) references type_vin(id) on delete cascade
  );

-- ======================================================================

CREATE TABLE stock
  (
    vin_id        int    not null,
    contenant_id  int    not null,
    annee         int4   not null,
    nombre        int4   not null,

    primary key(vin_id,contenant_id,annee),

    foreign key(vin_id) references vin(id) on delete cascade,
    foreign key(contenant_id) references contenant(id) on delete cascade
  );

-- ======================================================================

ALTER TABLE recoltant SET (autovacuum_enabled = off);
ALTER TABLE type_vin SET (autovacuum_enabled = off);
ALTER TABLE region SET (autovacuum_enabled = off);
ALTER TABLE contenant SET (autovacuum_enabled = off);
ALTER TABLE appellation SET (autovacuum_enabled = off);
ALTER TABLE vin SET (autovacuum_enabled = off);
ALTER TABLE stock SET (autovacuum_enabled = off);

