Dyachkov_Museum_PM04/dump.sql

--
-- PostgreSQL database dump
--

\restrict fMX8pe7fyPeennYB6eeK17lVC7rkBp0LxzYWCFMQgMflMYdB3BYjGmqxfiz2Aj2

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

-- Started on 2026-06-30 18:28:52 MSK

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 16 (class 2615 OID 442818)
-- Name: dyachkov_ya; Type: SCHEMA; Schema: -; Owner: dyachkov_ya
--

CREATE SCHEMA dyachkov_ya;


ALTER SCHEMA dyachkov_ya OWNER TO dyachkov_ya;

--
-- TOC entry 1403 (class 1255 OID 443423)
-- Name: add_user_with_phones(character varying, character varying, integer, integer, integer, character varying, real, integer, character varying, character varying); Type: PROCEDURE; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE PROCEDURE dyachkov_ya.add_user_with_phones(IN p_lastname character varying, IN p_name character varying, IN p_year_b integer, IN p_groups integer, IN p_age integer, IN p_category character varying DEFAULT NULL::character varying, IN p_debt real DEFAULT 0, IN p_level integer DEFAULT 1, IN p_home_phone character varying DEFAULT NULL::character varying, IN p_mobile_phone character varying DEFAULT NULL::character varying)
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_has_phone BOOLEAN;
BEGIN
    BEGIN
        -- Проверка наличия хотя бы одного телефона
        v_has_phone := (p_home_phone IS NOT NULL AND p_home_phone != '') OR
                       (p_mobile_phone IS NOT NULL AND p_mobile_phone != '');
        
        IF NOT v_has_phone THEN
            RAISE EXCEPTION 'Необходимо указать хотя бы один телефон (домашний или мобильный) для связи с читателем';
        END IF;
        
        -- Вставка пользователя
        INSERT INTO users (lastname, name, year_b, groups, age, category, debt, level, home_phone, mobile_phone)
        VALUES (p_lastname, p_name, p_year_b, p_groups, p_age, p_category, p_debt, p_level, p_home_phone, p_mobile_phone);
        
        COMMIT;
        RAISE NOTICE 'Пользователь "% %" успешно добавлен', p_lastname, p_name;
        RAISE NOTICE 'Телефоны: дом.%, моб.%', 
                     COALESCE(p_home_phone, 'не указан'), 
                     COALESCE(p_mobile_phone, 'не указан');
        
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
            RAISE NOTICE 'ОШИБКА: %', SQLERRM;
    END;
END;
$$;


ALTER PROCEDURE dyachkov_ya.add_user_with_phones(IN p_lastname character varying, IN p_name character varying, IN p_year_b integer, IN p_groups integer, IN p_age integer, IN p_category character varying, IN p_debt real, IN p_level integer, IN p_home_phone character varying, IN p_mobile_phone character varying) OWNER TO dyachkov_ya;

--
-- TOC entry 1404 (class 1255 OID 443424)
-- Name: authenticate_user(character varying, text); Type: FUNCTION; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE FUNCTION dyachkov_ya.authenticate_user(p_username character varying, p_password text) RETURNS TABLE(user_id integer, username character varying, decrypted_name text, role_name character varying, is_authenticated boolean)
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_user_record RECORD;
    v_decrypted_name TEXT;
BEGIN
    -- Поиск пользователя
    SELECT u.id, u.username, u.encrypted_name, u.password_hash, r.role_name
    INTO v_user_record
    FROM system_users u
    JOIN roles r ON u.role_id = r.id
    WHERE u.username = p_username AND u.is_active = TRUE;
    
    IF v_user_record.id IS NULL THEN
        RETURN QUERY SELECT NULL::INTEGER, NULL::VARCHAR, NULL::TEXT, NULL::VARCHAR, FALSE::BOOLEAN;
        RETURN;
    END IF;
    
    -- Проверка пароля
    IF v_user_record.password_hash = crypt(p_password, v_user_record.password_hash) THEN
        -- Расшифровываем имя
        BEGIN
            v_decrypted_name := convert_from(decrypt(decode(v_user_record.encrypted_name, 'base64'), 'master_key', 'aes'), 'UTF8');
        EXCEPTION WHEN OTHERS THEN
            v_decrypted_name := '[Ошибка расшифровки]';
        END;
        
        RETURN QUERY SELECT 
            v_user_record.id, 
            v_user_record.username, 
            v_decrypted_name, 
            v_user_record.role_name, 
            TRUE::BOOLEAN;
    ELSE
        RETURN QUERY SELECT NULL::INTEGER, NULL::VARCHAR, NULL::TEXT, NULL::VARCHAR, FALSE::BOOLEAN;
    END IF;
END;
$$;


ALTER FUNCTION dyachkov_ya.authenticate_user(p_username character varying, p_password text) OWNER TO dyachkov_ya;

--
-- TOC entry 1405 (class 1255 OID 443425)
-- Name: autonomous_log(integer, real, real); Type: FUNCTION; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE FUNCTION dyachkov_ya.autonomous_log(p_book_bshifr integer, p_old_price real, p_new_price real) RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
    -- Имитация автономной транзакции
    BEGIN
        INSERT INTO logs (bk, cena_old, cena_new)
        VALUES (p_book_bshifr, p_old_price, p_new_price);
        
        -- Фиксируем лог независимо от основной транзакции
        COMMIT;
        RAISE NOTICE 'Лог сохранен автономно: книга bshifr=%, цена %.2f -> %.2f', 
                     p_book_bshifr, p_old_price, p_new_price;
    EXCEPTION
        WHEN OTHERS THEN
            RAISE NOTICE 'Не удалось сохранить лог: %', SQLERRM;
    END;
END;
$$;


ALTER FUNCTION dyachkov_ya.autonomous_log(p_book_bshifr integer, p_old_price real, p_new_price real) OWNER TO dyachkov_ya;

--
-- TOC entry 1406 (class 1255 OID 443426)
-- Name: check_ticket_constraints(); Type: FUNCTION; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE FUNCTION dyachkov_ya.check_ticket_constraints() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
user_exists BOOLEAN;
book_exists BOOLEAN;
BEGIN
SELECT EXISTS (SELECT 1 FROM users WHERE id = NEW.user_id)
INTO user_exists;
IF NOT user_exists THEN
RAISE EXCEPTION 'Ошибка: Пользователь с ID % не существует в таблице users. Запись в ticket не может быть добавлена.', NEW.user_id;
END IF;
SELECT EXISTS (SELECT 1 FROM book WHERE bshifr = NEW.book_id)
INTO book_exists;
IF NOT book_exists THEN
RAISE EXCEPTION 'Ошибка: Книга с шифром % не существует в таблице book. Запись в ticket не может быть добавлена.', NEW.book_id;
END IF;
IF NEW.date_return IS NOT NULL AND NEW.date_of_issue IS NOT NULL THEN
IF NEW.date_return < NEW.date_of_issue THEN
RAISE EXCEPTION 'Ошибка: Дата возврата (%) не может быть раньше даты выдачи (%).', NEW.date_return, NEW.date_of_issue;
END IF;
END IF;
RETURN NEW;
END;
$$;


ALTER FUNCTION dyachkov_ya.check_ticket_constraints() OWNER TO dyachkov_ya;

--
-- TOC entry 1407 (class 1255 OID 443427)
-- Name: check_user_access(character varying, character varying); Type: FUNCTION; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE FUNCTION dyachkov_ya.check_user_access(p_username character varying, p_required_role character varying) RETURNS boolean
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_user_role VARCHAR(50);
    v_has_access BOOLEAN := FALSE;
BEGIN
    -- Начинаем транзакцию
    BEGIN
        SELECT r.role_name INTO v_user_role
        FROM system_users u
        JOIN roles r ON u.role_id = r.id
        WHERE u.username = p_username AND u.is_active = TRUE;
        
        -- Проверка роли (admin имеет полный доступ)
        IF v_user_role = 'admin' OR v_user_role = p_required_role THEN
            v_has_access := TRUE;
        END IF;
        
        -- Логируем попытку доступа (в реальной системе)
        INSERT INTO access_log (username, access_time, has_access, required_role)
        VALUES (p_username, CURRENT_TIMESTAMP, v_has_access, p_required_role);
        
        COMMIT;
    EXCEPTION WHEN OTHERS THEN
        ROLLBACK;
        RAISE NOTICE 'Ошибка при проверке доступа: %', SQLERRM;
        v_has_access := FALSE;
    END;
    
    RETURN v_has_access;
END;
$$;


ALTER FUNCTION dyachkov_ya.check_user_access(p_username character varying, p_required_role character varying) OWNER TO dyachkov_ya;

--
-- TOC entry 1408 (class 1255 OID 443428)
-- Name: check_user_by_email(text); Type: FUNCTION; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE FUNCTION dyachkov_ya.check_user_by_email(p_email text) RETURNS integer
    LANGUAGE plpgsql
    AS $$
DECLARE
    user_exists INTEGER;
    decrypted_email TEXT;
    user_record RECORD;
BEGIN
    user_exists := 0;
    
    -- Проходим по всем пользователям и расшифровываем email для сравнения
    FOR user_record IN SELECT id, email FROM users LOOP
        IF user_record.email IS NOT NULL THEN
            BEGIN
                decrypted_email := decrypt_email(user_record.email);
                IF decrypted_email = p_email THEN
                    user_exists := 1;
                    EXIT;
                END IF;
            EXCEPTION WHEN OTHERS THEN
                -- Пропускаем ошибки дешифрования
                CONTINUE;
            END;
        END IF;
    END LOOP;
    
    RETURN user_exists;
END;
$$;


ALTER FUNCTION dyachkov_ya.check_user_by_email(p_email text) OWNER TO dyachkov_ya;

--
-- TOC entry 1409 (class 1255 OID 443429)
-- Name: clean_string(text); Type: FUNCTION; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE FUNCTION dyachkov_ya.clean_string(input text) RETURNS text
    LANGUAGE plpgsql
    AS $$
BEGIN
    IF input IS NULL THEN
        RETURN NULL;
    END IF;
    -- Удаление лишних пробелов, замена нескольких пробелов на один
    RETURN TRIM(REGEXP_REPLACE(input, '\s+', ' ', 'g'));
END;
$$;


ALTER FUNCTION dyachkov_ya.clean_string(input text) OWNER TO dyachkov_ya;

--
-- TOC entry 1410 (class 1255 OID 443430)
-- Name: count_books_by_user(character varying, character varying); Type: FUNCTION; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE FUNCTION dyachkov_ya.count_books_by_user(p_lastname character varying, p_name character varying) RETURNS integer
    LANGUAGE plpgsql
    AS $$
DECLARE
books_count INTEGER := 0;
BEGIN
IF p_lastname IS NULL OR p_name IS NULL THEN
RAISE EXCEPTION 'Фамилия и имя пользователя не могут быть NULL.';
END IF;
SELECT COUNT(t.id)
INTO books_count
FROM ticket t
JOIN users u ON t.user_id = u.id
WHERE u.lastname = p_lastname AND u.name = p_name;
RETURN books_count;
END;
$$;


ALTER FUNCTION dyachkov_ya.count_books_by_user(p_lastname character varying, p_name character varying) OWNER TO dyachkov_ya;

--
-- TOC entry 1411 (class 1255 OID 443431)
-- Name: count_unique_readers_in_month(integer); Type: FUNCTION; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE FUNCTION dyachkov_ya.count_unique_readers_in_month(p_month integer) RETURNS bigint
    LANGUAGE plpgsql
    AS $$
DECLARE
readers_count BIGINT := 0;
BEGIN
IF p_month IS NULL OR p_month < 1 OR p_month > 12 THEN
RAISE EXCEPTION 'Номер месяца должен быть от 1 до 12.';
END IF;
SELECT COUNT(DISTINCT t.user_id)
INTO readers_count
FROM ticket t
WHERE EXTRACT(MONTH FROM t.date_of_issue) = p_month; -- EXTRACT(MONTH FROM ...) извлекает номер месяца из даты
RETURN readers_count;
END;
$$;


ALTER FUNCTION dyachkov_ya.count_unique_readers_in_month(p_month integer) OWNER TO dyachkov_ya;

--
-- TOC entry 1412 (class 1255 OID 443432)
-- Name: days_since_issue(integer); Type: FUNCTION; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE FUNCTION dyachkov_ya.days_since_issue(p_ticket_id integer) RETURNS integer
    LANGUAGE plpgsql
    AS $$
DECLARE
issue_date DATE;
days_elapsed INTEGER;
BEGIN
SELECT date_of_issue
INTO issue_date
FROM ticket
WHERE id = p_ticket_id;
IF NOT FOUND THEN
RAISE EXCEPTION 'Билет с ID % не найден.', p_ticket_id;
END IF;
days_elapsed := Kol_Dney(issue_date, CURRENT_DATE::date);
RETURN days_elapsed;
END;
$$;


ALTER FUNCTION dyachkov_ya.days_since_issue(p_ticket_id integer) OWNER TO dyachkov_ya;

--
-- TOC entry 1413 (class 1255 OID 443433)
-- Name: decrypt_data(text, text); Type: FUNCTION; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE FUNCTION dyachkov_ya.decrypt_data(p_encrypted text, p_key text DEFAULT 'default_key'::text) RETURNS text
    LANGUAGE plpgsql
    AS $$
DECLARE
    result TEXT;
BEGIN
    result := convert_from(decrypt(decode(p_encrypted, 'hex'), p_key, 'aes'), 'UTF8');
    RETURN result;
EXCEPTION WHEN OTHERS THEN
    RETURN 'Ошибка дешифрования';
END;
$$;


ALTER FUNCTION dyachkov_ya.decrypt_data(p_encrypted text, p_key text) OWNER TO dyachkov_ya;

--
-- TOC entry 1414 (class 1255 OID 443435)
-- Name: decrypt_email(text); Type: FUNCTION; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE FUNCTION dyachkov_ya.decrypt_email(encrypted_email text) RETURNS text
    LANGUAGE plpgsql
    AS $$
DECLARE
    decrypted_email TEXT;
BEGIN
    IF encrypted_email IS NULL THEN
        RETURN NULL;
    END IF;
    
    BEGIN
        decrypted_email := convert_from(decrypt(decode(encrypted_email, 'base64'), 'my_secret_key', 'aes'), 'UTF8');
        RETURN decrypted_email;
    EXCEPTION WHEN OTHERS THEN
        RETURN encrypted_email; -- Если не зашифровано, возвращаем как есть
    END;
END;
$$;


ALTER FUNCTION dyachkov_ya.decrypt_email(encrypted_email text) OWNER TO dyachkov_ya;

--
-- TOC entry 1415 (class 1255 OID 443436)
-- Name: delete_book(integer); Type: PROCEDURE; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE PROCEDURE dyachkov_ya.delete_book(IN p_bshifr integer)
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_title VARCHAR(25);
    v_vidano BOOLEAN;
BEGIN
    BEGIN
        -- Получаем информацию о книге
        SELECT title, vidano INTO v_title, v_vidano
        FROM book WHERE bshifr = p_bshifr;
        
        IF NOT FOUND THEN
            RAISE EXCEPTION 'Книга с bshifr=% не найдена', p_bshifr;
        END IF;
        
        -- Проверка: если книга на руках - запрещаем удаление
        IF v_vidano = TRUE THEN
            RAISE EXCEPTION 'Невозможно удалить книгу "%" - экземпляр находится на руках у читателя', v_title;
        END IF;
        
        -- Удаляем книгу
        DELETE FROM book WHERE bshifr = p_bshifr;
        
        COMMIT;
        RAISE NOTICE 'Книга "% (bshifr=%) успешно удалена', v_title, p_bshifr;
        
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
            RAISE NOTICE 'ОШИБКА: %', SQLERRM;
    END;
END;
$$;


ALTER PROCEDURE dyachkov_ya.delete_book(IN p_bshifr integer) OWNER TO dyachkov_ya;

--
-- TOC entry 1416 (class 1255 OID 443437)
-- Name: delete_reader(integer); Type: PROCEDURE; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE PROCEDURE dyachkov_ya.delete_reader(IN p_user_id integer)
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_books_count INTEGER;
    v_username VARCHAR(50);
    v_books_list TEXT;
BEGIN
    BEGIN
        -- Получаем имя пользователя
        SELECT lastname || ' ' || name INTO v_username
        FROM users WHERE id = p_user_id;
        
        IF NOT FOUND THEN
            RAISE EXCEPTION 'Пользователь с ID % не найден', p_user_id;
        END IF;
        
        -- Подсчет книг на руках
        SELECT COUNT(*) INTO v_books_count
        FROM ticket
        WHERE user_id = p_user_id 
          AND (date_of_return IS NULL OR date_of_return > CURRENT_DATE);
        
        -- Если есть книги - формируем список и выдаем ошибку
        IF v_books_count > 0 THEN
            SELECT STRING_AGG(b.title, ', ') INTO v_books_list
            FROM ticket t
            JOIN book b ON t.book_id = b.bshifr
            WHERE t.user_id = p_user_id 
              AND (t.date_of_return IS NULL OR t.date_of_return > CURRENT_DATE);
            
            RAISE EXCEPTION 'Невозможно удалить читателя "%". У него на руках % книг: %',
                            v_username, v_books_count, v_books_list;
        END IF;
        
        -- Удаляем пользователя
        DELETE FROM users WHERE id = p_user_id;
        
        COMMIT;
        RAISE NOTICE 'Читатель "% (ID: %) успешно удален', v_username, p_user_id;
        
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
            RAISE NOTICE 'ОШИБКА: %', SQLERRM;
    END;
END;
$$;


ALTER PROCEDURE dyachkov_ya.delete_reader(IN p_user_id integer) OWNER TO dyachkov_ya;

--
-- TOC entry 1417 (class 1255 OID 443438)
-- Name: encrypt_data(text, text); Type: FUNCTION; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE FUNCTION dyachkov_ya.encrypt_data(p_data text, p_key text DEFAULT 'default_key'::text) RETURNS text
    LANGUAGE plpgsql
    AS $$
BEGIN
    RETURN encode(encrypt(p_data::bytea, p_key, 'aes'), 'hex');
END;
$$;


ALTER FUNCTION dyachkov_ya.encrypt_data(p_data text, p_key text) OWNER TO dyachkov_ya;

--
-- TOC entry 1418 (class 1255 OID 443439)
-- Name: encrypt_email(); Type: FUNCTION; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE FUNCTION dyachkov_ya.encrypt_email() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    IF NEW.email IS NOT NULL AND NEW.email != '' THEN
        -- Используем симметричное шифрование с ключом 'secret_key'
        NEW.email := encode(encrypt(NEW.email::bytea, 'my_secret_key', 'aes'), 'base64');
    END IF;
    RETURN NEW;
END;
$$;


ALTER FUNCTION dyachkov_ya.encrypt_email() OWNER TO dyachkov_ya;

--
-- TOC entry 1419 (class 1255 OID 443440)
-- Name: generate_random_id_for_usernew(); Type: FUNCTION; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE FUNCTION dyachkov_ya.generate_random_id_for_usernew() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
    new_id INTEGER;
    is_unique BOOLEAN := FALSE;
    max_attempts INTEGER := 100; -- Количество попыток генерации ID
    current_attempt INTEGER := 0;
BEGIN
    -- Если ID уже предоставлен (НЕ NULL), оставляем его.
    IF NEW.id IS NOT NULL THEN
        RETURN NEW;
    END IF;

    -- Генерируем новый ID, пока он не станет уникальным.
    WHILE NOT is_unique LOOP
        -- Генерируем случайное число (диапазон 1 - 1000000).
        new_id := floor(random() * 1000000 + 1);

        -- Проверяем уникальность ID.
        IF NOT EXISTS (SELECT 1 FROM usernew WHERE id = new_id) THEN
            is_unique := TRUE;
        END IF;
        
        current_attempt := current_attempt + 1;
        -- Защита от бесконечного цикла.
        IF current_attempt > max_attempts THEN
            RAISE EXCEPTION 'Не удалось сгенерировать уникальный ID для usernew после % попыток.', max_attempts;
        END IF;
    END LOOP;
    
    -- Присваиваем сгенерированный ID.
    NEW.id := new_id;
    
    RETURN NEW;
END;
$$;


ALTER FUNCTION dyachkov_ya.generate_random_id_for_usernew() OWNER TO dyachkov_ya;

--
-- TOC entry 1420 (class 1255 OID 443441)
-- Name: generate_sentence_from_dict(integer, text); Type: FUNCTION; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE FUNCTION dyachkov_ya.generate_sentence_from_dict(p_word_count integer, p_separator text DEFAULT ' '::text) RETURNS text
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_words TEXT[];
    v_sentence TEXT := '';
    v_capitalized_word TEXT;
    i INTEGER;
BEGIN
    -- Получаем случайные слова
    v_words := get_random_words(p_word_count);
    
    -- Формируем предложение
    FOR i IN 1..array_length(v_words, 1) LOOP
        IF i = 1 THEN
            -- Первое слово с заглавной буквы
            v_sentence := v_sentence || initcap(v_words[i]);
        ELSE
            v_sentence := v_sentence || p_separator || lower(v_words[i]);
        END IF;
    END LOOP;
    
    -- Добавляем точку в конце
    v_sentence := v_sentence || '.';
    
    RETURN v_sentence;
END;
$$;


ALTER FUNCTION dyachkov_ya.generate_sentence_from_dict(p_word_count integer, p_separator text) OWNER TO dyachkov_ya;

--
-- TOC entry 1421 (class 1255 OID 443442)
-- Name: generate_uuid_and_time(); Type: FUNCTION; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE FUNCTION dyachkov_ya.generate_uuid_and_time() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    IF NEW.uuid IS NULL THEN
        NEW.uuid := gen_random_uuid();
    END IF;
    NEW.time_created := CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$;


ALTER FUNCTION dyachkov_ya.generate_uuid_and_time() OWNER TO dyachkov_ya;

--
-- TOC entry 1849 (class 1255 OID 444742)
-- Name: get_exhibit_count_by_author(integer); Type: FUNCTION; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE FUNCTION dyachkov_ya.get_exhibit_count_by_author(author_id integer) RETURNS integer
    LANGUAGE plpgsql
    AS $$
DECLARE
    exhibit_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO exhibit_count
    FROM Exhibit
    WHERE id_creator = author_id;
    
    RETURN exhibit_count;
END;
$$;


ALTER FUNCTION dyachkov_ya.get_exhibit_count_by_author(author_id integer) OWNER TO dyachkov_ya;

--
-- TOC entry 1422 (class 1255 OID 443443)
-- Name: get_most_expensive_book_by_author(character varying); Type: FUNCTION; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE FUNCTION dyachkov_ya.get_most_expensive_book_by_author(p_author_lastname character varying) RETURNS character varying
    LANGUAGE plpgsql
    AS $$
DECLARE
book_title_result VARCHAR(255);
BEGIN
IF p_author_lastname IS NULL THEN
RAISE EXCEPTION 'Фамилия автора не может быть NULL.';
END IF;
SELECT b.title
INTO book_title_result
FROM book b
JOIN author a ON b.id_author = a.id
WHERE a.lastname = p_author_lastname 
ORDER BY b.price DESC 
LIMIT 1; 
IF NOT FOUND THEN
RAISE NOTICE 'Автор "%" не найден или у него нет книг в базе.', p_author_lastname;
RETURN NULL; -- Возвращаем NULL, если ничего не найдено.
END IF;
RETURN book_title_result;
END;
$$;


ALTER FUNCTION dyachkov_ya.get_most_expensive_book_by_author(p_author_lastname character varying) OWNER TO dyachkov_ya;

--
-- TOC entry 1423 (class 1255 OID 443444)
-- Name: get_random_sentence(integer); Type: FUNCTION; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE FUNCTION dyachkov_ya.get_random_sentence(word_count integer DEFAULT 5) RETURNS text
    LANGUAGE plpgsql
    AS $$
DECLARE
    words TEXT[];
    sentence TEXT := '';
    i INTEGER;
BEGIN
    words := get_random_words(word_count);
    
    FOR i IN 1..array_length(words, 1) LOOP
        IF i > 1 THEN
            sentence := sentence || ' ';
        END IF;
        sentence := sentence || words[i];
    END LOOP;
    
    RETURN sentence || '.';
END;
$$;


ALTER FUNCTION dyachkov_ya.get_random_sentence(word_count integer) OWNER TO dyachkov_ya;

--
-- TOC entry 1424 (class 1255 OID 443445)
-- Name: get_random_words(integer); Type: FUNCTION; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE FUNCTION dyachkov_ya.get_random_words(word_count integer DEFAULT 1) RETURNS text[]
    LANGUAGE plpgsql
    AS $$
DECLARE
    words_array TEXT[];
    result_array TEXT[] := '{}';
    i INTEGER;
    random_index INTEGER;
BEGIN
    -- Пытаемся прочитать файл со словами
    BEGIN
        SELECT string_to_array(pg_read_file('/usr/share/dict/words', 0, 1000000), E'\n') INTO words_array;
    EXCEPTION WHEN OTHERS THEN
        -- Если файл не доступен, используем встроенный словарь
        words_array := ARRAY['apple', 'table', 'chair', 'house', 'computer', 'program', 'database', 'security', 'encryption', 'hash'];
    END;
    
    -- Проверяем, что массив не пустой
    IF array_length(words_array, 1) IS NULL THEN
        words_array := ARRAY['apple', 'table', 'chair', 'house', 'computer'];
    END IF;
    
    -- Выбираем случайные слова
    FOR i IN 1..word_count LOOP
        random_index := 1 + floor(random() * array_length(words_array, 1))::INTEGER;
        result_array := array_append(result_array, words_array[random_index]);
    END LOOP;
    
    RETURN result_array;
END;
$$;


ALTER FUNCTION dyachkov_ya.get_random_words(word_count integer) OWNER TO dyachkov_ya;

--
-- TOC entry 1425 (class 1255 OID 443446)
-- Name: get_ticket_details_refcursor(integer); Type: FUNCTION; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE FUNCTION dyachkov_ya.get_ticket_details_refcursor(p_ticket_id integer) RETURNS refcursor
    LANGUAGE plpgsql
    AS $$
DECLARE
ticket_cur REFCURSOR;
BEGIN
IF p_ticket_id IS NULL THEN
RAISE EXCEPTION 'ID билета не может быть NULL.';
END IF;
OPEN ticket_cur FOR
SELECT
u.lastname AS user_lastname,
b.title AS book_title,
t.date_of_issue AS ticket_date
FROM ticket t
JOIN users u ON t.user_id = u.id
JOIN book b ON t.book_id = b.bshifr
WHERE t.id = p_ticket_id;
RETURN ticket_cur;
END;
$$;


ALTER FUNCTION dyachkov_ya.get_ticket_details_refcursor(p_ticket_id integer) OWNER TO dyachkov_ya;

--
-- TOC entry 1838 (class 1255 OID 444504)
-- Name: getaverageratingforexhibit(integer); Type: FUNCTION; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE FUNCTION dyachkov_ya.getaverageratingforexhibit(exhibit_id integer) RETURNS numeric
    LANGUAGE plpgsql
    AS $$
DECLARE
    avg_rating DECIMAL(3,2);
BEGIN
    SELECT AVG(exhibit_rating) INTO avg_rating
    FROM Viewing
    WHERE id_exhibit = exhibit_id;
    
    RETURN COALESCE(avg_rating, 0);
END;
$$;


ALTER FUNCTION dyachkov_ya.getaverageratingforexhibit(exhibit_id integer) OWNER TO dyachkov_ya;

--
-- TOC entry 1836 (class 1255 OID 444502)
-- Name: getexhibitcountbyauthor(integer); Type: FUNCTION; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE FUNCTION dyachkov_ya.getexhibitcountbyauthor(author_id integer) RETURNS integer
    LANGUAGE plpgsql
    AS $$
DECLARE
    exhibit_count INT;
BEGIN
    SELECT COUNT(*) INTO exhibit_count
    FROM Exhibit
    WHERE id_creator = author_id;
    
    RETURN exhibit_count;
END;
$$;


ALTER FUNCTION dyachkov_ya.getexhibitcountbyauthor(author_id integer) OWNER TO dyachkov_ya;

--
-- TOC entry 1837 (class 1255 OID 444503)
-- Name: getexhibitsbyauthor(integer); Type: FUNCTION; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE FUNCTION dyachkov_ya.getexhibitsbyauthor(author_id integer) RETURNS TABLE(id_exhibit integer, type character varying, creation_date date, copy_price numeric, creation_cost numeric)
    LANGUAGE plpgsql
    AS $$
BEGIN
    RETURN QUERY
    SELECT e.id_exhibit, e.type, e.creation_date, e.copy_price, e.creation_cost
    FROM Exhibit e
    WHERE e.id_creator = author_id;
END;
$$;


ALTER FUNCTION dyachkov_ya.getexhibitsbyauthor(author_id integer) OWNER TO dyachkov_ya;

--
-- TOC entry 1840 (class 1255 OID 444506)
-- Name: getmuseumtotalvalue(integer); Type: FUNCTION; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE FUNCTION dyachkov_ya.getmuseumtotalvalue(museum_id integer) RETURNS numeric
    LANGUAGE plpgsql
    AS $$
DECLARE
    total_value DECIMAL(15,2);
BEGIN
    SELECT SUM(e.copy_price) INTO total_value
    FROM Exhibit e
    WHERE e.id_museum = museum_id;
    
    RETURN COALESCE(total_value, 0);
END;
$$;


ALTER FUNCTION dyachkov_ya.getmuseumtotalvalue(museum_id integer) OWNER TO dyachkov_ya;

--
-- TOC entry 1839 (class 1255 OID 444505)
-- Name: gettop5expensiveexhibits(); Type: FUNCTION; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE FUNCTION dyachkov_ya.gettop5expensiveexhibits() RETURNS TABLE(id_exhibit integer, type character varying, copy_price numeric, author_name character varying)
    LANGUAGE plpgsql
    AS $$
BEGIN
    RETURN QUERY
    SELECT e.id_exhibit, e.type, e.copy_price, c.full_name
    FROM Exhibit e
    JOIN Creator c ON e.id_creator = c.id_creator
    ORDER BY e.copy_price DESC
    LIMIT 5;
END;
$$;


ALTER FUNCTION dyachkov_ya.gettop5expensiveexhibits() OWNER TO dyachkov_ya;

--
-- TOC entry 1426 (class 1255 OID 443447)
-- Name: hash_password(text, text); Type: FUNCTION; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE FUNCTION dyachkov_ya.hash_password(p_password text, p_salt text DEFAULT NULL::text) RETURNS text
    LANGUAGE plpgsql
    AS $$
DECLARE
    salt TEXT;
    hashed_password TEXT;
BEGIN
    -- Генерируем соль, если не передана
    IF p_salt IS NULL THEN
        salt := gen_salt('bf'); -- Blowfish алгоритм
    ELSE
        salt := p_salt;
    END IF;
    
    -- Создаём хэш пароля
    hashed_password := crypt(p_password, salt);
    RETURN hashed_password;
END;
$$;


ALTER FUNCTION dyachkov_ya.hash_password(p_password text, p_salt text) OWNER TO dyachkov_ya;

--
-- TOC entry 1427 (class 1255 OID 443448)
-- Name: increase_price_by_cursor(); Type: PROCEDURE; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE PROCEDURE dyachkov_ya.increase_price_by_cursor()
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_book RECORD;
    v_new_price REAL;
    v_increase REAL := 20; -- 20%
    v_max_price REAL := 10000;
    v_processed INTEGER := 0;
    v_updated INTEGER := 0;
BEGIN
    -- Курсор для книг авторов, родившихся менее 1950 года
    FOR v_book IN 
        SELECT b.bshifr, b.title, b.price, a.lastname, a.name, a.year_b
        FROM book b
        JOIN author a ON b.id_author = a.id
        WHERE a.year_b < 1950
    LOOP
        -- Создаем точку сохранения для каждой книги
        PERFORM 1;
        
        BEGIN
            -- Вычисляем новую цену (+20%)
            v_new_price := v_book.price * (1 + v_increase / 100);
            
            RAISE NOTICE 'Книга: "%", Автор: % % (род. %), Цена: % -> %',
                        v_book.title, v_book.lastname, v_book.name, 
                        v_book.year_b, v_book.price, v_new_price;
            
            -- Проверка: не превышает ли новая цена 10000
            IF v_new_price <= v_max_price THEN
                UPDATE book SET price = v_new_price WHERE bshifr = v_book.bshifr;
                v_updated := v_updated + 1;
                RAISE NOTICE '  -> ЦЕНА УВЕЛИЧЕНА';
            ELSE
                -- Цена не изменяется, просто пропускаем
                RAISE NOTICE '  -> ЦЕНА НЕ ИЗМЕНЕНА (%.2f превысила бы лимит %.0f)', v_new_price, v_max_price;
            END IF;
            
            v_processed := v_processed + 1;
            
        EXCEPTION
            WHEN OTHERS THEN
                -- При ошибке просто пропускаем книгу
                RAISE NOTICE '  -> ОШИБКА при обработке книги "%": %', v_book.title, SQLERRM;
        END;
    END LOOP;
    
    RAISE NOTICE '=========================================';
    RAISE NOTICE 'Обработано книг: %, Обновлено: %', v_processed, v_updated;
    RAISE NOTICE '=========================================';
END;
$$;


ALTER PROCEDURE dyachkov_ya.increase_price_by_cursor() OWNER TO dyachkov_ya;

--
-- TOC entry 1428 (class 1255 OID 443449)
-- Name: issue_book(integer, integer, date); Type: PROCEDURE; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE PROCEDURE dyachkov_ya.issue_book(IN p_user_id integer, IN p_book_bshifr integer, IN p_date_issue date)
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_books_count INTEGER;
    v_title VARCHAR(25);
    v_username VARCHAR(50);
    v_available BOOLEAN;
BEGIN
    BEGIN
        -- Получаем имя пользователя
        SELECT lastname || ' ' || name INTO v_username
        FROM users WHERE id = p_user_id;
        
        IF NOT FOUND THEN
            RAISE EXCEPTION 'Пользователь с ID % не найден', p_user_id;
        END IF;
        
        -- Получаем информацию о книге
        SELECT title, vidano INTO v_title, v_available
        FROM book WHERE bshifr = p_book_bshifr;
        
        IF NOT FOUND THEN
            RAISE EXCEPTION 'Книга с bshifr=% не найдена', p_book_bshifr;
        END IF;
        
        -- Проверка доступности книги
        IF v_available = TRUE THEN
            RAISE EXCEPTION 'Книга "%" уже выдана другому читателю', v_title;
        END IF;
        
        -- Подсчет книг на руках у читателя
        SELECT COUNT(*) INTO v_books_count
        FROM ticket
        WHERE user_id = p_user_id 
          AND (date_of_return IS NULL OR date_of_return > CURRENT_DATE);
        
        RAISE NOTICE 'У читателя "%" на руках % книг', v_username, v_books_count;
        
        -- Проверка лимита (не более 3)
        IF v_books_count >= 3 THEN
            RAISE EXCEPTION 'Читатель "%" уже имеет % книг на руках (максимум 3)', 
                            v_username, v_books_count;
        END IF;
        
        -- Выдача книги
        INSERT INTO ticket (user_id, book_id, date_of_issue, date_of_return)
        VALUES (p_user_id, p_book_bshifr, p_date_issue, NULL);
        
        UPDATE book SET vidano = TRUE WHERE bshifr = p_book_bshifr;
        
        COMMIT;
        RAISE NOTICE 'Книга "%" выдана читателю "%"', v_title, v_username;
        
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
            RAISE NOTICE 'ОШИБКА: %', SQLERRM;
    END;
END;
$$;


ALTER PROCEDURE dyachkov_ya.issue_book(IN p_user_id integer, IN p_book_bshifr integer, IN p_date_issue date) OWNER TO dyachkov_ya;

--
-- TOC entry 1429 (class 1255 OID 443450)
-- Name: kol_dney(date, date); Type: FUNCTION; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE FUNCTION dyachkov_ya.kol_dney(date1 date, date2 date) RETURNS integer
    LANGUAGE plpgsql
    AS $$
DECLARE
    num_days INTEGER;
BEGIN
    IF date1 IS NULL OR date2 IS NULL THEN
        RAISE EXCEPTION 'Даты не могут быть NULL.';
    END IF;

    -- В PostgreSQL разница между двумя DATE автоматически возвращает количество дней.
    num_days := date2 - date1;

    RETURN num_days; -- Возвращаем разницу в днях
END;
$$;


ALTER FUNCTION dyachkov_ya.kol_dney(date1 date, date2 date) OWNER TO dyachkov_ya;

--
-- TOC entry 1430 (class 1255 OID 443451)
-- Name: month_name_by_number(integer); Type: FUNCTION; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE FUNCTION dyachkov_ya.month_name_by_number(month_num integer) RETURNS character varying
    LANGUAGE plpgsql
    AS $$
BEGIN
IF month_num IS NULL OR month_num < 1 OR month_num > 12 THEN
RAISE EXCEPTION 'Номер месяца должен быть от 1 до 12.';
END IF;
RETURN CASE month_num
WHEN 1 THEN 'Январь'
WHEN 2 THEN 'Февраль'
WHEN 3 THEN 'Март'
WHEN 4 THEN 'Апрель'
WHEN 5 THEN 'Май'
WHEN 6 THEN 'Июнь'
WHEN 7 THEN 'Июль'
WHEN 8 THEN 'Август'
WHEN 9 THEN 'Сентябрь'
WHEN 10 THEN 'Октябрь'
WHEN 11 THEN 'Ноябрь'
WHEN 12 THEN 'Декабрь'
ELSE 'Неизвестный месяц'
END;
END;
$$;


ALTER FUNCTION dyachkov_ya.month_name_by_number(month_num integer) OWNER TO dyachkov_ya;

--
-- TOC entry 1431 (class 1255 OID 443452)
-- Name: multi_hash(text, integer); Type: FUNCTION; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE FUNCTION dyachkov_ya.multi_hash(p_value text, p_iterations integer DEFAULT 1) RETURNS text
    LANGUAGE plpgsql
    AS $$
DECLARE
    hashed_value TEXT;
    i INTEGER;
BEGIN
    IF p_value IS NULL THEN
        RETURN NULL;
    END IF;
    
    hashed_value := p_value;
    
    FOR i IN 1..p_iterations LOOP
        hashed_value := encode(digest(hashed_value, 'sha256'), 'hex');
    END LOOP;
    
    RETURN hashed_value;
END;
$$;


ALTER FUNCTION dyachkov_ya.multi_hash(p_value text, p_iterations integer) OWNER TO dyachkov_ya;

--
-- TOC entry 1432 (class 1255 OID 443453)
-- Name: normalize_region(text); Type: FUNCTION; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE FUNCTION dyachkov_ya.normalize_region(region_name text) RETURNS text
    LANGUAGE plpgsql
    AS $$
BEGIN
    RETURN CASE
        WHEN region_name ILIKE '%Санкт-Петербург%' OR region_name ILIKE '%СПб%' THEN 'Санкт-Петербург'
        WHEN region_name ILIKE '%Ленинградская%' THEN 'Ленинградская область'
        WHEN region_name ILIKE '%Новгород%' THEN 'Новгородская область'
        ELSE clean_string(region_name)
    END;
END;
$$;


ALTER FUNCTION dyachkov_ya.normalize_region(region_name text) OWNER TO dyachkov_ya;

--
-- TOC entry 1433 (class 1255 OID 443454)
-- Name: nvl(anyelement, anyelement); Type: FUNCTION; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE FUNCTION dyachkov_ya.nvl(input_value anyelement, default_value anyelement) RETURNS anyelement
    LANGUAGE plpgsql
    AS $$
BEGIN
    RETURN COALESCE(input_value, default_value);
END;
$$;


ALTER FUNCTION dyachkov_ya.nvl(input_value anyelement, default_value anyelement) OWNER TO dyachkov_ya;

--
-- TOC entry 1434 (class 1255 OID 443455)
-- Name: parse_salary(text); Type: FUNCTION; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE FUNCTION dyachkov_ya.parse_salary(salary_text text) RETURNS TABLE(min_salary numeric, max_salary numeric)
    LANGUAGE plpgsql
    AS $$
DECLARE
    numbers NUMERIC[];
BEGIN
    IF salary_text IS NULL THEN
        min_salary := NULL;
        max_salary := NULL;
        RETURN NEXT;
        RETURN;
    END IF;
    
    -- Извлечение чисел из строки
    SELECT ARRAY(
        SELECT REGEXP_MATCHES(salary_text, '(\d+[.,]?\d*)', 'g')
    ) INTO numbers;
    
    IF array_length(numbers, 1) >= 1 THEN
        min_salary := numbers[1]::NUMERIC;
        IF array_length(numbers, 1) >= 2 THEN
            max_salary := numbers[2]::NUMERIC;
        ELSE
            max_salary := min_salary;
        END IF;
    ELSE
        min_salary := NULL;
        max_salary := NULL;
    END IF;
    
    RETURN NEXT;
END;
$$;


ALTER FUNCTION dyachkov_ya.parse_salary(salary_text text) OWNER TO dyachkov_ya;

--
-- TOC entry 1435 (class 1255 OID 443456)
-- Name: proizv(integer); Type: FUNCTION; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE FUNCTION dyachkov_ya.proizv(n integer) RETURNS bigint
    LANGUAGE plpgsql
    AS $$
DECLARE
    i INTEGER;
    total_product BIGINT := 1; -- Инициализируем произведение единицей
BEGIN
    IF n IS NULL OR n <= 0 THEN
        RAISE EXCEPTION 'Параметр n должен быть положительным целым числом.';
    END IF;

    -- Цикл от 1 до n
    FOR i IN 1..n LOOP
        total_product := total_product * i; -- Умножаем текущее число на произведение
    END LOOP;

    RETURN total_product; -- Возвращаем вычисленное произведение
END;
$$;


ALTER FUNCTION dyachkov_ya.proizv(n integer) OWNER TO dyachkov_ya;

--
-- TOC entry 1436 (class 1255 OID 443457)
-- Name: proverka(integer, integer, date, date); Type: PROCEDURE; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE PROCEDURE dyachkov_ya.proverka(IN kodu integer, IN kodb integer, IN dvid date, IN dvoz date)
    LANGUAGE plpgsql
    AS $$
DECLARE
    rez VARCHAR(10) := 'ОК';
    pr INTEGER;
    v_last_id INTEGER;
BEGIN
    -- Начинаем транзакцию
    BEGIN
        -- Ввод записи в таблицу ticket (она же bilet)
        INSERT INTO ticket (user_id, book_id, date_of_issue, date_of_return)
        VALUES (kodU, kodB, dVid, dVoz)
        RETURNING id INTO v_last_id;
        
        RAISE NOTICE 'Вставлена запись с ID: %', v_last_id;
        
        -- Вычисляем разницу между датой возврата и датой выдачи
        SELECT (date_of_return - date_of_issue) INTO pr
        FROM ticket WHERE id = v_last_id;
        
        RAISE NOTICE 'Разница между датами: % дней', pr;
        
        -- Проверка: если разница > 0, то фиксируем, иначе откатываем
        IF pr > 0 THEN
            COMMIT;
            rez := 'ОК';
            RAISE NOTICE 'Дата возврата корректна. Транзакция зафиксирована.';
        ELSE
            ROLLBACK;
            rez := 'No';
            RAISE NOTICE 'ОШИБКА: дата возврата (%) меньше или равна дате выдачи (%)', dVoz, dVid;
            RAISE NOTICE 'Транзакция откачена.';
        END IF;
        
        -- Выводим значение переменной rez
        RAISE NOTICE 'Результат: %', rez;
        
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
            rez := 'Error';
            RAISE NOTICE 'Ошибка при выполнении: %', SQLERRM;
    END;
END;
$$;


ALTER PROCEDURE dyachkov_ya.proverka(IN kodu integer, IN kodb integer, IN dvid date, IN dvoz date) OWNER TO dyachkov_ya;

--
-- TOC entry 1437 (class 1255 OID 443458)
-- Name: register_user(character varying, text, text, character varying); Type: FUNCTION; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE FUNCTION dyachkov_ya.register_user(p_username character varying, p_name text, p_password text, p_role_name character varying DEFAULT 'user'::character varying) RETURNS integer
    LANGUAGE plpgsql
    AS $$
DECLARE
    v_salt TEXT;
    v_role_id INTEGER;
    v_encrypted_name TEXT;
    v_user_id INTEGER;
BEGIN
    -- Получаем ID роли
    SELECT id INTO v_role_id FROM roles WHERE role_name = p_role_name;
    IF v_role_id IS NULL THEN
        RAISE EXCEPTION 'Роль "%" не существует', p_role_name;
    END IF;
    
    -- Генерируем соль
    v_salt := gen_salt('bf');
    
    -- Шифруем имя пользователя (используем AES)
    v_encrypted_name := encode(encrypt(p_name::bytea, 'master_key', 'aes'), 'base64');
    
    -- Вставляем пользователя
    INSERT INTO system_users (username, encrypted_name, password_hash, salt, role_id)
    VALUES (p_username, v_encrypted_name, crypt(p_password, v_salt), v_salt, v_role_id)
    RETURNING id INTO v_user_id;
    
    RETURN v_user_id;
EXCEPTION WHEN unique_violation THEN
    RAISE EXCEPTION 'Пользователь с именем "%" уже существует', p_username;
END;
$$;


ALTER FUNCTION dyachkov_ya.register_user(p_username character varying, p_name text, p_password text, p_role_name character varying) OWNER TO dyachkov_ya;

--
-- TOC entry 1438 (class 1255 OID 443459)
-- Name: summa(integer); Type: FUNCTION; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE FUNCTION dyachkov_ya.summa(n integer) RETURNS bigint
    LANGUAGE plpgsql
    AS $$
DECLARE
    i INTEGER;
    total_sum BIGINT := 0; -- Инициализируем сумму нулем
BEGIN
    IF n IS NULL OR n <= 0 THEN
        RAISE EXCEPTION 'Параметр n должен быть положительным целым числом.';
    END IF;

    -- Цикл от 1 до n
    FOR i IN 1..n LOOP
        total_sum := total_sum + i; -- Добавляем текущее число к сумме
    END LOOP;

    RETURN total_sum; -- Возвращаем вычисленную сумму
END;
$$;


ALTER FUNCTION dyachkov_ya.summa(n integer) OWNER TO dyachkov_ya;

--
-- TOC entry 1439 (class 1255 OID 443460)
-- Name: tek_vr(integer); Type: PROCEDURE; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE PROCEDURE dyachkov_ya.tek_vr(IN n integer)
    LANGUAGE plpgsql
    AS $$
DECLARE
    i INTEGER;
BEGIN
    IF n IS NULL OR n <= 0 THEN
        RAISE EXCEPTION 'Параметр N должен быть положительным числом.';
    END IF;

    RAISE NOTICE '--- Вывод текущего времени % раз ---', n;
    
    FOR i IN 1..n LOOP
        -- NOW() возвращает текущую дату и время
        RAISE NOTICE 'Итерация %: Текущее время: %', i, NOW();
    END LOOP;
END;
$$;


ALTER PROCEDURE dyachkov_ya.tek_vr(IN n integer) OWNER TO dyachkov_ya;

--
-- TOC entry 1440 (class 1255 OID 443461)
-- Name: ticket_insert_trigger(); Type: FUNCTION; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE FUNCTION dyachkov_ya.ticket_insert_trigger() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
IF ( NEW.date_of_issue >= DATE '2024-01-01' AND NEW.date_of_issue < DATE '2024-02-01' ) THEN INSERT INTO ticket2024m1 VALUES (NEW.*);
ELSIF ( NEW.date_of_issue >= DATE '2024-02-01' AND NEW.date_of_issue < DATE '2024-03-01' ) THEN INSERT INTO ticket2024m2 VALUES (NEW.*);
ELSIF ( NEW.date_of_issue >= DATE '2024-03-01' AND NEW.date_of_issue < DATE '2024-04-01' ) THEN INSERT INTO ticket2024m3 VALUES (NEW.*);
ELSIF ( NEW.date_of_issue >= DATE '2024-04-01' AND NEW.date_of_issue < DATE '2024-05-01' ) THEN INSERT INTO ticket2024m4 VALUES (NEW.*);
ELSIF ( NEW.date_of_issue >= DATE '2024-05-01' AND NEW.date_of_issue < DATE '2024-06-01' ) THEN INSERT INTO ticket2024m5 VALUES (NEW.*);
ELSIF ( NEW.date_of_issue >= DATE '2024-06-01' AND NEW.date_of_issue < DATE '2024-07-01' ) THEN INSERT INTO ticket2024m6 VALUES (NEW.*);
ELSIF ( NEW.date_of_issue >= DATE '2024-07-01' AND NEW.date_of_issue < DATE '2024-08-01' ) THEN INSERT INTO ticket2024m7 VALUES (NEW.*);
ELSIF ( NEW.date_of_issue >= DATE '2024-08-01' AND NEW.date_of_issue < DATE '2024-09-01' ) THEN INSERT INTO ticket2024m8 VALUES (NEW.*);
ELSIF ( NEW.date_of_issue >= DATE '2024-09-01' AND NEW.date_of_issue < DATE '2024-10-01' ) THEN INSERT INTO ticket2024m9 VALUES (NEW.*);
ELSIF ( NEW.date_of_issue >= DATE '2024-10-01' AND NEW.date_of_issue < DATE '2024-11-01' ) THEN INSERT INTO ticket2024m10 VALUES (NEW.*);
ELSIF ( NEW.date_of_issue >= DATE '2024-11-01' AND NEW.date_of_issue < DATE '2024-12-01' ) THEN INSERT INTO ticket2024m11 VALUES (NEW.*);
ELSIF ( NEW.date_of_issue >= DATE '2024-12-01' AND NEW.date_of_issue < DATE '2025-01-01' ) THEN INSERT INTO ticket2024m12 VALUES (NEW.*);
ELSE RAISE EXCEPTION 'Date out of range for all partitions';
END IF;
RETURN NULL;
END;
$$;


ALTER FUNCTION dyachkov_ya.ticket_insert_trigger() OWNER TO dyachkov_ya;

--
-- TOC entry 1441 (class 1255 OID 443462)
-- Name: track_price_change(); Type: FUNCTION; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE FUNCTION dyachkov_ya.track_price_change() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    -- Если цена изменилась и новая цена > 1000 рублей
    IF OLD.price IS DISTINCT FROM NEW.price AND NEW.price > 1000 THEN
        -- Автономное логирование (сохранится даже при ROLLBACK)
        PERFORM autonomous_log(NEW.bshifr, OLD.price, NEW.price);
        
        RAISE NOTICE 'Зафиксирована попытка изменения цены книги bshifr=%: % -> %', 
                     NEW.bshifr, OLD.price, NEW.price;
    END IF;
    
    RETURN NEW;
END;
$$;


ALTER FUNCTION dyachkov_ya.track_price_change() OWNER TO dyachkov_ya;

--
-- TOC entry 1841 (class 1255 OID 444522)
-- Name: update_museum_profit(); Type: FUNCTION; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE FUNCTION dyachkov_ya.update_museum_profit() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
    -- Обновляем прибыль музея при добавлении нового экспоната
    UPDATE Museum
    SET profit = profit + (NEW.copy_price - NEW.creation_cost)
    WHERE id_museum = NEW.id_museum;
    
    RETURN NEW;
END;
$$;


ALTER FUNCTION dyachkov_ya.update_museum_profit() OWNER TO dyachkov_ya;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- TOC entry 720 (class 1259 OID 450407)
-- Name: auditlog; Type: TABLE; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE TABLE dyachkov_ya.auditlog (
    id_log integer NOT NULL,
    id_user integer,
    action character varying(255),
    table_name character varying(100),
    record_id integer,
    action_time timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    ip_address character varying(45),
    details text
);


ALTER TABLE dyachkov_ya.auditlog OWNER TO dyachkov_ya;

--
-- TOC entry 719 (class 1259 OID 450406)
-- Name: auditlog_id_log_seq; Type: SEQUENCE; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE SEQUENCE dyachkov_ya.auditlog_id_log_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE dyachkov_ya.auditlog_id_log_seq OWNER TO dyachkov_ya;

--
-- TOC entry 6622 (class 0 OID 0)
-- Dependencies: 719
-- Name: auditlog_id_log_seq; Type: SEQUENCE OWNED BY; Schema: dyachkov_ya; Owner: dyachkov_ya
--

ALTER SEQUENCE dyachkov_ya.auditlog_id_log_seq OWNED BY dyachkov_ya.auditlog.id_log;


--
-- TOC entry 325 (class 1259 OID 444440)
-- Name: creator; Type: TABLE; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE TABLE dyachkov_ya.creator (
    id_creator integer NOT NULL,
    full_name character varying(100) NOT NULL,
    royalty numeric(10,2),
    phone character varying(20),
    address character varying(200)
);


ALTER TABLE dyachkov_ya.creator OWNER TO dyachkov_ya;

--
-- TOC entry 327 (class 1259 OID 444460)
-- Name: exhibit; Type: TABLE; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE TABLE dyachkov_ya.exhibit (
    id_exhibit integer NOT NULL,
    type character varying(50),
    creation_date date,
    copy_price numeric(10,2),
    creation_cost numeric(10,2),
    id_creator integer,
    id_museum integer
);


ALTER TABLE dyachkov_ya.exhibit OWNER TO dyachkov_ya;

--
-- TOC entry 324 (class 1259 OID 444434)
-- Name: museum; Type: TABLE; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE TABLE dyachkov_ya.museum (
    id_museum integer NOT NULL,
    area numeric(10,2),
    profit numeric(12,2),
    phone character varying(20),
    address character varying(200)
);


ALTER TABLE dyachkov_ya.museum OWNER TO dyachkov_ya;

--
-- TOC entry 716 (class 1259 OID 450373)
-- Name: roles; Type: TABLE; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE TABLE dyachkov_ya.roles (
    id_role integer NOT NULL,
    role_name character varying(50) NOT NULL,
    description text
);


ALTER TABLE dyachkov_ya.roles OWNER TO dyachkov_ya;

--
-- TOC entry 715 (class 1259 OID 450372)
-- Name: roles_id_role_seq; Type: SEQUENCE; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE SEQUENCE dyachkov_ya.roles_id_role_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE dyachkov_ya.roles_id_role_seq OWNER TO dyachkov_ya;

--
-- TOC entry 6623 (class 0 OID 0)
-- Dependencies: 715
-- Name: roles_id_role_seq; Type: SEQUENCE OWNED BY; Schema: dyachkov_ya; Owner: dyachkov_ya
--

ALTER SEQUENCE dyachkov_ya.roles_id_role_seq OWNED BY dyachkov_ya.roles.id_role;


--
-- TOC entry 718 (class 1259 OID 450386)
-- Name: users; Type: TABLE; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE TABLE dyachkov_ya.users (
    id_user integer NOT NULL,
    username character varying(50) NOT NULL,
    password_hash character varying(255) NOT NULL,
    salt character varying(100) NOT NULL,
    id_role integer NOT NULL,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    last_login timestamp without time zone,
    is_active boolean DEFAULT true
);


ALTER TABLE dyachkov_ya.users OWNER TO dyachkov_ya;

--
-- TOC entry 717 (class 1259 OID 450385)
-- Name: users_id_user_seq; Type: SEQUENCE; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE SEQUENCE dyachkov_ya.users_id_user_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE dyachkov_ya.users_id_user_seq OWNER TO dyachkov_ya;

--
-- TOC entry 6624 (class 0 OID 0)
-- Dependencies: 717
-- Name: users_id_user_seq; Type: SEQUENCE OWNED BY; Schema: dyachkov_ya; Owner: dyachkov_ya
--

ALTER SEQUENCE dyachkov_ya.users_id_user_seq OWNED BY dyachkov_ya.users.id_user;


--
-- TOC entry 328 (class 1259 OID 444476)
-- Name: viewing; Type: TABLE; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE TABLE dyachkov_ya.viewing (
    id_ticket integer NOT NULL,
    id_exhibit integer NOT NULL,
    exhibit_rating integer,
    CONSTRAINT viewing_exhibit_rating_check CHECK (((exhibit_rating >= 1) AND (exhibit_rating <= 5)))
);


ALTER TABLE dyachkov_ya.viewing OWNER TO dyachkov_ya;

--
-- TOC entry 330 (class 1259 OID 444512)
-- Name: v_exhibit_popularity; Type: VIEW; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE VIEW dyachkov_ya.v_exhibit_popularity AS
 SELECT e.id_exhibit,
    e.type,
    count(v.id_ticket) AS views_count,
    avg(v.exhibit_rating) AS average_rating
   FROM (dyachkov_ya.exhibit e
     LEFT JOIN dyachkov_ya.viewing v ON ((e.id_exhibit = v.id_exhibit)))
  GROUP BY e.id_exhibit, e.type
  ORDER BY (avg(v.exhibit_rating)) DESC;


ALTER VIEW dyachkov_ya.v_exhibit_popularity OWNER TO dyachkov_ya;

--
-- TOC entry 329 (class 1259 OID 444507)
-- Name: v_exhibits_with_authors; Type: VIEW; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE VIEW dyachkov_ya.v_exhibits_with_authors AS
 SELECT e.id_exhibit,
    e.type,
    e.creation_date,
    e.copy_price,
    e.creation_cost,
    c.full_name AS author_name,
    m.address AS museum_location
   FROM ((dyachkov_ya.exhibit e
     LEFT JOIN dyachkov_ya.creator c ON ((e.id_creator = c.id_creator)))
     LEFT JOIN dyachkov_ya.museum m ON ((e.id_museum = m.id_museum)));


ALTER VIEW dyachkov_ya.v_exhibits_with_authors OWNER TO dyachkov_ya;

--
-- TOC entry 326 (class 1259 OID 444447)
-- Name: visitor; Type: TABLE; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE TABLE dyachkov_ya.visitor (
    id_ticket integer NOT NULL,
    full_name character varying(100) NOT NULL,
    phone character varying(20),
    visit_date date,
    museum_rating integer,
    id_museum integer,
    CONSTRAINT visitor_museum_rating_check CHECK (((museum_rating >= 1) AND (museum_rating <= 5)))
);


ALTER TABLE dyachkov_ya.visitor OWNER TO dyachkov_ya;

--
-- TOC entry 331 (class 1259 OID 444517)
-- Name: v_museum_statistics; Type: VIEW; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE VIEW dyachkov_ya.v_museum_statistics AS
 SELECT m.id_museum,
    m.address,
    count(DISTINCT e.id_exhibit) AS exhibits_count,
    count(DISTINCT v.id_ticket) AS visitors_count,
    sum(e.copy_price) AS total_exhibits_value
   FROM ((dyachkov_ya.museum m
     LEFT JOIN dyachkov_ya.exhibit e ON ((m.id_museum = e.id_museum)))
     LEFT JOIN dyachkov_ya.visitor v ON ((m.id_museum = v.id_museum)))
  GROUP BY m.id_museum, m.address;


ALTER VIEW dyachkov_ya.v_museum_statistics OWNER TO dyachkov_ya;

--
-- TOC entry 6329 (class 2604 OID 450410)
-- Name: auditlog id_log; Type: DEFAULT; Schema: dyachkov_ya; Owner: dyachkov_ya
--

ALTER TABLE ONLY dyachkov_ya.auditlog ALTER COLUMN id_log SET DEFAULT nextval('dyachkov_ya.auditlog_id_log_seq'::regclass);


--
-- TOC entry 6325 (class 2604 OID 450376)
-- Name: roles id_role; Type: DEFAULT; Schema: dyachkov_ya; Owner: dyachkov_ya
--

ALTER TABLE ONLY dyachkov_ya.roles ALTER COLUMN id_role SET DEFAULT nextval('dyachkov_ya.roles_id_role_seq'::regclass);


--
-- TOC entry 6326 (class 2604 OID 450389)
-- Name: users id_user; Type: DEFAULT; Schema: dyachkov_ya; Owner: dyachkov_ya
--

ALTER TABLE ONLY dyachkov_ya.users ALTER COLUMN id_user SET DEFAULT nextval('dyachkov_ya.users_id_user_seq'::regclass);


--
-- TOC entry 6616 (class 0 OID 450407)
-- Dependencies: 720
-- Data for Name: auditlog; Type: TABLE DATA; Schema: dyachkov_ya; Owner: dyachkov_ya
--

COPY dyachkov_ya.auditlog (id_log, id_user, action, table_name, record_id, action_time, ip_address, details) FROM stdin;
\.


--
-- TOC entry 6607 (class 0 OID 444440)
-- Dependencies: 325
-- Data for Name: creator; Type: TABLE DATA; Schema: dyachkov_ya; Owner: dyachkov_ya
--

COPY dyachkov_ya.creator (id_creator, full_name, royalty, phone, address) FROM stdin;
1	Иван Иванов	10000.00	+7-111-1111111	ул. Пушкина, 1
2	Мария Петрова	15000.00	+7-222-2222222	ул. Лермонтова, 2
3	Алексей Сидоров	12000.00	+7-333-3333333	ул. Гоголя, 3
4	Елена Васильева	18000.00	+7-444-4444444	ул. Толстого, 4
\.


--
-- TOC entry 6609 (class 0 OID 444460)
-- Dependencies: 327
-- Data for Name: exhibit; Type: TABLE DATA; Schema: dyachkov_ya; Owner: dyachkov_ya
--

COPY dyachkov_ya.exhibit (id_exhibit, type, creation_date, copy_price, creation_cost, id_creator, id_museum) FROM stdin;
101	Живопись	2020-01-15	200000.00	50000.00	1	1
102	Скульптура	2019-10-20	150000.00	40000.00	2	1
103	Графика	2021-05-10	80000.00	20000.00	1	2
104	Живопись	2018-07-22	250000.00	60000.00	3	1
105	Фотография	2022-03-15	50000.00	10000.00	2	2
106	Скульптура	2020-11-30	120000.00	35000.00	4	3
107	Икона	2017-09-05	300000.00	80000.00	1	1
108	Графика	2019-12-18	50000.00	15000.00	3	3
\.


--
-- TOC entry 6606 (class 0 OID 444434)
-- Dependencies: 324
-- Data for Name: museum; Type: TABLE DATA; Schema: dyachkov_ya; Owner: dyachkov_ya
--

COPY dyachkov_ya.museum (id_museum, area, profit, phone, address) FROM stdin;
1	1200.50	500000.00	+7-123-4567890	ул. Тверская, 5, Москва
3	1500.00	750000.00	+7-555-1234567	ул. Ленина, 15, Екатеринбург
2	800.00	400000.00	+7-987-6543210	пр. Невский, 10, Санкт-Петербург
\.


--
-- TOC entry 6612 (class 0 OID 450373)
-- Dependencies: 716
-- Data for Name: roles; Type: TABLE DATA; Schema: dyachkov_ya; Owner: dyachkov_ya
--

COPY dyachkov_ya.roles (id_role, role_name, description) FROM stdin;
1	Admin	Полный доступ ко всем функциям
2	Curator	Управление экспонатами и авторами
3	Cashier	Работа с посетителями
4	Guest	Только просмотр
\.


--
-- TOC entry 6614 (class 0 OID 450386)
-- Dependencies: 718
-- Data for Name: users; Type: TABLE DATA; Schema: dyachkov_ya; Owner: dyachkov_ya
--

COPY dyachkov_ya.users (id_user, username, password_hash, salt, id_role, created_at, last_login, is_active) FROM stdin;
9	curator	1bc936e86f9f3f3082b9e81e5489639dda3661522011f0586d74630d30aa9c2f	wkcZ1ZKs7I9Fkrhs4V6P6uM0kx0IYbY8aRyYfa06+mE=	2	2026-06-01 16:30:13.375361	\N	t
10	cashier	7f3c6eac18259fab2b0916dcd958ecbd6c5a7e1e11823fd64d23388852c57445	Yomt7dErmMWuTyE/Ut3HMN1gzzXuF9y0YFL8yVVObOU=	3	2026-06-01 16:30:13.402044	\N	t
11	guest	c09bbc66d7d00cbd91441685c4f0a23811d59d2d57754fc0b82f7271df245428	dAO2P8AgdohaItFZI4JpZ8PH/JH/AXbXJELFl4DPEmY=	4	2026-06-01 16:30:13.433068	\N	t
8	admin	119e41ff69e6c9fd2133b72169dd666a9caf9d24765483274de2730f00d85f5a	WLdeT8d1JEiq//inGEmb83MFydypm84/dldG8Bg366k=	1	2026-06-01 16:30:13.324373	2026-06-29 00:42:40.906973	t
\.


--
-- TOC entry 6610 (class 0 OID 444476)
-- Dependencies: 328
-- Data for Name: viewing; Type: TABLE DATA; Schema: dyachkov_ya; Owner: dyachkov_ya
--

COPY dyachkov_ya.viewing (id_ticket, id_exhibit, exhibit_rating) FROM stdin;
1001	101	5
1001	102	4
1001	103	5
1002	103	4
1002	105	5
1003	101	5
1003	104	4
1003	107	5
1004	106	3
1004	108	4
1005	103	5
1005	105	4
1006	101	5
1006	102	5
1006	104	4
\.


--
-- TOC entry 6608 (class 0 OID 444447)
-- Dependencies: 326
-- Data for Name: visitor; Type: TABLE DATA; Schema: dyachkov_ya; Owner: dyachkov_ya
--

COPY dyachkov_ya.visitor (id_ticket, full_name, phone, visit_date, museum_rating, id_museum) FROM stdin;
1001	Петр Сидоров	+7-123-4567890	2023-06-01	5	1
1002	Анна Ким	+7-234-5678901	2023-06-02	4	2
1003	Олег Иванов	+7-345-6789012	2023-06-03	5	1
1004	Наталья Смирнова	+7-456-7890123	2023-06-04	3	3
1005	Дмитрий Петров	+7-567-8901234	2023-06-05	4	2
1006	Екатерина Кузнецова	+7-678-9012345	2023-06-06	5	1
\.


--
-- TOC entry 6625 (class 0 OID 0)
-- Dependencies: 719
-- Name: auditlog_id_log_seq; Type: SEQUENCE SET; Schema: dyachkov_ya; Owner: dyachkov_ya
--

SELECT pg_catalog.setval('dyachkov_ya.auditlog_id_log_seq', 1, false);


--
-- TOC entry 6626 (class 0 OID 0)
-- Dependencies: 715
-- Name: roles_id_role_seq; Type: SEQUENCE SET; Schema: dyachkov_ya; Owner: dyachkov_ya
--

SELECT pg_catalog.setval('dyachkov_ya.roles_id_role_seq', 9, true);


--
-- TOC entry 6627 (class 0 OID 0)
-- Dependencies: 717
-- Name: users_id_user_seq; Type: SEQUENCE SET; Schema: dyachkov_ya; Owner: dyachkov_ya
--

SELECT pg_catalog.setval('dyachkov_ya.users_id_user_seq', 11, true);


--
-- TOC entry 6352 (class 2606 OID 450416)
-- Name: auditlog auditlog_pkey; Type: CONSTRAINT; Schema: dyachkov_ya; Owner: dyachkov_ya
--

ALTER TABLE ONLY dyachkov_ya.auditlog
    ADD CONSTRAINT auditlog_pkey PRIMARY KEY (id_log);


--
-- TOC entry 6336 (class 2606 OID 444446)
-- Name: creator creator_pkey; Type: CONSTRAINT; Schema: dyachkov_ya; Owner: dyachkov_ya
--

ALTER TABLE ONLY dyachkov_ya.creator
    ADD CONSTRAINT creator_pkey PRIMARY KEY (id_creator);


--
-- TOC entry 6340 (class 2606 OID 444465)
-- Name: exhibit exhibit_pkey; Type: CONSTRAINT; Schema: dyachkov_ya; Owner: dyachkov_ya
--

ALTER TABLE ONLY dyachkov_ya.exhibit
    ADD CONSTRAINT exhibit_pkey PRIMARY KEY (id_exhibit);


--
-- TOC entry 6334 (class 2606 OID 444439)
-- Name: museum museum_pkey; Type: CONSTRAINT; Schema: dyachkov_ya; Owner: dyachkov_ya
--

ALTER TABLE ONLY dyachkov_ya.museum
    ADD CONSTRAINT museum_pkey PRIMARY KEY (id_museum);


--
-- TOC entry 6344 (class 2606 OID 450382)
-- Name: roles roles_pkey; Type: CONSTRAINT; Schema: dyachkov_ya; Owner: dyachkov_ya
--

ALTER TABLE ONLY dyachkov_ya.roles
    ADD CONSTRAINT roles_pkey PRIMARY KEY (id_role);


--
-- TOC entry 6346 (class 2606 OID 450384)
-- Name: roles roles_role_name_key; Type: CONSTRAINT; Schema: dyachkov_ya; Owner: dyachkov_ya
--

ALTER TABLE ONLY dyachkov_ya.roles
    ADD CONSTRAINT roles_role_name_key UNIQUE (role_name);


--
-- TOC entry 6348 (class 2606 OID 450398)
-- Name: users users_pkey; Type: CONSTRAINT; Schema: dyachkov_ya; Owner: dyachkov_ya
--

ALTER TABLE ONLY dyachkov_ya.users
    ADD CONSTRAINT users_pkey PRIMARY KEY (id_user);


--
-- TOC entry 6350 (class 2606 OID 450400)
-- Name: users users_username_key; Type: CONSTRAINT; Schema: dyachkov_ya; Owner: dyachkov_ya
--

ALTER TABLE ONLY dyachkov_ya.users
    ADD CONSTRAINT users_username_key UNIQUE (username);


--
-- TOC entry 6342 (class 2606 OID 444485)
-- Name: viewing viewing_pkey; Type: CONSTRAINT; Schema: dyachkov_ya; Owner: dyachkov_ya
--

ALTER TABLE ONLY dyachkov_ya.viewing
    ADD CONSTRAINT viewing_pkey PRIMARY KEY (id_ticket, id_exhibit);


--
-- TOC entry 6338 (class 2606 OID 444454)
-- Name: visitor visitor_pkey; Type: CONSTRAINT; Schema: dyachkov_ya; Owner: dyachkov_ya
--

ALTER TABLE ONLY dyachkov_ya.visitor
    ADD CONSTRAINT visitor_pkey PRIMARY KEY (id_ticket);


--
-- TOC entry 6360 (class 2620 OID 444523)
-- Name: exhibit after_exhibit_insert; Type: TRIGGER; Schema: dyachkov_ya; Owner: dyachkov_ya
--

CREATE TRIGGER after_exhibit_insert AFTER INSERT ON dyachkov_ya.exhibit FOR EACH ROW EXECUTE FUNCTION dyachkov_ya.update_museum_profit();


--
-- TOC entry 6359 (class 2606 OID 450417)
-- Name: auditlog auditlog_id_user_fkey; Type: FK CONSTRAINT; Schema: dyachkov_ya; Owner: dyachkov_ya
--

ALTER TABLE ONLY dyachkov_ya.auditlog
    ADD CONSTRAINT auditlog_id_user_fkey FOREIGN KEY (id_user) REFERENCES dyachkov_ya.users(id_user);


--
-- TOC entry 6354 (class 2606 OID 444466)
-- Name: exhibit exhibit_id_creator_fkey; Type: FK CONSTRAINT; Schema: dyachkov_ya; Owner: dyachkov_ya
--

ALTER TABLE ONLY dyachkov_ya.exhibit
    ADD CONSTRAINT exhibit_id_creator_fkey FOREIGN KEY (id_creator) REFERENCES dyachkov_ya.creator(id_creator);


--
-- TOC entry 6355 (class 2606 OID 444471)
-- Name: exhibit exhibit_id_museum_fkey; Type: FK CONSTRAINT; Schema: dyachkov_ya; Owner: dyachkov_ya
--

ALTER TABLE ONLY dyachkov_ya.exhibit
    ADD CONSTRAINT exhibit_id_museum_fkey FOREIGN KEY (id_museum) REFERENCES dyachkov_ya.museum(id_museum);


--
-- TOC entry 6358 (class 2606 OID 450401)
-- Name: users users_id_role_fkey; Type: FK CONSTRAINT; Schema: dyachkov_ya; Owner: dyachkov_ya
--

ALTER TABLE ONLY dyachkov_ya.users
    ADD CONSTRAINT users_id_role_fkey FOREIGN KEY (id_role) REFERENCES dyachkov_ya.roles(id_role);


--
-- TOC entry 6356 (class 2606 OID 444491)
-- Name: viewing viewing_id_exhibit_fkey; Type: FK CONSTRAINT; Schema: dyachkov_ya; Owner: dyachkov_ya
--

ALTER TABLE ONLY dyachkov_ya.viewing
    ADD CONSTRAINT viewing_id_exhibit_fkey FOREIGN KEY (id_exhibit) REFERENCES dyachkov_ya.exhibit(id_exhibit);


--
-- TOC entry 6357 (class 2606 OID 444486)
-- Name: viewing viewing_id_ticket_fkey; Type: FK CONSTRAINT; Schema: dyachkov_ya; Owner: dyachkov_ya
--

ALTER TABLE ONLY dyachkov_ya.viewing
    ADD CONSTRAINT viewing_id_ticket_fkey FOREIGN KEY (id_ticket) REFERENCES dyachkov_ya.visitor(id_ticket);


--
-- TOC entry 6353 (class 2606 OID 444455)
-- Name: visitor visitor_id_museum_fkey; Type: FK CONSTRAINT; Schema: dyachkov_ya; Owner: dyachkov_ya
--

ALTER TABLE ONLY dyachkov_ya.visitor
    ADD CONSTRAINT visitor_id_museum_fkey FOREIGN KEY (id_museum) REFERENCES dyachkov_ya.museum(id_museum);


-- Completed on 2026-06-30 18:28:57 MSK

--
-- PostgreSQL database dump complete
--

\unrestrict fMX8pe7fyPeennYB6eeK17lVC7rkBp0LxzYWCFMQgMflMYdB3BYjGmqxfiz2Aj2