package com.elab.es.data;//package com.elab.es.data;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.parser.Feature;
import com.elab.es.model.Posts;
import io.github.iamazy.elasticsearch.dsl.sql.ElasticSql2DslParser;
import io.github.iamazy.elasticsearch.dsl.sql.model.ElasticSqlParseResult;
import org.elasticsearch.client.RestHighLevelClient;
import org.junit.Test;
import org.zxp.esclientrhl.repository.ElasticsearchTemplateImpl;

import java.sql.*;
import java.util.function.Supplier;

/**
 * @Module TODO
 * @Description TODO
 * @Author liukaixiong
 * @Date 2021/1/29 15:34
 */
public class ElasticSQLAPITest {

    static final String JDBC_DRIVER = "io.github.iamazy.elasticsearch.dsl.jdbc.ElasticDriver";
    static final String DB_URL = "jdbc:es://localhost:9200/posts?useSSL=false&mode=single";
    ElasticSql2DslParser parser = new ElasticSql2DslParser();
    private RestHighLevelClient restHighLevelClient;
    private ElasticsearchTemplateImpl<Posts, String> elasticsearchTemplate;

//    @Before
//    public void before() throws Exception {
////        client = new RestHighLevelClient(
////                RestClient.builder(
////                        new HttpHost("localhost", 9200, "http")));
////                        new HttpHost("192.168.0.24", 9200, "http")));
//        String host = "localhost";
//        String port = "9300";
//        TransportAddress transportAddress = new TransportAddress(InetAddress.getByName(host), Integer.parseInt(port));
//        Settings settings = Settings.builder().put("client.transport.ignore_cluster_name", true).build();
//    }

    @Test
    public void connect() {
        Connection conn = null;
        Statement stmt = null;
        try {
            Class.forName(JDBC_DRIVER);
            conn = DriverManager.getConnection(DB_URL, null, null);
            stmt = conn.createStatement();
            String sql;
            sql = "SELECT postDate, id, message, user FROM posts WHERE id = '1'";
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                String id = rs.getString("_id");
                String name = rs.getString("name");
                String url = rs.getString("list");
                System.out.print("ID: " + id);
                System.out.print(", 站点名称: " + name);
                System.out.print(", 站点 URL: " + url);
                System.out.print("\n");
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception se) {
            se.printStackTrace();
        } finally {
            try {
                if (stmt != null) stmt.close();
            } catch (SQLException ignored) {
            }
            try {
                if (conn != null) conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
    }


    @Test
    public void parse() {
        long now = System.currentTimeMillis();
        String sql = "select name,^h!age,h!gender from student where ((a in (1,2,3,4)) and has_parent(apple,bb~='fruit')) and c=1 and (location.coordinate = [40.0,30.0] and distance = '1km' or t='bb') limit 2,5";

        ElasticSqlParseResult parseResult = parser.parse(sql);
        System.out.println(parseResult.toPrettyDsl(parseResult.getSearchRequest()));
        System.out.println(System.currentTimeMillis() - now);
    }

    @Test
    public void nested() {
        long now = System.currentTimeMillis();
        String sql = "select name from student where (([class1, age>1 and [class1.class2, name='hhha']] and c=1) or b~=='hhhhh') and query by 'apppple' limit 2,5";

        ElasticSqlParseResult parseResult = parser.parse(sql);
        System.out.println(parseResult.toPrettyDsl(parseResult.getSearchRequest()));
        System.out.println(System.currentTimeMillis() - now);
    }

    @Test
    public void nested2() {
        long now = System.currentTimeMillis();
        String sql = "select * from class where [student,student.name is null]";

        ElasticSqlParseResult parseResult = parser.parse(sql);
        System.out.println(parseResult.toPrettyDsl(parseResult.getSearchRequest()));
        System.out.println(System.currentTimeMillis() - now);
    }

    @Test
    public void exists() {
        long now = System.currentTimeMillis();
        String sql = "select * from student where name is not null and age >15";

        ElasticSqlParseResult parseResult = parser.parse(sql);
        parseResult.toPrettyDsl(parseResult.getSearchRequest());
        System.out.println(parseResult.toPrettyDsl(parseResult.getSearchRequest()));
        System.out.println(System.currentTimeMillis() - now);
    }

    @Test
    public void group() {
        long now = System.currentTimeMillis();
        String sql = "select name from student aggregate by terms(name,1)>(terms(aa,2),terms(bb,3)>(terms(cc,4))),terms(age,10)>(terms(weight,10))";

        ElasticSqlParseResult parseResult = parser.parse(sql);
        System.out.println(parseResult.toPrettyDsl(parseResult.getSearchRequest()));
        System.out.println(System.currentTimeMillis() - now);
    }

    @Test
    public void nestedAgg() {
        long now = System.currentTimeMillis();
        String sql = "select name from student aggregate by terms(name,1)>(terms(aa,2),[apple,cardinality(ip),terms(aaa,1)>(terms(bb,1),terms(cc,10)>(terms(hh,3),avg(age)),terms(vv,1))]) limit 2,5";

        ElasticSqlParseResult parseResult = parser.parse(sql);
        System.out.println(parseResult.toPrettyDsl(parseResult.getSearchRequest()));
        System.out.println(System.currentTimeMillis() - now);
    }

    @Test
    public void distinct() {
        String sql = "select distinct name from student where a like '%appl%' limit 2,5";

        ElasticSqlParseResult parseResult = time("1", () -> {
            return parser.parse(sql);
        });

        time("2", () -> {
            System.out.println(parseResult.toPrettyDsl(parseResult.getSearchRequest()));
            return null;
        });

    }


    public <T> T time(String key, Supplier<T> supplier) {
        long start = System.currentTimeMillis();
        T o = supplier.get();
        long time = System.currentTimeMillis() - start;
        System.out.println("--->>>>" + time);
        return o;
    }

    @Test
    public void betweenAnd() {
        long now = System.currentTimeMillis();
        String sql = "select distinct name from student where a between 'dfsfsd' and 'ffff' aggregate by [apple,terms(a,1)],terms(b,1) limit 2,5";

        ElasticSqlParseResult parseResult = parser.parse(sql);
        System.out.println(parseResult.toPrettyDsl(parseResult.getSearchRequest()));
        System.out.println(System.currentTimeMillis() - now);
    }

    @Test
    public void betweenAnd2() {
        long now = System.currentTimeMillis();
        String sql = "select distinct name from student where a ranged in (1,2] aggregate by [apple,terms(a,1)],terms(b,1) limit 2,5";

        ElasticSqlParseResult parseResult = parser.parse(sql);
        System.out.println(parseResult.toPrettyDsl(parseResult.getSearchRequest()));
        System.out.println(System.currentTimeMillis() - now);
    }

    @Test
    public void agg1() {
        long now = System.currentTimeMillis();
        String sql = "select distinct h!name,h!age from student aggregate by [apple,terms(a,1)],terms(bb,2),cardinality(ip) limit 2,5";

        ElasticSqlParseResult parseResult = parser.parse(sql);
        System.out.println(parseResult.toPrettyDsl(parseResult.getSearchRequest()));
        System.out.println(System.currentTimeMillis() - now);
    }

    @Test
    public void agg2() {
        long now = System.currentTimeMillis();
        String sql = "select distinct name from student aggregate by terms(bb,2)>([apple,terms(a,1)]),cardinality(ip) limit 2,5";

        ElasticSqlParseResult parseResult = parser.parse(sql);
        System.out.println(parseResult.toPrettyDsl(parseResult.getSearchRequest()));
        System.out.println(System.currentTimeMillis() - now);
    }

    @Test
    public void agg3() {
        long now = System.currentTimeMillis();
        String sql = "select distinct name from student aggregate by terms(bb,2)>([apple,terms(a,1)],cardinality(ip)) limit 2,5";

        ElasticSqlParseResult parseResult = parser.parse(sql);
        System.out.println(parseResult.toPrettyDsl(parseResult.getSearchRequest()));
        System.out.println(System.currentTimeMillis() - now);
    }

    @Test
    public void agg4() {
        long now = System.currentTimeMillis();
        String sql = "select * from student aggregate by terms(bb,2)>(terms(cc,2)),[apple,terms(a,1)],cardinality(ip) limit 2,5";

        ElasticSqlParseResult parseResult = parser.parse(sql);
        System.out.println(parseResult.toPrettyDsl(parseResult.getSearchRequest()));
        System.out.println(System.currentTimeMillis() - now);
    }

    @Test
    public void compositeAgg() {

    }

    @Test
    public void desc() {
        long now = System.currentTimeMillis();
        String sql = "describe student/xiaoming";

        ElasticSqlParseResult parseResult = parser.parse(sql);
        System.out.println(parseResult.getFieldMappingsRequest());
        System.out.println(System.currentTimeMillis() - now);
    }

    @Test
    public void functionScore() {
        long now = System.currentTimeMillis();
        String sql = "select * from student where h!age> 10 and h!weight between 80 and 90 and color = 'red' func_score high > 160 && name ='小明' aggregate by terms(name,10),terms(age,10)>(cardinality(clazz))";

        ElasticSqlParseResult parseResult = parser.parse(sql);
        System.out.println(parseResult.toPrettyDsl(parseResult.getSearchRequest()));
        System.out.println(System.currentTimeMillis() - now);
    }

    @Test
    public void multiMatch() {
        long now = System.currentTimeMillis();
        String sql = "select * from student where (name,age) ~= 'hahah'";

        ElasticSqlParseResult parseResult = parser.parse(sql);
        System.out.println(parseResult.toPrettyDsl(parseResult.getSearchRequest()));
        System.out.println(System.currentTimeMillis() - now);
    }

    @Test
    public void reindex() {
        long now = System.currentTimeMillis();
        String sql = "insert into port_info_v1 select * from port_info where a=1";

        ElasticSqlParseResult parseResult = parser.parse(sql);
        System.out.println(parseResult.getReindexRequest());
        System.out.println(System.currentTimeMillis() - now);
    }

    @Test
    public void highlighter() {
        long now = System.currentTimeMillis();
        String sql = "select * from student where (h!name,age) ~= 'hahah' and h!ab='haliluya'";

        ElasticSqlParseResult parseResult = parser.parse(sql);
        System.out.println(parseResult.toPrettyDsl(parseResult.getSearchRequest()));
        System.out.println(System.currentTimeMillis() - now);
    }

    @Test
    public void geoDistance() {
        long now = System.currentTimeMillis();
        String sql = "select * from student where location.coordinate=[40,30] and distance='100km' ";

        ElasticSqlParseResult parseResult = parser.parse(sql);
        System.out.println(parseResult.toPrettyDsl(parseResult.getSearchRequest()));
        System.out.println(System.currentTimeMillis() - now);
    }

    @Test
    public void geoBoundingBox() {
        long now = System.currentTimeMillis();
        String sql = "select * from student where location.coordinate between [30,70] and [20,50]";

        ElasticSqlParseResult parseResult = parser.parse(sql);
        System.out.println(parseResult.toPrettyDsl(parseResult.getSearchRequest()));
        System.out.println(System.currentTimeMillis() - now);
    }

    @Test
    public void geoPolygon() {
        long now = System.currentTimeMillis();
        String sql = "select * from student where location.coordinate in [[30.132,50],[40,60],[50,212]]";

        ElasticSqlParseResult parseResult = parser.parse(sql);
        System.out.println(parseResult.toPrettyDsl(parseResult.getSearchRequest()));
        System.out.println(System.currentTimeMillis() - now);
    }

    @Test
    public void geoShape() {
        long now = System.currentTimeMillis();
        String sql = "select * from student where location.coordinate within [[10,11],[21,22]] shaped as envelope";

        ElasticSqlParseResult parseResult = parser.parse(sql);
        System.out.println(parseResult.toPrettyDsl(parseResult.getSearchRequest()));
        System.out.println(System.currentTimeMillis() - now);
    }

    @Test
    public void geoShape2() {
        long now = System.currentTimeMillis();
        String sql = "select * from student where location.coordinate contains [[[10,10],[20,20]],[[11,11],[21,21]]] shaped as multilinestring";

        ElasticSqlParseResult parseResult = parser.parse(sql);
        System.out.println(parseResult.toPrettyDsl(parseResult.getSearchRequest()));
        System.out.println(System.currentTimeMillis() - now);
    }

    @Test
    public void geoShape3() {
        long now = System.currentTimeMillis();
        String sql = "select * from student where location.coordinate within [10,10] shaped as point";

        ElasticSqlParseResult parseResult = parser.parse(sql);
        System.out.println(parseResult.toPrettyDsl(parseResult.getSearchRequest()));
        System.out.println(System.currentTimeMillis() - now);
    }

    @Test
    public void geoShape4() {
        long now = System.currentTimeMillis();
        String sql = "select * from student where location.coordinate intersects [[[10,10],[20,20],[11,11],[10,10]]] shaped as polygon";

        ElasticSqlParseResult parseResult = parser.parse(sql);
        System.out.println(parseResult.toPrettyDsl(parseResult.getSearchRequest()));
        System.out.println(System.currentTimeMillis() - now);
    }

    @Test
    public void geoShape5() {
        long now = System.currentTimeMillis();
        String sql = "select * from student where location.coordinate disjoint [[[[10,10],[22,21],[11,11],[10,10]]],[[[10,10],[20,20],[11,11],[10,10]]]] shaped as multipolygon ";

        ElasticSqlParseResult parseResult = parser.parse(sql);
        System.out.println(parseResult.toPrettyDsl(parseResult.getSearchRequest()));
        System.out.println(System.currentTimeMillis() - now);
    }

    @Test
    public void geoShape6() {
        long now = System.currentTimeMillis();
        String sql = "select * from student where location.coordinate disjoint `{\"coordinates\": [[-155.52, 19.61], [-156.22, 20.74], [-157.97, 21.46]], \"type\": \"linestring\"}`";

        ElasticSqlParseResult parseResult = parser.parse(sql);
        System.out.println(parseResult.toPrettyDsl(parseResult.getSearchRequest()));
        System.out.println(System.currentTimeMillis() - now);
    }

    @Test
    public void groupBy() {
        long now = System.currentTimeMillis();
        String sql = "select max(name) as max_name,avg(height) as avg_height,avg(name) as avg_name,min(name) as min_name from student group by name,age,height";

        ElasticSqlParseResult parseResult = parser.parse(sql);
        System.out.println(parseResult.toPrettyDsl(parseResult.getSearchRequest()));
        System.out.println(System.currentTimeMillis() - now);
    }

    @Test
    public void disMax() {
        long now = System.currentTimeMillis();
        String sql = "select * from student dis_max age>11 || name='小米宁' and ( height>1.6 or fruit ='apple') || firstName is not null and tie_breaker=0.7";

        ElasticSqlParseResult parseResult = parser.parse(sql);
        System.out.println(parseResult.toPrettyDsl(parseResult.getSearchRequest()));
        System.out.println(System.currentTimeMillis() - now);
    }

    @Test
    public void trackTotalHits() {
        long now = System.currentTimeMillis();
        String sql = "select * from aaa where resCategory='port_info' aggregate by terms(deviceInfo.deviceType,10) limit 0 track total";

        ElasticSqlParseResult parseResult = parser.parse(sql);
        System.out.println(parseResult.toPrettyDsl(parseResult.getSearchRequest()));
        System.out.println(System.currentTimeMillis() - now);
    }

    @Test
    public void fuzzyLike() {
        long now = System.currentTimeMillis();
        String sql = "select * from aaa where resCategory fuzzy like'port_info'";

        ElasticSqlParseResult parseResult = parser.parse(sql);
        System.out.println(parseResult.toPrettyDsl(parseResult.getSearchRequest()));
        System.out.println(System.currentTimeMillis() - now);
    }

    @Test
    public void wildcardLike() {
        long now = System.currentTimeMillis();
        String sql = "select * from aaa where resCategory wildcard like 'port_info'";

        ElasticSqlParseResult parseResult = parser.parse(sql);
        System.out.println(parseResult.toPrettyDsl(parseResult.getSearchRequest()));
        System.out.println(System.currentTimeMillis() - now);
    }

    @Test
    public void prefixLike() {
        long now = System.currentTimeMillis();
        String sql = "select * from aaa where resCategory prefix like 'port_info'";

        ElasticSqlParseResult parseResult = parser.parse(sql);
        System.out.println(parseResult.toPrettyDsl(parseResult.getSearchRequest()));
        System.out.println(System.currentTimeMillis() - now);
    }

    @Test
    public void regexpLike() {
        long now = System.currentTimeMillis();
        String sql = "select * from aaa where resCategory regexp like 'port*'";

        ElasticSqlParseResult parseResult = parser.parse(sql);
        System.out.println(parseResult.toPrettyDsl(parseResult.getSearchRequest()));
        System.out.println(System.currentTimeMillis() - now);
    }

    @Test
    public void like() {
        long now = System.currentTimeMillis();
        String sql = "select * from aaa where resCategory like 'port*'";

        ElasticSqlParseResult parseResult = parser.parse(sql);
        System.out.println(parseResult.toPrettyDsl(parseResult.getSearchRequest()));
        System.out.println(System.currentTimeMillis() - now);
    }

    @Test
    public void not() {
        long now = System.currentTimeMillis();
        String sql = "select * from aaa where (not resCategory ~=== 'port*') or aa ~='apple'";

        ElasticSqlParseResult parseResult = parser.parse(sql);
        System.out.println(parseResult.toPrettyDsl(parseResult.getSearchRequest()));
        System.out.println(System.currentTimeMillis() - now);
    }

    @Test
    public void dataParse() {
        String json = " {\n" +
                "    mts:'1353358',\n" +
                "    province:'广东',\n" +
                "    catName:'中国移动',\n" +
                "    telString:'13533588275',\n" +
                "  areaVid:'30517',\n" +
                "  ispVid:'3236139',\n" +
                "  carrier:'广东移动'\n" +
                "}";
        JSONObject jsonObject = JSON.parseObject(json, Feature.AllowUnQuotedFieldNames);

        String s = jsonObject.toJSONString();

        jsonObject = JSON.parseObject(s, Feature.AllowUnQuotedFieldNames);

        System.out.println(jsonObject);

    }


}
