package com.example.sleep.database;


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

public class MyDataBaseHelper extends SQLiteOpenHelper//用于创建和更新数据库
{
    private static final String DB_NAME = "DB2.db";
    private static final String TABLE_NAME = "grade_table";
    private static final int DB_VERSION = 1;


    // 用户状态表
    public static final String TABLE_STATUS = "statusTable";
    public static final String STATUS_ID = "status_id";
    public static final String USER_NAME = "user_name";
    public static final String CONTENT = "content";
    public static final String IMAGE_URL = "image_url";
    public static final String TIMESTAMP = "timestamp";

    // 评论表
    public static final String TABLE_COMMENTS = "commentsTable";
    public static final String COMMENT_ID = "comment_id";
    public static final String COMMENTER = "commenter";
    public static final String COMMENT = "comment";

    public MyDataBaseHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    public MyDataBaseHelper(Context context) {
        this(context, DB_NAME, null, DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        try {
            //创建成绩表
            String CREATE_TABLE = "CREATE TABLE if not exists " + TABLE_NAME
                    + "(_id INTEGER PRIMARY KEY autoincrement,time TEXT," +
                    "sumOfSleep INTEGER," +
                    "timeOfSleep DOUBLE,grade DOUBLE)";
            db.execSQL(CREATE_TABLE);
            //创建用户状态表
            String createStatusTable = "CREATE TABLE " + TABLE_STATUS + "(" +
                    STATUS_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
                    USER_NAME + " TEXT," +
                    CONTENT + " TEXT," +
                    IMAGE_URL + " TEXT," +
                    TIMESTAMP + " DATETIME DEFAULT CURRENT_TIMESTAMP)";
            //创建评论表
            String createCommentsTable = "CREATE TABLE " + TABLE_COMMENTS + "(" +
                    COMMENT_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
                    STATUS_ID + " INTEGER," +
                    COMMENTER + " TEXT," +
                    COMMENT + " TEXT," +
                    TIMESTAMP + " DATETIME DEFAULT CURRENT_TIMESTAMP," +
                    "FOREIGN KEY(" + STATUS_ID + ") REFERENCES " + TABLE_STATUS + "(" + STATUS_ID + "))";

            db.execSQL(createStatusTable);//执行创建用户状态表的SQL语句
            db.execSQL(createCommentsTable);//执行创建评论表的SQL语句


        } catch (Exception e) {
            Log.i("e", e.toString());//若出现异常，记录错误日志
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        System.out.println("database update!");//输出日志，表示数据库正在更新
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_STATUS);//删除用户状态表
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_COMMENTS);//删除评论表
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);//删除成绩表
        onCreate(db);
    }
}