Changes between Initial Version and Version 1 of dbdevelopment

Timestamp:

01/30/26 17:36:28 (4 hours ago)

Author:

231175

Comment:

--

dbdevelopment

@@ +1 @@
+= Напреден развој на базата =
+
+== Правила за запишување на курс (Enrollment) ==
+
+=== Опис на барањата за податочни ограничувања ===
+
+Системот мора да обезбеди дека:
+* Кога корисникот се запишува на курс по даден course_id, автоматски се избира и запишува моментално активната верзија на курсот (course_version.active = true) за тој курс.
+* Не е дозволено запишување на курс за кој воопшто нема активна верзија.
+* Секоја верзија се третира независно: корисникот може да има повеќе запишувања на различни верзии на истиот курс, без разлика дали претходните се завршени или не.
+
+=== Имплементација ===
+
+==== Тригери ====
+
+{{{
+CREATE OR REPLACE FUNCTION set_active_course_version_on_enrollment()
+RETURNS TRIGGER 
+AS $$
+DECLARE
+    v_course_version_id INTEGER;
+BEGIN
+    SELECT cv.id
+    INTO v_course_version_id
+    FROM course_version cv
+    WHERE cv.course_id = NEW.course_id
+      AND cv.active = TRUE
+    ORDER BY cv.version_number DESC
+    LIMIT 1;
+    
+    IF v_course_version_id IS NULL THEN
+        RAISE EXCEPTION 'No active course version found for course_id=%', NEW.course_id;
+    END IF;
+    
+    NEW.course_version_id := v_course_version_id;
+    
+    RETURN NEW;
+END;
+$$ 
+LANGUAGE plpgsql;
+
+CREATE TRIGGER trg_set_active_course_version_on_enrollment
+BEFORE INSERT ON enrollment
+FOR EACH ROW
+EXECUTE FUNCTION set_active_course_version_on_enrollment();
+}}}
+
+==== Функции / Stored Procedures ====
+
+{{{
+CREATE OR REPLACE FUNCTION create_enrollment_for_active_version(p_user_id INTEGER, p_course_id INTEGER)
+RETURNS INTEGER 
+AS $$
+DECLARE
+    v_course_version_id INTEGER;
+    v_enrollment_id INTEGER;
+BEGIN
+    SELECT cv.id
+    INTO v_course_version_id
+    FROM course_version cv
+    WHERE cv.course_id = p_course_id
+      AND cv.active = TRUE
+    ORDER BY cv.version_number DESC
+    LIMIT 1;
+    
+    IF v_course_version_id IS NULL THEN
+        RAISE EXCEPTION 'No active course version found for course_id=%', p_course_id;
+    END IF;
+    
+    INSERT INTO enrollment (user_id, course_id, course_version_id, enrollment_purchase_date, status)
+    VALUES (p_user_id, p_course_id, v_course_version_id, NOW(), 'PENDING')
+    RETURNING id INTO v_enrollment_id;
+    
+    RETURN v_enrollment_id;
+END;
+$$ 
+LANGUAGE plpgsql;
+}}}
+
+==== Погледи (Views) ====
+
+{{{
+CREATE OR REPLACE VIEW enrollments_with_active_version AS
+SELECT
+    e.id AS enrollment_id,
+    u.id AS user_id,
+    u.name AS user_name,
+    c.id AS course_id,
+    ct.title_short AS course_title,
+    cv.id AS course_version_id,
+    cv.version_number,
+    cv.active,
+    e.enrollment_purchase_date,
+    e.status
+FROM enrollment e
+JOIN "user" u ON e.user_id = u.id
+JOIN course c ON e.course_id = c.id
+JOIN course_version cv ON e.course_version_id = cv.id
+JOIN course_translate ct ON c.id = ct.course_id AND ct.language = 'mk';
+}}}
+
+----
+
+== Менаџирање на верзии на курс (Course Version Management) ==
+
+=== Опис на барањата за податочни ограничувања ===
+
+Системот мора да обезбеди дека:
+* Само една верзија може да биде активна (active = true) по курс во исто време
+* Кога се активира нова верзија, претходните активни верзии на истиот курс автоматски се деактивираат
+* Не смее да се избрише верзија која има активни (незавршени) запишувања (enrollments)
+
+=== Имплементација ===
+
+==== Тригери ====
+
+{{{
+CREATE OR REPLACE FUNCTION ensure_single_active_version()
+RETURNS TRIGGER AS $$
+BEGIN
+    IF NEW.active = TRUE THEN
+        UPDATE course_version
+        SET active = FALSE
+        WHERE course_id = NEW.course_id
+          AND id != NEW.id
+          AND active = TRUE;
+    END IF;
+    
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER trg_ensure_single_active_version
+BEFORE UPDATE OF active ON course_version
+FOR EACH ROW
+WHEN (NEW.active = TRUE)
+EXECUTE FUNCTION ensure_single_active_version();
+}}}
+
+{{{
+CREATE OR REPLACE FUNCTION prevent_version_deletion_with_active_enrollments()
+RETURNS TRIGGER AS $$
+DECLARE
+    v_active_enrollments INTEGER;
+BEGIN
+    SELECT COUNT(*) INTO v_active_enrollments
+    FROM enrollment
+    WHERE course_version_id = OLD.id
+      AND completion_date IS NULL;
+    
+    IF v_active_enrollments > 0 THEN
+        RAISE EXCEPTION 'Cannot delete course version with % active enrollments', v_active_enrollments;
+    END IF;
+    
+    RETURN OLD;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER trg_prevent_version_deletion
+BEFORE DELETE ON course_version
+FOR EACH ROW
+EXECUTE FUNCTION prevent_version_deletion_with_active_enrollments();
+}}}
+
+==== Погледи (Views) ====
+
+{{{
+CREATE OR REPLACE VIEW course_latest_versions AS
+SELECT
+    c.id AS course_id,
+    ct.title_short AS course_title,
+    cv.id AS version_id,
+    cv.version_number,
+    cv.active,
+    cv.version_creation_date,
+    COUNT(DISTINCT e.id) FILTER (WHERE e.completion_date IS NULL) AS active_enrollments,
+    COUNT(DISTINCT e.id) AS total_enrollments
+FROM course c
+JOIN course_version cv ON c.id = cv.course_id
+JOIN course_translate ct ON c.id = ct.course_id AND ct.language = 'mk'
+LEFT JOIN enrollment e ON cv.id = e.course_version_id
+WHERE cv.version_number = (
+    SELECT MAX(version_number)
+    FROM course_version
+    WHERE course_id = c.id
+)
+GROUP BY c.id, ct.title_short, cv.id, cv.version_number, cv.active, cv.version_creation_date;
+}}}
+
+----
+
+== Валидација на рецензии и рејтинг (Review Validation & Rating) ==
+
+=== Опис на барањата за податочни ограничувања ===
+
+Системот мора да обезбеди дека:
+* Корисникот може да остави рецензија само ако enrollment е завршен (completion_date IS NOT NULL)
+* Еден корисник може да остави само една рецензија по enrollment
+* Рејтингот мора да биде валиден број од 1 до 5
+* Сите промени на рецензии се евидентираат во audit табела
+
+=== Имплементација ===
+
+==== Прилагодени домени (Custom Domains) ====
+
+{{{
+CREATE DOMAIN rating_scale AS INTEGER
+CHECK (VALUE >= 1 AND VALUE <= 5);
+
+ALTER TABLE review ALTER COLUMN rating TYPE rating_scale;
+}}}
+
+==== Тригери ====
+
+{{{
+CREATE OR REPLACE FUNCTION validate_review_before_insert()
+RETURNS TRIGGER AS $$
+DECLARE
+    v_completion_date TIMESTAMP;
+    v_existing_review_count INTEGER;
+BEGIN
+    SELECT completion_date INTO v_completion_date
+    FROM enrollment
+    WHERE id = NEW.enrollment_id;
+    
+    IF v_completion_date IS NULL THEN
+        RAISE EXCEPTION 'Cannot create review for incomplete enrollment';
+    END IF;
+    
+    SELECT COUNT(*) INTO v_existing_review_count
+    FROM review
+    WHERE enrollment_id = NEW.enrollment_id;
+    
+    IF v_existing_review_count > 0 THEN
+        RAISE EXCEPTION 'Review already exists for this enrollment';
+    END IF;
+    
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER trg_validate_review
+BEFORE INSERT ON review
+FOR EACH ROW
+EXECUTE FUNCTION validate_review_before_insert();
+}}}
+
+{{{
+CREATE OR REPLACE FUNCTION audit_review_changes()
+RETURNS TRIGGER AS $$
+BEGIN
+    IF TG_OP = 'INSERT' THEN
+        INSERT INTO review_audit (review_id, new_rating, new_comment, action)
+        VALUES (NEW.id, NEW.rating, NEW.comment, 'INSERT');
+    ELSIF TG_OP = 'UPDATE' THEN
+        INSERT INTO review_audit (review_id, old_rating, new_rating, old_comment, new_comment, action)
+        VALUES (NEW.id, OLD.rating, NEW.rating, OLD.comment, NEW.comment, 'UPDATE');
+    ELSIF TG_OP = 'DELETE' THEN
+        INSERT INTO review_audit (review_id, old_rating, old_comment, action)
+        VALUES (OLD.id, OLD.rating, OLD.comment, 'DELETE');
+    END IF;
+    
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER trg_audit_review
+AFTER INSERT OR UPDATE OR DELETE ON review
+FOR EACH ROW
+EXECUTE FUNCTION audit_review_changes();
+}}}
+
+==== Погледи (Views) ====
+
+{{{
+CREATE OR REPLACE VIEW course_average_ratings AS
+SELECT
+    c.id AS course_id,
+    ct.title_short AS course_title,
+    COUNT(r.id) AS total_reviews,
+    AVG(r.rating)::NUMERIC(3,2) AS average_rating,
+    COUNT(r.id) FILTER (WHERE r.rating = 5) AS five_star_count,
+    COUNT(r.id) FILTER (WHERE r.rating = 4) AS four_star_count,
+    COUNT(r.id) FILTER (WHERE r.rating = 3) AS three_star_count,
+    COUNT(r.id) FILTER (WHERE r.rating = 2) AS two_star_count,
+    COUNT(r.id) FILTER (WHERE r.rating = 1) AS one_star_count
+FROM course c
+JOIN course_translate ct ON c.id = ct.course_id AND ct.language = 'mk'
+LEFT JOIN course_version cv ON c.id = cv.course_id
+LEFT JOIN enrollment e ON cv.id = e.course_version_id
+LEFT JOIN review r ON e.id = r.enrollment_id
+GROUP BY c.id, ct.title_short;
+}}}
+
+==== Помошни табели (Audit) ====
+
+{{{
+CREATE TABLE review_audit (
+    id SERIAL PRIMARY KEY,
+    review_id INTEGER NOT NULL REFERENCES review(id),
+    old_rating INTEGER,
+    new_rating INTEGER,
+    old_comment TEXT,
+    new_comment TEXT,
+    changed_at TIMESTAMP DEFAULT NOW(),
+    action VARCHAR(20)
+);
+
+CREATE INDEX idx_review_audit_review ON review_audit(review_id);
+CREATE INDEX idx_review_audit_changed_at ON review_audit(changed_at);
+}}}
+
+----
+
+== Следење и ограничување на бесплатни консултации ==
+
+=== Опис на барањата за податочни ограничувања ===
+
+Системот мора да обезбеди дека:
+* Корисникот може да користи само една бесплатна консултација
+* Може да се креира meeting_reminder за бесплатна консултација само ако корисникот сè уште не ја искористил
+* После креирање на meeting_reminder за бесплатна консултација, флагот user_used_free_consultation автоматски се поставува на TRUE
+
+=== Имплементација ===
+
+==== Тригери ====
+
+{{{
+CREATE OR REPLACE FUNCTION check_free_consultation_eligibility()
+RETURNS TRIGGER AS $$
+DECLARE
+    v_used_free_consultation BOOLEAN;
+BEGIN
+    SELECT user_used_free_consultation INTO v_used_free_consultation
+    FROM "user"
+    WHERE id = NEW.user_id;
+    
+    IF v_used_free_consultation = TRUE THEN
+        RAISE EXCEPTION 'User has already used their free consultation';
+    END IF;
+    
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER trg_check_free_consultation_before_meeting
+BEFORE INSERT ON meeting_reminder
+FOR EACH ROW
+EXECUTE FUNCTION check_free_consultation_eligibility();
+}}}
+
+{{{
+CREATE OR REPLACE FUNCTION mark_free_consultation_as_used()
+RETURNS TRIGGER AS $$
+BEGIN
+    UPDATE "user"
+    SET user_used_free_consultation = TRUE
+    WHERE id = NEW.user_id
+      AND user_used_free_consultation = FALSE;
+    
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER trg_mark_free_consultation_used
+AFTER INSERT ON meeting_reminder
+FOR EACH ROW
+EXECUTE FUNCTION mark_free_consultation_as_used();
+}}}
+
+==== Функции / Stored Procedures ====
+
+{{{
+CREATE OR REPLACE FUNCTION can_user_schedule_free_consultation(p_user_id INTEGER)
+RETURNS TABLE(
+    can_schedule BOOLEAN,
+    reason TEXT
+) AS $$
+DECLARE
+    v_used_free_consultation BOOLEAN;
+    v_pending_meetings INTEGER;
+BEGIN
+    SELECT user_used_free_consultation INTO v_used_free_consultation
+    FROM "user"
+    WHERE id = p_user_id;
+    
+    IF v_used_free_consultation = TRUE THEN
+        RETURN QUERY SELECT FALSE, 'Free consultation already used';
+        RETURN;
+    END IF;
+    
+    SELECT COUNT(*) INTO v_pending_meetings
+    FROM meeting_reminder
+    WHERE user_id = p_user_id
+      AND meeting_at > NOW();
+    
+    IF v_pending_meetings > 0 THEN
+        RETURN QUERY SELECT FALSE, 'Already has pending meeting scheduled';
+        RETURN;
+    END IF;
+    
+    RETURN QUERY SELECT TRUE, 'Eligible for free consultation';
+END;
+$$ LANGUAGE plpgsql;
+}}}
+