pantrydb.sql

--
-- PostgreSQL database dump
--

-- Dumped from database version 14.2
-- Dumped by pg_dump version 14.2

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 xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: pantry; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.pantry (
    id integer NOT NULL,
    address text NOT NULL,
    latitude real NOT NULL,
    longitude real NOT NULL,
    description text NOT NULL,
    host integer NOT NULL,
    overflow_dropoff text NOT NULL,
    size text NOT NULL,
    type text DEFAULT 'pantry'::text NOT NULL,
    deplete_pct_per_day real DEFAULT 0.05 NOT NULL
);


--
-- Name: restock; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.restock (
    id integer NOT NULL,
    submitted_at timestamp without time zone NOT NULL,
    pantry_id integer NOT NULL,
    restock_date date NOT NULL,
    reporter_id integer,
    cleanliness_pct real NOT NULL,
    name text NOT NULL,
    pct_full_on_arrival real NOT NULL,
    pct_full_on_departure real NOT NULL,
    photo_url text
);


--
-- Name: estimated_pantry_status; Type: VIEW; Schema: public; Owner: -
--

CREATE VIEW public.estimated_pantry_status AS
 SELECT pantry.id AS pantry_id,
    last_restock.restock_date AS last_restock_date,
    last_restock.pct_full_on_departure AS last_pct_full,
    ( SELECT avg(restock.pct_full_on_arrival) AS avg
           FROM public.restock
          WHERE ((restock.pantry_id = pantry.id) AND (restock.restock_date > (CURRENT_DATE - '3 mons'::interval)))) AS avg_pct_full,
    GREATEST(COALESCE((last_restock.pct_full_on_departure - (pantry.deplete_pct_per_day * (last_restock.restock_days_ago)::double precision)), (0)::double precision), (0.0)::double precision) AS est_pct_full
   FROM (public.pantry
     LEFT JOIN LATERAL ( SELECT restock.restock_date,
            restock.pct_full_on_departure,
            ((EXTRACT(epoch FROM CURRENT_TIMESTAMP) - EXTRACT(epoch FROM restock.restock_date)) / (((60 * 60) * 24))::numeric) AS restock_days_ago
           FROM public.restock
          WHERE (restock.pantry_id = pantry.id)
          ORDER BY restock.restock_date DESC
         LIMIT 1) last_restock ON ((1 = 1)));


--
-- Name: feature; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.feature (
    id integer NOT NULL,
    name text NOT NULL
);


--
-- Name: food_bank; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.food_bank (
    id integer NOT NULL,
    name text NOT NULL,
    address text NOT NULL,
    description text,
    contact_person_id integer NOT NULL
);


--
-- Name: item; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.item (
    id integer NOT NULL,
    name text NOT NULL
);


--
-- Name: pantry_feature; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.pantry_feature (
    pantry_id integer NOT NULL,
    feature_id integer NOT NULL
);


--
-- Name: person; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.person (
    id integer NOT NULL,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP NOT NULL,
    email text NOT NULL,
    name text
);


--
-- Name: restock_need; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.restock_need (
    restock_id integer NOT NULL,
    item_id integer NOT NULL
);


--
-- Name: route; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.route (
    id integer NOT NULL,
    name text NOT NULL,
    active boolean DEFAULT true NOT NULL,
    food_bank_id integer NOT NULL
);


--
-- Name: route_stop; Type: TABLE; Schema: public; Owner: -
--

CREATE TABLE public.route_stop (
    route_id integer NOT NULL,
    pantry_id integer NOT NULL
);


--
-- Name: feature feature_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.feature
    ADD CONSTRAINT feature_pkey PRIMARY KEY (id);


--
-- Name: food_bank food_bank_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.food_bank
    ADD CONSTRAINT food_bank_pkey PRIMARY KEY (id);


--
-- Name: item item_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.item
    ADD CONSTRAINT item_pkey PRIMARY KEY (id);


--
-- Name: pantry pantry_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.pantry
    ADD CONSTRAINT pantry_pkey PRIMARY KEY (id);


--
-- Name: person person_email_key; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.person
    ADD CONSTRAINT person_email_key UNIQUE (email);


--
-- Name: person person_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.person
    ADD CONSTRAINT person_pkey PRIMARY KEY (id);


--
-- Name: restock restock_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.restock
    ADD CONSTRAINT restock_pkey PRIMARY KEY (id);


--
-- Name: route route_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.route
    ADD CONSTRAINT route_pkey PRIMARY KEY (id);


--
-- Name: food_bank food_bank_contact_person_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.food_bank
    ADD CONSTRAINT food_bank_contact_person_id_fkey FOREIGN KEY (contact_person_id) REFERENCES public.person(id);


--
-- Name: pantry_feature pantry_feature_feature_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.pantry_feature
    ADD CONSTRAINT pantry_feature_feature_id_fkey FOREIGN KEY (feature_id) REFERENCES public.feature(id);


--
-- Name: pantry_feature pantry_feature_pantry_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.pantry_feature
    ADD CONSTRAINT pantry_feature_pantry_id_fkey FOREIGN KEY (pantry_id) REFERENCES public.pantry(id);


--
-- Name: pantry pantry_host_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.pantry
    ADD CONSTRAINT pantry_host_fkey FOREIGN KEY (host) REFERENCES public.person(id);


--
-- Name: restock_need restock_need_item_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.restock_need
    ADD CONSTRAINT restock_need_item_id_fkey FOREIGN KEY (item_id) REFERENCES public.item(id);


--
-- Name: restock_need restock_need_restock_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.restock_need
    ADD CONSTRAINT restock_need_restock_id_fkey FOREIGN KEY (restock_id) REFERENCES public.restock(id);


--
-- Name: restock restock_pantry_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.restock
    ADD CONSTRAINT restock_pantry_id_fkey FOREIGN KEY (pantry_id) REFERENCES public.pantry(id);


--
-- Name: restock restock_reporter_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.restock
    ADD CONSTRAINT restock_reporter_id_fkey FOREIGN KEY (reporter_id) REFERENCES public.person(id);


--
-- Name: route route_food_bank_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.route
    ADD CONSTRAINT route_food_bank_id_fkey FOREIGN KEY (food_bank_id) REFERENCES public.food_bank(id);


--
-- Name: route_stop route_stop_pantry_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.route_stop
    ADD CONSTRAINT route_stop_pantry_id_fkey FOREIGN KEY (pantry_id) REFERENCES public.pantry(id);


--
-- Name: route_stop route_stop_route_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.route_stop
    ADD CONSTRAINT route_stop_route_id_fkey FOREIGN KEY (route_id) REFERENCES public.route(id);


--
-- PostgreSQL database dump complete
--