bootstrap.sql

--
-- PostgreSQL database dump
--

-- Dumped from database version 18beta1
-- Dumped by pg_dump version 18beta1

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET transaction_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;


ALTER SCHEMA public OWNER TO pg_database_owner;

SET default_tablespace = '';

SET default_table_access_method = heap;

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

CREATE TABLE public.assets (
    id bigint NOT NULL,
    kind text NOT NULL,
    event_id integer,
    uploader_id bigint NOT NULL,
    uploader_ip integer,
    path text NOT NULL,
    file_size integer NOT NULL,
    width integer,
    height integer,
    visible_at timestamp with time zone NOT NULL,
    deleted_at timestamp with time zone,
    inserted_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone NOT NULL
);


ALTER TABLE public.assets OWNER TO postgres;

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

ALTER TABLE public.assets ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME public.assets_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


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

CREATE TABLE public.events (
    id bigint NOT NULL,
    event_key text NOT NULL,
    name_en text,
    name_tr text,
    banner text,
    valid_from timestamp with time zone NOT NULL,
    valid_until timestamp with time zone NOT NULL,
    deleted_at timestamp without time zone,
    whitelisted_country character varying(2),
    description_en text,
    description_tr text,
    welcome_markdown text,
    inserted_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone NOT NULL
);


ALTER TABLE public.events OWNER TO postgres;

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

ALTER TABLE public.events ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME public.events_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


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

CREATE TABLE public.guest_ip_addresses (
    id bigint NOT NULL,
    guest_id bigint NOT NULL,
    ip_address text NOT NULL,
    inserted_at timestamp with time zone DEFAULT now() NOT NULL
);


ALTER TABLE public.guest_ip_addresses OWNER TO postgres;

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

ALTER TABLE public.guest_ip_addresses ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME public.guest_ip_addresses_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


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

CREATE TABLE public.guests (
    id bigint NOT NULL,
    session_id text,
    username text,
    avatar_path text,
    color integer,
    side text,
    audio_recording_path text,
    audio_recording_public boolean DEFAULT true,
    memo text,
    memo_public boolean DEFAULT true,
    setup_at timestamp with time zone,
    last_upload timestamp with time zone,
    inserted_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone NOT NULL
);


ALTER TABLE public.guests OWNER TO postgres;

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

ALTER TABLE public.guests ALTER COLUMN id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME public.guests_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- Data for Name: assets; Type: TABLE DATA; Schema: public; Owner: postgres
--

INSERT INTO public.assets VALUES (1, 'image', 1, 1, 0, '/images/placeholder1.png', 1000, 792, 1024, '2025-06-26 14:30:42.758+00', NULL, '2025-06-26 14:30:42.913193+00', '2025-06-26 14:30:42.909+00');
INSERT INTO public.assets VALUES (2, 'video', 1, 1, 6, '/images/placeholder2.mp4', 5095, 458, 672, '2025-06-26 14:29:42.758+00', NULL, '2025-06-26 14:30:42.913193+00', '2025-06-26 14:30:42.909+00');
INSERT INTO public.assets VALUES (3, 'video', 1, 3, 4, '/images/taylor_swift.mp4', 1038, NULL, NULL, '2025-06-26 14:28:42.758+00', NULL, '2025-06-26 14:30:42.913193+00', '2025-06-26 14:30:42.909+00');
INSERT INTO public.assets VALUES (4, 'video', 1, 4, 2, '/images/bad_car.mp4', 808, NULL, NULL, '2025-06-26 14:28:42.758+00', NULL, '2025-06-26 14:30:42.913193+00', '2025-06-26 14:30:42.909+00');
INSERT INTO public.assets VALUES (5, 'video', 1, 3, 4, '/images/thatslife.mov', 400, NULL, NULL, '2025-06-26 14:27:42.758+00', NULL, '2025-06-26 14:30:42.913193+00', '2025-06-26 14:30:42.909+00');


--
-- Data for Name: events; Type: TABLE DATA; Schema: public; Owner: postgres
--

INSERT INTO public.events VALUES (1, '01JKCVP4M2CH34SVTQGHSW4Y5G', 'test event', 'Test', '/images/banner.jpg', '2025-06-25 14:30:42.758+00', '2026-06-26 14:30:42.758+00', NULL, NULL, NULL, NULL, '## Hello', '2025-06-26 14:30:42.82511+00', '2025-06-26 14:30:42.759+00');
INSERT INTO public.events VALUES (2, '00JKCVP4M2CH34SVTQGHSW4Y5G', 'expired event', 'sey olmus event', NULL, '2025-06-19 14:30:42.758+00', '2025-06-25 14:30:42.758+00', NULL, NULL, 'Test', 'testing', NULL, '2025-06-26 14:30:42.848842+00', '2025-06-26 14:30:42.845+00');

--
-- Data for Name: guests; Type: TABLE DATA; Schema: public; Owner: postgres
--

INSERT INTO public.guests VALUES (1, NULL, 'Mark S.', NULL, 123123, 'groom', NULL, true, 'Love u guys', true, NULL, '2025-06-26 14:29:42.758+00', '2025-06-26 14:30:42.865674+00', '2025-06-26 14:30:42.862+00');
INSERT INTO public.guests VALUES (2, NULL, 'Dylan G.', NULL, 345345, 'bride', '/audio/test2.m4a', false, 'Hey kid, what''s for dinner?', false, NULL, NULL, '2025-06-26 14:30:42.881585+00', '2025-06-26 14:30:42.878+00');
INSERT INTO public.guests VALUES (3, NULL, 'Helly R.', NULL, 141414, 'bride', '/audio/test.m4a', true, NULL, true, NULL, '2025-06-26 14:28:42.758+00', '2025-06-26 14:30:42.89433+00', '2025-06-26 14:30:42.892+00');
INSERT INTO public.guests VALUES (4, NULL, 'Irving B.', NULL, 854925, NULL, '/audio/test.m4a', false, 'Hey kid, what''s for dinner?', true, NULL, '2025-06-26 14:27:42.758+00', '2025-06-26 14:30:42.903261+00', '2025-06-26 14:30:42.9+00');


--
-- Name: assets_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('public.assets_id_seq', 5, true);


--
-- Name: events_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('public.events_id_seq', 2, true);


--
-- Name: guest_ip_addresses_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('public.guest_ip_addresses_id_seq', 1, false);


--
-- Name: guests_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('public.guests_id_seq', 4, true);


--
-- Name: assets assets_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.assets
    ADD CONSTRAINT assets_pkey PRIMARY KEY (id);


--
-- Name: events events_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.events
    ADD CONSTRAINT events_pkey PRIMARY KEY (id);


--
-- Name: guest_ip_addresses guest_ip_addresses_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.guest_ip_addresses
    ADD CONSTRAINT guest_ip_addresses_pkey PRIMARY KEY (id);


--
-- Name: guests guests_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.guests
    ADD CONSTRAINT guests_pkey PRIMARY KEY (id);


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

CREATE UNIQUE INDEX events_event_key_index ON public.events USING btree (event_key);


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

CREATE UNIQUE INDEX guest_sessions ON public.guests USING btree (session_id);


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

CREATE INDEX guests_last_upload_desc_id_desc_index ON public.guests USING btree (last_upload DESC, id DESC);


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

CREATE INDEX timeline_cursor_idx ON public.assets USING btree (event_id, visible_at DESC, id DESC) WHERE (deleted_at IS NULL);


--
-- Name: assets assets_uploader_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.assets
    ADD CONSTRAINT assets_uploader_id_fkey FOREIGN KEY (uploader_id) REFERENCES public.guests(id);

SET search_path = 'public';

select
  "guests"."id",
  "guests"."session_id",
  "guests"."username",
  "guests"."avatar_path",
  "guests"."color",
  "guests"."side",
  "guests"."audio_recording_path",
  "guests"."audio_recording_public",
  "guests"."memo",
  "guests"."memo_public",
  "guests"."setup_at",
  "guests"."last_upload",
  "guests"."inserted_at",
  "guests"."updated_at",
  "userAssets"."id",
  "userAssets"."kind",
  "userAssets"."event_id",
  "userAssets"."uploader_id",
  "userAssets"."uploader_ip",
  "userAssets"."path",
  "userAssets"."file_size",
  "userAssets"."width",
  "userAssets"."height",
  "userAssets"."visible_at",
  "userAssets"."deleted_at",
  "userAssets"."inserted_at",
  "userAssets"."updated_at"
from
  (
    select
      "id",
      "session_id",
      "username",
      "avatar_path",
      "color",
      "side",
      "audio_recording_path",
      "audio_recording_public",
      "memo",
      "memo_public",
      "setup_at",
      "last_upload",
      "inserted_at",
      "updated_at"
    from
      "guests"
    order by
      "guests"."last_upload" desc,
      "guests"."id" desc
    limit
      100
  ) "guests"
  cross join lateral (
    select
      "id",
      "kind",
      "event_id",
      "uploader_id",
      "uploader_ip",
      "path",
      "file_size",
      "width",
      "height",
      "visible_at",
      "deleted_at",
      "inserted_at",
      "updated_at"
    from
      "assets"
    where
      (
        "assets"."event_id" = (
          select
            "id"
          from
            "events"
          where
            "events"."event_key" = '01JKCVP4M2CH34SVTQGHSW4Y5G'
        )
        and "assets"."uploader_id" = "guests"."id"
      )
    order by
      "assets"."inserted_at" desc
    limit
      100
  ) "userAssets";

select * from guests order by last_upload desc, id desc limit 100;

select * from guest_ip_addresses where ip_address = '127.0.0.1';
insert into guest_ip_addresses (guest_id, ip_address) values (1, '127.0.0.1');
--
-- PostgreSQL database dump complete
--