package com.vci.ubcs.ddl.mapper;

import com.vci.ubcs.ddl.bo.DdlTableBO;
import com.vci.ubcs.ddl.bo.DdlTableInDataBaseBO;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.type.JdbcType;

import java.util.List;

/**
 * Description: 数据库操作
 *
 * @author LiHang
 * @date 2023/4/24
 */
public interface DdlMapper {
	/**
	 * 统计表格中有多少数据
	 * @param tableName 表格名称
	 * @return 数据的总数
	 */
	@Select( "select count(*) from ${tableName}")
	@ResultType(Integer.class)
	int countAll(@Param("tableName")String tableName);

	/**
	 * 根据SQL语句创建视图
	 * @param viewCreateSql
	 * @return 影响行数
	 */
	@Update("${viewCreateSql}")
	@ResultType(Integer.class)
	int createViewBySql(@Param("viewCreateSql") String viewCreateSql);

	/**
	 * 根据sql语句插入数据
	 * @param insertSql 要执行的sql语句
	 * @return 影响的行数
	 */
	@Insert("${insertSql}")
	@ResultType(Integer.class)
	int insertBySql(@Param("insertSql")String insertSql);

	/**
	 * 创建数据库表
	 * @param tableName 表格的名称
	 * @param attributeSql 属性的sql
	 * @return 影响的行数
	 */
	@Update("create table ${tableName} ( ${attributeSql} )")
	@ResultType(Integer.class)
	int createTableBySql(@Param("tableName") String tableName, @Param("attributeSql") String attributeSql);

	/**
	 * 为表格添加注释内容
	 * @param tableName 表格的名称
	 * @param comment 注释的内容
	 * @return 受影响的行数
	 */
	@Update("COMMENT ON TABLE ${tableName}  IS '${comment}' ")
	@ResultType(Integer.class)
	int commentTable(@Param("tableName") String tableName, @Param("comment") String comment);

	/**
	 * 为表格的列添加注释内容
	 * @param tableName 表格的名称
	 * @param columnName 列
	 * @param comment 注释的内容
	 * @return 受影响的行数
	 */
	@Update("COMMENT ON column ${tableName}.${columnName} IS '${comment}' ")
	@ResultType(Integer.class)
	int commentColumnTable(@Param("tableName") String tableName,@Param("columnName")String columnName, @Param("comment") String comment);

	/**
	 * 校验数据库表或者视图是否存在
	 * @param tableName 表格的名称，不区分大小写
	 * @return 存在的个数
	 */
	@Select( "select count(table_name) from user_tables where upper(table_name) = upper(#{tableName,jdbcType=VARCHAR})")
	@ResultType(Integer.class)
	int checkTableExist(@Param("tableName") String tableName);

	/**
	 * 查询表格的字段信息
	 * @param tableName 表格的名称
	 * @return 数据库表的字段信息
	 */
	@Results(id="ddlTableColumnInfo",value = {
		@Result(property = "id",column = "COLUMN_NAME",jdbcType = JdbcType.VARCHAR),
		@Result(property = "name",column = "COMMENTS",jdbcType = JdbcType.VARCHAR),
		@Result(property = "attrDataType",column = "DATA_TYPE",jdbcType = JdbcType.VARCHAR),
		@Result(property = "attributeLength",column = "DATA_LENGTH",jdbcType = JdbcType.DECIMAL),
		@Result(property = "nullableFlag",column = "NULLABLE",jdbcType = JdbcType.VARCHAR),
		@Result(property = "precisionLength",column = "DATA_PRECISION",jdbcType = JdbcType.DECIMAL),
		@Result(property = "scaleLength",column = "DATA_SCALE",jdbcType = JdbcType.DECIMAL)
	})
	@Select("SELECT t.column_name,t.data_type,t.data_length,t.nullable,t.data_precision,t.data_scale,c.comments FROM user_tab_columns t inner JOIN user_col_comments c on t.TABLE_NAME  = c.table_name and t.COLUMN_NAME = c.column_name where t.table_name = upper(#{tableName,jdbcType=VARCHAR}) order by t.column_name asc")
	List<DdlTableInDataBaseBO> selectTableColumnInfo(@Param("tableName") String tableName);

	/**
	 * 获取所有的表格名称
	 * @return 表格名称
	 */
	@Select("select table_name from user_tables order by table_name asc")
	List<String> selectAllTableName();

	/**
	 * 查询表格的中文名称
	 * @param tableName 表格名称
	 * @return 表格名称和表格中文名称
	 */
	@Results(id="ddlTableInfo",value = {
		@Result(property = "tableName",column = "TABLE_NAME",jdbcType = JdbcType.VARCHAR),
		@Result(property = "tableDesc",column = "COMMENTS",jdbcType = JdbcType.VARCHAR)
	})
	@Select("select table_name,comments from user_tab_comments where table_name = upper(#{tableName,jdbcType=VARCHAR}) ")
	DdlTableBO selectTableComment(@Param("tableName")String tableName);

	int addColumn2TableBySql(String tableName, String attributeSql);

	int modifyTableBySql(String tableName, String attributeSql);

	int dropTable(String tableName);

	int dropTableColumn(String tableName, String columnName);
}