init.sql

CREATE USER concertchair WITH ENCRYPTED PASSWORD '';

CREATE DATABASE pafe
    WITH TEMPLATE = 'template0'
    OWNER = 'concertchair'
    LOCALE = 'en_US.UTF-8';

ALTER DATABASE pafe SET timezone TO 'UTC';

GRANT ALL PRIVILEGES ON DATABASE pafe TO concertchair;

\c pafe

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO concertchair;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON SEQUENCES TO concertchair;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON FUNCTIONS TO concertchair;


CREATE TYPE instrument_list AS ENUM ('Cello',
                                'Flute',
                                'Piano',
                                'Violin',
                                'Soprano',
                                'Viola',
                                'Tenor',
                                'Clarinet',
                                'Oboe',
                                'Bassoon',
                                'Ensemble');

CREATE TYPE contributor_role AS ENUM (
    'Composer',
    'Copyist',
    'Editor',
    'Arranger',
    'Transcriber',
    'Realizer',
    'Orchestrator'
);

CREATE TYPE piece_category AS ENUM (
    'Concerto',
    'Solo',
    'Ensemble',
    'Not Appropriate'
);

CREATE TYPE division_tag AS ENUM (
    'Violin Viola',
    'Cello Bass',
    'Piano',
    'Woodwinds'
);

CREATE TYPE review_status AS ENUM (
    'Complete'
);

CREATE TABLE performer (
    id SERIAL PRIMARY KEY,
    full_name VARCHAR(255) NOT NULL,
    epoch INTEGER NOT NULL,
    archive BOOLEAN NOT NULL DEFAULT FALSE,
    email VARCHAR(255) NULL,
    phone VARCHAR(18) NULL,
    instrument VARCHAR(255) NULL
);
CREATE INDEX performer_search_idx ON performer(epoch, instrument, full_name);

CREATE TABLE accompanist (
    id SERIAL PRIMARY KEY,
    full_name VARCHAR(255) NOT NULL
);

CREATE TABLE contributor (
    id SERIAL PRIMARY KEY,
    full_name VARCHAR(255) NOT NULL,
    years_active VARCHAR(25) NOT NULL,
    role contributor_role NOT NULL DEFAULT 'Composer',
    notes VARCHAR(255) NULL,
    updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_by INTEGER NULL
);
CREATE INDEX contributor_name_idx ON contributor(full_name);

CREATE TABLE musical_piece (
    id SERIAL PRIMARY KEY,
    printed_name VARCHAR(512) NOT NULL,
    first_contributor_id INTEGER NOT NULL,
    all_movements VARCHAR(512) NULL,
    second_contributor_id INTEGER NULL,
    third_contributor_id INTEGER NULL,
    imslp_url VARCHAR(512) NULL,
    comments VARCHAR(1000) NULL,
    flag_for_discussion BOOLEAN NOT NULL DEFAULT FALSE,
    discussion_notes VARCHAR(1000) NULL,
    is_not_appropriate BOOLEAN NOT NULL DEFAULT FALSE,
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);
CREATE INDEX musical_piece_name_idx ON musical_piece(printed_name);

CREATE TABLE musical_piece_category_map (
    musical_piece_id INTEGER NOT NULL,
    category piece_category NOT NULL
);
CREATE UNIQUE INDEX musical_piece_category_map_unique_idx
    ON musical_piece_category_map(musical_piece_id, category);
CREATE INDEX musical_piece_category_map_category_idx
    ON musical_piece_category_map(category, musical_piece_id);

CREATE TABLE musical_piece_division_tag (
    musical_piece_id INTEGER NOT NULL,
    division_tag division_tag NOT NULL
);
CREATE UNIQUE INDEX musical_piece_division_tag_unique_idx
    ON musical_piece_division_tag(musical_piece_id, division_tag);
CREATE INDEX musical_piece_division_tag_lookup_idx
    ON musical_piece_division_tag(division_tag, musical_piece_id);

CREATE TABLE musical_piece_review (
    musical_piece_id INTEGER NOT NULL,
    reviewer_id INTEGER NOT NULL,
    status review_status NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);
CREATE UNIQUE INDEX musical_piece_review_unique_idx
    ON musical_piece_review(musical_piece_id, reviewer_id);
CREATE INDEX musical_piece_review_queue_idx
    ON musical_piece_review(reviewer_id, status, musical_piece_id);

CREATE OR REPLACE FUNCTION enforce_not_appropriate_category()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.category = 'Not Appropriate' THEN
        DELETE FROM musical_piece_category_map
        WHERE musical_piece_id = NEW.musical_piece_id
          AND category <> 'Not Appropriate';

        UPDATE musical_piece
        SET is_not_appropriate = TRUE
        WHERE id = NEW.musical_piece_id;
    ELSE
        IF EXISTS (
            SELECT 1 FROM musical_piece
            WHERE id = NEW.musical_piece_id
              AND is_not_appropriate = TRUE
        ) THEN
            UPDATE musical_piece
            SET is_not_appropriate = FALSE
            WHERE id = NEW.musical_piece_id;

            DELETE FROM musical_piece_category_map
            WHERE musical_piece_id = NEW.musical_piece_id
              AND category = 'Not Appropriate';
        END IF;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION sync_not_appropriate_category_on_delete()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.category = 'Not Appropriate' THEN
        UPDATE musical_piece
        SET is_not_appropriate = FALSE
        WHERE id = OLD.musical_piece_id;
    END IF;

    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION sync_not_appropriate_flag()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.is_not_appropriate = TRUE THEN
        DELETE FROM musical_piece_category_map
        WHERE musical_piece_id = NEW.id
          AND category <> 'Not Appropriate';

        IF NOT EXISTS (
            SELECT 1 FROM musical_piece_category_map
            WHERE musical_piece_id = NEW.id
              AND category = 'Not Appropriate'
        ) THEN
            INSERT INTO musical_piece_category_map (musical_piece_id, category)
            VALUES (NEW.id, 'Not Appropriate');
        END IF;
    ELSE
        DELETE FROM musical_piece_category_map
        WHERE musical_piece_id = NEW.id
          AND category = 'Not Appropriate';
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER musical_piece_category_enforce_not_appropriate
AFTER INSERT OR UPDATE ON musical_piece_category_map
FOR EACH ROW
EXECUTE FUNCTION enforce_not_appropriate_category();

CREATE TRIGGER musical_piece_category_sync_not_appropriate_delete
AFTER DELETE ON musical_piece_category_map
FOR EACH ROW
EXECUTE FUNCTION sync_not_appropriate_category_on_delete();

CREATE TRIGGER musical_piece_sync_not_appropriate_flag
AFTER UPDATE OF is_not_appropriate ON musical_piece
FOR EACH ROW
EXECUTE FUNCTION sync_not_appropriate_flag();

CREATE TABLE adjudicated_pieces (
    performance_id INTEGER NOT NULL,
    musical_piece_id INTEGER NOT NULL,
    movement VARCHAR(255) NULL,
    is_merged BOOLEAN NOT NULL DEFAULT FALSE
);
CREATE UNIQUE INDEX adjudicated_pieces_idx ON adjudicated_pieces(performance_id,musical_piece_id);

CREATE TABLE performance_pieces (
    performance_id INTEGER NOT NULL,
    musical_piece_id INTEGER NOT NULL,
    movement VARCHAR(255) NULL,
    is_performance_piece BOOLEAN NOT NULL DEFAULT FALSE
);
CREATE UNIQUE INDEX performance_pieces_idx ON performance_pieces(performance_id,musical_piece_id);
CREATE UNIQUE INDEX performance_pieces_one_selected_idx
    ON performance_pieces(performance_id)
    WHERE is_performance_piece = true;

CREATE TABLE class_lottery (
                               class_name VARCHAR(25) NOT NULL,
                               lottery INTEGER NOT NULL);
CREATE UNIQUE INDEX lookup_class_lottery_idx ON class_lottery(class_name);

CREATE TABLE performance (
    id SERIAL PRIMARY KEY,
    performer_id INTEGER NOT NULL,
    performance_order INTEGER DEFAULT 100 NOT NULL,
    class_name VARCHAR(255) NOT NULL,
    concert_series VARCHAR(255) NOT NULL,
    year INTEGER NOT NULL,
    chair_override BOOLEAN NOT NULL DEFAULT FALSE,
    duration INTEGER DEFAULT 0 NOT NULL,
    accompanist_id INTEGER NULL,
    instrument instrument_list,
    comment VARCHAR(500) NULL,
    warm_up_room_name VARCHAR(255) NULL,
    warm_up_room_start TIMESTAMP NULL,
    warm_up_room_end TIMESTAMP NULL
);

CREATE UNIQUE INDEX performance_performer_series_year_class_idx
    ON performance(performer_id, concert_series, class_name, year);

CREATE TABLE concert_times (
    id BIGSERIAL PRIMARY KEY,
    concert_series VARCHAR(255) NOT NULL,
    year INTEGER NOT NULL,
    concert_number_in_series INTEGER NOT NULL DEFAULT 0,
    start_time TIMESTAMP NOT NULL,
    CONSTRAINT concert_times_unique UNIQUE (concert_series, year, concert_number_in_series)
);

CREATE TABLE schedule_slot_choice (
    performer_id INTEGER NOT NULL,
    concert_series VARCHAR(255) NOT NULL,
    year INTEGER NOT NULL,
    slot_id BIGINT NOT NULL,
    rank INTEGER NULL,
    not_available BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE UNIQUE INDEX schedule_slot_choice_unique_idx
    ON schedule_slot_choice(performer_id, concert_series, year, slot_id);

CREATE INDEX schedule_slot_choice_lookup_idx
    ON schedule_slot_choice(performer_id, concert_series, year);

CREATE TABLE authorized_user (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    role VARCHAR(32) NOT NULL
        CHECK (role IN (
            'Admin',
            'ConcertMaster',
            'MusicEditor',
            'DivisionChair'
        ))
);

CREATE TABLE login_user (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    code INTEGER NOT NULL,
    first_login_at TIMESTAMP NULL,
    last_login_at TIMESTAMP NULL,
    last_code_sent_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX login_user_code_idx ON login_user(code);

INSERT INTO concert_times (concert_series, year, concert_number_in_series, start_time)
VALUES
  ('Concerto', 2026, 0, '2026-04-12 15:00:00'),
  ('Eastside', 2026, 1, '2026-04-18 16:00:00'),
  ('Eastside', 2026, 2, '2026-04-18 19:00:00'),
  ('Eastside', 2026, 3, '2026-04-19 14:00:00'),
  ('Eastside', 2026, 4, '2026-04-19 17:00:00');


INSERT INTO contributor (full_name, years_active) VALUES ('Adele Adkins','1988 - current');
INSERT INTO contributor (full_name, years_active) VALUES ('Amy Beach','1867 - 1944');
INSERT INTO contributor (full_name, years_active) VALUES ('Antonín Dvořák','1841 - 1904');
INSERT INTO contributor (full_name, years_active) VALUES ('Antonio Vivaldi','1678 - 1741');
INSERT INTO contributor (full_name, years_active) VALUES ('Aram Khachaturian','1903 - 1978');
INSERT INTO contributor (full_name, years_active) VALUES ('Bohuslav Martinu','1890 - 1959');
INSERT INTO contributor (full_name, years_active) VALUES ('Camille Saint-Saëns','1835 - 1921');
INSERT INTO contributor (full_name, years_active) VALUES ('Carl Böhm','1844 - 1920');
INSERT INTO contributor (full_name, years_active) VALUES ('Carl Maria von Weber','1786 - 1826');
INSERT INTO contributor (full_name, years_active) VALUES ('Carl Philipp Stamitz','1745 - 1801');
INSERT INTO contributor (full_name, years_active) VALUES ('Charles Dancla','1817 - 1907');
INSERT INTO contributor (full_name, years_active) VALUES ('Dan Wilson','1961 - current');
INSERT INTO contributor (full_name, years_active) VALUES ('Dmitri Shostakovich','1906 - 1975');
INSERT INTO contributor (full_name, years_active) VALUES ('Édouard Lalo','1823 - 1892');
INSERT INTO contributor (full_name, years_active) VALUES ('Edward Elgar','1857 - 1934');
INSERT INTO contributor (full_name, years_active) VALUES ('Emile Pessard','1843 - 1917');
INSERT INTO contributor (full_name, years_active) VALUES ('Frank Wildhorn','1958 - current');
INSERT INTO contributor (full_name, years_active) VALUES ('Franz Joseph Haydn','1732 - 1809');
INSERT INTO contributor (full_name, years_active) VALUES ('Franz Liszt','1811 - 1886');
INSERT INTO contributor (full_name, years_active) VALUES ('Frédéric Chopin','1810 - 1849');
INSERT INTO contributor (full_name, years_active) VALUES ('Georg Philipp Telemann','1681 - 1767');
INSERT INTO contributor (full_name, years_active) VALUES ('George Frideric Handel','1685 - 1759');
INSERT INTO contributor (full_name, years_active, notes) VALUES ('Georges Bizet','1838 - 1875','Bizet');
INSERT INTO contributor (full_name, years_active) VALUES ('Henri Vieuxtemps','1820 - 1881');
INSERT INTO contributor (full_name, years_active) VALUES ('Henryk Wieniawski','1835 - 1881');
INSERT INTO contributor (full_name, years_active) VALUES ('Irving Berlin','1888 - 1989');
INSERT INTO contributor (full_name, years_active) VALUES ('Jean Sibelius','1865 - 1957');
INSERT INTO contributor (full_name, years_active) VALUES ('Johann Christian Bach','1735 - 1782');
INSERT INTO contributor (full_name, years_active, notes) VALUES ('Johann Sebastian Bach','1685 - 1750', 'Johan Sebastian bach');
INSERT INTO contributor (full_name, years_active) VALUES ('Maurice Ravel','1875 - 1837');
INSERT INTO contributor (full_name, years_active) VALUES ('Max Bruch','1838 - 1920');
INSERT INTO contributor (full_name, years_active) VALUES ('Niccolò Paganini','1782 - 1840');
INSERT INTO contributor (full_name, years_active) VALUES ('Nikolai Kapustin','1937 - 2020');
INSERT INTO contributor (full_name, years_active) VALUES ('Otar Gordeli','1928 - 1994');
INSERT INTO contributor (full_name, years_active) VALUES ('Pablo de Sarasate','1844 - 1908');
INSERT INTO contributor (full_name, years_active, notes) VALUES ('Pyotr Ilyich Tchaikovsky','1840 - 1893','Tchaikovsky');
INSERT INTO contributor (full_name, years_active) VALUES ('Richard Rodgers','1902 - 1979');
INSERT INTO contributor (full_name, years_active) VALUES ('Robert Schumann','1810 - 1856');
INSERT INTO contributor (full_name, years_active) VALUES ('Rossini Niccolò Paganini','1782 - 1840');
INSERT INTO contributor (full_name, years_active) VALUES ('Sergei Prokofiev','1891 - 1953');
INSERT INTO contributor (full_name, years_active) VALUES ('William Gillock','1917 - 1993');
INSERT INTO contributor (full_name, years_active, notes) VALUES ('William Henry Squire','1871 - 1963', 'William H. Squire');
INSERT INTO contributor (full_name, years_active) VALUES ('Wolfgang Amadeus Mozart','1756 - 1791');
INSERT INTO contributor (full_name, years_active) VALUES ('Luigi Boccherini','1745 - 1805');
INSERT INTO contributor (full_name, years_active) VALUES ('Gabriel Fauré','1842 - 1924');
INSERT INTO contributor (full_name, years_active) VALUES ('Ludwig van Beethoven','1770 - 1827');