/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.ctecinf.orm;

import br.com.ctecinf.Database;
import br.com.ctecinf.combobox.ComboBoxModel;
import br.com.ctecinf.table.TableColumn;
import br.com.ctecinf.table.TableModel;
import br.com.ctecinf.autocomplete.AutoCompleteModel;
import br.com.ctecinf.swing.OptionPane;
import java.io.Serializable;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;

/**
 *
 * @author Cássio Conceição
 * @since 24/09/2019
 * @version 1909
 * @see http://ctecinf.com.br/
 */
public class Model implements Comparable<Model>, Serializable {

    protected String primaryKey = "id";

    private Object id;

    /**
     *
     * @return Valor da chave primária
     */
    public Long getId() {
        return id == null ? null : Long.parseLong(id.toString());
    }

    /**
     *
     * @param <T>
     * @param id Valor da chave primária
     * @return
     * @throws Exception
     */
    public <T extends Model> T setId(Object id) throws Exception {

        this.id = id;

        if (id != null) {

            Class<T> model = (Class<T>) getClass();

            String where = getTableName(model) + "." + primaryKey + " = " + id;
            List<T> list = find(model, where, -1);

            if (list == null) {
                this.id = null;
                return null;
            }

            T obj = list.get(0);

            for (Field field : model.getDeclaredFields()) {

                Field f = obj.getClass().getDeclaredField(field.getName());
                f.setAccessible(true);

                field.setAccessible(true);
                field.set(this, f.get(obj));
            }
        }

        return (T) this;
    }

    /**
     * Recupera lista de nomes e valores das colunas do banco de dados
     *
     * @param <T>
     * @return Map nome da coluna no banco de dados e seu valor
     * @throws Exception
     */
    public <T extends Model> Map<String, Object> getParams() throws Exception {

        Map<String, Object> params = new HashMap();
        params.put(primaryKey, id);

        for (Field field : getClass().getDeclaredFields()) {

            if (field.isAnnotationPresent(Column.class)) {

                Column column = field.getAnnotation(Column.class);

                String name = column.name().isEmpty() ? Database.java2Database(field.getName()) : column.name();

                field.setAccessible(true);

                Object obj = field.get(this);

                if (obj != null && column.join().isAssignableFrom(NullModel.class)) {

                    try {

                        if (field.getType().isAssignableFrom(String.class) && !obj.getClass().isAssignableFrom(String.class)) {
                            obj = obj.toString();
                        } else if (field.getType().isAssignableFrom(BigDecimal.class) && !obj.getClass().isAssignableFrom(BigDecimal.class)) {
                            obj = BigDecimal.valueOf(Double.valueOf(obj.toString()));
                        } else if ((field.getType().isAssignableFrom(Boolean.class) && !obj.getClass().isAssignableFrom(Boolean.class)) || (field.getType().isAssignableFrom(boolean.class) && !obj.getClass().isAssignableFrom(boolean.class))) {
                            obj = obj.toString().equalsIgnoreCase("sim") || obj.toString().equalsIgnoreCase("true") || obj.toString().equalsIgnoreCase("yes") || obj.toString().equalsIgnoreCase("1");
                        } else if ((field.getType().isAssignableFrom(Double.class) && !obj.getClass().isAssignableFrom(Double.class)) || (field.getType().isAssignableFrom(double.class) && !obj.getClass().isAssignableFrom(double.class))) {
                            obj = Double.parseDouble(obj.toString());
                        } else if ((field.getType().isAssignableFrom(Float.class) && !obj.getClass().isAssignableFrom(Float.class)) || (field.getType().isAssignableFrom(float.class) && !obj.getClass().isAssignableFrom(float.class))) {
                            obj = Float.parseFloat(obj.toString());
                        } else if ((field.getType().isAssignableFrom(Long.class) && !obj.getClass().isAssignableFrom(Long.class)) || (field.getType().isAssignableFrom(long.class) && !obj.getClass().isAssignableFrom(long.class))) {
                            obj = Long.parseLong(obj.toString());
                        } else if ((field.getType().isAssignableFrom(Integer.class) && !obj.getClass().isAssignableFrom(Integer.class)) || (field.getType().isAssignableFrom(int.class) && !obj.getClass().isAssignableFrom(int.class))) {
                            obj = Integer.parseInt(obj.toString());
                        }

                    } catch (Exception ex) {
                        throw new Exception("Valor do campo [" + field.getName() + "] não corresponde ao tipo.\n" + ex.getMessage());
                    }
                }

                if (!column.join().isAssignableFrom(NullModel.class)) {
                    params.put(name.toLowerCase(), extractId((T) obj));
                } else {
                    params.put(name.toLowerCase(), obj);
                }
            }
        }

        return params;
    }

    /**
     * Seta os valores de determinados fields
     *
     * @param params Nome do field e valor
     * @throws Exception
     */
    public void setValues(Map<String, Object> params) throws Exception {

        if (params.containsKey(primaryKey)) {
            this.id = params.get(primaryKey);
        }

        for (Map.Entry<String, Object> entry : params.entrySet()) {

            Field field = getClass().getDeclaredField(Database.database2Java(entry.getKey()));

            if (field != null) {

                field.setAccessible(true);

                Object value = params.get(entry.getKey());

                if (value instanceof Number) {

                    Number num = (Number) value;

                    if (field.getType().isAssignableFrom(Long.class) || field.getType().isAssignableFrom(long.class)) {
                        value = num.longValue();
                    } else if (field.getType().isAssignableFrom(Integer.class) || field.getType().isAssignableFrom(int.class)) {
                        value = num.intValue();
                    } else if (field.getType().isAssignableFrom(Double.class) || field.getType().isAssignableFrom(double.class)) {
                        value = num.doubleValue();
                    } else if (field.getType().isAssignableFrom(Float.class) || field.getType().isAssignableFrom(float.class)) {
                        value = num.floatValue();
                    } else if (field.getType().isAssignableFrom(BigDecimal.class)) {
                        value = BigDecimal.valueOf(num.doubleValue());
                    }
                }

                if (field.getType().isAssignableFrom(String.class) && value != null && !value.getClass().isAssignableFrom(String.class)) {
                    value = value.toString();
                }

                field.set(this, value);
            }
        }
    }

    /**
     * Salva registro no banco de dados
     *
     * @param <T> Tipo da classe que faz o controle
     * @return Long Identificador do registro
     * @throws Exception
     * @see      <code>
     * Derby: id BIGINT GENERATED ALWAYS AS IDENTITY(START WITH 1, INCREMENT BY 1) PRIMARY KEY<br>
     * Mysql: id BIGINT PRIMARY KEY AUTO_INCREMENT<br>
     * Postgres: id SERIAL PRIMARY KEY<br>
     * Firbird: Através de TRIGGER
     * </code>
     */
    public <T extends Model> Long save() throws Exception {

        String sql;
        Map<String, Object> params = getParams();

        if (id == null) {
            sql = Database.insertScript(getTableName((Class) getClass()), primaryKey, params);
        } else {
            sql = Database.updateScript(getTableName((Class) getClass()), primaryKey, params);
        }

        id = Database.executeUpdate(sql);

        return getId();
    }

    /**
     * Apaga registro no banco de dados
     *
     * @return boolean
     * @throws Exception
     */
    public boolean delete() throws Exception {

        if (id == null) {
            throw new Exception("Identificador nulo.");
        }

        String sql = "DELETE FROM " + getTableName((Class) getClass()) + " WHERE " + primaryKey + "='" + id + "'";

        Long ret = Database.executeUpdate(sql);

        id = null;

        return ret != null && ret > -1;
    }

    /**
     * Retorna todos os registros do banco de dados
     *
     * @param <T> Tipo da classe que faz o controle
     * @return List
     * @throws Exception
     */
    public <T extends Model> List<T> findAll() throws Exception {
        return find((Class<T>) getClass(), null, -1);
    }

    /**
     * Consulta no banco de dados lista de registros associados a esse objeto
     *
     * @param <T> Tipo da classe que faz o controle
     * @param model Classe modelo que estende de <i>Controller</i>
     * @return List Lista com 'n' registros associados a este objeto
     * @throws Exception
     */
    public <T extends Model> List<T> listOf(Class<T> model) throws Exception {

        if (id == null) {
            return null;
        }

        String where = getTableName((Class<T>) getClass()) + "." + primaryKey + "=" + id;

        return find(model, where, -1);
    }

    /**
     * Recupera um table model de uma determinada classe controller
     *
     * @param <T> Tipo da classe que faz o controle
     * @return
     * @throws java.lang.Exception
     */
    public <T extends Model> TableModel getTableModel() throws Exception {
        return getTableModel((Class<T>) getClass());
    }

    /**
     * Recupera um table model de uma determinada classe controller
     *
     * @param <T> Tipo da classe que faz o controle
     * @param model Modelo para recuperar todos os objetos relacionado ao
     * <i>model(controller)</i> instâciado que chama o método.
     * @return
     * @throws java.lang.Exception
     */
    public <T extends Model> TableModel getTableModel(Class<T> model) throws Exception {

        List<TableColumn> columns = new ArrayList();
        List<T> data = Collections.synchronizedList(new ArrayList());

        columns.add(new TableColumn(0, primaryKey, "Código", Long.class, 6));

        int index = 1;

        for (Field field : model.getDeclaredFields()) {

            if (field.isAnnotationPresent(Column.class) && field.getAnnotation(Column.class).tableDisplay()) {

                Column column = field.getAnnotation(Column.class);

                String label = column.label().isEmpty() ? column.name() : column.label();

                columns.add(new TableColumn(index, field.getName(), label, field.getType(), field.getName().length()));

                index++;
            }
        }

        String where = null;

        if (!getClass().isAssignableFrom(model) && id != null) {
            where = getTableName(getClass()) + "." + primaryKey + "=" + id;
        } else if (!getClass().isAssignableFrom(model) && id == null) {
            return new TableModel(columns, data);
        }

        String query = query(model, where);

        try (Connection conn = Database.openConnection(); Statement st = conn.createStatement(); ResultSet rs = st.executeQuery(query)) {
            while (rs.next()) {
                data.add(newInstance(model, rs, columns));
            }
        }

        return new TableModel(columns, data);
    }

    /**
     * Recupera um modelo de lista de uma determinada classe controller
     *
     * @param <T> Tipo da classe que faz o controle
     *
     * @return
     */
    public <T extends Model> AutoCompleteModel<T> getAutoCompleteModel() {

        final AutoCompleteModel<T> listModel = new AutoCompleteModel();

        final Class<T> model = (Class<T>) getClass();

        new Thread(new Runnable() {
            @Override
            public void run() {

                try {

                    try (Connection conn = Database.openConnection(); Statement st = conn.createStatement(); ResultSet rs = st.executeQuery(query(model, null))) {

                        while (rs.next()) {
                            T obj = (T) newInstance(model, rs);
                            listModel.add(obj);
                        }

                        listModel.endLoad();
                    }

                } catch (Exception ex) {
                    OptionPane.error(ex);
                    listModel.endLoad();
                }
            }
        }).start();

        return listModel;
    }

    /**
     * Recupera um modelo de lista de uma determinada classe controller
     *
     * @param <T> Tipo da classe que faz o controle
     *
     * @return
     */
    public <T extends Model> ComboBoxModel<T> getComboBoxModel() {

        ComboBoxModel<T> comboModel = new ComboBoxModel();

        Class<T> model = (Class<T>) getClass();

        try (Connection conn = Database.openConnection(); Statement st = conn.createStatement(); ResultSet rs = st.executeQuery(query(model, null))) {

            while (rs.next()) {
                T obj = (T) newInstance(model, rs);
                comboModel.addElement(obj);
                comboModel.setWidth(Math.max(comboModel.getWidth(), obj.toString().length()));
            }

        } catch (Exception ex) {
            OptionPane.error(ex);
        }

        return comboModel;
    }

    /**
     * Tipo do campo
     *
     * @param <T> Tipo da classe que faz o controle
     * @param fieldName Nome do campo da classe que faz o controle
     * @return
     */
    public <T extends Model> Class<?> getType(String fieldName) {
        return getType((Class<T>) getClass(), fieldName);
    }

    /**
     * Tipo do campo
     *
     * @param <T> Tipo da classe que faz o controle
     * @param fieldName Nome do campo da classe que faz o controle
     * @param model Classe modelo que estende de <i>Controller</i>
     * @return
     */
    public <T extends Model> Class<?> getType(Class<T> model, String fieldName) {

        try {
            Field field = model.getDeclaredField(fieldName);
            return field.getType();
        } catch (NoSuchFieldException | SecurityException ex) {
            return Object.class;
        }
    }

    /**
     * Verifica se campo é not null
     *
     * @param <T> Tipo da classe que faz o controle
     * @param fieldName Nome do campo da classe que faz o controle
     * @return
     */
    public <T extends Model> boolean isNotNull(String fieldName) {
        return isNotNull((Class<T>) getClass(), fieldName);
    }

    /**
     * Verifica se campo é not null
     *
     * @param <T> Tipo da classe que faz o controle
     * @param fieldName Nome do campo da classe que faz o controle
     * @param model Classe modelo que estende de <i>Controller</i>
     * @return
     */
    public <T extends Model> boolean isNotNull(Class<T> model, String fieldName) {

        try {

            Field field = model.getDeclaredField(fieldName);

            if (field.isAnnotationPresent(Column.class)) {
                return field.getAnnotation(Column.class).isNotNull();
            } else {
                return false;
            }

        } catch (NoSuchFieldException | SecurityException ex) {
            return false;
        }
    }

    /**
     * Rótulo do campo
     *
     * @param <T> Tipo da classe que faz o controle
     * @param fieldName Nome do campo da classe que faz o controle
     * @return
     */
    public <T extends Model> String getLabel(String fieldName) {
        return getLabel((Class<T>) getClass(), fieldName);
    }

    /**
     * Rótulo do campo
     *
     * @param <T> Tipo da classe que faz o controle
     * @param fieldName Nome do campo da classe que faz o controle
     * @param model Classe modelo que estende de <i>Controller</i>
     * @return
     */
    public <T extends Model> String getLabel(Class<T> model, String fieldName) {

        try {

            Field field = model.getDeclaredField(fieldName);

            if (field.isAnnotationPresent(Column.class)) {
                return field.getAnnotation(Column.class).label();
            } else {
                return null;
            }

        } catch (NoSuchFieldException | SecurityException ex) {
            return null;
        }
    }

    /**
     * Valor default para o campo
     *
     * @param <T> Tipo da classe que faz o controle
     * @param fieldName Nome do campo da classe que faz o controle
     * @return
     */
    public <T extends Model> String[] getDefaultValues(String fieldName) {
        return getDefaultValues((Class<T>) getClass(), fieldName);
    }

    /**
     * Valor default para o campo
     *
     * @param <T> Tipo da classe que faz o controle
     * @param fieldName Nome do campo da classe que faz o controle
     * @param model Classe modelo que estende de <i>Controller</i>
     * @return
     */
    public <T extends Model> String[] getDefaultValues(Class<T> model, String fieldName) {

        try {

            Field field = model.getDeclaredField(fieldName);

            if (field.isAnnotationPresent(Column.class)) {
                return field.getAnnotation(Column.class).defaultValues();
            } else {
                return new String[]{};
            }

        } catch (NoSuchFieldException | SecurityException ex) {
            return new String[]{};
        }
    }

    /**
     * Consulta no banco de dados
     *
     * @param <T>
     * @param model
     * @param where Sempre utilizar o nome da tabela antes do campo. Ex.:
     * tabela.coluna = ''
     * @param maxResults
     * @return
     * @throws Exception
     */
    protected <T extends Model> List<T> find(Class<T> model, String where, int maxResults) throws Exception {

        List<T> list = new ArrayList();

        try (Connection conn = Database.openConnection(); Statement st = conn.createStatement()) {

            if (maxResults > 0) {
                st.setMaxRows(maxResults);
            }

            try (ResultSet rs = st.executeQuery(query(model, where))) {

                while (rs.next()) {
                    T obj = (T) newInstance(model, rs);
                    list.add(obj);
                }
            }
        }

        return list.isEmpty() ? null : list;
    }

    private <T extends Model> Object extractId(T obj) throws Exception {

        if (obj == null) {
            return null;
        }

        Class model = obj.getClass().getSuperclass();

        if (model.isAssignableFrom(Model.class)) {
            Field field = model.getDeclaredField("id");
            field.setAccessible(true);
            return field.get(obj);
        }

        return null;
    }

    private static <T extends Model> String getTableName(Class<T> model) {
        return model.isAnnotationPresent(Table.class) ? model.getAnnotation(Table.class).value() : Database.java2Database(model.getSimpleName());
    }

    private <T extends Model> String getPrimaryKey(Class<T> model) throws Exception {

        Field field = model.getSuperclass().getDeclaredField("primaryKey");
        field.setAccessible(true);

        return (String) field.get(model.newInstance());
    }

    private <T extends Model> T newInstance(Class<T> model, ResultSet rs) throws Exception {
        return newInstance(model, rs, null);
    }

    private <T extends Model> T newInstance(Class<T> model, ResultSet rs, List<TableColumn> columns) throws Exception {

        String table = getTableName(model);

        String idColumn = table + "_" + primaryKey;
        Object idValue = Database.toValue(rs, idColumn);

        if (idValue != null) {

            T obj = model.newInstance();
            model.getSuperclass().getDeclaredField("id").set(obj, idValue);

            for (Field field : model.getDeclaredFields()) {

                if (field.isAnnotationPresent(Column.class)) {

                    Column column = field.getAnnotation(Column.class);

                    String name = column.name().isEmpty() ? Database.java2Database(field.getName()) : column.name();
                    TableColumn tableColumn = getTableColumn(columns, name);

                    field.setAccessible(true);

                    if (field.getAnnotation(Column.class).join().isAssignableFrom(NullModel.class)) {

                        Object o = Database.toValue(rs, table + "_" + name);

                        if (tableColumn != null && o != null && o.toString().trim().length() > tableColumn.getWidth()) {
                            tableColumn.setWidth(o.toString().trim().length());
                        }

                        field.set(obj, o);

                    } else {

                        Object o = newInstance((Class<T>) field.getType(), rs, columns);

                        if (tableColumn != null && o != null && o.toString().trim().length() > tableColumn.getWidth()) {
                            tableColumn.setWidth(o.toString().trim().length());
                        }

                        field.set(obj, o);
                    }
                }
            }

            return obj;

        } else {
            return null;
        }
    }

    private TableColumn getTableColumn(List<TableColumn> columns, String name) {

        if (columns != null && name != null) {

            for (TableColumn column : columns) {

                if (column.getName().equalsIgnoreCase(name)) {
                    return column;
                }
            }
        }

        return null;
    }

    private <T extends Model> String query(Class<T> model, String where) throws Exception {

        String table = getTableName(model);
        String join = setJoin(model);

        String cols = table + "." + primaryKey + " AS " + table + "_" + primaryKey;
        cols += setCols(model);

        return "SELECT " + cols + " FROM " + table + join + (where == null ? "" : " WHERE " + where);
    }

    private <T extends Model> String setCols(Class<T> model) throws Exception {

        String table = getTableName(model);
        String cols = "";

        for (Field field : model.getDeclaredFields()) {

            if (field.isAnnotationPresent(Column.class)) {

                String name = field.getAnnotation(Column.class).name().isEmpty() ? Database.java2Database(field.getName()) : field.getAnnotation(Column.class).name();
                cols += ", " + table + "." + name + " AS " + table + "_" + name;

            }

            if (field.isAnnotationPresent(Column.class) && !field.getAnnotation(Column.class).join().isAssignableFrom(NullModel.class)) {

                Column column = field.getAnnotation(Column.class);
                String name = column.name().isEmpty() ? Database.java2Database(field.getName()) : column.name();

                Class<T> ref = (Class<T>) column.join();

                String tableRef = getTableName(ref);
                String idRef = getPrimaryKey(ref);

                cols += ", CASE WHEN " + table + "." + name + " IS NULL THEN NULL ELSE " + tableRef + "." + idRef + " END AS " + tableRef + "_" + idRef;
                cols += setCols(ref);
            }
        }

        return cols;
    }

    private <T extends Model> String setJoin(Class<T> model) throws Exception {

        String table = getTableName(model);
        String join = "";

        for (Field field : model.getDeclaredFields()) {

            if (field.isAnnotationPresent(Column.class) && !field.getAnnotation(Column.class).join().isAssignableFrom(NullModel.class)) {

                Column column = field.getAnnotation(Column.class);

                String name = column.name().isEmpty() ? Database.java2Database(field.getName()) : column.name();

                Class<T> ref = (Class<T>) column.join();

                String tableRef = getTableName(ref);
                String idRef = getPrimaryKey(ref);

                join += " JOIN " + tableRef + " ON CASE WHEN " + table + "." + name + " IS NULL THEN (SELECT MIN(" + tableRef + "." + idRef + ") FROM " + tableRef + ") ELSE " + table + "." + name + " END = " + tableRef + "." + idRef;
                join += setJoin(ref);
            }
        }

        return join;
    }

    @Override
    public int hashCode() {
        int hash = 3;
        hash = 53 * hash + Objects.hashCode(id);
        return hash;
    }

    @Override
    public boolean equals(Object obj) {

        if (this == obj) {
            return true;
        }

        if (obj == null) {
            return false;
        }

        if (getClass() != obj.getClass()) {
            return false;
        }

        final Model other = (Model) obj;

        return Objects.equals(id, other.id);
    }

    @Override
    public int compareTo(Model o) {

        if (o == null) {
            return 1;
        }

        return this.toString().compareToIgnoreCase(o.toString());
    }
}