DROP TABLE IF EXISTS user_ak;

 DROP TABLE IF EXISTS user_subscriptions;

 DROP TABLE IF EXISTS user_links;
 DROP TABLE IF EXISTS users;

 DROP TABLE IF EXISTS link_votes;

 CREATE TABLE users (
   id SERIAL UNIQUE NOT NULL,

   name TEXT UNIQUE NOT NULL CHECK (name <> ''),

   password TEXT NOT NULL,
   email TEXT NOT NULL,
   PRIMARY KEY (id)
 );
 
 DROP TABLE IF EXISTS links;
 CREATE TABLE links (
   id SERIAL UNIQUE NOT NULL,
   url TEXT NOT NULL,
   title TEXT DEFAULT '',
   posted TIMESTAMP DEFAULT current_timestamp,
   PRIMARY KEY (id)
 );
 CREATE INDEX url_index ON links (url);
 

 CREATE TABLE user_subscriptions (
   id SERIAL UNIQUE NOT NULL,
   fro_id INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE,
   to_id INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE,
   CHECK (fro_id != to_id),
   UNIQUE (fro_id, to_id),
   PRIMARY KEY (id)
 );
 

 CREATE TABLE link_votes (
   id SERIAL UNIQUE NOT NULL,
   user_id INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE,
   link_id INTEGER NOT NULL REFERENCES links (id) ON DELETE CASCADE,
   voted TIMESTAMP DEFAULT current_timestamp,
   vote BIGINT DEFAULT 0,
   UNIQUE (link_id,user_id,voted),
   PRIMARY KEY (id)
 );
 

 CREATE TABLE user_links (
   id SERIAL UNIQUE NOT NULL,

   user_id INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE,
   link_id INTEGER NOT NULL REFERENCES links (id) ON DELETE RESTRICT,

   UNIQUE (user_id, link_id),
   PRIMARY KEY (id)
 );
 

 CREATE TABLE user_ak (
   id SERIAL UNIQUE NOT NULL,
   generated TIMESTAMP DEFAULT current_timestamp,
   user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
   ak TEXT NOT NULL,
   UNIQUE (user_id, ak),
   PRIMARY KEY (id)
 );

 
 DROP VIEW IF EXISTS recently_active_users;
 CREATE VIEW recently_active_users AS
 WITH recent_users AS (
   SELECT user_id,name,posted
   FROM user_links
   LEFT JOIN links ON link_id = links.id
   RIGHT JOIN users ON user_id=users.id
   WHERE posted > now() - interval '1 week'
   ORDER BY posted desc, user_id)

 SELECT DISTINCT ON (user_id) user_id,name,posted FROM recent_users;