package com.product.org.admin.service;

import com.product.core.service.support.QueryFilterService;
import com.product.core.spring.context.SpringMVCContextHolder;
import com.product.module.sys.entity.SystemUser;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.alibaba.druid.util.StringUtils;
import com.product.core.dao.BaseDao;
import com.product.core.entity.DataTableEntity;
import com.product.core.entity.FieldMetaEntity;
import com.product.core.entity.FieldSetEntity;
import com.product.core.exception.BaseException;
import com.product.core.permission.PermissionService;
import com.product.core.service.support.AbstractBaseService;
import com.product.core.transfer.Transactional;
import com.product.org.admin.config.CmnConst;
import com.product.org.admin.config.SystemCode;
import com.product.org.admin.service.idel.IViewTableService;
import com.product.org.admin.util.DatabaseUtil;
import com.product.util.BaseUtil;

/**
 * Copyright  © LX-BASE
 * @Title: ViewTableService
 * @Project: LX-BASE-SERVER
 * @Date: 2020-05-29 11:42:06
 * @Author: Xin.Luo
 * @Description:  视图管理Service层
 */
@Service
public class ViewTableService extends AbstractBaseService implements IViewTableService {
	@Autowired
	public BaseDao baseDao;
	@Override
	public BaseDao getBaseDao() {
		return baseDao;
	}
	@Override
	public void setBaseDao(BaseDao baseDao) {
		this.baseDao = baseDao;
	}
	
	@Autowired
	PermissionService permissionService;
	@Autowired
	QueryFilterService queryFilterService;

	@Override
	@Transactional
	public String addAView(FieldSetEntity viewTable) throws BaseException {
		//判断视图是否已经存在
		if(DatabaseUtil.isTableExistEntrust(viewTable.getString(CmnConst.TABLE_NAME))){
			throw new BaseException(SystemCode.SYSTEM_VIEW_REUSE_FAIL.getValue(),SystemCode.SYSTEM_VIEW_REUSE_FAIL.getText(),this.getClass(),"public String addAView(FieldSetEntity viewTable) throws BaseException");
		}
		String uuid = "";
		// 生产数据建模数据源表信息。 
			if(DatabaseUtil.createTable(viewTable)){
				// 存入数据库时，碰到'或“，数据库会认为值已经传完了    
				//-- 双引号，直接存就可以了，单引号，用两个单引号代替； 示例：
				String changeSql = viewTable.getString(CmnConst.VIEW_SQL);
				changeSql = changeSql.replaceAll("\n", "  ");//替换为空格
				changeSql = changeSql.replaceAll("\r", "  ");//替换为空格ssss
				viewTable.setValue(CmnConst.VIEW_SQL,changeSql); 
				uuid = baseDao.add(viewTable);
					}
			return uuid;
	}
	
	@Override
	@Transactional
	public boolean updateTableName(FieldSetEntity fieldSet) throws BaseException {
			FieldSetEntity newFSetEntity = new FieldSetEntity();
			
        	FieldMetaEntity f = new FieldMetaEntity();
        	f.setTableName(new Object[]{"product_sys_datamodel_table"});
        	newFSetEntity.setMeta(f);
        	
			String uuid = fieldSet.getString(CmnConst.UUID);//uuid
			String tableName = fieldSet.getString(CmnConst.TABLE_NAME);
			FieldSetEntity fEntity = baseDao.getFieldSetEntity(CmnConst.PRODUCT_SYS_DATAMODEL_TABLE, uuid, false);
			String name = fEntity.getString(CmnConst.TABLE_NAME);//原表名
			String description = fieldSet.getString(CmnConst.TABLE_DESCRIPTION);//描述
			String sql = fieldSet.getString(CmnConst.VIEW_SQL);//view_sql
			newFSetEntity.setValue(CmnConst.TABLE_NAME, name);//以前的表名
			newFSetEntity.setValue("new_table_name", tableName);//新表名
			//更.PRODUCT_SYS_DATAMODEL_TABLE  数据表信息
			
			if(!tableName.equals(name)){//新表名与旧表名不同
				DatabaseUtil.updateTableName(newFSetEntity);//修改表名称
			}
			fEntity.setValue(CmnConst.TABLE_NAME, tableName);
			fEntity.setValue(CmnConst.UUID, uuid);
			fEntity.setValue(CmnConst.TABLE_DESCRIPTION, description);
			fEntity.setValue(CmnConst.VIEW_SQL, sql);
			fEntity.setValue(CmnConst.UPDATED_BY, SpringMVCContextHolder.getCurrentUser().getUser_id());
			fEntity.setValue(CmnConst.UPDATED_UTC_DATETIME,transitionDate(new Date()));
			baseDao.update(fEntity);//修改表信息
		return true;
	}

	@Override
	@Transactional
	public List<String> addFieldInfo(DataTableEntity data, String table_uuid) throws BaseException {
		List<String> list = new ArrayList<>();
		for (int i = 0; i < data.getRows(); i++) {
			FieldSetEntity fEntity = data.getFieldSetEntity(i);
			fEntity.setValue(CmnConst.TABLE_UUID,table_uuid);
			fEntity.setValue(CmnConst.FIELD_DESCRIPTION,fEntity.getString(CmnConst.FIELD_NAME));
			fEntity.setValue(CmnConst.CREATED_UTC_DATETIME, transitionDate(new Date())); 
			list.add(baseDao.add(fEntity));
		}
		return list;
	}

	@Override
	@Transactional
	public Boolean upFieldInfo(DataTableEntity data) throws BaseException {
		for (int i = 0; i < data.getRows(); i++) {
			FieldSetEntity fEntity = data.getFieldSetEntity(i);
			//放入修改
			fEntity.setValue(CmnConst.UPDATED_UTC_DATETIME, transitionDate(new Date())); 
			baseDao.update(fEntity);
		}
		return true;
	}

	@Override
	@Transactional
	public boolean updateViewSql(FieldSetEntity fieldSet) throws BaseException {
		// 生产数据建模数据源表信息。
		String uuid = fieldSet.getString(CmnConst.UUID);//表的UUID
		fieldSet.setTableName(CmnConst.PRODUCT_SYS_DATAMODEL_TABLE);// 放入表名
		fieldSet.setValue(CmnConst.UPDATED_UTC_DATETIME, transitionDate(new Date()));// 修改时间
		fieldSet.setValue(CmnConst.TABLE_PRIMARY_KEY, CmnConst.UUID);// 默认关联UUID
			if(DatabaseUtil.updateViewSql(fieldSet)){
				// 存入数据库时，碰到'或“，数据库会认为值已经传完了    
				//-- 双引号，直接存就可以了，单引号，用两个单引号代替； 示例：
				String changeSql = fieldSet.getString(CmnConst.VIEW_SQL);
				//changeSql = changeSql.replaceAll("\'", "\''");//替换为2个
				changeSql = changeSql.replaceAll("\n", "  ");//替换为空格
				changeSql = changeSql.replaceAll("\r", "  ");//替换为空格
				fieldSet.setValue(CmnConst.VIEW_SQL,changeSql);
				if(baseDao.update(fieldSet)){
					baseDao.delete(CmnConst.PRODUCT_SYS_DATAMODEL_FIELD, CmnConst.TABLE_UUID+"=?", new String[]{uuid});							
				}
			}
			return true;
	}

	@Override
	@Transactional
	public DataTableEntity runSql(String view_sql) throws BaseException {
		view_sql = view_sql.replaceAll("\n", "  ");//替换为空格
		DataTableEntity dataTableEntity = DatabaseUtil.getColumnNames(view_sql);
		for (int i = 0; i <dataTableEntity.getRows(); i++) {
			FieldSetEntity fieldSetEntity = dataTableEntity.getFieldSetEntity(i);
			fieldSetEntity.setTableName(CmnConst.PRODUCT_SYS_DATAMODEL_FIELD);
				switch(fieldSetEntity.getString(CmnConst.FIELD_TYPE)){
			    case "VARCHAR" :
			    	fieldSetEntity.setValue(CmnConst.FIELD_TYPE, "string");
			       break; 
		      	default:
		    	 String type = fieldSetEntity.getString(CmnConst.FIELD_TYPE).toLowerCase();//转换为小写
		    	 fieldSetEntity.setValue(CmnConst.FIELD_TYPE, type);
		    	  break;
			}
		}
		//data存放表名
		dataTableEntity.setMeta(dataTableEntity.getFieldSetEntity(0).getMeta());
		return  dataTableEntity;
	}
	@Override
	@Transactional
	public FieldSetEntity getTableInfo(String table_uuid) throws BaseException {
		FieldSetEntity tableInfo = baseDao.getFieldSetEntity(CmnConst.PRODUCT_SYS_DATAMODEL_TABLE, table_uuid, false);
		//获取表对应字段
		DataTableEntity dataTableEntity = baseDao.listTable(CmnConst.PRODUCT_SYS_DATAMODEL_FIELD, "table_uuid = ?", new String[]{table_uuid}," field_id ASC");
		Map<String, DataTableEntity> subMap = new HashMap<String, DataTableEntity>();
		subMap.put(CmnConst.PRODUCT_SYS_DATAMODEL_FIELD, dataTableEntity);
		tableInfo.setSubData(subMap);
		return tableInfo;
	}
	@Override
	@Transactional
	public boolean delTable(String[] tableUuid) throws BaseException {
		DataTableEntity dataTableEntity = new DataTableEntity();
				//查询表   还要查询它是否有子表绑定，被绑定无法删除。
				for (int i = 0; i < tableUuid.length; ++i) {
					FieldSetEntity Entity = baseDao.getFieldSetEntity(CmnConst.PRODUCT_SYS_DATAMODEL_TABLE, tableUuid[i], false);
					dataTableEntity.addFieldSetEntity(Entity);
				}
				for (int i = 0; i < dataTableEntity.getRows(); i++) {
					FieldSetEntity fieldSetEntity = dataTableEntity.getFieldSetEntity(i);
					baseDao.delete(CmnConst.PRODUCT_SYS_DATAMODEL_FIELD, "table_uuid = ?",new String[]{ fieldSetEntity.getString("uuid")});
					//判断数据库是否有表
					if(DatabaseUtil.isTableExistEntrust(fieldSetEntity.getString(CmnConst.TABLE_NAME))){
						DatabaseUtil.deleteTable(fieldSetEntity);
					}
				}
	
			return baseDao.delete(CmnConst.PRODUCT_SYS_DATAMODEL_TABLE, tableUuid);//uuid
	}
	// 时间处理为字符串
	public String transitionDate(Date date) {
		// yyyy表示年数，MM表示月，dd表示日
		// HH表示24小时制的小时(hh表示12小时的小时)，mm表示分钟，ss表示秒，SSS表示毫秒
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		return sdf.format(date);
	}
	
	/**
	 * 	视图列表
	 * 
	 * @throws BaseException
	 * @return DataTableEntity
	 */
	@Override
	public DataTableEntity getViewTableAll(FieldSetEntity fse) throws BaseException {
		SystemUser currentUser = SpringMVCContextHolder.getCurrentUser();
		String dataFilter=permissionService.getDataFilter("a."+CmnConst.ORG_LEVEL_UUID);
		if(currentUser.getUserType() == 1) {
			dataFilter = "a.table_type = 2 AND a.created_by = " + SpringMVCContextHolder.getCurrentUser().getUser_id();
		}else {
			dataFilter = "a.table_type = 2 AND "+ dataFilter;
		}
		String queryFilter;
		if(BaseUtil.dataTableIsEmpty(fse.getSubDataTable("systemSeniorQueryString"))){
			queryFilter = "";
		}else {
			queryFilter	= " AND " + queryFilterService.getQueryFilter(fse);
		}

		String sql="SELECT a.uuid,b.uuid uuid1,a.table_name,a.table_description,b.org_level_name FROM product_sys_datamodel_table a LEFT JOIN product_sys_org_levels b ON a.org_level_uuid = b.uuid WHERE "+dataFilter+queryFilter;
		DataTableEntity dt = baseDao.listTable(sql, new Object[] {},fse.getInteger(CmnConst.PAGESIZE), fse.getInteger(CmnConst.CPAGE));
		if (!BaseUtil.dataTableIsEmpty(dt)) {
			baseDao.listInternationDataTable(dt, null);
		}
		return dt;
	}

	@Override
	@Transactional
	public void addNameSubsidiary(FieldSetEntity fse) {
		String table_name = fse.getString(CmnConst.TABLE_NAME);//表名
		table_name = table_name.replace(" ", "");
		String org_level_uuid = fse.getString(CmnConst.ORG_LEVEL_UUID);
		SystemUser currentUser = SpringMVCContextHolder.getCurrentUser();
		if (currentUser != null && currentUser.getCurrentManager() != null) {
			int manager_type = currentUser.getCurrentManager().getInteger("manager_type");
			if (manager_type == 3) {
				fse.setValue(CmnConst.ORG_LEVEL_UUID, org_level_uuid);
				FieldSetEntity fseOrg = baseDao
						.getFieldSetEntity(CmnConst.PRODUCT_SYS_ORG_LEVELS, org_level_uuid, false);
				if (fseOrg != null) {
					fse.setValue(CmnConst.TABLE_NAME, table_name + "_" + fseOrg.getInteger("org_level_id"));
				}
			}
		}
	}
}