--
-- File Transmission Unicast stored procedures
--

CREATE OR REPLACE FUNCTION kcFTUMarkUnicastSites(
   CHAR(36),
   INTEGER,
   CHAR(1) )
RETURNS VOID AS '
DECLARE
   _tuuid ALIAS FOR $1;
   _failAttempts ALIAS FOR $2;
   _allowMulticast ALIAS FOR $3;
   _isBroadcast CHAR(1);
   _ackMode CHAR(1);
   _lastSent TIMESTAMP;
   _cnt INTEGER;
BEGIN

	SELECT IsBroadcast, AckReportMode, LastTxEndTime INTO _isBroadcast, _ackMode, _lastSent
	FROM SendTransmissions WHERE TransmissionUUID = _tuuid;

	IF( _allowMulticast = ''F'' OR _ackMode <> ''N'') THEN
	BEGIN
		SELECT COUNT(TransmissionUUID) INTO _cnt FROM SendTransmissionSites WHERE TransmissionUUID=_tuuid;
		IF( _cnt = 0 AND _isBroadcast = ''T'' ) THEN
		BEGIN
			INSERT INTO SendTransmissionSites (TransmissionUUID, SiteID, Unicast, DeliveryStatus)
			SELECT _tuuid, SiteID, ''T'', ''N''
			FROM UnicastSendSites
			JOIN ReceiveSites AS R ON R.ReceiveSiteID = UnicastSendSites.ReceiveSiteID
			WHERE UnicastSendSites.Disable = ''F'' AND UnicastSendSites.AllowMulticast = _allowMulticast;
		END;
		END IF;

		IF( _cnt <> 0 AND _isBroadcast <> ''T'' ) THEN
		BEGIN
			-- update the  the site IDs in SentTransmissionSites
			UPDATE SendTransmissionSites
			SET Unicast = ''T''
				WHERE TransmissionUUID = _tuuid
			AND Unicast <> ''T'' AND DeliveryStatus = ''N''
			AND SiteID IN
				(SELECT SiteID
				FROM UnicastSendSites
				JOIN ReceiveSites AS R ON R.ReceiveSiteID = UnicastSendSites.ReceiveSiteID
				WHERE UnicastSendSites.Disable = ''F'' AND UnicastSendSites.AllowMulticast = _allowMulticast);
		END;
		END IF;
	END;
	END IF;

	-- for sites that acknowledged the receiving of the file set Unicast = ''F''
	IF( _ackmode = ''A'' ) THEN
	BEGIN
		-- report always
		UPDATE SendTransmissionSites
		SET Unicast = ''F''
		WHERE TransmissionUUID = _tuuid
		AND SiteID IN
			(SELECT SiteID
			FROM TransmissionAck AS A
			WHERE A.Status IN (''V'', ''C'', ''D'', ''N'')
			AND A.TransmissionUUID = _tuuid);
	END;
	ELSIF( _ackmode = ''F'' AND _lastSent IS NOT NULL ) THEN
	BEGIN
		-- report on incomplete
		UPDATE SendTransmissionSites
		SET Unicast = ''F''
		WHERE TransmissionUUID = _tuuid
		AND SiteID NOT IN
			(SELECT SiteID
			FROM TransmissionAck AS A
			WHERE A.Status IN (''E'', ''I'')
			AND A.TransmissionUUID = _tuuid
			AND A.ReceivedOn > _lastSent);
	END;
	END IF;

	-- skip sites which fail too often
	IF( _failAttempts > 0 ) THEN
	BEGIN
		-- report always
		UPDATE SendTransmissionSites
		SET DeliveryStatus = ''K''
		WHERE TransmissionUUID = _tuuid
		AND Unicast = ''T''
		AND SiteID IN
			(SELECT SiteID
			FROM UnicastSendSites
			JOIN ReceiveSites AS R ON R.ReceiveSiteID = UnicastSendSites.ReceiveSiteID
			WHERE UnicastSendSites.FailedConnectionCount > _failAttempts
			AND UnicastSendSites.LastConnectionTime > CURRENT_TIMESTAMP + INTERVAL ''4 hours'');
	END;
	END IF;

	RETURN;

END;
' LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION kcFTULog(
   CHAR(36),
   CHAR(36),
   INTEGER,
   INTEGER,
   VARCHAR(255),
   INTEGER,
   INTEGER,
   DECIMAL,
   DECIMAL,
   CHAR(1),
   VARCHAR(255),
   INTEGER,
   DECIMAL,
   DECIMAL)
RETURNS VOID AS '
DECLARE
   _logid ALIAS FOR $1;
   _tuuid ALIAS FOR $2;
   _tid ALIAS FOR $3;
   _siteid ALIAS FOR $4;
   _sendToAddress ALIAS FOR $5;
   _sendToPort ALIAS FOR $6;
   _duration ALIAS FOR $7;
   _sentBytes ALIAS FOR $8;
   _sentPackets ALIAS FOR $9;
   _status ALIAS FOR $10;
   _errorMessage ALIAS FOR $11;
   _channelID ALIAS FOR $12;
   _bytes ALIAS FOR $13;
   _packets ALIAS FOR $14;
   _tmp INTEGER;
BEGIN

	-- log the transmission

	INSERT INTO UnicastSendLog (
      LogID
	 , TransmissionUUID
	 , UnicastTransmissionID
	 , SiteID
	 , SendToAddress
	 , SendToPort
	 , Duration
	 , SentBytes
	 , SentPackets
	 , Status
	 , ErrorMessage
	 , ChannelID
   )
	VALUES (
      _logid
	, _tuuid
	, _tid
	, _siteid
	, _sendToAddress
	, _sendToPort
	, _duration
	, _sentBytes
	, _sentPackets
	, _status
	, _errorMessage
	, _channelID
	);

	-- update byte/packet count
	UPDATE SendTransmissions
	SET TransmissionCount = TransmissionCount + 1
	, SentTxPackets = SentTxPackets + _sentPackets
	, SentTxBytes = SentTxBytes + _sentBytes
	WHERE TransmissionUUID = _tuuid AND TransmissionCount > 0;

	IF NOT FOUND THEN
	BEGIN
		UPDATE SendTransmissions
		SET TransmissionCount = TransmissionCount + 1
		, SentTxPackets = SentTxPackets + _sentPackets
		, SentTxBytes = SentTxBytes + _sentBytes
		, TxPackets = _packets
		, TxBytes = _bytes
		WHERE TransmissionUUID = _tuuid;

		-- remote management calls
		SELECT COUNT(proname) INTO _tmp FROM pg_proc WHERE proname = ''kctxreceivefilesent'';
		IF (_tmp > 0) THEN
			PERFORM kcTxReceiveFileSent(_tuuid);
		END IF;
	END;
   END IF;

   RETURN;

END;
' LANGUAGE 'plpgsql';