CREATE TABLE celestial_body(
  body_id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT,
  type TEXT,
  discovery_date DATE,
  mass REAL,
  gravity REAL,
  description TEXT
);

CREATE TABLE composition(
  composition_id INTEGER PRIMARY KEY AUTOINCREMENT,
  body_id INTEGER,
  element_id INTEGER,
  percentage REAL,
  FOREIGN KEY (body_id) REFERENCES celestial_body(body_id),
  FOREIGN KEY (element_id) REFERENCES element(element_id)
);

CREATE TABLE discovery(
  discovery_id INTEGER PRIMARY KEY AUTOINCREMENT,
  exploration_id INTEGER,
  description TEXT,
  significance TEXT,
  FOREIGN KEY (exploration_id) REFERENCES exploration(exploration_id)
);

CREATE TABLE element(
  element_id INTEGER PRIMARY KEY AUTOINCREMENT,
  symbol TEXT,
  name TEXT,
  atomic_num INTEGER,
  atomic_weight REAL,
  is_radioactive INTEGER DEFAULT 0
);

CREATE TABLE exploration(
  exploration INTEGER PRIMARY KEY AUTOINCREMENT,
  mission_id INTEGER,
  body_id INTEGER,
  arrival_date DATE,
  departure_date DATE,
  findings TEXT,
  FOREIGN KEY (mission_id) REFERENCES mission(mission_id),
  FOREIGN KEY (body_id) REFERENCES celestial_body(body_id)
);

CREATE TABLE mission(
  mission_id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT,
  spacecraft_id INTEGER,
  objective TEXT,
  start_date DATE,
  end_date DATE,
  outcome TEXT,
  description TEXT,
  FOREIGN KEY (spacecraft_id) REFERENCES spacecraft(spacecraft_id)
);

CREATE TABLE spacecraft(
  spacecraft_id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT,
  type TEXT,
  launch_date DATE,
  mission_duration INTEGER,
  status TEXT
);

CREATE TABLE spacecraft_element(
  spacecraft_id INTEGER,
  element_id INTEGER,
  quantity REAL,
  PRIMARY KEY (spacecraft_id, element_id)
  FOREIGN KEY (spacecraft_id) REFERENCES spacecraft(spacecraft_id),
  FOREIGN KEY (element_id) REFERENCES element(element_id)
);

INSERT INTO "celestial_body" ("body_id", "name", "type", "discovery_date", "mass", "gravity", "description") VALUES
('1', 'Sun', 'Star', NULL, '1.989e+30', '274.0', 'The star at the center of the Solar System.'),
('2', 'Mercury', 'Planet', NULL, '3.301e+23', '3.7', 'The smallest planet in the Solar System, closest to the Sun.'),
('3', 'Venus', 'Planet', NULL, '4.867e+24', '8.87', 'The second planet from the Sun, known for its dense atmosphere.'),
('4', 'Earth', 'Planet', NULL, '5.972e+24', '9.8', 'Our home planet, the third from the Sun.'),
('5', 'Mars', 'Planet', NULL, '6.39e+23', '3.72', 'The fourth planet from the Sun, known for its reddish appearance.'),
('6', 'Jupiter', 'Planet', NULL, '1.898e+27', '24.79', 'The largest planet in the Solar System, a gas giant.'),
('7', 'Saturn', 'Planet', NULL, '5.683e+26', '10.44', 'The sixth planet from the Sun, known for its ring system.'),
('8', 'Uranus', 'Planet', '1781-03-13', '8.681e+25', '8.87', 'The seventh planet from the Sun, an ice giant.'),
('9', 'Neptune', 'Planet', '1846-09-23', '1.024e+26', '11.15', 'The eighth and farthest known planet from the Sun.'),
('10', 'Moon', 'Natural Satellite', NULL, '7.342e+22', '1.62', 'Earth''s only natural satellite.'),
('11', 'Phobos', 'Natural Satellite', '1877-08-18', '1.065e+16', '0.0058', 'A small and irregularly shaped moon of Mars.'),
('12', 'Deimos', 'Natural Satellite', '1877-08-12', '1.476e+15', '0.003', 'Another small moon of Mars.'),
('13', 'Io', 'Natural Satellite', '1610', '8.93e+22', '1.796', 'A moon of Jupiter with active volcanoes.'),
('14', 'Europa', 'Natural Satellite', '1610', '4.79e+22', '1.314', 'A moon of Jupiter with a potential subsurface ocean.'),
('15', 'Ganymede', 'Natural Satellite', '1610', '1.482e+23', '1.428', 'The largest moon in the Solar System, orbiting Jupiter.'),
('16', 'Callisto', 'Natural Satellite', '1610', '1.076e+23', '1.235', 'Another moon of Jupiter, known for its ancient surface.'),
('17', 'Titan', 'Natural Satellite', '1655', '1.345e+23', '1.35', 'The largest moon of Saturn with a dense atmosphere.'),
('18', 'Enceladus', 'Natural Satellite', '1789', '1.08e+20', '0.114', 'A moon of Saturn with geysers and potential subsurface ocean.'),
('19', 'Triton', 'Natural Satellite', '1846', '2.14e+22', '0.78', 'A large moon of Neptune with a retrograde orbit.'),
('20', 'Eris', 'Dwarf Planet', '2005-01-05', '1.66e+22', '0.82', 'A dwarf planet in the Kuiper belt, similar in size to Pluto.'),
('21', 'Ceres', 'Dwarf Planet', '1801-01-01', '9.393e+20', '0.27', 'The largest object in the asteroid belt, classified as a dwarf planet.');

INSERT INTO "composition" ("composition_id", "body_id", "element_id", "percentage") VALUES
('1', '4', '1', '0.05'),
('2', '4', '6', '13.01'),
('3', '4', '7', '54.98'),
('4', '4', '8', '20.9'),
('5', '4', '13', '8.1'),
('6', '4', '14', '2.0'),
('7', '4', '19', '0.4'),
('8', '4', '25', '0.5'),
('9', '4', '28', '0.06'),
('10', '5', '1', '0.15'),
('11', '5', '6', '2.1'),
('12', '5', '7', '65.0'),
('13', '5', '8', '10.9'),
('14', '5', '13', '7.6'),
('15', '5', '14', '15.2'),
('16', '5', '25', '4.1'),
('17', '6', '1', '86.0'),
('18', '6', '6', '0.6'),
('19', '6', '7', '3.5'),
('20', '6', '8', '5.4'),
('21', '6', '9', '0.1'),
('22', '6', '10', '2.0'),
('23', '6', '13', '0.15'),
('24', '6', '14', '0.4'),
('25', '7', '1', '92.0'),
('26', '7', '6', '1.2'),
('27', '7', '7', '2.1'),
('28', '7', '8', '0.52'),
('29', '7', '10', '0.58'),
('30', '7', '13', '2.6'),
('31', '7', '14', '2.0'),
('32', '8', '1', '82.5'),
('33', '8', '6', '1.6'),
('34', '8', '7', '2.2'),
('35', '8', '8', '3.1'),
('36', '8', '11', '1.2'),
('37', '8', '14', '5.2'),
('38', '9', '1', '74.0'),
('39', '9', '6', '0.9'),
('40', '9', '7', '1.7'),
('41', '9', '8', '2.6'),
('42', '9', '12', '2.4'),
('43', '9', '14', '3.7'),
('44', '9', '33', '1.1');

INSERT INTO "discovery" ("discovery_id", "exploration_id", "description", "significance") VALUES
('1', '1', 'First manned mission to land on the Moon.', 'Major milestone in space exploration, expanding our understanding of the Moon and our capabilities.'),
('2', '2', 'Interstellar space exploration beyond the Solar System.', 'Provides valuable data about interstellar space, cosmic rays, and plasma, advancing our knowledge of the universe beyond our solar system.'),
('3', '3', 'Long-term presence of international crews on the ISS.', 'Facilitates international collaboration, enabling continuous scientific experiments and advancements in space research.'),
('4', '4', 'Study of Pluto and its moons, revealing ice and potential past oceans.', 'Enhanced our understanding of dwarf planets and the outer reaches of the Solar System, providing insights into planetary geology and potential habitability.'),
('5', '5', 'Capture of detailed images and spectra of distant celestial objects.', 'Improved our knowledge of distant galaxies, black holes, and the early universe, contributing to astrophysics and cosmology.'),
('6', '6', 'Exploration of Mars, searching for signs of past or present life.', 'Provided valuable insights into Mars'' geology, climate, and potential past or present habitability, aiding in future exploration and astrobiology studies.'),
('7', '7', 'Discovery of geysers on Enceladus and study of Titan''s atmosphere.', 'Suggested the presence of subsurface oceans and potential habitability, making Enceladus a prime target for future exploration.'),
('8', '8', 'Discovery of thousands of exoplanets, expanding our understanding of planetary systems.', 'Revolutionized our perspective on planetary formation and the potential for life beyond our solar system.'),
('9', '9', 'Successful collection of asteroid Bennu samples for analysis.', 'Offers insights into the early Solar System and the potential delivery of water and organic compounds to Earth.'),
('10', '10', 'Infrared astronomy mission studying distant galaxies and the early universe.', 'Provides new insights into galaxy formation, star birth, and the evolution of the cosmos.'),
('11', '11', 'Discovery of water ice in permanently shadowed craters on the Moon.', 'Suggests the potential for lunar resources and supports future lunar exploration and habitation.'),
('12', '12', 'Return of comet dust samples to Earth for analysis.', 'Enhanced our understanding of comets, their composition, and the formation of the Solar System.'),
('13', '13', 'Exploration of dwarf planet Ceres, revealing its surface features and composition.', 'Provided insights into the differentiation and evolution of dwarf planets, and the potential for past or present habitability.'),
('14', '14', 'Survey of potential landing sites for future missions.', 'Identified safe and scientifically interesting landing sites, aiding in the planning of subsequent lunar missions.'),
('15', '15', 'Study of Pluto and its moons, investigating their geology, atmosphere, and potential habitability.', 'Enhanced our understanding of diverse planetary bodies in the Kuiper belt, providing insights into the formation and evolution of the Solar System.');

INSERT INTO "element" ("element_id", "symbol", "name", "atomic_num", "atomic_weight", "is_radioactive") VALUES
('1', 'H', 'Hydrogen', '1', '1.00794', '0'),
('2', 'He', 'Helium', '2', '4.002602', '0'),
('3', 'Li', 'Lithium', '3', '6.941', '0'),
('4', 'Be', 'Beryllium', '4', '9.012182', '0'),
('5', 'B', 'Boron', '5', '10.811', '0'),
('6', 'C', 'Carbon', '6', '12.0107', '0'),
('7', 'N', 'Nitrogen', '7', '14.0067', '0'),
('8', 'O', 'Oxygen', '8', '15.9994', '0'),
('9', 'F', 'Fluorine', '9', '18.9984032', '0'),
('10', 'Ne', 'Neon', '10', '20.1797', '0'),
('11', 'Na', 'Sodium', '11', '22.98976928', '0'),
('12', 'Mg', 'Magnesium', '12', '24.305', '0'),
('13', 'Al', 'Aluminum', '13', '26.9815386', '0'),
('14', 'Si', 'Silicon', '14', '28.0855', '0'),
('15', 'P', 'Phosphorus', '15', '30.973762', '0'),
('16', 'S', 'Sulfur', '16', '32.065', '0'),
('17', 'Cl', 'Chlorine', '17', '35.453', '0'),
('18', 'Ar', 'Argon', '18', '39.948', '0'),
('19', 'K', 'Potassium', '19', '39.0983', '0'),
('20', 'Ca', 'Calcium', '20', '40.078', '0'),
('21', 'Sc', 'Scandium', '21', '44.955912', '0'),
('22', 'Ti', 'Titanium', '22', '47.867', '0'),
('23', 'V', 'Vanadium', '23', '50.9415', '0'),
('24', 'Cr', 'Chromium', '24', '51.9961', '0'),
('25', 'Mn', 'Manganese', '25', '54.938045', '0'),
('26', 'Fe', 'Iron', '26', '55.845', '0'),
('27', 'Co', 'Cobalt', '27', '58.933195', '0'),
('28', 'Ni', 'Nickel', '28', '58.6934', '0'),
('29', 'Cu', 'Copper', '29', '63.546', '0'),
('30', 'Zn', 'Zinc', '30', '65.39', '0'),
('31', 'Ga', 'Gallium', '31', '69.723', '0'),
('32', 'Ge', 'Germanium', '32', '72.64', '0'),
('33', 'As', 'Arsenic', '33', '74.9216', '0'),
('34', 'Se', 'Selenium', '34', '78.96', '0');

INSERT INTO "exploration" ("exploration", "mission_id", "body_id", "arrival_date", "departure_date", "findings") VALUES
('1', '1', '2', '1969-07-20', '1969-07-21', 'Successful landing on the Moon. Collected rock samples and conducted scientific experiments.'),
('2', '2', '7', '2015-07-14', NULL, 'Currently exploring interstellar space beyond the Solar System. Studying cosmic rays and plasma.'),
('3', '3', '3', '2000-11-02', NULL, 'Continuous presence of international crews. Conducted thousands of scientific experiments.'),
('4', '4', '5', '2015-07-14', NULL, 'Studying the geology and composition of Pluto and its moons. Discovered ice and potential past oceans.'),
('5', '5', '6', NULL, NULL, 'Ongoing study of distant celestial objects. Captured detailed images and spectra.'),
('6', '6', '6', '2012-08-06', NULL, 'Exploring the surface of Mars. Searching for signs of past or present life. Studying geology and climate.'),
('7', '7', '7', '2004-07-01', '2017-09-15', 'Successful exploration of Saturn and its moons. Discovered geysers on Enceladus and studied Titan''s atmosphere.'),
('8', '8', '8', NULL, NULL, 'Discovered thousands of exoplanets and characterized their orbits and sizes. Expanded our understanding of planetary systems.'),
('9', '9', '9', '2018-12-03', '2020-05-27', 'Successful sample collection from asteroid Bennu. Studied its composition and surface features.'),
('10', '10', '10', NULL, NULL, 'Ongoing infrared astronomy mission. Studying distant galaxies and the early universe.'),
('11', '11', '11', '2008-10', '2009-08', 'Studied the Moon using remote sensing techniques. Discovered water ice in permanently shadowed craters.'),
('12', '12', '12', '2011-01-15', NULL, 'Successful return of comet dust samples to Earth. Studied the composition and structure of comets.'),
('13', '13', '4', '2015-03-17', NULL, 'Exploring the dwarf planet Ceres. Studied its surface features and composition.'),
('14', '14', '1', '1942', '1941', 'Tested the lunar module and surveyed potential landing sites for future missions.'),
('15', '15', '12', '2015-03-06', NULL, 'Studying the dwarf planet Pluto and its moons. Investigating their geology, atmosphere, and potential habitability.');

INSERT INTO "mission" ("mission_id", "name", "spacecraft_id", "objective", "start_date", "end_date", "outcome", "description") VALUES
('1', 'Apollo 11 Moon Landing', '1', 'Land humans on the Moon', '1969-07-16', '1969-07-24', 'Successful', 'First manned mission to land on the Moon.'),
('2', 'Voyager 1 Interstellar Mission', '2', 'Explore outer planets and interstellar space', '1977-09-05', NULL, 'Ongoing', 'Currently in interstellar space, continuing to send back data.'),
('3', 'ISS Assembly', '3', 'Assemble and maintain the International Space Station', '1998-11-20', NULL, 'Ongoing', 'Long-term project with continuous crew presence since 2000.'),
('4', 'New Horizons Pluto Flyby', '4', 'Study the dwarf planet Pluto and its moons', '2006-01-19', NULL, 'Successful', 'First spacecraft to explore the Pluto system.'),
('5', 'Hubble Space Telescope Mission', '5', 'Capture detailed images and spectra of distant celestial objects', '1990-04-24', NULL, 'Ongoing', 'Has provided groundbreaking insights into the universe.'),
('6', 'Mars Rover Curiosity', '6', 'Explore Mars and search for signs of life', '2011-11-26', NULL, 'Ongoing', 'Has made significant discoveries about Mars'' geology and potential past habitability.'),
('7', 'Cassini-Huygens Saturn Mission', '7', 'Study the planet Saturn and its moons', '1997-10-15', '2017-09-15', 'Successful', 'Provided invaluable data and images of Saturn and its moons.'),
('8', 'Kepler Space Telescope Mission', '8', 'Discover exoplanets and study their characteristics', '2009-03-07', '2018-10-30', 'Successful', 'Discovered thousands of exoplanets and expanded our understanding of planetary systems.'),
('9', 'OSIRIS-REx Asteroid Sample Return', '9', 'Collect a sample from asteroid Bennu and return it to Earth', '2016-09-08', '2023-09-24', 'Ongoing', 'Sample collection successful, return to Earth expected in 2023.'),
('10', 'James Webb Space Telescope Mission', '10', 'Infrared astronomy and studying early universe', '2021-12-25', NULL, 'Ongoing', 'Successor to the Hubble Telescope with enhanced capabilities.'),
('11', 'Chandrayaan-1 Moon Mission', '11', 'Study the Moon and search for water ice', '2008-10-22', '2009-08-29', 'Partially Successful', 'Conducted remote sensing of the Moon but experienced premature end.'),
('12', 'Stardust Comet Sample Return', '12', 'Collect comet dust samples and return to Earth', '1999-02-07', '2011-01-15', 'Successful', 'First mission to return comet dust samples to Earth.'),
('13', 'Apollo 11 Moon Landing', '1', 'Land humans on the Moon', '1969-07-16', '1969-07-24', 'Successful', 'First manned mission to land on the Moon.'),
('14', 'Voyager 1 Interstellar Mission', '2', 'Explore outer planets and interstellar space', '1977-09-05', NULL, 'Ongoing', 'Currently in interstellar space, continuing to send back data.'),
('15', 'ISS Assembly', '3', 'Assemble and maintain the International Space Station', '1998-11-20', NULL, 'Ongoing', 'Long-term project with continuous crew presence since 2000.'),
('16', 'New Horizons Pluto Flyby', '4', 'Study the dwarf planet Pluto and its moons', '2006-01-19', NULL, 'Successful', 'First spacecraft to explore the Pluto system.'),
('17', 'Hubble Space Telescope Mission', '5', 'Capture detailed images and spectra of distant celestial objects', '1990-04-24', NULL, 'Ongoing', 'Has provided groundbreaking insights into the universe.'),
('18', 'Mars Rover Curiosity', '6', 'Explore Mars and search for signs of life', '2011-11-26', NULL, 'Ongoing', 'Has made significant discoveries about Mars'' geology and potential past habitability.'),
('19', 'Cassini-Huygens Saturn Mission', '7', 'Study the planet Saturn and its moons', '1997-10-15', '2017-09-15', 'Successful', 'Provided invaluable data and images of Saturn and its moons.'),
('20', 'Kepler Space Telescope Mission', '8', 'Discover exoplanets and study their characteristics', '2009-03-07', '2018-10-30', 'Successful', 'Discovered thousands of exoplanets and expanded our understanding of planetary systems.'),
('21', 'OSIRIS-REx Asteroid Sample Return', '9', 'Collect a sample from asteroid Bennu and return it to Earth', '2016-09-08', '2023-09-24', 'Ongoing', 'Sample collection successful, return to Earth expected in 2023.'),
('22', 'James Webb Space Telescope Mission', '10', 'Infrared astronomy and studying early universe', '2021-12-25', NULL, 'Ongoing', 'Successor to the Hubble Telescope with enhanced capabilities.'),
('23', 'Chandrayaan-1 Moon Mission', '11', 'Study the Moon and search for water ice', '2008-10-22', '2009-08-29', 'Partially Successful', 'Conducted remote sensing of the Moon but experienced premature end.'),
('24', 'Stardust Comet Sample Return', '12', 'Collect comet dust samples and return to Earth', '1999-02-07', '2011-01-15', 'Successful', 'First mission to return comet dust samples to Earth.'),
('25', 'Generic Lunar Mission', '13', 'Explore the Moon and test equipment.', '2030-01-15', '2030-08-20', 'Planned', 'Upcoming mission to survey lunar terrain and test new technologies.'),
('26', 'Lunar Base Preparation', '14', 'Prepare a permanent lunar base.', '2035-05-05', NULL, 'Planned', 'Long-term project to establish a human presence on the Moon.'),
('27', 'Exploring Pluto''s Moons', '15', 'Study the geology and composition of Pluto''s moons.', '2028-11-10', NULL, 'Proposed', 'Proposed mission to explore the diverse moons of Pluto.');

INSERT INTO "spacecraft" ("spacecraft_id", "name", "type", "launch_date", "mission_duration", "status") VALUES
('1', 'Apollo 11', 'Manned Spacecraft', '1969-07-16', '8', 'Successful'),
('2', 'Voyager 1', 'Unmanned Spacecraft', '1977-09-05', NULL, 'Operational'),
('3', 'International Space Station', 'Space Station', '1998-11-20', NULL, 'Operational'),
('4', 'New Horizons', 'Unmanned Spacecraft', '2006-01-19', NULL, 'Operational'),
('5', 'Hubble Space Telescope', 'Space Telescope', '1990-04-24', NULL, 'Operational');

INSERT INTO "spacecraft_element" ("spacecraft_id", "element_id", "quantity") VALUES
('1', '14', '30.0'),
('1', '25', '50.0'),
('1', '28', '100.0'),
('2', '1', '200.0'),
('2', '7', '80.0'),
('2', '8', '50.0'),
('3', '13', '300.0'),
('3', '25', '200.0'),
('3', '30', '100.0'),
('4', '6', '50.0'),
('4', '14', '80.0'),
('4', '22', '20.0'),
('5', '8', '200.0'),
('5', '14', '150.0'),
('5', '26', '30.0'),
('6', '14', '100.0'),
('6', '25', '250.0'),
('6', '32', '10.0'),
('7', '1', '800.0'),
('7', '7', '300.0'),
('7', '8', '250.0'),
('8', '1', '300.0'),
('8', '6', '150.0'),
('8', '14', '400.0'),
('9', '1', '150.0'),
('9', '13', '80.0'),
('9', '25', '120.0'),
('10', '8', '500.0'),
('10', '14', '350.0'),
('10', '29', '150.0'),
('11', '1', '100.0'),
('11', '6', '80.0'),
('11', '14', '150.0'),
('12', '6', '50.0'),
('12', '14', '100.0'),
('12', '25', '30.0'),
('13', '1', '200.0'),
('13', '25', '150.0'),
('13', '28', '80.0'),
('14', '1', '400.0'),
('14', '22', '100.0'),
('14', '26', '50.0'),
('15', '8', '300.0'),
('15', '14', '200.0'),
('15', '33', '20.0');



-- Classify Misisons
-- SELECT 
--     m.name AS MissionName,
--     sc.name AS Spacecraft,
--     cb.name AS TargetCelestialBody,
--     CASE
--         WHEN m.start_date > '2024-04-01' THEN 'Future'
--         WHEN m.end_date IS NULL OR m.end_date >= DATE('now') THEN 'Ongoing'
--         ELSE 'Completed'
--     END AS MissionStatus
-- FROM mission m
-- JOIN spacecraft sc ON m.spacecraft_id = sc.spacecraft_id
-- JOIN exploration ex ON m.mission_id = ex.mission_id
-- JOIN celestial_body cb ON ex.body_id = cb.body_id
-- ORDER BY 
--     CASE
--         WHEN m.start_date > DATE('now') THEN 1
--         WHEN m.end_date IS NULL OR m.end_date >= DATE('now') THEN 2
--         ELSE 3
--     END,
--     m.start_date;

-- WITH ElementPresence AS (
--   SELECT
--     e.name,
--     COUNT(DISTINCT c.body_id) AS BodiesCount,
--     AVG(c.percentage) AS AveragePercentage
--   FROM composition c
--   JOIN element e ON c.element_id = e.element_id
--   GROUP BY e.name
-- )
-- SELECT *
-- FROM ElementPresence
-- ORDER BY BodiesCount DESC, AveragePercentage DESC
-- LIMIT 5;

-- SELECT 
--   cb.name AS CelestialBodyName,
--   COUNT(exploration.mission_id) AS NumberOfMissions,
--   SUM(
--     CASE 
--       WHEN exploration.departure_date IS NOT NULL THEN JULIANDAY(exploration.departure_date) - JULIANDAY(exploration.arrival_date) 
--       ELSE 0 
--     END
--   ) AS TotalExplorationDuration
-- FROM celestial_body cb
-- JOIN exploration ON cb.body_id = exploration.body_id
-- GROUP BY cb.body_id
-- ORDER BY NumberOfMissions DESC, TotalExplorationDuration DESC
-- LIMIT 1;

-- WITH RankedCompositions AS (
--   SELECT
--     cb.name AS PlanetName,
--     el.name AS ElementName,
--     c.percentage,
--     ROW_NUMBER() OVER(PARTITION BY cb.body_id ORDER BY c.percentage DESC) AS Rank
--   FROM composition c
--   INNER JOIN celestial_body cb ON c.body_id = cb.body_id
--   INNER JOIN element el ON c.element_id = el.element_id
--   WHERE cb.type = 'Planet'
-- )

-- SELECT
--   PlanetName,
--   ElementName,
--   percentage
-- FROM RankedCompositions
-- WHERE Rank <= 3
-- ORDER BY PlanetName;

WITH MissionOutcomes AS (
  SELECT
    sc.type AS SpacecraftType,
    CASE 
      WHEN m.outcome = 'Successful' THEN 'Successful'
      WHEN m.outcome = 'Ongoing' THEN 'Ongoing'
      ELSE 'Failed/Other'
    END AS Outcome
  FROM mission m
  INNER JOIN spacecraft sc ON m.spacecraft_id = sc.spacecraft_id
)

SELECT
  SpacecraftType,
  ROUND((COUNT(CASE WHEN Outcome = 'Successful' THEN 1 END) * 100.0 / COUNT(*)), 2) AS SuccessRate
FROM MissionOutcomes
GROUP BY SpacecraftType
ORDER BY SuccessRate DESC;