73 lines
2.6 KiB
SQL
73 lines
2.6 KiB
SQL
-- Rollback: Restore old course hierarchy
|
|
-- Note: This will lose any new data created with the simplified structure
|
|
|
|
-- Step 1: Recreate old tables
|
|
CREATE TABLE IF NOT EXISTS programs (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
course_id BIGINT NOT NULL REFERENCES courses(id) ON DELETE CASCADE,
|
|
title VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
thumbnail TEXT,
|
|
display_order INT NOT NULL DEFAULT 0,
|
|
is_active BOOLEAN NOT NULL DEFAULT TRUE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS levels (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
program_id BIGINT NOT NULL REFERENCES programs(id) ON DELETE CASCADE,
|
|
title VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
level_index INT NOT NULL,
|
|
number_of_modules INT NOT NULL DEFAULT 0,
|
|
number_of_practices INT NOT NULL DEFAULT 0,
|
|
number_of_videos INT NOT NULL DEFAULT 0,
|
|
is_active BOOLEAN NOT NULL DEFAULT TRUE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS modules (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
level_id BIGINT NOT NULL REFERENCES levels(id) ON DELETE CASCADE,
|
|
title VARCHAR(255) NOT NULL,
|
|
content TEXT,
|
|
display_order INT NOT NULL DEFAULT 0,
|
|
is_active BOOLEAN NOT NULL DEFAULT TRUE
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS module_videos (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
module_id BIGINT NOT NULL REFERENCES modules(id) ON DELETE CASCADE,
|
|
title VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
video_url TEXT NOT NULL,
|
|
duration INT NOT NULL,
|
|
resolution VARCHAR(20),
|
|
is_published BOOLEAN NOT NULL DEFAULT FALSE,
|
|
publish_date TIMESTAMPTZ,
|
|
visibility VARCHAR(50),
|
|
instructor_id VARCHAR(100),
|
|
thumbnail TEXT,
|
|
is_active BOOLEAN NOT NULL DEFAULT TRUE
|
|
);
|
|
|
|
-- Step 2: Restore practices polymorphic columns
|
|
ALTER TABLE practices ADD COLUMN IF NOT EXISTS owner_type VARCHAR(50);
|
|
ALTER TABLE practices ADD COLUMN IF NOT EXISTS owner_id BIGINT;
|
|
|
|
-- Step 3: Recreate old indexes
|
|
CREATE INDEX IF NOT EXISTS idx_programs_course_id ON programs(course_id);
|
|
CREATE INDEX IF NOT EXISTS idx_levels_program_id ON levels(program_id);
|
|
CREATE INDEX IF NOT EXISTS idx_modules_level_id ON modules(level_id);
|
|
CREATE INDEX IF NOT EXISTS idx_videos_module_id ON module_videos(module_id);
|
|
CREATE INDEX IF NOT EXISTS idx_practices_owner ON practices(owner_type, owner_id);
|
|
|
|
-- Step 4: Drop new tables
|
|
ALTER TABLE practices DROP CONSTRAINT IF EXISTS practices_sub_course_id_fkey;
|
|
DROP INDEX IF EXISTS idx_practices_sub_course_id;
|
|
ALTER TABLE practices DROP COLUMN IF EXISTS sub_course_id;
|
|
|
|
DROP TABLE IF EXISTS sub_course_videos CASCADE;
|
|
DROP TABLE IF EXISTS sub_courses CASCADE;
|
|
|
|
-- Step 5: Add back constraint on practices
|
|
ALTER TABLE practices ADD CONSTRAINT practices_owner_type_check CHECK (owner_type IN ('LEVEL', 'MODULE'));
|