-- Function: fn_purgedeleted(integer)

DROP FUNCTION fn_purgedeleted(integer);

CREATE OR REPLACE FUNCTION fn_purgedeleted(threshold integer DEFAULT 7)
  RETURNS bigint AS
$BODY$

/* *************************************************************************  */
/*                                                                            */
/*    fn_purgedeleted( threshold )                                            */
/*                                                                            */
/*    Returns:  Number of documented purged                                   */
/*                                                                            */
/*  This stored procedure purges deleted records older than [threshold] days  */
/*  If a threshold value is not passed, it will use a value of 7              */
/*                                                                            */
/*  To execute, issue the following sql command:                              */
/*         select fn_purgedeleted( threshold );                               */
/*                                                                            */
/* Copyright, 2017 No Magic, Inc.                                             */
/*                                                                            */
/* ************************************************************************** */
DECLARE
	_deleted_documents		bigint;
	_qname_id			bigint;
	_epoch_ms			bigint;
	_transaction_id			bigint;
	_day_interval			interval;

BEGIN
	_day_interval := (threshold::text || ' days')::interval;
	_epoch_ms := (EXTRACT('epoch' FROM (now() - _day_interval)::timestamp)*1000)::bigint;
	_qname_id := id FROM alf_qname WHERE local_name = 'deleted';
	_transaction_id := id FROM alf_transaction WHERE commit_time_ms <= _epoch_ms ORDER BY commit_time_ms DESC LIMIT 1;
	_deleted_documents := count(id) FROM alf_node WHERE type_qname_id = _qname_id AND transaction_id <= _transaction_id;
	DELETE FROM alf_node_properties WHERE node_id IN (SELECT id FROM alf_node WHERE type_qname_id = _qname_id AND transaction_id <= _transaction_id);
	DELETE FROM alf_node WHERE type_qname_id = _qname_id AND transaction_id <= _transaction_id;
	RETURN _deleted_documents;
END;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION fn_purgedeleted(integer)
  OWNER TO alfresco;
COMMENT ON FUNCTION fn_purgedeleted(integer) IS 'This stored procedure purges deleted docuemts older than X days';
