package org.fox.ttcomics2;

import android.annotation.SuppressLint;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.os.AsyncTask;
import android.provider.BaseColumns;

import org.fox.ttcomics2.archive.CbrComicArchive;
import org.fox.ttcomics2.archive.CbzComicArchive;
import org.fox.ttcomics2.archive.ComicArchive;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Date;

public class DatabaseHelper extends SQLiteOpenHelper {
	
	public static final String DATABASE_NAME = "ComicsCache.db";
	public static final int DATABASE_VERSION = 2;
	private Context m_context;
	private static DatabaseHelper m_instance;

	public static synchronized DatabaseHelper getInstance(Context context) {

		if (m_instance == null) {
			m_instance = new DatabaseHelper(context.getApplicationContext());
		}

		return m_instance;
	}

	public interface DirectoryScanListener {
		void onProgressUpdate(int progress, int max);
		void onPostExecute(int result);
	}

	private DatabaseHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		m_context = context;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("DROP TABLE IF EXISTS comics_cache;");
		
		db.execSQL("CREATE TABLE IF NOT EXISTS comics_cache (" +
				BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
				"filename TEXT, " +
				"path TEXT, " +                //v2
				"checksum TEXT, " +            //v2
				"size INTEGER, " +
				"position INTEGER, " +
				"max_position INTEGER" +
				");");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		if (oldVersion == 1 && newVersion == 2) {
			
			db.execSQL("ALTER TABLE comics_cache ADD COLUMN path TEXT;");
			db.execSQL("ALTER TABLE comics_cache ADD COLUMN checksum TEXT;");
			
			Cursor c = db.query("comics_cache", null,
					null, null, null, null, null);
			
			if (c.moveToFirst()) {
				while (!c.isAfterLast()) {
					int id = c.getInt(c.getColumnIndex(BaseColumns._ID));
					String fileName = c.getString(c.getColumnIndex("filename"));
	
					File file = new File(fileName);
					
					SQLiteStatement stmt = db.compileStatement("UPDATE comics_cache SET filename = ?, path = ? WHERE " + BaseColumns._ID + " = ?");
					stmt.bindString(1, file.getName());
					stmt.bindString(2, file.getParentFile().getAbsolutePath());
					stmt.bindLong(3, id);
					stmt.execute();
	
					c.moveToNext();
				}
			}
			
			c.close();
		}		
	}

	public Cursor findComicByFileName(String fileName) {
		File file = new File(fileName);

		if (getWritableDatabase() == null)
			return null;

		Cursor c = getWritableDatabase().query("comics_cache", null,
				"filename = ? AND path = ?",
				new String[]{file.getName(), file.getParentFile().getAbsolutePath()}, null, null, null);

		if (c.moveToFirst()) {
			return c;
		} else {
			c.close();

			SQLiteStatement stmt = getWritableDatabase().compileStatement("INSERT INTO comics_cache " +
					"(filename, path, position, max_position, size, checksum) VALUES (?, ?, 0, 0, -1, '')");
			stmt.bindString(1, file.getName());
			stmt.bindString(2, file.getParentFile().getAbsolutePath());
			stmt.execute();

			c = getWritableDatabase().query("comics_cache", null,
					"filename = ? AND path = ?",
					new String[] { file.getName(), file.getParentFile().getAbsolutePath() }, null, null, null);

			if (c.moveToFirst()) {
				return c;
			} else {
				c.close();
			}
		}

		return null;
	}

	public void setSize(String fileName, int size) {
		Cursor c = findComicByFileName(fileName);

		if (c != null) {
			c.close();

			File file = new File(fileName);

			SQLiteStatement stmt = getWritableDatabase().compileStatement("UPDATE comics_cache SET size = ? WHERE filename = ? AND path = ?");
			stmt.bindLong(1, size);
			stmt.bindString(2, file.getName());
			stmt.bindString(3, file.getParentFile().getAbsolutePath());
			stmt.execute();
			stmt.close();
		}
	}

	public void setLastPosition(String fileName, int position) {
		int lastPosition = getLastPosition(fileName);

		Cursor c = findComicByFileName(fileName);

		if (c != null) {
			c.close();

			File file = new File(fileName);

			SQLiteStatement stmt = getWritableDatabase().compileStatement("UPDATE comics_cache SET position = ?, max_position = ? WHERE filename = ? AND path = ?");
			stmt.bindLong(1, position);
			stmt.bindLong(2, Math.max(position, lastPosition));
			stmt.bindString(3, file.getName());
			stmt.bindString(4, file.getParentFile().getAbsolutePath());
			stmt.execute();
			stmt.close();
		}

	}

	public void setLastMaxPosition(String fileName, int position) {

		Cursor c = findComicByFileName(fileName);

		if (c != null) {
			c.close();

			File file = new File(fileName);

			SQLiteStatement stmt = getWritableDatabase().compileStatement("UPDATE comics_cache SET max_position = ? WHERE filename = ? AND path = ?");
			stmt.bindLong(1, position);
			stmt.bindString(2, file.getName());
			stmt.bindString(3, file.getParentFile().getAbsolutePath());
			stmt.execute();
			stmt.close();
		}
	}

	public int getLastPosition(String fileName) {
		int position = 0;

		File file = new File(fileName);

		Cursor c = getWritableDatabase().query("comics_cache", new String[]{"position"},
				"filename = ? AND path = ?",
				new String[]{file.getName(), file.getParentFile().getAbsolutePath()}, null, null, null);

		if (c.moveToFirst()) {
			position = c.getInt(c.getColumnIndex("position"));
		}

		c.close();

		return position;

	}

	public int getCachedItemCount(String baseDir) {
		Cursor c = getWritableDatabase().query("comics_cache", new String[] { "COUNT(*)" },
				"path = ?",
				new String[] { baseDir }, null, null, null);

		c.moveToFirst();
		int count = c.getInt(0);
		c.close();

		//Log.d(TAG, "getCachedItemCount:" + baseDir + "=" + count);

		return count;
	}

	public int getMaxPosition(String fileName) {
		int position = 0;

		File file = new File(fileName);

		Cursor c = getWritableDatabase().query("comics_cache", new String[]{"max_position"},
				"filename = ? AND path = ?",
				new String[]{file.getName(), file.getParentFile().getAbsolutePath()}, null, null, null);

		if (c.moveToFirst()) {
			position = c.getInt(c.getColumnIndex("max_position"));
		}

		c.close();

		return position;
	}

	public int getSize(String fileName) {
		int size = -1;

		File file = new File(fileName);

		Cursor c = getWritableDatabase().query("comics_cache", new String[] { "size" },
				"filename = ? AND path = ?",
				new String[] { file.getName(), file.getParentFile().getAbsolutePath() }, null, null, null);

		if (c.moveToFirst()) {
			size = c.getInt(c.getColumnIndex("size"));
		}

		c.close();

		//Log.d(TAG, "getSize:" + fileName + "=" + size);

		return size;
	}

	public void cleanupSqliteCache(String baseDir) {
		Cursor c = getWritableDatabase().query("comics_cache", null,
				null, null, null, null, null);

		if (c.moveToFirst()) {
			while (!c.isAfterLast()) {
				int id = c.getInt(c.getColumnIndex(BaseColumns._ID));
				String fileName = c.getString(c.getColumnIndex("path")) + "/" + c.getString(c.getColumnIndex("filename"));

				File file = new File(fileName);

				if (!file.exists()) {
					SQLiteStatement stmt = getWritableDatabase().compileStatement("DELETE FROM comics_cache WHERE " + BaseColumns._ID + " = ?");
					stmt.bindLong(1, id);
					stmt.execute();
				}

				c.moveToNext();
			}
		}

		c.close();
	}

	public void rescanDirectory(String comicsDir, final DirectoryScanListener listener) {

		@SuppressLint("StaticFieldLeak") AsyncTask<String, Integer, Integer> task = new AsyncTask<String, Integer, Integer>() {

			@Override
			protected void onProgressUpdate(Integer... progress) {
				listener.onProgressUpdate(progress[0], progress[1]);
			}

			@Override
			protected void onPostExecute(Integer result) {
				listener.onPostExecute(result);
			}

			@Override
			protected Integer doInBackground(String... params) {
				try {

					String comicsDir = params[0];
					File dir = new File(comicsDir);

					int fileIndex = 0;

					if (dir.isDirectory()) {
                        File[] archives = dir.listFiles();

						java.util.Arrays.sort(archives);

						for (File archive : archives) {
							String filePath = archive.getAbsolutePath();

							if (archive.isDirectory()) {
								setSize(filePath, ComicListFragment.SIZE_DIR);

							} else if (archive.getName().toLowerCase().matches(".*\\.(cbz|zip|cbr|rar)")) {
								try {
									ComicArchive cba = null;

									if (archive.getName().toLowerCase().matches(".*\\.(cbz|zip)")) {
										cba = new CbzComicArchive(filePath);
									} else if (archive.getName().toLowerCase().matches(".*\\.(cbr|rar)")) {
										cba = new CbrComicArchive(filePath);
									}

									if (cba != null && cba.getCount() > 0) {
										// Get cover

										try {
											File thumbnailFile = new File(CommonActivity.getThumbnailFileName(m_context, filePath));

											if (!thumbnailFile.exists()) {
												InputStream is = cba.getItem(0);

												if (is != null) {
													FileOutputStream fos = new FileOutputStream(thumbnailFile);

													byte[] buffer = new byte[1024];
													int len;
													while ((len = is.read(buffer)) != -1) {
														fos.write(buffer, 0, len);
													}

													fos.close();
													is.close();
												}
											} else if (thumbnailFile.exists()) {
												thumbnailFile.setLastModified(new Date().getTime());
											}

										} catch (IOException e) {
											e.printStackTrace();
										}

										int size = cba.getCount();

										setSize(filePath, size);
									}
								} catch (IOException e) {
									e.printStackTrace();
								} catch (SQLException e) {
									e.printStackTrace();
								}
							}

							++fileIndex;

							publishProgress(fileIndex, archives.length);
						}
					}

					cleanupSqliteCache(comicsDir);

					return fileIndex;

				} catch (Exception e) {
					e.printStackTrace();
				}

				return 0;
			}
		};

		task.execute(comicsDir);
	}
}