Креирање на курс

Актери

• Најавен експерт

Чекор 1

При клик на копчето за креирање на курс на навбарот на корисникот му се прикажува форма со полиња. При пополнување на овие полиња и клик на копчето "Create" се креира курс во базата.

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';