/* testing recommend when user, charlie, when he has clan friends*/

delete from likes;
delete from users;

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

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

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

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


insert into likes (login_name,mtitle) values ('david', 'Rambo');
insert into likes (login_name,mtitle) values ('david', 'Armageddon');
insert into likes (login_name,mtitle) values ('david', 'Batman');



/* Kate's version:*/
/*
create view charlie_liked as select mtitle from (select mtitle from likes                                                     
       where login_name='charlie') as cc;                                                                                     
create view all_charlie as select distinct(login_name) from likes                                                             
       where login_name='charlie';                                                                                            
create view all_comb as select login_name,mtitle from all_charlie, movies;                                                    
create view nonexst as select login_name, mtitle from all_comb                                                                
       except (select login_name, mtitle from likes);                                                                         
select mtitle from nonexst;   
*/

/*
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_name,mtitle from users,charlie_liked  intersect                                                    
       select login_name, mtitle from likes);                                                                                 
create view clan_names as                                                                                                     
       select distinct  login_name from  clan where login_name <> 'charlie';                                                  
*/
/*select the movies that the clan has seen*/                                                                                  
/*
                                                                                                                            
create view clan_movies as                                                                                                    
       select login_name,mtitle from                                                                                          
        (select login_name, mtitle from clan_names, movies) as d intersect                                                    
        (select login_name,mtitle from likes);                                                                                
                                                                                                                              
select mtitle from                                                                                                            
       (select mtitle  from clan_movies) as e except                                                                          
        (select mtitle from clan where login_name = 'charlie');          

*/  
                                                                 


/* all pairs of like-minded people */                                                                            
/*
drop view if exists clan_pairs;
create view clan_pairs as
   select distinct L1.login_name as first, L2.login_name as second
   from likes L1, likes L2
   where L1.login_name <> L2.login_name
      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_name
    and likes.mtitle not in
    (select mtitle
     from likes
     where login_name = 'charlie')
group by likes.mtitle
order by popularity desc;
*/
-- /* gives (Batman, 2), (Rambo, 1) */                                                                              


