-- Enable Extensions
CREATE EXTENSION IF NOT EXISTS pg_cron;
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Create table
CREATE TABLE if NOT EXISTS auctions (
	id UUID PRIMARY KEY,
	auctionid VARCHAR(255) NOT NULL,
	lbin DECIMAL(65,5),
	modified TIMESTAMP(2),
	UNIQUE (auctionid)
);

-- Update trigger function
CREATE OR REPLACE FUNCTION public.fn_update_modified_column()   
RETURNS TRIGGER AS $$
BEGIN
    NEW.modified = now();
    RETURN NEW;   
END;
$$ language 'plpgsql';

-- Update trigger
DROP TRIGGER IF EXISTS update_auction_modtime on auctions;
CREATE TRIGGER update_auction_modtime
BEFORE UPDATE ON auctions
FOR EACH ROW
EXECUTE PROCEDURE public.fn_update_modified_column();

-- Cleanup function
CREATE OR REPLACE FUNCTION public.cleanup_old_entries()
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
  temp_id UUID;
BEGIN
  FOR temp_id IN
	SELECT id
	FROM auctions
	WHERE EXTRACT(EPOCH FROM (NOW() - modified))/3600 >= 6  -- 6 hours
  LOOP
	DELETE FROM auctions
	WHERE auctions.id = temp_id;
  END LOOP;

  RETURN;
END;
$$;

-- Add job to pg_cron
DO $outer$
DECLARE job_exists boolean := false;
BEGIN
  SELECT (count(*) > 0)
  INTO job_exists
  FROM cron.job
  WHERE jobname = 'cleanup_old_entries_event';

  IF NOT job_exists THEN
	PERFORM cron.schedule(
	  'cleanup_old_entries_event',
	  '*/2 * * * *',
	  $sql$ SELECT public.cleanup_old_entries(); $sql$
	);
  END IF;
END;
$outer$;