<?php

class Teryt {

	const SIMC_TABLE = "TERYT_SIMC";
	const TERC_TABLE = "TERYT_TERC";
	const ULIC_TABLE = "TERYT_ULIC";
	const PNA_TABLE = "PNA";

	private static function checkArgs($args, $argsAvailable) {
		if ($args == null) throw new Exception("Missing argument");
		if (!is_array($args)) throw new Exception("Armument must be an array");
		if (!count($args)) throw new Exception("Argument list cannot be empty");
		foreach ($args as $arg => $value) {
			if (!in_array($arg, $argsAvailable)) throw new Exception("Unknown argument {$arg}");
		}
	}

	private static function throwException($msg, $sql) {
		throw new Exception(json_encode(['msg' => $msg, 'sql' => $sql]));
	}

	public static function search($args = null) {
		$argsAvailable = ["kodPocztowy", "wojewodztwo", "powiat", "gmina", "miejscowosc", "ulica"];
		self::checkArgs($args, $argsAvailable);
		$args = array_map('trim', $args);

		$where = "";
		$return = null;

		if (isset($args['kodPocztowy'])) {
			$args['kodPocztowy'] = str_replace(' ', '', $args['kodPocztowy']);
			if (isset($args['miejscowosc'])) $whereKod = "and miejscowosc = " . DB::getPDO()->quote($args['miejscowosc']);
			else $whereKod = "";
			$query = "select wojewodztwo, powiat, gmina from `" . self::PNA_TABLE . "` where kodPocztowy = " . DB::getPDO()->quote($args['kodPocztowy']) . " $whereKod and nazwa = '' group by wojewodztwo, powiat, gmina";
			$result = DB::getPDO()->fetchall($query);
			if (count($result) == 1) {
				$kodPocztowy['wojewodztwo'] = $result[0]['wojewodztwo'];
				$kodPocztowy['powiat'] = $result[0]['powiat'];
				$kodPocztowy['gmina'] = $result[0]['gmina'];
			}
		}

		try {
			if (isset($args['wojewodztwo'])) {
				$query = "select WOJ from `" . self::TERC_TABLE . "` where NAZWA = " . DB::getPDO()->quote($args['wojewodztwo']) . " and POW is null";
				if (!($return['WOJ'] = DB::getPDO()->fetchValue($query))) throw new Exception("wojewodztwo not found");
				$where = "and WOJ = '{$return['WOJ']}'";
			}

			if (isset($args['powiat'])) {
				$args['powiat'] = preg_replace('/^M. /i', '', $args['powiat']);
				$query = "select WOJ, POW from `" . self::TERC_TABLE . "` where NAZWA = " . DB::getPDO()->quote($args['powiat']) . " and POW is not null and GMI is null {$where}";
				if (!($result = DB::getPDO()->fetchall($query))) throw new Exception("powiat not found");
				if (count($result) > 1) throw new Exception("Found too many powiats");
				$return['WOJ'] = $result[0]['WOJ'];
				$return['POW'] = $result[0]['POW'];
				$where = "and WOJ = '{$return['WOJ']}' and POW = '{$return['POW']}'";
			}

			if (isset($args['gmina'])) {
				$args['gmina'] = preg_replace('/^M. /i', '', $args['gmina']);
				$query = "select WOJ, POW, GMI from `" . self::TERC_TABLE. "` where NAZWA = " . DB::getPDO()->quote($args['gmina']) . " and GMI is not null {$where} group by WOJ, POW, GMI";
				if (!($result = DB::getPDO()->fetchall($query))) throw new Exception("gmina not found");
				if (count($result) == 1) {// throw new Exception("Found too many gminas ({$query})");
					$return['WOJ'] = $result[0]['WOJ'];
					$return['POW'] = $result[0]['POW'];
					$return['GMI'] = $result[0]['GMI'];
					$where = "and WOJ = '{$return['WOJ']}' and POW = '{$return['POW']}' and GMI = '{$return['GMI']}'";
				}
			}

			if (!isset($return['WOJ'])) throw new Exception("Try with data from kodPocztowy");

			if (isset($args['miejscowosc'])) {
				$args['miejscowosc'] = preg_replace('/^M. /i', '', $args['miejscowosc']);
				$args['miejscowosc'] = str_replace(' - ', '-', $args['miejscowosc']);
				$args['miejscowosc'] = str_replace(' ', '%', $args['miejscowosc']);
				$query = "select WOJ, POW, GMI, SYM from `" . self::SIMC_TABLE . "` where NAZWA like " . DB::getPDO()->quote($args['miejscowosc']) . " {$where}";
				if (!($result = DB::getPDO()->fetchall($query))) self::throwException("miejscowosc not found", $query);
				$return['WOJ'] = $result[0]['WOJ'];
				$return['POW'] = $result[0]['POW'];
				$return['GMI'] = $result[0]['GMI'];
				if (count($result) > 1) {//self::throwException("Found too many miejscowoscs", $query);
					$SYMarr = array_map(function ($a) {return $a['SYM'];}, $result);
					$where = "and WOJ = '{$return['WOJ']}' and POW = '{$return['POW']}' and ((RODZ_GMI<8 and GMI = '{$return['GMI']}' and SYM in (" . implode(",", $SYMarr) . ")) or RODZ_GMI>=8)";
				} else {
					$return['SYM'] = $result[0]['SYM'];
					$where = "and WOJ = '{$return['WOJ']}' and POW = '{$return['POW']}' and ((RODZ_GMI<8 and GMI = '{$return['GMI']}' and SYM = '{$return['SYM']}') or RODZ_GMI>=8)";
				}
			}
		} catch (Exception $e) {
			if (!isset($kodPocztowy)) throw new Exception($e->getMessage());

			$query = "select WOJ from `" . self::TERC_TABLE . "` where NAZWA = " . DB::getPDO()->quote($kodPocztowy['wojewodztwo']) . " and POW is null";
			if (!($return['WOJ'] = DB::getPDO()->fetchValue($query))) throw new Exception("wojewodztwo not found");
			$where = "and WOJ = '{$return['WOJ']}'";

			$query = "select WOJ, POW from `" . self::TERC_TABLE . "` where NAZWA = " . DB::getPDO()->quote($kodPocztowy['powiat']) . " and POW is not null and GMI is null {$where}";
			if (!($result = DB::getPDO()->fetchall($query))) throw new Exception("powiat not found");
			if (count($result) > 1) throw new Exception("Found too many powiats");
			$return['WOJ'] = $result[0]['WOJ'];
			$return['POW'] = $result[0]['POW'];
			$where = "and WOJ = '{$return['WOJ']}' and POW = '{$return['POW']}'";

			$query = "select WOJ, POW, GMI from `" . self::TERC_TABLE. "` where NAZWA = " . DB::getPDO()->quote($kodPocztowy['gmina']) . " and GMI is not null {$where} group by WOJ, POW, GMI";
			if (!($result = DB::getPDO()->fetchall($query))) throw new Exception("gmina not found");
			if (count($result) == 1) {
				$return['WOJ'] = $result[0]['WOJ'];
				$return['POW'] = $result[0]['POW'];
				$return['GMI'] = $result[0]['GMI'];
				$where = "and WOJ = '{$return['WOJ']}' and POW = '{$return['POW']}' and GMI = '{$return['GMI']}'";
			}

			if (isset($args['miejscowosc'])) {
				$args['miejscowosc'] = preg_replace('/^M. /i', '', $args['miejscowosc']);
				$args['miejscowosc'] = str_replace(' - ', '-', $args['miejscowosc']);
				$args['miejscowosc'] = str_replace(' ', '%', $args['miejscowosc']);
				$query = "select WOJ, POW, GMI, SYM from `" . self::SIMC_TABLE . "` where NAZWA like " . DB::getPDO()->quote($args['miejscowosc']) . " {$where}";
				if (!($result = DB::getPDO()->fetchall($query))) self::throwException("miejscowosc not found", $query);
				$return['WOJ'] = $result[0]['WOJ'];
				$return['POW'] = $result[0]['POW'];
				$return['GMI'] = $result[0]['GMI'];
				if (count($result) > 1) {//self::throwException("Found too many miejscowoscs", $query);
					$SYMarr = array_map(function ($a) {return $a['SYM'];}, $result);
					$where = "and WOJ = '{$return['WOJ']}' and POW = '{$return['POW']}' and ((RODZ_GMI<8 and GMI = '{$return['GMI']}' and SYM in (" . implode(",", $SYMarr) . ")) or RODZ_GMI>=8)";
				} else {
					$return['SYM'] = $result[0]['SYM'];
					$where = "and WOJ = '{$return['WOJ']}' and POW = '{$return['POW']}' and ((RODZ_GMI<8 and GMI = '{$return['GMI']}' and SYM = '{$return['SYM']}') or RODZ_GMI>=8)";
				}
        	        }

		}

		$ulicaNeeded = DB::getPDO()->fetchValue("select count(*) from `" . self::ULIC_TABLE . "` where 1 = 1 {$where}") > 0 ? true : false;

		if (isset($args['ulica'])) {
			$args['ulica'] = trim(preg_replace('/osiedle/i', 'os.', $args['ulica']));
			$args['ulica'] = trim(preg_replace('/aleja/i', 'al.', $args['ulica']));
			$args['ulica'] = trim(preg_replace('/plac/i', 'pl.', $args['ulica']));
			$args['ulica'] = trim(preg_replace('/kardynała/i', 'kard', $args['ulica']));
			$args['ulica'] = trim(preg_replace('/\(.*\)/', '', $args['ulica']));
			$args['ulica'] = trim(preg_replace('/-go/i', '', $args['ulica']));
			$args['ulica'] = trim(preg_replace('/\.$/', '%', $args['ulica']));
			$search = [' ', '..'];
			$replace = ['%', '.'];
			$args['ulica'] = str_replace($search, $replace, $args['ulica']);

			$ulica = DB::getPDO()->quote("{$args['ulica']}");
			$query = "select WOJ, POW, GMI, SYM, SYM_UL from `" . self::ULIC_TABLE . "` where (concat(CECHA, ' ', NAZWA_1) like {$ulica} or concat(CECHA, ' ', NAZWA_2, ' ', NAZWA_1) like {$ulica} " .
				"or concat(CECHA, NAZWA_1) like {$ulica} or concat(CECHA, NAZWA_2, ' ', NAZWA_1) like {$ulica}) {$where} group by WOJ, POW, SYM_UL";
			$result = DB::getPDO()->fetchall($query);

			if (!$result) {
				$ulica = DB::getPDO()->quote("ul. {$args['ulica']}");
				$query = "select WOJ, POW, GMI, SYM, SYM_UL from `" . self::ULIC_TABLE . "` where (concat(CECHA, ' ', NAZWA_1) like {$ulica} or concat(CECHA, ' ', NAZWA_2, ' ', NAZWA_1) like {$ulica}) " .
					"{$where} group by WOJ, POW, SYM_UL";
				$result = DB::getPDO()->fetchall($query);
			}

			if (!$result) {
				$ulica = DB::getPDO()->quote("ul. {$args['ulica']}%");
				$query = "select WOJ, POW, GMI, SYM, SYM_UL from `" . self::ULIC_TABLE . "` where (concat(CECHA, ' ', NAZWA_1) like {$ulica} or concat(CECHA, ' ', NAZWA_2, ' ', NAZWA_1) like {$ulica} " .
					"or concat(CECHA, NAZWA_1) like {$ulica} or concat(CECHA, NAZWA_2, ' ', NAZWA_1) like {$ulica}) {$where} group by WOJ, POW, SYM_UL";
				$result = DB::getPDO()->fetchall($query);
			}

			if (!$result) {
				$ulica = DB::getPDO()->quote("{$args['ulica']}%");
				$query = "select WOJ, POW, GMI, SYM, SYM_UL from `" . self::ULIC_TABLE . "` where (concat(CECHA, ' ', NAZWA_1) like {$ulica} or concat(CECHA, ' ', NAZWA_2, ' ', NAZWA_1) like {$ulica} " .
					"or concat(CECHA, NAZWA_1) like {$ulica} or concat(CECHA, NAZWA_2, ' ', NAZWA_1) like {$ulica}) {$where} group by WOJ, POW, SYM_UL";
				$result = DB::getPDO()->fetchall($query);
			}

			if (!$result) {
				$ulica = DB::getPDO()->quote("{$args['ulica']}%");
				$query = "select WOJ, POW, GMI, SYM, SYM_UL from `" . self::ULIC_TABLE . "` where (NAZWA_1 like {$ulica} or concat(NAZWA_2, ' ', NAZWA_1) like {$ulica}) {$where} group by WOJ, POW, SYM_UL";
				$result = DB::getPDO()->fetchall($query);
			}

			if (!$result) {
				$ulica = DB::getPDO()->quote("%{$args['ulica']}");
				$query = "select WOJ, POW, GMI, SYM, SYM_UL from `" . self::ULIC_TABLE . "` where (NAZWA_1 like {$ulica} or concat(NAZWA_2, ' ', NAZWA_1) like {$ulica}) {$where} group by WOJ, POW, SYM_UL";
				$result = DB::getPDO()->fetchall($query);
			}

			if (!$result) {
				$ulica = DB::getPDO()->quote("%{$args['ulica']}%");
				$query = "select WOJ, POW, GMI, SYM, SYM_UL from `" . self::ULIC_TABLE . "` where (NAZWA_1 like {$ulica} or concat(NAZWA_2, ' ', NAZWA_1) like {$ulica}) {$where} group by WOJ, POW, SYM_UL";
				$result = DB::getPDO()->fetchall($query);
			}

			if (!$result) {
				preg_match('/^([^\.]*)\.([^\.]*)\.(.*)$/', $args['ulica'], $matches);
				if (count($matches) == 4) {
					$ulica = DB::getPDO()->quote(trim($matches[1]) . ". " . trim($matches[2]) . "% " . trim($matches[3]) . "%");
					$query = "select WOJ, POW, GMI, SYM, SYM_UL from `" . self::ULIC_TABLE . "` where (concat(CECHA, ' ', NAZWA_1) like {$ulica} " .
						"or concat(CECHA, ' ', NAZWA_2, ' ', NAZWA_1) like {$ulica}) {$where} group by WOJ, POW, SYM_UL";
					$result = DB::getPDO()->fetchall($query);

					if (!$result) {
						$ulica = DB::getPDO()->quote(trim($matches[1]) . ". " . trim($matches[3]) . "%");
						$query = "select WOJ, POW, GMI, SYM, SYM_UL from `" . self::ULIC_TABLE . "` where concat(CECHA, ' ', NAZWA_1) like {$ulica} {$where} group by WOJ, POW, SYM_UL";
						$result = DB::getPDO()->fetchall($query);
					}

					if (!$result) {
						$ulica = DB::getPDO()->quote("ul. " . trim($matches[3]) . "%");
						$query = "select WOJ, POW, GMI, SYM, SYM_UL from `" . self::ULIC_TABLE . "` where concat(CECHA, ' ', NAZWA_1) like {$ulica} {$where} group by WOJ, POW, SYM_UL";
						$result = DB::getPDO()->fetchall($query);
					}
				}
			}

			if (!$result) {
				preg_match('/^([^\.]*)\.(.*)$/', $args['ulica'], $matches);
				if (count($matches) == 3) {
					$ulica = DB::getPDO()->quote("ul. " . trim($matches[1]) . "% " . trim($matches[2]) . "%");
					$query = "select WOJ, POW, GMI, SYM, SYM_UL from `" . self::ULIC_TABLE . "` where (concat(CECHA, ' ', NAZWA_1) like {$ulica} " .
						"or concat(CECHA, ' ', NAZWA_2, ' ', NAZWA_1) like {$ulica}) {$where} group by WOJ, POW, SYM_UL";
					$result = DB::getPDO()->fetchall($query);

					if (!$result) {
						$ulica = DB::getPDO()->quote("ul. " . trim($matches[2]) . "%");
						$query = "select WOJ, POW, GMI, SYM, SYM_UL from `" . self::ULIC_TABLE . "` where concat(CECHA, ' ', NAZWA_1) like {$ulica} {$where} group by WOJ, POW, SYM_UL";
						$result = DB::getPDO()->fetchall($query);
					}

					if (!$result) {
						$ulica = DB::getPDO()->quote(trim($matches[1]) . ". " . trim($matches[2]) . "%");
						$query = "select WOJ, POW, GMI, SYM, SYM_UL from `" . self::ULIC_TABLE . "` where concat(CECHA, ' ', NAZWA_1) like {$ulica} {$where} group by WOJ, POW, SYM_UL";
						$result = DB::getPDO()->fetchall($query);
					}

					if (!$result) {
						$ulica = DB::getPDO()->quote(trim($matches[1]) . ". %" . trim($matches[2]));
						$query = "select WOJ, POW, GMI, SYM, SYM_UL from `" . self::ULIC_TABLE . "` where concat(CECHA, ' ', NAZWA_1) like {$ulica} {$where} group by WOJ, POW, SYM_UL";
						$result = DB::getPDO()->fetchall($query);
					}
				}
			}

			if (!$result) {
				$where = "and WOJ = '{$return['WOJ']}' and POW = '{$return['POW']}' and GMI = '{$return['GMI']}'";
				$query = "select WOJ, POW, GMI, SYM from `" . self::SIMC_TABLE . "` where NAZWA like " . DB::getPDO()->quote($args['ulica']) . " {$where}";
				$result = DB::getPDO()->fetchall($query);
				if (count($result) == 1) $ulicaNeeded = false;
			}

			if (!$result) {
				if (isset($return['SYM'])) $where2 = "and SYM = '{$return['SYM']}'";
				else $where2 = "";
				$where = "and WOJ = '{$return['WOJ']}' and POW = '{$return['POW']}' and ((RODZ_GMI<8 and GMI = '{$return['GMI']}' {$where2}) or RODZ_GMI>=8)";
				$query = "select count(*) from `" . self::ULIC_TABLE . "` where 1 = 1 {$where}";
				if ($liczbaUlic = DB::getPDO()->fetchValue($query)) throw new Exception(json_encode(['msg' => "ulica not found", 'sql' => $query]));
			} else {
				if (count($result) > 1) self::throwException("Found too many ulicas", $query);
				$return['WOJ'] = $result[0]['WOJ'];
				$return['POW'] = $result[0]['POW'];
				$return['GMI'] = $result[0]['GMI'];
				$return['SYM'] = $result[0]['SYM'];
				if (isset($result[0]['SYM_UL'])) $return['SYM_UL'] = $result[0]['SYM_UL'];
				elseif ($ulicaNeeded) throw new Exception("Ulica not found, but needed");
			}

			if (!isset($return['SYM'])) {
				if (isset($SYMarr)) $return['SYM'] = $SYMarr[0];
				else throw new Exception("miejscowosc not found");
			}
		} elseif (isset($return['SYM'])) {
			$query = "select count(*) from `" . self::ULIC_TABLE . "` where `SYM` = '{$return['SYM']}'";
			if (DB::getPDO()->fetchValue($query)) throw new Exception(json_encode(['msg' => "missing required parameter - ulica", 'sql' => $query]));
		}

		return $return;
	}

	public static function verify($args) {
		$argsAvailable = ["WOJ", "POW", "GMI", "SYM", "SYM_UL"];
		self::checkArgs($args, $argsAvailable);

		if (!(isset($args['SYM']) && isset($args['SYM_UL']))) throw new Exception("Missing SYM or/and SYM_UL");

		if ($args['SYM_UL']) {
			$query = "select WOJ, POW, GMI, CECHA, NAZWA_1, NAZWA_2 from `" . self::ULIC_TABLE . "` where SYM = " . DB::getPDO()->quote($args['SYM']) . " and SYM_UL = " . DB::getPDO()->quote($args['SYM_UL']);
			if (!($result = DB::getPDO()->fetchall($query))) throw new Exception("ulica not found");
			if (count($result) > 1) throw new Exception("Found too many ulicas");
		} else {
			$query = "select count(*) from `" . self::ULIC_TABLE . "` where `SYM` = '{$args['SYM']}'";
			if (DB::getPDO()->fetchValue($query)) throw new Exception("Invalid SYM_UL");
			$query = "select `WOJ`, `POW`, `GMI` from `" . self::SIMC_TABLE . "` where SYM = " . DB::getPDO()->quote($args['SYM']);
			if (!($result = DB::getPDO()->fetchall($query))) throw new Exception("miejscowosc not found");
		}

		return $result[0];
	}

	public static function getNames($args) {
		$result = self::verify($args);

		if ($args['SYM_UL']) {
			$return['ulica_cecha'] = $result['CECHA'];
			$return['ulica_nazwa_1'] = $result['NAZWA_1'];
			$return['ulica_nazwa_2'] = $result['NAZWA_2'];
		}

		if (isset($args['WOJ'])) {
			if ($args['WOJ'] != $result['WOJ']) throw new Exception("Invalid WOJ");
		} else $args['WOJ'] = $result['WOJ'];

		if (isset($args['POW'])) {
			if ($args['POW'] != $result['POW']) throw new Exception("Invalid POW");
		} else $args['POW'] = $result['POW'];

		if (isset($args['GMI'])) {
			if ($args['GMI'] != $result['GMI']) throw new Exception("Invalid POW");
		} else $args['GMI'] = $result['GMI'];

		$query = "select NAZWA from `" . self::TERC_TABLE . "` where WOJ = '{$args['WOJ']}' and POW is null";
		if (!($return['wojewodztwo'] = DB::getPDO()->fetchValue($query))) throw new Exception("wojewodztwo not found");

		$query = "select NAZWA, NAZWA_DOD from `" . self::TERC_TABLE . "` where WOJ = '{$args['WOJ']}' and POW = '{$args['POW']}' and GMI is null";
		if (!($result = DB::getPDO()->fetchall($query))) throw new Exception("powiat not found");
		if (count($result) > 1) throw new Exception("Found too many powiats");
		$return['powiat'] = $result[0]['NAZWA'];
		$return['powiat_rodzaj'] = $result[0]['NAZWA_DOD'];

		$query = "select NAZWA, NAZWA_DOD from `" . self::TERC_TABLE . "` where WOJ = '{$args['WOJ']}' and POW = '{$args['POW']}' and GMI = '{$args['GMI']}' group by WOJ, POW, GMI, NAZWA";
		if (!($result = DB::getPDO()->fetchall($query))) throw new Exception("gmina not found");
		if (count($result) > 1) throw new Exception("Found too many gminas");
		$return['gmina'] = $result[0]['NAZWA'];
		$return['gmina_rodzaj'] = $result[0]['NAZWA_DOD'];

		$query = "select NAZWA from `" . self::SIMC_TABLE . "` where WOJ = '{$args['WOJ']}' and POW = '{$args['POW']}' and GMI = '{$args['GMI']}' and SYM = '{$args['SYM']}'";
		if (!($result = DB::getPDO()->fetchall($query))) throw new Exception("miejscowosc not found");
		if (count($result) > 1) throw new Exception("Found too many miejscowoscs");
		$return['miejscowosc'] = $result[0]['NAZWA'];

		return $return;
	}

}