CREATE TABLE Characters (
    CharacterID INTEGER PRIMARY KEY autoincrement,
    Name VARCHAR(255) NOT NULL,
    RaceID INTEGER,
    Description TEXT,
    BirthDate DATE,
    DeathDate DATE,
    Height DECIMAL(5,2),
    Weight DECIMAL(5,2),
    FOREIGN KEY (RaceID) REFERENCES Races(RaceID)
);

CREATE TABLE Races (
    RaceID INTEGER PRIMARY KEY autoincrement,
    Name VARCHAR(255) NOT NULL,
    Description TEXT,
    AverageLifespan INT
);

CREATE TABLE Locations (
    LocationID INTEGER PRIMARY KEY autoincrement,
    Name VARCHAR(255) NOT NULL,
    Description TEXT,
    Climate VARCHAR(100),
    Population INT
);

CREATE TABLE Artifacts (
    ArtifactID INTEGER PRIMARY KEY autoincrement,
    Name VARCHAR(255) NOT NULL,
    Description TEXT,
    CreationDate DATE,
    Creator VARCHAR(255),
    PowerLevel INT
);

CREATE TABLE Events (
    EventID INTEGER PRIMARY KEY autoincrement,
    Name VARCHAR(255) NOT NULL,
    Description TEXT,
    Date DATE,
    LocationID INTEGER,
    FOREIGN KEY (LocationID) REFERENCES Locations(LocationID)
);

CREATE TABLE Groups (
    GroupID INTEGER PRIMARY KEY autoincrement,
    Name VARCHAR(255) NOT NULL,
    Description TEXT,
    FormationDate DATE,
    DisbandmentDate DATE
);

CREATE TABLE Languages (
    LanguageID INTEGER PRIMARY KEY autoincrement,
    Name VARCHAR(255) NOT NULL,
    Description TEXT,
    Speakers VARCHAR(255),
    Script VARCHAR(100)
);

CREATE TABLE Battles (
    BattleID INTEGER PRIMARY KEY autoincrement,
    Name VARCHAR(255) NOT NULL,
    Description TEXT,
    LocationID INTEGER,
    Date DATE,
    Outcome VARCHAR(100),
    FOREIGN KEY (LocationID) REFERENCES Locations(LocationID)
);

CREATE TABLE GroupMembers (
    GroupID INTEGER,
    CharacterID INTEGER,
    JoinDate DATE,
    LeaveDate DATE,
    Role VARCHAR(100),
    FOREIGN KEY (GroupID) REFERENCES Groups(GroupID),
    FOREIGN KEY (CharacterID) REFERENCES Characters(CharacterID),
    PRIMARY KEY (GroupID, CharacterID)
);

CREATE TABLE CharacterArtifacts (
    CharacterID INTEGER,
    ArtifactID INTEGER,
    AcquisitionDate DATE,
    LossDate DATE,
    Notes TEXT,
    FOREIGN KEY (CharacterID) REFERENCES Characters(CharacterID),
    FOREIGN KEY (ArtifactID) REFERENCES Artifacts(ArtifactID),
    PRIMARY KEY (CharacterID, ArtifactID)
);

CREATE TABLE CharacterRelationships (
    RelationshipID INTEGER PRIMARY KEY autoincrement,
    Character1ID INTEGER,
    Character2ID INTEGER,
    RelationshipType VARCHAR(100) NOT NULL,
    Description TEXT,
    StartDate DATE,
    EndDate DATE,
    FOREIGN KEY (Character1ID) REFERENCES Characters(CharacterID),
    FOREIGN KEY (Character2ID) REFERENCES Characters(CharacterID)
);


INSERT INTO Characters (Name, RaceID, Description, BirthDate, DeathDate, Height, Weight)
VALUES
('Frodo Baggins', 4, 'Hobbit from the Shire', '2968-09-22', NULL, 0.91, 50),
('Gandalf', 7, 'Wise wizard and leader of the Fellowship', 'Unknown', NULL, NULL, NULL),
('Aragorn', 1, 'Heir of Isildur and leader of the Rangers of the North', '2931-03-01', NULL, 1.85, 88),
('Legolas', 2, 'Prince of the Woodland Realm and member of the Fellowship', 'Unknown', NULL, NULL, NULL),
('Gimli', 3, 'Son of Glóin and member of the Fellowship', 'Unknown', NULL, NULL, NULL),
('Bilbo Baggins', 4, 'Uncle of Frodo Baggins and former Ring-bearer', '2890-09-22', NULL, 1.29, 45),
('Sauron', 7, 'Dark Lord of Mordor and creator of the One Ring', NULL, NULL, NULL, NULL),
('Saruman', 7, 'White Wizard and betrayer of the White Council', 'Unknown', '3019-03-15', NULL, NULL),
('Witch-king of Angmar', 1, 'Leader of the Nazgûl', NULL, NULL, NULL, NULL),
('Samwise Gamgee', 4, 'Loyal friend and companion of Frodo Baggins', '2980-04-06', NULL, 1.00, 69),
('Faramir', 1, 'Son of Denethor II and Captain of Gondor', '2983-02-20', NULL, 1.83, 75),
('Galadriel', 2, 'Lady of Lothlórien and bearer of Nenya', 'Unknown', NULL, NULL, NULL),
('Gollum', 4, 'Formerly a Hobbit named Sméagol corrupted by the One Ring', 'Unknown', NULL, NULL, NULL),
('Elrond', 2, 'Lord of Rivendell and keeper of Vilya', 'Unknown', NULL, NULL, NULL),
('Gloin', 3, 'Father of Gimli and member of Thorin''s Company', 'Unknown', NULL, NULL, NULL),
('Éowyn', 1, 'Niece of King Théoden and shieldmaiden of Rohan', '2995-12-22', NULL, 1.75, 62),
('Treebeard', 6, 'Leader of the Ents and guardian of Fangorn Forest', 'Unknown', NULL, NULL, NULL),
('Denethor II', 1, 'Steward of Gondor and father of Faramir and Boromir', '2930', '3019-03-15', NULL, NULL),
('Boromir', 1, 'Son of Denethor II and Captain of the White Tower', '2978', '3019-02-26', 1.88, 90);

INSERT INTO Races (Name, Description, AverageLifespan)
VALUES
('Human', 'Men of Middle-earth', 80),
('Elves', 'Immortal beings with great wisdom', NULL),
('Dwarf', 'Skilled craftsmen and warriors', 250),
('Hobbit', 'Small and unassuming folk of the Shire', 100),
('Orc', 'Corrupted beings bred by Sauron', 50),
('Ent', 'Shepherds of the trees', NULL),
('Maiar', 'Divine spirits', NULL);

INSERT INTO Locations (Name, Description, Climate, Population)
VALUES
('The Shire', 'Home of the Hobbits', 'Temperate', 100000),
('Rivendell', 'Elves refuge in the Misty Mountains', 'Temperate', 300),
('Mordor', 'Land of Sauron''s dark forces', 'Arid', 50000),
('Minas Tirith', 'Capital of Gondor and city of the White Tower', 'Temperate', 100000),
('Moria', 'Ancient Dwarven city in the Misty Mountains', 'Subterranean', 0),
('Lothlórien', 'Elf realm ruled by Celeborn and Galadriel', 'Temperate', 300),
('Isengard', 'Saruman''s stronghold in the Gap of Rohan', 'Temperate', 1000),
('Rohan', 'Land of the Horse-lords', 'Temperate', 50000),
('Gondor', 'The southern kingdom of Men and one of the chief realms in Middle-earth', 'Temperate', 200000);

INSERT INTO Artifacts (Name, Description, CreationDate, Creator, PowerLevel)
VALUES
('One Ring', 'The Ring of Power forged by Sauron', 'Unknown', 'Sauron', 10),
('Sting', 'Bilbo and Frodo''s Elvish dagger', NULL, NULL, 5),
('Andúril', 'The reforged sword of Elendil', '3019-03-02', 'Elves of Rivendell', 8),
('Palantír', 'Seeing-stone used for communication and scrying', 'Unknown', 'Elves of Aman', 7),
('Phial of Galadriel', 'A crystal phial with the light of Eärendil''s star', 'Unknown', 'Galadriel', 6),
('Nenya', 'The Ring of Adamant wielded by Galadriel', 'Unknown', 'Celebrimbor', 9),
('Orcrist', 'Sword of Thorin Oakenshield found in a troll-hoard', 'Unknown', 'Elves of Gondolin', 8),
('Durin''s Axe', 'Axe of Durin VII wielded by Thorin Oakenshield', 'Unknown', 'Dwarves of Erebor', 7),
('Hadhafang', 'Sword of Arwen Evenstar', 'Unknown', 'Elrond', 6),
('Mithril Coat', 'Chainmail shirt made of mithril', 'Unknown', 'Dwarves of Khazad-dûm', 9);

INSERT INTO Events (Name, Description, Date, LocationID)
VALUES
('Council of Elrond', 'Meeting to discuss the fate of the One Ring', '3018-10-25', 2),
('Battle of Helm''s Deep', 'Pivotal battle during the War of the Ring', '3019-03-03', 7),
('Battle of Pelennor Fields', 'Largest battle of the War of the Ring', '3019-03-15', 4),
('Fall of Gondolin', 'The last stand of the hidden city against Morgoth', 'Unknown', NULL),
('Destruction of the One Ring', 'The climax of the War of the Ring', '3019-03-25', 3),
('Battle of the Black Gate', 'Final battle of the War of the Ring', '3019-03-25', 3),
('Sacking of Erebor', 'Smaug''s attack on the Lonely Mountain', '2941', NULL),
('Fall of Númenor', 'The island kingdom''s destruction', '3319', NULL),
('Battle of Dagorlad', 'Alliance of Elves and Men against Sauron', '3434', 3),
('Flight to the Ford', 'Frodo and the hobbits pursued by Ringwraiths', '3018', NULL),
('Discovery of the Mithril Coat', 'Mithril Coat discovered in Moria', '3018-10-25', 5),
('Battle of Pelennor Fields', 'Largest battle of the War of the Ring', '3019-03-15', 4);

INSERT INTO Groups (Name, Description, FormationDate, DisbandmentDate)
VALUES
('Fellowship of the Ring', 'Group formed to destroy the One Ring', '3018-12-25', '3019-03-25'),
('White Council', 'Council of the Wise', 'Unknown', NULL),
('Nazgûl', 'Sauron''s ringwraiths seeking the One Ring', 'Unknown', NULL),
('Rangers of the North', 'Protectors of the realm of Arnor', 'Unknown', NULL),
('Rangers of Ithilien', 'Gondor''s rangers protecting the borders of Mordor', 'Unknown', NULL),
('Eagles of Manwë', 'Giant eagles aiding in the War of the Ring', 'Unknown', NULL),
('Thorin''s Company', 'Dwarves accompanying Thorin Oakenshield on the quest to Erebor', 'Unknown', NULL),
('Uruk-hai', 'Saruman''s crossbred orcs and men', 'Unknown', NULL),
('Army of the Dead', 'Undead warriors sworn to Isildur''s oath', 'Unknown', NULL);

INSERT INTO Languages (Name, Description, Speakers, Script)
VALUES
('Westron', 'Common language of Middle-earth', 'Humans, Hobbits, Dwarves, Elves', 'Latin script'),
('Quenya', 'High Elven language', 'Elves of Valinor', 'Tengwar script'),
('Khuzdul', 'Dwarvish language', 'Dwarves', 'Cirth script'),
('Black Speech', 'Language of Mordor', 'Orcs, Trolls', 'Cirth script'),
('Rohirric', 'Language of the Rohirrim', 'Rohirrim', 'Anglo-Saxon runes'),
('Adûnaic', 'Language of Númenor', 'Númenóreans', 'Cirth script'),
('Valarin', 'Language of the Ainur', 'Valar, Maiar', 'Unknown'),
('Entish', 'Language of the Ents', 'Ents', 'Unknown');

INSERT INTO Battles (Name, Description, LocationID, Date, Outcome)
VALUES
('Battle of the Last Alliance', 'Alliance of Elves and Men against Sauron', 3, '3430-3441', 'Victory'),
('Battle of Five Armies', 'Dwarves, Elves, Men, and Goblins clash near Erebor', NULL, '2941', 'Undecided'),
('Siege of Gondor', 'Sauron''s assault on the city of Gondor', 9, '3019-03-15', 'Defeat for Sauron'),
('Battle of Helm''s Deep', 'Defensive battle against Saruman''s forces', 7, '3019-03-03', 'Victory for Rohan'),
('Battle of the Black Gate', 'Final battle of the War of the Ring', 3, '3019-03-25', 'Victory'),
('Battle of Dagorlad', 'Alliance of Elves and Men against Sauron', 3, '3434', 'Victory'),
('Battle of the Pelennor Fields', 'Largest battle of the War of the Ring', 4, '3019-03-15', 'Victory'),
('Fall of Númenor', 'The island kingdom''s destruction', NULL, '3319', 'Destruction'),
('Flight to the Ford', 'Frodo and the hobbits pursued by Ringwraiths', NULL, '3018', 'Escape'),
('Battle of the Morannon', 'The last battle of the War of the Ring', 3, '3019-03-25', 'Victory'),
('Battle of Azanulbizar', 'Dwarves battle orcs in the War of the Dwarves and Orcs', NULL, '2799', 'Victory'),
('Dagor-nuin-Giliath', 'First battle between Elves and Orcs in the War of the Jewels', NULL, '1 FA', 'Victory'),
('Battle of the Hornburg', 'Rohan defends Helm''s Deep against Saruman''s forces', 7, '3019-03-03', 'Victory'),
('Destruction of Isengard', 'Ents attack Isengard and overthrow Saruman', 7, '3019-03-03', 'Victory for the Ents'),
('Sack of Eregion', 'Sauron attacks the Elven realm of Eregion', NULL, '1697 SA', 'Defeat for the Elves'),
('Siege of Barad-dûr', 'The siege of Sauron''s fortress during the War of the Last Alliance', 3, '3441 SA', 'Victory for the Elves'),
('War of the Elves and Sauron', 'Conflict between Sauron and the Elves of Middle-earth', NULL, '1693-1701 SA', 'Stalemate'),
('Battle of the Gwathló', 'Final battle of the War of the Elves and Sauron', NULL, '1700 SA', 'Victory for the Elves'),
('First Battle of Beleriand', 'Morgoth attacks the Sindar Elves', NULL, '455 FA', 'Victory for the Elves'),
('Battle of Sudden Flame', 'Morgoth breaks the Siege of Angband', NULL, '455 FA', 'Victory for Morgoth');

INSERT INTO GroupMembers (GroupID, CharacterID, JoinDate, LeaveDate, Role)
VALUES
(1, 1, '3018-12-25', '3019-03-25', 'Ring-bearer'),
(1, 2, '3018-12-25', '3019-03-25', 'Guide'),
(1, 3, '3018-12-25', '3019-03-25', 'Protector'),
(1, 4, '3018-12-25', '3019-03-25', 'Member'),
(1, 5, '3018-12-25', '3019-03-25', 'Member'),
(1, 10, '3018-12-25', '3019-03-25', 'Member'),
(1, 19, '3018-12-25', '3019-02-26', 'Member'),
(2, 2, 'Unknown', NULL, 'Member'),
(2, 8, 'Unknown', '3019-03-15', 'Member'),
(2, 12, 'Unknown', NULL, 'Member'),
(2, 14, 'Unknown', NULL, 'Member'),
(3, 7, 'Unknown', NULL, 'Leader'),
(3, 9, 'Unknown', NULL, 'Member'),
(4, 3, 'Unknown', NULL, 'Leader'),
(5, 11, 'Unknown', NULL, 'Leader'),
(7, 6, 'Unknown', NULL, 'Member'),
(7, 15, 'Unknown', NULL, 'Member');

INSERT INTO CharacterArtifacts (CharacterID, ArtifactID, AcquisitionDate, LossDate, Notes)
VALUES
(1, 1, '3018-12-25', NULL, 'Given by Bilbo in Rivendell.'),
(1, 2, 'Unknown', NULL, 'Given by Bilbo before his journey to Rivendell.'),
(2, 4, 'Unknown', NULL, 'Acquired during travels.'),
(3, 3, '3019-03-02', NULL, 'Gifted by Elrond after the Council of Elrond.'),
(4, 7, 'Unknown', NULL, 'Found in a troll-hoard.'),
(4, 10, 'Unknown', NULL, 'Gifted by Arwen Evenstar.'),
(5, 7, 'Unknown', NULL, 'Found in a troll-hoard.'),
(5, 9, 'Unknown', NULL, 'Gifted by Galadriel.'),
(6, 2, 'Unknown', NULL, 'Found in the goblin tunnels of the Misty Mountains.'),
(7, 1, 'Unknown', NULL, 'Forged by himself.'),
(8, 4, 'Unknown', NULL, 'Stolen from Isengard.'),
(9, 1, 'Unknown', NULL, 'Servant of Sauron, wields the Ring of Power.'),
(10, 1, '3018-12-25', NULL, 'Given by Frodo in Mordor.'),
(10, 2, 'Unknown', NULL, 'Received from Frodo in Mordor.'),
(11, 3, 'Unknown', NULL, 'Gifted by Aragorn after the War of the Ring.'),
(12, 5, 'Unknown', NULL, 'Given by Galadriel in Lothlórien.'),
(12, 6, 'Unknown', NULL, 'Bearer of the Ring of Adamant.'),
(13, 1, 'Unknown', NULL, 'Stolen by Bilbo Baggins.'),
(14, 3, 'Unknown', NULL, 'Keeper of Andúril.'),
(14, 9, 'Unknown', NULL, 'Owner of Hadhafang.'),
(15, 7, 'Unknown', NULL, 'Found in a troll-hoard.'),
(17, 6, 'Unknown', NULL, 'Given by Galadriel in Fangorn Forest.'),
(18, 8, 'Unknown', NULL, 'Inherited from his ancestors.'),
(19, 3, '3019-03-02', '3019-02-26', 'Inherited from his ancestors.'),
(1, 10, '3018-12-25', NULL, 'Gifted by Galadriel.');

INSERT INTO CharacterRelationships (Character1ID, Character2ID, RelationshipType, Description, StartDate, EndDate)
VALUES
(1, 10, 'Friendship', 'Frodo and Sam are best friends and companions.', NULL, NULL),
(2, 1, 'Mentorship', 'Gandalf mentors Frodo in his quest to destroy the One Ring.', NULL, NULL),
(3, 19, 'Brotherhood', 'Boromir and Aragorn have a mutual respect and brotherhood in the Fellowship.', NULL, '3019-02-26'),
(4, 5, 'Friendship', 'Legolas and Gimli become close friends despite initial distrust.', NULL, NULL),
(6, 1, 'Family', 'Bilbo is Frodo’s uncle.', NULL, NULL),
(8, 9, 'Betrayal', 'Saruman betrays the White Council and allies with Sauron.', NULL, '3019-03-15'),
(11, 18, 'Family', 'Faramir is the son of Denethor II.', NULL, '3019-03-15'),
(14, 2, 'Alliance', 'Elrond is part of the White Council with Gandalf.', NULL, NULL),
(3, 16, 'Romantic', 'Aragorn and Éowyn have a mutual affection.', NULL, NULL),
(17, 5, 'Alliance', 'Treebeard and Gimli ally against Saruman.', NULL, NULL),
(10, 13, 'Servitude', 'Gollum serves as a guide to Sam.', NULL, NULL),
(19, 18, 'Family', 'Boromir is the son of Denethor II.', NULL, '3019-02-26'),
(1, 13, 'Distrust', 'Frodo is wary of Gollum.', NULL, NULL),
(2, 8, 'Rivalry', 'Gandalf and Saruman have a conflict of ideologies.', NULL, '3019-03-15'),
(11, 19, 'Sibling', 'Faramir and Boromir are brothers.', NULL, '3019-02-26'),
(12, 14, 'Alliance', 'Galadriel and Elrond are both bearers of Elven rings.', NULL, NULL),
(1, 3, 'Alliance', 'Frodo and Aragorn are allies in the Fellowship.', NULL, NULL),
(9, 7, 'Servitude', 'The Witch-king of Angmar serves Sauron.', NULL, NULL),
(2, 12, 'Alliance', 'Gandalf and Galadriel are members of the White Council.', NULL, NULL);


-- WITH CharacterArtifactCount AS (
--     SELECT 
--         CharacterID,
--         COUNT(ArtifactID) AS artifact_count
--     FROM 
--         CharacterArtifacts
--     GROUP BY 
--         CharacterID
-- ),
-- MaxArtifactCount AS (
--     SELECT 
--         MAX(artifact_count) AS max_artifacts
--     FROM 
--         CharacterArtifactCount
-- )
-- SELECT 
--     c.Name,
--     cac.artifact_count
-- FROM 
--     Characters c
-- JOIN 
--     CharacterArtifactCount cac ON c.CharacterID = cac.CharacterID
-- JOIN 
--     MaxArtifactCount mac ON cac.artifact_count = mac.max_artifacts
-- ORDER BY c.Name ASC;

-- WITH GroupMemberCount AS (
--     SELECT 
--         GroupID,
--         COUNT(CharacterID) AS member_count
--     FROM 
--         GroupMembers
--     GROUP BY 
--         GroupID
-- ),
-- MaxMemberCount AS (
--     SELECT 
--         MAX(member_count) AS max_members
--     FROM 
--         GroupMemberCount
-- )
-- SELECT 
--     g.GroupID,
--     g.Name AS GroupName,
--     gmc.member_count
-- FROM 
--     Groups g
-- JOIN 
--     GroupMemberCount gmc ON g.GroupID = gmc.GroupID
-- JOIN 
--     MaxMemberCount mmc ON gmc.member_count = mmc.max_members;

-- WITH MembershipDuration AS (
--     SELECT 
--         gm.CharacterID,
--         gm.GroupID,
--         (julianday(gm.LeaveDate) - julianday(gm.JoinDate)) / 365.25 AS duration
--     FROM 
--         GroupMembers gm
--     WHERE 
--         gm.JoinDate IS NOT NULL AND gm.LeaveDate IS NOT NULL
-- ),
-- LongestMembership AS (
--     SELECT 
--         CharacterID,
--         GroupID,
--         MAX(duration) AS max_duration
--     FROM 
--         MembershipDuration
--     GROUP BY 
--         CharacterID, GroupID
-- )
-- SELECT 
--     c.CharacterID,
--     c.Name,
--     lm.GroupID,
--     g.Name AS GroupName,
--     lm.max_duration
-- FROM 
--     Characters c
-- JOIN 
--     LongestMembership lm ON c.CharacterID = lm.CharacterID
-- JOIN 
--     Groups g ON lm.GroupID = g.GroupID
-- ORDER BY 
--     lm.max_duration DESC;

-- WITH CharacterGroupCount AS (
--     SELECT 
--         gm.CharacterID,
--         COUNT(gm.GroupID) AS group_count
--     FROM 
--         GroupMembers gm
--     GROUP BY 
--         gm.CharacterID
-- ),
-- MultipleGroupMembers AS (
--     SELECT 
--         CharacterID,
--         group_count
--     FROM 
--         CharacterGroupCount
--     WHERE 
--         group_count > 1
-- )
-- SELECT 
--     c.CharacterID,
--     c.Name,
--     mgm.group_count
-- FROM 
--     Characters c
-- JOIN 
--     MultipleGroupMembers mgm ON c.CharacterID = mgm.CharacterID
-- ORDER BY 
--     mgm.group_count DESC;

-- Characer Relationships
-- WITH RelationshipDurations AS (
--     SELECT
--         cr.Character1ID,
--         c1.Name AS Character1Name,
--         cr.Character2ID,
--         c2.Name AS Character2Name,
--         cr.RelationshipType,
--         (julianday(cr.EndDate) - julianday(cr.StartDate)) / 365.25 AS duration_years
--     FROM
--         CharacterRelationships cr
--     JOIN
--         Characters c1 ON cr.Character1ID = c1.CharacterID
--     JOIN
--         Characters c2 ON cr.Character2ID = c2.CharacterID
--     WHERE
--         cr.StartDate IS NOT NULL AND cr.EndDate IS NOT NULL
-- )
-- SELECT
--     Character1ID,
--     Character1Name,
--     RelationshipType,
--     duration_years,
--     Character2ID,
--     Character2Name
-- FROM
--     RelationshipDurations
-- ORDER BY
--     duration_years DESC;

-- WITH GroupArtifacts AS (
--     SELECT
--         gm.GroupID,
--         g.Name AS GroupName,
--         ca.ArtifactID,
--         a.Name AS ArtifactName
--     FROM
--         GroupMembers gm
--     JOIN
--         Characters c ON gm.CharacterID = c.CharacterID
--     JOIN
--         CharacterArtifacts ca ON c.CharacterID = ca.CharacterID
--     JOIN
--         Artifacts a ON ca.ArtifactID = a.ArtifactID
--     JOIN
--         Groups g ON gm.GroupID = g.GroupID
-- ),
-- GroupArtifactCount AS (
--     SELECT
--         GroupID,
--         GroupName,
--         COUNT(ArtifactID) AS artifact_count,
--         GROUP_CONCAT(DISTINCT ArtifactName) AS artifacts
--     FROM
--         GroupArtifacts
--     GROUP BY
--         GroupID, GroupName
-- )
-- SELECT
--     GroupID,
--     GroupName,
--     artifact_count,
--     artifacts
-- FROM
--     GroupArtifactCount
-- ORDER BY
--     artifact_count DESC;

-- WITH RaceArtifactPower AS (
--     SELECT
--         c.RaceID,
--         r.Name AS RaceName,
--         a.PowerLevel
--     FROM
--         Characters c
--     JOIN
--         CharacterArtifacts ca ON c.CharacterID = ca.CharacterID
--     JOIN
--         Artifacts a ON ca.ArtifactID = a.ArtifactID
--     JOIN
--         Races r ON c.RaceID = r.RaceID
-- ),
-- AverageRacePower AS (
--     SELECT
--         RaceID,
--         RaceName,
--         AVG(PowerLevel) AS avg_power_level
--     FROM
--         RaceArtifactPower
--     GROUP BY
--         RaceID, RaceName
-- )
-- SELECT
--     RaceID,
--     RaceName,
--     avg_power_level
-- FROM
--     AverageRacePower
-- ORDER BY
--     avg_power_level DESC;

-- WITH ArtifactPower AS (
--     SELECT
--         ca.CharacterID,
--         c.Name AS CharacterName,
--         ca.ArtifactID,
--         a.Name AS ArtifactName,
--         a.PowerLevel
--     FROM
--         CharacterArtifacts ca
--     JOIN
--         Characters c ON ca.CharacterID = c.CharacterID
--     JOIN
--         Artifacts a ON ca.ArtifactID = a.ArtifactID
-- )
-- SELECT
--     CharacterID,
--     CharacterName,
--     ArtifactName,
--     PowerLevel
-- FROM
--     ArtifactPower
-- ORDER BY
--     PowerLevel DESC
-- LIMIT 1;

-- WITH CreatorPower AS (
--     SELECT
--         a.Creator,
--         a.PowerLevel,
--         a.Name AS ArtifactName
--     FROM
--         Artifacts a
--     WHERE
--         a.Creator IS NOT NULL
-- ),
-- AverageCreatorPower AS (
--     SELECT
--         Creator,
--         AVG(PowerLevel) AS avg_power_level,
--         GROUP_CONCAT(ArtifactName) AS artifact_names
--     FROM
--         CreatorPower
--     GROUP BY
--         Creator
-- )
-- SELECT
--     Creator,
--     avg_power_level,
--     artifact_names
-- FROM
--     AverageCreatorPower
-- ORDER BY
--     avg_power_level DESC;

-- WITH RaceArtifacts AS (
--     SELECT r.RaceID, r.Name AS RaceName, a.Name AS ArtifactName, a.PowerLevel
--     FROM Characters ch
--     JOIN CharacterArtifacts ca ON ch.CharacterID = ca.CharacterID
--     JOIN Artifacts a ON ca.ArtifactID = a.ArtifactID
--     JOIN Races r ON ch.RaceID = r.RaceID
-- ),
-- MaxPowerArtifacts AS (
--     SELECT RaceID, MAX(PowerLevel) AS MaxPowerLevel
--     FROM RaceArtifacts
--     GROUP BY RaceID
-- )
-- SELECT ra.RaceName, ra.ArtifactName, ra.PowerLevel
-- FROM RaceArtifacts ra
-- JOIN MaxPowerArtifacts mpa ON ra.RaceID = mpa.RaceID AND ra.PowerLevel = mpa.MaxPowerLevel
-- ORDER BY ra.RaceName;

-- Calculate lifespans only for characters with known birth and death dates
WITH Lifespans AS (
    SELECT ch.Name AS CharacterName, r.Name AS RaceName,
           julianday(ch.DeathDate) - julianday(ch.BirthDate) AS Lifespan
    FROM Characters ch
    JOIN Races r ON ch.RaceID = r.RaceID
    WHERE ch.BirthDate IS NOT NULL AND ch.DeathDate IS NOT NULL
)
SELECT CharacterName, RaceName, Lifespan
FROM Lifespans
WHERE Lifespan = (SELECT MAX(Lifespan) FROM Lifespans)
   OR Lifespan = (SELECT MIN(Lifespan) FROM Lifespans);