using System.Text;
using SwDAL.DTO.QueryHistory;

namespace SwDAL.Query.QueryHistory;

public static class QueryHistoryQB
{
    public const string INSERT = @"
        INSERT INTO SW.LSWQUERYHISTORY
               (QUERYTEXT, SAVEDQUERYID, ROWSRETURNED, EXECUTIONMS, ISSUCCESS, ERRORMESSAGE,
                EXECUTEDBYID, EXECUTEDON, TENANTID)
        VALUES (@QueryText, @SavedQueryId, @RowsReturned, @ExecutionMs, @IsSuccess, @ErrorMessage,
                @ExecutedById, GETUTCDATE(), @TenantId);
        SELECT CAST(SCOPE_IDENTITY() AS INT);";

    public const string GET_BY_ID = @"
        SELECT  qh.QUERYHISTORYID AS QueryHistoryId,
                qh.QUERYTEXT      AS QueryText,
                qh.SAVEDQUERYID   AS SavedQueryId,
                qh.ROWSRETURNED   AS RowsReturned,
                qh.EXECUTIONMS    AS ExecutionMs,
                qh.ISSUCCESS      AS IsSuccess,
                qh.ERRORMESSAGE   AS ErrorMessage,
                qh.EXECUTEDBYID   AS ExecutedById,
                qh.EXECUTEDON     AS ExecutedOn,
                qh.TENANTID       AS TenantId
        FROM    SW.LSWQUERYHISTORY qh
        WHERE   qh.QUERYHISTORYID = @QueryHistoryId
        AND     qh.TENANTID       = @TenantId";

    public static (string sql, string countSql) BuildQueryHistoryList(
        QueryHistoryListCriteria criteria)
    {
        var where = new StringBuilder("WHERE qh.TENANTID = @TenantId");
        if (criteria.SavedQueryId.HasValue) where.Append(" AND qh.SAVEDQUERYID = @SavedQueryId");
        if (criteria.IsSuccess.HasValue)    where.Append(" AND qh.ISSUCCESS = @IsSuccess");
        if (!string.IsNullOrWhiteSpace(criteria.SearchText))
            where.Append(" AND qh.QUERYTEXT LIKE @SearchText");

        string body = $@"
        FROM SW.LSWQUERYHISTORY qh
        {where}";

        string sql = $@"
        SELECT  qh.QUERYHISTORYID AS QueryHistoryId,
                qh.QUERYTEXT      AS QueryText,
                qh.SAVEDQUERYID   AS SavedQueryId,
                qh.ROWSRETURNED   AS RowsReturned,
                qh.EXECUTIONMS    AS ExecutionMs,
                qh.ISSUCCESS      AS IsSuccess,
                qh.ERRORMESSAGE   AS ErrorMessage,
                qh.EXECUTEDBYID   AS ExecutedById,
                qh.EXECUTEDON     AS ExecutedOn
        {body}
        ORDER BY qh.EXECUTEDON DESC
        OFFSET @PageOffset ROWS FETCH NEXT @PageSize ROWS ONLY";

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

        return (sql, countSql);
    }
}