<?php
require("config.php");

class Data {
  var $layers;
  var $regions;
  var $suggest;
  function Data() {
    $this->layers = array();
    $this->regions = array();
    $this->suggest = array();
  }
  function get_layer($id) {
    return $this->layers[$id];
  }
  function add_layer($layer) {
    $this->layers[$layer->id] = $layer;
  }
  function get_region($id) {
    return $this->regions[$id];
  }
  function add_region($region) {
    $region->coordinates = unserialize($region->coordinates);
    $this->regions[$region->id] = $region;
  }
  function add_suggestion($name) {
    $this->suggest[] = $name;
  }
}

class LayerDB {
  var $mysql_link;
  var $data;

  var $geometry = array('point' => 'Point',
      'polygon' => 'Polygon',
      'polyline' => 'LineString');


  function LayerDB() {
    if (!isset($this->data)) {
      $this->data = new Data();
    }
    $this->mysql_link = mysql_connect(DB_SERVER, DB_USER, DB_PASSWORD) or
      die("could not connect to mysql server " . mysql_error());
    mysql_select_db(DB_NAME);
    $this->fetchLayers();
  }

  function getLayerArray() {
    return $this->data->layers;
  }

  function getLayer($id) {
    return $this->data->layers[$id];
  }

  function getRegion($id) {
    return $this->data->regions[$id];
  }

  function getColors() {
    // Fetch all the rows
    $colors = array();
    $sql = "SELECT colorid, color FROM " . COLOR_TABLE . " WHERE 1 > 0";
    $result = mysql_query($sql) or die(mysql_error());
    if (mysql_num_rows($result) > 0) {
      while ($row = mysql_fetch_object($result)) {
	$colors[$row->colorid] = $row->color;
      }
    }
    return $colors;
  }

  function fetchLayers() {
    // Fetch all the rows
    $sql = "SELECT * FROM " . LAYER_TABLE . ", " . COLOR_TABLE .
      " WHERE (status = 'locked' OR status = 'live') AND " .
      LAYER_TABLE . ".colorid = " . COLOR_TABLE . ".colorid";
    $result = mysql_query($sql) or die(mysql_error());
    if (mysql_num_rows($result) > 0) {
      while ($row = mysql_fetch_object($result)) {
	$this->data->add_layer($row);
      }
    }
  }

  function done() {
    mysql_close($this->mysql_link);
  }

  function points_in_region($region) {
    if ($region->geometry == 'polygon') {
      $sql = "SELECT id FROM " . REGION_TABLE .
	" WHERE geometry='point'".
	" AND (status = 'locked' OR status = 'live') ". 
	" AND minlat > " . strval($region->minlat) .
	" AND maxlat < " . strval($region->maxlat) .
	" AND minlng > " . strval($region->minlng) .
	" AND maxlng < " . strval($region->maxlng) .
	" AND layerid=" . $region->layerid;
      // echo $sql;
      $result = mysql_query($sql) or die(mysql_error());
      if (mysql_num_rows($result) > 0) {
	while ($row = mysql_fetch_object($result)) {
	  $this->fetch_region($row->id, false);
	}
      }
    }
  }

  function fetch_region($id, $recurse = true) {
    $sql = "SELECT * FROM " . REGION_TABLE .  " WHERE id=" . $id;
    $result = mysql_query($sql) or die(mysql_error());
    if ($row = mysql_fetch_object($result)) {
      $this->data->add_region($row); 
      if ($recurse) {
	$this->points_in_region($row);
      }
    }
  }

  function fetch_layer_regions($layerid) {
      $sql = "SELECT id FROM ". REGION_TABLE .
	" WHERE layerid=$layerid";
      $result = mysql_query($sql) or die(mysql_error());
      if (mysql_num_rows($result) > 0) {
	while ($row = mysql_fetch_object($result)) {
	  $this->fetch_region($row->id, false);
	}
      }
      mysql_free_result($result);
  }

  function delete_region($id) {
    $sql = "UPDATE region set status='deleted' WHERE id=$id";
    mysql_query($sql);
  }

  function update_region($id, $name, $info) {
    $sql = "UPDATE region SET ".
      ($name?" name='$name'":"") .
      ($info?", info='$info'":"") .
      " WHERE id=$id";
    mysql_query($sql);
  }

  function add_region($layerid, $ll, $name, $info) {
    $point = split(',', $ll);
    if (count($point) != 2) {
      return;
    }
    $lng = $point[0];
    $lat = $point[1];
    $carr = array(array($lat, $lng, 0));
    $coord_str = serialize($carr); 
    $sql = sprintf("INSERT INTO region (layerid, geometry, name, info,".
	"coordinates, minlat, maxlat, minlng, maxlng) VALUES " .
	"(%d, 'point', '%s', '%s', '%s', %f, %f, %f, %f); ",
	$layerid, $name, $info, $coord_str,
	$lat, $lat, $lng, $lng);
    echo $sql;
    mysql_query($sql);
  }

  function delete_layer($id) {
    $sql = "UPDATE layer set status='deleted' WHERE id=$id";
    mysql_query($sql);
    $sql = "UPDATE region set status='deleted' WHERE layerid=$id";
    mysql_query($sql);
  }

  function update_layer($id, $name, $color, $icon, $details, $status) {
    $sql = "UPDATE layer SET ".
      ($name?" name='$name'":"") .
      ($color?", colorid='$color'":"") .
      ($icon?", icon='$icon'":"") .
      ($details?", details='$details'":"") .
      ($status?", status='$status'":"") .
      " WHERE id=$id";
    mysql_query($sql);
  }

  function search($q) {
    if (!$q || $q == "") {
      return;
    } 
    $sql = "SELECT id FROM ". REGION_TABLE .
      " WHERE (status = 'locked' OR status = 'live') ". 
      " AND name LIKE '%$q%' " .
      " LIMIT 0, 100";
    // echo $sql;

    $result = mysql_query($sql) or die(mysql_error());
    if (mysql_num_rows($result) > 0) {
      while ($row = mysql_fetch_object($result)) {
	$this->fetch_region($row->id, false);
      }
    }
  }

  function find_near($bound, $layers) {
    $lsql = "";
    if ($layers) {
      $count = count($layers);
      if ($count > 0) {
	$lsql = " AND ( " . $lsql . " layerid=" . $layers[0];
	for ($i = 1; $i < $count; $i++) {
	  $lsql = $lsql . " OR layerid=" . $layers[$i];
	}
	$lsql = $lsql . " ) ";
      }
      // echo $lsql;
      $sql = "SELECT id FROM ". REGION_TABLE .
	" WHERE (status = 'locked' OR status = 'live') ". 
	" AND minlng < " . strval($bound[0] + 0.5 * $bound[2]) .
	" AND maxlng > " . strval($bound[0] - 0.5 * $bound[2]) .
	" AND minlat < " . strval($bound[1] + 0.5 * $bound[3]) .
	" AND maxlat > " . strval($bound[1] - 0.5 * $bound[3]) .
	$lsql .
	" LIMIT 0, 100";

      // echo $sql;
      $result = mysql_query($sql) or die(mysql_error());
      if (mysql_num_rows($result) > 0) {
	while ($row = mysql_fetch_object($result)) {
	  $this->fetch_region($row->id);
	}
      }
      mysql_free_result($result);
    }
  }

  function find_suggestions($q) {
    $sql = "SELECT DISTINCT name FROM ". REGION_TABLE .
      " WHERE (status = 'locked' OR status = 'live') ". 
      " AND name LIKE '$q%' AND geometry='point' " .
      " LIMIT 0, 10";

    // echo $sql;
    $result = mysql_query($sql) or die(mysql_error());
    if (mysql_num_rows($result) > 0) {
      while ($row = mysql_fetch_object($result)) {
	$this->data->add_suggestion($row->name);
      }
    }
    mysql_free_result($result);
  }

  function region_kml($region) {
    // print_r($region);
    $response =
      sprintf('<Placemark><description><![CDATA[%s]]></description><name>%s</name>',
	  $region->info, $region->name);
    $response .= '<visibility>1</visibility>';
    if ($region->geometry == "point") {
      $response .= '<Point><coordinates>';
    } elseif ($region->geometry == "polygon") {
      $response .= '<Polygon><outerBoundaryIs><LinearRing><coordinates>';
    } else {
      return "";
    }
    foreach ($region->coordinates as $coord) {
	$response .= ($coord[0].','.$coord[1].',0 ');
    }
    $response .= "</coordinates>";
    if ($region->geometry == "point") {
      $response .= '</Point>';
    } elseif ($region->geometry == "polygon") {
      $response .= '</LinearRing></outerBoundaryIs></Polygon>';
    } else {
      return "";
    }
    $response .= '</Placemark>';
    return $response;
  }

  function layer_kml($layerid) {
    $this->fetch_layer_regions($layerid);
    $response = '<?xml version="1.0" encoding="UTF-8"?>' .
      '<kml xmlns="http://earth.google.com/kml/2.0">';
    $layer = $this->getLayer($layerid);
    $response .=
      sprintf('<Document><description><![CDATA[%s]]></description><name>%s</name>',
	  $layer->description, $layer->name);
    foreach ($this->data->regions as $region) {
      $response .= $this->region_kml($region);
    }
    $response .= "</Document></kml>";
    return $response;
  }
}