------- Period ------------------------------------------------------------------------------------

/*
	First Day
	Get first day of month from date
	Ex.: from '2023-10-15' return '2023-10-01'
*/
DROP FUNCTION IF EXISTS firstDay(date);
CREATE FUNCTION firstDay(date) RETURNS date AS $$
	SELECT date_trunc('month', $1);
$$ LANGUAGE SQL;

/*
	Last Day
	Get last day of month from date
	Ex.: from '2023-10-15' return '2023-10-31'
*/
DROP FUNCTION IF EXISTS lastDay(date);
CREATE FUNCTION lastDay(date) RETURNS date AS $$
	SELECT date_trunc('month', $1) + INTERVAL '1 month' - INTERVAL '1 day'
$$ LANGUAGE SQL;

/*
	Period from Date in format 'yyyyMM' from date.
	Ex.: from '2023-10-25' return '202310'
*/
DROP FUNCTION IF EXISTS periodFromDate(date);
CREATE FUNCTION periodFromDate(date) RETURNS VARCHAR AS $$
	SELECT to_char($1, 'YYYYMM');
$$ LANGUAGE SQL;





------- Quarter ------------------------------------------------------------------------------------

/*
	Quarter Format
	Get quarter in format like 'Q4 2023'
*/
DROP FUNCTION IF EXISTS quarterFormat(date);
CREATE FUNCTION quarterFormat(date) RETURNS varchar AS $$
	SELECT to_char($1, '"Q"Q YYYY')
$$ LANGUAGE SQL;

/*
	Quarter First Day
	Get quarter firs dat
	Ex.: from '2023-09-15' returns '2023-07-01'
*/
DROP FUNCTION IF EXISTS quarterFirstDay(date);
CREATE FUNCTION quarterFirstDay(date) RETURNS date AS $$
	SELECT date_trunc('quarter', $1)
$$ LANGUAGE SQL;

/*
	Quarter Last Day
	Get quarter firs dat
	Ex.: from '2023-09-15' returns '2023-09-30'
*/
DROP FUNCTION IF EXISTS quarterLastDay(date);
CREATE FUNCTION quarterLastDay(date) RETURNS date AS $$
	SELECT date_trunc('quarter', $1) + interval '3 months' - interval '1 day'
$$ LANGUAGE SQL;





------- Leading Zeroes -----------------------------------------------------------------------------

/*
	Remove Leading Zeroes
	Ex.: from '0001234567' returns '1234567'
*/
DROP FUNCTION IF EXISTS removeLeadingZeroes(varchar);
CREATE FUNCTION removeLeadingZeroes(varchar) RETURNS VARCHAR AS $$
	SELECT regexp_replace($1, '^0*', '');
$$ LANGUAGE SQL;

/*
	Left Pad Zeroes
	First param is varchar where zereos to be add
	Second param is length of final varchar - default is 10
	Ex.: from '1234567' returns '0001234567'
*/
DROP FUNCTION IF EXISTS leftPadZeroes(varchar, int);
CREATE FUNCTION leftPadZeroes(varchar, int default 10) RETURNS VARCHAR AS $$
	SELECT right(concat(repeat('0', $2), $1), $2);
$$ LANGUAGE SQL;