package database;

import model.Field;
import model.MLD;
import model.Relation;
import model.ForeignKey;
#if (sys || nodejs)
import sys.io.File;
import sys.db.Sqlite;
import sys.db.Connection;
import sys.db.ResultSet;
#end
using StringTools;

@:expose
class DBSqlite {
	public static function generateSQL(mld : MLD) : String {
		var sql : String = "";
		for (r in mld.relations) {
			var hasPK : Bool = false;
			sql += "CREATE TABLE IF NOT EXISTS " + r.name + " (";
			var first : Bool = true;
			for (f in r.fields) {
				var fk : ForeignKey = Lambda.find(r.foreignKeys, fk -> fk.name == f.name);
				first ? first = false : sql += ", ";
				sql += f.name + " ";

				if (f.type.toUpperCase() == "COUNTER") {
					if (r.key.length == 1 && r.key[0] == f.name) { //the counter must be the primary key alone
						sql += "INTEGER PRIMARY KEY AUTOINCREMENT";
						hasPK = true;
					} else {
						sql += "INT";
					}
				} else {
					sql += f.dbType;
				}

				if (!hasPK && r.key.length == 1 && r.key[0] == f.name) {
					sql += " PRIMARY KEY";
				}
				if (fk != null) {
					sql += " REFERENCES " + fk.relation.name + "(" + fk.key + ")";
				}
			}
			if (r.key.length != 1) {
				sql += ", PRIMARY KEY(" + r.key.join(",") + ")";
			}
			sql += ");\n";
		}
		return sql;
	}


	#if (sys || nodejs)
	/**
	 * Standard library only support Neko, CPP, HL, Java, PHP and C# targets.
	 * Python support is missing.
	 */
	static function getConnection(filename : String) : Connection {
		return Sqlite.open(filename);
	}

	public static function createDatabase(filename : String, sqlFile : String) {
		var db : Connection = getConnection(filename);
		if (db != null) {
			for (r in File.getContent(sqlFile).split("\n")) {
				if (r != "") {
					db.request(r);
				}
			}
			db.close();
		}
	}

	public static function analyse(filename : String) : MLD {
		var mld : MLD = new MLD();
		var db : Connection = Sqlite.open(filename);
		var sqlrs : ResultSet = db.request("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name");
		for (sqlr in sqlrs) {
			if (sqlr.name != "sqlite_sequence") {
				mld.relations.set(sqlr.name, new Relation(sqlr.name));
			}
		}
		for (r in mld.relations) {
			sqlrs = db.request("PRAGMA table_info(" + r.name + ")");
			for (sqlf in sqlrs) {
				if (sqlf.type == "INTEGER" && sqlf.pk != 0 && db.request("SELECT * FROM sqlite_sequence WHERE name = '" + r.name + "'").hasNext()) {
																		//REMARK: table_sequence has no record for empty tables
					r.fields.push(new Field(sqlf.name, "COUNTER"));
				} else {
					r.fields.push(new Field(sqlf.name, sqlf.type));
				}
				if (sqlf.pk != 0) {
					r.key.push(sqlf.name);
				}
			}
			sqlrs = db.request("PRAGMA foreign_key_list(" + r.name + ")");
			for (sqlfk in sqlrs) {
				var t : Relation = mld.relations.get(sqlfk.table);
				if (t != null) {
					r.foreignKeys.push({ name : sqlfk.from, relation : t, key : sqlfk.to });
				}
			}
		}
		db.close();
		return mld;
	}
	#end
}
