ALTER TABLE recordings ADD COLUMN has_stream_mix BOOLEAN DEFAULT FALSE NOT NULL;
ALTER TABLE recordings ADD COLUMN has_final_mix BOOLEAN DEFAULT FALSE NOT NULL;
UPDATE recordings SET has_final_mix = TRUE WHERE (SELECT count(mixes.id) FROM mixes WHERE recording_id = recordings.id) > 0;

CREATE TABLE quick_mixes (
  id BIGINT PRIMARY KEY,
  next_part_to_upload INTEGER NOT NULL DEFAULT 0,
  fully_uploaded BOOLEAN NOT NULL DEFAULT FALSE,
  upload_id VARCHAR(1024),
  file_offset BIGINT DEFAULT 0,
  is_part_uploading BOOLEAN NOT NULL DEFAULT FALSE,
  upload_failures INTEGER DEFAULT 0,
  part_failures INTEGER DEFAULT 0,
  ogg_md5 VARCHAR(100),
  ogg_length INTEGER,
  ogg_url VARCHAR(1000),
  mp3_md5 VARCHAR(100),
  mp3_length INTEGER,
  mp3_url VARCHAR(1000),
  error_count INTEGER NOT NULL DEFAULT 0,
  error_reason TEXT,
  error_detail TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  started_at TIMESTAMP,
  completed_at TIMESTAMP,
  completed BOOLEAN NOT NULL DEFAULT FALSE,
  should_retry BOOLEAN NOT NULL DEFAULT FALSE,
  cleaned BOOLEAN NOT NULL DEFAULT FALSE,
  user_id VARCHAR(64) REFERENCES users(id) ON DELETE SET NULL,
  recording_id VARCHAR(64) REFERENCES recordings(id) ON DELETE CASCADE
);

ALTER TABLE quick_mixes ALTER COLUMN id SET DEFAULT nextval('tracks_next_tracker_seq');

CREATE VIEW user_syncs AS
    SELECT  b.id AS recorded_track_id,
            CAST(NULL as BIGINT) AS mix_id,
            CAST(NULL as BIGINT) as quick_mix_id,
            b.id AS unified_id,
            a.user_id AS user_id,
            b.fully_uploaded,
            recordings.created_at AS created_at,
            recordings.id AS recording_id
    FROM recorded_tracks a INNER JOIN recordings ON a.recording_id = recordings.id AND duration IS NOT NULL AND all_discarded = FALSE INNER JOIN recorded_tracks b ON a.recording_id = b.recording_id
    UNION ALL
    SELECT  CAST(NULL as BIGINT) AS recorded_track_id,
            mixes.id AS mix_id,
            CAST(NULL as BIGINT) AS quick_mix_id,
            mixes.id AS unified_id,
            claimed_recordings.user_id AS user_id,
            NULL as fully_uploaded,
            recordings.created_at AS created_at,
            recordings.id AS recording_id
    FROM mixes INNER JOIN recordings ON mixes.recording_id = recordings.id INNER JOIN claimed_recordings ON recordings.id = claimed_recordings.recording_id WHERE claimed_recordings.discarded = FALSE
    UNION ALL
    SELECT  CAST(NULL as BIGINT) AS recorded_track_id,
            CAST(NULL as BIGINT) AS mix_id,
            quick_mixes.id AS quick_mix_id,
            quick_mixes.id AS unified_id,
            quick_mixes.user_id,
            quick_mixes.fully_uploaded,
            recordings.created_at AS created_at,
            recordings.id AS recording_id
    FROM quick_mixes INNER JOIN recordings ON quick_mixes.recording_id = recordings.id AND duration IS NOT NULL AND all_discarded = FALSE;

ALTER TABLE recordings ADD COLUMN first_quick_mix_id BIGINT REFERENCES quick_mixes(id) ON DELETE SET NULL;