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.Note;

import java.util.ArrayList;
import java.util.List;

public class NoteRepository {
    private final Context context;
    private DatabaseHelper helper;
    private SQLiteDatabase db;
    
    private static final java.util.Map<String, java.util.List<com.example.myapplication.domain.Note>> recommendedCache = new java.util.LinkedHashMap<String, java.util.List<com.example.myapplication.domain.Note>>() {
        @Override
        protected boolean removeEldestEntry(java.util.Map.Entry<String, java.util.List<com.example.myapplication.domain.Note>> eldest) {
            return size() > 20; // 最多缓存20个键
        }
    };

    public static void clearRecommendedCacheFor(Long userMajorId, String userGradeText) {
        String majorKey = userMajorId == null ? "" : String.valueOf(userMajorId);
        String gradeArg = userGradeText == null ? "" : userGradeText;
        String cacheKey = majorKey + "|" + gradeArg;
        recommendedCache.remove(cacheKey);
    }

    public NoteRepository(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(); }

    /**
     * 插入笔记对象（用于NoteUploadActivity）
     * @param note 笔记对象
     * @return 插入成功返回笔记ID，失败返回-1
     */
    public long insertNote(Note note) {
        if (note == null) return -1;
        
        openDatabase();
        try {
            ContentValues cv = new ContentValues();
            cv.put("title", note.getTitle());
            cv.put("content", note.getDescription());
            cv.put("file_path", note.getFilePath());
            cv.put("chapter_id", note.getChapterId());
            cv.put("uploader_id", note.getUploaderId());
            
            // 处理新的字段
            if (note.getOcrText() != null) {
                cv.put("ocr_text", note.getOcrText());
            }
            if (note.getUploadTime() > 0) {
                cv.put("upload_time", note.getUploadTime());
            }
            if (note.getUpdateTime() > 0) {
                cv.put("update_time", note.getUpdateTime());
            }
            
            long result = db.insert("T_Note", null, cv);
            return result;
        } catch (Exception e) {
            e.printStackTrace();
            return -1;
        } finally {
            closeDatabase();
        }
    }

    // 保存（long 版本）
    public boolean saveNote(String title, String content, String filePath, long courseId, String uploaderId) {
        openDatabase();
        ContentValues cv = new ContentValues();
        cv.put("title", title);
        cv.put("content", content);
        cv.put("file_path", filePath);
        if (courseId <= 0) { cv.putNull("chapter_id"); } else { cv.put("chapter_id", courseId); }
        if (uploaderId == null || uploaderId.isEmpty()) { cv.putNull("uploader_id"); } else { cv.put("uploader_id", uploaderId); }

        long r = db.insert("T_Note", null, cv);
        closeDatabase();
        return r != -1;
    }

    // 保存（Long 版本）
    public boolean saveNote(String title, String content, String filePath, Long courseId, String uploaderId) {
        openDatabase();
        ContentValues cv = new ContentValues();
        cv.put("title", title);
        cv.put("content", content);
        cv.put("file_path", filePath);
        if (courseId == null) { cv.putNull("chapter_id"); } else { cv.put("chapter_id", courseId); }
        if (uploaderId == null || uploaderId.isEmpty()) { cv.putNull("uploader_id"); } else { cv.put("uploader_id", uploaderId); }

        long r = db.insert("T_Note", null, cv);
        closeDatabase();
        return r != -1;
    }

    // 新增重载：支持可空的 courseId / uploaderId
    public boolean saveNoteWithSentinel(String title, String description, String filePath, long courseIdOrMinus1, String uploaderId) {
        openDatabase();
        ContentValues cv = new ContentValues();
        cv.put("title", title);
        cv.put("content", description);
        cv.put("file_path", filePath);
        if (courseIdOrMinus1 == -1L) {
            cv.putNull("chapter_id");
        } else {
            cv.put("chapter_id", courseIdOrMinus1);
        }

        if (uploaderId == null || uploaderId.isEmpty()) {
            cv.putNull("uploader_id");
        } else {
            cv.put("uploader_id", uploaderId);
        }

        long r = db.insert("T_Note", null, cv);
        closeDatabase();
        return r != -1;
    }

    public boolean deleteNote(long noteId) {
        com.example.myapplication.domain.Note n = getNoteById(noteId);
        String currentUser = com.example.myapplication.service.AuthService.getCurrentUser(context);
        if (n == null || n.getUploaderId() == null || currentUser == null || !n.getUploaderId().equals(currentUser)) {
            return false;
        }
        openDatabase();
        int rows = db.delete("T_Note", "id=?", new String[]{String.valueOf(noteId)});
        closeDatabase();
        return rows > 0;
    }

    public boolean updateNote(long noteId, ContentValues updateData) {
        boolean needsOwner = false;
        if (updateData != null) {
            needsOwner = updateData.containsKey("title")
                    || updateData.containsKey("content")
                    || updateData.containsKey("file_path")
                    || updateData.containsKey("chapter_id");
        }
        if (needsOwner) {
            com.example.myapplication.domain.Note n = getNoteById(noteId);
            String currentUser = com.example.myapplication.service.AuthService.getCurrentUser(context);
            if (n == null || n.getUploaderId() == null || currentUser == null || !n.getUploaderId().equals(currentUser)) {
                return false;
            }
        }
        openDatabase();
        int rows = db.update("T_Note", updateData, "id=?", new String[]{String.valueOf(noteId)});
        closeDatabase();
        return rows > 0;
    }

    public Note getNoteById(long noteId) {
        openDatabase();
        Note note = null;
        try (Cursor c = db.query("T_Note", null, "id=?", new String[]{String.valueOf(noteId)}, null, null, null)) {
            if (c != null && c.moveToFirst()) {
                note = map(c);
            }
        }
        closeDatabase();
        return note;
    }

    public List<Note> getNotesByUser(String userId) {
        openDatabase();
        List<Note> list = new ArrayList<>();
        try (Cursor c = db.query("T_Note", null, "uploader_id=?", new String[]{userId}, null, null, "created_at DESC")) {
            while (c != null && c.moveToNext()) list.add(map(c));
        }
        closeDatabase();
        return list;
    }

    public List<Note> findNotesByKeywords(String keywords, String filters) {
        openDatabase();
        List<Note> list = new ArrayList<>();
        String q = keywords == null ? "" : keywords.trim();
        String like = "%" + q + "%";

        // 1) 章节匹配
        List<Long> chapterIds = new ArrayList<>();
        try (Cursor c1 = db.rawQuery("SELECT id FROM T_Chapter WHERE name LIKE ? OR code LIKE ? ORDER BY order_num ASC", new String[]{like, like})) {
            while (c1 != null && c1.moveToNext()) chapterIds.add(c1.getLong(0));
        }
        if (!chapterIds.isEmpty()) {
            StringBuilder in = new StringBuilder();
            for (int i = 0; i < chapterIds.size(); i++) { if (i > 0) in.append(","); in.append("?"); }
            String sql = "SELECT * FROM T_Note WHERE chapter_id IN (" + in + ") ORDER BY created_at DESC";
            String[] args = new String[chapterIds.size()];
            for (int i = 0; i < chapterIds.size(); i++) args[i] = String.valueOf(chapterIds.get(i));
            try (Cursor c = db.rawQuery(sql, args)) { while (c != null && c.moveToNext()) list.add(map(c)); }
            if (!list.isEmpty()) { closeDatabase(); return list; }
        }

        // 2) 科目匹配（包含描述）
        List<Long> subjectIds = new ArrayList<>();
        try (Cursor c2 = db.rawQuery("SELECT id FROM T_Subject WHERE name LIKE ? OR code LIKE ? OR description LIKE ? ORDER BY name ASC", new String[]{like, like, like})) {
            while (c2 != null && c2.moveToNext()) subjectIds.add(c2.getLong(0));
        }
        if (!subjectIds.isEmpty()) {
            StringBuilder in = new StringBuilder();
            for (int i = 0; i < subjectIds.size(); i++) { if (i > 0) in.append(","); in.append("?"); }
            String sql = "SELECT n.* FROM T_Note n WHERE n.chapter_id IN (SELECT ch.id FROM T_Chapter ch WHERE ch.subject_id IN (" + in + ")) ORDER BY n.created_at DESC";
            String[] args = new String[subjectIds.size()];
            for (int i = 0; i < subjectIds.size(); i++) args[i] = String.valueOf(subjectIds.get(i));
            try (Cursor c = db.rawQuery(sql, args)) { while (c != null && c.moveToNext()) list.add(map(c)); }
            if (!list.isEmpty()) { closeDatabase(); return list; }
        }

        // 3) 专业匹配
        List<Long> majorIds = new ArrayList<>();
        try (Cursor c3 = db.rawQuery("SELECT id FROM T_Major WHERE name LIKE ? OR code LIKE ? ORDER BY name ASC", new String[]{like, like})) {
            while (c3 != null && c3.moveToNext()) majorIds.add(c3.getLong(0));
        }
        if (!majorIds.isEmpty()) {
            StringBuilder in = new StringBuilder();
            for (int i = 0; i < majorIds.size(); i++) { if (i > 0) in.append(","); in.append("?"); }
            String sql = "SELECT n.* FROM T_Note n WHERE n.chapter_id IN (SELECT ch.id FROM T_Chapter ch WHERE ch.subject_id IN (SELECT ms.subject_id FROM T_Major_Subject ms WHERE ms.major_id IN (" + in + "))) ORDER BY n.created_at DESC";
            String[] args = new String[majorIds.size()];
            for (int i = 0; i < majorIds.size(); i++) args[i] = String.valueOf(majorIds.get(i));
            try (Cursor c = db.rawQuery(sql, args)) { while (c != null && c.moveToNext()) list.add(map(c)); }
            if (!list.isEmpty()) { closeDatabase(); return list; }
        }

        // 4) 标题/内容模糊
        String selection = "title LIKE ? OR content LIKE ?";
        String[] args = new String[]{like, like};
        try (Cursor c = db.query("T_Note", null, selection, args, null, null, "created_at DESC")) {
            while (c != null && c.moveToNext()) list.add(map(c));
        }
        closeDatabase();
        return list;
    }

    // 新增：按标题模糊查询，仅匹配 title
    public java.util.List<com.example.myapplication.domain.Note> findNotesByTitleLike(String keywords) {
        openDatabase();
        java.util.List<com.example.myapplication.domain.Note> list = new java.util.ArrayList<>();
        String like = "%" + (keywords == null ? "" : keywords) + "%";
        try (android.database.Cursor c = db.query(
                "T_Note",
                null,
                "title LIKE ?",
                new String[]{like},
                null,
                null,
                "created_at DESC"
        )) {
            while (c != null && c.moveToNext()) list.add(map(c));
        }
        closeDatabase();
        return list;
    }

    public java.util.List<com.example.myapplication.domain.Note> getRecentNotes(int limit) {
        openDatabase();
        java.util.List<com.example.myapplication.domain.Note> list = new java.util.ArrayList<>();
        String orderBy = "created_at DESC";
        String limitStr = String.valueOf(Math.max(1, limit));
        try (android.database.Cursor c = db.query("T_Note", null, null, null, null, null, orderBy, limitStr)) {
            while (c != null && c.moveToNext()) list.add(map(c));
        }
        closeDatabase();
        return list;
    }

    private Note map(Cursor c) {
        Integer pg = null;
        try {
            int gi = c.getColumnIndex("publisher_grade");
            if (gi >= 0 && !c.isNull(gi)) pg = c.getInt(gi);
        } catch (Exception ignored) {}
        String pm = null;
        try { int mi = c.getColumnIndex("publisher_major"); if (mi >= 0) pm = c.getString(mi); } catch (Exception ignored) {}
        long chapterId = 0;
        try { chapterId = c.getLong(c.getColumnIndexOrThrow("chapter_id")); } catch (Exception ignored) {}
        String created = null;
        try { created = c.getString(c.getColumnIndexOrThrow("created_at")); } catch (Exception ignored) {}
        int likeCount = 0;
        try { likeCount = c.getInt(c.getColumnIndexOrThrow("like_count")); } catch (Exception ignored) {}
        String content = null;
        try { content = c.getString(c.getColumnIndexOrThrow("content")); } catch (Exception ignored) {}
        return new Note(
                c.getLong(c.getColumnIndexOrThrow("id")),
                c.getString(c.getColumnIndexOrThrow("uploader_id")),
                chapterId,
                content,
                c.getInt(c.getColumnIndexOrThrow("download_count")),
                likeCount,
                created,
                c.getString(c.getColumnIndexOrThrow("title")),
                c.getString(c.getColumnIndexOrThrow("file_path")),
                pg,
                pm
        );
    }

    public int getTotalLikesByUser(String userId) {
        openDatabase();
        int total = 0;
        try (android.database.Cursor c = db.rawQuery("SELECT COALESCE(SUM(like_count),0) FROM T_Note WHERE uploader_id=?", new String[]{userId})) {
            if (c != null && c.moveToFirst()) total = c.getInt(0);
        }
        closeDatabase();
        return total;
    }

    // 推荐查询：按优先级排序（同专业同年级 > 同专业 > 同年级 > 基础学科），支持分页
    public List<Note> getRecommendedNotes(Long userMajorId, String userGradeText, int limit, int offset) {
        openDatabase();
        List<Note> list = new ArrayList<>();

        String gradeArg = userGradeText == null ? "" : userGradeText;
        String majorKey = userMajorId == null ? "" : String.valueOf(userMajorId);
        String cacheKey = majorKey + "|" + gradeArg;
        if (offset == 0) {
            java.util.List<com.example.myapplication.domain.Note> cached = recommendedCache.get(cacheKey);
            if (cached != null) {
                for (int i = 0; i < Math.min(limit, cached.size()); i++) list.add(cached.get(i));
                closeDatabase();
                return list;
            }
        }

        String sqlPrimary =
                "SELECT n.*, " +
                "       m.name AS publisher_major_name, " +
                "       u.grade AS publisher_grade_text, " +
                "       CASE " +
                "         WHEN u.major_id = ? AND u.grade = ? THEN 1000 " +
                "         WHEN u.major_id = ? THEN 800 " +
                "         WHEN u.grade = ? THEN 600 " +
                "         ELSE 0 " +
                "       END AS relevance " +
                "FROM T_Note n " +
                "LEFT JOIN T_User u ON u.account = n.uploader_id " +
                "LEFT JOIN T_Major m ON m.id = u.major_id " +
                "LEFT JOIN T_Chapter ch ON ch.id = n.chapter_id " +
                "LEFT JOIN T_Subject s ON s.id = ch.subject_id " +
                "WHERE NOT (s.id = 20013 OR s.name LIKE '%高等数学%' OR s.code = 'ADVMath') " +
                "ORDER BY relevance DESC, n.like_count DESC, n.created_at DESC " +
                "LIMIT ? OFFSET ?";

        String[] argsPrimary = new String[]{
                userMajorId == null ? "-1" : String.valueOf(userMajorId),
                gradeArg,
                userMajorId == null ? "-1" : String.valueOf(userMajorId),
                gradeArg,
                String.valueOf(limit),
                String.valueOf(offset)
        };

        try (Cursor c = db.rawQuery(sqlPrimary, argsPrimary)) {
            while (c != null && c.moveToNext()) {
                Note n = map(c);
                try {
                    int mi = c.getColumnIndex("publisher_major_name");
                    if (mi >= 0 && !c.isNull(mi)) n.setPublisherMajor(c.getString(mi));
                } catch (Exception ignored) {}
                list.add(n);
            }
        }

        if (list.isEmpty()) {
            String sqlFallback =
                    "SELECT n.*, " +
                    "       m.name AS publisher_major_name, " +
                    "       u.grade AS publisher_grade_text " +
                    "FROM T_Note n " +
                    "LEFT JOIN T_User u ON u.account = n.uploader_id " +
                    "LEFT JOIN T_Major m ON m.id = u.major_id " +
                    "LEFT JOIN T_Chapter ch ON ch.id = n.chapter_id " +
                    "LEFT JOIN T_Subject s ON s.id = ch.subject_id " +
                    "WHERE (s.id = 20013 OR s.name LIKE '%高等数学%' OR s.code = 'ADVMath') " +
                    "ORDER BY n.like_count DESC, n.created_at DESC " +
                    "LIMIT ? OFFSET ?";

            String[] argsFallback = new String[]{
                    String.valueOf(limit),
                    String.valueOf(offset)
            };

            try (Cursor c2 = db.rawQuery(sqlFallback, argsFallback)) {
                while (c2 != null && c2.moveToNext()) {
                    Note n = map(c2);
                    try {
                        int mi = c2.getColumnIndex("publisher_major_name");
                        if (mi >= 0 && !c2.isNull(mi)) n.setPublisherMajor(c2.getString(mi));
                    } catch (Exception ignored) {}
                    list.add(n);
                }
            }
        }

        if (offset == 0) {
            recommendedCache.put(cacheKey, new java.util.ArrayList<>(list));
        }
        closeDatabase();
        return list;
    }
}
