-- ------------------------------------------------
-- add geometry columns
select AddGeometryColumn('db_xenia_v2','multi_obs','the_geom',-1,'POINT',2);
select AddGeometryColumn('db_xenia_v2','multi_obs','the_geom_line',-1,'LINESTRING',2);

-- ------------------------------------------------
-- basic lookup table population
INSERT INTO organization (row_id,short_name,active,long_name,url) VALUES (1, 'horizon_marine', true, 'Horizon Marine', 'http://www.horizonmarine.com');
INSERT INTO organization (row_id,short_name,active,long_name,url) VALUES (2, 'aoml', true, 'aoml', '');

INSERT INTO platform_type (row_id,type_name,description) VALUES (1, 'drifter', 'A drifter');
INSERT INTO platform (row_id,organization_id,type_id,short_name,platform_handle) VALUES (1, 1, 1, '01712', 'horizon_marine:01712:drifter');

INSERT INTO sensor_type (row_id,type_name,description) VALUES (1, 'drifter', 'changes in position/speed related to drifter movement');
INSERT INTO sensor (row_id,platform_id,type_id,short_name,m_type_id,s_order) VALUES (1,1,1,'drifter',41,1);

-- ------------------------------------------------
-- drifter line segment function/trigger
-- DROP FUNCTION add_new_drifter_point() CASCADE;

CREATE FUNCTION add_new_drifter_point() RETURNS trigger AS $add_new_drifter_point$

declare
top_drifter_point record;
top_m_date timestamp without time zone;
top_drifter_point_lon float;
top_drifter_point_lat float;
this_drifter_point_lon float;
this_drifter_point_lat float;

begin

  if new.m_type_id = 41 then -- only adding line segments for drifter m_type_id
    for top_drifter_point in select m_date,m_lon,m_lat
      from multi_obs
      where platform_handle = new.platform_handle and sensor_id = new.sensor_id
      order by m_date desc limit 1 loop

      top_m_date := top_drifter_point.m_date;
      top_drifter_point_lon := top_drifter_point.m_lon;
      top_drifter_point_lat := top_drifter_point.m_lat;
      this_drifter_point_lon := new.m_lon;
      this_drifter_point_lat := new.m_lat;
    end loop;

    update multi_obs set
      the_geom_line = GeometryFromText('LINESTRING(' || top_drifter_point_lon || ' ' || top_drifter_point_lat || ',' || this_drifter_point_lon || ' ' || this_drifter_point_lat || ')',-1)
      where platform_handle = new.platform_handle and sensor_id = new.sensor_id
      and m_date = top_m_date;
  end if;

  return new;
end;
$add_new_drifter_point$ LANGUAGE plpgsql;

CREATE TRIGGER add_new_drifter_point BEFORE INSERT ON multi_obs
    FOR EACH ROW EXECUTE PROCEDURE add_new_drifter_point();