package com.product.server.report.controller;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

import com.product.core.config.ErrorCode;
import com.product.core.connection.ConnectionManager;
import com.product.core.dao.support.Dao;
import com.product.core.entity.DataTableEntity;
import com.product.core.entity.FieldSetEntity;
import com.product.core.exception.BaseException;
import com.mysql.cj.jdbc.DatabaseMetaData;


public class TestDao {
	/**
	 * 	创建表
	 * @param tfs
	 * @return
	 */
	public static boolean createTable(FieldSetEntity tfs) throws BaseException{
		if(tfs == null || tfs.getString("table_name") ==null  || "".equals(tfs.getString("table_name")) ) {
			throw new BaseException(
					ErrorCode.DATA_CREATE_TABLE.getValue()
					,ErrorCode.DATA_CREATE_TABLE.getText()
					,Dao.class
					,"createTable(FieldSetEntity tfs)"
			);
		}
		int table_type=tfs.getInteger("table_type");
		
		PreparedStatement ps = null;
		Connection        con = null;
		try{
			if(table_type==2) {
				//创建视图表
				con =ConnectionManager.getConnection();
				StringBuffer viewsql=new StringBuffer();
				viewsql.append(" create view `").append(tfs.getString("table_name")).append("` as ( ");
				viewsql.append(tfs.getString("create_view_sql")).append(" ); ");//msv_execute_sql   执行的SQL语句
				System.out.println(" DDL:"+viewsql.toString());
				ps  = con.prepareStatement(viewsql.toString()); 
				ps.execute();
				return true;
			}
			con =ConnectionManager.getConnection();
			StringBuffer sql=new StringBuffer();
			sql.append(" CREATE TABLE `").append(tfs.getString("table_name")).append("` ( ");
			sql.append("`id` int(10) NOT NULL AUTO_INCREMENT COMMENT '主键' , ");
			sql.append("`uuid` varchar(64) NOT NULL COMMENT '唯一标识', ");
			sql.append("`created_by` varchar(50) DEFAULT NULL COMMENT '创建者', ");
			sql.append("`created_utc_datetime` datetime DEFAULT NULL COMMENT '创建时间', ");
			sql.append("`updated_by` varchar(50) DEFAULT NULL COMMENT '更新者', ");
			sql.append("`updated_utc_datetime` datetime DEFAULT NULL COMMENT '更新时间', ");
			sql.append("PRIMARY KEY (`id`) USING BTREE, ");
			sql.append("UNIQUE KEY `uuid` (`uuid`) USING BTREE ");
			sql.append(") ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='").append(tfs.getString("table_description")==null?"":tfs.getString("table_description")).append("' ");
			System.out.println(" DDL:"+sql.toString());
			ps  = con.prepareStatement(sql.toString()); 
			ps.execute();
			return true;
		}catch(Exception e){
			throw new BaseException(
					ErrorCode.DATA_CREATE_TABLE.getValue()
					,ErrorCode.DATA_CREATE_TABLE.getText()+e.getMessage()
					,Dao.class
					,"createTable(FieldSetEntity tfs)"
					,e
			);
		}finally{
			//this.closeConnection(con,ps,null);
		}
	}
	/**
	 * 	新增表字段
	 * @param tfs
	 * @return
	 */
	public static boolean createTableField(FieldSetEntity tfs) throws BaseException{
		if(tfs == null || tfs.getString("table_name") ==null  || "".equals(tfs.getString("table_name")) ) {
			throw new BaseException(
					ErrorCode.DATA_CREATE_TABLE.getValue()
					,ErrorCode.DATA_CREATE_TABLE.getText()
					,Dao.class
					,"createTableField(FieldSetEntity tfs)"
			);
		}
		
		PreparedStatement ps = null;
		Connection        con = null;
		try{
			con =ConnectionManager.getConnection();
			StringBuffer sql=new StringBuffer();
			sql.append(" ALTER TABLE `").append(tfs.getString("table_name")).append("` ADD `");
			sql.append(tfs.getString("field_name")).append("` "+tfs.getString("field_type")).append("(");// 类型
			sql.append(tfs.getString("field_length"));// 字段长度	
			String unit = tfs.getString("field_unit");// 小数位数
			unit = (unit.equals("0")) ? "" : ("," + unit);
			sql.append(unit+") ");
			if (tfs.getString("is_required").equals("1")) {// 0不必填，1必填
				sql.append("NOT NULL ");
			}else {
				sql.append("DEFAULT NULL ");
			}
			if (tfs.getString("is_unique").equals("1")) {// 0不唯一，1唯一
				sql.append("unique ");
			}
			if(tfs.getString("field_description") != null && tfs.getString("field_description").length()>0){//字段描述
				sql.append("COMMENT"+"'"+ tfs.getString("field_description") +"' ");
			}
			System.out.println(" DDL:"+sql.toString());
			ps  = con.prepareStatement(sql.toString()); 
			ps.execute();
			return true;
		}catch(Exception e){
			throw new BaseException(
					ErrorCode.DATA_CREATE_TABLE.getValue()
					,ErrorCode.DATA_CREATE_TABLE.getText()+e.getMessage()
					,Dao.class
					,"createTableField(FieldSetEntity tfs)"
					,e
			);
		}finally{
			//this.closeConnection(con,ps,null);
		}
	}
	/**
	 * 修改字段
	 * @param tfs 原字段
	 * @param newtfs 修改后的字段
	 * @return
	 * @throws BaseException
	 */
	public static boolean updateTableField(FieldSetEntity tfs,FieldSetEntity newtfs) throws BaseException{
		if(newtfs == null || newtfs.getString("table_name") ==null  || "".equals(newtfs.getString("table_name")) ) {
			throw new BaseException(
					ErrorCode.DATA_CREATE_TABLE.getValue()
					,ErrorCode.DATA_CREATE_TABLE.getText()
					,Dao.class
					,"updateTableField(FieldSetEntity tfs,FieldSetEntity newtfs)"
			);
		}
		
		PreparedStatement ps = null;
		Connection        con = null;
		try{
			con =ConnectionManager.getConnection();
			StringBuffer sql=new StringBuffer();
			//alter  table test_newtable change newField100 newField1 VARCHAR(32) NOT NULL  COMMENT '为空';
			sql.append("ALTER TABLE `").append(newtfs.getString("table_name")).append("` ");
			String fieldName = tfs.getString("field_name");//原表名
			String newFieldName = newtfs.getString("field_name");//新表名
			String isUnique = tfs.getString("is_unique");//原唯一约束
			isUnique = isUnique==null?"0":isUnique;
			String newIsUnique = newtfs.getString("is_unique");//新唯一约束
			newIsUnique = newIsUnique==null?"0":newIsUnique;
			String isRequired = tfs.getString("is_required");
			isRequired = isRequired==null?"0":isRequired;
			String newTfsisRequired = newtfs.getString("is_required");
			newTfsisRequired = newTfsisRequired==null?"0":newTfsisRequired;
			//字段名是否修改
			if(fieldName.equals(newFieldName)){//字段名相同不用修改
				sql.append(" MODIFY `").append(fieldName).append("` ");//不修改字段名用MODIFY
			}else {
				sql.append(" CHANGE `").append(fieldName).append("` `").append(newFieldName).append("` ");//修改字段名用CHANGE
			}
			sql.append(" ").append(newtfs.getString("field_type")).append("("+newtfs.getString("field_length"));// DEFAULT NULL  COMMENT '为空';
			String unit = newtfs.getString("field_unit");// 小数位数
			unit = (unit.equals("0")) ? "" : ("," + unit);
			sql.append(unit+") ");
			if (!isRequired.equals(newTfsisRequired)) {
				if (newTfsisRequired.equals("1")) {// 0不必填，1必填
					sql.append("NOT NULL ");//不能为空
				}else {
					sql.append("DEFAULT NULL ");//能为空
				}
			}
			if(newtfs.getString("field_description") != null && newtfs.getString("field_description").length()>0){//字段描述
				sql.append("COMMENT "+"'"+ newtfs.getString("field_description") +"' ");
			}
			//ALTER TABLE   test_newtable  ADD unique(newField1)
			//ALTER TABLE test_newtable DROP INDEX newField1;
			System.out.println(" DDL:"+sql.toString());
			ps  = con.prepareStatement(sql.toString()); 
			ps.execute();
				if(isUnique.equals(newIsUnique)){
					return true;
				}else {
					StringBuffer unique=new StringBuffer();
					if (newIsUnique.equals("1")) {//1唯一   新增唯一约束
						unique.append(" ALTER TABLE ").append(tfs.getString("table_name")).append(" ADD unique(").append(newFieldName+")");
					}else{//不唯一   去掉唯一约束
						unique.append(" ALTER TABLE ").append(tfs.getString("table_name")).append(" DROP INDEX ").append(newFieldName);
					}
					System.out.println(" DDL:"+unique.toString());
					ps = con.prepareStatement(unique.toString()); 
					ps.execute();
				}
			return true;
		}catch(Exception e){
			throw new BaseException(
					ErrorCode.DATA_CREATE_TABLE.getValue()
					,ErrorCode.DATA_CREATE_TABLE.getText()+e.getMessage()
					,Dao.class
					,"updateTableField(FieldSetEntity tfs,FieldSetEntity newtfs)"
					,e
			);
		}finally{
			//this.closeConnection(con,ps,null);
		}
	}
	/**
	 * 删除表或视图
	 * @param tfs
	 * @return
	 * @throws BaseException
	 */
	public static boolean deleteTable(FieldSetEntity tfs) throws BaseException{

		if(tfs == null || tfs.getString("table_name") ==null  || "".equals(tfs.getString("table_name")) ) {
			throw new BaseException(
					ErrorCode.DATA_CREATE_TABLE.getValue()
					,ErrorCode.DATA_CREATE_TABLE.getText()
					,Dao.class
					,"deleteTable(FieldSetEntity tfs)"
			);
		}
		int table_type=tfs.getInteger("table_type");
		
		PreparedStatement ps = null;
		Connection        con = null;
		try{
			if(table_type==2) {
				//删除视图表    DROP  VIEW  IF  EXISTS  stu_glass;
				con =ConnectionManager.getConnection();
				StringBuffer delviewsql=new StringBuffer();
				delviewsql.append(" DROP  VIEW  IF  EXISTS ").append("`").append(tfs.getString("table_name")).append("`");//执行的SQL删除视图语句
				System.out.println(" DDL:"+delviewsql.toString());
				ps  = con.prepareStatement(delviewsql.toString()); 
				ps.execute();
				return true;
			} 
				con =ConnectionManager.getConnection();
				StringBuffer sql=new StringBuffer();
				sql.append(" DROP TABLE `").append(tfs.getString("table_name")).append("`");
				System.out.println(" DDL:"+sql.toString());
				ps  = con.prepareStatement(sql.toString()); 
				ps.execute();
				return true;
		}catch(Exception e){
			throw new BaseException(
					ErrorCode.DATA_CREATE_TABLE.getValue()
					,ErrorCode.DATA_CREATE_TABLE.getText()+e.getMessage()
					,Dao.class
					,"deleteTable(FieldSetEntity tfs)"
					,e
			);
		}finally{
			//this.closeConnection(con,ps,null);
		}
	}
	/**
	 * 删除表字段
	 * @param tfs
	 * @return
	 * @throws BaseException
	 */
	public static boolean deleteTableField(FieldSetEntity tfs) throws BaseException{

		if(tfs == null || tfs.getString("table_name") ==null  || "".equals(tfs.getString("table_name")) ) {
			throw new BaseException(
					ErrorCode.DATA_CREATE_TABLE.getValue()
					,ErrorCode.DATA_CREATE_TABLE.getText()
					,Dao.class
					,"deleteTableField(FieldSetEntity tfs)"
			);
		}
		PreparedStatement ps = null;
		Connection        con = null;
		try{
			con =ConnectionManager.getConnection();
			StringBuffer sql=new StringBuffer();
			sql.append(" ALTER TABLE `").append(tfs.getString("table_name")).append("`").append(" DROP `").append(tfs.getString("field_name")).append("`");
			System.out.println(" DDL:"+sql.toString());
			ps  = con.prepareStatement(sql.toString()); 
			ps.execute();
			return true;
		}catch(Exception e){
			throw new BaseException(
					ErrorCode.DATA_CREATE_TABLE.getValue()
					,ErrorCode.DATA_CREATE_TABLE.getText()+e.getMessage()
					,Dao.class
					,"deleteTableField(FieldSetEntity tfs)"
					,e
			);
		}finally{
			//this.closeConnection(con,ps,null);
		}
	}
	/**
	 * 修改表名
	 * @param tfs
	 * @return
	 * @throws BaseException
	 */
	public static boolean updateTableName(FieldSetEntity tfs) throws BaseException{

		if(tfs == null || tfs.getString("table_name") ==null  || "".equals(tfs.getString("table_name")) ) {
			throw new BaseException(
					ErrorCode.DATA_CREATE_TABLE.getValue()
					,ErrorCode.DATA_CREATE_TABLE.getText()
					,Dao.class
					,"deleteTableField(FieldSetEntity tfs)"
			);
		}
		PreparedStatement ps = null;
		Connection        con = null;
		try{
			con =ConnectionManager.getConnection();
			StringBuffer sql=new StringBuffer();
			sql.append(" RENAME TABLE `").append(tfs.getString("table_name")).append("` TO `").append(tfs.getString("new_table_name")).append("`");
			System.out.println(" DDL:"+sql.toString());
			ps  = con.prepareStatement(sql.toString()); 
			ps.execute();
			return true;
		}catch(Exception e){
			throw new BaseException(
					ErrorCode.DATA_CREATE_TABLE.getValue()
					,ErrorCode.DATA_CREATE_TABLE.getText()+e.getMessage()
					,Dao.class
					,"deleteTableField(FieldSetEntity tfs)"
					,e
			);
		}finally{
			//this.closeConnection(con,ps,null);
		}
	}
		/**
		 * 修改视图sql语句
		 * @param tfs
		 * @return
		 * @throws BaseException
		 */
		public static boolean updateViewSql(FieldSetEntity tfs) throws BaseException{
			//CREATE  OR  REPLACE VIEW test_tableTwo_v  AS  SELECT  *  FROM test_newTable_two; 
			if(tfs == null || tfs.getString("table_name") ==null  || "".equals(tfs.getString("table_name")) ) {
				throw new BaseException(
						ErrorCode.DATA_CREATE_TABLE.getValue()
						,ErrorCode.DATA_CREATE_TABLE.getText()
						,Dao.class
						,"updateViewSql(FieldSetEntity tfs)"
				);
			}
			PreparedStatement ps = null;
			Connection        con = null;
			try{
				con =ConnectionManager.getConnection();
				StringBuffer sql=new StringBuffer();
				sql.append(" CREATE OR REPLACE VIEW `").append(tfs.getString("table_name")).append("` AS ( ");
				sql.append(tfs.getString("create_view_sql")).append(" ); ");//msv_execute_sql   执行的SQL语句
				System.out.println(" DDL:"+sql.toString());
				ps  = con.prepareStatement(sql.toString()); 
				ps.execute();
				return true;
			}catch(Exception e){
				throw new BaseException(
						ErrorCode.DATA_CREATE_TABLE.getValue()
						,ErrorCode.DATA_CREATE_TABLE.getText()+e.getMessage()
						,Dao.class
						,"updateViewSql(FieldSetEntity tfs)"
						,e
				);
			}finally{
				//this.closeConnection(con,ps,null);
			}
		}
	    /**
	     * 获取表中所有字段名称
	     * @param sql 表名
	     * @return
	     * @throws BaseException 
	     * show full columns from tableName 通过表名获取数据库元数据
	     */
	    public static DataTableEntity getColumnNames(String sql) throws BaseException {
	    	DataTableEntity dataTableEntity = new DataTableEntity();
	        //与数据库的连接
	        PreparedStatement ps = null;
			Connection        con = null;
	        try {
	        	con =ConnectionManager.getConnection();
	        	//DatabaseMetaData data = con.getMetaData();
	        	ps = con.prepareStatement(sql);
	            //结果集元数据
	            ResultSetMetaData rsmd = ps.getMetaData();
	            //ps.executeQuery("show full columns from " + sql);
	            //表列数
	            int size = rsmd.getColumnCount();
	            for (int i = 0; i < size; i++) {
	            	FieldSetEntity fieldSetEntity = new FieldSetEntity();
	            	//别名
	            	fieldSetEntity.setValue("field_name", rsmd.getColumnLabel(i+1));
    				//字段名 
	            	//System.out.println("字段名"+rsmd.getColumnName(i + 1));
        			//字段类型 field_type
	            	fieldSetEntity.setValue("field_type",rsmd.getColumnTypeName(i + 1));
        			//获取指定列的指定列大小。 field_length
	            	fieldSetEntity.setValue("field_length",rsmd.getPrecision(i + 1));
        			//获取小数点右侧的指定列的位数。 field_unit
	            	fieldSetEntity.setValue("field_unit",rsmd.getScale(i + 1));		
	            	dataTableEntity.addFieldSetEntity(fieldSetEntity);
	            }
	            
	            
	        } catch (Exception e) {
				throw new BaseException(
						ErrorCode.DATA_CREATE_TABLE.getValue()
						,ErrorCode.DATA_CREATE_TABLE.getText()+e.getMessage()
						,Dao.class
						,"updateViewSql(FieldSetEntity tfs)"
						,e
				);
			} finally {
				//this.closeConnection(con,ps,null);
				}
	        return dataTableEntity;
	    }
	    /**
	     * 判断表是否存在
	     * @param tableName
	     * @return
	     * @throws BaseException
	     * @throws SQLException
	     */
	    public static boolean isTableExistEntrust(String tableName) throws BaseException{    
	    	  	PreparedStatement ps = null;
				Connection        con = null;
				con = ConnectionManager.getConnection();
	    try{  
	    	ResultSet rs = con.getMetaData().getTables(null, null, tableName, null);  
        if (rs.next()) {  
	              return true;  
	        }else {  
	              return false;  
	        } 
        } catch (Exception e) {
			throw new BaseException(
					ErrorCode.DATA_CREATE_TABLE.getValue()
					,ErrorCode.DATA_CREATE_TABLE.getText()+e.getMessage()
					,Dao.class
					,"updateViewSql(FieldSetEntity tfs)"
					,e
			);
		} finally {
			//this.closeConnection(con,ps,null);
			}
        }
}