-- tables_td10b.txt

drop table achat cascade constraints;
drop procedure achat;
drop table promotion cascade constraints;
drop table vin cascade constraints;
drop table client cascade constraints;

create table vin(
  idv integer                   primary key, 
  nom varchar2(13)    not null  unique,
  region varchar2(10) --  null possible 
);

create table promotion(
  idp integer                   primary key,
  idv integer         not null, foreign key (idv) references vin, 
  prix integer        not null, check(0 < prix and prix <= 50000),
  statut varchar2(10) default 'disponible'
                      not null, check(statut='disponible' or statut='vendu')
);

create table client(
  idc integer                   primary key,
  nom varchar2(10)    not null,
  points integer      not null, check(0 < points and points <= 50000)
);

create table achat(
  ida integer                   primary key,
  idp integer         not null  unique,
                                foreign key (idp) references promotion,
  idc integer         not null, foreign key (idc) references client
);

drop sequence seq_vin;
drop sequence seq_promotion;
drop sequence seq_client;
drop sequence seq_achat;

create sequence seq_vin;
create sequence seq_promotion start with 10;
create sequence seq_client start with 100;
create sequence seq_achat start with 1000;

-------------------------------------------------------------------------------
-- remplissage base minimale pour tester exo PL/SQL achat et appel JDBC

delete achat;
delete promotion;
delete vin;
delete client;


insert into vin values(seq_vin.nextval, 'romanee-conti', 'bourgogne');
    -- idv : 1
insert into promotion(idp, idv, prix) values(
    seq_promotion.nextval, seq_vin.currval, 5000);
insert into promotion(idp, idv, prix) values(
    seq_promotion.nextval, seq_vin.currval, 6000);
insert into promotion(idp, idv, prix) values(
    seq_promotion.nextval, seq_vin.currval, 7000);
insert into vin values(seq_vin.nextval, 'montrachet', 'bourgogne');
    -- idv : 2
insert into promotion values(
    seq_promotion.nextval, seq_vin.currval, 4000, 'vendu');
insert into promotion(idp, idv, prix) values(
    seq_promotion.nextval, seq_vin.currval, 5000);
insert into promotion(idp, idv, prix) values(
    seq_promotion.nextval, seq_vin.currval, 4000);
insert into promotion(idp, idv, prix) values(
    seq_promotion.nextval, seq_vin.currval, 4000);
insert into client values(seq_client.nextval, 'Rita', 9000);
    -- idc : 100

select * from vin;
select * from promotion;
select * from client;
select * from achat;
-- verifier les id : 1, 2, 100

-------------------------------------------------------------------------------