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.PostgreType;

import cn.hutool.core.util.StrUtil;

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

	protected FlyPostgre() {
	}

	@Override
	public List<Table> getTableList(String schema) {
		StringBuffer sb = new StringBuffer();
		sb.append("select relname as tablename,");
		sb.append("cast(obj_description(relfilenode, 'pg_class') as varchar) as comment ");
		sb.append("from pg_class c ");
		sb.append("inner join ( ");
		if (StrUtil.isNotBlank(schema)) {
			sb.append("select tablename from pg_tables where schemaname = '" + schema + "' ");
		} else {
			sb.append("select tablename from pg_tables ");
		}
		sb.append(") s on c.relname = s.tablename ");
		sb.append("where relkind = 'r' ");
		sb.append("and relname not like 'pg_%' ");
		sb.append("and relname not like 'sql_%' ");
		sb.append("order by relname");
		List<Table> tables = new ArrayList<>();
		String sql = sb.toString();
		List<Map<String, Object>> retList = fly.getMapList(sql);
		retList.forEach(item -> {
			String tableName = item.get("tablename").toString();
			String comment = item.get("comment") != null ? item.get("comment").toString() : "";
			tables.add(new Table(tableName, comment));
		});
		return tables;
	}

	@Override
	public Table getTable(String schema, String tableName) {
		StringBuffer sb = new StringBuffer();
		sb.append("select relname as tablename,");
		sb.append("cast(obj_description(relfilenode, 'pg_class') as varchar) as comment ");
		sb.append("from pg_class c ");
		sb.append("inner join ( ");
		if (StrUtil.isNotBlank(schema)) {
			sb.append("select tablename from pg_tables where schemaname = '" + schema + "' ");
		} else {
			sb.append("select tablename from pg_tables ");
		}
		sb.append(") s on c.relname = s.tablename ");
		sb.append("where relkind = 'r' ");
		sb.append("and relname not like 'pg_%' ");
		sb.append("and relname not like 'sql_%' ");
		sb.append("and relname = '" + tableName + "' ");
		sb.append("order by relname");
		String sql = sb.toString();
		Map<String, Object> item = fly.getMap(sql);
		if (item != null) {
			String comment = item.get("comment") != null ? item.get("comment").toString() : "";
			return new Table(tableName, comment);
		}
		return null;
	}

	@Override
	public List<Column> getColumnList(String tableName) {
		List<Column> list = new ArrayList<>();
		StringBuffer sb = new StringBuffer();
		sb.append("select col_description(a.attrelid,a.attnum) as comments,");
		sb.append("pg_type.typname as typename,");
		sb.append("a.attname as columnname,");
		sb.append("a.attnotnull as notnull ");
		sb.append("from pg_class as c,");
		sb.append("pg_attribute as a ");
		sb.append("inner join pg_type ");
		sb.append("on pg_type.oid = a.atttypid ");
		sb.append("where c.relname = '" + tableName + "' ");
		sb.append("and a.attrelid = c.oid ");
		sb.append("and a.attnum>0");
		String sql = sb.toString();
		List<Map<String, Object>> retList = fly.getMapList(sql);
		if (retList != null) {
			JavaType javaType = new PostgreType();
			retList.forEach(map -> {
				Column dataColumn = new Column();
				dataColumn.setColumnName(map.get("columnname").toString());
				dataColumn.setColumnComment(map.get("comments") != null ? map.get("comments").toString() : "");
				String type = map.get("typename").toString();
				Boolean notnull = Boolean.parseBoolean(map.get("notnull").toString());

				if (!notnull) {
					dataColumn.setIsNullable("YES");
				} else {
					dataColumn.setIsNullable("NO");
				}
				dataColumn.setDataType(type);
				dataColumn.setJavaType(javaType.get(dataColumn.getDataType()));
				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 pg_attribute.attname as colname,pg_type.typname as typename,");
		sb.append("pg_constraint.conname as pk_name ");
		sb.append("from pg_constraint inner join pg_class ");
		sb.append("on pg_constraint.conrelid = pg_class.oid ");
		sb.append("inner join pg_attribute on pg_attribute.attrelid = pg_class.oid ");
		sb.append("and pg_attribute.attnum = pg_constraint.conkey[1] ");
		sb.append("inner join pg_type on pg_type.oid = pg_attribute.atttypid ");
		sb.append("where pg_class.relname = '" + tableName + "' ");
		sb.append("and pg_constraint.contype='p'");
		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("colname") != null ? map.get("colname").toString() : "";
			if (StrUtil.isNotBlank(primaryColumn)) {
				break;
			}
		}
		if (StrUtil.isNotBlank(primaryColumn)) {
			// 有主键
			for (Column col : list) {
				if (col.getColumnName().equalsIgnoreCase(primaryColumn)) {
					col.setPrimaryKey(true);
					break;
				}
			}
		}
	}

}
