init-dump.sql - master - Egor1k/comp-club-database

init-dump.sql
543 строки16 KB
Dima Oshuev
first_commit
27 мар 2025, 16:50
27 мар 2025, 16:5064b76b0
--
-- PostgreSQL database dump
--

-- Dumped from database version 17.0
-- Dumped by pg_dump version 17.0

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: gamecenterschema; Type: SCHEMA; Schema: -; Owner: postgres
--

CREATE SCHEMA gamecenterschema;


ALTER SCHEMA gamecenterschema OWNER TO postgres;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: account; Type: TABLE; Schema: gamecenterschema; Owner: postgres
--

CREATE TABLE gamecenterschema.account (
    user_id integer NOT NULL,
    first_name character varying(50) NOT NULL,
    last_name character varying(50) NOT NULL,
    email character varying(100) NOT NULL,
    password character varying(255) NOT NULL,
    balance integer,
    role character varying(20) DEFAULT 'User'::character varying
);


ALTER TABLE gamecenterschema.account OWNER TO postgres;

--
-- Name: account_user_id_seq; Type: SEQUENCE; Schema: gamecenterschema; Owner: postgres
--

CREATE SEQUENCE gamecenterschema.account_user_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE gamecenterschema.account_user_id_seq OWNER TO postgres;

--
-- Name: account_user_id_seq; Type: SEQUENCE OWNED BY; Schema: gamecenterschema; Owner: postgres
--

ALTER SEQUENCE gamecenterschema.account_user_id_seq OWNED BY gamecenterschema.account.user_id;


--
-- Name: computer; Type: TABLE; Schema: gamecenterschema; Owner: postgres
--

CREATE TABLE gamecenterschema.computer (
    computer_id integer NOT NULL,
    location character varying(30) NOT NULL,
    specifications character varying(255),
    status character varying(20) DEFAULT 'Доступный'::character varying,
    CONSTRAINT computer_status_check CHECK (((status)::text = ANY ((ARRAY['Доступный'::character varying, 'Занят'::character varying, 'На ремонте'::character varying])::text[])))
);


ALTER TABLE gamecenterschema.computer OWNER TO postgres;

--
-- Name: computer_computer_id_seq; Type: SEQUENCE; Schema: gamecenterschema; Owner: postgres
--

CREATE SEQUENCE gamecenterschema.computer_computer_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE gamecenterschema.computer_computer_id_seq OWNER TO postgres;

--
-- Name: computer_computer_id_seq; Type: SEQUENCE OWNED BY; Schema: gamecenterschema; Owner: postgres
--

ALTER SEQUENCE gamecenterschema.computer_computer_id_seq OWNED BY gamecenterschema.computer.computer_id;


--
-- Name: payment; Type: TABLE; Schema: gamecenterschema; Owner: postgres
--

CREATE TABLE gamecenterschema.payment (
    payment_id integer NOT NULL,
    session_id integer NOT NULL,
    account_id integer NOT NULL,
    amount numeric(10,2) NOT NULL,
    payment_date timestamp without time zone NOT NULL,
    payment_method character varying(20) DEFAULT 'Наличная оплата'::character varying,
    CONSTRAINT payment_amount_check CHECK ((amount > (0)::numeric)),
    CONSTRAINT payment_payment_method_check CHECK (((payment_method)::text = ANY ((ARRAY['Наличная оплата'::character varying, 'Карта'::character varying, 'Бонусы'::character varying])::text[])))
);


ALTER TABLE gamecenterschema.payment OWNER TO postgres;

--
-- Name: payment_payment_id_seq; Type: SEQUENCE; Schema: gamecenterschema; Owner: postgres
--

CREATE SEQUENCE gamecenterschema.payment_payment_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE gamecenterschema.payment_payment_id_seq OWNER TO postgres;

--
-- Name: payment_payment_id_seq; Type: SEQUENCE OWNED BY; Schema: gamecenterschema; Owner: postgres
--

ALTER SEQUENCE gamecenterschema.payment_payment_id_seq OWNED BY gamecenterschema.payment.payment_id;


--
-- Name: player_session; Type: TABLE; Schema: gamecenterschema; Owner: postgres
--

CREATE TABLE gamecenterschema.player_session (
    session_id integer NOT NULL,
    tariff_id integer NOT NULL,
    computer_id integer NOT NULL,
    account_id integer NOT NULL,
    start_time timestamp without time zone NOT NULL,
    end_time timestamp without time zone,
    total_time integer GENERATED ALWAYS AS ((EXTRACT(epoch FROM (end_time - start_time)) / (60)::numeric)) STORED,
    cost numeric(10,2) NOT NULL,
    promotion_id integer,
    CONSTRAINT player_session_cost_check CHECK ((cost > (0)::numeric))
);


ALTER TABLE gamecenterschema.player_session OWNER TO postgres;

--
-- Name: player_session_session_id_seq; Type: SEQUENCE; Schema: gamecenterschema; Owner: postgres
--

CREATE SEQUENCE gamecenterschema.player_session_session_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE gamecenterschema.player_session_session_id_seq OWNER TO postgres;

--
-- Name: player_session_session_id_seq; Type: SEQUENCE OWNED BY; Schema: gamecenterschema; Owner: postgres
--

ALTER SEQUENCE gamecenterschema.player_session_session_id_seq OWNED BY gamecenterschema.player_session.session_id;


--
-- Name: promotion; Type: TABLE; Schema: gamecenterschema; Owner: postgres
--

CREATE TABLE gamecenterschema.promotion (
    promotion_id integer NOT NULL,
    tariff_id integer NOT NULL,
    promotion_description character varying(255),
    bonus_type character varying(50) NOT NULL,
    validity_period date,
    discount_percentage numeric(5,2),
    bonus_time integer,
    CONSTRAINT promotion_bonus_time_check CHECK ((bonus_time >= 0)),
    CONSTRAINT promotion_bonus_type_check CHECK (((bonus_type)::text = ANY ((ARRAY['Скидка'::character varying, 'Дополнительное время'::character varying])::text[]))),
    CONSTRAINT promotion_discount_percentage_check CHECK (((discount_percentage >= (0)::numeric) AND (discount_percentage <= (50)::numeric)))
);


ALTER TABLE gamecenterschema.promotion OWNER TO postgres;

--
-- Name: promotion_promotion_id_seq; Type: SEQUENCE; Schema: gamecenterschema; Owner: postgres
--

CREATE SEQUENCE gamecenterschema.promotion_promotion_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE gamecenterschema.promotion_promotion_id_seq OWNER TO postgres;

--
-- Name: promotion_promotion_id_seq; Type: SEQUENCE OWNED BY; Schema: gamecenterschema; Owner: postgres
--

ALTER SEQUENCE gamecenterschema.promotion_promotion_id_seq OWNED BY gamecenterschema.promotion.promotion_id;


--
-- Name: tariff; Type: TABLE; Schema: gamecenterschema; Owner: postgres
--

CREATE TABLE gamecenterschema.tariff (
    tariff_id integer NOT NULL,
    tariff_name character varying(50) NOT NULL,
    hourly_rate numeric(5,2) NOT NULL,
    description character varying(255),
    CONSTRAINT tariff_hourly_rate_check CHECK ((hourly_rate > (0)::numeric))
);


ALTER TABLE gamecenterschema.tariff OWNER TO postgres;

--
-- Name: tariff_tariff_id_seq; Type: SEQUENCE; Schema: gamecenterschema; Owner: postgres
--

CREATE SEQUENCE gamecenterschema.tariff_tariff_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE gamecenterschema.tariff_tariff_id_seq OWNER TO postgres;

--
-- Name: tariff_tariff_id_seq; Type: SEQUENCE OWNED BY; Schema: gamecenterschema; Owner: postgres
--

ALTER SEQUENCE gamecenterschema.tariff_tariff_id_seq OWNED BY gamecenterschema.tariff.tariff_id;


--
-- Name: account user_id; Type: DEFAULT; Schema: gamecenterschema; Owner: postgres
--

ALTER TABLE ONLY gamecenterschema.account ALTER COLUMN user_id SET DEFAULT nextval('gamecenterschema.account_user_id_seq'::regclass);


--
-- Name: computer computer_id; Type: DEFAULT; Schema: gamecenterschema; Owner: postgres
--

ALTER TABLE ONLY gamecenterschema.computer ALTER COLUMN computer_id SET DEFAULT nextval('gamecenterschema.computer_computer_id_seq'::regclass);


--
-- Name: payment payment_id; Type: DEFAULT; Schema: gamecenterschema; Owner: postgres
--

ALTER TABLE ONLY gamecenterschema.payment ALTER COLUMN payment_id SET DEFAULT nextval('gamecenterschema.payment_payment_id_seq'::regclass);


--
-- Name: player_session session_id; Type: DEFAULT; Schema: gamecenterschema; Owner: postgres
--

ALTER TABLE ONLY gamecenterschema.player_session ALTER COLUMN session_id SET DEFAULT nextval('gamecenterschema.player_session_session_id_seq'::regclass);


--
-- Name: promotion promotion_id; Type: DEFAULT; Schema: gamecenterschema; Owner: postgres
--

ALTER TABLE ONLY gamecenterschema.promotion ALTER COLUMN promotion_id SET DEFAULT nextval('gamecenterschema.promotion_promotion_id_seq'::regclass);


--
-- Name: tariff tariff_id; Type: DEFAULT; Schema: gamecenterschema; Owner: postgres
--

ALTER TABLE ONLY gamecenterschema.tariff ALTER COLUMN tariff_id SET DEFAULT nextval('gamecenterschema.tariff_tariff_id_seq'::regclass);


--
-- Data for Name: account; Type: TABLE DATA; Schema: gamecenterschema; Owner: postgres
--

COPY gamecenterschema.account (user_id, first_name, last_name, email, password, balance, role) FROM stdin;
2	Jane	Smith	jane.smith@example.com	password2	200	admin
3	Alice	Johnson	alice.johnson@example.com	password3	150	user
1	John	Doe	john.doe@example.com	password1	253	user
4	Bob	Brown	bob.brown@example.com	password4	240	admin
7	Лева	Левин	leva@gmail.com	leva	490	User
8	FirstName	LastName	myemail@gmail.com	qwerty	2985	User
\.


--
-- Data for Name: computer; Type: TABLE DATA; Schema: gamecenterschema; Owner: postgres
--

COPY gamecenterschema.computer (computer_id, location, specifications, status) FROM stdin;
1	Room 101	Intel i5, 8GB RAM, 500GB SSD	Доступный
2	Room 102	Intel i7, 16GB RAM, 1TB SSD	Занят
3	Room 103	AMD Ryzen 5, 16GB RAM, 1TB HDD	Доступный
4	Room 104	Intel i3, 4GB RAM, 250GB SSD	На ремонте
\.


--
-- Data for Name: payment; Type: TABLE DATA; Schema: gamecenterschema; Owner: postgres
--

COPY gamecenterschema.payment (payment_id, session_id, account_id, amount, payment_date, payment_method) FROM stdin;
13	10	1	9.00	2025-02-11 00:00:00	Наличная оплата
14	24	4	10.00	2025-02-11 00:00:00	Наличная оплата
15	25	7	5.00	2025-02-12 00:00:00	Наличная оплата
16	26	7	4.50	2025-02-12 00:00:00	Наличная оплата
17	27	8	15.00	2025-03-27 00:00:00	Наличная оплата
\.


--
-- Data for Name: player_session; Type: TABLE DATA; Schema: gamecenterschema; Owner: postgres
--

COPY gamecenterschema.player_session (session_id, tariff_id, computer_id, account_id, start_time, end_time, cost, promotion_id) FROM stdin;
10	1	1	1	2024-02-07 10:00:00	2024-02-07 12:00:00	9.00	1
24	1	1	4	2025-02-11 20:00:00	2025-02-11 22:00:00	10.00	\N
25	1	1	7	2025-02-12 19:00:00	2025-02-12 20:00:00	5.00	\N
26	1	1	7	2025-02-12 22:00:00	2025-02-12 23:00:00	4.50	1
27	1	1	8	2025-03-27 17:00:00	2025-03-27 20:00:00	15.00	\N
\.


--
-- Data for Name: promotion; Type: TABLE DATA; Schema: gamecenterschema; Owner: postgres
--

COPY gamecenterschema.promotion (promotion_id, tariff_id, promotion_description, bonus_type, validity_period, discount_percentage, bonus_time) FROM stdin;
2	2	Loyalty Bonus	Дополнительное время	2023-11-30	15.00	60
3	3	Student Discount	Скидка	2023-10-31	20.00	90
4	1	Holiday Special	Дополнительное время	2023-09-30	25.00	120
1	1	Welcome Bonus	Скидка	2026-12-12	10.00	30
\.


--
-- Data for Name: tariff; Type: TABLE DATA; Schema: gamecenterschema; Owner: postgres
--

COPY gamecenterschema.tariff (tariff_id, tariff_name, hourly_rate, description) FROM stdin;
1	Basic	5.00	Basic hourly rate
2	Premium	10.00	Premium hourly rate with additional benefits
3	Student	3.00	Special rate for students
\.


--
-- Name: account_user_id_seq; Type: SEQUENCE SET; Schema: gamecenterschema; Owner: postgres
--

SELECT pg_catalog.setval('gamecenterschema.account_user_id_seq', 8, true);


--
-- Name: computer_computer_id_seq; Type: SEQUENCE SET; Schema: gamecenterschema; Owner: postgres
--

SELECT pg_catalog.setval('gamecenterschema.computer_computer_id_seq', 1, false);


--
-- Name: payment_payment_id_seq; Type: SEQUENCE SET; Schema: gamecenterschema; Owner: postgres
--

SELECT pg_catalog.setval('gamecenterschema.payment_payment_id_seq', 17, true);


--
-- Name: player_session_session_id_seq; Type: SEQUENCE SET; Schema: gamecenterschema; Owner: postgres
--

SELECT pg_catalog.setval('gamecenterschema.player_session_session_id_seq', 27, true);


--
-- Name: promotion_promotion_id_seq; Type: SEQUENCE SET; Schema: gamecenterschema; Owner: postgres
--

SELECT pg_catalog.setval('gamecenterschema.promotion_promotion_id_seq', 1, false);


--
-- Name: tariff_tariff_id_seq; Type: SEQUENCE SET; Schema: gamecenterschema; Owner: postgres
--

SELECT pg_catalog.setval('gamecenterschema.tariff_tariff_id_seq', 1, false);


--
-- Name: account account_email_key; Type: CONSTRAINT; Schema: gamecenterschema; Owner: postgres
--

ALTER TABLE ONLY gamecenterschema.account
    ADD CONSTRAINT account_email_key UNIQUE (email);


--
-- Name: account account_pkey; Type: CONSTRAINT; Schema: gamecenterschema; Owner: postgres
--

ALTER TABLE ONLY gamecenterschema.account
    ADD CONSTRAINT account_pkey PRIMARY KEY (user_id);


--
-- Name: computer computer_pkey; Type: CONSTRAINT; Schema: gamecenterschema; Owner: postgres
--

ALTER TABLE ONLY gamecenterschema.computer
    ADD CONSTRAINT computer_pkey PRIMARY KEY (computer_id);


--
-- Name: payment payment_pkey; Type: CONSTRAINT; Schema: gamecenterschema; Owner: postgres
--

ALTER TABLE ONLY gamecenterschema.payment
    ADD CONSTRAINT payment_pkey PRIMARY KEY (payment_id);


--
-- Name: player_session player_session_pkey; Type: CONSTRAINT; Schema: gamecenterschema; Owner: postgres
--

ALTER TABLE ONLY gamecenterschema.player_session
    ADD CONSTRAINT player_session_pkey PRIMARY KEY (session_id);


--
-- Name: promotion promotion_pkey; Type: CONSTRAINT; Schema: gamecenterschema; Owner: postgres
--

ALTER TABLE ONLY gamecenterschema.promotion
    ADD CONSTRAINT promotion_pkey PRIMARY KEY (promotion_id);


--
-- Name: tariff tariff_pkey; Type: CONSTRAINT; Schema: gamecenterschema; Owner: postgres
--

ALTER TABLE ONLY gamecenterschema.tariff
    ADD CONSTRAINT tariff_pkey PRIMARY KEY (tariff_id);


--
-- Name: player_session fk_promotion; Type: FK CONSTRAINT; Schema: gamecenterschema; Owner: postgres
--

ALTER TABLE ONLY gamecenterschema.player_session
    ADD CONSTRAINT fk_promotion FOREIGN KEY (promotion_id) REFERENCES gamecenterschema.promotion(promotion_id) ON DELETE SET NULL;


--
-- Name: payment payment_account_id_fkey; Type: FK CONSTRAINT; Schema: gamecenterschema; Owner: postgres
--

ALTER TABLE ONLY gamecenterschema.payment
    ADD CONSTRAINT payment_account_id_fkey FOREIGN KEY (account_id) REFERENCES gamecenterschema.account(user_id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: payment payment_session_id_fkey; Type: FK CONSTRAINT; Schema: gamecenterschema; Owner: postgres
--

ALTER TABLE ONLY gamecenterschema.payment
    ADD CONSTRAINT payment_session_id_fkey FOREIGN KEY (session_id) REFERENCES gamecenterschema.player_session(session_id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- Name: player_session player_session_account_id_fkey; Type: FK CONSTRAINT; Schema: gamecenterschema; Owner: postgres
--

ALTER TABLE ONLY gamecenterschema.player_session
    ADD CONSTRAINT player_session_account_id_fkey FOREIGN KEY (account_id) REFERENCES gamecenterschema.account(user_id);


--
-- Name: player_session player_session_computer_id_fkey; Type: FK CONSTRAINT; Schema: gamecenterschema; Owner: postgres
--

ALTER TABLE ONLY gamecenterschema.player_session
    ADD CONSTRAINT player_session_computer_id_fkey FOREIGN KEY (computer_id) REFERENCES gamecenterschema.computer(computer_id);


--
-- Name: player_session player_session_tariff_id_fkey; Type: FK CONSTRAINT; Schema: gamecenterschema; Owner: postgres
--

ALTER TABLE ONLY gamecenterschema.player_session
    ADD CONSTRAINT player_session_tariff_id_fkey FOREIGN KEY (tariff_id) REFERENCES gamecenterschema.tariff(tariff_id);


--
-- Name: promotion promotion_tariff_id_fkey; Type: FK CONSTRAINT; Schema: gamecenterschema; Owner: postgres
--

ALTER TABLE ONLY gamecenterschema.promotion
    ADD CONSTRAINT promotion_tariff_id_fkey FOREIGN KEY (tariff_id) REFERENCES gamecenterschema.tariff(tariff_id) ON UPDATE CASCADE ON DELETE CASCADE;


--
-- PostgreSQL database dump complete
--