#TODO: make policy_advisors.status default submitted
#TODO: make view say *_total_unmaterialized and materialized table just have *_total

DROP TABLE advisor_submitted_premium_total_materialized;

CREATE TABLE advisor_submitted_premium_total_materialized AS
  SELECT * FROM advisor_submitted_premium_total;

ALTER TABLE advisor_submitted_premium_total_materialized ADD PRIMARY KEY (advisor_id,channel_id,status,submitted_at);

create language 'plpgsql';

CREATE FUNCTION advisor_submitted_premium_total_refresh_rows(
  a_id integer) RETURNS VOID
  security definer language 'plpgsql' as $$
  begin
    delete
      from advisor_submitted_premium_total_materialized WHERE advisor_id = a_id;
  insert into advisor_submitted_premium_total_materialized
    select * from advisor_submitted_premium_total WHERE advisor_id = a_id;
  end
  $$;

select advisor_submitted_premium_total_refresh_rows(38420);

select * from advisor_submitted_premium_total_materialized where advisor_id = 38420;
update advisor_submitted_premium_total_materialized set total = 5 where advisor_id = 38420;

#advisor_submitted_premium_total_materialized_view == asptmv
CREATE or REPLACE FUNCTION asptmv_policy_advisors_update_trigger() returns trigger
  security definer language 'plpgsql' as $$
  begin
    if old.advisor_id = new.advisor_id then
      perform advisor_submitted_premium_total_refresh_rows(new.advisor_id);
    else
      perform advisor_submitted_premium_total_refresh_rows(new.advisor_id);
      perform advisor_submitted_premium_total_refresh_rows(old.advisor_id);
    end if;
    return null;
  end
  $$;
CREATE or REPLACE FUNCTION asptmv_policy_advisors_delete_trigger() returns trigger
  security definer language 'plpgsql' as $$
  begin
    perform advisor_submitted_premium_total_refresh_rows(old.advisor_id);
    return null;
  end
  $$;
CREATE or REPLACE FUNCTION asptmv_policy_advisors_insert_trigger() returns trigger
  security definer language 'plpgsql' as $$
  begin
    perform advisor_submitted_premium_total_refresh_rows(old.advisor_id);
    return null;
  end
  $$;

CREATE TRIGGER asptmv_policy_advisors_update_trigger after update on policy_advisors
  for each row execute procedure asptmv_policy_advisors_update_trigger();
CREATE TRIGGER asptmv_view_policy_advisors_delete_trigger after delete on policy_advisors
  for each row execute procedure asptmv_policy_advisors_delete_trigger();
CREATE TRIGGER asptmv_policy_advisors_insert_trigger after insert on policy_advisors
  for each row execute procedure asptmv_policy_advisors_insert_trigger();


CREATE or REPLACE FUNCTION asptmv_policies_update_trigger() returns trigger
  security definer language 'plpgsql' as $$
  begin
    if old.id = new.id then
      perform advisor_submitted_premium_total_refresh_rows(advisor_id) from policy_advisors where policy_id = new.id;
    else
      perform advisor_submitted_premium_total_refresh_rows(advisor_id) from policy_advisors where policy_id = new.id;
      perform advisor_submitted_premium_total_refresh_rows(advisor_id) from policy_advisors where policy_id = old.id;
    end if;
    return null;
  end
  $$;
CREATE TRIGGER asptmv_policies_update_trigger after update on policies
  for each row execute procedure asptmv_policies_update_trigger();



CREATE or REPLACE FUNCTION asptmv_submitted_premiums_update_trigger() returns trigger
  security definer language 'plpgsql' as $$
  begin
    if old.id = new.id then
      perform advisor_submitted_premium_total_refresh_rows(advisor_id) from policy_advisors where policy_id = new.policy_id;
    else
      perform advisor_submitted_premium_total_refresh_rows(advisor_id) from policy_advisors where policy_id = new.policy_id;
      perform advisor_submitted_premium_total_refresh_rows(advisor_id) from policy_advisors where policy_id = old.policy_id;
    end if;
    return null;
  end
  $$;
CREATE or REPLACE FUNCTION asptmv_submitted_premiums_delete_trigger() returns trigger
  security definer language 'plpgsql' as $$
  begin
    perform advisor_submitted_premium_total_refresh_rows(advisor_id) from policy_advisors where policy_id = old.policy_id;
    return null;
  end
  $$;
CREATE or REPLACE FUNCTION asptmv_submitted_premiums_insert_trigger() returns trigger
  security definer language 'plpgsql' as $$
  begin
    perform advisor_submitted_premium_total_refresh_rows(advisor_id) from policy_advisors where policy_id = new.policy_id;
    return null;
  end
  $$;

CREATE TRIGGER asptmv_submitted_premiums_update_trigger after update on submitted_premiums
  for each row execute procedure asptmv_submitted_premiums_update_trigger();
CREATE TRIGGER asptmv_submitted_premiums_delete_trigger after delete on submitted_premiums
  for each row execute procedure asptmv_submitted_premiums_delete_trigger();
CREATE TRIGGER asptmv_submitted_premiums_insert_trigger after insert on submitted_premiums
  for each row execute procedure asptmv_submitted_premiums_insert_trigger();


EXPLAIN ANALYZE SELECT SUM(total) AS total FROM advisor_submitted_premium_total_materialized WHERE advisor_id = 38420 AND channel_id = 3
  AND submitted_at >= '2010-01-01' AND submitted_at < '2011-01-01';

  SELECT
    COALESCE(SUM(CASE WHEN submitted_at >= '2010-01-01' AND submitted_at < '2010-02-01' THEN (COALESCE(total,0.0)) ELSE 0.0 END),0.0) AS jan,
    COALESCE(SUM(CASE WHEN submitted_at >= '2010-02-01' AND submitted_at < '2010-03-01' THEN (COALESCE(total,0.0)) ELSE 0.0 END),0.0) AS feb,
    COALESCE(SUM(CASE WHEN submitted_at >= '2010-03-01' AND submitted_at < '2010-04-01' THEN (COALESCE(total,0.0)) ELSE 0.0 END),0.0) AS mar,
    COALESCE(SUM(CASE WHEN submitted_at >= '2010-04-01' AND submitted_at < '2010-05-01' THEN (COALESCE(total,0.0)) ELSE 0.0 END),0.0) AS apr,
    COALESCE(SUM(CASE WHEN submitted_at >= '2010-05-01' AND submitted_at < '2010-06-01' THEN (COALESCE(total,0.0)) ELSE 0.0 END),0.0) AS may,
    COALESCE(SUM(CASE WHEN submitted_at >= '2010-06-01' AND submitted_at < '2010-07-01' THEN (COALESCE(total,0.0)) ELSE 0.0 END),0.0) AS jun,
    COALESCE(SUM(CASE WHEN submitted_at >= '2010-07-01' AND submitted_at < '2010-08-01' THEN (COALESCE(total,0.0)) ELSE 0.0 END),0.0) AS jul,
    COALESCE(SUM(CASE WHEN submitted_at >= '2010-08-01' AND submitted_at < '2010-09-01' THEN (COALESCE(total,0.0)) ELSE 0.0 END),0.0) AS aug,
    COALESCE(SUM(CASE WHEN submitted_at >= '2010-09-01' AND submitted_at < '2010-10-01' THEN (COALESCE(total,0.0)) ELSE 0.0 END),0.0) AS sep,
    COALESCE(SUM(CASE WHEN submitted_at >= '2010-10-01' AND submitted_at < '2010-11-01' THEN (COALESCE(total,0.0)) ELSE 0.0 END),0.0) AS oct,
    COALESCE(SUM(CASE WHEN submitted_at >= '2010-11-01' AND submitted_at < '2010-12-01' THEN (COALESCE(total,0.0)) ELSE 0.0 END),0.0) AS nov,
    COALESCE(SUM(CASE WHEN submitted_at >= '2010-12-01' AND submitted_at < '2011-01-01' THEN (COALESCE(total,0.0)) ELSE 0.0 END),0.0) AS dec
  FROM advisor_submitted_premium_total_materialized
  WHERE advisor_id = 38420 AND channel_id = 3;