DROP PROCEDURE IF EXISTS `seis_net`.`sp_insert_pick_ew`;
DELIMITER $$
CREATE DEFINER=`root`@`%.int.ingv.it` PROCEDURE `seis_net`.`sp_insert_pick_ew`(
	in_Pick_Flag           INT,
	in_Pin_Numb            BIGINT,
	in_Station             CHAR(5),
	in_Comp                CHAR(3),
	in_Net                 CHAR(2),
	in_Loc                 CHAR(2),
	in_Itr1                BIGINT,
	in_MinSmallZC          BIGINT,
	in_MinBigZC            BIGINT,
	in_MinPeakSize         BIGINT,
	in_MaxMint             BIGINT,
	in_i9                  BIGINT,
	in_RawDataFilt         DOUBLE,
	in_CharFuncFilt        DOUBLE,
	in_StaFilt             DOUBLE,
	in_LtaFilt             DOUBLE,
	in_EventThresh         DOUBLE,
	in_RmavFilt            DOUBLE,
	in_DeadSta             DOUBLE,
	in_CodaTerm            DOUBLE,
	in_AltCoda             DOUBLE,
	in_PreEvent            DOUBLE,
	in_Erefs               DOUBLE,
	in_ClipCount           BIGINT
	)

BEGIN

DECLARE l_id_cha                 INT                 DEFAULT -1;
DECLARE l_id_cha_max_rev         INT                 DEFAULT -1;
DECLARE l_id_cha_cmp_rev         INT                 DEFAULT -1;


	# Catch Channel ID into l_id_cha 
	SELECT channel.id INTO l_id_cha FROM `seis_net`.`channel`
	    JOIN `network` ON `channel`.`fk_network` = network.id
	    JOIN `station` ON `channel`.`fk_station` = station.id
	WHERE channel.end_time > NOW()
	    AND station.id_inter = in_Station
	    AND network.net_code = in_Net
	    AND substr(channel.id_cha,-3,3) = in_Comp;


	IF (l_id_cha > -1) THEN

	    # Catch Max number revision for l_id_cha into l_id_cha_max_rev
	    SELECT pick_ew.revision INTO l_id_cha_max_rev FROM `seis_net`.`pick_ew`
	    WHERE fk_channel_52 = l_id_cha
	    ORDER BY pick_ew.revision DESC
	    LIMIT 1;

	    SELECT pick_ew.revision INTO l_id_cha_cmp_rev FROM `seis_net`.`pick_ew`
	    WHERE
		    fk_channel_52 = l_id_cha AND 
		    Pick_Flag = in_Pick_Flag AND
		    Pin_Numb = in_Pin_Numb AND
		    Itr1 = in_Itr1 AND
		    MinSmallZC = in_MinSmallZC AND
		    MinBigZC = in_MinBigZC AND
		    MinPeakSize = in_MinPeakSize AND
		    MaxMint = in_MaxMint AND
		    i9 = in_i9 AND
		    RawDataFilt = in_RawDataFilt AND
		    CharFuncFilt = in_CharFuncFilt AND
		    StaFilt = in_StaFilt AND
		    LtaFilt = in_LtaFilt AND
		    EventThresh = in_EventThresh AND
		    RmavFilt = in_RmavFilt AND
		    DeadSta = in_DeadSta AND
		    CodaTerm = in_CodaTerm AND
		    AltCoda = in_AltCoda AND
		    PreEvent = in_PreEvent AND
		    Erefs = in_Erefs AND
		    ClipCount = in_ClipCount
	    ORDER BY pick_ew.revision DESC
	    LIMIT 1;

	    IF (l_id_cha_max_rev > -1  AND l_id_cha_cmp_rev > -1
		    AND l_id_cha_cmp_rev = l_id_cha_max_rev) THEN
		SELECT "Warning: equal to current revision" as '----------------------------------', in_Station as '     ', in_Comp as '   ', in_Net as '  ';
	    ELSE

		SELECT "Message: Insert value revision" as '------------------------------', (l_id_cha_max_rev+1) as '  ', in_Station as '     ', in_Comp as '   ', in_Net as '  ';

		INSERT INTO `seis_net`.`pick_ew` 
		    SET
			fk_channel_52 = l_id_cha, 
			revision = l_id_cha_max_rev+1,
			Pick_Flag = in_Pick_Flag,
			Pin_Numb = in_Pin_Numb,
			Itr1 = in_Itr1,
			MinSmallZC = in_MinSmallZC,
			MinBigZC = in_MinBigZC,
			MinPeakSize = in_MinPeakSize,
			MaxMint = in_MaxMint,
			i9 = in_i9,
			RawDataFilt = in_RawDataFilt,
			CharFuncFilt = in_CharFuncFilt,
			StaFilt = in_StaFilt,
			LtaFilt = in_LtaFilt,
			EventThresh = in_EventThresh,
			RmavFilt = in_RmavFilt,
			DeadSta = in_DeadSta,
			CodaTerm = in_CodaTerm,
			AltCoda = in_AltCoda,
			PreEvent = in_PreEvent,
			Erefs = in_Erefs,
			ClipCount = in_ClipCount
			;
	    END IF;


	ELSE
	    SELECT "ERROR: does not exists" as '----------------------', in_Station as '     ', in_Comp as '   ', in_Net as '  ';
	END IF;

END$$
DELIMITER ;
