package com.xlg.forkids.db;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import com.xlg.forkids.Functions;
import com.xlg.forkids.R;
import com.xlg.forkids.baseitems.AreaCity;
import com.xlg.forkids.baseitems.AreaProvince;
import com.xlg.view.Utils;

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

public class AreaDbHelper {

	private SQLiteDatabase database;
	private Context context;

	private static final int BUFFER_SIZE = 4096;
	private static final String dbName = "china.db";
	private static final String dbPath = "database";

	private static final String TableProvince = "Province";
	private static final String ProvinceId = "ProvinceId";
	private static final String ProvinceCode = "ProvinceCode";
	private static final String ProvinceName = "ProvinceName";

	private static final String TableCity = "City";
	private static final String CityId = "CityId";
	private static final String CityCode = "CityCode";
	private static final String CityName = "CityName";

	private static AreaDbHelper mInstance = null;

	public static AreaDbHelper getInstance(Context context) {
		if (null == mInstance) {
			mInstance = new AreaDbHelper(context);
		}
		return mInstance;
	}

	AreaDbHelper(Context context) {
		this.context = context;
		this.database = this.open(Functions.getCachePath(this.context, dbPath));
	}

	// public SQLiteDatabase open() {
	// return this.database;
	// }

	public void close() {
		this.database.close();
		mInstance = null;
	}

	/** ÅжÏ×Ö·û´®ÊÇ·ñΪ¿Õ£ºnull»ò""»ò"null" */
	// protected boolean isEmpty(String string) {
	// return (string == null || string.equals("") || string.equals("null"));
	// }

	private SQLiteDatabase open(String fullPath) {
		try {
			// ÅжÏÊý¾Ý¿âÎļþÊÇ·ñ´æÔÚ£¬Èô²»´æÔÚÔòÖ´Ðе¼È룬·ñÔòÖ±½Ó´ò¿ªÊý¾Ý¿â
			String dbFullPath = fullPath + dbName;
			// ´´½¨Îļþ¼Ð
			File f = new File(fullPath);
			if (!f.exists()) {
				// ´´½¨ÏàÓ¦µÄĿ¼
				f.mkdirs();
			}
			// ´´½¨Êý¾Ý¿âÎļþ
			File d = new File(dbFullPath);
			if (!d.exists()) {
				// Óûµ¼ÈëµÄÊý¾Ý¿â
				InputStream is = this.context.getResources().openRawResource(
						R.raw.china);
				FileOutputStream fos = new FileOutputStream(dbFullPath);
				byte[] buffer = new byte[BUFFER_SIZE];
				int count = 0;
				while ((count = is.read(buffer)) > 0) {
					fos.write(buffer, 0, count);
				}
				buffer = null;
				fos.close();
				is.close();
			}
			SQLiteDatabase db = SQLiteDatabase.openOrCreateDatabase(dbFullPath,
					null);
			return db;
		} catch (FileNotFoundException e) {
			Log.e("Database", "File not found");
			e.printStackTrace();
		} catch (IOException e) {
			Log.e("Database", "IO exception");
			e.printStackTrace();
		}
		return null;
	}

	private AreaProvince getProvince(Cursor cursor) {
		AreaProvince item = new AreaProvince();
		item.setProvinceCode(cursor.getString(cursor
				.getColumnIndexOrThrow(ProvinceCode)));
		item.setProvinceId(cursor.getInt(cursor
				.getColumnIndexOrThrow(ProvinceId)));
		item.setProvinceName(cursor.getString(cursor
				.getColumnIndexOrThrow(ProvinceName)));
		return item;
	}

	/** »ñÈ¡Ê¡¼¶ÐÐÕþÇøÓòÁбí */
	public List<AreaProvince> getProvinces(String search) {
		List<AreaProvince> tmp = new ArrayList<AreaProvince>();
		String sql = " where " + ProvinceCode + " in(select " + ProvinceCode
				+ " from " + TableCity + " where " + CityName + " like '%"
				+ search + "%') ";
		Cursor cursor = database.rawQuery("select * from " + TableProvince
				+ (Utils.isEmpty(search) ? "" : (sql)) + " order by "
				+ ProvinceCode, null);
		try {
			while (cursor.moveToNext()) {
				tmp.add(getProvince(cursor));
			}
		} finally {
			cursor.close();
		}
		return tmp;
	}

	private AreaCity getCity(Cursor cursor) {
		AreaCity item = new AreaCity();
		item.setCityCode(cursor.getString(cursor
				.getColumnIndexOrThrow(CityCode)));
		item.setCityId(cursor.getInt(cursor.getColumnIndexOrThrow(CityId)));
		item.setCityName(cursor.getString(cursor
				.getColumnIndexOrThrow(CityName)));
		item.setProvinceCode(cursor.getString(cursor
				.getColumnIndexOrThrow(ProvinceCode)));
		return item;
	}

	/** »ñÈ¡Êм¶ÐÐÕþÇøÓòÁбí */
	public List<AreaCity> getCities(String provinceCode, String search) {
		List<AreaCity> tmp = new ArrayList<AreaCity>();
		Cursor cursor = database.rawQuery("select * from "
				+ TableCity
				+ " where "
				+ ProvinceCode
				+ " = '"
				+ provinceCode
				+ "' "
				+ (!Utils.isEmpty(search) ? (" and " + CityName + " like '%"
						+ search + "%'") : "") + " order by " + CityCode, null);
		try {
			while (cursor.moveToNext()) {
				tmp.add(getCity(cursor));
			}
		} finally {
			cursor.close();
		}
		return tmp;
	}
}
