CREATE OR REPLACE FUNCTION public.write_off_inventory(
	date timestamp with time zone,
	figi character varying,
	quantity numeric)
    RETURNS integer
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
    
AS $BODY$DECLARE
	rec packages%rowtype;
	current_quantity numeric(15) = $3;
	current_amount numeric(15, 2);
	current_commission numeric(15, 2);
BEGIN	
	-- Списание методом ФИФО
	-- Удалить из партий
	
	FOR rec IN SELECT * FROM packages AS packages
	WHERE
	packages.date <= $1
	AND packages.figi LIKE $2 
	ORDER BY date ASC LOOP
	
		IF rec.quantity >= current_quantity THEN
			-- Количества хватает, просто списываем и выходим из цикла.
			current_amount := rec.amount / rec.quantity * current_quantity;
			current_commission := rec.commission / rec.quantity * current_quantity;
			UPDATE packages SET quantity = packages.quantity - current_quantity, amount = packages.amount - current_amount, commission = packages.commission - current_commission WHERE id = rec.id;
			INSERT INTO costs ("date", figi, quantity, amount, commission) VALUES ($1, $2, current_quantity, current_amount, current_commission);
			EXIT;
		ELSE
			-- Количества не хватает, берем сколько есть, корректируем current_quantity и продолжаем цикл.
			IF rec.quantity <> 0 THEN
				current_amount := (rec.amount / rec.quantity * rec.quantity);
				current_commission := (rec.commission / rec.quantity * rec.quantity);
			ELSE
				current_amount := 0;
				current_commission := 0;
			END IF;
			UPDATE packages SET quantity = packages.quantity - rec.quantity, amount = packages.amount - current_amount, commission = packages.commission - current_commission WHERE id = rec.id;
			INSERT INTO costs ("date", figi, quantity, amount, commission) VALUES ($1, $2, rec.quantity, current_amount, current_commission);
			IF rec.quantity <> 0 THEN
				current_quantity = current_quantity - rec.quantity;
			END IF;
		END IF;
		
	END LOOP;
	
	DELETE FROM packages AS packages WHERE packages.quantity = 0 AND amount = 0 AND commission = 0;
	RETURN 0;
END;
$BODY$;

CREATE OR REPLACE FUNCTION public.update_costs(
	"from" timestamp with time zone,
	"to" timestamp with time zone)
    RETURNS integer
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
    
AS $BODY$
DECLARE
	rec sales%rowtype;
BEGIN
	-- Обновить себестоимость продаж
	DElETE FROM costs WHERE date BETWEEN "from" AND "to";
	FOR rec IN SELECT * FROM sales LOOP
		PERFORM write_off_inventory(rec."date", rec.figi, rec.quantity);
	END LOOP;
	
	RETURN 0;
END;
$BODY$;