
----------------------------------------------------------------------------------------------------------------------------------------------------------------
-- public.duty_duties definition

-- Drop table

-- DROP TABLE public.duty_duties;

CREATE TABLE public.duty_duties (
	id bigserial NOT NULL,
	task varchar NULL,
	gear_id int8 NULL,
	meta jsonb NOT NULL DEFAULT '{}'::json,
	user_id int8 NULL,
	status varchar(5) NULL,
	gear_ref varchar(50) NULL,
	"type" varchar(7) NULL,
	client_id int8 NULL,
	date_request timestamp NULL,
	date_expiry timestamp NULL,
	date_start timestamp NULL,
	date_completed timestamp NULL,
	pipeline_id int8 NULL,
	CONSTRAINT duty_duties_pk PRIMARY KEY (id),
	CONSTRAINT duty_duties_fk FOREIGN KEY (user_id) REFERENCES public.core_users(id),
	CONSTRAINT duty_duties_gear_fk FOREIGN KEY (gear_id) REFERENCES public.gear_gears(id)
);
CREATE UNIQUE INDEX duty_duties_id_idx ON public.duty_duties USING btree (id);

CREATE TABLE public.duty_reminders (
	id varchar(50) null,
	duty_id int4 NULL,
	user_id int4 NULL,
	description varchar(255) NULL,
	meta jsonb NULL,
	date_from timestamp with time zone NULL,
	date_to timestamp with time zone NULL,
	title varchar(50) NULL
);
CREATE UNIQUE INDEX duty_reminders_id_idx ON public.duty_reminders USING btree (id);
CREATE UNIQUE INDEX duty_reminders_reminder_id_idx ON public.duty_reminders USING btree (id);


-- public.duty_duties foreign keys

ALTER TABLE public.duty_duties ADD CONSTRAINT duty_duties_fk FOREIGN KEY (user_id) REFERENCES public.core_users(id);
ALTER TABLE public.duty_duties ADD CONSTRAINT duty_duties_gear_fk FOREIGN KEY (gear) REFERENCES public.duty_gears(id);
ALTER TABLE public.duty_duties RENAME COLUMN gear TO gear_id;
ALTER TABLE public.duty_duties ADD gear_ref varchar(50) NULL;
ALTER TABLE public.duty_duties ADD "type" varchar(7) NULL;

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

CREATE TABLE public.duty_duties_activities (
	id bigserial NOT NULL,
	activity varchar(255) NOT NULL,
	code varchar(7) NOT NULL
);

ALTER TABLE public.duty_duties_activities ADD "date" timestamp NULL;
ALTER TABLE public.duty_duties_activities ADD user_id int NULL;
ALTER TABLE public.duty_duties_activities ADD completed bool NULL;
ALTER TABLE public.duty_duties_activities ADD duty_id int4 NULL;
ALTER TABLE public.duty_duties_activities ADD meta jsonb NULL;
ALTER TABLE public.duty_duties_activities ADD ord int NULL;
ALTER TABLE public.duty_duties_activities ALTER COLUMN code DROP NOT NULL;

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

CREATE TABLE public.code_list_duty_type (
	code varchar(7) NULL,
	description varchar(255) NULL,
	properties jsonb NULL
);

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

CREATE TABLE public.duty_news (
 id serial NOT NULL,
 "date" date not null,
 title varchar(50) NOT NULL,
 "text" varchar not NULL
);
CREATE UNIQUE INDEX duty_news_id_idx ON public.duty_news (id);

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


CREATE TABLE public.code_list_duties_services (
    code varchar(3) NOT NULL,
    description varchar(50) NULL,
    ord int8 NULL,
    icon varchar(255) NULL,
    misc varchar(255) NULL,
    properties jsonb NULL,
    CONSTRAINT code_list_duties_services_pk PRIMARY KEY (code)
);
CREATE UNIQUE INDEX code_list_duties_services_code_idx ON public.code_list_duties_services USING btree (code);

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

CREATE TABLE public.code_list_duties_task_types (
    code varchar(3) NOT NULL,
    description varchar(50) NULL,
    ord int8 NULL,
    icon varchar(255) NULL,
    misc varchar(255) NULL,
    properties jsonb NULL,
    CONSTRAINT code_list_duties_task_types_pk PRIMARY KEY (code)
);
CREATE UNIQUE INDEX code_list_duties_task_types_code_idx ON public.code_list_duties_task_types USING btree (code);

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


-- public.duty_duties_tasks definition

-- Drop table

-- DROP TABLE public.duty_duties_tasks;

CREATE TABLE public.duty_duties_tasks (
	id bigserial NOT NULL,
	task_name varchar(255) NULL,
	duty_id int8 NULL,
	user_id int8 NULL,
	date_created date NULL,
	date_expiry date NULL,
	task_type varchar(3) NULL,
	remarks varchar(255) NULL,
	meta jsonb NOT NULL DEFAULT '{}'::json,
	status varchar(3) NULL,
    ord int4 NULL,
	CONSTRAINT duty_duties_tasks_fk FOREIGN KEY (duty_id) REFERENCES public.duty_duties(id)  ON DELETE CASCADE
);
CREATE UNIQUE INDEX duty_duties_tasks_id_idx ON public.duty_duties_tasks USING btree (id);


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



-- public.duty_pipelines definition

-- Drop table

-- DROP TABLE public.duty_pipelines;

CREATE TABLE public.duty_pipelines (
	id bigserial NOT NULL,
	"name" varchar(255) NULL,
	user_id int8 NULL,
	group_id int8 NULL,
	meta jsonb NOT NULL DEFAULT '{}'::json
);