package com.example.myapplication.data;

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

public class NotesDatabaseHelper extends SQLiteOpenHelper {

    private static final String DB_NAME = "note.db";
    private static final int DB_VERSION = 1;

    // 定义表名常量（或者直接使用 NoteColumns.TABLE_NAME）
    private static final String TABLE_NOTE = NoteColumns.TABLE_NAME;
    private static final String TABLE_DATA = NoteColumns.TABLE_DATA;

    // 创建 notes 表
    private static final String CREATE_NOTE_TABLE_SQL =
            "CREATE TABLE " + TABLE_NOTE + " (" +
                    NoteColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    NoteColumns.COLUMN_PARENT_ID + " INTEGER NOT NULL DEFAULT 0, " +
                    NoteColumns.COLUMN_ALERTED_DATE + " INTEGER NOT NULL DEFAULT 0, " +
                    NoteColumns.COLUMN_BG_COLOR_ID + " INTEGER NOT NULL DEFAULT 0, " +
                    NoteColumns.COLUMN_CREATED_DATE + " INTEGER NOT NULL DEFAULT (strftime('%s', 'now')), " +
                    NoteColumns.COLUMN_HAS_ATTACHMENT + " INTEGER NOT NULL DEFAULT 0, " +
                    NoteColumns.COLUMN_MODIFIED_DATE + " INTEGER NOT NULL DEFAULT (strftime('%s', 'now')), " +
                    NoteColumns.COLUMN_NOTES_COUNT + " INTEGER NOT NULL DEFAULT 0, " +
                    NoteColumns.COLUMN_SNIPPET + " TEXT NOT NULL DEFAULT '', " +
                    NoteColumns.COLUMN_TYPE + " INTEGER NOT NULL DEFAULT 0, " +
                    NoteColumns.COLUMN_WIDGET_ID + " INTEGER NOT NULL DEFAULT -1, " +
                    NoteColumns.COLUMN_WIDGET_TYPE + " INTEGER NOT NULL DEFAULT -1, " +
                    NoteColumns.COLUMN_SYNC_ID + " INTEGER NOT NULL DEFAULT 0, " +
                    NoteColumns.COLUMN_LOCAL_MODIFIED + " INTEGER NOT NULL DEFAULT 0, " +
                    NoteColumns.COLUMN_ORIGIN_PARENT_ID + " INTEGER NOT NULL DEFAULT 0, " +
                    NoteColumns.COLUMN_GTASK_ID + " TEXT NOT NULL DEFAULT '', " +
                    NoteColumns.COLUMN_VERSION + " INTEGER NOT NULL DEFAULT 0" +
                    ")";

    // 创建 data 表
    private static final String CREATE_DATA_TABLE_SQL =
            "CREATE TABLE " + TABLE_DATA + " (" +
                    NoteColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    DataColumns.MIME_TYPE + " TEXT NOT NULL, " +
                    DataColumns.NOTE_ID + " INTEGER NOT NULL DEFAULT 0, " +
                    NoteColumns.COLUMN_CREATED_DATE + " INTEGER NOT NULL DEFAULT (strftime('%s', 'now')), " +
                    NoteColumns.COLUMN_MODIFIED_DATE + " INTEGER NOT NULL DEFAULT (strftime('%s', 'now')), " +
                    DataColumns.CONTENT + " TEXT NOT NULL DEFAULT '', " +
                    DataColumns.DATA1 + " INTEGER, " +
                    DataColumns.DATA2 + " INTEGER, " +
                    DataColumns.DATA3 + " TEXT NOT NULL DEFAULT '', " +
                    DataColumns.DATA4 + " TEXT NOT NULL DEFAULT '', " +
                    DataColumns.DATA5 + " TEXT NOT NULL DEFAULT ''" +
                    ")";

    // 创建索引
    private static final String CREATE_DATA_NOTE_ID_INDEX_SQL =
            "CREATE INDEX IF NOT EXISTS note_id_index ON " +
                    TABLE_DATA + "(" + DataColumns.NOTE_ID + ")";

    // 触发器1：插入笔记时增加文件夹计数
    private static final String NOTE_INCREASE_FOLDER_COUNT_ON_INSERT_TRIGGER =
            "CREATE TRIGGER increase_folder_count_on_insert " +
                    "AFTER INSERT ON " + TABLE_NOTE + " " +
                    "BEGIN " +
                    "    UPDATE " + TABLE_NOTE + " " +
                    "    SET " + NoteColumns.COLUMN_NOTES_COUNT + " = " + NoteColumns.COLUMN_NOTES_COUNT + " + 1 " +
                    "    WHERE " + NoteColumns._ID + " = new." + NoteColumns.COLUMN_PARENT_ID + "; " +
                    "END";

    // 触发器2：删除笔记时减少文件夹计数
    private static final String NOTE_DECREASE_FOLDER_COUNT_ON_DELETE_TRIGGER =
            "CREATE TRIGGER decrease_folder_count_on_delete " +
                    "AFTER DELETE ON " + TABLE_NOTE + " " +
                    "BEGIN " +
                    "    UPDATE " + TABLE_NOTE + " " +
                    "    SET " + NoteColumns.COLUMN_NOTES_COUNT + " = " + NoteColumns.COLUMN_NOTES_COUNT + " - 1 " +
                    "    WHERE " + NoteColumns._ID + " = old." + NoteColumns.COLUMN_PARENT_ID + " " +
                    "    AND " + NoteColumns.COLUMN_NOTES_COUNT + " > 0; " +
                    "END";

    // 触发器3：插入 data 时更新笔记内容
    private static final String DATA_UPDATE_NOTE_CONTENT_ON_INSERT_TRIGGER =
            "CREATE TRIGGER update_note_content_on_insert " +
                    "AFTER INSERT ON " + TABLE_DATA + " " +
                    "WHEN new." + DataColumns.MIME_TYPE + " = '" + DataConstants.NOTE + "' " +
                    "BEGIN " +
                    "    UPDATE " + TABLE_NOTE + " " +
                    "    SET " + NoteColumns.COLUMN_SNIPPET + " = new." + DataColumns.CONTENT + " " +
                    "    WHERE " + NoteColumns._ID + " = new." + DataColumns.NOTE_ID + "; " +
                    "END";

    // 触发器4：更新 data 时更新笔记内容
    private static final String DATA_UPDATE_NOTE_CONTENT_ON_UPDATE_TRIGGER =
            "CREATE TRIGGER update_note_content_on_update " +
                    "AFTER UPDATE ON " + TABLE_DATA + " " +
                    "WHEN old." + DataColumns.MIME_TYPE + " = '" + DataConstants.NOTE + "' " +
                    "BEGIN " +
                    "    UPDATE " + TABLE_NOTE + " " +
                    "    SET " + NoteColumns.COLUMN_SNIPPET + " = new." + DataColumns.CONTENT + " " +
                    "    WHERE " + NoteColumns._ID + " = new." + DataColumns.NOTE_ID + "; " +
                    "END";

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

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_NOTE_TABLE_SQL);
        db.execSQL(CREATE_DATA_TABLE_SQL);
        db.execSQL(CREATE_DATA_NOTE_ID_INDEX_SQL);

        // 创建触发器
        db.execSQL(NOTE_INCREASE_FOLDER_COUNT_ON_INSERT_TRIGGER);
        db.execSQL(NOTE_DECREASE_FOLDER_COUNT_ON_DELETE_TRIGGER);
        db.execSQL(DATA_UPDATE_NOTE_CONTENT_ON_INSERT_TRIGGER);
        db.execSQL(DATA_UPDATE_NOTE_CONTENT_ON_UPDATE_TRIGGER);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // 删除所有触发器
        db.execSQL("DROP TRIGGER IF EXISTS increase_folder_count_on_insert");
        db.execSQL("DROP TRIGGER IF EXISTS decrease_folder_count_on_delete");
        db.execSQL("DROP TRIGGER IF EXISTS update_note_content_on_insert");
        db.execSQL("DROP TRIGGER IF EXISTS update_note_content_on_update");

        // 删除表
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NOTE);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_DATA);

        // 重新创建
        onCreate(db);
    }
}