BEGIN;
SET search_path = public, pg_catalog;

DROP VIEW IF EXISTS public.time_for_10000;
DROP VIEW IF EXISTS public.namespace_usage;
DROP VIEW IF EXISTS public.growth_estimate;
DROP VIEW IF EXISTS public.by_month;

CREATE VIEW by_month AS
 WITH sums_by_month AS (
         SELECT to_char(plans.entered_on, 'YYYY-MM'::text) AS month,
            count(*) AS "all",
            count(NULLIF(plans.is_public, false)) AS public,
            count(NULLIF(plans.is_public, true)) AS private,
            count(NULLIF(plans.is_anonymized, false)) AS anonymized,
            count(NULLIF(plans.is_deleted, false)) AS deleted,
            (((count(*))::double precision / (count(DISTINCT (plans.entered_on)::date))::double precision))::numeric(10,1) AS avg_per_day
           FROM plans
          GROUP BY (to_char(plans.entered_on, 'YYYY-MM'::text))
        )
 SELECT sums_by_month.month,
    sums_by_month."all",
    sums_by_month.public,
    sums_by_month.private,
    sums_by_month.anonymized,
    sums_by_month.avg_per_day,
    sum(sums_by_month."all") OVER (ORDER BY sums_by_month.month) AS total,
    sum(sums_by_month.public) OVER (ORDER BY sums_by_month.month) AS total_public,
    sum(sums_by_month.private) OVER (ORDER BY sums_by_month.month) AS total_private,
    sum(sums_by_month.anonymized) OVER (ORDER BY sums_by_month.month) AS total_anonymized,
    sum(sums_by_month.deleted) OVER (ORDER BY sums_by_month.month) AS total_deleted
   FROM sums_by_month
  ORDER BY sums_by_month.month;

CREATE VIEW growth_estimate AS
 WITH base_info AS (
         SELECT count(*) AS all_count,
            count(*) FILTER (WHERE (plans.entered_on > (now() - '7 days'::interval))) AS count_7d,
            count(*) FILTER (WHERE (plans.entered_on > (now() - '30 days'::interval))) AS count_30d,
            count(*) FILTER (WHERE (plans.entered_on > (now() - '365 days'::interval))) AS count_365d,
            floor(log((count(*))::double precision)) AS all_count_log
           FROM plans
        ), with_targets AS (
         SELECT base_info.all_count,
            base_info.count_7d,
            base_info.count_30d,
            base_info.count_365d,
            base_info.all_count_log,
            ((10)::double precision ^ ((1)::double precision + base_info.all_count_log)) AS target_high,
            (((1)::double precision + floor(((base_info.all_count)::double precision / ((10)::double precision ^ base_info.all_count_log)))) * ((10)::double precision ^ base_info.all_count_log)) AS target_low
           FROM base_info
        )
 SELECT with_targets.target_low AS target,
    date_part('day'::text, (((with_targets.target_low - (with_targets.all_count)::double precision) / (with_targets.count_7d)::double precision) * '7 days'::interval)) AS estimate_7d,
    ((now() + (((with_targets.target_low - (with_targets.all_count)::double precision) / (with_targets.count_7d)::double precision) * '7 days'::interval)))::timestamp(0) with time zone AS eta_7d,
    date_part('day'::text, (((with_targets.target_low - (with_targets.all_count)::double precision) / (with_targets.count_30d)::double precision) * '30 days'::interval)) AS estimate_30d,
    ((now() + (((with_targets.target_low - (with_targets.all_count)::double precision) / (with_targets.count_30d)::double precision) * '30 days'::interval)))::timestamp(0) with time zone AS eta_30d,
    date_part('day'::text, (((with_targets.target_low - (with_targets.all_count)::double precision) / (with_targets.count_365d)::double precision) * '365 days'::interval)) AS estimate_365d,
    ((now() + (((with_targets.target_low - (with_targets.all_count)::double precision) / (with_targets.count_365d)::double precision) * '365 days'::interval)))::timestamp(0) with time zone AS eta_365d
   FROM with_targets
UNION ALL
 SELECT with_targets.target_high AS target,
    date_part('day'::text, (((with_targets.target_high - (with_targets.all_count)::double precision) / (with_targets.count_7d)::double precision) * '7 days'::interval)) AS estimate_7d,
    ((now() + (((with_targets.target_high - (with_targets.all_count)::double precision) / (with_targets.count_7d)::double precision) * '7 days'::interval)))::timestamp(0) with time zone AS eta_7d,
    date_part('day'::text, (((with_targets.target_high - (with_targets.all_count)::double precision) / (with_targets.count_30d)::double precision) * '30 days'::interval)) AS estimate_30d,
    ((now() + (((with_targets.target_high - (with_targets.all_count)::double precision) / (with_targets.count_30d)::double precision) * '30 days'::interval)))::timestamp(0) with time zone AS eta_30d,
    date_part('day'::text, (((with_targets.target_high - (with_targets.all_count)::double precision) / (with_targets.count_365d)::double precision) * '365 days'::interval)) AS estimate_365d,
    ((now() + (((with_targets.target_high - (with_targets.all_count)::double precision) / (with_targets.count_365d)::double precision) * '365 days'::interval)))::timestamp(0) with time zone AS eta_365d
   FROM with_targets;

CREATE VIEW namespace_usage AS
 SELECT length(plans.id) AS length,
    count(*) AS count,
    ((((100 * count(*)))::double precision / ((62)::double precision ^ (length(plans.id))::double precision)))::numeric(12,9) AS filled_percent,
    ((62)::double precision ^ (length(plans.id))::double precision) AS namespace_size
   FROM plans
  GROUP BY (length(plans.id))
  ORDER BY (length(plans.id));

CREATE VIEW time_for_10000 AS
 WITH numbered AS (
         SELECT plans.entered_on,
            row_number() OVER (ORDER BY plans.entered_on) - 1 AS number,
            ( (row_number() OVER (ORDER BY plans.entered_on) - 1 ) / 10000) AS range_number
           FROM plans
        ), times AS (
         SELECT format('%s - %s'::text, min(numbered.number) + 1, max(numbered.number) + 1) AS range,
            (max(numbered.entered_on) - min(numbered.entered_on)) AS time_for_10000,
            count(*) as plan_count,
            numbered.range_number
           FROM numbered
          GROUP BY numbered.range_number
        )
 SELECT times.range,
    times.time_for_10000,
    times.plan_count,
    (((100)::double precision * (date_part('epoch'::text, times.time_for_10000) / date_part('epoch'::text, lag(times.time_for_10000) OVER (ORDER BY times.range_number)))))::numeric(5,2) AS percent_of_previous_10k
   FROM times
  ORDER BY times.range_number;
COMMIT;