package com.purui.prims.chat.core.parser.aggregate;

import com.alibaba.fastjson.JSON;
import com.purui.prims.headless.api.pojo.enums.AggOption;
import com.purui.prims.headless.core.translator.calcite.CalciteQueryParser;
import com.purui.prims.headless.core.pojo.QueryStatement;
import org.junit.jupiter.api.Test;
import org.testng.Assert;

public class CalciteSqlParserTest {

    @Test
    public void testCalciteSqlParser() throws Exception {
        String json = "{\n"
                + "    \"dataSetId\": 1,\n"
                + "    \"sql\": \"\",\n"
                + "    \"sourceId\": \"\",\n"
                + "    \"errMsg\": \"\",\n"
                + "    \"metricQueryParam\": {\n"
                + "        \"metrics\": [\n"
                + "            \"pv\"\n"
                + "        ],\n"
                + "        \"dimensions\": [\n"
                + "            \"sys_imp_date\"\n"
                + "        ],\n"
                + "        \"nativeQuery\": false\n"
                + "    },\n"
                + "    \"status\": 0,\n"
                + "    \"isS2SQL\": false,\n"
                + "    \"enableOptimize\": true,\n"
                + "    \"minMaxTime\": {\n"
                + "        \"left\": \"sys_imp_date\",\n"
                + "        \"middle\": \"2024-03-24\",\n"
                + "        \"right\": \"2024-03-18\"\n"
                + "    },\n"
                + "    \"dataSetSql\": \"SELECT sys_imp_date, SUM(pv) AS pv FROM t_1 WHERE "
                + "sys_imp_date >= '2024-03-18' AND sys_imp_date <= '2024-03-24' GROUP BY sys_imp_date LIMIT 365\",\n"
                + "    \"dataSetAlias\": \"t_1\",\n"
                + "    \"dataSetSimplifySql\": \"\",\n"
                + "    \"enableLimitWrapper\": false,\n"
                + "    \"semanticModel\": {\n"
                + "        \"schemaKey\": \"VIEW_1\",\n"
                + "        \"metrics\": [\n"
                + "            {\n"
                + "                \"name\": \"pv\",\n"
                + "                \"owners\": [\n"
                + "                    \"admin\"\n"
                + "                ],\n"
                + "                \"type\": \"ATOMIC\",\n"
                + "                \"metricTypeParams\": {\n"
                + "                    \"measures\": [\n"
                + "                        {\n"
                + "                            \"name\": \"pr_pv_uv_statis_pv\",\n"
                + "                            \"agg\": \"SUM\",\n"
                + "                            \"constraint\": \"\"\n"
                + "                        }\n"
                + "                    ],\n"
                + "                    \"isFieldMetric\": false,\n"
                + "                    \"expr\": \"pr_pv_uv_statis_pv\"\n"
                + "                }\n"
                + "            },\n"
                + "            {\n"
                + "                \"name\": \"uv\",\n"
                + "                \"owners\": [\n"
                + "                    \"admin\"\n"
                + "                ],\n"
                + "                \"type\": \"DERIVED\",\n"
                + "                \"metricTypeParams\": {\n"
                + "                    \"measures\": [\n"
                + "                        {\n"
                + "                            \"name\": \"user_id\",\n"
                + "                            \"expr\": \"user_id\"\n"
                + "                        }\n"
                + "                    ],\n"
                + "                    \"isFieldMetric\": true,\n"
                + "                    \"expr\": \"user_id\"\n"
                + "                }\n"
                + "            },\n"
                + "            {\n"
                + "                \"name\": \"pv_avg\",\n"
                + "                \"owners\": [\n"
                + "                    \"admin\"\n"
                + "                ],\n"
                + "                \"type\": \"DERIVED\",\n"
                + "                \"metricTypeParams\": {\n"
                + "                    \"measures\": [\n"
                + "                        {\n"
                + "                            \"name\": \"pv\",\n"
                + "                            \"expr\": \"pv\"\n"
                + "                        },\n"
                + "                        {\n"
                + "                            \"name\": \"uv\",\n"
                + "                            \"expr\": \"uv\"\n"
                + "                        }\n"
                + "                    ],\n"
                + "                    \"isFieldMetric\": true,\n"
                + "                    \"expr\": \"pv\"\n"
                + "                }\n"
                + "            },\n"
                + "            {\n"
                + "                \"name\": \"stay_hours\",\n"
                + "                \"owners\": [\n"
                + "                    \"admin\"\n"
                + "                ],\n"
                + "                \"type\": \"ATOMIC\",\n"
                + "                \"metricTypeParams\": {\n"
                + "                    \"measures\": [\n"
                + "                        {\n"
                + "                            \"name\": \"pr_stay_time_statis_stay_hours\",\n"
                + "                            \"agg\": \"SUM\",\n"
                + "                            \"constraint\": \"\"\n"
                + "                        }\n"
                + "                    ],\n"
                + "                    \"isFieldMetric\": false,\n"
                + "                    \"expr\": \"pr_stay_time_statis_stay_hours\"\n"
                + "                }\n"
                + "            }\n"
                + "        ],\n"
                + "        \"datasourceMap\": {\n"
                + "            \"user_department\": {\n"
                + "                \"id\": 1,\n"
                + "                \"name\": \"user_department\",\n"
                + "                \"sourceId\": 1,\n"
                + "                \"type\": \"h2\",\n"
                + "                \"sqlQuery\": \"select user_name,department from pr_user_department\",\n"
                + "                \"identifiers\": [\n"
                + "                    {\n"
                + "                        \"name\": \"user_name\",\n"
                + "                        \"type\": \"primary\"\n"
                + "                    }\n"
                + "                ],\n"
                + "                \"dimensions\": [\n"
                + "                    {\n"
                + "                        \"name\": \"department\",\n"
                + "                        \"type\": \"categorical\",\n"
                + "                        \"expr\": \"department\",\n"
                + "                        \"dimensionTimeTypeParams\": {\n"
                + "                        },\n"
                + "                        \"dataType\": \"UNKNOWN\",\n"
                + "                        \"bizName\": \"department\"\n"
                + "                    }\n"
                + "                ],\n"
                + "                \"measures\": [\n"
                + "                    {\n"
                + "                        \"name\": \"user_department_internal_cnt\",\n"
                + "                        \"agg\": \"count\",\n"
                + "                        \"expr\": \"user_name\"\n"
                + "                    },\n"
                + "                    {\n"
                + "                        \"name\": \"user_name\",\n"
                + "                        \"agg\": \"\",\n"
                + "                        \"expr\": \"user_name\"\n"
                + "                    },\n"
                + "                    {\n"
                + "                        \"name\": \"department\",\n"
                + "                        \"agg\": \"\",\n"
                + "                        \"expr\": \"department\"\n"
                + "                    }\n"
                + "                ],\n"
                + "                \"aggTime\": \"none\"\n"
                + "            },\n"
                + "            \"pr_pv_uv_statis\": {\n"
                + "                \"id\": 2,\n"
                + "                \"name\": \"pr_pv_uv_statis\",\n"
                + "                \"sourceId\": 1,\n"
                + "                \"type\": \"h2\",\n"
                + "                \"sqlQuery\": \"SELECT imp_date, user_name, page, 1 as pv, user_name as user_id "
                + "FROM pr_pv_uv_statis\",\n"
                + "                \"identifiers\": [\n"
                + "                    {\n"
                + "                        \"name\": \"user_name\",\n"
                + "                        \"type\": \"primary\"\n"
                + "                    }\n"
                + "                ],\n"
                + "                \"dimensions\": [\n"
                + "                    {\n"
                + "                        \"name\": \"imp_date\",\n"
                + "                        \"type\": \"time\",\n"
                + "                        \"expr\": \"imp_date\",\n"
                + "                        \"dimensionTimeTypeParams\": {\n"
                + "                            \"isPrimary\": \"true\",\n"
                + "                            \"timeGranularity\": \"day\"\n"
                + "                        },\n"
                + "                        \"dataType\": \"UNKNOWN\",\n"
                + "                        \"bizName\": \"imp_date\"\n"
                + "                    },\n"
                + "                    {\n"
                + "                        \"name\": \"page\",\n"
                + "                        \"type\": \"categorical\",\n"
                + "                        \"expr\": \"page\",\n"
                + "                        \"dimensionTimeTypeParams\": {\n"
                + "                        },\n"
                + "                        \"dataType\": \"UNKNOWN\",\n"
                + "                        \"bizName\": \"page\"\n"
                + "                    },\n"
                + "                    {\n"
                + "                        \"name\": \"sys_imp_date\",\n"
                + "                        \"type\": \"time\",\n"
                + "                        \"expr\": \"imp_date\",\n"
                + "                        \"dimensionTimeTypeParams\": {\n"
                + "                            \"isPrimary\": \"true\",\n"
                + "                            \"timeGranularity\": \"day\"\n"
                + "                        },\n"
                + "                        \"dataType\": \"UNKNOWN\",\n"
                + "                        \"bizName\": \"sys_imp_date\"\n"
                + "                    },\n"
                + "                    {\n"
                + "                        \"name\": \"sys_imp_week\",\n"
                + "                        \"type\": \"time\",\n"
                + "                        \"expr\": \"DATE_TRUNC('week',imp_date)\",\n"
                + "                        \"dimensionTimeTypeParams\": {\n"
                + "                            \"isPrimary\": \"false\",\n"
                + "                            \"timeGranularity\": \"week\"\n"
                + "                        },\n"
                + "                        \"dataType\": \"UNKNOWN\",\n"
                + "                        \"bizName\": \"sys_imp_week\"\n"
                + "                    },\n"
                + "                    {\n"
                + "                        \"name\": \"sys_imp_month\",\n"
                + "                        \"type\": \"time\",\n"
                + "                        \"expr\": \"FORMATDATETIME(PARSEDATETIME"
                + "(imp_date, 'yyyy-MM-dd'),'yyyy-MM') \",\n"
                + "                        \"dimensionTimeTypeParams\": {\n"
                + "                            \"isPrimary\": \"false\",\n"
                + "                            \"timeGranularity\": \"month\"\n"
                + "                        },\n"
                + "                        \"dataType\": \"UNKNOWN\",\n"
                + "                        \"bizName\": \"sys_imp_month\"\n"
                + "                    }\n"
                + "                ],\n"
                + "                \"measures\": [\n"
                + "                    {\n"
                + "                        \"name\": \"pr_pv_uv_statis_pv\",\n"
                + "                        \"agg\": \"SUM\",\n"
                + "                        \"expr\": \"pv\"\n"
                + "                    },\n"
                + "                    {\n"
                + "                        \"name\": \"pr_pv_uv_statis_user_id\",\n"
                + "                        \"agg\": \"SUM\",\n"
                + "                        \"expr\": \"user_id\"\n"
                + "                    },\n"
                + "                    {\n"
                + "                        \"name\": \"pr_pv_uv_statis_internal_cnt\",\n"
                + "                        \"agg\": \"count\",\n"
                + "                        \"expr\": \"user_name\"\n"
                + "                    },\n"
                + "                    {\n"
                + "                        \"name\": \"user_name\",\n"
                + "                        \"agg\": \"\",\n"
                + "                        \"expr\": \"user_name\"\n"
                + "                    },\n"
                + "                    {\n"
                + "                        \"name\": \"imp_date\",\n"
                + "                        \"agg\": \"\",\n"
                + "                        \"expr\": \"imp_date\"\n"
                + "                    },\n"
                + "                    {\n"
                + "                        \"name\": \"page\",\n"
                + "                        \"agg\": \"\",\n"
                + "                        \"expr\": \"page\"\n"
                + "                    },\n"
                + "                    {\n"
                + "                        \"name\": \"pv\",\n"
                + "                        \"agg\": \"\",\n"
                + "                        \"expr\": \"pv\"\n"
                + "                    },\n"
                + "                    {\n"
                + "                        \"name\": \"user_id\",\n"
                + "                        \"agg\": \"\",\n"
                + "                        \"expr\": \"user_id\"\n"
                + "                    }\n"
                + "                ],\n"
                + "                \"aggTime\": \"day\"\n"
                + "            },\n"
                + "            \"pr_stay_time_statis\": {\n"
                + "                \"id\": 3,\n"
                + "                \"name\": \"pr_stay_time_statis\",\n"
                + "                \"sourceId\": 1,\n"
                + "                \"type\": \"h2\",\n"
                + "                \"sqlQuery\": \"select imp_date,user_name,stay_hours"
                + ",page from pr_stay_time_statis\",\n"
                + "                \"identifiers\": [\n"
                + "                    {\n"
                + "                        \"name\": \"user_name\",\n"
                + "                        \"type\": \"primary\"\n"
                + "                    }\n"
                + "                ],\n"
                + "                \"dimensions\": [\n"
                + "                    {\n"
                + "                        \"name\": \"imp_date\",\n"
                + "                        \"type\": \"time\",\n"
                + "                        \"expr\": \"imp_date\",\n"
                + "                        \"dimensionTimeTypeParams\": {\n"
                + "                            \"isPrimary\": \"true\",\n"
                + "                            \"timeGranularity\": \"day\"\n"
                + "                        },\n"
                + "                        \"dataType\": \"UNKNOWN\",\n"
                + "                        \"bizName\": \"imp_date\"\n"
                + "                    },\n"
                + "                    {\n"
                + "                        \"name\": \"page\",\n"
                + "                        \"type\": \"categorical\",\n"
                + "                        \"expr\": \"page\",\n"
                + "                        \"dimensionTimeTypeParams\": {\n"
                + "                        },\n"
                + "                        \"dataType\": \"UNKNOWN\",\n"
                + "                        \"bizName\": \"page\"\n"
                + "                    },\n"
                + "                    {\n"
                + "                        \"name\": \"sys_imp_date\",\n"
                + "                        \"type\": \"time\",\n"
                + "                        \"expr\": \"imp_date\",\n"
                + "                        \"dimensionTimeTypeParams\": {\n"
                + "                            \"isPrimary\": \"true\",\n"
                + "                            \"timeGranularity\": \"day\"\n"
                + "                        },\n"
                + "                        \"dataType\": \"UNKNOWN\",\n"
                + "                        \"bizName\": \"sys_imp_date\"\n"
                + "                    },\n"
                + "                    {\n"
                + "                        \"name\": \"sys_imp_week\",\n"
                + "                        \"type\": \"time\",\n"
                + "                        \"expr\": \"DATE_TRUNC('week',imp_date)\",\n"
                + "                        \"dimensionTimeTypeParams\": {\n"
                + "                            \"isPrimary\": \"false\",\n"
                + "                            \"timeGranularity\": \"week\"\n"
                + "                        },\n"
                + "                        \"dataType\": \"UNKNOWN\",\n"
                + "                        \"bizName\": \"sys_imp_week\"\n"
                + "                    },\n"
                + "                    {\n"
                + "                        \"name\": \"sys_imp_month\",\n"
                + "                        \"type\": \"time\",\n"
                + "                        \"expr\": \"FORMATDATETIME(PARSEDATETIME"
                + "(imp_date, 'yyyy-MM-dd'),'yyyy-MM') \",\n"
                + "                        \"dimensionTimeTypeParams\": {\n"
                + "                            \"isPrimary\": \"false\",\n"
                + "                            \"timeGranularity\": \"month\"\n"
                + "                        },\n"
                + "                        \"dataType\": \"UNKNOWN\",\n"
                + "                        \"bizName\": \"sys_imp_month\"\n"
                + "                    }\n"
                + "                ],\n"
                + "                \"measures\": [\n"
                + "                    {\n"
                + "                        \"name\": \"pr_stay_time_statis_stay_hours\",\n"
                + "                        \"agg\": \"SUM\",\n"
                + "                        \"expr\": \"stay_hours\"\n"
                + "                    },\n"
                + "                    {\n"
                + "                        \"name\": \"pr_stay_time_statis_internal_cnt\",\n"
                + "                        \"agg\": \"count\",\n"
                + "                        \"expr\": \"user_name\"\n"
                + "                    },\n"
                + "                    {\n"
                + "                        \"name\": \"user_name\",\n"
                + "                        \"agg\": \"\",\n"
                + "                        \"expr\": \"user_name\"\n"
                + "                    },\n"
                + "                    {\n"
                + "                        \"name\": \"imp_date\",\n"
                + "                        \"agg\": \"\",\n"
                + "                        \"expr\": \"imp_date\"\n"
                + "                    },\n"
                + "                    {\n"
                + "                        \"name\": \"page\",\n"
                + "                        \"agg\": \"\",\n"
                + "                        \"expr\": \"page\"\n"
                + "                    },\n"
                + "                    {\n"
                + "                        \"name\": \"stay_hours\",\n"
                + "                        \"agg\": \"\",\n"
                + "                        \"expr\": \"stay_hours\"\n"
                + "                    }\n"
                + "                ],\n"
                + "                \"aggTime\": \"day\"\n"
                + "            }\n"
                + "        },\n"
                + "        \"dimensionMap\": {\n"
                + "            \"user_department\": [\n"
                + "                {\n"
                + "                    \"name\": \"department\",\n"
                + "                    \"owners\": \"admin\",\n"
                + "                    \"type\": \"categorical\",\n"
                + "                    \"expr\": \"department\",\n"
                + "                    \"dimensionTimeTypeParams\": {\n"
                + "                    },\n"
                + "                    \"dataType\": \"UNKNOWN\",\n"
                + "                    \"bizName\": \"department\"\n"
                + "                }\n"
                + "            ],\n"
                + "            \"pr_pv_uv_statis\": [\n"
                + "            ],\n"
                + "            \"pr_stay_time_statis\": [\n"
                + "                {\n"
                + "                    \"name\": \"page\",\n"
                + "                    \"owners\": \"admin\",\n"
                + "                    \"type\": \"categorical\",\n"
                + "                    \"expr\": \"page\",\n"
                + "                    \"dimensionTimeTypeParams\": {\n"
                + "                    },\n"
                + "                    \"dataType\": \"UNKNOWN\",\n"
                + "                    \"bizName\": \"page\"\n"
                + "                }\n"
                + "            ]\n"
                + "        },\n"
                + "        \"materializationList\": [\n"
                + "        ],\n"
                + "        \"joinRelations\": [\n"
                + "            {\n"
                + "                \"id\": 1,\n"
                + "                \"left\": \"user_department\",\n"
                + "                \"right\": \"pr_pv_uv_statis\",\n"
                + "                \"joinType\": \"left join\",\n"
                + "                \"joinCondition\": [\n"
                + "                    {\n"
                + "                        \"left\": \"user_name\",\n"
                + "                        \"middle\": \"=\",\n"
                + "                        \"right\": \"user_name\"\n"
                + "                    }\n"
                + "                ]\n"
                + "            },\n"
                + "            {\n"
                + "                \"id\": 2,\n"
                + "                \"left\": \"user_department\",\n"
                + "                \"right\": \"pr_stay_time_statis\",\n"
                + "                \"joinType\": \"left join\",\n"
                + "                \"joinCondition\": [\n"
                + "                    {\n"
                + "                        \"left\": \"user_name\",\n"
                + "                        \"middle\": \"=\",\n"
                + "                        \"right\": \"user_name\"\n"
                + "                    }\n"
                + "                ]\n"
                + "            }\n"
                + "        ],\n"
                + "        \"database\": {\n"
                + "            \"id\": 1,\n"
                + "            \"name\": \"数据实例\",\n"
                + "            \"description\": \"样例数据库实例\",\n"
                + "            \"url\": \"jdbc:h2:mem:semantic;DATABASE_TO_UPPER=false\",\n"
                + "            \"username\": \"root\",\n"
                + "            \"password\": \"semantic\",\n"
                + "            \"type\": \"h2\",\n"
                + "            \"connectInfo\": {\n"
                + "                \"url\": \"jdbc:h2:mem:semantic;DATABASE_TO_UPPER=false\",\n"
                + "                \"userName\": \"root\",\n"
                + "                \"password\": \"semantic\"\n"
                + "            },\n"
                + "            \"admins\": [\n"
                + "            ],\n"
                + "            \"viewers\": [\n"
                + "            ],\n"
                + "            \"createdBy\": \"admin\",\n"
                + "            \"updatedBy\": \"admin\",\n"
                + "            \"createdAt\": 1711367511146,\n"
                + "            \"updatedAt\": 1711367511146\n"
                + "        }\n"
                + "    }\n"
                + "}";
        QueryStatement queryStatement = JSON.parseObject(json, QueryStatement.class);
        CalciteQueryParser calciteSqlParser = new CalciteQueryParser();
        calciteSqlParser.parse(queryStatement, AggOption.DEFAULT);
        Assert.assertEquals(queryStatement.getSql().trim().replaceAll("\\s+", ""),
                "SELECT`imp_date`AS`sys_imp_date`,SUM(1)AS`pv`"
                + "FROM"
                + "`pr_pv_uv_statis`"
                + "GROUPBY`imp_date`,`imp_date`");
    }
}
