package com.teemlink.sync.dao;

import cn.myapps.util.StringUtil;
import com.teemlink.sync.model.ColumnValue;
import com.teemlink.sync.model.DataObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

/**
 * 同步dao
 */
public class SyncDao {

    private static Logger log = LoggerFactory.getLogger(SyncDao.class);

    private Connection connection;

    public SyncDao(Connection conn) throws Exception {
        this.connection = conn;
    }

    /**
     * 新建
     * @param dataObject
     * @return
     */
    public boolean create(DataObject dataObject) throws Exception{
        boolean flag = false;

        PreparedStatement stmt = null;
        StringBuffer sql = new StringBuffer();
        sql.append(" insert into ");
        sql.append(dataObject.getTableName());
        sql.append(" ( ");
        List<ColumnValue> columnValues = dataObject.getColumnValues();
        for(int i = 0; i < columnValues.size(); i++){
            ColumnValue columnValue = columnValues.get(i);
            if(i != 0){
                sql.append(",");
            }
            sql.append(columnValue.getName());
        }
        sql.append(" ) ");
        sql.append(" values ( ");
        for(int i = 0; i < columnValues.size(); i++){
            if(i != 0){
                sql.append(",");
            }
            sql.append("?");
        }
        sql.append(" ) ");
        log.debug(sql.toString());
        int index = 0;
        try {
            stmt = connection.prepareStatement(sql.toString());
            setParameters(columnValues, stmt, index);
            stmt.execute();
            flag = true;
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e);
        } finally {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return flag;
    }

    /**
     * 更新
     * @param dataObject
     * @return
     */
    public boolean update(DataObject dataObject) throws Exception{
        boolean flag = false;

        PreparedStatement stmt = null;
        StringBuffer sql = new StringBuffer();
        sql.append(" update ");
        sql.append(dataObject.getTableName());
        sql.append(" set ");
        List<ColumnValue> columnValues = dataObject.getColumnValues();
        for(int i = 0; i < columnValues.size(); i++){
            ColumnValue columnValue = columnValues.get(i);
            if(i != 0){
                sql.append(",");
            }
            sql.append(columnValue.getName());
            sql.append("=?");
        }
        sql.append(" where id=?");

        log.debug(sql.toString());
        int index = 0;
        try {
            stmt = connection.prepareStatement(sql.toString());
            setParameters(columnValues, stmt, index);
            stmt.setObject(columnValues.size()+1, dataObject.getId());
            stmt.execute();
            flag = true;
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e);
        } finally {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return flag;
    }

    /**
     * 删除
     * @param dataObject
     * @return
     */
    public boolean delete(DataObject dataObject) throws Exception{
        boolean flag = false;

        PreparedStatement stmt = null;
        StringBuffer sql = new StringBuffer();
        sql.append(" delete from ");
        sql.append(dataObject.getTableName());
        sql.append(" where id=? ");

        log.debug(sql.toString());
        int index = 0;
        try {
            stmt = connection.prepareStatement(sql.toString());
            stmt.setObject(++index, dataObject.getId());
            stmt.execute();
            flag = true;
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception(e);
        } finally {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return flag;
    }

    /**
     * 设置参数
     */
    private void setParameters(List<ColumnValue> columnValues, PreparedStatement stmt, int index) throws Exception{
        for(Iterator<ColumnValue> iterator = columnValues.iterator();iterator.hasNext();){
            ColumnValue columnValue = iterator.next();
            String type = columnValue.getType();
            switch (type){
                case ColumnValue.TYPE_DATE:
                    Date dateValue = parseDate(columnValue.getValue());
                    if (dateValue == null) {
                        stmt.setNull(++index, Types.DATE);
                    } else {
                        stmt.setDate(++index, new java.sql.Date(dateValue.getTime()));
                    }
                    break;
                case ColumnValue.TYPE_TEXT:
                    String stringValue = columnValue.getValue();
                    if (stringValue == null) {
                        stmt.setNull(++index, Types.VARCHAR);
                    } else {
                        stmt.setString(++index, stringValue);
                    }
                    break;
                case ColumnValue.TYPE_DATETIME:
                    Date dateTimeValue = parseDateTime(columnValue.getValue());
                    if (dateTimeValue == null) {
                        stmt.setNull(++index, Types.TIMESTAMP);
                    } else {
                        stmt.setTimestamp(++index, new Timestamp(dateTimeValue.getTime()));
                    }
                    break;
                case ColumnValue.TYPE_TIMESTAMP:
                    Timestamp timestampValue = new Timestamp(parseTimestamp(columnValue.getValue()).getTime());
                    if (timestampValue == null) {
                        stmt.setNull(++index, Types.TIMESTAMP);
                    } else {
                        stmt.setTimestamp(++index, timestampValue);
                    }
                    break;
                case ColumnValue.TYPE_DOUBLE:
                    if(StringUtil.isNumber(columnValue.getValue())){
                        double doubleValue = StringUtil.parseDouble(columnValue.getValue());
                        stmt.setDouble(++index, doubleValue);
                    } else {
                        stmt.setNull(++index, Types.NUMERIC);
                    }
                    break;
                case ColumnValue.TYPE_INTEGER:
                    if(StringUtil.isNumber(columnValue.getValue())){
                    long longValue = StringUtil.parseLong(columnValue.getValue());
                        stmt.setLong(++index, longValue);
                    } else {
                        stmt.setNull(++index, Types.NUMERIC);
                    }
                    break;
                case ColumnValue.TYPE_BOOLEAN:
                    if (columnValue.getValue() != null) {
                        boolean booleanValue = "1".equals(columnValue.getValue()) || "true".equals(columnValue.getValue());
                        stmt.setBoolean(++index, booleanValue);
                    }
                    else {
                        stmt.setNull(++index, Types.BOOLEAN);
                    }
                    break;
            }
        }
    }

    private Date parseDateTime(String time){
        try{
            SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            return format.parse(time);
        } catch (Exception e){
            try{
                return new Date(time);
            } catch (Exception x){
                log.debug(x.getMessage());
            }
        }
        return null;
    }

    private Date parseDate(String time){
        try{
            SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
            return format.parse(time);
        } catch (Exception e){
            try{
                return new Date(time);
            } catch (Exception x){
                log.debug(x.getMessage());
            }
        }
        return null;
    }

    private Date parseTimestamp(String time){
        try{
            SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
            return format.parse(time);
        } catch (Exception e){
            try{
                return new Date(time);
            } catch (Exception x){
                log.debug(x.getMessage());
            }
        }
        return null;
    }
}
