-- Competitions Table
CREATE TABLE competition (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  found_year INTEGER
);

-- Instances Table
CREATE TABLE instance (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  competition_id INTEGER NOT NULL,
  start_date TEXT NOT NULL,
  end_date TEXT NOT NULL,
  FOREIGN KEY (competition_id) REFERENCES competition(id) 
);

-- Pianists Table
CREATE TABLE pianist (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  age INTEGER NOT NULL,
  sex TEXT,
  country TEXT NOT NULL
);

-- Entries Table
CREATE TABLE entry (
  instance_id INTEGER,
  pianist_id INTEGER,
  place INTEGER NOT NULL,
  prize INTEGER NOT NULL,
  PRIMARY KEY (instance_id, pianist_id),
  FOREIGN KEY (instance_id) REFERENCES instance(id),
  FOREIGN KEY (pianist_id) REFERENCES pianist(id)
);

-- Performances Table
CREATE TABLE performance (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  instance_id INTEGER NOT NULL,
  pianist_id INTEGER NOT NULL,
  piece_id INTEGER NOT NULL,
  duration INTEGER NOT NULL,
  round TEXT NOT NULL, 
  FOREIGN KEY (instance_id) REFERENCES instance(id), 
  FOREIGN KEY (pianist_id) REFERENCES pianist(id),
  FOREIGN KEY (piece_id) REFERENCES piece(id)
);

-- Pieces Table
CREATE TABLE piece (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT NOT NULL,
  piece_key TEXT NOT NULL,
  composer TEXT NOT NULL,
  composed_in INTEGER,
  avg_duration INTEGER NOT NULL
);

-- Judges Table
CREATE TABLE judge (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  age INTEGER NOT NULL, 
  country TEXT NOT NULL
);

-- Scores Table
CREATE TABLE score (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  performance_id INTEGER NOT NULL,
  judge_id INTEGER NOT NULL,
  score INTEGER CHECK(score BETWEEN 1 AND 100), 
  FOREIGN KEY (performance_id) REFERENCES performance(id), 
  FOREIGN KEY (judge_id) REFERENCES judge(id)
);


INSERT INTO
  competition (name, found_year)
VALUES
  ('International Chopin Competition', 1927),
  ('Van Cliburn International Piano Competition',1962),
  ('London International Piano Competition', 1986),
  ('Shanghai International Piano Competition', 1995),
  ('Scriabin Competition', 1999),
  ('Tchaikovsky International Music Competition', 1958),
  ('Leeds International Piano Competition', 1963);

INSERT INTO
  instance (competition_id, start_date, end_date)
VALUES
  (1, '2022-10-02', '2022-10-23'),
  (2, '2022-05-20', '2022-06-05'),
  (3, '2023-08-15', '2023-08-28'),
  (4, '2023-03-10', '2023-03-21'),
  (5, '2021-09-01', '2021-09-12'),
  (6, '2024-06-01', '2024-06-15'),
  (7, '2024-09-10', '2024-09-25');

INSERT INTO
  pianist (id, first_name, last_name, age, sex, country)
VALUES
  (1, 'Maria', 'Sztompka', '25', 'F', 'Poland'),
  (2, 'Albert', 'Wu', '20', 'M', 'China'),
  (3, 'Sophia', 'Leventhal', '19', 'F', 'USA'),
  (4, 'Julien', 'Andron', '32', 'M', 'France'),
  (5, 'Sonata', 'Tachibana', '23', 'F', 'Japan'),
  (6, 'Ludwig', 'Baum', '28', 'M', 'Germany'),
  (7, 'Elena', 'Ivanova', '30', 'F', 'Russia');

INSERT INTO
  entry (instance_id, pianist_id, place, prize)
VALUES
  (1, 1, 1, 1000),
  (1, 2, 2, 800),
  (1, 3, 3, 700), 
  (2, 3, 1, 1200),
  (2, 4, 2, 950),
  (3, 5, 1, 1500), 
  (3, 4, 2, 1200),
  (3, 2, 3, 900),
  (4, 3, 1, 1300),
  (4, 1, 2, 1100),
  (4, 5, 3, 800),
  (5, 2, 1, 1400),
  (5, 3, 2, 1150),
  (5, 4, 3, 950),
  (6, 6, 1, 1600),
  (6, 7, 2, 1300),
  (7, 1, 1, 2100),
  (7, 6, 2, 1700),
  (7, 7, 3, 1500);

INSERT INTO
  performance (
    instance_id,
    pianist_id,
    piece_id,
    duration,
    ROUND
  )
VALUES
  (1, 1, 1, 432, 'Semifinal'),
  (1, 2, 2, 451, 'Final'),
  (2, 3, 5, 502, 'Semifinal'),
  (2, 4, 2, 387, 'Final'),
  (3, 5, 4, 485, 'Quarterfinal'),
  (6, 6, 3, 360, 'Final'), -- More data
  (6, 7, 4, 440, 'Semifinal'),
  (7, 1, 5, 500, 'Final'),
  (7, 2, 1, 330, 'Quarterfinal');

INSERT INTO
  piece (
    title,
    piece_key,
    composer,
    composed_in,
    avg_duration
  )
VALUES
  ('Etude Op. 10, No. 3', 'C sharp minor', 'Fryderyk Chopin', 1832, 320 ),
  ("Mozart's Piano Sonata No. 1",'C major','Wolfgang Amadeus Mozart', 1766,450),
  ('Prélude No. 2','C sharp minor','Claude Debussy',1910,380),
  ('Sonata No. 21','C major','Ludwig van Beethoven',1822,420),
  ('Impromptu No. 3','G flat major','Franz Schubert',1827,510),
  ('Ballade No. 1', 'G minor', 'Fryderyk Chopin', 1835, 540), 
  ('Moonlight Sonata', 'C sharp minor', 'Ludwig van Beethoven', 1801, 545);

INSERT INTO
  judge (name, age, country)
VALUES
  ('Professor Vladimir Horowitz', '65', 'Russia'),
  ('Dr. Alice Wang', '52', 'USA'),
  ('Sir William Congreve', '78', 'UK'),
  ('Ms. Kaori Miyoshi', '47', 'Japan'),
  ('Dr. Jonathan Arnel', '41', 'France'),
  ('Maestro Giuseppe Rossi', '60', 'Italy'),
  ('Professor Li Wei', '50', 'China');

INSERT INTO score (performance_id, judge_id, score)
VALUES
  (1, 1, 95), 
  (1, 2, 90), 
  (2, 3, 88), 
  (3, 4, 92), 
  (4, 5, 85), 
  (5, 6, 87), 
  (6, 7, 93), 
  (7, 1, 89), 
  (8, 2, 92);

-- Country's Best Pianist
-- WITH BestPianist AS (
--   SELECT p.first_name || ' ' || p.last_name AS full_name,
--   p.age,
--   SUM(e.prize) AS total_prize,
--   p.country,
--   RANK() OVER (
--     PARTITION BY p.country
--     ORDER BY SUM(e.prize) DESC, p.age
--   ) AS rank
--   FROM pianist p 
--   JOIN entry e on p.id = e.pianist_id
--   GROUP BY p.id
-- )
-- SELECT full_name, age, total_prize, country FROM BestPianist
-- WHERE rank = 1;


-- Composer Billboard Top 3
-- WITH ComposerPerformances AS (
--   SELECT
--     strftime('%Y', i.start_date) AS year,
--     pc.composer,
--     COUNT(pc.id) AS performances
--   FROM
--     performance pf
--   JOIN
--     instance i ON pf.instance_id = i.id
--   JOIN
--     piece pc ON pf.piece_id = pc.id
--   GROUP BY
--     composer, year
-- ),
-- RankedComposers AS (
--   SELECT
--     year,
--     composer,
--     performances,
--     ROW_NUMBER() OVER (PARTITION BY year ORDER BY performances DESC) AS row_num -- Changed to ROW_NUMBER
--   FROM
--     ComposerPerformances
-- )
-- SELECT
--   year,
--   composer,
--   performances
-- FROM
--   RankedComposers
-- WHERE
--   row_num <= 3 -- Ensures a strict limit
-- ORDER BY
--   year DESC, row_num ASC;

-- Most Diverse Judges
-- WITH JudgeRange AS (
--   SELECT
--     j.name,
--     (MAX(s.score) - MIN(s.score)) AS score_range
--   FROM
--     judge j
--   JOIN
--     score s ON j.id = s.judge_id
--   GROUP BY
--     j.id
-- ),
-- RankedJudges AS (
--   SELECT
--     name,
--     score_range,
--     ROW_NUMBER() OVER (ORDER BY score_range DESC, name ASC) AS row_number -- Adjusted for breaking ties alphabetically
--   FROM
--     JudgeRange
-- )
-- SELECT
--   name,
--   score_range
-- FROM
--   RankedJudges
-- WHERE
--   row_number <= 3; -- Limits to the top 3, breaking ties alphabetically

-- Most Versatile Pianist
-- WITH PianistRepertoire AS (
--   SELECT
--     p.id,
--     p.first_name || ' ' || p.last_name AS full_name,
--     p.age,
--     COUNT(DISTINCT pf.piece_id) AS unique_pieces
--   FROM
--     pianist p
--   JOIN
--     performance pf ON p.id = pf.pianist_id
--   GROUP BY
--     p.id
-- ),
-- RankedPianists AS (
--   SELECT
--     full_name,
--     age,
--     unique_pieces,
--     ROW_NUMBER() OVER (ORDER BY unique_pieces DESC, age ASC) AS row_num
--   FROM
--     PianistRepertoire
-- )
-- SELECT
--   full_name,
--   unique_pieces
-- FROM
--   RankedPianists
-- WHERE
--   row_num = 1;

-- Most and Least Diverse Competitions, # of different countries participating. break ties by unique_pieces
-- performed and pick by alphabetical order of competition name.
-- WITH Diversity AS (
--   SELECT
--     c.id,
--     c.name,
--     COUNT(DISTINCT p.country) AS diversity_index,
--     COUNT(DISTINCT pf.piece_id) AS unique_pieces
--   FROM
--     competition c
--   JOIN
--     instance i ON c.id = i.competition_id
--   JOIN
--     entry e ON i.id = e.instance_id
--   JOIN
--     pianist p ON e.pianist_id = p.id
--   JOIN
--     performance pf ON pf.instance_id = i.id
--   GROUP BY
--     c.id
-- ),
-- RankedCompetitions AS (
--   SELECT
--     name,
--     diversity_index,
--     unique_pieces,
--     RANK() OVER (ORDER BY diversity_index DESC, unique_pieces DESC, name) AS rank_asc,
--     RANK() OVER (ORDER BY diversity_index ASC, unique_pieces DESC, name) AS rank_desc
--   FROM
--     Diversity
-- )
-- SELECT
--   name,
--   diversity_index
-- FROM
--   RankedCompetitions
-- WHERE
--   rank_asc = 1 OR rank_desc = 1;

-- Composition Duration -> Scores
-- WITH PieceDurations AS (
--   SELECT
--     id,
--     avg_duration,
--     PERCENT_RANK() OVER (ORDER BY avg_duration) AS percent_rank
--   FROM
--     piece
-- ),
-- tertileAssignments AS (
--   SELECT
--     id,
--     avg_duration,
--     CASE
--       WHEN percent_rank <= 0.33 THEN 1
--       WHEN percent_rank <= 0.66 THEN 2
--       ELSE 3
--     END AS tertile
--   FROM
--     PieceDurations
-- ),
-- ScoredPerformances AS (
--   SELECT
--     qa.tertile,
--     AVG(s.score) AS avg_score
--   FROM
--     performance pf
--   JOIN
--     score s ON pf.id = s.performance_id
--   JOIN
--     tertileAssignments qa ON pf.piece_id = qa.id
--   GROUP BY
--     qa.tertile
-- )
-- SELECT
--   tertile,
--   ROUND(AVG(avg_score), 2) AS tertile_avg_score
-- FROM
--   ScoredPerformances
-- GROUP BY
--   tertile
-- ORDER BY
--   tertile;

-- Influential Composers
-- WITH ComposerCountries AS (
--   SELECT
--     pc.composer,
--     COUNT(DISTINCT p.country) AS country_count
--   FROM
--     piece pc
--   JOIN
--     performance pf ON pc.id = pf.piece_id
--   JOIN
--     pianist p ON pf.pianist_id = p.id
--   GROUP BY
--     pc.composer
-- ),
-- RankedComposers AS (
--   SELECT
--     composer,
--     country_count,
--     RANK() OVER (ORDER BY country_count DESC) AS rank
--   FROM
--     ComposerCountries
-- )
-- SELECT
--   composer,
--   country_count
-- FROM
--   RankedComposers
-- ORDER BY
--   rank,
--   composer; -- Including composer in ORDER BY for a deterministic sort in case of ties.