package com.basic.security.utils;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

public class SqlSplit {
    static char[] startEndQuotes = new char[]{'\'', '"', '`'};

    static Set<String> tableNameList = new HashSet<>();

    static {
        tableNameList.addAll(Arrays.asList(new String[]{
                "base_setting",
                "business",
                "business_apply_device",
                "business_person",
                "cluster",
                "cluster_node",
                "cluster_setting",
                "confirm",
                "device",
                "guest",
                "hint_door_access",
                "hint_recognize_message",
                "hint_sign_up",
                "id_card",
                "identity",
                "Log",
                "OfficeDevice",
                "org",
                "outdoor",
                "person",
                "person_identity",
                "temporary_person",
                "time",
                "user",
                "dbtablepersons",
                "dbTables",
                "visit"
        }));
    }

    public static boolean containsTable(String sql) {
        String tableName = getTableName(sql).trim();
        if (tableNameList.contains(tableName)) {
            return true;
        }
        return false;
    }

    public static String getTableName(String sql) {
        sql = sql.toLowerCase();
        sql = sql.trim();
        if (sql.startsWith("insert")) {
            return getTableNameFromInsertSql(sql);
        }
        if (sql.startsWith("delete")) {
            return getTableNameFromDeleteSql(sql);
        }
        if (sql.startsWith("update")) {
            return getTableNameFromUpdateSql(sql);
        }
        return sql;
    }

    public static boolean isInsert(String sql) {
        sql = sql.toLowerCase();
        sql = sql.trim();
        if (sql.startsWith("insert")) {
            return true;
        }
        return false;
    }

    public static boolean isDelete(String sql) {
        sql = sql.toLowerCase();
        sql = sql.trim();
        if (sql.startsWith("delete")) {
            return true;
        }
        return false;
    }

    public static Map<String, String> getRowDataMap(String sql) {
        sql = sql.toLowerCase();
        sql = sql.trim();
        if (sql.startsWith("insert")) {
            return insertToMap(sql);
        }
        if (sql.startsWith("delete")) {
            return deleteToMap(sql);
        }
        if (sql.startsWith("update")) {
            return updateToMap(sql);
        }
        return new HashMap<>();
    }

    public static String getRowSqlOperateType(String sql) {
        sql = sql.toLowerCase();
        sql = sql.trim();
        if (sql.startsWith("insert")) {
            return "insert";
        }
        if (sql.startsWith("delete")) {
            return "delete";
        }
        if (sql.startsWith("update")) {
            return "update";
        }
        return "";
    }

    public static boolean isUpdate(String sql) {
        sql = sql.toLowerCase();
        sql = sql.trim();
        if (sql.startsWith("update")) {
            return true;
        }
        return false;
    }

    public static String getTableNameFromDeleteSql(String sql) {
        String tableName = "";
        sql = sql.replace("\n", " ");
        sql = sql.replace("\r\n", " ");
        sql = sql.replace("  ", " ")
                .replace("  ", " ").replace("  ", " ").replace("  ", " ").replace("  ", " ")
        ;
        sql = sql.substring(0, sql.toLowerCase().indexOf(" where ")).trim().split(" ")[2];
        tableName = sql.substring(sql.indexOf(" ")).trim();
        return tableName;
    }

    public static String getTableNameFromUpdateSql(String sql) {
        String tableName = "";
        sql = sql.replace("\n", " ");
        sql = sql.replace("\r\n", " ");
        sql = sql.replace("  ", " ")
                .replace("  ", " ").replace("  ", " ").replace("  ", " ").replace("  ", " ")
        ;
        sql = sql.substring(0, sql.toLowerCase().indexOf(" set ")).trim().split(" ")[1];
        int index = sql.indexOf(" ");
        if (index != -1) {
            tableName = sql.substring(index);
        }
        tableName = tableName.trim();
        return tableName;
    }

    public static String getTableNameFromInsertSql(String sql) {
        sql = sql.toLowerCase();
        sql = sql.replace("insert or replace into", "insert into");
        String tableName = "";
        sql = sql.replace("\n", " ");
        sql = sql.replace("\r\n", " ");
        sql = sql.replace("  ", " ");
        sql = sql.substring(sql.toLowerCase().indexOf("into ") + "into ".length()).trim();
        tableName = sql.substring(0, sql.indexOf("(")).trim();
        return tableName;
    }

    public static Map<String, String> deleteToMap(String sql) {
        Map<String, String> columnMap = new HashMap<>();
        sql = sql.replace("\r\n", " ").replace("\n", " ");
        sql = sql.replace(" = ", " ")
                .replace("= ", " ")
                .replace("= ", " ")
                .replace("= ", " ")
                .replace(" =", " ")
                .replace(" =", " ")
                .replace(" =", " ")
        ;
        String[] sqlParts = sql.split(" ");
        for (String sqlPart : sqlParts) {
            if (sqlPart.contains("=")) {
                sqlPart = sqlPart.trim();
                columnMap.put(sqlPart.split("=")[0], sqlPart.split("=")[1]);
            }
        }
        System1.out.println(columnMap);
        return columnMap;
    }

    public static Map<String, String> updateToMap(String sql) {
        Map<String, String> columnMap = new HashMap<>();
        sql = sql.replace("\r\n", " ").replace("\n", " ");
        sql = sql.replace(" = ", " ")
                .replace("= ", " ")
                .replace("= ", " ")
                .replace("= ", " ")
                .replace(" =", " ")
                .replace(" =", " ")
                .replace(" =", " ")
        ;
        String[] sqlParts = sql.split(" ");
        for (String sqlPart : sqlParts) {
            if (sqlPart.contains("=")) {
                sqlPart = sqlPart.trim();
                columnMap.put(sqlPart.split("=")[0], sqlPart.split("=")[1]);
            }
        }
//        System1.out.println(columnMap);
        return columnMap;
    }

    public static Map<String, String> insertToMap(String sql) {
        Map<String, String> columnMap = new HashMap<>();
        String columnNameSql = sql;
        columnNameSql = sql.substring(sql.indexOf("("));
        columnNameSql = columnNameSql.substring(0, columnNameSql.indexOf(")"));
        columnNameSql = columnNameSql.replace("(", "");
        columnNameSql = columnNameSql.replace(")", "");
        String[] columnNames = columnNameSql.split(",");
        for (int i = 0; i < columnNames.length; i++) {
            columnNames[i] = columnNames[i].trim();
        }
        String valueSql = sql.substring(sql.toLowerCase().indexOf("values")).trim();
        if (valueSql.toLowerCase().startsWith("values")) {
            valueSql = valueSql.substring("values".length());
            valueSql = valueSql.trim();
        }
        if (valueSql.charAt(0) == '(') {
            valueSql = valueSql.substring(1);
        }
        if (valueSql.charAt(valueSql.length() - 1) == ';') {
            valueSql = valueSql.substring(0, valueSql.length() - 1);
        }
        if (valueSql.charAt(valueSql.length() - 1) == ')') {
            valueSql = valueSql.substring(0, valueSql.length() - 1);
        }
        List<String> valueList = parseValues(valueSql, columnNames.length);
//        System1.out.println(columnNames.length + ", " + valueList.size());
        if (valueList.size() == columnNames.length) {
            for (int i = 0; i < columnNames.length; i++) {
//                System1.out.println(columnNames[i] + "=" + valueList.get(i));
                Character startEndQuote = startEndQuote(columnNames[i].charAt(0));
                if (startEndQuote != null) {
                    columnNames[i] = columnNames[i].substring(1);
                    columnNames[i] = columnNames[i].substring(0, columnNames[i].length() - 1);
                }
                columnMap.put(columnNames[i], valueList.get(i));
            }
        }
        return columnMap;
    }

    public static Character startEndQuote(char startChar) {
        for (char startEndQuote : startEndQuotes) {
            if (startEndQuote == startChar) {
                return startChar;
            }
        }
        return null;
    }

    private static List<String> parseValues(String valueSql, int columnNamesLength) {
        List<String> valueList = new ArrayList<>();
        while (true) {
            if (valueSql.trim().length() == 0) {
                break;
            }
            char quoteBegin = valueSql.charAt(0);
            Character startEndQuote = startEndQuote(quoteBegin);
            if (startEndQuote == null) {
                if (valueList.size() == columnNamesLength - 1) {
                    String value = valueSql.trim();
                    valueList.add(value);
                } else {
                    int commaIndex = valueSql.indexOf(",");
                    String value = valueSql.substring(0, commaIndex);
                    valueSql = valueSql.substring(commaIndex + 1).trim();
                    valueList.add(value);
                }
            } else {
                valueSql = valueSql.substring(1);
                valueSql = valueSql.replace(startEndQuote + "   " + ",", startEndQuote + ",");
                valueSql = valueSql.replace(startEndQuote + "  " + ",", startEndQuote + ",");
                valueSql = valueSql.replace(startEndQuote + " " + ",", startEndQuote + ",");
                valueSql = valueSql.trim();
                int endIndex = valueSql.indexOf(startEndQuote + ",");
                if (valueList.size() == columnNamesLength - 1) {
                    endIndex = valueSql.indexOf(startEndQuote);
                }
                if (endIndex == -1) {
                    break;
                }
                String value = valueSql.substring(0, endIndex);
                valueSql = valueSql.substring(endIndex + 1).trim();
                if (valueSql.length() > 0 && valueSql.charAt(0) == ',') {
                    valueSql = valueSql.substring(1).trim();
                }
                valueList.add(value);
            }
        }
        return valueList;
    }

    public static void main(String[] args) {
        tableNameList.contains("person");
//        String sql = "INSERT INTO person(\"camera_image_feature_path\", \"checked_time_rule_id\", \"sign_up_rule\", \"id\", \"name\", \"camera_image_path\", \"camera_image_feature\", \"camera_image_fastdfs_path\", \"id_card_number\", \"id_card_image_path\", \"id_card_image_feature\", \"type\", \"phone\", \"sign_up_time\", \"sign_up_method\", \"sign_up_status\", \"verify_status\", \"auto_init\", \"update_time\", \"del_flag\", \"all_device\", \"company_id\", \"table\", \"device_id\") VALUES ('', '', '注册通行', 'PSPAD010120190716172753922', '', '/sdcard/security_attachment/person_camera_image_path_PSPAD010120190716172753922', 'kQKvjWNp89euRzjBJGVNRUSXWI7F7yvh1VTBngS8JPI8G3oEw1i5AVG2ZeWBAOkABZbA4tGXyVf2h5vPoe0auBcZhXEbmenD2duANhmHLyCqpXxkq7Iak57lVzV5N5mx6BtUUrhtMhAth4S8FYe87qstbLoQrz+7DEt+d1SFJKDjqag6C2myZhgIVLNnDHAO22Gnn5Msfmt5r8Awl6S6nORL+vsad3yk9gzt/KCtbJwSH74B9sS95z+HvbchaT5wRL+9z+DePHOoL74Vhm89FCQHPciH9bs65Qw79VsQPo/LtDzb8Jq+BO8dvk8vgrtjVF++2D6SvjSwS71ERl+8cH8YPP/c5D1r3oQ9hcIiPOISmr2rj7q9ExEYvlgooD3Q/dK93pYdPm5jgr2liO69M6CuvcoN8L1fVEq9MLzovancBr4y6C6+EdUTPu9eUby+Lbu9rBH+vefvtj3R4WK9LacZPlJO2ryb5iI+qBBcvjW8MrzfZLM7dy/PPRlGDz5Zjl09DkEDPq5ywb0jKrC9A5H9vAF1eD2WFke9IAADvCaUcbytZ1s8TmH3vXHZx7wvUMO9IiuRPX0cnD1lEh69TgMovGIEPj1RjCU96Ac6PeWOwTy3PwI+S4WoPXxNa70eFLw9oxifvG1zsb2QIOk9aYaoPUtY2rwF93+9eXMqvcbqPr0YZsQ8DRsQvbIQhrydACQ9Kq4RPcBtqrvx3ho9lzMQvZof6DwrP5o8ttYwPW+elL1cr/Q8YO1LPETaGLsCgS47LoWEPXWfRD125z08qNzTPKdoAj2aS/Y72crBvOSwgDtdrq27whnLvJ24cLza5Jm8c449O76rcrxE0A885CxEPMoamry5n1s8pKGcvHXNObxCEr08cORhPKk/Uzx29QO9eHTDO59UIr2Baaw74XW9u2CHfTzv8gE7WnAPvfHHwLuJKgE9Ws41vOBqAzyISMc8xd6QPCbdiTwOSAc8TpHUvL4MrTvpbHe8/yKDvNzXKbzXFJc8h58PvEkR17tRCw48KcbMvA8FNDw1TyA8t0c5PJEuijz2odg8XdsxPHgqIT0mySu8o91MOOWU7LqlHT48XM1PulOuF716FBm8QpN+vA4LFrxB9UK7P42Tu2zjDT1WOw08L9Zuu0Q9ibwGZbS7h/9VPG/UF7s4klw6ak4zvHpX/DvOHoO8ikXcPEz2iDzkfoI8fXhLvBzfgbzMQcU8xbddvHUQ1jowN/s70OvBPEhXMLr4YdM8Zzk0vLnjQDwkd5S8qxCMOzEHNjziTTI8gmekvCfDFLyDAlG7dtDDvJ4PCDzRtfQ75zKZPJG7ATtJSwi6Khbwu8ex4bzp1FC8uMstPAb5M7w4vAs8XNaZPBbQ1LqZNrQ7VZ4NvE+QYzku9NC7tMnXO5GOc7oX/O28nBc4vAwtMLoN47i8br+ivCi32DtirdQ7w0InurrNULxAO/A7ZDIJOGz4mDs3PoY8LMUCPTU+hLzlNks83yO1vOGX6bv5S3W7Nb8nPK3N/bnpdWK6uPQkvGi8sbxmpoc7UbReulmMUDsOEjm6nYJ3uu/tbrvLlVG7uooGvHUxvDoQZNO6Mf4lPLEEXTsk95q7D2LEvHof7Lq97/c7F2YrvA0rGbwN5ZU7QHgUu6gxZ7nXePS8OBE+uztWFDtvXVQ51tAkPFM0aro7HJO8R4QlO1dBIjsTe0m79d3Yu3hzEDvwvxw7XmqMvNi21jsb1Js7utLbO/h/AjzcNyM78EgkPMMxSDtEA/Q6daekO/QF7zvfYwQ6QphqvLi+BTwf+6O7RbiOO+OYZbzDtNi6omNFPDznAzrqiwc8YzCJu0lArjuHAce8EHULOnbA3jughSg8ZxGCu9GGTzyDi4M8jSi3O6o4C7xexlG8qUAqPJLv5jmku2I8/CuZu7y0fTwxLjI8kgXuO6tZGrwnJaI7By2tO0SfBbvf3D88K+o7u8/3iLpii6M75jzKO6XnCbxZYWk7yqCEPL+QrDuimiS8AG0SvHrQ/Ds7nzs8HrMAPNVQajuL4hg8zVxHvMxAvDtQmq67IvoPvPPEm7shjZS7Pe6OuxTfeTsEvNO7DKefu8uPz7uuzfu6DS1SO+alV7sbpbo7QqTfu4AoaztzddK6PjeUuoCyB7sH+Cq7cqUguuRB27t70iA7LfeZOyMslzvv2ms6mIaLO7FbWzzYcew6l69TO2zRgbs7fLy6uRxkOleadDrDJWu7h6Q0urBq5blfUCU77vfwOcGLZbtnFJO7EeLyulNOsLs3kdU7rc82OU6h6LoCR0m7KhinO/+qDTvpHaY7QSlGOvzwvTmDvBU5jKkyuwe/prvc44u3cLJtu6bjWDpzi8a6xBxyujiJ0boP7AE85TXnObdjiDvh1Xq7FFOQu8PSBbtcLFY7yVSHO3uFbLvjOs47VKhzOVRfWzupKA668wuruqxrorq2lXE7zCOrOy+DY7uMJyS73L1ruUYJBDuJSSG75JYUOghPMbpB7hE7GuMLutfMNjrEzxg752MmuVrDAjugqDg79OuAOwOj7Dpv8Re7b54yOpxnzDrozbU6iDsuOT2i7zipG5O7QYueusmyvLq+y145UPwjO1S5pzrHLmu7GZEMuzqbBrumcXI7OHMHu/3pUDvVzTe7sFR/uz1ICruGsle6t2LCOrusrjmPqQw7ApH6OiPf/boIFfc5Y2nwuX3LubpWoFi6AfJbum6iMrkUesE6fClAuM8lBbplNbI63ZebutlDkbqG8Ku64fJrusVHWTorVYK64s0AO4ZggrsTndG54zn+t5wCkToZfZc5DN32uH0fjjpGwyE7+UQtu89oHTpJCwU6KLsCOoR1HLnGmpu3yaaRuvb5RrqpDKu6SbI6Oln3FTp3g9k6XNoMOnpB8rpgPzi6PcgRO92zkzlGGp24jpfrOmWPGTpK4CY6j2ybOmfJ1TpR3NW6m6ZbujKmEjryUIm6ZuJzjmsGWsQdTaAbDUWLbYkmkAXNq5AUzYj62nodpXLtCuGrKLzEwOknu62anHpq8Ulv3nwJp7SVW8jgmJ6T9U73rrqNPKYb+tPo4uE6perTg+kuI2MrXz6llMfQ3r72ZjXZYxSr4HndEHU0BzIHMq3HCAD7QqP0HVZo6Zwj4IQy0+AX/kUUSVb55WKa6HQh4BOQ0CBI9ls7FzAYV9wCmS69Ehno2tVPfLGnebBrhEBWlxDgdPmY+U0iLqo7kHbaaFZxvCeD/KGzA2+K3orK3h6Dv+6S8CTZvk1vln7LabW+A7s1XvylnOZ71baZSMnHdN/FzC4D2wdO3q0CE6hpStoIA5nbLlVHoI6t+nYOQQrAFEtkg8+ao7mTNeiEW4WzTUtgiDVnyrRtLZI/xjk3VwV/651GQqIu+mlAO3pRbR5rbUPporz60xZZLiscZ092Nw==', '', '', '', '', '', '', '1563269273923', '人工注册', '已注册', '不需要验证身份证', '', '', '0', '0', '', 'person', '');\n";
//        System1.out.println("SqlSplit.main " + getTableNameFromInsertSql(sql));
//        insertToMap(query);
//        System1.out.println(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date(Long.parseLong("1573901869122"))));
//        if (1 == 1) {
//            return;
//        }
//        Pattern pattern = Pattern.compile("\\((.*?)\\)", Pattern.DOTALL);
//        Matcher matcher = pattern.matcher(query);
//        List<String[]> tokens = new ArrayList<String[]>();
//        while (matcher.find()) {
//            tokens.add(matcher.group().replace("(", "").replace(")", "").split(","));
//        }
//        Map<String, String> map = new HashMap<String, String>();
//        if (tokens.size() == 2 && tokens.get(0).length == tokens.get(1).length) {
//            for (int x = 0; x < tokens.get(0).length; x++) {
//                map.put(tokens.get(0)[x], tokens.get(1)[x].replace("'", ""));
//            }
//        }
//        for (Map.Entry<String, String> entry : map.entrySet()) {
//            System1.out.println("KEY:" + entry.getKey());
//            System1.out.println("VALUE:" + entry.getValue());
//        }
    }

}