namespace WiDAL.Query.Wi;

public class WiMasterQB
{
    // Index: IX_mwi_WISTATUS_TENANTID on (WISTATUS, TENANTID) INCLUDE (WIID, WICODE, WITITLE, ACTIVITYTYPE)
    // Index: IX_mwi_WISUBPROCESSID on (WISUBPROCESSID, TENANTID) INCLUDE (WICODE, WITITLE, WISTATUS, ACTIVITYTYPE, ISACTIVE)
    public const string GET_WI_LIST = @"SELECT  
                w.WIID                       AS WiId,
                w.WICODE                     AS WiCode,
                w.WITITLE                    AS WiTitle,
                w.WIVERSION                   AS WiVersion,
                w.WISTATUS                    AS WiStatus,
                p.WIPROCESSNAME             AS ProcessName,
                s.WISUBPROCESSNAME          AS SubProcessName
        FROM    WI.MWI w
        LEFT JOIN  WI.MWIPROCESS    p ON p.WIPROCESSID    = w.WIPROCESSID
        LEFT JOIN  WI.MWISUBPROCESS s ON s.WISUBPROCESSID = w.WISUBPROCESSID
        WHERE   w.TENANTID = @TenantId
          AND   w.STATUS   = 1
          AND   (@WiProcessId = -1 OR w.WIPROCESSID = @WiProcessId)
          AND   (@WiStatus    = 255 OR w.WISTATUS   = @WiStatus)
        ORDER BY w.WICODE";

    public const string GET_WI_BY_ID = @"SELECT 
                            WI.WIID,
                            WI.WISUBPROCESSID,
                            SP.WISUBPROCESSNAME,
                            WI.WIPROCESSID,
                            P.WIPROCESSNAME,
                            WI.WICODE,
                            WI.WITITLE,
                            WI.WIVERSION,
                            WI.WISTATUS,
                            WI.DISPLAYCONTEXT,
                            WI.STEPADVANCEMODE,
                            WI.STDCYCLETIMEMINS,
                            WI.EFFECTIVEDATE,
                            WI.REVIEWDUEDATE,
                            WI.APPROVEDBYID,
                            U.USERNAME AS APPROVEDBYNAME,
                            WI.APPROVEDON,
                            WI.TAGS,
                            WI.DESCRIPTION AS WiDescription,
                            WI.VERSION,
                            WI.STATUS,
                            WI.SORTORDER,
                            WI.CREATEDBYID,
                            WI.MODIFIEDBYID,
                            WI.SOURCETYPE,
                            WI.TENANTID
                        FROM WI.MWI WI
                        LEFT JOIN WI.MWIPROCESS P
                            ON WI.WIPROCESSID = P.WIPROCESSID
                        LEFT JOIN WI.MWISUBPROCESS SP
                            ON WI.WISUBPROCESSID = SP.WISUBPROCESSID
                        LEFT JOIN MUSER U
                            ON WI.APPROVEDBYID = U.USERID
                        WHERE WI.WIID = @WiId;";

    public const string GET_SECTIONS = @"
        SELECT  WISECTIONID, WIID, SLNO, SECTIONTITLE, DESCRIPTION
        FROM    WI.MWISECTION
        WHERE   WIID = @WIID
        ORDER BY SLNO";

    public const string GET_STEPS = @"
        SELECT  WISTEPID, WIID, WISECTIONID, SLNO, STEPDISPLAYNO,
                STEPTITLE, WARNINGLEVEL, STDDURATIONSECS, TOOLSREQUIRED,
                CAST(ISOPTIONAL     AS BIT) AS IsOptional,
                CAST(REQUIRESIGNOFF AS BIT) AS RequireSignOff,
                AUTOADVANCESECS,
                CAST(ISACTIVE AS BIT) AS IsActive
        FROM    WI.MWISTEP
        WHERE   WIID     = @WiId
          AND   ISACTIVE = 1
        ORDER BY SLNO";

    public const string GET_STEP_CONTENTS = @"
         SELECT  c.WISTEPCONTENTID       AS WiStepContentId,
                c.WISTEPID              AS WiStepId,
                c.SLNO                  AS WiStepContentSlNo,
                c.CONTENTTYPE           AS ContentType,
                c.CONTENTSOURCE         AS ContentSource,
                c.CONTENTREF            AS ContentRef,
                c.MEDIACAPTION          AS MediaCaption,
                c.DISPLAYDURATIONSECS   AS DisplayDurationSecs,
                c.LOOPMEDIA             AS LoopMedia,
                c.ZOOMREGION            AS ZoomRegion,
                c.APPLICABLEMODELIDS    AS ApplicableModelIds,
                CAST(c.ISACTIVE AS BIT) AS IsActive,
                ISNULL(c.CONTAINERID, 0)          AS ContainerId,
                ISNULL(c.CONTAINERTYPE, 0)        AS ContainerType,
                c.CONTAINERLABEL                  AS ContainerLabel,
                ISNULL(c.ZONENAME, 'main')        AS ZoneName,
                ISNULL(c.BLOCKINCONTAINERPOS, 1)  AS BlockInContainerPos
        FROM    WI.MWISTEPCONTENT c
        JOIN    WI.MWISTEP        t ON t.WISTEPID = c.WISTEPID
        WHERE   t.WIID     = @WiId
          AND   c.ISACTIVE = 1
        ORDER BY c.WISTEPID, ISNULL(c.CONTAINERID, 0), ISNULL(c.ZONENAME, 'main'), ISNULL(c.BLOCKINCONTAINERPOS, 1)";

    public const string GET_SKILL_REQS = @"
        SELECT  r.WISKILLREQUIREMENTID, r.WIID, r.SKILLID,
                s.SKILLNAME, r.MINLEVELNO, r.ENFORCEMENT,
                CAST(r.ISMANDATORY AS BIT) AS IsMandatory,
                r.REMARKS
        FROM    WI.MWISKILLREQUIREMENT r
        JOIN    MSKILL              s ON s.SKILLID = r.SKILLID
        WHERE   r.WIID = @WiId";

    public const string GET_COMPETENCY_REQS = @"
        SELECT  r.WICOMPETENCYREQUIREMENTID, r.WIID, r.COMPETENCYID,
                --c.COMPETENCYNAME, 
                r.MINLEVELNO, r.ENFORCEMENT,
                CAST(r.ISMANDATORY AS BIT) AS IsMandatory,
                r.REMARKS
        FROM    WI.MWICOMPETENCYREQUIREMENT r
        --JOIN    WI.MCOMPETENCY              c ON c.COMPETENCYID = r.COMPETENCYID
        WHERE   r.WIID = @WiId";

    public const string UPSERT_WI_HEADER = @"
        MERGE WI.MWI AS target
        USING (SELECT @WiId AS WIID) AS source ON target.WIID = source.WIID
        WHEN MATCHED THEN UPDATE SET
            WISUBPROCESSID   = @WiSubProcessId,
            WIPROCESSID      = @WiProcessId,
            WITITLE          = @WiTitle,
            ACTIVITYTYPE     = @ActivityType,
            ACTIVITYSUBTYPE  = @ActivitySubType,
            WIVERSION        = @WiVersion,
            WISTATUS         = @WiStatus,
            DISPLAYCONTEXT   = @DisplayContext,
            STEPADVANCEMODE  = @StepAdvanceMode,
            STDCYCLETIMEMINS = @StdCycleTimeMins,
            EFFECTIVEDATE    = @EffectiveDate,
            REVIEWDUEDATE    = @ReviewDueDate,
            TAGS             = @Tags,
            DESCRIPTION      = @WiDescription,
            STATUS           = @Status,
            SORTORDER        = @SortOrder,
            VERSION          = VERSION,
            MODIFIEDBYID     = @ModifiedById,
            MODIFIEDON       = GETDATE()
        WHEN NOT MATCHED THEN INSERT
            (WIID, WISUBPROCESSID, WIPROCESSID, WICODE, WITITLE,
             ACTIVITYTYPE, ACTIVITYSUBTYPE, WIVERSION, WISTATUS,
             DISPLAYCONTEXT, STEPADVANCEMODE, STDCYCLETIMEMINS,
             EFFECTIVEDATE, REVIEWDUEDATE, TAGS, DESCRIPTION,
             STATUS, SORTORDER, APPROVEDBYID,
             CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, SOURCETYPE, TENANTID)
        VALUES
            (@WiId, @WiSubProcessId, @WiProcessId, @WiCode, @WiTitle,
             @ActivityType, @ActivitySubType, @WiVersion, @WiStatus,
             @DisplayContext, @StepAdvanceMode, @StdCycleTimeMins,
             @EffectiveDate, @ReviewDueDate, @Tags, @WiDescription,
             @Status, @SortOrder, @ApprovedById,
             @CreatedById, GETDATE(), @ModifiedById, GETDATE(), @SourceType, @TenantId);";

    public const string UPDATE_WI_STATUS = @"
        UPDATE WI.MWI
        SET    WISTATUS      = @WiStatus,
               VERSION       = VERSION,
               MODIFIEDBYID  = @ModifiedById,
               MODIFIEDON    = GETDATE()
        WHERE  WIID     = @WiId
          AND  TENANTID = @TenantId";

    public const string SOFT_DELETE_SECTIONS = @"DELETE FROM WI.MWISECTION
                                                 WHERE WIID = @wiId";

    public const string SOFT_DELETE_STEPS = @"DELETE FROM WI.MWISTEP
                                              WHERE WIID = @wiId";

    public const string SOFT_DELETE_CONTENTS = @"DELETE FROM WI.MWISTEPCONTENT
                                                WHERE WISTEPID IN
                                                (
                                                    SELECT WISTEPID
                                                    FROM WI.MWISTEP
                                                    WHERE WIID = @wiId);";

    public const string INSERT_SECTION = @"
        INSERT INTO WI.MWISECTION (WISECTIONID, WIID, SLNO, SECTIONTITLE, DESCRIPTION)
        VALUES (@WiSectionId, @WiId, @SlNo, @SectionTitle, @Description)";

    public const string INSERT_STEP = @"
        INSERT INTO WI.MWISTEP
            (WISTEPID, WIID, WISECTIONID, SLNO, STEPDISPLAYNO, STEPTITLE,
             WARNINGLEVEL, STDDURATIONSECS, TOOLSREQUIRED, ISOPTIONAL,
             REQUIRESIGNOFF, AUTOADVANCESECS, ISACTIVE)
        VALUES
            (@WiStepId, @WiId, @WiSectionId, @SlNo, @StepDisplayNo, @StepTitle,
             @WarningLevel, @StdDurationSecs, @ToolsRequired, @IsOptional,
             @RequireSignOff, @AutoAdvanceSecs, 1)";

    public const string INSERT_STEP_CONTENT = @"INSERT INTO WI.MWISTEPCONTENT
                                            (
                                                WISTEPCONTENTID, 
                                                WISTEPID, 
                                                SLNO,
                                                CONTENTTYPE,
                                                CONTENTSOURCE,
                                                CONTENTREF,
                                                MEDIACAPTION,
                                                DISPLAYDURATIONSECS,
                                                LOOPMEDIA,
                                                ZOOMREGION,
                                                APPLICABLEMODELIDS,
                                                ISACTIVE,
                                                CONTAINERID,
                                                CONTAINERTYPE,
                                                CONTAINERLABEL,
                                                ZONENAME,
                                                BLOCKINCONTAINERPOS
                                            )
                                            VALUES
                                            (
                                                @WiStepContentId,
                                                @WiStepId,
                                                @WiStepContentSlNo,
                                                @ContentType,
                                                @ContentSource,
                                                @ContentRef,
                                                @MediaCaption,
                                                @DisplayDurationSecs,
                                                @LoopMedia,
                                                @ZoomRegion,
                                                @ApplicableModelIds,
                                                @IsActive,
                                                @ContainerId,
                                                @ContainerType,
                                                @ContainerLabel,
                                                @ZoneName,
                                                @BlockInContainerPos
                                            );";

    public const string DELETE_SKILL_REQS = @"
        DELETE FROM WI.MWISKILLREQUIREMENT WHERE WIID = @WiId";

    public const string INSERT_SKILL_REQ = @"
        INSERT INTO WI.MWISKILLREQUIREMENT
            (WISKILLREQUIREMENTID, WIID, SKILLID, MINLEVELNO, ENFORCEMENT, ISMANDATORY, REMARKS)
        VALUES
            (@WiSkillRequirementId, @WiId, @SkillId, @MinLevelNo, @Enforcement, @IsMandatory, @Remarks)";

    public const string DELETE_COMPETENCY_REQS = @"
        DELETE FROM WI.MWICOMPETENCYREQUIREMENT WHERE WIID = @WiId";

    public const string INSERT_COMPETENCY_REQ = @"
        INSERT INTO WI.MWICOMPETENCYREQUIREMENT
            (WICOMPETENCYREQUIREMENTID, WIID, COMPETENCYID, MINLEVELNO, ENFORCEMENT, ISMANDATORY, REMARKS)
        VALUES
            (@WiCompetencyRequirementId, @WiId, @CompetencyId, @MinLevelNo, @Enforcement, @IsMandatory, @Remarks)";

    public const string INVALIDATE_ACKS = @"
        UPDATE WI.TWIACKNOWLEDGEMENT
        SET    ISVALID      = 0,
               MODIFIEDBYID = @ModifiedById,
               MODIFIEDON   = GETDATE()
        WHERE  WIID     = @WiId
          AND  TENANTID = @TenantId
          AND  ISVALID  = 1";
    public const string GET_SELECTLIST_WI = @"SELECT
                                                 WIID AS Id,
                                                 WICODE AS Code,
                                                 WITITLE AS Name
                                              FROM WI.MWI
                                              ORDER BY WITITLE;";
    // DeleteWiMaster cascade — WI.MWI has 9 direct-FK children plus 4 further transitive
    // levels (confirmed live via sys.foreign_keys 2026-08-15, not guessed); run each of
    // these in order, in a transaction (WiMasterDAL.DeleteWiMaster), deepest-first.
    // TWIEXECUTION is unusual — it has an FK to MWI directly AND to MWIACTIVITYSCHEDULE,
    // so it must be deleted before MWIACTIVITYSCHEDULE despite MWIACTIVITYSCHEDULE's own
    // parent (MWIASSIGNMENT) being "higher" in the WI.MWI tree.
    public const string DELETE_CASCADE_CHECKLIST_RESPONSES = @"
        DELETE FROM WI.TWISTEPCONTENTCHECKLISTRESPONSE
        WHERE WISTEPCONTENTID IN (
            SELECT c.WISTEPCONTENTID FROM WI.MWISTEPCONTENT c
            JOIN WI.MWISTEP s ON s.WISTEPID = c.WISTEPID
            WHERE s.WIID = @wiId)";

    public const string DELETE_CASCADE_STEP_EXECUTIONS = @"
        DELETE FROM WI.TWISTEPEXECUTION
        WHERE WISTEPID IN (SELECT WISTEPID FROM WI.MWISTEP WHERE WIID = @wiId)
           OR WIEXECUTIONID IN (SELECT WIEXECUTIONID FROM WI.TWIEXECUTION WHERE WIID = @wiId)";

    public const string DELETE_CASCADE_EXECUTIONS = @"
        DELETE FROM WI.TWIEXECUTION WHERE WIID = @wiId";

    public const string DELETE_CASCADE_STEP_CONTENT = @"
        DELETE FROM WI.MWISTEPCONTENT
        WHERE WISTEPID IN (SELECT WISTEPID FROM WI.MWISTEP WHERE WIID = @wiId)";

    public const string DELETE_CASCADE_STEPS = @"
        DELETE FROM WI.MWISTEP WHERE WIID = @wiId";

    public const string DELETE_CASCADE_SECTIONS = @"
        DELETE FROM WI.MWISECTION WHERE WIID = @wiId";

    public const string DELETE_CASCADE_ACTIVITY_SCHEDULES = @"
        DELETE FROM WI.MWIACTIVITYSCHEDULE
        WHERE WIASSIGNMENTID IN (SELECT WIASSIGNMENTID FROM WI.MWIASSIGNMENT WHERE WIID = @wiId)";

    public const string DELETE_CASCADE_ASSIGNMENTS = @"
        DELETE FROM WI.MWIASSIGNMENT WHERE WIID = @wiId";

    public const string DELETE_CASCADE_ACKNOWLEDGEMENTS = @"
        DELETE FROM WI.TWIACKNOWLEDGEMENT WHERE WIID = @wiId";

    public const string DELETE_CASCADE_SKILL_REQUIREMENTS = @"
        DELETE FROM WI.MWISKILLREQUIREMENT WHERE WIID = @wiId";

    public const string DELETE_CASCADE_COMPETENCY_REQUIREMENTS = @"
        DELETE FROM WI.MWICOMPETENCYREQUIREMENT WHERE WIID = @wiId";

    public const string DELETE_CASCADE_VERSION_SNAPSHOTS = @"
        DELETE FROM WI.LWIVERSIONSNAPSHOT WHERE WIID = @wiId";

    public const string DELETE_CASCADE_VERSIONS = @"
        DELETE FROM WI.LWIVERSION WHERE WIID = @wiId";

    public const string DELETE_WIMASTER = @"DELETE FROM WI.MWI
                                            WHERE WIID = @wiId";

    // Q-WI-1: Swimlane — resolve effective workstation for every step of a WI.
    // Step-level WORKSTATIONID overrides the WI-level assignment when <> -1.
    // Resolution: COALESCE(NULLIF(s.WORKSTATIONID, -1), a.WORKSTATIONID)
    // Indexes: IX_MWISTEP_WORKSTATIONID_OVERRIDE + MWIASSIGNMENT primary lookup
    public const string GET_STEPS_WITH_EFFECTIVE_WORKSTATION = @"
        SELECT  s.WIID                                                AS WiId,
                s.WISTEPID                                            AS WiStepId,
                s.SLNO                                                AS SlNo,
                s.STEPTITLE                                           AS StepTitle,
                s.WARNINGLEVEL                                        AS WarningLevel,
                s.STDDURATIONSECS                                     AS StdDurationSecs,
                COALESCE(NULLIF(s.WORKSTATIONID, -1),
                         a.WORKSTATIONID)                             AS EffectiveWorkstationId,
                CAST(CASE WHEN s.WORKSTATIONID <> -1
                          THEN 1 ELSE 0 END AS BIT)                  AS IsStepLevelOverride,
                ws.WORKSTATIONCODE                                    AS WorkstationCode,
                ws.WORKSTATIONNAME                                    AS WorkstationName,
                sec.SECTIONTITLE                                      AS SectionTitle
        FROM       WI.MWISTEP       s
        LEFT JOIN  WI.MWISECTION    sec ON sec.WISECTIONID   = s.WISECTIONID
                                       AND s.WISECTIONID    <> -1
        LEFT JOIN  WI.MWIASSIGNMENT a   ON a.WIID            = s.WIID
                                       AND a.STATUS         = 1
        LEFT JOIN  DBO.MWORKSTATION ws  ON ws.WORKSTATIONID  =
                                           COALESCE(NULLIF(s.WORKSTATIONID, -1),
                                                    a.WORKSTATIONID)
        WHERE  s.WIID       = @WiId
          AND  s.ISACTIVE= 1
        ORDER BY s.SLNO";
}