Changes between Version 1 and Version 2 of UseCase0001PrototypeImplementationDB

Timestamp:

01/27/26 23:31:27 (3 days ago)

Author:

231175

Comment:

--

UseCase0001PrototypeImplementationDB

@@ -17 +17 @@
 * Експертот е навигиран кон почетната страна за експерти. Во база се креира новиот курс.
 {{{
+DROP TABLE IF EXISTS temp_course_id;
+DROP TABLE IF EXISTS temp_course_version_id;
+DROP TABLE IF EXISTS temp_course_translate_id;
+DROP TABLE IF EXISTS temp_course_content_id;
+DROP TABLE IF EXISTS temp_course_lecture_id;
+DROP TABLE IF EXISTS temp_tag_id;
 
+CREATE TEMP TABLE temp_course_id (id BIGINT);
+CREATE TEMP TABLE temp_course_version_id (id BIGINT);
+CREATE TEMP TABLE temp_course_translate_id (id BIGINT, language VARCHAR(2));
+CREATE TEMP TABLE temp_course_content_id (id BIGINT);
+CREATE TEMP TABLE temp_course_lecture_id (id BIGINT);
+CREATE TEMP TABLE temp_tag_id (id BIGINT, type VARCHAR(50));
+
+WITH new_course AS (
+    INSERT INTO course (image_url, difficulty, duration_minutes, price, color)
+    VALUES ('https://example.com/image.png', 'INTERMEDIATE', 120, 50, '#008CC2')
+    RETURNING id
+)
+INSERT INTO temp_course_id (id)
+SELECT id FROM new_course;
+
+WITH new_course_version AS (
+    INSERT INTO course_version (version_number, creation_date, active, course_id)
+    SELECT 1, CURRENT_DATE, true, id FROM temp_course_id
+    RETURNING id
+)
+INSERT INTO temp_course_version_id (id)
+SELECT id FROM new_course_version;
+
+WITH new_course_translate_en AS (
+    INSERT INTO course_translate (language, title_short, title, description_short, description, description_long, course_id)
+    SELECT 'en', 'Advanced Sales', 'Advanced Sales For Senior Salesman', 'Short desc', 'Normal desc', 'Long desc', id FROM temp_course_id
+    RETURNING id, language
+)
+INSERT INTO temp_course_translate_id (id, language)
+SELECT id, language FROM new_course_translate_en;
+
+WITH new_course_translate_mk AS (
+    INSERT INTO course_translate (language, title_short, title, description_short, description, description_long, course_id)
+    SELECT 'mk', 'Напредна Продажба', 'Напредна Продажба за Сениор Продавачи', 'Кратка дескрипција', 'Нормална дескрипција', 'Долга дескрипција', id FROM temp_course_id
+    RETURNING id, language
+)
+INSERT INTO temp_course_translate_id (id, language)
+SELECT id, language FROM new_course_translate_mk;
+
+WITH new_course_content AS (
+    INSERT INTO course_content (position, course_version_id)
+    SELECT 1, id FROM temp_course_version_id
+    RETURNING id
+)
+INSERT INTO temp_course_content_id (id)
+SELECT id FROM new_course_content;
+
+INSERT INTO course_content_translate (title, language, course_content_id)
+SELECT 'Module 1: Sales Strategies', 'en', id FROM temp_course_content_id;
+
+INSERT INTO course_content_translate (title, language, course_content_id)
+SELECT 'Модул 1: Продажни Стратегии', 'mk', id FROM temp_course_content_id;
+
+WITH new_course_lecture AS (
+    INSERT INTO course_lecture (duration_minutes, position, content_type, course_content_id)
+    SELECT 30, 1, 'video', id FROM temp_course_content_id
+    RETURNING id
+)
+INSERT INTO temp_course_lecture_id (id)
+SELECT id FROM new_course_lecture;
+
+INSERT INTO course_lecture_translate (title, language, content_file_name, description, content_text, course_lecture_id)
+SELECT 'Lecture 1: Intro', 'en', 'lecture1.mp4', 'Introduction to advanced sales', 'Video text content', id
+FROM temp_course_lecture_id;
+
+INSERT INTO course_lecture_translate (title, language, content_file_name, description, content_text, course_lecture_id)
+SELECT 'Предавање 1: Вовед', 'mk', 'lecture1.mp4', 'Вовед во напредна продажба', 'Тмп', id
+FROM temp_course_lecture_id;
+
+WITH new_tags AS (
+    INSERT INTO tag (type)
+    VALUES ('skill'), ('interest')
+    RETURNING id, type
+)
+INSERT INTO temp_tag_id (id, type)
+SELECT id, type FROM new_tags;
+
+INSERT INTO tag_translate (language, value, tag_id)
+SELECT 'en', CASE type WHEN 'skill' THEN 'Skill' WHEN 'interest' THEN 'Interest' END, id FROM temp_tag_id;
+
+INSERT INTO tag_translate (language, value, tag_id)
+SELECT 'mk', CASE type WHEN 'skill' THEN 'Вештина' WHEN 'interest' THEN 'Интерес' END, id FROM temp_tag_id;
+
+INSERT INTO course_tag (tag_id, course_id)
+SELECT t.id, c.id
+FROM temp_tag_id t
+CROSS JOIN temp_course_id c;
+
+INSERT INTO expert_course (course_id, expert_id)
+SELECT id, 1
+FROM temp_course_id;
+
+INSERT INTO course_translate_what_will_be_learned (course_translate_id, what_will_be_learned)
+SELECT id, 'Advanced sales techniques'
+FROM temp_course_translate_id
+WHERE language = 'en';
+
+INSERT INTO course_translate_what_will_be_learned (course_translate_id, what_will_be_learned)
+SELECT id, 'How to successfully close deals'
+FROM temp_course_translate_id
+WHERE language = 'en';
+
+INSERT INTO course_translate_what_will_be_learned (course_translate_id, what_will_be_learned)
+SELECT id, 'Напредни техники на продажба'
+FROM temp_course_translate_id
+WHERE language = 'mk';
+
+INSERT INTO course_translate_what_will_be_learned (course_translate_id, what_will_be_learned)
+SELECT id, 'Како до успешно затворање на продажби'
+FROM temp_course_translate_id
+WHERE language = 'mk';
 }}}