--
--


SELECT pg_catalog.set_config('search_path', '', false);

--
-- Name: lag_return; Type: DATABASE; Schema: -; Owner: -
--

CREATE DATABASE lag_return WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE = 'C';


\connect lag_return

SELECT pg_catalog.set_config('search_path', '', false);


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

CREATE TABLE public.history_10043 (
    id bigint,
    peer_id bigint NOT NULL,
    calculation_time timestamp(6) with time zone DEFAULT now() NOT NULL,
    graph_coordinates jsonb
)
PARTITION BY RANGE (calculation_time);



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

CREATE TABLE public.history_10043_2019 (
    id bigint,
    peer_id bigint NOT NULL,
    calculation_time timestamp(6) with time zone DEFAULT now() NOT NULL,
    graph_coordinates jsonb
);


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

CREATE TABLE public.history_10043_2020 (
    id bigint,
    peer_id bigint NOT NULL,
    calculation_time timestamp(6) with time zone DEFAULT now() NOT NULL,
    graph_coordinates jsonb
);


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

CREATE TABLE public.history_10043_2021 (
    id bigint,
    peer_id bigint NOT NULL,
    calculation_time timestamp(6) with time zone DEFAULT now() NOT NULL,
    graph_coordinates jsonb
);


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

CREATE TABLE public.history_10043_2022 (
    id bigint,
    peer_id bigint NOT NULL,
    calculation_time timestamp(6) with time zone DEFAULT now() NOT NULL,
    graph_coordinates jsonb
);


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

CREATE TABLE public.history_full (
    id bigint,
    peer_id bigint NOT NULL,
    calculation_time timestamp(6) with time zone DEFAULT now() NOT NULL,
    graph_coordinates jsonb
)
PARTITION BY RANGE (calculation_time);


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

CREATE TABLE public.history_full_2019 (
    id bigint,
    peer_id bigint NOT NULL,
    calculation_time timestamp(6) with time zone DEFAULT now() NOT NULL,
    graph_coordinates jsonb
);


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

CREATE TABLE public.history_full_2020 (
    id bigint,
    peer_id bigint NOT NULL,
    calculation_time timestamp(6) with time zone DEFAULT now() NOT NULL,
    graph_coordinates jsonb
);


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

CREATE TABLE public.history_full_2021 (
    id bigint,
    peer_id bigint NOT NULL,
    calculation_time timestamp(6) with time zone DEFAULT now() NOT NULL,
    graph_coordinates jsonb
);


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

CREATE TABLE public.history_full_2022 (
    id bigint,
    peer_id bigint NOT NULL,
    calculation_time timestamp(6) with time zone DEFAULT now() NOT NULL,
    graph_coordinates jsonb
);


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

CREATE VIEW public.history_latest_results AS
 SELECT DISTINCT ON (history_full.peer_id) history_full.id,
    history_full.peer_id,
    history_full.calculation_time,
    history_full.graph_coordinates
   FROM public.history_full
  ORDER BY history_full.peer_id, history_full.calculation_time DESC;


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

CREATE VIEW public.history_latest_results_10043 AS
 SELECT DISTINCT ON (history_10043.peer_id) history_10043.id,
    history_10043.peer_id,
    history_10043.calculation_time,
    history_10043.graph_coordinates
   FROM public.history_10043
  ORDER BY history_10043.peer_id, history_10043.calculation_time DESC;


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

CREATE VIEW public.points_timeserie_10043 AS
 SELECT history_latest_results_10043.peer_id,
    graph.date,
    graph.agg_points AS points_count,
    lag(graph.agg_points, 1, (0)::numeric) OVER (ORDER BY history_latest_results_10043.peer_id, graph.date) AS previous_points_count
   FROM public.history_latest_results_10043,
    LATERAL jsonb_to_recordset(history_latest_results_10043.graph_coordinates) graph(date timestamp with time zone, agg_points numeric);


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

CREATE VIEW public.points_timeserie_full AS
 SELECT history_latest_results.peer_id,
    graph.date,
    graph.agg_points AS points_count,
    lag(graph.agg_points, 1, (0)::numeric) OVER (ORDER BY history_latest_results.peer_id, graph.date) AS previous_points_count
   FROM public.history_latest_results,
    LATERAL jsonb_to_recordset(history_latest_results.graph_coordinates) graph(date timestamp with time zone, agg_points numeric);


--
-- Name: history_10043_2019; Type: TABLE ATTACH; Schema: public; Owner: -
--

ALTER TABLE ONLY public.history_10043 ATTACH PARTITION public.history_10043_2019 FOR VALUES FROM ('2019-01-01 00:00:00+01') TO ('2020-01-01 00:00:00+01');


--
-- Name: history_10043_2020; Type: TABLE ATTACH; Schema: public; Owner: -
--

ALTER TABLE ONLY public.history_10043 ATTACH PARTITION public.history_10043_2020 FOR VALUES FROM ('2020-01-01 00:00:00+01') TO ('2021-01-01 00:00:00+01');


--
-- Name: history_10043_2021; Type: TABLE ATTACH; Schema: public; Owner: -
--

ALTER TABLE ONLY public.history_10043 ATTACH PARTITION public.history_10043_2021 FOR VALUES FROM ('2021-01-01 00:00:00+01') TO ('2022-01-01 00:00:00+01');


--
-- Name: history_10043_2022; Type: TABLE ATTACH; Schema: public; Owner: -
--

ALTER TABLE ONLY public.history_10043 ATTACH PARTITION public.history_10043_2022 FOR VALUES FROM ('2022-01-01 00:00:00+01') TO ('2023-01-01 00:00:00+01');


--
-- Name: history_full_2019; Type: TABLE ATTACH; Schema: public; Owner: -
--

ALTER TABLE ONLY public.history_full ATTACH PARTITION public.history_full_2019 FOR VALUES FROM ('2019-01-01 00:00:00+01') TO ('2020-01-01 00:00:00+01');


--
-- Name: history_full_2020; Type: TABLE ATTACH; Schema: public; Owner: -
--

ALTER TABLE ONLY public.history_full ATTACH PARTITION public.history_full_2020 FOR VALUES FROM ('2020-01-01 00:00:00+01') TO ('2021-01-01 00:00:00+01');


--
-- Name: history_full_2021; Type: TABLE ATTACH; Schema: public; Owner: -
--

ALTER TABLE ONLY public.history_full ATTACH PARTITION public.history_full_2021 FOR VALUES FROM ('2021-01-01 00:00:00+01') TO ('2022-01-01 00:00:00+01');


--
-- Name: history_full_2022; Type: TABLE ATTACH; Schema: public; Owner: -
--

ALTER TABLE ONLY public.history_full ATTACH PARTITION public.history_full_2022 FOR VALUES FROM ('2022-01-01 00:00:00+01') TO ('2023-01-01 00:00:00+01');


--
-- Name: history_10043 history_10043_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.history_10043
    ADD CONSTRAINT history_10043_pkey PRIMARY KEY (peer_id, calculation_time);


--
-- Name: history_10043_2019 history_10043_2019_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.history_10043_2019
    ADD CONSTRAINT history_10043_2019_pkey PRIMARY KEY (peer_id, calculation_time);


--
-- Name: history_10043_2020 history_10043_2020_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.history_10043_2020
    ADD CONSTRAINT history_10043_2020_pkey PRIMARY KEY (peer_id, calculation_time);


--
-- Name: history_10043_2021 history_10043_2021_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.history_10043_2021
    ADD CONSTRAINT history_10043_2021_pkey PRIMARY KEY (peer_id, calculation_time);


--
-- Name: history_10043_2022 history_10043_2022_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.history_10043_2022
    ADD CONSTRAINT history_10043_2022_pkey PRIMARY KEY (peer_id, calculation_time);


--
-- Name: history_full history_full_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.history_full
    ADD CONSTRAINT history_full_pkey PRIMARY KEY (peer_id, calculation_time);


--
-- Name: history_full_2019 history_full_2019_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.history_full_2019
    ADD CONSTRAINT history_full_2019_pkey PRIMARY KEY (peer_id, calculation_time);


--
-- Name: history_full_2020 history_full_2020_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.history_full_2020
    ADD CONSTRAINT history_full_2020_pkey PRIMARY KEY (peer_id, calculation_time);


--
-- Name: history_full_2021 history_full_2021_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.history_full_2021
    ADD CONSTRAINT history_full_2021_pkey PRIMARY KEY (peer_id, calculation_time);


--
-- Name: history_full_2022 history_full_2022_pkey; Type: CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY public.history_full_2022
    ADD CONSTRAINT history_full_2022_pkey PRIMARY KEY (peer_id, calculation_time);


--
-- Name: history_10043_2019_pkey; Type: INDEX ATTACH; Schema: public; Owner: -
--

ALTER INDEX public.history_10043_pkey ATTACH PARTITION public.history_10043_2019_pkey;


--
-- Name: history_10043_2020_pkey; Type: INDEX ATTACH; Schema: public; Owner: -
--

ALTER INDEX public.history_10043_pkey ATTACH PARTITION public.history_10043_2020_pkey;


--
-- Name: history_10043_2021_pkey; Type: INDEX ATTACH; Schema: public; Owner: -
--

ALTER INDEX public.history_10043_pkey ATTACH PARTITION public.history_10043_2021_pkey;


--
-- Name: history_10043_2022_pkey; Type: INDEX ATTACH; Schema: public; Owner: -
--

ALTER INDEX public.history_10043_pkey ATTACH PARTITION public.history_10043_2022_pkey;


--
-- Name: history_full_2019_pkey; Type: INDEX ATTACH; Schema: public; Owner: -
--

ALTER INDEX public.history_full_pkey ATTACH PARTITION public.history_full_2019_pkey;


--
-- Name: history_full_2020_pkey; Type: INDEX ATTACH; Schema: public; Owner: -
--

ALTER INDEX public.history_full_pkey ATTACH PARTITION public.history_full_2020_pkey;


--
-- Name: history_full_2021_pkey; Type: INDEX ATTACH; Schema: public; Owner: -
--

ALTER INDEX public.history_full_pkey ATTACH PARTITION public.history_full_2021_pkey;


--
-- Name: history_full_2022_pkey; Type: INDEX ATTACH; Schema: public; Owner: -
--

ALTER INDEX public.history_full_pkey ATTACH PARTITION public.history_full_2022_pkey;


--
--

