package com.example.checkregister;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.widget.Toast;

public class CheckRegisterDBAdapter {
  public static final int REGISTER_DB_VERSION = 3;
  public static final String REGISTER_DB_NAME = "register.db";
  public static final String REGISTER_TAB_NAME = "register";
  public static final String ACCOUNT_TAB_NAME = "accounts";
  public static final String KEY_ID = "_id";
  public static final String KEY_CHECKNUM = "check_number";
  public static final String KEY_DATE = "date";
  public static final String KEY_PAYEE = "payee";
  public static final String KEY_AMOUNT = "amount";
  public static final String KEY_BALANCE = "balance";
  public static final String KEY_MEMO = "memo";
  public static final String KEY_ACCOUNT_ID = "account_id";
  public static final String KEY_ACCOUNT_NAME = "account_name";
  public static final String KEY_ACCOUNT_DESC = "account_description";
  public static final String KEY_ACCOUNT_ISDEFAULT = "isdefault";
  private final Context context;
  private SQLiteDatabase db;
  private CheckRegisterDBOpenHelper dbHelper;

  public CheckRegisterDBAdapter(Context _context) {
    this.context = _context;
    dbHelper = new CheckRegisterDBOpenHelper(context, REGISTER_DB_NAME, null,
        REGISTER_DB_VERSION);
    open();
  }

  @Override
  protected void finalize() throws Throwable {
    super.finalize();
    close();
  }

  public void open() throws SQLiteException {
    try {
      db = dbHelper.getWritableDatabase();
    }
    catch (SQLiteException ex) {
      db = dbHelper.getReadableDatabase();
    }
  }

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

  public float currentBalanceFromDb() {
    float balance = 0f;
    Cursor c = db.rawQuery("SELECT " + KEY_BALANCE + " FROM "
        + REGISTER_TAB_NAME + " WHERE " + KEY_ID + " = (SELECT MAX(" + KEY_ID
        + ") FROM " + REGISTER_TAB_NAME + ")", null);
    if (c.moveToFirst()) {
      balance = c.getFloat(c.getColumnIndex(KEY_BALANCE));
    }
    c.close();
    return balance;
  }

  public Cursor getCheckList(String[] projection, String selection,
      String[] selectionArgs, String sortKey) {
    String orderBy = sortKey;
    if (orderBy == null) {
      orderBy = KEY_DATE + " DESC";
    }
    return db.query(REGISTER_TAB_NAME, projection, selection, selectionArgs,
        null, null, orderBy);
  }

  public Cursor getCheckList(String sortKey) {
    String orderBy = sortKey;
    if (orderBy == null) {
      orderBy = KEY_DATE + " DESC";
    }
    return getCheckList(null, null, null, orderBy);
  }

  public Cursor getCheckList() {
    return getCheckList(null);
  }

  public int getNextHigherCheckNum() {
    long checkNum = 0;
    checkNum = db.compileStatement(
        "SELECT MAX(" + KEY_CHECKNUM + ") + 1 FROM " + REGISTER_TAB_NAME)
        .simpleQueryForLong();
    return (int) checkNum;
  }

  public Cursor getAmountByMonthList(String selectedAccount) {
    Cursor c;
    String acctExpr;
    String acctStr;
    String[] selectionArgs = null;
    if (selectedAccount == null || selectedAccount.length() == 0) {
      acctExpr = KEY_ACCOUNT_NAME + " IS NULL";
      acctStr = acctExpr;
    }
    else {
      acctExpr = KEY_ACCOUNT_NAME + " = ?";
      acctStr = KEY_ACCOUNT_NAME + " = '" + selectedAccount + "'";
      selectionArgs = new String[] { selectedAccount };
    }
    Toast.makeText(context, acctExpr, Toast.LENGTH_LONG).show();

    // Using rawQuery():
    String myrawquery =
       "SELECT"
     +       " abs(random()) AS _id,"
     +       " substr(date,1,7) AS month,"
     +       " count(amount) AS checks_written,"
     +       " sum(amount) AS month_amount"
     +  " FROM register"
     + " WHERE amount > 0"
     +   " AND " + acctStr
     + " GROUP BY substr(date,1,7)"
     +" HAVING sum(amount) > 0"
     + " ORDER BY substr(date,1,7) DESC";
    // c = db.rawQuery(myrawquery, null);
    

    // Using query():
    c = db.query(
    /*table*/     REGISTER_TAB_NAME,
    /*columns*/   new String[]{"abs(random()) AS _id",
                               "count("+KEY_AMOUNT+") AS checks_written",
                  "substr("+KEY_DATE+",1,7) AS month",
                       "sum("+KEY_AMOUNT+") AS month_amount" },
    /*selection*/ KEY_AMOUNT + "> 0"
                  + " AND " + acctExpr,
                  selectionArgs, /* selectionArgs */
    /*groupBy*/   "substr(" + KEY_DATE + ",1,7)",
    /*having*/    "sum(" + KEY_AMOUNT + ") > 0",
    /*orderBy*/   "substr(" + KEY_DATE + ",1,7) DESC");
    return c;
  }

  public String[] getPayeeList() {
    ArrayList<String> payee = new ArrayList<String>();
    Cursor c = db.rawQuery("SELECT DISTINCT " + KEY_PAYEE + " FROM "
        + REGISTER_TAB_NAME, null);
    if (c.moveToFirst()) {
      int colIndex = c.getColumnIndex(KEY_PAYEE);
      while (!c.isAfterLast()) {
        payee.add(c.getString(colIndex));
        c.moveToNext();
      }
    }
    c.close();
    String[] payeeArray = new String[payee.size()];
    payeeArray = payee.toArray(payeeArray);
    return payeeArray;
  }

  public Cursor getCheckRecord(long id) {
    return db.query(REGISTER_TAB_NAME, null, KEY_ID + " = ?", new String[] { ""
        + id }, null, null, null);
  }

  public long addCheck(int checkNum, String date, String payee, float amount,
      String memo, String account) {
    float balance = currentBalanceFromDb();
    ContentValues newdata = new ContentValues();
    newdata.put(KEY_CHECKNUM, checkNum);
    newdata.put(KEY_DATE, date);
    newdata.put(KEY_PAYEE, payee);
    newdata.put(KEY_AMOUNT, amount);
    newdata.put(KEY_MEMO, memo);
    newdata.put(KEY_ACCOUNT_ID, account);
    newdata.put(KEY_BALANCE, balance - amount);
    return addCheck(newdata);
  }

  public long addCheck(ContentValues newdata) {
    return db.insert(REGISTER_TAB_NAME, null, newdata);
  }

  public long updateCheck(ContentValues values, String selection,
      String[] selectionArgs) {
    return db.update(REGISTER_TAB_NAME, values, selection, selectionArgs);
  }

  public long updateCheck(long _rowid, ContentValues values) {
    return updateCheck(values, KEY_ID + "= " + _rowid, null);
  }

  public long updateCheck(long _rowid, int checkNum, String date, String payee,
      float amount, String memo, String account) {
    ContentValues newdata = new ContentValues();
    if (checkNum > 0) newdata.put(KEY_CHECKNUM, checkNum);
    if (date != null) newdata.put(KEY_DATE, date);
    if (payee != null) newdata.put(KEY_PAYEE, payee);
    if (amount > 0) newdata.put(KEY_AMOUNT, amount);
    if (memo != null) newdata.put(KEY_MEMO, memo);
    if (account != null) newdata.put(KEY_ACCOUNT_ID, account);
    return updateCheck(_rowid, newdata);
  }

  public long deleteCheck(String selection, String[] selectionArgs) {
    return db.delete(REGISTER_TAB_NAME, selection, selectionArgs);
  }

  public long deleteCheck(long _rowid) {
    return deleteCheck(KEY_ID + "= " + _rowid, null);
  }

  private static class CheckRegisterDBOpenHelper extends SQLiteOpenHelper {
    public static final String CREATE_ACCOUNT_TAB = "CREATE TABLE "
        + ACCOUNT_TAB_NAME + " ( "
        + KEY_ID + " INTEGER PRIMARY KEY, "
        + KEY_ACCOUNT_NAME + " TEXT, "
        + KEY_ACCOUNT_DESC + "TEXT, "
        + KEY_ACCOUNT_ISDEFAULT + " BOOLEAN );"
        ;

    public static final String CREATE_REGISTER_TAB = "CREATE TABLE "
        + REGISTER_TAB_NAME + " ( "
        + KEY_ID + " INTEGER PRIMARY KEY, "
        + KEY_CHECKNUM + " REAL, "
        + KEY_DATE + " TEXT, "
        + KEY_PAYEE + " TEXT, "
        + KEY_AMOUNT + " REAL, "
        + KEY_BALANCE + " REAL, "
        + KEY_MEMO + " TEXT, "
        + KEY_ACCOUNT_ID + " INTEGER, "
        + "CONSTRAINT fk_account FOREIGN KEY ( "
        + KEY_ACCOUNT_ID + ") REFERENCES "
        + ACCOUNT_TAB_NAME + " ( " + KEY_ID + " ) );";
    
    public static final String ADD_MEMO_COLUMN = "ALTER TABLE "
        + REGISTER_TAB_NAME + " ADD COLUMN " + KEY_MEMO + " TEXT;";
    
    public static final String ADD_ACCOUNT_COLUMN = "ALTER TABLE "
        + REGISTER_TAB_NAME + " ADD COLUMN " + KEY_ACCOUNT_ID + " INTEGER"
        + " REFERENCES " + ACCOUNT_TAB_NAME + " ( " + KEY_ID + " );";

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

    public CheckRegisterDBOpenHelper(Context context) {
      super(context, REGISTER_DB_NAME, null, REGISTER_DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase _db) {
      _db.execSQL(CREATE_ACCOUNT_TAB);
      _db.execSQL(CREATE_REGISTER_TAB);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVer, int newVer) {
      if (oldVer < 2) {
        db.execSQL(ADD_MEMO_COLUMN);
      }
      if (oldVer < 3) {
        db.execSQL(CREATE_ACCOUNT_TAB);
        db.execSQL(ADD_ACCOUNT_COLUMN);
      }
    }
  } // class CheckRegisterDBOpenHelper

} // class CheckRegisterDBAdapter