package com.ycl.scheme;


import com.ycl.scheme.entity.BlockCode;
import com.ycl.scheme.entity.Keyword;
import com.ycl.scheme.entity.KeywordType;
import com.ycl.scheme.entity.TriggerType;
import org.apache.commons.lang3.StringUtils;

import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

public class SqlQueryBuilder extends AbstractBlockCodeBuilder {

    private static final String[] splits = {" ", "/", "\\", "(", ")", System.lineSeparator(), ":", ";", ",", "."};
    private BlockText blockText;

    private String sourceText;

    private String sql;

    private String where;

    private String select;

    private String compareScript;

    private String resultText;

    private List<Keyword> keywords;

    private  Keyword tableKeyword;

    private Integer  idx ;

    private TriggerType triggerType;


    private Map<String,Parameter> parameters = new HashMap<>();


    public SqlQueryBuilder(BlockText blockText, List<Keyword> keywords, Integer idx, TriggerType triggerType) {
        this.blockText = blockText;

        this.keywords = keywords;

        this.idx= idx;

        this.triggerType = triggerType;

    }

    @Override
    public BlockCode build() {
        this.tableKeyword =this.keywords.stream().filter(k->k.getName().equals(blockText.getSource().trim())).findFirst().orElse(null);
        if(this.tableKeyword == null){
            throw  new RuntimeException("没有找到数据源:" + this.blockText.getSource().trim());
        }

        this.scanWhere();
        if(this.triggerType== TriggerType.CANAL){
            this.addWhereRowSql();
        }
        this.scanResult();
        this.buildSql();
        BlockCode bc = this.buildCode();
        return bc;
    }

    private void scanWhere() {
        String inputExpression =  this.blockText.getExpression();
        int idx = 0;

        String res = "";
        String skw ="";

        while (idx + 1 < inputExpression.length()) {
            String c = inputExpression.substring(idx, idx+1);

            if (isSplit(c)) {
                Keyword kw = null;
                if (StringUtils.isNotBlank(skw)) {
                    kw = findKeyword(skw.trim());
                    if(kw !=null){
                        MatchResult mr = matchByKeyword(skw,kw,idx);
                        res += mr.getOutput();
                        idx = mr.getIdx();
                    }else{
                        res += skw;
                    }
                }else{
                    res +=c;
                }
                idx++;
                if(kw!=null && !kw.getKeywordType().equals(KeywordType.TIME_RANGE_FUNCTION) && !c.equals("(")){
                    res+=c;
                }
                skw="";
            }else{
                skw += c;
                idx++;
            }
        }

        this.where = res;

    }

    private void addWhereRowSql(){
        this.where += this.tableKeyword.getTableKey() + "= :rowId";
    }

    private boolean sqlTest(){
        //测试sql执行是否正确
        return true;
    }

    private void scanResult(){
        int idx=0;
        String res="";
        String skw ="";
        String resultText = this.blockText.getResult();

        String[]  ss = resultText.split(">|<|=");
        if(ss.length < 2){
            throw  new RuntimeException("结果表达式不正确");
        }

        String fields = ss[0];
        String compareStr = resultText.substring(fields.length());
        fields+=" ";
        while (idx+1  < fields.length()){
            String c = fields.substring(idx, idx+1);
            if(isSplit(c) ){
                Keyword kw = null;
                if (StringUtils.isNotBlank(skw)) {
                    kw = findKeyword(skw.trim());
                    if(kw !=null){
                        MatchResult mr = matchByKeyword(skw,kw,idx);
                        res += mr.getOutput();

                        idx = mr.getIdx();
                    }else{
                        res += skw;
                    }
                }else{
                    res +=c;
                }
                idx++;
                if(kw==null || !kw.getKeywordType().equals(KeywordType.FUNCTION) || kw.getFunctionParameterNullable()==null || !kw.getFunctionParameterNullable()){
                    res +=c;
                }

                skw="";
            }else{
                skw += c;
                idx++;
            }
        }
        this.select = res;
        this.compareScript= compareStr;
    }

    private void buildQuery(){



    }

    private void buildSql(){
        this.sql = "select " + this.select + " from " + this.tableKeyword.getTable() + " where " + this.where;

    }

    private BlockCode buildCode(){
        BlockCode  blockCode = new BlockCode();
        String funcName = "func_" + this.idx;
        StringBuilder csb = new StringBuilder();
        String sql = "\"" +this.sql + "\"";
        csb.append("        def " + funcName + "(){\n");
        csb.append("            Map<String,Object> pars = super.getSqlParameters();\n");
        csb.append("            def sql= " +sql + ";\n");

        this.parameters.forEach((k,v)->{
            String pname= "p_" + k;
            csb.append("            def " + pname  + " = " + v.getScript()+";\n");
            csb.append("            pars.put('" +v.getName() +"'," + pname +");\n");

        });
        csb.append("            def rs = service.execQuery(sql,pars);\n");
        csb.append("            if(rs " + this.compareScript + "){\n");
        csb.append("                return true;\n");
        csb.append("            }else{\n");
        csb.append("                return false;\n");
        csb.append("            }\n");
        csb.append("        }\n");
        String  ol = blockText.getLogic();
        String logic ="";
        if(StringUtils.isNotBlank(ol)){
            if(ol.trim().equals("并且") || ol.equals("and")){
                logic = "&&";
            }else if(ol.trim().equals("或者") || ol.equals("||")){
                logic="||";
            }
        }

        blockCode.setName(funcName);
        blockCode.setLogic(logic);
        blockCode.setCode(csb.toString());
        blockCode.setSql(this.sql);
        return blockCode;
    }
    private boolean isSplit(String c) {
        return Arrays.stream(splits).filter(k -> k.equals(c)).findFirst().isPresent();
    }

    private MatchResult getMatchResult(String skw,int idx){
        Keyword kw = findKeyword(skw);
        MatchResult mr = new MatchResult();
        if(kw == null){
            mr.setInput(skw);
            mr.setOutput(skw);
            mr.setIdx(idx);
        }else{
            mr = matchByKeyword(skw,kw,idx);
        }
        return  mr;
    }

    private MatchResult matchByKeyword(String skw,Keyword kw,int idx){
        MatchResult mr = new MatchResult();
        switch (kw.getKeywordType()){
            case SYNTAX:
                if(kw.getName().equals("包含")){
                    return  matchLike(skw,kw,idx);
                }else{
                    mr.setIdx(idx);
                    mr.setInput(skw);
                    mr.setOutput(kw.getCode());
                }
                break;
            case FUNCTION:

                if(kw.getCode().trim().equals("count")){
                    mr = matchCount(skw,kw,idx);
                }else{
                    mr.setIdx(idx);
                    mr.setInput(skw);
                    mr.setOutput(kw.getCode());
                    break;
                }
                break;
            case FIELD_NAME:
                mr.setIdx(idx);
                mr.setInput(skw);
                mr.setOutput(kw.getField());
                break;
            case TIME_RANGE_FUNCTION:
                mr = this.matchTimeRange(skw,kw,idx);

        }
        return mr;
    }

    private Keyword findKeyword(String skw) {
        List<Keyword> kws = keywords.stream().filter(k -> k.getName().equals(skw)).collect(Collectors.toList());

        if (kws.size() == 0) {
            return null;
        } else if (kws.size() == 1) {
            return kws.get(0);
        } else if (kws.size() > 1) {
            //不同表名的字段名可能是重复的，需要再根据表名做判断
            String tab = this.tableKeyword.getTable();
            List<Keyword> kws2 = kws.stream().filter(k -> k.getTable().equals(this.tableKeyword.getTable())).collect(Collectors.toList());
            if (kws2.size() == 0) {
                throw new RuntimeException("字段[" + skw + "]在数据源[" + this.tableKeyword.getTable() + "]中不存在");
            } else if (kws2.size() > 1) {
                throw new RuntimeException("字段[" + skw + "]在数据源[" + this.tableKeyword.getTable() + "]中存在多个同名字段");
            } else if (kws2.size() == 1) {
                return kws2.get(0);
            }
        }

        throw new RuntimeException("读取字段名称时发生错误");
    }

    private MatchResult matchLike(String skw,Keyword lkw,int idx){
        String sub = blockText.getExpression().substring(idx);

        String regex =  lkw.getRegex();

        Pattern pattern = Pattern.compile(regex);
        Matcher matcher = pattern.matcher(sub);

        String output="";

        MatchResult rs = new MatchResult();
        if(matcher.find()){
            int e = matcher.end();
            String r = matcher.group(1);
            String out = "like '%"+ r + "%'";
            rs.setInput(skw);
            rs.setOutput(out);
            rs.setIdx(idx + e);
        }else {
            throw new RuntimeException("没有找到[包含]操作符号对应的参数");
        }

        return rs;
    }

    private MatchResult matchCount(String skw,Keyword ckw,int idx){
        String resultText = this.blockText.getResult();

        String regex = ckw.getName() + ckw.getRegex();

        Pattern p = Pattern.compile(regex);
        Matcher matcher = p.matcher(resultText);

        MatchResult mr = new MatchResult();

        if(matcher.find()){
            String f = matcher.group(0);
            int e = matcher.end();
            Keyword fkw = this.keywords.stream().filter(k->k.getName().equals(f)
                    && k.getKeywordType().equals(KeywordType.FIELD_NAME)
                    && k.getTable().equals(this.tableKeyword.getTable()))
                    .findFirst().orElse(null);
            if(fkw==null){
                throw new RuntimeException("没有在"+ this.tableKeyword.getTable()+"中找到对应的字段定义");
            }
            mr.setOutput("count("+ fkw.getField() + ")");

            mr.setIdx(idx +e );
            mr.setInput(skw);
        }else{
            mr.setIdx(idx);
            mr.setOutput("count(*)");
            mr.setInput(skw);
        }

        return mr;
    }

    private  MatchResult matchTimeRange(String skw, Keyword tkw,int idx){

        String sub = this.blockText.getExpression().substring(idx);

        String regex =  tkw.getRegex();

        Pattern pattern = Pattern.compile(regex);
        Matcher matcher = pattern.matcher(sub);

        String output="";

        MatchResult rs = new MatchResult();

        Integer day=0;

        if(matcher.find()){
            int e = matcher.end();
            Keyword  timeRangeKw = this.keywords.stream().filter(k->k.getKeywordType().equals(KeywordType.TIME_RANGE)).findFirst().orElse(null);
            String m = matcher.group(0);

            Pattern vp = Pattern.compile(timeRangeKw.getRegex());

            Matcher vm = vp.matcher(m);

            if(vm.find()){
                String d = vm.group(0);
                day = Integer.valueOf(d);

                rs.setOutput(tableKeyword.getTableTimeField() + " between :beginTime and :endTime");
                rs.setInput(skw);
                rs.setIdx(idx + e);

                //有几个问号，就要有几个对应的参数
                Parameter p1 = new Parameter();
                p1.setName("endTime");
                p1.setScript("LocalDateTime.now()");
                p1.setTypeName("LocalDateTime");
                Parameter p2 = new Parameter();
                p2.setName("beginTime");
                p2.setScript("LocalDateTime.now().minusDays("+day+ ")");
                p2.setTypeName("LocalDateTime");
                parameters.put(p1.getName(),p1);
                parameters.put(p2.getName(),p2);

            }else{
                throw new RuntimeException("没有在日期范围中找到指定天数");
            }
        }else{
            throw  new RuntimeException("没有找到日期范围的值");
        }

        return rs;
    }

}