/*
 * Copyright © 2019 Lisk Foundation
 *
 * See the LICENSE file at the top-level directory of this distribution
 * for licensing information.
 *
 * Unless otherwise agreed in a custom licensing agreement with the Lisk Foundation,
 * no part of this software, including this file, may be copied, modified,
 * propagated, or distributed except according to the terms contained in the
 * LICENSE file.
 *
 * Removal or modification of this copyright notice is prohibited.
 */


/*
  DESCRIPTION: Create table 'rounds_fees', calculate fees & populate it, set triggers.

  PARAMETERS: None
*/

-- Create table 'rounds_fees' for storing fees
CREATE TABLE IF NOT EXISTS "rounds_fees"(
	"height"    INT     NOT NULL,
	"timestamp" INT     NOT NULL,
	"fees"      BIGINT  NOT NULL,
	"publicKey" BYTEA   NOT NULL
);

-- Drop existing triggers and functions
DROP FUNCTION IF EXISTS rounds_fees_init();
DROP TRIGGER  IF EXISTS rounds_fees_delete ON "blocks";
DROP FUNCTION IF EXISTS round_fees_delete();
DROP TRIGGER  IF EXISTS rounds_fees_insert ON "blocks";
DROP FUNCTION IF EXISTS round_fees_insert();

-- Create function that compute all fees for previous rounds and insert them to 'rounds_fees'
CREATE FUNCTION rounds_fees_init() RETURNS void LANGUAGE PLPGSQL AS $$
	DECLARE
		row record;
	BEGIN
		RAISE NOTICE 'Calculating fees for rounds, please wait...';
		FOR row IN
			SELECT
				-- Round number
				CEIL(height / 101::float)::int AS round
			FROM blocks
			-- Perform only for rounds that are completed and not present in 'rounds_fees'
			WHERE height % 101 = 0 AND height NOT IN (SELECT height FROM rounds_fees)
			-- Group by round
			GROUP BY CEIL(height / 101::float)::int
			-- Order by round
			ORDER BY CEIL(height / 101::float)::int ASC
		LOOP
			WITH
				-- Selecting all blocks of round
				round AS (SELECT timestamp, height, "generatorPublicKey", "totalFee" FROM blocks WHERE CEIL(height / 101::float)::int = row.round),
				-- Calculating total fees of round
				fees AS (SELECT SUM("totalFee") AS total, FLOOR(SUM("totalFee") / 101) AS single FROM round),
				-- Get last delegate and timestamp of round's last block
				last AS (SELECT "generatorPublicKey" AS pk, timestamp FROM round ORDER BY height DESC LIMIT 1)
			INSERT INTO rounds_fees
				SELECT
					-- Block height
					round.height,
					-- Timestamp of last round's block
					last.timestamp,
					-- Calculating real fee reward for delegate:
					-- Rounded fee per delegate + remaining fees if block is last one of round
					(fees.single + (CASE WHEN last.pk = round."generatorPublicKey" AND last.timestamp = round.timestamp THEN (fees.total - fees.single * 101) ELSE 0 END)) AS fees,
					-- Delegate public key
					round."generatorPublicKey" AS "publicKey"
				FROM last, fees, round
				-- Sort fees by block height
				ORDER BY round.height ASC;
		END LOOP;
	RETURN;
END $$;
-- Execution time

-- Execute 'rounds_fees_init' function
SELECT rounds_fees_init();

-- Create indexes on all columns of 'rounds_fees' + additional index for round
CREATE INDEX IF NOT EXISTS "rounds_fees_timestamp" ON "rounds_fees" ("timestamp");
CREATE INDEX IF NOT EXISTS "rounds_fees_fees" ON "rounds_fees" ("fees");
CREATE INDEX IF NOT EXISTS "rounds_fees_height" ON "rounds_fees" ("height");
CREATE INDEX IF NOT EXISTS "rounds_fees_round" ON "rounds_fees" ((CEIL(height / 101::float)::int));
CREATE INDEX IF NOT EXISTS "rounds_fees_public_key" ON "rounds_fees" ("publicKey");

-- Create function for deleting round fees when last block of round is deleted
CREATE FUNCTION round_fees_delete() RETURNS TRIGGER LANGUAGE PLPGSQL AS $$
	BEGIN
		DELETE FROM rounds_fees WHERE CEIL(height / 101::float)::int = (CEIL(OLD.height / 101::float)::int);
	RETURN NULL;
END $$;

-- Create trigger that will execute 'round_fees_delete' after deletion of last block of round
CREATE TRIGGER rounds_fees_delete
	AFTER DELETE ON blocks
	FOR EACH ROW
	WHEN (OLD.height % 101 = 0)
	EXECUTE PROCEDURE round_fees_delete();

-- Create function for inserting round fees when last block of round is inserted
CREATE FUNCTION round_fees_insert() RETURNS TRIGGER LANGUAGE PLPGSQL AS $$
	BEGIN
		WITH
			-- Selecting all blocks of round
			round AS (SELECT timestamp, height, "generatorPublicKey", "totalFee" FROM blocks WHERE CEIL(height / 101::float)::int = CEIL(NEW.height / 101::float)::int),
			-- Calculating total fees of round
			fees AS (SELECT SUM("totalFee") AS total, FLOOR(SUM("totalFee") / 101) AS single FROM round),
			-- Get last delegate and timestamp of round's last block
			last AS (SELECT "generatorPublicKey" AS pk, timestamp FROM round ORDER BY height DESC LIMIT 1)
		INSERT INTO rounds_fees
			SELECT
				-- Block height
				round.height,
				-- Timestamp of last round's block
				last.timestamp,
				-- Calculating real fee reward for delegate:
				-- Rounded fee per delegate + remaining fees if block is last one of round
				(fees.single + (CASE WHEN last.pk = round."generatorPublicKey" AND last.timestamp = round.timestamp THEN (fees.total - fees.single * 101) ELSE 0 END)) AS fees,
				-- Delegate public key
				round."generatorPublicKey" AS "publicKey"
			FROM last, fees, round
			-- Sort fees by block height
			ORDER BY round.height ASC;
	RETURN NULL;
END $$;

-- Create trigger that will execute 'round_fees_insert' after insertion of last block of round
CREATE TRIGGER rounds_fees_insert
	AFTER INSERT ON blocks
	FOR EACH ROW
	WHEN (NEW.height % 101 = 0)
	EXECUTE PROCEDURE round_fees_insert();
