namespace GB5Shared.CriteriaConfig
{
    /// <summary>
    /// Raw SQL for the "remember my last-used filter" auto-save path (ported from GB4's
    /// CriteriaConfig.SaveCriteriaConfigFast). Self-contained here (rather than reusing
    /// FrameworkDAL.Query.CriteriaConfig.CriteriaConfigQB) because BaseReportEndpoint runs inside
    /// every module host (AccountsSL, MMSL, TMSSL, PayRollSL, AnalyticsSL), none of which reference
    /// FrameworkDAL/FrameworkBLL — TCRITERIACONFIG lives in the same tenant database every module
    /// already connects to via IQueryExecutor(LoginDTO), so no cross-service call is needed.
    /// </summary>
    internal static class CriteriaConfigFastQB
    {
        // GB4 parity: only ever replaces the CriteriaConfigType=2 ("Auto") row for this
        // (WebServiceId, UserId, OUId, MenuId) combination — one remembered filter per key, not history.
        public const string DELETE_CRITERIACONFIGATTRIBUTE_BY_KEY = @"
            DELETE FROM TCRITERIACONFIGATTRIBUTE
            WHERE CRITERIASECTIONID IN (
                SELECT CRITERIACONFIGSECTIONID FROM TCRITERIACONFIGSECTION
                WHERE CRITERIACONFIGID IN (
                    SELECT CRITERIACONFIGID FROM TCRITERIACONFIG
                    WHERE CRITERIACONFIGTYPE = 2
                      AND CRITERIASERVICEID = @webserviceid
                      AND CRITERIAUSERID = @userid
                      AND CRITERIAOUID = @ouid
                      AND CRITERIAMENUID = @menuid
                )
            );";

        public const string DELETE_CRITERIACONFIGSECTION_BY_KEY = @"
            DELETE FROM TCRITERIACONFIGSECTION
            WHERE CRITERIACONFIGID IN (
                SELECT CRITERIACONFIGID FROM TCRITERIACONFIG
                WHERE CRITERIACONFIGTYPE = 2
                  AND CRITERIASERVICEID = @webserviceid
                  AND CRITERIAUSERID = @userid
                  AND CRITERIAOUID = @ouid
                  AND CRITERIAMENUID = @menuid
            );";

        public const string DELETE_CRITERIACONFIG_BY_KEY = @"
            DELETE FROM TCRITERIACONFIG
            WHERE CRITERIACONFIGTYPE = 2
              AND CRITERIASERVICEID = @webserviceid
              AND CRITERIAUSERID = @userid
              AND CRITERIAOUID = @ouid
              AND CRITERIAMENUID = @menuid;";

        public const string SAVE_CRITERIACONFIG = @"INSERT
            INTO TCRITERIACONFIG (
                 CRITERIACONFIGID, CRITERIACONFIGNAME, CRITERIASERVICEID, CRITERIACONFIGTYPE,
                 CRITERIAUSERID, PERIODFILTER, CRITERIAMENUID, STATUS, VERSION,
                 CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, CRITERIAOUID,
                 RECORDSPERPAGE, REPORTFORMATID, DEFAULTREPORTVIEWID, SOURCETYPE, CRITERIARUNTYPE
            ) VALUES (
                 @CriteriaConfigId, @CriteriaConfigName, @CriteriaConfigCriteriaServiceId, @CriteriaConfigCriteriaConfigType,
                 @CriteriaConfigCriteriaUserId, @CriteriaConfigPeriodFilter, @CriteriaConfigCriteriaMenuId, @CriteriaConfigStatus, @CriteriaConfigVersion,
                 @CriteriaConfigCreatedById, @CriteriaConfigCreatedOn, @CriteriaConfigModifiedById, @CriteriaConfigModifiedOn, @CriteriaConfigCriteriaOUId,
                 @CriteriaConfigRecordsPerPage, @ReportFormatId, @DefaultReportViewId, @CriteriaConfigSourceType, @UserCriteriaRunType
            );";

        public const string SAVE_CRITERIACONFIGSECTION = @"INSERT
            INTO TCRITERIACONFIGSECTION (
                 CRITERIACONFIGSECTIONID, CRITERIACONFIGID, SLNO, SECTIONJOIN, STATUS, VERSION,
                 CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
            ) VALUES (
                 @CriteriaConfigSectionId, @CriteriaConfigId, @CriteriaConfigSectionSlNo, @CriteriaConfigSectionSectionJoin, @CriteriaConfigSectionStatus, @CriteriaConfigSectionVersion,
                 @CriteriaConfigSectionCreatedById, @CriteriaConfigSectionCreatedOn, @CriteriaConfigSectionModifiedById, @CriteriaConfigSectionModifiedOn
            );";

        public const string SAVE_CRITERIACONFIGATTRIBUTE = @"INSERT
            INTO TCRITERIACONFIGATTRIBUTE (
                 CRITERIACONFIGATTRIBUTEID, CRITERIASECTIONID, SLNO, CRITERIAATTRIBUTEID, OPERATIONTYPE,
                 CRITERIAFIELDVALUE, CRITERIAFIELDVALUEIN, CRITERIAJOIN, CRITERIAFIELDDISPLAYVALUE,
                 CRITERIAVARIABLEFIELD, WEBSERVICECRITERIAID
            ) VALUES (
                 @CriteriaConfigAttributeId, @CriteriaSectionId, @CriteriaConfigAttributeSlNo, @CriteriaAttributeId, @CriteriaConfigAttributeOperationType,
                 @CriteriaConfigAttributeCriteriaFieldValue, @CriteriaConfigAttributeCriteriaFieldValueIn, @CriteriaConfigAttributeCriteriaJoin, @CriteriaConfigAttributeCriteriaFieldDisplayValue,
                 @CriteriaConfigAttributeCriteriaVariableField, @WebServiceCriteriaId
            );";
    }
}