<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.cloudhandson.vpdbackoffice.mapper.SchemaMetadataMapper">
  <select id="findTableComment" resultType="string">
    SELECT comments
    FROM all_tab_comments
    WHERE owner = #{owner,jdbcType=VARCHAR}
      AND table_name = #{tableName,jdbcType=VARCHAR}
  </select>

  <select id="findColumns"
          resultType="com.cloudhandson.vpdbackoffice.domain.schemametadata.SchemaMetadataColumnRow">
    SELECT c.column_name AS column_name,
           CASE
             WHEN c.data_type IN ('VARCHAR2', 'CHAR', 'NVARCHAR2', 'NCHAR')
               THEN c.data_type || '(' || c.char_length || ')'
             WHEN c.data_type = 'NUMBER' AND c.data_precision IS NOT NULL AND c.data_scale IS NOT NULL
               THEN c.data_type || '(' || c.data_precision || ',' || c.data_scale || ')'
             WHEN c.data_type = 'NUMBER' AND c.data_precision IS NOT NULL
               THEN c.data_type || '(' || c.data_precision || ')'
             ELSE c.data_type
           END AS data_type,
           c.nullable,
           cc.comments
    FROM all_tab_columns c
    LEFT JOIN all_col_comments cc
      ON cc.owner = c.owner
     AND cc.table_name = c.table_name
     AND cc.column_name = c.column_name
    WHERE c.owner = #{owner,jdbcType=VARCHAR}
      AND c.table_name = #{tableName,jdbcType=VARCHAR}
    ORDER BY c.column_id
  </select>

  <select id="findAnnotations"
          resultType="com.cloudhandson.vpdbackoffice.domain.schemametadata.SchemaMetadataAnnotationRow">
    SELECT column_name,
           annotation_name,
           annotation_value
    FROM all_annotations_usage
    WHERE object_name = #{tableName,jdbcType=VARCHAR}
      AND object_type = 'TABLE'
    ORDER BY column_name NULLS FIRST, annotation_name, annotation_value
  </select>

  <select id="countColumn" resultType="int">
    SELECT COUNT(*)
    FROM all_tab_columns
    WHERE owner = #{owner,jdbcType=VARCHAR}
      AND table_name = #{tableName,jdbcType=VARCHAR}
      AND column_name = #{columnName,jdbcType=VARCHAR}
  </select>

  <select id="countTableAnnotation" resultType="int">
    SELECT COUNT(*)
    FROM all_annotations_usage
    WHERE object_name = #{tableName,jdbcType=VARCHAR}
      AND object_type = 'TABLE'
      AND annotation_name = #{annotationName,jdbcType=VARCHAR}
      AND column_name IS NULL
  </select>

  <select id="countColumnAnnotation" resultType="int">
    SELECT COUNT(*)
    FROM all_annotations_usage
    WHERE object_name = #{tableName,jdbcType=VARCHAR}
      AND object_type = 'TABLE'
      AND annotation_name = #{annotationName,jdbcType=VARCHAR}
      AND column_name = #{columnName,jdbcType=VARCHAR}
  </select>

  <!-- Identifiers are server-side validated names. Oracle DDL comments require a literal. -->
  <update id="updateTableComment">
    COMMENT ON TABLE "${owner}"."${tableName}" IS ${commentLiteral}
  </update>

  <update id="updateColumnComment">
    COMMENT ON COLUMN "${owner}"."${tableName}"."${columnName}" IS ${commentLiteral}
  </update>

  <update id="dropTableAnnotation">
    ALTER TABLE "${owner}"."${tableName}" ANNOTATIONS (DROP "${annotationName}")
  </update>

  <update id="addTableAnnotation">
    ALTER TABLE "${owner}"."${tableName}"
      ANNOTATIONS (ADD "${annotationName}" ${annotationValueLiteral})
  </update>

  <update id="dropColumnAnnotation">
    ALTER TABLE "${owner}"."${tableName}" MODIFY "${columnName}"
      ANNOTATIONS (DROP "${annotationName}")
  </update>

  <update id="addColumnAnnotation">
    ALTER TABLE "${owner}"."${tableName}" MODIFY "${columnName}"
      ANNOTATIONS (ADD "${annotationName}" ${annotationValueLiteral})
  </update>
</mapper>
