using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace FrameworkDAL.Query.ParameterSet
{
    public static class ParameterSetQB
    {
        // LatestInspParam: most recent TINSPECTIONPARAMETER row per PARAMETERID
        // (by CREATEDON, tie-broken by INSPECTIONPARAMETERID) — TINSPECTIONPARAMETER
        // has no ParameterSetId/InspectionId column to join through directly, so this
        // is "whatever was last recorded anywhere for this parameter" across all
        // inspections. Falls back to MPARAMETER.MINVALUE/MAXVALUE when the parameter
        // has never been recorded in an inspection yet.
        public const string GET_PARAMETERSET = @"
        SELECT
    PS.PARAMETERSETID AS ParameterSetId,
    PS.PARAMETERSETCODE AS ParameterSetCode,
    PS.PARAMETERSETNAME AS ParameterSetName,
    PS.REMARKS AS ParameterSetRemarks,
    PS.SORTORDER AS ParameterSetSortOrder,
    PS.STATUS AS ParameterSetStatus,
    PS.VERSION AS ParameterSetVersion,
    PS.SOURCETYPE AS ParameterSetSourceType,
    PS.CREATEDBYID AS ParameterSetCreatedById,
    PS.CREATEDON AS ParameterSetCreatedOn,
    PS.MODIFIEDBYID AS ParameterSetModifiedById,
    PS.MODIFIEDON AS ParameterSetModifiedOn,

    PSD.PARAMETERSETLINEID AS ParameterSetDetailLineId,
    PSD.PARAMETERSETID AS ParameterSetId,
    PS.PARAMETERSETCODE AS ParameterSetCode,
    PS.PARAMETERSETNAME AS ParameterSetName,
    PSD.SLNO AS ParameterSetDetailSlno,

    P.PARAMETERID AS ParameterId,
    P.PARAMETERCODE AS ParameterCode,
    P.PARAMETERNAME AS ParameterName,
    P.PARAMETERDATATYPE AS ParameterDataType,
    P.TYPEOFINFO AS ParameterTypeOfInfo,
    P.LOVTYPEID AS ParameterLovTypeId,
    P.PARAMETERDATASIZE AS ParameterDataSize,
    COALESCE(LIP.MINVALUE, P.MINVALUE) AS ParameterMinValue,
    COALESCE(LIP.MAXVALUE, P.MAXVALUE) AS ParameterMaxValue,
    LIP.AVERAGE    AS Average,
    LIP.RESULT     AS Result,
    LIP.INPUTVALUE AS InputValue,
    LIP.INPUTTEXT  AS InputText,
    LIP.INPUTDATE  AS InputDate,
    LIP.FINALVALUE AS FinalValue,

    PSD.ISTRANSACTIONGET AS ParameterSetDetailIsTransactionGet,
    PSD.SECTION AS ParameterSetDetailSection,
    PSD.REJECTIONTYPE AS ParameterSetDetailRejectionType,

    UOM.UOMID AS ParameterParameterUOMId,
    UOM.UOMCODE AS ParameterParameterUOMCode,
    UOM.UOMNAME AS ParameterParameterUOMName

FROM MPARAMETERSET PS

LEFT JOIN MPARAMETERSETDETAIL PSD ON PSD.PARAMETERSETID = PS.PARAMETERSETID
LEFT JOIN MPARAMETER P ON P.PARAMETERID = PSD.PARAMETERID
LEFT JOIN MUOM UOM ON UOM.UOMID = P.UOMID
OUTER APPLY (
    SELECT TOP 1 TIP.MINVALUE, TIP.MAXVALUE, TIP.AVERAGE, TIP.RESULT,
           CAST(TIP.INPUTVALUE AS NVARCHAR(50)) AS INPUTVALUE,
           TIP.INPUTTEXT, TIP.INPUTDATE, TIP.FINALVALUE
    FROM TINSPECTIONPARAMETER TIP
    WHERE TIP.PARAMETERID = P.PARAMETERID
    ORDER BY TIP.CREATEDON DESC, TIP.INSPECTIONPARAMETERID DESC
) LIP
WHERE PS.PARAMETERSETID = @parametersetid
ORDER BY PSD.SLNO;";


        // GetParameterSetDetailList: modern equivalent of the legacy GB4
        // Criteria.svc/List ObjectCode=PARAMETERSETDETAIL contract. Callers post a
        // CriteriaDTO (SectionCriteriaList/AttributesCriteriaList) with FieldName
        // "Parameter.Id" / "ParameterSet.Id" — ParameterSetDetailDAL remaps those
        // to PARAMETERID/PARAMETERSETID (see _filterableFields whitelist) before
        // CriteriaBuilder.Build fills in /*CRITERIA*/.
        public const string GET_PARAMETERSETDETAIL_LIST = @"
        SELECT
            PSD.PARAMETERSETLINEID AS ParameterSetDetailLineId,
            PSD.PARAMETERSETID AS ParameterSetId,
            PS.PARAMETERSETCODE AS ParameterSetCode,
            PS.PARAMETERSETNAME AS ParameterSetName,
            PSD.SLNO AS ParameterSetDetailSlno,

            P.PARAMETERID AS ParameterId,
            P.PARAMETERCODE AS ParameterCode,
            P.PARAMETERNAME AS ParameterName,
            P.PARAMETERDATATYPE AS ParameterDataType,
            P.TYPEOFINFO AS ParameterTypeOfInfo,
            P.LOVTYPEID AS ParameterLovTypeId,
            P.PARAMETERDATASIZE AS ParameterDataSize,
            P.MINVALUE AS ParameterMinValue,
            P.MAXVALUE AS ParameterMaxValue,

            PSD.ISTRANSACTIONGET AS ParameterSetDetailIsTransactionGet,
            PSD.SECTION AS ParameterSetDetailSection,
            PSD.REJECTIONTYPE AS ParameterSetDetailRejectionType,

            UOM.UOMID AS ParameterParameterUOMId,
            UOM.UOMCODE AS ParameterParameterUOMCode,
            UOM.UOMNAME AS ParameterParameterUOMName

        FROM MPARAMETERSETDETAIL PSD
        INNER JOIN MPARAMETERSET PS ON PS.PARAMETERSETID = PSD.PARAMETERSETID
        INNER JOIN MPARAMETER P ON P.PARAMETERID = PSD.PARAMETERID
        LEFT JOIN MUOM UOM ON UOM.UOMID = P.UOMID
        WHERE 1 = 1
        /*CRITERIA*/
        ORDER BY PSD.SLNO;";

        // Only lists ParameterSets that actually have detail rows — a ParameterSet
        // with no MPARAMETERSETDETAIL rows can't produce a parameter list downstream
        // (e.g. GetParameterSet, InspectionParamSetOverride detail fetch), so it must
        // never be pickable here.
        public const string GetSelectList_ParameterSet = @"
        SELECT
         PS.PARAMETERSETID AS Id,
         PS.PARAMETERSETCODE AS Code,
         PS.PARAMETERSETNAME AS Name
         FROM
         MPARAMETERSET PS
         WHERE EXISTS (
            SELECT 1 FROM MPARAMETERSETDETAIL PSD
            WHERE PSD.PARAMETERSETID = PS.PARAMETERSETID
         );";


        public const string SAVE_PARAMETERSET = @"INSERT INTO MPARAMETERSET
                                                (
                                                    PARAMETERSETID,
                                                    PARAMETERSETCODE,
                                                    PARAMETERSETNAME,
                                                    REMARKS,
                                                    SORTORDER,
                                                    STATUS,
                                                    VERSION,
                                                    SOURCETYPE,
                                                    CREATEDBYID,
                                                    CREATEDON,
                                                    MODIFIEDBYID,
                                                    MODIFIEDON
                                                )
                                                VALUES
                                                (
                                                    @ParameterSetId,
                                                    @ParameterSetCode,
                                                    @ParameterSetName,
                                                    @ParameterSetRemarks,
                                                    @ParameterSetSortOrder,
                                                    @ParameterSetStatus,
                                                    @ParameterSetVersion,
                                                    @ParameterSetSourceType,
                                                    @ParameterSetCreatedById,
                                                    @ParameterSetCreatedOn,
                                                    @ParameterSetModifiedById,
                                                    @ParameterSetModifiedOn
                                                );";

        public const string SAVE_PARAMETERSET_DETAIL = @"INSERT INTO MPARAMETERSETDETAIL
                                                        (
                                                            PARAMETERSETLINEID,
                                                            PARAMETERSETID,
                                                            SLNO,
                                                            PARAMETERID,
                                                            ISTRANSACTIONGET,
                                                            SECTION,
                                                            REJECTIONTYPE
                                                        )
                                                        VALUES
                                                        (
                                                            @ParameterSetDetailLineId,
                                                            @ParameterSetId,
                                                            @ParameterSetDetailSlno,
                                                            @ParameterId,
                                                            @ParameterSetDetailIsTransactionGet,
                                                            @ParameterSetDetailSection,
                                                            @ParameterSetDetailRejectionType
                                                        );";

        public const string UPDATE_PARAMETERSET = @"UPDATE MPARAMETERSET
                                                    SET
                                                        PARAMETERSETCODE = @ParameterSetCode,
                                                        PARAMETERSETNAME = @ParameterSetName,
                                                        REMARKS          = @ParameterSetRemarks,
                                                        SORTORDER        = @ParameterSetSortOrder,
                                                        STATUS           = @ParameterSetStatus,
                                                        VERSION          = @ParameterSetVersion,
                                                        SOURCETYPE       = @ParameterSetSourceType,
                                                        MODIFIEDBYID     = @ParameterSetModifiedById,
                                                        MODIFIEDON       = @ParameterSetModifiedOn

                                                    WHERE PARAMETERSETID = @ParameterSetId;";

        public const string UPDATE_PARAMETERSET_DETAIL = @"UPDATE MPARAMETERSETDETAIL
                                                        SET
                                                            PARAMETERSETID     = @ParameterSetId,
                                                            SLNO               = @ParameterSetDetailSlno,
                                                            PARAMETERID        = @ParameterId,
                                                            ISTRANSACTIONGET   = @ParameterSetDetailIsTransactionGet,
                                                            SECTION            = @ParameterSetDetailSection,
                                                            REJECTIONTYPE      = @ParameterSetDetailRejectionType
                                                        WHERE PARAMETERSETLINEID = @ParameterSetDetailLineId;";

        public const string DELETE_PARAMETERSET = @"DELETE FROM MPARAMETERSET WHERE PARAMETERSETID = @parametersetid";

        public const string DELETE_PARAMETERSET_DETAIL = @"DELETE FROM MPARAMETERSETDETAIL WHERE PARAMETERSETID = @parametersetid";

        public const string GET_PARAMETERSET_COUNT = @"SELECT COUNT(1)
                                                    FROM dbo.MPARAMETERSET
                                                    WHERE
                                                        (
                                                            @ParameterSetId <> -1 AND PARAMETERSETID = @ParameterSetId
                                                        )
                                                        AND PARAMETERSETID <> @ParameterSetId";





    }
}