package com.dev.btse.data;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.battmonitor.base.Com;
import com.battmonitor.sql.MysqlConnPool;
import com.battmonitor.sql.Sql_Mysql;

public class TmpSensor_SQL {
	
	
	public static void createRealDataBase(MysqlConnPool conn_pool) {
		Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());		
		try {
			sql.sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS " + Sql_Mysql.DB_Sensor);					//´´½¨´«¸ÐÆ÷ÐÅÏ¢±í
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void createHisDataBase(MysqlConnPool conn_pool) {
		Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());		
		try {
			sql.sqlMysqlExecute("CREATE DATABASE IF NOT EXISTS " + Sql_Mysql.DB_Sensor_History);			//´´½¨´«¸ÐÆ÷ÀúÊ·Êý¾Ý±í
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * ´´½¨´«¸ÐÆ÷ÐÅÏ¢±í
	 * @param conn_pool
	 */
	public static void createSensor_infTable(MysqlConnPool conn_pool) {
		String sql_str = "CREATE TABLE IF NOT EXISTS " + Sql_Mysql.SensorInf_Table + " (" + 
				"  `num` bigint(20) NOT NULL AUTO_INCREMENT," + 
				"  `province` varchar(255) NOT NULL COMMENT 'Ê¡' DEFAULT ''," + 
				"  `city` varchar(255) NOT NULL COMMENT 'ÊÐ' DEFAULT ''," + 
				"  `county` varchar(255) NOT NULL COMMENT 'Çø/ÏØ' DEFAULT ''," + 
				"  `sensor_dev_id` int(11) NOT NULL DEFAULT '0' COMMENT 'É豸id'," + 
				"  `device_name` varchar(255) NOT NULL COMMENT 'É豸Ãû³Æ' DEFAULT ''," + 
				"  `note` varchar(255) NOT NULL DEFAULT ''," + 
				"  `airtmp_alarm` float NOT NULL DEFAULT '52.5'," + 
				"  `airhum_alarm` float NOT NULL DEFAULT '40.8'," + 
				"  `smoke_alarm` int(11) NOT NULL DEFAULT '8000'," + 
				"  PRIMARY KEY (`num`)," + 
				"  KEY `int_sensor_dev_id` (`sensor_dev_id`) USING BTREE" + 
				") ENGINE=InnoDB DEFAULT CHARSET=utf8;";
		
		Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
		try {
			sql.sqlMysqlExecute(sql_str);
			//Ìí¼Ó¿ÕÆøζȸ澯ÁÐ
			ResultSet res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
					+ " WHERE table_schema='db_sensor'"
					+ " AND table_name='tb_sensor_inf'"
					+ " AND column_name='airtmp_alarm'");
			if(false == res.next()) {
				sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.SensorInf_Table 
								+ " ADD COLUMN `airtmp_alarm` float NOT NULL DEFAULT '52.5';");
			}
			//Ìí¼Ó¿ÕÆøʪ¶È¸æ¾¯ÁÐ
			res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
					+ " WHERE table_schema='db_sensor'"
					+ " AND table_name='tb_sensor_inf'"
					+ " AND column_name='airhum_alarm'");
			if(false == res.next()) {
				sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.SensorInf_Table 
						+ " ADD COLUMN `airhum_alarm` float NOT NULL DEFAULT '40.8';");
			}
			//Ìí¼ÓÑ̸и澯ÁÐ
			res = sql.sqlMysqlQuery("SELECT * FROM information_schema.columns"
					+ " WHERE table_schema='db_sensor'"
					+ " AND table_name='tb_sensor_inf'"
					+ " AND column_name='smoke_alarm'");
			if(false == res.next()) {
				sql.sqlMysqlExecute("ALTER TABLE " + Sql_Mysql.SensorInf_Table 
						+ " ADD COLUMN `smoke_alarm` int(11) NOT NULL DEFAULT '8000';");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			sql.close_con();
		}
	}
	
	/**
	 * ´´½¨´«¸ÐÆ÷¶¨Î»ÐÅÏ¢±í
	 * @param conn_pool
	 */
	public static void createSensor_mapinfoTable(MysqlConnPool conn_pool) {
		String sql_str = "CREATE TABLE IF NOT EXISTS "+ Sql_Mysql.SensorMapinfo_Table +" (" + 
				"  `num` bigint(20) NOT NULL AUTO_INCREMENT," + 
				"  `sensor_dev_id` int(11) NOT NULL DEFAULT ''," + 
				"  `longitude` double NOT NULL DEFAULT '0'," + 
				"  `latitude` double NOT NULL DEFAULT '0'," + 
				"  `address` varchar(255) NOT NULL DEFAULT ''," + 
				"  `note` varchar(255) NOT NULL DEFAULT ''," + 
				"  PRIMARY KEY (`num`)," + 
				"  UNIQUE KEY `unique_sensor_dev_id` (`sensor_dev_id`) USING BTREE" + 
				") ENGINE=InnoDB DEFAULT CHARSET=utf8;";
		Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
		try {
			sql.sqlMysqlExecute(sql_str);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			sql.close_con();
		}
	}

	/**
	 * ´´½¨´«¸ÐÆ÷É豸ÐÅÏ¢±í
	 * @param conn_pool
	 */
	public static void createSensor_StateTable(MysqlConnPool conn_pool) {
		String sql_str = "CREATE TABLE IF NOT EXISTS "+Sql_Mysql.SensorState_Table+" (" + 
				"  `num` bigint(20) NOT NULL AUTO_INCREMENT," + 
				"  `sensor_dev_id` int(11) NOT NULL COMMENT 'É豸id'," + 
				"  `record_time` datetime NOT NULL COMMENT '¼Ç¼ʱ¼ä'," + 
				"  `airtmp` float NOT NULL COMMENT '¿ÕÆøζÈ'," + 
				"  `airhum` float NOT NULL COMMENT '¿ÕÆøʪ¶È'," + 
				"  `smoke` int(11) NOT NULL COMMENT 'Ñ̸Ð'," + 
				"  `water` int(11) NOT NULL," + 
				"  `lightintensity` int(11) NOT NULL," + 
				"  `CO2concentration` int(11) NOT NULL," + 
				"  `COconcentration` int(11) NOT NULL," + 
				"  `CH4concentration` int(11) NOT NULL," + 
				"  `O2concentration` int(11) NOT NULL," + 
				"  `dev_commcount` int(11) NOT NULL," + 
				"  `dev_errcommcount` int(11) NOT NULL," + 
				"  `note` varchar(255) NOT NULL DEFAULT ''," + 
				"  PRIMARY KEY (`num`)," + 
				"  UNIQUE KEY `index_sensor_dev_id` (`sensor_dev_id`) USING BTREE" + 
				") ENGINE=InnoDB DEFAULT CHARSET=utf8;";
		Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
		try {
			sql.sqlMysqlExecute(sql_str);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			sql.close_con();
		}
	}
	
	/**
	 * ¸ù¾ÝÉ豸id²éѯÊÇ·ñ´æÔÚµ±Ç°É豸
	 * @param conn_pool
	 * @param sensor_dev_id
	 * @return
	 */
	public static TmpSensor_inf queryTmpSensorById(MysqlConnPool conn_pool,int sensor_dev_id) {
		TmpSensor_inf sensor = null;
		String sql_str = "SELECT * FROM db_sensor.tb_sensor_inf WHERE sensor_dev_id = "+sensor_dev_id;
		Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
		try {
			ResultSet res = sql.sqlMysqlQuery(sql_str);
			if(res.next()) {
				sensor = new TmpSensor_inf();
				sensor.setCity(res.getString("city"));
				sensor.setProvince(res.getString("province"));
				sensor.setCounty(res.getString("county"));
				sensor.setDevice_name(res.getString("device_name"));
				sensor.setSensor_dev_id(res.getInt("sensor_dev_id"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			sql.close_con();
		}
		return sensor;
	}
	
	/**
	 * 	²åÈë»ò¸üÐÂÉ豸״̬
	 * @param conn_pool
	 * @param state
	 */
	public static void insertOrUpdateTmpSensorState(MysqlConnPool conn_pool,TmpSensorState state) {
		String sql_str = Sql_Mysql.SensorState_Table +
				" SET sensor_dev_id = " + state.sensor_dev_id + "," + 
				" record_time = '" + Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms) + "'," + 
				" airtmp = " + state.getAirtmp()+"," + 
				" airhum = " + state.getAirhum()+"," + 
				" smoke = " + state.getSmoke()+"," + 
				" water = " + state.getWater()+"," + 
				" lightintensity = " + state.getLightintensity()+"," + 
				" CO2concentration = " + state.getCO2concentration()+"," + 
				" COconcentration = " + state.getCOconcentration()+"," + 
				" CH4concentration = " + state.getCH4concentration()+"," + 
				" O2concentration = " + state.getO2concentration()+"," + 
				" dev_commcount = " + state.getDev_commcount()+"," + 
				" dev_errcommcount = " + state.getDev_errcommcount();
		String sql_str_replace = "REPLACE INTO " + sql_str;
		Sql_Mysql sql = new Sql_Mysql(conn_pool.getConn());
		try {
			sql.sqlMysqlExecute(sql_str_replace);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			sql.close_con();
		}
	}
	
	/**
	 *  ²éѯËùÓеĴ«¸ÐÆ÷É豸
	 * @return
	 */
	public static List<TmpSensor_inf> queryAllTmpSensor(MysqlConnPool pool){
		String sql_str = 
						" SELECT * " + 
						" FROM db_sensor.tb_sensor_inf " ;
		List<TmpSensor_inf> tmpinf = new ArrayList<>();
		Sql_Mysql sql = new Sql_Mysql(pool.getConn());
		ResultSet res = sql.sqlMysqlQuery(sql_str);
		try {
			while(res.next()) {
				TmpSensor_inf tmp = new TmpSensor_inf();
				tmp.setCity(res.getString("city"));
				tmp.setCounty(res.getString("county"));
				tmp.setDevice_name(res.getString("device_name"));
				tmp.setProvince(res.getString("province"));
				tmp.setSensor_dev_id(res.getInt("sensor_dev_id"));
				
				tmpinf.add(tmp);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			sql.close_con();
		}
		return tmpinf;
	}
	
	//²éѯָ¶¨µÄ´«¸ÐÆ÷µÄ״̬
	public static TmpSensorState queryTmpSensorState(MysqlConnPool pool,int sensor_dev_id) {
		TmpSensorState state = null ;
		String sql_str = "SELECT * " + 
				" FROM " + Sql_Mysql.SensorState_Table + 
				" WHERE sensor_dev_id = " + sensor_dev_id;
		Sql_Mysql sql = new Sql_Mysql(pool.getConn());
		ResultSet res = sql.sqlMysqlQuery(sql_str);
		try {
			if(res.next()) {
				state = new TmpSensorState();
				state.setSensor_dev_id(res.getInt("sensor_dev_id"));
				state.setAirhum(res.getFloat("airhum"));
				state.setAirtmp(res.getFloat("airtmp"));
				state.setSmoke(res.getInt("smoke"));
				state.setWater(res.getInt("water"));
				state.setLightintensity(res.getInt("lightintensity"));
				state.setCO2concentration(res.getInt("cO2concentration"));
				state.setCH4concentration(res.getInt("cH4concentration"));
				state.setCOconcentration(res.getInt("cOconcentration"));
				state.setO2concentration(res.getFloat("o2concentration"));
				state.setRecord_time(res.getTimestamp("record_time"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			sql.close_con();
		}
		return state;
	}

	/**
	 * ´´½¨Ö¸¶¨µÄ´«¸ÐÆ÷ÀúÊ·Êý¾Ý±í
	 * @param pool
	 * @param sensor_dev_id
	 */
	public static void createTmpSensorHistoryDataTable(MysqlConnPool pool, int sensor_dev_id) {
		String sql_str = "CREATE TABLE IF NOT EXISTS  "+Sql_Mysql.DB_Sensor_History+".`tb_sensor_hisdata_" + sensor_dev_id + "` (" + 
				"  `num` bigint(20) NOT NULL AUTO_INCREMENT," + 
				"  `sensor_dev_id` int(11) NOT NULL DEFAULT '1' COMMENT 'É豸id'," + 
				"  `record_time` datetime NOT NULL DEFAULT '2000-01-01 00:00:00'," + 
				"  `airtmp` float NOT NULL DEFAULT '0' COMMENT '¿ÕÆøζÈ'," + 
				"  `airhum` float NOT NULL DEFAULT '0' COMMENT '¿ÕÆøʪ¶È'," + 
				"  `smoke` int(11) NOT NULL DEFAULT '100' COMMENT 'Ñ̸Ð'," + 
				"  `water` int(11) NOT NULL DEFAULT '0' COMMENT 'Ë®½þ'," + 
				"  `lightintensity` int(11) NOT NULL DEFAULT '0' COMMENT '¹âÕÕÇ¿¶È'," + 
				"  `CO2concentration` int(11) NOT NULL DEFAULT '0' COMMENT 'CO2Ũ¶È'," + 
				"  `COconcentration` int(11) NOT NULL DEFAULT '0'," + 
				"  `CH4concentration` int(11) NOT NULL DEFAULT '0'," + 
				"  `O2concentration` int(11) NOT NULL DEFAULT '0'," + 
				"  `note` varchar(255) DEFAULT NULL DEFAULT ''," + 
				"  PRIMARY KEY (`num`)," + 
				"  KEY `index_sensor_dev_id` (`sensor_dev_id`) USING BTREE" + 
				") ENGINE=InnoDB DEFAULT CHARSET=utf8;";
		Sql_Mysql sql = new Sql_Mysql(pool.getConn());
		try {
			sql.sqlMysqlExecute(sql_str);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			sql.close_con();
		}
		
	}
	
	/**
	 * ±£´æÀúÊ·µÄ´«¸ÐÆ÷Êý¾Ý
	 * @param pool
	 * @param state
	 */
	public static void saveSensorHistoryData(MysqlConnPool pool,TmpSensorState state) {
		String sql_str = "INSERT INTO "+Sql_Mysql.DB_Sensor_History +".`tb_sensor_hisdata_"+state.sensor_dev_id+"`"
				+ "(sensor_dev_id,record_time,airtmp,airhum,smoke,water,lightintensity,CO2concentration,COconcentration,CH4concentration,O2concentration)"
				+ " VALUES ("
					+state.getSensor_dev_id()+", '"
					+Com.getDateTimeFormat(new Date(), Com.DTF_YMDhms)+"', "
					+state.getAirtmp()+","
					+state.getAirhum()+", "
					+state.getSmoke()+","
					+state.getWater()+", "
					+state.getLightintensity()+", "
					+state.getCO2concentration()+", "
					+state.getCOconcentration()+","
					+state.getCH4concentration()+", "
					+state.getO2concentration()+
				");";
		Sql_Mysql sql = new Sql_Mysql(pool.getConn());
		try {
			sql.sqlMysqlExecute(sql_str);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			sql.close_con();
		}
	}
}