create or replace function explode_array(anyarray) returns setof anyelement as
$$
    select ($1)[s] from generate_series(1,array_upper($1, 1)) as s;
$$
language sql immutable;

create or replace function unzip(anyarray) returns setof anyelement as
$$
    select ($1)[s] from generate_series(1,array_upper($1, 1)) as s;
$$
language sql immutable;


create or replace function explode_array(anyarray, anyarray) returns setof record as
$$
    select ($1)[s],($2)[s] from generate_series(1,array_upper($1, 1)) as s;
$$
language sql immutable;

-- Quickly threshold a score list.  Scores are stored in sorted order.
CREATE TYPE thresh_rettype AS (
       hit_list      int[],
       score_list    double precision[]
);

-- All hits > Minimum score
create or replace function thresh_min(
hit_list anyarray, score_list double precision[], thresh double precision)
returns thresh_rettype AS
$$
DECLARE
   lists thresh_min_rettype;
   high integer;
   low integer;
   mid integer;
   i integer;
BEGIN
   low = 1;
   high = array_upper(hit_list, 1);

   WHILE low < high LOOP

         mid = (low + high) / 2;

         IF score_list[mid] > thresh THEN
            low = mid + 1;
         ELSE
            high = mid;
         END IF;
   END LOOP;

   IF low < array_upper(hit_list,1) THEN
      low = low - 1;
   END IF;

   lists.hit_list = hit_list[1:low];
   lists.score_list = score_list[1:low];
   RETURN lists;
END;
$$
language plpgsql STABLE;

-- Emulate the old blast_hit and blast_hit_xtra tables.

-- NOTE: If using a score threshold, using the above thresholding
-- function will be at least 500 times faster.
CREATE OR REPLACE VIEW blast_hit AS
SELECT br_id, hit_rank, seq_id_0, 
unzip(hit_list) AS seq_id_1, 
unzip(bit_score) AS bit_score, 
unzip(e_value) AS e_value
FROM blast_hit_arr;

CREATE OR REPLACE VIEW blast_hit_symmetric AS
SELECT br_id, seq_id_0, 
unzip(hit_list) AS seq_id_1, 
unzip(bit_score) AS bit_score
FROM blast_hit_symmetric_arr;

CREATE OR REPLACE VIEW blast_hit_nc AS
SELECT nc_id, seq_id_0, num_match_seq_0,
unzip(hit_list) AS seq_id_1,
unzip(num_match_seq_both) AS num_match_seq_both,
unzip(num_match_seq_1) AS num_match_seq_1,
unzip(nc_score) AS nc_score
FROM blast_hit_nc_arr;

CREATE VIEW blast_hit_xtra AS
SELECT br_id, hit_rank, seq_id_0, seq_id_1,
seq_start_0, seq_end_0, seq_start_1, seq_end_1, identities, gaps,
positives, low_complexity, coverage
FROM blast_hit;


-- Return a histogram of scores
-----------------------------------
CREATE OR REPLACE FUNCTION score_hist(num_bins integer)
RETURNS integer[] AS
$$
DECLARE
   retarr integer[];
   hit_list integer[];
	score_list double precision[];
BEGIN
   FOR retarr in SELECT nc_score from blast_hit_nc_arr LOOP
      RETURN retarr;
      FOR i in array_lower( nc_score, 1) .. array_upper( nc_score, 1) LOOP
           retarr[1] = retarr[1] + nc_score[i];
      END LOOP;
   END LOOP;
RETURN retarr;
END;
$$
LANGUAGE plpgsql STABLE;
bins integer


----------------------------------

create function explode_plpg(anyarray) returns setof anyelement as
'begin
for i in array_lower($1, 1) .. array_upper($1, 1) loop
return next $1[i];
end loop;
return;
end' 
language plpgsql strict immutable;



CREATE OR REPLACE function expand_composite(record_list anyarray) 
RETURNS tmp_array_type AS
$$
DECLARE
   lists tmp_array_type;
   cur_rec nc_record;
BEGIN
   FOR i in array_lower( record_list, 1) .. array_upper(record_list, 1) LOOP
      cur_rec = record_list[i];
      lists.nc_score[i] = cur_rec.nc_score;
      lists.num_match_seq_1[i] = cur_rec.num_match_seq_1;
      lists.num_match_seq_both[i] = cur_rec.num_match_seq_both;
   END LOOP;
   RETURN lists;
END;
$$
language plpgsql STABLE;

create or replace function fetch_nc_thresh(
hit_list anyarray, record_list nc_record[], thresh double precision)
returns fetch_nc_thresh_rettype AS
$$
DECLARE
   lists fetch_nc_thresh_rettype;
   nc_list double precision[];
   score double precision;
	tmp_list nc_record[];
	min_i integer;
	max_i integer;
BEGIN
	min_i = array_lower( hit_list,1);
	max_i = array_upper(

   FOR i in array_lower( tmp_list, 1) .. array_upper( tmp_list, 1) LOOP

       score = tmp_list[i].nc_score;

       IF score < thresh THEN
          lists.hit_list = hit_list[1:i-1];

          RETURN lists;
       END IF;
       
       lists.nc_list[i] = score;
   END LOOP;
END;
$$
language plpgsql STABLE;


nc_list[i] = record_list[i].nc_score;
drop function explode_array_blast_record(in_array anyarray);
create or replace function explode_array_blast_record(in_array anyarray)
returns setof blast_record as
$$
    select explode_blast_record(($1)[s]) from generate_series(1,array_upper($1, 1)) as s;
$$
language sql immutable;

-- extract a single element (like bit_score) from an array of
-- blast_record elements
create or replace function blast_logbit(score_list blast_score[])
RETURNS double precision[] AS
$$
DECLARE
   bit_list double precision[];
BEGIN
   FOR i IN array_lower( score_list, 1) .. array_upper( score_list, 1) LOOP
       bit_list[i] = log(score_list[i].bit_score);
   END LOOP;
   RETURN bit_list;
END;  
$$
language plpgsql STABLE;

create or replace function blast_logbit(score_list blast_record[])
RETURNS double precision[] AS
$$
DECLARE
   bit_list double precision[];
BEGIN
   FOR i IN array_lower( score_list, 1) .. array_upper( score_list, 1) LOOP
       bit_list[i] = log(score_list[i].bit_score);
   END LOOP;
   RETURN bit_list;
END;  
$$
language plpgsql STABLE;

create or replace function blast_logbit(score_list blast_record[])
RETURNS double precision[] AS
$$
DECLARE
   bit_list double precision[];
BEGIN
   FOR i IN array_lower( score_list, 1) .. array_upper( score_list, 1) LOOP
       bit_list[i] = log(score_list[i].bit_score);
   END LOOP;
   RETURN bit_list;
END;  
$$
language plpgsql STABLE;



-- 10x faster than the view
create or replace function blast_query_fast(q_br_id integer, 
q_hit_rank integer, q_seq_id_0 integer)
returns setof blast_fullrecord as
$$
DECLARE
   brow blast_hit_arr;
   cur_rec blast_record;
   retrow blast_fullrecord;
BEGIN
   SELECT * INTO brow FROM blast_hit_arr 
   WHERE br_id = q_br_id AND hit_rank = q_hit_rank AND seq_id_0 = q_seq_id_0;

   IF NOT FOUND THEN
      return;
   END IF;

   retrow.br_id = q_br_id;
   retrow.hit_rank = q_hit_rank;
   retrow.seq_id_0 = q_seq_id_0;

   for i in array_lower( brow.hit_list, 1) .. array_upper( brow.hit_list, 1) loop
        retrow.seq_id_1 = brow.hit_list[i];
	     cur_rec = brow.record_list[i];
        retrow.bit_score =cur_rec.bit_score;
        retrow.e_value = cur_rec.e_value;
        retrow.seq_start_0 = cur_rec.seq_start_0;
        retrow.seq_end_0 = cur_rec.seq_end_0;
        retrow.seq_start_1 = cur_rec.seq_start_1;
        retrow.seq_end_1 = cur_rec.seq_end_1;
        retrow.identities = cur_rec.identities;
        retrow.gaps = cur_rec.gaps;
        retrow.positives = cur_rec.positives;
        retrow.low_complexity = cur_rec.low_complexity;
        retrow.coverage = cur_rec.coverage;

        RETURN NEXT retrow;
   end loop;
   return;
END;
$$
language plpgsql STABLE;

create or replace function blast_expand_row(brow blast_hit_arr)
returns setof blast_fullrecord as
$$
DECLARE
   cur_rec blast_record;
   retrow blast_fullrecord;
BEGIN
   retrow.br_id = brow.br_id;
   retrow.hit_rank = brow.hit_rank;
   retrow.seq_id_0 = brow.seq_id_0;

   for i in array_lower( brow.hit_list, 1) .. array_upper( brow.hit_list, 1) loop
        retrow.seq_id_1 = brow.hit_list[i];
         cur_rec = brow.record_list[i];
        retrow.bit_score =cur_rec.bit_score;
        retrow.e_value = cur_rec.e_value;
        retrow.seq_start_0 = cur_rec.seq_start_0;
        retrow.seq_end_0 = cur_rec.seq_end_0;
        retrow.seq_start_1 = cur_rec.seq_start_1;
        retrow.seq_end_1 = cur_rec.seq_end_1;
        retrow.identities = cur_rec.identities;
        retrow.gaps = cur_rec.gaps;
        retrow.positives = cur_rec.positives;
        retrow.low_complexity = cur_rec.low_complexity;
        retrow.coverage = cur_rec.coverage;

        RETURN NEXT retrow;
   end loop;
   return;
END;
$$
language plpgsql STABLE;

create or replace function blast_test(q_br_id integer, q_hit_rank integer)
RETURNS setof blast_fullrecord AS
$$
SELECT blast_expand_row(s) from blast_hit_arr as s where br_id=$1 AND hit_rank=$2;
$$ language sql STABLE;


create or replace function blast_query_all(q_br_id integer, 
q_hit_rank integer, q_limit integer)
returns setof blast_fullrecord as
$$
DECLARE
   brow blast_hit_arr%rowtype;
   cur_rec blast_record;
   retrow blast_fullrecord;
BEGIN
   retrow.br_id = q_br_id;
   retrow.hit_rank = q_hit_rank;

   FOR brow IN SELECT * FROM blast_hit_arr WHERE br_id = q_br_id AND hit_rank = q_hit_rank limit q_limit LOOP

      retrow.seq_id_0 = brow.seq_id_0;

      FOR i IN array_lower( brow.hit_list, 1) .. array_upper( brow.hit_list, 1) loop
            retrow.seq_id_1 = brow.hit_list[i];
            cur_rec = brow.record_list[i];
            retrow.bit_score = cur_rec.bit_score;
            retrow.e_value = cur_rec.e_value;
            retrow.seq_start_0 = cur_rec.seq_start_0;
            retrow.seq_end_0 = cur_rec.seq_end_0;
            retrow.seq_start_1 = cur_rec.seq_start_1;
            retrow.seq_end_1 = cur_rec.seq_end_1;
            retrow.identities = cur_rec.identities;
            retrow.gaps = cur_rec.gaps;
            retrow.positives = cur_rec.positives;
            retrow.low_complexity = cur_rec.low_complexity;
            retrow.coverage = cur_rec.coverage;

            RETURN NEXT retrow;
      END LOOP;

   END LOOP;
   return;
END;
$$
language plpgsql STABLE;

create or replace function blast_query_all4(q_br_id integer, 
q_hit_rank integer)
returns setof blast_fullrecord as
$$
DECLARE
   brow blast_hit_arr%rowtype;
   cur_rec blast_record;
   retrow blast_fullrecord;
BEGIN
   retrow.br_id = q_br_id;
   retrow.hit_rank = q_hit_rank;

   FOR brow IN SELECT * FROM blast_hit_arr WHERE br_id = q_br_id AND hit_rank = q_hit_rank limit 100 LOOP

      retrow.seq_id_0 = brow.seq_id_0;

      FOR i IN array_lower( brow.hit_list, 1) .. array_upper( brow.hit_list, 1) loop
            retrow.seq_id_1 = brow.hit_list[i];
            retrow.bit_score = brow.record_list[i].bit_score;
            retrow.e_value = brow.record_list[i].e_value;
            retrow.seq_start_0 = brow.record_list[i].seq_start_0;
            retrow.seq_end_0 = brow.record_list[i].seq_end_0;
            retrow.seq_start_1 = brow.record_list[i].seq_start_1;
            retrow.seq_end_1 = brow.record_list[i].seq_end_1;
            retrow.identities = brow.record_list[i].identities;
            retrow.gaps = brow.record_list[i].gaps;
            retrow.positives = brow.record_list[i].positives;
            retrow.low_complexity = brow.record_list[i].low_complexity;
            retrow.coverage = brow.record_list[i].coverage;

            RETURN NEXT retrow;
      END LOOP;

   END LOOP;
   return;
END;
$$
language plpgsql STABLE;

create or replace function blast_query_all2(q_br_id integer, 
q_hit_rank integer, q_limit integer)
returns setof blast_fullrecord as
$$
DECLARE
   brow blast_hit_arr%rowtype;
BEGIN
   FOR brow IN SELECT * FROM blast_hit_arr WHERE br_id = q_br_id AND hit_rank = q_hit_rank limit q_limit LOOP

      RETURN QUERY
      SELECT 
      brow.br_id,
      brow.hit_rank,
      brow.seq_id_0,
      t_hit_list, 
      (t_rec_list).*
      from (select brow.hit_list[i] as t_hit_list, 
            brow.record_list[i] as t_rec_list
            FROM generate_series(array_lower(brow.hit_list, 1), array_upper(brow.hit_list, 1)) i offset 0) s;
   END LOOP;
   return;
END;
$$
language plpgsql STABLE;

create or replace function blast_query_all3(q_br_id integer, 
q_hit_rank integer)
returns setof blast_fullrecord as
$$
DECLARE
   brow blast_hit_arr%rowtype;
BEGIN
   FOR brow IN SELECT * FROM blast_hit_arr WHERE br_id = q_br_id AND hit_rank = q_hit_rank limit 100 LOOP

      RETURN QUERY
      SELECT 
      * 
      FROM blast_expand_row(brow);

   END LOOP;
   return;
END;
$$
language plpgsql STABLE;


create or replace function expand_blast_query(integer, smallint, integer)
returns setof blast_fullrecord as
$$
    select $1, $2, $3,
    explode_array(hit_list) AS seq_id_1,
    (explode_array(record_list)).*
    FROM blast_hit_arr
    WHERE br_id=$1
    AND hit_rank=$2
    AND seq_id_0=$3;
$$
language sql stable;


drop function explode_blast_record(blast_record);
create function explode_array_blast_record(br_id int, hit_rank smallint, seq_id_0 integer,hit_list anyarray, record_list anyarray)
returns setof blast_record
as
$$
    select (($1)[s]).bit_score,
           (($1)[s]).e_value,
           (($1)[s]).seq_start_0,
           (($1)[s]).seq_end_0,
           (($1)[s]).seq_start_1,
           (($1)[s]).seq_end_1,
           (($1)[s]).identities,
           (($1)[s]).gaps,
           (($1)[s]).positives,
           (($1)[s]).low_complexity,
           (($1)[s]).coverage
	   from generate_series(1,array_upper($1, 1)) as s;
$$
language sql immutable;

select * from explode_array_blast_record( ARRAY[ '(99.7525, 1.0527e-17 ,0,100,1,99,5,6,0,0.99)'::blast_record ]);

select * from explode_array(array[1,2,3,4]);

select br_id, hit_rank, seq_id_0, 
explode_array(hit_list),
(explode_array(record_list)).bit_score,
(explode_array(record_list)).e_value,
(explode_array(record_list)).bit_score,
 
FROM blast_hit_arr order by br_id, hit_rank, seq_id_0



CREATE TABLE blast_hit_symmetric_tmp (
       seq_id_0         integer NOT NULL,
       seq_id_1         integer NOT NULL,
       bit_score        double precision NOT NULL,
       e_value          double precision NOT NULL,
       PRIMARY KEY (seq_id_0, seq_id_1)
);

SELECT seq_id_0, explode_array(hit_list) as seq_id_1,
explode_array(record_list).*
FROM blast_hit_arr
WHERE br_id=95
AND hit_rank=0
AND seq_id_0=21123433;


;; insert missing inverted scores
INSERT INTO blast_hit_symmetric_tmp
FROM
SELECT bs0.seq_id_0 as seq_id_1, bs0.seq_id_1 as seq_id_0, bs0.bit_score, bs0.e_value, bs1.bit_score, bs1.e_value
FROM blast_hit_symmetric_tmp AS bs0
LEFT JOIN blast_hit_symmetric_tmp AS bs1 ON (bs0.seq_id_0 = bs1.seq_id_1 AND bs0.seq_id_1 = bs1.seq_id_0)
WHERE bs1.bit_score IS NULL

limit 10;

;; Make scores symmetric
UPDATE blast_hit_symmetric_tmp
SELECT bs0.seq_id_0, bs0.seq_id_1, bs0.bit_score, bs0.e_value, bs1.bit_score, bs1.e_value,
GREATEST(bs0.bit_score, bs1.bit_score),
LEAST(bs0.e_value, bs0.e_value)
FROM blast_hit_symmetric_tmp AS bs0
LEFT JOIN blast_hit_symmetric_tmp AS bs1 ON (bs0.seq_id_0 = bs1.seq_id_1 AND bs0.seq_id_1 = bs1.seq_id_0)
WHERE bs0.bit_score != bs1.bit_score

create type unzip_blast_score_ret AS (
       bit_score	double precision[],
       e_value		double precision[]
)

;; try to unzip array of records into multiple arrays
create function unzip_blast_score(anyarray)




CREATE TYPE unzip_blast_score_type AS (
   bit_score double precision[],
   e_value double precision[]
);

;; Migrate from composite type to individual arrays
CREATE OR REPLACE function unzip_blast_score(record_list anyarray) 
RETURNS unzip_blast_score_type AS
$$
DECLARE
   lists unzip_blast_score_type;
   cur_rec blast_score;
BEGIN
   FOR i in array_lower( record_list, 1) .. array_upper(record_list, 1) LOOP
      cur_rec = record_list[i];
      lists.bit_score[i] = cur_rec.bit_score;
      lists.e_value[i] = cur_rec.e_value;
   END LOOP;
   RETURN lists;
END;
$$
language plpgsql STABLE;

INSERT INTO blast_hit_symmetric_arr2
SELECT br_id, seq_id_0, hit_list, 
(unzip_blast_score(score_list)).* 
FROM blast_hit_symmetric_arr;


CREATE TYPE unzip_blast_record_type AS (
       bit_score	 double precision[],
       e_value 		 double precision[],
       seq_start_0	 integer[],
       seq_end_0	 integer[],
       seq_start_1	 integer[],
       seq_end_1	 integer[],
       identities	 integer[],
       gaps		 integer[],
       positives	 integer[],
       low_complexity	 boolean[],
       coverage		 double precision[]
);

;; Migrate from composite type to individual arrays
CREATE OR REPLACE function unzip_blast_record(record_list anyarray) 
RETURNS unzip_blast_record_type AS
$$
DECLARE
   lists unzip_blast_record_type;
   cur_rec blast_record;
BEGIN
   FOR i in array_lower( record_list, 1) .. array_upper(record_list, 1) LOOP
      cur_rec = record_list[i];
      lists.bit_score[i] = cur_rec.bit_score;
      lists.e_value[i] = cur_rec.e_value;
      lists.seq_start_0[i] = cur_rec.seq_start_0;
      lists.seq_end_0[i] = cur_rec.seq_end_0;
      lists.seq_start_1[i] = cur_rec.seq_start_1;
      lists.seq_end_1[i] = cur_rec.seq_end_1;
      lists.identities[i] = cur_rec.identities;
      lists.gaps[i] = cur_rec.gaps;
      lists.positives[i] = cur_rec.positives;
      lists.low_complexity[i] = cur_rec.low_complexity;
      lists.coverage[i] = cur_rec.coverage;
   END LOOP;
   RETURN lists;
END;
$$
language plpgsql STABLE;

INSERT INTO blast_hit_arr2
SELECT br_id, hit_rank, seq_id_0, hit_list, 
(unzip_blast_record(record_list)).* 
FROM blast_hit_arr;
