using System.Data.Common;
using Dapper;
using FrameworkDAL.DTO.Server;
using Microsoft.Data.SqlClient;
using Npgsql;

namespace FrameworkBLL.Connection
{
    /// <summary>
    /// Resolves a DbConnection driver by DBINSTANCE.DBTypeId:
    ///   1 = SQL Server (Microsoft.Data.SqlClient)
    ///   2 = PostgreSQL  (Npgsql)
    ///   MySQL / Oracle  = Phase 4
    /// </summary>
    public class ExternalDbConnectionFactory : IExternalDbConnectionFactory
    {
        // DBTypeId constants (from MDBTYPE seed data)
        private const int DbTypeSqlServer  = 1;
        private const int DbTypePostgreSQL = 2;

        public async Task<DbConnection> OpenAsync(DBInstanceDTO instance, CancellationToken ct)
        {
            var conn = CreateConnection(instance);
            await conn.OpenAsync(ct).ConfigureAwait(false);
            return conn;
        }

        public async Task<bool> TestConnectionAsync(DBInstanceDTO instance, CancellationToken ct)
        {
            try
            {
                await using var conn = CreateConnection(instance);
                await conn.OpenAsync(ct).ConfigureAwait(false);
                return true;
            }
            catch
            {
                return false;
            }
        }

        public async Task<IEnumerable<ExternalColumnMetaResult>> GetColumnMetaAsync(
            DbConnection conn, string schemaName, string tableName, CancellationToken ct)
        {
            // INFORMATION_SCHEMA is supported by SQL Server, PostgreSQL, and MySQL.
            const string sql = @"
                SELECT
                    c.COLUMN_NAME                                                  AS ColumnName,
                    c.DATA_TYPE                                                    AS DataType,
                    c.CHARACTER_MAXIMUM_LENGTH                                     AS MaxLength,
                    c.NUMERIC_PRECISION                                            AS NumericPrecision,
                    c.NUMERIC_SCALE                                                AS NumericScale,
                    CASE WHEN c.IS_NULLABLE = 'YES' THEN 1 ELSE 0 END             AS IsNullable,
                    CASE WHEN kcu.COLUMN_NAME IS NOT NULL THEN 1 ELSE 0 END       AS IsPrimaryKey,
                    c.ORDINAL_POSITION                                             AS OrdinalPosition
                FROM INFORMATION_SCHEMA.COLUMNS c
                LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
                    ON  kcu.TABLE_SCHEMA = c.TABLE_SCHEMA
                    AND kcu.TABLE_NAME   = c.TABLE_NAME
                    AND kcu.COLUMN_NAME  = c.COLUMN_NAME
                    AND EXISTS (
                        SELECT 1
                        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
                        WHERE tc.TABLE_SCHEMA      = kcu.TABLE_SCHEMA
                          AND tc.TABLE_NAME        = kcu.TABLE_NAME
                          AND tc.CONSTRAINT_NAME   = kcu.CONSTRAINT_NAME
                          AND tc.CONSTRAINT_TYPE   = 'PRIMARY KEY'
                    )
                WHERE c.TABLE_SCHEMA = @SchemaName
                  AND c.TABLE_NAME   = @TableName
                ORDER BY c.ORDINAL_POSITION";

            var rows = await conn.QueryAsync(
                new CommandDefinition(sql, new { SchemaName = schemaName, TableName = tableName },
                    cancellationToken: ct))
                .ConfigureAwait(false);

            return rows.Select(r => new ExternalColumnMetaResult(
                ColumnName:       (string)r.ColumnName,
                DataType:         (string)r.DataType,
                MaxLength:        (int?)r.MaxLength,
                NumericPrecision: (int?)r.NumericPrecision,
                NumericScale:     (int?)r.NumericScale,
                IsNullable:       (bool)(r.IsNullable == 1),
                IsPrimaryKey:     (bool)(r.IsPrimaryKey == 1),
                OrdinalPosition:  (int)r.OrdinalPosition));
        }

        public async Task<IEnumerable<string>> GetExternalTablesAsync(
            DbConnection conn, string schemaName, CancellationToken ct)
        {
            const string sql = @"
                SELECT TABLE_NAME
                FROM   INFORMATION_SCHEMA.TABLES
                WHERE  TABLE_SCHEMA = @SchemaName
                  AND  TABLE_TYPE   = 'BASE TABLE'
                ORDER  BY TABLE_NAME";

            var rows = await conn.QueryAsync<string>(
                new CommandDefinition(sql, new { SchemaName = schemaName }, cancellationToken: ct))
                .ConfigureAwait(false);
            return rows;
        }

        // ── Private ──────────────────────────────────────────────────────────

        private static DbConnection CreateConnection(DBInstanceDTO instance)
        {
            return instance.DBTypeId switch
            {
                DbTypeSqlServer  => BuildSqlServerConnection(instance),
                DbTypePostgreSQL => BuildPostgresConnection(instance),
                3 => throw new NotSupportedException(
                    $"MySQL driver (DBTypeId=3) support is scheduled for Phase 4. " +
                    "Add MySqlConnector package and implement BuildMySqlConnection()."),
                4 => throw new NotSupportedException(
                    $"Oracle driver (DBTypeId=4) support is scheduled for Phase 4. " +
                    "Add Oracle.ManagedDataAccess.Core package and implement BuildOracleConnection()."),
                _ => throw new InvalidOperationException(
                    $"Unsupported DBTypeId {instance.DBTypeId} ('{instance.DBTypeName}').")
            };
        }

        private static SqlConnection BuildSqlServerConnection(DBInstanceDTO instance)
        {
            var csb = new SqlConnectionStringBuilder
            {
                DataSource             = instance.ServerIp,
                InitialCatalog         = instance.DBInstanceDataBase,
                UserID                 = instance.DBInstanceUserName,
                Password               = instance.DBInstancePassword,
                TrustServerCertificate = true,
                ConnectTimeout         = 30
            };
            return new SqlConnection(csb.ConnectionString);
        }

        private static NpgsqlConnection BuildPostgresConnection(DBInstanceDTO instance)
        {
            var csb = new NpgsqlConnectionStringBuilder
            {
                Host     = instance.ServerIp,
                Database = instance.DBInstanceDataBase,
                Username = instance.DBInstanceUserName,
                Password = instance.DBInstancePassword,
                Timeout  = 30
            };
            return new NpgsqlConnection(csb.ConnectionString);
        }
    }
}