Course DB

Akademi Eğitim Platformu Course DB Dökümanı

Core Tables (MariaDB)

-- 1. Kursların ana tablosu (reusable, tenant-independent içerik)
CREATE TABLE courses (
    course_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    creator_user_id BIGINT UNSIGNED NOT NULL,          -- Oluşturan (freelancer veya kurumsal admin)
    tenant_id BIGINT UNSIGNED DEFAULT NULL,            -- Opsiyonel: Eğer "sadece bu tenant'a özel" ise dolu, yoksa NULL (global reusable)
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(150) UNIQUE NOT NULL,                 -- unique global slug (ucca.ai/courses/slug)
    description TEXT,
    cover_image_url VARCHAR(500),
    estimated_duration_minutes INT UNSIGNED,           -- AI hesaplasın
    difficulty ENUM('beginner', 'intermediate', 'advanced') DEFAULT 'intermediate',
    language CHAR(2) DEFAULT 'tr',
    learning_objectives JSON,                          -- [{"objective": "X'i yapabilsin"}, ...] AI üretimi
    prerequisites JSON,                                -- [{"course_id": 123, "required_completion": true}, ...]
    status ENUM('draft', 'published', 'archived') DEFAULT 'draft',
    version INT UNSIGNED DEFAULT 1,                    -- Değişiklikte artır
    ai_generated TINYINT(1) DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_slug (slug),
    INDEX idx_creator (creator_user_id),
    INDEX idx_tenant (tenant_id)
);

-- 2. Kursu akademi(ler)e bağlama (many-to-many + tenant-specific ayarlar)
-- Bu tablo sayesinde aynı course birden fazla akademide farklı şekilde yayınlanır
CREATE TABLE academy_course_links (
    link_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    academy_id BIGINT UNSIGNED NOT NULL,               -- FK academies tablosuna (senin tenant tablon)
    course_id BIGINT UNSIGNED NOT NULL,
    
    -- Tenant-specific override'lar
    display_title VARCHAR(255) DEFAULT NULL,           -- Akademide farklı isim göster
    display_description TEXT DEFAULT NULL,
    display_order INT UNSIGNED DEFAULT 0,              -- Akademi içindeki sıralama
    access_level ENUM('open', 'invite_only', 'group_restricted', 'paid') DEFAULT 'invite_only',
    start_date DATETIME DEFAULT NULL,
    end_date DATETIME DEFAULT NULL,
    group_ids JSON DEFAULT NULL,                       -- Departman/rol bazlı kısıtlama [{"group_id":45},...]
    completion_criteria JSON DEFAULT NULL,             -- Akademiye özel: {"min_quiz_score":80, "all_modules":true}
    custom_price DECIMAL(10,2) DEFAULT NULL,           -- Bu akademide ücretliyse
    
    status ENUM('active', 'hidden', 'scheduled') DEFAULT 'active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE KEY uk_academy_course (academy_id, course_id),
    FOREIGN KEY (academy_id) REFERENCES academies(academy_id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE RESTRICT
);

-- 3. Modüller (course içinde reusable bloklar, ama genelde course'a bağlı)
CREATE TABLE course_modules (
    module_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    course_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(150) NOT NULL,
    module_type ENUM('detailed_content', 'quiz', 'flashcards', 'audio_script', 'case_study', 'discussion_topics', 'faq', 'roleplay', 'project', 'other') NOT NULL,
    display_order INT UNSIGNED NOT NULL,
    is_required TINYINT(1) DEFAULT 1,
    estimated_duration_minutes SMALLINT UNSIGNED,
    prerequisites JSON,                                -- [{"module_id":456, ...}]
    content JSON,                                      -- Modül tipine göre yapılandırılmış içerik (structured JSON)
                                                       -- Örn: detailed_content → {sections: [], keypoints: []}
                                                       -- quiz → {questions: [{type:"multiple_choice", ...}]}
    ai_prompt_used TEXT,                               -- Hangi prompt ile üretildi (debug/rollback için)
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE KEY uk_course_module (course_id, slug),
    FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE CASCADE,
    INDEX idx_course_order (course_id, display_order)
);

-- 4. Dersler / Sections (modül içinde daha küçük parçalar, opsiyonel)
-- Eğer modüller çok granular değilse, bu tabloyu atlayabilirsin (content'i module.content JSON'unda tut)
CREATE TABLE module_sections (
    section_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    module_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(255),
    content_type ENUM('text', 'video_script', 'infographic_desc', 'quiz_embed', 'discussion_prompt'),
    content JSON,                                      -- Markdown text, script, vs.
    display_order INT UNSIGNED,
    FOREIGN KEY (module_id) REFERENCES course_modules(module_id) ON DELETE CASCADE
);

-- 5. Kullanıcı ilerlemesi (tenant-specific, academy bazlı)
CREATE TABLE user_course_progress (
    progress_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    academy_id BIGINT UNSIGNED NOT NULL,               -- Hangi akademide takip ediliyor
    course_id BIGINT UNSIGNED NOT NULL,
    completion_percentage DECIMAL(5,2) DEFAULT 0.00,
    started_at DATETIME,
    completed_at DATETIME,
    last_activity DATETIME,
    -- Detaylı: modül bazlı progress ayrı tabloda (user_module_progress) olabilir
    FOREIGN KEY (academy_id) REFERENCES academies(academy_id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE RESTRICT,
    UNIQUE KEY uk_user_academy_course (user_id, academy_id, course_id)
);