.timer on
.echo on
.eqp on
.header on
.mode column
-- --
-- DROP TABLE IF EXISTS posts;
-- DROP TABLE IF EXISTS comments;

-- CREATE TABLE posts(
--    id INTEGER PRIMARY KEY,
--    author VARCHAR(20),
--    likes INTEGER,
--    content VARCHAR(200),
--    posted TIMESTAMP DEFAULT CURRENT_TIMESTAMP
-- );

-- CREATE TABLE comments(
--    id INTEGER PRIMARY KEY,
--    author VARCHAR(20),
--    likes INTEGER,
--    content VARCHAR(200),
--    commented TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
--    post_id INTEGER
-- );

-- INSERT INTO posts(author, likes, content)
--   WITH RECURSIVE
--   randomdata(x,y,z) AS (
--     SELECT lower(hex(randomblob(20))), abs(random()) % 1000000, lower(hex(randomblob(200)))
--     UNION ALL
--     SELECT lower(hex(randomblob(20))), abs(random()) % 1000000, lower(hex(randomblob(200)))
--     FROM randomdata
--     LIMIT 10000
--   ) SELECT * FROM randomdata;

-- SELECT COUNT(*) from posts;

-- INSERT INTO comments(author, content, likes, post_id)
--   WITH RECURSIVE
--   randomdata(x,y,z) AS (
--     SELECT lower(hex(randomblob(20))) AS author,
--            lower(hex(randomblob(200))) AS content,
--            abs(random()) % 1000
--     UNION ALL
--     SELECT lower(hex(randomblob(20))) AS author,
--            lower(hex(randomblob(200))) AS content,
--            abs(random()) % 1000
--     FROM randomdata
--     LIMIT 20
--   )
--   SELECT *
--   FROM randomdata,
--        (SELECT id FROM posts ORDER BY RANDOM()) AS post_ids ;

-- SELECT COUNT(*) from comments;

-- SELECT COUNT(*)
--   FROM comments INNER JOIN posts ON comments.post_id=posts.id
--   WHERE posts.content LIKE '%abcd%';

-- SELECT COUNT(*)
--   FROM comments INNER JOIN posts ON comments.post_id=posts.id
--   WHERE posts.content LIKE '%abcd%' AND comments.content LIKE '%abcd%';

-- SELECT COUNT(*)
-- FROM comments, posts
-- WHERE comments.likes = posts.likes
--       AND posts.content LIKE '%abcd%'
--       AND comments.content LIKE '%abcd%';
-- --
-- PRAGMA automatic_index = false;
-- --
-- SELECT COUNT(*)
-- FROM comments, posts
-- WHERE comments.likes = posts.likes
--       AND posts.content LIKE '%abcd%'
--       AND comments.content LIKE '%abcd%';

-- CREATE INDEX idx_comment_likes ON comments(likes);

-- SELECT COUNT(*)
-- FROM comments, posts
-- WHERE comments.likes = posts.likes
--       AND posts.content LIKE '%abcd%'
--       AND comments.content LIKE '%abcd%';

-- CREATE INDEX idx_posts_likes ON posts(likes);

-- SELECT COUNT(*)
-- FROM comments, posts
-- WHERE comments.likes = posts.likes
--       AND posts.content LIKE '%abcd%'
--       AND comments.content LIKE '%abcd%';

-- DROP TABLE IF EXISTS tags;
-- DROP TABLE IF EXISTS tagged;

-- CREATE TABLE tags(
--    id INTEGER PRIMARY KEY,
--    name VARCHAR(20)
-- );

-- CREATE TABLE tagged(
--    id INTEGER PRIMARY KEY,
--    tag_id INTEGER,
--    post_id INTEGER
-- );

-- INSERT INTO tags(name)
  -- WITH RECURSIVE
  -- randomdata(x) AS (
    -- SELECT lower(hex(randomblob(20)))
    -- UNION ALL
    -- SELECT lower(hex(randomblob(20)))
    -- FROM randomdata
    -- LIMIT 1000
  -- ) SELECT * FROM randomdata;

--  SELECT COUNT(*) FROM tags;

-- INSERT INTO tagged(tag_id, post_id)
  -- SELECT tag_ids.id, post_ids.id
  -- FROM (SELECT id FROM tags ORDER BY RANDOM() LIMIT ABS((RANDOM() % 50) + 50) ) AS tag_ids ,
       -- (SELECT id FROM posts ORDER BY RANDOM() LIMIT ABS((RANDOM() % 50) + 50)) AS post_ids ;

-- INSERT INTO tagged(tag_id, post_id)
  -- SELECT tag_ids.id, post_ids.id
  -- FROM (SELECT id FROM tags ORDER BY RANDOM() LIMIT ABS((RANDOM() % 50) + 50) ) AS tag_ids ,
       -- (SELECT id FROM posts ORDER BY RANDOM() LIMIT ABS((RANDOM() % 50) + 50)) AS post_ids ;

-- INSERT INTO tagged(tag_id, post_id)
  -- SELECT tag_ids.id, post_ids.id
  -- FROM (SELECT id FROM tags ORDER BY RANDOM() LIMIT ABS((RANDOM() % 50) + 50) ) AS tag_ids ,
       -- (SELECT id FROM posts ORDER BY RANDOM() LIMIT ABS((RANDOM() % 50) + 50)) AS post_ids ;

-- INSERT INTO tagged(tag_id, post_id)
  -- SELECT tag_ids.id, post_ids.id
  -- FROM (SELECT id FROM tags ORDER BY RANDOM() LIMIT ABS((RANDOM() % 50) + 50) ) AS tag_ids ,
  --      (SELECT id FROM posts ORDER BY RANDOM() LIMIT ABS((RANDOM() % 50) + 50)) AS post_ids ;

-- SELECT COUNT(*) FROM tagged;

-- DROP INDEX IF EXISTS idx_tagged_tag_id;
-- CREATE INDEX IF NOT EXISTS idx_tagged_tag_id ON tagged(tag_id);

-- DROP INDEX IF EXISTS idx_tagged_post_id;
-- CREATE INDEX IF NOT EXISTS idx_tagged_post_id ON tagged(post_id);

-- DROP INDEX IF EXISTS idx_liked_posts;
-- CREATE INDEX idx_liked_posts
  -- ON posts(id)
  -- WHERE likes >= 10000;

-- DROP INDEX IF EXISTS idx_tagged_post_id_tag_id;
-- CREATE INDEX idx_tagged_post_id_tag_id ON tagged(tag_id, post_id);

-- DROP INDEX IF EXISTS idx_posts_author_abc;
-- CREATE INDEX idx_posts_author_abc 
-- 	ON posts(author)
-- 	WHERE author LIKE '%abc%';

-- DROP INDEX IF EXISTS idx_comments_content_abc;
-- CREATE INDEX idx_comments_content_abc
-- 	ON comments(content)
-- 	WHERE content LIKE '%abc%';


-- List all tags with the post count (i.e. use GROUP BY)

SELECT tags.name, COUNT(posts.id) num_posts
FROM tags
  INNER JOIN tagged ON tags.id = tagged.tag_id
  INNER JOIN posts ON tagged.post_id = posts.id
GROUP BY tags.id
LIMIT 10
;

-- List all tags with the post count that had at least 10000 likes and had at least 10 posts, sorted by post count descending
SELECT tags.name, COUNT(posts.id) num_posts
FROM tags
INNER JOIN tagged ON tags.id = tagged.tag_id
INNER JOIN posts ON tagged.post_id = posts.id
WHERE posts.likes >= 10000
GROUP BY tags.id
HAVING COUNT(posts.id) > 10
ORDER BY COUNT(posts.id) DESC 
LIMIT 10
;

-- List all posts that has a specific tag
-- TBD


-- List all posts that had at least 10000 that has a specific tag
-- TBD 

-- List all posts, ordered by the number of tags they have in descending order.
SELECT posts.author, COUNT(tags.id) num_tags
FROM posts
INNER JOIN tagged ON tagged.post_id = posts.id 
INNER JOIN tags ON tagged.tag_id = tags.id
GROUP BY posts.id
ORDER BY COUNT(tags.id) DESC
LIMIT 10
;

-- List all posts that have 2 or more tags that happen to have "abcd" in their name.
SELECT posts.author, COUNT(tagged.tag_id) num_tags
FROM posts
  INNER JOIN tagged ON tagged.post_id = posts.id
  INNER JOIN tags ON tagged.tag_id = tags.id
WHERE tags.name LIKE '%ab%'
GROUP BY posts.id
HAVING COUNT(tagged.tag_id) > 10
;

-- List all posts that have 20 or more tags and 10 or more comments, 
-- where the post author has "abc" as a substring 
-- and the comment content has "abc" as a substring
SELECT posts.author, 
	   COUNT(DISTINCT tagged.tag_id) num_tags, 
	   COUNT(DISTINCT comments.id) num_comments
FROM posts
  INNER JOIN tagged ON tagged.post_id = posts.id
  INNER JOIN comments ON comments.post_id = posts.id
WHERE posts.author LIKE '%ab%'
  AND comments.content LIKE '%ab%'
GROUP BY posts.id
HAVING COUNT(DISTINCT tagged.tag_id) >= 20 
   AND COUNT(DISTINCT comments.id) >= 10 
