-- migrate:up

-- What each tag can label: time slots, tasks and/or course files
ALTER TABLE tags ADD COLUMN scopes TEXT[] NOT NULL DEFAULT '{}'
    CHECK (scopes <@ ARRAY['slot', 'task', 'file']);
-- Stable name, for the tags the backend needs to find (e.g. `notes` for the lecture notes)
ALTER TABLE tags ADD COLUMN slug TEXT UNIQUE;
UPDATE tags SET scopes = '{slot,task}', slug = lower("name"->>'en')
    WHERE "name"->>'en' IN ('Lecture', 'Exercises', 'Lab');
UPDATE tags SET scopes = '{task}', slug = lower("name"->>'en')
    WHERE "name"->>'en' IN ('Homework', 'Exam', 'Project');
INSERT INTO tags ("name", color, scopes, slug) VALUES
    ('{"fr": "Slides", "en": "Slides"}', '#0d9488', '{file}', 'slides'),
    ('{"fr": "Notes", "en": "Notes"}', '#ca8a04', '{file}', 'notes');

-- The lecture notes become the first kind of course files. Uploaded files (slides...)
-- will be the second one. Ids are kept: they are part of the paths in the storage.
ALTER TABLE lecture_notes RENAME TO course_files;
ALTER TABLE course_files RENAME CONSTRAINT lecture_notes_pkey TO course_files_pkey;
ALTER TABLE course_files RENAME CONSTRAINT lecture_notes_course_id_fkey TO course_files_course_id_fkey;
ALTER SEQUENCE lecture_notes_id_seq RENAME TO course_files_id_seq;
ALTER INDEX lecture_notes_course_id_idx RENAME TO course_files_course_id_idx;
ALTER TABLE course_files RENAME COLUMN title TO "name";
-- `note`: markdown written in the app and rendered to pdf, `upload`: any file
ALTER TABLE course_files ADD COLUMN kind TEXT NOT NULL DEFAULT 'note'
    CHECK (kind IN ('note', 'upload'));
ALTER TABLE course_files ALTER COLUMN kind DROP DEFAULT;

CREATE TABLE file_tags (
    file_id INTEGER NOT NULL REFERENCES course_files (id) ON DELETE CASCADE,
    tag_id INTEGER NOT NULL REFERENCES tags (id) ON DELETE CASCADE,
    PRIMARY KEY (file_id, tag_id)
);

INSERT INTO file_tags (file_id, tag_id)
SELECT f.id, t.id FROM course_files f, tags t WHERE t.slug = 'notes';

-- migrate:down
DROP TABLE IF EXISTS file_tags;
DELETE FROM course_files WHERE kind <> 'note';
ALTER TABLE course_files DROP COLUMN kind;
ALTER TABLE course_files RENAME COLUMN "name" TO title;
ALTER INDEX course_files_course_id_idx RENAME TO lecture_notes_course_id_idx;
ALTER SEQUENCE course_files_id_seq RENAME TO lecture_notes_id_seq;
ALTER TABLE course_files RENAME CONSTRAINT course_files_course_id_fkey TO lecture_notes_course_id_fkey;
ALTER TABLE course_files RENAME CONSTRAINT course_files_pkey TO lecture_notes_pkey;
ALTER TABLE course_files RENAME TO lecture_notes;

DELETE FROM tags WHERE scopes = '{file}';
ALTER TABLE tags DROP COLUMN slug;
ALTER TABLE tags DROP COLUMN scopes;