package net.ddns.mahaljsp.ch13_01_sqlite;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Created by Thomas Wu on 2016/12/5.
 */

public class ItemDAO {
    public static final String TABLE_NAME = "contact";
    public static final String KEY_ID="_id";
    public static final String FIRSTNAME_COLUMN="firstname";
    public static final String LASTNAME_COLUMN="lastname";
    public static final String TEL_COLUMN="tel";
    public static final String EMAIL_COLUMN="email";
    public static final String ADDRESS_COLUMN="address";
    public static final String BIRTHDAY_COLUMN="birthday";
    public static final String CREATE_TABLE =
            "CREATE TABLE " + TABLE_NAME + " (" +
                    KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    FIRSTNAME_COLUMN + " TEXT NOT NULL, " +
                    LASTNAME_COLUMN + " TEXT NOT NULL, " +
                    TEL_COLUMN + " TEXT NOT NULL, " +
                    EMAIL_COLUMN + " TEXT, " +
                    ADDRESS_COLUMN + " TEXT, " +
                    BIRTHDAY_COLUMN + " INTEGER)";
    private SQLiteDatabase db;
    public ItemDAO(Context context){
        db=DBHelper.getDatabase(context);
    }
    public Item insert(Item item){
        ContentValues cv=new ContentValues();
        cv.put(FIRSTNAME_COLUMN, item.getFirstname());
        cv.put(LASTNAME_COLUMN, item.getLastname());
        cv.put(TEL_COLUMN, item.getTel());
        cv.put(EMAIL_COLUMN, item.getEmail());
        cv.put(ADDRESS_COLUMN, item.getAddress());
        cv.put(BIRTHDAY_COLUMN, item.getBirthday());
        long id=db.insert(TABLE_NAME, null, cv);
        item.setId(id);
        return item;
    }
    public boolean update(Item item){
        ContentValues cv=new ContentValues();
        cv.put(FIRSTNAME_COLUMN, item.getFirstname());
        cv.put(LASTNAME_COLUMN, item.getLastname());
        cv.put(TEL_COLUMN, item.getTel());
        cv.put(EMAIL_COLUMN, item.getEmail());
        cv.put(ADDRESS_COLUMN, item.getAddress());
        cv.put(BIRTHDAY_COLUMN, item.getBirthday());
        String where=KEY_ID+"="+item.getId();
        return db.update(TABLE_NAME, cv, where, null)>0;
    }
    public boolean delete(long id){
        String where =KEY_ID+"="+id;
        return db.delete(TABLE_NAME, where, null)>0;
    }
    public Item get(long id) {
        Item item = null;
        String where = KEY_ID + "=" + id;
        Cursor result = db.query(TABLE_NAME, null, where,
                null, null, null, null, null);
        if (result.moveToFirst()) {
            item = getRecord(result);
        }
        result.close();
        return item;
    }
    public Item getRecord(Cursor cursor){
        Item item=new Item();
        item.setId(cursor.getLong(0));
        item.setFirstname(cursor.getString(1));
        item.setLastname(cursor.getString(2));
        item.setTel(cursor.getString(3));
        item.setEmail(cursor.getString(4));
        item.setAddress(cursor.getString(5));
        item.setBirthday(cursor.getLong(6));
        return item;
    }
    public List<Item> getAll(){
        List<Item>list=new ArrayList<>();
        Cursor cursor = db.query(
                TABLE_NAME, null, null, null, null, null, null, null);
        while (cursor.moveToNext()) {
            list.add(getRecord(cursor));
        }
        cursor.close();
        return list;
    }
    public int getCount() {
        int count = 0;
        Cursor cursor = db.rawQuery("SELECT COUNT(*) FROM " + TABLE_NAME, null);
        if (cursor.moveToNext()) {
            count = cursor.getInt(0);
        }
        return count;
    }
    public void sample() {
        Item item1 = new Item(0, "Thomas", "Wu", "0989123456", "mahaljsp@gmail.com", "彰化市中正路100號", new Date().getTime());
        Item item2 = new Item(0, "John", "Hung", "0912123456", "john@gmail.com", "台北市忠孝東路一段23巷5號6F", new Date().getTime());
        insert(item1);
        insert(item2);
    }
}
