"""funcs_mma_dfdt


Revision ID: d6c19bdaeac3
Revises: dcaf8627acac
Create Date: 2018-03-28 14:27:50.771233

"""

# revision identifiers, used by Alembic.
revision = 'd6c19bdaeac3'
down_revision = 'dcaf8627acac'
branch_labels = None
depends_on = None

from alembic import op
import sqlalchemy as sa


def upgrade():
    op.execute('''
create or replace function dfdt_stat_array(jsonb) returns jsonb
	immutable
	strict
	language plpgsql
as $$
DECLARE
  rw jsonb;
  ret jsonb;
  prev jsonb;
  val jsonb;
BEGIN
  ret := jsonb_build_array();
  prev := null;
  for rw in select * from jsonb_array_elements($1) LOOP
    if prev is null THEN
      prev := rw;
    elsif (rw->>0)::float - (prev->>0)::float <= 0 THEN
      prev := prev;
    else
      val := ((rw->>1)::float - (prev->>1)::float) / ((rw->>0)::float - (prev->>0)::float);
      ret := ret || jsonb_build_array(jsonb_build_array(rw->0, val));
      prev := rw;
    END IF;
  END LOOP;
  return ret;
END;
$$
''')

    op.execute('''
create or replace function lastn_stat_array(statarray jsonb, n integer) returns jsonb
	immutable
	strict
	language sql
as $$
select
  to_jsonb ((
    select  array_agg(statarray->i order by i)
    from generate_series(
      greatest(jsonb_array_length(statarray) - n, 0),
      least(
        greatest(jsonb_array_length(statarray) - n, 0) + n,
        jsonb_array_length(statarray)
      ) - 1) gs(i)));
$$
''')

    op.execute('''
create or replace function mma_stat_array(jsonb) returns jsonb
	immutable
	strict
	language plpgsql
as $$
declare
  rw jsonb;
  mma float[];
BEGIN
  FOR rw IN SELECT * FROM jsonb_array_elements($1) LOOP
    if mma is null THEN
      mma = array[(rw->>1)::float, (rw->>1)::float, (rw->>1)::float];
    ELSE
      mma = array[least(mma[1], (rw->>1)::float), greatest(mma[2], (rw->>1)::float), mma[3]+(rw->>1)::float];
    END IF;
  END LOOP;

  return jsonb_build_array(
    mma[1],
    mma[2],
    mma[3] / jsonb_array_length($1)::float
  );
END;
$$
''')


def downgrade():
    op.execute('drop function mma_stat_array(jsonb)')
    op.execute('drop function lastn_stat_array(statarray jsonb, n integer)')
    op.execute('drop function dfdt_stat_array(jsonb)')