package org.springblade.sqlfly.opt;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.springblade.sqlfly.config.Column;
import org.springblade.sqlfly.config.Table;
import org.springblade.sqlfly.type.JavaType;
import org.springblade.sqlfly.type.OracleType;

import cn.hutool.core.util.NumberUtil;
import cn.hutool.core.util.StrUtil;

/**
 * oracle数据库操作
 * 
 * @author William
 * @date 2022年10月28日 上午11:30:03
 */
public class FlyOracle extends IFly {

	protected FlyOracle() {
	}

	@Override
	public List<Table> getTableList(String schema) {
		List<Table> tables = new ArrayList<>();
		String sql = "select * from user_tab_comments";
		List<Map<String, Object>> retList = fly.getMapList(sql);
		retList.forEach(item -> {
			Table table = new Table();
			table.setTableName(item.get("TABLE_NAME").toString());
			Object obj = item.get("COMMENTS");
			table.setTableComment(obj != null ? obj.toString() : "");
			tables.add(table);
		});

		return tables;
	}

	@Override
	public Table getTable(String schema, String tableName) {
		Table table = new Table(tableName);
		String sql = "select * from user_tab_comments where Table_Name='" + tableName + "'";
		Map<String, Object> map = fly.getMap(sql);
		if (map != null) {
			Object obj = map.get("COMMENTS");
			table.setTableComment(obj != null ? obj.toString() : "");
			return table;
		}
		return null;
	}

	@Override
	public List<Column> getColumnList(String tableName) {
		List<Column> list = new ArrayList<>();
		StringBuffer sb = new StringBuffer();
		sb.append("select t.table_name,t.column_name,t.data_type,t.data_length,t.data_scale,c.comments ");
		sb.append("from user_tab_columns t ");
		sb.append("left join  (");
		sb.append("select column_name,comments from user_col_comments ");
		sb.append("where table_name='" + tableName + "' ");
		sb.append(")c on t.column_name=c.column_name ");
		sb.append("where t.table_name='" + tableName + "'");
		String sql = sb.toString();
		List<Map<String, Object>> retList = fly.getMapList(sql);
		if (retList != null) {
			JavaType javaType = new OracleType();
			retList.forEach(map -> {
				Column dataColumn = new Column();
				dataColumn.setColumnName(map.get("COLUMN_NAME").toString());
				dataColumn.setColumnComment(map.get("COMMENTS") != null ? map.get("COMMENTS").toString() : "");
				dataColumn.setDataType(map.get("DATA_TYPE").toString());
				dataColumn.setSize(NumberUtil.parseInt(map.get("DATA_LENGTH").toString()));
				Object scale = map.get("DATA_SCALE");
				dataColumn.setScale(scale != null ? NumberUtil.parseInt(scale.toString()) : 0);
				String jType = javaType.get(dataColumn.getDataType(), dataColumn.getSize(), dataColumn.getScale());
				dataColumn.setJavaType(jType);
				list.add(dataColumn);
			});
			findPrimaryKey(tableName, list);
		}
		return list;
	}

	private void findPrimaryKey(String tableName, List<Column> list) {
		String primaryColumn = "";
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT cu.* ");
		sb.append("FROM user_cons_columns cu,user_constraints au ");
		sb.append("WHERE cu.constraint_name = au.constraint_name ");
		sb.append("AND au.constraint_type = 'P' ");
		sb.append("AND au.table_name = '" + tableName + "'");
		String sql = sb.toString();
		List<Map<String, Object>> retList = fly.getMapList(sql);
		if (retList == null || retList.isEmpty()) {
			return;
		}
		for (Map<String, Object> map : retList) {
			primaryColumn = map.get("COLUMN_NAME") != null ? map.get("COLUMN_NAME").toString() : "";
			if (StrUtil.isNotBlank(primaryColumn)) {
				break;
			}
		}
		if (StrUtil.isNotBlank(primaryColumn)) {
			// 有主键
			for (Column col : list) {
				if (col.getColumnName().equalsIgnoreCase(primaryColumn)) {
					col.setPrimaryKey(true);
					break;
				}
			}
		}
	}
}
