CREATE TABLE IF NOT EXISTS proc (
	id		INTEGER
			PRIMARY KEY AUTOINCREMENT
			NOT NULL
			UNIQUE,
	ident		VARCHAR(255) DEFAULT NULL,
	action		VARCHAR(255) DEFAULT NULL,
	owner		INTEGER KEY,
	pid			INTEGER KEY DEFAULT NULL,
	asyncid		VARCHAR(32) DEFAULT NULL,
	uident		VARCHAR(255) KEY 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 DEFAULT NULL
);

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

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

CREATE TABLE IF NOT EXISTS asyncdata(
	proc_id
			INTEGER KEY
			DEFAULT NULL
			REFERENCES proc(id)
				ON DELETE CASCADE,
	ppid	INTEGER KEY
			DEFAULT NULL
			REFERENCES proc(pid),
	pid		INTEGER KEY
			DEFAULT NULL,
	userdata	BLOB
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_asyncdata_pk ON asyncdata(proc_id, pid);

CREATE TRIGGER IF NOT EXISTS trg_proc_delete
	DELETE ON proc
FOR EACH ROW
BEGIN
    DELETE FROM asyncdata
          WHERE proc_id = OLD.id;
END;


CREATE TABLE IF NOT EXISTS task (
	id		INTEGER PRIMARY KEY
			AUTOINCREMENT
			NOT NULL
			UNIQUE,
	ppid
			INTEGER KEY
			DEFAULT NULL
			REFERENCES proc(id)
				ON DELETE CASCADE,
	pid		INTEGER KEY
			DEFAULT NULL,
	inici
			DATETIME
			DEFAULT NULL,
	final
			DATETIME
			DEFAULT NULL
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_proc_pids ON task(ppid, pid);

CREATE TABLE IF NOT EXISTS queue (
	id		INTEGER PRIMARY KEY
			AUTOINCREMENT NOT NULL
			UNIQUE,
	script		TEXT NOT NULL,
	params		BLOB,
	completat	INTEGER,
	datahora_inici	datetime DEFAULT NULL,
	datahora_final	datetime DEFAULT NULL,
	task_md5_hash	VARCHAR(32) NOT NULL
);

CREATE TRIGGER IF NOT EXISTS trg_proc_delete
	DELETE ON proc
FOR EACH ROW
BEGIN
    DELETE FROM task
          WHERE pid = OLD.pid;
END;

CREATE TRIGGER IF NOT EXISTS trg_async_task_delete
	DELETE ON task
FOR EACH ROW
BEGIN
    DELETE FROM asycdata
          WHERE pid = OLD.pid;
END;