package mara.mybox.db.table;

import java.io.BufferedReader;
import java.io.InputStream;
import java.io.StringReader;
import java.lang.reflect.ParameterizedType;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import mara.mybox.data.StringTable;
import mara.mybox.data2d.DataInternalTable;
import mara.mybox.data2d.DataTable;
import mara.mybox.db.Database;
import mara.mybox.db.DerbyBase;
import mara.mybox.db.data.BaseData;
import mara.mybox.db.data.ColumnDefinition;
import mara.mybox.db.data.ColumnDefinition.ColumnType;
import static mara.mybox.db.data.ColumnDefinition.ColumnType.Boolean;
import static mara.mybox.db.data.ColumnDefinition.ColumnType.Clob;
import static mara.mybox.db.data.ColumnDefinition.ColumnType.Enumeration;
import static mara.mybox.db.data.ColumnDefinition.ColumnType.Era;
import static mara.mybox.db.data.ColumnDefinition.ColumnType.Short;
import mara.mybox.db.data.ColumnDefinition.InvalidAs;
import mara.mybox.db.data.Data2DColumn;
import mara.mybox.dev.MyBoxLog;
import mara.mybox.fxml.style.HtmlStyles;
import mara.mybox.tools.DateTools;
import mara.mybox.tools.DoubleTools;
import mara.mybox.tools.FloatTools;
import mara.mybox.tools.HtmlWriteTools;
import mara.mybox.tools.IntTools;
import mara.mybox.tools.LongTools;
import mara.mybox.tools.ShortTools;
import mara.mybox.tools.StringTools;
import mara.mybox.value.AppValues;
import static mara.mybox.value.Languages.message;

/**
 * @param <D> Should be extened from "BaseData"
 * @Author Mara
 * @CreateDate 2020-7-12
 * @License Apache License Version 2.0
 */
public abstract class BaseTable<D> {

    public final static int FilenameMaxLength = 32672;
    public final static int StringMaxLength = 32672;

    public String tableName, idColumnName, orderColumns, tableTitle;
    public List<ColumnDefinition> columns, primaryColumns, foreignColumns, referredColumns;
    public boolean supportBatchUpdate;
    public long newID = -1;

    public abstract boolean valid(D data);

    public abstract boolean setValue(D data, String column, Object value);

    public abstract Object getValue(D data, String column);

    /*
        methods need implemented
     */
    public Object readForeignValue(ResultSet results, String column) {
        return null;
    }

    public boolean setForeignValue(D data, String column, Object value) {
        return true;
    }

    public D readData(Connection conn, D data) {
        if (conn == null || data == null) {
            return null;
        }
        String sql = queryStatement();
        if (sql == null || sql.isBlank()) {
            return null;
        }
        try (PreparedStatement statement = conn.prepareStatement(sql)) {
            if (setColumnsValues(statement, primaryColumns, data, 1) < 0) {
                return null;
            }
            return query(conn, statement);
        } catch (Exception e) {
            MyBoxLog.debug(e, sql);
            return null;
        }
    }

    public D readData(ResultSet results) {
        if (results == null) {
            return null;
        }
        try {
            D data = newData();
            for (int i = 0; i < columns.size(); ++i) {
                ColumnDefinition column = columns.get(i);
                Object value = readColumnValue(results, column);
                setValue(data, column.getColumnName(), value);
            }
            for (int i = 0; i < foreignColumns.size(); ++i) {
                ColumnDefinition column = foreignColumns.get(i);
                String name = column.getColumnName();
                Object value = readForeignValue(results, name);
                if (!setForeignValue(data, name, value)) {
                    return null;
                }
            }
            return data;
        } catch (Exception e) {
            MyBoxLog.debug(e, tableName);
        }
        return null;
    }

    public Object readColumnValue(ResultSet results, ColumnDefinition column) {
        if (results == null || column == null) {
            return null;
        }
        return column.value(results);
    }

    public boolean setColumnValue(PreparedStatement statement,
            ColumnDefinition column, D data, int index) {
        if (statement == null || data == null || column == null || index < 0) {
            return false;
        }
        try {
            Object value = getValue(data, column.getColumnName());
            // Not check maxValue/minValue.
            boolean notPermitNull = column.isNotNull();
            switch (column.getType()) {
                case String:
                case Color:
                case File:
                case Image:
                case Enumeration:
                case EnumerationEditable:
                    if (value == null && !notPermitNull) {
                        statement.setNull(index, Types.VARCHAR);
                    } else {
                        String s;
                        try {
                            s = (String) value;
                        } catch (Exception ex) {
                            try {
                                s = (String) column.defaultValue();
                            } catch (Exception exs) {
                                s = "";
                            }
                        }
                        if (s == null && !notPermitNull) {
                            s = "";
                        }
                        if (s == null) {
                            statement.setNull(index, Types.VARCHAR);
                        } else {
                            if (column.getLength() > 0 && s.length() > column.getLength()) {
                                s = s.substring(0, column.getLength());
                            }
                            statement.setString(index, s);
                        }
                    }
                    break;
                case Double:
                    if (value == null && !notPermitNull) {
                        statement.setNull(index, Types.DOUBLE);
                    } else {
                        double d;
                        try {
                            d = (double) value;
                        } catch (Exception ex) {
                            try {
                                d = (double) column.defaultValue();
                            } catch (Exception exs) {
                                d = AppValues.InvalidDouble;
                            }
                        }
                        if (DoubleTools.invalidDouble(d)) {
                            d = AppValues.InvalidDouble;
                            if (!notPermitNull) {
                                statement.setNull(index, Types.DOUBLE);
                                break;
                            }
                        }
                        statement.setDouble(index, d);
                    }
                    break;
                case Longitude:
                    if (value == null && !notPermitNull) {
                        statement.setNull(index, Types.DOUBLE);
                    } else {
                        double d;
                        try {
                            d = (double) value;
                        } catch (Exception ex) {
                            d = -200;
                        }
                        if (DoubleTools.invalidDouble(d)
                                || d > 180 || d < -180) {
                            d = -200;
                            if (!notPermitNull) {
                                statement.setNull(index, Types.DOUBLE);
                                break;
                            }
                        }
                        statement.setDouble(index, d);
                    }
                    break;
                case Latitude:
                    if (value == null && !notPermitNull) {
                        statement.setNull(index, Types.DOUBLE);
                    } else {
                        double d;
                        try {
                            d = (double) value;
                        } catch (Exception ex) {
                            d = -200;
                        }
                        if (DoubleTools.invalidDouble(d)
                                || d > 90 || d < -90) {
                            d = -200;
                            if (!notPermitNull) {
                                statement.setNull(index, Types.DOUBLE);
                                break;
                            }
                        }
                        statement.setDouble(index, d);
                    }
                    break;
                case Float:
                    if (value == null && !notPermitNull) {
                        statement.setNull(index, Types.FLOAT);
                    } else {
                        float f;
                        try {
                            f = (float) value;
                        } catch (Exception ex) {
                            try {
                                f = (float) column.defaultValue();
                            } catch (Exception exs) {
                                f = AppValues.InvalidFloat;
                            }
                        }
                        if (FloatTools.invalidFloat(f)) {
                            f = AppValues.InvalidFloat;
                            if (!notPermitNull) {
                                statement.setNull(index, Types.FLOAT);
                                break;
                            }
                        }
                        statement.setFloat(index, f);
                    }
                    break;
                case Long:
                    if (value == null && !notPermitNull) {
                        statement.setNull(index, Types.BIGINT);
                    } else {
                        long l;
                        try {
                            l = (long) value;
                        } catch (Exception ex) {
                            try {
                                l = (long) column.defaultValue();
                            } catch (Exception exx) {
                                l = AppValues.InvalidLong;
                            }
                        }
                        if (LongTools.invalidLong(l)) {
                            l = AppValues.InvalidLong;
                            if (!notPermitNull) {
                                statement.setNull(index, Types.BIGINT);
                                break;
                            }
                        }
                        statement.setLong(index, l);
                    }
                    break;
                case Integer:
                    if (value == null && !notPermitNull) {
                        statement.setNull(index, Types.INTEGER);
                    } else {
                        int i;
                        try {
                            i = (int) value;
                        } catch (Exception ex) {
                            try {
                                i = (int) column.defaultValue();
                            } catch (Exception exx) {
                                i = AppValues.InvalidInteger;
                            }
                        }
                        if (IntTools.invalidInt(i)) {
                            i = AppValues.InvalidInteger;
                            if (!notPermitNull) {
                                statement.setNull(index, Types.INTEGER);
                                break;
                            }
                        }
                        statement.setInt(index, i);
                    }
                    break;
                case Short:
                    if (value == null && !notPermitNull) {
                        statement.setNull(index, Types.SMALLINT);
                    } else {
                        short r;
                        try {
                            if (value instanceof Integer) { // sometime value becomes Integer...
                                r = (short) ((int) value);
                            } else {
                                r = (short) value;
                            }
                        } catch (Exception ex) {
                            try {
                                r = (short) column.defaultValue();
                            } catch (Exception exx) {
                                r = AppValues.InvalidShort;
                            }
                        }
                        if (ShortTools.invalidShort(r)) {
                            r = AppValues.InvalidShort;
                            if (!notPermitNull) {
                                statement.setNull(index, Types.SMALLINT);
                                break;
                            }
                        }
                        statement.setShort(index, r);
                    }
                    break;
                case EnumeratedShort:
                    if (value == null && !notPermitNull) {
                        statement.setNull(index, Types.SMALLINT);
                    } else {
                        short r;
                        try {
                            if (value instanceof Integer) { // sometime value becomes Integer...
                                r = (short) ((int) value);
                            } else {
                                r = (short) value;
                            }
                        } catch (Exception ex) {
                            r = 0;
                        }
                        if (ShortTools.invalidShort(r)) {
                            r = 0;
                            if (!notPermitNull) {
                                statement.setNull(index, Types.SMALLINT);
                                break;
                            }
                        }
                        statement.setShort(index, r);
                    }
                    break;
                case Boolean:
                    if (value == null && !notPermitNull) {
                        statement.setNull(index, Types.BOOLEAN);
                    } else {
                        boolean b;
                        try {
                            b = (boolean) value;
                        } catch (Exception ex) {
                            try {
                                b = (boolean) column.defaultValue();
                            } catch (Exception exx) {
                                b = false;
                            }
                        }
                        statement.setBoolean(index, b);
                    }
                    break;
                case NumberBoolean:
                    if (value == null && !notPermitNull) {
                        statement.setNull(index, Types.SMALLINT);
                    } else {
                        short r;
                        try {
                            if (value instanceof Integer) {
                                r = (short) ((int) value);
                            } else {
                                r = (short) value;
                            }
                        } catch (Exception ex) {
                            r = 0;
                        }
                        if (ShortTools.invalidShort(r)) {
                            r = 0;
                            if (!notPermitNull) {
                                statement.setNull(index, Types.SMALLINT);
                                break;
                            }
                        }
                        statement.setShort(index, r);
                    }
                    break;
                case Datetime:
                    if (value == null && !notPermitNull) {
                        statement.setNull(index, Types.TIMESTAMP);
                    } else {
                        Date dt;
                        try {
                            dt = (Date) value;
                        } catch (Exception ex) {
                            try {
                                dt = (Timestamp) column.defaultValue();
                            } catch (Exception exx) {
                                dt = null;
                            }
                        }
                        if (dt == null) {
                            if (!notPermitNull) {
                                statement.setNull(index, Types.TIMESTAMP);
                                break;
                            }
                            dt = new Date();
                        }
                        statement.setTimestamp(index, new Timestamp(dt.getTime()));
                    }
                    break;
                case Date:
                    if (value == null && !notPermitNull) {
                        statement.setNull(index, Types.DATE);
                    } else {
                        Date dd;
                        try {
                            dd = (Date) value;
                        } catch (Exception ex) {
                            try {
                                dd = (java.sql.Date) column.defaultValue();
                            } catch (Exception exx) {
                                dd = null;
                            }
                        }
                        if (dd == null) {
                            if (!notPermitNull) {
                                statement.setNull(index, Types.DATE);
                                break;
                            }
                            dd = new Date();
                        }
                        statement.setDate(index, new java.sql.Date(dd.getTime()));
                    }
                    break;
                case Era:
                    if (value == null && !notPermitNull) {
                        statement.setNull(index, Types.BIGINT);
                    } else {
                        long el;
                        try {
                            el = Long.parseLong(value + "");
                            if (el < 10000 && el > -10000) {
                                Date ed = DateTools.encodeDate((String) value);
                                el = ed.getTime();
                            }
                        } catch (Exception ex) {
                            try {
                                Date ed = DateTools.encodeDate((String) value);
                                el = ed.getTime();
                            } catch (Exception e) {
                                el = AppValues.InvalidLong;
                            }
                        }
                        if (LongTools.invalidLong(el)) {
                            el = AppValues.InvalidLong;
                            if (!notPermitNull) {
                                statement.setNull(index, Types.BIGINT);
                                break;
                            }
                        }
                        statement.setLong(index, el);
                    }
                    break;
                case Clob:
                    if (value == null && !notPermitNull) {
                        statement.setNull(index, Types.CLOB);
                    } else {
                        String cb;
                        try {
                            cb = (String) value;
                        } catch (Exception ex) {
                            try {
                                cb = (String) column.defaultValue();
                            } catch (Exception exs) {
                                cb = null;
                            }
                        }
                        if (cb == null) {
                            if (!notPermitNull) {
                                statement.setNull(index, Types.CLOB);
                                break;
                            }
                            cb = "";
                        }
                        // CLOB is handled as string internally, and maxmium length is Integer.MAX(2G)
                        statement.setCharacterStream(index, new BufferedReader(new StringReader(cb)), cb.length());
                    }
                    break;
                case Blob:
                    if (value == null) {
                        statement.setNull(index, Types.BLOB);
                    } else {
                        // BLOB is handled as InputStream internally
                        try {
                            statement.setBinaryStream(index, (InputStream) value);
                        } catch (Exception ex) {
                            statement.setNull(index, Types.BLOB);
                        }
                    }
                    break;
                default:
                    MyBoxLog.debug(column.getColumnName() + " " + column.getType() + " " + value);
                    return false;
            }
            return true;
        } catch (Exception e) {
            MyBoxLog.debug(e.toString(), tableName + " " + column.getColumnName());
            return false;
        }
    }

    public int setColumnsValues(PreparedStatement statement,
            List<ColumnDefinition> valueColumns, D data, int startIndex) {
        if (statement == null || data == null || startIndex < 0) {
            return -1;
        }
        try {
            int index = startIndex;
            for (int i = 0; i < valueColumns.size(); ++i) {
                ColumnDefinition column = valueColumns.get(i);
                if (!setColumnValue(statement, column, data, index++)) {
                    return -1;
                }
            }
            return index;
        } catch (Exception e) {
            MyBoxLog.debug(e, tableName);
            return -1;
        }
    }

    public int setColumnNamesValues(PreparedStatement statement,
            List<String> valueColumns, D data, int startIndex) {
        if (statement == null || data == null || startIndex < 0) {
            return -1;
        }
        try {
            int index = startIndex;
            for (int i = 0; i < valueColumns.size(); ++i) {
                ColumnDefinition column = column(valueColumns.get(i));
                if (!setColumnValue(statement, column, data, index++)) {
                    return -1;
                }
            }
            return index;
        } catch (Exception e) {
            MyBoxLog.debug(e, tableName);
            return -1;
        }
    }

    public boolean setInsertStatement(Connection conn,
            PreparedStatement statement, D data) {
        if (conn == null || statement == null || data == null) {
            return false;
        }
        return setColumnsValues(statement, insertColumns(), data, 1) > 0;
    }

    public boolean setUpdateStatement(Connection conn,
            PreparedStatement statement, D data) {
        if (conn == null || statement == null || !valid(data)) {
            return false;
        }
        try {
            int index = setColumnsValues(statement, updateColumns(), data, 1);
            if (index < 0) {
                return false;
            }
            return setColumnsValues(statement, primaryColumns, data, index) > 0;
        } catch (Exception e) {
            MyBoxLog.debug(e, tableName);
            return false;
        }
    }

    public boolean setDeleteStatement(Connection conn,
            PreparedStatement statement, D data) {
        if (conn == null || statement == null || data == null) {
            return false;
        }
        return setColumnsValues(statement, primaryColumns, data, 1) > 0;
    }

    public List<String> allFields() {
        List<String> names = new ArrayList<>();
        for (ColumnDefinition column : columns) {
            names.add(column.getColumnName());
        }
        return names;
    }

    public List<String> importNecessaryFields() {
        List<String> names = new ArrayList<>();
        for (ColumnDefinition column : columns) {
            if (column.isNotNull() && !column.isAuto()) {
                names.add(column.getColumnName());
            }
        }
        return names;
    }

    public List<String> importAllFields() {
        return allFields();
    }

    public List<String> exportAllFields() {
        return allFields();
    }

    /*
        general methods which may need not change
     */
    private void init() {
        tableName = null;
        idColumnName = null;
        orderColumns = null;
        columns = new ArrayList<>();
        primaryColumns = new ArrayList<>();
        foreignColumns = new ArrayList<>();
        referredColumns = new ArrayList<>();
        supportBatchUpdate = false;
        newID = -1;
    }

    public BaseTable() {
        init();
    }

    public void reset() {
        init();
    }

    public BaseTable addColumn(ColumnDefinition column) {
        if (column != null) {
            column.setTableName(tableName);
            column.setIndex(columns.size() + 1);
            if (column.isIsPrimaryKey()) {
                primaryColumns.add(column);
                if (column.isAuto()) {
                    idColumnName = column.getColumnName();
                }
            }
            if (column.getReferTable() != null && column.getReferColumn() != null) {
                foreignColumns.add(column);
            }
            columns.add(column);
        }
        return this;
    }

    public String createTableStatement() {
        if (tableName == null || columns.isEmpty()) {
            return null;
        }
        String sql = "CREATE TABLE " + DerbyBase.fixedIdentifier(tableName) + " ( \n";
        for (int i = 0; i < columns.size(); ++i) {
            if (i > 0) {
                sql += ", \n";
            }
            sql += createColumnDefiniton(columns.get(i));
        }
        if (!primaryColumns.isEmpty()) {
            sql += ", \n";
            sql += "PRIMARY KEY ( ";
            for (int i = 0; i < primaryColumns.size(); ++i) {
                if (i > 0) {
                    sql += ", ";
                }
                sql += primaryColumns.get(i).getColumnName();

            }
            sql += " ) ";
        }
        for (int i = 0; i < columns.size(); ++i) {
            ColumnDefinition column = columns.get(i);
            String f = column.foreignText();
            if (f != null && !f.isBlank()) {
                sql += ", \n" + f;
            }
        }
        sql += "\n)";
        return sql;
    }

    public String createColumnDefiniton(ColumnDefinition column) {
        if (tableName == null || columns.isEmpty()) {
            return null;
        }
        String colName = DerbyBase.fixedIdentifier(column.getColumnName());
        String def = colName + " ";
        ColumnType type = column.getType();
        switch (type) {
            case String:
            case File:
            case Image:
            case Enumeration:
            case EnumerationEditable:
                def += "VARCHAR(" + column.getLength() + ")";
                break;
            case Color:
                def += "VARCHAR(16)";
                break;
            case Double:
            case Longitude:
            case Latitude:
                def += "DOUBLE";
                break;
            case Float:
                def += "FLOAT";
                break;
            case Long:
                def += "BIGINT";
                break;
            case Integer:
                def += "INT";
                break;
            case Short:
            case EnumeratedShort:
                def += "SMALLINT";
                break;
            case Boolean:
                def += "BOOLEAN";
                break;
            case NumberBoolean:
                def += "SMALLINT";
                break;
            case Datetime:
                def += "TIMESTAMP";
                break;
            case Date:
                def += "DATE";
                break;
            case Era:
                def += "BIGINT";
                break;
            case Clob:
                def += "CLOB";
                break;
            case Blob:
                def += "BLOB";
                break;
            default:
                MyBoxLog.debug(colName + " " + type);
                return null;
        }
        if (column.isAuto()) {
            def += " NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1)";

        } else if (column.isNotNull()) {
            def += " NOT NULL WITH DEFAULT " + column.dbDefaultValue();
        } else if (column.getDefaultValue() != null) {
            def += " WITH DEFAULT " + column.dbDefaultValue();
        }
        return def;
    }

    public boolean createTable(Connection conn) {
        if (conn == null) {
            return false;
        }
        String sql = null;
        try {
            sql = createTableStatement();
            conn.createStatement().executeUpdate(sql);
//            MyBoxLog.console(sql);
            return true;
        } catch (Exception e) {
            MyBoxLog.debug(e, sql);
            return false;
        }
    }

    public final boolean createTable(Connection conn, boolean dropExisted) {
        if (conn == null || tableName == null) {
            return false;
        }
        try {
            if (DerbyBase.exist(conn, tableName) > 0) {
                if (!dropExisted) {
                    return true;
                }
                dropTable(conn);
                conn.commit();
            }
            createTable(conn);
            return true;
        } catch (Exception e) {
            MyBoxLog.debug(e);
            return false;
        }
    }

    public boolean dropTable(Connection conn) {
        if (conn == null) {
            return false;
        }
        String sql = null;
        try {
            sql = "DROP TABLE " + DerbyBase.fixedIdentifier(tableName);
            conn.createStatement().executeUpdate(sql);
            return true;
        } catch (Exception e) {
            MyBoxLog.debug(e, sql);
            return false;
        }
    }

    public boolean dropColumn(Connection conn, String colName) {
        if (conn == null || colName == null || colName.isBlank()) {
            return false;
        }
        String sql = null;
        try {
            sql = "ALTER TABLE " + DerbyBase.fixedIdentifier(tableName)
                    + " DROP COLUMN " + DerbyBase.fixedIdentifier(colName);
//            MyBoxLog.console(sql);
            return conn.createStatement().executeUpdate(sql) >= 0;
        } catch (Exception e) {
            MyBoxLog.debug(e, sql);
            return false;
        }
    }

    public boolean addColumn(Connection conn, ColumnDefinition column) {
        if (conn == null || column == null) {
            return false;
        }
        String sql = null;
        try {
            sql = "ALTER TABLE " + DerbyBase.fixedIdentifier(tableName)
                    + " ADD COLUMN  " + createColumnDefiniton(column);
            return conn.createStatement().executeUpdate(sql) >= 0;
        } catch (Exception e) {
            MyBoxLog.debug(e, sql);
            return false;
        }
    }

    public void setId(D source, D target) {
        if (source == null || target == null || idColumnName == null) {
            return;
        }
        setValue(target, idColumnName, getValue(source, idColumnName));
    }

    // https://stackoverflow.com/questions/18555122/create-instance-of-generic-type-in-java-when-parameterized-type-passes-through-h?r=SearchResults
    // https://docs.oracle.com/en/java/javase/14/docs/api/java.base/java/lang/Class.html#newInstance()
    public D newData() {
        try {
            Class<D> entityClass = (Class<D>) ((ParameterizedType) getClass().getGenericSuperclass()).getActualTypeArguments()[0];
            D data = entityClass.getDeclaredConstructor().newInstance();
            return data;
        } catch (Exception e) {
            MyBoxLog.debug(e);
            return null;
        }
    }

    public String name() {
        return tableName;
    }

    public List<String> columnNames() {
        List<String> names = new ArrayList<>();
        for (int i = 0; i < columns.size(); ++i) {
