course.sql

-- ========================
-- Database: Online Learning Website
-- ========================
CREATE DATABASE IF NOT EXISTS course;
USE course;

-- DROP TABLES IF YOU WANT FRESH IMPORT (uncomment jika perlu)
DROP TABLE IF EXISTS comments;
DROP TABLE IF EXISTS posts;
DROP TABLE IF EXISTS forums;
DROP TABLE IF EXISTS certifications;
DROP TABLE IF EXISTS online_courses;
DROP TABLE IF EXISTS modules;
DROP TABLE IF EXISTS programs;
DROP TABLE IF EXISTS about_us;
DROP TABLE IF EXISTS contacts;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS community_links;
DROP TABLE IF EXISTS documentaries;
DROP TABLE IF EXISTS roadmaps;

-- Users (guest user)
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(150)
);

-- Contact Us
CREATE TABLE IF NOT EXISTS contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    phone VARCHAR(20),
    whatsapp VARCHAR(20),
    email VARCHAR(150)
);

-- About Us
CREATE TABLE IF NOT EXISTS about_us (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    photo VARCHAR(255), -- URL atau path foto
    bio TEXT,
    history TEXT,
    social_links TEXT
);

-- Programs
CREATE TABLE IF NOT EXISTS programs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(150),
    description TEXT
);

-- Modules (pdf/jurnal)
CREATE TABLE IF NOT EXISTS modules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    program_id INT,
    title VARCHAR(150),
    file_path VARCHAR(255),
    CONSTRAINT fk_modules_program FOREIGN KEY (program_id) REFERENCES programs(id)
        ON DELETE CASCADE
);

-- Online Courses (YouTube link)
CREATE TABLE IF NOT EXISTS online_courses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    program_id INT,
    title VARCHAR(150),
    youtube_link VARCHAR(255),
    CONSTRAINT fk_courses_program FOREIGN KEY (program_id) REFERENCES programs(id)
        ON DELETE CASCADE
);

-- Certifications (hanya laporan statis)
CREATE TABLE IF NOT EXISTS certifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    program_id INT,
    certificate_name VARCHAR(150),
    description TEXT,
    CONSTRAINT fk_cert_program FOREIGN KEY (program_id) REFERENCES programs(id)
        ON DELETE CASCADE
);

-- Forums
CREATE TABLE IF NOT EXISTS forums (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(150),
    description TEXT
);

-- Posts (pertanyaan user di forum)
CREATE TABLE IF NOT EXISTS posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    forum_id INT,
    user_id INT,
    title VARCHAR(150),
    content TEXT,
    CONSTRAINT fk_posts_forum FOREIGN KEY (forum_id) REFERENCES forums(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_posts_user FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE SET NULL
);

-- Comments (jawaban user lain)
CREATE TABLE IF NOT EXISTS comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    post_id INT,
    user_id INT,
    content TEXT,
    CONSTRAINT fk_comments_post FOREIGN KEY (post_id) REFERENCES posts(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_comments_user FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE SET NULL
);

-- Community (link sosial media)
CREATE TABLE IF NOT EXISTS community_links (
    id INT AUTO_INCREMENT PRIMARY KEY,
    platform VARCHAR(100),
    link VARCHAR(255)
);

-- Documentaries (seperti w3schools)
CREATE TABLE IF NOT EXISTS documentaries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(150),
    code_example TEXT
);

-- Roadmaps (langkah belajar sesuai karir)
CREATE TABLE IF NOT EXISTS roadmaps (
    id INT AUTO_INCREMENT PRIMARY KEY,
    career_path VARCHAR(150),
    steps TEXT
);

-- ========================
-- Expanded Dummy Data
-- ========================

-- Users (guest)
INSERT INTO users (name, email) VALUES
('Andi Guest', 'andi.guest@example.com'),
('Siti Learner', 'siti.learner@example.com'),
('Budi Dev', 'budi.dev@example.com'),
('Rina Student', 'rina.student@example.com'),
('Agus Mentor', 'agus.mentor@example.com'),
('Dewi Junior', 'dewi.junior@example.com'),
('Fajar Expert', 'fajar.expert@example.com'),
('Lina Contributor', 'lina.contributor@example.com'),
('Toni Helper', 'toni.helper@example.com'),
('Maya Researcher', 'maya.researcher@example.com');

-- Contact Us (keep single primary contact, add another)
INSERT INTO contacts (phone, whatsapp, email) VALUES
('085822112870', '085822112870', 'bebleblablibub@gmail.com'),
('081234567890', '081234567890', 'contact@onlinelearning.example');

-- About Us (add 2 entries: primary + team)
INSERT INTO about_us (name, photo, bio, history, social_links) VALUES
(
  'Belva Pranama Sriwibowo',
  'https://avatars.githubusercontent.com/u/9919?s=280&v=4',
  'Seorang developer yang membangun platform pembelajaran online gratis.',
  'Website ini dibuat sebagai wadah berbagi ilmu pemrograman agar semua orang bisa belajar tanpa hambatan biaya.',
  'GitHub: https://github.com/belpythons | LinkedIn: https://www.linkedin.com/in/belva-pranama-01048a346/'
),
(
  'Tim EduTech Open',
  'https://via.placeholder.com/200',
  'Kami tim yang berdedikasi membuat materi pembelajaran yang mudah diakses.',
  'Dibentuk untuk menyebarkan ilmu dan kolaborasi komunitas.',
  'Twitter: https://twitter.com/edutechopen | Instagram: https://instagram.com/edutechopen'
);

-- Programs (10 programs)
INSERT INTO programs (title, description) VALUES
('Web Development Bootcamp', 'Belajar membuat website dari dasar sampai mahir.'),
('Python for Data Science', 'Belajar Python untuk analisis data dan machine learning.'),
('Frontend Mastery (React)', 'Kuasai HTML/CSS modern dan React untuk membangun antarmuka interaktif.'),
('Backend with Node.js', 'Membangun API dan layanan backend menggunakan Node.js dan Express.'),
('DevOps Essentials', 'Konsep dasar CI/CD, Docker, dan deployment modern.'),
('Mobile App Development (React Native)', 'Bangun aplikasi mobile cross-platform dengan React Native.'),
('Machine Learning Practical', 'Implementasi model ML dasar hingga lanjutan.'),
('Database Design & SQL', 'Desain database, normalisasi, dan optimasi query SQL.'),
('Security Basics for Web', 'Keamanan web: XSS, SQLi, autentikasi, dan best practices.'),
('UI/UX Design Fundamentals', 'Prinsip desain antarmuka dan pengalaman pengguna.');

-- Modules (4 per program => 40 modules)
INSERT INTO modules (program_id, title, file_path) VALUES
-- Program 1: Web Dev Bootcamp (program_id 1)
(1, 'HTML & CSS Basics', 'https://example.com/files/html-css-basics.pdf'),
(1, 'JavaScript Fundamentals', 'https://example.com/files/js-fundamentals.pdf'),
(1, 'Responsive Design & Flexbox', 'https://example.com/files/responsive-flexbox.pdf'),
(1, 'Project: Build a Portfolio Site', 'https://example.com/files/portfolio-project.pdf'),

-- Program 2: Python for Data Science (2)
(2, 'Python Basics', 'https://example.com/files/python-basics.pdf'),
(2, 'Data Analysis with Pandas', 'https://example.com/files/pandas-analysis.pdf'),
(2, 'Data Visualization (Matplotlib & Seaborn)', 'https://example.com/files/data-viz.pdf'),
(2, 'Project: Analysis Case Study', 'https://example.com/files/data-project.pdf'),

-- Program 3: Frontend Mastery (3)
(3, 'Advanced HTML5 & Accessibility', 'https://example.com/files/html5-accessibility.pdf'),
(3, 'Modern CSS (Grid & Animations)', 'https://example.com/files/modern-css.pdf'),
(3, 'React Hooks & State Management', 'https://example.com/files/react-hooks.pdf'),
(3, 'Deploying Frontend Apps', 'https://example.com/files/deploy-frontend.pdf'),

-- Program 4: Backend Node.js (4)
(4, 'Node.js Fundamentals', 'https://example.com/files/node-basics.pdf'),
(4, 'Express.js & REST APIs', 'https://example.com/files/express-rest.pdf'),
(4, 'Database Integration (MySQL)', 'https://example.com/files/node-mysql.pdf'),
(4, 'Authentication & Security', 'https://example.com/files/node-auth.pdf'),

-- Program 5: DevOps (5)
(5, 'Intro to Docker', 'https://example.com/files/docker-intro.pdf'),
(5, 'CI/CD with GitHub Actions', 'https://example.com/files/ci-cd.pdf'),
(5, 'Monitoring & Logging', 'https://example.com/files/monitoring.pdf'),
(5, 'Infrastructure as Code (Terraform)', 'https://example.com/files/terraform.pdf'),

-- Program 6: React Native (6)
(6, 'React Native Basics', 'https://example.com/files/rn-basics.pdf'),
(6, 'Navigation & State', 'https://example.com/files/rn-navigation.pdf'),
(6, 'Using Native Modules', 'https://example.com/files/rn-native.pdf'),
(6, 'Publishing to App Stores', 'https://example.com/files/rn-publish.pdf'),

-- Program 7: Machine Learning (7)
(7, 'Math for ML (Linear Algebra)', 'https://example.com/files/ml-math.pdf'),
(7, 'Supervised Learning', 'https://example.com/files/supervised-learning.pdf'),
(7, 'Model Evaluation & Tuning', 'https://example.com/files/model-eval.pdf'),
(7, 'Project: Build a Classifier', 'https://example.com/files/ml-project.pdf'),

-- Program 8: Database Design (8)
(8, 'ER Modeling & Normalization', 'https://example.com/files/er-normalization.pdf'),
(8, 'Advanced SQL Queries', 'https://example.com/files/advanced-sql.pdf'),
(8, 'Indexing & Performance', 'https://example.com/files/indexing.pdf'),
(8, 'Backup & Replication Strategies', 'https://example.com/files/backup-replication.pdf'),

-- Program 9: Security Basics (9)
(9, 'Intro to Web Security', 'https://example.com/files/web-security.pdf'),
(9, 'Preventing XSS & CSRF', 'https://example.com/files/xss-csrf.pdf'),
(9, 'Secure Authentication', 'https://example.com/files/secure-auth.pdf'),
(9, 'Security Testing Tools', 'https://example.com/files/security-tools.pdf'),

-- Program 10: UI/UX (10)
(10, 'Design Principles', 'https://example.com/files/design-principles.pdf'),
(10, 'Wireframing & Prototyping', 'https://example.com/files/wireframing.pdf'),
(10, 'Design Systems', 'https://example.com/files/design-systems.pdf'),
(10, 'Usability Testing', 'https://example.com/files/usability-testing.pdf')
;

-- Online Courses (2-3 per program)
INSERT INTO online_courses (program_id, title, youtube_link) VALUES
(1, 'Intro to HTML & CSS', 'https://www.youtube.com/watch?v=UB1O30fR-EE'),
(1, 'JavaScript Crash Course', 'https://www.youtube.com/watch?v=hdI2bqOjy3c'),
(1, 'Deploying Static Sites', 'https://www.youtube.com/watch?v=GfM1wWzJrT8'),

(2, 'Python for Beginners', 'https://www.youtube.com/watch?v=rfscVS0vtbw'),
(2, 'Data Science Full Course', 'https://www.youtube.com/watch?v=ua-CiDNNj30'),

(3, 'React JS Tutorial', 'https://www.youtube.com/watch?v=Ke90Tje7VS0'),
(3, 'Advanced React Patterns', 'https://www.youtube.com/watch?v=dpw9EHDh2bM'),

(4, 'Node.js Crash Course', 'https://www.youtube.com/watch?v=TlB_eWDSMt4'),
(4, 'Building REST APIs', 'https://www.youtube.com/watch?v=pKd0Rpw7O48'),

(5, 'Docker Tutorial', 'https://www.youtube.com/watch?v=9zUHg7xjIqQ'),
(5, 'CI/CD Concepts', 'https://www.youtube.com/watch?v=sX7nd5F9YJg'),

(6, 'React Native for Beginners', 'https://www.youtube.com/watch?v=Hf4MJH0jDb4'),
(6, 'React Native Navigation', 'https://www.youtube.com/watch?v=hkHkG0T1bQw'),

(7, 'Machine Learning Basics', 'https://www.youtube.com/watch?v=Gv9_4yMHFhI'),
(7, 'Deep Learning Overview', 'https://www.youtube.com/watch?v=aircAruvnKk'),

(8, 'SQL Tutorial - Full Course', 'https://www.youtube.com/watch?v=HXV3zeQKqGY'),
(8, 'Database Design Best Practices', 'https://www.youtube.com/watch?v=ztHopE5Wnpc'),

(9, 'Web Security Crash Course', 'https://www.youtube.com/watch?v=5QF7zB0hjUY'),
(9, 'OWASP Top 10 Explained', 'https://www.youtube.com/watch?v=3-ntzS28pRA'),

(10, 'Intro to UI/UX Design', 'https://www.youtube.com/watch?v=9Q5lIOo5i9w'),
(10, 'Figma Tutorial for Beginners', 'https://www.youtube.com/watch?v=4W4LvJnNegI')
;

-- Certifications (one per program)
INSERT INTO certifications (program_id, certificate_name, description) VALUES
(1, 'Web Development Certificate', 'Sertifikat penyelesaian bootcamp Web Development.'),
(2, 'Data Science Certificate', 'Sertifikat penyelesaian bootcamp Python for Data Science.'),
(3, 'Frontend Mastery Certificate', 'Sertifikat penyelesaian Frontend Mastery.'),
(4, 'Backend Node.js Certificate', 'Sertifikat penyelesaian Backend with Node.js.'),
(5, 'DevOps Essentials Certificate', 'Sertifikat penyelesaian DevOps Essentials.'),
(6, 'React Native Certificate', 'Sertifikat penyelesaian Mobile App Development.'),
(7, 'Machine Learning Certificate', 'Sertifikat penyelesaian Machine Learning Practical.'),
(8, 'Database Design Certificate', 'Sertifikat penyelesaian Database Design & SQL.'),
(9, 'Web Security Certificate', 'Sertifikat penyelesaian Security Basics for Web.'),
(10, 'UI/UX Certificate', 'Sertifikat penyelesaian UI/UX Design Fundamentals.');

-- Forums (6 topics)
INSERT INTO forums (title, description) VALUES
('Belajar Web Development', 'Forum diskusi untuk materi Web Development.'),
('Belajar Data Science', 'Forum diskusi untuk materi Data Science.'),
('Frontend Discussion', 'Diskusi khusus frontend: React, CSS, performance.'),
('Backend & API', 'Membahas best practice backend dan API design.'),
('DevOps & Deployment', 'Praktik CI/CD, Docker, hosting.'),
('Design & UX', 'Diskusi tentang UI/UX dan prototyping.');

-- Posts (multiple posts across forums)
INSERT INTO posts (forum_id, user_id, title, content) VALUES
(1, 1, 'Bagaimana cara membuat layout responsif?', 'Saya bingung menggunakan CSS Grid dan Flexbox. Ada tips?'),
(2, 2, 'Cara install library Pandas?', 'Saya baru belajar Python. Bagaimana cara install Pandas di laptop saya?'),
(3, 3, 'State management antara Redux dan Context', 'Kapan sebaiknya memakai Redux vs Context API?'),
(4, 5, 'Error 500 pada API saya', 'Saya deploy API tapi sering kena 500, bagaimana debugging terbaik?'),
(5, 6, 'Pipeline CI gagal di langkah test', 'Apakah ada langkah debugging untuk pipeline CI?'),
(6, 7, 'Cara melakukan usability testing', 'Bagaimana cara budget-friendly menguji usability?'),
(1, 8, 'Mengenal BEM untuk CSS', 'Apakah BEM masih relevan untuk proyek kecil?'),
(3, 9, 'Optimasi render React', 'Tips mengurangi rerender di komponen kompleks.'),
(4, 10, 'Rate limiting di API', 'Bagaimana menerapkan rate limiting sederhana?'),
(2, 4, 'Feature engineering ide', 'Saran fitur apa yang baik untuk dataset kecil?'),
(1, 2, 'Form validation best practice', 'Client-side vs server-side validation, apa yang wajib?'),
(5, 1, 'Monitoring app setelah deploy', 'Tools sederhana untuk monitoring aplikasi gratis?');

-- Comments (answers / replies)
INSERT INTO comments (post_id, user_id, content) VALUES
(1, 3, 'Gunakan CSS Flexbox untuk layout sederhana, dan Grid untuk layout kompleks.'),
(1, 5, 'Buat mobile-first, gunakan media queries berdasarkan breakpoint ponsel.'),
(2, 1, 'Ketik "pip install pandas" di terminal. Pastikan Python dan pip sudah terinstall.'),
(3, 4, 'Redux cocok untuk app besar, Context cukup untuk state yang tidak sering berubah.'),
(4, 6, 'Cek logs di server dan tambahkan middleware error handler untuk detail stacktrace.'),
(5, 2, 'Jalankan test lokal dulu lalu re-run job pipeline dengan debug enabled.'),
(6, 8, 'Lakukan remote moderated testing dengan teman/keluarga, rekam sesi untuk catatan.'),
(7, 3, 'BEM membantu konsistensi tetapi terkadang verbose untuk proyek kecil.'),
(8, 5, 'Gunakan memoization (React.memo, useMemo) dan hindari inline functions.'),
(9, 7, 'Gunakan express-rate-limit atau middleware serupa.'),
(10, 9, 'Coba teknik feature selection sederhana lalu cross-validation.'),
(11, 6, 'Validasi sisi klien untuk UX, selalu validasi sisi server untuk keamanan.'),
(12, 4, 'Gunakan free tier dari services seperti Grafana Cloud atau Prometheus + Grafana.');

-- Community Links
INSERT INTO community_links (platform, link) VALUES
('Telegram Group', 'https://t.me/joinchat/AAAAAE7yNnexample'),
('Discord Server', 'https://discord.gg/example'),
('WhatsApp Group', 'https://chat.whatsapp.com/example'),
('Youtube Channel', 'https://www.youtube.com/channel/example'),
('Twitter', 'https://twitter.com/onlinelearning_example');

-- Documentaries (mengambil dari W3Schools + resource lain)
INSERT INTO documentaries (title, code_example) VALUES
('HTML Tutorial', 'https://www.w3schools.com/html/'),
('CSS Tutorial', 'https://www.w3schools.com/css/'),
('JavaScript Tutorial', 'https://www.w3schools.com/js/'),
('Python Tutorial', 'https://www.w3schools.com/python/'),
('SQL Tutorial', 'https://www.w3schools.com/sql/'),
('Docker Tutorial', 'https://docs.docker.com/get-started/');

-- Roadmaps (more career paths)
INSERT INTO roadmaps (career_path, steps) VALUES
('Web Developer', '1. HTML & CSS -> 2. JavaScript -> 3. Framework (React/Vue) -> 4. Backend (Node.js) -> 5. Database (MySQL/MongoDB)'),
('Frontend Developer', '1. HTML/CSS -> 2. JavaScript -> 3. React/Vue -> 4. Performance -> 5. Accessibility'),
('Backend Developer', '1. HTTP & REST -> 2. Node.js/Express -> 3. Databases -> 4. Caching -> 5. Scaling'),
('Fullstack Developer', '1. Frontend dasar -> 2. Backend dasar -> 3. Deployment -> 4. DevOps dasar -> 5. Project portfolio'),
('Data Scientist', '1. Python -> 2. Pandas/Numpy -> 3. Visualization -> 4. Machine Learning -> 5. Deployment'),
('DevOps Engineer', '1. Linux -> 2. Containers -> 3. CI/CD -> 4. Monitoring -> 5. Infra as Code'),
('Mobile Developer', '1. JavaScript/React Native -> 2. Native Modules -> 3. Performance -> 4. Publishing'),
('UI/UX Designer', '1. Design Principles -> 2. Wireframing -> 3. Prototyping -> 4. User Testing');

-- EOF`