Data-Coupler/DataConnection/DB/EF/SchemaProviders/SqlServerSchemaProvider.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;
using DataConnection.Interfaces;
using Microsoft.Data.SqlClient;

namespace DataConnection.EF.SchemaProviders;

/// <summary>
/// Provider di schema per database SQL Server
/// </summary>
public class SqlServerSchemaProvider : IDatabaseSchemaProvider
{    public async Task<IDictionary<string, IEnumerable<DbColumnInfo>>> GetDatabaseSchemaAsync(string connectionString)
    {
        var result = new Dictionary<string, IEnumerable<DbColumnInfo>>();
        
        try
        {
            using (var connection = new SqlConnection(connectionString))
            {
                await connection.OpenAsync();
                
                // Verifica se la connection string specifica già un database
                var connectionBuilder = new SqlConnectionStringBuilder(connectionString);
                var currentDatabase = connectionBuilder.InitialCatalog;
                
                if (string.IsNullOrEmpty(currentDatabase))
                {
                    // Nessun database specificato - restituisce dizionario vuoto per indicare 
                    // che è necessaria la selezione del database
                    Console.WriteLine("Nessun database specificato nella connection string. È richiesta la selezione del database.");
                    return new Dictionary<string, IEnumerable<DbColumnInfo>>();
                }
                
                Console.WriteLine($"Analizzando database: {currentDatabase}");
                
                // Prima verifichiamo se ci sono tabelle utente nel database specificato
                string testSql = "SELECT COUNT(*) FROM sys.tables WHERE is_ms_shipped = 0";
                using (var testCommand = new SqlCommand(testSql, connection))
                {
                    var scalarResult = await testCommand.ExecuteScalarAsync();
                    var tableCount = scalarResult != null ? (int)scalarResult : 0;
                    
                    Console.WriteLine($"Trovate {tableCount} tabelle utente nel database {currentDatabase}");
                    
                    if (tableCount == 0)
                    {
                        return new Dictionary<string, IEnumerable<DbColumnInfo>>(); // Restituisce dizionario vuoto
                    }
                }
                
                // Se ci sono tabelle, procediamo con la query completa
                // Query per ottenere la struttura delle tabelle in SQL Server
                string sql = @"
                    SELECT 
                        SCHEMA_NAME(t.schema_id) + '.' + t.name AS TableName,
                        c.name AS ColumnName,
                        tp.name AS DataType,
                        c.max_length,
                        c.precision,
                        c.scale,
                        c.is_nullable,
                        CASE WHEN pk.column_id IS NOT NULL THEN 1 ELSE 0 END AS IsPrimaryKey,
                        CASE WHEN fk.parent_column_id IS NOT NULL THEN 1 ELSE 0 END AS IsForeignKey,
                        SCHEMA_NAME(ref_t.schema_id) + '.' + ref_t.name AS ReferencedTable,
                        ref_c.name AS ReferencedColumn
                    FROM 
                        sys.tables t
                    INNER JOIN 
                        sys.columns c ON t.object_id = c.object_id
                    INNER JOIN 
                        sys.types tp ON c.user_type_id = tp.user_type_id
                    LEFT JOIN 
                        (SELECT 
                            ic.object_id, 
                            ic.column_id
                         FROM 
                            sys.indexes i
                         INNER JOIN 
                            sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
                         WHERE 
                            i.is_primary_key = 1) pk ON t.object_id = pk.object_id AND c.column_id = pk.column_id
                    LEFT JOIN 
                        sys.foreign_key_columns fk ON t.object_id = fk.parent_object_id AND c.column_id = fk.parent_column_id
                    LEFT JOIN 
                        sys.tables ref_t ON fk.referenced_object_id = ref_t.object_id
                    LEFT JOIN 
                        sys.columns ref_c ON fk.referenced_object_id = ref_c.object_id AND fk.referenced_column_id = ref_c.column_id
                    WHERE
                        t.is_ms_shipped = 0
                    ORDER BY 
                        TableName, c.column_id";

                using (var command = new SqlCommand(sql, connection))
                {
                    command.CommandType = CommandType.Text;
                    
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        string? currentTable = null;
                        List<DbColumnInfo>? columns = null;
                        
                        while (await reader.ReadAsync())
                        {
                            string tableName = reader.GetString(0);
                            
                            // Se stiamo passando a una nuova tabella, aggiungiamo la tabella precedente e creiamo una nuova lista
                            if (currentTable != tableName)
                            {
                                if (currentTable != null && columns != null && columns.Count > 0)
                                {
                                    result[currentTable] = columns;
                                }
                                
                                currentTable = tableName;
                                columns = new List<DbColumnInfo>();
                            }
                            
                            // Formato del tipo di dati con precisione e scala per tipi numerici o lunghezza per tipi stringa
                            string dataType = reader.GetString(2);
                            int maxLength = reader.GetInt16(3);
                            byte precision = reader.GetByte(4);
                            byte scale = reader.GetByte(5);
                            
                            // Formattazione tipo di dati per SQL Server
                            string formattedDataType = FormatSqlServerDataType(dataType, maxLength, precision, scale);
                            
                            var columnInfo = new DbColumnInfo
                            {
                                Name = reader.GetString(1),
                                DataType = formattedDataType,
                                IsNullable = reader.GetBoolean(6),
                                IsPrimaryKey = reader.GetInt32(7) == 1,
                                IsForeignKey = reader.GetInt32(8) == 1,
                                ReferencedTable = reader.IsDBNull(9) ? null : reader.GetString(9),
                                ReferencedColumn = reader.IsDBNull(10) ? null : reader.GetString(10)
                            };
                            
                            columns?.Add(columnInfo);
                        }
                          // Aggiungiamo l'ultima tabella
                        if (currentTable != null && columns != null && columns.Count > 0)
                        {
                            result[currentTable] = columns;
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Errore nel recupero dello schema del database SQL Server: {ex.Message}");
            throw;
        }
        
        return result;
    }
    
    /// <summary>
    /// Ottiene la lista dei database disponibili sul server SQL Server
    /// </summary>
    public async Task<IEnumerable<string>> GetAvailableDatabasesAsync(string connectionString)
    {
        var databases = new List<string>();
        
        try
        {
            // Crea una connection string per il server (rimuove il database specifico)
            var connectionBuilder = new SqlConnectionStringBuilder(connectionString);
            connectionBuilder.InitialCatalog = ""; // Rimuove il database specifico
            var serverConnectionString = connectionBuilder.ConnectionString;
            
            using (var connection = new SqlConnection(serverConnectionString))
            {
                await connection.OpenAsync();
                
                // Query per ottenere i database disponibili (esclude quelli di sistema)
                string sql = @"
                    SELECT name 
                    FROM sys.databases 
                    WHERE state_desc = 'ONLINE' 
                    AND name NOT IN ('master', 'tempdb', 'model', 'msdb', 'distribution')
                    AND HAS_DBACCESS(name) = 1
                    ORDER BY name";
                
                using (var command = new SqlCommand(sql, connection))
                {
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            databases.Add(reader.GetString(0));
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Errore nel recupero della lista dei database: {ex.Message}");
            throw;
        }
        
        return databases;
    }

    /// <summary>
    /// Ottiene solo la lista dei nomi delle tabelle disponibili (senza dettagli delle colonne)
    /// </summary>
    public async Task<IEnumerable<string>> GetTableNamesAsync(string connectionString)
    {
        var tableNames = new List<string>();
        
        try
        {
            using (var connection = new SqlConnection(connectionString))
            {
                await connection.OpenAsync();
                
                // Verifica se la connection string specifica già un database
                var connectionBuilder = new SqlConnectionStringBuilder(connectionString);
                var currentDatabase = connectionBuilder.InitialCatalog;
                
                if (string.IsNullOrEmpty(currentDatabase))
                {
                    Console.WriteLine("Nessun database specificato nella connection string per ottenere i nomi delle tabelle.");
                    return new List<string>();
                }
                
                Console.WriteLine($"Recuperando nomi tabelle dal database: {currentDatabase}");
                
                // Query semplice per ottenere solo i nomi delle tabelle
                string sql = @"
                    SELECT SCHEMA_NAME(t.schema_id) + '.' + t.name AS TableName
                    FROM sys.tables t
                    WHERE t.is_ms_shipped = 0
                    ORDER BY TableName";

                using (var command = new SqlCommand(sql, connection))
                {
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            tableNames.Add(reader.GetString(0));
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Errore nel recupero dei nomi delle tabelle: {ex.Message}");
            throw;
        }
        
        return tableNames;
    }
    
    /// <summary>
    /// Ottiene i dettagli delle colonne per una specifica tabella
    /// </summary>
    public async Task<IEnumerable<DbColumnInfo>> GetTableSchemaAsync(string connectionString, string tableName)
    {
        var columns = new List<DbColumnInfo>();
        
        try
        {
            using (var connection = new SqlConnection(connectionString))
            {
                await connection.OpenAsync();
                
                // Verifica se la connection string specifica già un database
                var connectionBuilder = new SqlConnectionStringBuilder(connectionString);
                var currentDatabase = connectionBuilder.InitialCatalog;
                
                if (string.IsNullOrEmpty(currentDatabase))
                {
                    Console.WriteLine("Nessun database specificato nella connection string per ottenere lo schema della tabella.");
                    return new List<DbColumnInfo>();
                }
                
                Console.WriteLine($"Recuperando schema della tabella {tableName} dal database: {currentDatabase}");
                
                // Separa schema e nome tabella
                string schemaName = "dbo";
                string tableNameOnly = tableName;
                if (tableName.Contains('.'))
                {
                    var parts = tableName.Split('.');
                    schemaName = parts[0];
                    tableNameOnly = parts[1];
                }
                
                // Query per ottenere i dettagli delle colonne di una specifica tabella
                string sql = @"
                    SELECT 
                        c.name AS ColumnName,
                        tp.name AS DataType,
                        c.max_length,
                        c.precision,
                        c.scale,
                        c.is_nullable,
                        CASE WHEN pk.column_id IS NOT NULL THEN 1 ELSE 0 END AS IsPrimaryKey,
                        CASE WHEN fk.parent_column_id IS NOT NULL THEN 1 ELSE 0 END AS IsForeignKey,
                        SCHEMA_NAME(ref_t.schema_id) + '.' + ref_t.name AS ReferencedTable,
                        ref_c.name AS ReferencedColumn
                    FROM 
                        sys.tables t
                    INNER JOIN 
                        sys.columns c ON t.object_id = c.object_id
                    INNER JOIN 
                        sys.types tp ON c.user_type_id = tp.user_type_id
                    LEFT JOIN 
                        (SELECT 
                            ic.object_id, 
                            ic.column_id
                         FROM 
                            sys.indexes i
                         INNER JOIN 
                            sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
                         WHERE 
                            i.is_primary_key = 1) pk ON t.object_id = pk.object_id AND c.column_id = pk.column_id
                    LEFT JOIN 
                        sys.foreign_key_columns fk ON t.object_id = fk.parent_object_id AND c.column_id = fk.parent_column_id
                    LEFT JOIN 
                        sys.tables ref_t ON fk.referenced_object_id = ref_t.object_id
                    LEFT JOIN 
                        sys.columns ref_c ON fk.referenced_object_id = ref_c.object_id AND fk.referenced_column_id = ref_c.column_id
                    WHERE
                        t.is_ms_shipped = 0
                        AND SCHEMA_NAME(t.schema_id) = @schemaName
                        AND t.name = @tableName
                    ORDER BY 
                        c.column_id";

                using (var command = new SqlCommand(sql, connection))
                {
                    // Aggiungi parametri per evitare SQL injection
                    var schemaParam = command.CreateParameter();
                    schemaParam.ParameterName = "@schemaName";
                    schemaParam.Value = schemaName;
                    command.Parameters.Add(schemaParam);
                    
                    var tableParam = command.CreateParameter();
                    tableParam.ParameterName = "@tableName";
                    tableParam.Value = tableNameOnly;
                    command.Parameters.Add(tableParam);
                    
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            // Formato del tipo di dati con precisione e scala per tipi numerici o lunghezza per tipi stringa
                            string dataType = reader.GetString(1);
                            int maxLength = reader.GetInt16(2);
                            byte precision = reader.GetByte(3);
                            byte scale = reader.GetByte(4);
                            
                            // Formattazione tipo di dati per SQL Server
                            string formattedDataType = FormatSqlServerDataType(dataType, maxLength, precision, scale);
                            
                            var columnInfo = new DbColumnInfo
                            {
                                Name = reader.GetString(0),
                                DataType = formattedDataType,
                                IsNullable = reader.GetBoolean(5),
                                IsPrimaryKey = reader.GetInt32(6) == 1,
                                IsForeignKey = reader.GetInt32(7) == 1,
                                ReferencedTable = reader.IsDBNull(8) ? null : reader.GetString(8),
                                ReferencedColumn = reader.IsDBNull(9) ? null : reader.GetString(9)
                            };
                            
                            columns.Add(columnInfo);
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Errore nel recupero dello schema della tabella {tableName}: {ex.Message}");
            throw;
        }
        
        return columns;
    }

    private static string FormatSqlServerDataType(string dataType, int maxLength, byte precision, byte scale)
    {
        string formattedDataType = dataType;
        
        if (dataType == "nvarchar" || dataType == "varchar" || dataType == "char" || dataType == "nchar")
        {
            if (maxLength == -1)
                formattedDataType += "(MAX)";
            else if (dataType.StartsWith("n")) // tipi Unicode - la lunghezza è in byte, dobbiamo dividerla per 2
                formattedDataType += $"({maxLength / 2})";
            else
                formattedDataType += $"({maxLength})";
        }
        else if (dataType == "decimal" || dataType == "numeric")
        {
            formattedDataType += $"({precision},{scale})";
        }
        
        return formattedDataType;
    }
}