/* Script to create the tables to store products and price lists */

-- drop table price_list_products;
-- drop table price_lists;
-- alter table products drop constraint products_writing_packs_fk;
-- drop table writing_packs;
-- drop table products;

create table products (
    product_id integer primary key, 
    product_type varchar(30), 
    product_name varchar(20),
    price decimal(6,2),
    product_brand varchar(30), 
    description varchar(1000), 
    colour varchar(10),
    height varchar(10),
    weight varchar(10),
    country varchar(50),
    --specific columns for certain product types 
    ruler_length varchar(10),
    pen_type varchar(10),
    pencil_shade varchar(5),
    writing_pack_id integer);

create table writing_packs (
    writing_pack_id integer primary key,
    pencil_case integer references products(product_id),
    pen1 integer references products(product_id),
    pen2 integer references products(product_id),
    pencil integer references products(product_id),
    eraser integer references products(product_id));

alter table products add constraint products_writing_packs_fk 
    foreign key (writing_pack_id) references writing_packs(writing_pack_id);

create table price_lists (
    list_name varchar(20) primary key,
    date_created date);

create table price_list_products (
    price_list varchar(20) references price_lists(list_name),
    product integer references products(product_id));


