create or replace package ts as
  type NestedTable is table of binary_double;
  type SeriesCursor is ref cursor;
  
  SeriesDoNotExist exception;
  
--  function GetAvg(args IN NestedTable) return binary_double;
  function GetAvg2(args in NestedTable, startPos pls_integer, windowSize pls_integer) return binary_double;
  
  procedure Init;
  procedure CreateSeries(name varchar, delta number, description varchar);
  procedure Kill();
  procedure AggregateSeriesByAvg(source varchar, newDelta number, dest varchar);
--  procedure MovingAverageOdd(source varchar, windowSize pls_integer, dest varchar);
  procedure MovingAverage(source varchar, windowSize pls_integer, dest varchar);
  procedure Seasonal(source varchar, trend varchar, dest varchar);
end ts;
   
    
create or replace package body ts
as

procedure Init
as
begin
    execute immediate 'create table timeSeries (
          name VARCHAR(100),
          delta number,
          description VARCHAR(100),
          CONSTRAINT pk_timeseries PRIMARY KEY(name)
    )';
end;

procedure CreateSeries(name varchar, delta number, description varchar)
as
begin
   IF name IS NOT NULL THEN
       EXECUTE IMMEDIATE 'create table ' || name || 
       ' (id number constraint ' || name || 'Pk Primary Key, 
          value binary_double)';
       EXECUTE IMMEDIATE 'insert into timeseries values(:name, :delta, :descr)' 
                                                using name, delta, description;
   END IF;
end;

function GetAvg(args IN NestedTable) 
return binary_double
as
res binary_double :=0;
begin
  --i'd like to use the following, but toad tells me that i cant select from collections       
  --select AVG(COLUMN_VALUE) into res from table(args);
    for i in args.first .. args.last loop
       res := res + args(i);
    end loop;
    res := res / args.count();
    return res;
end;

procedure Kill()
as
killerCursor SeriesCursor;
seriesCatalogueName varchar(100):= 'timeSeries';
victimName varchar(100); --TODO: rowtype needed
begin
   if (seriesCatalogueName is not null) then 
      --TODO: Should check if there is a table with such name and specific rowtype
       open killerCursor for 'select name from ' || seriesCatalogueName   ;
       loop 
        fetch killerCursor into victimName;
        exit when killerCursor%notfound;
        EXECUTE IMMEDIATE 'drop table ' || victimName;
       end loop;
       close killerCursor;
       EXECUTE IMMEDIATE 'drop table ' || seriesCatalogueName;  
   end if;
end;

procedure AggregateSeriesByAvg(source varchar, newDelta number, dest varchar)
as
sourceCursor SeriesCursor;
tempTable NestedTable := NestedTable(0);
exitCondition number:= 0;
begin
    IF ((source IS NOT NULL) AND (dest IS NOT NULL)) THEN
    --TODO: Should check if there are tables with such names and specific rowtype
     if not sourceCursor%isOpen then
        open sourceCursor for 'select value from ' || source;
     end if; 
     
     tempTable.extend(newDelta - 1);     
          
     LOOP
      
         for i in 1..newDelta loop --forall and fetch bulk? 
           fetch sourceCursor into tempTable(i); 
           if sourceCursor%NOTFOUND then
            return;
           end if;
         end loop;

        EXECUTE IMMEDIATE 'insert into ' || dest || ' values(:1)' using GetAvg(tempTable);
      END LOOP;
     
     if sourceCursor%isOpen then
       close sourceCursor; 
     end if;
    END IF;
end;

procedure MovingAverageOdd(source varchar, windowSize pls_integer, dest varchar)
as
sourceCursor SeriesCursor;
tempTable NestedTable := NestedTable(0);
i pls_integer := windowSize;
temp binary_double;
begin
    IF ((source IS NOT NULL) AND (dest IS NOT NULL)) THEN
    --TODO: Should check if there are tables with such names and specific rowtype
     if not sourceCursor%isOpen then
         open sourceCursor for 'select value from ' || source;
     end if; 
         
     tempTable.extend(windowSize - 1);
               
     for j in 1..windowSize loop
        fetch sourceCursor into tempTable(j);
        if sourceCursor%notfound then
           return; --TODO: Indicate that there is not enough data;
        end if;
     end loop;
     
     loop
        EXECUTE IMMEDIATE 'insert into ' || dest || ' values(:1)' using GetAvg(tempTable);
        
        fetch sourceCursor into tempTable(MOD(i, windowSize)+1);
        
        if sourceCursor%notfound then
            return; 
        end if;
        
        i := i + 1;
     end loop;
     
     
     if sourceCursor%isOpen then
       close sourceCursor; 
     end if;
     ELSE
        raise SeriesDoNotExist;  
     END IF;
end;

procedure MovingAverage(source varchar, windowSize pls_integer, dest varchar)
as
sourceCursor SeriesCursor;
tempTable NestedTable := NestedTable(0);
i pls_integer := windowSize + 1;
temp binary_double;
begin
    IF ((source IS NOT NULL) AND (dest IS NOT NULL)) THEN
         if (mod(windowSize, 2) = 1) then
             MovingAverageOdd(source, windowSize, dest);
             return;
         end if;    
    
         if not sourceCursor%isOpen then
            open sourceCursor for 'select value from ' || source;
         end if; 
         
         tempTable.extend(windowSize);
                
         for j in 1..windowSize+1 loop
            fetch sourceCursor into tempTable(j);
            if sourceCursor%notfound then
               return; --TODO: Indicate that there is not enough data;
            end if;
         end loop;
     
         loop
            EXECUTE IMMEDIATE 'insert into ' || dest || ' values(:1)' 
                                     using GetAvg2(tempTable, mod(i, windowSize+1)+1, windowSize+1);
        
            fetch sourceCursor into tempTable(MOD(i, windowSize+1)+1);
            --dbms_output.put_line(MOD(i, windowSize+1)+1);
        
            if sourceCursor%notfound then
               return; 
            end if;
        
            i := i + 1;
         end loop;
     
     
        if sourceCursor%isOpen then
           close sourceCursor; 
         end if;
    ELSE
        raise SeriesDoNotExist;        
    END IF;
end;

function GetAvg2(args in NestedTable, startPos pls_integer, windowSize pls_integer) 
return binary_double
as
res binary_double :=0;
weight binary_double := 0;
begin
     for i in startPos - 1 .. startPos + windowSize - 2 loop
        if (i = startPos - 1) or (i = startPos+windowSize - 2) then
            weight := (2*(windowSize-1));
        else
            weight := (windowSize-1); 
        end if;     
        
        res := res + args(mod(i, windowSize)+1) / weight;
    end loop;
    
    return res;
end;

procedure Seasonal(source varchar, trend varchar, dest varchar)
as
sourceCursor SeriesCursor;
trendCursor SeriesCursor;
seasonalIndex NestedTable := NestedTable(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
seasonalNumber NestedTable := NestedTable(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);
i pls_integer := 0;
src binary_double;
trd binary_double;
s binary_double := 0;
begin
  IF ((source IS NOT NULL) AND (dest IS NOT NULL) AND (dest IS NOT NULL)) THEN
     
     open sourceCursor for 'select value from ' || source;
     open trendCursor for 'select value from ' || trend;
     
     loop
        fetch sourceCursor into src;
        fetch trendCursor into trd;
        --dbms_output.put_line(src || ' ' || trd);
        
        exit when (sourceCursor%notfound or trendCursor%notfound);
        
       -- dbms_output.put_line(i mod 12 + 1);
        seasonalIndex(i mod 12 + 1) := seasonalIndex(i mod 12 + 1) + src - trd;
        seasonalNumber(i mod 12 + 1) := seasonalNumber(i mod 12 + 1) + 1;

        i:= i + 1;
     end loop;  
  
     for i in 1..12 loop
        --dbms_output.put_line(seasonalIndex(i) || ' ' || seasonalNumber(i));
        seasonalIndex(i) := seasonalIndex(i) / seasonalNumber(i);
        s := s + seasonalIndex(i);
     end loop;
     
     s:= s / 12;
     
     for i in 1..12 loop
        --dbms_output.put_line(seasonalIndex(i) - s);
        EXECUTE IMMEDIATE 'insert into ' || dest || ' values(:1)' 
                using (seasonalIndex(i) - s);
     end loop;
  ELSE
    raise SeriesDoNotExist;       
  END IF;
end;

end ts;