feat: migrate db table

src/main/resources/db/migration/V1__baseline.sql

@@ -0,0 +1,62 @@
+-- Baseline schema: matches current JPA entities (Course, Lesson, Section + BaseEntity).
+-- History-only: flyway.enabled=false, runtime schema is maintained by Hibernate (ddl-auto: update).
+-- To rebuild tables in new shape, run this file manually against the lms schema (DESTROYS DATA),
+-- or drop tables and restart the app so Hibernate recreates them.
+
+DROP TABLE IF EXISTS sections CASCADE;
+DROP TABLE IF EXISTS lessons CASCADE;
+DROP TABLE IF EXISTS courses CASCADE;
+DROP TYPE IF EXISTS section_type;
+
+CREATE TABLE courses (
+    id BIGSERIAL PRIMARY KEY,
+    course_code VARCHAR(50) NOT NULL UNIQUE,
+    title VARCHAR(200) NOT NULL,
+    slug VARCHAR(255) NOT NULL UNIQUE,
+    description VARCHAR(1000),
+    image VARCHAR(500),
+    is_published BOOLEAN NOT NULL,
+    created_at TIMESTAMP(6) NOT NULL,
+    created_by VARCHAR(50),
+    updated_at TIMESTAMP(6),
+    updated_by VARCHAR(50),
+    deleted_at TIMESTAMP(6),
+    deleted_by VARCHAR(50)
+);
+
+CREATE TABLE lessons (
+    id BIGSERIAL PRIMARY KEY,
+    course_id BIGINT NOT NULL REFERENCES courses(id) ON DELETE CASCADE,
+    title VARCHAR(200) NOT NULL,
+    slug VARCHAR(255) NOT NULL UNIQUE,
+    description VARCHAR(1000),
+    position INTEGER NOT NULL,
+    is_published BOOLEAN NOT NULL,
+    created_at TIMESTAMP(6) NOT NULL,
+    created_by VARCHAR(50),
+    updated_at TIMESTAMP(6),
+    updated_by VARCHAR(50),
+    deleted_at TIMESTAMP(6),
+    deleted_by VARCHAR(50)
+);
+
+CREATE INDEX idx_lessons_course_id ON lessons(course_id);
+
+CREATE TABLE sections (
+    id BIGSERIAL PRIMARY KEY,
+    lesson_id BIGINT NOT NULL REFERENCES lessons(id) ON DELETE CASCADE,
+    title VARCHAR(255) NOT NULL,
+    type VARCHAR(255) NOT NULL,
+    position INTEGER NOT NULL,
+    content JSONB,
+    duration_seconds INTEGER NOT NULL DEFAULT 0,
+    is_free_preview BOOLEAN NOT NULL,
+    created_at TIMESTAMP(6) NOT NULL,
+    created_by VARCHAR(50),
+    updated_at TIMESTAMP(6),
+    updated_by VARCHAR(50),
+    deleted_at TIMESTAMP(6),
+    deleted_by VARCHAR(50)
+);
+
+CREATE INDEX idx_sections_lesson_id ON sections(lesson_id);

src/main/resources/db/migration/V1__course_module.sql

@@ -1,8 +0,0 @@
--- Flyway Migration V1: Initial schema
--- Creates courses table
-
-CREATE TABLE IF NOT EXISTS courses (
-    id BIGSERIAL PRIMARY KEY,
-    title VARCHAR(200) NOT NULL,
-    description VARCHAR(1000)
-);

src/main/resources/db/migration/V2__lesson_module.sql

@@ -1,14 +0,0 @@
--- Flyway Migration V3: Create lessons table
-
-CREATE TABLE IF NOT EXISTS lessons (
-    id BIGSERIAL PRIMARY KEY,
-    lesson_code VARCHAR(50) NOT NULL UNIQUE,
-    lesson_name VARCHAR(200) NOT NULL,
-    description VARCHAR(1000),
-    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
-    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
-    created_by VARCHAR(255),
-    updated_by VARCHAR(255),
-    deleted_at TIMESTAMPTZ,
-    deleted_by VARCHAR(255)
-);

src/main/resources/db/migration/V3__update_course_add_fields.sql

@@ -1,16 +0,0 @@
--- Flyway Migration V3: Update courses table
--- Add course_code, slug, image, is_published
-
-ALTER TABLE courses ADD COLUMN course_code VARCHAR(50);
-ALTER TABLE courses ADD COLUMN slug VARCHAR(255);
-ALTER TABLE courses ADD COLUMN image VARCHAR(500);
-ALTER TABLE courses ADD COLUMN is_published BOOLEAN NOT NULL DEFAULT false;
-
-ALTER TABLE courses ADD CONSTRAINT uq_course_code UNIQUE (course_code);
-ALTER TABLE courses ADD CONSTRAINT uq_course_slug UNIQUE (slug);
-
-UPDATE courses SET slug = LOWER(REPLACE(REPLACE(TRIM(title), ' ', '-'), '.', ''));
-UPDATE courses SET course_code = 'CRS-' || id;
-
-ALTER TABLE courses ALTER COLUMN course_code SET NOT NULL;
-ALTER TABLE courses ALTER COLUMN slug SET NOT NULL;

src/main/resources/db/migration/V4__update_lesson_add_course_fk_and_fields.sql

@@ -1,26 +0,0 @@
--- Flyway Migration V4: Update lessons table
--- Add course_id FK, slug, position, is_published; remove lesson_code
-
-ALTER TABLE lessons ADD COLUMN course_id BIGINT;
-ALTER TABLE lessons ADD COLUMN slug VARCHAR(255);
-ALTER TABLE lessons ADD COLUMN position INT;
-ALTER TABLE lessons ADD COLUMN is_published BOOLEAN NOT NULL DEFAULT false;
-
-ALTER TABLE lessons RENAME COLUMN lesson_name TO title;
-
-UPDATE lessons SET slug = LOWER(REPLACE(REPLACE(TRIM(title), ' ', '-'), '.', ''));
-
-ALTER TABLE lessons ALTER COLUMN course_id SET NOT NULL;
-ALTER TABLE lessons ALTER COLUMN slug SET NOT NULL;
-ALTER TABLE lessons ALTER COLUMN position SET NOT NULL;
-
-ALTER TABLE lessons ADD CONSTRAINT fk_lesson_course
-    FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE;
-
-ALTER TABLE lessons ADD CONSTRAINT uq_course_lesson_position UNIQUE (course_id, position);
-ALTER TABLE lessons ADD CONSTRAINT uq_course_lesson_slug UNIQUE (course_id, slug);
-
-ALTER TABLE lessons DROP CONSTRAINT IF EXISTS lessons_lesson_code_key;
-ALTER TABLE lessons DROP COLUMN lesson_code;
-
-CREATE INDEX idx_lessons_course_id ON lessons(course_id);

src/main/resources/db/migration/V5__create_section_module.sql

@@ -1,23 +0,0 @@
--- Flyway Migration V5: Create sections table
-
-CREATE TYPE section_type AS ENUM ('text', 'video', 'quiz', 'embed');
-
-CREATE TABLE sections (
-    id BIGSERIAL PRIMARY KEY,
-    lesson_id BIGINT NOT NULL REFERENCES lessons(id) ON DELETE CASCADE,
-    title VARCHAR(255) NOT NULL,
-    type section_type NOT NULL DEFAULT 'text',
-    position INT NOT NULL,
-    content JSONB,
-    duration_seconds INT DEFAULT 0,
-    is_free_preview BOOLEAN NOT NULL DEFAULT false,
-    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
-    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
-    created_by VARCHAR(255),
-    updated_by VARCHAR(255),
-    deleted_at TIMESTAMPTZ,
-    deleted_by VARCHAR(255),
-    CONSTRAINT uq_lesson_section_position UNIQUE (lesson_id, position)
-);
-
-CREATE INDEX idx_sections_lesson_id ON sections(lesson_id);