<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="2.0" 
	xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
	xmlns:xs="http://www.w3.org/2001/XMLSchema" 
	xmlns:fn="http://www.w3.org/2004/07/xpath-functions" 
	xmlns:xdt="http://www.w3.org/2004/07/xpath-datatypes" 
	xmlns:p="http://protege.stanford.edu/xml">
	
	<xsl:import href="model_utils.xslt"/>
	
	<xsl:output method="text" encoding="UTF-8" />
	
	<xsl:key match="/p:knowledge_base/p:simple_instance" use="p:name" name="instances"/>
	<xsl:key match="/p:knowledge_base/p:slot" use="p:name" name="slots"/>
	<xsl:key match="/p:knowledge_base/p:class" use="p:name" name="classes"/>
	
	<xsl:variable name="KB" select="/p:knowledge_base"/>
	
	<xsl:variable name="space" select="' '"/>
	<xsl:template match="text()|@*"/>
	
	<xsl:function name="p:include-class-in-reporting-database">
		<xsl:param name="class"/>
		<xsl:sequence select="$class[p:superclass = 'Include_in_Reporting_Database']"/>
	</xsl:function>
	
	<xsl:template match="/">
	<!--  
	<xsl:text>
	/*
		 The following classes are excluded from this database by not extending the Include_in_reporting_Database mixin
	</xsl:text>
	<xsl:for-each select="//p:class[not(p:superclass = 'Include_in_Reporting_Database')]">
	<xsl:value-of select="p:name"/><xsl:text>
	</xsl:text>
	</xsl:for-each>
	<xsl:text>
	*/
		</xsl:text> -->
		<!-- do entity tables -->
		<xsl:text>
-- entity tables

</xsl:text>
		<xsl:apply-templates select="p:knowledge_base/p:class[p:own_slot_value[p:slot_reference = ':ROLE']/p:value != 'Abstract']" mode="schema">
			<xsl:sort select="p:name"/>
		</xsl:apply-templates>
		
		<!-- do join tables -->
		<xsl:text>
-- join tables
		
</xsl:text>
		<xsl:apply-templates select="p:knowledge_base/p:slot" mode="schema"/>
		
		<!-- now do the INSERT statements for the instances -->
		<xsl:apply-templates select="/p:knowledge_base/p:class" mode="insert"/>
		<xsl:apply-templates select="p:knowledge_base/p:slot" mode="insert"/>
	</xsl:template>
	
	<xsl:template match="p:class" mode="schema">
		<xsl:if test="p:include-class-in-reporting-database(.)">
		<xsl:variable name="slots">
			<slots>
				<xsl:call-template name="collectslots">
					<xsl:with-param name="class" select="."/>
				</xsl:call-template>
			</slots>
		</xsl:variable>	
		<xsl:text>
DROP TABLE IF EXISTS </xsl:text><xsl:value-of select="translate(p:name,'#:- ','____')"/> <xsl:text> ;
CREATE TABLE </xsl:text><xsl:value-of select="translate(p:name,'#:- ','____')"/> <xsl:text> (
	</xsl:text>
	<xsl:value-of select="translate(p:name,'#:- ','____')"/><xsl:text>_ID TEXT</xsl:text>
		<xsl:for-each-group select="$slots//p:slot" group-by="p:name">
			<xsl:sort select="current-grouping-key()"/>
			<xsl:call-template name="calc_column_def">
				<xsl:with-param name="slot" select="."/>
			</xsl:call-template>
		</xsl:for-each-group>
		<xsl:text>
);
</xsl:text>		
		</xsl:if>
	</xsl:template>

	<!-- determine whether a multivalued slot needs to be represented by a table in the reporting database -->
	<xsl:function name="p:include-slot-in-reporting-database">
		<xsl:param name="slot"/>
		<xsl:variable name="max" select="p:slotvalue($slot,':SLOT-MAXIMUM-CARDINALITY')"/>
		<!-- only multivalued slots pre-qualify -->
		<xsl:if test="empty($max)">
			<!-- some *concrete* class included in the reporting database must use the slot (directly or inherited) -->
			<xsl:variable name="classes" select="$KB/p:class[p:template_slot = $slot/p:name]"/>
			<xsl:if test="p:include-class-or-subclass-in-reporting-database($classes)">
				<xsl:sequence select="$slot"/>
			</xsl:if>
		</xsl:if>
	</xsl:function>

	<xsl:function name="p:include-class-or-subclass-in-reporting-database">
		<xsl:param name="classes"/>
		<xsl:choose>
			<!-- we're satisfied if the class itself is in the reporting database -->
			<xsl:when test="p:include-class-in-reporting-database($classes)">
				<xsl:sequence select="$classes"/>
			</xsl:when>
			<!-- otherwise we have to find out if any of their (indirect) subclasses is in the reporting database -->
			<xsl:otherwise>
				<xsl:if test="$classes">
					<xsl:variable name="subs" select="$KB/p:class[some $c in $classes satisfies $c/p:name = p:superclass]"/>
					<xsl:sequence select="p:include-class-or-subclass-in-reporting-database($subs)"/>
				</xsl:if>
			</xsl:otherwise>
		</xsl:choose>
	</xsl:function>
	
	<!-- here I determine whether we need a join table or not -->
	<xsl:template match="p:slot" mode="schema">
			<xsl:variable name="name" select="translate(p:name,'#:- ','____')"/>
			<xsl:variable name="class" select="p:slotvalue(., ':SLOT-VALUE-TYPE')[@value_type = 'class']"/>
			<xsl:variable name="min" select="p:slotvalue(., ':SLOT-MINIMUM-CARDINALITY')"/>
			<xsl:variable name="max" select="p:slotvalue(.,':SLOT-MAXIMUM-CARDINALITY')"/>
			<xsl:variable name="inverse" select="p:slotvalue(., ':SLOT-INVERSE')"/>
			<xsl:variable name="rmax" select="p:slotvalue(key('slots', $inverse),':SLOT-MAXIMUM-CARDINALITY')"/>
			<!-- has to be multivalued and actually used by some class which is included in the database -->
			<xsl:if test="p:include-slot-in-reporting-database(.)">
			<xsl:text>
	DROP TABLE IF EXISTS </xsl:text><xsl:value-of select="$name"/><xsl:text> ;
	CREATE TABLE </xsl:text><xsl:value-of select="$name"/><xsl:text> (
	</xsl:text>
				<xsl:choose>
					<xsl:when test="$class and $inverse">
						<xsl:value-of select="$name"/><xsl:text>_ID TEXT, </xsl:text><xsl:value-of select="translate($inverse, '#:- ','____')"/><xsl:text>_ID TEXT</xsl:text>
					</xsl:when>
					<!-- otherwise a multivalued primitive or no inverse relationship defined -->
					<xsl:otherwise>
						<xsl:value-of select="$name"/><xsl:text>_ID TEXT, VALUE TEXT</xsl:text>
					</xsl:otherwise>
				</xsl:choose>
<xsl:text>);

</xsl:text>
			</xsl:if>
	</xsl:template>


	<!-- 
		(maybe) add a column definition based on the slot specification.
		What happens here depends on the cardinality of this slot and its inverse slot
	-->
	<xsl:template name="calc_column_def">
		<xsl:param name="slot" required="yes"/>
		<xsl:if test="$slot">
			<xsl:variable name="name" select="translate($slot/p:name,'#:- ','____')"/>
			<xsl:variable name="max" select="p:slotvalue($slot,':SLOT-MAXIMUM-CARDINALITY')"/>
			
			<!-- undefined max means many, this table won't have this slot as a column -->
			<xsl:if test="$max">
						<xsl:call-template name="emit_column_def">
							<xsl:with-param name="name" select="$name"/>
						</xsl:call-template>
			</xsl:if>
		</xsl:if>	
	</xsl:template>

	<xsl:template name="emit_column_def">
		<xsl:param name="name" required="yes"/>
		<xsl:text>,
	</xsl:text>
		<xsl:value-of select="$name"/><xsl:text>  TEXT</xsl:text>
	</xsl:template>

<xsl:template match="p:class" mode="insert">
	<xsl:if test="p:include-class-in-reporting-database(.)">
		<xsl:variable name="slots">
			<slots>
				<xsl:call-template name="collectslots">
					<xsl:with-param name="class" select="."/>
					<xsl:with-param name="one" tunnel="yes">1</xsl:with-param>
				</xsl:call-template>
			</slots>
		</xsl:variable>	
		<xsl:variable name="table" select="translate(p:name, '#:- ','____')"/>
		<xsl:apply-templates select="/p:knowledge_base/p:simple_instance[p:type = current()/p:name]">
			<xsl:with-param name="table" select="$table"/>
			<xsl:with-param name="slots" select="$slots"/>
		</xsl:apply-templates>	
	</xsl:if>
</xsl:template>

<xsl:template match="p:simple_instance">
	<xsl:param name="table"/>
	<xsl:param name="slots"/>
	<xsl:variable name="oid_col" select="concat($table,'_ID')"/>
	<xsl:variable name="oid_val">'<xsl:value-of select="p:name"/>'</xsl:variable>
	<xsl:variable name="colnames" select="p:concat-names($slots//p:name,'')"/>
	<xsl:variable name="values" select="p:collect-values(., distinct-values($slots//p:name), $oid_val)"/>
	<xsl:text>INSERT INTO </xsl:text><xsl:value-of select="$table"/><xsl:text>
	(</xsl:text><xsl:value-of select="if ($slots//p:name) then concat($oid_col, ', ', translate($colnames,'#:-','___')) else $oid_col"/><xsl:text>) 
	VALUES (</xsl:text><xsl:value-of select="$values"/><xsl:text>);
</xsl:text>
</xsl:template>

<xsl:function name="p:concat-names">
	<xsl:param name="list"/>
	<xsl:param name="result"/>
	<xsl:choose>
		<xsl:when test="$list">
			<xsl:variable name="first" select="translate($list[1], '#:- ','____')"/>
			<xsl:choose>
				<xsl:when test="contains($result, concat(', ',$first,', '))"><xsl:value-of select="$result"/></xsl:when>
				<xsl:otherwise>
					<xsl:value-of select="p:concat-names($list[position() != 1], 
						if ($result) then concat($result, ', ', $first) else $first)"/>
				</xsl:otherwise>
			</xsl:choose>
		</xsl:when>
		<xsl:otherwise><xsl:value-of select="$result"/></xsl:otherwise>
	</xsl:choose>
</xsl:function>

<xsl:function name="p:collect-values">
	<xsl:param name="instance"/>
	<xsl:param name="slots"/>
	<xsl:param name="result"/>
	<xsl:choose>
		<xsl:when test="not(empty($slots))">
			<xsl:variable name="val" select="p:slotvalue($instance, $slots[1])"/>
			<xsl:variable name="insert"><xsl:value-of select="if ($val) then concat('''',p:escape($val),'''') else 
			'NULL'"/></xsl:variable>
			<xsl:value-of select="p:collect-values($instance, $slots[position() != 1], concat($result, ', ', $insert))"/>
		</xsl:when>
		<xsl:otherwise><xsl:value-of select="$result"/></xsl:otherwise>
	</xsl:choose>
</xsl:function>

<xsl:template match="p:slot" mode="insert">
			<xsl:variable name="name" select="p:name"/>
			<xsl:variable name="table" select="translate($name,'#:- ','____')"/>
			<xsl:variable name="class" select="p:slotvalue(., ':SLOT-VALUE-TYPE')[@value_type = 'class']"/>
			<xsl:variable name="min" select="p:slotvalue(., ':SLOT-MINIMUM-CARDINALITY')"/>
			<xsl:variable name="max" select="p:slotvalue(.,':SLOT-MAXIMUM-CARDINALITY')"/>
			<xsl:variable name="inverse" select="p:slotvalue(., ':SLOT-INVERSE')"/>
			<xsl:variable name="rmax" select="p:slotvalue(key('slots', $inverse),':SLOT-MAXIMUM-CARDINALITY')"/>
			<!-- has to be multivalued and actually used by some class -->
			<xsl:if test="p:include-slot-in-reporting-database(.)">
				<xsl:for-each 
					select="/p:knowledge_base/p:simple_instance[p:own_slot_value[p:slot_reference = $name]]">
					<xsl:variable name="me" select="p:name"/>
					<xsl:choose>
						<!-- one-many to String -->
						<xsl:when test="empty($class)">
							<xsl:for-each select="p:own_slot_value[p:slot_reference = $name]/p:value">
								<xsl:text>INSERT INTO </xsl:text><xsl:value-of select="$table"/><xsl:text> (</xsl:text><xsl:value-of select="$table"/><xsl:text>_ID, VALUE)
	VALUES ('</xsl:text><xsl:value-of select="$me"/><xsl:text>', '</xsl:text><xsl:value-of select="p:escape(.)"/><xsl:text>');
</xsl:text>
							</xsl:for-each>						
						</xsl:when>
						<!-- many-many entity -->
						<xsl:when test="$class and $inverse">
							<xsl:for-each select="p:own_slot_value[p:slot_reference = $name]/p:value">
								<xsl:text>INSERT INTO </xsl:text><xsl:value-of select="$table"/><xsl:text> (</xsl:text><xsl:value-of select="$table"/><xsl:text>_ID, </xsl:text><xsl:value-of select="translate($inverse,'#:- ','____')"/><xsl:text>_ID)
	VALUES ('</xsl:text><xsl:value-of select="."/><xsl:text>', '</xsl:text><xsl:value-of select="$me"/><xsl:text>');
</xsl:text>
							</xsl:for-each>
						</xsl:when>
					</xsl:choose>
					
				</xsl:for-each>
			</xsl:if>
	</xsl:template>
	
	<!-- replace single quotes with two single quotes -->
	<xsl:function name="p:escape">
		<xsl:param name="value"/>
		<xsl:value-of select="replace($value[1], '''', '''''')"/>
	</xsl:function>
</xsl:stylesheet>
