namespace BizProcessDAL.Query
{
    public static class BizProcessQB
    {
        // ── MBIZPROCESS ──────────────────────────────────────────────
        // Index: IX_MBIZPROCESS_PARENTID (PARENTBIZPROCESSID), IX_MBIZPROCESS_DEPARTMENTID (DEPARTMENTID)
        public const string GET_PROCESS = @"
            SELECT BIZPROCESSID, BIZPROCESSCODE, BIZPROCESSNAME, DESCRIPTION,
                   DERIVEDFROMBIZPROCESSID, PARENTBIZPROCESSID, DEPARTMENTID, PERSPECTIVEID,
                   MODULEID, STATUS, TENANTID, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
            FROM   MBIZPROCESS
            WHERE  BIZPROCESSID = @BizProcessId
            AND    TENANTID     = @TenantId";

        public const string GET_SELECTLIST_PROCESS = @"
            SELECT BIZPROCESSID   AS Id,
                   BIZPROCESSCODE AS Code,
                   BIZPROCESSNAME AS Name,
                   PARENTBIZPROCESSID AS ParentId
            FROM   MBIZPROCESS
            WHERE  TENANTID = @TenantId
            AND    STATUS   = 1
            ORDER  BY BIZPROCESSNAME";

        public const string SAVE_PROCESS = @"
            INSERT INTO MBIZPROCESS
                (BIZPROCESSID, BIZPROCESSCODE, BIZPROCESSNAME, DESCRIPTION,
                 DERIVEDFROMBIZPROCESSID, PARENTBIZPROCESSID, DEPARTMENTID, PERSPECTIVEID,
                 MODULEID, STATUS, TENANTID, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
            VALUES
                (@BizProcessId, @BizProcessCode, @BizProcessName, @Description,
                 @DerivedFromBizProcessId, @ParentBizProcessId, @DepartmentId, @PerspectiveId,
                 @ModuleId, @Status, @TenantId, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn)";

        public const string UPDATE_PROCESS = @"
            UPDATE MBIZPROCESS
            SET    BIZPROCESSNAME = @BizProcessName,
                   DESCRIPTION    = @Description,
                   PARENTBIZPROCESSID = @ParentBizProcessId,
                   DEPARTMENTID   = @DepartmentId,
                   PERSPECTIVEID  = @PerspectiveId,
                   MODIFIEDBYID   = @ModifiedById,
                   MODIFIEDON     = @ModifiedOn
            WHERE  BIZPROCESSID = @BizProcessId
            AND    TENANTID     = @TenantId";

        // ── MBIZPROCESSVARIANT ───────────────────────────────────────
        public const string GET_PROCESS_VARIANT = @"
            SELECT BIZPROCESSVARIANTID, BIZPROCESSID, VARIANTCODE, VARIANTNAME, DESCRIPTION,
                   DERIVEDFROMVARIANTID, LIFECYCLESTATUS, VERSIONNO, PUBLISHEDSNAPSHOTJSON,
                   TENANTID, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
            FROM   MBIZPROCESSVARIANT
            WHERE  BIZPROCESSVARIANTID = @BizProcessVariantId
            AND    TENANTID            = @TenantId";

        // Index: IX_MBIZPROCESSVARIANT_PROCESSID (BIZPROCESSID, LIFECYCLESTATUS)
        // @BizProcessId = -1 lists every variant for the tenant (flat dropdown across all
        // processes) — the shape ProcessMapViewer's single process-picker dropdown needs;
        // a positive id scopes to that process's own variants (e.g. an authoring screen's
        // "pick a variant of THIS process" list).
        public const string GET_SELECTLIST_VARIANT = @"
            SELECT v.BIZPROCESSVARIANTID AS Id,
                   v.VARIANTCODE         AS Code,
                   v.VARIANTNAME         AS Name,
                   p.BIZPROCESSNAME + ' - ' + v.VARIANTNAME AS Label,
                   v.BIZPROCESSID        AS BizProcessId,
                   v.LIFECYCLESTATUS     AS LifecycleStatus
            FROM   MBIZPROCESSVARIANT v
            INNER  JOIN MBIZPROCESS p ON p.BIZPROCESSID = v.BIZPROCESSID
            WHERE  (@BizProcessId = -1 OR v.BIZPROCESSID = @BizProcessId)
            AND    v.TENANTID = @TenantId
            ORDER  BY Label";

        // Flat, cross-variant stage picker (e.g. QMS's checklist-applicability screen picking
        // a BizProcessStage for its ScopeType=6 audit scope) -- no variant-scoping parameter,
        // unlike GET_SELECTLIST_VARIANT above, since a stage picker's caller is usually outside
        // any single process/variant context.
        public const string GET_SELECTLIST_ALL_STAGES = @"
            SELECT s.BIZPROCESSSTAGEID    AS Id,
                   s.STAGENAME            AS Name,
                   p.BIZPROCESSNAME + ' / ' + v.VARIANTNAME + ' / ' + s.STAGENAME AS Label,
                   s.BIZPROCESSVARIANTID  AS BizProcessVariantId
            FROM   MBIZPROCESSSTAGE s
            INNER  JOIN MBIZPROCESSVARIANT v ON v.BIZPROCESSVARIANTID = s.BIZPROCESSVARIANTID
            INNER  JOIN MBIZPROCESS p ON p.BIZPROCESSID = v.BIZPROCESSID
            WHERE  s.STATUS = 1
            AND    s.TENANTID = @TenantId
            ORDER  BY Label";

        // Used to resolve a SubProcess stage's drill-in target: the most recently modified
        // variant of the referenced child process. Not a "published-only" lookup by design —
        // authors previewing an unpublished draft should still see their subprocess nesting.
        public const string GET_LATEST_VARIANT_FOR_PROCESS = @"
            SELECT TOP 1 BIZPROCESSVARIANTID, BIZPROCESSID, VARIANTCODE, VARIANTNAME, DESCRIPTION,
                   DERIVEDFROMVARIANTID, LIFECYCLESTATUS, VERSIONNO, PUBLISHEDSNAPSHOTJSON,
                   TENANTID, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
            FROM   MBIZPROCESSVARIANT
            WHERE  BIZPROCESSID = @BizProcessId
            AND    TENANTID     = @TenantId
            ORDER  BY MODIFIEDON DESC";

        public const string SAVE_PROCESS_VARIANT = @"
            INSERT INTO MBIZPROCESSVARIANT
                (BIZPROCESSVARIANTID, BIZPROCESSID, VARIANTCODE, VARIANTNAME, DESCRIPTION,
                 DERIVEDFROMVARIANTID, LIFECYCLESTATUS, VERSIONNO, PUBLISHEDSNAPSHOTJSON,
                 TENANTID, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
            VALUES
                (@BizProcessVariantId, @BizProcessId, @VariantCode, @VariantName, @Description,
                 @DerivedFromVariantId, @LifecycleStatus, @VersionNo, @PublishedSnapshotJson,
                 @TenantId, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn)";

        public const string UPDATE_PROCESS_VARIANT = @"
            UPDATE MBIZPROCESSVARIANT
            SET    VARIANTNAME  = @VariantName,
                   DESCRIPTION  = @Description,
                   MODIFIEDBYID = @ModifiedById,
                   MODIFIEDON   = @ModifiedOn
            WHERE  BIZPROCESSVARIANTID = @BizProcessVariantId
            AND    TENANTID            = @TenantId";

        // LIFECYCLESTATUS: 1=Draft, 2=Published, 3=Retired
        public const string PUBLISH_PROCESS_VARIANT = @"
            UPDATE MBIZPROCESSVARIANT
            SET    LIFECYCLESTATUS      = 2,
                   VERSIONNO            = VERSIONNO + 1,
                   PUBLISHEDSNAPSHOTJSON = @PublishedSnapshotJson,
                   MODIFIEDBYID         = @ModifiedById,
                   MODIFIEDON           = @ModifiedOn
            WHERE  BIZPROCESSVARIANTID = @BizProcessVariantId
            AND    TENANTID            = @TenantId";

        // ── MBIZPROCESSSTAGE ─────────────────────────────────────────
        // Index: IX_MBIZPROCESSSTAGE_VARIANT (BIZPROCESSVARIANTID, STAGESEQUENCE)
        public const string GET_STAGES_FOR_VARIANT = @"
            SELECT BIZPROCESSSTAGEID, BIZPROCESSVARIANTID, STAGESEQUENCE, STAGENAME, DESCRIPTION,
                   STAGETYPE, ISOPTIONAL, CHILDBIZPROCESSID,
                   BIZTRANSACTIONCLASSID, BIZTRANSACTIONSUBCLASSID, BIZTRANSACTIONID, BIZTRANSACTIONTYPEID,
                   MENUID, STATUS, TENANTID, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
            FROM   MBIZPROCESSSTAGE
            WHERE  BIZPROCESSVARIANTID = @BizProcessVariantId
            AND    TENANTID            = @TenantId
            AND    STATUS              = 1
            ORDER  BY STAGESEQUENCE";

        public const string GET_STAGE = @"
            SELECT BIZPROCESSSTAGEID, BIZPROCESSVARIANTID, STAGESEQUENCE, STAGENAME, DESCRIPTION,
                   STAGETYPE, ISOPTIONAL, CHILDBIZPROCESSID,
                   BIZTRANSACTIONCLASSID, BIZTRANSACTIONSUBCLASSID, BIZTRANSACTIONID, BIZTRANSACTIONTYPEID,
                   MENUID, STATUS, TENANTID, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
            FROM   MBIZPROCESSSTAGE
            WHERE  BIZPROCESSSTAGEID = @BizProcessStageId
            AND    TENANTID          = @TenantId
            AND    STATUS            = 1";

        public const string SAVE_STAGE = @"
            INSERT INTO MBIZPROCESSSTAGE
                (BIZPROCESSSTAGEID, BIZPROCESSVARIANTID, STAGESEQUENCE, STAGENAME, DESCRIPTION,
                 STAGETYPE, ISOPTIONAL, CHILDBIZPROCESSID,
                 BIZTRANSACTIONCLASSID, BIZTRANSACTIONSUBCLASSID, BIZTRANSACTIONID, BIZTRANSACTIONTYPEID,
                 MENUID, STATUS, TENANTID, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
            VALUES
                (@BizProcessStageId, @BizProcessVariantId, @StageSequence, @StageName, @Description,
                 @StageType, @IsOptional, @ChildBizProcessId,
                 @BizTransactionClassId, @BizTransactionSubClassId, @BizTransactionId, @BizTransactionTypeId,
                 @MenuId, @Status, @TenantId, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn)";

        public const string UPDATE_STAGE = @"
            UPDATE MBIZPROCESSSTAGE
            SET    STAGESEQUENCE = @StageSequence,
                   STAGENAME     = @StageName,
                   DESCRIPTION   = @Description,
                   STAGETYPE     = @StageType,
                   ISOPTIONAL    = @IsOptional,
                   CHILDBIZPROCESSID = @ChildBizProcessId,
                   BIZTRANSACTIONCLASSID    = @BizTransactionClassId,
                   BIZTRANSACTIONSUBCLASSID = @BizTransactionSubClassId,
                   BIZTRANSACTIONID         = @BizTransactionId,
                   BIZTRANSACTIONTYPEID     = @BizTransactionTypeId,
                   MENUID        = @MenuId,
                   MODIFIEDBYID  = @ModifiedById,
                   MODIFIEDON    = @ModifiedOn
            WHERE  BIZPROCESSSTAGEID = @BizProcessStageId
            AND    TENANTID          = @TenantId";

        public const string DELETE_STAGE = @"
            UPDATE MBIZPROCESSSTAGE
            SET    STATUS       = 2,
                   MODIFIEDBYID = @ModifiedById,
                   MODIFIEDON   = @ModifiedOn
            WHERE  BIZPROCESSSTAGEID = @BizProcessStageId
            AND    TENANTID          = @TenantId";

        // ── MBIZPROCESSSTAGEEDGE ─────────────────────────────────────
        // Fetches edges for a variant via a join through Stage (edges have no direct
        // VariantId of their own by design — they connect two stages, and a stage
        // already belongs to exactly one variant).
        public const string GET_EDGES_FOR_VARIANT = @"
            SELECT e.BIZPROCESSSTAGEEDGEID, e.FROMSTAGEID, e.TOSTAGEID, e.CONDITION, e.CONNECTIONLABEL,
                   e.STATUS, e.TENANTID, e.CREATEDBYID, e.CREATEDON, e.MODIFIEDBYID, e.MODIFIEDON
            FROM   MBIZPROCESSSTAGEEDGE e
            INNER  JOIN MBIZPROCESSSTAGE s ON s.BIZPROCESSSTAGEID = e.FROMSTAGEID
            WHERE  s.BIZPROCESSVARIANTID = @BizProcessVariantId
            AND    e.TENANTID            = @TenantId
            AND    e.STATUS              = 1
            AND    s.STATUS              = 1";

        public const string SAVE_STAGE_EDGE = @"
            INSERT INTO MBIZPROCESSSTAGEEDGE
                (BIZPROCESSSTAGEEDGEID, FROMSTAGEID, TOSTAGEID, CONDITION, CONNECTIONLABEL,
                 STATUS, TENANTID, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
            VALUES
                (@BizProcessStageEdgeId, @FromStageId, @ToStageId, @Condition, @ConnectionLabel,
                 @Status, @TenantId, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn)";

        public const string UPDATE_STAGE_EDGE = @"
            UPDATE MBIZPROCESSSTAGEEDGE
            SET    CONDITION       = @Condition,
                   CONNECTIONLABEL = @ConnectionLabel,
                   MODIFIEDBYID    = @ModifiedById,
                   MODIFIEDON      = @ModifiedOn
            WHERE  BIZPROCESSSTAGEEDGEID = @BizProcessStageEdgeId
            AND    TENANTID              = @TenantId";

        public const string DELETE_STAGE_EDGE = @"
            UPDATE MBIZPROCESSSTAGEEDGE
            SET    STATUS       = 2,
                   MODIFIEDBYID = @ModifiedById,
                   MODIFIEDON   = @ModifiedOn
            WHERE  BIZPROCESSSTAGEEDGEID = @BizProcessStageEdgeId
            AND    TENANTID              = @TenantId";

        // ── MBIZPROCESSFRAMEWORK ─────────────────────────────────────
        public const string GET_SELECTLIST_FRAMEWORK = @"
            SELECT FRAMEWORKID   AS Id,
                   FRAMEWORKCODE AS Code,
                   FRAMEWORKNAME AS Name,
                   FRAMEWORKCATEGORY AS Category
            FROM   MBIZPROCESSFRAMEWORK
            ORDER  BY FRAMEWORKNAME";

        // ── MBIZPROCESSSTAGEANNOTATION ───────────────────────────────
        // Index: IX_MBIZPROCESSSTAGEANNOTATION_STAGE (BIZPROCESSSTAGEID, FRAMEWORKID)
        public const string GET_ANNOTATIONS_FOR_STAGE = @"
            SELECT BIZPROCESSSTAGEANNOTATIONID, BIZPROCESSSTAGEID, FRAMEWORKID, JOBROLEID, ROLELEVEL,
                   KRAID, MEASUREID, METHODREFERENCE, ISCONTROLPOINT, CONTROLTYPE, CONTROLMODE,
                   REQUIREMENTREF, EVIDENCETYPE, RETENTIONPERIODDAYS, EXTRADATAJSON, REMARKS,
                   TENANTID, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
            FROM   MBIZPROCESSSTAGEANNOTATION
            WHERE  BIZPROCESSSTAGEID = @BizProcessStageId
            AND    TENANTID          = @TenantId";

        public const string SAVE_STAGE_ANNOTATION = @"
            INSERT INTO MBIZPROCESSSTAGEANNOTATION
                (BIZPROCESSSTAGEANNOTATIONID, BIZPROCESSSTAGEID, FRAMEWORKID, JOBROLEID, ROLELEVEL,
                 KRAID, MEASUREID, METHODREFERENCE, ISCONTROLPOINT, CONTROLTYPE, CONTROLMODE,
                 REQUIREMENTREF, EVIDENCETYPE, RETENTIONPERIODDAYS, EXTRADATAJSON, REMARKS,
                 TENANTID, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
            VALUES
                (@BizProcessStageAnnotationId, @BizProcessStageId, @FrameworkId, @JobRoleId, @RoleLevel,
                 @KraId, @MeasureId, @MethodReference, @IsControlPoint, @ControlType, @ControlMode,
                 @RequirementRef, @EvidenceType, @RetentionPeriodDays, @ExtraDataJson, @Remarks,
                 @TenantId, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn)";

        public const string UPDATE_STAGE_ANNOTATION = @"
            UPDATE MBIZPROCESSSTAGEANNOTATION
            SET    JOBROLEID       = @JobRoleId,
                   ROLELEVEL       = @RoleLevel,
                   KRAID           = @KraId,
                   MEASUREID       = @MeasureId,
                   METHODREFERENCE = @MethodReference,
                   ISCONTROLPOINT  = @IsControlPoint,
                   CONTROLTYPE     = @ControlType,
                   CONTROLMODE     = @ControlMode,
                   REQUIREMENTREF  = @RequirementRef,
                   EVIDENCETYPE    = @EvidenceType,
                   RETENTIONPERIODDAYS = @RetentionPeriodDays,
                   EXTRADATAJSON   = @ExtraDataJson,
                   REMARKS         = @Remarks,
                   MODIFIEDBYID    = @ModifiedById,
                   MODIFIEDON      = @ModifiedOn
            WHERE  BIZPROCESSSTAGEANNOTATIONID = @BizProcessStageAnnotationId
            AND    TENANTID                    = @TenantId";

        // ── MBIZPROCESSSTAGECOMPETENCYREQUIREMENT ────────────────────
        public const string GET_COMPETENCY_REQUIREMENTS_FOR_STAGE = @"
            SELECT STAGECOMPETENCYREQUIREMENTID, BIZPROCESSSTAGEID, COMPETENCYID, MINLEVELNO,
                   ENFORCEMENT, ISMANDATORY, REMARKS, TENANTID, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
            FROM   MBIZPROCESSSTAGECOMPETENCYREQUIREMENT
            WHERE  BIZPROCESSSTAGEID = @BizProcessStageId
            AND    TENANTID          = @TenantId";

        public const string SAVE_COMPETENCY_REQUIREMENT = @"
            INSERT INTO MBIZPROCESSSTAGECOMPETENCYREQUIREMENT
                (STAGECOMPETENCYREQUIREMENTID, BIZPROCESSSTAGEID, COMPETENCYID, MINLEVELNO,
                 ENFORCEMENT, ISMANDATORY, REMARKS, TENANTID, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
            VALUES
                (@StageCompetencyRequirementId, @BizProcessStageId, @CompetencyId, @MinLevelNo,
                 @Enforcement, @IsMandatory, @Remarks, @TenantId, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn)";

        public const string UPDATE_COMPETENCY_REQUIREMENT = @"
            UPDATE MBIZPROCESSSTAGECOMPETENCYREQUIREMENT
            SET    COMPETENCYID = @CompetencyId,
                   MINLEVELNO   = @MinLevelNo,
                   ENFORCEMENT  = @Enforcement,
                   ISMANDATORY  = @IsMandatory,
                   REMARKS      = @Remarks,
                   MODIFIEDBYID = @ModifiedById,
                   MODIFIEDON   = @ModifiedOn
            WHERE  STAGECOMPETENCYREQUIREMENTID = @StageCompetencyRequirementId
            AND    TENANTID                     = @TenantId";

        // ── MBIZPROCESSROLEDEVPLAN ────────────────────────────────────
        public const string GET_ROLEDEVPLAN_FOR_JOBROLE = @"
            SELECT ROLEDEVPLANID, JOBROLEID, PLANTYPE, ITEMTEXT, TARGETDATE,
                   TENANTID, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
            FROM   MBIZPROCESSROLEDEVPLAN
            WHERE  JOBROLEID = @JobRoleId
            AND    TENANTID  = @TenantId";

        public const string SAVE_ROLEDEVPLAN = @"
            INSERT INTO MBIZPROCESSROLEDEVPLAN
                (ROLEDEVPLANID, JOBROLEID, PLANTYPE, ITEMTEXT, TARGETDATE,
                 TENANTID, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
            VALUES
                (@RoleDevPlanId, @JobRoleId, @PlanType, @ItemText, @TargetDate,
                 @TenantId, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn)";

        // ── MBIZPROCESSGOVERNANCE ─────────────────────────────────────
        public const string GET_GOVERNANCE_FOR_PROCESS = @"
            SELECT BIZPROCESSGOVERNANCEID, BIZPROCESSID, JOBROLEID, GOVERNANCEROLE,
                   TENANTID, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
            FROM   MBIZPROCESSGOVERNANCE
            WHERE  BIZPROCESSID = @BizProcessId
            AND    TENANTID     = @TenantId";

        public const string SAVE_GOVERNANCE = @"
            INSERT INTO MBIZPROCESSGOVERNANCE
                (BIZPROCESSGOVERNANCEID, BIZPROCESSID, JOBROLEID, GOVERNANCEROLE,
                 TENANTID, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
            VALUES
                (@BizProcessGovernanceId, @BizProcessId, @JobRoleId, @GovernanceRole,
                 @TenantId, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn)";

        // ── MBIZPROCESSVARIANTMETRIC ──────────────────────────────────
        public const string GET_METRICS_FOR_VARIANT = @"
            SELECT BIZPROCESSVARIANTMETRICID, BIZPROCESSVARIANTID, KRAID, MEASUREID, METRICCATEGORY, TARGETVALUE,
                   TENANTID, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
            FROM   MBIZPROCESSVARIANTMETRIC
            WHERE  BIZPROCESSVARIANTID = @BizProcessVariantId
            AND    TENANTID            = @TenantId";

        public const string SAVE_VARIANT_METRIC = @"
            INSERT INTO MBIZPROCESSVARIANTMETRIC
                (BIZPROCESSVARIANTMETRICID, BIZPROCESSVARIANTID, KRAID, MEASUREID, METRICCATEGORY, TARGETVALUE,
                 TENANTID, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
            VALUES
                (@BizProcessVariantMetricId, @BizProcessVariantId, @KraId, @MeasureId, @MetricCategory, @TargetValue,
                 @TenantId, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn)";
    }
}