/* 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;
	}

	Statement add_statement = null;
	public int add_movie (string title, int year) {
		int rc;

		if (add_statement == null) {
			string sql = "INSERT INTO Movies(Title, Year) VALUES (?, ?);";
			assert (db.prepare_v2 (sql, -1, out add_statement) == Sqlite.OK);
		}

		assert (add_statement.bind_text (1, title) == Sqlite.OK);
		assert (add_statement.bind_int (2, year) == Sqlite.OK);

		rc = add_statement.step ();
		if (rc != Sqlite.DONE) {
			if (rc == Sqlite.CONSTRAINT)
				stderr.printf ("Skipped duplicate \"%s\" (%d)\n", title, year);
			else
				stderr.printf ("Failed to insert movie \"%s\" (%d): %d, %s\n", title, year, rc, db.errmsg ());
			add_statement.reset ();
			return 1;
		}
		add_statement.reset ();

		return 0;
	}

	Statement rating_statement = null;
	public int movie_set_rating (string title, int rating, int votes) {
		int rc;

		if (rating_statement == null) {
			var sql = "UPDATE Movies SET Rating=?, Votes=? WHERE Title=?;";
			assert (db.prepare_v2 (sql, -1, out rating_statement) == Sqlite.OK);
		}

		assert (rating_statement.bind_int (1, rating) == Sqlite.OK);
		assert (rating_statement.bind_int (2, votes) == Sqlite.OK);
		assert (rating_statement.bind_text (3, title) == Sqlite.OK);

		rc = rating_statement.step ();
		if (rc != Sqlite.DONE) {
			stderr.printf ("Failed to set rating %d(%d) for \"%s\": %d, %s\n", rating, votes, title, rc, db.errmsg ());
			rating_statement.reset ();
			return 1;
		}
		rating_statement.reset ();

		return 0;
	}

	Statement genre_statement = null;
	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;
			}
		}

		if (genre_statement == null) {
			sql = "UPDATE Movies SET Genres=Genres|? WHERE Title=?;";
			assert (db.prepare_v2 (sql, -1, out genre_statement) == Sqlite.OK);
		}

		assert (genre_statement.bind_int (1, bit) == Sqlite.OK);
		assert (genre_statement.bind_text (2, title) == Sqlite.OK);


		rc = genre_statement.step ();
		if (rc != Sqlite.DONE) {
			stderr.printf ("Failed to add genre %s to \"%s\": %d, %s\n", genre, title, rc, db.errmsg ());
			rating_statement.reset ();
			return 1;
		}
		genre_statement.reset ();

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

	Statement aka_statement = null;
	public int add_aka (string title, string aka) {
		int rc;
		int rowid;

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

		if (aka_statement == null) {
			string sql = "INSERT INTO Akas(Aka, TitleID) VALUES (?, ?);";
			assert (db.prepare_v2 (sql, -1, out aka_statement) == Sqlite.OK);
		}

		assert (aka_statement.bind_text (1, aka) == Sqlite.OK);
		assert (aka_statement.bind_int (2, rowid) == Sqlite.OK);
		
		rc = aka_statement.step ();
		if (rc != Sqlite.DONE) {
			stderr.printf ("Failed to add aka \"%s\" to \"%s\": %d, %s\n", aka, title, rc, db.errmsg ());
			aka_statement.reset ();
			return 1;
		}
		aka_statement.reset ();

		return 0;
	}

	Statement plot_statement = null;
	public int add_plot (string title, string plot, string? author) {
		int rc;
		int rowid;

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

		if (plot_statement == null) {
			var sql = "INSERT INTO Plots(rowid, Plot, Author) VALUES (?, ?, ?);";
			assert (db.prepare_v2 (sql, -1, out plot_statement) == Sqlite.OK);
		}

		assert (plot_statement.bind_int (1, rowid) == Sqlite.OK);
		assert (plot_statement.bind_text (2, plot.replace ("\"", "&quot;")) == Sqlite.OK);
		if (author != null)
			assert (plot_statement.bind_text (3, author) == Sqlite.OK);
		else
			assert (plot_statement.bind_null (3) == Sqlite.OK);

		rc = plot_statement.step ();
		if (rc != Sqlite.DONE) {
			stderr.printf ("Failed to add plot for \"%s\": %d, %s\n", title, rc, db.errmsg ());
			plot_statement.reset ();
			return 1;
		}
		plot_statement.reset ();

		return 0;
	}

	Statement person_statement = null;
	public int add_person (string name) {
		int rc;

		if (person_statement == null) {
			string sql = "INSERT INTO People(Name) VALUES (?);";
			rc = db.prepare_v2 (sql, -1, out person_statement);
			if (rc != Sqlite.OK) {
				error ("Failed to prepare statement \"" + sql + "\": " + db.errmsg ());
			}
		}

		assert (person_statement.bind_text (1, name) == Sqlite.OK);

		rc = person_statement.step ();
		if (rc != Sqlite.DONE) {
			if (rc == Sqlite.CONSTRAINT)
				stderr.printf ("Skipped duplicate person \"%s\"\n", name);
			else
				stderr.printf ("Failed to insert person \"%s\": %d, %s\n", name, rc, db.errmsg ());
			person_statement.reset ();
			return 1;
		}
		person_statement.reset ();

		return 0;
	}

	Statement actor_statement;
	public int add_actor (string name, string title, string? info, string? character, int number = 0) {
		int actorid;
		int titleid;
		int rc;

		if (!person_exists (name, out actorid))
			return 1;
		if (!movie_exists (title, out titleid))
			return 1;

		if (actor_statement == null) {
			string sql = "INSERT INTO Actors(ActorID, TitleID, Info, Character, Number) VALUES (?, ?, ?, ?, ?);";
			assert (db.prepare_v2 (sql, -1, out actor_statement) == Sqlite.OK);
		}

		assert (actor_statement.bind_int (1, actorid) == Sqlite.OK);
		assert (actor_statement.bind_int (2, titleid) == Sqlite.OK);
		if (info != null)
			assert (actor_statement.bind_text (3, info) == Sqlite.OK);
		else
			assert (actor_statement.bind_null (3) == Sqlite.OK);

		if (character != null)
			assert (actor_statement.bind_text (4, character) == Sqlite.OK);
		else
			assert (actor_statement.bind_null (4) == Sqlite.OK);
		assert (actor_statement.bind_int (5, number) == Sqlite.OK);

		rc = actor_statement.step ();
		if (rc != Sqlite.DONE) {
			stderr.printf ("Failed to insert actor \"%s\": %d, %s\n", name, rc, db.errmsg ());
			actor_statement.reset ();
			return 1;
		}
		actor_statement.reset ();

		return 0;

	}

	Statement exists_statement = null;
	public bool movie_exists (string title, out int rowid = null) {
		if (exists_statement == null) {
			string sql = "SELECT rowid FROM Movies WHERE Title=?";
			assert (db.prepare_v2 (sql, -1, out exists_statement) == Sqlite.OK);
		}

		assert (exists_statement.bind_text (1, title) == Sqlite.OK);

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

		return false;
	}

	Statement person_exists_statement = null;
	public bool person_exists (string name, out int rowid = null) {
		if (person_exists_statement == null) {
			string sql = "SELECT rowid FROM People WHERE Name=?";
			assert (db.prepare_v2 (sql, -1, out person_exists_statement) == Sqlite.OK);
		}

		assert (person_exists_statement.bind_text (1, name) == Sqlite.OK);

		int rc = Sqlite.OK;
		do {
			rc = person_exists_statement.step ();
			if (rc == Sqlite.ROW) {
				if (&rowid != null) {
					rowid = person_exists_statement.column_int (0);
				}
				person_exists_statement.reset ();

				return true;
			}
		} while (rc == Sqlite.ROW);
		person_exists_statement.reset ();

		return false;
	}

	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 has_table (string table) {
		string sql = "SELECT * FROM sqlite_master WHERE type=\"table\" AND name=\"%s\"".printf (table);
		Statement stmt;
		int rc;

		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)
				return true;
		} while (rc == Sqlite.ROW);

		return false;
	}

	public string get_plot (string title) {
		string sql = "SELECT Plot FROM Plots WHERE rowid in (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 ());
			db.progress_handler (0, null);
			return "";
		}

		do {
			rc = stmt.step ();
			if (rc == Sqlite.ROW) {
				return stmt.column_text (0).replace ("&quot;", "\"");
			}
		} while (rc == Sqlite.ROW);

		return "";
	}

	public List<Role> get_cast (string title) {
		int rowid;
		string sql = "SELECT Name,Character FROM Actors,People WHERE TitleID IN (SELECT rowid FROM Movies WHERE Title=\"%s\") AND ActorID=People.rowid AND Number>0 ORDER BY NUMBER LIMIT 3".printf (title);
		Statement stmt;
		int rc;
		int count = 0;
		var cast = new List<Role> ();

		if (!movie_exists (title, out rowid))
			return 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) {
				var role = new Role ();
				role.actor_name = stmt.column_text (0);
				role.character = stmt.column_text (1);
				cast.append (role);
			}
		} while (rc == Sqlite.ROW);

		return cast;
	}

	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);" +
			"DROP TABLE IF EXISTS Plots;" +
			"CREATE TABLE Plots (Plot TEXT NOT NULL, Author TEXT);" +
			"DROP TABLE IF EXISTS People;" +
			"CREATE TABLE People (Name TEXT PRIMARY KEY COLLATE NOCASE);" +
			"DROP TABLE IF EXISTS Actors;" +
			"CREATE TABLE Actors (ActorID INTEGER NOT NULL, TitleID INTEGER NOT NULL, Info TEXT, Character TEXT, Number INTEGER NOT NULL DEFAULT 0);" +
			"CREATE INDEX MovieCast ON Actors(TitleID);" +
			"CREATE INDEX ActorFilmography ON Actors(ActorID);",
			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 + 1);

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

		int n = 0;
		do {
			if (++n > limit)
				break;
			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;
				if (stmt.column_type (2) != Sqlite.NULL)
					rating = stmt.column_int (2);
				else
					rating = -1;
				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 n;
	}

	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 ();
	}
}