DROP FUNCTION IF EXISTS get_subscriptions(text);
 CREATE OR REPLACE FUNCTION get_subscriptions(username text) RETURNS
   TABLE(name text) AS $$
 DECLARE
   uid INT;
 BEGIN
   SELECT INTO uid id FROM users WHERE users.name=username;
   RETURN QUERY SELECT users.name FROM user_subscriptions
     RIGHT JOIN users ON users.id = to_id
     WHERE fro_id=uid
     ORDER BY users.name
     ;
 END
 $$ LANGUAGE plpgsql;
 
 

 DROP FUNCTION IF EXISTS subscribe_link(text,int);
 CREATE OR REPLACE FUNCTION subscribe_link(username text, linkid int)
   RETURNS bool AS $$
 DECLARE
   uid int;
   result bool;
   n_title text;
   n_url text;
 BEGIN
   SELECT INTO result NOT exists(SELECT * FROM user_links WHERE user_id=uid AND link_id=linkid);
   IF result THEN
     SELECT title,url INTO n_title,n_url FROM links WHERE links.id=linkid;
     PERFORM put_link(username, n_url, n_title);
   END IF;
   RETURN result;
 END
 $$ LANGUAGE plpgsql;
 

 DROP FUNCTION IF EXISTS delete_link(text,int);
 CREATE OR REPLACE FUNCTION delete_link(username text, linkid int)
   RETURNS bool AS $$
 DECLARE
   uid int;
   result bool;
 BEGIN
   SELECT INTO uid id FROM users WHERE name=username;
   SELECT INTO result exists(SELECT * FROM user_links WHERE user_id=uid AND link_id=linkid);
   IF result THEN
     DELETE FROM user_links WHERE user_id=uid AND link_id=linkid;
   END IF;
   RETURN result;
 END
 $$ LANGUAGE plpgsql;
 

 DROP FUNCTION IF EXIST has_user_shared(text, text);
 CREATE OR REPLACE FUNCTION has_user_shared(username text, linkurl text) RETURNS bool AS $$
 DECLARE
   result bool;
 BEGIN
   SELECT INTO result EXISTS(
     SELECT 1 FROM user_links ul
     LEFT JOIN users u ON user_id=u.id
     LEFT JOIN links l ON link_id=l.id
     WHERE linkurl=l.url AND username=u.name
   );
   RETURN result;
 END
 $$ LANGUAGE plpgsql;
 

 DROP FUNCTION IF EXISTS get_bones(text, timestamp, int);

 DROP FUNCTION IF EXISTS get_bones(text, int, timestamp);
 CREATE OR REPLACE FUNCTION get_bones(username text, lim int, before timestamp)

   RETURNS TABLE(linkid int, url text, title text, posted timestamp, poster text, total_votes bigint, subscriber_vote int, shared bool) AS $$

 DECLARE
   subscriber_id int;
 BEGIN
   SELECT INTO subscriber_id id FROM users WHERE users.name = username;
   CREATE TEMP TABLE middle
     ON COMMIT DROP
     AS
       SELECT
         DISTINCT ON (links.url)

         links.id,links.url,links.title,links.posted,users1.name,
         total_votes(links.id),user_vote(subscriber_id,links.id),
         has_user_shared(username, links.url)

         FROM user_subscriptions
         RIGHT JOIN user_links ON user_subscriptions.to_id=user_links.user_id

         INNER JOIN links ON link_id=links.id

         INNER JOIN users ON users.id=fro_id
         LEFT JOIN users as users1 ON users1.id=to_id
         WHERE fro_id = subscriber_id
               AND
               links.posted < before ;
   RETURN QUERY SELECT * FROM middle ORDER BY middle.posted DESC LIMIT lim;
 END
 $$ LANGUAGE plpgsql;
 

 DROP FUNCTION IF EXISTS get_bones(text, int);
 CREATE OR REPLACE FUNCTION get_bones(username text, lim int)

   RETURNS TABLE(linkid int, url text, title text, posted timestamp, poster text, total_votes bigint,
                 subscriber_vote int, shared bool) AS $$

 DECLARE
   subscriber_id int;
 BEGIN
   SELECT INTO subscriber_id id FROM users WHERE users.name = username;
   CREATE TEMP TABLE middle
     ON COMMIT DROP
     AS
       SELECT
         DISTINCT ON (links.url)

         links.id,links.url,links.title,links.posted,users1.name,
         total_votes(links.id),user_vote(subscriber_id,links.id),
         has_user_shared(username, links.url)

         FROM user_subscriptions
         RIGHT JOIN user_links ON user_subscriptions.to_id=user_links.user_id
         INNER JOIN links ON link_id=links.id
         INNER JOIN users ON users.id=fro_id
         LEFT JOIN users as users1 ON users1.id=to_id
         WHERE fro_id = subscriber_id;
   RETURN QUERY SELECT * FROM middle ORDER BY middle.posted DESC LIMIT lim;
 END
 $$ LANGUAGE plpgsql;
 

 DROP FUNCTION IF EXISTS get_bones(text);
 CREATE OR REPLACE FUNCTION get_bones(username text)

   RETURNS TABLE(linkid int, url text, title text, posted timestamp, poster text, total_votes bigint,
                 subscriber_vote int, shared bool) AS $$

 DECLARE
   subscriber_id int;
 BEGIN
   SELECT INTO subscriber_id id FROM users WHERE users.name = username;
   CREATE TEMP TABLE middle
     ON COMMIT DROP
     AS
       SELECT
         DISTINCT ON (links.url)

         links.id,links.url,links.title,links.posted,users1.name,
         total_votes(links.id),user_vote(subscriber_id,links.id),
         has_user_shared(username, links.url)

         FROM user_subscriptions
         RIGHT JOIN user_links ON user_subscriptions.to_id=user_links.user_id
         INNER JOIN links ON link_id=links.id
         INNER JOIN users ON users.id=fro_id

         LEFT JOIN users as users1 ON users1.id=to_id

         WHERE fro_id = subscriber_id;
   RETURN QUERY SELECT * FROM middle ORDER BY middle.posted DESC;
 END
 $$ LANGUAGE plpgsql;
 
 DROP FUNCTION IF EXISTS get_bone(text);
 CREATE OR REPLACE FUNCTION get_bone(username text)

   RETURNS TABLE(name text, url text, title text, posted timestamp, linkid int, votes bigint, shared bool) AS $$

 BEGIN

   RETURN QUERY SELECT users.name, links.url, links.title, links.posted, links.id, total_votes(links.id)

       FROM users
         INNER JOIN user_links ON user_links.user_id = users.id
         INNER JOIN links ON user_links.link_id = links.id
         WHERE users.name=username
         ORDER BY links.posted DESC;
 END
 $$ LANGUAGE plpgsql;
 
 DROP FUNCTION IF EXISTS unsubscribe(text,text);
 CREATE OR REPLACE FUNCTION unsubscribe(user1 text, user2 text) RETURNS int
 AS $$
 DECLARE
   user1_id int;
   user2_id int;
   result int;
 BEGIN
   SELECT INTO user1_id id FROM users WHERE users.name = user1;
   SELECT INTO user2_id id FROM users WHERE users.name = user2;
 
   DELETE FROM user_subscriptions WHERE fro_id = user1_id AND to_id = user2_id;
   RETURN result;
 END
 $$ LANGUAGE plpgsql;
 
 DROP FUNCTION IF EXISTS subscribe(text,text);
 CREATE OR REPLACE FUNCTION subscribe(user1 text, user2 text) RETURNS int
 AS $$
 DECLARE
   user1_id int;
   user2_id int;
   result int;
 BEGIN
   SELECT INTO user1_id id FROM users WHERE users.name = user1;
   SELECT INTO user2_id id FROM users WHERE users.name = user2;
 
   INSERT INTO user_subscriptions (fro_id, to_id) VALUES (user1_id, user2_id) RETURNING user_subscriptions.id INTO result;
   RETURN result;
 END
 $$ LANGUAGE plpgsql;
 
 DROP FUNCTION IF EXISTS follows(text,text);
 CREATE OR REPLACE FUNCTION follows(fro text, to_ text)
   RETURNS bool
 AS $$
 DECLARE
   froid int;
   toid int;
   result bool;
 BEGIN
   SELECT id INTO froid FROM users WHERE name = fro;
   SELECT id INTO toid FROM users WHERE name = to_;
   SELECT INTO result exists(SELECT 1 FROM user_subscriptions WHERE fro_id=froid and to_id=toid);
   RETURN result;
 END
 $$ LANGUAGE plpgsql;
 
 
 DROP FUNCTION IF EXISTS put_link(text,text,text);
 DROP FUNCTION IF EXISTS put_link(text,text,text,timestamp);
 DROP FUNCTION IF EXISTS put_link(text,text,timestamp,text);
 DROP TYPE IF EXISTS link_url_type;
 CREATE TYPE link_url_type AS (link_id int, user_id int);
 
 CREATE OR REPLACE FUNCTION put_link(username text, link_url text, n_posted timestamp, link_title text default '')
   RETURNS link_url_type
 AS $$
 DECLARE
   n_link_id int;
   n_user_id int;
   result link_url_type;
 BEGIN
   INSERT INTO links (url, title, posted) VALUES
     (link_url, link_title, n_posted) RETURNING id INTO n_link_id;
   SELECT users.id FROM users WHERE users.name = username INTO n_user_id;
   INSERT INTO user_links (user_id, link_id) VALUES (n_user_id, n_link_id);
 
   SELECT n_link_id INTO result.link_id;
   SELECT n_user_id INTO result.user_id;
   RETURN result;
 END
 $$ LANGUAGE plpgsql;
 
 CREATE OR REPLACE FUNCTION put_link(username text, link_url text, link_title text default '')
   RETURNS link_url_type
 AS $$
 DECLARE
   n_link_id int;
   n_user_id int;
   result link_url_type;
 BEGIN
   INSERT INTO links (url, title) VALUES
     (link_url, link_title) RETURNING id INTO n_link_id;
   SELECT users.id FROM users WHERE users.name = username INTO n_user_id;
   INSERT INTO user_links (user_id, link_id) VALUES (n_user_id, n_link_id);
 
   SELECT n_link_id INTO result.link_id;
   SELECT n_user_id INTO result.user_id;
   RETURN result;
 END
 $$ LANGUAGE plpgsql;
 

 DROP FUNCTION IF EXISTS put_ak(text,text);
 CREATE OR REPLACE FUNCTION put_ak(username text, supplied_ak text) RETURNS text
 AS $$
 DECLARE
   uid INT;
 BEGIN
   SELECT INTO uid id FROM users WHERE name = username;
   INSERT INTO user_ak (user_id,ak) VALUES (uid,supplied_ak);
   RETURN supplied_ak;
 END
 $$ LANGUAGE plpgsql;

 DROP FUNCTION IF EXISTS check_ak(text,text);
 CREATE OR REPLACE FUNCTION check_ak(username text, supplied_ak text) RETURNS TEXT
 AS $$
 DECLARE
   uid INT;
   result TEXT;
 BEGIN
   SELECT INTO uid id FROM users WHERE name = username;
   DELETE FROM user_ak WHERE user_id=uid AND user_ak.ak=supplied_ak RETURNING user_ak.ak INTO result;
   RETURN result;
 END

 $$ LANGUAGE plpgsql;
 
 DROP FUNCTION IF EXISTS check_password(text,text);
 CREATE OR REPLACE FUNCTION check_password(username text, provided_pass text) RETURNS bool
 AS $$
 DECLARE
   uid INT;
   stored_hash TEXT;
   hash TEXT;
   result BOOL;
 BEGIN
   SELECT id,password INTO uid,stored_hash FROM users WHERE users.name = username;
   SELECT stored_hash = crypt(provided_pass, stored_hash) INTO result;
   RETURN result;
 END
 $$ LANGUAGE plpgsql;

 
 DROP FUNCTION IF EXISTS vote_link(text);
 DROP FUNCTION IF EXISTS vote_link(text,int);
 DROP FUNCTION IF EXISTS vote_link(text,text,int);
 DROP FUNCTION IF EXISTS vote_link(int,text,int);

 DROP TYPE IF EXISTS vote_result_type;
 
 CREATE TYPE vote_result_type AS (myvote int, totalvotes bigint);
 

 CREATE OR REPLACE FUNCTION vote_link(linkid int, uid int, newvote int) RETURNS vote_result_type
 AS $$
 BEGIN
   INSERT INTO link_votes(vote, user_id, link_id, voted) VALUES (newvote, uid, linkid, now());
   RETURN (newvote,total_votes(linkid));
 END
 $$ LANGUAGE plpgsql;
 

 CREATE OR REPLACE FUNCTION vote_link(linkid int, username text, newvote int) RETURNS vote_result_type

 AS $$
 DECLARE
   uid INT;
 BEGIN
   SELECT id INTO uid FROM users WHERE users.name = username;
   INSERT INTO link_votes(vote, user_id, link_id, voted) VALUES (newvote, uid, linkid, now());

   RETURN (newvote,total_votes(linkid));

 END
 $$ LANGUAGE plpgsql;
 

 CREATE OR REPLACE FUNCTION vote_link(linkurl text, username text, newvote int) RETURNS vote_result_type

 AS $$
 DECLARE
   linkid INT;
 BEGIN
   SELECT id INTO linkid FROM links WHERE url = linkurl;
   RETURN vote_link(linkid, username, newvote);
 END
 $$ LANGUAGE plpgsql;
 
 DROP FUNCTION IF EXISTS total_votes(int);
 DROP FUNCTION IF EXISTS total_votes(text);
 CREATE OR REPLACE FUNCTION total_votes(req_linkid int) RETURNS bigint
 AS $$
 DECLARE
   result BIGINT;
   linkid INT;
 BEGIN
   SELECT id INTO linkid FROM links WHERE links.id = req_linkid;
   IF linkid IS NOT null THEN

     WITH RECURSIVE
       rel_links AS (
         SELECT l2.id FROM links l1 INNER JOIN links l2 ON l1.url=l2.url WHERE l1.id=linkid
       ), ordered AS (
         SELECT DISTINCT ON (user_id) vote FROM link_votes INNER JOIN rel_links ON link_id=rel_links.id ORDER BY user_id,voted DESC
       )

     SELECT sum(vote) INTO result FROM ordered;

     IF result IS null THEN
       SELECT 0 INTO result;
     END IF;
   END IF;
   RETURN result;
 END
 $$ LANGUAGE plpgsql;
 
 CREATE OR REPLACE FUNCTION total_votes(linkurl text) RETURNS bigint
 AS $$
 DECLARE
   linkid INT;
   result BIGINT;
 BEGIN
   SELECT id INTO linkid FROM links WHERE links.url = linkurl;
   SELECT total_votes(linkid) INTO result;
   IF linkid IS NOT null AND result IS null THEN
     SELECT 0 INTO result;
   END IF;
   RETURN result;
 END
 $$ LANGUAGE plpgsql;

 
 DROP FUNCTION IF EXISTS user_vote(int,int);
 CREATE OR REPLACE FUNCTION user_vote(uid int, linkid int) RETURNS int
 AS $$
 DECLARE
   result INT;
 BEGIN

   WITH votes_cast AS (
     SELECT link_id,vote,url FROM link_votes
       INNER JOIN links l1 ON link_id=l1.id
       WHERE user_id=uid
       ORDER BY voted DESC
   ) SELECT vote INTO result FROM links l2, votes_cast
       WHERE l2.url=votes_cast.url AND l2.id=linkid
       LIMIT 1;

   IF result IS null THEN
     SELECT 0 INTO result;
   END IF;
   RETURN result;
 END
 $$ LANGUAGE plpgsql;
 
 DROP FUNCTION IF EXISTS user_vote(text,int);
 CREATE OR REPLACE FUNCTION user_vote(username text, linkid int) RETURNS int
 AS $$
 DECLARE
   uid INT;
 BEGIN
   SELECT id INTO uid FROM users WHERE users.name = username;
   RETURN user_vote(uid, linkid);
 END
 $$ LANGUAGE plpgsql;

 
 DROP FUNCTION IF EXISTS total_user_votes(text);
 DROP FUNCTION IF EXISTS total_user_votes(int);
 CREATE OR REPLACE FUNCTION total_user_votes(username text) RETURNS bigint
 AS $$
 DECLARE
   uid int;
   result bigint;
 BEGIN
   SELECT total_user_votes(id) INTO result FROM users WHERE users.name=username;
   RETURN result;
 END
 $$ LANGUAGE plpgsql;
 CREATE OR REPLACE FUNCTION total_user_votes(uid int) RETURNS bigint
 AS $$
 DECLARE
   result bigint;
 BEGIN

   SELECT sum(total_votes(link_id)) INTO result FROM user_links WHERE user_id = uid;

   IF result IS NULL AND exists(SELECT 1 FROM users WHERE id=uid LIMIT 1) THEN
     SELECT 0 INTO result;
   END IF;
   RETURN result;
 END
 $$ LANGUAGE plpgsql;