--
--  Call the pgroute traveling sales man soultion using an sql string
--  instead of a matrix solution.
--
--  The input is a sql query that returns a source/target nodes  and cost between, the out put
--  is a list of nodes.
--
--    select source,target,cost from route_table
--
--    Were source && target are any integers and cost is a double
--

--  DUE to the number of messages generated about temporary tables, chaging the
--  default of PGOPTIONS to ='--client-min-messages=warning' will stop a lot
--  of uncessary error messages
--

DROP  type pgr_tsprowtype CASCADE;
CREATE type pgr_tsprowtype as (id integer,seq integer );

--
-- Take an sql string with source,target and cost and a starting node and generate
-- a result set.
--

CREATE or REPLACE FUNCTION pgr_mktspret(sqltext text ,startpt integer) 
	RETURNS SETOF pgr_tsprowtype 

AS $$
DECLARE
	matrix float [][];
	newstartpt integer;
	tmp pgr_tsprowtype%rowtype;
BEGIN
	SELECT pgr_mktsparray INTO matrix FROM pgr_mktsparray(sqltext);
	SELECT pgr_tspname2index  INTO newstartpt FROM pgr_tspname2index(startpt);


	IF ( newstartpt is null )  THEN
		RAISE EXCEPTION  ' Error Can not find node % in sql request ',startpt;
	END IF ;
	FOR tmp IN  
		SELECT (pgr_tsp.seq),(pgr_tspindex2name(pgr_tsp.id))  FROM pgr_tsp(matrix,newstartpt) LOOP
		RETURN NEXT tmp;
	END LOOP;
END
$$
LANGUAGE 'plpgsql' VOLATILE;

--
-- Take an sql string with source,target and cost and a starting/end node and generate
-- a result set.
--

CREATE or REPLACE FUNCTION pgr_mktspret(sqltext text ,startpt integer, endpt integer) 
	RETURNS SETOF pgr_tsprowtype 

AS $$
DECLARE
	matrix float [][];
	newstartpt integer;
	newendpt integer;
	tmp pgr_tsprowtype%rowtype;
BEGIN
	SELECT pgr_mktsparray INTO matrix from pgr_mktsparray(sqltext);
	SELECT pgr_tspname2index  INTO newstartpt from pgr_tspname2index(startpt);
	SELECT pgr_tspname2index  INTO newendpt from pgr_tspname2index(endpt);

	IF ( newstartpt is null )  THEN
		RAISE EXCEPTION  ' Error Can not find node % in sql request ',startpt;
	END IF ;

	IF ( newendpt is null )  THEN
		RAISE EXCEPTION  ' Error Can not find node % in sql request ',endpt;
	END IF ;

	FOR tmp IN  
		SELECT (pgr_tsp.seq),pgr_tspindex2name(pgr_tsp.id)  FROM pgr_tsp(matrix,newstartpt,newendpt) LOOP
		RETURN NEXT tmp;
	END LOOP;
END
$$
LANGUAGE 'plpgsql' VOLATILE;


--
-- Check tsp array
--
-- It needs to have the leading axis filled with zeros and it
-- must be symmetric, 
--
--
CREATE or REPLACE FUNCTION pgr_checktsparray(float [][]) RETURNS boolean  AS
$$
DECLARE 
	userData alias for $1;
	ret boolean;
	lookupi integer;
	lookupj integer;
	
BEGIN


	ret:= false;

	FOR i IN 1..array_size LOOP
		FOR j IN 1..array_size LOOP

			IF ( i = j AND ret_array[i][j] <> 0 )  THEN

				RAISE WARNING  ' Error in mktsparray m[%][%] should be zero ',i,j;
				RETURN ret;
			ELSE 
				IF ( ret_array[i][j] <> ret_array[j][i] ) THEN
					SELECT pgr_tspindex2name(i) INTO lookupi;
					SELECT pgr_tspindex2name(j) INTO lookupj;
  					RAISE WARNING 'Error in mktsparray target % source % should be the same as source % target % ',lookupi,lookupj,lookupj,lookupi;
					RETURN ret;

				END IF ;

			END IF ;
		END LOOP;

 	END LOOP;

	ret := true;

	return ret;

END 
$$
LANGUAGE 'plpgsql' STRICT;

--
-- Lookup a internal network number and convert it to a user number 
--
CREATE or REPLACE FUNCTION pgr_tspindex2name(integer) RETURNS integer AS 
$$
DECLARE
	userid alias for $1;

	ret integer;
BEGIN
 	SELECT nid  from pgr_tsp_map where id= userid +1 INTO ret ;
	RETURN ret;
END
$$
LANGUAGE 'plpgsql';

--
-- Lookup a user network node and convert it to an internal matrix number
--
CREATE or REPLACE FUNCTION pgr_tspname2index(integer) RETURNS integer AS 
$$
DECLARE
	userid alias for $1;

	ret integer;
BEGIN
 	SELECT id  FROM pgr_tsp_map WHERE nid= userid INTO ret ;
	return ret-1;
END
$$
LANGUAGE 'plpgsql';

--
-- Make a distance matrix for the traveling salesman  problem
--
CREATE or REPLACE FUNCTION pgr_mktsparray(sqlin text ) RETURNS double precision[] AS
$$
DECLARE
    sql text;
    r record;
    retmatrix double precision[];
    dmatrix double precision[];
    array_size integer;
    i integer;
    j integer;
    k integer;
    offset integer;
   

BEGIN
    offset:= 0;
    dmatrix := array[]::double precision[];
	
    -- create a copy of the source
    EXECUTE 'DROP TABLE IF EXISTS pgr_tsp_src';
    EXECUTE 'DROP TABLE IF EXISTS pgr_tsp_map';
    EXECUTE 'DROP TABLE IF EXISTS pgr_tsp_cp';
    CREATE temporary TABLE pgr_tsp_cp ( id serial not null,source integer not null,
                                target integer not null,  cost float not null);

    CREATE temporary TABLE pgr_tsp_src ( id serial not null,source integer not null,
                                target integer not null,  cost float not null);
    -- create a tsp node to matrix lookup table
    create temporary table pgr_tsp_map ( id serial not null primary key, nid integer);

    -- take of a copy of the users data, to get rid of any possible complex
    -- group in type clauses 
    EXECUTE 'INSERT INTO pgr_tsp_cp (source, target, cost)'|| sqlin;
    --
    -- Map input node ids which may be in the range 1-N  in to a single
    -- numerical range 1,2,3,4 etc 
    --
    INSERT INTO pgr_tsp_map (nid) SELECT nid FROM (select distinct source as nid from pgr_tsp_cp union select distinct target as nid from pgr_tsp_cp  ) as foo ;

    EXECUTE 'INSERT into pgr_tsp_src (source, target, cost) select src.id as source,tar.id as target,pgr_tsp_cp.cost from pgr_tsp_cp, pgr_tsp_map src, pgr_tsp_map tar where pgr_tsp_cp.target = tar.nid and pgr_tsp_cp.source= src.nid';

    SELECT count(*) INTO array_size FROM pgr_tsp_map;

    retmatrix :=  array_fill(0,ARRAY  [ array_size,array_size]);

    sql='SELECT i, array_agg(cost) as arow FROM (
            select source as i, target as j, cost  from pgr_tsp_src
             order by source, target
           ) as foo group by i order by i';

    FOR r IN EXECUTE sql LOOP
        dmatrix := array_cat(dmatrix, array[r.arow]);
    END LOOP;

    -- Generated array is missing the leading dignal of zeros
    -- add it now.
    FOR i IN 1..array_size LOOP
  	k:=0;
    	FOR j in 1..array_size-1 LOOP

		IF ( i = j ) THEN 
			k:=1;
		END IF;
		retmatrix[i][j+k]:=dmatrix[i][j];
	END LOOP;
      END LOOP;


    RETURN retmatrix;

END;
$$
LANGUAGE 'plpgsql';
