using Dapper;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.ListQuery;
using SwDAL.DTO.DbServer;

namespace SwDAL.Query.DbServer;

// Required index: IX_MSWDBSERVER_TENANTID ON SW.MSWDBSERVER (TENANTID, STATUS)
public class DbServerQB : IQueryBuilder<DbServerListQuery, DbServerListDTO>
{
    // ── Sort allowlist ────────────────────────────────────────────────────
    private static readonly IReadOnlyDictionary<string, string> _sort =
        new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
        {
            ["hostname"] = "s.HOSTNAME"
        };

    // ── IQueryBuilder implementation ──────────────────────────────────────
    public (string Sql, DynamicParameters Params) Build(DbServerListQuery query, LoginDTO login, ISqlDialect dialect)
    {
        var orderBy = SqlClauses.OrderBy(
            query.Criteria.SortBy, query.Criteria.SortDesc, _sort, "s.DBSERVERID DESC");
        return BuildDbServerList(query.Criteria, dialect, orderBy);
    }

    // ── CRUD SQL constants ────────────────────────────────────────────────

    // Credentials (DBUSERNAME, DBPASSWORD) are intentionally excluded from GET_BY_ID
    // to prevent accidental exposure. Use a dedicated credential-rotation endpoint for updates.
    // TENANTID allows an exact match OR the shared platform sentinel (-1) — confirmed live,
    // tracker §31.13, that DBSERVERID rows are commonly SHARED across many clients' own
    // MSWCLIENTDATABASE rows (TENANTID=-1 on the server, the owning client's real ID on the
    // client-database row); requiring an exact match here meant a shared server could never be
    // retrieved by any tenant-scoped caller — the exact call path ChangeRequestBLL.Execute /
    // ProvisioningBLL / DmlScriptBLL all go through to reach real DB credentials.
    public const string GET_BY_ID_WITH_CREDENTIALS = @"
        SELECT s.DBSERVERID    AS DbServerId,
               s.HOSTNAME      AS HostName,
               s.PORT          AS Port,
               s.DBTYPE        AS DbType,
               s.DBUSERNAME    AS DbUsername,
               s.DBPASSWORD    AS DbPassword,
               s.SERVERDESC    AS ServerDesc,
               s.VERSION,
               s.STATUS,
               s.SORTORDER     AS SortOrder,
               s.CREATEDBYID   AS CreatedById,
               s.CREATEDON     AS CreatedOn,
               s.MODIFIEDBYID  AS ModifiedById,
               s.MODIFIEDON    AS ModifiedOn,
               s.SOURCETYPE    AS SourceType,
               s.TENANTID      AS TenantId
        FROM   SW.MSWDBSERVER s
        WHERE  s.DBSERVERID = @DbServerId
          AND  (s.TENANTID = @TenantId OR s.TENANTID = -1)";

    public const string GET_BY_ID = @"
        SELECT s.DBSERVERID    AS DbServerId,
               s.HOSTNAME      AS HostName,
               s.PORT          AS Port,
               s.DBTYPE        AS DbType,
               s.DBUSERNAME    AS DbUsername,
               s.DBPASSWORD    AS DbPassword,
               s.SERVERDESC    AS ServerDesc,
               s.VERSION       AS Version,
               s.STATUS        AS Status,
               s.SORTORDER     AS SortOrder,
               s.CREATEDBYID   AS CreatedById,
               s.CREATEDON     AS CreatedOn,
               s.MODIFIEDBYID  AS ModifiedById,
               s.MODIFIEDON    AS ModifiedOn,
               s.SOURCETYPE    AS SourceType,
               s.TENANTID      AS TenantId
        FROM   SW.MSWDBSERVER s
        WHERE  s.DBSERVERID = @DbServerId";

    public const string INSERT = @"
        INSERT INTO SW.MSWDBSERVER
            (DBSERVERID, HOSTNAME, PORT, DBTYPE, DBUSERNAME, DBPASSWORD, SERVERDESC,
             VERSION, STATUS, SORTORDER,
             CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SOURCETYPE, TENANTID)
        VALUES
            (@DbServerId, @HostName, @Port, @DbType, @DbUsername, @DbPassword, @ServerDesc,
             @Version, @Status, @SortOrder,
             @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @SourceType, @TenantId)";

    // Credentials are NOT updated here — use UPDATE_CREDENTIALS for credential rotation
    public const string UPDATE = @"
        UPDATE SW.MSWDBSERVER SET
            HOSTNAME     = @HostName,
            PORT         = @Port,
            DBTYPE       = @DbType,
            SERVERDESC   = @ServerDesc,
            STATUS       = @Status,
            SORTORDER    = @SortOrder,
            MODIFIEDBYID = @ModifiedById,
            MODIFIEDON   = @ModifiedOn
        WHERE DBSERVERID = @DbServerId AND TENANTID = @TenantId";

    public const string UPDATE_CREDENTIALS = @"
        UPDATE SW.MSWDBSERVER SET
            DBUSERNAME   = @DbUsername,
            DBPASSWORD   = @DbPassword,
            MODIFIEDBYID = @ModifiedById,
            MODIFIEDON   = @ModifiedOn
        WHERE DBSERVERID = @DbServerId AND TENANTID = @TenantId";

    public const string DELETE = @"
        DELETE FROM SW.MSWDBSERVER
        WHERE DBSERVERID = @DbServerId AND TENANTID = @TenantId AND STATUS = 1";

    public const string GET_SELECT_LIST = @" SELECT DBSERVERID   AS DbServerId, 
                                                    HOSTNAME     AS HostName,
                                                    DBUSERNAME   AS DbUserName
                                            FROM   SW.MSWDBSERVER
                                            WHERE  STATUS   = 1 
                                              AND  TENANTID = @TenantId
                                            ORDER  BY HOSTNAME, DBUSERNAME
                                            OFFSET @firstnumber ROWS
                                            FETCH  NEXT @maxresult ROWS ONLY";

    public const string GET_LIST_DBSERVER = @"SELECT
                                                DBSERVERID    AS DbServerId,
                                                HOSTNAME      AS HostName,
                                                PORT          AS Port,
                                                DBTYPE        AS DbType,
                                                DBUSERNAME    AS DbUsername,
                                                DBPASSWORD    AS DbPassword,
                                                SERVERDESC    AS ServerDesc,
                                                VERSION       AS Version,
                                                STATUS        AS Status,
                                                SORTORDER     AS SortOrder,
                                                CREATEDBYID   AS CreatedById,
                                                CREATEDON     AS CreatedOn,
                                                MODIFIEDBYID  AS ModifiedById,
                                                MODIFIEDON    AS ModifiedOn,
                                                SOURCETYPE    AS SourceType,
                                                TENANTID      AS TenantId
                                            FROM SW.MSWDBSERVER;";


    public const string GET_SERVER_HOSTNAME_LIST = @"SELECT DISTINCT
                                                        DBSERVERID AS DbServerId,
                                                        HOSTNAME AS HostName,
                                                        DBUSERNAME AS DbUsername
                                                    FROM SW.MSWDBSERVER
                                                    WHERE TENANTID = @TenantId
                                                      AND STATUS = 1
                                                      AND HOSTNAME IS NOT NULL
                                                      AND DBUSERNAME IS NOT NULL
                                                    ORDER BY HOSTNAME, DBUSERNAME;";


    // ClientDbId is the field a caller must key off of to identify a specific database — a
    // single DbServerId can back several ClientDatabase rows (Main/Report/Archive, or several
    // unrelated clients sharing one physical server registration), so DbServerId alone is
    // ambiguous here.
    public const string GET_DATABASE_LIST_BY_HOSTNAME = @"SELECT
                                                        CD.CLIENTDBID AS ClientDbId,
                                                        DS.DBSERVERID AS DbServerId,
                                                        DS.HOSTNAME   AS HostName,
                                                        DS.DBUSERNAME AS DBUserName,
                                                        DS.DBPASSWORD AS DBPassword,
                                                        CD.DATABASENAME AS DatabaseName
                                                    FROM SW.MSWDBSERVER DS
                                                    INNER JOIN SW.MSWCLIENTDATABASE CD ON DS.DBSERVERID = CD.DBSERVERID
                                                    WHERE DS.HOSTNAME = @HostName
                                                      AND DS.TENANTID = @TenantId
                                                      AND DS.STATUS = 1
                                                    ORDER BY DATABASENAME";
    // ── List builder ──────────────────────────────────────────────────────

    public static (string Sql, DynamicParameters Params) BuildDbServerList(
        DbServerListCriteria c, ISqlDialect d, string orderBy)
    {
        var ctx = new QueryContext(d).Register("dbserver", "s");

        var (whereSql, whereParams) = SqlClause.AsWhere(new[]
        {
            SqlClauses.ExactByte(ctx, c.DbType,    "dbserver", "DBTYPE"),
            SqlClauses.DocumentSearch(ctx, c.SearchText),
        });

        var (pagingSql, pagingParams) = SqlClauses.Paging(ctx, c, orderBy);

        string sql = $@"
            SELECT {d.TotalCountExpr()},
                   s.DBSERVERID  AS DbServerId,
                   s.HOSTNAME    AS HostName,
                   s.PORT        AS Port,
                   s.DBTYPE      AS DbType,
                   s.TENANTID    AS TenantId
            FROM   SW.MSWDBSERVER s
            {whereSql}
            {pagingSql}";

        return (sql, SqlClause.Merge(whereParams, pagingParams));
    }
}