/* 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 IMDbSqlite : Object {
	Database db;
	List<string> genres;

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

	public IMDbSqlite (string filename) {
		int rc;

		genres = new List<string> ();

		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 journal_mode = OFF;", callback, null);
		if (rc != Sqlite.OK) {
			stderr.printf ("Can't turn off journal mode: %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 ());

	}

	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 title, int year) {
		string sql = "INSERT INTO Movies(Title, Year) VALUES (\"%s\", %d);".printf (title, year);
		int rc;

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

		return 0;
	}

	public int movie_set_rating (string title, int rating, int votes) {
		var sql = "UPDATE Movies SET Rating=%d, Votes=%d WHERE Title=\"%s\";".printf (rating, votes, title);
		int rc;

		rc = db.exec (sql, callback, null);
		if (rc != Sqlite.OK) {
			stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
			return 1;
		}

		return 0;
	}

	public int movie_add_genre (string title, string genre) {
		string sql;
		int bit;
		int rc;

		bit = genre_bit (genre);
		if (bit == 0) {
			genres.append (genre);
			bit = genre_bit (genre);

			sql = "INSERT INTO Genres(Bit, Genre) VALUES (%d, \"%s\");".printf (bit, genre);

			rc = db.exec (sql, callback, null);
			if (rc != Sqlite.OK) {
				stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
				return 1;
			}
		}

		sql = "UPDATE Movies SET Genres=Genres|%d WHERE Title=\"%s\";".printf (bit, title);
		rc = db.exec (sql, callback, null);
		if (rc != Sqlite.OK) {
			stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
			return 1;
		}

		return 0;
	}

	int genre_bit (string genre) {
		for (int i = 0; i < genres.length (); i++) {
			if (genres.nth_data (i) == genre)
				return 1 << i;
		}
		return 0;
	}

	public int add_aka (string title, string aka) {
		int rowid;

		if (!movie_exists (title, out rowid))
			return 1;

		string sql = "INSERT INTO Akas(Aka, TitleID) VALUES (\"%s\", %d);".printf (aka, rowid);
		int rc;
		rc = db.exec (sql, callback, null);
		if (rc != Sqlite.OK) {
			stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
			return 1;
		}

		return 0;
	}

	public bool movie_exists (string title, out int rowid = null) {
		string sql = "SELECT rowid FROM Movies WHERE Title=\"%s\"".printf (title);
		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 ());
			return false;
		}

		do {
			rc = stmt.step ();
			if (rc == Sqlite.ROW) {
				if (&rowid != null) {
					rowid = stmt.column_int (0);
				}
				return true;
			}
		} while (rc == Sqlite.ROW);

		return false;
	}

	public int clear () {
		int rc;

		rc = db.exec (
			"DROP TABLE IF EXISTS Movies;" +
			"CREATE TABLE Movies (Title TEXT PRIMARY KEY COLLATE NOCASE, Year INTEGER, Rating INTEGER, Votes INTEGER NOT NULL DEFAULT 0, Genres INTEGER NOT NULL DEFAULT 0);" +
			"DROP TABLE IF EXISTS Genres;" +
			"CREATE TABLE Genres (Bit INTEGER PRIMARY KEY, Genre TEXT NOT NULL);" +
			"DROP TABLE IF EXISTS Akas;" +
			"CREATE TABLE Akas (Aka TEXT NOT NULL COLLATE NOCASE, TitleID INTEGER NOT NULL);",
			callback, null);
		if (rc != Sqlite.OK) {
			stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
			return 1;
		}

		return 0;
	}

	public int create_aka_index () {
		int rc;

		rc = db.exec ("CREATE INDEX AkasAka ON Akas(Aka);", callback, null);
		if (rc != Sqlite.OK) {
			stderr.printf ("SQL error: %d, %s\n", rc, db.errmsg ());
			return 1;
		}

		return 0;
	}

	public int create_votes_index () {
		int rc;

		rc = db.exec ("CREATE INDEX MovieVotes ON Movies(Votes);", 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 (MovieFilter filter, ReceiveMovieFunction receive_movie, int limit, Cancellable? cancellable) {
		var sql = "SELECT Title, Year, Rating, Genres FROM Movies";
		var sep = " WHERE ";
		string match = null;
		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) {
				match = "GLOB \"%s (*)\"".printf (filter.title);
			} else {
				match = "LIKE \"%s%%\"".printf (filter.title);
			}
			sql += sep + "(Title %s OR rowid IN (SELECT TitleID FROM Akas WHERE Aka %s))".printf (match, match);
			sep = " AND ";
		}
		if (filter.year_min > 0) {
			sql += sep + "Year >= %d".printf (filter.year_min);
			sep = " AND ";
		}
		if (filter.year_max > 0) {
			sql += sep + "Year <= %d".printf (filter.year_max);
			sep = " AND ";
		}
		if (filter.rating_min > 0) {
			sql += sep + "Rating >= %d".printf (filter.rating_min);
			sep = " AND ";
		}
		if (filter.genres.field != 0) {
			sql += sep + "Genres&%d = %d".printf (filter.genres.field, filter.genres.field);
		}
		sql += " ORDER BY Votes DESC LIMIT %d;".printf (limit);

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

		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 1;
		}

		do {
			Idle.add (query.callback);
			yield;
			rc = stmt.step ();
			if (rc == Sqlite.ROW) {
				int year = stmt.column_int (1);
				string title = stmt.column_text (0);
				int rating = stmt.column_int (2);
				int genres = stmt.column_int (3);
				string aka = null;
				if (match != null && !(filter.matches_title (strip_year (title, year)))) {
					aka = movie_aka (title, match);
					if (aka != null)
						aka = strip_year (aka, year);
				}
				receive_movie (strip_year (title, year), aka, year, rating, genres);
			}
		} while (rc == Sqlite.ROW);

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

	private string movie_aka (string title, string match) {
		string sql = "SELECT Aka FROM Akas WHERE (TitleID = (SELECT rowid FROM Movies WHERE Title = \"%s\") AND Aka %s) LIMIT 1;".printf (title, match);
		Statement stmt;
		int rc;
		string aka = null;

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

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

		return aka;
	}

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

	private string strip_year (string title, int year) {
		string year_suffix = " (%d)".printf (year);
		if (title.has_suffix (year_suffix))
			return title.substring (0, title.length - year_suffix.length);
		year_suffix = " (%d/I)".printf (year);
		if (title.has_suffix (year_suffix))
			return title.substring (0, title.length - year_suffix.length);
		year_suffix = " (%d/II)".printf (year);
		if (title.has_suffix (year_suffix))
			return title.substring (0, title.length - year_suffix.length);
		return title.dup ();
	}
}