using System.Text;
using SwDAL.DTO.SavedQuery;

namespace SwDAL.Query.SavedQuery;

public static class SavedQueryQB
{
    public static readonly HashSet<string> AllowedSortColumns = new(StringComparer.OrdinalIgnoreCase)
    {
        "queryname", "createdon", "isshared"
    };

    public const string GET_BY_ID = @"
        SELECT  sq.SAVEDQUERYID  AS SavedQueryId,
                sq.QUERYNAME     AS QueryName,
                sq.DESCRIPTION   AS Description,
                sq.QUERYTEXT     AS QueryText,
                sq.ISSHARED      AS IsShared,
                sq.DBSERVERID    AS DbServerId,
                sq.TENANTID      AS TenantId,
                sq.VERSION       AS Version,
                sq.STATUS        AS Status,
                sq.CREATEDBYID   AS CreatedById,
                sq.CREATEDON     AS CreatedOn,
                sq.MODIFIEDBYID  AS ModifiedById,
                sq.MODIFIEDON    AS ModifiedOn
        FROM    SW.MSWSAVEDQUERY sq
        WHERE   sq.SAVEDQUERYID = @SavedQueryId
        AND     sq.TENANTID     = @TenantId
        AND     sq.STATUS       = 1";

    public const string GET_SELECT_LIST = @"
        SELECT  sq.SAVEDQUERYID AS SavedQueryId,
                sq.QUERYNAME    AS QueryName,
                sq.DESCRIPTION  AS Description,
                sq.ISSHARED     AS IsShared,
                sq.DBSERVERID   AS DbServerId,
                sq.CREATEDON    AS CreatedOn
        FROM    SW.MSWSAVEDQUERY sq
        WHERE   sq.TENANTID = @TenantId
        AND     sq.STATUS   = 1
        AND     (sq.ISSHARED = 1 OR sq.CREATEDBYID = @UserId)
        ORDER BY sq.QUERYNAME
        OFFSET @FirstNumber ROWS FETCH NEXT @MaxResult ROWS ONLY";

    public const string INSERT = @"
        INSERT INTO SW.MSWSAVEDQUERY
               (QUERYNAME, DESCRIPTION, QUERYTEXT, ISSHARED, DBSERVERID,
                TENANTID, VERSION, STATUS, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
        VALUES (@QueryName, @Description, @QueryText, @IsShared, @DbServerId,
                @TenantId, 1, 1, @CreatedById, GETUTCDATE(), @ModifiedById, GETUTCDATE());
        SELECT CAST(SCOPE_IDENTITY() AS INT);";

    public const string UPDATE = @"
        UPDATE SW.MSWSAVEDQUERY
        SET    QUERYNAME    = @QueryName,
               DESCRIPTION  = @Description,
               QUERYTEXT    = @QueryText,
               ISSHARED     = @IsShared,
               DBSERVERID   = @DbServerId,
               VERSION      = VERSION + 1,
               MODIFIEDBYID = @ModifiedById,
               MODIFIEDON   = GETUTCDATE()
        WHERE  SAVEDQUERYID = @SavedQueryId
        AND    TENANTID     = @TenantId
        AND    STATUS       = 1";

    public const string DELETE = @"
        UPDATE SW.MSWSAVEDQUERY
        SET    STATUS       = 0,
               MODIFIEDBYID = @ModifiedById,
               MODIFIEDON   = GETUTCDATE()
        WHERE  SAVEDQUERYID = @SavedQueryId
        AND    TENANTID     = @TenantId
        AND    STATUS       = 1";

    public static (string sql, string countSql) BuildSavedQueryList(
        SavedQueryListCriteria criteria, int userId, string sortColumn, string sortDirection)
    {
        string safeSort = AllowedSortColumns.Contains(sortColumn) ? sortColumn.ToUpperInvariant() : "QUERYNAME";
        string safeDir  = sortDirection.Equals("DESC", StringComparison.OrdinalIgnoreCase) ? "DESC" : "ASC";

        var where = new StringBuilder("WHERE sq.TENANTID = @TenantId AND sq.STATUS = 1");
        where.Append(" AND (sq.ISSHARED = 1 OR sq.CREATEDBYID = @UserId)");

        if (criteria.IsShared.HasValue)   where.Append(" AND sq.ISSHARED = @IsShared");
        if (criteria.DbServerId.HasValue) where.Append(" AND sq.DBSERVERID = @DbServerId");
        if (!string.IsNullOrWhiteSpace(criteria.SearchText))
            where.Append(" AND sq.QUERYNAME LIKE @SearchText");

        string body = $@"
        FROM SW.MSWSAVEDQUERY sq
        {where}";

        string sql = $@"
        SELECT  sq.SAVEDQUERYID AS SavedQueryId,
                sq.QUERYNAME    AS QueryName,
                sq.DESCRIPTION  AS Description,
                sq.ISSHARED     AS IsShared,
                sq.DBSERVERID   AS DbServerId,
                sq.CREATEDON    AS CreatedOn
        {body}
        ORDER BY sq.{safeSort} {safeDir}
        OFFSET @PageOffset ROWS FETCH NEXT @PageSize ROWS ONLY";

        string countSql = $"SELECT COUNT(1) {body}";

        return (sql, countSql);
    }
}