namespace GB5Shared.GOP.GBQueryExecutor
{
    // ============================================================
    // GBQueryExecutorQB — Safe User-Configured Query Executor
    //
    // Tables (ALL UPPERCASE per CLAUDE-DB-SCHEMA.md):
    //   MGBCONFIGUREDQUERY        — tenant-scoped query library
    //   MGBQUERYINTENTPROFILE     — global intent definitions (no TENANTID — seeded)
    //   LGBQUERYEXECUTIONLOG      — append-only execution audit (IDENTITY PK)
    //
    // Security: TENANTID = @TenantId mandatory on MGBCONFIGUREDQUERY queries.
    //
    // Required indexes:
    //   UQ_MGBCONFIGUREDQUERY_CODE_TID  (TENANTID, QUERYCODE) — already in DDL as unique constraint
    // ============================================================
    public static class GBQueryExecutorQB
    {
        // ── Configured Query ──────────────────────────────────────

        public const string GET_CONFIGURED_QUERY_BY_CODE = @"
            SELECT  cq.TENANTID             AS TenantId,
                    cq.CONFIGUREDQUERYID    AS ConfiguredQueryId,
                    cq.QUERYCODE            AS QueryCode,
                    cq.QUERYNAME            AS QueryName,
                    cq.SQLTEMPLATE          AS SqlTemplate,
                    cq.PARAMETERJSON        AS ParameterJson,
                    cq.RESULTMAPPINGJSON    AS ResultMappingJson,
                    1 - cq.ISVALIDATED      AS IsValidated,
                    cq.INTENTPROFILEID      AS IntentProfileId,
                    cq.STATUS               AS Status
            FROM    MGBCONFIGUREDQUERY cq
            WHERE   cq.TENANTID  = @TenantId
              AND   cq.QUERYCODE = @QueryCode
              AND   cq.STATUS    = 1;";

        public const string GET_CONFIGURED_QUERY_BY_ID = @"
        SELECT  cq.TENANTID             AS TenantId,
                    cq.CONFIGUREDQUERYID    AS ConfiguredQueryId,
                    cq.QUERYCODE            AS QueryCode,
                    cq.QUERYNAME            AS QueryName,
                    cq.SQLTEMPLATE          AS SqlTemplate,
                    cq.PARAMETERJSON        AS ParameterJson,
                    cq.RESULTMAPPINGJSON    AS ResultMappingJson,
                    1 - cq.ISVALIDATED      AS IsValidated,
                    cq.INTENTPROFILEID      AS IntentProfileId,
                    qi.INTENTCODE  as IntentCode, 
                    cq.STATUS               AS Status

            FROM    MGBCONFIGUREDQUERY cq , MGBQUERYINTENTPROFILE qi
            WHERE   cq.TENANTID         = @TenantId
              AND   cq.CONFIGUREDQUERYID = @ConfiguredQueryId
              AND   cq.INTENTPROFILEID = qi.INTENTPROFILEID;";

        // CONFIGUREDQUERYID is a live IDENTITY column on GB5DEMO (found via direct inspection --
        // conflicts with an earlier assumption of an AutoNumber-assigned PK like sibling GOP
        // tables). Insert lets IDENTITY assign the PK and reads it back via OUTPUT INSERTED.
        public const string SAVE_CONFIGURED_QUERY = @"
            INSERT INTO MGBCONFIGUREDQUERY
                (QUERYCODE, QUERYNAME, SQLTEMPLATE, PARAMETERJSON,
                 RESULTMAPPINGJSON, ISVALIDATED, INTENTPROFILEID,
                 VERSION, STATUS, SORTORDER, SOURCETYPE,
                 CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, TENANTID)
            OUTPUT INSERTED.CONFIGUREDQUERYID
            VALUES
                (@QueryCode, @QueryName, @SqlTemplate, @ParameterJson,
                 @ResultMappingJson, 1, @IntentProfileId,
                 1, 1, 0, @SourceType,
                 @CreatedById, GETDATE(), @ModifiedById, GETDATE(), @TenantId);";

        public const string UPDATE_CONFIGURED_QUERY = @"
            UPDATE MGBCONFIGUREDQUERY
            SET    QUERYNAME         = @QueryName,
                   SQLTEMPLATE       = @SqlTemplate,
                   PARAMETERJSON     = @ParameterJson,
                   RESULTMAPPINGJSON = @ResultMappingJson,
                   ISVALIDATED       = 1,             -- reset validation on edit (1=No/Not validated)
                   INTENTPROFILEID   = @IntentProfileId,
                   MODIFIEDBYID      = @ModifiedById,
                   MODIFIEDON        = GETDATE(),
                   VERSION           = VERSION + 1
            WHERE  CONFIGUREDQUERYID = @ConfiguredQueryId
              AND  TENANTID          = @TenantId;";

        public const string MARK_QUERY_VALIDATED = @"
            UPDATE MGBCONFIGUREDQUERY
            SET    ISVALIDATED  = 0,
                   MODIFIEDBYID = @ModifiedById,
                   MODIFIEDON   = GETDATE()
            WHERE  CONFIGUREDQUERYID = @ConfiguredQueryId
              AND  TENANTID          = @TenantId;";

        public const string DELETE_CONFIGURED_QUERY = @"
            UPDATE MGBCONFIGUREDQUERY
            SET    STATUS        = 2,
                   MODIFIEDBYID  = @ModifiedById,
                   MODIFIEDON    = GETDATE()
            WHERE  CONFIGUREDQUERYID = @ConfiguredQueryId
              AND  TENANTID          = @TenantId;";

        public const string GET_CONFIGURED_QUERY_LIST = @"
            SELECT  cq.TENANTID             AS TenantId,
                    cq.CONFIGUREDQUERYID    AS ConfiguredQueryId,
                    cq.QUERYCODE            AS QueryCode,
                    cq.QUERYNAME            AS QueryName,
                    1 - cq.ISVALIDATED      AS IsValidated,
                    cq.INTENTPROFILEID      AS IntentProfileId,
                    ip.INTENTCODE           AS IntentCode,
                    cq.STATUS               AS Status
            FROM    MGBCONFIGUREDQUERY cq
            LEFT JOIN MGBQUERYINTENTPROFILE ip
                    ON ip.INTENTPROFILEID = cq.INTENTPROFILEID
            WHERE   cq.TENANTID = @TenantId
              AND   cq.STATUS  <> 2
            ORDER BY cq.QUERYCODE;";

        // /*CRITERIA*/ placeholder — replaced at runtime by CriteriaBuilder.Build() with
        // caller-supplied AND-ed conditions (see GBQueryExecutorDAL.GetSelectListConfiguredQuery).
        public const string GET_CONFIGURED_QUERY_SELECT_LIST = @"
            SELECT  cq.CONFIGUREDQUERYID    AS ConfiguredQueryId,
                    cq.TENANTID             AS TenantId,
                    cq.QUERYCODE            AS QueryCode,
                    cq.QUERYNAME            AS QueryName,
                    1 - cq.ISVALIDATED      AS IsValidated,
                    cq.INTENTPROFILEID      AS IntentProfileId,
                    ip.INTENTCODE           AS IntentCode,
                    cq.STATUS               AS Status
            FROM    MGBCONFIGUREDQUERY cq
            LEFT JOIN MGBQUERYINTENTPROFILE ip
                    ON ip.INTENTPROFILEID = cq.INTENTPROFILEID
            WHERE   cq.TENANTID = @TenantId
              AND   cq.STATUS  <> 2 /*CRITERIA*/
            ORDER BY cq.QUERYCODE;";

        // ── Intent Profile ────────────────────────────────────────
        // No TENANTID filter — intent profiles are global/seeded.

        public const string GET_INTENT_PROFILE_BY_ID = @"
            SELECT  ip.INTENTPROFILEID      AS IntentProfileId,
                    ip.INTENTCODE           AS IntentCode,
                    ip.QUERYINTENT          AS QueryIntent,
                    ip.HARDROWCAP           AS HardRowCap,
                    ip.HARDTIMEOUTMS        AS HardTimeoutMs,
                    ip.SOFTROWCAP           AS SoftRowCap,
                    ip.SOFTTIMEOUTMS        AS SoftTimeoutMs,
                    ip.JOINSDEPTHALLOWED    AS JoinsDepthAllowed,
                    ip.REQUIRESPERMISSION   AS RequiresPermission,
                    1 - ip.REQUIRESDUALAPPROVAL AS RequiresDualApproval,
                    ip.STATUS               AS Status
            FROM    MGBQUERYINTENTPROFILE ip
            WHERE   ip.INTENTPROFILEID = @IntentProfileId
              AND   ip.STATUS          = 1;";

        // /*CRITERIA*/ placeholder — replaced at runtime by CriteriaBuilder.Build() with
        // caller-supplied AND-ed conditions (see GBQueryExecutorDAL.GetSelectListIntentProfiles).
        // Base filter excludes only soft-deleted rows (STATUS <> 2), matching
        // GET_CONFIGURED_QUERY_SELECT_LIST's pattern -- Status is whitelisted as a caller
        // filter field, so a hardcoded "STATUS = 1" here would AND against any
        // caller-supplied Status criteria and silently return zero rows on a mismatch.
        public const string GET_INTENT_PROFILE_SELECT_LIST = @"
            SELECT  ip.INTENTPROFILEID  AS IntentProfileId,
                    ip.INTENTCODE       AS IntentCode,
                    ip.QUERYINTENT      AS QueryIntent,
                    ip.STATUS           AS Status
            FROM    MGBQUERYINTENTPROFILE ip
            WHERE   ip.STATUS <> 2 /*CRITERIA*/
            ORDER BY ip.INTENTPROFILEID;";

        public const string GET_INTENT_PROFILE_BY_CODE = @"
            SELECT  ip.INTENTPROFILEID      AS IntentProfileId,
                    ip.INTENTCODE           AS IntentCode,
                    ip.QUERYINTENT          AS QueryIntent,
                    ip.HARDROWCAP           AS HardRowCap,
                    ip.HARDTIMEOUTMS        AS HardTimeoutMs,
                    ip.SOFTROWCAP           AS SoftRowCap,
                    ip.SOFTTIMEOUTMS        AS SoftTimeoutMs,
                    ip.JOINSDEPTHALLOWED    AS JoinsDepthAllowed,
                    ip.REQUIRESPERMISSION   AS RequiresPermission,
                    1 - ip.REQUIRESDUALAPPROVAL AS RequiresDualApproval,
                    ip.STATUS               AS Status
            FROM    MGBQUERYINTENTPROFILE ip
            WHERE   ip.INTENTCODE = @IntentCode
              AND   ip.STATUS     = 1;";

        // ── Execution Log ─────────────────────────────────────────
        // L table: IDENTITY PK, append-only audit.

        public const string LOG_EXECUTION = @"
            INSERT INTO LGBQUERYEXECUTIONLOG
                (CONFIGUREDQUERYID, QUERYCODE, QUERYINTENT, CALLERMODULE,
                 DURATIONMS, ROWSRETURNED, ISSUCCESS, ERRORMESSAGE, PARAMETERSJSON,
                 CREATEDBYID, CREATEDON, TENANTID)
            VALUES
                (@ConfiguredQueryId, @QueryCode, @QueryIntent, @CallerModule,
                 @DurationMs, @RowsReturned, @IsSuccess, @ErrorMessage, @ParametersJson,
                 @CreatedById, GETDATE(), @TenantId);";
    }
}