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

namespace SwDAL.Query.ClientDatabase;

// Required index: IX_MSWCLIENTDATABASE_TENANTID ON SW.MSWCLIENTDATABASE (TENANTID, STATUS, DBSERVERID)
public class ClientDatabaseQB : IQueryBuilder<ClientDatabaseListQuery, ClientDatabaseListDTO>
{
    // ── Sort allowlist ────────────────────────────────────────────────────
    private static readonly IReadOnlyDictionary<string, string> _sort =
        new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
        {
            ["clientdbcode"] = "c.CLIENTDBCODE",
            ["clientdbname"] = "c.CLIENTDBNAME"
        };

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

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

    public const string GET_BY_ID = @"
        SELECT c.CLIENTDBID        AS ClientDbId,
               c.CLIENTDBCODE      AS ClientDbCode,
               c.CLIENTDBNAME      AS ClientDbName,
               c.DBSERVERID        AS DbServerId,
               c.DATABASENAME      AS DatabaseName,
               c.DBMODELID         AS DbModelId,
               c.CLIENTDBSTATUS    AS ClientDbStatus,
               c.DESCRIPTION       AS Description,
               c.DATABASEROLE      AS DatabaseRole,
               c.TIER              AS Tier,
               c.DELIVERYMODE      AS DeliveryMode,
               c.DBAVAULTPATH      AS DbaVaultPath,
               c.APPVAULTPATH      AS AppVaultPath,
               c.READONLYVAULTPATH AS ReadOnlyVaultPath,
               c.SERVERCONFIGID    AS ServerConfigId,
               c.VERSION,
               c.STATUS,
               c.SORTORDER         AS SortOrder,
               c.CREATEDBYID       AS CreatedById,
               c.CREATEDON         AS CreatedOn,
               c.MODIFIEDBYID      AS ModifiedById,
               c.MODIFIEDON        AS ModifiedOn,
               c.SOURCETYPE        AS SourceType,
               c.TENANTID          AS TenantId,
               s.HOSTNAME          AS DbServerHostName,
               m.DBMODELNAME       AS DbModelName
        -- DBUSERNAME/DBPASSWORD were removed from this SELECT (2026-08 live test, tracker
        -- §31.14) — SW.MSWCLIENTDATABASE genuinely has no such columns, confirmed live. This
        -- table's own per-client credentials are Vault-path-based (DBAVAULTPATH/APPVAULTPATH/
        -- READONLYVAULTPATH, already selected above), not raw stored strings — plaintext
        -- DBUSERNAME/DBPASSWORD is MSWDBSERVER's own, separate, shared-server-credential model
        -- (already flagged elsewhere as a known plaintext-storage gap, not this table's design).
        -- ClientDatabaseDTO.DBUserName/DBPassword are left in place, unused, for whoever
        -- resumes this — not this pass's call to make.
        -- MSWDBSERVER/MSWDBMODEL are commonly SHARED platform rows (TENANTID = -1, one physical
        -- server/baseline model referenced by many clients' own databases) — confirmed live,
        -- tracker §31.13. Requiring an exact TENANTID match here meant GetById could never
        -- retrieve ANY client database referencing a shared server/model (the common case),
        -- since c.TENANTID is the owning client's real ID while s/m.TENANTID stays -1. Allow
        -- either an exact match (a genuinely tenant-dedicated server/model, if one ever exists)
        -- or the shared -1 sentinel.
        FROM   SW.MSWCLIENTDATABASE c
        JOIN   SW.MSWDBSERVER  s ON s.DBSERVERID = c.DBSERVERID AND (s.TENANTID = c.TENANTID OR s.TENANTID = -1)
        JOIN   SW.MSWDBMODEL   m ON m.DBMODELID  = c.DBMODELID  AND (m.TENANTID = c.TENANTID OR m.TENANTID = -1)
        WHERE  c.CLIENTDBID = @ClientDbId
          AND  c.TENANTID   = @TenantId";

    public const string INSERT = @"
        INSERT INTO SW.MSWCLIENTDATABASE
            (CLIENTDBID, CLIENTDBCODE, CLIENTDBNAME, DBSERVERID, DATABASENAME, DBMODELID,
             CLIENTDBSTATUS, DESCRIPTION, DATABASEROLE, TIER, VERSION, STATUS, SORTORDER,
             CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SOURCETYPE, TENANTID)
        VALUES
            (@ClientDbId, @ClientDbCode, @ClientDbName, @DbServerId, @DatabaseName, @DbModelId,
             @ClientDbStatus, @Description, @DatabaseRole, @Tier, @Version, @Status, @SortOrder,
             @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @SourceType, @TenantId)";

    public const string UPDATE_CREDENTIAL_VAULT_PATHS = @"
        UPDATE SW.MSWCLIENTDATABASE SET
            DBAVAULTPATH      = @DbaVaultPath,
            APPVAULTPATH      = @AppVaultPath,
            READONLYVAULTPATH = @ReadOnlyVaultPath,
            MODIFIEDBYID      = @ModifiedById,
            MODIFIEDON        = @ModifiedOn
        WHERE CLIENTDBID = @ClientDbId AND TENANTID = @TenantId";

    public const string UPDATE_SERVER_CONFIG_ID = @"
        UPDATE SW.MSWCLIENTDATABASE SET
            SERVERCONFIGID = @ServerConfigId,
            MODIFIEDBYID   = @ModifiedById,
            MODIFIEDON     = @ModifiedOn
        WHERE CLIENTDBID = @ClientDbId AND TENANTID = @TenantId";

    // The sibling Main-role row for the same client/DbModel — Report/Archive provisioning looks
    // this up to know which MSERVERCONFIG row to point ReportServerConfigId/ArchiveServerConfigId at.
    public const string GET_MAIN_SERVER_CONFIG_ID_FOR_MODEL = @"
        SELECT SERVERCONFIGID
        FROM   SW.MSWCLIENTDATABASE
        WHERE  TENANTID     = @TenantId
          AND  DBMODELID    = @DbModelId
          AND  DATABASEROLE = 0
          AND  STATUS       = 1";

    public const string UPDATE = @"
        UPDATE SW.MSWCLIENTDATABASE SET
            CLIENTDBNAME     = @ClientDbName,
            DESCRIPTION      = @Description,
            CLIENTDBSTATUS   = @ClientDbStatus,
            STATUS           = @Status,
            SORTORDER        = @SortOrder,
            MODIFIEDBYID     = @ModifiedById,
            MODIFIEDON       = @ModifiedOn
        WHERE CLIENTDBID = @ClientDbId AND TENANTID = @TenantId";

    public const string UPDATE_STATUS = @"
        UPDATE SW.MSWCLIENTDATABASE SET
            CLIENTDBSTATUS = @ClientDbStatus,
            MODIFIEDBYID   = @ModifiedById,
            MODIFIEDON     = @ModifiedOn
        WHERE CLIENTDBID = @ClientDbId AND TENANTID = @TenantId";

    // Plain UPDATE, not routed through BaseEntityAppService.ExecuteSaveAsync — DeliveryMode is
    // operational/config metadata (which delivery channel a due rollout target uses), the same
    // posture as Tier, not a business-entity mutation needing the full save pipeline.
    public const string UPDATE_DELIVERY_MODE = @"
        UPDATE SW.MSWCLIENTDATABASE SET
            DELIVERYMODE = @DeliveryMode,
            MODIFIEDBYID = @ModifiedById,
            MODIFIEDON   = @ModifiedOn
        WHERE CLIENTDBID = @ClientDbId AND TENANTID = @TenantId";

    public const string DELETE = @"
        DELETE FROM SW.MSWCLIENTDATABASE
        WHERE CLIENTDBID    = @ClientDbId
          AND STATUS        = 1
          AND CLIENTDBSTATUS = 0";

    // Resolves the ClientDatabase row (if any) representing a raw DbServer+DatabaseName pair —
    // used to pick a least-privilege contained-user connection for schema-browse/ad-hoc query
    // paths that only carry the raw pair, not a ClientDbId. Not every DbServerId+DatabaseName
    // corresponds to a registered ClientDatabase row (e.g. exploratory browsing before
    // provisioning), so callers must treat a null result as "no contained users to route to".
    // Recommended index: IX_MSWCLIENTDATABASE_DBSERVERID_DATABASENAME ON SW.MSWCLIENTDATABASE
    // (DBSERVERID, DATABASENAME).
    public const string GET_BY_SERVER_AND_DATABASE_NAME = @"
        SELECT c.CLIENTDBID        AS ClientDbId,
               c.CLIENTDBCODE      AS ClientDbCode,
               c.CLIENTDBNAME      AS ClientDbName,
               c.DBSERVERID        AS DbServerId,
               c.DATABASENAME      AS DatabaseName,
               c.DBMODELID         AS DbModelId,
               c.CLIENTDBSTATUS    AS ClientDbStatus,
               c.DESCRIPTION       AS Description,
               c.DATABASEROLE      AS DatabaseRole,
               c.TIER              AS Tier,
               c.DELIVERYMODE      AS DeliveryMode,
               c.DBAVAULTPATH      AS DbaVaultPath,
               c.APPVAULTPATH      AS AppVaultPath,
               c.READONLYVAULTPATH AS ReadOnlyVaultPath,
               c.SERVERCONFIGID    AS ServerConfigId,
               c.VERSION,
               c.STATUS,
               c.SORTORDER         AS SortOrder,
               c.CREATEDBYID       AS CreatedById,
               c.CREATEDON         AS CreatedOn,
               c.MODIFIEDBYID      AS ModifiedById,
               c.MODIFIEDON        AS ModifiedOn,
               c.SOURCETYPE        AS SourceType,
               c.TENANTID          AS TenantId
        -- DBUSERNAME/DBPASSWORD removed — see GET_BY_ID's own comment above (tracker §31.14).
        FROM   SW.MSWCLIENTDATABASE c
        WHERE  c.DBSERVERID   = @DbServerId
          AND  c.DATABASENAME = @DatabaseName
          AND  c.TENANTID     = @TenantId";

    public const string GET_SELECT_LIST = @"SELECT CLIENTDBID   AS ClientDbId,
                                               CLIENTDBCODE AS ClientDbCode,
                                               CLIENTDBNAME AS ClientDbName,
                                               DATABASENAME AS DatabaseName,
                                               CLIENTDBSTATUS AS ClientDBStatus
                                        FROM SW.MSWCLIENTDATABASE
                                        WHERE STATUS = 1
                                        ORDER BY CLIENTDBNAME
                                        OFFSET @firstnumber ROWS
                                        FETCH NEXT @maxresult ROWS ONLY";

    public const string CHECK_DATABASE_EXISTS = @"SELECT CASE
                                                WHEN DB_ID(@DatabaseName) IS NOT NULL THEN 1
                                                ELSE 0
                                            END;";

    // ── List builder ──────────────────────────────────────────────────────

    public static (string Sql, DynamicParameters Params) BuildClientDatabaseList(
        ClientDatabaseListCriteria c, ISqlDialect d, string orderBy)
    {
        var ctx = new QueryContext(d).Register("clientdb", "c");

        var (whereSql, whereParams) = SqlClause.AsWhere(new[]
        {
            SqlClauses.ExactInt(ctx,  c.DbServerId,     "clientdb", "DBSERVERID"),
            SqlClauses.ExactInt(ctx,  c.DbModelId,      "clientdb", "DBMODELID"),
            SqlClauses.ExactByte(ctx, c.ClientDbStatus, "clientdb", "CLIENTDBSTATUS"),
            SqlClauses.ExactString(ctx, c.Tier,         "clientdb", "TIER"),
            SqlClauses.DocumentSearch(ctx, c.SearchText),
        });

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

        string sql = $@"
            SELECT {d.TotalCountExpr()},
                   c.CLIENTDBID      AS ClientDbId,
                   c.CLIENTDBCODE    AS ClientDbCode,
                   c.CLIENTDBNAME    AS ClientDbName,
                   c.DBSERVERID      AS DbServerId,
                   c.DBMODELID       AS DbModelId,
                   c.CLIENTDBSTATUS  AS ClientDbStatus,
                   c.TIER            AS Tier,
                   c.TENANTID        AS TenantId
            FROM   SW.MSWCLIENTDATABASE c
            {whereSql}
            {pagingSql}";

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