package com.foc.ods.service;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.lang3.StringUtils;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.foc.conf.Constants;
import com.foc.ods.entity.OdsChangesEntity;
import com.foc.ods.entity.OdsEntity;
import com.foc.util.DBHandler;
import com.foc.util.DBHelper;
import com.foc.util.DateUtil;
import com.foc.util.IdWorker;

/**
 * 飞行计划接口
 * 
 * @author hx
 *
 */
public class ODSService {

    QueryRunner run = null;

    public ODSService() {
        try {
            this.run = new QueryRunner(DBHelper.getDataSource());
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    public void finish(final String flightDate, final String modified) {
        long startTime = System.nanoTime();
        this.deleteLastOdsData(flightDate, modified);
        long endTime  = System.nanoTime();
        long duration = endTime - startTime;
        System.out.println("【ODSService】【finish】处理时间：" + DateUtil.getDurationTime(duration));
    }
    /**
     * 删除本次未同步的数据，即这些数据已经丢失
     * 
     * @author renmb
     * @time  2016年12月16日
     * @param flightDate
     * @param modified
     * @return
     */
    public boolean deleteLastOdsData(final String flightDate, final String modified){
        try {
            if(StringUtils.isNotEmpty(flightDate)){
                
                String insert = "INSERT INTO "+Constants.FOC_ODS+"_history SELECT * FROM "+Constants.FOC_ODS+" WHERE flight_date=? AND modified !=?";
                System.out.println(insert+"["+flightDate+","+modified+"]");
                run.update(insert, flightDate, modified);
                
                String sql = "DELETE FROM "+Constants.FOC_ODS+" WHERE flight_date=? AND modified !=?";
                System.out.println(sql+"["+flightDate+","+modified+"]");
                int count = run.update(sql, flightDate, modified);
                if(count>0)
                    return true;
            }
        } catch (Exception e) {
            // TODO: handle exception
        }
        return false;
    }
    
    public void insertOdsData(JSONObject result, final String modified) {

        JSONObject dataJson = (JSONObject) result.get("data");
        JSONArray jsonArray = (JSONArray) dataJson.get("apiResp");

        if (jsonArray != null) {
            int index = 0;
            int size = jsonArray.size();
            for (int i=0;i<size;i++) {
                index++;
                try {
                    JSONObject jsonObject = jsonArray.getJSONObject(i);
                    
                    String flightNo = (String) jsonObject.get("flightNo");
                    String flightDate = (String) jsonObject.get("flightDate");
                    String staffId = (String) jsonObject.get("staffId");
                    String staffName = (String) jsonObject.get("staffName");
                    
                    System.out.print("【ODS】第【"+index+"/"+size+"】条【"+flightNo+"】【"+flightDate+"】【"+staffId+"】【"+staffName+"】");
                    
//                    String flightNo = (String) jsonObject.get("flightNo");
//                    String flightDate = (String) jsonObject.get("flightDate");
                    String depStn = (String) jsonObject.get("departStation");
                    String arrStn = (String) jsonObject.get("arrivalStation");
//                    String staffId = (String) jsonObject.get("staffId");

//                    Map<MapType, OdsEntity> odsEntityMap = new HashMap<MapType, OdsEntity>();

                    OdsEntity odsEntity = existOds(flightNo, flightDate, depStn, arrStn, staffId);
                    if (odsEntity != null) {// 已经存在则更新数据
//                        OdsEntity odsEntity = odsEntityMap.get(MapType.odsEntityType);
                        updateOds(odsEntity.getId(), jsonObject, modified);
                    } else {
                        
                        insertOds(jsonObject, modified);
                    }
                    
                    System.out.println("【正常】");
                } catch (Exception e) {
                    // TODO: handle exception
                    System.out.println("【异常】");
                }
            }
        }
    }

//    private void mergeOds(JSONObject jsonObject, final Date modified) {
//        String flightNo = (String) jsonObject.get("flightNo");
//        String flightDate = (String) jsonObject.get("flightDate");
//        String depStn = (String) jsonObject.get("departStation");
//        String arrStn = (String) jsonObject.get("arrivalStation");
//        String staffId = (String) jsonObject.get("staffId");
//
//        Map<MapType, OdsEntity> odsEntityMap = new HashMap<MapType, OdsEntity>();
//
//        if (existOds(odsEntityMap, flightNo, flightDate, depStn, arrStn, staffId)) {
//            OdsEntity odsEntity = odsEntityMap.get(MapType.odsEntityType);
//            updateOds(odsEntity.getId(), jsonObject, modified);
//        } else {
//            
//            insertOds(jsonObject, modified);
//        }
//
//    }

    private void updateOds(String odsId, JSONObject jsonObject, final String modified) {

        DBHandler dBHandler = new DBHandler();
        Map<String, Object> columnMap = new TreeMap<String, Object>();
        Map<String, Object> whereMap = new TreeMap<String, Object>();

        // 字段和值绑定
        columnMap.put("login_id", getStringValue(jsonObject.get("loginId"), ""));
        columnMap.put("rank_en_sd", getStringValue(jsonObject.get("rankEnSd"), ""));
        columnMap.put("wp_date", getStringValue(jsonObject.get("wpDate"), null));
        columnMap.put("ac_no", getStringValue(jsonObject.get("acNo"), ""));
        columnMap.put("staff_id", getStringValue(jsonObject.get("staffId"), ""));
        columnMap.put("aircraft_type", getStringValue(jsonObject.get("aircraftType"), ""));
        columnMap.put("sign_up", getStringValue(jsonObject.get("signUp"), ""));
        columnMap.put("remark", getStringValue(jsonObject.get("remark"), ""));
        columnMap.put("trng_mode", getStringValue(jsonObject.get("trngMode"), ""));
        columnMap.put("mod_time", getStringValue(jsonObject.get("modTime"), null));
        columnMap.put("flight_no", getStringValue(jsonObject.get("flightNo"), ""));
        columnMap.put("staff_name", getStringValue(jsonObject.get("staffName"), ""));
        columnMap.put("flight_date", getStringValue(jsonObject.get("flightDate"), null));
        columnMap.put("crew_type_id", getStringValue(jsonObject.get("crewTypeId"), ""));
        columnMap.put("rank_sd", getStringValue(jsonObject.get("rankSd"), ""));
        columnMap.put("dep_stn", getStringValue(jsonObject.get("departStation"), ""));
        columnMap.put("arr_stn", getStringValue(jsonObject.get("arrivalStation"), ""));
        
        // 二期新增字段
        columnMap.put("sector", getStringValue(jsonObject.get("sector"), ""));
        columnMap.put("sector_nick", getStringValue(jsonObject.get("sectorNick"), ""));
        columnMap.put("sign_up_time", getStringValue(jsonObject.get("signupTime"), null));
        columnMap.put("min_sign_up_start_time", getStringValue(jsonObject.get("minSignupStartTime"), null));
        columnMap.put("sign_up_start_time", getStringValue(jsonObject.get("signUpStartTime"), null));
        columnMap.put("max_sign_up_start_time", getStringValue(jsonObject.get("maxSignupStartTime"), null));
        columnMap.put("sign_up_end_time", getStringValue(jsonObject.get("signUpEndTime"), null));
        columnMap.put("modified", modified);
        
        whereMap.put("id", odsId);

        dBHandler.update(Constants.FOC_ODS, columnMap, whereMap);
    }
    public String getStringValue(Object object, String want){
        
        if(object == null || StringUtils.isEmpty(String.valueOf(object)))
            return want;
        return String.valueOf(object);
    }

    private void insertOds(JSONObject jsonObject, final String modified) {
        
        List<Map<String, Object>> columnMapList = new ArrayList<Map<String, Object>>();
        Map<String, Object> columnMap = new TreeMap<String, Object>();
        // 字段和值绑定
        // columnMap.put("id", jsonObject.get("id"));
        columnMap.put("id", IdWorker.getNextId());
        columnMap.put("login_id", getStringValue(jsonObject.get("loginId"), ""));
        columnMap.put("rank_en_sd", getStringValue(jsonObject.get("rankEnSd"), ""));
        columnMap.put("wp_date", getStringValue(jsonObject.get("wpDate"), null));
        columnMap.put("ac_no", getStringValue(jsonObject.get("acNo"), ""));
        columnMap.put("staff_id", getStringValue(jsonObject.get("staffId"), ""));
        columnMap.put("aircraft_type", getStringValue(jsonObject.get("aircraftType"), ""));
        columnMap.put("sign_up", getStringValue(jsonObject.get("signUp"), ""));
        columnMap.put("remark", getStringValue(jsonObject.get("remark"), ""));
        columnMap.put("trng_mode", getStringValue(jsonObject.get("trngMode"), ""));
        columnMap.put("mod_time", getStringValue(jsonObject.get("modTime"), null));
        columnMap.put("flight_no", getStringValue(jsonObject.get("flightNo"), ""));
        columnMap.put("staff_name", getStringValue(jsonObject.get("staffName"), ""));
        columnMap.put("flight_date", getStringValue(jsonObject.get("flightDate"), ""));
        columnMap.put("crew_type_id", getStringValue(jsonObject.get("crewTypeId"), ""));
        columnMap.put("rank_sd", getStringValue(jsonObject.get("rankSd"), ""));
        columnMap.put("dep_stn", getStringValue(jsonObject.get("departStation"), ""));
        columnMap.put("arr_stn", getStringValue(jsonObject.get("arrivalStation"), ""));
        // 二期新增字段
        columnMap.put("sector", getStringValue(jsonObject.get("sector"), ""));
        columnMap.put("sector_nick", getStringValue(jsonObject.get("sectorNick"), ""));
        columnMap.put("sign_up_time", getStringValue(jsonObject.get("signupTime"), null));
        columnMap.put("min_sign_up_start_time", getStringValue(jsonObject.get("minSignupStartTime"), null));
        columnMap.put("sign_up_start_time", getStringValue(jsonObject.get("signUpStartTime"), null));
        columnMap.put("max_sign_up_start_time", getStringValue(jsonObject.get("maxSignupStartTime"), null));
        columnMap.put("sign_up_end_time", getStringValue(jsonObject.get("signUpEndTime"), null));
        
        columnMap.put("modified", modified);
        
        columnMapList.add(columnMap);

        /**
         * 单条插入，保证一条语句插入报错，还能往下执行
         */
        DBHandler dBHandler = new DBHandler();
        dBHandler.batchInsert(Constants.FOC_ODS, columnMapList);

    }

    private OdsEntity existOds(String flightNo, String flightDate, String depStn,
            String arrStn, String staffId) {
        ResultSetHandler<List<OdsEntity>> handler = new BeanListHandler<OdsEntity>(OdsEntity.class);
        String query = "SELECT * FROM foc_ods WHERE flight_no =? and flight_date = ? and dep_stn = ? and arr_stn = ? and staff_id = ?";
        List<OdsEntity> list = null;
        try {
            list = run.query(query, handler, flightNo, flightDate, depStn, arrStn, staffId);
            if (list != null && list.size() > 0) {
//                odsEntityMap.put(MapType.odsEntityType, list.get(0));
                return list.get(0);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
//        if (list != null && list.size() > 0) {
//            odsEntityMap.put(MapType.odsEntityType, list.get(0));
//            return true;
//        }

        return null;
    }

//    private boolean deleteOds(String flightNo, String flightDate, String depStn,
//            String arrStn) {
//        ResultSetHandler<List<OdsEntity>> handler = new BeanListHandler<OdsEntity>(OdsEntity.class);
//        String query = "DELETE FROM foc_ods WHERE flight_no ='" + flightNo + "' and flight_date = '" + flightDate
//                + "' and dep_stn = '" + depStn + "' and arr_stn = '" + arrStn + "'";
//        try {
//            run.update(query);
//            return true;
//        } catch (SQLException e) {
//            // TODO Auto-generated catch block
//            e.printStackTrace();
//        }
//        return false;
//    }
    
    public void insertOdsChangesAndDetailData(JSONObject result, final String modified) {

        JSONObject dataJson = (JSONObject) result.get("data");
        JSONArray odsChangesArray = (JSONArray) dataJson.get("fltLegsChanges");

        if (odsChangesArray != null) {
            int index = 0;
            int size = odsChangesArray.size();
            for (int i=0;i<size;i++) {
                
                index++;
                try {
                    JSONObject jsonObject = odsChangesArray.getJSONObject(i);
                    
                    String reason  = jsonObject.getString("reason");
                    String subtime = jsonObject.getString("subtime");
                    System.out.print("【ODS CHANGE】第【"+index+"/"+size+"】条【"+subtime+"】【"+reason+"】");
//                    System.out.println("【正常】");
                    
                    mergeOdsChangesAndDetail(jsonObject, modified);
                    
                    System.out.println("【正常】");
                } catch (Exception e) {
                    // TODO: handle exception
                    System.out.println("【异常】");
                }
            }
        }

    }

    private void mergeOdsChangesAndDetail(JSONObject jsonObject, final String modified) {
//        Integer id = (Integer) jsonObject.get("id");
        String odsChangesId = getStringValue(jsonObject.get("id"), null);
        if(StringUtils.isEmpty(odsChangesId))
            return ;
//        Map<MapType, OdsChangesEntity> odsChangesEntityMap = new HashMap<MapType, OdsChangesEntity>();
        
        OdsChangesEntity changesEntity = existOdsChanges(odsChangesId);
        if (changesEntity != null)
            updateOdsChanges(odsChangesId, jsonObject, modified);
        else
            insertOdsChanges(jsonObject, modified);

        JSONArray odsDetailArray = (JSONArray) jsonObject.get("details");
        String changeSno = (String) jsonObject.get("sno");
//        String changeSno = (String) jsonObject.get("id");

        mergeOdsDetailArray(odsChangesId, changeSno, odsDetailArray, modified);
    }

    private void mergeOdsDetailArray(String changeId, String changeSno, JSONArray odsDetailArray, final String modified) {

        if (odsDetailArray != null) {
            for (Object object : odsDetailArray) {
                JSONObject jsonObject = (JSONObject) object;
                mergeOdsDetail(changeId, changeSno, jsonObject, modified);
            }
        }
    }

    private void mergeOdsDetail(String changeId, String changeSno, JSONObject jsonObject, final String modified) {

//        String flightNo = (String) jsonObject.get("flightNo");
//        String flightDate = (String) jsonObject.get("fltDate");
//        String depStn = (String) jsonObject.get("depStn");
//        String arrStn = (String) jsonObject.get("arrStn");
//        Map<MapType, OdsDetailEntity> odsDetailEntityMap = new HashMap<MapType, OdsDetailEntity>();
//        if (existOdsDetail(odsDetailEntityMap, changeSno, flightNo, flightDate, depStn, arrStn)) {
//            OdsDetailEntity odsDetailEntity = odsDetailEntityMap.get(MapType.odsDetailEntityType);
//            updateDetail(odsDetailEntity.getId(), jsonObject);
//        } else {
//            insertDetail(changeId, changeSno, jsonObject);
//        }
        deleteOdsDetail(changeId);// 删除记录
        insertDetail(changeId, changeSno, jsonObject, modified);
    }

    private void insertDetail(String changeId, String changeSno, JSONObject jsonObject, final String modified) {

        List<Map<String, Object>> columnMapList = new ArrayList<Map<String, Object>>();
        Map<String, Object> columnMap = new TreeMap<String, Object>();
        // 字段和值绑定
        columnMap.put("id", IdWorker.getNextId());
         columnMap.put("change_id", changeId);
        columnMap.put("change_sno", changeSno);
        columnMap.put("flt_date", getStringValue(jsonObject.get("fltDate"), null));
        columnMap.put("flight_no", getStringValue(jsonObject.get("flightNo"), null));
        columnMap.put("ac_type", getStringValue(jsonObject.get("acType"), null));
        columnMap.put("old_ac_type", getStringValue(jsonObject.get("oldAcType"), null));
        columnMap.put("ac_no", getStringValue(jsonObject.get("acNo"), null));
        columnMap.put("old_ac_no", getStringValue(jsonObject.get("oldAcNo"), null));
        columnMap.put("std", getStringValue(jsonObject.get("std"), null));
        columnMap.put("dep_stn", getStringValue(jsonObject.get("depStn"), null));
        columnMap.put("arr_stn", getStringValue(jsonObject.get("arrStn"), null));
        columnMap.put("sta", getStringValue(jsonObject.get("sta"), null));
        columnMap.put("status", getStringValue(jsonObject.get("status"), ""));
        columnMap.put("etd", getStringValue(jsonObject.get("etd"), null));
        columnMap.put("eta", getStringValue(jsonObject.get("eta"), null));
        columnMap.put("f_type", getStringValue(jsonObject.get("fType"), null));
        columnMap.put("delay_code", getStringValue(jsonObject.get("delayCode"), null));
        columnMap.put("dur1", getStringValue(jsonObject.get("dur1"), null));
        columnMap.put("delay1", getStringValue(jsonObject.get("delay1"), null));
        columnMap.put("modified", modified);

        columnMapList.add(columnMap);

        /**
         * 单条插入，保证一条语句插入报错，还能往下执行
         */
        DBHandler dBHandler = new DBHandler();
        dBHandler.batchInsert(Constants.FOC_ODS_DETAIL, columnMapList);
    }

//    private void updateDetail(String odsDetailId, JSONObject jsonObject) {
//
//        DBHandler dBHandler = new DBHandler();
//        Map<String, Object> columnMap = new TreeMap<String, Object>();
//        Map<String, Object> whereMap = new TreeMap<String, Object>();
//
//        // 字段和值绑定
//        columnMap.put("flt_date", jsonObject.get("fltDate"));
//        columnMap.put("flight_no", jsonObject.get("flightNo"));
//        columnMap.put("ac_type", jsonObject.get("acType"));
//        columnMap.put("old_ac_type", jsonObject.get("oldAcType"));
//        columnMap.put("ac_no", jsonObject.get("acNo"));
//        columnMap.put("old_ac_no", jsonObject.get("oldAcNo"));
//        columnMap.put("std", jsonObject.get("std"));
//        columnMap.put("dep_stn", jsonObject.get("depStn"));
//        columnMap.put("arr_stn", jsonObject.get("arrStn"));
//        columnMap.put("sta", jsonObject.get("sta"));
//        columnMap.put("status", jsonObject.get("status"));
//        columnMap.put("etd", jsonObject.get("etd"));
//        columnMap.put("eta", jsonObject.get("eta"));
//        columnMap.put("f_type", jsonObject.get("fType"));
//        columnMap.put("delay_code", jsonObject.get("delayCode"));
//        columnMap.put("dur1", jsonObject.get("dur1"));
//        columnMap.put("delay1", jsonObject.get("delay1"));
//
//        whereMap.put("id", odsDetailId);
//
//        dBHandler.update(Constants.FOC_ODS_DETAIL, columnMap, whereMap);
//    }

    private boolean deleteOdsDetail(String changeId) {
        try {
            String query = "DELETE FROM " + Constants.FOC_ODS_DETAIL + " WHERE change_id = ?";
            run.update(query, changeId);
            return true;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return false;
    }
    
//    private boolean existOdsDetail(Map<MapType, OdsDetailEntity> odsDetailEntityMap, String changeSno, String flightNo,
//            String flightDate, String depStn, String arrStn) {
//        ResultSetHandler<List<OdsDetailEntity>> handler = new BeanListHandler<OdsDetailEntity>(OdsDetailEntity.class);
//        String query = "SELECT * FROM " + Constants.FOC_ODS_DETAIL + " WHERE flight_no ='" + flightNo
//                + "' and flt_date = '" + flightDate + "' and dep_stn = '" + depStn + "' and arr_stn = '" + arrStn
//                + "' and change_sno = '" + changeSno + "'";
//        List<OdsDetailEntity> list = null;
//        try {
//            list = run.query(query, handler);
//        } catch (SQLException e) {
//            // TODO Auto-generated catch block
//            e.printStackTrace();
//        }
//
//        if (list != null && list.size() > 1)
//            throw new IllegalRecordException("记录应该只有一条，但存在多条", OdsDetailEntity.class);
//
//        if (list != null && list.size() == 1) {
//            odsDetailEntityMap.put(MapType.odsDetailEntityType, list.get(0));
//            return true;
//        }
//
//        return false;
//    }

    private void updateOdsChanges(String odsChangesId, JSONObject jsonObject, final String modified) {

        DBHandler dBHandler = new DBHandler();
        Map<String, Object> columnMap = new TreeMap<String, Object>();
        Map<String, Object> whereMap = new TreeMap<String, Object>();

        // 字段和值绑定
        columnMap.put("subtime", getStringValue(jsonObject.get("subtime"), null));
        columnMap.put("sno", getStringValue(jsonObject.get("sno"), null));
        columnMap.put("reason", getStringValue(jsonObject.get("reason"), null));
        columnMap.put("phone", getStringValue(jsonObject.get("phone"), null));
        columnMap.put("remarks", getStringValue(jsonObject.get("remarks"), null));
        columnMap.put("modified", modified);

        whereMap.put("id", odsChangesId);

        dBHandler.update(Constants.FOC_ODS_CHANGES, columnMap, whereMap);
    }

    private void insertOdsChanges(JSONObject jsonObject, final String modified) {

        List<Map<String, Object>> columnMapList = new ArrayList<Map<String, Object>>();

        Map<String, Object> columnMap = new TreeMap<String, Object>();
        // 字段和值绑定
        columnMap.put("id", jsonObject.get("id"));
        // columnMap.put("id", IdWorker.getNextId());
        columnMap.put("subtime", getStringValue(jsonObject.get("subtime"), null));
        columnMap.put("sno", getStringValue(jsonObject.get("sno"), null));
        columnMap.put("reason", getStringValue(jsonObject.get("reason"), null));
        columnMap.put("phone", getStringValue(jsonObject.get("phone"), null));
        columnMap.put("remarks", getStringValue(jsonObject.get("remarks"), null));
        columnMap.put("modified", modified);
        // columnMap.put("aircraft_type", jsonObject.get("positionName"));

        columnMapList.add(columnMap);

        /**
         * 单条插入，保证一条语句插入报错，还能往下执行
         */
        DBHandler dBHandler = new DBHandler();
        dBHandler.batchInsert(Constants.FOC_ODS_CHANGES, columnMapList);
    }

    private OdsChangesEntity existOdsChanges(String odsChangesId) {
        ResultSetHandler<List<OdsChangesEntity>> handler = new BeanListHandler<OdsChangesEntity>(
                OdsChangesEntity.class);
        String query = "SELECT * FROM " + Constants.FOC_ODS_CHANGES + " WHERE id =?";
        List<OdsChangesEntity> list = null;
        try {
            list = run.query(query, handler, odsChangesId);
            if (list != null && list.size() > 0) {
//                odsChangesEntityMap.put(MapType.odsChangesEntityType, list.get(0));
                return list.get(0);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

//        if (list != null && list.size() > 1)
//            throw new IllegalRecordException("记录应该只有一条，但存在多条", OdsChangesEntity.class);
//
//        if (list != null && list.size() == 1) {
//            odsChangesEntityMap.put(MapType.odsChangesEntityType, list.get(0));
//            return true;
//        }

        return null;
    }

//    enum MapType {
//        odsEntityType, odsChangesEntityType, odsDetailEntityType;
//    }

}
