package database;

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

@:expose
class DBMariaDB {
	public static function generateSQL(mld : MLD) : String {
		var sql : String = "";
		var sqlrefs : 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 += "INT PRIMARY KEY AUTO_INCREMENT";
						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) {
					sqlrefs += "ALTER TABLE " + r.name + " ADD FOREIGN KEY (" + f.name + ") REFERENCES " + fk.relation.name + "(" + fk.key + ");\n";
				}
			}
			if (r.key.length != 1) {
				sql += ", PRIMARY KEY(" + r.key.join(",") + ")";
			}
			sql += ");\n";
		}
		sql += sqlrefs;
		return sql;
	}


	#if (sys || nodejs)
	/**
	 * Standard library only support Neko, CPP, HL, Java and PHP targets.
	 * Python support is missing.
	 */
	static function getConnection(paramStr : String) : Connection {
		var params : Array<String> = paramStr.split(",");
		return Mysql.connect({ user : params[2], pass : params[3], host : params[0], database : params[1] });
	}

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

	public static function analyse(paramStr : String) : MLD {
		var mld : MLD = new MLD();
		var db : Connection = getConnection(paramStr);
		var database : String = paramStr.split(",")[1];
		var sqlrs : ResultSet = db.request("SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = '" + database + "'");
		for (sqlr in sqlrs) {
			mld.relations.set(sqlr.TABLE_NAME, new Relation(sqlr.TABLE_NAME));
		}
		for (r in mld.relations) {
			sqlrs = db.request("SELECT COLUMN_NAME, DATA_TYPE, COLUMN_TYPE, COLUMN_KEY, EXTRA FROM information_schema.COLUMNS " +
					"WHERE TABLE_SCHEMA = '" + database + "' AND TABLE_NAME = '" + r.name + "' ORDER BY ORDINAL_POSITION");
			for (sqlf in sqlrs) {
				if (sqlf.DATA_TYPE == "int" && sqlf.EXTRA == "auto_increment") {
					r.fields.push(new Field(sqlf.COLUMN_NAME, "COUNTER"));
				} else {
					r.fields.push(new Field(sqlf.COLUMN_NAME, sqlf.COLUMN_TYPE));
				}
				if (sqlf.COLUMN_KEY == "PRI") {
					r.key.push(sqlf.COLUMN_NAME);
				}
			}
			sqlrs = db.request("SELECT COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME  FROM information_schema.KEY_COLUMN_USAGE " +
					"WHERE TABLE_SCHEMA = '" + database + "' AND TABLE_NAME = '" + r.name + "' AND REFERENCED_TABLE_NAME IS NOT NULL");
			for (sqlfk in sqlrs) {
				var t : Relation = mld.relations.get(sqlfk.REFERENCED_TABLE_NAME);
				if (t != null) {
					r.foreignKeys.push({ name : sqlfk.COLUMN_NAME, relation : t, key : sqlfk.REFERENCED_COLUMN_NAME });
				}
			}
		}
		db.close();
		return mld;
	}
	#end
}
