-- CACHE TABLE GENERATION

SELECT c.concept_key, t.item_name, t.plaintext, trim(both from t.plaintext) as plaintext_normalized, t.language_key,
c.list_preferred, c.author_copy as author_string, cr.item_name as rank,
t2.plaintext as termtype, c.list_code, c.sort_code,
c.meaning_key, cg.concept_group_key, ld.local_domain_key, domain.domain_key, subject_area.subject_area_key

INTO tcache
FROM concept as c
left join term as t on t.term_key = c.term_key
left join concept_rank as cr on c.concept_rank_key = cr.concept_rank_key
left join language as l on l.language_key=t.language_key
left join concept as c2 on c.name_type_concept_key  = c2.concept_key
left join term as t2 on c2.term_key = t2.term_key
,concept_group as cg, local_domain as ld, domain, subject_area
WHERE
c.concept_group_key = cg.concept_group_key
and cg.local_domain_key = ld.local_domain_key 
and ld.domain_key = domain.domain_key
and domain.subject_area_key = subject_area.subject_area_key;




-- INDEX GENERATION

CREATE INDEX ix_tcache_plaintext
  ON public.tcache
  USING btree
  (plaintext);

CREATE INDEX ix_tcache_plaintext_normalized
  ON public.tcache
  USING btree
  (plaintext_normalized);

CREATE INDEX ix_tcache_itemname
  ON public.tcache
  USING btree
  (item_name);

CREATE INDEX ix_tcache_cgkey
  ON public.tcache
  USING btree
  (concept_group_key);

CREATE INDEX ix_tcache_ldomain
  ON public.tcache
  USING btree
  (local_domain_key);

CREATE INDEX ix_tcache_domain
  ON public.tcache
  USING btree
  (domain_key);

CREATE INDEX ix_tcache_subject
  ON public.tcache
  USING btree
  (subject_area_key);

CREATE INDEX ix_tcache_language
  ON public.tcache
  USING btree
  (language_key);

CREATE INDEX ix_tcache_meaning
  ON public.tcache
  USING btree
  (meaning_key);