namespace MMDAL.Query.ParameterPartyOverride
{
    // CRUD over MPARAMETERPARTYOVERRIDE — standing Min/Max threshold override
    // for one Parameter within a ParameterSet, scoped to a Party (+ optionally
    // an Item). See 20260811_MPARAMETERPARTYOVERRIDE_Create.sql.
    public static class ParameterPartyOverrideQB
    {
        public const string GET_PARAMETERPARTYOVERRIDE = @"
            SELECT
                O.PARAMETERPARTYOVERRIDEID AS ParameterPartyOverrideId,
                O.PARTYID                  AS PartyId,
                PT.PARTYCODE               AS PartyCode,
                PT.PARTYNAME               AS PartyName,
                O.ITEMID                   AS ItemId,
                IM.ITEMCODE                AS ItemCode,
                IM.ITEMNAME                AS ItemName,
                O.PARAMETERSETID           AS ParameterSetId,
                O.PARAMETERID              AS ParameterId,
                P.PARAMETERCODE            AS ParameterCode,
                P.PARAMETERNAME            AS ParameterName,
                O.MINVALUE                 AS MinValue,
                O.MAXVALUE                 AS MaxValue,
                O.STATUS                   AS Status,
                O.VERSION                  AS Version,
                O.CREATEDBYID              AS CreatedById,
                O.CREATEDON                AS CreatedOn,
                O.MODIFIEDBYID             AS ModifiedById,
                O.MODIFIEDON               AS ModifiedOn
            FROM MPARAMETERPARTYOVERRIDE O
            INNER JOIN MPARAMETER P ON P.PARAMETERID = O.PARAMETERID
            LEFT JOIN MPARTY PT ON PT.PARTYID = O.PARTYID
            LEFT JOIN MITEM IM ON IM.ITEMID = O.ITEMID
            WHERE O.PARAMETERPARTYOVERRIDEID = @ParameterPartyOverrideId;";

        public const string GET_PARAMETERPARTYOVERRIDE_LIST = @"
            SELECT
                O.PARAMETERPARTYOVERRIDEID AS ParameterPartyOverrideId,
                O.PARTYID                  AS PartyId,
                PT.PARTYCODE               AS PartyCode,
                PT.PARTYNAME               AS PartyName,
                O.ITEMID                   AS ItemId,
                IM.ITEMCODE                AS ItemCode,
                IM.ITEMNAME                AS ItemName,
                O.PARAMETERSETID           AS ParameterSetId,
                O.PARAMETERID              AS ParameterId,
                P.PARAMETERCODE            AS ParameterCode,
                P.PARAMETERNAME            AS ParameterName,
                O.MINVALUE                 AS MinValue,
                O.MAXVALUE                 AS MaxValue,
                O.STATUS                   AS Status,
                O.VERSION                  AS Version,
                O.CREATEDBYID              AS CreatedById,
                O.CREATEDON                AS CreatedOn,
                O.MODIFIEDBYID             AS ModifiedById,
                O.MODIFIEDON               AS ModifiedOn
            FROM MPARAMETERPARTYOVERRIDE O
            INNER JOIN MPARAMETER P ON P.PARAMETERID = O.PARAMETERID
            LEFT JOIN MPARTY PT ON PT.PARTYID = O.PARTYID
            LEFT JOIN MITEM IM ON IM.ITEMID = O.ITEMID
            WHERE O.STATUS <> 2
              AND (@PartyId IS NULL OR O.PARTYID = @PartyId)
              AND (@ItemId IS NULL OR O.ITEMID = @ItemId)
              AND (@ParameterSetId IS NULL OR O.PARAMETERSETID = @ParameterSetId)
            ORDER BY O.PARAMETERPARTYOVERRIDEID DESC;";

        // Upsert semantics live in the BLL (checks whether a row already exists
        // for this PartyId+ItemId+ParameterSetId+ParameterId before choosing
        // INSERT vs UPDATE) — see ParameterPartyOverrideBLL.SaveParameterPartyOverride.
        public const string GET_PARAMETERPARTYOVERRIDE_BY_SCOPE = @"
            SELECT
                O.PARAMETERPARTYOVERRIDEID AS ParameterPartyOverrideId,
                O.PARTYID                  AS PartyId,
                O.ITEMID                   AS ItemId,
                O.PARAMETERSETID           AS ParameterSetId,
                O.PARAMETERID              AS ParameterId,
                O.MINVALUE                 AS MinValue,
                O.MAXVALUE                 AS MaxValue,
                O.STATUS                   AS Status,
                O.VERSION                  AS Version,
                O.CREATEDBYID              AS CreatedById,
                O.CREATEDON                AS CreatedOn,
                O.MODIFIEDBYID             AS ModifiedById,
                O.MODIFIEDON               AS ModifiedOn
            FROM MPARAMETERPARTYOVERRIDE O
            WHERE O.STATUS <> 2
              AND O.PARTYID = @PartyId
              AND O.ITEMID = @ItemId
              AND O.PARAMETERSETID = @ParameterSetId
              AND O.PARAMETERID = @ParameterId;";

        public const string SAVE_PARAMETERPARTYOVERRIDE = @"
            INSERT INTO MPARAMETERPARTYOVERRIDE
                (PARAMETERPARTYOVERRIDEID, PARTYID, ITEMID, PARAMETERSETID, PARAMETERID,
                 MINVALUE, MAXVALUE, STATUS, VERSION,
                 CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
            VALUES
                (@ParameterPartyOverrideId, @PartyId, @ItemId, @ParameterSetId, @ParameterId,
                 @MinValue, @MaxValue, @Status, @Version,
                 @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn);";

        public const string UPDATE_PARAMETERPARTYOVERRIDE = @"
            UPDATE MPARAMETERPARTYOVERRIDE
            SET
                MINVALUE     = @MinValue,
                MAXVALUE     = @MaxValue,
                VERSION      = @Version,
                MODIFIEDBYID = @ModifiedById,
                MODIFIEDON   = @ModifiedOn
            WHERE PARAMETERPARTYOVERRIDEID = @ParameterPartyOverrideId;";

        public const string DELETE_PARAMETERPARTYOVERRIDE = @"
            UPDATE MPARAMETERPARTYOVERRIDE
            SET STATUS = 2, MODIFIEDBYID = @ModifiedById, MODIFIEDON = @ModifiedOn
            WHERE PARAMETERPARTYOVERRIDEID = @ParameterPartyOverrideId;";
    }
}