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


-- public.core_permissions definition

-- Drop table

-- DROP TABLE public.core_permissions;

CREATE TABLE public.core_permissions (
	code varchar(7) NOT NULL,
	permission_name varchar(50) NOT NULL,
	parent varchar(7) NULL DEFAULT NULL::character varying,
	permission_function varchar(255) NULL DEFAULT NULL::character varying,
	default_child varchar(50) NULL DEFAULT NULL::character varying,
	permission_namespace varchar(255) NOT NULL,
	"module" varchar(25) NOT NULL,
	permission_description varchar(255) NULL DEFAULT NULL::character varying,
	permission_type varchar(3) NULL DEFAULT NULL::character varying,
	icon varchar(48) NULL DEFAULT NULL::character varying,
	ord int8 NULL,
	hidden bool NULL DEFAULT false,
	guest bool NOT NULL DEFAULT false,
	meta jsonb NULL,
	CONSTRAINT idx_34541_primary PRIMARY KEY (code)
);
CREATE UNIQUE INDEX idx_34541_code_unique ON public.core_permissions USING btree (code);
CREATE INDEX idx_34541_core_permissions_code_list_permission_types_fk_idx ON public.core_permissions USING btree (permission_type);



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

-- public.core_users definition

-- Drop table

-- DROP TABLE public.core_users;

CREATE TABLE public.core_users (
	id bigserial NOT NULL,
	username varchar(20) NOT NULL,
	"password" text NOT NULL,
	registryid int8 NULL,
	email varchar(255) NULL DEFAULT NULL::character varying,
	socketid int8 NULL,
	enabled int2 NULL,
	sessionid varchar(32) NULL DEFAULT NULL::character varying,
	avatar varchar(255) NULL DEFAULT NULL::character varying,
	status varchar(5) NULL,
	hidden int2 NULL DEFAULT '0'::smallint,
	"language" varchar(6) NOT NULL DEFAULT 'en-EN'::character varying,
	settings jsonb NOT NULL DEFAULT '{}'::json,
	meta jsonb NOT NULL DEFAULT '{}'::json,
	organization int8 NULL,
	verify_token varchar(50) NULL,
	first_name varchar(50) NULL,
	last_name varchar(50) NULL,
	group_id int8 NULL,
	CONSTRAINT idx_34580_primary PRIMARY KEY (id)
);
CREATE UNIQUE INDEX core_users_email_idx ON public.core_users USING btree (email);
CREATE INDEX idx_34580_core_users_registry_entities_fk ON public.core_users USING btree (registryid);
CREATE UNIQUE INDEX idx_34580_core_users_username_idx ON public.core_users USING btree (username);



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


-- public.core_user_roles definition

-- Drop table

-- DROP TABLE public.core_user_roles;

CREATE TABLE public.core_user_roles (
	id bigserial NOT NULL,
	role_name varchar(45) NOT NULL,
	hidden int2 NULL DEFAULT '0'::smallint,
	CONSTRAINT idx_34604_primary PRIMARY KEY (id)
);
CREATE UNIQUE INDEX idx_34604_core_user_roles_role_name_idx ON public.core_user_roles USING btree (role_name);

ALTER SEQUENCE public.core_user_roles_id_seq
	RESTART 100;


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


-- public.core_user_role_has_permission definition

-- Drop table

-- DROP TABLE public.core_user_role_has_permission;


CREATE TABLE public.core_user_role_has_permission (
	id bigserial NOT NULL,
	role_id numeric NOT NULL,
	permission_code varchar(7) NULL DEFAULT NULL::character varying,
	CONSTRAINT idx_34611_primary PRIMARY KEY (id)
);
CREATE INDEX idx_34611_core_user_role_has_permission_core_permissions_fk ON public.core_user_role_has_permission USING btree (permission_code);
CREATE INDEX idx_34611_core_user_role_has_permission_core_user_roles_fk ON public.core_user_role_has_permission USING btree (role_id);


-- public.core_user_role_has_permission foreign keys

ALTER TABLE public.core_user_role_has_permission ADD CONSTRAINT core_user_role_has_permission_core_permissions_fk FOREIGN KEY (permission_code) REFERENCES public.core_permissions(code) ON DELETE RESTRICT ON UPDATE RESTRICT;



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


-- public.core_user_has_user_role definition

-- Drop table

-- DROP TABLE public.core_user_has_user_role;

CREATE TABLE public.core_user_has_user_role (
	id bigserial NOT NULL,
	user_id numeric NOT NULL,
	role_id numeric NOT NULL,
	CONSTRAINT idx_34595_primary PRIMARY KEY (id)
);
CREATE UNIQUE INDEX idx_34595_core_user_has_user_role_user_id_idx ON public.core_user_has_user_role USING btree (user_id, role_id);
CREATE INDEX idx_34595_user_has_user_role_core_user_roles_fk ON public.core_user_has_user_role USING btree (role_id);



-----------------------------------------------------------------------------------------------------------------------------
--SERVER SQL WAS HERE

-- public.core_files definition

-- Drop table

-- DROP TABLE public.core_files;

CREATE TABLE public.core_files (
	id bigserial NOT NULL,
	title varchar NULL,
	mime varchar NULL,
	url varchar NULL,
	created_on timestamp NOT NULL DEFAULT now(),
	updated_on timestamp NOT NULL DEFAULT now(),
	user_id int8 NOT NULL DEFAULT 0,
	meta jsonb NULL,
	lastcheck_on timestamp NOT NULL DEFAULT now(),
	CONSTRAINT core_files_pk PRIMARY KEY (id)
);
CREATE UNIQUE INDEX core_files_id_idx ON public.core_files USING btree (id);


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


-- public.core_assets definition

-- Drop table

-- DROP TABLE public.core_assets;

CREATE TABLE public.core_assets (
	id bigserial NOT NULL,
	role_id int4 NULL,
	asset_type varchar(7) NULL DEFAULT NULL::character varying,
	asset_name varchar(50) NULL DEFAULT NULL::character varying,
	description varchar(255) NULL DEFAULT NULL::character varying,
	custom bool NULL DEFAULT false,
	asset_innerhtml text NULL,
	asset_parameters jsonb NULL DEFAULT '{}'::jsonb,
	asset_code text NULL,
	before_trigger varchar(255) NULL DEFAULT NULL::character varying,
	after_trigger varchar(255) NULL DEFAULT NULL::character varying,
	failure_rollback int2 NULL,
	exec_order int8 NULL,
	meta jsonb NULL,
	CONSTRAINT idx_34512_primary PRIMARY KEY (id)
);
CREATE UNIQUE INDEX idx_34512_core_assets_asset_name_idx ON public.core_assets USING btree (asset_name, custom, role_id);


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



CREATE OR REPLACE FUNCTION public.on_update_current_timestamp_core_sm_log()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
   NEW.changedon = now();
   RETURN NEW;
END;
$function$
;



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



-- public.code_lists definition

-- Drop table

-- DROP TABLE public.code_lists;

CREATE TABLE public.code_lists (
	id serial4 NOT NULL,
	"table" varchar(255) NOT NULL,
	description varchar(255) NULL,
	editable bool NOT NULL DEFAULT true,
	icon varchar(255) NULL,
	meta jsonb NULL
);
CREATE UNIQUE INDEX code_lists_id_idx ON public.code_lists USING btree (id);
CREATE UNIQUE INDEX code_lists_table_idx ON public.code_lists USING btree ("table");

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


-- public.code_list_permission_types definition

-- Drop table

-- DROP TABLE public.code_list_permission_types;

CREATE TABLE public.code_list_permission_types (
	code varchar(3) NOT NULL,
	description varchar(50) NULL DEFAULT NULL::character varying,
	ord int8 NULL,
	icon varchar(255) NULL,
	misc varchar(255) NULL,
	properties jsonb NULL,
	CONSTRAINT idx_34488_primary PRIMARY KEY (code)
);
CREATE UNIQUE INDEX code_list_permission_types_code_idx ON public.code_list_permission_types USING btree (code);

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



-- FUNCTION;

-- Table Triggers

create trigger on_update_current_timestamp before
update
    on
    public.core_sm_log for each row execute function on_update_current_timestamp_core_sm_log();



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

CREATE TABLE public.core_user_groups (
	id bigserial NOT NULL,
	group_name varchar(20) NOT NULL,
    group_type varchar(5) NOT NULL,
	email varchar(255) NULL DEFAULT NULL::character varying,
	"language" varchar(6) NOT NULL DEFAULT 'en-EN'::character varying,
	meta jsonb NOT NULL DEFAULT '{}'::json,
	group_description TEXT NULL,
	CONSTRAINT idx_cug_primary PRIMARY KEY (id),
	CONSTRAINT core_user_groups_fk FOREIGN KEY (group_type) REFERENCES public.code_list_usergroups_types(code)
);
CREATE UNIQUE INDEX core_user_groups_group_name_idx ON public.core_user_groups USING btree (group_name);