Akulova_Autoschool_PM04/Autoschool_PM11/autoschool.sql

--
-- PostgreSQL database dump
--

\restrict wUhTUEtP7H3sZbn0Sj8yBWOgI7rW8eGIJqHJvYsaK1avC0CCtslcJ40NJgPs7Rm

-- Dumped from database version 18.0
-- Dumped by pg_dump version 18.0

-- Started on 2026-06-02 17:20:08

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;

--
-- TOC entry 10 (class 2615 OID 442812)
-- Name: akulova_vs; Type: SCHEMA; Schema: -; Owner: akulova_vs
--

CREATE SCHEMA akulova_vs;


ALTER SCHEMA akulova_vs OWNER TO akulova_vs;

--
-- TOC entry 2457 (class 1247 OID 448285)
-- Name: app_role; Type: TYPE; Schema: akulova_vs; Owner: akulova_vs
--

CREATE TYPE akulova_vs.app_role AS ENUM (
    'admin',
    'user'
);


ALTER TYPE akulova_vs.app_role OWNER TO akulova_vs;

--
-- TOC entry 1805 (class 1255 OID 454368)
-- Name: fn_audit_log_changes(); Type: FUNCTION; Schema: akulova_vs; Owner: akulova_vs
--

CREATE FUNCTION akulova_vs.fn_audit_log_changes() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
declare
    pk_column text := tg_argv[0];
    row_data jsonb;
    current_record_id integer;
begin
    if tg_op = 'DELETE' then
        row_data := to_jsonb(old);
    else
        row_data := to_jsonb(new);
    end if;

    current_record_id := nullif(row_data ->> pk_column, '')::integer;

    insert into akulova_vs.audit_log (
        table_name,
        operation,
        record_id,
        changed_by,
        old_data,
        new_data
    ) values (
        tg_table_name,
        tg_op,
        current_record_id,
        current_user,
        case when tg_op in ('UPDATE', 'DELETE') then to_jsonb(old) else null end,
        case when tg_op in ('INSERT', 'UPDATE') then to_jsonb(new) else null end
    );

    if tg_op = 'DELETE' then
        return old;
    end if;

    return new;
end;
$$;


ALTER FUNCTION akulova_vs.fn_audit_log_changes() OWNER TO akulova_vs;

--
-- TOC entry 1772 (class 1255 OID 450327)
-- Name: fn_authenticate_user(text, text); Type: FUNCTION; Schema: akulova_vs; Owner: akulova_vs
--

CREATE FUNCTION akulova_vs.fn_authenticate_user(p_login text, p_password text) RETURNS TABLE(user_id integer, login character varying, role text, instructor_id integer, driver_id integer)
    LANGUAGE sql STABLE
    AS $$
    select
        au.user_id,
        au.login,
        au.role::text,
        au.instructor_id,
        au.driver_id
    from akulova_vs.app_users au
    where au.login = p_login
      and au.password_hash = public.crypt(p_password, au.password_hash)
      and au.is_active = true
    limit 1;
$$;


ALTER FUNCTION akulova_vs.fn_authenticate_user(p_login text, p_password text) OWNER TO akulova_vs;

--
-- TOC entry 1753 (class 1255 OID 448456)
-- Name: fn_hash_password(text); Type: FUNCTION; Schema: akulova_vs; Owner: akulova_vs
--

CREATE FUNCTION akulova_vs.fn_hash_password(p_password text) RETURNS text
    LANGUAGE sql
    AS $$
    select public.crypt(p_password, public.gen_salt('bf'));
$$;


ALTER FUNCTION akulova_vs.fn_hash_password(p_password text) OWNER TO akulova_vs;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- TOC entry 614 (class 1259 OID 448321)
-- Name: app_users; Type: TABLE; Schema: akulova_vs; Owner: akulova_vs
--

CREATE TABLE akulova_vs.app_users (
    user_id integer NOT NULL,
    login character varying(80) NOT NULL,
    password_hash text NOT NULL,
    role akulova_vs.app_role NOT NULL,
    is_active boolean DEFAULT true NOT NULL,
    instructor_id integer,
    driver_id integer,
    CONSTRAINT chk_app_users_user_has_driver CHECK ((((role = 'admin'::akulova_vs.app_role) AND (driver_id IS NULL)) OR ((role = 'user'::akulova_vs.app_role) AND (driver_id IS NOT NULL))))
);


ALTER TABLE akulova_vs.app_users OWNER TO akulova_vs;

--
-- TOC entry 613 (class 1259 OID 448320)
-- Name: app_users_user_id_seq; Type: SEQUENCE; Schema: akulova_vs; Owner: akulova_vs
--

ALTER TABLE akulova_vs.app_users ALTER COLUMN user_id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME akulova_vs.app_users_user_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- TOC entry 1003 (class 1259 OID 454349)
-- Name: audit_log; Type: TABLE; Schema: akulova_vs; Owner: akulova_vs
--

CREATE TABLE akulova_vs.audit_log (
    log_id integer NOT NULL,
    table_name character varying(80) NOT NULL,
    operation character varying(10) NOT NULL,
    record_id integer,
    changed_at timestamp without time zone DEFAULT now() NOT NULL,
    changed_by character varying(80) DEFAULT CURRENT_USER NOT NULL,
    old_data jsonb,
    new_data jsonb,
    CONSTRAINT audit_log_operation_check CHECK (((operation)::text = ANY ((ARRAY['INSERT'::character varying, 'UPDATE'::character varying, 'DELETE'::character varying])::text[])))
);


ALTER TABLE akulova_vs.audit_log OWNER TO akulova_vs;

--
-- TOC entry 1002 (class 1259 OID 454348)
-- Name: audit_log_log_id_seq; Type: SEQUENCE; Schema: akulova_vs; Owner: akulova_vs
--

ALTER TABLE akulova_vs.audit_log ALTER COLUMN log_id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME akulova_vs.audit_log_log_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- TOC entry 616 (class 1259 OID 448344)
-- Name: drivers; Type: TABLE; Schema: akulova_vs; Owner: akulova_vs
--

CREATE TABLE akulova_vs.drivers (
    driver_id integer NOT NULL,
    school_id integer NOT NULL,
    last_name character varying(80) NOT NULL,
    first_name character varying(80) NOT NULL,
    middle_name character varying(80),
    birth_date date NOT NULL,
    phone character varying(20),
    CONSTRAINT chk_drivers_birth_date_min_age CHECK ((birth_date <= ((CURRENT_DATE - '16 years'::interval))::date)),
    CONSTRAINT chk_drivers_phone_format CHECK (((phone IS NULL) OR ((phone)::text ~ '^\+?[0-9 ()-]{5,25}$'::text)))
);


ALTER TABLE akulova_vs.drivers OWNER TO akulova_vs;

--
-- TOC entry 615 (class 1259 OID 448343)
-- Name: drivers_driver_id_seq; Type: SEQUENCE; Schema: akulova_vs; Owner: akulova_vs
--

ALTER TABLE akulova_vs.drivers ALTER COLUMN driver_id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME akulova_vs.drivers_driver_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- TOC entry 610 (class 1259 OID 448294)
-- Name: driving_schools; Type: TABLE; Schema: akulova_vs; Owner: akulova_vs
--

CREATE TABLE akulova_vs.driving_schools (
    school_id integer NOT NULL,
    school_name character varying(150) NOT NULL,
    address text NOT NULL,
    phone character varying(20),
    CONSTRAINT chk_schools_phone_format CHECK (((phone IS NULL) OR ((phone)::text ~ '^\+?[0-9 ()-]{5,25}$'::text)))
);


ALTER TABLE akulova_vs.driving_schools OWNER TO akulova_vs;

--
-- TOC entry 609 (class 1259 OID 448293)
-- Name: driving_schools_school_id_seq; Type: SEQUENCE; Schema: akulova_vs; Owner: akulova_vs
--

ALTER TABLE akulova_vs.driving_schools ALTER COLUMN school_id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME akulova_vs.driving_schools_school_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- TOC entry 618 (class 1259 OID 448360)
-- Name: exam_routes; Type: TABLE; Schema: akulova_vs; Owner: akulova_vs
--

CREATE TABLE akulova_vs.exam_routes (
    route_id integer NOT NULL,
    route_name character varying(120) NOT NULL,
    start_address text NOT NULL,
    end_address text NOT NULL,
    difficulty character varying(30) NOT NULL,
    CONSTRAINT chk_exam_routes_difficulty CHECK (((difficulty)::text = ANY ((ARRAY['легкий'::character varying, 'средний'::character varying, 'сложный'::character varying])::text[])))
);


ALTER TABLE akulova_vs.exam_routes OWNER TO akulova_vs;

--
-- TOC entry 617 (class 1259 OID 448359)
-- Name: exam_routes_route_id_seq; Type: SEQUENCE; Schema: akulova_vs; Owner: akulova_vs
--

ALTER TABLE akulova_vs.exam_routes ALTER COLUMN route_id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME akulova_vs.exam_routes_route_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- TOC entry 624 (class 1259 OID 448393)
-- Name: exams; Type: TABLE; Schema: akulova_vs; Owner: akulova_vs
--

CREATE TABLE akulova_vs.exams (
    exam_id integer NOT NULL,
    driver_id integer NOT NULL,
    route_id integer NOT NULL,
    inspector_id integer NOT NULL,
    exam_date date NOT NULL,
    result character varying(20) NOT NULL,
    CONSTRAINT chk_exams_result CHECK (((result)::text = ANY ((ARRAY['сдан'::character varying, 'не сдан'::character varying])::text[])))
);


ALTER TABLE akulova_vs.exams OWNER TO akulova_vs;

--
-- TOC entry 623 (class 1259 OID 448392)
-- Name: exams_exam_id_seq; Type: SEQUENCE; Schema: akulova_vs; Owner: akulova_vs
--

ALTER TABLE akulova_vs.exams ALTER COLUMN exam_id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME akulova_vs.exams_exam_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- TOC entry 612 (class 1259 OID 448305)
-- Name: instructors; Type: TABLE; Schema: akulova_vs; Owner: akulova_vs
--

CREATE TABLE akulova_vs.instructors (
    instructor_id integer NOT NULL,
    school_id integer NOT NULL,
    last_name character varying(80) NOT NULL,
    first_name character varying(80) NOT NULL,
    middle_name character varying(80),
    phone character varying(20),
    license_number character varying(30) NOT NULL,
    CONSTRAINT chk_instructors_phone_format CHECK (((phone IS NULL) OR ((phone)::text ~ '^\+?[0-9 ()-]{5,25}$'::text)))
);


ALTER TABLE akulova_vs.instructors OWNER TO akulova_vs;

--
-- TOC entry 611 (class 1259 OID 448304)
-- Name: instructors_instructor_id_seq; Type: SEQUENCE; Schema: akulova_vs; Owner: akulova_vs
--

ALTER TABLE akulova_vs.instructors ALTER COLUMN instructor_id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME akulova_vs.instructors_instructor_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- TOC entry 626 (class 1259 OID 448420)
-- Name: licenses; Type: TABLE; Schema: akulova_vs; Owner: akulova_vs
--

CREATE TABLE akulova_vs.licenses (
    license_id integer NOT NULL,
    driver_id integer NOT NULL,
    officer_id integer NOT NULL,
    issue_date date NOT NULL,
    category character varying(10) NOT NULL,
    license_number character varying(30) NOT NULL,
    CONSTRAINT chk_licenses_category CHECK (((category)::text = ANY ((ARRAY['A'::character varying, 'B'::character varying, 'C'::character varying, 'D'::character varying, 'BE'::character varying, 'CE'::character varying, 'DE'::character varying])::text[])))
);


ALTER TABLE akulova_vs.licenses OWNER TO akulova_vs;

--
-- TOC entry 625 (class 1259 OID 448419)
-- Name: licenses_license_id_seq; Type: SEQUENCE; Schema: akulova_vs; Owner: akulova_vs
--

ALTER TABLE akulova_vs.licenses ALTER COLUMN license_id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME akulova_vs.licenses_license_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- TOC entry 620 (class 1259 OID 448373)
-- Name: traffic_inspectors; Type: TABLE; Schema: akulova_vs; Owner: akulova_vs
--

CREATE TABLE akulova_vs.traffic_inspectors (
    inspector_id integer NOT NULL,
    last_name character varying(80) NOT NULL,
    first_name character varying(80) NOT NULL,
    middle_name character varying(80),
    rank_name character varying(80) NOT NULL
);


ALTER TABLE akulova_vs.traffic_inspectors OWNER TO akulova_vs;

--
-- TOC entry 619 (class 1259 OID 448372)
-- Name: traffic_inspectors_inspector_id_seq; Type: SEQUENCE; Schema: akulova_vs; Owner: akulova_vs
--

ALTER TABLE akulova_vs.traffic_inspectors ALTER COLUMN inspector_id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME akulova_vs.traffic_inspectors_inspector_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- TOC entry 622 (class 1259 OID 448383)
-- Name: traffic_officers; Type: TABLE; Schema: akulova_vs; Owner: akulova_vs
--

CREATE TABLE akulova_vs.traffic_officers (
    officer_id integer NOT NULL,
    last_name character varying(80) NOT NULL,
    first_name character varying(80) NOT NULL,
    middle_name character varying(80),
    position_name character varying(120) NOT NULL
);


ALTER TABLE akulova_vs.traffic_officers OWNER TO akulova_vs;

--
-- TOC entry 621 (class 1259 OID 448382)
-- Name: traffic_officers_officer_id_seq; Type: SEQUENCE; Schema: akulova_vs; Owner: akulova_vs
--

ALTER TABLE akulova_vs.traffic_officers ALTER COLUMN officer_id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME akulova_vs.traffic_officers_officer_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- TOC entry 1001 (class 1259 OID 454343)
-- Name: v_exam_search; Type: VIEW; Schema: akulova_vs; Owner: akulova_vs
--

CREATE VIEW akulova_vs.v_exam_search AS
 SELECT e.exam_id,
    e.exam_date,
    e.result,
    concat_ws(' '::text, d.last_name, d.first_name, d.middle_name) AS driver_full_name,
    ds.school_name,
    er.route_name,
    concat_ws(' '::text, ti.last_name, ti.first_name, ti.middle_name) AS inspector_full_name,
    (row_number() OVER (PARTITION BY e.driver_id ORDER BY e.exam_date, e.exam_id))::integer AS attempt_number,
    (EXTRACT(year FROM age((e.exam_date)::timestamp with time zone, (d.birth_date)::timestamp with time zone)))::integer AS driver_age
   FROM ((((akulova_vs.exams e
     JOIN akulova_vs.drivers d ON ((d.driver_id = e.driver_id)))
     JOIN akulova_vs.driving_schools ds ON ((ds.school_id = d.school_id)))
     JOIN akulova_vs.exam_routes er ON ((er.route_id = e.route_id)))
     JOIN akulova_vs.traffic_inspectors ti ON ((ti.inspector_id = e.inspector_id)));


ALTER VIEW akulova_vs.v_exam_search OWNER TO akulova_vs;

--
-- TOC entry 6227 (class 0 OID 448321)
-- Dependencies: 614
-- Data for Name: app_users; Type: TABLE DATA; Schema: akulova_vs; Owner: akulova_vs
--

COPY akulova_vs.app_users (user_id, login, password_hash, role, is_active, instructor_id, driver_id) FROM stdin;
1	admin	$1$adm2026$f/c1i.QLTZq9f0yi58hF./	admin	t	1	\N
2	user	$1$usr2026$TH.zVKjQW4ADmsUOkhAi01	user	t	\N	1
3	ivanov	$1$ivanov26$RIsEw0mN69yf0AIDaIl/x0	user	t	\N	2
4	smirnova	$1$smirn26$AXwttYrGMEPAdMwyF7JGF/	user	t	\N	3
5	kuzmin	$1$kuzmin26$qc4zcHQl0zHs/XEZp1JfN/	user	t	\N	4
6	volkova	$1$volkov26$QVaboMFeUPclfBgauZxuE/	user	t	\N	5
\.


--
-- TOC entry 6241 (class 0 OID 454349)
-- Dependencies: 1003
-- Data for Name: audit_log; Type: TABLE DATA; Schema: akulova_vs; Owner: akulova_vs
--

COPY akulova_vs.audit_log (log_id, table_name, operation, record_id, changed_at, changed_by, old_data, new_data) FROM stdin;
3	traffic_officers	DELETE	7	2026-06-02 15:45:20.119893	admin	{"last_name": "Иванов", "first_name": "Иван", "officer_id": 7, "middle_name": "Иванович", "position_name": "специалист по выдаче удостоверений"}	\N
1	traffic_officers	INSERT	6	2026-06-02 15:11:15.206198	admin	\N	{"last_name": "Непоткупнов", "first_name": "Константин", "officer_id": 6, "middle_name": "Сергеевич", "position_name": "специалист по выдаче удостоверений"}
2	traffic_officers	INSERT	7	2026-06-02 15:38:00.496125	admin	\N	{"last_name": "Иванов", "first_name": "Иван", "officer_id": 7, "middle_name": "Иванович", "position_name": "специалист по выдаче удостоверений"}
4	driving_schools	INSERT	6	2026-06-02 16:54:29.853262	akulova_vs	\N	{"phone": "+ 7 8162 80-90-10", "address": "ул Гагарина, 37A", "school_id": 6, "school_name": "Автошкола Шанс"}
5	driving_schools	UPDATE	6	2026-06-02 16:54:39.890178	akulova_vs	{"phone": "+ 7 8162 80-90-10", "address": "ул Гагарина, 37A", "school_id": 6, "school_name": "Автошкола Шанс"}	{"phone": "+ 7 8162 80-90-20", "address": "ул Гагарина, 37A", "school_id": 6, "school_name": "Автошкола Шанс"}
6	driving_schools	DELETE	6	2026-06-02 16:54:45.676122	akulova_vs	{"phone": "+ 7 8162 80-90-20", "address": "ул Гагарина, 37A", "school_id": 6, "school_name": "Автошкола Шанс"}	\N
7	instructors	INSERT	9	2026-06-02 16:56:11.176451	akulova_vs	\N	{"phone": "+7 900 999-99-99", "last_name": "Т", "school_id": 4, "first_name": "Т", "middle_name": "Т", "instructor_id": 9, "license_number": "INS-009"}
8	instructors	UPDATE	9	2026-06-02 16:56:16.350856	akulova_vs	{"phone": "+7 900 999-99-99", "last_name": "Т", "school_id": 4, "first_name": "Т", "middle_name": "Т", "instructor_id": 9, "license_number": "INS-009"}	{"phone": "+7 900 999-99-99", "last_name": "Т", "school_id": 4, "first_name": "ТE", "middle_name": "Т", "instructor_id": 9, "license_number": "INS-009"}
9	instructors	DELETE	9	2026-06-02 16:56:19.916324	akulova_vs	{"phone": "+7 900 999-99-99", "last_name": "Т", "school_id": 4, "first_name": "ТE", "middle_name": "Т", "instructor_id": 9, "license_number": "INS-009"}	\N
10	drivers	INSERT	11	2026-06-02 16:57:11.182215	akulova_vs	\N	{"phone": "+ 7 900 600 45 45", "driver_id": 11, "last_name": "n", "school_id": 2, "birth_date": "2007-05-04", "first_name": "n", "middle_name": null}
11	drivers	DELETE	11	2026-06-02 16:57:24.148653	akulova_vs	{"phone": "+ 7 900 600 45 45", "driver_id": 11, "last_name": "n", "school_id": 2, "birth_date": "2007-05-04", "first_name": "n", "middle_name": null}	\N
12	drivers	UPDATE	6	2026-06-02 16:57:46.715882	akulova_vs	{"phone": "+7 900 999-00-11", "driver_id": 6, "last_name": "Морозова", "school_id": 5, "birth_date": "2003-01-25", "first_name": "Дарья", "middle_name": "Алексеевна"}	{"phone": "+7 900 999-00-11", "driver_id": 6, "last_name": "Морозова", "school_id": 5, "birth_date": "2007-01-25", "first_name": "Дарья", "middle_name": "Алексеевна"}
13	traffic_inspectors	UPDATE	1	2026-06-02 16:58:01.520372	akulova_vs	{"last_name": "Морозов", "rank_name": "старший лейтенант", "first_name": "Павел", "middle_name": "Сергеевич", "inspector_id": 1}	{"last_name": "Морозов", "rank_name": "майор", "first_name": "Павел", "middle_name": "Сергеевич", "inspector_id": 1}
14	traffic_inspectors	UPDATE	1	2026-06-02 17:08:03.177358	admin	{"last_name": "Морозов", "rank_name": "майор", "first_name": "Павел", "middle_name": "Сергеевич", "inspector_id": 1}	{"last_name": "Морозов", "rank_name": "старший лейтенант", "first_name": "Павел", "middle_name": "Сергеевич", "inspector_id": 1}
\.


--
-- TOC entry 6229 (class 0 OID 448344)
-- Dependencies: 616
-- Data for Name: drivers; Type: TABLE DATA; Schema: akulova_vs; Owner: akulova_vs
--

COPY akulova_vs.drivers (driver_id, school_id, last_name, first_name, middle_name, birth_date, phone) FROM stdin;
1	1	Петров	Илья	Андреевич	2001-05-20	+7 900 444-44-44
2	2	Иванов	Сергей	Петрович	2000-11-14	+7 900 555-55-55
3	3	Смирнова	Анна	Олеговна	1999-03-08	+7 900 666-66-66
4	1	Кузьмин	Даниил	Романович	2002-07-12	+7 900 777-77-77
5	4	Волкова	Екатерина	Ильинична	2001-12-02	+7 900 888-88-88
7	2	Лебедев	Максим	Викторович	1998-09-17	+7 901 111-22-33
8	3	Семенова	Полина	Дмитриевна	2004-04-10	+7 901 222-33-44
9	4	Зайцев	Никита	Павлович	2000-06-30	+7 901 333-44-55
10	5	Андреева	Алина	Сергеевна	2002-10-05	+7 901 444-55-66
6	5	Морозова	Дарья	Алексеевна	2007-01-25	+7 900 999-00-11
\.


--
-- TOC entry 6223 (class 0 OID 448294)
-- Dependencies: 610
-- Data for Name: driving_schools; Type: TABLE DATA; Schema: akulova_vs; Owner: akulova_vs
--

COPY akulova_vs.driving_schools (school_id, school_name, address, phone) FROM stdin;
1	Автошкола Старт	ул. Большая Московская, 12	+7 8162 10-20-30
2	Автошкола Вектор	ул. Псковская, 25	+7 8162 20-30-40
3	Автошкола Драйв	пр-т Мира, 8	+7 8162 30-40-50
4	Автошкола Профи	ул. Ломоносова, 18	+7 8162 40-50-60
5	Автошкола Магистраль	ул. Кочетова, 31	+7 8162 50-60-70
\.


--
-- TOC entry 6231 (class 0 OID 448360)
-- Dependencies: 618
-- Data for Name: exam_routes; Type: TABLE DATA; Schema: akulova_vs; Owner: akulova_vs
--

COPY akulova_vs.exam_routes (route_id, route_name, start_address, end_address, difficulty) FROM stdin;
1	Маршрут №1 - центр	ул. Большая Московская, 12	Сенная площадь	легкий
2	Маршрут №2 - город	ул. Псковская, 25	Воскресенский бульвар	средний
3	Маршрут №3 - сложный	пр-т Мира, 8	ул. Кочетова, 30	сложный
4	Маршрут №4 - учебная площадка	ул. Ломоносова, 18	ул. Нехинская, 57	легкий
5	Маршрут №5 - кольцевая развязка	ул. Кочетова, 31	пр-т Александра Корсунова, 14	средний
\.


--
-- TOC entry 6237 (class 0 OID 448393)
-- Dependencies: 624
-- Data for Name: exams; Type: TABLE DATA; Schema: akulova_vs; Owner: akulova_vs
--

COPY akulova_vs.exams (exam_id, driver_id, route_id, inspector_id, exam_date, result) FROM stdin;
2	2	2	2	2026-05-21	не сдан
3	3	3	3	2026-05-22	сдан
4	4	4	4	2026-05-23	не сдан
5	5	5	5	2026-05-24	сдан
6	6	1	2	2026-05-25	сдан
7	7	2	3	2026-05-26	не сдан
8	8	3	4	2026-05-27	сдан
9	9	4	5	2026-05-28	сдан
10	10	5	1	2026-05-29	не сдан
11	2	2	2	2026-06-05	сдан
12	4	4	4	2026-06-06	сдан
1	1	1	1	2026-05-20	сдан
\.


--
-- TOC entry 6225 (class 0 OID 448305)
-- Dependencies: 612
-- Data for Name: instructors; Type: TABLE DATA; Schema: akulova_vs; Owner: akulova_vs
--

COPY akulova_vs.instructors (instructor_id, school_id, last_name, first_name, middle_name, phone, license_number) FROM stdin;
1	1	Соколов	Алексей	Игоревич	+7 900 111-11-11	INS-001
2	2	Кузнецова	Марина	Олеговна	+7 900 222-22-22	INS-002
3	3	Орлов	Дмитрий	Павлович	+7 900 333-33-33	INS-003
4	1	Николаев	Артём	Сергеевич	+7 900 444-11-22	INS-004
5	4	Федорова	Ирина	Владимировна	+7 900 555-22-33	INS-005
6	5	Григорьев	Павел	Андреевич	+7 900 666-33-44	INS-006
7	2	Мельникова	Ольга	Петровна	+7 900 777-44-55	INS-007
8	3	Белов	Кирилл	Олегович	+7 900 888-55-66	INS-008
\.


--
-- TOC entry 6239 (class 0 OID 448420)
-- Dependencies: 626
-- Data for Name: licenses; Type: TABLE DATA; Schema: akulova_vs; Owner: akulova_vs
--

COPY akulova_vs.licenses (license_id, driver_id, officer_id, issue_date, category, license_number) FROM stdin;
1	1	1	2026-05-21	B	VU-2026-0001
2	3	3	2026-05-23	B	VU-2026-0002
3	5	5	2026-05-25	B	VU-2026-0003
4	6	2	2026-05-26	C	VU-2026-0004
5	8	4	2026-05-28	B	VU-2026-0005
6	9	5	2026-05-29	A	VU-2026-0006
7	2	2	2026-06-06	B	VU-2026-0007
8	4	4	2026-06-07	B	VU-2026-0008
\.


--
-- TOC entry 6233 (class 0 OID 448373)
-- Dependencies: 620
-- Data for Name: traffic_inspectors; Type: TABLE DATA; Schema: akulova_vs; Owner: akulova_vs
--

COPY akulova_vs.traffic_inspectors (inspector_id, last_name, first_name, middle_name, rank_name) FROM stdin;
2	Васильева	Елена	Александровна	капитан
3	Никитин	Роман	Игоревич	майор
4	Савельев	Олег	Владимирович	лейтенант
5	Крылова	Наталья	Павловна	подполковник
1	Морозов	Павел	Сергеевич	старший лейтенант
\.


--
-- TOC entry 6235 (class 0 OID 448383)
-- Dependencies: 622
-- Data for Name: traffic_officers; Type: TABLE DATA; Schema: akulova_vs; Owner: akulova_vs
--

COPY akulova_vs.traffic_officers (officer_id, last_name, first_name, middle_name, position_name) FROM stdin;
1	Фёдоров	Андрей	Викторович	инспектор регистрационно-экзаменационного отдела
2	Белова	Ольга	Дмитриевна	старший инспектор
3	Громов	Николай	Петрович	специалист по выдаче удостоверений
4	Соколова	Мария	Игоревна	инспектор регистрационно-экзаменационного отдела
5	Егоров	Владислав	Андреевич	старший инспектор
6	Непоткупнов	Константин	Сергеевич	специалист по выдаче удостоверений
\.


--
-- TOC entry 6247 (class 0 OID 0)
-- Dependencies: 613
-- Name: app_users_user_id_seq; Type: SEQUENCE SET; Schema: akulova_vs; Owner: akulova_vs
--

SELECT pg_catalog.setval('akulova_vs.app_users_user_id_seq', 6, true);


--
-- TOC entry 6248 (class 0 OID 0)
-- Dependencies: 1002
-- Name: audit_log_log_id_seq; Type: SEQUENCE SET; Schema: akulova_vs; Owner: akulova_vs
--

SELECT pg_catalog.setval('akulova_vs.audit_log_log_id_seq', 14, true);


--
-- TOC entry 6249 (class 0 OID 0)
-- Dependencies: 615
-- Name: drivers_driver_id_seq; Type: SEQUENCE SET; Schema: akulova_vs; Owner: akulova_vs
--

SELECT pg_catalog.setval('akulova_vs.drivers_driver_id_seq', 11, true);


--
-- TOC entry 6250 (class 0 OID 0)
-- Dependencies: 609
-- Name: driving_schools_school_id_seq; Type: SEQUENCE SET; Schema: akulova_vs; Owner: akulova_vs
--

SELECT pg_catalog.setval('akulova_vs.driving_schools_school_id_seq', 6, true);


--
-- TOC entry 6251 (class 0 OID 0)
-- Dependencies: 617
-- Name: exam_routes_route_id_seq; Type: SEQUENCE SET; Schema: akulova_vs; Owner: akulova_vs
--

SELECT pg_catalog.setval('akulova_vs.exam_routes_route_id_seq', 5, true);


--
-- TOC entry 6252 (class 0 OID 0)
-- Dependencies: 623
-- Name: exams_exam_id_seq; Type: SEQUENCE SET; Schema: akulova_vs; Owner: akulova_vs
--

SELECT pg_catalog.setval('akulova_vs.exams_exam_id_seq', 12, true);


--
-- TOC entry 6253 (class 0 OID 0)
-- Dependencies: 611
-- Name: instructors_instructor_id_seq; Type: SEQUENCE SET; Schema: akulova_vs; Owner: akulova_vs
--

SELECT pg_catalog.setval('akulova_vs.instructors_instructor_id_seq', 9, true);


--
-- TOC entry 6254 (class 0 OID 0)
-- Dependencies: 625
-- Name: licenses_license_id_seq; Type: SEQUENCE SET; Schema: akulova_vs; Owner: akulova_vs
--

SELECT pg_catalog.setval('akulova_vs.licenses_license_id_seq', 8, true);


--
-- TOC entry 6255 (class 0 OID 0)
-- Dependencies: 619
-- Name: traffic_inspectors_inspector_id_seq; Type: SEQUENCE SET; Schema: akulova_vs; Owner: akulova_vs
--

SELECT pg_catalog.setval('akulova_vs.traffic_inspectors_inspector_id_seq', 5, true);


--
-- TOC entry 6256 (class 0 OID 0)
-- Dependencies: 621
-- Name: traffic_officers_officer_id_seq; Type: SEQUENCE SET; Schema: akulova_vs; Owner: akulova_vs
--

SELECT pg_catalog.setval('akulova_vs.traffic_officers_officer_id_seq', 7, true);


--
-- TOC entry 5963 (class 2606 OID 450092)
-- Name: app_users app_users_driver_id_key; Type: CONSTRAINT; Schema: akulova_vs; Owner: akulova_vs
--

ALTER TABLE ONLY akulova_vs.app_users
    ADD CONSTRAINT app_users_driver_id_key UNIQUE (driver_id);


--
-- TOC entry 5965 (class 2606 OID 448337)
-- Name: app_users app_users_instructor_id_key; Type: CONSTRAINT; Schema: akulova_vs; Owner: akulova_vs
--

ALTER TABLE ONLY akulova_vs.app_users
    ADD CONSTRAINT app_users_instructor_id_key UNIQUE (instructor_id);


--
-- TOC entry 5967 (class 2606 OID 448335)
-- Name: app_users app_users_login_key; Type: CONSTRAINT; Schema: akulova_vs; Owner: akulova_vs
--

ALTER TABLE ONLY akulova_vs.app_users
    ADD CONSTRAINT app_users_login_key UNIQUE (login);


--
-- TOC entry 5969 (class 2606 OID 448333)
-- Name: app_users app_users_pkey; Type: CONSTRAINT; Schema: akulova_vs; Owner: akulova_vs
--

ALTER TABLE ONLY akulova_vs.app_users
    ADD CONSTRAINT app_users_pkey PRIMARY KEY (user_id);


--
-- TOC entry 5985 (class 2606 OID 454363)
-- Name: audit_log audit_log_pkey; Type: CONSTRAINT; Schema: akulova_vs; Owner: akulova_vs
--

ALTER TABLE ONLY akulova_vs.audit_log
    ADD CONSTRAINT audit_log_pkey PRIMARY KEY (log_id);


--
-- TOC entry 5971 (class 2606 OID 448353)
-- Name: drivers drivers_pkey; Type: CONSTRAINT; Schema: akulova_vs; Owner: akulova_vs
--

ALTER TABLE ONLY akulova_vs.drivers
    ADD CONSTRAINT drivers_pkey PRIMARY KEY (driver_id);


--
-- TOC entry 5957 (class 2606 OID 448303)
-- Name: driving_schools driving_schools_pkey; Type: CONSTRAINT; Schema: akulova_vs; Owner: akulova_vs
--

ALTER TABLE ONLY akulova_vs.driving_schools
    ADD CONSTRAINT driving_schools_pkey PRIMARY KEY (school_id);


--
-- TOC entry 5973 (class 2606 OID 448371)
-- Name: exam_routes exam_routes_pkey; Type: CONSTRAINT; Schema: akulova_vs; Owner: akulova_vs
--

ALTER TABLE ONLY akulova_vs.exam_routes
    ADD CONSTRAINT exam_routes_pkey PRIMARY KEY (route_id);


--
-- TOC entry 5979 (class 2606 OID 448403)
-- Name: exams exams_pkey; Type: CONSTRAINT; Schema: akulova_vs; Owner: akulova_vs
--

ALTER TABLE ONLY akulova_vs.exams
    ADD CONSTRAINT exams_pkey PRIMARY KEY (exam_id);


--
-- TOC entry 5959 (class 2606 OID 448314)
-- Name: instructors instructors_pkey; Type: CONSTRAINT; Schema: akulova_vs; Owner: akulova_vs
--

ALTER TABLE ONLY akulova_vs.instructors
    ADD CONSTRAINT instructors_pkey PRIMARY KEY (instructor_id);


--
-- TOC entry 5981 (class 2606 OID 448430)
-- Name: licenses licenses_pkey; Type: CONSTRAINT; Schema: akulova_vs; Owner: akulova_vs
--

ALTER TABLE ONLY akulova_vs.licenses
    ADD CONSTRAINT licenses_pkey PRIMARY KEY (license_id);


--
-- TOC entry 5975 (class 2606 OID 448381)
-- Name: traffic_inspectors traffic_inspectors_pkey; Type: CONSTRAINT; Schema: akulova_vs; Owner: akulova_vs
--

ALTER TABLE ONLY akulova_vs.traffic_inspectors
    ADD CONSTRAINT traffic_inspectors_pkey PRIMARY KEY (inspector_id);


--
-- TOC entry 5977 (class 2606 OID 448391)
-- Name: traffic_officers traffic_officers_pkey; Type: CONSTRAINT; Schema: akulova_vs; Owner: akulova_vs
--

ALTER TABLE ONLY akulova_vs.traffic_officers
    ADD CONSTRAINT traffic_officers_pkey PRIMARY KEY (officer_id);


--
-- TOC entry 5961 (class 2606 OID 454339)
-- Name: instructors uq_instructors_license_number; Type: CONSTRAINT; Schema: akulova_vs; Owner: akulova_vs
--

ALTER TABLE ONLY akulova_vs.instructors
    ADD CONSTRAINT uq_instructors_license_number UNIQUE (license_number);


--
-- TOC entry 5983 (class 2606 OID 454341)
-- Name: licenses uq_licenses_license_number; Type: CONSTRAINT; Schema: akulova_vs; Owner: akulova_vs
--

ALTER TABLE ONLY akulova_vs.licenses
    ADD CONSTRAINT uq_licenses_license_number UNIQUE (license_number);


--
-- TOC entry 5997 (class 2620 OID 454377)
-- Name: app_users trg_audit_app_users; Type: TRIGGER; Schema: akulova_vs; Owner: akulova_vs
--

CREATE TRIGGER trg_audit_app_users AFTER INSERT OR DELETE OR UPDATE ON akulova_vs.app_users FOR EACH ROW EXECUTE FUNCTION akulova_vs.fn_audit_log_changes('user_id');


--
-- TOC entry 5998 (class 2620 OID 454371)
-- Name: drivers trg_audit_drivers; Type: TRIGGER; Schema: akulova_vs; Owner: akulova_vs
--

CREATE TRIGGER trg_audit_drivers AFTER INSERT OR DELETE OR UPDATE ON akulova_vs.drivers FOR EACH ROW EXECUTE FUNCTION akulova_vs.fn_audit_log_changes('driver_id');


--
-- TOC entry 5995 (class 2620 OID 454369)
-- Name: driving_schools trg_audit_driving_schools; Type: TRIGGER; Schema: akulova_vs; Owner: akulova_vs
--

CREATE TRIGGER trg_audit_driving_schools AFTER INSERT OR DELETE OR UPDATE ON akulova_vs.driving_schools FOR EACH ROW EXECUTE FUNCTION akulova_vs.fn_audit_log_changes('school_id');


--
-- TOC entry 5999 (class 2620 OID 454372)
-- Name: exam_routes trg_audit_exam_routes; Type: TRIGGER; Schema: akulova_vs; Owner: akulova_vs
--

CREATE TRIGGER trg_audit_exam_routes AFTER INSERT OR DELETE OR UPDATE ON akulova_vs.exam_routes FOR EACH ROW EXECUTE FUNCTION akulova_vs.fn_audit_log_changes('route_id');


--
-- TOC entry 6002 (class 2620 OID 454375)
-- Name: exams trg_audit_exams; Type: TRIGGER; Schema: akulova_vs; Owner: akulova_vs
--

CREATE TRIGGER trg_audit_exams AFTER INSERT OR DELETE OR UPDATE ON akulova_vs.exams FOR EACH ROW EXECUTE FUNCTION akulova_vs.fn_audit_log_changes('exam_id');


--
-- TOC entry 5996 (class 2620 OID 454370)
-- Name: instructors trg_audit_instructors; Type: TRIGGER; Schema: akulova_vs; Owner: akulova_vs
--

CREATE TRIGGER trg_audit_instructors AFTER INSERT OR DELETE OR UPDATE ON akulova_vs.instructors FOR EACH ROW EXECUTE FUNCTION akulova_vs.fn_audit_log_changes('instructor_id');


--
-- TOC entry 6003 (class 2620 OID 454376)
-- Name: licenses trg_audit_licenses; Type: TRIGGER; Schema: akulova_vs; Owner: akulova_vs
--

CREATE TRIGGER trg_audit_licenses AFTER INSERT OR DELETE OR UPDATE ON akulova_vs.licenses FOR EACH ROW EXECUTE FUNCTION akulova_vs.fn_audit_log_changes('license_id');


--
-- TOC entry 6000 (class 2620 OID 454373)
-- Name: traffic_inspectors trg_audit_traffic_inspectors; Type: TRIGGER; Schema: akulova_vs; Owner: akulova_vs
--

CREATE TRIGGER trg_audit_traffic_inspectors AFTER INSERT OR DELETE OR UPDATE ON akulova_vs.traffic_inspectors FOR EACH ROW EXECUTE FUNCTION akulova_vs.fn_audit_log_changes('inspector_id');


--
-- TOC entry 6001 (class 2620 OID 454374)
-- Name: traffic_officers trg_audit_traffic_officers; Type: TRIGGER; Schema: akulova_vs; Owner: akulova_vs
--

CREATE TRIGGER trg_audit_traffic_officers AFTER INSERT OR DELETE OR UPDATE ON akulova_vs.traffic_officers FOR EACH ROW EXECUTE FUNCTION akulova_vs.fn_audit_log_changes('officer_id');


--
-- TOC entry 5987 (class 2606 OID 450086)
-- Name: app_users app_users_driver_id_fkey; Type: FK CONSTRAINT; Schema: akulova_vs; Owner: akulova_vs
--

ALTER TABLE ONLY akulova_vs.app_users
    ADD CONSTRAINT app_users_driver_id_fkey FOREIGN KEY (driver_id) REFERENCES akulova_vs.drivers(driver_id) ON UPDATE CASCADE ON DELETE RESTRICT;


--
-- TOC entry 5988 (class 2606 OID 448338)
-- Name: app_users app_users_instructor_id_fkey; Type: FK CONSTRAINT; Schema: akulova_vs; Owner: akulova_vs
--

ALTER TABLE ONLY akulova_vs.app_users
    ADD CONSTRAINT app_users_instructor_id_fkey FOREIGN KEY (instructor_id) REFERENCES akulova_vs.instructors(instructor_id);


--
-- TOC entry 5989 (class 2606 OID 448354)
-- Name: drivers drivers_school_id_fkey; Type: FK CONSTRAINT; Schema: akulova_vs; Owner: akulova_vs
--

ALTER TABLE ONLY akulova_vs.drivers
    ADD CONSTRAINT drivers_school_id_fkey FOREIGN KEY (school_id) REFERENCES akulova_vs.driving_schools(school_id);


--
-- TOC entry 5990 (class 2606 OID 448404)
-- Name: exams exams_driver_id_fkey; Type: FK CONSTRAINT; Schema: akulova_vs; Owner: akulova_vs
--

ALTER TABLE ONLY akulova_vs.exams
    ADD CONSTRAINT exams_driver_id_fkey FOREIGN KEY (driver_id) REFERENCES akulova_vs.drivers(driver_id);


--
-- TOC entry 5991 (class 2606 OID 448414)
-- Name: exams exams_inspector_id_fkey; Type: FK CONSTRAINT; Schema: akulova_vs; Owner: akulova_vs
--

ALTER TABLE ONLY akulova_vs.exams
    ADD CONSTRAINT exams_inspector_id_fkey FOREIGN KEY (inspector_id) REFERENCES akulova_vs.traffic_inspectors(inspector_id);


--
-- TOC entry 5992 (class 2606 OID 448409)
-- Name: exams exams_route_id_fkey; Type: FK CONSTRAINT; Schema: akulova_vs; Owner: akulova_vs
--

ALTER TABLE ONLY akulova_vs.exams
    ADD CONSTRAINT exams_route_id_fkey FOREIGN KEY (route_id) REFERENCES akulova_vs.exam_routes(route_id);


--
-- TOC entry 5986 (class 2606 OID 448315)
-- Name: instructors instructors_school_id_fkey; Type: FK CONSTRAINT; Schema: akulova_vs; Owner: akulova_vs
--

ALTER TABLE ONLY akulova_vs.instructors
    ADD CONSTRAINT instructors_school_id_fkey FOREIGN KEY (school_id) REFERENCES akulova_vs.driving_schools(school_id);


--
-- TOC entry 5993 (class 2606 OID 448431)
-- Name: licenses licenses_driver_id_fkey; Type: FK CONSTRAINT; Schema: akulova_vs; Owner: akulova_vs
--

ALTER TABLE ONLY akulova_vs.licenses
    ADD CONSTRAINT licenses_driver_id_fkey FOREIGN KEY (driver_id) REFERENCES akulova_vs.drivers(driver_id);


--
-- TOC entry 5994 (class 2606 OID 448436)
-- Name: licenses licenses_officer_id_fkey; Type: FK CONSTRAINT; Schema: akulova_vs; Owner: akulova_vs
--

ALTER TABLE ONLY akulova_vs.licenses
    ADD CONSTRAINT licenses_officer_id_fkey FOREIGN KEY (officer_id) REFERENCES akulova_vs.traffic_officers(officer_id);


-- Completed on 2026-06-02 17:20:16

--
-- PostgreSQL database dump complete
--

\unrestrict wUhTUEtP7H3sZbn0Sj8yBWOgI7rW8eGIJqHJvYsaK1avC0CCtslcJ40NJgPs7Rm