text: -- DROP SCHEMA IF EXISTS review_tables;
  -- CREATE SCHEMA review_tables;
  
  DROP TABLE IF EXISTS reviews CASCADE;
  CREATE TABLE reviews (
   --product_id     int GENERATED BY DEFAULT AS IDENTITY,
   review_id      int UNIQUE,
   product_id     int GENERATED BY DEFAULT AS IDENTITY,
   rating         int NOT NULL,
   post_date           date NOT NULL,
   summary        varchar(250) NOT NULL,
   body           varchar(500) NOT NULL,
   recommend      boolean NOT NULL,
   reported       boolean NOT NULL,
   reviewer_name  varchar(50) NOT NULL,
   reviewer_email varchar(50) NOT NULL,
   response       varchar(250),
   helpfulness    integer NOT NULL,
   PRIMARY KEY (review_id)
  );
  --TODO add unique constraints on email and name...
  -- DROP INDEX if EXISTS product_key;
  -- CREATE INDEX product_key ON reviews (product_id);
  
  copy reviews from '/Users/jverploeg/Desktop/data/reviews.csv' delimiter ',' csv header;
  DROP INDEX if EXISTS product_key;
  CREATE INDEX product_key ON reviews (product_id);
  
  
  -- characteristics
  -- DROP TABLE IF EXISTS characteristics CASCADE;
  -- CREATE TABLE characteristics (
  --  characteristic_id int PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  --  char_name              varchar(50) NOT NULL,
  --  char_value             numeric(5,2)
  -- );
  DROP TABLE IF EXISTS characteristics CASCADE;
  CREATE TABLE characteristics (
   characteristic_id int PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
   product_id              integer GENERATED BY DEFAULT AS IDENTITY,
   name             varchar(50) NOT NULL
  );
  DROP INDEX if EXISTS char_key;
  CREATE INDEX char_key ON characteristics (product_id);
  copy characteristics from '/Users/jverploeg/Desktop/data/characteristics.csv'  delimiter ',' csv header;
  
  
  
  
  
  -- ************************************** characteristics-reviews
  DROP TABLE IF EXISTS characteristics_reviews CASCADE;
  CREATE TABLE characteristics_reviews
  (
   id               int PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
   characteristic_id int,-- GENERATED BY DEFAULT AS IDENTITY,
   review_id         int,-- GENERATED BY DEFAULT AS IDENTITY,
   value             integer not null
   --CONSTRAINT PK_characterisctics PRIMARY KEY ( id ),
   --CONSTRAINT fk_char FOREIGN KEY ( characteristic_id ) REFERENCES characteristics ( characteristic_id ),
   --CONSTRAINT fk_review FOREIGN KEY ( review_id ) REFERENCES reviews ( review_id )
   --CONSTRAINT fk_char FOREIGN KEY ( characteristic_id ) REFERENCES characteristics ( characteristic_id )
  );
  DROP INDEX if EXISTS char_rev_key;
  CREATE INDEX char_rev_key ON characteristics_reviews (review_id);
  copy characteristics_reviews from '/Users/jverploeg/Desktop/data/characteristic_reviews.csv'  delimiter ',' csv header;
  
  
  
  -- ************************************** photos
  DROP TABLE IF EXISTS photos;
  CREATE TABLE photos
  (
   id               int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
   review_id int GENERATED BY DEFAULT AS IDENTITY,
   product_id     int,
   url       varchar(150) NOT NULL,
   CONSTRAINT fk_reviews FOREIGN KEY ( review_id ) REFERENCES reviews ( review_id )
  );
  DROP INDEX if EXISTS photo_key;
  CREATE INDEX photo_key ON photos (product_id);
  copy photos (id, review_id, url)  from '/Users/jverploeg/Desktop/data/reviews_photos.csv' delimiter ','  csv header;
  update photos set product_id = reviews.product_id from reviews where photos.review_id = reviews.review_id;
  -- DROP INDEX if EXISTS photo_key;
  -- CREATE INDEX photo_key ON photos (product_id);
  
  
  
  
  -- ************************************** ratings
  DROP TABLE IF EXISTS ratings CASCADE;
  CREATE TABLE ratings
  (
   product_id integer,
   count        integer default(0),
   rating       double precision,
   average      numeric(4,3),
   recommended      integer
  );
  DROP INDEX if EXISTS rating_key;
  CREATE INDEX rating_key ON ratings (product_id);
  insert into ratings (product_id, count, rating, recommended) select product_id, count(product_id), sum(rating), count(recommend) from reviews group by product_id;
  update ratings set average = rating / count;
  
  -- with src as (
  -- update reviews set helpfulness = helpfulness + 1 where review_id=5777874 returning *)
  -- update ratings set
  
  
  
  -- -- ************************************** products
  -- DROP TABLE IF EXISTS products;
  -- CREATE TABLE products
  -- (
  --  product_id      int UNIQUE NOT NULL,
  --  review_count    int NOT NULL,
  --  ratings_val     numeric(4,3),
  --  recommend_count integer NOT NULL,
  --  characteristics integer NOT NULL,
  --  CONSTRAINT fk_reviews FOREIGN KEY ( product_id )
  --  REFERENCES ratings ( product_id ),
  --  CONSTRAINT fk_chars FOREIGN KEY ( characteristics ) REFERENCES characteristics_reviews ( id )
  -- );
  
  
  
  
  
  
  