package com.example.myapplication.repository;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * Central SQLite helper creating tables for T_User, T_Note, T_Comment, T_Like, T_Course.
 */
public class DatabaseHelper extends SQLiteOpenHelper {
    public static final String DB_NAME = "mynote.db";
    private static final int DB_VERSION = 10;

    public DatabaseHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    @Override
    public void onConfigure(SQLiteDatabase db) {
        db.setForeignKeyConstraintsEnabled(true);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE IF NOT EXISTS T_User (" +
                "account TEXT PRIMARY KEY," +
                "name TEXT," +
                "password TEXT," +
                "type INTEGER," +
                "grade TEXT," +
                "major_id INTEGER," +
                "point INTEGER," +
                "FOREIGN KEY(major_id) REFERENCES T_Major(id) ON DELETE SET NULL" +
                ")");

        db.execSQL("CREATE TABLE IF NOT EXISTS T_Major (" +
                "id INTEGER PRIMARY KEY," +
                "name TEXT," +
                "code TEXT," +
                "description TEXT" +
                ")");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10001,'计算机科学与技术','CS','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10002,'软件工程','SE','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10003,'电子信息工程','EE','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10004,'电气工程及其自动化','EEA','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10005,'机械工程','ME','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10006,'土木工程','CE','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10007,'建筑学','ARCH','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10008,'材料科学与工程','MSE','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10009,'数学与应用数学','MATH','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10010,'物理学','PHYS','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10011,'化学','CHEM','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10012,'生物科学','BIO','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10013,'医学','MED','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10014,'经济学','ECON','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10015,'金融学','FIN','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10016,'会计学','ACC','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10017,'工商管理','MBA','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10018,'法学','LAW','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10019,'教育学','EDU','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10020,'外国语言文学','LANG','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10021,'思想政治教育','IDEO','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10022,'信息安全','INFOSEC','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10023,'安全工程','SAFEENG','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10024,'应用心理学','APPSY','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10025,'物流管理','LOGMGT','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10026,'飞行器质量与可靠性','AEROQUAL','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10027,'无人驾驶航空器系统工程','UAVSYS','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10028,'飞行器运维工程','AEROMAINT','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10029,'交通工程','TRAENG','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10030,'智慧交通','SMARTTRA','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10031,'交通管理','TRAMGT','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10032,'交通运输','TRANSPORT','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10033,'应用气象学','APPMETEOR','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10034,'飞行技术','FLYTECH','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10035,'机械电子工程','MECHATRON','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10036,'飞行器动力工程','AEROPROP','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10037,'材料科学与工程','MSE2','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10038,'飞行器制造工程','AEROMFG','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10039,'飞行器设计与工程','AERODESIGN','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10040,'油气储运工程','OILGASTR','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10041,'通信工程','COMM','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10042,'物联网工程','IOT','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10043,'自动化','AUTO','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10044,'人工智能','AI','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10045,'信息与计算科学','INFOCS','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10046,'统计学','STAT','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10047,'材料物理','MATERIALPHY','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10048,'材料化学','MATERIALCHEM','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10049,'财务管理','FINMGT','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10050,'公共事业管理','PUBMGT','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10051,'经济与金融','ECONFIN','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10052,'工商管理','BUSADM','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10053,'翻译','TRANS','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10054,'英语','ENGLISH','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10055,'空中乘务','CABIN','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10056,'航空服务艺术与管理','AIRSRV','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10057,'民航空中安全保卫','AIRSEC','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10058,'航空安防管理','AIRSAFE','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10059,'民航安全技术管理','AVSAFETECH','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10060,'飞机电子设备维修','AVIONICSR','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10061,'飞机电设备维修','AIRPOWER','')");
        db.execSQL("INSERT OR IGNORE INTO T_Major (id,name,code,description) VALUES (10062,'民航运输服务','AIRTRANS','')");

        db.execSQL("CREATE TABLE IF NOT EXISTS T_Subject (" +
                "id INTEGER PRIMARY KEY," +
                "name TEXT," +
                "code TEXT," +
                "description TEXT" +
                ")");

        // 课程种子（按常见课程与贵校专业扩展）
        db.execSQL("INSERT OR IGNORE INTO T_Subject (id,name,code,description) VALUES (20001,'人工智能','AI','机器学习、深度学习、强化学习')");
        db.execSQL("INSERT OR IGNORE INTO T_Subject (id,name,code,description) VALUES (20002,'数据结构与算法','DSA','线性表、树、图、排序与查找')");
        db.execSQL("INSERT OR IGNORE INTO T_Subject (id,name,code,description) VALUES (20003,'操作系统','OS','进程与线程、调度、内存管理、文件系统')");
        db.execSQL("INSERT OR IGNORE INTO T_Subject (id,name,code,description) VALUES (20004,'计算机网络','NET','TCP/IP、路由、交换、应用层协议')");
        db.execSQL("INSERT OR IGNORE INTO T_Subject (id,name,code,description) VALUES (20005,'数据库系统','DB','SQL、事务、索引、范式与设计')");
        db.execSQL("INSERT OR IGNORE INTO T_Subject (id,name,code,description) VALUES (20006,'信号与系统','SIGSYS','时域与频域、卷积、傅里叶、拉普拉斯')");
        db.execSQL("INSERT OR IGNORE INTO T_Subject (id,name,code,description) VALUES (20007,'数字通信','DIGCOM','调制解调、编码、容量、误码率')");
        db.execSQL("INSERT OR IGNORE INTO T_Subject (id,name,code,description) VALUES (20008,'交通工程导论','TRAENG','道路、交通流、信控、设施设计')");
        db.execSQL("INSERT OR IGNORE INTO T_Subject (id,name,code,description) VALUES (20009,'飞行原理','FLYPRIN','空气动力、升力阻力、机翼特性')");
        db.execSQL("INSERT OR IGNORE INTO T_Subject (id,name,code,description) VALUES (20010,'材料科学基础','MSEBAS','晶体结构、相图、力学与热学性能')");
        db.execSQL("INSERT OR IGNORE INTO T_Subject (id,name,code,description) VALUES (20011,'安全工程导论','SAFEENG','风险评估、隐患排查、应急管理')");
        db.execSQL("INSERT OR IGNORE INTO T_Subject (id,name,code,description) VALUES (20012,'应用气象学','APPMET','环流、边界层、观测与预报')");
        db.execSQL("INSERT OR IGNORE INTO T_Subject (id,name,code,description) VALUES (20013,'高等数学','ADVMath','微积分、线性代数、概率统计')");


        db.execSQL("CREATE TABLE IF NOT EXISTS T_Major_Subject (" +
                "major_id INTEGER," +
                "subject_id INTEGER," +
                "grade TEXT NOT NULL DEFAULT ''," +
                "PRIMARY KEY(major_id, subject_id, grade)," +
                "FOREIGN KEY(major_id) REFERENCES T_Major(id) ON DELETE CASCADE," +
                "FOREIGN KEY(subject_id) REFERENCES T_Subject(id) ON DELETE CASCADE" +
                ")");

        // 计算机学院专业-课程映射（按年级限定）
        String[] grades = new String[]{"大一","大二","大三","大四"};
        for (String g : grades) {
            // 计算机科学与技术（10001）→ 数据结构、操作系统、计算机网络、数据库系统
            db.execSQL("INSERT OR IGNORE INTO T_Major_Subject (major_id,subject_id,grade) VALUES (10001,20002,'" + g + "')");
            db.execSQL("INSERT OR IGNORE INTO T_Major_Subject (major_id,subject_id,grade) VALUES (10001,20003,'" + g + "')");
            db.execSQL("INSERT OR IGNORE INTO T_Major_Subject (major_id,subject_id,grade) VALUES (10001,20004,'" + g + "')");
            db.execSQL("INSERT OR IGNORE INTO T_Major_Subject (major_id,subject_id,grade) VALUES (10001,20005,'" + g + "')");
            // 人工智能（10044）→ 人工智能、以及通用基础课
            db.execSQL("INSERT OR IGNORE INTO T_Major_Subject (major_id,subject_id,grade) VALUES (10044,20001,'" + g + "')");
            db.execSQL("INSERT OR IGNORE INTO T_Major_Subject (major_id,subject_id,grade) VALUES (10044,20002,'" + g + "')");
            db.execSQL("INSERT OR IGNORE INTO T_Major_Subject (major_id,subject_id,grade) VALUES (10044,20003,'" + g + "')");
            db.execSQL("INSERT OR IGNORE INTO T_Major_Subject (major_id,subject_id,grade) VALUES (10044,20004,'" + g + "')");
            db.execSQL("INSERT OR IGNORE INTO T_Major_Subject (major_id,subject_id,grade) VALUES (10044,20005,'" + g + "')");
            // 公共数学基础
            db.execSQL("INSERT OR IGNORE INTO T_Major_Subject (major_id,subject_id,grade) VALUES (10001,20013,'" + g + "')");
            db.execSQL("INSERT OR IGNORE INTO T_Major_Subject (major_id,subject_id,grade) VALUES (10044,20013,'" + g + "')");
        }

        db.execSQL("CREATE TABLE IF NOT EXISTS T_Chapter (" +
                "id INTEGER PRIMARY KEY," +
                "subject_id INTEGER," +
                "name TEXT," +
                "code TEXT," +
                "description TEXT," +
                "keywords TEXT," +
                "aliases TEXT," +
                "order_num INTEGER," +
                "FOREIGN KEY(subject_id) REFERENCES T_Subject(id) ON DELETE CASCADE" +
                ")");
        db.execSQL("CREATE INDEX IF NOT EXISTS idx_chapter_subject_order ON T_Chapter(subject_id, order_num)");

        // 章节种子（含关键词与别名，便于自动分类）
        // 人工智能
        db.execSQL("INSERT OR IGNORE INTO T_Chapter (id,subject_id,name,order_num,keywords,aliases) VALUES (30001,20001,'机器学习基础',1,'监督学习,非监督学习,回归,分类,交叉验证','machine learning,ml,supervised,unsupervised')");
        db.execSQL("INSERT OR IGNORE INTO T_Chapter (id,subject_id,name,order_num,keywords,aliases) VALUES (30002,20001,'深度学习',2,'神经网络,卷积,反向传播,激活函数,梯度下降','deep learning,cnn,rnn,bp,activation')");
        db.execSQL("INSERT OR IGNORE INTO T_Chapter (id,subject_id,name,order_num,keywords,aliases) VALUES (30003,20001,'强化学习',3,'马尔可夫决策,策略,价值函数,探索利用','reinforcement learning,mdp,policy,value,exploration')");
        // 数据结构与算法
        db.execSQL("INSERT OR IGNORE INTO T_Chapter (id,subject_id,name,order_num,keywords,aliases) VALUES (30011,20002,'线性结构',1,'数组,链表,栈,队列','array,list,stack,queue')");
        db.execSQL("INSERT OR IGNORE INTO T_Chapter (id,subject_id,name,order_num,keywords,aliases) VALUES (30012,20002,'树与图',2,'二叉树,平衡树,堆,图遍历,最短路径','tree,graph,dfs,bfs,heap,dijkstra')");
        db.execSQL("INSERT OR IGNORE INTO T_Chapter (id,subject_id,name,order_num,keywords,aliases) VALUES (30013,20002,'排序与查找',3,'快速排序,归并排序,哈希,二分查找','quicksort,mergesort,hash,binary search')");
        // 操作系统
        db.execSQL("INSERT OR IGNORE INTO T_Chapter (id,subject_id,name,order_num,keywords,aliases) VALUES (30021,20003,'进程与线程',1,'调度,同步,死锁,临界区','process,thread,schedule,lock')");
        db.execSQL("INSERT OR IGNORE INTO T_Chapter (id,subject_id,name,order_num,keywords,aliases) VALUES (30022,20003,'内存管理',2,'分页,分段,虚拟内存,置换算法','memory,virtual,paging,mmu')");
        db.execSQL("INSERT OR IGNORE INTO T_Chapter (id,subject_id,name,order_num,keywords,aliases) VALUES (30023,20003,'文件系统',3,'索引,日志,磁盘,目录结构','filesystem,inode,journal,disk')");
        // 计算机网络
        db.execSQL("INSERT OR IGNORE INTO T_Chapter (id,subject_id,name,order_num,keywords,aliases) VALUES (30031,20004,'网络层',1,'路由,IP,子网,拥塞','ip,routing,subnet,congestion')");
        db.execSQL("INSERT OR IGNORE INTO T_Chapter (id,subject_id,name,order_num,keywords,aliases) VALUES (30032,20004,'传输层',2,'TCP,UDP,流控,拥塞控制','tcp,udp,flow')");
        db.execSQL("INSERT OR IGNORE INTO T_Chapter (id,subject_id,name,order_num,keywords,aliases) VALUES (30033,20004,'应用层',3,'HTTP,DNS,SMTP,FTP','http,dns,email,ftp')");
        // 数据库系统
        db.execSQL("INSERT OR IGNORE INTO T_Chapter (id,subject_id,name,order_num,keywords,aliases) VALUES (30041,20005,'关系模型与SQL',1,'范式,主键,外键,查询,连接','sql,normal form,join')");
        db.execSQL("INSERT OR IGNORE INTO T_Chapter (id,subject_id,name,order_num,keywords,aliases) VALUES (30042,20005,'事务与并发',2,'ACID,锁,隔离级别,日志','transaction,acid,lock,isolation')");
        db.execSQL("INSERT OR IGNORE INTO T_Chapter (id,subject_id,name,order_num,keywords,aliases) VALUES (30043,20005,'索引与优化',3,'B树,哈希索引,执行计划,优化器','index,b-tree,optimizer')");
        // 信号与系统 / 数字通信
        db.execSQL("INSERT OR IGNORE INTO T_Chapter (id,subject_id,name,order_num,keywords,aliases) VALUES (30051,20006,'时域与频域',1,'卷积,傅里叶变换,频谱','convolution,fourier,spectrum')");
        db.execSQL("INSERT OR IGNORE INTO T_Chapter (id,subject_id,name,order_num,keywords,aliases) VALUES (30061,20007,'调制与编码',1,'ASK,PSK,QAM,信道编码','modulation,qam,channel coding')");
        // 交通工程 / 飞行原理 / 材料科学基础 / 安全工程 / 应用气象学
        db.execSQL("INSERT OR IGNORE INTO T_Chapter (id,subject_id,name,order_num,keywords,aliases) VALUES (30071,20008,'信号控制',1,'交叉口,配时,路口控制','signal control,intersection')");
        db.execSQL("INSERT OR IGNORE INTO T_Chapter (id,subject_id,name,order_num,keywords,aliases) VALUES (30081,20009,'空气动力学基础',1,'升力,阻力,机翼,攻角','aerodynamics,lift,drag,airfoil')");
        db.execSQL("INSERT OR IGNORE INTO T_Chapter (id,subject_id,name,order_num,keywords,aliases) VALUES (30091,20010,'晶体与相图',1,'晶体结构,相变,相图','crystal,phase diagram')");
        db.execSQL("INSERT OR IGNORE INTO T_Chapter (id,subject_id,name,order_num,keywords,aliases) VALUES (30101,20011,'风险评估',1,'危险源,隐患,评估方法,应急','risk,hazard,assessment,emergency')");
        db.execSQL("INSERT OR IGNORE INTO T_Chapter (id,subject_id,name,order_num,keywords,aliases) VALUES (30111,20012,'天气系统',1,'环流,锋面,降水,预报','circulation,front,precipitation,forecast')");
        // 高等数学
        db.execSQL("INSERT OR IGNORE INTO T_Chapter (id,subject_id,name,order_num,keywords,aliases) VALUES (30121,20013,'微积分',1,'极限,导数,积分,泰勒展开','calculus,derivative,integral,taylor')");
        db.execSQL("INSERT OR IGNORE INTO T_Chapter (id,subject_id,name,order_num,keywords,aliases) VALUES (30122,20013,'线性代数',2,'矩阵,向量,特征值,特征向量,秩','linear algebra,matrix,eigenvalue,eigenvector,rank')");
        db.execSQL("INSERT OR IGNORE INTO T_Chapter (id,subject_id,name,order_num,keywords,aliases) VALUES (30123,20013,'概率论与数理统计',3,'概率分布,方差,期望,假设检验,样本','probability,distribution,variance,expectation,hypothesis test')");

        db.execSQL("CREATE TABLE IF NOT EXISTS T_Note (" +
                "id INTEGER PRIMARY KEY," +
                "title TEXT," +
                "content TEXT," +
                "file_path TEXT," +
                "uploader_id TEXT," +
                "chapter_id INTEGER," +
                "download_count INTEGER DEFAULT 0," +
                "like_count INTEGER DEFAULT 0," +
                "created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP," +
                "FOREIGN KEY(uploader_id) REFERENCES T_User(account) ON DELETE SET NULL," +
                "FOREIGN KEY(chapter_id) REFERENCES T_Chapter(id) ON DELETE SET NULL" +
                ")");

        db.execSQL("CREATE INDEX IF NOT EXISTS idx_user_grade ON T_User(grade)");
        db.execSQL("CREATE INDEX IF NOT EXISTS idx_user_major_id ON T_User(major_id)");

        db.execSQL("CREATE TABLE IF NOT EXISTS T_Comment (" +
                "id INTEGER PRIMARY KEY," +
                "user_id TEXT," +
                "note_id INTEGER," +
                "content TEXT," +
                "created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP," +
                "FOREIGN KEY(user_id) REFERENCES T_User(account) ON DELETE SET NULL," +
                "FOREIGN KEY(note_id) REFERENCES T_Note(id) ON DELETE CASCADE" +
                ")");

        db.execSQL("CREATE TABLE IF NOT EXISTS T_Like (" +
                "user_id TEXT," +
                "note_id INTEGER," +
                "created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP," +
                "PRIMARY KEY(user_id, note_id)," +
                "FOREIGN KEY(user_id) REFERENCES T_User(account) ON DELETE CASCADE," +
                "FOREIGN KEY(note_id) REFERENCES T_Note(id) ON DELETE CASCADE" +
                ")");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        try {
            if (oldVersion < 2) {
                db.execSQL("CREATE TABLE IF NOT EXISTS T_Note_new (id INTEGER PRIMARY KEY AUTOINCREMENT,uploader_id TEXT,course_id INTEGER,content TEXT,download_count INTEGER DEFAULT 0,created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,title TEXT,file_path TEXT,FOREIGN KEY(uploader_id) REFERENCES T_User(account) ON DELETE SET NULL)");
                db.execSQL("INSERT INTO T_Note_new (id, uploader_id, course_id, content, download_count, created, title, file_path) SELECT id, uploader_id, course_id, COALESCE(ocr_text, description), download_count, created, title, file_path FROM T_Note");
                db.execSQL("DROP TABLE IF EXISTS T_Note");
                db.execSQL("ALTER TABLE T_Note_new RENAME TO T_Note");
            }

            if (oldVersion < 3) {
                db.execSQL("ALTER TABLE T_User ADD COLUMN points INTEGER DEFAULT 10");
            }

            if (oldVersion < 5) {
                db.execSQL("CREATE TABLE IF NOT EXISTS T_User_new (account TEXT PRIMARY KEY,name TEXT,password TEXT,type INTEGER,mobile TEXT,grade TEXT,major TEXT,point INTEGER)");
                db.execSQL("INSERT INTO T_User_new (account,name,password,type,mobile,grade,major,point) SELECT account,name,password,type,mobile,CASE WHEN grade IS NULL THEN NULL ELSE CAST(grade AS TEXT) END,major,COALESCE(points,0) FROM T_User");
                db.execSQL("DROP TABLE IF EXISTS T_User");
                db.execSQL("ALTER TABLE T_User_new RENAME TO T_User");

                onCreate(db);

                db.execSQL("CREATE TABLE IF NOT EXISTS T_Note_new (id INTEGER PRIMARY KEY,title TEXT,description TEXT,file_path TEXT,uploader_id TEXT,chapter_id INTEGER,ocr_text TEXT,ocr_status TEXT,download_count INTEGER DEFAULT 0,like_count INTEGER DEFAULT 0,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(uploader_id) REFERENCES T_User(account) ON DELETE SET NULL,FOREIGN KEY(chapter_id) REFERENCES T_Chapter(id) ON DELETE SET NULL)");
                db.execSQL("INSERT INTO T_Note_new (id,title,description,file_path,uploader_id,chapter_id,ocr_text,ocr_status,download_count,like_count,created_at) SELECT n.id,n.title,COALESCE(n.content,''),n.file_path,n.uploader_id,NULL,NULL,NULL,COALESCE(n.download_count,0),(SELECT COUNT(*) FROM T_Like l WHERE l.note_id = n.id),COALESCE(n.created,CURRENT_TIMESTAMP) FROM T_Note n");
                db.execSQL("DROP TABLE IF EXISTS T_Note");
                db.execSQL("ALTER TABLE T_Note_new RENAME TO T_Note");

                db.execSQL("CREATE TABLE IF NOT EXISTS T_Comment_new (id INTEGER PRIMARY KEY,user_id TEXT,note_id INTEGER,content TEXT,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(user_id) REFERENCES T_User(account) ON DELETE SET NULL,FOREIGN KEY(note_id) REFERENCES T_Note(id) ON DELETE CASCADE)");
                db.execSQL("INSERT INTO T_Comment_new (id,user_id,note_id,content,created_at) SELECT id,uploader_id,note_id,content,created_at FROM T_Comment");
                db.execSQL("DROP TABLE IF EXISTS T_Comment");
                db.execSQL("ALTER TABLE T_Comment_new RENAME TO T_Comment");
            }

            if (oldVersion < 6) {
                db.execSQL("CREATE TABLE IF NOT EXISTS T_User_new (account TEXT PRIMARY KEY,name TEXT,password TEXT,type INTEGER,mobile TEXT,grade TEXT,major_id INTEGER,point INTEGER,FOREIGN KEY(major_id) REFERENCES T_Major(id) ON DELETE SET NULL)");
                db.execSQL("INSERT INTO T_User_new (account,name,password,type,mobile,grade,major_id,point) SELECT account,name,password,type,mobile,grade,(SELECT id FROM T_Major m WHERE m.name LIKE '%'||COALESCE(T_User.major,'')||'%' OR m.code LIKE '%'||COALESCE(T_User.major,'')||'%' LIMIT 1),COALESCE(point,0) FROM T_User");
                db.execSQL("DROP TABLE IF EXISTS T_User");
                db.execSQL("ALTER TABLE T_User_new RENAME TO T_User");
            }

            if (oldVersion < 7) {
                db.execSQL("CREATE TABLE IF NOT EXISTS T_Note_new (id INTEGER PRIMARY KEY,title TEXT,content TEXT,file_path TEXT,uploader_id TEXT,chapter_id INTEGER,download_count INTEGER DEFAULT 0,like_count INTEGER DEFAULT 0,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(uploader_id) REFERENCES T_User(account) ON DELETE SET NULL,FOREIGN KEY(chapter_id) REFERENCES T_Chapter(id) ON DELETE SET NULL)");
                db.execSQL("INSERT INTO T_Note_new (id,title,content,file_path,uploader_id,chapter_id,download_count,like_count,created_at) SELECT n.id,n.title,COALESCE(n.description,''),n.file_path,n.uploader_id,n.chapter_id,COALESCE(n.download_count,0),COALESCE(n.like_count,(SELECT COUNT(*) FROM T_Like l WHERE l.note_id = n.id)),COALESCE(n.created_at,CURRENT_TIMESTAMP) FROM T_Note n");
                db.execSQL("DROP TABLE IF EXISTS T_Note");
                db.execSQL("ALTER TABLE T_Note_new RENAME TO T_Note");
            }

            if (oldVersion < 8) {
                db.execSQL("CREATE TABLE IF NOT EXISTS T_User_new (account TEXT PRIMARY KEY,name TEXT,password TEXT,type INTEGER,grade TEXT,major_id INTEGER,point INTEGER,FOREIGN KEY(major_id) REFERENCES T_Major(id) ON DELETE SET NULL)");
                db.execSQL("INSERT INTO T_User_new (account,name,password,type,grade,major_id,point) SELECT account,name,password,type,grade,major_id,point FROM T_User");
                db.execSQL("DROP TABLE IF EXISTS T_User");
                db.execSQL("ALTER TABLE T_User_new RENAME TO T_User");

                db.execSQL("CREATE TABLE IF NOT EXISTS T_Major_Subject_new (major_id INTEGER,subject_id INTEGER,grade TEXT NOT NULL DEFAULT '',PRIMARY KEY(major_id, subject_id, grade),FOREIGN KEY(major_id) REFERENCES T_Major(id) ON DELETE CASCADE,FOREIGN KEY(subject_id) REFERENCES T_Subject(id) ON DELETE CASCADE)");
                db.execSQL("INSERT INTO T_Major_Subject_new (major_id,subject_id,grade) SELECT major_id,subject_id,'' FROM T_Major_Subject");
                db.execSQL("DROP TABLE IF EXISTS T_Major_Subject");
                db.execSQL("ALTER TABLE T_Major_Subject_new RENAME TO T_Major_Subject");
            }

        if (oldVersion < 9) {
            try { db.execSQL("ALTER TABLE T_Chapter ADD COLUMN keywords TEXT"); } catch (Exception ignored) {}
            try { db.execSQL("ALTER TABLE T_Chapter ADD COLUMN aliases TEXT"); } catch (Exception ignored) {}
        }
        if (oldVersion < 10) {
            String[] grades2 = new String[]{"大一","大二","大三","大四"};
            for (String g : grades2) {
                db.execSQL("INSERT OR IGNORE INTO T_Major_Subject (major_id,subject_id,grade) VALUES (10001,20013,'" + g + "')");
                db.execSQL("INSERT OR IGNORE INTO T_Major_Subject (major_id,subject_id,grade) VALUES (10044,20013,'" + g + "')");
            }
        }
        if (oldVersion < 10) {
            // 补种课程与章节（老安装无需清数据即可获得新课程/章节）
            db.execSQL("INSERT OR IGNORE INTO T_Subject (id,name,code,description) VALUES (20001,'人工智能','AI','机器学习、深度学习、强化学习')");
            db.execSQL("INSERT OR IGNORE INTO T_Subject (id,name,code,description) VALUES (20002,'数据结构与算法','DSA','线性表、树、图、排序与查找')");
            db.execSQL("INSERT OR IGNORE INTO T_Subject (id,name,code,description) VALUES (20003,'操作系统','OS','进程与线程、调度、内存管理、文件系统')");
            db.execSQL("INSERT OR IGNORE INTO T_Subject (id,name,code,description) VALUES (20004,'计算机网络','NET','TCP/IP、路由、交换、应用层协议')");
            db.execSQL("INSERT OR IGNORE INTO T_Subject (id,name,code,description) VALUES (20005,'数据库系统','DB','SQL、事务、索引、范式与设计')");
            db.execSQL("INSERT OR IGNORE INTO T_Subject (id,name,code,description) VALUES (20013,'高等数学','ADVMath','微积分、线性代数、概率统计')");

            // 补种关键章节与关键词/别名
            db.execSQL("INSERT OR IGNORE INTO T_Chapter (id,subject_id,name,order_num,keywords,aliases) VALUES (30001,20001,'机器学习基础',1,'监督学习,非监督学习,回归,分类,交叉验证','machine learning,ml,supervised,unsupervised')");
            db.execSQL("INSERT OR IGNORE INTO T_Chapter (id,subject_id,name,order_num,keywords,aliases) VALUES (30002,20001,'深度学习',2,'神经网络,卷积,反向传播,激活函数,梯度下降','deep learning,cnn,rnn,bp,activation')");
            db.execSQL("INSERT OR IGNORE INTO T_Chapter (id,subject_id,name,order_num,keywords,aliases) VALUES (30003,20001,'强化学习',3,'马尔可夫决策,策略,价值函数,探索利用','reinforcement learning,mdp,policy,value,exploration')");
            db.execSQL("INSERT OR IGNORE INTO T_Chapter (id,subject_id,name,order_num,keywords,aliases) VALUES (30011,20002,'线性结构',1,'数组,链表,栈,队列','array,list,stack,queue')");
            db.execSQL("INSERT OR IGNORE INTO T_Chapter (id,subject_id,name,order_num,keywords,aliases) VALUES (30012,20002,'树与图',2,'二叉树,平衡树,堆,图遍历,最短路径','tree,graph,dfs,bfs,heap,dijkstra')");
            db.execSQL("INSERT OR IGNORE INTO T_Chapter (id,subject_id,name,order_num,keywords,aliases) VALUES (30013,20002,'排序与查找',3,'快速排序,归并排序,哈希,二分查找','quicksort,mergesort,hash,binary search')");
            db.execSQL("INSERT OR IGNORE INTO T_Chapter (id,subject_id,name,order_num,keywords,aliases) VALUES (30021,20003,'进程与线程',1,'调度,同步,死锁,临界区','process,thread,schedule,lock')");
            db.execSQL("INSERT OR IGNORE INTO T_Chapter (id,subject_id,name,order_num,keywords,aliases) VALUES (30022,20003,'内存管理',2,'分页,分段,虚拟内存,置换算法','memory,virtual,paging,mmu')");
            db.execSQL("INSERT OR IGNORE INTO T_Chapter (id,subject_id,name,order_num,keywords,aliases) VALUES (30023,20003,'文件系统',3,'索引,日志,磁盘,目录结构','filesystem,inode,journal,disk')");
            db.execSQL("INSERT OR IGNORE INTO T_Chapter (id,subject_id,name,order_num,keywords,aliases) VALUES (30031,20004,'网络层',1,'路由,IP,子网,拥塞','ip,routing,subnet,congestion')");
            db.execSQL("INSERT OR IGNORE INTO T_Chapter (id,subject_id,name,order_num,keywords,aliases) VALUES (30032,20004,'传输层',2,'TCP,UDP,流控,拥塞控制','tcp,udp,flow')");
            db.execSQL("INSERT OR IGNORE INTO T_Chapter (id,subject_id,name,order_num,keywords,aliases) VALUES (30033,20004,'应用层',3,'HTTP,DNS,SMTP,FTP','http,dns,email,ftp')");
            db.execSQL("INSERT OR IGNORE INTO T_Chapter (id,subject_id,name,order_num,keywords,aliases) VALUES (30041,20005,'关系模型与SQL',1,'范式,主键,外键,查询,连接','sql,normal form,join')");
            db.execSQL("INSERT OR IGNORE INTO T_Chapter (id,subject_id,name,order_num,keywords,aliases) VALUES (30042,20005,'事务与并发',2,'ACID,锁,隔离级别,日志','transaction,acid,lock,isolation')");
            db.execSQL("INSERT OR IGNORE INTO T_Chapter (id,subject_id,name,order_num,keywords,aliases) VALUES (30043,20005,'索引与优化',3,'B树,哈希索引,执行计划,优化器','index,b-tree,optimizer')");
            db.execSQL("INSERT OR IGNORE INTO T_Chapter (id,subject_id,name,order_num,keywords,aliases) VALUES (30121,20013,'微积分',1,'极限,导数,积分,泰勒展开','calculus,derivative,integral,taylor')");
            db.execSQL("INSERT OR IGNORE INTO T_Chapter (id,subject_id,name,order_num,keywords,aliases) VALUES (30122,20013,'线性代数',2,'矩阵,向量,特征值,特征向量,秩','linear algebra,matrix,eigenvalue,eigenvector,rank')");
            db.execSQL("INSERT OR IGNORE INTO T_Chapter (id,subject_id,name,order_num,keywords,aliases) VALUES (30123,20013,'概率论与数理统计',3,'概率分布,方差,期望,假设检验,样本','probability,distribution,variance,expectation,hypothesis test')");

            // 计算机学院两专业课程映射（含公共数学）
            String[] grades2 = new String[]{"大一","大二","大三","大四"};
            for (String g : grades2) {
                db.execSQL("INSERT OR IGNORE INTO T_Major_Subject (major_id,subject_id,grade) VALUES (10001,20002,'" + g + "')");
                db.execSQL("INSERT OR IGNORE INTO T_Major_Subject (major_id,subject_id,grade) VALUES (10001,20003,'" + g + "')");
                db.execSQL("INSERT OR IGNORE INTO T_Major_Subject (major_id,subject_id,grade) VALUES (10001,20004,'" + g + "')");
                db.execSQL("INSERT OR IGNORE INTO T_Major_Subject (major_id,subject_id,grade) VALUES (10001,20005,'" + g + "')");
                db.execSQL("INSERT OR IGNORE INTO T_Major_Subject (major_id,subject_id,grade) VALUES (10044,20001,'" + g + "')");
                db.execSQL("INSERT OR IGNORE INTO T_Major_Subject (major_id,subject_id,grade) VALUES (10044,20002,'" + g + "')");
                db.execSQL("INSERT OR IGNORE INTO T_Major_Subject (major_id,subject_id,grade) VALUES (10044,20003,'" + g + "')");
                db.execSQL("INSERT OR IGNORE INTO T_Major_Subject (major_id,subject_id,grade) VALUES (10044,20004,'" + g + "')");
                db.execSQL("INSERT OR IGNORE INTO T_Major_Subject (major_id,subject_id,grade) VALUES (10044,20005,'" + g + "')");
                db.execSQL("INSERT OR IGNORE INTO T_Major_Subject (major_id,subject_id,grade) VALUES (10001,20013,'" + g + "')");
                db.execSQL("INSERT OR IGNORE INTO T_Major_Subject (major_id,subject_id,grade) VALUES (10044,20013,'" + g + "')");
            }
        }
        } catch (Exception e) {
            db.execSQL("DROP TABLE IF EXISTS T_Comment");
            db.execSQL("DROP TABLE IF EXISTS T_Like");
            db.execSQL("DROP TABLE IF EXISTS T_Note");
            db.execSQL("DROP TABLE IF EXISTS T_Chapter");
            db.execSQL("DROP TABLE IF EXISTS T_Major_Subject");
            db.execSQL("DROP TABLE IF EXISTS T_Subject");
            db.execSQL("DROP TABLE IF EXISTS T_Major");
            db.execSQL("DROP TABLE IF EXISTS T_User");
            onCreate(db);
        }
    }
}