-- Data model
 DROP TABLE reviews;
 DROP TABLE reviewers;
 DROP TABLE restaurants;
 CREATE TABLE restaurants (
   id SERIAL UNIQUE NOT NULL,
   restaurant_name TEXT NOT NULL,
   short_name TEXT NOT NULL UNIQUE,
   PRIMARY KEY (id)
 );
 
 CREATE TABLE reviewers (
   id SERIAL UNIQUE NOT NULL,
   reviewer_name TEXT UNIQUE NOT NULL,
   created_date TIMESTAMP,
   last_seen TIMESTAMP,
   PRIMARY KEY (id)
 );
 
 CREATE TABLE reviews (
   id SERIAL UNIQUE NOT NULL,
   reviewer_id INTEGER REFERENCES reviewers (id) NOT NULL,
   restaurant_id INTEGER REFERENCES restaurants (id) NOT NULL,
   review_posted TIMESTAMP DEFAULT current_timestamp,
   review TEXT NOT NULL,
   UNIQUE (reviewer_id, restaurant_id),
   PRIMARY KEY (id)
 );
 
 -- Stored Procedures
 
 CREATE OR REPLACE FUNCTION get_reviews(sn text)
   RETURNS TABLE(review text, reviewer text)  AS $$
 BEGIN
   RETURN QUERY SELECT reviews.review, reviewers.reviewer_name
                 FROM reviews
                   INNER JOIN reviewers ON reviews.reviewer_id=reviewers.id
                   INNER JOIN restaurants ON reviews.restaurant_id=restaurants.id
                   WHERE restaurants.short_name=sn
                   ORDER BY review_posted DESC,
                            reviewer_name ASC;
 END;
 $$ LANGUAGE plpgsql;
 
 CREATE OR REPLACE FUNCTION put_review(sn text, rn text, review text)
   RETURNS VOID AS $$
 DECLARE
   restaurant_id int;
   reviewer_id int;
   nowstamp timestamp;
 BEGIN
   SELECT restaurants.id INTO restaurant_id FROM restaurants WHERE restaurants.short_name=sn;
   SELECT reviewers.id INTO reviewer_id FROM reviewers WHERE reviewers.reviewer_name=rn;
 
   WITH new_values (rest_id, rev_id, review, tm) as (values (restaurant_id, reviewer_id, review, current_timestamp)),
   upsert as
   ( 
       UPDATE reviews m 
           SET restaurant_id = nv.rest_id,
               reviewer_id = nv.rev_id,
               review = nv.review,
               review_posted = tm
       FROM new_values nv
       WHERE m.restaurant_id = nv.rest_id AND m.reviewer_id = nv.rev_id
       RETURNING m.*
   )
   INSERT INTO reviews (restaurant_id, reviewer_id, review)
   SELECT rest_id, rev_id, new_values.review
   FROM new_values
   WHERE NOT EXISTS (SELECT 1 
                     FROM upsert up 
                     WHERE up.restaurant_id = new_values.rest_id AND up.reviewer_id = new_values.rev_id);
 END;
 $$ LANGUAGE plpgsql;
 
 -- Test Data
 INSERT INTO restaurants  (restaurant_name, short_name) VALUES
   ('Vigilante Coffee', 'vigilante-coffee'),
   ('The Coffee Bar', 'the-coffee-bar'),
   ('Peregrine Espresso', 'peregrine-espresso'),
   ('Graffiato', 'graffiato');
 
 INSERT INTO reviewers (reviewer_name) VALUES
   ('the_tooth_fairy'),
   ('fiddlerwoaroof'),
   ('grandma_johnson');
 
 INSERT INTO reviews (reviewer_id, restaurant_id, review) VALUES
   ((SELECT id FROM reviewers where reviewer_name='the_tooth_fairy'), (SELECT id FROM restaurants where short_name='vigilante-coffee'), 'Great Coffee!'),
   ((SELECT id FROM reviewers where reviewer_name='fiddlerwoaroof'),  (SELECT id FROM restaurants where short_name='vigilante-coffee'), 'My coffee was cold :/'),
   ((SELECT id FROM reviewers where reviewer_name='grandma_johnson'), (SELECT id FROM restaurants where short_name='vigilante-coffee'), 'Bad Customer service, will actively avoid!'),
   ((SELECT id FROM reviewers where reviewer_name='the_tooth_fairy'), (SELECT id FROM restaurants where short_name='peregrine-espresso'), 'The owner looked at me funny,  do not return!'),
   ((SELECT id FROM reviewers where reviewer_name='fiddlerwoaroof'),  (SELECT id FROM restaurants where short_name='peregrine-espresso'), 'Great Coffee!'),
   ((SELECT id FROM reviewers where reviewer_name='grandma_johnson'), (SELECT id FROM restaurants where short_name='peregrine-espresso'), 'Cockroach in bottom of french press, disgusting!'),
   ((SELECT id FROM reviewers where reviewer_name='the_tooth_fairy'), (SELECT id FROM restaurants where short_name='graffiato'), 'Too Italian'),
   ((SELECT id FROM reviewers where reviewer_name='fiddlerwoaroof'),  (SELECT id FROM restaurants where short_name='graffiato'), 'They dont have a peanut butter pizza!'),
   ((SELECT id FROM reviewers where reviewer_name='grandma_johnson'), (SELECT id FROM restaurants where short_name='graffiato'), 'Whatever . . . '),
   ((SELECT id FROM reviewers where reviewer_name='the_tooth_fairy'), (SELECT id FROM restaurants where short_name='the-coffee-bar'), 'Great Location! Good Coffee!'),
   ((SELECT id FROM reviewers where reviewer_name='fiddlerwoaroof'),  (SELECT id FROM restaurants where short_name='the-coffee-bar'), 'Great Coffee! Good Location!'),
   ((SELECT id FROM reviewers where reviewer_name='grandma_johnson'), (SELECT id FROM restaurants where short_name='the-coffee-bar'), 'This is too far from my knitting needles . . . ');
 
 SELECT * FROM reviews
   RIGHT JOIN reviewers ON reviews.reviewer_id=reviewers.id
   RIGHT JOIN restaurants ON reviews.restaurant_id=restaurants.id
   WHERE short_name='vigilante-coffee';
 
 INSERT INTO reviewers (reviewer_name) VALUES ('the_great_one');