<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:variable name="var-database-type" select="/database_configuration/database_type"/>
<xsl:variable name="var-config-category" select="/database_configuration/category"/>
<xsl:variable name="var-table-postfix" select="/database_configuration/table_postfix"/>

<!-- Default templates -->
<xsl:template match="*">
  <xsl:copy>
    <xsl:copy-of select="@*"/>
    <xsl:apply-templates/>
  </xsl:copy>
</xsl:template>

<!-- Custom templates -->

<!-- translate <schema_definition> node to the actual content -->
<xsl:template match="/database_configuration/database_installation/schema_definition">
  <xsl:variable name="var-schema-definition" select="document(@src)"/>
  <xsl:call-template name="install">
    <xsl:with-param name="param-schema" select="$var-schema-definition/database/schema"/>
  </xsl:call-template>
</xsl:template>

<!-- simple upgrade schema implementation -->
<xsl:template match="/database_configuration/database_upgrade/upgrade_schema">
  <xsl:variable name="var-schema-definition" select="document(@src)"/>
  <xsl:for-each select="$var-schema-definition/database/schema/table/field[./@introduced-version = ../../@version]">
    <sql>
        ALTER TABLE [<xsl:value-of select="concat(../@name,$var-table-postfix)"/>]
        ADD <xsl:apply-templates select="."/>
    </sql>
  </xsl:for-each>
  <xsl:for-each select="$var-schema-definition/database/schema/table[./@introduced-version = ../@version]">
    <sql>
      <xsl:apply-templates select="."/>
    </sql>
    <xsl:for-each select="./instance">
    <sql>
      <xsl:call-template name="insert-instance">
        <xsl:with-param name="param-table" select=".."/>
        <xsl:with-param name="param-row-instance" select="."/>
      </xsl:call-template>
    </sql>
    </xsl:for-each>
  </xsl:for-each>
</xsl:template>


<!-- translate <database_upgrade><upgrade_name> node to the actual content -->
<xsl:template match="/database_configuration/database_upgrade/upgrade_name">
  <xsl:variable name="var-schema-definition" select="document(@src)"/>
  <xsl:call-template name="upgrade_name">
    <xsl:with-param name="param-schema" select="$var-schema-definition/database/schema"/>
  </xsl:call-template>
</xsl:template>

<!-- translate <simple_commands><name_conversion>  -->
<xsl:template match="/database_configuration/simple_commands/name_conversion">
  <xsl:variable name="var-schema-definition" select="document(@src)"/>
  <xsl:call-template name="conversion_name">
    <xsl:with-param name="param-schema" select="$var-schema-definition/database/schema"/>
  </xsl:call-template>
</xsl:template>

<!--// Template: Generate conversion name -->
<xsl:template name="conversion_name">
  <xsl:param name="param-schema"/>
  <!--conversion table name-->
  <xsl:for-each select="$param-schema/table">
    <command name="{./@name}">
      [<xsl:value-of select="concat(./@name,$var-table-postfix)"/>]
    </command>
  </xsl:for-each>
  
  <!--conversion view name-->
  <xsl:for-each select="$param-schema/view">
    <command name="{./@name}">
      [<xsl:value-of select="concat(./@name,$var-table-postfix)"/>]
    </command>
  </xsl:for-each>
  
</xsl:template>

<!--// Template: Generate upgrade name Sql scripts -->
<xsl:template name="upgrade_name">
  <xsl:param name="param-schema"/>
  
  <!-- drop constraint first -->
  <xsl:for-each select="$param-schema/table/field[@constraintid]">
  <sql>
      ALTER TABLE <xsl:value-of select="../@name"/> DROP CONSTRAINT <xsl:value-of select="./@constraintid"/>
  </sql>
  </xsl:for-each>
  
  <!--rename table -->
  <xsl:for-each select="$param-schema/table[not(@introduced-version)]">
    <sql>
	  <xsl:variable name="old_table_name" select="./@name"/>
      <xsl:variable name="new_table_name" select="concat(./@name,$var-table-postfix)"/>
	  SP_RENAME <xsl:value-of select="$old_table_name"/>,<xsl:value-of select="$new_table_name"/>
    </sql>
  </xsl:for-each>
  
  <!--create constraint-->
  <xsl:for-each select="$param-schema/table/field[@constraint]">
  <sql>
      ALTER TABLE <xsl:value-of select="concat(../@name,$var-table-postfix)"/> ADD CONSTRAINT <xsl:value-of select="./@constraint"/>
  </sql>
  </xsl:for-each>
  
  <!-- rename index -->
  <xsl:for-each select="$param-schema/table/field[@index]">
    <xsl:variable name="old_index_name">
    <xsl:text>N'[</xsl:text><xsl:value-of select="concat(../@name,$var-table-postfix)"/><xsl:text>]</xsl:text>
    <xsl:text>.[idx</xsl:text><xsl:value-of select="./@name"/><xsl:text>]'</xsl:text>
    </xsl:variable>
    <xsl:variable name="new_index_name">
    <xsl:text>N'[</xsl:text><xsl:value-of select="concat(../@name,$var-table-postfix)"/><xsl:text>]</xsl:text>
    <xsl:text>.[idx</xsl:text><xsl:value-of select="concat(./@name,$var-table-postfix)"/><xsl:text>]'</xsl:text>
    </xsl:variable>
    <sql>
	  SP_RENAME <xsl:value-of select="$old_index_name"/>,<xsl:value-of select="$new_index_name"/>,N'INDEX'
    </sql>
  </xsl:for-each>
  
  <!--recreate view-->
  <xsl:for-each select="$param-schema/view">
    <xsl:call-template name="upgrade-view">
        <xsl:with-param name="param-view" select="."/>
    </xsl:call-template>
  </xsl:for-each>

</xsl:template>


<!-- translate <schema_definition> node to the actual content -->
<xsl:template match="/database_configuration/database_uninstallation/schema_definition">
  <xsl:variable name="var-schema-definition" select="document(@src)"/>
  <xsl:call-template name="uninstall">
    <xsl:with-param name="param-schema" select="$var-schema-definition/database/schema"/>
  </xsl:call-template>
</xsl:template>

<!--// Template: Generate uninstallation Sql scripts -->
<xsl:template name="uninstall">
  <xsl:param name="param-schema"/>
  <xsl:for-each select="$param-schema/table/field[@index]">
  <sql>
    <xsl:call-template name="drop-index">
      <xsl:with-param name="param-table" select=".."/>
      <xsl:with-param name="param-field" select="."/>
    </xsl:call-template>
  </sql>
  </xsl:for-each>

  <xsl:for-each select="$param-schema/view">
    <xsl:sort select="position()" data-type="number" order="descending"/>
    <sql>
      <xsl:call-template name="drop-view">
        <xsl:with-param name="param-view-name" select="concat(./@name,$var-table-postfix)"/>
      </xsl:call-template>
    </sql>
  </xsl:for-each>
  
  <xsl:for-each select="$param-schema/table">
    <xsl:sort select="position()" data-type="number" order="descending"/>
    <sql>
      <xsl:call-template name="drop-table">
        <xsl:with-param name="param-table-name" select="concat(./@name,$var-table-postfix)"/>
      </xsl:call-template>
    </sql>
  </xsl:for-each>
</xsl:template>

<xsl:template name="drop-view">
    <xsl:param name="param-view-name"/>
    DROP VIEW <xsl:value-of select="$param-view-name"/>
</xsl:template>

<xsl:template name="drop-index">
  <xsl:param name="param-table"/>
  <xsl:param name="param-field"/>
  <xsl:text>DROP INDEX [</xsl:text><xsl:value-of select="concat($param-table/@name,$var-table-postfix)"/><xsl:text>]</xsl:text>
  <xsl:text>.[idx</xsl:text><xsl:value-of select="concat($param-field/@name,$var-table-postfix)"/><xsl:text>]</xsl:text>
</xsl:template>


<!--// Template: Generate installation Sql scripts -->
<xsl:template name="install">
    <xsl:param name="param-schema"/>
    <xsl:for-each select="$param-schema/table">
	<xsl:if test="not(@exist_no_drop)">
	<sql>
	    BEGIN TRY
         	IF  EXISTS  (SELECT  * FROM dbo.SysObjects WHERE ID = object_id(N'[<xsl:value-of select="concat(@name,$var-table-postfix)"/>]') AND OBJECTPROPERTY(ID, 'IsTable') = 1)
            	drop table [<xsl:value-of select="concat(@name,$var-table-postfix)"/>]
	    END TRY
	    BEGIN CATCH
	    END CATCH
    </sql>
	</xsl:if>
    <sql>
        <xsl:apply-templates select="."/>
    </sql>
    </xsl:for-each>
  
  <xsl:for-each select="$param-schema/table/instance">
  <sql>
    <xsl:call-template name="insert-instance">
      <xsl:with-param name="param-table" select=".."/>
      <xsl:with-param name="param-row-instance" select="."/>
    </xsl:call-template>
  </sql>
  </xsl:for-each>

  <xsl:for-each select="$param-schema/view">
    <sql>
	  BEGIN TRY
  		IF EXISTS  (SELECT  * FROM dbo.SysObjects WHERE ID = object_id(N'[<xsl:value-of select="concat(@name,$var-table-postfix)"/>]') AND OBJECTPROPERTY(ID, 'IsView') = 1)
		drop view  [<xsl:value-of select="concat(@name,$var-table-postfix)"/>]
	  END TRY
	  BEGIN CATCH
	  END CATCH
	</sql>
    <sql>
        <xsl:apply-templates select="."/>
    </sql>
  </xsl:for-each>

  <xsl:for-each select="$param-schema/table/field[@index]">
  <sql>
    <xsl:call-template name="create-index">
      <xsl:with-param name="param-table" select=".."/>
      <xsl:with-param name="param-field" select="."/>
    </xsl:call-template>
  </sql>
  </xsl:for-each>
</xsl:template>

<xsl:template name="drop-table">
    <xsl:param name="param-table-name"/>
    DROP TABLE <xsl:value-of select="$param-table-name"/>
</xsl:template>


<xsl:template name="upgrade-column">
  <xsl:param name="param-table-name"/>
  <xsl:param name="param-column"/>
  <xsl:param name="param-column-upgrade"/>
  <xsl:choose>
    <xsl:when test="$param-column-upgrade">
      ALTER TABLE [<xsl:value-of select="$param-table-name"/>] 
        ALTER COLUMN <xsl:apply-templates select="$param-column"><xsl:with-param name="param-is-upgrade">true</xsl:with-param></xsl:apply-templates>
    </xsl:when>
    <xsl:otherwise>
      ALTER TABLE [<xsl:value-of select="$param-table-name"/>]
        ADD <xsl:apply-templates select="$param-column"/>
    </xsl:otherwise>
  </xsl:choose>
</xsl:template>


<!--// Template: Upgrade view -->
<xsl:template name="upgrade-view">
    <xsl:param name="param-view"/>
  <sql haltonerror="false">
    DROP VIEW [<xsl:value-of select="$param-view/@name"/>]
    </sql>
    <sql>
    <xsl:apply-templates select="$param-view"/>
    </sql>
</xsl:template>


<!--// Template: Create Table -->
<xsl:template match="/database/schema/table">
  <xsl:variable name="var-table-name" select="concat(@name,$var-table-postfix)"/>
    <xsl:if test="@ignore_exist_error = 'true'">
         BEGIN TRY 
    </xsl:if> 
    <xsl:text>IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = '</xsl:text><xsl:value-of select="$var-table-name"/>
    <xsl:text>')  CREATE TABLE [</xsl:text>
    <xsl:value-of select="$var-table-name"/>] 
    (
        <xsl:for-each select="field">
        <xsl:apply-templates select="."/> 
        <xsl:if test="position() != last()">,
        </xsl:if>
        </xsl:for-each>
    )
	<xsl:if test="@ignore_exist_error = 'true'">
        END TRY
        BEGIN CATCH
		END CATCH
    </xsl:if> 
</xsl:template>

<xsl:template name="insert-instance">
    <xsl:param name="param-table"/>
    <xsl:param name="param-row-instance"/>
	<xsl:variable name="var-table-name" select="concat($param-table/@name,$var-table-postfix)"/>
    <xsl:if test="$param-row-instance/@__drop_if_exists_key">
        <xsl:variable name="var-drop-key" select="@__drop_if_exists_key"/>
        IF (SELECT COUNT(*) FROM [<xsl:value-of select="$var-table-name"/>] 
            WHERE [<xsl:value-of select="$var-drop-key"/>] = 
                <xsl:call-template name="sql-value">
                    <xsl:with-param name="param-value" select="$param-row-instance/@*[name()=$var-drop-key]"/>
                    <xsl:with-param name="param-type" select="$param-table/field[@*[$var-drop-key]]/@type"/>
                </xsl:call-template>
            ) = 0
    </xsl:if> 
  <xsl:text>INSERT INTO [</xsl:text><xsl:value-of select="$var-table-name"/><xsl:text>]</xsl:text>
  (
    <xsl:for-each select="$param-row-instance/@*[name() != '__drop_if_exists_key']">
    <xsl:value-of select="name(.)"/>
    <xsl:if test="position() != last()">,</xsl:if>
    </xsl:for-each>
  )
  VALUES
  (
    <xsl:for-each select="$param-row-instance/@*[name() != '__drop_if_exists_key']">
    <xsl:variable name="var-type" select="$param-table/field[@name=name(current())]/@type"/>
    <xsl:call-template name="sql-value">
      <xsl:with-param name="param-type" select="$var-type"/>
      <xsl:with-param name="param-value" select="."/>
    </xsl:call-template><xsl:if test="position() != last()">,</xsl:if>
    </xsl:for-each>
  )
</xsl:template>

<xsl:template match="/database/schema/table/field">
  <xsl:param name="param-is-upgrade"/>

  <xsl:text>[</xsl:text><xsl:value-of select="@name"/><xsl:text>]</xsl:text>

    <xsl:text> </xsl:text>
    <xsl:call-template name="data-type">
        <xsl:with-param name="param-type" select="@type"/>
        <xsl:with-param name="param-size" select="@size"/>
    </xsl:call-template>

    <xsl:text> </xsl:text>
    <xsl:call-template name="nullity">
        <xsl:with-param name="param-allow-null" select="@allow-null"/>
    </xsl:call-template>

    <xsl:text> </xsl:text>
    <xsl:call-template name="primary-key">
        <xsl:with-param name="param-primary-key" select="@primary-key"/>
    </xsl:call-template>

    <xsl:text> </xsl:text>
    <xsl:call-template name="default-value">
    <xsl:with-param name="param-is-upgrade" select="$param-is-upgrade"/>
        <xsl:with-param name="param-type" select="@type"/>
        <xsl:with-param name="param-value" select="@default"/>
    </xsl:call-template>

  <xsl:text> </xsl:text>
  <xsl:if test="@constraint">CONSTRAINT <xsl:value-of select="@constraint"/></xsl:if>
</xsl:template>

<xsl:template name="data-type">
    <xsl:param name="param-type"/>
    <xsl:param name="param-size"/>
  <xsl:variable name="param-database-type" select="$var-database-type"/>
  <xsl:variable name="generic-data-type" select="document('dbSqlDataTypes.xml')/sql_datatypes/*[@name=$param-database-type]"/>
  <xsl:value-of select="$generic-data-type/*[name()=$param-type]"/>
  <xsl:if test="$param-type='text'">(<xsl:value-of select="$param-size"/>)<xsl:if test="$param-database-type='JET'"> WITH COMP</xsl:if></xsl:if>
  <xsl:if test="$param-type='varbinary'">(<xsl:value-of select="$param-size"/>)</xsl:if>
  
  <xsl:text> </xsl:text>
</xsl:template>

<xsl:template name="nullity">
    <xsl:param name="param-allow-null"/>
    <xsl:choose>
        <xsl:when test="$param-allow-null='true'" >NULL</xsl:when>
        <xsl:when test="$param-allow-null='false'">NOT NULL</xsl:when>
    </xsl:choose>
</xsl:template>

<xsl:template name="primary-key">
    <xsl:param name="param-primary-key"/>
    <xsl:if test="$param-primary-key">PRIMARY KEY</xsl:if>
</xsl:template>

<xsl:template name="default-value">
  <xsl:param name="param-is-upgrade"/>
    <xsl:param name="param-type"/>
    <xsl:param name="param-value"/>
    <xsl:if test="$param-value">
        <xsl:text>DEFAULT </xsl:text>
    <xsl:call-template name="sql-value">
      <xsl:with-param name="param-type" select="$param-type"/>
      <xsl:with-param name="param-value" select="$param-value"/>
    </xsl:call-template>
    </xsl:if>
</xsl:template>

<!--// Template: Create View -->
<xsl:template match="/database/schema/view">
    CREATE VIEW [<xsl:value-of select="concat(@name,$var-table-postfix)"/>]
    (
        <xsl:for-each select="column">
        [<xsl:value-of select="@name"/>]<xsl:if test="position()!=last()">,</xsl:if>
        </xsl:for-each>
    )
    AS SELECT<xsl:for-each select="column">
        <xsl:variable name="var-fieldname">
            <xsl:choose>
                <xsl:when test="not(@from-field)"><xsl:value-of select="@name"/></xsl:when>
                <xsl:otherwise><xsl:value-of select="@from-field"/></xsl:otherwise>
            </xsl:choose>
        </xsl:variable>
    <xsl:if test ="@name='suspicious_url'">
            CAST(
        </xsl:if>
        [<xsl:value-of select="concat(@from-table,$var-table-postfix)"/>].[<xsl:value-of select="$var-fieldname"/>]<xsl:if test ="@name='suspicious_url'"> AS nvarchar(4000))</xsl:if><xsl:if test="position()!=last()">,</xsl:if>
    </xsl:for-each>
    FROM <xsl:apply-templates select="from/*"/>
  <xsl:if test="from/@where"> WHERE <xsl:value-of select="from/@where"/></xsl:if>
    
</xsl:template>

<xsl:template match="inner-join|outter-join|left-join|right-join">
    ([<xsl:value-of select="concat(table[1]/@name,$var-table-postfix)"/>] <xsl:choose>
        <xsl:when test="name(current()) = 'inner-join'" >INNER JOIN</xsl:when>
        <xsl:when test="name(current()) = 'outter-join'">OUTTER JOIN</xsl:when>
        <xsl:when test="name(current()) = 'left-join'"  >LEFT JOIN</xsl:when>
        <xsl:when test="name(current()) = 'right-join'" >RIGHT JOIN</xsl:when>
    </xsl:choose> [<xsl:value-of select="concat(table[2]/@name,$var-table-postfix)"/>]
        ON <xsl:value-of select="@on"/>)
</xsl:template>

<xsl:template match="from/table">
  [<xsl:value-of select="concat(@name,$var-table-postfix)"/>]
</xsl:template>
  
<xsl:template name="create-index">
  <xsl:param name="param-table"/>
  <xsl:param name="param-field"/>
  <xsl:text>CREATE INDEX [idx</xsl:text><xsl:value-of select="concat($param-field/@name,$var-table-postfix)"/><xsl:text>] </xsl:text>
  <xsl:text>ON </xsl:text>
  <xsl:text> [</xsl:text><xsl:value-of select="concat($param-table/@name,$var-table-postfix)"/><xsl:text>] </xsl:text>
  <xsl:text> (</xsl:text>
  <xsl:text> [</xsl:text><xsl:value-of select="$param-field/@name"/><xsl:text>] </xsl:text>
  <xsl:text> </xsl:text>
  <xsl:choose>
    <xsl:when test="$param-field/@index='asc'"><xsl:text>ASC</xsl:text></xsl:when>
    <xsl:when test="$param-field/@index='desc'"><xsl:text>DESC</xsl:text></xsl:when>
  </xsl:choose>
  <xsl:text>) </xsl:text>
</xsl:template>

<xsl:template name="sql-value">
  <xsl:param name="param-type"/>
  <xsl:param name="param-value"/>
  <xsl:if test="$param-value">
        <xsl:choose>
            <xsl:when test="$param-type='longtext' or $param-type='text'">
        <xsl:text>'</xsl:text>
          <xsl:call-template name="escape_quot">
          <xsl:with-param name="substr" select="$param-value"/>
        </xsl:call-template><xsl:text>'</xsl:text>
      </xsl:when>
      <xsl:when test="$param-type='boolean'">
        <xsl:choose>
          <xsl:when test="$param-value='0'">0</xsl:when>
          <xsl:otherwise>-1</xsl:otherwise>
        </xsl:choose>
      </xsl:when>
            <xsl:otherwise>
        <xsl:value-of select="$param-value"/>
      </xsl:otherwise>
        </xsl:choose>
    </xsl:if>
</xsl:template>

<xsl:template name="escape_quot">
  <xsl:param name="substr"/>
  <xsl:variable name="var-single-quote">&apos;</xsl:variable>
  <xsl:choose>
    <xsl:when test="contains($substr, $var-single-quote)">
      <xsl:variable name="str_before_quot" select="substring-before($substr, $var-single-quote)"/>
      <xsl:variable name="str_after_quot" select="substring-after($substr, $var-single-quote)"/>
      <xsl:value-of select="$str_before_quot"/>''<xsl:text/>
      <xsl:call-template name="escape_quot">
        <xsl:with-param name="substr" select="$str_after_quot"/>
      </xsl:call-template>
    </xsl:when>
    <xsl:otherwise>
      <xsl:value-of select="$substr"/>
    </xsl:otherwise>
  </xsl:choose>
</xsl:template>

<!-- import DLP template -->

<xsl:template match="/database_configuration/database_installation/import_dlp">
  <xsl:variable name="var-dlp-template" select="document(@src)"/>
  <xsl:call-template name="import_dlp">
    <xsl:with-param name="param-policy" select="$var-dlp-template/SAPolicy"/>
  </xsl:call-template>
</xsl:template>

<xsl:template match="/database_configuration/database_upgrade/import_dlp">
  <xsl:variable name="var-dlp-template" select="document(@src)"/>
  <xsl:call-template name="import_dlp">
    <xsl:with-param name="param-policy" select="$var-dlp-template/SAPolicy"/>
    <xsl:with-param name="skip-template" select="@skip_template"/>
  </xsl:call-template>
</xsl:template>

<xsl:template name="import_dlp">
  <xsl:param name="param-policy"/>
  <xsl:param name="skip-template"/>
  <xsl:for-each select="$param-policy/digitalAssets/dictionary">
    <xsl:variable name="display_name">
      <xsl:call-template name="escape_quot">
          <xsl:with-param name="substr" select="./@display_name"/>
      </xsl:call-template>
    </xsl:variable>
    <xsl:variable name="discription">
      <xsl:call-template name="escape_quot">
          <xsl:with-param name="substr" select="./@description"/>
      </xsl:call-template>
    </xsl:variable>
    <xsl:variable name="threshold">
      <xsl:choose><xsl:when test="./@threshold"><xsl:value-of select="./@threshold"/></xsl:when>
      <xsl:otherwise>0</xsl:otherwise></xsl:choose>
    </xsl:variable>
    <xsl:variable name="number">
      <xsl:choose><xsl:when test="./@number"><xsl:value-of select="./@number"/></xsl:when>
      <xsl:otherwise>0</xsl:otherwise></xsl:choose>
    </xsl:variable>
    <xsl:variable name="builtin">
      <xsl:choose><xsl:when test="./@pre-defined = 'true'">1</xsl:when>
      <xsl:otherwise>0</xsl:otherwise></xsl:choose>
    </xsl:variable>
    <xsl:variable name="keywordlist">
      <xsl:call-template name="escape_quot">
          <xsl:with-param name="substr"><xsl:apply-templates select="." mode="serialize"/></xsl:with-param>
      </xsl:call-template>
    </xsl:variable>
    <sql>
       INSERT INTO TM_DLPFilterKeyword<xsl:value-of select="$var-table-postfix"/> (CFG_INDEX, CFG_DEFAULT_INSTANCE, DLP_KEYWORD_NAME, DLP_KEYWORD_INTERNALNAME, DLP_KEYWORD_DESC, DLP_KEYWORD_CONDITION, DLP_KEYWORD_THRESHOLD, DLP_KEYWORD_NUMBER, DLP_KEYWORD_BUILTIN, DLP_SUBKEYWORD_LIST) VALUES 
            ('SMEX_PLC_NULL:SMEX_SUB_CFG_DLP_KEYWORD<xsl:value-of select="./@name"/>', 
             'SMEX_PLC_NULL:SMEX_SUB_CFG_DLP_KEYWORD_DEFAULT', 
             N'<xsl:value-of select="$display_name"/> ',
             N'<xsl:value-of select="./@name"/>', 
             N'<xsl:value-of select="$discription"/>', 
             '<xsl:value-of select="./@type"/>',
              <xsl:value-of select="$threshold"/> , 
              <xsl:value-of select="$number"/> , 
              <xsl:value-of select="$builtin"/> , 
              N'<xsl:value-of select="$keywordlist"/>')
    </sql>
  </xsl:for-each>

  <xsl:variable name="keywordlist">
      <xsl:for-each select="$param-policy/digitalAssets/dictionary">TM_DLPFilterKeyword:SMEX_SUB_CFG_DLP_KEYWORD<xsl:value-of select="@name"/>:</xsl:for-each>
  </xsl:variable>
  <sql>
      UPDATE TM_DLPFilterKeywordMgr<xsl:value-of select="$var-table-postfix"/> SET CFG_DLP_KEYWORD_LIST_CFGSETTING_MV='<xsl:value-of select="$keywordlist"/>'
  </sql>

  <xsl:for-each select="$param-policy/digitalAssets/entity">
    <xsl:variable name="minlen">
      <xsl:choose><xsl:when test="./@minLength"><xsl:value-of select="./@minLength"/></xsl:when>
      <xsl:otherwise>0</xsl:otherwise></xsl:choose>
    </xsl:variable>
    <xsl:variable name="maxlen">
      <xsl:choose><xsl:when test="./@maxLength"><xsl:value-of select="./@maxLength"/></xsl:when>
      <xsl:otherwise>0</xsl:otherwise></xsl:choose>
    </xsl:variable>
    <xsl:variable name="minleftR">
      <xsl:choose><xsl:when test="./@minLeftRadius"><xsl:value-of select="./@minLeftRadius"/></xsl:when>
      <xsl:otherwise>0</xsl:otherwise></xsl:choose>
    </xsl:variable>
    <xsl:variable name="maxleftR">
      <xsl:choose><xsl:when test="./@maxLeftRadius"><xsl:value-of select="./@maxLeftRadius"/></xsl:when>
      <xsl:otherwise>0</xsl:otherwise></xsl:choose>
    </xsl:variable>
    <xsl:variable name="maxrightR">
      <xsl:choose><xsl:when test="./@maxRightRadius"><xsl:value-of select="./@maxRightRadius"/></xsl:when>
      <xsl:otherwise>0</xsl:otherwise></xsl:choose>
    </xsl:variable>
    <xsl:variable name="suflen">
      <xsl:choose><xsl:when test="./@suffixLength"><xsl:value-of select="./@suffixLength"/></xsl:when>
      <xsl:otherwise>0</xsl:otherwise></xsl:choose>
    </xsl:variable>
    
    <xsl:variable name="alphacharset">
      <xsl:choose><xsl:when test="./alphabetCharset"><xsl:call-template name="escape_quot"><xsl:with-param name="substr" select="./alphabetCharset/text()"/></xsl:call-template></xsl:when>
      <xsl:otherwise></xsl:otherwise></xsl:choose>
    </xsl:variable>
    
    <xsl:variable name="builtin">
      <xsl:choose><xsl:when test="./@pre-defined = 'true'">1</xsl:when>
      <xsl:otherwise>0</xsl:otherwise></xsl:choose>
    </xsl:variable>
    
    <xsl:variable name="casesense">
      <xsl:choose><xsl:when test="./@caseSensitive = 'true'">1</xsl:when>
      <xsl:otherwise>0</xsl:otherwise></xsl:choose>
    </xsl:variable>
    
    <sql>
      INSERT INTO TM_DLPFilterExpression<xsl:value-of select="$var-table-postfix"/> (CFG_INDEX, CFG_DEFAULT_INSTANCE, 
                DLP_EXPRESSION_NAME, DLP_EXPRESSION_INTERNALNAME, DLP_EXPRESSION_DESC, DLP_EXPRESSION_TYPE, 
                DLP_EXPRESSION_MINLENGTH, DLP_EXPRESSION_MAXLENGTH, DLP_EXPRESSION_MINLEFTRADIUS, DLP_EXPRESSION_MAXLEFTRADIUS, 
                DLP_EXPRESSION_MAXRIGHTRADIUS, DLP_EXPRESSION_SUFFIXLENGTH, DLP_EXPRESSION_PATTERN, 
                DLP_EXPRESSION_ALPHABETCHARSET, DLP_EXPRESSION_CASESENSITIVE, DLP_EXPRESSION_DISPLAYFORMAT, 
                DLP_EXPRESSION_EXAMPLE, DLP_EXPRESSION_VALIDATOR, DLP_EXPRESSION_BUILTIN) 
                VALUES ('SMEX_PLC_NULL:SMEX_SUB_CFG_DLP_EXPRESSION<xsl:value-of select="./@name"/>', 
                'SMEX_PLC_NULL:SMEX_SUB_CFG_DLP_EXPRESSION_DEFAULT', 
                N'<xsl:call-template name="escape_quot"><xsl:with-param name="substr" select="./@display_name"/></xsl:call-template>', 
                '<xsl:value-of select="./@name"/>', 
                N'<xsl:call-template name="escape_quot"><xsl:with-param name="substr" select="./@description"/></xsl:call-template>', 
                '<xsl:value-of select="./@type"/>',
                 <xsl:value-of select="$minlen"/>, 
                 <xsl:value-of select="$maxlen"/>, 
                 <xsl:value-of select="$minleftR"/>, 
                 <xsl:value-of select="$maxleftR"/>,
                 <xsl:value-of select="$maxrightR"/>, 
                 <xsl:value-of select="$suflen"/> , 
                 N'<xsl:call-template name="escape_quot"><xsl:with-param name="substr" select="./regularExpression/text()"/></xsl:call-template>', 
                 '<xsl:value-of select="$alphacharset"/>', 
                  <xsl:value-of select="$casesense"/> , 
                 '<xsl:call-template name="escape_quot"><xsl:with-param name="substr" select="./patternForDisplay/text()"/></xsl:call-template>',
                 '<xsl:call-template name="escape_quot"><xsl:with-param name="substr" select="./examples/text()"/></xsl:call-template>', 
                 '<xsl:call-template name="escape_quot"><xsl:with-param name="substr" select="./@validator"/></xsl:call-template>', 
                  <xsl:value-of select="$builtin"/> )
    </sql>
  </xsl:for-each>
  
  <xsl:variable name="exprlist">
      <xsl:for-each select="$param-policy/digitalAssets/entity">TM_DLPFilterExpression:SMEX_SUB_CFG_DLP_EXPRESSION<xsl:value-of select="@name"/>:</xsl:for-each>
  </xsl:variable>
  <sql>
      UPDATE TM_DLPFilterExpressionMgr<xsl:value-of select="$var-table-postfix"/> SET CFG_DLP_EXPRESSION_LIST_CFGSETTING_MV='<xsl:value-of select="$exprlist"/>'
  </sql>
  
  <xsl:if test="not($skip-template)">
    <xsl:for-each select="$param-policy/complianceTemplates/template">
      <xsl:variable name="builtin">
        <xsl:choose><xsl:when test="./@pre-defined = 'true'">1</xsl:when>
        <xsl:otherwise>0</xsl:otherwise></xsl:choose>
      </xsl:variable>
      
      <xsl:variable name="content">
        <xsl:call-template name="escape_quot">
            <xsl:with-param name="substr"><xsl:apply-templates select="." mode="serialize"/></xsl:with-param>
        </xsl:call-template>
      </xsl:variable>
      
      <xsl:variable name="id">
          <xsl:value-of select="./@name"/>
      </xsl:variable>
      
      <xsl:variable name="industry">
        <xsl:for-each select="$param-policy/industries/industry[.//template_id=$id]/@name"><xsl:call-template name="escape_quot"><xsl:with-param name="substr" select="."/></xsl:call-template>;</xsl:for-each>
      </xsl:variable>
      
      
      <xsl:variable name="region">
        <xsl:for-each select="$param-policy/regions/region[.//template_id=$id]/@name"><xsl:call-template name="escape_quot"><xsl:with-param name="substr" select="."/></xsl:call-template>;</xsl:for-each>
      </xsl:variable>
      <sql>
        INSERT INTO TM_DLPFilterTemplate<xsl:value-of select="$var-table-postfix"/> (CFG_INDEX, DLP_TEMPLATE_NAME, DLP_TEMPLATE_BUILTIN,
                        DLP_TEMPLATE_DESCRIPTION, DLP_TEMPLATE_ELEMENT, DLP_TEMPLATE_INDUSTRY, DLP_TEMPLATE_REGION) 
                        VALUES ('SMEX_PLC_NULL:SMEX_SUB_CFG_DLP_TEMPLATE<xsl:value-of select="./@name"/>',
                               N'<xsl:call-template name="escape_quot"><xsl:with-param name="substr" select="./@display_name"/></xsl:call-template>', 
                                <xsl:value-of select="$builtin"/>, 
                               N'<xsl:call-template name="escape_quot"><xsl:with-param name="substr" select="./@description"/></xsl:call-template>', 
                               '<xsl:value-of select="$content"/>', 
                               '<xsl:value-of select="$industry"/>',
                               '<xsl:value-of select="$region"/>')
      </sql>
    </xsl:for-each>
    
    <xsl:variable name="templist">
        <xsl:for-each select="$param-policy/complianceTemplates/template">TM_DLPFilterTemplate:SMEX_SUB_CFG_DLP_TEMPLATE<xsl:value-of select="@name"/>:</xsl:for-each>
    </xsl:variable>
    <sql>
       UPDATE TM_DLPTemplateManagement<xsl:value-of select="$var-table-postfix"/> SET DLP_TEMPLATE_LIST_CFGSETTING_MV='<xsl:value-of select="$templist"/>'
    </sql>
  </xsl:if>
</xsl:template>


<!-- serialize XML nodes -->

<xsl:template match="*" mode="serialize">
    <xsl:text>&lt;</xsl:text>
    <xsl:value-of select="name()"/>
    <xsl:apply-templates select="@*" mode="serialize" />
    <xsl:choose>
        <xsl:when test="node()">
            <xsl:text>&gt;</xsl:text>
            <xsl:apply-templates mode="serialize" />
            <xsl:text>&lt;/</xsl:text>
            <xsl:value-of select="name()"/>
            <xsl:text>&gt;</xsl:text>
        </xsl:when>
        <xsl:otherwise>
            <xsl:text> /&gt;</xsl:text>
        </xsl:otherwise>
    </xsl:choose>
</xsl:template>

<xsl:template match="@*" mode="serialize">
    <xsl:text> </xsl:text>
    <xsl:value-of select="name()"/>
    <xsl:text>="</xsl:text>
    <xsl:call-template name="escape-xml">
        <xsl:with-param name="text" select="."/>
    </xsl:call-template>
    <xsl:text>"</xsl:text>
</xsl:template>

<xsl:template match="text()" mode="serialize">
    <xsl:call-template name="escape-xml">
        <xsl:with-param name="text" select="."/>
    </xsl:call-template>
</xsl:template>

<xsl:template name="escape-xml">
    <xsl:param name="text"/>
    <xsl:if test="$text != ''">
        <xsl:variable name="head" select="substring($text, 1, 1)"/>
        <xsl:variable name="tail" select="substring($text, 2)"/>
        <xsl:choose>
            <xsl:when test="$head = '&amp;'">&amp;amp;</xsl:when>
            <xsl:when test="$head = '&lt;'">&amp;lt;</xsl:when>
            <xsl:when test="$head = '&gt;'">&amp;gt;</xsl:when>
            <xsl:when test="$head = '&quot;'">&amp;quot;</xsl:when>
            <xsl:when test="$head = &quot;&apos;&quot;">&amp;apos;</xsl:when>
            <xsl:otherwise><xsl:value-of select="$head"/></xsl:otherwise>
        </xsl:choose>
        <xsl:call-template name="escape-xml">
            <xsl:with-param name="text" select="$tail"/>
        </xsl:call-template>
    </xsl:if>
</xsl:template>

<!-- serialize XML nodes -->

</xsl:stylesheet> 
