init_db.sql

CREATE DATABASE staticpi;

GRANT ALL PRIVILEGES ON DATABASE staticpi TO staticpi;

\c staticpi

CREATE TABLE IF NOT EXISTS ip_address (
	ip_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	timestamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
	ip INET UNIQUE NOT NULL
);

GRANT ALL ON ip_address TO staticpi;

GRANT USAGE,
SELECT
	ON SEQUENCE ip_address_ip_id_seq TO staticpi;

CREATE INDEX ip_index ON ip_address(ip);

CREATE TABLE IF NOT EXISTS user_agent (
	user_agent_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	timestamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
	user_agent_string TEXT UNIQUE NOT NULL
);

GRANT ALL ON user_agent TO staticpi;

GRANT USAGE,
SELECT
	ON SEQUENCE user_agent_user_agent_id_seq TO staticpi;

CREATE INDEX user_agent_string_index ON user_agent(user_agent_string);

CREATE TABLE IF NOT EXISTS user_level (
	user_level_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	custom_device_name BOOLEAN NOT NULL,
	max_number_of_devices SMALLINT NOT NULL,
	max_message_size_in_bytes INT NOT NULL,
	max_monthly_bandwidth_in_bytes BIGINT NOT NULL,
	max_clients_per_device SMALLINT NOT NULL,
	structured_data BOOLEAN NOT NULL,
	device_password BOOLEAN NOT NULL,
	-- could make this a postgres enum
	user_level_name TEXT NOT NULL UNIQUE CHECK (user_level_name IN ('free', 'pro', 'admin'))
);

GRANT ALL ON user_level TO staticpi;

GRANT USAGE,
SELECT
	ON SEQUENCE user_level_user_level_id_seq TO staticpi;

CREATE INDEX user_level_index ON user_level(user_level_name);

-- Insert different user levels into db
INSERT INTO
	user_level(
		custom_device_name,
		max_number_of_devices,
		max_message_size_in_bytes,
		max_monthly_bandwidth_in_bytes,
		max_clients_per_device,
		structured_data,
		device_password,
		user_level_name
	)
VALUES
	(
		false,
		1,
		10 * 1000,
		5 * 1000000,
		1,
		FALSE,
		FALSE,
		'free'
	);

INSERT INTO
	user_level(
		custom_device_name,
		max_number_of_devices,
		max_message_size_in_bytes,
		max_monthly_bandwidth_in_bytes,
		max_clients_per_device,
		structured_data,
		device_password,
		user_level_name
	)
VALUES
	(
		true,
		20,
		5 * 1000 * 1000,
		100 * 100000000 :: bigint,
		100,
		TRUE,
		TRUE,
		'pro'
	);

INSERT INTO
	user_level(
		custom_device_name,
		max_number_of_devices,
		max_message_size_in_bytes,
		max_monthly_bandwidth_in_bytes,
		max_clients_per_device,
		structured_data,
		device_password,
		user_level_name
	)
VALUES
	(
		true,
		20,
		10 * 1000 * 1000,
		100 * 1000000000 :: bigint,
		100,
		TRUE,
		TRUE,
		'admin'
	);

CREATE TABLE IF NOT EXISTS email_address (
	email_address_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	timestamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
	email TEXT NOT NULL UNIQUE
);

GRANT ALL ON email_address TO staticpi;

GRANT USAGE,
SELECT
	ON SEQUENCE email_address_email_address_id_seq TO staticpi;

CREATE INDEX email_address_index ON email_address(email);

-- At the moment can only have a single api key attached to a device
-- maybe use this for registered_user password as well?
CREATE TABLE IF NOT EXISTS device_password (
	device_password_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	timestamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
	password_hash TEXT
);

GRANT ALL ON device_password TO staticpi;

GRANT USAGE,
SELECT
	ON SEQUENCE device_password_device_password_id_seq TO staticpi;

CREATE TABLE IF NOT EXISTS registered_user (
	registered_user_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	full_name TEXT NOT NULL,
	email_address_id BIGINT UNIQUE REFERENCES email_address(email_address_id) NOT NULL,
	active BOOLEAN DEFAULT FALSE,
	password_hash TEXT NOT NULL,
	timestamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
	ip_id BIGINT REFERENCES ip_address(ip_id) NOT NULL,
	user_agent_id BIGINT REFERENCES user_agent(user_agent_id) NOT NULL,
	user_level_id BIGINT REFERENCES user_level(user_level_id) NOT NULL
);

GRANT ALL ON registered_user TO staticpi;

GRANT USAGE,
SELECT
	ON SEQUENCE registered_user_registered_user_id_seq TO staticpi;

CREATE INDEX email_address_id_index ON registered_user(email_address_id);

CREATE TABLE IF NOT EXISTS invite_code (
	invite_code_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	registered_user_id BIGINT NOT NULL REFERENCES registered_user(registered_user_id),
	timestamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
	ip_id BIGINT REFERENCES ip_address(ip_id) NOT NULL,
	user_agent_id BIGINT REFERENCES user_agent(user_agent_id) NOT NULL,
	invite TEXT UNIQUE NOT NULL CHECK(LENGTH(invite) >= 12),
	count SMALLINT NOT NULL
);

GRANT ALL ON invite_code TO staticpi;

GRANT USAGE,
SELECT
	ON SEQUENCE invite_code_invite_code_id_seq TO staticpi;

CREATE INDEX invite_index ON invite_code(invite);

CREATE TABLE IF NOT EXISTS password_reset (
	password_reset_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	timestamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
	registered_user_id BIGINT NOT NULL REFERENCES registered_user(registered_user_id) ON DELETE CASCADE,
	reset_string TEXT NOT NULL UNIQUE CHECK (reset_string ~ '^[0-7][0-9A-HJKMNP-TV-Z]{25}$'),
	ip_id BIGINT REFERENCES ip_address(ip_id) NOT NULL,
	user_agent_id BIGINT REFERENCES user_agent(user_agent_id) NOT NULL,
	consumed BOOLEAN DEFAULT false
);

GRANT ALL ON password_reset TO staticpi;

GRANT USAGE,
SELECT
	ON SEQUENCE password_reset_password_reset_id_seq TO staticpi;

CREATE INDEX reset_string_index ON password_reset(reset_string);

CREATE INDEX registered_user_id ON password_reset(registered_user_id);

CREATE TABLE IF NOT EXISTS login_history (
	login_history_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	registered_user_id BIGINT NOT NULL REFERENCES registered_user(registered_user_id) ON DELETE CASCADE,
	timestamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
	ip_id BIGINT REFERENCES ip_address(ip_id) NOT NULL,
	user_agent_id BIGINT REFERENCES user_agent(user_agent_id) NOT NULL,
	session_name TEXT,
	success BOOLEAN
);

GRANT ALL ON login_history TO staticpi;

GRANT USAGE,
SELECT
	ON SEQUENCE login_history_login_history_id_seq TO staticpi;

CREATE TABLE IF NOT EXISTS login_attempt (
	login_attempt_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	registered_user_id BIGINT UNIQUE REFERENCES registered_user(registered_user_id) ON DELETE CASCADE,
	login_attempt_number INT DEFAULT 0
);

GRANT ALL ON login_attempt TO staticpi;

GRANT USAGE,
SELECT
	ON SEQUENCE login_attempt_login_attempt_id_seq TO staticpi;

CREATE TABLE IF NOT EXISTS two_fa_secret (
	two_fa_secret_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	timestamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
	registered_user_id BIGINT UNIQUE REFERENCES registered_user(registered_user_id) ON DELETE CASCADE,
	ip_id BIGINT REFERENCES ip_address(ip_id) NOT NULL,
	user_agent_id BIGINT REFERENCES user_agent(user_agent_id) NOT NULL,
	two_fa_secret TEXT DEFAULT NULL,
	always_required BOOLEAN DEFAULT FALSE
);

GRANT ALL ON two_fa_secret TO staticpi;

GRANT USAGE,
SELECT
	ON SEQUENCE two_fa_secret_two_fa_secret_id_seq TO staticpi;

CREATE TABLE IF NOT EXISTS two_fa_backup (
	two_fa_backup_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	timestamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
	registered_user_id BIGINT NOT NULL REFERENCES registered_user(registered_user_id) ON DELETE CASCADE,
	two_fa_backup_code TEXT NOT NULL,
	ip_id BIGINT REFERENCES ip_address(ip_id) NOT NULL,
	user_agent_id BIGINT REFERENCES user_agent(user_agent_id) NOT NULL,
	UNIQUE (registered_user_id, two_fa_backup_code)
);

GRANT ALL ON two_fa_backup TO staticpi;

GRANT USAGE,
SELECT
	ON SEQUENCE two_fa_backup_two_fa_backup_id_seq TO staticpi;

-- remove active, just delete instead?
CREATE TABLE IF NOT EXISTS api_key (
	api_key_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	api_key_string TEXT NOT NULL UNIQUE CHECK (api_key_string ~ '^[A-F0-9]{128}$'),
	registered_user_id BIGINT NOT NULL REFERENCES registered_user(registered_user_id) ON DELETE CASCADE,
	timestamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
	ip_id BIGINT REFERENCES ip_address(ip_id) NOT NULL,
	user_agent_id BIGINT REFERENCES user_agent(user_agent_id) NOT NULL,
	active BOOLEAN DEFAULT TRUE
);

GRANT ALL ON api_key TO staticpi;

GRANT USAGE,
SELECT
	ON SEQUENCE api_key_api_key_id_seq TO staticpi;

-- Create index on api_key_string for quicker look ups
CREATE INDEX api_key_string_index ON api_key(api_key_string);

CREATE TABLE IF NOT EXISTS device_name (
	device_name_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	-- check max 64?
	name_of_device TEXT UNIQUE NOT NULL,
	timestamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL
);

GRANT ALL ON device_name TO staticpi;

GRANT USAGE,
SELECT
	ON SEQUENCE device_name_device_name_id_seq TO staticpi;

CREATE INDEX device_name_index ON device_name(name_of_device);

-- At the moment can only have a single api key attached to a device
CREATE TABLE IF NOT EXISTS device (
	device_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	registered_user_id BIGINT NOT NULL REFERENCES registered_user(registered_user_id) ON DELETE CASCADE,
	timestamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
	ip_id BIGINT REFERENCES ip_address(ip_id) NOT NULL,
	user_agent_id BIGINT REFERENCES user_agent(user_agent_id) NOT NULL,
	api_key_id BIGINT UNIQUE REFERENCES api_key(api_key_id) ON DELETE CASCADE NOT NULL,
	device_name_id BIGINT NOT NULL REFERENCES device_name(device_name_id) ON DELETE CASCADE,
	structured_data BOOLEAN DEFAULT FALSE,
	paused BOOLEAN DEFAULT FALSE NOT NULL,
	client_password_id BIGINT UNIQUE REFERENCES device_password(device_password_id) ON DELETE CASCADE,
	device_password_id BIGINT UNIQUE REFERENCES device_password(device_password_id) ON DELETE CASCADE,
	max_clients SMALLINT DEFAULT 1 CHECK(
		max_clients > 0
		AND max_clients < 101
	),
	active BOOLEAN DEFAULT true
);

GRANT ALL ON device TO staticpi;

GRANT USAGE,
SELECT
	ON SEQUENCE device_device_id_seq TO staticpi;

-- Unique index, so that a single user cannot have two devices that are both active with the same name
CREATE UNIQUE INDEX unique_user_device_name ON device (device_name_id, registered_user_id)
WHERE
	(active is true);

CREATE INDEX api_key_id ON device(api_key_id);

CREATE INDEX registered_user_id_index ON device(registered_user_id);

CREATE INDEX device_name_id ON device(device_name_id);

CREATE TABLE IF NOT EXISTS connection (
	connection_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	device_id BIGINT REFERENCES device(device_id) ON DELETE CASCADE NOT NULL,
	api_key_id BIGINT REFERENCES api_key(api_key_id) ON DELETE CASCADE NOT NULL,
	user_agent_id BIGINT REFERENCES user_agent(user_agent_id) NOT NULL,
	timestamp_online TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
	timestamp_offline TIMESTAMPTZ,
	ip_id BIGINT REFERENCES ip_address(ip_id) NOT NULL,
	is_pi BOOLEAN NOT NULL
);

GRANT ALL ON connection TO staticpi;

GRANT USAGE,
SELECT
	ON SEQUENCE connection_connection_id_seq TO staticpi;

CREATE INDEX device_id_index ON connection(device_id);

CREATE TABLE IF NOT EXISTS email_subject (
	email_subject_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	timestamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
	subject TEXT UNIQUE NOT NULL
);

GRANT ALL ON email_subject TO staticpi;

GRANT USAGE,
SELECT
	ON SEQUENCE email_subject_email_subject_id_seq TO staticpi;

CREATE TABLE IF NOT EXISTS email_log (
	email_log_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	timestamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
	sent BOOLEAN DEFAULT TRUE NOT NULL,
	ip_id BIGINT REFERENCES ip_address(ip_id) NOT NULL,
	user_agent_id BIGINT REFERENCES user_agent(user_agent_id) NOT NULL,
	email_address_id BIGINT REFERENCES email_address(email_address_id) ON DELETE CASCADE,
	email_subject_id BIGINT REFERENCES email_subject(email_subject_id)
);

GRANT ALL ON email_log TO staticpi;

GRANT USAGE,
SELECT
	ON SEQUENCE email_log_email_log_id_seq TO staticpi;

CREATE TABLE IF NOT EXISTS contact_message (
	contact_message_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	timestamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
	ip_id BIGINT REFERENCES ip_address(ip_id) NOT NULL,
	user_agent_id BIGINT REFERENCES user_agent(user_agent_id) NOT NULL,
	email_address_id BIGINT REFERENCES email_address(email_address_id) ON DELETE CASCADE,
	registered_user_id BIGINT REFERENCES registered_user(registered_user_id) ON DELETE CASCADE,
	message TEXT NOT NULL
);

GRANT ALL ON contact_message TO staticpi;

GRANT USAGE,
SELECT
	ON SEQUENCE contact_message_contact_message_id_seq TO staticpi;

CREATE TABLE IF NOT EXISTS banned_email_domain (
	banned_email_domain_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	domain TEXT UNIQUE NOT NULL
);

GRANT ALL ON banned_email_domain TO staticpi;

GRANT USAGE,
SELECT
	ON SEQUENCE banned_email_domain_banned_email_domain_id_seq TO staticpi;

CREATE TABLE IF NOT EXISTS hourly_bandwidth (
	hourly_bandwidth_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	device_id BIGINT REFERENCES device(device_id) ON DELETE CASCADE NOT NULL,
	timestamp TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
	size_in_bytes BIGINT NOT NULL,
	is_pi BOOLEAN NOT NULL,
	is_counted BOOLEAN NOT NULL
);

GRANT ALL ON hourly_bandwidth TO staticpi;

GRANT USAGE,
SELECT
	ON SEQUENCE hourly_bandwidth_hourly_bandwidth_id_seq TO staticpi;

CREATE UNIQUE INDEX on hourly_bandwidth(
	extract(
		year
		FROM
			(timestamp AT TIME ZONE 'UTC')
	),
	extract(
		month
		FROM
			(timestamp AT TIME ZONE 'UTC')
	),
	extract(
		day
		FROM
			(timestamp AT TIME ZONE 'UTC')
	),
	extract(
		hour
		FROM
			(timestamp AT TIME ZONE 'UTC')
	),
	device_id,
	is_pi,
	is_counted
);
/***********
** AUDITS **
***********/

-- Function to minus jsonb from jsonb, works with nested jsons as well!
CREATE OR REPLACE FUNCTION jsonb_minus ( arg1 jsonb, arg2 jsonb ) RETURNS jsonb AS $$
SELECT
	COALESCE(json_object_agg(
	key,
	CASE
		WHEN jsonb_typeof(value) = 'object' AND arg2 -> key IS NOT NULL
			THEN jsonb_minus(value, arg2 -> key)
			ELSE value
		END
	), '{}')::jsonb
FROM
	jsonb_each(arg1)
WHERE
	arg1 -> key <> arg2 -> key
	OR arg2 -> key IS NULL
$$ LANGUAGE SQL;

-- create the actual operator, using minus symbol, to use the jsonb_minus functions
CREATE OPERATOR - (
	PROCEDURE = jsonb_minus,
	LEFTARG = jsonb,
	RIGHTARG = jsonb
);

/**
** Registered User Audit, executed on update, delete, and, insert
*/
CREATE TABLE IF NOT EXISTS registered_user_audit (
	user_audit_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	table_name TEXT NOT NULL,
	user_name TEXT,
	action_timestamp TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
	action TEXT NOT NULL CHECK (action IN ('i','d','u')),
	old_values jsonb,
	new_values jsonb,
	difference jsonb,
	query TEXT
);

GRANT ALL ON registered_user_audit TO staticpi;
GRANT USAGE, SELECT ON SEQUENCE registered_user_audit_user_audit_id_seq TO staticpi;

CREATE FUNCTION registered_user_modified_func() RETURNS TRIGGER AS $body$
BEGIN
IF tg_op = 'UPDATE' THEN
	INSERT into registered_user_audit (table_name,	user_name, action, old_values, new_values, difference, query)
	VALUES (tg_table_name::TEXT, current_user, 'u', to_jsonb(OLD), to_jsonb(NEW), to_jsonb(OLD) - to_jsonb(NEW), current_query());
	RETURN new;
ELSIF tg_op = 'DELETE' THEN
	INSERT into registered_user_audit ( table_name, user_name, action, old_values, query)
	VALUES (tg_table_name::TEXT, current_user, 'd', to_jsonb(OLD), current_query());
	RETURN old;
ELSIF tg_op = 'INSERT' THEN
	INSERT into registered_user_audit (table_name, user_name, action, new_values, query)
	VALUES (tg_table_name::TEXT, current_user, 'i', to_jsonb(NEW), current_query());
	RETURN new;
END IF;
END;
$body$
LANGUAGE plpgsql;

CREATE TRIGGER registered_user_audit_trig
BEFORE INSERT OR UPDATE OR DELETE
ON registered_user
FOR EACH ROW
EXECUTE PROCEDURE registered_user_modified_func();

/**
** Device Audit, executed on update, delete, and, insert
*/
CREATE TABLE IF NOT EXISTS device_audit (
	device_audit_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	table_name TEXT NOT NULL,
	user_name TEXT,
	action_timestamp TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
	action TEXT NOT NULL CHECK (action IN ('i','d','u')),
	old_values jsonb,
	new_values jsonb,
	difference jsonb,
	query TEXT
);

GRANT ALL ON device_audit TO staticpi;
GRANT USAGE, SELECT ON SEQUENCE device_audit_device_audit_id_seq TO staticpi;

CREATE FUNCTION device_audit_func() RETURNS TRIGGER AS $body$
BEGIN
IF tg_op = 'UPDATE' THEN
	INSERT into device_audit (table_name, user_name, action, old_values, new_values, difference, query)
	VALUES (tg_table_name::TEXT, current_user, 'u', to_jsonb(OLD), to_jsonb(NEW), to_jsonb(OLD) - to_jsonb(NEW), current_query());
	RETURN new;
ELSIF tg_op = 'DELETE' THEN
	INSERT into device_audit ( table_name, user_name, action, old_values, query)
	VALUES (tg_table_name::TEXT, current_user, 'd', to_jsonb(OLD), current_query());
	RETURN old;
ELSIF tg_op = 'INSERT' THEN
	INSERT into device_audit (table_name, user_name, action, new_values, query)
	VALUES (tg_table_name::TEXT, current_user, 'i', to_jsonb(NEW), current_query());
	RETURN new;
END IF;
END;
$body$
LANGUAGE plpgsql;

CREATE TRIGGER device_trig
BEFORE INSERT OR UPDATE OR DELETE
ON device
FOR EACH ROW
EXECUTE PROCEDURE device_audit_func();

/**
** Api Key Audit, executed on update, delete, and, insert
*/
CREATE TABLE IF NOT EXISTS api_key_audit (
	api_key_audit_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	table_name TEXT NOT NULL,
	user_name TEXT,
	action_timestamp TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
	action TEXT NOT NULL CHECK (action IN ('i','d','u')),
	old_values jsonb,
	new_values jsonb,
	difference jsonb,
	query TEXT
);

GRANT ALL ON api_key_audit TO staticpi;
GRANT USAGE, SELECT ON SEQUENCE api_key_audit_api_key_audit_id_seq TO staticpi;

CREATE FUNCTION api_key_audit_func() RETURNS TRIGGER AS $body$
BEGIN
IF tg_op = 'UPDATE' THEN
	INSERT into api_key_audit (table_name, user_name, action, old_values, new_values, difference, query)
	VALUES (tg_table_name::TEXT, current_user, 'u', to_jsonb(OLD), to_jsonb(NEW), to_jsonb(OLD) - to_jsonb(NEW), current_query());
	RETURN new;
ELSIF tg_op = 'DELETE' THEN
	INSERT into api_key_audit ( table_name, user_name, action, old_values, query)
	VALUES (tg_table_name::TEXT, current_user, 'd', to_jsonb(OLD), current_query());
	RETURN old;
ELSIF tg_op = 'INSERT' THEN
	INSERT into api_key_audit (table_name, user_name, action, new_values, query)
	VALUES (tg_table_name::TEXT, current_user, 'i', to_jsonb(NEW), current_query());
	RETURN new;
END IF;
END;
$body$
LANGUAGE plpgsql;

CREATE TRIGGER api_key_trig
BEFORE INSERT OR UPDATE OR DELETE
ON api_key
FOR EACH ROW
EXECUTE PROCEDURE api_key_audit_func();


/**
** Two FA Audit, executed on update, delete, and, insert
*/
CREATE TABLE IF NOT EXISTS two_fa_secret_audit (
	two_fa_secret_audit_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	table_name TEXT NOT NULL,
	user_name TEXT,
	action_timestamp TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
	action TEXT NOT NULL CHECK (action IN ('i','d','u')),
	old_values jsonb,
	new_values jsonb,
	difference jsonb,
	query TEXT
);

GRANT ALL ON two_fa_secret_audit TO staticpi;
GRANT USAGE, SELECT ON SEQUENCE two_fa_secret_audit_two_fa_secret_audit_id_seq TO staticpi;

CREATE FUNCTION two_fa_secret_audit_func() RETURNS TRIGGER AS $body$
BEGIN
IF tg_op = 'UPDATE' THEN
	INSERT into two_fa_secret_audit (table_name, user_name, action, old_values, new_values, difference, query)
	VALUES (tg_table_name::TEXT, current_user, 'u', to_jsonb(OLD), to_jsonb(NEW), to_jsonb(OLD) - to_jsonb(NEW), current_query());
	RETURN new;
ELSIF tg_op = 'DELETE' THEN
	INSERT into two_fa_secret_audit ( table_name, user_name, action, old_values, query)
	VALUES (tg_table_name::TEXT, current_user, 'd', to_jsonb(OLD), current_query());
	RETURN old;
ELSIF tg_op = 'INSERT' THEN
	INSERT into two_fa_secret_audit (table_name, user_name, action, new_values, query)
	VALUES (tg_table_name::TEXT, current_user, 'i', to_jsonb(NEW), current_query());
	RETURN new;
END IF;
END;
$body$
LANGUAGE plpgsql;

CREATE TRIGGER two_fa_secret_trig
BEFORE INSERT OR UPDATE OR DELETE
ON two_fa_secret
FOR EACH ROW
EXECUTE PROCEDURE two_fa_secret_audit_func();

/**
** Two FA Backup, executed on update, delete, and, insert
*/
CREATE TABLE IF NOT EXISTS two_fa_backup_audit (
	two_fa_backup_audit_id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
	table_name TEXT NOT NULL,
	user_name TEXT,
	action_timestamp TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
	action TEXT NOT NULL CHECK (action IN ('i','d','u')),
	old_values jsonb,
	new_values jsonb,
	difference jsonb,
	query TEXT
);

GRANT ALL ON two_fa_backup_audit TO staticpi;
GRANT USAGE, SELECT ON SEQUENCE two_fa_backup_audit_two_fa_backup_audit_id_seq TO staticpi;

CREATE FUNCTION two_fa_backup_audit_func() RETURNS TRIGGER AS $body$
BEGIN
IF tg_op = 'UPDATE' THEN
	INSERT into two_fa_backup_audit (table_name, user_name, action, old_values, new_values, difference, query)
	VALUES (tg_table_name::TEXT, current_user, 'u', to_jsonb(OLD), to_jsonb(NEW), to_jsonb(OLD) - to_jsonb(NEW), current_query());
	RETURN new;
ELSIF tg_op = 'DELETE' THEN
	INSERT into two_fa_backup_audit ( table_name, user_name, action, old_values, query)
	VALUES (tg_table_name::TEXT, current_user, 'd', to_jsonb(OLD), current_query());
	RETURN old;
ELSIF tg_op = 'INSERT' THEN
	INSERT into two_fa_backup_audit (table_name, user_name, action, new_values, query)
	VALUES (tg_table_name::TEXT, current_user, 'i', to_jsonb(NEW), current_query());
	RETURN new;
END IF;
END;
$body$
LANGUAGE plpgsql;

CREATE TRIGGER two_fa_backup_trig
BEFORE INSERT OR UPDATE OR DELETE
ON two_fa_backup
FOR EACH ROW
EXECUTE PROCEDURE two_fa_backup_audit_func();