CREATE TABLE UNGoals (
    id INTEGER PRIMARY KEY,
    goal_name TEXT NOT NULL,
    explanation TEXT
);
INSERT INTO UNGoals (id, goal_name, explanation) VALUES
(1, 'No Poverty', 'End poverty in all its forms everywhere.'),
(2, 'Zero Hunger', 'End hunger, achieve food security and improved nutrition and promote sustainable agriculture.'),
(3, 'Good Health and Well-being', 'Ensure healthy lives and promote well-being for all at all ages.'),
(4, 'Quality Education', 'Ensure inclusive and equitable quality education and promote lifelong learning opportunities for all.'),
(5, 'Gender Equality', 'Achieve gender equality and empower all women and girls.'),
(6, 'Clean Water and Sanitation', 'Ensure availability and sustainable management of water and sanitation for all.'),
(7, 'Affordable and Clean Energy', 'Ensure access to affordable, reliable, sustainable and modern energy for all.'),
(8, 'Decent Work and Economic Growth', 'Promote sustained, inclusive and sustainable economic growth, full and productive employment and decent work for all.'),
(9, 'Industry, Innovation and Infrastructure', 'Build resilient infrastructure, promote inclusive and sustainable industrialization and foster innovation.'),
(10, 'Reduced Inequality', 'Reduce inequality within and among countries.'),
(11, 'Sustainable Cities and Communities', 'Make cities and human settlements inclusive, safe, resilient and sustainable.'),
(12, 'Responsible Consumption and Production', 'Ensure sustainable consumption and production patterns.'),
(13, 'Climate Action', 'Take urgent action to combat climate change and its impacts.'),
(14, 'Life Below Water', 'Conserve and sustainably use the oceans, seas and marine resources for sustainable development.'),
(15, 'Life on Land', 'Protect, restore and promote sustainable use of terrestrial ecosystems, sustainably manage forests, combat desertification, and halt and reverse land degradation and halt biodiversity loss.'),
(16, 'Peace, Justice and Strong Institutions', 'Promote peaceful and inclusive societies for sustainable development, provide access to justice for all and build effective, accountable and inclusive institutions at all levels.'),
(17, 'Partnerships for the Goals', 'Strengthen the means of implementation and revitalize the global partnership for sustainable development.');
--
-- Table of classification systems
CREATE TABLE CategorySystems (
    id INTEGER PRIMARY KEY,
    system_name TEXT NOT NULL UNIQUE
);

-- Table of categories (linked to a system)
CREATE TABLE UNGoalCategories (
    id INTEGER PRIMARY KEY,
    category_name TEXT NOT NULL,
    system_id INTEGER NOT NULL,
    FOREIGN KEY (system_id) REFERENCES CategorySystems(id)
);

-- Linking table between goals and categories
CREATE TABLE GoalCategoryAssignments (
    goal_id INTEGER,
    category_id INTEGER,
    PRIMARY KEY (goal_id, category_id),
    FOREIGN KEY (goal_id) REFERENCES UNGoals(id),
    FOREIGN KEY (category_id) REFERENCES UNGoalCategories(id)
);
-- insert systems
INSERT INTO CategorySystems (id, system_name) VALUES
(1, '5 Ps'),
(2, 'Three Pillars');
-- insert categories
-- Categories for 5 Ps
INSERT INTO UNGoalCategories (id, category_name, system_id) VALUES
(1, 'People', 1),
(2, 'Planet', 1),
(3, 'Prosperity', 1),
(4, 'Peace', 1),
(5, 'Partnership', 1);

-- Categories for Three Pillars
INSERT INTO UNGoalCategories (id, category_name, system_id) VALUES
(6, 'Social', 2),
(7, 'Economic', 2),
(8, 'Environmental', 2);
-- link goals to categories
INSERT INTO GoalCategoryAssignments (goal_id, category_id) VALUES
(1, 1),  -- People
(2, 1),
(3, 1),
(4, 1),
(5, 1),
(6, 2),  -- Planet
(7, 3),  -- Prosperity
(8, 3),
(9, 3),
(10, 1),
(11, 2),
(12, 2),
(13, 2),
(14, 2),
(15, 2),
(16, 4),  -- Peace
(17, 5);  -- Partnership
-- three pillars classification
INSERT INTO GoalCategoryAssignments (goal_id, category_id) VALUES
(1, 6),  -- Social
(2, 6),
(3, 6),
(4, 6),
(5, 6),
(6, 8),  -- Environmental
(7, 7),  -- Economic
(8, 7),
(9, 7),
(10, 6),
(11, 8),
(12, 8),
(13, 8),
(14, 8),
(15, 8),
(16, 6),
(17, 7);  -- Often listed as economic, but arguably also governance
--
CREATE VIEW Goals_By_5Ps AS
SELECT 
    g.id AS goal_id,
    g.goal_name,
    c.category_name AS category,
    cs.system_name
FROM UNGoals g
JOIN GoalCategoryAssignments a ON g.id = a.goal_id
JOIN UNGoalCategories c ON a.category_id = c.id
JOIN CategorySystems cs ON c.system_id = cs.id
WHERE cs.system_name = '5 Ps';
--
CREATE VIEW Goals_By_Three_Pillars AS
SELECT 
    g.id AS goal_id,
    g.goal_name,
    c.category_name AS category,
    cs.system_name
FROM UNGoals g
JOIN GoalCategoryAssignments a ON g.id = a.goal_id
JOIN UNGoalCategories c ON a.category_id = c.id
JOIN CategorySystems cs ON c.system_id = cs.id
WHERE cs.system_name = 'Three Pillars';