using GB5Shared.DTO.Framework.SchemaIntrospection;
using SwDAL.DTO.ClientDatabase;
using SwDAL.DTO.DbServer;
using SwDAL.Enums;

namespace SwBLL.Provisioning;

/// <summary>
/// Executes SQL against a remote target database using credentials from DbServerDTO.
/// Never uses Integrated Security — always SqlConnectionStringBuilder with Username/Password.
/// </summary>
public interface ITargetDbExecutor
{
    /// <summary>Executes a DDL or batch script (GO-separated) against the target database. Returns rows affected.</summary>
    Task<int> ExecuteScriptAsync(string connectionString, string sql, CancellationToken ct);

    /// <summary>Executes a single parameterized DML statement (INSERT/UPDATE/DELETE). Returns rows affected.</summary>
    Task<int> ExecuteDmlAsync(string connectionString, string sql,
        Dictionary<string, object?> parameters, CancellationToken ct);

    /// <summary>
    /// Executes a SELECT-only query. Row cap: 5000. Throws if cap exceeded or non-SELECT detected.
    /// </summary>
    Task<IEnumerable<Dictionary<string, object?>>> ExecuteQueryAsync(
        string connectionString, string sql,
        Dictionary<string, object?> parameters,
        CancellationToken ct);

    /// <summary>Builds a connection string from DbServerDTO credentials. Never Integrated Security.
    /// When dbUsername/dbPassword are not supplied, falls back to the DbServer's own admin
    /// credential — server.DbPassword is decrypted internally in that case (it is stored
    /// encrypted in MSWDBSERVER).</summary>
    string BuildConnectionString(DbServerDTO server, string databaseName, string? dbUsername = null, string? dbPassword = null);

    /// <summary>Builds a connection string using the least-privilege contained-user credential
    /// for the given role (Dba/App/ReadOnly), read from the Vault path recorded on
    /// ClientDatabaseDTO. If no credential has been provisioned for that role (e.g. a client
    /// database registered outside SqlWorkbench's own provisioner), falls back to the DbServer
    /// admin credential and logs a warning.</summary>
    Task<string> BuildConnectionStringForRoleAsync(
        DbServerDTO server, ClientDatabaseDTO clientDb, ClientDbLoginRole role, CancellationToken ct);

    /// <summary>Builds a connection string pointed at the server's admin database (SQL Server:
    /// "master") rather than a specific client database — needed for RESTORE DATABASE/CREATE
    /// DATABASE/ALTER DATABASE, which must run before the target database exists or while it's
    /// not the current database context.</summary>
    string BuildAdminConnectionString(DbServerDTO server);

    /// <summary>Introspects the schema of a remote database and returns object metadata.</summary>
    Task<IEnumerable<RemoteDbObjectDTO>> GetServerObjectsAsync(
        string connectionString, CancellationToken ct);

    /// <summary>Checks whether a database with this name already exists on the server the admin
    /// connection string points at. Used before RESTORE DATABASE ... WITH REPLACE (which silently
    /// overwrites an existing database of the same name) and before CREATE DATABASE, so a naming
    /// collision is refused with a clear error instead of destroying or racing an existing database.</summary>
    Task<bool> DatabaseExistsAsync(string adminConnectionString, string databaseName, CancellationToken ct);

    /// <summary>Introspects table/column metadata for a registered client database — the schema
    /// browsing step of the Analytics Catalog Wizard. dbType follows SwDAL.Enums.DbType
    /// (0=SqlServer, 1=PostgreSQL, 2=MySQL, 3=Oracle); only SqlServer is implemented today.</summary>
    Task<IEnumerable<SchemaTableColumnDTO>> GetServerColumnsAsync(
        string connectionString, byte dbType, CancellationToken ct);

    /// <summary>Introspects foreign-key relationships for a registered client database — used by
    /// the Analytics Catalog Wizard to suggest joins between tables. dbType follows
    /// SwDAL.Enums.DbType (0=SqlServer, 1=PostgreSQL, 2=MySQL, 3=Oracle); only SqlServer is
    /// implemented today.</summary>
    Task<IEnumerable<SchemaForeignKeyDTO>> GetServerForeignKeysAsync(
        string connectionString, byte dbType, CancellationToken ct);

    /// <summary>
    /// Executes a fully self-contained, possibly multi-statement, server-assembled SQL batch
    /// (e.g. <c>SELECT ... INTO #temp; SELECT ... FROM #temp</c>) as ONE command on ONE
    /// connection, so a temp table created by an early statement in the batch remains visible to
    /// a later statement in the same batch — something <see cref="ExecuteQueryAsync"/>'s
    /// per-call-fresh-connection model cannot do.
    ///
    /// This method is for pre-validated, server-assembled SQL text built by trusted BLL code
    /// (e.g. the Analytics report-execution engine) — NEVER for raw end-user-supplied SQL. It is
    /// not exposed via any SL/FastEndpoint route and must never be.
    ///
    /// The SELECT-only guard is applied to the full batch text, same as
    /// <see cref="ExecuteQueryAsync"/>. Row cap defaults to a higher internal cap
    /// (<c>InternalBatchRowCap</c> = 100,000) than the 5,000-row <c>SelectRowCap</c> used by
    /// <see cref="ExecuteQueryAsync"/>, since this path is meant for full report execution rather
    /// than ad-hoc exploration; callers may pass <paramref name="rowCapOverride"/> to tighten it
    /// further (e.g. to honor an explicit page size).
    /// </summary>
    Task<IEnumerable<Dictionary<string, object?>>> ExecuteQueryBatchAsync(
        string connectionString, string sql, Dictionary<string, object?> parameters,
        int? rowCapOverride, CancellationToken ct);
}

/// <summary>Remote database object metadata (table, view, procedure, function).</summary>
public class RemoteDbObjectDTO
{
    public string SchemaName  { get; set; } = string.Empty;
    public string ObjectName  { get; set; } = string.Empty;
    public string ObjectType  { get; set; } = string.Empty;  // TABLE, VIEW, PROCEDURE, FUNCTION
}