gistfile1.txt

drop table if exists genre cascade;
drop table if exists song cascade;
drop table if exists users cascade;
drop table if exists listened_song cascade;

create table genre(
  id serial primary key,
  name text not null
);

create table song(
  id serial primary key,
  name text not null,
  genre_id integer not null references genre(id),
  release_date date not null
);
create index on song(genre_id);

create table users(
  id serial primary key,
  name text not null
);

create table listened_song(
  users_id integer not null references users(id),
  song_id integer not null references song(id),
  primary key(users_id, song_id)
);
create index on listened_song(users_id);
create index on listened_song(song_id);

insert into genre(name) values ('popsa'),('rock'),('jazz');
insert into users(name) values ('kureev');
insert into song(name,genre_id,release_date) values
  ('Kirkorov - Zaika moya', 1, date '1990-01-01'),
  ('Metallica - Saint anger', 2, date '2003-01-01'),
  ('Billy Strayhorn- Take the train', 3, date '1920-01-01');

-- returns best matching song that has not been listened yet and marks it to be
-- listened by the user
create or replace function listen_song(
  user_id integer, 
  preferenced_date date, 
  preferenced_genre_id integer
) returns song as 
$$
  declare 
    result_song song%rowtype;

  begin
    select 
      song.*,
      -- calculate relevance
      -- 20 points for genre match
      (case when preferenced_genre_id = genre.id then 20 else 0 end) -
      -- subtract 1 point for each year between release date and preferred date
      (abs(preferenced_date - release_date) / 365)
        as relevance
    from song,genre where 
      song.genre_id = genre.id and
      not exists(
        select * from listened_song s where 
        s.users_id = user_id and song.id = s.song_id
      )
    order by relevance desc limit 1
    into result_song;

    insert into listened_song(users_id,song_id) values(user_id,result_song.id);

    return result_song;
  end;
$$ language plpgsql;

select listen_song(1,date('1950-01-01'),3);
select listen_song(1,date('1950-01-01'),3);