CREATE TABLE hrms_employee_profile (
	id serial4 NOT NULL,
	user_id int4 NULL,
	first_name varchar(50) NULL,
	last_name varchar(50) NULL,
	CONSTRAINT hrms_employee_profile_pkey PRIMARY KEY (id)
);


CREATE TABLE proman_project (
	id serial4 NOT NULL,
	"name" varchar(100) NULL,
	status varchar(20) NULL,	
	is_active bool DEFAULT true NULL,
	created_at timestamptz DEFAULT CURRENT_TIMESTAMP NULL,
	updated_at timestamptz NOT NULL,
	created_by int4 NULL,
	updated_by int4 NULL,
	CONSTRAINT proman_project_pkey PRIMARY KEY (id)
);



CREATE TABLE proman_module (
	id serial4 NOT NULL,
	project_id int4 NOT NULL,
	title varchar(100) NOT NULL,
	description varchar(255) NULL,
	is_active bool NOT NULL,
	created_at timestamptz DEFAULT CURRENT_TIMESTAMP NULL,
	updated_at timestamptz NOT NULL,
	created_by int4 NULL,
	updated_by int4 NULL,
	CONSTRAINT proman_module_pkey PRIMARY KEY (id)
);


ALTER TABLE proman_module ADD CONSTRAINT proman_module_project_id_fkey FOREIGN KEY (project_id) REFERENCES proman_project(id) ON DELETE CASCADE;



CREATE TABLE proman_member (
	id serial4 NOT NULL,
	member_hrms_user_id int4 NOT NULL,
	project_id int4 NOT NULL,
	is_active bool NOT NULL,
	created_at timestamptz DEFAULT CURRENT_TIMESTAMP NULL,
	updated_at timestamptz NOT NULL,
	created_by int4 NULL,
	updated_by int4 NULL,
	CONSTRAINT proman_member_pkey PRIMARY KEY (id)
);



-- ALTER TABLE proman_member ADD CONSTRAINT proman_member_member_hrms_user_id_fkey FOREIGN KEY (member_hrms_user_id) REFERENCES hrms_employee_profile(user_id) ON DELETE CASCADE;
ALTER TABLE proman_member ADD CONSTRAINT proman_member_project_id_fkey FOREIGN KEY (project_id) REFERENCES proman_project(id) ON DELETE CASCADE;



CREATE TABLE proman_task (
	id serial4 NOT NULL,
	module_id int4 NOT NULL,
	start_date date NULL,
	end_date date NULL,
	status varchar(20) NOT NULL,
	priority varchar(20) NOT NULL,
	title varchar(100) NOT NULL,
	description varchar(255) NOT NULL,
	is_active bool NULL,
	created_at timestamptz NULL,
	updated_at timestamptz NOT NULL,
	created_by int4 NULL,
	updated_by int4 NULL,
	CONSTRAINT proman_task_pkey PRIMARY KEY (id)
);


ALTER TABLE proman_task ADD CONSTRAINT proman_task_module_id_fkey FOREIGN KEY (module_id) REFERENCES public.proman_module(id);



CREATE TABLE proman_task_assigned_member (
	id serial4 NOT NULL,
	task_id int4 NOT NULL,
	hrms_user_id int4 NOT NULL,
	is_active bool NULL,
	created_at timestamptz NULL,
	updated_at timestamptz NOT NULL,
	created_by int4 NULL,
	updated_by int4 NULL,
	CONSTRAINT proman_task_assigned_member_pkey PRIMARY KEY (id)
);



-- ALTER TABLE proman_task_assigned_member ADD CONSTRAINT proman_task_assigned_member_hrms_user_id_fkey FOREIGN KEY (hrms_user_id) REFERENCES hrms_employee_profile(user_id);
ALTER TABLE proman_task_assigned_member ADD CONSTRAINT proman_task_assigned_member_task_id_fkey FOREIGN KEY (task_id) REFERENCES proman_task(id) ON DELETE CASCADE;


CREATE TABLE proman_task_msg (
	id SERIAL primary key,
	task_id int NOT NULL REFERENCES proman_task(id) ON DELETE CASCADE,
    	message text NOT NULL,
	sent_at timestamptz NOT NULL,
	is_read bool NOT NULL,
	created_at timestamptz NOT NULL,
	updated_at timestamptz NOT NULL,
	created_by int4 NULL,
	updated_by int4 NULL,
	receiver_id int8 NOT NULL,
	sender_id int8 NOT NULL
	);
	
CREATE TABLE proman_task_attachment (
	id SERIAL primary key,
	task_id int NOT NULL REFERENCES proman_task(id) ON DELETE CASCADE,
    	file varchar(100) NOT NULL,
    	is_active bool NOT NULL,
    	created_at timestamptz DEFAULT CURRENT_TIMESTAMP,
	updated_at timestamptz NOT NULL,
	created_by int4 NULL,
	updated_by int4 NULL
	);