-- 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');