sql_labs/lab3.sql - master - nmktth/web-development-sem-4

sql_labs/lab3.sql
126 строк8 KB
artem
postgres lab complete
23 май 2026, 02:27
23 май 2026, 02:27454c592
-- =========================================================================
-- ЛАБОРАТОРНАЯ РАБОТА №3: ФУНКЦИИ И ПРОЦЕДУРЫ 
-- =========================================================================

-- Служебная процедура для заливки фейковых данных. 
-- Помогает для тестов, чтобы не забивать базу руками по одной строчке.
CREATE OR REPLACE PROCEDURE lab3_seed_data()
LANGUAGE plpgsql AS $$
BEGIN
    IF EXISTS (SELECT 1 FROM realty_advertisement WHERE title LIKE 'PRO-AD-%') THEN
        RAISE NOTICE 'Данные уже посеяны, скипаем'; RETURN;
    END IF;

    -- Генерим 500 фейк-юзеров
    INSERT INTO realty_user (username, password, email, first_name, last_name, middle_name, is_active, is_staff, is_superuser, date_joined, role)
    SELECT 'user_' || gs, 'hash', 'user' || gs || '@realty.ru', 'Имя' || gs, 'Фамилия' || gs, 'Отчество' || gs, true, false, false, NOW() - MAKE_INTERVAL(days => gs % 365), 'user'
    FROM generate_series(1, 500) AS gs;

    -- Закидываем 2000 объявлений для массовки
    INSERT INTO realty_advertisement (user_id, title, description, type, deal_type, price, deposit, address, city, is_active, views_count, created_at, updated_at)
    SELECT (SELECT id FROM realty_user ORDER BY RANDOM() LIMIT 1), 'PRO-AD-' || gs, 'Описание ' || gs, (ARRAY['flat', 'house', 'room'])[1 + (gs % 3)], 'rent_out', 20000 + (gs % 100) * 1000, 5000 + (gs % 50) * 500, 'Адрес ' || gs, (ARRAY['Москва', 'СПб', 'Казань'])[1 + (gs % 3)], true, gs % 1000, NOW() - MAKE_INTERVAL(days => gs % 200), NOW() - MAKE_INTERVAL(days => gs % 30)
    FROM generate_series(1, 2000) AS gs;

    -- Накидываем 1000 жалоб, чтобы было что обрабатывать
    INSERT INTO realty_complaint (complainant_id, content_type, content_id, reason, status, created_at)
    SELECT (SELECT id FROM realty_user ORDER BY RANDOM() LIMIT 1), 'advertisement', (SELECT id FROM realty_advertisement ORDER BY RANDOM() LIMIT 1), 'Причина ' || gs, (ARRAY['pending', 'resolved', 'rejected'])[1 + (gs % 3)], NOW() - MAKE_INTERVAL(hours => gs % 500)
    FROM generate_series(1, 1000) AS gs;

    RAISE NOTICE 'Успех: 2000 объявлений залито в базу';
END; $$;

-- Вызываем заливку данных
CALL lab3_seed_data();


-- ЗАДАЧА 1: Функция для поиска зависших жалоб. 
-- Помогает админам не провтыкать старые репорты от юзеров.
CREATE OR REPLACE PROCEDURE notify_overdue_complaints(p_hours INTEGER)
LANGUAGE plpgsql AS $$
DECLARE rec RECORD;
BEGIN
    FOR rec IN SELECT id, complainant_id, reason, created_at FROM realty_complaint WHERE status = 'pending' AND created_at <= NOW() - MAKE_INTERVAL(hours => p_hours) ORDER BY created_at LOOP
        RAISE NOTICE 'Алярм! Просроченная жалоба %: От юзера % (Дата: %)', rec.id, rec.complainant_id, rec.created_at;
    END LOOP;
END; $$;

-- ЗАДАЧА 2: Простая функция, считает сколько дней живет объява. 
-- Помогает для фронта, чтобы писать "На сайте 15 дней".
CREATE OR REPLACE FUNCTION get_ad_active_duration(p_ad_id BIGINT)
RETURNS INTERVAL LANGUAGE plpgsql AS $$
DECLARE v_dur INTERVAL;
BEGIN
    SELECT NOW() - created_at INTO v_dur FROM realty_advertisement WHERE id = p_ad_id;
    RETURN v_dur;
END; $$;

-- ЗАДАЧА 3: Процедура для закрытия жалобы.
-- Помогает безопасно сменить статус, чтобы случайно не тронуть уже закрытые.
CREATE OR REPLACE PROCEDURE resolve_complaint_safe(p_id BIGINT, p_new_status TEXT)
LANGUAGE plpgsql AS $$
DECLARE v_status TEXT;
BEGIN
    SELECT status INTO v_status FROM realty_complaint WHERE id = p_id;
    IF v_status IS NULL THEN RAISE EXCEPTION 'Бро, жалобы % не существует', p_id; END IF;
    IF v_status = 'pending' THEN
        UPDATE realty_complaint SET status = p_new_status WHERE id = p_id;
        RAISE NOTICE 'Ок, жалоба % переведена в статус %', p_id, p_new_status;
    ELSE
        RAISE NOTICE 'Эту жалобу (%) уже трогали, статус: %', p_id, v_status;
    END IF;
END; $$;

-- ЗАДАЧА 4: Функция для сбора статы по юзеру.
-- Помогает понять, насколько активный чувак (всего объявлений vs активных).
CREATE OR REPLACE FUNCTION get_user_activity_stats(p_uid BIGINT)
RETURNS TABLE (total_ads BIGINT, active_ads BIGINT) LANGUAGE plpgsql AS $$
BEGIN
    RETURN QUERY SELECT COUNT(*), COUNT(*) FILTER (WHERE is_active = true) FROM realty_advertisement WHERE user_id = p_uid;
END; $$;

-- ЗАДАЧА 5: Процедура для платного поднятия объявы (буст).
-- Помогает для монетизации. Добавляем защиту от космических ценников.
CREATE OR REPLACE PROCEDURE upgrade_ad_to_premium(p_ad_id BIGINT, p_surcharge NUMERIC, p_limit NUMERIC DEFAULT 10000)
LANGUAGE plpgsql AS $$
DECLARE v_price NUMERIC;
BEGIN
    SELECT price INTO v_price FROM realty_advertisement WHERE id = p_ad_id;
    IF p_surcharge > p_limit THEN RAISE EXCEPTION 'Слишком жирная наценка: % (лимит %)', p_surcharge, p_limit; END IF;
    UPDATE realty_advertisement SET updated_at = NOW(), price = price + p_surcharge WHERE id = p_ad_id;
    RAISE NOTICE 'Готово, объявление % залетело в топ. Прайс: %', p_ad_id, v_price + p_surcharge;
END; $$;

-- ЗАДАЧА 6: Процедура для чистки базы от старья.
-- Помогает массово сносить неактуальные посты в архив.
CREATE OR REPLACE PROCEDURE archive_old_ads(p_cutoff_date DATE)
LANGUAGE plpgsql AS $$
BEGIN
    UPDATE realty_advertisement SET is_active = false WHERE is_active = true AND updated_at < p_cutoff_date;
    RAISE NOTICE 'Все старые объявы до % улетели в архив', p_cutoff_date;
END; $$;

-- ЗАДАЧА 7: Функция для подсчета среднего времени ответа на репорты.
-- Помогает оценить, насколько админы тормозят.
CREATE OR REPLACE FUNCTION get_avg_complaint_response_time(p_uid BIGINT)
RETURNS INTERVAL LANGUAGE plpgsql AS $$
DECLARE v_avg INTERVAL;
BEGIN
    SELECT AVG(NOW() - created_at) INTO v_avg FROM realty_complaint WHERE complainant_id = p_uid;
    RETURN v_avg;
END; $$;

-- ЗАДАЧА 8: Процедура жесткого бана токсиков.
-- Сносит юзера, его объявы и заявки разом в одной транзакции.
CREATE OR REPLACE PROCEDURE ban_user_completely(p_uid BIGINT)
LANGUAGE plpgsql AS $$
DECLARE v_ad_count INT;
BEGIN
    -- Вырубаем все его хаты
    UPDATE realty_advertisement SET is_active = false WHERE user_id = p_uid;
    GET DIAGNOSTICS v_ad_count = ROW_COUNT;
    -- Вырубаем заявки на подселение
    UPDATE realty_neighborrequest SET is_active = false WHERE user_id = p_uid;
    -- Гасим самого акка
    UPDATE realty_user SET is_active = false WHERE id = p_uid;
    RAISE NOTICE 'Юзер % улетел в пермач. Сняли % объявлений.', p_uid, v_ad_count;
END; $$;