using System.Data;
using System.Diagnostics.CodeAnalysis;
using Npgsql;
using NpgsqlTypes;
using ServerDatabase.Utils;

namespace ServerDatabase;

public class TableColumn<TRow, Ttype> : ITableColumn where Ttype : notnull where TRow : class, new()
{
    public TableColumn(string Name, bool IsPrimaryKey = false)
    {
        this.Name = Name;
        this.IsPrimaryKey = IsPrimaryKey;
    }
    
    public IForeignKey[] ForeignKeys { get; init; } = Array.Empty<IForeignKey>();

    public long ColumnVersion { get; set; } = 0;
    public Order GetDecendingOrder() => new Order() { Assending = false, Type = Name };
    public Order GetAssendingOrder() => new Order() { Assending = true, Type = Name };

    public Ttype DefaultValue { get; set; }

    public string Name { get; set; } = default!;
	public Type Type { get; } = typeof(Ttype);
	public bool IsPrimaryKey { get; set; } = false;
    public object? GetDefault() => DefaultValue;

    public string? GetDefaultStr()
    {
        if (DefaultValue is null) return null; 
        else if (DefaultValue is DateTime) return "CURRENT_DATE";
        else if (Type.FullName == typeof(string).FullName) return $"'{DefaultValue.ToString()}'::text";
        else if (Type.IsArray)
        {
            Type t = Type.GetElementType();
            if (t.IsEnum)
            {
                t = t.GetEnumUnderlyingType();
            }
            if (t.Name == typeof(long).Name) return $"'{{{string.Join(", ", DefaultValue as long[])}}}'::bigint[]";
            else if (t.Name == typeof(int).Name) return $"'{{{string.Join(", ", DefaultValue as int[])}}}'::integer[]";
            else if (t.Name == typeof(short).Name)
                return $"'{{{string.Join(", ", DefaultValue as short[])}}}'::smallint[]";
            else return null;
        }
        else if (Type.IsEnum)
        {
            return $"{Convert.ChangeType(DefaultValue, Type.GetEnumUnderlyingType())}";
        }
        else
        {
            return DefaultValue.ToString();
        }
    }

    private object? _defalt = null;

    public Type GetDatabaseType()
    {
        if (Type.IsEnum)
        {
            return Type.GetEnumUnderlyingType();
        }

        if (Type.IsNullableEnum())
        {
            return Nullable.GetUnderlyingType(Type)!.GetEnumUnderlyingType();
        }

        if (Type.IsArray)
        {
            Type element = Type.GetElementType()!;
            if (element.IsEnum)
            {
                return element.GetEnumUnderlyingType().MakeArrayType();
            }

            if (element.IsNullableEnum())
            {
                return Nullable.GetUnderlyingType(element)!.GetEnumUnderlyingType().MakeArrayType();
            }
        }
        
        return Type;
    }
    
    public string GetDatabaseTypeStr()
    {
        return typestrmap[GetDatabaseType()];
    }

    public Parameter CreateDefaultParameter([DisallowNull] string sign = "=") =>
        CreateParameter(DefaultValue, sign);

    public Parameter CreateParameter([DisallowNull]Ttype Value, [DisallowNull]string sign = "=")
    {
        Type t = Value.GetType();
        Tuple<DbType, NpgsqlDbType>? types;
        if (t.IsEnum)
        {
            t = t.GetEnumUnderlyingType();
            types = typemap[t];
            return new()
            {
                PGParameter = new NpgsqlParameter(Name, Convert.ChangeType(Value, t))
                {
                    DbType = types.Item1,
                    NpgsqlDbType = types.Item2
                },
                Sign = sign
            };
        }
        if (t.IsArray)
        {
            t = t.GetElementType()!;
            
            if (t.IsEnum)
            {
                t = t.GetEnumUnderlyingType();
                types = typemap[t.MakeArrayType()];
                Enum[] tempenum = Ext.Cast<Ttype, Enum>(Value);
                switch (t.FullName)
                {
                    case "System.Int64":
                        return new()
                        {
                            PGParameter = new NpgsqlParameter(Name, Array.ConvertAll(tempenum, value => Convert.ToInt64(value)))
                            {
                                DbType = types.Item1,
                                NpgsqlDbType = types.Item2
                            },
                            Sign = sign
                        };
                    case "System.Int32":
                        return new()
                        {
                            PGParameter = new NpgsqlParameter(Name, Array.ConvertAll(tempenum, value => Convert.ToInt32(value)))
                            {
                                DbType = types.Item1,
                                NpgsqlDbType = types.Item2
                            },
                            Sign = sign
                        };
                    case "System.Int16":
                        return new()
                        {
                            PGParameter = new NpgsqlParameter(Name, Array.ConvertAll(tempenum, value => Convert.ToInt16(value)))
                            {
                                DbType = types.Item1,
                                NpgsqlDbType = types.Item2
                            },
                            Sign = sign
                        };
                }
            }

            t = t.MakeArrayType();
            types = typemap[t];
        }
        types = typemap[t!];
        if (types is null)
        {
            return new()
            {
                PGParameter = new(Name, Value)
            };
        }
        return new()
        {
            PGParameter = new NpgsqlParameter(Name, Value)
            {
                DbType = types.Item1,
                NpgsqlDbType = types.Item2
            },
            Sign = sign
        };
    }

    public Parameter CreateJsonParameter([DisallowNull]Ttype value, [DisallowNull]string sign = "=")
    {
        return new()
        {
            PGParameter = new NpgsqlParameter(Name, value)
            {
                NpgsqlDbType = NpgsqlDbType.Jsonb
            },
            Sign = sign
        };
    }
    
    private readonly Dictionary<Type, Tuple<DbType, NpgsqlDbType>?> typemap = new()
    {
        {typeof(byte), new(DbType.Byte, NpgsqlDbType.Unknown)},
        {typeof(sbyte), new(DbType.SByte, NpgsqlDbType.Unknown)},
        {typeof(short), new(DbType.Int16, NpgsqlDbType.Smallint)},
        {typeof(ushort), new(DbType.UInt16, NpgsqlDbType.Smallint)},
        {typeof(int), new(DbType.Int32, NpgsqlDbType.Integer)},
        {typeof(uint), new(DbType.UInt32, NpgsqlDbType.Integer)},
        {typeof(long), new(DbType.Int64, NpgsqlDbType.Bigint)},
        {typeof(ulong), new(DbType.UInt64, NpgsqlDbType.Bigint)},
        {typeof(float), new(DbType.Single, NpgsqlDbType.Real)},
        {typeof(double), new(DbType.Double, NpgsqlDbType.Double)},
        {typeof(decimal), new(DbType.Decimal, NpgsqlDbType.Money)},
        {typeof(bool), new(DbType.Boolean, NpgsqlDbType.Boolean)},
        {typeof(string), new(DbType.String, NpgsqlDbType.Text)},
        {typeof(char), new(DbType.StringFixedLength, NpgsqlDbType.Char)},
        {typeof(Guid), new(DbType.Guid, NpgsqlDbType.Uuid)},
        {typeof(DateTime), new(DbType.DateTime, NpgsqlDbType.Date)},
        {typeof(DateTimeOffset), new(DbType.DateTimeOffset, NpgsqlDbType.TimeTz)},
        {typeof(byte[]), new(DbType.Binary, NpgsqlDbType.Bytea)},
        {typeof(byte[][]), new(DbType.Object, NpgsqlDbType.Array | NpgsqlDbType.Bytea)},
        {typeof(byte?), new(DbType.Byte, NpgsqlDbType.Unknown)},
        {typeof(sbyte?), new(DbType.SByte, NpgsqlDbType.Unknown)},
        {typeof(short?), new(DbType.Int16, NpgsqlDbType.Smallint)},
        {typeof(ushort?), new(DbType.UInt16, NpgsqlDbType.Smallint)},
        {typeof(int?), new(DbType.Int32, NpgsqlDbType.Integer)},
        {typeof(uint?), new(DbType.UInt32, NpgsqlDbType.Integer)},
        {typeof(long?), new(DbType.Int64, NpgsqlDbType.Bigint)},
        {typeof(ulong?), new(DbType.UInt64, NpgsqlDbType.Bigint)},
        {typeof(float?), new(DbType.Single, NpgsqlDbType.Real)},
        {typeof(double?), new(DbType.Double, NpgsqlDbType.Double)},
        {typeof(decimal?), new(DbType.Decimal, NpgsqlDbType.Money)},
        {typeof(bool?), new(DbType.Boolean, NpgsqlDbType.Boolean)},
        {typeof(char?), new(DbType.StringFixedLength, NpgsqlDbType.Char)},
        {typeof(Guid?), new(DbType.Guid, NpgsqlDbType.Uuid)},
        {typeof(DateTime?), new(DbType.DateTime, NpgsqlDbType.Uuid)},
        {typeof(DateTimeOffset?), new(DbType.DateTimeOffset, NpgsqlDbType.TimeTz)},
        {typeof(string[]), new(DbType.Object, NpgsqlDbType.Array | NpgsqlDbType.Text)},
        {typeof(long[]), new(DbType.Object, NpgsqlDbType.Array | NpgsqlDbType.Bigint)},
        {typeof(long?[]), new(DbType.Object, NpgsqlDbType.Array | NpgsqlDbType.Bigint)},
        {typeof(int[]), new(DbType.Object, NpgsqlDbType.Array | NpgsqlDbType.Integer)},
        {typeof(int?[]), new(DbType.Object, NpgsqlDbType.Array | NpgsqlDbType.Integer)},
        {typeof(short[]), new(DbType.Object, NpgsqlDbType.Array | NpgsqlDbType.Smallint)},
        {typeof(short?[]), new(DbType.Object, NpgsqlDbType.Array | NpgsqlDbType.Smallint)}
    };
    
    private readonly Dictionary<Type, string> typestrmap = new()
    {
        {typeof(byte), null! },
        {typeof(sbyte), null! },
        {typeof(short), "smallint" },
        {typeof(ushort), "smallint" },
        {typeof(int), "integer" },
        {typeof(uint), "integer" },
        {typeof(long), "bigint" },
        {typeof(ulong), "bigint" },
        {typeof(float), "real" },
        {typeof(double), "double precision" },
        {typeof(decimal), "money" },
        {typeof(bool), "boolean" },
        {typeof(string), "text" },
        {typeof(char), "character" },
        {typeof(Guid), "uuid" },
        {typeof(DateTime), "date" },
        {typeof(DateTimeOffset), "time with time zone" },
        {typeof(byte[]), "bytea" },
        {typeof(byte[][]), "bytea[]" },
        {typeof(byte?), null! },
        {typeof(sbyte?), null! },
        {typeof(short?), "smallint" },
        {typeof(ushort?), "smallint" },
        {typeof(int?), "integer" },
        {typeof(uint?), "integer" },
        {typeof(long?), "bigint" },
        {typeof(ulong?), "bigint" },
        {typeof(float?), "real" },
        {typeof(double?), "double precision" },
        {typeof(decimal?), "money" },
        {typeof(bool?), "boolean" },
        {typeof(char?), "character" },
        {typeof(Guid?), "uuid" },
        {typeof(DateTime?), "date" },
        {typeof(DateTimeOffset?), "time with time zone" },
        {typeof(string[]), "text[]" },
        {typeof(long[]), "bigint[]" },
        {typeof(long?[]), "bigint[]" },
        {typeof(int[]), "integer[]" },
        {typeof(int?[]), "integer[]" },
        {typeof(short[]), "smallint[]" },
        {typeof(short?[]), "smallint[]" }
    };
}