insert into sequpdate_state(updateid,uid) values('tasks tables and view',17.0);


CREATE TABLE public.task (
	id serial4 NOT NULL,
	task_master_id serial4 NOT NULL,
	due_date timestamptz NOT NULL,
	title varchar NOT NULL,
	description varchar NULL,
	"comment" varchar NULL,
	status_id int4 DEFAULT 3 NULL,
	removed bool DEFAULT false NULL,
	closing_date timestamptz NULL,
	CONSTRAINT task_pk PRIMARY KEY (id)
);
CREATE TABLE public.task_link (
	id serial4 NOT NULL,
	task_id int4 NOT NULL,
	staff_member_id int4 NOT NULL,
	status_id int4 NOT NULL,
	"comment" varchar NULL,
	status_date timestamptz NULL,
	last_reminder timestamptz NULL,
	last_reminder_staff_id int4 NULL
);
CREATE TABLE public.task_master (
	id serial4 NOT NULL,
	title varchar NOT NULL,
	description varchar NULL,
	start_date timestamptz NOT NULL,
	end_date timestamptz DEFAULT '2099-12-31 00:00:00+01'::timestamp with time zone NULL,
	recurrence int4 DEFAULT 0 NOT NULL,
	day_of_week varchar DEFAULT '-1'::character varying NOT NULL,
	active bool DEFAULT true NOT NULL,
	dep_id int4 DEFAULT '-1'::integer NOT NULL,
	removed bool DEFAULT false NOT NULL,
	closing_type int4 DEFAULT 0 NULL,
	CONSTRAINT task_master_pk PRIMARY KEY (id)
);
CREATE TABLE public.task_master_link (
	id serial4 NOT NULL,
	task_master_id int4 NOT NULL,
	staff_member_id int4 NOT NULL
);
CREATE OR REPLACE VIEW public.v_alert_task
AS WITH t1 AS (
         SELECT t.id,
            t.task_master_id,
            t.due_date,
            t.title,
            t.description,
            t.comment,
            t.status_id,
            t.removed,
            tl.id,
            tl.task_id,
            tl.staff_member_id,
            tl.status_id,
            tl.comment,
            tl.status_date,
            tl.last_reminder,
            tl.last_reminder_staff_id
           FROM task t,
            task_link tl
          WHERE t.id = tl.task_id AND tl.status_id <> 4
        )
 SELECT task_id,
    description,
        CASE
            WHEN now()::date >= due_date::date THEN 3
            WHEN (now()::date + '3 days'::interval)::date >= due_date::date THEN 2
            ELSE 1
        END AS level,
    due_date,
    title,
    staff_member_id,
    task_master_id,
    last_reminder,
    last_reminder_staff_id
   FROM t1 t1(id, task_master_id, due_date, title, description, comment, status_id, removed, id_1, task_id, staff_member_id, status_id_1, comment_1, status_date, last_reminder, last_reminder_staff_id)
  WHERE removed = false;
