package com.seeyon.utils.form;

import com.seeyon.aicloud.common.JsonUtils;
import com.seeyon.cap4.form.api.FormApi4Cap4;
import com.seeyon.cap4.form.bean.FormBean;
import com.seeyon.cap4.form.bean.FormFieldBean;
import com.seeyon.ctp.common.AppContext;
import com.seeyon.ctp.common.exceptions.BusinessException;
import com.seeyon.ctp.util.JDBCAgent;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import java.util.*;
import java.util.stream.Collectors;

public class FormTableExecutor {

    private static final Log log = LogFactory.getLog(FormTableExecutor.class);

    /*获取主表定义bean对象*/
    public static TableContext master(String formNo) throws BusinessException {
        FormBean form = getForm(formNo);
        return new TableContext(form.getMasterTableBean());
    }

    /*获取从表定义bean对象*/
    public static TableContext sub(String formNo, String subTable) throws BusinessException {
        FormBean form = getForm(formNo);
        return form.getSubTableBean().stream()
                .filter(t -> t.getTableName().equals(subTable) || t.getDisplay().equals(subTable))
                .findFirst()
                .map(TableContext::new)
                .orElseThrow(() -> new BusinessException("未找到子表：" + subTable));
    }

    private static FormBean getForm(String formNo) throws BusinessException {
        FormApi4Cap4 api = (FormApi4Cap4) AppContext.getBean("formApi4Cap4");
        return api.getFormByFormCode(formNo);
    }

    /*填充update字段fieldName*/
    private static void fillUpdateFields(TableContext ctx, List<FormUpdateField> updateFields) {
        if (updateFields == null) return;

        for (FormUpdateField c : updateFields) {
            FormFieldBean field = ctx.getTableBean().getFieldBeanByDisplay(c.getDisplay());
            if (field != null) {
                c.setFieldName(field.getColumnName());
            }
        }
    }

    /*填充condition字段fieldName*/
    private static void fillConditionFields(TableContext ctx, List<FormWhereCondition> conditions) {
        if (conditions == null) return;

        for (FormWhereCondition c : conditions) {
            FormFieldBean field = ctx.getTableBean().getFieldBeanByDisplay(c.getDisplay());
            if (field != null) {
                c.setFieldName(field.getColumnName());
            } else if ("ID".equalsIgnoreCase(c.getDisplay())) {
                c.setFieldName("ID");
            } else if ("formmain_id".equalsIgnoreCase(c.getDisplay())) {
                c.setFieldName("formmain_id");
            }
        }
    }

    /*将查询列display转换为fieldName*/
    private static List<String> resolveQueryColumns(TableContext ctx, List<String> displays) {
        if (displays == null) return Collections.emptyList();
        return displays.stream()
                .map(ctx.getTableBean()::getFieldBeanByDisplay)
                .filter(Objects::nonNull)
                .map(FormFieldBean::getColumnName)
                .collect(Collectors.toList());
    }

    /* ========== 查询方法 ========== */

    public static List<FormColumn> query(TableContext ctx,
                                  List<String> queryDisplays,
                                  List<FormWhereCondition> conditions,
                                  boolean changeEnum) {

        fillConditionFields(ctx, conditions);
        List<String> columns = resolveQueryColumns(ctx, queryDisplays);
        SqlBuildParam param = new SqlBuildParam();
        param.setSqlType(SqlType.SELECT);
        param.setTableName(ctx.getTableName());
        param.setQueryColumns(columns);
        param.setConditions(conditions);
        Map<String, Object> sqlMap = generateSql(param);
        JDBCAgent agent = new JDBCAgent();

        try {
            agent.execute((String) sqlMap.get("sql"), (List<Object>) sqlMap.get("params"));
            List<Map<String, Object>> rows = agent.resultSetToList();

            return rows.stream()
                    .map(row -> buildFormColumn(row, ctx, changeEnum))
                    .collect(Collectors.toList());
        } catch (Exception e) {
            log.error("执行sql为: " + sqlMap.get("sql"));
            log.error("执行sql参数为: " + JsonUtils.toJSONString(sqlMap.get("params")));
            log.error(e.getMessage(),e);
        } finally {
            agent.close();
        }
        return new ArrayList<>();
    }

    /*查询单条记录*/
    public static FormColumn queryOne(TableContext ctx,
                               List<FormWhereCondition> conditions,
                               boolean changeEnum) {

        fillConditionFields(ctx, conditions);
        SqlBuildParam param = new SqlBuildParam();
        param.setSqlType(SqlType.SELECT);
        param.setTableName(ctx.getTableName());
        param.setConditions(conditions);
        Map<String, Object> sqlMap = generateSql(param);
        JDBCAgent agent = new JDBCAgent();

        try {
            agent.execute((String) sqlMap.get("sql"), (List<Object>) sqlMap.get("params"));
            Map<String, Object> row = agent.resultSetToMap();
            if (row == null) return null;

            return buildFormColumn(row, ctx, changeEnum);
        } catch (Exception e) {
            log.error("执行sql为: " + sqlMap.get("sql"));
            log.error("执行sql参数为: " + JsonUtils.toJSONString(sqlMap.get("params")));
            log.error(e.getMessage(),e);
        } finally {
            agent.close();
        }
        return null;
    }

    /*分页查询*/
    public static List<FormColumn> pageQuery(TableContext ctx,
                                      List<String> displays,
                                      List<FormWhereCondition> conditions,
                                      int pageNo,
                                      int pageSize,
                                      boolean changeEnum) {

        fillConditionFields(ctx, conditions);
        List<String> columns = resolveQueryColumns(ctx, displays);
        SqlBuildParam param = new SqlBuildParam();
        param.setSqlType(SqlType.SELECT);
        param.setTableName(ctx.getTableName());
        param.setQueryColumns(columns);
        param.setConditions(conditions);
        param.setPageNo(pageNo);
        param.setPageSize(pageSize);
        Map<String, Object> sqlMap = generateSql(param);;
        JDBCAgent agent = new JDBCAgent();

        try {
            agent.execute((String) sqlMap.get("sql"), (List<Object>) sqlMap.get("params"));
            List<Map<String, Object>> rows = agent.resultSetToList();

            return rows.stream()
                    .map(r -> buildFormColumn(r, ctx, changeEnum))
                    .collect(Collectors.toList());
        } catch (Exception e){
            log.error("执行sql为: " + sqlMap.get("sql"));
            log.error("执行sql参数为: " + JsonUtils.toJSONString(sqlMap.get("params")));
            log.error(e.getMessage(),e);
        } finally {
            agent.close();
        }
        return new ArrayList<>();
    }

    /*统计数量*/
    public static long count(TableContext ctx,List<String> countField,List<FormWhereCondition> conditions) {
        fillConditionFields(ctx, conditions);
        List<String> countColumn = resolveQueryColumns(ctx,countField);
        SqlBuildParam param = new SqlBuildParam();
        param.setSqlType(SqlType.COUNT);
        param.setTableName(ctx.getTableName());
        param.setConditions(conditions);
        param.setCountField(countColumn.size() > 0 ? countColumn.get(0) : null);
        Map<String, Object> sqlMap = generateSql(param);

        JDBCAgent agent = new JDBCAgent();
        try {
            agent.execute((String) sqlMap.get("sql"), (List<Object>) sqlMap.get("params"));
            return (Long) agent.resultSetToMap().get("countnum");
        } catch (Exception e) {
            log.error("执行sql为: " + sqlMap.get("sql"));
            log.error("执行sql参数为: " + JsonUtils.toJSONString(sqlMap.get("params")));
            log.error(e.getMessage(),e);
            return 0L;
        } finally {
            agent.close();
        }
    }


    /*更新操作*/
    public static int update(TableContext ctx, List<FormUpdateField> fields, List<FormWhereCondition> conditions) {
        if (fields == null || fields.isEmpty()) throw new IllegalArgumentException("更新字段不能为空");
        if (conditions == null || conditions.isEmpty()) throw new IllegalArgumentException("UPDATE必须带条件");
        fillConditionFields(ctx, conditions);
        fillUpdateFields(ctx,fields);
        SqlBuildParam param = new SqlBuildParam();
        param.setSqlType(SqlType.UPDATE);
        param.setTableName(ctx.getTableName());
        param.setConditions(conditions);
        param.setUpdateFields(fields);
        Map<String, Object> sqlMap = generateSql(param);
        JDBCAgent agent = new JDBCAgent();
        try {
            return agent.execute((String) sqlMap.get("sql"), (List<Object>) sqlMap.get("params"));
        } catch (Exception e){
            log.error("执行sql为: " + sqlMap.get("sql"));
            log.error("执行sql参数为: " + JsonUtils.toJSONString(sqlMap.get("params")));
            log.error(e.getMessage(),e);
        }finally {
            agent.close();
        }
        return 0;
    }

    /*删除操作*/
    public static int delete(TableContext ctx, List<FormWhereCondition> conditions) {
        if (conditions == null || conditions.isEmpty()) throw new IllegalArgumentException("DELETE必须带条件");
        fillConditionFields(ctx, conditions);
        SqlBuildParam param = new SqlBuildParam();
        param.setSqlType(SqlType.DELETE);
        param.setTableName(ctx.getTableName());
        param.setConditions(conditions);

        Map<String, Object> sqlMap = generateSql(param);
        JDBCAgent agent = new JDBCAgent();
        try {
            return agent.execute((String) sqlMap.get("sql"), (List<Object>) sqlMap.get("params"));
        }catch (Exception e){
            log.error("执行sql为: " + sqlMap.get("sql"));
            log.error("执行sql参数为: " + JsonUtils.toJSONString(sqlMap.get("params")));
            log.error(e.getMessage(),e);
        }finally {
            agent.close();
        }
        return 0;
    }
    /* ========== 构建 FormColumn ========== */

    /*填充结果字段*/
    private static FormColumn buildFormColumn(Map<String, Object> row,
                                       TableContext ctx,
                                       boolean changeEnum) {
        FormColumn column = new FormColumn();
        try {
            List<FormFieldVo> vos = new ArrayList<>();

            for (Map.Entry<String, Object> e : row.entrySet()) {
                FormFieldBean field = ctx.getFieldMap().get(e.getKey());
                if (field == null) continue;

                Object value = e.getValue();
                if (changeEnum && field.isEnumField()) {
                    value = EnumMapUtils.getEnumShowValue(value);
                }
                // 枚举、部门、成员等特殊字段处理
                // value = handleFieldSpecialType(field, value);
                FormFieldVo vo = new FormFieldVo();
                vo.setDisplayName(field.getDisplay());
                vo.setValue(value);
                vos.add(vo);
            }

            column.setVos(vos);

            Object idVal = row.get("ID");
            if (idVal == null) idVal = row.get("id");
            if (idVal != null) column.setId(String.valueOf(idVal));

        } catch (Exception e) {
            log.error("构建 FormColumn 失败", e);
        }
        return column;
    }

    private static Object handleFieldSpecialType(FormFieldBean field, Object value) throws BusinessException {
        if (value == null) return null;

        switch (field.getInputType()) {
            case "select":
                if (field.isEnumField()) {
                    return EnumMapUtils.getEnumShowValue(value);
                }
                break;
            case "department":
            case "account":
            case "member":
            case "radio":
            case "checkbox":
            case "date":
            case "text":
            case "image":
            case "attachment":
            default:
                break;
        }
        return value;
    }

    private static Map<String, Object> buildCountSql(
            String countField,
            String tableName,
            List<FormWhereCondition> conditions) {

        StringBuilder sql = new StringBuilder("SELECT count(*) as countnum FROM ")
                .append(tableName);

        List<Object> params = new ArrayList<>();
        sql.append(buildWhereClause(conditions, params));

        return buildResult(sql, params);
    }

    private static Map<String, Object> buildSelectSql(
            String tableName,
            List<String> queryColumns,
            List<FormWhereCondition> conditions,
            Integer limit,
            Integer offset,
            String orderField) {

        StringBuilder sql = new StringBuilder("SELECT ");
        List<Object> params = new ArrayList<>();

        if (queryColumns == null || queryColumns.isEmpty()) {
            sql.append("*");
        } else {
            for (int i = 0; i < queryColumns.size(); i++) {
                sql.append(queryColumns.get(i));
                if (i < queryColumns.size() - 1) {
                    sql.append(",");
                }
            }
            sql.append(",ID");
        }

        sql.append(" FROM ").append(tableName);
        sql.append(buildWhereClause(conditions, params));
        if(orderField == null) {
            sql.append(" order by start_date desc");
        }else {
            sql.append(" order by "+ orderField +" desc");
        }
        if(limit != null && offset != null) {
            sql.append(" "+ offset + "," + limit);
        }
        sql.append(";");
        return buildResult(sql, params);
    }

    private static Map<String, Object> buildUpdateSql(
            String tableName,
            List<FormUpdateField> fields,
            List<FormWhereCondition> conditions) {

        if (fields == null || fields.isEmpty()) {
            throw new IllegalArgumentException("Update fields cannot be empty");
        }
        if (conditions == null || conditions.isEmpty()) {
            throw new IllegalArgumentException("UPDATE must have WHERE conditions");
        }

        StringBuilder sql = new StringBuilder("UPDATE ")
                .append(tableName)
                .append(" SET ");

        List<Object> params = new ArrayList<>();
        int count = 0;

        for (FormUpdateField f : fields) {
            if (f.getValue() == null || "".equals(f.getValue()) || "null".equals(f.getValue())) {
                continue;
            }
            if (count++ > 0) {
                sql.append(", ");
            }
            if (f.getValue() instanceof String && ((String) f.getValue()).startsWith("DATE>")) {
                sql.append(f.getFieldName()).append(" = TO_DATE(?, 'YYYY-MM-DD')");
                params.add(((String) f.getValue()).substring(5));
            } else {
                sql.append(f.getFieldName()).append(" = ?");
                params.add(f.getValue());
            }
        }

        if (count == 0) {
            throw new IllegalArgumentException("No valid update fields");
        }

        sql.append(buildWhereClause(conditions, params));
        return buildResult(sql, params);
    }

    private static Map<String, Object> buildDeleteSql(
            String tableName,
            List<FormWhereCondition> conditions) {

        if (conditions == null || conditions.isEmpty()) {
            throw new IllegalArgumentException("DELETE must have WHERE conditions");
        }

        StringBuilder values = new StringBuilder();
        List<Object> params = new ArrayList<>();

        StringBuilder sql = new StringBuilder("DELETE FROM ")
                .append(tableName);

        sql.append(buildWhereClause(conditions, params));
        return buildResult(sql, params);
    }

    private static Map<String, Object> buildInsertSql(
            String tableName,
            Map<String, Object> data) {

        if (data == null || data.isEmpty()) {
            throw new IllegalArgumentException("Insert data cannot be empty");
        }

        StringBuilder columns = new StringBuilder();
        StringBuilder values = new StringBuilder();
        List<Object> params = new ArrayList<>();

        for (Map.Entry<String, Object> e : data.entrySet()) {
            if (columns.length() > 0) {
                columns.append(",");
                values.append(",");
            }
            columns.append(e.getKey());
            values.append("?");
            params.add(e.getValue());
        }

        StringBuilder sql = new StringBuilder("INSERT INTO ")
                .append(tableName)
                .append(" (")
                .append(columns)
                .append(") VALUES (")
                .append(values)
                .append(")");

        return buildResult(sql, params);
    }

    /**
     * 动态生成 WHERE 子句
     * @param conditions 条件集合
     * @param params 参数列表（引用传递）
     * @return 拼接后的 WHERE 子句（包含 WHERE 关键字）
     */
    private static String buildWhereClause(List<FormWhereCondition> conditions, List<Object> params) {
        if (conditions == null || conditions.isEmpty()) {
            return "";
        }
        StringBuilder whereCause = new StringBuilder(" WHERE ");
        for (int i = 0; i < conditions.size(); i++) {
            FormWhereCondition c = conditions.get(i);
            // 左括号
            if (c.isStartWithBracket()) {
                whereCause.append("(");
            }
            String field = c.getFieldName();
            ClauseFactor factor = c.getClauseFactor();
            String operator = parseOperator(factor);
            // NULL / NOT NULL
            if (factor.isNullType()) {
                whereCause.append(field).append(" ").append(operator);
            } else if (factor.isInType()) {
                appendInClause(whereCause, field, operator, c.getValue(), params);
            } else {
                String placeholder = "?";
                // 函数模板（如 TO_DATE(?)）
                if (c.getIndex() != null) {
                    placeholder = c.getIndex();
                }
                whereCause.append(field).append(" ").append(operator).append(" ").append(placeholder);
                if (ClauseFactor.LIKE.equals(factor)) {
                    params.add("%" + c.getValue() + "%");
                } else {
                    params.add(c.getValue());
                }
            }
            // 右括号
            if (c.isEndWithBracket()) {
                whereCause.append(")");
            }
            // AND / OR
            if (i < conditions.size() - 1) {
                whereCause.append(" ").append(c.getConcatFactor()).append(" ");
            }
        }
        return whereCause.toString();
    }

    private static void appendInClause(
            StringBuilder sql,
            String field,
            String operator,
            Object value,
            List<Object> params) {
        if (!(value instanceof Collection)) {
            throw new IllegalArgumentException("IN 条件的值必须是 Collection");
        }
        Collection<?> values = (Collection<?>) value;
        if (values.isEmpty()) {
            // 防止 SQL 语法错误
            sql.append("1 = 0");
            return;
        }
        sql.append(field).append(" ").append(operator).append(" (");
        int index = 0;
        for (Object v : values) {
            if (index > 0) {
                sql.append(", ");
            }
            sql.append("?");
            params.add(v);
            index++;
        }
        sql.append(")");
    }

    /**
     * 解析运算符映射（eq -> =, lt -> < 等）
     */
    private static String parseOperator(ClauseFactor factor) {
        if(factor == null) {
            return "=";
        }
        switch (factor) {
            case EQ: return "=";
            case GT: return ">";
            case GE: return ">=";
            case LT: return "<";
            case LE: return "<=";
            case LIKE: return "LIKE";
            case NULL: return "IS NULL";      // 空值判断
            case NOT_NULL: return "IS NOT NULL"; // 非空判断
            default: throw new UnsupportedOperationException("不支持的运算符: " + factor);
        }
    }

    private static Map<String, Object> buildResult(StringBuilder sql, List<Object> params) {
        Map<String, Object> result = new HashMap<>();
        result.put("sql", sql.toString());
        result.put("params", params);
        return result;
    }

    private static Map<String, Object> generateSql(SqlBuildParam param) {

        if (param == null || param.getSqlType() == null) {
            throw new IllegalArgumentException("SqlBuildParam or SqlType cannot be null");
        }

        switch (param.getSqlType()) {
            case COUNT:
                return buildCountSql(
                        param.getCountField(),
                        param.getTableName(),
                        param.getConditions()
                );
            case SELECT:
                return buildSelectSql(
                        param.getTableName(),
                        param.getQueryColumns(),
                        param.getConditions(),
                        param.getLimit(),
                        param.getOffset(),
                        param.getOrderField()
                );
            case UPDATE:
                return buildUpdateSql(
                        param.getTableName(),
                        param.getUpdateFields(),
                        param.getConditions()
                );
            case INSERT:
                return buildInsertSql(
                        param.getTableName(),
                        param.getInsertData()
                );
            case DELETE:
                return buildDeleteSql(
                        param.getTableName(),
                        param.getConditions()
                );
            default:
                throw new UnsupportedOperationException("Unsupported SqlType: " + param.getSqlType());
        }
    }
}