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

-- public.registry_entities definition

-- Drop table

-- DROP TABLE public.registry_entities;
CREATE TABLE public.registry_entities (
    id bigserial NOT NULL,
    entity_category character varying(3) NOT NULL,
    entity_form character varying(3),
    entity_type character varying(5),
    entity_name character varying(255),
    person_name character varying(50),
    person_surname character varying(50),
    entity_address character varying(100),
    entity_pcode character varying(10),
    entity_locality character varying(45),
    entity_country character varying(2) DEFAULT 'XX'::character varying NOT NULL,
    entity_state bigint,
    entity_vatn character varying(45),
    parent_id bigint,
    reference_id character varying(45),
    remarks text,
    meta jsonb DEFAULT '{}'::jsonb NOT NULL,
    entity_brand character varying(50)
);

CREATE UNIQUE INDEX registry_entities_entity_category_idx ON public.registry_entities USING btree (entity_category, entity_name, entity_country, entity_vatn);
-- CREATE UNIQUE INDEX registry_entities_entity_category_idx ON public.registry_entities USING btree (entity_category, entity_name, entity_state, entity_vatn);


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

-- public.code_list_countries definition

-- Drop table

-- DROP TABLE public.code_list_countries;

CREATE TABLE public.code_list_countries (
	code varchar(2) NOT NULL,
	description varchar(60) NOT NULL,
	ord int8 NULL,
	icon varchar(255) NULL,
	misc varchar(255) NULL,
	properties jsonb NULL,
	CONSTRAINT idx_34469_primary PRIMARY KEY (code)
);
CREATE UNIQUE INDEX code_list_countries_idx ON public.code_list_countries USING btree (code);




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

-- public.code_list_registry_categories definition

-- Drop table

-- DROP TABLE public.code_list_registry_categories;

CREATE TABLE public.code_list_registry_categories (
	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_34492_primary PRIMARY KEY (code)
);
CREATE UNIQUE INDEX code_list_registry_categories_code_idx ON public.code_list_registry_categories USING btree (code);



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

-- public.code_list_registry_forms definition

-- Drop table

-- DROP TABLE public.code_list_registry_forms;

CREATE TABLE public.code_list_registry_forms (
	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_34496_primary PRIMARY KEY (code)
);
CREATE UNIQUE INDEX code_list_registry_forms_code_idx ON public.code_list_registry_forms USING btree (code);



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

-- public.code_list_registry_types definition

-- Drop table

-- DROP TABLE public.code_list_registry_types;

CREATE TABLE public.code_list_registry_types (
	code varchar(5) 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_34500_primary PRIMARY KEY (code)
);
CREATE UNIQUE INDEX code_list_registry_types_code_idx ON public.code_list_registry_types USING btree (code);



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


-- public.code_list_states definition

-- Drop table

-- DROP TABLE public.code_list_states;

CREATE TABLE public.code_list_states (
	id bigserial NOT NULL,
	state_code varchar(5) NOT NULL,
	country_code varchar(2) NOT NULL,
	description varchar(60) NOT NULL,
	ord int8 NULL,
	CONSTRAINT idx_34506_primary PRIMARY KEY (id)
);
CREATE UNIQUE INDEX idx_34506_code ON public.code_list_states USING btree (country_code, state_code);


-- public.code_list_states foreign keys

ALTER TABLE public.code_list_states ADD CONSTRAINT state_country_fk FOREIGN KEY (country_code) REFERENCES public.code_list_countries(code) ON DELETE RESTRICT ON UPDATE RESTRICT;




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

-- public.registry_entities foreign keys

ALTER TABLE public.registry_entities ADD CONSTRAINT registry_country_fk FOREIGN KEY (entity_country) REFERENCES public.code_list_countries(code) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE public.registry_entities ADD CONSTRAINT registry_entities_code_list_registry_categories_fk FOREIGN KEY (entity_category) REFERENCES public.code_list_registry_categories(code) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE public.registry_entities ADD CONSTRAINT registry_entities_code_list_registry_forms_fk FOREIGN KEY (entity_form) REFERENCES public.code_list_registry_forms(code) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE public.registry_entities ADD CONSTRAINT registry_entities_code_list_registry_types_fk FOREIGN KEY (entity_type) REFERENCES public.code_list_registry_types(code) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE public.registry_entities ADD CONSTRAINT registry_state_fk FOREIGN KEY (entity_state) REFERENCES public.code_list_states(id) ON DELETE RESTRICT ON UPDATE RESTRICT;



