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

namespace MMDAL.Query.ParameterSet
{
    public static class ParameterSetQB
    {
        // Min/Max resolution priority (party-scoped, never cross-contaminates other parties):
        //   1. MPARAMETERPARTYOVERRIDE row for exact (PARTYID, ITEMID, PARAMETERSETID, PARAMETERID)
        //   2. MPARAMETERPARTYOVERRIDE row for (PARTYID, ITEMID=-1, ...) — party-wide, any item
        //   3. MPARAMETER.MINVALUE/MAXVALUE — global default, shared by every party
        // This is a STANDING configured value — set directly via SaveParameterThreshold,
        // independent of any inspection ever being recorded. @partyid/@itemid are optional
        // (NULL = no party context) — in that case only the MPARAMETER default applies,
        // same as before this feature existed.
        //
        // Average/Result/InputValue/InputText/InputDate/FinalValue are separate: they come
        // from the most recent TINSPECTIONPARAMETER row per PARAMETERID, scoped to this
        // party's own inspection history when @partyid is given (transactional history,
        // not a threshold — never falls back to another party's data).
        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(PPO.MINVALUE, P.MINVALUE) AS ParameterMinValue,
    COALESCE(PPO.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 ParameterUOMId,
    UOM.UOMCODE AS ParameterUOMCode,
    UOM.UOMNAME AS ParameterUOMName

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 O.MINVALUE, O.MAXVALUE
    FROM MPARAMETERPARTYOVERRIDE O
    WHERE O.STATUS <> 2
      AND @partyid IS NOT NULL
      AND O.PARTYID = @partyid
      AND O.PARAMETERSETID = PS.PARAMETERSETID
      AND O.PARAMETERID = P.PARAMETERID
      AND (O.ITEMID = @itemid OR O.ITEMID = -1)
    ORDER BY CASE WHEN O.ITEMID = @itemid THEN 0 ELSE 1 END
) PPO
OUTER APPLY (
    SELECT TOP 1 TIP.AVERAGE, TIP.RESULT,
           CAST(TIP.INPUTVALUE AS NVARCHAR(50)) AS INPUTVALUE,
           TIP.INPUTTEXT, TIP.INPUTDATE, TIP.FINALVALUE
    FROM TINSPECTIONPARAMETER TIP
    INNER JOIN TINSPECTIONDETAIL TID ON TID.INSPECTIONDETAILID = TIP.INSPECTIONDETAILID
    INNER JOIN TINSPECTION TI ON TI.INSPECTIONID = TID.INSPECTIONID
    WHERE TIP.PARAMETERID = P.PARAMETERID
      AND @partyid IS NOT NULL
      AND TI.PARTYID = @partyid
      AND (@itemid IS NULL OR TID.ITEMID = @itemid)
    ORDER BY TIP.CREATEDON DESC, TIP.INSPECTIONPARAMETERID DESC
) LIP
WHERE PS.PARAMETERSETID = @parametersetid
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.
        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";





    }
}