namespace SwDAL.Query.UserResourceRole;

// Required indexes: UX_MSWURR_USER_SERVER/UX_MSWURR_USER_CLIENTDB (filtered unique) and
// IX_MSWURR_TENANTID ON SW.MSWUSERRESOURCEROLE — see 052_MSWUSERRESOURCEROLE.sql.
public static class UserResourceRoleQB
{
    private const string SELECT_COLUMNS = @"
        r.USERRESOURCEROLEID AS UserResourceRoleId,
        r.USERID             AS UserId,
        u.USERNAME            AS UserName,
        r.DBSERVERID          AS DbServerId,
        s.HOSTNAME            AS HostName,
        r.CLIENTDBID          AS ClientDbId,
        c.CLIENTDBCODE        AS ClientDbCode,
        r.ROLE                AS Role,
        r.STATUS,
        r.SORTORDER           AS SortOrder,
        r.CREATEDBYID         AS CreatedById,
        r.CREATEDON           AS CreatedOn,
        r.MODIFIEDBYID        AS ModifiedById,
        r.MODIFIEDON          AS ModifiedOn,
        r.SOURCETYPE          AS SourceType,
        r.TENANTID            AS TenantId";

    private const string FROM_JOINS = @"
        FROM   SW.MSWUSERRESOURCEROLE r
        LEFT JOIN MUSER               u ON u.USERID = r.USERID
        LEFT JOIN SW.MSWDBSERVER      s ON s.DBSERVERID = r.DBSERVERID
        LEFT JOIN SW.MSWCLIENTDATABASE c ON c.CLIENTDBID = r.CLIENTDBID";

    public const string GET_GRANTS_FOR_USER = $@"
        SELECT {SELECT_COLUMNS}
        {FROM_JOINS}
        WHERE  r.USERID = @UserId AND r.TENANTID = @TenantId AND r.STATUS = 1
        ORDER BY r.CREATEDON DESC";

    public const string GET_GRANTEES_FOR_SERVER = $@"
        SELECT {SELECT_COLUMNS}
        {FROM_JOINS}
        WHERE  r.DBSERVERID = @DbServerId AND r.TENANTID = @TenantId AND r.STATUS = 1
        ORDER BY r.CREATEDON DESC";

    public const string GET_GRANTEES_FOR_CLIENTDB = $@"
        SELECT {SELECT_COLUMNS}
        {FROM_JOINS}
        WHERE  r.CLIENTDBID = @ClientDbId AND r.TENANTID = @TenantId AND r.STATUS = 1
        ORDER BY r.CREATEDON DESC";

    // Most-specific-wins: a ClientDatabase-level grant always overrides that database's
    // DbServer-level grant when both exist for the same user (CASE ordering below picks the
    // ClientDb-scoped row first). Either @ClientDbId or @DbServerId may be null — a null
    // parameter never matches a NOT NULL column via '=', so passing only one target simply
    // limits the search to that dimension.
    public const string GET_EFFECTIVE_ROLE = @"
        SELECT TOP 1 r.ROLE AS Role
        FROM   SW.MSWUSERRESOURCEROLE r
        WHERE  r.USERID = @UserId AND r.TENANTID = @TenantId AND r.STATUS = 1
          AND  ((@ClientDbId IS NOT NULL AND r.CLIENTDBID = @ClientDbId)
                OR (@DbServerId IS NOT NULL AND r.DBSERVERID = @DbServerId))
        ORDER BY CASE WHEN r.CLIENTDBID IS NOT NULL THEN 0 ELSE 1 END";

    // Single statement handles both server- and database-targeted grants: ISNULL(...,-1) lets
    // the two NULL-able target columns compare equal to themselves in the match predicate
    // (SW's own established sentinel-comparison convention, e.g. ClientDatabaseQB's TENANTID
    // OR -1 pattern), matching UX_MSWURR_USER_SERVER/UX_MSWURR_USER_CLIENTDB's own filtered-
    // unique shape (exactly one of DbServerId/ClientDbId set per row).
    public const string UPSERT_GRANT = @"
        UPDATE SW.MSWUSERRESOURCEROLE SET
            ROLE         = @Role,
            MODIFIEDBYID = @ModifiedById,
            MODIFIEDON   = @ModifiedOn
        WHERE  USERID = @UserId AND TENANTID = @TenantId
          AND  ISNULL(DBSERVERID, -1) = ISNULL(@DbServerId, -1)
          AND  ISNULL(CLIENTDBID, -1) = ISNULL(@ClientDbId, -1);

        IF @@ROWCOUNT = 0
        INSERT INTO SW.MSWUSERRESOURCEROLE
            (USERRESOURCEROLEID, USERID, DBSERVERID, CLIENTDBID, ROLE,
             STATUS, SORTORDER, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SOURCETYPE, TENANTID)
        VALUES
            (@UserResourceRoleId, @UserId, @DbServerId, @ClientDbId, @Role,
             1, 9999, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, 5, @TenantId)";

    // A hard delete, not a soft STATUS=0 flag — a soft-deleted row would still satisfy
    // UX_MSWURR_USER_SERVER/UX_MSWURR_USER_CLIENTDB's filtered-unique constraint (STATUS isn't
    // part of the filter) and block re-granting the same user/resource pair. "Revoke" means
    // the grant no longer exists, so a real delete is both simpler and correct here.
    public const string REVOKE_GRANT = @"
        DELETE FROM SW.MSWUSERRESOURCEROLE
        WHERE  USERID = @UserId AND TENANTID = @TenantId
          AND  ISNULL(DBSERVERID, -1) = ISNULL(@DbServerId, -1)
          AND  ISNULL(CLIENTDBID, -1) = ISNULL(@ClientDbId, -1)";
}