 -- Function: gis.get_handovers
--- DROP FUNCTION gis.get_handovers(integer);
-CREATE OR REPLACE FUNCTION gis.get_handovers(IN integer)
+
+CREATE OR REPLACE FUNCTION gis.get_handovers(integer)
     RETURNS TABLE(
         source_id integer,
         handover_id integer,

        direction text,
        existance text
    ) AS
$BODY$
    SELECT	src_rel.source_id, src_rel.target_id, trg_rel.target_id, 
        trg_et.name, src_et.name , et.name, 'OUT', 
        array_to_string( 
            array_agg(
                ex.exists || ',' || date_part('epoch',ex.timestamp)
            ), ';')

    FROM relation."Cell->HandoverRelation" src_rel
    JOIN relation."HandoverRelation->Cell" trg_rel on src_rel.target_id=trg_rel.source_id
    JOIN directory.entity src_et ON src_et.id = src_rel.source_id
    JOIN directory.entity trg_et ON trg_et.id = trg_rel.target_id
    JOIN directory.entitytaglink etl on etl.entity_id = trg_et.id
    JOIN directory.taggroup etg on etg.name = 'generation'
    JOIN directory.tag et on etl.tag_id = et.id  and etg.id = et.taggroup_id
    JOIN relation.real_handover real_ho on real_ho.source_id = src_rel.target_id
    JOIN directory.existence ex on ex.entity_id = real_ho.target_id
    WHERE src_rel.source_id = $1
    GROUP BY src_rel.source_id, src_rel.target_id, trg_rel.target_id, trg_et.name, src_et.name , et.name, real_ho.source_id
        
    UNION
        
    SELECT 	src_rel.source_id, src_rel.target_id, trg_rel.target_id,
        trg_et.name, src_et.name , et.name, 'IN',
        array_to_string( 
            array_agg(
                ex.exists || ',' || date_part('epoch',ex.timestamp)
            ), ';')

    FROM relation."Cell->HandoverRelation" src_rel 
    JOIN relation."HandoverRelation->Cell" trg_rel on src_rel.target_id=trg_rel.source_id 
    JOIN directory.entity src_et ON src_et.id=src_rel.source_id 
    JOIN directory.entity trg_et ON trg_et.id = trg_rel.target_id 
    JOIN directory.entitytaglink etl on etl.entity_id=src_et.id 
    JOIN directory.taggroup etg on etg.name = 'generation'
    JOIN directory.tag et on etl.tag_id = et.id  and etg.id = et.taggroup_id
    JOIN relation.real_handover real_ho on real_ho.source_id = src_rel.target_id 
    JOIN directory.existence ex on ex.entity_id = real_ho.target_id 
    WHERE trg_rel.target_id = $1
    GROUP BY src_rel.source_id, src_rel.target_id, trg_rel.target_id, trg_et.name, src_et.name , et.name, real_ho.source_id

$BODY$
  LANGUAGE sql STABLE
  COST 100
  ROWS 1000;


ALTER FUNCTION gis.get_handovers(integer) OWNER TO postgres;


-- Function: gis.get_changed_handover_cells(timestamp with time zone)

-- DROP FUNCTION gis.get_changed_handover_cells(timestamp with time zone);

CREATE OR REPLACE FUNCTION gis.get_changed_handover_cells(timestamp with time zone)
  RETURNS SETOF integer AS
$BODY$
SELECT id FROM (
    SELECT sc.source_id as id FROM (
        SELECT e.id as id 

    JOIN relation."HandoverRelation->Cell" tc on tc.source_id = ho.id
) t group by t.id order by t.id

$$ LANGUAGE sql STABLE;
