package com.yutou.common.databases;


import com.alibaba.fastjson2.JSON;
import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import com.alibaba.fastjson2.annotation.JSONField;
import com.alibaba.fastjson2.util.DateUtils;
import com.yutou.biliapi.bean.live.database.LiveConfigDatabaseBean;
import com.yutou.common.inter.ISqlDatabaseBean;
import com.yutou.common.utils.Log;
import lombok.Data;
import lombok.Getter;
import org.sqlite.SQLiteConfig;

import java.io.File;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.*;
import java.util.Date;

public abstract class SQLiteManager {
    protected Connection conn;
    private String url = "jdbc:sqlite:";
    private File sql;
    @Getter
    public BuildSql build;

    public abstract String getFileName();

    private BuildSql createBuildSQL(List<AbsDatabasesBean> data) {
        List<BuildSqlItem> items;
        List<BuildSqlTable> tables = new ArrayList<>();
        for (AbsDatabasesBean bean : data) {
            List<Field> fields = bean.getFields();
            items = new ArrayList<>();
            for (Field field : fields) {
                String name = field.getAnnotation(JSONField.class).name();
                if ("tableName".equals(name)) {
                    continue;
                }
                String type = BuildSqlItem.TYPE_STRING;
                if (field.getType() == int.class) {
                    type = BuildSqlItem.TYPE_INT;
                } else if (field.getType() == long.class) {
                    type = BuildSqlItem.TYPE_LONG;
                } else if (field.getType() == String.class) {
                    type = BuildSqlItem.TYPE_STRING;
                } else if (field.getType() == Date.class) {
                    type = BuildSqlItem.TYPE_TIME;
                } else if (field.getType() == boolean.class) {
                    type = BuildSqlItem.TYPE_BOOLEAN;
                } else if (field.getType() == ISqlDatabaseBean.class) {
                    type = BuildSqlItem.TYPE_TEXT;
                }
                items.add(BuildSqlItem.create().setName(name).setType(type).setNull(!"id".equals(name)).setKey("id".equals(name)));
            }
            tables.add(BuildSqlTable.create().setName(bean.getTableName()).setItem(items));
        }

        return BuildSql.create()
                .setFileName(getFileName())
                .setTable(tables);
    }

    public void init() {
        build(createBuildSQL(getDataBean()));
    }

    public void recreateSql(List<AbsDatabasesBean> beans) {
        createSql(createBuildSQL(beans));
    }

    public void startBatch() {
        try {
            conn.setAutoCommit(false);
        } catch (SQLException e) {
            Log.e(e);
        }
    }

    public void closeBatch() {
        try {
            conn.setAutoCommit(true);
        } catch (SQLException e) {
            Log.e(e);
        }
    }

    public void close() {
        try {
            conn.close();
        } catch (SQLException throwables) {
            Log.e(throwables);
        }
    }

    public void commit() {
        try {
            conn.commit();
        } catch (SQLException e) {
            Log.e(e);
        }
    }

    protected <T extends AbsDatabasesBean> void add(T t) {
        StringBuilder sb = new StringBuilder();
        PreparedStatement statement = null;
        try {

            StringBuilder value = new StringBuilder();
            sb.append("INSERT INTO `").append(t.getTableName()).append("` ");
            sb.append("(");
            value.append("(");
            JSONObject json = t.toJson();
            Set<String> keySet = json.keySet();
            for (String key : keySet) {
                if ("id".equals(key)) {
                    continue;
                }
                sb.append("`").append(key).append("`,");
                //value.append("'").append(json.get(key)).append("',");
                value.append("?").append(",");
            }
            sb.deleteCharAt(sb.length() - 1);
            value.deleteCharAt(value.length() - 1);
            value.append(")");
            sb.append(") VALUES ");
            sb.append(value);
            statement = getConnection().prepareStatement(sb.toString());
            int i = 1;
            for (String key : keySet) {
                if ("id".equals(key)) {
                    continue;
                }
                if ("sql_time".equals(key)) {
                    statement.setLong(i++, json.getDate(key).getTime());
                    continue;
                }
                if (json.get(key) instanceof String) {
                    statement.setString(i++, json.get(key).toString());
                } else if (json.get(key) instanceof Integer) {
                    statement.setInt(i++, json.getInteger(key));
                } else if (json.get(key) instanceof Long) {
                    statement.setLong(i++, json.getLong(key));
                } else if (json.get(key) instanceof Boolean) {
                    statement.setBoolean(i++, json.getBoolean(key));
                } else if (json.get(key) instanceof Date) {
                    statement.setLong(i++, json.getDate(key).getTime());
                } else {
                    statement.setObject(i++, json.get(key));
                }
            }
            statement.execute();
        } catch (SQLException e) {
            Log.e(e, sb);
            throw new RuntimeException(e);
        } finally {
            try {
                if (statement != null) {
                    statement.closeOnCompletion();
                }
            } catch (SQLException e) {
                Log.e(e);
            }
        }
    }

    protected <T extends AbsDatabasesBean> void update(T t) {
        PreparedStatement statement = null;
        try {
            StringBuilder sb = new StringBuilder();
            StringBuilder setPart = new StringBuilder();

            JSONObject json = t.toJson();
            Set<String> keySet = json.keySet();

            for (String key : keySet) {
                if ("id".equals(key) || "sql_time".equals(key)) {
                    continue;
                }
                setPart.append("`").append(key).append("` = ?, ");
            }

            setPart.deleteCharAt(setPart.length() - 2);

            sb.append("UPDATE `").append(t.getTableName()).append("` ");
            sb.append("SET ").append(setPart);
            sb.append(" WHERE `sql_time` = ?");

            statement = getConnection().prepareStatement(sb.toString());

            int i = 1;
            for (String key : keySet) {
                if ("id".equals(key) || "sql_time".equals(key)) {
                    continue;
                }

                if (json.get(key) instanceof String) {
                    statement.setString(i++, json.getString(key));
                } else if (json.get(key) instanceof Integer) {
                    statement.setInt(i++, json.getInteger(key));
                } else if (json.get(key) instanceof Long) {
                    statement.setLong(i++, json.getLong(key));
                } else if (json.get(key) instanceof Boolean) {
                    statement.setBoolean(i++, json.getBoolean(key));
                } else if (json.get(key) instanceof Date) {
                    statement.setLong(i++, json.getDate(key).getTime());
                } else {
                    statement.setObject(i++, json.get(key));
                }
            }

            // 设置 sql_time 的值
            long id = t.getSql_time().getTime();
            statement.setLong(i, id);

            statement.executeUpdate();

        } catch (SQLException e) {
            Log.e(e);
            throw new RuntimeException(e);
        } finally {
            try {
                if (statement != null) {
                    statement.closeOnCompletion();
                }
            } catch (SQLException e) {
                Log.e(e);
            }
        }
    }

    protected JSONArray getJSONArray(String table) {
        return getJSONArray(table, null);
    }

    protected JSONArray getJSONArray(String table, String where) {
        JSONArray array = new JSONArray();
        ResultSet resultSet = null;
        Statement statement = null;
        try {
            statement = getConnection().createStatement();
            String sql = "SELECT * FROM `" + table + "`";
            if (where != null) {
                sql += " WHERE " + where;
            }
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                array.add(getSQL(resultSet));
            }
        } catch (Exception e) {
            Log.e(e);
            throw new RuntimeException(e);
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (statement != null) {
                    statement.closeOnCompletion();
                }
            } catch (SQLException e) {
                Log.e(e);
            }
        }
        return array;
    }

    private JSONObject getSQL(ResultSet resultSet) throws SQLException {
        JSONObject json = new JSONObject();
        for (int i = 0; i < resultSet.getMetaData().getColumnCount(); i++) {
            String name = resultSet.getMetaData().getColumnName(i + 1);
            Object value = resultSet.getObject(name);
            if (value instanceof String && ((String) value).startsWith("[")) {
                try {
                    json.put(name, JSON.parseArray((String) value));
                } catch (Exception e) {
                    json.put(name, value);
                }
            } else {
                json.put(name, value);
                if (json.get(name) == null || "null".equals(json.get(name).toString())) {
                    json.put(name, null);
                }
            }
        }
        return json;
    }

    protected <T extends AbsDatabasesBean> List<T> get(String table, Class<T> tClass) {
        return get(table, null, tClass);
    }

    protected int getCount(String table) {
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = getConnection().prepareStatement("SELECT count(*) FROM " + table);
            try (ResultSet resultSet = preparedStatement.executeQuery()) {
                if (resultSet.next()) {
                    return resultSet.getInt(1);
                }
            }

        } catch (SQLException e) {
            Log.e(e);
            throw new RuntimeException("Error executing SQL query", e);
        } finally {
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException e) {
                Log.e(e);
            }
        }

        return 0;
    }

    protected <T extends AbsDatabasesBean> List<T> get(String table, String where, Class<T> tClass) {
        List<T> list = new ArrayList<>();
        Statement statement = null;
        ResultSet resultSet=null;
        try {
            statement = getConnection().createStatement();
            String sql = "SELECT * FROM `" + table + "`";
            if (where != null) {
                sql += " WHERE " + where;
            }
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                list.add(getSQL(resultSet).to(tClass));
            }
        } catch (Exception e) {
            Log.e(e);
            throw new RuntimeException(e);
        } finally {
            try {
                if (statement != null) {
                    statement.closeOnCompletion();
                }
                if(resultSet!=null){
                    resultSet.close();
                }
            } catch (SQLException e) {
                Log.e(e);
            }
        }
        return list;
    }

    protected JSONArray get(String sql) {
        JSONArray json = new JSONArray();
        ResultSet resultSet = null;
        Statement statement=null;
        try {
            statement = getConnection().createStatement();
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                json.add(getSQL(resultSet));
            }
        } catch (Exception e) {
            throw new RuntimeException(e);
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if(statement!=null){
                    statement.closeOnCompletion();
                }
            } catch (SQLException e) {
                Log.e(e);
            }
        }
        return json;
    }

    protected <T extends AbsDatabasesBean> boolean delete(T t) {
        Statement statement = null;
        try {
            statement = getConnection().createStatement();
            StringBuilder sb = new StringBuilder();
            sb.append("DELETE FROM `").append(t.getTableName()).append("` ");
            sb.append(" WHERE `sql_time` = ").append("'").append(t.getSql_time().getTime()).append("'");
            int ret = statement.executeUpdate(sb.toString());
            return ret != 0;
        } catch (Exception e) {
            Log.e(e);
            throw new RuntimeException(e);
        } finally {
            try {
                if (statement != null) {
                    statement.closeOnCompletion();
                }
            } catch (SQLException e) {
                Log.e(e);
            }
        }
    }

    /**
     * 清空指定表中的所有数据。
     *
     * @param tableName 要清空的表名
     */
    public void clearTable(String tableName) {
        try {
            String sql = "DROP TABLE IF EXISTS " + tableName;
            try (Statement stmt = getConnection().createStatement()) {
                int rowsDeleted = stmt.executeUpdate(sql);
                stmt.close();
                System.out.println("已删除 " + rowsDeleted + " 行数据从表 " + tableName);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    private void createSql(BuildSql build) {
        try {
            sql.mkdirs();
            sql.delete();
            linkDB();
        } catch (Exception e) {
            Log.e(e);
        }
        startBatch();
        for (BuildSqlTable table : build.getTable()) {
            Log.i("创建表:" + table.getName());
            createSqlOfTable(table);
        }
        closeBatch();
    }

    private void createSqlOfTable(BuildSqlTable table) {
        String tableName = table.getName();
        Statement statement = null;
        try {
            statement = getConnection().createStatement();
            List<BuildSqlItem> items = table.getItem();
            StringBuilder sql = new StringBuilder();
            sql.append("CREATE TABLE `")
                    .append(tableName)
                    .append("` (");
            for (BuildSqlItem item : items) {
                StringBuilder builder = new StringBuilder();
                String type = switch (item.getType()) {
                    case BuildSqlItem.TYPE_INT -> " INTEGER ";
                    case BuildSqlItem.TYPE_TIME, BuildSqlItem.TYPE_LONG -> " NUMERIC ";
                    case BuildSqlItem.TYPE_BOOLEAN -> " BOOLEAN ";
                    default -> " TEXT ";
                };
                builder.append("`")
                        .append(item.getName())
                        .append("`")
                        .append(type)
                        .append(item.isNull() ? "" : " NOT NULL ")
                        .append(item.isKey() ? " PRIMARY KEY AUTOINCREMENT " : "")
                        .append(",");
                sql.append(builder.toString());
            }
            sql.append(");");
            Log.i("创建表 > " + sql.toString());
            statement.execute(sql.toString().replace(",);", ");"));
        } catch (Exception e) {
            Log.e(e);
            try {
                if (statement != null) {
                    statement.closeOnCompletion();
                }
            } catch (SQLException e1) {
                Log.e(e1);
            }
        }
    }

    protected void build(BuildSql buildSql) {
        try {
            this.build = buildSql;
            Class.forName("org.sqlite.JDBC");
            if (buildSql.getFileName().startsWith(new File("").getAbsolutePath())) {
                sql = new File(buildSql.getFileName());
            } else {
                sql = new File("databases" + File.separator + buildSql.getFileName());
            }
            if (!sql.exists()) {
                createSql(buildSql);
            } else {
                linkDB();
            }
        } catch (Exception e) {
            Log.e(e);
        }
    }

    private void linkDB() throws SQLException {
        SQLiteConfig config = new SQLiteConfig();
        config.enforceForeignKeys(true);
        conn = config.createConnection(url + sql.getAbsolutePath());
    }

    protected Connection getConnection() {
        try {
            if (conn.isClosed()) {
                linkDB();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return conn;
    }


    protected abstract List<AbsDatabasesBean> getDataBean();

    public static void main(String[] args) {
        LiveConfigDatabaseBean bean = new LiveConfigDatabaseBean();
        bean.setRoomId(new String("123"));
        bean.setRecordLive(true);
        bean.setKeywordList(List.of("111", "22"));
        JSONObject json = bean.toJson();
        Log.i("json = " + json);
    }

    @Data
    public static class BuildSql {
        String fileName;
        List<BuildSqlTable> table;

        public static BuildSql create() {
            return new BuildSql();
        }

        public BuildSql setFileName(String fileName) {
            this.fileName = fileName;
            return this;
        }

        public BuildSql setTable(List<BuildSqlTable> table) {
            this.table = table;
            return this;
        }

        public BuildSql setTable(BuildSqlTable... table) {
            this.table = List.of(table);
            return this;
        }
    }

    @Data
    public static class BuildSqlTable {
        String name;
        List<BuildSqlItem> item;

        public static BuildSqlTable create() {
            return new BuildSqlTable();
        }

        public BuildSqlTable setName(String name) {
            this.name = name;
            return this;
        }

        public BuildSqlTable setItem(List<BuildSqlItem> item) {
            this.item = item;
            return this;
        }

        public BuildSqlTable setItem(BuildSqlItem... item) {
            this.item = List.of(item);
            return this;
        }
    }

    @Data
    public static class BuildSqlItem {
        public static final String TYPE_INT = "INTEGER";
        public static final String TYPE_LONG = "NUMERIC";
        public static final String TYPE_STRING = "TEXT";
        public static final String TYPE_TIME = "TIME";
        public static final String TYPE_BOOLEAN = "boolean";
        public static final String TYPE_TEXT = "TEXT";
        public static final String TYPE_DATE = "DATE";


        String name;
        String type;
        boolean isNull;
        boolean isKey;

        public static BuildSqlItem create() {
            return new BuildSqlItem();
        }

        public BuildSqlItem setName(String name) {
            this.name = name;
            return this;
        }

        public BuildSqlItem setType(String type) {
            this.type = type;
            return this;
        }

        public BuildSqlItem setNull(boolean aNull) {
            isNull = aNull;
            return this;
        }

        public BuildSqlItem setKey(boolean key) {
            isKey = key;
            return this;
        }
    }
}
