-- 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
--