/* testing recommend when user, charlie */

delete from likes;
delete from users;

drop table if exists movies cascade;
\i /usr0/stu415_s13/etaralova415/loading.sql

insert into users (login_id,email,passwd) values ('anca', 'anca@cmu.edu', 'ancapass');
insert into users (login_id,email,passwd) values ('bob', 'bob@cmu.edu', 'bobpass');
insert into users (login_id,email,passwd) values ('charlie', 'charlie@cmu.edu', 'charliepass');

insert into users (login_id,email,passwd) values ('david', 'david@cmu.edu', 'davidpass');

insert into likes (login_id,mtitle) values ('anca', 'Armageddon');
insert into likes (login_id,mtitle) values ('anca', 'Batman');
insert into likes (login_id,mtitle) values ('bob', 'Armageddon');
insert into likes (login_id,mtitle) values ('bob', 'TRON');
insert into likes (login_id,mtitle) values ('bob', 'Dracula');
insert into likes (login_id,mtitle) values ('bob', 'Batman');
insert into likes (login_id,mtitle) values ('charlie', 'Armageddon');
insert into likes (login_id,mtitle) values ('charlie', 'TRON');
insert into likes (login_id,mtitle) values ('charlie', 'Bolt');
insert into likes (login_id,mtitle) values ('charlie', 'Casino');


insert into likes (login_id,mtitle) values ('david', 'Rambo');
insert into likes (login_id,mtitle) values ('david', 'Armageddon');
insert into likes (login_id,mtitle) values ('david', 'Dracula');


/*
drop view  if exists charlie_liked cascade;
drop view  if exists all_charlie cascade;
drop view  if exists all_comb cascade;
drop view  if exists nonexst cascade;
    
create view charlie_liked as select mtitle from (select mtitle from likes
       where login_id='charlie') as cc;
create view all_charlie as select distinct(login_id) from likes
       where login_id='charlie';
create view all_comb as select login_id,mtitle from all_charlie, movies;
create view nonexst as select login_id, mtitle from all_comb
       except (select login_id, mtitle from likes);
select mtitle from nonexst;
*/

/*recommend*/
/*
drop view if exists clan_name cascade;
drop view if exists clan_movies cascade;
drop view if exists clan cascade;
create view clan as
       (select users.login_id,mtitle from users,charlie_liked  intersect
       select login_id, mtitle from likes);
create view clan_names as
       select distinct  login_id from  clan where login_id <> 'charlie';
*/
/*select the movies that the clan has seen*/
/*

create view clan_movies as                                                                                                    
       select login_id,mtitle from                                                                                          
        (select login_id, mtitle from clan_names, movies) as d intersect                                                    
        (select login_id,mtitle from likes);                                                                                
                                                                                                                              
select mtitle from                                                                                                            
       (select mtitle  from clan_movies) as e except                                                                          
        (select mtitle from clan where login_id = 'charlie');       

*/

/*Christos' solution gives output different from mine:*/
/* all pairs of like-minded people */                                                                            
/*
drop view if exists clan_pairs;
create view clan_pairs as
   select distinct L1.login_id as first, L2.login_id as second
   from likes L1, likes L2
   where L1.login_id <> L2.login_id
      and L1.mtitle = L2.mtitle;
*/
/* now the full query, with popularity, and excluding 'charlie' himself */                                       
/*
select likes.mtitle, count(likes.mtitle) as popularity
from clan_pairs, likes
where clan_pairs.first = 'charlie'
    and clan_pairs.second = likes.login_id
    and likes.mtitle not in
    (select mtitle
     from likes
     where login_id = 'charlie')
group by likes.mtitle
order by popularity desc, likes.mtitle desc;
*/

/*gives
 mtitle  | popularity 
---------+------------
 Dracula |          2
 Batman  |          2
 Rambo   |          1
(3 rows)

*/



