-- -----------------------------------------------------
-- 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 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 
;