/* This file is part of Cinaest.
 *
 * Copyright (C) 2009 Philipp Zabel
 *
 * Cinaest is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * Cinaest is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with Cinaest. If not, see <http://www.gnu.org/licenses/>.
 */

using Sqlite;

class CatalogSqlite : Object {
	Database db;
	SList<Movie> result;
	int results_waiting;

	public delegate void ReceiveMovieFunction (string title, int year, int rating, int genres);

	public CatalogSqlite (string filename) {
		int rc;

		rc = Database.open (filename, out db);
		if (rc != Sqlite.OK) {
			stderr.printf ("Can't open database: %d, %s\n", rc, db.errmsg ());
			return;
		}

		rc = db.exec ("PRAGMA locking_mode = EXCLUSIVE;", callback, null);
		if (rc != Sqlite.OK) {
			stderr.printf ("Can't get exclusive lock: %d, %s\n", rc, db.errmsg ());
			return;
		}

		rc = db.exec ("PRAGMA synchronous = OFF;", callback, null);
		if (rc != Sqlite.OK)
			stderr.printf ("Can't turn off synchronous access: %d, %s\n", rc, db.errmsg ());

		prepare ();
	}

	public static int callback (int n_columns, string[] values,
	                            string[] column_names) {
		for (int i = 0; i < n_columns; i++) {
			stdout.printf ("%s = %s\n", column_names[i], values[i]);
		}
		stdout.printf ("\n");

		return 0;
	}

	public int add_movie (string table, Movie movie) {
		var sql = new StringBuilder ();
		int rc;
		sql.append_printf ("INSERT INTO %s(Title, Year, Rating, Genres", table);
		if (table == "Watched")
			sql.append_printf (", Date) VALUES (\"%s\", %d, %d, %d, %u);",
			                   movie.title, movie.year, movie.rating, movie.genres.field, movie.julian_date);
		else
			sql.append_printf (") VALUES (\"%s\", %d, %d, %d);",
			                   movie.title, movie.year, movie.rating, movie.genres.field);

		rc = db.exec (sql.str, callback, null);
		if (rc != Sqlite.OK) {
			stderr.printf ("Failed to insert movie \"%s\" (%d): %d, %s\n", movie.title, movie.year, rc, db.errmsg ());
			return 1;
		}

		return 0;
	}

	public int delete_movie (string table, Movie movie) {
		string sql = "DELETE FROM %s WHERE Title=\"%s\" AND Year=%d".printf (table, movie.title, movie.year);
		int rc;

		rc = db.exec (sql, callback, null);
		if (rc != Sqlite.OK) {
			stderr.printf ("Failed to delete movie \"%s\" (%d): %d, %s\n", movie.title, movie.year, rc, db.errmsg ());
			return 1;
		}

		return 0;
	}

	public int count (string table) {
		string sql = "SELECT count(*) FROM %s".printf (table);
		Statement stmt;
		int rc;
		int count = 0;

		rc = db.prepare_v2 (sql, -1, out stmt);
		if (rc != Sqlite.OK) {
			stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
			db.progress_handler (0, null);
			return 0;
		}

		do {
			rc = stmt.step ();
			if (rc == Sqlite.ROW) {
				count = stmt.column_int (0);
			}
		} while (rc == Sqlite.ROW);

		return count;
	}

	public bool contains (string table, Movie movie) {
		string sql = "SELECT count(*) FROM %s WHERE Title=\"%s\" AND Year=%d".printf (table, movie.title, movie.year);
		Statement stmt;
		int rc;
		int count = 0;

		rc = db.prepare_v2 (sql, -1, out stmt);
		if (rc != Sqlite.OK) {
			stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
			db.progress_handler (0, null);
			return false;
		}

		do {
			rc = stmt.step ();
			if (rc == Sqlite.ROW) {
				count = stmt.column_int (0);
			}
		} while (rc == Sqlite.ROW);

		return (count > 0);
	}

	private int prepare () {
		Statement stmt;
		int rc;

		rc = db.exec ("CREATE TABLE IF NOT EXISTS Collection (Title TEXT NOT NULL, Year INTEGER, Rating INTEGER, Genres INTEGER NOT NULL DEFAULT 0); " +
		              "CREATE TABLE IF NOT EXISTS Loaned (Title TEXT NOT NULL, Year INTEGER, Rating INTEGER, Genres INTEGER NOT NULL DEFAULT 0); " +
		              "CREATE TABLE IF NOT EXISTS Watched (Title TEXT NOT NULL, Year INTEGER, Rating INTEGER, Genres INTEGER NOT NULL DEFAULT 0, Date INTEGER); " +
		              "CREATE TABLE IF NOT EXISTS Watchlist (Title TEXT NOT NULL, Year INTEGER, Rating INTEGER, Genres INTEGER NOT NULL DEFAULT 0);",
		              callback, null);
		if (rc != Sqlite.OK) {
			stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
			return 1;
		}

		// Add a date column to the Watched table
		rc = db.prepare_v2 ("SELECT sql FROM sqlite_master WHERE (type = 'table' AND name = 'Watched');",
		                    -1, out stmt);
		if (rc != Sqlite.OK) {
			stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
			return 1;
		}
		do {
			rc = stmt.step ();
			if (rc == Sqlite.ROW) {
				var sql = stmt.column_text (0);
				if (sql == "CREATE TABLE Watched (Title TEXT NOT NULL, Year INTEGER, Rating INTEGER, Genres INTEGER NOT NULL DEFAULT 0)") {
					rc = db.exec ("ALTER TABLE Watched ADD COLUMN Date INTEGER;",
						      callback, null);
					if (rc != Sqlite.OK) {
						stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
						return 1;
					}
				}
				break;
			}
		} while (rc == Sqlite.ROW);

		return 0;
	}

	public int clear () {
		int rc;

		rc = db.exec ("DROP TABLE IF EXISTS Collection; CREATE TABLE Collection (Title TEXT NOT NULL, Year INTEGER, Rating INTEGER, Genres INTEGER NOT NULL DEFAULT 0); DROP TABLE IF EXISTS Loaned; CREATE TABLE Loaned (Title TEXT NOT NULL, Year INTEGER, Rating INTEGER, Genres INTEGER NOT NULL DEFAULT 0); DROP TABLE IF EXISTS Watchlist; CREATE TABLE Watchlist (Title TEXT NOT NULL, Year INTEGER, Rating INTEGER, Genres INTEGER NOT NULL DEFAULT 0);", callback, null);
		if (rc != Sqlite.OK) {
			stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
			return 1;
		}

		return 0;
	}

	private Cancellable? _cancellable;
	public async int query (string table, MovieFilter filter, MovieSource.ReceiveMovieFunction callback, int limit, Cancellable? cancellable) {
		var sql = new StringBuilder ();
		sql.append ("SELECT Title, Year, Rating, Genres");
		if (table == "Watched")
			sql.append (", Date");
		sql.append (" FROM ");
		sql.append (table);
		var sep = " WHERE ";
		Statement stmt;
		int rc;

		// FIXME - how many opcodes until main loop iteration for best responsivity?
		_cancellable = cancellable;
		db.progress_handler (1000, progress_handler);

		if (filter.title != null && filter.title != "") {
			if ("*" in filter.title) {
				sql.append (sep);
				sql.append_printf ("Title GLOB \"%s\"", filter.title);
			} else {
				sql.append (sep);
				sql.append_printf ("Title LIKE \"%s%%\"", filter.title);
			}
			sep = " AND ";
		}
		if (filter.year_min > 0) {
			sql.append (sep);
			sql.append_printf ("Year >= %d", filter.year_min);
			sep = " AND ";
		}
		if (filter.year_max > 0) {
			sql.append (sep);
			sql.append_printf ("Year <= %d", filter.year_max);
			sep = " AND ";
		}
		if (filter.rating_min > 0) {
			sql.append (sep);
			sql.append_printf ("Rating >= %d", filter.rating_min);
			sep = " AND ";
		}
		if (filter.genres.field != 0) {
			sql.append (sep);
			sql.append_printf ("Genres&%d = %d", filter.genres.field, filter.genres.field);
		}
		sql.append_printf (" LIMIT %d;", limit);

		stdout.printf("SQL: \"%s\"\n", sql.str);

		rc = db.prepare_v2 (sql.str, -1, out stmt);
		if (rc != Sqlite.OK) {
			stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
			db.progress_handler (0, null);
			return 1;
		}

		result = new SList<Movie> ();
		results_waiting = 0;

		do {
			Idle.add (query.callback);
			yield;
			rc = stmt.step ();
			if (rc == Sqlite.ROW) {
				var movie = new Movie ();
				movie.year = stmt.column_int (1);
				movie.title = stmt.column_text (0);
				movie.rating = stmt.column_int (2);
				movie.genres.field = stmt.column_int (3);
				if (table == "Watched")
					movie.julian_date = stmt.column_int (4);
				// TODO - depending on settings, this could be something else, like director info or runtime
				movie.secondary = movie.genres.to_string ();
				result.append (movie);
				if (++results_waiting >= 10) {
					callback (result);
					result = new SList<Movie> ();
					results_waiting = 0;
				}
			}
		} while (rc == Sqlite.ROW);

		if (results_waiting > 0)
			callback (result);
		result = new SList<Movie> ();

		db.progress_handler (0, null);
		return 0;
	}

	private int progress_handler () {
		((MainContext) null).iteration (false);
		return (int) _cancellable.is_cancelled ();
	}
}