drop table taggings;
drop table contacts;
drop table books;
drop table tags;

create table contacts (
  id  serial primary key,
  name varchar
);

create table books (
  id  serial primary key,
  name varchar
);

create table tags (
  id serial primary key,
  name varchar
);

create table taggings (
  tag_id int,
  tagged_id int,
  tagged_type int -- points to the table this tag is tagging
);

insert into contacts (id,name) VALUES (1,'guy');
insert into contacts (id,name) VALUES (2,'michal');
insert into contacts (id,name) VALUES (3,'gal');
insert into contacts (id,name) VALUES (4,'noa');
insert into contacts (id,name) VALUES (5,'edo');

insert into books (id,name) VALUES (1,'B1');
insert into books (id,name) VALUES (2,'B2');
insert into books (id,name) VALUES (3,'B3');
insert into books (id,name) VALUES (4,'B4');
insert into books (id,name) VALUES (5,'B5');

insert into tags (id,name) values (1,'summer');
insert into tags (id,name) values (2,'winter');
insert into tags (id,name) values (3,'spring');
insert into tags (id,name) values (4,'fall');
insert into tags (id,name) values (5,'sea');
insert into tags (id,name) values (6,'beach');

insert into taggings (tag_id,tagged_id,tagged_type) values (1,1,1);
insert into taggings (tag_id,tagged_id,tagged_type) values (2,1,1);
insert into taggings (tag_id,tagged_id,tagged_type) values (3,1,1);
insert into taggings (tag_id,tagged_id,tagged_type) values (4,1,1);

insert into taggings (tag_id,tagged_id,tagged_type) values (1,2,1);
insert into taggings (tag_id,tagged_id,tagged_type) values (2,2,1);
insert into taggings (tag_id,tagged_id,tagged_type) values (3,2,1);
insert into taggings (tag_id,tagged_id,tagged_type) values (4,2,1);
insert into taggings (tag_id,tagged_id,tagged_type) values (5,2,1);

insert into taggings (tag_id,tagged_id,tagged_type) values (1,3,1);
insert into taggings (tag_id,tagged_id,tagged_type) values (4,3,1);

insert into taggings (tag_id,tagged_id,tagged_type) values (1,4,1);
insert into taggings (tag_id,tagged_id,tagged_type) values (2,4,1);
insert into taggings (tag_id,tagged_id,tagged_type) values (3,4,1);
insert into taggings (tag_id,tagged_id,tagged_type) values (4,4,1);

insert into taggings (tag_id,tagged_id,tagged_type) values (1,2,2);
insert into taggings (tag_id,tagged_id,tagged_type) values (2,2,2);
insert into taggings (tag_id,tagged_id,tagged_type) values (3,2,2);
insert into taggings (tag_id,tagged_id,tagged_type) values (4,2,2);

insert into taggings (tag_id,tagged_id,tagged_type) values (1,1,2);
insert into taggings (tag_id,tagged_id,tagged_type) values (4,1,2);

-- Find all items tagged with the same set of tags
SELECT DISTINCT taggings.tagged_id, taggings.tagged_type
FROM taggings
WHERE 4= 
  ( SELECT COUNT(*) 
    FROM tags, taggings as taggings2
    WHERE tags.id = taggings2.tag_id 
      AND lower(tags.name) IN ( 'summer' , 'winter', 'spring', 'fall' ) 
      AND taggings.tagged_type = taggings2.tagged_type
      AND taggings.tagged_id = taggings2.tagged_id 
  );

-- Find all items of same type with the same set of tags
SELECT * 
FROM contacts
WHERE 4 = 
  ( SELECT COUNT(*)
    FROM tags, taggings
    WHERE tags.id = taggings.tag_id
          AND lower(tags.name) IN ( 'summer' , 'winter', 'spring', 'fall' )
          AND taggings.tagged_type = 1
          AND taggings.tagged_id = contacts.id 
  );