const db = require(`../config/dbMysqlConn`)
const MysqlHelpers = require(`../library/LibMysqlHelper`)
const moment = require(`moment`)

class GpsTracksModels {
	static DEFAULT_COUNTRY_ID = 1

	static TYPE_ADDR_OSM = 1

	static STTS_REVERSE_GEO_SC = 1
	static STTS_REVERSE_GEO_NOT = 2
	static STTS_REVERSE_GEO_ER = 3
	static STTS_REVERSE_GEO_LOST = 4

	static STTS_EN_DFT = 0
	static STTS_EN_IDLING = 1
	static STTS_EN_MOVING = 2
	static STTS_EN_STOPING = 3

	static STTS_IGNITION_DFT = 0
	static STTS_IGNITION_ON = 1
	static STTS_IGNITION_OFF = 2
	static STTS_IGNITION_LOW = 3
	static STTS_IGNITION_HIGH = 4

	static STTS_GPS_DFT = 0 // default
	static STTS_GPS_ON = 1
	static STTS_GPS_OFF = 2

	// realtime GPS or differential positioning
	static STTS_POS_TYPE_GPS_DFT = 0 // default
	static STTS_POS_TYPE_GPS_DIFF = 1
	static STTS_POS_TYPE_GPS_RLTM = 2

	// GPS has been positioned or not
	static STTS_IS_POS_GPS_DFT = 0 // default
	static STTS_IS_POS_GPS_NOT = 1 // not positioning
	static STTS_IS_POS_GPS_HAS = 2 // positioning

	// 1=>no signal, 2=>extremely weak signal 3=>very weak signal, 4=>good signal, 5=>strong signal
	static STTS_GSM_DFT = 0 // default
	static STTS_GSM_NO_SIGNAL = 1
	static STTS_GSM_BAD_SIGNAL = 2
	static STTS_GSM_WEAK_SIGNAL = 3
	static STTS_GSM_GOOD_SIGNAL = 4
	static STTS_GSM_STRONG_SIGNAL = 5

	static STTS_OIL_ELECTRIC_DFT = 0
	static STTS_OIL_ELECTRIC_ON = 1
	static STTS_OIL_ELECTRIC_OFF = 2

	static STTS_ALARM_DFT = 0
	static STTS_ALARM_NORMAL = 1
	static STTS_ALARM_SHOCK = 2
	static STTS_ALARM_POWER_CUT = 3
	static STTS_ALARM_LOW_BATTERY = 4
	static STTS_ALARM_SOS = 5

	static STTS_CHARGE_DFT = 0
	static STTS_CHARGE_OFF = 1
	static STTS_CHARGE_ON = 2

	static STTS_ACC_DFT = 0
	static STTS_ACC_LOW = 1
	static STTS_ACC_HIGH = 2

	static STTS_VOLT_DFT = 0
	static STTS_VOLT_SHUTDOWN = 1
	static STTS_VOLT_EXTREME_LOW_BATTERY = 2
	static STTS_VOLT_VERY_LOW_BATTERY = 3
	static STTS_VOLT_LOW_BATTERY = 4
	static STTS_VOLT_MEDIUM = 5
	static STTS_VOLT_HIGH = 6
	static STTS_VOLT_VERY_HIGH = 7

	static STTS_SWITCH_DFT = 0
	static STTS_SWITCH_OFF = 1
	static STTS_SWITCH_ON = 2

	static STTS_ENTER_ZONE = 1
	static STTS_LEAVE_ZONE = 2

	// type source
	static SOURCE_GPS_TRACKER = 1
	static SOURCE_SMARTPHONE = 2

	static PROTOCOL_SMARTPHONE = "smartphone"

	static ACT_LOGIN = "login"
	static ACT_HEARTBEAT = "heartbeat"
	static ACT_LOCATION = "location"
	static ACT_OTHER = "other"

	static async create2(data) {
		return new Promise((resolve, reject) => {
			const query = `INSERT INTO t_gps_tracks SET ?;`
			db.getConnection(function (err, conn) {
				if (err) {
					conn.release()
					reject(err)
					return false
				}

				conn.beginTransaction(async function (err) {
					if (err) {
						conn.release()
						reject(err)
						return false
					}
					conn.query(query, data, function (err, resp) {
						if (err) {
							conn.rollback(async function () {
								conn.release()
								reject(err.message)
							})
							return false
						}
						// Number(resp.insertId);
						conn.commit(async function (err) {
							if (err) {
								conn.release()
								reject(err)
								return false
							}
							conn.release()
							resolve(resp)
							return true
						})
					})
				})
			})
		})
	}

	static bundleCreate2(logs = {}, rltm = {}) {
		return new Promise(async (resolve, reject) => {
			try {
				const conn = await MysqlHelpers.createConnection()
				await MysqlHelpers.createTrx(conn)

				let rltmLength = Object.keys(rltm).length
				let resLogs = undefined

				if (Object.keys(logs).length > 0) {
					resLogs = await MysqlHelpers.queryTrx(conn, `INSERT INTO t_gps_tracks SET ?;`, [logs])

					if (logs.action == "location") {
						const date = logs.crt_d
						const mm = moment.unix(date).format("MM")
						const yy = moment.unix(date).format("YY")
						logs.id = resLogs.insertId
						await MysqlHelpers.queryTrx(
							conn,
							`
							INSERT INTO tracks_${yy}${mm}
							SET ?	
						`,
							[logs]
						)
					}
				}

				if (rltmLength > 0 && typeof resLogs !== "undefined") rltm.master_id = resLogs.insertId
				if (rltmLength > 0 && rltm.latitude !== null && rltm.longitude !== null) {
					let devices = await MysqlHelpers.queryTrx(
						conn,
						`SELECT id FROM t_gps_tracks_rltm as rltm WHERE rltm.device_id = ?`,
						[rltm.device_id]
					)
					if (devices.length > 1)
						await MysqlHelpers.queryTrx(conn, `DELETE from t_gps_tracks_rltm WHERE device_id = ?;`, [
							rltm.device_id,
						])

					if (rltm.vhc_id != 0) {
						let vhcs = await MysqlHelpers.queryTrx(
							conn,
							`SELECT id FROM t_gps_tracks_rltm as rltm WHERE rltm.vhc_id = ?`,
							[rltm.vhc_id]
						)
						if (vhcs.length > 1)
							await MysqlHelpers.queryTrx(conn, `DELETE from t_gps_tracks_rltm WHERE vhc_id = ?;`, [
								rltm.vhc_id,
							])
					}

					if (rltm.drv_id != 0) {
						let drvs = await MysqlHelpers.queryTrx(
							conn,
							`SELECT id FROM t_gps_tracks_rltm as rltm WHERE rltm.drv_id = ?`,
							[rltm.drv_id]
						)
						if (drvs.length > 1)
							await MysqlHelpers.queryTrx(conn, `DELETE from t_gps_tracks_rltm WHERE drv_id = ?;`, [
								rltm.drv_id,
							])
					}

					await MysqlHelpers.queryTrx(
						conn,
						`INSERT INTO t_gps_tracks_rltm SET ? ON DUPLICATE KEY UPDATE ?;`,
						[rltm, rltm]
					)
				}

				await MysqlHelpers.commit(conn)
				resolve({
					type: "success",
					result: resLogs,
				})
			} catch (err) {
				console.log("err")
				reject(err)
			}
		})
	}

	static async get2() {
		return new Promise((resolve, reject) => {
			let params = []
			let query = "SELECT * FROM t_gps_tracks ORDER BY id DESC LIMIT 1000"
			db.query(query, params, function (err, resp) {
				if (err) {
					reject(err)
					return false
				}
				resolve(resp)
				return true
			})
		})
	}

	static async get2ForReverseGeo(limit) {
		return new Promise((resolve, reject) => {
			let params = []
			let query = `
				SELECT *
				FROM t_gps_tracks 
				WHERE 
					latitude is not null 
					AND longitude is not null
					AND stts_reverse_geo = ? 
					and action = 'location'
				ORDER BY id ASC 
				LIMIT ?
			`
			params.push(GpsTracksModels.STTS_REVERSE_GEO_NOT, limit)
			db.query(query, params, function (err, resp) {
				if (err) {
					reject(err)
					return false
				}
				resolve(resp)
				return true
			})
		})
	}

	static async get2LastDevice(device_id) {
		return new Promise((resolve, reject) => {
			let params = []
			let query = "SELECT * FROM t_gps_tracks WHERE device_id = ? ORDER BY id DESC LIMIT 1"
			params.push(device_id)
			db.query(query, params, function (err, resp) {
				if (err) {
					reject(err)
					return false
				}
				resolve(resp)
				return true
			})
		})
	}

	static async get2LastLocByDeviceId(device_id) {
		return new Promise((resolve, reject) => {
			let params = []
			// let query = 'SELECT * FROM t_gps_tracks WHERE device_id = ? AND latitude is not null AND longitude != 0 ORDER BY id DESC LIMIT 1';
			let query =
				'SELECT * FROM t_gps_tracks WHERE action IN ("location","alarm") AND device_id = ? AND latitude is not null AND longitude != 0 ORDER BY id DESC LIMIT 1' // kalo gamau ngambil data heartbeat(idling)
			params.push(device_id)
			db.query(query, params, function (err, resp) {
				if (err) {
					reject(err)
					return false
				}
				resolve(resp)
				return true
			})
		})
	}

	static async getLastSumMileage(device_id) {
		return new Promise((resolve, reject) => {
			let params = []
			let query = `
        select
          *
        from
          t_vehicles tv
        where
          tv.device_id = "0866833070208837"
          and tv.dlt is null
        limit 1
      `
			params.push(device_id)
			db.query(query, params, function (err, resp) {
				if (err) {
					reject(err)
					return false
				}
				resolve(resp)
				return true
			})
		})
	}

	static async get2LastLoc(filter = {}) {
		return new Promise((resolve, reject) => {
			let params = [],
				where = ""
			if (filter["device_id"]) {
				where += " AND device_id = ?"
				params.push(filter["device_id"])
			} else if (filter["vhc_id"]) {
				where += " AND vhc_id = ?"
				params.push(filter["vhc_id"])
			} else if (filter["drv_id"]) {
				where += " AND drv_id = ?"
				params.push(filter["drv_id"])
			} else {
				reject(false)
				return false
			}
			let query = `SELECT * FROM t_gps_tracks WHERE latitude is not null AND longitude != 0 ${where} ORDER BY id DESC LIMIT 1`
			db.query(query, params, function (err, resp) {
				if (err) {
					reject(err)
					return false
				}
				resolve(resp)
				return true
			})
		})
	}

	static async getLastLocAndAddrByDid(filter = {}) {
		return new Promise((resolve, reject) => {
			let params = [],
				where = "",
				limit = ""

			if (typeof filter["did"] !== "undefined") {
				where += " AND tr.drv_id = ?"
				params.push(filter["did"])
			}

			if (typeof filter["limit"] !== "undefined") {
				limit += " LIMIT ?"
				params.push(filter["limit"])
			}

			let query = `SELECT tr.*,tra.state_text,tra.city_text,tra.district_text,tra.village_text,tra.postcode,tra.streets,tra.fulladdress FROM t_gps_tracks as tr LEFT JOIN t_gps_tracks_address as tra ON tr.id = tra.master_id WHERE latitude is not null AND longitude != 0 ${where} ORDER BY id DESC ${limit}`
			db.query(query, params, function (err, resp) {
				if (err) {
					reject(err)
					return false
				}
				resolve(resp)
				return true
			})
		})
	}

	static async checkSttsEngineBetweenDate(device_id, stts_engine, start, end) {
		return new Promise((resolve, reject) => {
			let params = []
			let query =
				"SELECT * FROM t_gps_tracks WHERE device_id = ? AND stts_engine = ? AND crt BETWEEN ? AND ? ORDER BY id ASC LIMIT 1"
			params.push(device_id, stts_engine, start, end)
			db.query(query, params, function (err, resp) {
				if (err) {
					reject(err)
					return false
				}
				resolve(resp)
				return true
			})
		})
	}

	static async get2LastHeartbeatOrAlarm(device_id) {
		return new Promise((resolve, reject) => {
			let params = []
			let query =
				'SELECT * FROM t_gps_tracks WHERE device_id = ? AND action IN ("heartbeat","alarm") AND stts_gps != 0 AND stts_gsm != 0 ORDER BY id DESC LIMIT 1'
			params.push(device_id)
			db.query(query, params, function (err, resp) {
				if (err) {
					reject(err)
					return false
				}
				resolve(resp)
				return true
			})
		})
	}

	static async getLastHeartbeatToDeterminIdling(device_id, lst_pos_crt, now) {
		return new Promise((resolve, reject) => {
			let params = []
			let query = `
				SELECT * 
				FROM t_gps_tracks 
				WHERE 
					device_id = ? 
					AND action IN ("heartbeat") 
					AND stts_gps != 0 
					AND crt BETWEEN ${lst_pos_crt} AND ${now}
				ORDER BY id DESC;
			`
			params.push(device_id)
			db.query(query, params, function (err, resp) {
				if (err) {
					reject(err)
					return false
				}
				resolve(resp)
				return true
			})
		})
	}

	static async update2(id, data) {
		return new Promise((resolve, reject) => {
			const query = `UPDATE t_gps_tracks SET ? WHERE id = ?;`
			db.getConnection(function (err, conn) {
				if (err) {
					conn.release()
					reject(err)
					return false
				}

				conn.beginTransaction(async function (err) {
					if (err) {
						conn.release()
						reject(err)
						return false
					}
					conn.query(query, [data, id], function (err, resp) {
						if (err) {
							conn.rollback(async function () {
								conn.release()
								reject(err.message)
							})
							return false
						}
						conn.commit(async function (err) {
							if (err) {
								conn.release()
								reject(err)
								return false
							}
							conn.release()
							resolve(resp)
							return true
						})
					})
				})
			})
		})
	}

	static async select2Address(lat = "", lng = "") {
		return new Promise((resolve, reject) => {
			let params = []
			let query =
				"SELECT * FROM t_gps_tracks_address WHERE stts_reverse_geo = " +
				GpsTracksModels.STTS_REVERSE_GEO_SC +
				" AND lat = ? AND lng = ? ORDER BY id DESC LIMIT 1;"
			params.push(lat, lng)
			db.query(query, params, function (err, resp) {
				if (err) {
					reject(err)
					return false
				}
				resolve(resp)
				return true
			})
		})
	}

	static async create2Address(data) {
		return new Promise((resolve, reject) => {
			const query = `INSERT INTO t_gps_tracks_address SET ?;`
			db.getConnection(function (err, conn) {
				if (err) {
					conn.release()
					reject(err)
					return false
				}

				conn.beginTransaction(async function (err) {
					if (err) {
						conn.release()
						reject(err)
						return false
					}
					conn.query(query, data, function (err, resp) {
						if (err) {
							conn.rollback(async function () {
								conn.release()
								reject(err.message)
							})
							return false
						}
						// Number(resp.insertId);
						conn.commit(async function (err) {
							if (err) {
								conn.release()
								reject(err)
								return false
							}
							conn.release()
							resolve(resp)
							return true
						})
					})
				})
			})
		})
	}

	static async create2SpawnZone(data) {
		return new Promise((resolve, reject) => {
			const query = `INSERT INTO t_gps_tracks_spawn SET ?;`
			db.getConnection(function (err, conn) {
				if (err) {
					conn.release()
					reject(err)
					return false
				}

				conn.beginTransaction(async function (err) {
					if (err) {
						conn.release()
						reject(err)
						return false
					}
					conn.query(query, data, function (err, resp) {
						if (err) {
							conn.rollback(async function () {
								conn.release()
								reject(err.message)
							})
							return false
						}
						// Number(resp.insertId);
						conn.commit(async function (err) {
							if (err) {
								conn.release()
								reject(err)
								return false
							}
							conn.release()
							resolve(resp)
							return true
						})
					})
				})
			})
		})
	}

	static async updt2SpawnZone(data, id) {
		return new Promise((resolve, reject) => {
			const query = `UPDATE t_gps_tracks_spawn SET ? WHERE id = ?;`
			db.getConnection(function (err, conn) {
				if (err) {
					conn.release()
					reject(err)
					return false
				}

				conn.beginTransaction(async function (err) {
					if (err) {
						conn.release()
						reject(err)
						return false
					}
					conn.query(query, [data, id], function (err, resp) {
						if (err) {
							conn.rollback(async function () {
								conn.release()
								reject(err.message)
							})
							return false
						}
						// Number(resp.insertId);
						conn.commit(async function (err) {
							if (err) {
								conn.release()
								reject(err)
								return false
							}
							conn.release()
							resolve(resp)
							return true
						})
					})
				})
			})
		})
	}

	static listSpawnZone(filter = {}) {
		return new Promise(async (resolve, reject) => {
			let params = [],
				select = "",
				where = "",
				join = "",
				order_by = "",
				group_by = "",
				limit = ""

			if (filter["zone_id"]) {
				where += " AND spawn.zone_id = ?"
				params.push(filter["zone_id"])
			}
			if (filter["ord_id"]) {
				where += " AND spawn.ord_id = ?"
				params.push(filter["ord_id"])
			}
			if (filter["ord_pck_drop_id"]) {
				where += " AND spawn.ord_pck_drop_id = ?"
				params.push(filter["ord_pck_drop_id"])
			}
			if (filter["drv_id"]) {
				where += " AND spawn.drv_id = ?"
				params.push(filter["drv_id"])
			}
			if (filter["vhc_id"]) {
				where += " AND spawn.vhc_id = ?"
				params.push(filter["vhc_id"])
			}
			if (filter["source"]) {
				where += " AND spawn.source = ?"
				params.push(filter["source"])
			}
			if (typeof filter["leave_at_d"] != undefined) {
				where += " AND spawn.leave_at_d = ?"
				params.push(filter["leave_at_d"])
			}

			if (filter["group_by"]) {
				group_by = filter["group_by"]
			}
			if (filter["order_by"]) {
				order_by = filter["order_by"]
			}
			if (filter["limit"]) {
				limit = " LIMIT " + filter["limit"]
			}

			let query = `SELECT
      spawn.*
      ${select}
      FROM t_gps_tracks_spawn as spawn
      ${join}
      WHERE spawn.master_id != 0
      ${where}
      ${group_by}
      ${order_by}
      ${limit}
      ;`
			db.query(query, params, function (err, resp) {
				if (err) {
					reject(err)
					return false
				}
				resolve(resp)
				return true
			})
		})
	}
}

module.exports = GpsTracksModels