using Npgsql;
using ServerDatabase.Utils;

namespace ServerDatabase;

public sealed class Database
{
    internal string ConectionString
    {
        get
        {
            return $"SSL Mode=Disable;Persist Security Info=True;Password={PW};Username={Uname};CommandTimeout={CMD};Database={DB};Host={IP}{CName}";
        }
    }

    public List<IBetterTable> Tables { get; } = new()
    {
    };

    internal string? IP = null, DB = null, Uname = null, PW = null, CName = "";
    internal int CMD { get; set; } = 20;

    public Database(string IpAddress, string Database, string Username, string Password, string? CustomeName = null, int CommandTimeout = 20)
    {
		if (!string.IsNullOrWhiteSpace(IpAddress)) IP = IpAddress;
		if (!string.IsNullOrWhiteSpace(Database)) DB = Database;
		if (!string.IsNullOrWhiteSpace(Username)) Uname = Username;
		if (!string.IsNullOrWhiteSpace(Password)) PW = Password;
		if (!string.IsNullOrWhiteSpace(CustomeName)) CName = $";ApplicationName={CustomeName}";
		else CName = "";
		VersionsTable = new TableVersions(this);
        CMD = CommandTimeout;
	}

    public void RegisterTables()
    {
	    foreach (IBetterTable Table in Tables)
	    {
		    foreach (ITableColumn Column in Table.Colums)
		    {
			    VersionsTable.WithColumn(new TableColumn<TableVersions.blankrow, long>($"{Table.Name}_{Column.Name}")
			    {
				    DefaultValue = Column.ColumnVersion
			    }, string.Empty);
		    }
	    }
	    VersionsTable.UpdateTable();
	    foreach (IBetterTable Table in Tables)
	    {
		    Table.UpdateTable();
	    }
    }

    public TableVersions VersionsTable { get; } = null!;

    public void CreateTable(string table, params ITableColumn[] Parameters)
	{
		if (string.IsNullOrEmpty(DB) || string.IsNullOrEmpty(IP) || string.IsNullOrEmpty(Uname) || string.IsNullOrEmpty(PW)) throw new Exception("Database conection not fully defined");
		using NpgsqlConnection con = new(ConectionString);
		con.Open();
		using NpgsqlCommand cmd = new();
		cmd.Connection = con;
		string command = $"CREATE TABLE public.{table}(";
		string key = "";
		foreach (ITableColumn column in Parameters)
		{
			command += $"{column.Name} {column.GetDatabaseTypeStr()}";
			if (column.IsPrimaryKey) key = $"PRIMARY KEY ({column.Name})";
			command += " NOT NULL, ";
		}
		if (key == "") command = command.Remove(command.Length - 2, 2) + ");";
		command += $"{key});";
		cmd.CommandText = command;
		cmd.Prepare();
		cmd.ExecuteNonQuery();
		con.Close();
	}
    
	public NpgsqlConnection CreateConnection()
    {
        if (string.IsNullOrEmpty(DB) || string.IsNullOrEmpty(IP) || string.IsNullOrEmpty(Uname) || string.IsNullOrEmpty(PW)) throw new Exception("Database conection not fully defined");
        return new NpgsqlConnection(ConectionString);
    }
	
	public void ExecuteNonQuery(string command)
	{
		if (string.IsNullOrEmpty(DB) || string.IsNullOrEmpty(IP) || string.IsNullOrEmpty(Uname) || string.IsNullOrEmpty(PW)) throw new Exception("Database conection not fully defined");
		using NpgsqlConnection connection = new(ConectionString);
		connection.Open();
		using NpgsqlCommand cmd = new();
		cmd.Connection = connection;
		cmd.CommandText = command;
		cmd.ExecuteNonQuery();
		connection.Close();
	}
	
	public T Read<T>(string command, Order? order = null, params Parameter[] Parameters)
	{
		if (string.IsNullOrEmpty(DB) || string.IsNullOrEmpty(IP) || string.IsNullOrEmpty(Uname) || string.IsNullOrEmpty(PW)) throw new Exception("Database conection not fully defined");
		using NpgsqlConnection con = new(ConectionString);
		con.Open();
		using NpgsqlCommand cmd = new();
		cmd.Connection = con;
		string vals = "";
		foreach (Parameter param in Parameters)
		{
			vals += $"{param.PGParameter.ParameterName} {param.Sign} @{param.PGParameter.ParameterName} AND ";
			cmd.Parameters.Add(param.PGParameter);
		}
		if (command.EndsWith(";")) command = command.Remove(command.Length - 1, 1);
		if (!string.IsNullOrWhiteSpace(vals)) vals = vals.Remove(vals.Length - 5, 5);
		if (!string.IsNullOrWhiteSpace(vals) && command.EndsWith("WHERE")) command += $" {vals}";
		if (order is not null) command += $" ORDER BY {order.Type} ";
		if (order is not null && order.Assending) command += "ASC";
		else { if (order is not null) command += "DESC"; }
		command += ";";
		cmd.CommandText = command;
		object? temp = cmd.ExecuteScalar();
		con.Close();
		if (temp is DBNull || temp is null) return default!;
		if (typeof(T).IsEnum) return (T?)Enum.Parse(typeof(T), temp.ToString()!)!;
		if (typeof(T).IsNullableEnum()) return (T?)Enum.Parse(Nullable.GetUnderlyingType(typeof(T))!, temp.ToString()!)!;
		return (T?)temp!;
	}
	
	public (T, T2) Read<T, T2>(string command, Order? order = null, params Parameter[] Parameters)
	{
		if (string.IsNullOrEmpty(DB) || string.IsNullOrEmpty(IP) || string.IsNullOrEmpty(Uname) || string.IsNullOrEmpty(PW)) throw new Exception("Database conection not fully defined");
		using NpgsqlConnection con = new(ConectionString);
		con.Open();
		using NpgsqlCommand cmd = new();
		cmd.Connection = con;
		string vals = "";
		foreach (Parameter param in Parameters)
		{
			vals += $"{param.PGParameter.ParameterName} {param.Sign} @{param.PGParameter.ParameterName} AND ";
			cmd.Parameters.Add(param.PGParameter);
		}
		if (command.EndsWith(";")) command = command.Remove(command.Length - 1, 1);
		if (!string.IsNullOrWhiteSpace(vals)) vals = vals.Remove(vals.Length - 5, 5);
		if (!string.IsNullOrWhiteSpace(vals) && command.EndsWith("WHERE")) command += $" {vals}";
		if (order is not null) command += $" ORDER BY {order.Type} ";
		if (order is not null && order.Assending) command += "ASC";
		else { if (order is not null) command += "DESC"; }
		command += ";";
		cmd.CommandText = command;
		NpgsqlDataReader reader = cmd.ExecuteReader();
		reader.Read();
		object?[] temp = new object[reader.FieldCount];
		for (int i = 0; i < reader.FieldCount; i++)
		{
			string colname = reader.GetName(i);
			object val = reader.GetValue(i);
			if (i == 0)
			{
				if (typeof(T).IsEnum) temp[i] = (T?)Enum.Parse(typeof(T), val.ToString()!)!;
				if (typeof(T).IsNullableEnum()) temp[i] =  (T?)Enum.Parse(Nullable.GetUnderlyingType(typeof(T))!, val.ToString()!)!;
			}
			else if (i == 1)
			{
				if (typeof(T2).IsEnum) temp[i] = (T2?)Enum.Parse(typeof(T2), val.ToString()!)!;
				if (typeof(T2).IsNullableEnum()) temp[i] =  (T2?)Enum.Parse(Nullable.GetUnderlyingType(typeof(T2))!, val.ToString()!)!;
			}
			if (temp[i] is null) temp[i] = val;
		}
		con.Close();
		return ((T)temp[0]!, (T2)temp[1]!);
	}
	
	public (T, T2, T3) Read<T, T2, T3>(string command, Order? order = null, params Parameter[] Parameters)
	{
		if (string.IsNullOrEmpty(DB) || string.IsNullOrEmpty(IP) || string.IsNullOrEmpty(Uname) || string.IsNullOrEmpty(PW)) throw new Exception("Database conection not fully defined");
		using NpgsqlConnection con = new(ConectionString);
		con.Open();
		using NpgsqlCommand cmd = new();
		cmd.Connection = con;
		string vals = "";
		foreach (Parameter param in Parameters)
		{
			vals += $"{param.PGParameter.ParameterName} {param.Sign} @{param.PGParameter.ParameterName} AND ";
			cmd.Parameters.Add(param.PGParameter);
		}
		if (command.EndsWith(";")) command = command.Remove(command.Length - 1, 1);
		if (!string.IsNullOrWhiteSpace(vals)) vals = vals.Remove(vals.Length - 5, 5);
		if (!string.IsNullOrWhiteSpace(vals) && command.EndsWith("WHERE")) command += $" {vals}";
		if (order is not null) command += $" ORDER BY {order.Type} ";
		if (order is not null && order.Assending) command += "ASC";
		else { if (order is not null) command += "DESC"; }
		command += ";";
		cmd.CommandText = command;
		NpgsqlDataReader reader = cmd.ExecuteReader();
		reader.Read();
		object?[] temp = new object[reader.FieldCount];
		for (int i = 0; i < reader.FieldCount; i++)
		{
			string colname = reader.GetName(i);
			object val = reader.GetValue(i);
			if (i == 0)
			{
				if (typeof(T).IsEnum) temp[i] = (T?)Enum.Parse(typeof(T), val.ToString()!)!;
				if (typeof(T).IsNullableEnum()) temp[i] =  (T?)Enum.Parse(Nullable.GetUnderlyingType(typeof(T))!, val.ToString()!)!;
			}
			else if (i == 1)
			{
				if (typeof(T2).IsEnum) temp[i] = (T2?)Enum.Parse(typeof(T2), val.ToString()!)!;
				if (typeof(T2).IsNullableEnum()) temp[i] =  (T2?)Enum.Parse(Nullable.GetUnderlyingType(typeof(T2))!, val.ToString()!)!;
			}
			else if (i == 2)
			{
				if (typeof(T3).IsEnum) temp[i] = (T3?)Enum.Parse(typeof(T3), val.ToString()!)!;
				if (typeof(T3).IsNullableEnum()) temp[i] =  (T3?)Enum.Parse(Nullable.GetUnderlyingType(typeof(T3))!, val.ToString()!)!;
			}
			if (temp[i] is null) temp[i] = val;
		}
		con.Close();
		return ((T)temp[0]!, (T2)temp[1]!, (T3)temp[2]!);
	}
	
	public (T, T2, T3)[] ReadColumns<T, T2, T3>(string command, Order? order = null, params Parameter[] Parameters)
	{
		if (string.IsNullOrEmpty(DB) || string.IsNullOrEmpty(IP) || string.IsNullOrEmpty(Uname) || string.IsNullOrEmpty(PW)) throw new Exception("Database conection not fully defined");
		using NpgsqlConnection con = new(ConectionString);
		con.Open();
		using NpgsqlCommand cmd = new();
		cmd.Connection = con;
		string vals = "";
		foreach (Parameter param in Parameters)
		{
			vals += $"{param.PGParameter.ParameterName} {param.Sign} @{param.PGParameter.ParameterName} AND ";
			cmd.Parameters.Add(param.PGParameter);
		}
		if (command.EndsWith(";")) command = command.Remove(command.Length - 1, 1);
		if (!string.IsNullOrWhiteSpace(vals)) vals = vals.Remove(vals.Length - 5, 5);
		if (!string.IsNullOrWhiteSpace(vals) && command.EndsWith("WHERE")) command += $" {vals}";
		if (order is not null) command += $" ORDER BY {order.Type} ";
		if (order is not null && order.Assending) command += "ASC";
		else { if (order is not null) command += "DESC"; }
		command += ";";
		cmd.CommandText = command;
		NpgsqlDataReader reader = cmd.ExecuteReader();
		List<(T, T2, T3)> l = new();
		while (reader.Read())
		{
			object?[] temp = new object[reader.FieldCount];
			for (int i = 0; i < reader.FieldCount; i++)
			{
				object val = reader.GetValue(i);
				if (i == 0)
				{
					if (typeof(T).IsEnum) temp[i] = (T?)Enum.Parse(typeof(T), val.ToString()!)!;
					if (typeof(T).IsNullableEnum()) temp[i] =  (T?)Enum.Parse(Nullable.GetUnderlyingType(typeof(T))!, val.ToString()!)!;
				}
				else if (i == 1)
				{
					if (typeof(T2).IsEnum) temp[i] = (T2?)Enum.Parse(typeof(T2), val.ToString()!)!;
					if (typeof(T2).IsNullableEnum()) temp[i] =  (T2?)Enum.Parse(Nullable.GetUnderlyingType(typeof(T2))!, val.ToString()!)!;
				}
				else if (i == 2)
				{
					if (typeof(T3).IsEnum) temp[i] = (T3?)Enum.Parse(typeof(T3), val.ToString()!)!;
					if (typeof(T3).IsNullableEnum()) temp[i] =  (T3?)Enum.Parse(Nullable.GetUnderlyingType(typeof(T3))!, val.ToString()!)!;
				}
				if (temp[i] is null) temp[i] = val;
			}
			l.Add(new((T)temp[0]!, (T2)temp[1]!, (T3)temp[2]!));
		}
		
		con.Close();
		return l.ToArray();
	}
}