DROP TABLE IF EXISTS categories CASCADE;
 
CREATE TABLE categories (
id serial PRIMARY KEY,
title VARCHAR (50) UNIQUE ,
parent_id INT,
FOREIGN KEY (parent_id) REFERENCES categories (id));
 
INSERT INTO categories(title, parent_id) VALUES('Electronics', NULL);
INSERT INTO categories(title, parent_id) VALUES('Televisions', 1);
INSERT INTO categories(title, parent_id) VALUES('Portables', 1);
INSERT INTO categories(title, parent_id) VALUES('Plasma', 2);
INSERT INTO categories(title, parent_id) VALUES('LCD', 2);
INSERT INTO categories(title, parent_id) VALUES('MP3', 3);
INSERT INTO categories(title, parent_id) VALUES('CD', 3);
INSERT INTO categories(title, parent_id) VALUES('Laptops', 3);
INSERT INTO categories(title, parent_id) VALUES('Osio', 6);
 
select * from categories;
 
-- Finding the root node
SELECT *
FROM categories c
WHERE parent_id IS NULL;
 
-- Finding the immediate children of a node
SELECT * 
FROM categories c 
WHERE parent_id  = 1;
 
-- Find how many immediate children a node has
SELECT parents.title, COUNT(c2.id)  
FROM categories parents
JOIN categories c2 ON c2.parent_id = parents.id
GROUP BY parents.title;
 
-- Finding the leaf nodes
SELECT c.id, c.title 
FROM categories c 
WHERE c.id NOT IN (
SELECT parent_id FROM categories c2
WHERE parent_id IS NOT NULL);
 
-- Finding all pairs (Televisions/Portables and 
-- Portables/Televisions) with a shared parent
SELECT c1.id AS node_id, c1.title AS node_title, 
       c2.id AS sibling_id, c2.title AS sibling_title,
       c1.parent_id AS shared_parent_id
FROM categories c1
INNER JOIN categories c2 
  ON c1.parent_id = c2.parent_id 
 AND c1.id <> c2.id
ORDER BY c1.parent_id, c1.id;
 
-- Excluding reverse duplicate pairs (e.g., keeping only one variation of Televisions/Portables)
-- with a shared parent
SELECT c1.id AS node_id_1, c1.title AS node_title_1, 
       c2.id AS node_id_2, c2.title AS node_title_2,
       c1.parent_id AS shared_parent_id
FROM categories c1
INNER JOIN categories c2 
  ON c1.parent_id = c2.parent_id 
 AND c1.id < c2.id
ORDER BY c1.parent_id, c1.id;
 
-- Keeping titles of parent and child nodes
 
SELECT c1.parent_id AS shared_parent_id, c2.title AS parent_name,
   STRING_AGG(c1.title, ', ' ORDER BY c1.id) AS sibling_list
FROM categories c1
INNER JOIN categories c2 
   ON c1.parent_id = c2.id
GROUP BY c1.parent_id, c2.title
HAVING COUNT(c1.id) > 1
ORDER BY c1.parent_id;
 
 
-- Using recursion
-- Finding the parent of a node by id
WITH RECURSIVE category_path (id, title, parent_id) AS
(
    SELECT id, title, parent_id
    FROM categories 
WHERE id = 3
 UNION ALL
    select c.id, c.title, cp.parent_id
    FROM category_path AS cp JOIN categories AS c
    ON cp.id = c.parent_id
)
Select * from category_path
LIMIT 1;
 
-- Finding the parent of a node by title
WITH RECURSIVE category_path (id, title, parent_id) AS
(
    SELECT id, title, parent_id
    FROM categories 
WHERE title = 'Osio'
 UNION ALL
    select c.id, c.title, cp.parent_id
    FROM category_path AS cp JOIN categories AS c
    ON cp.id = c.parent_id
)
Select * from category_path
LIMIT 1;
 
-- Finding full path for each node
WITH RECURSIVE category_path (id, title, path) AS
(
  SELECT id, title, title as path
    FROM categories
    WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.title, CONCAT(cp.path, ' --> ', c.title)::varchar(50)
    FROM category_path AS cp JOIN categories AS c
      ON cp.id = c.parent_id
)
SELECT * FROM category_path
ORDER BY path;
 
-- Finding full path with array and level
WITH RECURSIVE category_path (id, title, parent_id, PATH, lvl) AS
(
    SELECT id, title, parent_id, string_to_array(title, '') as PATH, 0 AS lvl
    FROM categories
    WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.title, c.parent_id, array_cat(cp.PATH, string_to_array(c.title, '')), cp.lvl + 1 AS level
    FROM category_path AS cp JOIN categories AS c ON cp.id = c.parent_id
)
SELECT * FROM category_path;
 
-- Querying a sub-tree
WITH RECURSIVE category_path (id, title, parent_id, PATH, lvl) AS
(
    SELECT id, title, parent_id, string_to_array(title, '') as PATH, 0 AS lvl
    FROM categories
    WHERE id = 2
  UNION ALL
  SELECT c.id, c.title, c.parent_id, array_cat(cp.PATH, string_to_array(c.title, '')), cp.lvl + 1 AS level
    FROM category_path AS cp JOIN categories AS c ON cp.id = c.parent_id 
)
SELECT * FROM category_path;
 
-- Querying a single path from BOTTOM to TOP
WITH RECURSIVE category_path (id, title, parent_id, PATH, lvl) AS
(
    SELECT id, title, parent_id, string_to_array(title, '') as PATH, 0 AS lvl
    FROM categories
    WHERE id = 6
--WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.title, c.parent_id, array_cat(cp.PATH, string_to_array(c.title, '')), cp.lvl + 1 AS level
    FROM category_path AS cp JOIN categories AS c ON cp.id = c.parent_id
)
SELECT * FROM category_path;
 
 
-- Updating the hierarchy
 
-- Inserting a new root node
WITH parent AS (
  INSERT INTO categories(parent_id)
  VALUES (NULL)
  RETURNING id
)
UPDATE categories
SET parent_id = parent.id
FROM parent
WHERE parent_id IS NULL;
 
select * from categories;
 
-- Inserting a node between two existing nodes
WITH created_node AS (
  INSERT INTO categories(parent_id)
  VALUES (1)
  RETURNING id
)
UPDATE categories
SET parent_id = created_node.id
FROM created_node
WHERE categories.id = 10;
 
-- Adding a child node, that gets it’s siblings as children
WITH created_node AS (
  INSERT INTO categories(parent_id)
  VALUES (2)
  RETURNING id
)
UPDATE categories
SET parent_id = created_node.id
FROM created_node
WHERE categories.parent_id = 2;
 
SELECT * 
FROM categories;