package com.icecondor.eaglet.db;

import java.util.Date;

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

import com.icecondor.eaglet.Constants;

public class Database {
    private final String DATABASE_NAME = "icecondor";
    private final OpenHelper dbHelper;
    public static final int DATABASE_VERSION = 1;
    public static final String ROW_CREATED_AT = "created_at";

    /* Users table */
    public static final String USERS_TABLE = "users";
    public static final String USERS_USERNAME = "username";
    public static final String USERS_SESSION_KEY = "session_key";

    /* Activities table */
    public static final String ACTIVITIES_TABLE = "activities";
    public static final String ACTIVITIES_UUID = "uuid";
    public static final String ACTIVITIES_VERB = "verb";
    public static final String ACTIVITIES_DESCRIPTION = "description";
    public static final String ACTIVITIES_JSON = "json";
    public static final String ACTIVITIES_SYNCED_AT = "synced_at";

    private SQLiteDatabase db;

    public Database(Context context) {
        dbHelper = new OpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    public Database open() throws SQLException {
        db = dbHelper.getWritableDatabase();
        return this;
    }

    public SQLiteDatabase getReadonly() {
        return dbHelper.getReadableDatabase();
    }

    public void close() {
        db.close();
    }

    private static class OpenHelper extends SQLiteOpenHelper {

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

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL("CREATE TABLE "+USERS_TABLE+" ("+
                    "_id integer primary key, "+
                    USERS_USERNAME + " text," +
                    USERS_SESSION_KEY + " text," +
                    ROW_CREATED_AT + " DATETIME DEFAULT CURRENT_TIMESTAMP" +
                    ")");

            db.execSQL("CREATE TABLE "+ACTIVITIES_TABLE+" ("+
                    "_id integer primary key, "+
                    ACTIVITIES_UUID + " text," +
                    ACTIVITIES_VERB + " text," +
                    ACTIVITIES_DESCRIPTION + " text," +
                    ACTIVITIES_JSON + " text," +
                    ACTIVITIES_SYNCED_AT + " text," +
                    ROW_CREATED_AT + " DATETIME DEFAULT (strftime('%Y-%m-%dT%H:%M:%fZ','now'))" +
                    ")");

        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        }
    }

    public void append(Sqlitable obj) {
        ContentValues cv = obj.getAttributes();
        Date now = new Date();
        Log.d(Constants.APP_TAG, ""+now+" Database append("+obj.getClass().getSimpleName()+") "+cv);
        db.insert(obj.getTableName(), null, cv);
    }

}

@@ -121,16 +121,48 @@
 ontext;%0A
+import android.database.Cursor;%0A
 import a
@@ -191,16 +191,16 @@
 eption;%0A
-
 import a
@@ -581,16 +581,63 @@
 ON = 1;%0A
+    public static final String ROW_ID = %22_id%22;%0A
     publ
@@ -2101,36 +2101,40 @@
                 
-%22_id
+ROW_ID+%22
  integer primary
@@ -2427,20 +2427,24 @@
         
-%22_id
+ROW_ID+%22
  integer
@@ -3007,24 +3007,132 @@
 able obj) %7B%0A
+        if(rowCount(obj.getTableName()) %3E 1000) %7B%0A            trimTable(obj.getTableName(), 900);%0A        %7D%0A
         Cont
@@ -3295,24 +3295,24 @@
 )+%22) %22+cv);%0A
-
         db.i
@@ -3355,10 +3355,562 @@
 %0A    %7D%0A%0A
+    private void trimTable(String tableName, int i) %7B%0A        Cursor cursor = db.query(tableName, new String%5B%5D %7BROW_ID%7D,%0A                                 null, null, null, null, ROW_ID+%22 asc%22, %22%22+i);%0A        cursor.moveToFirst();%0A        long firstRowId = cursor.getLong(cursor.getColumnIndex(ROW_ID));%0A        db.delete(tableName, ROW_ID+%22 %3C ?%22, new String%5B%5D %7B%22%22+firstRowId%7D);%0A    %7D%0A%0A    private int rowCount(String tableName) %7B%0A        Cursor cursor = db.query(tableName, null, null, null, null, null, null);%0A        return cursor.getCount();%0A    %7D%0A%0A
 %7D%0A

trim the activity table