DatabaseCreation-AdvDb: shifter_ddl.sql

File shifter_ddl.sql, 50.1 KB (added by 231175, 3 days ago)
Line 
1
2-- Drop existing tables (in reverse dependency order)
3DROP TABLE IF EXISTS quiz_answer CASCADE;
4DROP TABLE IF EXISTS quiz_attempt CASCADE;
5DROP TABLE IF EXISTS answer_option_translate CASCADE;
6DROP TABLE IF EXISTS answer_option CASCADE;
7DROP TABLE IF EXISTS question_translate CASCADE;
8DROP TABLE IF EXISTS question_skill CASCADE;
9DROP TABLE IF EXISTS question CASCADE;
10DROP TABLE IF EXISTS quiz_translate CASCADE;
11DROP TABLE IF EXISTS quiz CASCADE;
12DROP TABLE IF EXISTS user_skill CASCADE;
13DROP TABLE IF EXISTS user_skill_snapshot CASCADE;
14DROP TABLE IF EXISTS skill_translate CASCADE;
15DROP TABLE IF EXISTS skill CASCADE;
16DROP TABLE IF EXISTS lecture_progress CASCADE;
17DROP TABLE IF EXISTS user_course_interaction CASCADE;
18DROP TABLE IF EXISTS course_similarity CASCADE;
19DROP TABLE IF EXISTS certificate CASCADE;
20DROP TABLE IF EXISTS review CASCADE;
21DROP TABLE IF EXISTS enrollment CASCADE;
22DROP TABLE IF EXISTS order_details CASCADE;
23DROP TABLE IF EXISTS "order" CASCADE;
24DROP TABLE IF EXISTS payment CASCADE;
25DROP TABLE IF EXISTS course_price CASCADE;
26DROP TABLE IF EXISTS learning_path_course CASCADE;
27DROP TABLE IF EXISTS user_learning_path CASCADE;
28DROP TABLE IF EXISTS personalized_learning_path CASCADE;
29DROP TABLE IF EXISTS manual_learning_path CASCADE;
30DROP TABLE IF EXISTS learning_path_translate CASCADE;
31DROP TABLE IF EXISTS learning_path CASCADE;
32DROP TABLE IF EXISTS personalized_bundle CASCADE;
33DROP TABLE IF EXISTS manual_bundle CASCADE;
34DROP TABLE IF EXISTS bundle_translate CASCADE;
35DROP TABLE IF EXISTS bundle CASCADE;
36DROP TABLE IF EXISTS course_lecture_translate CASCADE;
37DROP TABLE IF EXISTS course_lecture CASCADE;
38DROP TABLE IF EXISTS course_content_translate CASCADE;
39DROP TABLE IF EXISTS course_content CASCADE;
40DROP TABLE IF EXISTS course_version CASCADE;
41DROP TABLE IF EXISTS course_translate CASCADE;
42DROP TABLE IF EXISTS course_topic CASCADE;
43DROP TABLE IF EXISTS course CASCADE;
44DROP TABLE IF EXISTS topic_translate CASCADE;
45DROP TABLE IF EXISTS topic CASCADE;
46DROP TABLE IF EXISTS meeting_email_reminder CASCADE;
47DROP TABLE IF EXISTS verification_token CASCADE;
48DROP TABLE IF EXISTS expert CASCADE;
49DROP TABLE IF EXISTS "user" CASCADE;
50DROP TABLE IF EXISTS admin CASCADE;
51DROP TABLE IF EXISTS language CASCADE;
52
53-- Drop custom types
54DROP TYPE IF EXISTS enrollment_status_enum CASCADE;
55DROP TYPE IF EXISTS enrollment_type_enum CASCADE;
56DROP TYPE IF EXISTS payment_method_enum CASCADE;
57DROP TYPE IF EXISTS payment_status_enum CASCADE;
58DROP TYPE IF EXISTS course_difficulty_enum CASCADE;
59DROP TYPE IF EXISTS content_type_enum CASCADE;
60DROP TYPE IF EXISTS bundle_type_enum CASCADE;
61DROP TYPE IF EXISTS interaction_type_enum CASCADE;
62DROP TYPE IF EXISTS interaction_source_enum CASCADE;
63DROP TYPE IF EXISTS similarity_type_enum CASCADE;
64DROP TYPE IF EXISTS quiz_type_enum CASCADE;
65DROP TYPE IF EXISTS question_type_enum CASCADE;
66DROP TYPE IF EXISTS quiz_attempt_status_enum CASCADE;
67DROP TYPE IF EXISTS learning_path_status_enum CASCADE;
68DROP TYPE IF EXISTS order_status_enum CASCADE;
69DROP TYPE IF EXISTS proficiency_level_enum CASCADE;
70DROP TYPE IF EXISTS snapshot_type_enum CASCADE;
71DROP TYPE IF EXISTS personalized_type_enum CASCADE;
72DROP TYPE IF EXISTS login_provider_enum CASCADE;
73DROP TYPE IF EXISTS company_size_enum CASCADE;
74
75-- ============================================================================
76-- ENUM TYPES
77-- ============================================================================
78
79CREATE TYPE login_provider_enum AS ENUM ('EMAIL', 'GOOGLE');
80CREATE TYPE company_size_enum AS ENUM ('FREELANCE', 'SMALL', 'MEDIUM', 'LARGE', 'ENTERPRISE');
81CREATE TYPE enrollment_status_enum AS ENUM ('ACTIVE', 'COMPLETED', 'EXPIRED', 'SUSPENDED');
82CREATE TYPE enrollment_type_enum AS ENUM ('PAID', 'FREE', 'GIFTED', 'ADMIN_GRANTED');
83CREATE TYPE payment_method_enum AS ENUM ('CREDIT_CARD', 'PAYPAL', 'BANK_TRANSFER', 'STRIPE', 'ADMIN_MANUAL');
84CREATE TYPE payment_status_enum AS ENUM ('PENDING', 'COMPLETED', 'FAILED', 'REFUNDED', 'CANCELLED');
85CREATE TYPE course_difficulty_enum AS ENUM ('BEGINNER', 'INTERMEDIATE', 'ADVANCED', 'EXPERT');
86CREATE TYPE content_type_enum AS ENUM ('VIDEO', 'ARTICLE', 'QUIZ', 'INTERACTIVE', 'EXERCISE', 'RESOURCE');
87CREATE TYPE bundle_type_enum AS ENUM ('MANUAL', 'PERSONALIZED');
88CREATE TYPE interaction_type_enum AS ENUM ('VIEW', 'CLICK', 'SEARCH', 'PREVIEW', 'BOOKMARK', 'SHARE');
89CREATE TYPE interaction_source_enum AS ENUM ('CATALOG', 'SEARCH', 'RECOMMENDATION', 'EMAIL', 'SOCIAL', 'DIRECT');
90CREATE TYPE similarity_type_enum AS ENUM ('CONTENT', 'COLLABORATIVE', 'HYBRID');
91CREATE TYPE quiz_type_enum AS ENUM ('PRE_DIAGNOSTIC', 'MODULE_CHECKPOINT', 'FINAL_CERTIFICATION', 'PRACTICE');
92CREATE TYPE question_type_enum AS ENUM ('MULTIPLE_CHOICE', 'TRUE_FALSE', 'SHORT_ANSWER', 'ESSAY', 'MULTI_SELECT');
93CREATE TYPE quiz_attempt_status_enum AS ENUM ('IN_PROGRESS', 'SUBMITTED', 'GRADED', 'ABANDONED');
94CREATE TYPE learning_path_status_enum AS ENUM ('NOT_STARTED', 'IN_PROGRESS', 'COMPLETED', 'ABANDONED');
95CREATE TYPE order_status_enum AS ENUM ('PENDING', 'PROCESSING', 'COMPLETED', 'CANCELLED', 'REFUNDED');
96CREATE TYPE proficiency_level_enum AS ENUM ('BEGINNER', 'INTERMEDIATE', 'ADVANCED');
97CREATE TYPE snapshot_type_enum AS ENUM ('PRE_DIAGNOSTIC', 'POST_DIAGNOSTIC', 'CHECKPOINT', 'FINAL');
98CREATE TYPE personalized_type_enum AS ENUM ('SKILL_GAP', 'INTEREST_BASED', 'CAREER_PATH', 'TRENDING');
99
100-- ============================================================================
101-- CORE TABLES
102-- ============================================================================
103
104-- Language table (referenced by many translation tables)
105CREATE TABLE language (
106 id BIGSERIAL PRIMARY KEY,
107 language VARCHAR(10) NOT NULL UNIQUE,
108 CONSTRAINT chk_language_code CHECK (language ~ '^[a-z]{2}(-[A-Z]{2})?$')
109);
110
111CREATE INDEX idx_language_code ON language(language);
112
113-- User table
114CREATE TABLE "user" (
115 id BIGSERIAL PRIMARY KEY,
116 name VARCHAR(255) NOT NULL,
117 email VARCHAR(255) NOT NULL,
118 password_hash VARCHAR(255),
119 login_provider login_provider_enum NOT NULL DEFAULT 'EMAIL',
120 verified BOOLEAN NOT NULL DEFAULT FALSE,
121 profile_completed BOOLEAN NOT NULL DEFAULT FALSE,
122 deleted BOOLEAN NOT NULL DEFAULT FALSE,
123 used_free_consultation BOOLEAN NOT NULL DEFAULT FALSE,
124 company_size company_size_enum,
125 work_position VARCHAR(255),
126 points INTEGER NOT NULL DEFAULT 0,
127 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
128 updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
129 CONSTRAINT chk_email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$'),
130 CONSTRAINT chk_points_non_negative CHECK (points >= 0)
131);
132
133CREATE UNIQUE INDEX idx_user_email_where_user_not_deleted ON "user"(email) WHERE deleted = FALSE;
134CREATE INDEX idx_user_verified ON "user"(verified);
135
136-- Expert table
137CREATE TABLE expert (
138 id BIGSERIAL PRIMARY KEY,
139 name VARCHAR(255) NOT NULL,
140 email VARCHAR(255) NOT NULL UNIQUE,
141 password_hash VARCHAR(255),
142 login_provider login_provider_enum NOT NULL DEFAULT 'EMAIL',
143 CONSTRAINT chk_expert_email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$')
144);
145
146CREATE INDEX idx_expert_email ON expert(email);
147
148-- Admin table
149CREATE TABLE admin (
150 id BIGSERIAL PRIMARY KEY,
151 email VARCHAR(255) NOT NULL UNIQUE,
152 password_hash VARCHAR(255) NOT NULL,
153 CONSTRAINT chk_admin_email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$')
154);
155
156CREATE INDEX idx_admin_email ON admin(email);
157
158-- Verification Token
159CREATE TABLE verification_token (
160 uuid UUID PRIMARY KEY DEFAULT gen_random_uuid(),
161 user_id BIGINT NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
162 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
163 expires_at TIMESTAMP NOT NULL,
164 CONSTRAINT chk_token_expiry CHECK (expires_at > created_at)
165);
166
167CREATE INDEX idx_verification_token_user ON verification_token(user_id);
168CREATE INDEX idx_verification_token_expires ON verification_token(expires_at);
169
170-- Meeting Email Reminder
171CREATE TABLE meeting_email_reminder (
172 id BIGSERIAL PRIMARY KEY,
173 user_id BIGINT NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
174 meeting_at TIMESTAMP NOT NULL,
175 scheduled_at TIMESTAMP NOT NULL,
176 sent BOOLEAN NOT NULL DEFAULT FALSE,
177 meeting_link TEXT,
178 CONSTRAINT chk_meeting_future CHECK (meeting_at > scheduled_at)
179);
180
181CREATE INDEX idx_meeting_reminder_user ON meeting_email_reminder(user_id);
182CREATE INDEX idx_meeting_reminder_sent ON meeting_email_reminder(sent, meeting_at);
183
184-- ============================================================================
185-- COURSE STRUCTURE TABLES
186-- ============================================================================
187
188-- Topic (Category)
189CREATE TABLE topic (
190 id BIGSERIAL PRIMARY KEY,
191 slug VARCHAR(100) NOT NULL UNIQUE,
192 CONSTRAINT chk_topic_slug CHECK (slug ~ '^[a-z0-9]+(?:-[a-z0-9]+)*$')
193);
194
195CREATE INDEX idx_topic_slug ON topic(slug);
196
197-- Topic Translation
198CREATE TABLE topic_translate (
199 id BIGSERIAL PRIMARY KEY,
200 topic_id BIGINT NOT NULL REFERENCES topic(id) ON DELETE CASCADE,
201 language_id BIGINT NOT NULL REFERENCES language(id) ON DELETE RESTRICT,
202 name VARCHAR(255) NOT NULL,
203 CONSTRAINT uq_topic_translate_lang UNIQUE (topic_id, language_id)
204);
205
206CREATE INDEX idx_topic_translate_topic ON topic_translate(topic_id);
207CREATE INDEX idx_topic_translate_language ON topic_translate(language_id);
208
209-- Course
210CREATE TABLE course (
211 id BIGSERIAL PRIMARY KEY,
212 image_url TEXT,
213 color VARCHAR(7),
214 difficulty course_difficulty_enum NOT NULL DEFAULT 'BEGINNER',
215 duration_minutes INTEGER NOT NULL DEFAULT 0,
216 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
217 updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
218 CONSTRAINT chk_course_color CHECK (color ~ '^#[0-9A-Fa-f]{6}$'),
219 CONSTRAINT chk_course_duration CHECK (duration_minutes >= 0)
220);
221
222CREATE INDEX idx_course_difficulty ON course(difficulty);
223CREATE INDEX idx_course_created_at ON course(created_at);
224
225CREATE TABLE course_topic (
226 course_id BIGINT NOT NULL REFERENCES course(id) ON DELETE CASCADE,
227 topic_id BIGINT NOT NULL REFERENCES topic(id) ON DELETE CASCADE,
228 PRIMARY KEY (course_id, topic_id)
229);
230
231-- Course Translation
232CREATE TABLE course_translate (
233 id BIGSERIAL PRIMARY KEY,
234 course_id BIGINT NOT NULL REFERENCES course(id) ON DELETE CASCADE,
235 language_id BIGINT NOT NULL REFERENCES language(id) ON DELETE RESTRICT,
236 title_short VARCHAR(100) NOT NULL,
237 title VARCHAR(255) NOT NULL,
238 description_short TEXT NOT NULL,
239 description TEXT NOT NULL,
240 description_long TEXT,
241 what_will_be_learned TEXT,
242 CONSTRAINT uq_course_translate_lang UNIQUE (course_id, language_id)
243);
244
245CREATE INDEX idx_course_translate_course ON course_translate(course_id);
246CREATE INDEX idx_course_translate_language ON course_translate(language_id);
247
248-- Course Version
249CREATE TABLE course_version (
250 id BIGSERIAL PRIMARY KEY,
251 course_id BIGINT NOT NULL REFERENCES course(id) ON DELETE CASCADE,
252 version_number INTEGER NOT NULL,
253 creation_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
254 active BOOLEAN NOT NULL DEFAULT TRUE,
255 CONSTRAINT uq_course_version_number UNIQUE (course_id, version_number),
256 CONSTRAINT chk_version_positive CHECK (version_number > 0)
257);
258
259CREATE INDEX idx_course_version_course ON course_version(course_id);
260CREATE INDEX idx_course_version_active ON course_version(course_id, active);
261
262-- Course Content (Module/Section)
263CREATE TABLE course_content (
264 id BIGSERIAL PRIMARY KEY,
265 course_version_id BIGINT NOT NULL REFERENCES course_version(id) ON DELETE CASCADE,
266 position INTEGER NOT NULL,
267 CONSTRAINT uq_content_position UNIQUE (course_version_id, position),
268 CONSTRAINT chk_content_position CHECK (position > 0)
269);
270
271CREATE INDEX idx_course_content_version ON course_content(course_version_id);
272CREATE INDEX idx_course_content_position ON course_content(course_version_id, position);
273
274-- Course Content Translation
275CREATE TABLE course_content_translate (
276 id BIGSERIAL PRIMARY KEY,
277 course_content_id BIGINT NOT NULL REFERENCES course_content(id) ON DELETE CASCADE,
278 language_id BIGINT NOT NULL REFERENCES language(id) ON DELETE RESTRICT,
279 title VARCHAR(255) NOT NULL,
280 CONSTRAINT uq_content_translate_lang UNIQUE (course_content_id, language_id)
281);
282
283CREATE INDEX idx_content_translate_content ON course_content_translate(course_content_id);
284CREATE INDEX idx_content_translate_language ON course_content_translate(language_id);
285
286-- Course Lecture
287CREATE TABLE course_lecture (
288 id BIGSERIAL PRIMARY KEY,
289 course_content_id BIGINT NOT NULL REFERENCES course_content(id) ON DELETE CASCADE,
290 position INTEGER NOT NULL,
291 duration_minutes INTEGER NOT NULL DEFAULT 0,
292 content_type content_type_enum NOT NULL DEFAULT 'VIDEO',
293 CONSTRAINT uq_lecture_position UNIQUE (course_content_id, position),
294 CONSTRAINT chk_lecture_position CHECK (position > 0),
295 CONSTRAINT chk_lecture_duration CHECK (duration_minutes >= 0)
296);
297
298CREATE INDEX idx_course_lecture_content ON course_lecture(course_content_id);
299CREATE INDEX idx_course_lecture_position ON course_lecture(course_content_id, position);
300CREATE INDEX idx_course_lecture_type ON course_lecture(content_type);
301
302-- Course Lecture Translation
303CREATE TABLE course_lecture_translate (
304 id BIGSERIAL PRIMARY KEY,
305 course_lecture_id BIGINT NOT NULL REFERENCES course_lecture(id) ON DELETE CASCADE,
306 language_id BIGINT NOT NULL REFERENCES language(id) ON DELETE RESTRICT,
307 title VARCHAR(255) NOT NULL,
308 description TEXT,
309 content_file_name VARCHAR(255),
310 content_text TEXT,
311 CONSTRAINT uq_lecture_translate_lang UNIQUE (course_lecture_id, language_id)
312);
313
314CREATE INDEX idx_lecture_translate_lecture ON course_lecture_translate(course_lecture_id);
315CREATE INDEX idx_lecture_translate_language ON course_lecture_translate(language_id);
316
317-- ============================================================================
318-- PRICING & BUNDLE TABLES
319-- ============================================================================
320
321-- Course Price
322CREATE TABLE course_price (
323 id BIGSERIAL PRIMARY KEY,
324 course_id BIGINT NOT NULL REFERENCES course(id) ON DELETE CASCADE,
325 amount DECIMAL(10, 2) NOT NULL,
326 discount DECIMAL(5, 2) NOT NULL DEFAULT 0.00,
327 has_discount BOOLEAN NOT NULL DEFAULT FALSE,
328 active BOOLEAN NOT NULL DEFAULT TRUE,
329 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
330 valid_until TIMESTAMP,
331 CONSTRAINT chk_price_amount CHECK (amount >= 0),
332 CONSTRAINT chk_price_discount CHECK (discount >= 0 AND discount <= 100),
333 CONSTRAINT chk_valid_until CHECK (valid_until IS NULL OR valid_until > created_at),
334 CONSTRAINT chk_price_discount_consistency CHECK (
335 (has_discount = TRUE AND discount > 0) OR
336 (has_discount = FALSE AND discount = 0)
337 )
338);
339
340CREATE INDEX idx_course_price_course_active ON course_price(course_id, active);
341
342-- Bundle (supertype - Class Table Inheritance)
343CREATE TABLE bundle (
344 id BIGSERIAL PRIMARY KEY,
345 type bundle_type_enum NOT NULL,
346 base_price DECIMAL(10, 2) NOT NULL,
347 discount_pct DECIMAL(5, 2) NOT NULL DEFAULT 0.00,
348 final_price DECIMAL(10, 2) NOT NULL,
349 active BOOLEAN NOT NULL DEFAULT TRUE,
350 image_url TEXT,
351 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
352 deactivated_at TIMESTAMP,
353 CONSTRAINT chk_bundle_base_price CHECK (base_price >= 0),
354 CONSTRAINT chk_bundle_discount CHECK (discount_pct >= 0 AND discount_pct <= 100),
355 CONSTRAINT chk_bundle_final_price CHECK (final_price >= 0),
356 CONSTRAINT chk_bundle_deactivated CHECK (
357 (active = FALSE AND deactivated_at IS NOT NULL) OR
358 (active = TRUE AND deactivated_at IS NULL)
359 )
360);
361
362CREATE INDEX idx_bundle_type ON bundle(type);
363CREATE INDEX idx_bundle_active ON bundle(active);
364CREATE INDEX idx_bundle_created_at ON bundle(created_at);
365
366-- Manual Bundle (subtype)
367CREATE TABLE manual_bundle (
368 id BIGINT PRIMARY KEY REFERENCES bundle(id) ON DELETE CASCADE
369-- CONSTRAINT chk_manual_bundle_type CHECK ((SELECT type FROM bundle WHERE id = manual_bundle.id) = 'MANUAL')
370);
371
372-- Personalized Bundle (subtype)
373CREATE TABLE personalized_bundle (
374 id BIGINT PRIMARY KEY REFERENCES bundle(id) ON DELETE CASCADE,
375 user_id BIGINT NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
376 type personalized_type_enum NOT NULL,
377 generated_reason TEXT NOT NULL,
378 reason_display_text VARCHAR(500),
379 added_discount DECIMAL(5, 2) NOT NULL DEFAULT 0.00,
380 active BOOLEAN NOT NULL DEFAULT TRUE,
381 expires_at TIMESTAMP,
382-- CONSTRAINT chk_personalized_bundle_type CHECK ((SELECT type FROM bundle WHERE id = personalized_bundle.id) = 'PERSONALIZED'),
383 CONSTRAINT chk_personalized_discount CHECK (added_discount >= 0 AND added_discount <= 100)
384);
385
386CREATE INDEX idx_personalized_bundle_user ON personalized_bundle(user_id);
387CREATE INDEX idx_personalized_bundle_active ON personalized_bundle(active, expires_at);
388
389-- Bundle Translation
390CREATE TABLE bundle_translate (
391 id BIGSERIAL PRIMARY KEY,
392 bundle_id BIGINT NOT NULL REFERENCES bundle(id) ON DELETE CASCADE,
393 language_id BIGINT NOT NULL REFERENCES language(id) ON DELETE RESTRICT,
394 title VARCHAR(255) NOT NULL,
395 description TEXT,
396 CONSTRAINT uq_bundle_translate_lang UNIQUE (bundle_id, language_id)
397);
398
399CREATE INDEX idx_bundle_translate_bundle ON bundle_translate(bundle_id);
400CREATE INDEX idx_bundle_translate_language ON bundle_translate(language_id);
401
402-- ============================================================================
403-- LEARNING PATH TABLES
404-- ============================================================================
405
406-- Learning Path (supertype)
407CREATE TABLE learning_path (
408 id BIGSERIAL PRIMARY KEY,
409 discount DECIMAL(5, 2) NOT NULL DEFAULT 0.00,
410 estimated_duration_wks INTEGER NOT NULL DEFAULT 0,
411 difficulty course_difficulty_enum NOT NULL DEFAULT 'BEGINNER',
412 active BOOLEAN NOT NULL DEFAULT TRUE,
413 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
414 deactivated_at TIMESTAMP,
415 CONSTRAINT chk_lp_discount CHECK (discount >= 0 AND discount <= 100),
416 CONSTRAINT chk_lp_duration CHECK (estimated_duration_wks >= 0),
417 CONSTRAINT chk_lp_deactivated CHECK (
418 (active = FALSE AND deactivated_at IS NOT NULL) OR
419 (active = TRUE AND deactivated_at IS NULL)
420 )
421);
422
423CREATE INDEX idx_learning_path_active ON learning_path(active);
424CREATE INDEX idx_learning_path_difficulty ON learning_path(difficulty);
425
426-- Manual Learning Path (subtype)
427CREATE TABLE manual_learning_path (
428 id BIGINT PRIMARY KEY REFERENCES learning_path(id) ON DELETE CASCADE
429);
430
431-- Personalized Learning Path (subtype)
432CREATE TABLE personalized_learning_path (
433 id BIGINT PRIMARY KEY REFERENCES learning_path(id) ON DELETE CASCADE,
434 user_id BIGINT NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
435 type personalized_type_enum NOT NULL,
436 generated_reason TEXT NOT NULL,
437 reason_display_text VARCHAR(500),
438 added_discount DECIMAL(5, 2) NOT NULL DEFAULT 0.00,
439 active BOOLEAN NOT NULL DEFAULT TRUE,
440 expires_at TIMESTAMP,
441 CONSTRAINT chk_plp_discount CHECK (added_discount >= 0 AND added_discount <= 100)
442);
443
444CREATE INDEX idx_personalized_lp_user ON personalized_learning_path(user_id);
445CREATE INDEX idx_personalized_lp_active ON personalized_learning_path(active, expires_at);
446
447-- Learning Path Translation
448CREATE TABLE learning_path_translate (
449 id BIGSERIAL PRIMARY KEY,
450 learning_path_id BIGINT NOT NULL REFERENCES learning_path(id) ON DELETE CASCADE,
451 language_id BIGINT NOT NULL REFERENCES language(id) ON DELETE RESTRICT,
452 name VARCHAR(255) NOT NULL,
453 description TEXT,
454 learning_outcomes TEXT,
455 CONSTRAINT uq_lp_translate_lang UNIQUE (learning_path_id, language_id)
456);
457
458CREATE INDEX idx_lp_translate_lp ON learning_path_translate(learning_path_id);
459CREATE INDEX idx_lp_translate_language ON learning_path_translate(language_id);
460
461-- Learning Path Course (junction table)
462CREATE TABLE learning_path_course (
463 id BIGSERIAL PRIMARY KEY,
464 learning_path_id BIGINT NOT NULL REFERENCES learning_path(id) ON DELETE CASCADE,
465 course_id BIGINT NOT NULL REFERENCES course(id) ON DELETE CASCADE,
466 sequence_order INTEGER NOT NULL,
467 CONSTRAINT uq_lp_course UNIQUE (learning_path_id, course_id),
468 CONSTRAINT uq_lp_sequence UNIQUE (learning_path_id, sequence_order),
469 CONSTRAINT chk_lp_sequence CHECK (sequence_order > 0)
470);
471
472CREATE INDEX idx_lp_course_lp ON learning_path_course(learning_path_id);
473CREATE INDEX idx_lp_course_course ON learning_path_course(course_id);
474CREATE INDEX idx_lp_course_sequence ON learning_path_course(learning_path_id, sequence_order);
475
476-- ============================================================================
477-- ENROLLMENT & PAYMENT TABLES
478-- ============================================================================
479
480-- Payment
481CREATE TABLE payment (
482 id BIGSERIAL PRIMARY KEY,
483 user_id BIGINT NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
484 amount DECIMAL(10, 2) NOT NULL,
485 date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
486 method payment_method_enum NOT NULL,
487 status payment_status_enum NOT NULL DEFAULT 'PENDING',
488 CONSTRAINT chk_payment_amount CHECK (amount >= 0)
489);
490
491CREATE INDEX idx_payment_user ON payment(user_id);
492CREATE INDEX idx_payment_status ON payment(status);
493CREATE INDEX idx_payment_date ON payment(date);
494
495-- Enrollment
496CREATE TABLE enrollment (
497 id BIGSERIAL PRIMARY KEY,
498 user_id BIGINT NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
499 course_id BIGINT NOT NULL REFERENCES course(id) ON DELETE CASCADE,
500 payment_id BIGINT REFERENCES payment(id) ON DELETE SET NULL,
501 enrollment_status enrollment_status_enum NOT NULL DEFAULT 'ACTIVE',
502 enrollment_type enrollment_type_enum NOT NULL DEFAULT 'PAID',
503 enrollment_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
504 purchase_date TIMESTAMP,
505 activation_date TIMESTAMP,
506 completion_date TIMESTAMP,
507 CONSTRAINT uq_enrollment_user_course UNIQUE (user_id, course_id),
508 CONSTRAINT chk_enrollment_dates CHECK (
509 purchase_date IS NULL OR purchase_date <= enrollment_date
510 ),
511 CONSTRAINT chk_enrollment_completion CHECK (
512 completion_date IS NULL OR completion_date >= enrollment_date
513 )
514);
515
516CREATE INDEX idx_enrollment_user ON enrollment(user_id);
517CREATE INDEX idx_enrollment_course ON enrollment(course_id);
518CREATE INDEX idx_enrollment_status ON enrollment(enrollment_status);
519CREATE INDEX idx_enrollment_payment ON enrollment(payment_id);
520
521-- User Learning Path
522CREATE TABLE user_learning_path (
523 id BIGSERIAL PRIMARY KEY,
524 user_id BIGINT NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
525 learning_path_id BIGINT NOT NULL REFERENCES learning_path(id) ON DELETE CASCADE,
526 enrolled_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
527 completed_at TIMESTAMP,
528 status learning_path_status_enum NOT NULL DEFAULT 'NOT_STARTED',
529 progress DECIMAL(5, 2) NOT NULL DEFAULT 0.00,
530 CONSTRAINT uq_user_lp UNIQUE (user_id, learning_path_id),
531 CONSTRAINT chk_ulp_progress CHECK (progress >= 0 AND progress <= 100),
532 CONSTRAINT chk_ulp_completion CHECK (
533 (status = 'COMPLETED' AND completed_at IS NOT NULL AND progress = 100) OR
534 (status != 'COMPLETED')
535 )
536);
537
538CREATE INDEX idx_user_lp_user ON user_learning_path(user_id);
539CREATE INDEX idx_user_lp_path ON user_learning_path(learning_path_id);
540CREATE INDEX idx_user_lp_status ON user_learning_path(status);
541
542-- Order
543CREATE TABLE "order" (
544 id BIGSERIAL PRIMARY KEY,
545 user_id BIGINT NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
546 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
547 status order_status_enum NOT NULL DEFAULT 'PENDING'
548);
549
550CREATE INDEX idx_order_user ON "order"(user_id);
551CREATE INDEX idx_order_status ON "order"(status);
552CREATE INDEX idx_order_created_at ON "order"(created_at);
553
554-- Order Details
555CREATE TABLE order_details (
556 id BIGSERIAL PRIMARY KEY,
557 order_id BIGINT NOT NULL REFERENCES "order"(id) ON DELETE CASCADE,
558 course_id BIGINT REFERENCES course(id) ON DELETE SET NULL,
559 bundle_id BIGINT REFERENCES bundle(id) ON DELETE SET NULL,
560 price DECIMAL(10, 2) NOT NULL,
561 discount DECIMAL(5, 2) NOT NULL DEFAULT 0.00,
562 image_url TEXT,
563 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
564 CONSTRAINT chk_order_item_type CHECK (
565 (course_id IS NOT NULL AND bundle_id IS NULL) OR
566 (course_id IS NULL AND bundle_id IS NOT NULL)
567 ),
568 CONSTRAINT chk_order_price CHECK (price >= 0),
569 CONSTRAINT chk_order_discount CHECK (discount >= 0 AND discount <= 100)
570);
571
572CREATE INDEX idx_order_details_order ON order_details(order_id);
573CREATE INDEX idx_order_details_course ON order_details(course_id);
574CREATE INDEX idx_order_details_bundle ON order_details(bundle_id);
575
576-- ============================================================================
577-- USER INTERACTION & ANALYTICS TABLES
578-- ============================================================================
579
580-- User Course Interaction
581CREATE TABLE user_course_interaction (
582 id BIGSERIAL PRIMARY KEY,
583 user_id BIGINT NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
584 course_id BIGINT NOT NULL REFERENCES course(id) ON DELETE CASCADE,
585 type interaction_type_enum NOT NULL,
586 date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
587 source interaction_source_enum NOT NULL,
588 duration_seconds INTEGER,
589 CONSTRAINT chk_interaction_duration CHECK (duration_seconds IS NULL OR duration_seconds >= 0)
590);
591
592CREATE INDEX idx_user_interaction_user ON user_course_interaction(user_id);
593CREATE INDEX idx_user_interaction_course ON user_course_interaction(course_id);
594CREATE INDEX idx_user_interaction_type ON user_course_interaction(type);
595CREATE INDEX idx_user_interaction_date ON user_course_interaction(date);
596
597-- Course Similarity
598CREATE TABLE course_similarity (
599 id BIGSERIAL PRIMARY KEY,
600 course_id_1 BIGINT NOT NULL REFERENCES course(id) ON DELETE CASCADE,
601 course_id_2 BIGINT NOT NULL REFERENCES course(id) ON DELETE CASCADE,
602 score DECIMAL(5, 4) NOT NULL,
603 type similarity_type_enum NOT NULL,
604 calculated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
605 CONSTRAINT uq_course_similarity UNIQUE (course_id_1, course_id_2, type),
606 CONSTRAINT chk_similarity_different_courses CHECK (course_id_1 < course_id_2),
607 CONSTRAINT chk_similarity_score CHECK (score >= 0 AND score <= 1)
608);
609
610CREATE INDEX idx_similarity_course1 ON course_similarity(course_id_1);
611CREATE INDEX idx_similarity_course2 ON course_similarity(course_id_2);
612CREATE INDEX idx_similarity_score ON course_similarity(score DESC);
613
614-- Lecture Progress
615CREATE TABLE lecture_progress (
616 id BIGSERIAL PRIMARY KEY,
617 enrollment_id BIGINT NOT NULL REFERENCES enrollment(id) ON DELETE CASCADE,
618 course_lecture_id BIGINT NOT NULL REFERENCES course_lecture(id) ON DELETE CASCADE,
619 completed BOOLEAN NOT NULL DEFAULT FALSE,
620 started_ad TIMESTAMP,
621 completed_ad TIMESTAMP,
622 last_accessed_at TIMESTAMP,
623 CONSTRAINT uq_lecture_progress UNIQUE (enrollment_id, course_lecture_id),
624 CONSTRAINT chk_lecture_progress_completion CHECK (
625 (completed = FALSE) OR
626 (completed = TRUE AND completed_ad IS NOT NULL)
627 )
628);
629
630CREATE INDEX idx_lecture_progress_enrollment ON lecture_progress(enrollment_id);
631CREATE INDEX idx_lecture_progress_lecture ON lecture_progress(course_lecture_id);
632CREATE INDEX idx_lecture_progress_completed ON lecture_progress(completed);
633
634-- Review
635CREATE TABLE review (
636 id BIGSERIAL PRIMARY KEY,
637 enrollment_id BIGINT NOT NULL REFERENCES enrollment(id) ON DELETE CASCADE,
638 rating INTEGER NOT NULL,
639 comment TEXT,
640 date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
641 CONSTRAINT uq_review_enrollment UNIQUE (enrollment_id),
642 CONSTRAINT chk_review_rating CHECK (rating >= 1 AND rating <= 5)
643);
644
645CREATE INDEX idx_review_enrollment ON review(enrollment_id);
646CREATE INDEX idx_review_rating ON review(rating);
647CREATE INDEX idx_review_date ON review(date);
648
649-- Certificate
650CREATE TABLE certificate (
651 id BIGSERIAL PRIMARY KEY,
652 enrollment_id BIGINT NOT NULL REFERENCES enrollment(id) ON DELETE CASCADE,
653 course_id BIGINT NOT NULL REFERENCES course(id) ON DELETE CASCADE,
654 issue_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
655 pdf_url TEXT NOT NULL,
656 certificate_number VARCHAR(50) NOT NULL UNIQUE,
657 duration_seconds INTEGER NOT NULL,
658 CONSTRAINT uq_certificate_enrollment UNIQUE (enrollment_id),
659 CONSTRAINT chk_certificate_duration CHECK (duration_seconds > 0)
660);
661
662CREATE INDEX idx_certificate_enrollment ON certificate(enrollment_id);
663CREATE INDEX idx_certificate_course ON certificate(course_id);
664CREATE INDEX idx_certificate_number ON certificate(certificate_number);
665
666-- ============================================================================
667-- QUIZ & ASSESSMENT TABLES
668-- ============================================================================
669
670-- Skill
671CREATE TABLE skill (
672 id BIGSERIAL PRIMARY KEY,
673 slug VARCHAR(100) NOT NULL UNIQUE,
674 show_in_radar BOOLEAN NOT NULL DEFAULT TRUE,
675 CONSTRAINT chk_skill_slug CHECK (slug ~ '^[a-z0-9]+(?:-[a-z0-9]+)*$')
676);
677
678CREATE INDEX idx_skill_slug ON skill(slug);
679CREATE INDEX idx_skill_radar ON skill(show_in_radar);
680
681-- Skill Translation
682CREATE TABLE skill_translate (
683 id BIGSERIAL PRIMARY KEY,
684 skill_id BIGINT NOT NULL REFERENCES skill(id) ON DELETE CASCADE,
685 language_id BIGINT NOT NULL REFERENCES language(id) ON DELETE RESTRICT,
686 name VARCHAR(255) NOT NULL,
687 description TEXT,
688 CONSTRAINT uq_skill_translate_lang UNIQUE (skill_id, language_id)
689);
690
691CREATE INDEX idx_skill_translate_skill ON skill_translate(skill_id);
692CREATE INDEX idx_skill_translate_language ON skill_translate(language_id);
693
694-- Quiz
695CREATE TABLE quiz (
696 id BIGSERIAL PRIMARY KEY,
697 course_id BIGINT REFERENCES course(id) ON DELETE CASCADE,
698 course_content_id BIGINT REFERENCES course_content(id) ON DELETE CASCADE,
699 type quiz_type_enum NOT NULL,
700 passing_score INTEGER NOT NULL,
701 randomize BOOLEAN NOT NULL DEFAULT FALSE,
702 is_active BOOLEAN NOT NULL DEFAULT TRUE,
703 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
704 CONSTRAINT chk_quiz_parent CHECK (
705 (course_id IS NOT NULL AND course_content_id IS NULL) OR
706 (course_id IS NULL AND course_content_id IS NOT NULL)
707 ),
708 CONSTRAINT chk_quiz_passing_score CHECK (passing_score >= 0 AND passing_score <= 100)
709);
710
711CREATE INDEX idx_quiz_course ON quiz(course_id);
712CREATE INDEX idx_quiz_content ON quiz(course_content_id);
713CREATE INDEX idx_quiz_type ON quiz(type);
714CREATE INDEX idx_quiz_active ON quiz(is_active);
715
716-- Quiz Translation
717CREATE TABLE quiz_translate (
718 id BIGSERIAL PRIMARY KEY,
719 quiz_id BIGINT NOT NULL REFERENCES quiz(id) ON DELETE CASCADE,
720 language_id BIGINT NOT NULL REFERENCES language(id) ON DELETE RESTRICT,
721 title VARCHAR(255) NOT NULL,
722 description TEXT,
723 CONSTRAINT uq_quiz_translate_lang UNIQUE (quiz_id, language_id)
724);
725
726CREATE INDEX idx_quiz_translate_quiz ON quiz_translate(quiz_id);
727CREATE INDEX idx_quiz_translate_language ON quiz_translate(language_id);
728
729-- Question
730CREATE TABLE question (
731 id BIGSERIAL PRIMARY KEY,
732 quiz_id BIGINT NOT NULL REFERENCES quiz(id) ON DELETE CASCADE,
733 type question_type_enum NOT NULL,
734 points INTEGER NOT NULL DEFAULT 1,
735 position INTEGER NOT NULL,
736 CONSTRAINT uq_question_position UNIQUE (quiz_id, position),
737 CONSTRAINT chk_question_points CHECK (points > 0),
738 CONSTRAINT chk_question_position CHECK (position > 0)
739);
740
741CREATE INDEX idx_question_quiz ON question(quiz_id);
742CREATE INDEX idx_question_type ON question(type);
743CREATE INDEX idx_question_position ON question(quiz_id, position);
744
745-- Question Translation
746CREATE TABLE question_translate (
747 id BIGSERIAL PRIMARY KEY,
748 question_id BIGINT NOT NULL REFERENCES question(id) ON DELETE CASCADE,
749 language_id BIGINT NOT NULL REFERENCES language(id) ON DELETE RESTRICT,
750 question_text TEXT NOT NULL,
751 scenario_text TEXT,
752 CONSTRAINT uq_question_translate_lang UNIQUE (question_id, language_id)
753);
754
755CREATE INDEX idx_question_translate_question ON question_translate(question_id);
756CREATE INDEX idx_question_translate_language ON question_translate(language_id);
757
758-- Answer Option
759CREATE TABLE answer_option (
760 id BIGSERIAL PRIMARY KEY,
761 question_id BIGINT NOT NULL REFERENCES question(id) ON DELETE CASCADE,
762 is_correct BOOLEAN NOT NULL DEFAULT FALSE
763);
764
765CREATE INDEX idx_answer_option_question ON answer_option(question_id);
766
767-- Answer Option Translation
768CREATE TABLE answer_option_translate (
769 id BIGSERIAL PRIMARY KEY,
770 answer_option_id BIGINT NOT NULL REFERENCES answer_option(id) ON DELETE CASCADE,
771 language_id BIGINT NOT NULL REFERENCES language(id) ON DELETE RESTRICT,
772 answer_text TEXT NOT NULL,
773 explanation TEXT,
774 CONSTRAINT uq_answer_translate_lang UNIQUE (answer_option_id, language_id)
775);
776
777CREATE INDEX idx_answer_translate_option ON answer_option_translate(answer_option_id);
778CREATE INDEX idx_answer_translate_language ON answer_option_translate(language_id);
779
780-- Question Skill (many-to-many)
781CREATE TABLE question_skill (
782 id BIGSERIAL PRIMARY KEY,
783 question_id BIGINT NOT NULL REFERENCES question(id) ON DELETE CASCADE,
784 skill_id BIGINT NOT NULL REFERENCES skill(id) ON DELETE CASCADE,
785 weight DECIMAL(5, 2) NOT NULL DEFAULT 1.00,
786 CONSTRAINT uq_question_skill UNIQUE (question_id, skill_id),
787 CONSTRAINT chk_question_skill_weight CHECK (weight > 0 AND weight <= 100)
788);
789
790CREATE INDEX idx_question_skill_question ON question_skill(question_id);
791CREATE INDEX idx_question_skill_skill ON question_skill(skill_id);
792
793-- Quiz Attempt
794CREATE TABLE quiz_attempt (
795 id BIGSERIAL PRIMARY KEY,
796 enrollment_id BIGINT NOT NULL REFERENCES enrollment(id) ON DELETE CASCADE,
797 quiz_id BIGINT NOT NULL REFERENCES quiz(id) ON DELETE CASCADE,
798 attempt_number INTEGER NOT NULL DEFAULT 1,
799 started_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
800 submited_at TIMESTAMP,
801 status quiz_attempt_status_enum NOT NULL DEFAULT 'IN_PROGRESS',
802 score INTEGER,
803 total_points INTEGER NOT NULL,
804 earned_points INTEGER,
805 passed BOOLEAN,
806 CONSTRAINT uq_quiz_attempt_number UNIQUE (enrollment_id, quiz_id, attempt_number),
807 CONSTRAINT chk_quiz_attempt_number CHECK (attempt_number > 0),
808 CONSTRAINT chk_quiz_attempt_submit CHECK (
809 (status = 'IN_PROGRESS' AND submited_at IS NULL) OR
810 (status != 'IN_PROGRESS' AND submited_at IS NOT NULL)
811 ),
812 CONSTRAINT chk_quiz_attempt_score CHECK (score IS NULL OR (score >= 0 AND score <= 100)),
813 CONSTRAINT chk_quiz_attempt_points CHECK (
814 total_points >= 0 AND
815 (earned_points IS NULL OR earned_points >= 0) AND
816 (earned_points IS NULL OR earned_points <= total_points)
817 )
818);
819
820CREATE INDEX idx_quiz_attempt_enrollment ON quiz_attempt(enrollment_id);
821CREATE INDEX idx_quiz_attempt_quiz ON quiz_attempt(quiz_id);
822CREATE INDEX idx_quiz_attempt_status ON quiz_attempt(status);
823
824-- Quiz Answer
825CREATE TABLE quiz_answer (
826 id BIGSERIAL PRIMARY KEY,
827 quiz_attempt_id BIGINT NOT NULL REFERENCES quiz_attempt(id) ON DELETE CASCADE,
828 question_id BIGINT NOT NULL REFERENCES question(id) ON DELETE CASCADE,
829 answer_option_id BIGINT REFERENCES answer_option(id) ON DELETE CASCADE,
830 confidence_value DECIMAL(5, 2),
831 is_correct BOOLEAN NOT NULL DEFAULT FALSE,
832 CONSTRAINT uq_quiz_answer UNIQUE (quiz_attempt_id, question_id),
833 CONSTRAINT chk_quiz_answer_confidence CHECK (confidence_value IS NULL OR (confidence_value >= 0 AND confidence_value <= 100))
834);
835
836CREATE INDEX idx_quiz_answer_attempt ON quiz_answer(quiz_attempt_id);
837CREATE INDEX idx_quiz_answer_question ON quiz_answer(question_id);
838CREATE INDEX idx_quiz_answer_option ON quiz_answer(answer_option_id);
839
840-- ============================================================================
841-- SKILL TRACKING TABLES
842-- ============================================================================
843
844-- User Skill
845CREATE TABLE user_skill (
846 id BIGSERIAL PRIMARY KEY,
847 user_id BIGINT NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
848 skill_id BIGINT NOT NULL REFERENCES skill(id) ON DELETE CASCADE,
849 verified BOOLEAN NOT NULL DEFAULT FALSE,
850 proficiency proficiency_level_enum NOT NULL DEFAULT 'BEGINNER',
851 score_percent DECIMAL(5, 2) NOT NULL DEFAULT 0.00,
852 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
853 CONSTRAINT uq_user_skill UNIQUE (user_id, skill_id),
854 CONSTRAINT chk_user_skill_score CHECK (score_percent >= 0 AND score_percent <= 100)
855);
856
857CREATE INDEX idx_user_skill_user ON user_skill(user_id);
858CREATE INDEX idx_user_skill_skill ON user_skill(skill_id);
859CREATE INDEX idx_user_skill_proficiency ON user_skill(proficiency);
860
861-- User Skill Snapshot
862CREATE TABLE user_skill_snapshot (
863 id BIGSERIAL PRIMARY KEY,
864 user_id BIGINT NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
865 skill_id BIGINT NOT NULL REFERENCES skill(id) ON DELETE CASCADE,
866 quiz_attempt_id BIGINT NOT NULL REFERENCES quiz_attempt(id) ON DELETE CASCADE,
867 weight_percent DECIMAL(5, 2) NOT NULL,
868 confidence_avg DECIMAL(5, 2),
869 snapshot_type snapshot_type_enum NOT NULL,
870 CONSTRAINT chk_snapshot_weight CHECK (weight_percent >= 0 AND weight_percent <= 100),
871 CONSTRAINT chk_snapshot_confidence CHECK (confidence_avg IS NULL OR (confidence_avg >= 0 AND confidence_avg <= 100))
872);
873
874CREATE INDEX idx_skill_snapshot_user ON user_skill_snapshot(user_id);
875CREATE INDEX idx_skill_snapshot_skill ON user_skill_snapshot(skill_id);
876CREATE INDEX idx_skill_snapshot_attempt ON user_skill_snapshot(quiz_attempt_id);
877CREATE INDEX idx_skill_snapshot_type ON user_skill_snapshot(snapshot_type);
878
879-- ============================================================================
880-- TRIGGERS FOR AUTOMATED TIMESTAMP UPDATES
881-- ============================================================================
882
883CREATE OR REPLACE FUNCTION update_updated_at_column()
884 RETURNS TRIGGER AS $$
885BEGIN
886 NEW.updated_at = CURRENT_TIMESTAMP;
887 RETURN NEW;
888END;
889$$ LANGUAGE plpgsql;
890
891CREATE TRIGGER update_user_updated_at BEFORE UPDATE ON "user"
892 FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
893
894CREATE TRIGGER update_course_updated_at BEFORE UPDATE ON course
895 FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
896
897-- ============================================================================
898-- INITIAL DATA SEEDS
899-- ============================================================================
900
901-- Insert default languages
902INSERT INTO language (language) VALUES
903 ('en'),
904 ('mk'),
905 ('sq'),
906 ('sr')
907ON CONFLICT (language) DO NOTHING;
908
909-- ============================================================================
910-- COMMENTS FOR DOCUMENTATION
911-- ============================================================================
912
913COMMENT ON TABLE "user" IS 'Main user accounts for students and learners';
914COMMENT ON TABLE expert IS 'Expert/mentor accounts for consultations and meetings';
915COMMENT ON TABLE admin IS 'Administrative accounts with system access';
916COMMENT ON TABLE course IS 'Core course catalog';
917COMMENT ON TABLE enrollment IS 'User course enrollments tracking progress and completion';
918COMMENT ON TABLE bundle IS 'Course bundles - supertype for manual and personalized bundles';
919COMMENT ON TABLE manual_bundle IS 'Manually created course bundles (subtype of bundle)';
920COMMENT ON TABLE personalized_bundle IS 'AI-generated personalized bundles for specific users (subtype of bundle)';
921COMMENT ON TABLE learning_path IS 'Curated learning paths - supertype for manual and personalized paths';
922COMMENT ON TABLE quiz IS 'Assessments for skill tracking and certification';
923COMMENT ON TABLE user_skill IS 'User skill proficiency tracking with tiered levels';
924COMMENT ON TABLE user_skill_snapshot IS 'Point-in-time skill assessment snapshots from quiz attempts';
925
926-- ============================================================================
927-- END OF SCHEMA
928-- ============================================================================