package edu.psu.rcy5017.publicspeakingassistant;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

/**
 * A helper class that is responsible for creating and updating the app database, 
 * and for creating all the tables in the database.
 * @author ryosua
 *
 */
public final class DatabaseHelper extends SQLiteOpenHelper {
    
    private static final String TAG = "DatabaseHelper";
    
    public static final int DATABASE_VERSION = 2;
    public static final String DATABASE_NAME = "PublicSpeakingAssistantDatabase";
    public static final String COLUMN_ID = "_id";

    // Speech Table
    public static final String SPEECH_TABLE_NAME = "Speech";
    public static final String SPEECH_TITLE = "Title";
    
    // NoteCard Table
    public static final String NOTECARD_TABLE_NAME = "Notecard";
    public static final String NOTECARD_TITLE = "Title";
    public static final String SPEECH_ID = "SpeechID";
    
    // Note Table
    public static final String NOTE_TABLE_NAME = "Note";
    public static final String NOTE_TEXT = "Text";
    public static final String NOTECARD_ID = "NoteCardID";
    
    // SpeechRecording Table
    public static final String SPEECH_RECORDING_TABLE_NAME = "SpeechRecording";
    public static final String SPEECH_RECORDING_TITLE = "Title";
    public static final String SPEECH_RECORDING_FILE = "File";
    
    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }
    
    @Override
    public void onCreate(SQLiteDatabase db) {
        createSpeechTable(db);
        createNoteCardTable(db);
        createNoteTable(db);
        //createSpeechRecording(db);
        //createBulletPointTable(db);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Do nothing.
    }
    
    @Override
    public void onConfigure(SQLiteDatabase db) {
        super.onConfigure(db);
        db.setForeignKeyConstraintsEnabled(true);
    }
    
    private void createSpeechTable(SQLiteDatabase db) {
        final String createStatement =
                    "CREATE TABLE " + SPEECH_TABLE_NAME + " (" +
                            COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                            SPEECH_TITLE + " TEXT NOT NULL" +
                    ");";
        
        db.execSQL(createStatement);
    }
    
    private void createNoteCardTable(SQLiteDatabase db) {
        final String createStatement =
                "CREATE TABLE " + NOTECARD_TABLE_NAME + " (" +
                        COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                        NOTECARD_TITLE + " TEXT NOT NULL," +
                        SPEECH_ID + " INTEGER NOT NULL," +
                        "FOREIGN KEY("+ SPEECH_ID +") REFERENCES " + SPEECH_TABLE_NAME + "("+ COLUMN_ID +") ON DELETE CASCADE" + 
                ");";
            
        db.execSQL(createStatement);
    }
    
    private void createNoteTable(SQLiteDatabase db) {
        final String createStatement =
                "CREATE TABLE " + NOTE_TABLE_NAME + " (" +
                        COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                        NOTE_TEXT + " TEXT NOT NULL," +
                        NOTECARD_ID + " INTEGER NOT NULL," +
                        "FOREIGN KEY("+ NOTECARD_ID +") REFERENCES " + NOTECARD_TABLE_NAME + "("+ COLUMN_ID +") ON DELETE CASCADE" + 
                ");";
            
        db.execSQL(createStatement);
    }
    
}
@@ -1723,26 +1723,24 @@
 b);%0A        
-//
 createSpeech
@@ -1758,46 +1758,8 @@
 b);%0A
-        //createBulletPointTable(db);%0A
     
@@ -3562,9 +3562,666 @@
  %7D%0A    %0A
+    private void createSpeechRecording(SQLiteDatabase db) %7B%0A        final String createStatement =%0A                %22CREATE TABLE %22 + SPEECH_RECORDING_TABLE_NAME + %22 (%22 +%0A                        COLUMN_ID + %22 INTEGER PRIMARY KEY AUTOINCREMENT, %22 +%0A                        SPEECH_RECORDING_TITLE + %22 TEXT NOT NULL,%22 +%0A                        SPEECH_RECORDING_FILE + %22 TEXT NOT NULL,%22 +%0A                        SPEECH_ID + %22 INTEGER NOT NULL,%22 +%0A                        %22FOREIGN KEY(%22+ SPEECH_ID +%22) REFERENCES %22 + SPEECH_TABLE_NAME + %22(%22+ COLUMN_ID +%22) ON DELETE CASCADE%22 +  %0A                %22);%22;%0A            %0A        db.execSQL(createStatement);%0A    %7D%0A    %0A
 %7D

Create speech recording table.