create table graph (
  id text primary key,
  name text
);

create table node (
  id text primary key,
  graph_id text,
  x real,
  y real,
  "value" integer,
  foreign key (graph_id) references graph (id)
);

create table edge (
  id text primary key,
  from_id text,
  to_id text,
  "value" integer,
  foreign key (from_id) references node (id),
  foreign key (to_id) references node (id)
);

create table face (
  id text primary key,
  color text
);

create table etof (
  face_id text,
  edge_id text,
  primary key (face_id, edge_id),
  foreign key (face_id) references face (id),
  foreign key (edge_id) references edge (id)
);

-- Inserting into graph table
INSERT INTO graph (id, name) VALUES 
('g1', 'Graph One'), 
('g2', 'Another Graph'), 
('g3', 'My Special Graph');

-- Inserting into node table
INSERT INTO node (id, graph_id, x, y, value) VALUES 
('n1', 'g1', 1, 2, 10),
('n2', 'g1', 3, 4, 20),
('n3', 'g1', 5, 6, 30),
('n4', 'g2', 2, 3, 40),
('n5', 'g2', 4, 5, 50),
('n6', 'g2', 6, 7, 60),
('n7', 'g3', 11, 12, 70),
('n8', 'g3', 13, 14, 80),
('n9', 'g3', 15, 16, 90),
('n10', 'g3', 12.5, 13.5, 100),
('n11', 'g3', 14.5, 15.5, 110);

-- Inserting into edge table
INSERT INTO edge (id, from_id, to_id, value) VALUES 
('e1', 'n1', 'n2', 1),
('e2', 'n2', 'n3', 2),
('e3', 'n4', 'n5', 3),
('e4', 'n5', 'n6', 4),
('e5', 'n7', 'n8', 5),
('e6', 'n7', 'n9', 6),
('e7', 'n9', 'n10', 7),
('e8', 'n9', 'n11', 8),
('e9', 'n10', 'n11', 5);

-- Inserting into face table
INSERT INTO face (id, color) VALUES
('f1', 'red'),
('f2', 'blue'),
('f3', 'yellow'),
('f4', 'green'),
('f5', 'purple'),
('f6', 'brown'),
('f7', 'red'),
('f8', 'green'),
('f9', 'blue'),
('f10', 'yellow'),
('f11', 'purple'),
('f12', 'brown');

-- Inserting into etof table
INSERT INTO etof (face_id, edge_id) VALUES
('f1', 'e1'),
('f1', 'e2'),
('f2', 'e3'),
('f2', 'e4'),
('f3', 'e5'),
('f3', 'e6');

-- For a given graph, identify the shortest path between n9 and n10 in terms of the cumulative value of edges traversed.

-- WITH RECURSIVE PathFinder AS (
--   -- Initialization step: start from the start node
--   SELECT 
--     e.from_id,
--     e.to_id,
--     e.id AS edge_id,
--     e.value,
--     e.value AS total_value  -- Cumulative value of the path
--   FROM edge e
--   WHERE e.from_id = 'n9'
--   UNION ALL
--   -- Recursive step: expand to adjacent nodes
--   SELECT 
--     pf.from_id,
--     e.to_id,
--     e.id,
--     e.value,
--     pf.total_value + e.value
--   FROM PathFinder pf
--   JOIN edge e ON pf.to_id = e.from_id
--   WHERE e.to_id <> 'n9'  -- Prevent looping back to start
-- )
-- SELECT 
--   total_value
-- FROM PathFinder
-- WHERE to_id = 'n10'
-- ORDER BY total_value ASC
-- LIMIT 1;

-- Find the nodes with minimum and maximum value for each graph. Order the results by Graph ID. Show the Max value followed by the Min value for each graph.

-- Display the Node ID, Graph ID, Value, "Max" or "Min" depending on what the value is. (Eg - n1|g1|5|Min). 

-- WITH RankedValues AS (
--   SELECT
--     n.id AS node_id,
--     n.graph_id,
--     n.value,
--     DENSE_RANK() OVER (PARTITION BY n.graph_id ORDER BY n.value DESC) AS rank_desc,
--     DENSE_RANK() OVER (PARTITION BY n.graph_id ORDER BY n.value ASC) AS rank_asc
--   FROM node n
-- )
-- SELECT
--   rv.node_id,
--   rv.graph_id,
--   rv.value,
--   CASE
--     WHEN rv.rank_desc = 1 THEN 'Max'
--     WHEN rv.rank_asc = 1 THEN 'Min'
--     ELSE NULL
--   END AS MaxMin
-- FROM RankedValues rv
-- WHERE rv.rank_desc = 1 OR rv.rank_asc = 1
-- ORDER BY rv.graph_id;

-- -- Number of unique color faces in each graph
-- SELECT 
--   graph_id, 
--   COUNT(DISTINCT color) as unique_color_count
-- FROM (
--   SELECT 
--     n.graph_id, 
--     f.color
--   FROM face f
--   JOIN etof e ON f.id = e.face_id
--   JOIN edge ed ON e.edge_id = ed.id
--   JOIN node n ON ed.from_id = n.id OR ed.to_id = n.id
--   GROUP BY n.graph_id, f.color
-- ) grouped_data
-- GROUP BY graph_id
-- HAVING COUNT(*) = COUNT(DISTINCT color);

-- Cyclic Graph
-- WITH NodeInDegree AS (
--   SELECT
--     to_id,
--     COUNT(*) AS InDegreeCount
--   FROM edge
--   GROUP BY to_id
-- )
-- SELECT
--   g.name AS graph_name,
--   CASE
--     WHEN MAX(nid.InDegreeCount) > 1 THEN 'True'
--     ELSE 'False'
--   END AS CycleStatus
-- FROM graph g
-- LEFT JOIN node n ON g.id = n.graph_id
-- LEFT JOIN NodeInDegree nid ON n.id = nid.to_id
-- GROUP BY g.id;

-- Edges = Nodes - 1
-- SELECT
--   g.id AS graph_id,
--   CASE 
--     WHEN COUNT(DISTINCT e.id) = COUNT(DISTINCT n.id) - 1 THEN 'True' 
--     ELSE 'False' 
--   END AS IsTree
-- FROM
--   graph g
-- LEFT JOIN node n ON g.id = n.graph_id
-- LEFT JOIN edge e ON n.id = e.from_id OR n.id = e.to_id
-- GROUP BY g.id
-- ORDER BY g.id DESC;

-- List edges above the average value for that graph
-- WITH EdgeGraphs AS (
--   SELECT
--     e.id AS edge_id,
--     e.value,
--     (SELECT n.graph_id FROM node n WHERE n.id = e.from_id OR n.id = e.to_id LIMIT 1) AS graph_id
--   FROM edge e
-- ),
-- AvgEdgeValues AS (
--   SELECT
--     eg.graph_id,
--     AVG(eg.value) AS avg_graph_edge_value
--   FROM EdgeGraphs eg
--   GROUP BY eg.graph_id
-- )
-- SELECT
--   eg.edge_id,
--   eg.graph_id,
--   eg.value AS edge_value,
--   aev.avg_graph_edge_value
-- FROM EdgeGraphs eg
-- JOIN AvgEdgeValues aev ON eg.graph_id = aev.graph_id
-- WHERE eg.value > aev.avg_graph_edge_value
-- ORDER BY aev.avg_graph_edge_value;

-- WITH NodeEdgeValues AS (
--     SELECT
--         n.graph_id,
--         n.id AS node_id,
--         COALESCE(SUM(n.value), 0) + COALESCE(SUM(e.value), 0) AS total_value
--     FROM node n
--     LEFT JOIN edge e ON e.from_id = n.id OR e.to_id = n.id
--     GROUP BY n.graph_id, n.id
-- ),
-- GraphTotalValues AS (
--     SELECT
--         n.graph_id,
--         SUM(n.value) + COALESCE(SUM(e.value), 0) AS total_graph_value
--     FROM node n
--     LEFT JOIN edge e ON e.from_id = n.id OR e.to_id = n.id
--     GROUP BY n.graph_id
-- ),
-- RemovalEffects AS (
--     SELECT
--         g.graph_id,
--         nev.node_id,
--         (g.total_graph_value - nev.total_value) AS remaining_value,
--         RANK() OVER (PARTITION BY g.graph_id ORDER BY (g.total_graph_value - nev.total_value)) AS value_rank
--     FROM GraphTotalValues g
--     JOIN NodeEdgeValues nev ON nev.graph_id = g.graph_id
-- )
-- SELECT
--     re.graph_id,
--     re.node_id
-- FROM RemovalEffects re
-- WHERE re.value_rank = 1
-- ORDER BY re.graph_id;