
-- A different way of defining the ascent table so that multiple climbers can be recorded in
-- each ascent. The table name is ascent_multiple to avoid conflict with the existing ascent
-- table.
--
CREATE TABLE ascent_multiple1(
    id          INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    mountain_id INTEGER REFERENCES mountain(id),
    climber_ids INTEGER ARRAY,  -- We lose referential integrity.
    ascent_date DATE,
    notes       VARCHAR(280)
);

INSERT INTO ascent_multiple1 
    (mountain_id, climber_ids, ascent_date)
VALUES
    ((SELECT id FROM mountain WHERE name = 'Washington' AND state = 'NH'),
     ARRAY[(SELECT id FROM climber WHERE first_name = 'Alice' AND last_name = 'Atlas'),
           (SELECT id FROM climber WHERE first_name = 'Bob' AND last_name =  'Bobblehead')],
     '2022-01-01');

 -- For information about how to use arrays in queries and updates, including how to search
 -- for specific values, see: https://www.postgresql.org/docs/14/arrays.html. Note this
 -- warning, however:
 --
 -- "Arrays are not sets; searching for specific array elements can be a sign of database
 -- misdesign. Consider using a separate table with a row for each item that would be an
 -- array element. This will be easier to search, and is likely to scale better for a large
 -- number of elements."
 
 -- A table is in "first normal form" if it contains no aggregate attributes (such as an
 -- array). Thus avoiding arrays might be said to be "proper" database design. Use arrays
 -- with caution! To fix the above, move 'climber_ids' to a separate table.
 
 -- Notice: no information about who did the ascents!
 CREATE TABLE ascent_multiple2(
    id          INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    mountain_id INTEGER REFERENCES mountain(id),
    ascent_date DATE,
    notes       VARCHAR(280)
);

-- This table contains linkages between ascents and the climbers that did each ascent.
-- This is an "m to n" relation: each ascent can have multiple climbers and each climber
-- can be part of multiple ascents.
CREATE TABLE ascent_climbers(
    ascent_id   INTEGER REFERENCES ascent_multiple2(id),
    climber_id  INTEGER REFERENCES climber(id),
    PRIMARY KEY(ascent_id, climber_id)  -- A "full width" primary key.
);

-- Now insert an ascent without any climber information.
INSERT INTO ascent_multiple2
    (mountain_id, ascent_date)
VALUES
    ((SELECT id FROM mountain WHERE name = 'Washington' AND state = 'NH'),
     '2022-01-01');
 
 -- Now insert the climbers for the previously inserted ascent.
INSERT INTO ascent_climbers
    (ascent_id, climber_id)
VALUES
    ((SELECT id
      FROM ascent_multiple2
      WHERE mountain_id = (SELECT id FROM mountain WHERE name = 'Washington' AND state = 'NH') AND
            ascent_date = '2022-01-01'),
     (SELECT id FROM climber WHERE first_name = 'Alice' AND last_name = 'Atlas')),
    ((SELECT id
      FROM ascent_multiple2
      WHERE mountain_id = (SELECT id FROM mountain WHERE name = 'Washington' AND state = 'NH') AND
            ascent_date = '2022-01-01'),
     (SELECT id FROM climber WHERE first_name = 'Bob' AND last_name = 'Bobblehead'));

 -- Who climbed Washington on January 1, 2022?
 SELECT c.name, c.email
 FROM ascent_climbers ac
   JOIN ascent_multiple2 am ON ac.ascent_id   = am.id 
   JOIN climber c           ON ac.climber_id  = c.id 
   JOIN mountain m          ON am.mountain_id = m.id
WHERE am.ascent_date = '2022-01-01' AND m.name = 'Washington' and m.state = 'NH';

-- Surely that ugly INSERT above can be cleaned up! Use a "with query." The "table" produced,
-- in this case, has only a single row and a single column. It thus plays the role of a temporary
-- value that is in scope during the rest of the query. This allows us to factor out the sub-
-- query that looks up the ascent_id since all the records we are inserting are from the same
-- climbing party and thus part of the same ascent.
-- 
-- Compare this with "let" expressions in functional languages (Haskell, OCaml):
-- let temp_ascent_id = {some subexpression} in {some query}
--
WITH temp_ascent_id AS (
    SELECT id
    FROM ascent_multiple2
    WHERE mountain_id = (SELECT id FROM mountain WHERE name = 'Washington' AND state = 'NH') AND
          ascent_date = '2022-01-01'
)
INSERT INTO ascent_climbers
    (ascent_id, climber_id)
VALUES
    ((SELECT id FROM temp_ascent_id),
     (SELECT id FROM climber WHERE first_name = 'Alice' AND last_name = 'Atlas')),
    ((SELECT id FROM temp_ascent_id),
     (SELECT id FROM climber WHERE first_name = 'Bob' AND last_name = 'Bobblehead'));
