import mysql.connector import pandas as pd from app.config import DB_CONFIG def get_db_connection(): try: return mysql.connector.connect( **DB_CONFIG, ) except Exception as e: print(f"[DB Error] Gagal konek ke MySQL: {e}") return None # FUNGSI UNTUK CHATBOT & PROGRESS def fetch_student_progress(email): conn = get_db_connection() if not conn: return None try: # Ambil data progress user query = "SELECT * FROM student_progress WHERE email = %s" df = pd.read_sql(query, conn, params=(email,)) return df.to_dict(orient='records')[0] if not df.empty else None except Exception as e: print(f"[DB Error] Fetch Progress: {e}") return None finally: if conn and conn.is_connected(): conn.close() def fetch_current_tutorial_title(course_name, tutorial_order): """Mencari judul materi ke-X dari suatu course""" conn = get_db_connection() if not conn: return "Materi tidak ditemukan" try: query = """ SELECT t.tutorial_title FROM tutorials t JOIN courses c ON t.course_id = c.course_id WHERE c.course_name = %s ORDER BY t.tutorial_id ASC LIMIT 1 OFFSET %s """ # Offset = tutorial_order - 1 (karena index mulai 0) offset = max(0, int(tutorial_order) - 1) cursor = conn.cursor() cursor.execute(query, (course_name, offset)) result = cursor.fetchone() return result[0] if result else "Akhir Pembelajaran" except Exception as e: print(f"[DB Error] Fetch Tutorial: {e}") return "Info materi tidak tersedia" finally: if conn and conn.is_connected(): cursor.close() conn.close() def fetch_all_lp_course_names(): """Mengambil daftar semua nama kursus yang ada detailnya di tabel lp_answers""" conn = get_db_connection() if not conn: return [] try: query = "SELECT name FROM lp_answers" df = pd.read_sql(query, conn) return df['name'].tolist() finally: if conn and conn.is_connected(): conn.close() def fetch_lp_course_details_by_name(exact_name): """Mengambil detail lengkap kursus berdasarkan nama persis""" conn = get_db_connection() if not conn: return None try: query = "SELECT * FROM lp_answers WHERE name LIKE %s LIMIT 1" df = pd.read_sql(query, conn, params=(exact_name,)) return df.to_dict(orient='records')[0] if not df.empty else None finally: if conn and conn.is_connected(): conn.close() def fetch_all_learning_paths(): conn = get_db_connection() if not conn: return [] try: query = "SELECT learning_path_name FROM learning_paths" df = pd.read_sql(query, conn) return df['learning_path_name'].tolist() if not df.empty else [] finally: if conn and conn.is_connected(): conn.close() def fetch_courses_by_path_dynamic(path_name): conn = get_db_connection() if not conn: return [] try: query = """ SELECT c.course_name, c.course_level_str FROM courses c JOIN learning_paths lp ON c.learning_path_id = lp.learning_path_id WHERE lp.learning_path_name LIKE %s """ df = pd.read_sql(query, conn, params=(f"%{path_name}%",)) return df.to_dict(orient='records') finally: if conn and conn.is_connected(): conn.close() def fetch_all_courses(limit=40): conn = get_db_connection() if not conn: return [] try: query = """ SELECT c.course_name, lp.learning_path_name FROM courses c JOIN learning_paths lp ON c.learning_path_id = lp.learning_path_id LIMIT %s """ df = pd.read_sql(query, conn, params=(limit,)) return df.to_dict(orient='records') finally: if conn and conn.is_connected(): conn.close() # FUNGSI UNTUK ASSESSMENT & ROADMAP def fetch_questions_by_learning_path(lp_name): conn = get_db_connection() if not conn: return [] try: query = """ SELECT id, subskill, subskill_level, difficulty, question_desc, option_1, option_2, option_3, option_4, correct_answer FROM tech_questions WHERE learning_path LIKE %s """ df = pd.read_sql(query, conn, params=(f"%{lp_name}%",)) return df.to_dict(orient='records') finally: if conn and conn.is_connected(): conn.close() def fetch_specific_courses(path_name): conn = get_db_connection() if not conn: return [] try: query = """ SELECT c.course_name, c.course_level_str FROM courses c JOIN learning_paths lp ON c.learning_path_id = lp.learning_path_id WHERE lp.learning_path_name = %s """ df = pd.read_sql(query, conn, params=(path_name,)) return df.to_dict(orient='records') finally: conn.close() def fetch_questions_by_subskills(subskill_list): """ Mengambil soal berdasarkan DAFTAR SUBSKILL, bukan nama Learning Path. Ini solusi agar soal 'shared' (seperti Python) tetap muncul di GCP. """ conn = get_db_connection() if not conn: return [] if not subskill_list: return [] try: format_strings = ','.join(['%s'] * len(subskill_list)) query = f""" SELECT id, subskill, subskill_level, difficulty, question_desc, option_1, option_2, option_3, option_4, correct_answer FROM tech_questions WHERE subskill IN ({format_strings}) """ cursor = conn.cursor(dictionary=True) cursor.execute(query, tuple(subskill_list)) results = cursor.fetchall() return results except Exception as e: print(f"[DB Error] Fetch Questions by Subskill: {e}") return [] finally: if conn and conn.is_connected(): cursor.close() conn.close() def save_generated_roadmap(email, lp_name, roadmap_items, overall_level): conn = get_db_connection() if not conn: return None cursor = conn.cursor() try: # Hapus roadmap lama jika ada (untuk learning path yang sama) cursor.execute("DELETE FROM user_roadmaps WHERE user_email = %s AND learning_path_name = %s", (email, lp_name)) # Simpan Header cursor.execute( "INSERT INTO user_roadmaps (user_email, learning_path_name, status, overall_level, created_at, updated_at) VALUES (%s, %s, 'active', %s, NOW(), NOW())", (email, lp_name, overall_level) ) roadmap_id = cursor.lastrowid # Simpan Items for item in roadmap_items: cursor.execute( """INSERT INTO user_roadmap_items (user_roadmap_id, subskill_name, `order`, status, skill_level, created_at, updated_at) VALUES (%s, %s, %s, %s, %s, NOW(), NOW())""", (roadmap_id, item['subskill'], item['order'], item['status'], item['level_result']) ) conn.commit() return roadmap_id except Exception as e: conn.rollback() print(f"Error saving roadmap: {e}") return None finally: if conn and conn.is_connected(): cursor.close() conn.close() def fetch_weekly_progress(email): """Mencari skill yang diselesaikan dalam 7 hari terakhir""" conn = get_db_connection() if not conn: return None try: # Query: Cari item milik user yang completed & diupdate 7 hari terakhir query = """ SELECT r.learning_path_name, i.subskill_name, i.updated_at FROM user_roadmap_items i JOIN user_roadmaps r ON i.user_roadmap_id = r.id WHERE r.user_email = %s AND i.status = 'completed' AND i.updated_at >= DATE_SUB(NOW(), INTERVAL 7 DAY) ORDER BY i.updated_at DESC """ df = pd.read_sql(query, conn, params=(email,)) if df.empty: return None return df.to_dict(orient='records') except Exception as e: print(f"[DB Error] Fetch Weekly: {e}") return None finally: if conn and conn.is_connected(): conn.close() def fetch_next_recommendation(email): """Mencari materi Active selanjutnya untuk user""" conn = get_db_connection() if not conn: return None try: # Cari item 'active' dengan urutan (order) paling kecil query = """ SELECT r.learning_path_name, i.subskill_name, i.order, c.course_name FROM user_roadmap_items i JOIN user_roadmaps r ON i.user_roadmap_id = r.id LEFT JOIN learning_path_curriculums c ON (i.subskill_name = c.subskill_name AND r.learning_path_name = c.learning_path) WHERE r.user_email = %s AND i.status = 'active' ORDER BY i.order ASC LIMIT 1 """ cursor = conn.cursor(dictionary=True) cursor.execute(query, (email,)) result = cursor.fetchone() return result except Exception as e: print(f"[DB Error] Fetch Recommendation: {e}") return None finally: if conn and conn.is_connected(): cursor.close() conn.close() def recalculate_overall_level(roadmap_id): """Menghitung ulang level Learning Path berdasarkan item yang sudah completed""" conn = get_db_connection() if not conn: return None try: # 1. Ambil semua item di roadmap ini cursor = conn.cursor(dictionary=True) query = "SELECT skill_level, status FROM user_roadmap_items WHERE user_roadmap_id = %s" cursor.execute(query, (roadmap_id,)) items = cursor.fetchall() if not items: return "Beginner" # 2. Hitung Poin (Logika Rata-rata Berbobot) total_points = 0 total_items = len(items) for item in items: # Poin berdasarkan level skill yang tercatat level = item['skill_level'] status = item['status'] points = 1 # Default Beginner if level == 'Intermediate': points = 2 elif level == 'Advanced': points = 3 # Jika status 'completed', kita anggap user sudah mencapai level tersebut # Jika belum completed, kita bisa beri penalti atau hitung apa adanya (tergantung kebijakan) # Di sini kita hitung apa adanya sesuai hasil tes/update terakhir total_points += points # 3. Tentukan Level Baru avg_score = total_points / total_items new_level = "Beginner" if avg_score >= 2.6: new_level = "Advanced" elif avg_score >= 1.6: new_level = "Intermediate" # 4. Update ke Database update_query = "UPDATE user_roadmaps SET overall_level = %s WHERE id = %s" cursor.execute(update_query, (new_level, roadmap_id)) conn.commit() return new_level except Exception as e: print(f"[DB Error] Recalculate Level: {e}") return None finally: if conn and conn.is_connected(): cursor.close() conn.close()