jam-cloud/db/up/scheduled_sessions.sql

-- track the last measured audio gear latency
ALTER TABLE users ADD COLUMN audio_latency double precision;

-- begin moving all the fields traditionally on music_sessions to music_sessions_history
ALTER TABLE music_sessions_history ADD COLUMN scheduled_start TIMESTAMP WITH TIME ZONE;
ALTER TABLE music_sessions_history ADD COLUMN scheduled_duration INTERVAL;
ALTER TABLE music_sessions_history ADD COLUMN musician_access BOOLEAN NOT NULL DEFAULT TRUE;
ALTER TABLE music_sessions_history ADD COLUMN approval_required BOOLEAN NOT NULL DEFAULT FALSE;
ALTER TABLE music_sessions_history ADD COLUMN fan_chat BOOLEAN NOT NULL DEFAULT TRUE;
ALTER TABLE music_sessions_history ADD COLUMN genre_id VARCHAR(64) REFERENCES genres(id);
ALTER TABLE music_sessions_history ADD COLUMN legal_policy VARCHAR(255) NOT NULL DEFAULT 'standard';
ALTER TABLE music_sessions_history ADD COLUMN language VARCHAR(255) NOT NULL DEFAULT 'en';
ALTER TABLE music_sessions_history ADD COLUMN name TEXT;

-- get rid of genres in favor of just genre_id (no more multi-genres for a session)
UPDATE music_sessions_history SET name = description;
ALTER TABLE music_sessions_history ALTER COLUMN name SET NOT NULL;
-- production db has some null genres on older sessions
UPDATE music_sessions_history SET genres = 'rock' where genres = '';
UPDATE music_sessions_history SET genre_id = genres;
ALTER TABLE music_sessions_history ALTER COLUMN genre_id SET NOT NULL;
ALTER TABLE music_sessions_history DROP COLUMN genres;

-- likers should refer to id field of music_sessions_history (not music_session_id)
ALTER TABLE music_sessions_likers ADD COLUMN music_session_id2 VARCHAR(64) REFERENCES music_sessions_history(id) ON DELETE CASCADE;
-- production db has some bad data
DELETE from music_sessions_likers where music_session_id NOT IN (select id from music_sessions_history);
UPDATE      music_sessions_likers SET music_session_id2 = music_session_id;
ALTER TABLE music_sessions_likers DROP COLUMN music_session_id;
ALTER TABLE music_sessions_likers RENAME COLUMN music_session_id2 to music_session_id;

-- comments should refer to id field of music_sessions_history (not music_session_id)
ALTER TABLE music_sessions_comments ADD COLUMN music_session_id2 VARCHAR(64) REFERENCES music_sessions_history(id) ON DELETE CASCADE;
-- production db has some bad data
DELETE from music_sessions_comments where music_session_id NOT IN (select id from music_sessions_history);
UPDATE      music_sessions_comments SET music_session_id2 = music_session_id;
ALTER TABLE music_sessions_comments DROP COLUMN music_session_id;
ALTER TABLE music_sessions_comments RENAME COLUMN music_session_id2 to music_session_id;

-- user_history should refer to id field of music_sessions_history (not music_session_id)
ALTER TABLE music_sessions_user_history ADD COLUMN music_session_id2 VARCHAR(64) REFERENCES music_sessions_history(id) ON DELETE CASCADE;
-- production db has some bad data
DELETE from music_sessions_user_history where music_session_id NOT IN (select id from music_sessions_history);
UPDATE      music_sessions_user_history SET music_session_id2 = music_session_id;
ALTER TABLE music_sessions_user_history DROP COLUMN music_session_id;
ALTER TABLE music_sessions_user_history RENAME COLUMN music_session_id2 to music_session_id;

-- get rid of display fields on music_sessions
ALTER TABLE music_sessions DROP COLUMN musician_access;
ALTER TABLE music_sessions DROP COLUMN fan_access;
ALTER TABLE music_sessions DROP COLUMN description;
ALTER TABLE music_sessions DROP COLUMN fan_chat;
ALTER TABLE music_sessions DROP COLUMN approval_required;
ALTER TABLE music_sessions DROP COLUMN band_id;

ALTER TABLE music_sessions_history ALTER COLUMN music_session_id DROP NOT NULL;

-- create RSVP slots
CREATE TABLE rsvp_slots (
  id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4() NOT NULL,
  instrument_id VARCHAR(64) REFERENCES instruments (id),
  proficiency_level VARCHAR(255) NOT NULL,
  music_session_id VARCHAR(64) NOT NULL REFERENCES music_sessions_history (id) ON DELETE CASCADE,
  created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW() NOT NULL
);

-- create RSVP requests
CREATE TABLE rsvp_requests (
  id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4() NOT NULL,
  user_id VARCHAR(64) NOT NULL REFERENCES users (id) ON DELETE CASCADE,
  rsvp_slot_id VARCHAR(64) NOT NULL REFERENCES rsvp_slots(id) ON DELETE CASCADE,
  message TEXT,
  chosen BOOLEAN DEFAULT FALSE,
  canceled BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW() NOT NULL
);

CREATE TABLE recurring_sessions (
  id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4() NOT NULL,
  description VARCHAR(8000),
  scheduled_start TIMESTAMP WITH TIME ZONE,
  scheduled_duration INTERVAL,
  musician_access BOOLEAN NOT NULL,
  approval_required BOOLEAN NOT NULL,
  fan_chat BOOLEAN NOT NULL,
  genre_id VARCHAR(64) REFERENCES genres(id),
  legal_policy VARCHAR(255) NOT NULL DEFAULT 'standard',
  language VARCHAR(255) NOT NULL DEFAULT 'en',
  name TEXT,
  user_id VARCHAR(64) NOT NULL REFERENCES users (id) ON DELETE CASCADE,
  band_id VARCHAR(64) REFERENCES bands(id) ON DELETE CASCADE,
  created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW() NOT NULL
);

ALTER TABLE music_sessions_history ADD COLUMN recurring_session_id VARCHAR(64) REFERENCES recurring_sessions(id);

-- make these 3 tables be LOGGED, and refer to music_sessions_history instead of music_sessions
DROP TABLE fan_invitations;
DROP TABLE invitations;
DROP TABLE join_requests;


CREATE TABLE fan_invitations (
    id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4() NOT NULL,
    sender_id VARCHAR(64),
    receiver_id VARCHAR(64),
    music_session_id VARCHAR(64),
    created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW() NOT NULL,
    updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW() NOT NULL
);
ALTER TABLE ONLY fan_invitations ADD CONSTRAINT fan_invitations_music_session_id_fkey FOREIGN KEY (music_session_id) REFERENCES music_sessions_history(id) ON DELETE CASCADE;

CREATE UNLOGGED TABLE join_requests (
    id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4() NOT NULL,
    user_id VARCHAR(64),
    music_session_id VARCHAR(64),
    text VARCHAR(2000),
    created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW() NOT NULL,
    updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW() NOT NULL
);
ALTER TABLE ONLY join_requests ADD CONSTRAINT user_music_session_uniqkey UNIQUE (user_id, music_session_id);
ALTER TABLE ONLY join_requests ADD CONSTRAINT join_requests_music_session_id_fkey FOREIGN KEY (music_session_id) REFERENCES music_sessions_history(id) ON DELETE CASCADE;

-- INVITATIONS
--------------
CREATE UNLOGGED TABLE invitations (
    id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4() NOT NULL,
    sender_id VARCHAR(64),
    receiver_id VARCHAR(64),
    music_session_id VARCHAR(64),
    created_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW() NOT NULL,
    updated_at TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW() NOT NULL,
    join_request_id VARCHAR(64)
);
ALTER TABLE ONLY invitations ADD CONSTRAINT invitations_uniqkey UNIQUE (sender_id, receiver_id, music_session_id);
ALTER TABLE ONLY invitations ADD CONSTRAINT invitations_join_request_id_fkey FOREIGN KEY (join_request_id) REFERENCES join_requests(id) ON DELETE CASCADE;
ALTER TABLE ONLY invitations ADD CONSTRAINT invitations_music_session_id_fkey FOREIGN KEY (music_session_id) REFERENCES music_sessions_history(id) ON DELETE CASCADE;


-- finally, rename music_sessions and music_sessions_history to reflect true nature better
ALTER TABLE music_sessions RENAME TO active_music_sessions;
ALTER TABLE music_sessions_history RENAME TO music_sessions;

-- add fk to chat_messages so they delete cleanly when users are deleted
ALTER TABLE ONLY chat_messages ADD CONSTRAINT chat_messages_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

-- fix any promotionals
UPDATE promotionals SET latest_type = 'JamRuby::MusicSession' WHERE latest_type = 'JamRuby::MusicSessionHistory';