 USE `$DBNAME`;
 SELECT Now() `--`,"Create Transitive Closure and Proximal Primitive Views" '--';
 
--- CREATE VIEWS
-
--- Create view of transclosure with preferred terms
-CREATE  OR REPLACE VIEW `snap_transclose_pref` AS
-SELECT `t`.`supertypeId`,`p2`.`term` `supertypeTerm`, `t`.`subtypeId`, `p`.`term` `subtypeTerm`
-	FROM `snap_transclose` `t`
-	JOIN `snap_pref` `p` ON `t`.`subtypeId`=`p`.`conceptId`
-	JOIN `snap_pref` `p2` ON `t`.`supertypeId`=`p2`.`conceptId`;
-
--- Create view of proximal primitives with preferred terms	
-CREATE  OR REPLACE VIEW `snap_proxprim_pref` AS
-SELECT `t`.`supertypeId`,`p2`.`term` `supertypeTerm`, `t`.`subtypeId`, `p`.`term` `subtypeTerm`
-	FROM `snap_proximal_primitives` `t`
-	JOIN `snap_pref` `p` ON `t`.`subtypeId`=`p`.`conceptId`
-	JOIN `snap_pref` `p2` ON `t`.`supertypeId`=`p2`.`conceptId`;
+-- CREATE TRANSITIVE CLOSURE VIEWS
+
+DROP VIEW IF EXISTS `snap_tc_descendant_fsn`;
+
+CREATE VIEW `snap_tc_descendant_fsn` AS
+(SELECT `r`.`subtypeId` `id`,`d`.`term` `term`,`r`.`supertypeId` `conceptId`
+	FROM  `snap_transclose` `r`
+	JOIN `snap_fsn` `d` ON (`r`.`subtypeId` = `d`.`conceptId`));
+
+DROP VIEW IF EXISTS `snap_tc_descendant_pref`;
+
+CREATE VIEW `snap_tc_descendant_pref` AS
+(SELECT `r`.`subtypeId` `id`,`d`.`term` `term`,`r`.`supertypeId` `conceptId`
+	FROM  `snap_transclose` `r`
+	JOIN `snap_pref` `d` ON (`r`.`subtypeId` = `d`.`conceptId`));
+
+DROP VIEW IF EXISTS `snap_tc_ancestor_fsn`;
+
+CREATE VIEW `snap_tc_ancestor_fsn` AS
+(SELECT `r`.`supertypeId` `id`,`d`.`term` `term`,`r`.`subtypeId` `conceptId`
+	FROM  `snap_transclose` `r`
+	JOIN `snap_fsn` `d` ON (`r`.`supertypeId` = `d`.`conceptId`));
+
+DROP VIEW IF EXISTS `snap_tc_ancestor_pref`;
+
+CREATE VIEW `snap_tc_ancestor_pref` AS
+(SELECT `r`.`supertypeId` `id`,`d`.`term` `term`,`r`.`subtypeId` `conceptId`
+	FROM  `snap_transclose` `r`
+JOIN `snap_pref` `d` ON (`r`.`supertypeId` = `d`.`conceptId`));
+
+-- PROXIMAL PRIMITIVE VIEWS
+
+DROP VIEW IF EXISTS `snap_pp_child_fsn`;
+
+CREATE VIEW `snap_pp_child_fsn` AS
+(SELECT `r`.`subtypeId` `id`,`d`.`term` `term`,`r`.`supertypeId` `conceptId`
+	FROM  `snap_proximal_primitives` `r`
+	JOIN `snap_fsn` `d` ON (`r`.`subtypeId` = `d`.`conceptId`));
+
+DROP VIEW IF EXISTS `snap_pp_child_pref`;
+
+CREATE VIEW `snap_pp_child_pref` AS
+(SELECT `r`.`subtypeId` `id`,`d`.`term` `term`,`r`.`supertypeId` `conceptId`
+	FROM  `snap_proximal_primitives` `r`
+	JOIN `snap_pref` `d` ON (`r`.`subtypeId` = `d`.`conceptId`));
+
+DROP VIEW IF EXISTS `snap_pp_parent_fsn`;
+
+CREATE VIEW `snap_pp_parent_fsn` AS
+(SELECT `r`.`supertypeId` `id`,`d`.`term` `term`,`r`.`subtypeId` `conceptId`
+	FROM  `snap_proximal_primitives` `r`
+	JOIN `snap_fsn` `d` ON (`r`.`supertypeId` = `d`.`conceptId`));
+
+DROP VIEW IF EXISTS `snap_pp_parent_pref`;
+
+CREATE VIEW `snap_pp_parent_pref` AS
+(SELECT `r`.`supertypeId` `id`,`d`.`term` `term`,`r`.`subtypeId` `conceptId`
+	FROM  `snap_proximal_primitives` `r`
+JOIN `snap_pref` `d` ON (`r`.`supertypeId` = `d`.`conceptId`));
+
+
+
+
+
+
 
 -- fiTC
 -- Previous line replace by end block comment if no Transitive Closure

DROP TABLE IF EXISTS `snap_supertypes_prim_nonprox`;

CREATE TEMPORARY TABLE `snap_supertypes_defprim` (
  `subtypeId` bigint(20) NOT NULL DEFAULT '0',
  `supertypeId` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`subtypeId`,`supertypeId`),
  KEY `p_rev` (`supertypeId`,`subtypeId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

CREATE TEMPORARY TABLE `snap_supertypes_primprim` (
  `subtypeId` bigint(20) NOT NULL DEFAULT '0',
  `supertypeId` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`subtypeId`,`supertypeId`),
  KEY `pp_rev` (`supertypeId`,`subtypeId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

CREATE TEMPORARY TABLE `snap_supertypes_prim_nonprox` (
  `subtypeId` bigint(20) NOT NULL DEFAULT '0',
  `supertypeId` bigint(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`subtypeId`,`supertypeId`),
  KEY `p_rev` (`supertypeId`,`subtypeId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

DECLARE `v_defaultLanguage` text;
SET `v_defaultLanguage`=(SELECT `prefix` FROM `config_language` `cl` JOIN `config_settings` `cs` ON `cl`.`id`=`cs`.`languageId` WHERE `cs`.`id`=2);
DROP TABLE IF EXISTS `tmp_concept_terms`;
CREATE TEMPORARY TABLE `tmp_concept_terms` (`conceptId` bigint,`type_and_lang` text,`term` text);
CALL setLanguage(2,`p_langCodeA`);
INSERT INTO `tmp_concept_terms` (`conceptId`,`type_and_lang`,`term`)
SELECT `conceptid`,CONCAT('FSN',' ',`p_langCodeA`) `type and lang`,`term` FROM `snap2_fsn` 
