-- Downloaded from: https://github.com/DmitryAntipin151002/Diplom/blob/350c6c1b05e8ce85abe2eca8a77acacf5b14e4d4/dump_AuthS.sql -- -- PostgreSQL database dump -- -- Dumped from database version 17.4 -- Dumped by pg_dump version 17.4 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; -- -- Name: update_activity_stats(); Type: FUNCTION; Schema: public; Owner: postgres -- CREATE FUNCTION public.update_activity_stats() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN UPDATE user_profiles SET total_activities = (SELECT COUNT(*) FROM user_activities WHERE user_id = NEW.user_id), total_distance = (SELECT COALESCE(SUM(distance), 0) FROM user_activities WHERE user_id = NEW.user_id), updated_at = CURRENT_TIMESTAMP WHERE user_id = NEW.user_id; RETURN NEW; END; $$; ALTER FUNCTION public.update_activity_stats() OWNER TO postgres; SET default_tablespace = ''; SET default_table_access_method = heap; -- -- Name: event; Type: TABLE; Schema: public; Owner: postgres -- CREATE TABLE public.event ( id integer NOT NULL, name character varying(255), description text, event_date timestamp without time zone, max_participants integer, organizer_id uuid ); ALTER TABLE public.event OWNER TO postgres; -- -- Name: event_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres -- CREATE SEQUENCE public.event_id_seq AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; ALTER SEQUENCE public.event_id_seq OWNER TO postgres; -- -- Name: event_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres -- ALTER SEQUENCE public.event_id_seq OWNED BY public.event.id; -- -- Name: event_participant; Type: TABLE; Schema: public; Owner: postgres -- CREATE TABLE public.event_participant ( id integer NOT NULL, user_id uuid, joined_at timestamp without time zone, event_id integer ); ALTER TABLE public.event_participant OWNER TO postgres; -- -- Name: event_participant_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres -- CREATE SEQUENCE public.event_participant_id_seq AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; ALTER SEQUENCE public.event_participant_id_seq OWNER TO postgres; -- -- Name: event_participant_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres -- ALTER SEQUENCE public.event_participant_id_seq OWNED BY public.event_participant.id; -- -- Name: role; Type: TABLE; Schema: public; Owner: postgres -- CREATE TABLE public.role ( id integer NOT NULL, name character varying(50) NOT NULL ); ALTER TABLE public.role OWNER TO postgres; -- -- Name: role_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres -- CREATE SEQUENCE public.role_id_seq AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; ALTER SEQUENCE public.role_id_seq OWNER TO postgres; -- -- Name: role_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres -- ALTER SEQUENCE public.role_id_seq OWNED BY public.role.id; -- -- Name: status; Type: TABLE; Schema: public; Owner: postgres -- CREATE TABLE public.status ( id integer NOT NULL, name character varying(50) NOT NULL ); ALTER TABLE public.status OWNER TO postgres; -- -- Name: status_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres -- CREATE SEQUENCE public.status_id_seq AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; ALTER SEQUENCE public.status_id_seq OWNER TO postgres; -- -- Name: status_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres -- ALTER SEQUENCE public.status_id_seq OWNED BY public.status.id; -- -- Name: user_activities; Type: TABLE; Schema: public; Owner: postgres -- CREATE TABLE public.user_activities ( id uuid DEFAULT gen_random_uuid() NOT NULL, user_id uuid, activity_type character varying(50) NOT NULL, distance numeric(10,2), duration text, calories_burned integer, activity_date timestamp without time zone NOT NULL, external_id character varying(100), raw_data character varying ); ALTER TABLE public.user_activities OWNER TO postgres; -- -- Name: user_photos; Type: TABLE; Schema: public; Owner: postgres -- CREATE TABLE public.user_photos ( id uuid DEFAULT gen_random_uuid() NOT NULL, user_id uuid, photo_url character varying(255) NOT NULL, is_main boolean DEFAULT false, uploaded_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP, description text ); ALTER TABLE public.user_photos OWNER TO postgres; -- -- Name: user_profiles; Type: TABLE; Schema: public; Owner: postgres -- CREATE TABLE public.user_profiles ( user_id uuid NOT NULL, avatar_url character varying(255), bio text, date_of_birth date, gender character varying(10), location character varying(100), sport_type character varying(50), fitness_level character varying(20), goals text, achievements text, total_activities integer DEFAULT 0, total_distance numeric(10,2) DEFAULT 0, total_wins integer DEFAULT 0, personal_records text, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP, updated_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP, is_verified boolean, last_active_at date, CONSTRAINT user_profiles_fitness_level_check CHECK (((fitness_level)::text = ANY (ARRAY[('Beginner'::character varying)::text, ('Intermediate'::character varying)::text, ('Advanced'::character varying)::text]))), CONSTRAINT user_profiles_gender_check CHECK (((gender)::text = ANY (ARRAY[('Male'::character varying)::text, ('Female'::character varying)::text, ('Other'::character varying)::text]))) ); ALTER TABLE public.user_profiles OWNER TO postgres; -- -- Name: users; Type: TABLE; Schema: public; Owner: postgres -- CREATE TABLE public.users ( id uuid DEFAULT gen_random_uuid() NOT NULL, email character varying(100) NOT NULL, phone_number character varying(15), encrypted_password character(60) NOT NULL, status bigint, is_first_enter boolean DEFAULT false NOT NULL, end_date date, role_id bigint NOT NULL, last_login date ); ALTER TABLE public.users OWNER TO postgres; -- -- Name: verification_code; Type: TABLE; Schema: public; Owner: postgres -- CREATE TABLE public.verification_code ( id uuid NOT NULL, user_id uuid NOT NULL, code character varying(6) NOT NULL, created_at timestamp without time zone NOT NULL, expires_at timestamp without time zone NOT NULL ); ALTER TABLE public.verification_code OWNER TO postgres; -- -- Name: event id; Type: DEFAULT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.event ALTER COLUMN id SET DEFAULT nextval('public.event_id_seq'::regclass); -- -- Name: event_participant id; Type: DEFAULT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.event_participant ALTER COLUMN id SET DEFAULT nextval('public.event_participant_id_seq'::regclass); -- -- Name: role id; Type: DEFAULT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.role ALTER COLUMN id SET DEFAULT nextval('public.role_id_seq'::regclass); -- -- Name: status id; Type: DEFAULT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.status ALTER COLUMN id SET DEFAULT nextval('public.status_id_seq'::regclass); -- -- Data for Name: event; Type: TABLE DATA; Schema: public; Owner: postgres -- COPY public.event (id, name, description, event_date, max_participants, organizer_id) FROM stdin; 2 Футбол на стадионе Дружеский матч на 10 человек 2025-04-09 17:00:00 10 6b1cf72a-60b3-4f78-ac20-cfc2e75b8512 \. -- -- Data for Name: event_participant; Type: TABLE DATA; Schema: public; Owner: postgres -- COPY public.event_participant (id, user_id, joined_at, event_id) FROM stdin; \. -- -- Data for Name: role; Type: TABLE DATA; Schema: public; Owner: postgres -- COPY public.role (id, name) FROM stdin; 1 ADMIN 2 USER \. -- -- Data for Name: status; Type: TABLE DATA; Schema: public; Owner: postgres -- COPY public.status (id, name) FROM stdin; 1 ACTIVE \. -- -- Data for Name: user_activities; Type: TABLE DATA; Schema: public; Owner: postgres -- COPY public.user_activities (id, user_id, activity_type, distance, duration, calories_burned, activity_date, external_id, raw_data) FROM stdin; dfa160ae-d33f-4b01-8672-8168bd744ca3 960b0c25-4568-4a4a-b45a-1edf4a6dfcfc Running 5.00 PT30M 500 2025-04-05 20:03:26.558959 external-id-123 {"speed": "12km/h"} \. -- -- Data for Name: user_photos; Type: TABLE DATA; Schema: public; Owner: postgres -- COPY public.user_photos (id, user_id, photo_url, is_main, uploaded_at, description) FROM stdin; d5023ad9-591d-4a9f-9872-bf3209bc7166 960b0c25-4568-4a4a-b45a-1edf4a6dfcfc http://example.com/new-avatar.jpg t 2025-04-05 19:50:16.791349 \N \. -- -- Data for Name: user_profiles; Type: TABLE DATA; Schema: public; Owner: postgres -- COPY public.user_profiles (user_id, avatar_url, bio, date_of_birth, gender, location, sport_type, fitness_level, goals, achievements, total_activities, total_distance, total_wins, personal_records, created_at, updated_at, is_verified, last_active_at) FROM stdin; 960b0c25-4568-4a4a-b45a-1edf4a6dfcfc http://example.com/avatar.jpg Bio of the user 1990-01-01 Male New Sity Outdoor Intermediate Run 5k Completed 10k 1 5.00 5 5k in 30min 2025-04-05 16:09:11.481505 2025-04-05 20:03:26.561609 t 2025-04-05 6b1cf72a-60b3-4f78-ac20-cfc2e75b8512 http://example.com/avatar.jpg Bio of the user 1990-01-01 Male New York Outdoor Intermediate Run 5k Completed 10k 10 100.50 5 5k in 30min 2025-04-08 13:00:54.036292 2025-04-08 13:00:54.036292 t 2025-04-05 \. -- -- Data for Name: users; Type: TABLE DATA; Schema: public; Owner: postgres -- COPY public.users (id, email, phone_number, encrypted_password, status, is_first_enter, end_date, role_id, last_login) FROM stdin; ce2082c5-1f56-4293-aac4-4a057c6980bc user@example.com +1234567890 $2a$10$.PC11OLxxrEH8IodYZGY2OX4T617k4qq6vbrlGN4MdQNtnJGcI5j6 1 t \N 1 \N 960b0c25-4568-4a4a-b45a-1edf4a6dfcfc user@example1.com \N $2a$10$xYVXvwWtPQD/gqgANOVxeuMkAHK83PTcv5tm3QTM51JpAXHXP/Ake 1 f \N 1 2025-04-05 73c398a7-6612-4764-97ec-098c5802ae97 user@example2.com \N $2a$10$8AP//1k2Son9d/JUdUU6VuwMHGK9pzoCEAMhD5ryVN2MiMP56Pv7y 1 t \N 1 2025-04-05 1a54beaa-6290-4dbf-b18b-a3df94cc63b5 user@example4.com \N $2a$10$7E6Pt9/01D8jKK4z0i98BesuxJVVSxLbIO1VWEVkiT1jc2yH.LLAG 1 t \N 1 2025-04-05 6b1cf72a-60b3-4f78-ac20-cfc2e75b8512 dmitry@example.com \N $2a$10$hEZdn2vPjZkCJdMDuMs.hO31SyLqbIFgCeYfFcexZFVRBQIZeJzeq 1 t \N 1 2025-04-08 \. -- -- Data for Name: verification_code; Type: TABLE DATA; Schema: public; Owner: postgres -- COPY public.verification_code (id, user_id, code, created_at, expires_at) FROM stdin; 3b9a4c0b-b319-4639-ba60-ad064bc4596b ce2082c5-1f56-4293-aac4-4a057c6980bc 262495 2025-03-19 15:05:26.162304 2025-03-19 15:10:26.162304 39c31998-35f3-4e8e-aeeb-eb3c1a877a56 960b0c25-4568-4a4a-b45a-1edf4a6dfcfc 341250 2025-04-05 14:59:50.149167 2025-04-05 15:04:50.149167 3ecd4af4-f5e4-476b-83cf-cc4906d9efc4 6b1cf72a-60b3-4f78-ac20-cfc2e75b8512 226002 2025-04-08 13:00:08.830408 2025-04-08 13:05:08.830408 \. -- -- Name: event_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres -- SELECT pg_catalog.setval('public.event_id_seq', 2, true); -- -- Name: event_participant_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres -- SELECT pg_catalog.setval('public.event_participant_id_seq', 1, false); -- -- Name: role_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres -- SELECT pg_catalog.setval('public.role_id_seq', 1, false); -- -- Name: status_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres -- SELECT pg_catalog.setval('public.status_id_seq', 1, false); -- -- Name: event_participant event_participant_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.event_participant ADD CONSTRAINT event_participant_pkey PRIMARY KEY (id); -- -- Name: event event_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.event ADD CONSTRAINT event_pkey PRIMARY KEY (id); -- -- Name: role role_name_key; Type: CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.role ADD CONSTRAINT role_name_key UNIQUE (name); -- -- Name: role role_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.role ADD CONSTRAINT role_pkey PRIMARY KEY (id); -- -- Name: status status_name_key; Type: CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.status ADD CONSTRAINT status_name_key UNIQUE (name); -- -- Name: status status_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.status ADD CONSTRAINT status_pkey PRIMARY KEY (id); -- -- Name: user_activities user_activities_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.user_activities ADD CONSTRAINT user_activities_pkey PRIMARY KEY (id); -- -- Name: user_photos user_photos_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.user_photos ADD CONSTRAINT user_photos_pkey PRIMARY KEY (id); -- -- Name: user_profiles user_profiles_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.user_profiles ADD CONSTRAINT user_profiles_pkey PRIMARY KEY (user_id); -- -- Name: users users_email_key; Type: CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.users ADD CONSTRAINT users_email_key UNIQUE (email); -- -- Name: users users_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.users ADD CONSTRAINT users_pkey PRIMARY KEY (id); -- -- Name: verification_code verification_code_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.verification_code ADD CONSTRAINT verification_code_pkey PRIMARY KEY (id); -- -- Name: idx_user_activities_date; Type: INDEX; Schema: public; Owner: postgres -- CREATE INDEX idx_user_activities_date ON public.user_activities USING btree (activity_date); -- -- Name: idx_user_activities_user_id; Type: INDEX; Schema: public; Owner: postgres -- CREATE INDEX idx_user_activities_user_id ON public.user_activities USING btree (user_id); -- -- Name: idx_user_photos_user_id; Type: INDEX; Schema: public; Owner: postgres -- CREATE INDEX idx_user_photos_user_id ON public.user_photos USING btree (user_id); -- -- Name: user_activities trigger_update_activity_stats; Type: TRIGGER; Schema: public; Owner: postgres -- CREATE TRIGGER trigger_update_activity_stats AFTER INSERT OR DELETE OR UPDATE ON public.user_activities FOR EACH ROW EXECUTE FUNCTION public.update_activity_stats(); -- -- Name: event_participant event_participant_event_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.event_participant ADD CONSTRAINT event_participant_event_id_fkey FOREIGN KEY (event_id) REFERENCES public.event(id); -- -- Name: user_activities user_activities_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.user_activities ADD CONSTRAINT user_activities_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.user_profiles(user_id) ON DELETE CASCADE; -- -- Name: user_photos user_photos_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.user_photos ADD CONSTRAINT user_photos_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.user_profiles(user_id) ON DELETE CASCADE; -- -- Name: user_profiles user_profiles_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.user_profiles ADD CONSTRAINT user_profiles_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id) ON DELETE CASCADE; -- -- Name: users users_role_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.users ADD CONSTRAINT users_role_id_fkey FOREIGN KEY (role_id) REFERENCES public.role(id) ON DELETE CASCADE; -- -- Name: users users_status_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY public.users ADD CONSTRAINT users_status_fkey FOREIGN KEY (status) REFERENCES public.status(id) ON DELETE SET NULL; -- -- PostgreSQL database dump complete --