package com.example.myapplication.repository;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.example.myapplication.domain.Chapter;
import com.example.myapplication.domain.Course;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class CourseRepository {
    private final Context context;
    private DatabaseHelper helper;
    private SQLiteDatabase db;

    public CourseRepository(Context context) { this.context = context.getApplicationContext(); }

    public void openDatabase() {
        if (helper == null) helper = new DatabaseHelper(context);
        if (db == null || !db.isOpen()) db = helper.getWritableDatabase();
    }

    public void closeDatabase() { if (db != null && db.isOpen()) db.close(); db = null; }

    public void close() { closeDatabase(); }

    public List<Course> getAllCourses() {
        openDatabase();
        List<Course> list = new ArrayList<>();
        try (Cursor c = db.query("T_Subject", null, null, null, null, null, "name ASC")) {
            while (c != null && c.moveToNext()) list.add(map(c));
        }
        closeDatabase();
        return list;
    }

    public List<Course> getCoursesByMajorAndGrade(Long majorId, String gradeText) {
        openDatabase();
        List<Course> list = new ArrayList<>();
        if (majorId == null || gradeText == null || gradeText.isEmpty()) {
            closeDatabase();
            return list;
        }
        String sql = "SELECT s.* FROM T_Subject s INNER JOIN T_Major_Subject ms ON ms.subject_id = s.id WHERE ms.major_id = ? AND ms.grade = ? ORDER BY s.name ASC";
        try (Cursor c = db.rawQuery(sql, new String[]{String.valueOf(majorId), gradeText})) {
            while (c != null && c.moveToNext()) list.add(map(c));
        }
        closeDatabase();
        return list;
    }

    public List<Course> getCoursesByMajor(Long majorId) {
        openDatabase();
        List<Course> list = new ArrayList<>();
        if (majorId == null) { closeDatabase(); return list; }
        String sql = "SELECT DISTINCT s.* FROM T_Subject s INNER JOIN T_Major_Subject ms ON ms.subject_id = s.id WHERE ms.major_id = ? ORDER BY s.name ASC";
        try (Cursor c = db.rawQuery(sql, new String[]{String.valueOf(majorId)})) {
            while (c != null && c.moveToNext()) list.add(map(c));
        }
        closeDatabase();
        return list;
    }

    public Course getCourseById(long courseId) {
        openDatabase();
        Course course = null;
        try (Cursor c = db.query("T_Subject", null, "id=?", new String[]{String.valueOf(courseId)}, null, null, null)) {
            if (c != null && c.moveToFirst()) course = map(c);
        }
        closeDatabase();
        return course;
    }

    public List<Course> findSimilarCourses(String keywords) {
        openDatabase();
        List<Course> list = new ArrayList<>();
        String like = "%" + (keywords == null ? "" : keywords) + "%";
        try (Cursor c = db.query("T_Subject", null, "name LIKE ? OR description LIKE ? OR code LIKE ?",
                new String[]{like, like, like}, null, null, "name ASC")) {
            while (c != null && c.moveToNext()) list.add(map(c));
        }
        closeDatabase();
        return list;
    }

    public Map<Course, List<Chapter>> getCourseCatalog() {
        Map<Course, List<Chapter>> map = new HashMap<>();
        List<Course> courses = getAllCourses();
        for (Course c : courses) {
            List<Chapter> chapters = getChaptersBySubjectId(c.getId());
            map.put(c, chapters);
        }
        return map;
    }

    // Optional: helper to insert/update courses
    public long upsertCourse(String name, String code, String description) {
        openDatabase();
        ContentValues cv = new ContentValues();
        cv.put("name", name);
        cv.put("code", code);
        cv.put("description", description);
        long id = db.insert("T_Subject", null, cv);
        closeDatabase();
        return id;
    }

    // 新增方法：插入课程及其章节
    public void insertCourseWithChapters(Course course, List<Chapter> chapters) {
        openDatabase();
        try {
            db.beginTransaction();
            
            // 插入课程
            ContentValues courseValues = new ContentValues();
            courseValues.put("id", course.getId());
            courseValues.put("name", course.getName());
            courseValues.put("code", course.getCode());
            courseValues.put("description", course.getDescription());
            db.insert("T_Subject", null, courseValues);
            
            // 插入章节
            for (Chapter chapter : chapters) {
                ContentValues chapterValues = new ContentValues();
                chapterValues.put("id", chapter.getId());
                chapterValues.put("name", chapter.getName());
                chapterValues.put("subject_id", chapter.getCourseId());
                chapterValues.put("order_num", chapter.getOrderIndex());
                if (chapter.getKeywords() != null) chapterValues.put("keywords", chapter.getKeywords());
                if (chapter.getAliases() != null) chapterValues.put("aliases", chapter.getAliases());
                db.insert("T_Chapter", null, chapterValues);
            }
            
            db.setTransactionSuccessful();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            db.endTransaction();
            closeDatabase();
        }
    }

    private Course map(Cursor c) {
        return new Course(
                c.getLong(c.getColumnIndexOrThrow("id")),
                c.getString(c.getColumnIndexOrThrow("description")),
                c.getString(c.getColumnIndexOrThrow("name")),
                c.getString(c.getColumnIndexOrThrow("code"))
        );
    }

    public List<Chapter> getChaptersBySubjectId(long subjectId) {
        openDatabase();
        List<Chapter> list = new ArrayList<>();
        try (Cursor c = db.query("T_Chapter", null, "subject_id=?", new String[]{String.valueOf(subjectId)}, null, null, "order_num ASC")) {
            while (c != null && c.moveToNext()) {
                Chapter ch = new Chapter();
                ch.setId(c.getLong(c.getColumnIndexOrThrow("id")));
                ch.setName(c.getString(c.getColumnIndexOrThrow("name")));
                int oi = 0; try { oi = c.getInt(c.getColumnIndexOrThrow("order_num")); } catch (Exception ignored) {}
                ch.setOrderIndex(oi);
                ch.setCourseId((int) subjectId);
                try {
                    int kwi = c.getColumnIndex("keywords");
                    if (kwi >= 0 && !c.isNull(kwi)) ch.setKeywords(c.getString(kwi));
                } catch (Exception ignored) {}
                try {
                    int ali = c.getColumnIndex("aliases");
                    if (ali >= 0 && !c.isNull(ali)) ch.setAliases(c.getString(ali));
                } catch (Exception ignored) {}
                list.add(ch);
            }
        }
        closeDatabase();
        return list;
    }

    public List<Chapter> findChaptersByNameLike(String q, int limit) {
        openDatabase();
        List<Chapter> list = new ArrayList<>();
        String like = "%" + (q == null ? "" : q) + "%";
        String lim = String.valueOf(Math.max(1, limit));
        try (Cursor c = db.query("T_Chapter", null, "name LIKE ? OR code LIKE ?", new String[]{like, like}, null, null, "order_num ASC", lim)) {
            while (c != null && c.moveToNext()) {
                Chapter ch = new Chapter();
                ch.setId(c.getLong(c.getColumnIndexOrThrow("id")));
                ch.setName(c.getString(c.getColumnIndexOrThrow("name")));
                int oi = 0; try { oi = c.getInt(c.getColumnIndexOrThrow("order_num")); } catch (Exception ignored) {}
                int sid = 0; try { sid = c.getInt(c.getColumnIndexOrThrow("subject_id")); } catch (Exception ignored) {}
                ch.setOrderIndex(oi);
                ch.setCourseId(sid);
                try {
                    int kwi = c.getColumnIndex("keywords");
                    if (kwi >= 0 && !c.isNull(kwi)) ch.setKeywords(c.getString(kwi));
                } catch (Exception ignored) {}
                try {
                    int ali = c.getColumnIndex("aliases");
                    if (ali >= 0 && !c.isNull(ali)) ch.setAliases(c.getString(ali));
                } catch (Exception ignored) {}
                list.add(ch);
            }
        }
        closeDatabase();
        return list;
    }
}