/* 
 * Copyright (C) 2018 Nauta Tecnològica, SCP - All Rights Reserved.
 * Unauthorized copying, publishing, commercial usage and/or distribution of this file,
 * via any medium, is strictly prohibited without the express permission of the owner.
 */
/**
 * Author:  Jordi Santacana <jordistc@nautalogica.com>
 * Created: 23/05/2018
 */
--
-- procs
--
CREATE TABLE IF NOT EXISTS %tpl_prefix%proc (
	id		INTEGER
			PRIMARY KEY AUTOINCREMENT
			NOT NULL
			UNIQUE,
	ident		VARCHAR(255) DEFAULT NULL,
	action		VARCHAR(255) DEFAULT NULL,
	owner		INTEGER UNSIGNED KEY,
	pid			INTEGER UNSIGNED KEY DEFAULT NULL,
	asyncid		VARCHAR(64) DEFAULT NULL,
	uident		VARCHAR(255) DEFAULT NULL,
	sessid		VARCHAR(64) DEFAULT NULL,
	creat		DATE NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%S', 'now')),
	inici		DATE DEFAULT NULL,
	final		DATE DEFAULT NULL,
	status		INTEGER UNSIGNED DEFAULT NULL
);

CREATE INDEX IF NOT EXISTS idx_%tpl_prefix%proc_uident ON %tpl_prefix%proc(uident);
CREATE INDEX IF NOT EXISTS idx_%tpl_prefix%proc_sessid ON %tpl_prefix%proc(sessid);

--
-- response
--
CREATE TABLE IF NOT EXISTS %tpl_prefix%responses (
	proc_id
			INTEGER KEY
			DEFAULT NULL
			REFERENCES %tpl_prefix%proc(id)
				ON DELETE CASCADE,
	response	BLOB
);

--
-- procdata
--
CREATE TABLE IF NOT EXISTS %tpl_prefix%procdata (
	proc_id
			INTEGER KEY
			DEFAULT NULL
			REFERENCES %tpl_prefix%proc(id)
				ON DELETE CASCADE,
	procdata	BLOB
);

--
-- asyncdata
--
CREATE TABLE IF NOT EXISTS %tpl_prefix%asyncdata(
	proc_id
			INTEGER KEY
			DEFAULT NULL
			REFERENCES %tpl_prefix%proc(id)
				ON DELETE CASCADE,
	ppid	INTEGER KEY
			DEFAULT NULL
			REFERENCES %tpl_prefix%proc(pid),
	pid		INTEGER KEY
			DEFAULT NULL,
	userdata	BLOB
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_%tpl_prefix%_asyncdata_pk ON %tpl_prefix%asyncdata(proc_id, pid);