
local f, conn
local function addTableDefinition(conn)
	-- f.task = f["do"] -- f["do"] == f.do, do is reserved word, use task instead
	if not db.tableExists(conn, "task") then
		local sqlText = [[
      ALTER TABLE IF EXISTS task_work DROP CONSTRAINT task_id_link;
      ALTER TABLE IF EXISTS task_work DROP CONSTRAINT employee_id_link;
			ALTER TABLE IF EXISTS task_link DROP CONSTRAINT parent_id_link;
			ALTER TABLE IF EXISTS task_link DROP CONSTRAINT child_id_link;

			DROP TABLE IF EXISTS task;
			CREATE TABLE task(
				name varchar(80) NOT NULL DEFAULT '' UNIQUE,
				description text NOT NULL DEFAULT '',
				timer_rule text NOT NULL DEFAULT '',
				user_rule text NOT NULL DEFAULT '',
				due_time timestamp with time zone NOT NULL DEFAULT '1970-01-01 00:00:00+00',
				start_time timestamp with time zone NOT NULL DEFAULT '1970-01-01 00:00:00+00',
				estimated_work_time double precision NOT NULL DEFAULT 0.0,

				type varchar(40) NOT NULL DEFAULT '',
				state varchar(40) NOT NULL DEFAULT '',
				organization_id varchar(40) NOT NULL DEFAULT '',
				json_data jsonb NOT NULL DEFAULT '{}',

				record_id uuid NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000' UNIQUE,
				create_user varchar(40) NOT NULL DEFAULT '',
				create_time timestamp with time zone NOT NULL DEFAULT '1970-01-01 00:00:00+00',

				modify_id uuid NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000',
				modify_user varchar(40) NOT NULL DEFAULT '',
				modify_time timestamp with time zone NOT NULL DEFAULT '1970-01-01 00:00:00+00',
				PRIMARY KEY(record_id)
			);
			-- CREATE INDEX task_description_idx ON task(description);
			CREATE INDEX task_task_type_idx ON task(task_type);
			CREATE INDEX task_task_state_idx ON task(task_state);
		]]
		local data, info = db.sqlExecuteUnsafe(conn, sqlText)
		if info.error then
			print(tostring(info.error))
			return nil
		end
	end
	f = db.fieldTableAdd(conn, "task", "task", nil)

	if not db.tableExists(conn, "task_work") then
		local sqlText = [[
			DROP TABLE IF EXISTS task_work;
			CREATE TABLE task_work(
				task_id uuid NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000',
				employee_id varchar(40) DEFAULT '',
				start_time timestamp with time zone NOT NULL DEFAULT '1970-01-01 00:00:00+00',
				end_time timestamp with time zone NOT NULL DEFAULT '1970-01-01 00:00:00+00',
				work_time double precision NOT NULL DEFAULT 0.0,
				info text NOT NULL DEFAULT '',
				json_data jsonb NOT NULL DEFAULT '{}',

				record_id uuid NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000' UNIQUE,
				create_user varchar(40) NOT NULL DEFAULT '',
				create_time timestamp with time zone NOT NULL DEFAULT '1970-01-01 00:00:00+00',

				modify_id uuid NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000',
				modify_user varchar(40) NOT NULL DEFAULT '',
				modify_time timestamp with time zone NOT NULL DEFAULT '1970-01-01 00:00:00+00',
				PRIMARY KEY(record_id)
			);
			ALTER TABLE task_work ADD CONSTRAINT task_id_link FOREIGN KEY (task_id) REFERENCES task(record_id) ON UPDATE CASCADE ON DELETE RESTRICT;
			ALTER TABLE task_work ADD CONSTRAINT employee_id_link FOREIGN KEY (employee_id) REFERENCES employee(employee_id) ON UPDATE CASCADE ON DELETE RESTRICT;
		]]
		local data, info = db.sqlExecuteUnsafe(conn, sqlText)
		if info.error then
			print(tostring(info.error))
			return nil
		end
	end
	f = db.fieldTableAdd(conn, "task_work", "taskw", nil)


	if not db.tableExists(conn, "task_link") then
		local sqlText = [[
			DROP TABLE IF EXISTS task_link;
			CREATE TABLE task_link(
				parent_id uuid NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000',
				child_id uuid NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000',
				distance int NOT NULL,
				level_order int NOT NULL,
				json_data jsonb NOT NULL DEFAULT '{}',

				record_id uuid NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000' UNIQUE,
				create_user varchar(40) NOT NULL DEFAULT '',
				create_time timestamp with time zone NOT NULL DEFAULT '1970-01-01 00:00:00+00',

				modify_id uuid NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000',
				modify_user varchar(40) NOT NULL DEFAULT '',
				modify_time timestamp with time zone NOT NULL DEFAULT '1970-01-01 00:00:00+00',
				PRIMARY KEY(record_id)
			);
			CREATE INDEX taskl_parent_id_idx ON task_link(parent_id);
			CREATE INDEX taskl_child_id_idx ON task_link(child_id);
			ALTER TABLE task_link ADD CONSTRAINT parent_id_link FOREIGN KEY (parent_id) REFERENCES task(record_id) ON UPDATE CASCADE ON DELETE CASCADE;
			ALTER TABLE task_link ADD CONSTRAINT child_id_link FOREIGN KEY (child_id) REFERENCES task(record_id) ON UPDATE CASCADE ON DELETE CASCADE;
		]]
		local data, info = db.sqlExecuteUnsafe(conn, sqlText)
		if info.error then
			print(tostring(info.error))
			return nil
		end
	end
	f = db.fieldTableAdd(conn, "task_link", "taskl", nil)

end


=====================================
-------------

CREATE INDEX task_create_ts_idx ON task(create_ts);
CREATE INDEX task_change_ts_idx ON task(change_ts);
CREATE INDEX task_description_idx ON task(description);
CREATE INDEX task_task_type_idx ON task(task_type);
CREATE INDEX task_task_state_idx ON task(task_state);

-------------

DROP TABLE IF EXISTS task_link;
CREATE TABLE task_link(
  record_id uuid NOT NULL primary key,
  create_ts timestamp with time zone NOT NULL DEFAULT current_timestamp,
  change_ts timestamp with time zone NOT NULL DEFAULT current_timestamp,

  parent_id uuid NOT NULL,
  child_id uuid NOT NULL,
  distance int NOT NULL,
  level_order int NOT NULL
);
CREATE INDEX taskl_parent_id_idx ON task_link(parent_id);
CREATE INDEX taskl_child_id_idx ON task_link(child_id);

---

CREATE INDEX task_info_idx ON task(info);
  info text NOT NULL DEFAULT '',

---

CREATE TABLE public.to_do
(
  alert_ character varying(4) NOT NULL DEFAULT ''::character varying,
  calendar_name character varying(80) NOT NULL DEFAULT ''::character varying,
  change_id character varying(90) NOT NULL DEFAULT ''::character varying,
  color integer NOT NULL DEFAULT 0,
  company_id character varying(24) NOT NULL DEFAULT ''::character varying,
  confirmation character varying(4) NOT NULL DEFAULT ''::character varying,
  contact_person character varying(112) NOT NULL DEFAULT ''::character varying,
  creator_date date NOT NULL DEFAULT '1970-01-01'::date,
  creator_id character varying(24) NOT NULL DEFAULT ''::character varying,
  creator_time time without time zone NOT NULL DEFAULT '00:00:00'::time without time zone,
  customer_project character varying(80) NOT NULL DEFAULT ''::character varying,
  date_ date NOT NULL DEFAULT '1970-01-01'::date,
  direct_telephone_number character varying(40) NOT NULL DEFAULT ''::character varying,
  employee_id character varying(24) NOT NULL DEFAULT ''::character varying,
  end_date date NOT NULL DEFAULT '1970-01-01'::date,
  end_time time without time zone NOT NULL DEFAULT '00:00:00'::time without time zone,
  firm_id integer NOT NULL DEFAULT 0,
  in_calendar boolean NOT NULL DEFAULT false,
  info text NOT NULL DEFAULT ''::text,
  modifier_id character varying(40) NOT NULL DEFAULT ''::character varying,
  picture_name character varying(80) NOT NULL DEFAULT ''::character varying,
  priority integer NOT NULL DEFAULT 0,
  project_id character varying(80) NOT NULL DEFAULT ''::character varying,
  record_data text NOT NULL DEFAULT ''::text,
  record_id character varying(90) NOT NULL DEFAULT ''::character varying,
  repetition_rule character varying(160) NOT NULL DEFAULT ''::character varying,
  state character varying(24) NOT NULL DEFAULT ''::character varying,
  summary character varying(160) NOT NULL DEFAULT ''::character varying,
  time_ time without time zone NOT NULL DEFAULT '00:00:00'::time without time zone,
  transfer_id character varying(120) NOT NULL DEFAULT ''::character varying,
  transfer_state integer NOT NULL DEFAULT 0,
  type_ character varying(24) NOT NULL DEFAULT ''::character varying,
  CONSTRAINT to_do_pkey PRIMARY KEY (record_id)
)


DROP TABLE IF EXISTS task CASCADE;
CREATE TABLE task (
 record_id varchar(60) NOT NULL DEFAULT '' primary key,
 title varchar(80) NOT NULL DEFAULT '',
 master varchar(60) NOT NULL DEFAULT '',
 updated timestamp with time zone NOT NULL DEFAULT '1970-01-01 00:00:00+00',
 parent varchar(60) NOT NULL DEFAULT '',
 depth int NOT NULL DEFAULT 1,
 previous varchar(60) NOT NULL DEFAULT '',
 task_attribute json NOT NULL DEFAULT '{}',
 info text NOT NULL DEFAULT '',
 status varchar(20) NOT NULL DEFAULT '',
 due timestamp with time zone NOT NULL DEFAULT '1970-01-01 00:00:00+00',
 completed timestamp with time zone NOT NULL DEFAULT '1970-01-01 00:00:00+00',
 deleted boolean NOT NULL DEFAULT false,
 hidden boolean NOT NULL DEFAULT false
);
ALTER TABLE task OWNER TO "manage";
-- CREATE INDEX task_id_idx ON task_list(id); -- "primary key" will create the inde
CREATE INDEX task_title_idx ON task(title);
CREATE INDEX task_parent_idx ON task(parent);
CREATE INDEX task_status_idx ON task(status);

CREATE TABLE public.task
(
  record_id character varying(60) NOT NULL DEFAULT ''::character varying,
  title character varying(80) NOT NULL DEFAULT ''::character varying,
  master character varying(60) NOT NULL DEFAULT ''::character varying,
  updated timestamp with time zone NOT NULL DEFAULT '1970-01-01 02:00:00+02'::timestamp with time zone,
  parent character varying(60) NOT NULL DEFAULT ''::character varying,
  previous character varying(60) NOT NULL DEFAULT ''::character varying,
  task_attribute json NOT NULL DEFAULT '{}'::json,
  info text NOT NULL DEFAULT ''::text,
  status character varying(20) NOT NULL DEFAULT ''::character varying,
  due timestamp with time zone NOT NULL DEFAULT '1970-01-01 02:00:00+02'::timestamp with time zone,
  completed timestamp with time zone NOT NULL DEFAULT '1970-01-01 02:00:00+02'::timestamp with time zone,
  deleted boolean NOT NULL DEFAULT false,
  hidden boolean NOT NULL DEFAULT false,
  CONSTRAINT task_pkey PRIMARY KEY (record_id)
)



create_user varchar(80) NOT NULL DEFAULT '',
change_user varchar(80) NOT NULL DEFAULT '',
site_code,


---------------------------

old texts:

process_data example json
 when_ timestamp with time zone NOT NULL DEFAULT '1970-01-01 00:00:00+00',
 repeat_ varchar(20) NOT NULL DEFAULT '',
 who json
 bu json
 est double precision NOT NULL DEFAULT 0
-- master, parent, depth ja prev


DROP TABLE IF EXISTS task CASCADE;
CREATE TABLE task (
 record_id varchar(60) NOT NULL DEFAULT '' primary key,
 title varchar(80) NOT NULL DEFAULT '',
 master varchar(60) NOT NULL DEFAULT '',
 updated timestamp with time zone NOT NULL DEFAULT '1970-01-01 00:00:00+00',
 parent varchar(60) NOT NULL DEFAULT '',
 depth int NOT NULL DEFAULT 1,
 previous varchar(60) NOT NULL DEFAULT '',
 task_attribute json NOT NULL DEFAULT '{}',
 info text NOT NULL DEFAULT '',
 status varchar(20) NOT NULL DEFAULT '',
 due timestamp with time zone NOT NULL DEFAULT '1970-01-01 00:00:00+00',
 completed timestamp with time zone NOT NULL DEFAULT '1970-01-01 00:00:00+00',
 deleted boolean NOT NULL DEFAULT false,
 hidden boolean NOT NULL DEFAULT false
);
ALTER TABLE task OWNER TO "manage";
-- CREATE INDEX task_id_idx ON task_list(id); -- "primary key" will create the inde
CREATE INDEX task_title_idx ON task(title);
CREATE INDEX task_parent_idx ON task(parent);
CREATE INDEX task_status_idx ON task(status);

 -- task_list_id varchar(60) NOT NULL DEFAULT '' references task_list(id), -- on delete cascade on update cascade

  f = db.fieldTableAdd(conn, "task", "task",
{{name="record_id", type="s", length=60},  {name="title", type="s", length=80}, {name="master", type="s", length=60}, {name="updated", type="ts"}, {name="parent", type="s", length=60}, {name="previous", type="s", length=60}, {name="task_attribute", type="j"}, {name="info", type="t"}, {name="status", type="s", length=20}, {name="due", type="ts"}, {name="completed", type="ts"}, {name="deleted", type="f"}, {name="hidden", type="f"}})




--- NOT IN USE
DROP TABLE IF EXISTS task_link CASCADE;
CREATE TABLE task_link (
 id varchar(60) NOT NULL DEFAULT '' primary key,
 task_id varchar(60) NOT NULL DEFAULT '' references task(id), --on delete cascade
 type varchar(60) NOT NULL DEFAULT '',
 description varchar(80) NOT NULL DEFAULT '',
 link varchar(60) NOT NULL DEFAULT ''
);
ALTER TABLE task_link OWNER TO "manage";
-- DROP INDEX IF EXISTS task_links_id_idx;
-- CREATE INDEX task_links_id_idx ON task_link (id);



table task_list = tlist, task = task, task_link = tlink

s = varchar
t = text (in use?)
l = int
ts = timestamp
d = date
h = time
r = real

NOTE: field kind is omitted always from database structure = table name

https://developers.google.com/google-apps/tasks/v1/reference/tasklists?hl=fi#resource

{
  "kind": "tasks#taskList",
  "id": string,
  "etag": string,
  "title": string,
  "updated": datetime,
  "selfLink": string
}


kind	string	Type of the resource. This is always "tasks#taskList".
id	string	Task list identifier.
etag	string	ETag of the resource.
title	string	Title of the task list.
selfLink	string	URL pointing to this task list. Used to retrieve, update, or delete this task list.
updated	datetime	Last modification time of the task list (as a RFC 3339 timestamp).



table task

https://developers.google.com/google-apps/tasks/v1/reference/tasks?hl=fi
{
  "kind": "tasks#task",
  "id": string,
  "etag": etag,
  "title": string,
  "updated": datetime,
  "selfLink": string,
  "parent": string,
  "position": string,
  "notes": string,
  "status": string,
  "due": datetime,
  "completed": datetime,
  "deleted": boolean,
  "hidden": boolean,
  "links": [
    {
      "type": string,
      "description": string,
      "link": string
    }
  ]
}

kind	string	Type of the resource. This is always "tasks#task".
id	string	Task identifier.
etag	etag	ETag of the resource.
title	string	Title of the task.
updated	datetime	Last modification time of the task (as a RFC 3339 timestamp).
selfLink	string	URL pointing to this task. Used to retrieve, update, or delete this task.
parent	string	Parent task identifier. This field is omitted if it is a top-level task. This field is read-only. Use the "move" method to move the task under a different parent or to the top level.
position	string	String indicating the position of the task among its sibling tasks under the same parent task or at the top level. If this string is greater than another tasks corresponding position string according to lexicographical ordering, the task is positioned after the other task under the same parent task (or at the top level). This field is read-only. Use the "move" method to move the task to another position.
notes	string	Notes describing the task. Optional.
status	string	Status of the task. This is either "needsAction" or "completed".
due	datetime	Due date of the task (as a RFC 3339 timestamp). Optional.
completed	datetime	Completion date of the task (as a RFC 3339 timestamp). This field is omitted if the task has not been completed.
deleted	boolean	Flag indicating whether the task has been deleted. The default if False.
hidden	boolean	Flag indicating whether the task is hidden. This is the case if the task had been marked completed when the task list was last cleared. The default is False. This field is read-only.



table task_links
  "links": [
    {
      "type": string,
      "description": string,
      "link": string
    }

  f = db.fieldTableAdd(conn, "task_list", "tlink",
{{name="id", type="l"}, {name="task_id", type="l"}, {name="type", type="s", length=60}, {name="description", type="s", length=80},
 {name="link", type="s", length=80}})

links[]	list	Collection of links. This collection is read-only.
links[].type	string	Type of the link, e.g. "email".
links[].description	string	The description. In HTML speak: Everything between <a> and </a>.
links[].link	string	The URL.
