-- migrate:up

-- Associations are managed like courses: same tasks, files and slots, without grades.
ALTER TABLE courses ADD COLUMN kind TEXT NOT NULL DEFAULT 'course'
    CHECK (kind IN ('course', 'association'));
ALTER TABLE courses ALTER COLUMN kind DROP DEFAULT;

-- Folder of the course in the storage (the NAS), relative to its root, named after the
-- course: `Cours/CS-477 Advanced operating systems`. Moved when the course is renamed.
-- The existing folders were named after the ids and are not moved by this migration.
ALTER TABLE courses ADD COLUMN folder TEXT;
UPDATE courses SET folder = 'Cours/' || trim(regexp_replace(
    regexp_replace(COALESCE(code || ' ', '') || "name", '[/\\:*?"<>|]', '-', 'g'),
    '\s+', ' ', 'g'));
ALTER TABLE courses ALTER COLUMN folder SET NOT NULL;
ALTER TABLE courses ADD CONSTRAINT courses_folder_key UNIQUE (folder);

-- Path of a file in the folder of its course, without extension: `Notes/Lecture 1 Notes`
-- (the note is `Notes/Lecture 1 Notes.md` and its pdf `Notes/Lecture 1 Notes.pdf`)
ALTER TABLE course_files ADD COLUMN "path" TEXT;
UPDATE course_files SET "path" = 'Notes/' || trim(regexp_replace(
    regexp_replace("name", '[/\\:*?"<>|]', '-', 'g'), '\s+', ' ', 'g'));
ALTER TABLE course_files ALTER COLUMN "path" SET NOT NULL;
ALTER TABLE course_files ADD CONSTRAINT course_files_course_id_path_key UNIQUE (course_id, "path");

-- Time spent working on a task, counted by the pomodoro
ALTER TABLE tasks ADD COLUMN time_spent INTEGER NOT NULL DEFAULT 0 CHECK (time_spent >= 0);

-- Kinds of the slots and tasks of the associations
INSERT INTO tags ("name", color, scopes, slug) VALUES
    ('{"fr": "Réunion", "en": "Meeting"}', '#db2777', '{slot,task}', 'meeting'),
    ('{"fr": "Événement", "en": "Event"}', '#4f46e5', '{slot,task}', 'event');

-- migrate:down
DELETE FROM courses WHERE kind = 'association';
DELETE FROM time_slots WHERE tag_id IN (SELECT id FROM tags WHERE slug IN ('meeting', 'event'));
DELETE FROM tags WHERE slug IN ('meeting', 'event');
ALTER TABLE tasks DROP COLUMN time_spent;
ALTER TABLE course_files DROP COLUMN "path";
ALTER TABLE courses DROP COLUMN folder;
ALTER TABLE courses DROP COLUMN kind;