namespace GB5Shared.GOP.Qualifier
{
    // ============================================================
    // QualifierQB — Qualifier Engine Data Access Queries
    //
    // Tables (CLAUDE-DB-SCHEMA.md convention — ALL UPPERCASE):
    //   MQUALIFIERDEFINITION      — qualifier library entries
    //   MQUALIFIERVERSION         — versioned compiled plans
    //   MQUALIFIERENTITY          — entity-stage-scope bindings
    //   MQUALIFIERMETHOD          — execution methods (NCalc/DataSource/Service)
    //   MQUALIFIERRULEGROUP       — rule tree nodes (AND/OR groupings)
    //   MQUALIFIERRULECONDITION   — leaf conditions
    //   MQUALIFIEROUTPUT          — FactBag output declarations
    //   MQUALIFIERDEPENDENCY      — DAG edges between qualifiers
    //
    // Security: TENANTID = @TenantId mandatory on EVERY query.
    // @TenantId is auto-populated by IQueryExecutor from LoginDTO.TenantId.
    //
    // Required indexes:
    //   IX_MQUALIFIERDEFINITION_TID_CODE    (TENANTID, QUALIFIERCODE) WHERE STATUS=1
    //   IX_MQUALIFIERVERSION_TID_QID_VSTATUS (TENANTID, QUALIFIERID, VERSIONSTATUS) WHERE VERSIONSTATUS=1
    //   IX_MQUALIFIERENTITY_TID_ENTITY_STAGE (TENANTID, ENTITYCODE, STAGE, SCOPE) WHERE ISACTIVE=0
    // ============================================================
    public static class QualifierQB
    {
        // ── QualifierDefinition ───────────────────────────────────

        public const string SAVE_QUALIFIER_DEFINITION = @"
            INSERT INTO MQUALIFIERDEFINITION
                (QUALIFIERCODE, QUALIFIERNAME, ENTITYID, QUALIFIERTYPE, ENTITYCODE,
                 TEMPLATEID, ISSYSTEM, DESCRIPTION,
                 VERSION, STATUS, SORTORDER, SOURCETYPE,
                 CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, TENANTID)
            VALUES
                (@QualifierCode, @QualifierName, @EntityId, @QualifierType, @EntityCode,
                 @TemplateId, 1 - CONVERT(INT, @IsSystem), @Description,
                 1, 1, @SortOrder, @SourceType,
                 @CreatedById, GETDATE(), @ModifiedById, GETDATE(), @TenantId);";

        public const string UPDATE_QUALIFIER_DEFINITION = @"
            UPDATE MQUALIFIERDEFINITION
            SET    QUALIFIERNAME  = @QualifierName,
                   ENTITYID       = @EntityId,
                   QUALIFIERTYPE  = @QualifierType,
                   ENTITYCODE     = @EntityCode,
                   DESCRIPTION    = @Description,
                   SORTORDER      = @SortOrder,
                   MODIFIEDBYID   = @ModifiedById,
                   MODIFIEDON     = GETDATE(),
                   VERSION        = VERSION + 1
            WHERE  QUALIFIERID    = @QualifierId
              AND  TENANTID       = @TenantId;";

        public const string DELETE_QUALIFIER_DEFINITION = @"
            UPDATE MQUALIFIERDEFINITION
            SET    STATUS        = 2,
                   MODIFIEDBYID  = @ModifiedById,
                   MODIFIEDON    = GETDATE(),
                   VERSION       = VERSION + 1
            WHERE  QUALIFIERID   = @QualifierId
              AND  TENANTID      = @TenantId;";

        // LEFT JOIN (not INNER) — TEMPLATEID defaults to -1 (sentinel "no template") on
        // MQUALIFIERDEFINITION, and an INNER JOIN would silently drop those qualifiers
        // from GetQualifier's result. TemplateName is simply NULL when unset.
        public const string GET_QUALIFIER_BY_ID = @"
            SELECT  qd.TENANTID         AS TenantId,
                    qd.QUALIFIERID      AS QualifierId,
                    qd.QUALIFIERCODE    AS QualifierCode,
                    qd.QUALIFIERNAME    AS QualifierName,
                    qd.ENTITYID         AS EntityId,
                    qd.QUALIFIERTYPE    AS QualifierType,
                    qd.ENTITYCODE       AS EntityCode,
                    qd.TEMPLATEID       AS TemplateId,
                    t.TEMPLATENAME      AS TemplateName,
                    1 - qd.ISSYSTEM     AS IsSystem,
                    qd.DESCRIPTION      AS Description,
                    qd.STATUS           AS Status,
                    qd.SORTORDER        AS SortOrder
            FROM    MQUALIFIERDEFINITION qd
            LEFT JOIN MTEMPLATE t
                    ON  t.TEMPLATEID   = qd.TEMPLATEID
            WHERE   qd.TENANTID     = @TenantId
              AND   qd.QUALIFIERID  = @QualifierId;";

        public const string GET_QUALIFIER_BY_CODE = @"
            SELECT  qd.TENANTID         AS TenantId,
                    qd.QUALIFIERID      AS QualifierId,
                    qd.QUALIFIERCODE    AS QualifierCode,
                    qd.QUALIFIERNAME    AS QualifierName,
                    qd.ENTITYID         AS EntityId,
                    qd.QUALIFIERTYPE    AS QualifierType,
                    qd.ENTITYCODE       AS EntityCode,
                    qd.TEMPLATEID       AS TemplateId,
                    1 - qd.ISSYSTEM     AS IsSystem,
                    qd.DESCRIPTION      AS Description,
                    qd.STATUS           AS Status,
                    qd.SORTORDER        AS SortOrder
            FROM    MQUALIFIERDEFINITION qd
            WHERE   qd.TENANTID         = @TenantId
              AND   qd.QUALIFIERCODE    = @QualifierCode
              AND   qd.STATUS           = 1;";

        public const string GET_QUALIFIER_LIST = @"
            SELECT  qd.TENANTID         AS TenantId,
                    qd.QUALIFIERID      AS QualifierId,
                    qd.QUALIFIERCODE    AS QualifierCode,
                    qd.QUALIFIERNAME    AS QualifierName,
                    qd.ENTITYID         AS EntityId,
                    qd.QUALIFIERTYPE    AS QualifierType,
                    qd.ENTITYCODE       AS EntityCode,
                    1 - qd.ISSYSTEM     AS IsSystem,
                    qd.STATUS           AS Status,
                    qd.SORTORDER        AS SortOrder,
                    qv.VERSIONNO        AS ActiveVersionNo,
                    qv.VERSIONLABEL     AS ActiveVersionLabel
            FROM    MQUALIFIERDEFINITION qd
            LEFT JOIN MQUALIFIERVERSION  qv
                    ON  qv.TENANTID     = qd.TENANTID
                    AND qv.QUALIFIERID  = qd.QUALIFIERID
                    AND qv.VERSIONSTATUS = 1     -- Active only
            WHERE   qd.TENANTID = @TenantId
              AND   qd.STATUS  <> 2              -- exclude Deleted
            ORDER BY qd.SORTORDER, qd.QUALIFIERNAME;";

        // Same shape as GET_QUALIFIER_LIST, filtered by QualifierCode/QualifierName
        // (either matches — @QualifierCode/@QualifierName are null when not supplied).
        public const string GET_SELECTLIST_QUALIFIER = @"
            SELECT  qd.TENANTID         AS TenantId,
                    qd.QUALIFIERID      AS QualifierId,
                    qd.QUALIFIERCODE    AS QualifierCode,
                    qd.QUALIFIERNAME    AS QualifierName,
                    qd.ENTITYID         AS EntityId,
                    qd.QUALIFIERTYPE    AS QualifierType,
                    qd.ENTITYCODE       AS EntityCode,
                    1 - qd.ISSYSTEM     AS IsSystem,
                    qd.STATUS           AS Status,
                    qd.SORTORDER        AS SortOrder,
                    qv.VERSIONNO        AS ActiveVersionNo,
                    qv.VERSIONLABEL     AS ActiveVersionLabel
            FROM    MQUALIFIERDEFINITION qd
            LEFT JOIN MQUALIFIERVERSION  qv
                    ON  qv.TENANTID     = qd.TENANTID
                    AND qv.QUALIFIERID  = qd.QUALIFIERID
                    AND qv.VERSIONSTATUS = 1     -- Active only
            WHERE   qd.TENANTID = @TenantId
              AND   qd.STATUS  <> 2              -- exclude Deleted
              AND (
                    (@QualifierCode IS NULL AND @QualifierName IS NULL)
                    OR (@QualifierCode IS NOT NULL AND qd.QUALIFIERCODE LIKE @QualifierCode)
                    OR (@QualifierName IS NOT NULL AND qd.QUALIFIERNAME LIKE @QualifierName)
                  )
            ORDER BY qd.SORTORDER, qd.QUALIFIERNAME;";

        // ── QualifierLibrary (cross-qualifier summary, no N+1) ───────────────

        public const string GET_QUALIFIER_LIBRARY = @"
            SELECT  qd.TENANTID              AS TenantId,
                    qd.QUALIFIERID           AS QualifierId,
                    qd.QUALIFIERCODE         AS QualifierCode,
                    qd.QUALIFIERNAME         AS QualifierName,
                    qd.ENTITYID              AS EntityId,
                    qd.QUALIFIERTYPE         AS QualifierType,
                    qd.ENTITYCODE            AS EntityCode,
                    1 - qd.ISSYSTEM          AS IsSystem,
                    qd.DESCRIPTION           AS Description,
                    qd.STATUS                AS Status,
                    qd.SORTORDER             AS SortOrder,
                    av.VERSIONNO             AS ActiveVersionNo,
                    av.VERSIONLABEL          AS ActiveVersionLabel,
                    av.ACTIVATEDON           AS ActiveActivatedOn,
                    COALESCE(vs.VersionCount, 0) AS TotalVersionCount,
                    vs.LastActivatedOn       AS LastActivatedOn
            FROM    MQUALIFIERDEFINITION qd
            LEFT JOIN MQUALIFIERVERSION av
                    ON  av.TENANTID      = qd.TENANTID
                    AND av.QUALIFIERID   = qd.QUALIFIERID
                    AND av.VERSIONSTATUS = 1
            LEFT JOIN (
                SELECT  TENANTID,
                        QUALIFIERID,
                        COUNT(*)         AS VersionCount,
                        MAX(ACTIVATEDON) AS LastActivatedOn
                FROM    MQUALIFIERVERSION
                WHERE   TENANTID = @TenantId
                GROUP BY TENANTID, QUALIFIERID
            ) vs ON  vs.TENANTID    = qd.TENANTID
                 AND vs.QUALIFIERID = qd.QUALIFIERID
            WHERE   qd.TENANTID = @TenantId
              AND   qd.STATUS  <> 2
            ORDER BY qd.SORTORDER, qd.QUALIFIERNAME;";

        // ── QualifierVersion ──────────────────────────────────────

        public const string SAVE_QUALIFIER_VERSION = @"
            INSERT INTO MQUALIFIERVERSION
                ( QUALIFIERID, VERSIONNO, VERSIONLABEL,
                 VERSIONSTATUS, SNAPSHOTJSON, ACTIVATEDON,
                 VERSION, STATUS, SORTORDER, SOURCETYPE,
                 CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, TENANTID)
            VALUES
                (@QualifierId, @VersionNo, @VersionLabel,
                 0, NULL, NULL,
                 1, 1, 0, @SourceType,
                 @CreatedById, GETDATE(), @ModifiedById, GETDATE(), @TenantId);";

        public const string GET_ACTIVE_QUALIFIER_PLAN = @"
            SELECT  qv.TENANTID             AS TenantId,
                    qv.QUALIFIERVERSIONID   AS QualifierVersionId,
                    qv.QUALIFIERID          AS QualifierId,
                    qv.VERSIONNO            AS VersionNo,
                    qv.VERSIONLABEL         AS VersionLabel,
                    qv.VERSIONSTATUS        AS VersionStatus,
                    qv.SNAPSHOTJSON         AS SnapshotJson,
                    qv.ACTIVATEDON          AS ActivatedOn
            FROM    MQUALIFIERVERSION qv
            WHERE   qv.TENANTID     = @TenantId
              AND   qv.QUALIFIERID  = @QualifierId
              AND   qv.VERSIONSTATUS = 1;";

        public const string GET_QUALIFIER_VERSION_LIST = @"
            SELECT  qv.TENANTID             AS TenantId,
                    qv.QUALIFIERVERSIONID   AS QualifierVersionId,
                    qv.QUALIFIERID          AS QualifierId,
                    qv.VERSIONNO            AS VersionNo,
                    qv.VERSIONLABEL         AS VersionLabel,
                    qv.VERSIONSTATUS        AS VersionStatus,
                    qv.ACTIVATEDON          AS ActivatedOn
            FROM    MQUALIFIERVERSION qv
            WHERE   qv.TENANTID     = @TenantId
              AND   qv.QUALIFIERID  = @QualifierId
              AND   qv.STATUS       = 1
            ORDER BY qv.VERSIONNO DESC;";

        // Soft-delete a Draft version only — BLL guards VersionStatus=0 before calling this;
        // Active/Archived versions must never be deletable (audit/replay history).
        public const string DELETE_QUALIFIER_VERSION = @"
            UPDATE MQUALIFIERVERSION
            SET    STATUS        = 2,
                   MODIFIEDBYID  = @ModifiedById,
                   MODIFIEDON    = GETDATE(),
                   VERSION       = VERSION + 1
            WHERE  QUALIFIERVERSIONID = @QualifierVersionId
              AND  TENANTID           = @TenantId;";

        // Single-row lookup used only to guard DeleteQualifierVersion (Draft-only check).
        public const string GET_QUALIFIER_VERSION_STATUS = @"
            SELECT  qv.VERSIONSTATUS AS VersionStatus
            FROM    MQUALIFIERVERSION qv
            WHERE   qv.QUALIFIERVERSIONID = @QualifierVersionId
              AND   qv.TENANTID           = @TenantId
              AND   qv.STATUS             = 1;";

        // Activate: archive existing Active version and promote the target version.
        // Both UPDATE statements run in the same transaction (called from BLL within ExecuteSaveAsync).
        public const string ARCHIVE_ACTIVE_VERSION = @"
            UPDATE MQUALIFIERVERSION
            SET    VERSIONSTATUS = 2,           -- 2 = Archived
                   MODIFIEDBYID  = @ModifiedById,
                   MODIFIEDON    = GETDATE(),
                   VERSION       = VERSION + 1
            WHERE  QUALIFIERID   = @QualifierId
              AND  TENANTID      = @TenantId
              AND  VERSIONSTATUS = 1;";        // -- only Active row

        public const string ACTIVATE_VERSION = @"
            UPDATE MQUALIFIERVERSION
            SET    VERSIONSTATUS  = 1,           -- 1 = Active
                   SNAPSHOTJSON   = @SnapshotJson,
                   ACTIVATEDON    = GETDATE(),
                   MODIFIEDBYID   = @ModifiedById,
                   MODIFIEDON     = GETDATE(),
                   VERSION        = VERSION + 1
            WHERE  QUALIFIERVERSIONID = @QualifierVersionId
              AND  TENANTID           = @TenantId;";

        // ── QualifierEntity (bindings) ────────────────────────────

        // QUALIFIERENTITYID is an IDENTITY column on the live table (pre-existing rows
        // confirm this was the original design) -- unlike every other GOP table, this one
        // does NOT take a caller/AutoNumber-supplied PK. Use ExecuteIdentityAsync + OUTPUT.
        // ISACTIVE is stored inverted (0=Yes/Active, 1=No/Inactive) -- see GET_ENTITY_BINDINGS'
        // "1 - qe.ISACTIVE AS IsActive" projection. @IsActive here is the DTO's normal-sense bool.
        public const string SAVE_QUALIFIER_ENTITY = @"
            INSERT INTO MQUALIFIERENTITY
                (QUALIFIERID, ENTITYCODE, STAGE, SCOPE, ISACTIVE,
                 VERSION, STATUS, SORTORDER, SOURCETYPE,
                 CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, TENANTID)
            OUTPUT INSERTED.QUALIFIERENTITYID
            VALUES
                (@QualifierId, @EntityCode, @Stage, @Scope, CASE WHEN @IsActive = 1 THEN 0 ELSE 1 END,
                 1, 1, @SortOrder, @SourceType,
                 @CreatedById, GETDATE(), @ModifiedById, GETDATE(), @TenantId);";

        public const string UPDATE_QUALIFIER_ENTITY = @"
            UPDATE MQUALIFIERENTITY
            SET    STAGE        = @Stage,
                   SCOPE        = @Scope,
                   ISACTIVE     = CASE WHEN @IsActive = 1 THEN 0 ELSE 1 END,
                   SORTORDER    = @SortOrder,
                   MODIFIEDBYID = @ModifiedById,
                   MODIFIEDON   = GETDATE()
            WHERE  QUALIFIERENTITYID = @QualifierEntityId
              AND  TENANTID          = @TenantId;";

        public const string GET_ENTITY_BINDINGS = @"
            SELECT  qe.TENANTID             AS TenantId,
                    qe.QUALIFIERENTITYID    AS QualifierEntityId,
                    qe.QUALIFIERID          AS QualifierId,
                    qe.ENTITYCODE           AS EntityCode,
                    qe.STAGE                AS Stage,
                    qe.SCOPE                AS Scope,
                    1 - qe.ISACTIVE         AS IsActive
            FROM    MQUALIFIERENTITY qe
            WHERE   qe.TENANTID     = @TenantId
              AND   qe.ENTITYCODE   = @EntityCode
              AND   qe.STAGE        = @Stage
              AND   qe.SCOPE        = @Scope
              AND   qe.ISACTIVE     = 0
            ORDER BY qe.SORTORDER;";

        // Admin-UI listing — every binding for a given Qualifier, active or not (unlike
        // GET_ENTITY_BINDINGS above, which is the runtime engine's active-only lookup keyed by
        // EntityCode/Stage/Scope). Needed so an admin can see/manage all of a qualifier's bindings.
        public const string GET_ENTITY_BINDINGS_BY_QUALIFIER = @"
            SELECT  qe.TENANTID             AS TenantId,
                    qe.QUALIFIERENTITYID    AS QualifierEntityId,
                    qe.QUALIFIERID          AS QualifierId,
                    qe.ENTITYCODE           AS EntityCode,
                    qe.STAGE                AS Stage,
                    qe.SCOPE                AS Scope,
                    1 - qe.ISACTIVE         AS IsActive
            FROM    MQUALIFIERENTITY qe
            WHERE   qe.TENANTID     = @TenantId
              AND   qe.QUALIFIERID  = @QualifierId
            ORDER BY qe.SORTORDER;";

        // ── QualifierMethod ───────────────────────────────────────

        // QUALIFIERMETHODID is an IDENTITY column on the live table (confirmed via
        // sys.identity_columns) -- like MQUALIFIERENTITY, this does NOT take a
        // caller/AutoNumber-supplied PK. Use ExecuteIdentityAsync + OUTPUT.
        public const string SAVE_QUALIFIER_METHOD = @"
            INSERT INTO MQUALIFIERMETHOD
                (QUALIFIERID, EXECUTIONMODE, EXPRESSION,
                 QUERYCODE, SERVICECODE, RETURNTYPE,
                 VERSION, STATUS, SORTORDER, SOURCETYPE,
                 CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, TENANTID)
            OUTPUT INSERTED.QUALIFIERMETHODID
            VALUES
                (@QualifierId, @ExecutionMode, @Expression,
                 @QueryCode, @ServiceCode, @ReturnType,
                 1, 1, @SortOrder, @SourceType,
                 @CreatedById, GETDATE(), @ModifiedById, GETDATE(), @TenantId);";

        public const string GET_QUALIFIER_METHODS = @"
            SELECT  qm.TENANTID             AS TenantId,
                    qm.QUALIFIERMETHODID    AS QualifierMethodId,
                    qm.QUALIFIERID          AS QualifierId,
                    qm.EXECUTIONMODE        AS ExecutionMode,
                    qm.EXPRESSION           AS Expression,
                    qm.QUERYCODE            AS QueryCode,
                    qm.SERVICECODE          AS ServiceCode,
                    qm.RETURNTYPE           AS ReturnType,
                    qm.SORTORDER            AS SortOrder
            FROM    MQUALIFIERMETHOD qm
            WHERE   qm.TENANTID     = @TenantId
              AND   qm.QUALIFIERID  = @QualifierId
              AND   qm.STATUS       = 1
            ORDER BY qm.SORTORDER;";

        public const string DELETE_QUALIFIER_METHOD = @"
            UPDATE MQUALIFIERMETHOD
            SET    STATUS        = 2,
                   MODIFIEDBYID  = @ModifiedById,
                   MODIFIEDON    = GETDATE(),
                   VERSION       = VERSION + 1
            WHERE  QUALIFIERMETHODID = @QualifierMethodId
              AND  TENANTID          = @TenantId;";

        // ── QualifierRuleGroup ────────────────────────────────────

        // QUALIFIERRULEGROUPID is a live IDENTITY column on GB5DEMO (confirmed via
        // sys.identity_columns — the DDL script/DTO doc comment claiming "app-generated
        // via AutoNumber" does not match the actually-deployed schema, same divergence
        // already known for MQUALIFIERENTITY/MQUALIFIERMETHOD). Use ExecuteIdentityAsync
        // + OUTPUT INSERTED, not a caller/AutoNumber-supplied PK.
        // PARENTRULEGROUPID is NOT NULL DEFAULT(-1) on the live table (confirmed via
        // INFORMATION_SCHEMA.COLUMNS) — -1 is the real "no parent / root group" sentinel,
        // not NULL as the DTO's nullable field would naively suggest. ISNULL translates
        // the DTO's null (root group) to that sentinel; GET_RULE_GROUPS translates back.
        public const string SAVE_QUALIFIER_RULE_GROUP = @"
            INSERT INTO MQUALIFIERRULEGROUP
                (QUALIFIERID, PARENTRULEGROUPID, GROUPOPERATOR,
                 VERSION, STATUS, SORTORDER, SOURCETYPE,
                 CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, TENANTID)
            OUTPUT INSERTED.QUALIFIERRULEGROUPID
            VALUES
                (@QualifierId, ISNULL(@ParentRuleGroupId, -1), @GroupOperator,
                 1, 1, @SortOrder, @SourceType,
                 @CreatedById, GETDATE(), @ModifiedById, GETDATE(), @TenantId);";

        public const string GET_RULE_GROUPS = @"
            SELECT  rg.TENANTID             AS TenantId,
                    rg.QUALIFIERRULEGROUPID  AS RuleGroupId,
                    rg.QUALIFIERID          AS QualifierId,
                    NULLIF(rg.PARENTRULEGROUPID, -1) AS ParentRuleGroupId,
                    rg.GROUPOPERATOR        AS GroupOperator,
                    rg.SORTORDER            AS SortOrder
            FROM    MQUALIFIERRULEGROUP rg
            WHERE   rg.TENANTID     = @TenantId
              AND   rg.QUALIFIERID  = @QualifierId
              AND   rg.STATUS       = 1
            ORDER BY rg.SORTORDER;";

        public const string DELETE_QUALIFIER_RULE_GROUP = @"
            UPDATE MQUALIFIERRULEGROUP
            SET    STATUS        = 2,
                   MODIFIEDBYID  = @ModifiedById,
                   MODIFIEDON    = GETDATE(),
                   VERSION       = VERSION + 1
            WHERE  QUALIFIERRULEGROUPID = @RuleGroupId
              AND  TENANTID             = @TenantId;";

        // ── QualifierRuleCondition ────────────────────────────────

        // QUALIFIERRULECONDITIONID is a live IDENTITY column on GB5DEMO (see the
        // SAVE_QUALIFIER_RULE_GROUP comment above — same divergence from the DDL script).
        public const string SAVE_QUALIFIER_RULE_CONDITION = @"
            INSERT INTO MQUALIFIERRULECONDITION
                (QUALIFIERRULEGROUPID, QUALIFIERID,
                 FIELDPATH, CONDITIONOPERATOR, EXPECTEDVALUE, SEVERITY, ERRORCODE, ERRORMESSAGE,
                 VERSION, STATUS, SORTORDER, SOURCETYPE,
                 CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, TENANTID)
            OUTPUT INSERTED.QUALIFIERRULECONDITIONID
            VALUES
                (@RuleGroupId, @QualifierId,
                 @FieldPath, @ConditionOperator, @ExpectedValue, @Severity, @ErrorCode, @ErrorMessage,
                 1, 1, @SortOrder, @SourceType,
                 @CreatedById, GETDATE(), @ModifiedById, GETDATE(), @TenantId);";

        public const string GET_RULE_CONDITIONS = @"
            SELECT  qc.TENANTID                 AS TenantId,
                    qc.QUALIFIERRULECONDITIONID  AS ConditionId,
                    qc.QUALIFIERRULEGROUPID       AS RuleGroupId,
                    qc.QUALIFIERID               AS QualifierId,
                    qc.FIELDPATH                 AS FieldPath,
                    qc.CONDITIONOPERATOR         AS ConditionOperator,
                    qc.EXPECTEDVALUE             AS ExpectedValue,
                    qc.SEVERITY                  AS Severity,
                    qc.ERRORCODE                 AS ErrorCode,
                    qc.ERRORMESSAGE              AS ErrorMessage,
                    qc.SORTORDER                 AS SortOrder
            FROM    MQUALIFIERRULECONDITION qc
            WHERE   qc.TENANTID     = @TenantId
              AND   qc.QUALIFIERID  = @QualifierId
              AND   qc.STATUS       = 1
            ORDER BY qc.SORTORDER;";

        public const string DELETE_QUALIFIER_RULE_CONDITION = @"
            UPDATE MQUALIFIERRULECONDITION
            SET    STATUS        = 2,
                   MODIFIEDBYID  = @ModifiedById,
                   MODIFIEDON    = GETDATE(),
                   VERSION       = VERSION + 1
            WHERE  QUALIFIERRULECONDITIONID = @ConditionId
              AND  TENANTID                 = @TenantId;";

        // ── QualifierOutput ───────────────────────────────────────

        // QUALIFIEROUTPUTID is a live IDENTITY column on GB5DEMO (see the
        // SAVE_QUALIFIER_RULE_GROUP comment above — same divergence from the DDL script).
        public const string SAVE_QUALIFIER_OUTPUT = @"
            INSERT INTO MQUALIFIEROUTPUT
                (QUALIFIERID, OUTPUTKEY, OUTPUTSCOPE, SOURCEEXPRESSION,
                 VERSION, STATUS, SORTORDER, SOURCETYPE,
                 CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, TENANTID)
            OUTPUT INSERTED.QUALIFIEROUTPUTID
            VALUES
                (@QualifierId, @OutputKey, @OutputScope, @SourceExpression,
                 1, 1, @SortOrder, @SourceType,
                 @CreatedById, GETDATE(), @ModifiedById, GETDATE(), @TenantId);";

        public const string GET_QUALIFIER_OUTPUTS = @"
            SELECT  qo.TENANTID             AS TenantId,
                    qo.QUALIFIEROUTPUTID    AS OutputId,
                    qo.QUALIFIERID          AS QualifierId,
                    qo.OUTPUTKEY            AS OutputKey,
                    qo.OUTPUTSCOPE          AS OutputScope,
                    qo.SOURCEEXPRESSION     AS SourceExpression,
                    qo.SORTORDER            AS SortOrder
            FROM    MQUALIFIEROUTPUT qo
            WHERE   qo.TENANTID     = @TenantId
              AND   qo.QUALIFIERID  = @QualifierId
              AND   qo.STATUS       = 1
            ORDER BY qo.SORTORDER;";

        public const string DELETE_QUALIFIER_OUTPUT = @"
            UPDATE MQUALIFIEROUTPUT
            SET    STATUS        = 2,
                   MODIFIEDBYID  = @ModifiedById,
                   MODIFIEDON    = GETDATE(),
                   VERSION       = VERSION + 1
            WHERE  QUALIFIEROUTPUTID = @OutputId
              AND  TENANTID          = @TenantId;";

        // ── QualifierDependency ───────────────────────────────────

        // QUALIFIERDEPENDENCYID is a live IDENTITY column on GB5DEMO (see the
        // SAVE_QUALIFIER_RULE_GROUP comment above — same divergence from the DDL script).
        public const string SAVE_QUALIFIER_DEPENDENCY = @"
            INSERT INTO MQUALIFIERDEPENDENCY
                (QUALIFIERID, DEPENDSONQUALIFIERID,
                 VERSION, STATUS, SORTORDER, SOURCETYPE,
                 CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, TENANTID)
            OUTPUT INSERTED.QUALIFIERDEPENDENCYID
            VALUES
                (@QualifierId, @DependsOnQualifierId,
                 1, 1, 0, @SourceType,
                 @CreatedById, GETDATE(), @ModifiedById, GETDATE(), @TenantId);";

        public const string GET_QUALIFIER_DEPENDENCIES = @"
            SELECT  qd.TENANTID                 AS TenantId,
                    qd.QUALIFIERDEPENDENCYID    AS DependencyId,
                    qd.QUALIFIERID              AS QualifierId,
                    qd.DEPENDSONQUALIFIERID     AS DependsOnQualifierId
            FROM    MQUALIFIERDEPENDENCY qd
            WHERE   qd.TENANTID     = @TenantId
              AND   qd.QUALIFIERID  = @QualifierId
              AND   qd.STATUS       = 1;";

        public const string DELETE_QUALIFIER_DEPENDENCY = @"
            UPDATE MQUALIFIERDEPENDENCY
            SET    STATUS        = 2,
                   MODIFIEDBYID  = @ModifiedById,
                   MODIFIEDON    = GETDATE(),
                   VERSION       = VERSION + 1
            WHERE  QUALIFIERDEPENDENCYID = @DependencyId
              AND  TENANTID              = @TenantId;";

        // Load all dependencies for a qualifier group (needed for full DAG compilation)
        public const string GET_QUALIFIER_DEPENDENCIES_BY_ENTITY = @"
            SELECT  qd.TENANTID                 AS TenantId,
                    qd.QUALIFIERDEPENDENCYID    AS DependencyId,
                    qd.QUALIFIERID              AS QualifierId,
                    qd.DEPENDSONQUALIFIERID     AS DependsOnQualifierId
            FROM    MQUALIFIERDEPENDENCY qd
            INNER JOIN MQUALIFIERENTITY  qe
                    ON  qe.TENANTID    = qd.TENANTID
                    AND qe.QUALIFIERID = qd.QUALIFIERID
            WHERE   qd.TENANTID  = @TenantId
              AND   qe.ENTITYCODE = @EntityCode
              AND   qe.STAGE      = @Stage
              AND   qe.SCOPE      = @Scope
              AND   qe.ISACTIVE   = 0
              AND   qd.STATUS     = 1;";

        // ── QualifierScope ────────────────────────────────────────

        // QUALIFIERSCOPEID is a live IDENTITY column on GB5DEMO (see the
        // SAVE_QUALIFIER_RULE_GROUP comment above — same divergence from the DDL script).
        public const string SAVE_QUALIFIER_SCOPE = @"
            INSERT INTO MQUALIFIERSCOPE
                (QUALIFIERID, STAGE, SCOPE, ISACTIVE,
                 VERSION, STATUS, SORTORDER, SOURCETYPE,
                 CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, TENANTID)
            OUTPUT INSERTED.QUALIFIERSCOPEID
            VALUES
                (@QualifierId, @Stage, @Scope, @IsActive,
                 1, 1, @SortOrder, @SourceType,
                 @CreatedById, GETDATE(), @ModifiedById, GETDATE(), @TenantId);";

        public const string GET_QUALIFIER_SCOPES = @"
            SELECT  qs.TENANTID          AS TenantId,
                    qs.QUALIFIERSCOPEID  AS QualifierScopeId,
                    qs.QUALIFIERID       AS QualifierId,
                    qs.STAGE             AS Stage,
                    qs.SCOPE             AS Scope,
                    qs.ISACTIVE          AS IsActive,
                    qs.SORTORDER         AS SortOrder
            FROM    MQUALIFIERSCOPE qs
            WHERE   qs.TENANTID     = @TenantId
              AND   qs.QUALIFIERID  = @QualifierId
              AND   qs.STATUS       = 1
            ORDER BY qs.SORTORDER;";

        public const string DELETE_QUALIFIER_SCOPE = @"
            UPDATE MQUALIFIERSCOPE
            SET    STATUS        = 2,
                   MODIFIEDBYID  = @ModifiedById,
                   MODIFIEDON    = GETDATE(),
                   VERSION       = VERSION + 1
            WHERE  QUALIFIERSCOPEID = @QualifierScopeId
              AND  TENANTID         = @TenantId;";

        // ── Entity code resolution (BaseEntityAppService adapter) ────
        // MENTITY is global framework metadata, not tenant-scoped (confirmed against
        // EntityQB.cs — no TENANTID column/filter on any existing MENTITY query).
        public const string GET_ENTITY_CODE_BY_ID = @"
            SELECT  ENTITYCODE
            FROM    MENTITY
            WHERE   ENTITYID = @EntityId;";
    }
}