using System.Data.Common;
using FrameworkDAL.DTO.Server;

namespace FrameworkBLL.Connection
{
    /// <summary>
    /// Opens a live DbConnection to an external database using credentials from DBINSTANCE.
    /// Caller must dispose the returned connection (via await using).
    /// </summary>
    public interface IExternalDbConnectionFactory
    {
        // Opens a connection; throws InvalidOperationException for unsupported DBTypeId.
        Task<DbConnection> OpenAsync(DBInstanceDTO instance, CancellationToken ct);

        Task<bool> TestConnectionAsync(DBInstanceDTO instance, CancellationToken ct);

        // Returns column metadata for a given table on the external DB.
        // Used by SyncQueryBuilder to generate dynamic SELECT/INSERT/UPDATE SQL,
        // and by TypeNormalizer for cross-RDBMS type mapping.
        Task<IEnumerable<ExternalColumnMetaResult>> GetColumnMetaAsync(
            DbConnection conn, string schemaName, string tableName, CancellationToken ct);

        // Returns all table names in a schema — used by the UI table picker when configuring datasets.
        Task<IEnumerable<string>> GetExternalTablesAsync(
            DbConnection conn, string schemaName, CancellationToken ct);
    }

    // Inline result type for column introspection — avoids a circular DTO dependency.
    public sealed record ExternalColumnMetaResult(
        string ColumnName,
        string DataType,
        int? MaxLength,
        int? NumericPrecision,
        int? NumericScale,
        bool IsNullable,
        bool IsPrimaryKey,
        int OrdinalPosition);
}