package net.micode.myapplication.database;

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

public class DatabaseHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "MyNote.db";
    private static final int DATABASE_VERSION = 1;

    // 表名
    public static final String TABLE_COURSES = "courses";
    public static final String TABLE_CHAPTERS = "chapters";
    public static final String TABLE_NOTES = "notes";

    // Courses表列名
    public static final String COLUMN_COURSE_ID = "course_id";
    public static final String COLUMN_COURSE_NAME = "course_name";
    public static final String COLUMN_SCHOOL = "school";

    // Chapters表列名
    public static final String COLUMN_CHAPTER_ID = "chapter_id";
    public static final String COLUMN_CHAPTER_NUMBER = "chapter_number";
    public static final String COLUMN_CHAPTER_TITLE = "chapter_title";
    public static final String COLUMN_KEYWORDS = "keywords";
    public static final String COLUMN_ALIASES = "aliases";

    // Notes表列名
    public static final String COLUMN_NOTE_ID = "note_id";
    public static final String COLUMN_TITLE = "title";
    public static final String COLUMN_DESCRIPTION = "description";
    public static final String COLUMN_OCR_TEXT = "ocr_text";
    public static final String COLUMN_FILE_PATH = "file_path";
    public static final String COLUMN_UPLOAD_TIME = "upload_time";
    public static final String COLUMN_UPDATE_TIME = "update_time";

    // 创建Courses表的SQL语句
    private static final String CREATE_TABLE_COURSES = "CREATE TABLE " + TABLE_COURSES + " (" +
            COLUMN_COURSE_ID + " TEXT PRIMARY KEY, " +
            COLUMN_COURSE_NAME + " TEXT NOT NULL, " +
            COLUMN_SCHOOL + " TEXT" +
            ");";

    // 创建Chapters表的SQL语句
    private static final String CREATE_TABLE_CHAPTERS = "CREATE TABLE " + TABLE_CHAPTERS + " (" +
            COLUMN_CHAPTER_ID + " TEXT PRIMARY KEY, " +
            COLUMN_COURSE_ID + " TEXT NOT NULL, " +
            COLUMN_CHAPTER_NUMBER + " INTEGER NOT NULL, " +
            COLUMN_CHAPTER_TITLE + " TEXT NOT NULL, " +
            COLUMN_KEYWORDS + " TEXT, " +
            COLUMN_ALIASES + " TEXT, " +
            "FOREIGN KEY(" + COLUMN_COURSE_ID + ") REFERENCES " + TABLE_COURSES + "(" + COLUMN_COURSE_ID + ")" +
            ");";

    // 创建Notes表的SQL语句
    private static final String CREATE_TABLE_NOTES = "CREATE TABLE " + TABLE_NOTES + " (" +
            COLUMN_NOTE_ID + " TEXT PRIMARY KEY, " +
            COLUMN_TITLE + " TEXT NOT NULL, " +
            COLUMN_DESCRIPTION + " TEXT, " +
            COLUMN_OCR_TEXT + " TEXT, " +
            COLUMN_COURSE_ID + " TEXT, " +
            COLUMN_CHAPTER_ID + " TEXT, " +
            COLUMN_FILE_PATH + " TEXT, " +
            COLUMN_UPLOAD_TIME + " INTEGER, " +
            COLUMN_UPDATE_TIME + " INTEGER, " +
            "FOREIGN KEY(" + COLUMN_COURSE_ID + ") REFERENCES " + TABLE_COURSES + "(" + COLUMN_COURSE_ID + "), " +
            "FOREIGN KEY(" + COLUMN_CHAPTER_ID + ") REFERENCES " + TABLE_CHAPTERS + "(" + COLUMN_CHAPTER_ID + ")" +
            ");";

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_TABLE_COURSES);
        db.execSQL(CREATE_TABLE_CHAPTERS);
        db.execSQL(CREATE_TABLE_NOTES);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NOTES);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_CHAPTERS);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_COURSES);
        onCreate(db);
    }

    // 清除所有数据的方法
    public void clearAllData(SQLiteDatabase db) {
        db.execSQL("DELETE FROM " + TABLE_NOTES);
        db.execSQL("DELETE FROM " + TABLE_CHAPTERS);
        db.execSQL("DELETE FROM " + TABLE_COURSES);
    }
}