\c postgres;

drop database tetastock;
drop role tetastock;

create role tetastock with LOGIN ENCRYPTED PASSWORD 'tetastock';

create database tetastock;

\c tetastock;

CREATE TABLE stock_users (
  id serial primary key,
  mail text not NULL,
  password text not NULL,
  name text not NULL
);

CREATE TABLE stock_kits (
  id serial primary key,
  name text not NULL unique,
  designation text not NULL
);

CREATE TABLE stock_providers (
  id serial primary key,
  name text unique not NULL,
  address text not NULL,
  mail text not NULL,
  url text not NULL,
  comment text not NULL
);

CREATE TABLE stock_componants (
  id serial primary key,
  reference varchar(20) unique not NULL,
  designation varchar(100) not NULL,
  last_price NUMERIC not NULL default 0,
  mean_price NUMERIC not NULL default 0,
  quantity NUMERIC not NULL default 0,
  min_quantity NUMERIC not NULL default 0,
  place varchar(15) not NULL,
  provider_id integer REFERENCES stock_providers(id)
);

CREATE TABLE stock_kit_compositions (
  id serial primary key,
  kit_id integer REFERENCES Stock_kits(id),
  componant_id integer REFERENCES stock_componants(id),
  quantity integer not NULL
);

CREATE TABLE stock_orders (
  id serial primary key,
  componant_id integer REFERENCES stock_componants(id),
  quantity integer not NULL,
  price NUMERIC not NULL default 0,
  date timestamp not NULL
);

alter table stock_users owner to tetastock;
alter table stock_kits owner to tetastock;
alter table stock_providers owner to tetastock;
alter table stock_componants owner to tetastock;
alter table stock_kit_compositions owner to tetastock;
alter table stock_orders owner to tetastock;
alter database tetastock owner to tetastock;

insert into stock_users (mail, password, name) values ('test', '$2b$08$OkfihuGRyLdpftBpGhnpeeeUhUTQS0oXvR2NFByC.65XCKKvPBWHS', 'test');