
-- this is to make sure we don't delete any recordings for 7 days
UPDATE recordings SET updated_at = NOW();

ALTER TABLE recordings ADD COLUMN deleted BOOLEAN DEFAULT FALSE NOT NULL;


DROP VIEW user_syncs;

CREATE VIEW user_syncs AS
    SELECT  DISTINCT 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 AND deleted = 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 AND deleted = 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 AND deleted = FALSE;
