--
-- PostgreSQL database dump
--

-- Dumped from database version 10.4
-- Dumped by pg_dump version 10.4

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: 
--

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;


--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';


--
-- Name: plv8; Type: EXTENSION; Schema: -; Owner: 
--

CREATE EXTENSION IF NOT EXISTS plv8 WITH SCHEMA pg_catalog;


--
-- Name: EXTENSION plv8; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON EXTENSION plv8 IS 'PL/JavaScript (v8) trusted procedural language';


--
-- Name: histogram; Type: TYPE; Schema: public; Owner: postgres
--

CREATE TYPE public.histogram AS (
	keys double precision[],
	"values" integer[],
	total bigint
);


ALTER TYPE public.histogram OWNER TO postgres;

--
-- Name: fast_histogram_merge(plv8_float8array, plv8_int4array, plv8_float8array, plv8_int4array); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.fast_histogram_merge(x_keys plv8_float8array, x_values plv8_int4array, y_keys plv8_float8array, y_values plv8_int4array) RETURNS public.histogram
    LANGUAGE plv8 IMMUTABLE STRICT
    AS $$

var x_length = x_keys.length;
var y_length = y_keys.length;

var new_keys = [];
var new_values = [];
var new_total = 0;

var x = 0;
var y = 0;
var n = 0;

while (x !== x_length && y !== y_length) {
    if (x_keys[x] < y_keys[y]) {
        new_keys[n] = x_keys[x];
 new_values[n] = x_values[x];
 new_total += x_values[x];

        x += 1;
 n += 1;
    } else if (x_keys[x] > y_keys[y]) {
        new_keys[n] = y_keys[y];
 new_values[n] = y_values[y];
 new_total += y_values[y];

        y += 1;
        n += 1;
    } else {
        new_keys[n] = x_keys[x];
        var both_values_combined = x_values[x] + y_values[y];
 new_values[n] = both_values_combined;
 new_total += both_values_combined;

        x += 1;
 y += 1;
        n += 1;
    }
}

if (x === x_length && y !== y_length) {
    while (y != y_length) {
        new_keys[n] = y_keys[y];
 new_values[n] = y_values[y];
 new_total += y_values[y];

        y += 1;
        n += 1;
    }
}

if (y === y_length && x !== x_length) {
    while (x != x_length) {
        new_keys[n] = x_keys[x];
 new_values[n] = x_values[x];
 new_total += x_values[x];

        x += 1;
        n += 1;
    }
}

return {"keys": new_keys, "values": new_values, "total": new_total};
$$;


ALTER FUNCTION public.fast_histogram_merge(x_keys plv8_float8array, x_values plv8_int4array, y_keys plv8_float8array, y_values plv8_int4array) OWNER TO postgres;

--
-- Name: histogram_merge(public.histogram, public.histogram); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.histogram_merge(existing_hist public.histogram, new_hist public.histogram) RETURNS public.histogram
    LANGUAGE plpgsql IMMUTABLE STRICT
    AS $$
BEGIN
RETURN fast_histogram_merge(existing_hist.keys, existing_hist.values, new_hist.keys, new_hist.values);
END;
$$;


ALTER FUNCTION public.histogram_merge(existing_hist public.histogram, new_hist public.histogram) OWNER TO postgres;

--
-- Name: histogram_agg(public.histogram); Type: AGGREGATE; Schema: public; Owner: postgres
--

CREATE AGGREGATE public.histogram_agg(public.histogram) (
    SFUNC = public.histogram_merge,
    STYPE = public.histogram
);


ALTER AGGREGATE public.histogram_agg(public.histogram) OWNER TO postgres;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: client_purchaseorder; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.client_purchaseorder (
    id integer NOT NULL,
    item_number character varying(50),
    item_number_stripped character varying(50),
    item_description character varying(250),
    vendor_catalog_number character varying(50),
    manufacturer_catalog_number character varying(50),
    po_number character varying(50),
    po_line_number integer,
    po_date date,
    contract_number character varying(45),
    contract_name character varying(250),
    contract_type character varying(45),
    contract_start_date date,
    contract_end_date date,
    resource_id integer,
    uom_id integer,
    department_id integer,
    units numeric(13,4),
    extended_cost numeric(13,2),
    extended_cost_original numeric(13,2),
    po_line_status character varying(15),
    non_device boolean,
    job_id integer,
    facility_alias_id integer,
    category_id integer,
    price_per_each numeric(13,4),
    price_per_each_original numeric(13,4),
    deleted timestamp with time zone,
    exclude_from_benchmarking boolean,
    updated_by_id integer,
    updated timestamp with time zone,
    created timestamp with time zone,
    verified timestamp with time zone,
    verified_by_id integer
);


ALTER TABLE public.client_purchaseorder OWNER TO postgres;

--
-- Name: client_purchaseorder_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public.client_purchaseorder_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.client_purchaseorder_id_seq OWNER TO postgres;

--
-- Name: client_purchaseorder_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.client_purchaseorder_id_seq OWNED BY public.client_purchaseorder.id;


--
-- Name: po_poterm; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.po_poterm (
    id integer NOT NULL,
    created character varying(50),
    updated character varying(50),
    created_by_id integer,
    updated_by_id integer,
    product_id integer,
    brand_id integer,
    brand character varying(300),
    description character varying(480),
    catalog character varying(300),
    catalog_stripped character varying(300),
    group_type_id integer,
    group_type2_id integer,
    group_type3_id integer,
    group_type4_id integer,
    deleted timestamp without time zone,
    deleted_by_id integer,
    standard_catalog_id integer,
    source character varying(120),
    is_primary boolean
);


ALTER TABLE public.po_poterm OWNER TO postgres;

--
-- Name: po_poterm_po_uniquepurchasecatalog; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.po_poterm_po_uniquepurchasecatalog (
    id integer NOT NULL,
    unique_purchase_catalog_id integer,
    poterm_id integer
);


ALTER TABLE public.po_poterm_po_uniquepurchasecatalog OWNER TO postgres;

--
-- Name: po_uniquepurchasecataloguom; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.po_uniquepurchasecataloguom (
    id integer NOT NULL,
    unit_of_measure character varying(12),
    eaches_per_uom integer,
    unique_purchase_catalog_id integer,
    verified_by_id integer,
    verified timestamp with time zone
);


ALTER TABLE public.po_uniquepurchasecataloguom OWNER TO postgres;

--
-- Name: mat_view_by_catalog; Type: MATERIALIZED VIEW; Schema: public; Owner: mchhatani
--

CREATE MATERIALIZED VIEW public.mat_view_by_catalog AS
 WITH unique_po_term AS (
         SELECT po_poterm_po_uniquepurchasecatalog.unique_purchase_catalog_id,
            min(po_poterm_po_uniquepurchasecatalog.poterm_id) AS poterm_id
           FROM public.po_poterm_po_uniquepurchasecatalog
          GROUP BY po_poterm_po_uniquepurchasecatalog.unique_purchase_catalog_id
        ), price_per_each_count AS (
         SELECT po.price_per_each,
            sum((po.units * (uom.eaches_per_uom)::numeric)) AS ppe_count,
            min(po.po_date) AS min_po_date,
            max(po.po_date) AS max_po_date,
            po.facility_alias_id,
            pt.group_type_id,
            pt.product_id,
            pt.catalog_stripped,
            (date_trunc('month'::text, (po.po_date)::timestamp with time zone))::date AS po_date_month
           FROM (((public.client_purchaseorder po
             JOIN public.po_uniquepurchasecataloguom uom ON ((po.uom_id = uom.id)))
             JOIN unique_po_term m2m ON ((m2m.unique_purchase_catalog_id = uom.unique_purchase_catalog_id)))
             JOIN public.po_poterm pt ON ((pt.id = m2m.poterm_id)))
          WHERE ((po.price_per_each IS NOT NULL) AND (po.units IS NOT NULL) AND (uom.eaches_per_uom IS NOT NULL) AND (po.po_date <= ((date_trunc('month'::text, now()))::date - 1)) AND (po.deleted IS NULL) AND (po.price_per_each > (0)::numeric) AND (po.verified IS NOT NULL))
          GROUP BY po.facility_alias_id, pt.group_type_id, po.price_per_each, pt.product_id, pt.catalog_stripped, ((date_trunc('month'::text, (po.po_date)::timestamp with time zone))::date)
        )
 SELECT price_per_each_count.facility_alias_id,
    price_per_each_count.group_type_id,
    price_per_each_count.product_id,
    price_per_each_count.catalog_stripped,
    price_per_each_count.po_date_month,
    ROW((array_agg(price_per_each_count.price_per_each ORDER BY price_per_each_count.price_per_each))::double precision[], (array_agg(price_per_each_count.ppe_count ORDER BY price_per_each_count.price_per_each))::integer[], (sum(price_per_each_count.ppe_count))::bigint)::public.histogram AS histogram,
    sum((price_per_each_count.ppe_count * price_per_each_count.price_per_each)) AS total_spend,
    min(price_per_each_count.price_per_each) AS min_price,
    max(price_per_each_count.price_per_each) AS max_price,
    min(price_per_each_count.min_po_date) AS min_po_date,
    max(price_per_each_count.max_po_date) AS max_po_date,
    (array_agg(price_per_each_count.price_per_each ORDER BY price_per_each_count.max_po_date))[1] AS most_recent_price,
    sum(price_per_each_count.ppe_count) AS total_eaches
   FROM price_per_each_count
  GROUP BY price_per_each_count.facility_alias_id, price_per_each_count.group_type_id, price_per_each_count.product_id, price_per_each_count.catalog_stripped, price_per_each_count.po_date_month
  WITH NO DATA;


ALTER TABLE public.mat_view_by_catalog OWNER TO mchhatani;

--
-- Name: mat_view_by_product; Type: MATERIALIZED VIEW; Schema: public; Owner: mchhatani
--

CREATE MATERIALIZED VIEW public.mat_view_by_product AS
 SELECT mat_view_by_catalog.facility_alias_id,
    mat_view_by_catalog.group_type_id,
    mat_view_by_catalog.product_id,
    mat_view_by_catalog.po_date_month,
    public.histogram_agg(mat_view_by_catalog.histogram) AS histogram,
    sum(mat_view_by_catalog.total_spend) AS total_spend,
    min(mat_view_by_catalog.min_price) AS min_price,
    max(mat_view_by_catalog.max_price) AS max_price,
    min(mat_view_by_catalog.min_po_date) AS min_po_date,
    max(mat_view_by_catalog.max_po_date) AS max_po_date,
    (array_agg(mat_view_by_catalog.most_recent_price ORDER BY mat_view_by_catalog.max_po_date))[1] AS most_recent_price,
    sum(mat_view_by_catalog.total_eaches) AS total_eaches
   FROM public.mat_view_by_catalog
  GROUP BY mat_view_by_catalog.facility_alias_id, mat_view_by_catalog.group_type_id, mat_view_by_catalog.product_id, mat_view_by_catalog.po_date_month
  WITH NO DATA;


ALTER TABLE public.mat_view_by_product OWNER TO mchhatani;

--
-- Name: po_poterm_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public.po_poterm_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.po_poterm_id_seq OWNER TO postgres;

--
-- Name: po_poterm_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.po_poterm_id_seq OWNED BY public.po_poterm.id;


--
-- Name: po_poterm_po_uniquepurchasecatalog_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public.po_poterm_po_uniquepurchasecatalog_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.po_poterm_po_uniquepurchasecatalog_id_seq OWNER TO postgres;

--
-- Name: po_poterm_po_uniquepurchasecatalog_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.po_poterm_po_uniquepurchasecatalog_id_seq OWNED BY public.po_poterm_po_uniquepurchasecatalog.id;


--
-- Name: po_uniquepurchasecatalog; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.po_uniquepurchasecatalog (
    id integer NOT NULL,
    manufacturer_name character varying(125),
    manufacturer_catalog_number_stripped character varying(50),
    vendor_name character varying(125),
    vendor_catalog_number_stripped character varying(50)
);


ALTER TABLE public.po_uniquepurchasecatalog OWNER TO postgres;

--
-- Name: po_uniquepurchasecatalog_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public.po_uniquepurchasecatalog_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.po_uniquepurchasecatalog_id_seq OWNER TO postgres;

--
-- Name: po_uniquepurchasecatalog_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.po_uniquepurchasecatalog_id_seq OWNED BY public.po_uniquepurchasecatalog.id;


--
-- Name: po_uniquepurchasecataloguom_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE public.po_uniquepurchasecataloguom_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.po_uniquepurchasecataloguom_id_seq OWNER TO postgres;

--
-- Name: po_uniquepurchasecataloguom_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE public.po_uniquepurchasecataloguom_id_seq OWNED BY public.po_uniquepurchasecataloguom.id;


--
-- Name: client_purchaseorder id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.client_purchaseorder ALTER COLUMN id SET DEFAULT nextval('public.client_purchaseorder_id_seq'::regclass);


--
-- Name: po_poterm id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.po_poterm ALTER COLUMN id SET DEFAULT nextval('public.po_poterm_id_seq'::regclass);


--
-- Name: po_poterm_po_uniquepurchasecatalog id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.po_poterm_po_uniquepurchasecatalog ALTER COLUMN id SET DEFAULT nextval('public.po_poterm_po_uniquepurchasecatalog_id_seq'::regclass);


--
-- Name: po_uniquepurchasecatalog id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.po_uniquepurchasecatalog ALTER COLUMN id SET DEFAULT nextval('public.po_uniquepurchasecatalog_id_seq'::regclass);


--
-- Name: po_uniquepurchasecataloguom id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.po_uniquepurchasecataloguom ALTER COLUMN id SET DEFAULT nextval('public.po_uniquepurchasecataloguom_id_seq'::regclass);


--
-- Name: client_purchaseorder client_purchaseorder_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.client_purchaseorder
    ADD CONSTRAINT client_purchaseorder_pkey PRIMARY KEY (id);


--
-- Name: po_poterm po_poterm_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.po_poterm
    ADD CONSTRAINT po_poterm_pkey PRIMARY KEY (id);


--
-- Name: po_poterm_po_uniquepurchasecatalog po_poterm_po_uniquepurchasecatalog_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.po_poterm_po_uniquepurchasecatalog
    ADD CONSTRAINT po_poterm_po_uniquepurchasecatalog_pkey PRIMARY KEY (id);


--
-- Name: po_uniquepurchasecatalog po_uniquepurchasecatalog_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.po_uniquepurchasecatalog
    ADD CONSTRAINT po_uniquepurchasecatalog_pkey PRIMARY KEY (id);


--
-- Name: po_uniquepurchasecatalog po_uniquepurchasecatalog_vendor_catalog_number_stripped_man_key; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.po_uniquepurchasecatalog
    ADD CONSTRAINT po_uniquepurchasecatalog_vendor_catalog_number_stripped_man_key UNIQUE (vendor_catalog_number_stripped, manufacturer_catalog_number_stripped, manufacturer_name, vendor_name);


--
-- Name: po_uniquepurchasecataloguom po_uniquepurchasecataloguom_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.po_uniquepurchasecataloguom
    ADD CONSTRAINT po_uniquepurchasecataloguom_pkey PRIMARY KEY (id);


--
-- Name: client_purchaseorder_facility_alias_id_po_date_idx; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX client_purchaseorder_facility_alias_id_po_date_idx ON public.client_purchaseorder USING btree (facility_alias_id, po_date) WHERE (deleted IS NULL);


--
-- Name: client_purchaseorder_uom_id_idx; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX client_purchaseorder_uom_id_idx ON public.client_purchaseorder USING btree (uom_id);


--
-- Name: client_purchaseorder_updated_idx; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX client_purchaseorder_updated_idx ON public.client_purchaseorder USING brin (updated);


--
-- Name: po_poterm_group_type2_id_idx; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX po_poterm_group_type2_id_idx ON public.po_poterm USING btree (group_type2_id);


--
-- Name: po_poterm_group_type3_id_idx; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX po_poterm_group_type3_id_idx ON public.po_poterm USING btree (group_type3_id);


--
-- Name: po_poterm_group_type4_id_idx; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX po_poterm_group_type4_id_idx ON public.po_poterm USING btree (group_type4_id);


--
-- Name: po_poterm_group_type_id_idx; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX po_poterm_group_type_id_idx ON public.po_poterm USING btree (group_type_id);


--
-- Name: po_poterm_lower_idx; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX po_poterm_lower_idx ON public.po_poterm USING btree (lower((brand)::text));


--
-- Name: po_poterm_lower_idx1; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX po_poterm_lower_idx1 ON public.po_poterm USING btree (lower((catalog_stripped)::text));


--
-- Name: po_poterm_po_uniquepurchasecatal_unique_purchase_catalog_id_idx; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX po_poterm_po_uniquepurchasecatal_unique_purchase_catalog_id_idx ON public.po_poterm_po_uniquepurchasecatalog USING btree (unique_purchase_catalog_id);


--
-- Name: po_poterm_po_uniquepurchasecatalog_poterm_id_idx; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX po_poterm_po_uniquepurchasecatalog_poterm_id_idx ON public.po_poterm_po_uniquepurchasecatalog USING btree (poterm_id);


--
-- Name: po_poterm_product_id_idx; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX po_poterm_product_id_idx ON public.po_poterm USING btree (product_id);


--
-- Name: po_poterm_product_id_lower_lower1_idx; Type: INDEX; Schema: public; Owner: postgres
--

CREATE UNIQUE INDEX po_poterm_product_id_lower_lower1_idx ON public.po_poterm USING btree (product_id, lower((catalog)::text), lower((brand)::text));


--
-- Name: po_poterm_standard_catalog_id_idx; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX po_poterm_standard_catalog_id_idx ON public.po_poterm USING btree (standard_catalog_id);


--
-- Name: po_uniquepurchasecatalog_lower_idx; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX po_uniquepurchasecatalog_lower_idx ON public.po_uniquepurchasecatalog USING btree (lower((manufacturer_catalog_number_stripped)::text));


--
-- Name: po_uniquepurchasecatalog_lower_idx1; Type: INDEX; Schema: public; Owner: postgres
--

CREATE INDEX po_uniquepurchasecatalog_lower_idx1 ON public.po_uniquepurchasecatalog USING btree (lower((vendor_catalog_number_stripped)::text));


--
-- Name: po_uniquepurchasecataloguom_unique_purchase_catalog_id_unit_idx; Type: INDEX; Schema: public; Owner: postgres
--

CREATE UNIQUE INDEX po_uniquepurchasecataloguom_unique_purchase_catalog_id_unit_idx ON public.po_uniquepurchasecataloguom USING btree (unique_purchase_catalog_id, unit_of_measure, COALESCE(eaches_per_uom, '-200000000'::integer));


--
-- PostgreSQL database dump complete
--

