using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WiDAL.DTOs;
using static System.Collections.Specialized.BitVector32;

namespace WiDAL.Query.Wi
{
    public class WiSectionQB
    {
        public const string GET_BY_ID = @"SELECT
                                            WISECTIONID   AS WiSectionId,
                                            WIID          AS WiId,
                                            SLNO          AS SectionSlNo,
                                            SECTIONTITLE  AS SectionTitle,
                                            DESCRIPTION   AS SectionDescription
                                        FROM WI.MWISECTION
                                        WHERE WISECTIONID = @wiSectionId";

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

        public const string UPDATE = @"UPDATE WI.MWISECTION
                                        SET
                                            WIID          = @WiId,
                                            SLNO          = @SectionSlNo,
                                            SECTIONTITLE  = @SectionTitle,
                                            DESCRIPTION   = @SectionDescription
                                        WHERE
                                            WISECTIONID   = @WiSectionId";

        public const string GET_SELECTLIST_WISECTION = @"SELECT 
                                                        WISECTIONID AS Id,
                                                        SECTIONTITLE AS Name
                                                    FROM WI.MWISECTION
                                                    ORDER BY SECTIONTITLE;";

        public const string DELETE_WISECTION = @"DELETE FROM WI.MWISECTION
                                                 WHERE WISECTIONID =@wiSectionId;";

        public const string GET_LIST_SECTION_STEP = @"SELECT
                                                    -- Parent : WISECTION
                                                    WS.WISECTIONID              AS WiSectionId,
                                                    WS.WIID AS WiId,
                                                    WS.SLNO AS SectionSlNo,
                                                    WS.SECTIONTITLE AS SectionTitle,
                                                    WS.DESCRIPTION AS SectionDescription,

                                                    -- Child : WISTEP
                                                    ST.WISTEPID AS WiStepId,
                                                    ST.WIID AS StepWiId,
                                                    ST.WISECTIONID AS StepSectionId,
                                                    ST.SLNO AS WiStepSlNo,
                                                    ST.STEPDISPLAYNO AS StepDisplayNo,
                                                    ST.STEPTITLE AS StepTitle,
                                                    ST.WARNINGLEVEL AS WarningLevel,
                                                    ST.STDDURATIONSECS AS StdDurationSecs,
                                                    ST.TOOLSREQUIRED AS ToolsRequired,
                                                    ST.ISOPTIONAL AS IsOptional,
                                                    ST.REQUIRESIGNOFF AS RequireSignOff,
                                                    ST.AUTOADVANCESECS AS AutoAdvanceSecs,
                                                    ST.ISACTIVE AS IsActive,
                                                    ST.CONTENTID AS ContentId,
                                                        -- Content
                                                    CNT.TITLE            AS ContentTitle

                                                FROM WI.MWISECTION WS

                                                LEFT JOIN WI.MWISTEP ST
                                                    ON ST.WISECTIONID = WS.WISECTIONID

                                                LEFT JOIN WI.MWI WI
                                                    ON WI.WIID = WS.WIID

                                                LEFT JOIN TCONTENT CNT
                                                    ON CNT.CONTENTID = ST.CONTENTID;";
    }
}