-- ----------------------------------------------------- -- Drop existing database objects -- ----------------------------------------------------- DROP VIEW IF EXISTS public.vw_reports_group; DROP VIEW IF EXISTS public.vw_user_course; DROP VIEW IF EXISTS public.vw_catalog_course; DROP VIEW IF EXISTS public.vw_reports_general; DROP VIEW IF EXISTS public.vw_course_plan_module; DROP VIEW IF EXISTS public.vw_thread; DROP VIEW IF EXISTS public.vw_message; DROP VIEW IF EXISTS public.vw_email_subscription_to_thread; DROP VIEW IF EXISTS public.vw_content_request; DROP TABLE IF EXISTS public.content_request; DROP TABLE IF EXISTS public.content_request_type; DROP TABLE IF EXISTS public.message_file; DROP TABLE IF EXISTS public.message; DROP TABLE IF EXISTS public.follow; DROP TABLE IF EXISTS public.thread_group; DROP TABLE IF EXISTS public.thread_user; DROP TABLE IF EXISTS public.thread_role; DROP TABLE IF EXISTS public.thread CASCADE; DROP TABLE IF EXISTS public.thread_category; DROP TABLE IF EXISTS public.audit_login_attempt; DROP TABLE IF EXISTS public.reset_password_request; DROP TABLE IF EXISTS public.role_feature; DROP TABLE IF EXISTS public.user_attribute_value; DROP TABLE IF EXISTS public.user_attribute; DROP TABLE IF EXISTS public.user_group; DROP TABLE IF EXISTS public.course_schedule; DROP TABLE IF EXISTS public.course_plan_resource; DROP TABLE IF EXISTS public.course_plan_test; DROP TABLE IF EXISTS public.course_assignment; DROP TABLE IF EXISTS public.course_location; DROP TABLE IF EXISTS public.course_plan_link; DROP TABLE IF EXISTS public.learning_session; DROP TABLE IF EXISTS public.learning_attempt; DROP TABLE IF EXISTS public.test_item; DROP TABLE IF EXISTS public.test; DROP TABLE IF EXISTS public.course_plan_local_package; DROP TABLE IF EXISTS public.course_plan_hologram_live; DROP TABLE IF EXISTS public.course_plan_hologram_recorded; DROP TABLE IF EXISTS public.course_plan_hologram_multi; DROP TABLE IF EXISTS public.course_plan_hologram_interactive; DROP TABLE IF EXISTS public.local_course_package; DROP TABLE IF EXISTS public.course_plan; DROP TABLE IF EXISTS public.course_category; DROP TABLE IF EXISTS public.resource; DROP TABLE IF EXISTS public."group"; DROP TABLE IF EXISTS public.notification; DROP TABLE IF EXISTS public."user"; DROP TABLE IF EXISTS public."role"; DROP TABLE IF EXISTS public.feature; DROP TABLE IF EXISTS public.file; DROP TABLE IF EXISTS public.organization; DROP SEQUENCE IF EXISTS public.content_request_seq; DROP SEQUENCE IF EXISTS public.content_request_type_seq; DROP SEQUENCE IF EXISTS public.audit_login_attempt_seq; DROP SEQUENCE IF EXISTS public.course_assignment_seq ; DROP SEQUENCE IF EXISTS public.course_category_seq; DROP SEQUENCE IF EXISTS public.course_location_seq; DROP SEQUENCE IF EXISTS public.course_plan_inclass_seq; DROP SEQUENCE IF EXISTS public.course_plan_link_seq; DROP SEQUENCE IF EXISTS public.course_plan_local_package_seq ; DROP SEQUENCE IF EXISTS public.course_plan_resource_seq; DROP SEQUENCE IF EXISTS public.course_plan_seq; DROP SEQUENCE IF EXISTS public.course_plan_test_seq; DROP SEQUENCE IF EXISTS public.course_schedule_seq; DROP SEQUENCE IF EXISTS public.feature_seq; DROP SEQUENCE IF EXISTS public.file_seq; DROP SEQUENCE IF EXISTS public.follow_seq; DROP SEQUENCE IF EXISTS public.group_seq ; DROP SEQUENCE IF EXISTS public.learning_attempt_seq; DROP SEQUENCE IF EXISTS public.learning_session_seq; DROP SEQUENCE IF EXISTS public.local_course_package_seq; DROP SEQUENCE IF EXISTS public.course_plan_hologram_live_seq; DROP SEQUENCE IF EXISTS public.course_plan_hologram_recorded_seq; DROP SEQUENCE IF EXISTS public.course_plan_hologram_multi_seq; DROP SEQUENCE IF EXISTS public.course_plan_hologram_interactive_seq; DROP SEQUENCE IF EXISTS public.message_file_seq; DROP SEQUENCE IF EXISTS public.message_seq; DROP SEQUENCE IF EXISTS public.notification_seq; DROP SEQUENCE IF EXISTS public.organization_seq; DROP SEQUENCE IF EXISTS public.reset_password_request_seq; DROP SEQUENCE IF EXISTS public.resource_seq; DROP SEQUENCE IF EXISTS public.role_feature_seq; DROP SEQUENCE IF EXISTS public.role_seq; DROP SEQUENCE IF EXISTS public.test_item_seq ; DROP SEQUENCE IF EXISTS public.test_seq; DROP SEQUENCE IF EXISTS public.thread_category_seq; DROP SEQUENCE IF EXISTS public.thread_group_seq; DROP SEQUENCE IF EXISTS public.thread_role_seq; DROP SEQUENCE IF EXISTS public.thread_seq; DROP SEQUENCE IF EXISTS public.user_attribute_seq; DROP SEQUENCE IF EXISTS public.user_attribute_value_seq; DROP SEQUENCE IF EXISTS public.user_group_seq; DROP SEQUENCE IF EXISTS public.user_seq; -- ----------------------------------------------------- -- Table public.customer_type -- ----------------------------------------------------- CREATE SEQUENCE public.customer_type_seq; CREATE TABLE public.customer_type ( id INT NOT NULL DEFAULT NEXTVAL ('public.customer_type_seq'), "name" varchar(1024) NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT pk_customer_type PRIMARY KEY (id), CONSTRAINT uk_customer_type UNIQUE(name) ); -- ----------------------------------------------------- -- Table public.organization -- ----------------------------------------------------- CREATE SEQUENCE public.organization_seq; CREATE TABLE IF NOT EXISTS public.organization ( id INT NOT NULL DEFAULT NEXTVAL ('public.organization_seq'), name VARCHAR(128) NOT NULL, created_at TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, domain VARCHAR(1024) NOT NULL, email_from VARCHAR(1024) NOT NULL, customer_name VARCHAR(1024) NULL, vat_number VARCHAR(64) NULL, registration_number VARCHAR(64) NULL, represented_by VARCHAR(128) NULL, address VARCHAR(1024) NULL, contact_information VARCHAR(1024) NULL, customer_type_id INT NULL, enabled bool NOT NULL DEFAULT true, theme VARCHAR(45) NOT NULL DEFAULT 'paper', branding_colors VARCHAR(1024) NULL, branding_image_login VARCHAR(1024) NULL, branding_logo VARCHAR(1024) NULL, CONSTRAINT pk_organization PRIMARY KEY (id), CONSTRAINT uk_org_name UNIQUE (name), CONSTRAINT fk_org_customer_type FOREIGN KEY (customer_type_id) REFERENCES public.customer_type (id) ); -- ----------------------------------------------------- -- Table public.audit_login_attempt -- ----------------------------------------------------- CREATE SEQUENCE public.audit_login_attempt_seq; CREATE TABLE public.audit_login_attempt ( id INT NOT NULL DEFAULT nextval('audit_login_attempt_seq'), username varchar(1024) NOT NULL, organization_id INT NOT NULL, date TIMESTAMP NOT NULL DEFAULT now(), successful smallint NOT NULL, CONSTRAINT pk_ala PRIMARY KEY (id), CONSTRAINT fk_ala_org FOREIGN KEY (organization_id) REFERENCES public.organization (id) ); -- ----------------------------------------------------- -- Table public.feature -- ----------------------------------------------------- CREATE SEQUENCE public.feature_seq; CREATE TABLE IF NOT EXISTS public.feature ( id INT NOT NULL DEFAULT NEXTVAL ('public.feature_seq'), name VARCHAR(255) NOT NULL, description VARCHAR(512) NOT NULL, enabled bool NOT NULL DEFAULT true, auto_assign varchar(256) NULL, CONSTRAINT pk_feature PRIMARY KEY (id), CONSTRAINT uk_feat_name UNIQUE (name) ); -- ----------------------------------------------------- -- Table public.group -- ----------------------------------------------------- CREATE SEQUENCE public.group_seq; CREATE TABLE IF NOT EXISTS public.group ( id INT NOT NULL DEFAULT NEXTVAL ('public.group_seq'), name VARCHAR(64) NOT NULL, created_at TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, parent_id INT NULL, organization_id INT NOT NULL, CONSTRAINT pk_group PRIMARY KEY (id), CONSTRAINT uk_group_name UNIQUE (name, organization_id), CONSTRAINT fk_group_group FOREIGN KEY (parent_id) REFERENCES public.group (id) , CONSTRAINT fk_group_org FOREIGN KEY (organization_id) REFERENCES public.organization (id) ); -- ----------------------------------------------------- -- Table public.role -- ----------------------------------------------------- CREATE SEQUENCE public.role_seq; CREATE TABLE public."role" ( id INT NOT NULL DEFAULT NEXTVAL ('public.role_seq'), "name" varchar(45) NOT NULL, organization_id INT NOT NULL, can_autoregister bool NOT NULL DEFAULT false, needs_approval bool NOT NULL DEFAULT false, CONSTRAINT pk_role PRIMARY KEY (id), CONSTRAINT uk_role_name UNIQUE (name, organization_id), CONSTRAINT fk_role_org FOREIGN KEY (organization_id) REFERENCES public.organization(id) ); -- ----------------------------------------------------- -- Table public.content_request_type -- ----------------------------------------------------- CREATE SEQUENCE public.content_request_type_seq; CREATE TABLE public."content_request_type" ( id INT NOT NULL DEFAULT NEXTVAL ('public.content_request_type_seq'), "name" varchar(4096) NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT pk_content_request_type PRIMARY KEY (id), CONSTRAINT uk_content_request_type UNIQUE(name) ); -- ----------------------------------------------------- -- Table public.avatar -- ----------------------------------------------------- CREATE SEQUENCE public.avatar_seq; CREATE TABLE public.avatar ( id INT NOT NULL DEFAULT NEXTVAL ('public.avatar_seq'), "name" varchar(1024) NOT NULL, "gender" varchar(1) NOT NULL DEFAULT '-', "age" INT NOT NULL DEFAULT 0, "comments" varchar(4096) NULL, "url" varchar(2048) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT pk_avatar PRIMARY KEY (id), CONSTRAINT uk_avatar UNIQUE(name) ); -- ----------------------------------------------------- -- Table public.recorded_holo -- ----------------------------------------------------- CREATE SEQUENCE public.recorded_holo_seq; CREATE TABLE public.recorded_holo ( id INT NOT NULL DEFAULT NEXTVAL ('public.recorded_holo_seq'), "name" varchar(1024) NOT NULL, "url" varchar(2048) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT pk_recorded_holo PRIMARY KEY (id), CONSTRAINT uk_recorded_holo UNIQUE(name) ); -- ----------------------------------------------------- -- Table public.multi_holo -- ----------------------------------------------------- CREATE SEQUENCE public.multi_holo_seq; CREATE TABLE public.multi_holo ( id INT NOT NULL DEFAULT NEXTVAL ('public.multi_holo_seq'), "name" varchar(1024) NOT NULL, "url" varchar(2048) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT pk_multi_holo PRIMARY KEY (id), CONSTRAINT uk_multi_holo UNIQUE(name) ); -- ----------------------------------------------------- -- Table public.interactive_holo -- ----------------------------------------------------- CREATE SEQUENCE public.interactive_holo_seq; CREATE TABLE public.interactive_holo ( id INT NOT NULL DEFAULT NEXTVAL ('public.interactive_holo_seq'), "name" varchar(1024) NOT NULL, "url" varchar(2048) NULL, state_machine_tree varchar(32000) NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT pk_interactive_holo PRIMARY KEY (id), CONSTRAINT uk_interactive_holo UNIQUE(name) ); -- ----------------------------------------------------- -- Table public.user -- ----------------------------------------------------- CREATE SEQUENCE public.user_seq; CREATE TABLE public."user" ( id INT NOT NULL DEFAULT nextval('user_seq'), first_name varchar(45) NOT NULL, last_name varchar(45) NOT NULL, email varchar(255) NOT NULL, password varchar(255) NOT NULL, auth_token varchar(128) DEFAULT NULL, auth_series varchar(45) DEFAULT NULL, created_at TIMESTAMP NOT NULL DEFAULT now(), enabled bool NOT NULL DEFAULT true, role_id INT, approved bool NOT NULL DEFAULT true, approved_at TIMESTAMP NULL, organization_id INT NOT NULL, password_expired bool NOT NULL DEFAULT false, last_password_change TIMESTAMP DEFAULT NULL, CONSTRAINT pk_user PRIMARY KEY (id), CONSTRAINT uk_user_email UNIQUE (email, organization_id), CONSTRAINT fk_user_org FOREIGN KEY (organization_id) REFERENCES public.organization (id), CONSTRAINT fk_user_role FOREIGN KEY (role_id) REFERENCES public.role (id) ); -- ----------------------------------------------------- -- Table public.content_request -- ----------------------------------------------------- CREATE SEQUENCE public.content_request_seq; CREATE TABLE public."content_request" ( id INT NOT NULL DEFAULT NEXTVAL ('public.content_request_seq'), "name" varchar(4096) NOT NULL, comments varchar(4096) NULL, content_request_type_id INT NOT NULL, organization_id INT NOT NULL, user_id INT NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT pk_content_request PRIMARY KEY (id), CONSTRAINT pk_content_request_org FOREIGN KEY (organization_id) REFERENCES public.organization(id), CONSTRAINT pk_content_request_type FOREIGN KEY (content_request_type_id) REFERENCES public.content_request_type(id), CONSTRAINT pk_content_request_user FOREIGN KEY (user_id) REFERENCES public."user"(id) ); -- ----------------------------------------------------- -- Table public.reset_password_request -- ----------------------------------------------------- CREATE SEQUENCE public.reset_password_request_seq; CREATE TABLE IF NOT EXISTS public.reset_password_request ( id INT NOT NULL DEFAULT NEXTVAL ('public.reset_password_request_seq'), user_id INT NOT NULL, token VARCHAR(45) NOT NULL, request_date TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, reset_on TIMESTAMP(0) NULL, CONSTRAINT pk_rpr PRIMARY KEY (id), CONSTRAINT uk_rpr_token UNIQUE (token), CONSTRAINT fk_rpr_user FOREIGN KEY (user_id) REFERENCES public.user (id) ); -- ----------------------------------------------------- -- Table public.resource -- ----------------------------------------------------- CREATE SEQUENCE public.resource_seq; CREATE TABLE IF NOT EXISTS public.resource ( id INT NOT NULL DEFAULT NEXTVAL ('public.resource_seq'), guid VARCHAR(45) NOT NULL, file_name VARCHAR(1024) NOT NULL, file_size INT NOT NULL, organization_id INT NOT NULL, created_at TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, user_id INT NOT NULL, name VARCHAR(512) NOT NULL, file_type VARCHAR(256) NOT NULL DEFAULT 'text/plain', CONSTRAINT pk_resource PRIMARY KEY (id), CONSTRAINT uk_res_name_org UNIQUE (name, organization_id), CONSTRAINT uk_res_guid UNIQUE (guid), CONSTRAINT fk_res_org FOREIGN KEY (organization_id) REFERENCES public.organization (id) , CONSTRAINT fk_res_user FOREIGN KEY (user_id) REFERENCES public.user (id) ); -- ----------------------------------------------------- -- Table public.role_feature -- ----------------------------------------------------- CREATE SEQUENCE public.role_feature_seq; CREATE TABLE public.role_feature ( id INT NOT NULL DEFAULT nextval('role_feature_seq'), role_id INT NOT NULL, feature_id INT NOT NULL, organization_id INT NOT NULL, CONSTRAINT pk_rf PRIMARY KEY (id), CONSTRAINT fk_rf_feature FOREIGN KEY (feature_id) REFERENCES public.feature (id) , CONSTRAINT fk_rf_org FOREIGN KEY (organization_id) REFERENCES public.organization (id) , CONSTRAINT fk_rf_role FOREIGN KEY (role_id) REFERENCES public.role (id) , CONSTRAINT uk_rf_role_feature UNIQUE (role_id, feature_id) ); -- ----------------------------------------------------- -- Table public.user_attribute -- ----------------------------------------------------- CREATE SEQUENCE public.user_attribute_seq; CREATE TABLE IF NOT EXISTS public.user_attribute ( id INT NOT NULL DEFAULT NEXTVAL ('public.user_attribute_seq'), name VARCHAR(45) NOT NULL, type VARCHAR(45) NOT NULL, required bool NOT NULL DEFAULT false, organization_id INT NOT NULL, CONSTRAINT pk_ua PRIMARY KEY (id), CONSTRAINT uk_ua_name UNIQUE (name), CONSTRAINT fk_ua_org FOREIGN KEY (organization_id) REFERENCES public.organization (id) ); -- ----------------------------------------------------- -- Table public.user_attribute_value -- ----------------------------------------------------- CREATE SEQUENCE public.user_attribute_value_seq; CREATE TABLE IF NOT EXISTS public.user_attribute_value ( id INT NOT NULL DEFAULT NEXTVAL ('public.user_attribute_value_seq'), user_id INT NOT NULL, user_attribute_id INT NOT NULL, created_at TIMESTAMP(0) NOT NULL DEFAULT CURRENT_TIMESTAMP, organization_id INT NOT NULL, value VARCHAR(4096) NULL, CONSTRAINT pk_uav PRIMARY KEY (id), CONSTRAINT uk_uav_user_attr UNIQUE (user_id, user_attribute_id), CONSTRAINT fk_uav_org FOREIGN KEY (organization_id) REFERENCES public.organization (id) , CONSTRAINT fk_uav_user FOREIGN KEY (user_id) REFERENCES public.user (id) , CONSTRAINT fk_uav_ua FOREIGN KEY (user_attribute_id) REFERENCES public.user_attribute (id) ); -- ----------------------------------------------------- -- Table public.user_group -- ----------------------------------------------------- CREATE SEQUENCE public.user_group_seq; CREATE TABLE IF NOT EXISTS public.user_group ( id INT NOT NULL DEFAULT NEXTVAL ('public.user_group_seq'), user_id INT NOT NULL, group_id INT NOT NULL, created_at TIMESTAMP(0) NULL DEFAULT CURRENT_TIMESTAMP, organization_id INT NOT NULL, CONSTRAINT pk_ug PRIMARY KEY (id), CONSTRAINT uk_ug_user_group UNIQUE (user_id, group_id), CONSTRAINT fk_ug_org FOREIGN KEY (organization_id) REFERENCES public.organization (id) , CONSTRAINT fk_ug_user FOREIGN KEY (user_id) REFERENCES public.user (id) , CONSTRAINT fk_ug_group FOREIGN KEY (group_id) REFERENCES public.group (id) ); -- ----------------------------------------------------- -- Table public.course_location -- ----------------------------------------------------- CREATE SEQUENCE public.course_location_seq; CREATE TABLE public.course_location ( id INT NOT NULL DEFAULT NEXTVAL ('public.course_location_seq'), "name" varchar(1024) NOT NULL, address varchar(4096) NOT NULL, reference varchar(2048) NULL, details varchar(4096) NULL, organization_id INT NOT NULL, CONSTRAINT pk_course_location PRIMARY KEY (id) ); -- ----------------------------------------------------- -- Table public.reset_password_request -- ----------------------------------------------------- CREATE SEQUENCE public.course_category_seq; CREATE TABLE public.course_category ( id INT NOT NULL DEFAULT NEXTVAL ('course_category_seq'), "name" varchar(45) NOT NULL, description varchar(4096) NULL, enabled int2 NOT NULL DEFAULT 1, parent_id INT NULL, "order" INT NULL, organization_id INT NOT NULL, CONSTRAINT pk_course_category PRIMARY KEY (id), CONSTRAINT uk_cc_name UNIQUE (name, organization_id), CONSTRAINT fk_cc_org FOREIGN KEY (organization_id) REFERENCES public.organization(id) ); -- ----------------------------------------------------- -- Table public.course_plan -- ----------------------------------------------------- CREATE SEQUENCE public.course_plan_seq; CREATE TABLE public.course_plan ( id INT NOT NULL DEFAULT NEXTVAL ('course_plan_seq'), "name" varchar(255) NOT NULL, description varchar(4096) NULL, course_type int2 NOT NULL, course_category_id INT NOT NULL DEFAULT 0, created_at TIMESTAMP NOT NULL DEFAULT now(), optional int2 NOT NULL DEFAULT 0, organization_id INT NOT NULL, CONSTRAINT pk_course_plan PRIMARY KEY (id), CONSTRAINT uk_course_name UNIQUE (name, organization_id), CONSTRAINT fk_course_plan_category FOREIGN KEY (course_category_id) REFERENCES course_category(id), CONSTRAINT fk_course_plan_org FOREIGN KEY (organization_id) REFERENCES public.organization(id) ); -- ----------------------------------------------------- -- Table public.course_assignment -- ----------------------------------------------------- CREATE SEQUENCE public.course_assignment_seq; CREATE TABLE public.course_assignment ( id INT NOT NULL DEFAULT NEXTVAL ('course_assignment_seq'), user_id INT NOT NULL, course_plan_id INT NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT now(), due_at TIMESTAMP NULL, optional int2 NOT NULL DEFAULT 0, organization_id INT NOT NULL, CONSTRAINT pk_course_assignment PRIMARY KEY (id), CONSTRAINT fk_ca_user FOREIGN KEY (user_id) REFERENCES public.user (id), CONSTRAINT fk_ca_plan FOREIGN KEY (course_plan_id) REFERENCES public.course_plan (id), CONSTRAINT fk_ca_org FOREIGN KEY (organization_id) REFERENCES public.organization (id) ); -- ----------------------------------------------------- -- Table public.course_plan_link -- ----------------------------------------------------- CREATE SEQUENCE public.course_plan_link_seq; CREATE TABLE public.course_plan_link ( id INT NOT NULL DEFAULT NEXTVAL ('course_plan_link_seq'), course_plan_id INT NOT NULL, link varchar(1024) NOT NULL, organization_id INT NOT NULL, "position" INT NOT NULL DEFAULT 0, CONSTRAINT pk_course_plan_link PRIMARY KEY (id), CONSTRAINT fk_cpl_cp FOREIGN KEY (course_plan_id) REFERENCES public.course_plan(id), CONSTRAINT fk_cpl_org FOREIGN KEY (organization_id) REFERENCES public.organization(id) ); -- ----------------------------------------------------- -- Table public.course_plan_local_package -- ----------------------------------------------------- CREATE SEQUENCE public.local_course_package_seq; CREATE TABLE public.local_course_package ( id INT NOT NULL DEFAULT NEXTVAL ('local_course_package_seq'), "name" varchar(255) NOT NULL, "location" varchar(64) NOT NULL, organization_id INT NOT NULL, user_id INT NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT now(), CONSTRAINT pk_local_course_package PRIMARY KEY (id), CONSTRAINT fk_local_course_package_org FOREIGN KEY (organization_id) REFERENCES public.organization(id), CONSTRAINT fk_local_course_package_user FOREIGN KEY (user_id) REFERENCES public."user"(id) ); -- ----------------------------------------------------- -- Table public.course_plan_local_package -- ----------------------------------------------------- CREATE SEQUENCE public.course_plan_local_package_seq; CREATE TABLE public.course_plan_local_package ( id INT NOT NULL DEFAULT NEXTVAL ('course_plan_local_package_seq'), course_plan_id INT NOT NULL, local_course_package_id INT NOT NULL, organization_id INT NOT NULL, "position" INT NOT NULL DEFAULT 0, passing_score INT NOT NULL DEFAULT 80, CONSTRAINT pk_course_plan_local_package PRIMARY KEY (id), CONSTRAINT fk_cplp_cp FOREIGN KEY (course_plan_id) REFERENCES public.course_plan(id), CONSTRAINT fk_cplp_lcp FOREIGN KEY (local_course_package_id) REFERENCES public.local_course_package(id), CONSTRAINT fk_cplp_org FOREIGN KEY (organization_id) REFERENCES public.organization(id), CONSTRAINT fk_lcp_org FOREIGN KEY (organization_id) REFERENCES public.organization(id) ); -- ----------------------------------------------------- -- Table public.course_plan_hologram_live -- ----------------------------------------------------- CREATE SEQUENCE public.course_plan_hologram_live_seq; CREATE TABLE public.course_plan_hologram_live ( id INT NOT NULL DEFAULT NEXTVAL ('course_plan_hologram_live_seq'), course_plan_id INT NOT NULL, avatar_id INT NOT NULL, organization_id INT NOT NULL, "position" INT NOT NULL DEFAULT 0, CONSTRAINT pk_course_plan_hologram_live PRIMARY KEY (id), CONSTRAINT fk_cphl_cp FOREIGN KEY (course_plan_id) REFERENCES public.course_plan(id), CONSTRAINT fk_cphl_avatar FOREIGN KEY (avatar_id) REFERENCES public.avatar(id), CONSTRAINT fk_cphl_org FOREIGN KEY (organization_id) REFERENCES public.organization(id) ); -- ----------------------------------------------------- -- Table public.course_plan_hologram_recorded -- ----------------------------------------------------- CREATE SEQUENCE public.course_plan_hologram_recorded_seq; CREATE TABLE public.course_plan_hologram_recorded ( id INT NOT NULL DEFAULT NEXTVAL ('course_plan_hologram_recorded_seq'), course_plan_id INT NOT NULL, recorded_holo_id INT NOT NULL, organization_id INT NOT NULL, "position" INT NOT NULL DEFAULT 0, CONSTRAINT pk_course_plan_hologram_recorded PRIMARY KEY (id), CONSTRAINT fk_cphr_cp FOREIGN KEY (course_plan_id) REFERENCES public.course_plan(id), CONSTRAINT fk_cphr_hr FOREIGN KEY (recorded_holo_id) REFERENCES public.recorded_holo(id), CONSTRAINT fk_cphr_org FOREIGN KEY (organization_id) REFERENCES public.organization(id) ); -- ----------------------------------------------------- -- Table public.course_plan_hologram_multi -- ----------------------------------------------------- CREATE SEQUENCE public.course_plan_hologram_multi_seq; CREATE TABLE public.course_plan_hologram_multi ( id INT NOT NULL DEFAULT NEXTVAL ('course_plan_hologram_multi_seq'), course_plan_id INT NOT NULL, multi_holo_id INT NOT NULL, organization_id INT NOT NULL, "position" INT NOT NULL DEFAULT 0, CONSTRAINT pk_course_plan_hologram_multi PRIMARY KEY (id), CONSTRAINT fk_cphm_cp FOREIGN KEY (course_plan_id) REFERENCES public.course_plan(id), CONSTRAINT fk_cphm_hm FOREIGN KEY (multi_holo_id) REFERENCES public.multi_holo(id), CONSTRAINT fk_cphm_org FOREIGN KEY (organization_id) REFERENCES public.organization(id) ); -- ----------------------------------------------------- -- Table public.course_plan_hologram_interactive -- ----------------------------------------------------- CREATE SEQUENCE public.course_plan_hologram_interactive_seq; CREATE TABLE public.course_plan_hologram_interactive ( id INT NOT NULL DEFAULT NEXTVAL ('course_plan_hologram_interactive_seq'), course_plan_id INT NOT NULL, interactive_holo_id INT NOT NULL, organization_id INT NOT NULL, "position" INT NOT NULL DEFAULT 0, CONSTRAINT pk_course_plan_hologram_interactive PRIMARY KEY (id), CONSTRAINT fk_cphi_cp FOREIGN KEY (course_plan_id) REFERENCES public.course_plan(id), CONSTRAINT fk_cphi_hi FOREIGN KEY (interactive_holo_id) REFERENCES public.interactive_holo(id), CONSTRAINT fk_cphi_org FOREIGN KEY (organization_id) REFERENCES public.organization(id) ); -- ----------------------------------------------------- -- Table public.course_plan_resource -- ----------------------------------------------------- CREATE SEQUENCE public.course_plan_resource_seq; CREATE TABLE public.course_plan_resource ( id INT NOT NULL DEFAULT NEXTVAL ('course_plan_resource_seq'), course_plan_id INT NOT NULL, resource_id INT NOT NULL, organization_id INT NOT NULL, "position" INT NOT NULL DEFAULT 0, CONSTRAINT pk_course_plan_resource PRIMARY KEY (id), CONSTRAINT fk_cpr_cp FOREIGN KEY (course_plan_id) REFERENCES public.course_plan(id), CONSTRAINT fk_cpr_org FOREIGN KEY (organization_id) REFERENCES public.organization(id), CONSTRAINT fk_cpr_res FOREIGN KEY (resource_id) REFERENCES resource(id) ); -- ----------------------------------------------------- -- Table public.test -- ----------------------------------------------------- CREATE SEQUENCE public.test_seq; CREATE TABLE public.test ( id INT NOT NULL DEFAULT NEXTVAL ('test_seq'), "name" varchar(255) NOT NULL, description varchar(4096) NULL, organization_id INT NOT NULL, CONSTRAINT pk_test PRIMARY KEY (id), CONSTRAINT uk_name_test UNIQUE (name, organization_id), CONSTRAINT fk_test_org FOREIGN KEY (organization_id) REFERENCES public.organization(id) ); -- ----------------------------------------------------- -- Table public.test_item -- ----------------------------------------------------- CREATE SEQUENCE public.test_item_seq; CREATE TABLE public.test_item ( id INT NOT NULL DEFAULT NEXTVAL ('test_item_seq'), title varchar(4096) NOT NULL, "type" int2 NOT NULL DEFAULT 1, body varchar(10000) NULL, response_definition text NOT NULL, required int2 NOT NULL DEFAULT 1, do_not_shuffle int2 NOT NULL DEFAULT 0, difficulty float8 NOT NULL DEFAULT 1, test_id INT NOT NULL, organization_id INT NOT NULL, CONSTRAINT pk_test_item PRIMARY KEY (id), CONSTRAINT fk_ti_org FOREIGN KEY (organization_id) REFERENCES public.organization(id), CONSTRAINT fk_ti_test FOREIGN KEY (test_id) REFERENCES test(id) ); -- ----------------------------------------------------- -- Table public.course_plan_test -- ----------------------------------------------------- CREATE SEQUENCE public.course_plan_test_seq; CREATE TABLE public.course_plan_test ( id INT NOT NULL DEFAULT NEXTVAL ('course_plan_test_seq'), course_plan_id INT NOT NULL, test_id INT NOT NULL, organization_id INT NOT NULL, "position" INT NOT NULL DEFAULT 0, navigation varchar(45) NOT NULL DEFAULT 'choice', allow_skip int2 NOT NULL DEFAULT 0, attempts INT NOT NULL DEFAULT '-1' , question_attempts INT NOT NULL DEFAULT '-1' , passing_score INT NOT NULL DEFAULT 85, lock_passed int2 NOT NULL DEFAULT 0, limit_questions INT NOT NULL DEFAULT '-1' , shuffle_questions int2 NOT NULL DEFAULT 1, shuffle_answers int2 NOT NULL DEFAULT 1, time_limit int2 NOT NULL DEFAULT 0, limit_minutes INT NOT NULL DEFAULT '-1' , limit_seconds INT NOT NULL DEFAULT '-1' , timer_format varchar(45) NOT NULL DEFAULT 'remaining', question_list varchar(1024) NULL, CONSTRAINT pk_course_plan_test PRIMARY KEY (id), CONSTRAINT fk_cpt_cp FOREIGN KEY (course_plan_id) REFERENCES public.course_plan(id), CONSTRAINT fk_cpt_org FOREIGN KEY (organization_id) REFERENCES public.organization(id), CONSTRAINT fk_cpt_test FOREIGN KEY (test_id) REFERENCES public.test(id) ); -- ----------------------------------------------------- -- Table public.course_schedule -- ----------------------------------------------------- CREATE SEQUENCE public.course_schedule_seq; CREATE TABLE public.course_schedule ( id INT NOT NULL DEFAULT NEXTVAL ('course_schedule_seq'), "name" varchar(256) NOT NULL, course_plan_id INT NOT NULL, group_id INT NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT now(), start_at TIMESTAMP NULL, end_at TIMESTAMP NULL, organization_id INT NOT NULL, CONSTRAINT pk_course_schedule PRIMARY KEY (id), CONSTRAINT fk_cs_course_plan FOREIGN KEY (course_plan_id) REFERENCES public.course_plan(id), CONSTRAINT fk_cs_group FOREIGN KEY (group_id) REFERENCES "group"(id), CONSTRAINT fk_cs_org FOREIGN KEY (organization_id) REFERENCES public.organization(id) ); -- ----------------------------------------------------- -- Table public.learning_attempt -- ----------------------------------------------------- CREATE SEQUENCE public.learning_attempt_seq; CREATE TABLE public.learning_attempt ( id INT NOT NULL DEFAULT NEXTVAL ('learning_attempt_seq'), user_id INT NOT NULL, course_plan_id INT NOT NULL, module_id INT NOT NULL, module_type varchar(45) NOT NULL, entry INT NULL, exit INT NULL, credit INT NULL, total_time varchar(45) NULL, best_score_scaled float8 NULL, completion_status INT NULL, success_status INT NULL, suspend_data text NULL, "location" varchar(2048) NULL, first_start TIMESTAMP NULL, last_start TIMESTAMP NULL, completed_on TIMESTAMP NULL, organization_id INT NOT NULL, CONSTRAINT pk_learning_attempt PRIMARY KEY (id), CONSTRAINT uk_learning_attempt UNIQUE(user_id, course_plan_id, module_id, module_type, organization_id), CONSTRAINT fk_la_course_plan FOREIGN KEY (course_plan_id) REFERENCES public.course_plan(id), CONSTRAINT fk_la_org FOREIGN KEY (organization_id) REFERENCES public.organization(id) ); -- ----------------------------------------------------- -- Table public.learning_session -- ----------------------------------------------------- CREATE SEQUENCE public.learning_session_seq; CREATE TABLE public.learning_session ( id INT NOT NULL DEFAULT NEXTVAL ('learning_session_seq'), "mode" int2 NOT NULL DEFAULT 0, completion_status int2 NOT NULL, success_status int2 NOT NULL, score_scaled float8 NULL, score_raw float8 NULL, score_min float8 NULL, score_max float8 NULL, session_time varchar(45) NULL, "start" TIMESTAMP NOT NULL, "end" TIMESTAMP NULL, learning_attempt_id INT NOT NULL, organization_id INT NOT NULL, CONSTRAINT pk_learning_session PRIMARY KEY (id), CONSTRAINT fk_ls_la FOREIGN KEY (learning_attempt_id) REFERENCES public.learning_attempt(id), CONSTRAINT fk_ls_org FOREIGN KEY (organization_id) REFERENCES public.organization(id) ); -- ----------------------------------------------------- -- Table public.thread_category -- ----------------------------------------------------- CREATE SEQUENCE public.thread_category_seq; CREATE TABLE public.thread_category ( id INT NOT NULL DEFAULT NEXTVAL ('public.thread_category_seq'), "name" varchar(1024) NOT NULL, organization_id INT NOT NULL, CONSTRAINT pk_thread_category PRIMARY KEY (id), CONSTRAINT uk_tg_name UNIQUE (name, organization_id), CONSTRAINT fk_tg_org FOREIGN KEY (organization_id) REFERENCES public.organization(id) ); -- ----------------------------------------------------- -- Table public.thread -- ----------------------------------------------------- CREATE SEQUENCE public.thread_seq; CREATE TABLE public.thread ( id INT NOT NULL DEFAULT NEXTVAL ('public.thread_seq'), "name" varchar(1024) NOT NULL, owner_id INT NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT now(), public bool NOT NULL DEFAULT false, closed bool NOT NULL DEFAULT false, archived bool NOT NULL DEFAULT false, organization_id INT NOT NULL, thread_category_id INT NOT NULL, CONSTRAINT pk_thread PRIMARY KEY (id), CONSTRAINT fk_thread_org FOREIGN KEY (organization_id) REFERENCES public.organization(id), CONSTRAINT fk_thread_thread_category FOREIGN KEY (thread_category_id) REFERENCES public.thread_category(id), CONSTRAINT fk_thread_user FOREIGN KEY (owner_id) REFERENCES "user"(id) ); -- ----------------------------------------------------- -- Table public.message -- ----------------------------------------------------- CREATE SEQUENCE public.message_seq; CREATE TABLE public.message ( id INT NOT NULL DEFAULT NEXTVAL ('public.message_seq'), "content" text NOT NULL, user_id INT NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT now(), updated_at TIMESTAMP NULL, up_votes INT NOT NULL DEFAULT 0, down_votes INT NOT NULL DEFAULT 0, organization_id INT NOT NULL, thread_id INT NOT NULL, CONSTRAINT pk_message PRIMARY KEY (id), CONSTRAINT fk_mgs_user FOREIGN KEY (user_id) REFERENCES "user"(id), CONSTRAINT fk_msg_org FOREIGN KEY (organization_id) REFERENCES public.organization(id), CONSTRAINT fk_msg_thread FOREIGN KEY (thread_id) REFERENCES public.thread(id) ); -- ----------------------------------------------------- -- Table public.follow -- ----------------------------------------------------- CREATE SEQUENCE public.follow_seq; CREATE TABLE public.follow ( id INT NOT NULL DEFAULT NEXTVAL ('public.follow_seq'), thread_id INT NOT NULL, user_id INT NOT NULL, organization_id INT NOT NULL, CONSTRAINT pk_follow PRIMARY KEY (id), CONSTRAINT uk_flw_thread_user UNIQUE (thread_id, user_id, organization_id), CONSTRAINT fk_flw_org FOREIGN KEY (organization_id) REFERENCES public.organization(id), CONSTRAINT fk_flw_thread FOREIGN KEY (thread_id) REFERENCES public.thread(id), CONSTRAINT fk_flw_user FOREIGN KEY (user_id) REFERENCES "user"(id) ); -- ----------------------------------------------------- -- Table public.file -- ----------------------------------------------------- CREATE SEQUENCE public.file_seq; CREATE TABLE public.file ( id INT NOT NULL DEFAULT NEXTVAL ('public.file_seq'), "name" varchar(4096) NOT NULL, mime_type varchar(256) NOT NULL, "content" oid NOT NULL, user_id INT NOT NULL, organization_id INT NOT NULL DEFAULT 1, created_at TIMESTAMP NOT NULL DEFAULT now(), "size" INT NULL, CONSTRAINT file_pkey PRIMARY KEY (id), CONSTRAINT fk_file_org FOREIGN KEY (organization_id) REFERENCES public.organization(id) ); -- ----------------------------------------------------- -- Table public.message_file -- ----------------------------------------------------- CREATE SEQUENCE public.message_file_seq; CREATE TABLE public.message_file ( id INT NOT NULL DEFAULT NEXTVAL ('public.message_file_seq'), message_id INT NOT NULL, file_id INT NOT NULL, organization_id INT NOT NULL DEFAULT 1, CONSTRAINT pk_message_file PRIMARY KEY (id) ); ALTER TABLE public.message_file ADD CONSTRAINT fk_msg_file FOREIGN KEY (file_id) REFERENCES file(id); ALTER TABLE public.message_file ADD CONSTRAINT fk_msgf_message FOREIGN KEY (message_id) REFERENCES public.message(id); ALTER TABLE public.message_file ADD CONSTRAINT fk_msgf_org FOREIGN KEY (organization_id) REFERENCES public.organization(id); -- ----------------------------------------------------- -- Table public.notification -- ----------------------------------------------------- CREATE SEQUENCE public.notification_seq; CREATE TABLE public.notification ( id INT NOT NULL DEFAULT NEXTVAL ('public.notification_seq'), thread_id INT NOT NULL, user_id INT NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT now(), consumed bool NOT NULL DEFAULT false, organization_id INT NOT NULL, CONSTRAINT pk_notification PRIMARY KEY (id) ); ALTER TABLE public.notification ADD CONSTRAINT fk_noti_org FOREIGN KEY (organization_id) REFERENCES public.organization(id); ALTER TABLE public.notification ADD CONSTRAINT fk_noti_thread FOREIGN KEY (thread_id) REFERENCES public.thread(id); ALTER TABLE public.notification ADD CONSTRAINT fk_noti_user FOREIGN KEY (user_id) REFERENCES "user"(id); -- ----------------------------------------------------- -- Table public.thread_role -- ----------------------------------------------------- CREATE SEQUENCE public.thread_role_seq; CREATE TABLE public.thread_role ( id INT NOT NULL DEFAULT NEXTVAL ('public.thread_role_seq'), thread_id INT NOT NULL, role_id INT NOT NULL, organization_id INT NOT NULL, CONSTRAINT pk_thread_role PRIMARY KEY (id), CONSTRAINT uk_tr_thread_role UNIQUE (thread_id, role_id) ); ALTER TABLE public.thread_role ADD CONSTRAINT fk_tr_org FOREIGN KEY (organization_id) REFERENCES public.organization(id); ALTER TABLE public.thread_role ADD CONSTRAINT fk_tr_role FOREIGN KEY (role_id) REFERENCES role(id); ALTER TABLE public.thread_role ADD CONSTRAINT fk_tr_thread FOREIGN KEY (thread_id) REFERENCES public.thread(id); -- ----------------------------------------------------- -- Table public.thread_group -- ----------------------------------------------------- CREATE SEQUENCE public.thread_group_seq; CREATE TABLE public.thread_group ( id INT NOT NULL DEFAULT NEXTVAL ('public.thread_group_seq'), thread_id INT NOT NULL, group_id INT NOT NULL, organization_id INT NOT NULL, CONSTRAINT pk_thread_group2 PRIMARY KEY (id), CONSTRAINT uk_tr_thread_group UNIQUE (thread_id, group_id, organization_id), CONSTRAINT fk_tr_group FOREIGN KEY (group_id) REFERENCES public."group"(id), CONSTRAINT fk_tr_org FOREIGN KEY (organization_id) REFERENCES public.organization(id), CONSTRAINT fk_tr_thread FOREIGN KEY (thread_id) REFERENCES public.thread(id) ); -- ----------------------------------------------------- -- View public.vw_catalog_course -- ----------------------------------------------------- CREATE OR REPLACE VIEW public.vw_catalog_course AS SELECT DISTINCT c.id, c.name, c.description, c.course_type, c.course_category_id, c.created_at, c.optional, c.organization_id FROM public.course_plan c LEFT JOIN public.course_category cc ON cc.id = c.course_category_id WHERE c.optional = 1 AND cc.enabled = 1 ORDER BY c.name ; -- ----------------------------------------------------- -- View public.vw_course_plan_module -- ----------------------------------------------------- CREATE OR REPLACE VIEW public.vw_course_plan_module AS SELECT cpt.course_plan_id, cpt.id, t.id AS module_id, concat(cpt.id, '') AS reference, t.name, cpt."position", 'test' :: text AS type, cpt.organization_id, cpt.passing_score FROM public.course_plan_test cpt JOIN public.test t ON t.id = cpt.test_id UNION ALL SELECT cpr.course_plan_id, cpr.id, r.id AS module_id, r.guid AS reference, r.name, cpr."position", 'resource' :: text AS type, cpr.organization_id, 0 AS passing_score FROM public.course_plan_resource cpr JOIN resource r ON r.id = cpr.resource_id UNION ALL SELECT cpl.course_plan_id, cpl.id, cpl.id AS module_id, cpl.link AS reference, cpl.link AS name, cpl."position", 'link' :: text AS type, cpl.organization_id, 0 AS passing_score FROM public.course_plan_link cpl UNION ALL SELECT cplp.course_plan_id, cplp.id, lcp.id AS module_id, lcp.location AS reference, lcp.name, cplp."position", 'local_course_package' :: text AS type, cplp.organization_id, cplp.passing_score FROM public.course_plan_local_package cplp JOIN public.local_course_package lcp ON lcp.id = cplp.local_course_package_id UNION ALL SELECT cphl.course_plan_id, cphl.id, av.id AS module_id, av.name AS reference, av.name, cphl."position", 'hologram_live' :: text AS type, cphl.organization_id, 0 as passing_score FROM public.course_plan_hologram_live cphl JOIN public.avatar av ON av.id = cphl.avatar_id UNION ALL SELECT cphr.course_plan_id, cphr.id, rh.id AS module_id, rh.name AS reference, rh.name, cphr."position", 'hologram_recorded' :: text AS type, cphr.organization_id, 0 as passing_score FROM public.course_plan_hologram_recorded cphr JOIN public.recorded_holo rh ON rh.id = cphr.recorded_holo_id UNION ALL SELECT cphm.course_plan_id, cphm.id, mh.id AS module_id, mh.name AS reference, mh.name, cphm."position", 'hologram_multi' :: text AS type, cphm.organization_id, 0 as passing_score FROM public.course_plan_hologram_multi cphm JOIN public.multi_holo mh ON mh.id = cphm.multi_holo_id UNION ALL SELECT cphi.course_plan_id, cphi.id, ih.id AS module_id, ih.name AS reference, ih.name, cphi."position", 'hologram_interactive' :: text AS type, cphi.organization_id, 0 as passing_score FROM public.course_plan_hologram_interactive cphi JOIN public.interactive_holo ih ON ih.id = cphi.interactive_holo_id ; -- ----------------------------------------------------- -- View public.vw_course_plan_module -- ----------------------------------------------------- CREATE OR REPLACE VIEW public.vw_reports_general AS SELECT pg_catalog.concat(u.first_name, ' ', u.last_name) AS user_fullname, u.email, c.name AS course_name, la.id, la.course_plan_id, la.module_id, la.user_id, la.total_time, la.best_score_scaled, la.completion_status, la.success_status, la.first_start, la.last_start, la.completed_on, la.organization_id, cpm.name AS module_name, cpm.type AS module_type FROM public.learning_attempt la JOIN public.course_plan c ON c.id = la.course_plan_id JOIN public.vw_course_plan_module cpm ON cpm.course_plan_id = c.id AND cpm.module_id = la.module_id AND cpm.type = la.module_type :: text JOIN "user" u ON u.id = la.user_id ORDER BY la.id; -- ----------------------------------------------------- -- View public.vw_course_plan_module -- ----------------------------------------------------- CREATE OR REPLACE VIEW public.vw_reports_group AS SELECT g.id AS group_id, g.name AS group_name, pg_catalog.concat(u.first_name, ' ', u.last_name) AS user_fullname, u.email, cp.name AS course_name, cp.id AS course_plan_id, cpm.module_id, pg_catalog.concat(u.id, cp.id, cpm.module_id) AS id, u.id AS user_id, vrg.total_time, vrg.best_score_scaled, vrg.completion_status, vrg.success_status, vrg.first_start, vrg.last_start, vrg.completed_on, g.organization_id, cpm.name AS module_name, cpm.type AS module_type, cs.start_at AS schedule_start FROM "group" g JOIN user_group ug ON ug.group_id = g.id JOIN "user" u ON u.id = ug.user_id JOIN public.course_schedule cs ON cs.group_id = g.id JOIN public.course_plan cp ON cp.id = cs.course_plan_id JOIN public.vw_course_plan_module cpm ON cpm.course_plan_id = cp.id LEFT JOIN public.vw_reports_general vrg ON vrg.user_id = u.id AND vrg.course_plan_id = cs.course_plan_id AND vrg.module_id = cpm.module_id; -- ----------------------------------------------------- -- View public.vw_course_plan_module -- ----------------------------------------------------- CREATE OR REPLACE VIEW public.vw_user_course AS SELECT DISTINCT c.id, u.id AS user_id, c.name, c.description, ca.created_at, ca.tp AS course_type, ca.due_date AS due_at, ( SELECT min(learning_attempt.first_start) AS min FROM public.learning_attempt WHERE learning_attempt.course_plan_id = c.id AND learning_attempt.user_id = u.id ) AS started, ( SELECT count(*) AS count FROM public.vw_course_plan_module WHERE vw_course_plan_module.course_plan_id = c.id ) AS total_modules, ( SELECT count(*) AS count FROM public.learning_attempt WHERE learning_attempt.course_plan_id = c.id AND learning_attempt.completion_status = 1 AND learning_attempt.user_id = u.id ) AS completed_modules, u.organization_id FROM "user" u JOIN ( SELECT cs.course_plan_id, ug.user_id, cs.created_at, cs.end_at AS due_date, 'scheduled' :: text AS tp FROM public.course_schedule cs JOIN user_group ug ON ug.group_id = cs.group_id UNION ALL SELECT ca.course_plan_id, ca.user_id, ca.created_at, ca.due_at AS due_date, 'optional' :: text AS tp FROM public.course_assignment ca ) ca ON ca.user_id = u.id JOIN public.course_plan c ON c.id = ca.course_plan_id ORDER BY ca.created_at DESC; -- ----------------------------------------------------- -- View public.vw_email_subscription_to_thread -- ----------------------------------------------------- CREATE OR REPLACE VIEW public.vw_email_subscription_to_thread AS SELECT u.id, f.thread_id, u.email, u.organization_id FROM public.follow f JOIN "user" u ON u.id = f.user_id; -- ----------------------------------------------------- -- View public.vw_message -- ----------------------------------------------------- CREATE OR REPLACE VIEW public.vw_message AS SELECT msg.id, msg.content, msg.user_id, msg.created_at, msg.updated_at, msg.up_votes, msg.down_votes, msg.organization_id, msg.thread_id, concat(u.first_name, ' ', u.last_name) AS user_name, r.name AS role_name FROM public.message msg JOIN "user" u ON u.id = msg.user_id JOIN role r ON r.id = u.role_id; -- ----------------------------------------------------- -- View public.vw_thread -- ----------------------------------------------------- CREATE OR REPLACE VIEW public.vw_thread AS WITH visiblethreads AS ( SELECT th.id, th.name, th.owner_id, th.created_at, th.public, th.closed, th.archived, th.organization_id, th.thread_category_id, u.id AS user_id FROM public.thread th JOIN public.thread_role tr ON tr.thread_id = th.id JOIN "user" u ON u.role_id = tr.role_id UNION SELECT th.id, th.name, th.owner_id, th.created_at, th.public, th.closed, th.archived, th.organization_id, th.thread_category_id, u.id AS user_id FROM public.thread th JOIN public.thread_group tg ON tg.thread_id = th.id JOIN user_group ug ON ug.group_id = tg.group_id JOIN "user" u ON u.id = ug.user_id UNION SELECT th.id, th.name, th.owner_id, th.created_at, th.public, th.closed, th.archived, th.organization_id, th.thread_category_id, u.id AS user_id FROM public.thread th LEFT JOIN "user" u ON th.organization_id = u.organization_id LEFT JOIN public.thread_role tr ON tr.thread_id = th.id LEFT JOIN public.thread_group tg ON tg.thread_id = th.id WHERE tr.role_id IS NULL AND tg.group_id IS NULL UNION SELECT th.id, th.name, th.owner_id, th.created_at, th.public, th.closed, th.archived, th.organization_id, th.thread_category_id, u.id AS user_id FROM public.thread th LEFT JOIN "user" u ON th.organization_id = u.organization_id AND (u.role_id = ANY (ARRAY[1, 37])) UNION SELECT th.id, th.name, th.owner_id, th.created_at, th.public, th.closed, th.archived, th.organization_id, th.thread_category_id, '-1'::integer AS user_id FROM public.thread th LEFT JOIN public.thread_role tr ON tr.thread_id = th.id LEFT JOIN public.thread_group tg ON tg.thread_id = th.id WHERE th.public = true AND tr.role_id IS NULL AND tg.group_id IS NULL ) SELECT DISTINCT vt.id, vt.name, vt.owner_id, vt.created_at, vt.public, vt.closed, vt.archived, vt.organization_id, vt.thread_category_id, vt.user_id, CASE WHEN fl.id IS NOT NULL THEN true ELSE false END AS follow FROM visiblethreads vt LEFT JOIN public.follow fl ON fl.thread_id = vt.id AND fl.user_id = vt.user_id ORDER BY vt.user_id; -- ----------------------------------------------------- -- View public.vw_content_request -- ----------------------------------------------------- CREATE VIEW public.vw_content_request AS SELECT cr.id, cr.name, cr.content_request_type_id, cr.name AS content_request_type, cr.organization_id, o.name AS organization FROM public.content_request AS cr JOIN public.content_request_type AS crt ON crt.id = cr.content_request_type_id JOIN public.organization AS o ON o.id = cr.organization_id ;