-- +goose Up
-- +goose StatementBegin

-- =========================================
-- ТРИГГЕР: Автообновление updated_at
-- =========================================
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Применяем триггер к таблицам с updated_at
CREATE TRIGGER update_users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_requests_updated_at
    BEFORE UPDATE ON requests
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_volunteer_responses_updated_at
    BEFORE UPDATE ON volunteer_responses
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_ratings_updated_at
    BEFORE UPDATE ON ratings
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_complaints_updated_at
    BEFORE UPDATE ON complaints
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

COMMENT ON FUNCTION update_updated_at_column IS 'Автоматическое обновление поля updated_at при изменении записи';

-- =========================================
-- ТРИГГЕР: Обновление рейтинга волонтёра
-- =========================================
CREATE OR REPLACE FUNCTION update_volunteer_rating()
RETURNS TRIGGER AS $$
BEGIN
    -- Обновляем средний рейтинг и количество выполненных заявок
    UPDATE users
    SET
        volunteer_rating = (
            SELECT COALESCE(ROUND(AVG(rating)::numeric, 2), 0)
            FROM ratings
            WHERE volunteer_id = NEW.volunteer_id
        ),
        completed_requests_count = (
            SELECT COUNT(*)
            FROM ratings
            WHERE volunteer_id = NEW.volunteer_id
        )
    WHERE id = NEW.volunteer_id;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Триггеры для INSERT и UPDATE рейтингов
CREATE TRIGGER update_volunteer_rating_on_insert
    AFTER INSERT ON ratings
    FOR EACH ROW
    EXECUTE FUNCTION update_volunteer_rating();

CREATE TRIGGER update_volunteer_rating_on_update
    AFTER UPDATE ON ratings
    FOR EACH ROW
    WHEN (OLD.rating IS DISTINCT FROM NEW.rating OR OLD.volunteer_id IS DISTINCT FROM NEW.volunteer_id)
    EXECUTE FUNCTION update_volunteer_rating();

COMMENT ON FUNCTION update_volunteer_rating IS 'Автоматический пересчёт рейтинга волонтёра при добавлении/изменении оценки';

-- =========================================
-- ТРИГГЕР: Синхронизация статуса блокировки пользователя
-- =========================================
CREATE OR REPLACE FUNCTION sync_user_block_status()
RETURNS TRIGGER AS $$
BEGIN
    -- При INSERT или UPDATE активной блокировки
    IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') AND NEW.is_active = TRUE THEN
        UPDATE users SET is_blocked = TRUE WHERE id = NEW.user_id;

    -- При DELETE или деактивации блокировки
    ELSIF TG_OP = 'DELETE' OR (TG_OP = 'UPDATE' AND NEW.is_active = FALSE) THEN
        -- Проверяем, есть ли другие активные блокировки
        UPDATE users
        SET is_blocked = EXISTS(
            SELECT 1
            FROM user_blocks
            WHERE user_id = COALESCE(NEW.user_id, OLD.user_id)
            AND is_active = TRUE
            AND id != COALESCE(NEW.id, OLD.id)
        )
        WHERE id = COALESCE(NEW.user_id, OLD.user_id);
    END IF;

    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER sync_user_block_status_trigger
    AFTER INSERT OR UPDATE OR DELETE ON user_blocks
    FOR EACH ROW
    EXECUTE FUNCTION sync_user_block_status();

COMMENT ON FUNCTION sync_user_block_status IS 'Синхронизация флага is_blocked в users при изменении блокировок';

-- =========================================
-- ТРИГГЕР: Автоматическое создание записи в истории статусов
-- =========================================
CREATE OR REPLACE FUNCTION log_request_status_change()
RETURNS TRIGGER AS $$
BEGIN
    -- При создании заявки
    IF TG_OP = 'INSERT' THEN
        INSERT INTO request_status_history (request_id, from_status, to_status, changed_by)
        VALUES (NEW.id, NULL, NEW.status, NEW.requester_id);

    -- При изменении статуса
    ELSIF TG_OP = 'UPDATE' AND OLD.status IS DISTINCT FROM NEW.status THEN
        INSERT INTO request_status_history (request_id, from_status, to_status, changed_by)
        VALUES (
            NEW.id,
            OLD.status,
            NEW.status,
            COALESCE(NEW.moderated_by, NEW.assigned_volunteer_id, NEW.requester_id)
        );
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER log_request_status_change_trigger
    AFTER INSERT OR UPDATE ON requests
    FOR EACH ROW
    EXECUTE FUNCTION log_request_status_change();

COMMENT ON FUNCTION log_request_status_change IS 'Автоматическое логирование всех изменений статусов заявок';

-- =========================================
-- ТРИГГЕР: Проверка истечения временной блокировки
-- =========================================
CREATE OR REPLACE FUNCTION check_block_expiration()
RETURNS TRIGGER AS $$
BEGIN
    -- Если блокировка временная и истекла, деактивируем её
    IF NEW.blocked_until IS NOT NULL AND NEW.blocked_until < CURRENT_TIMESTAMP THEN
        NEW.is_active = FALSE;
        NEW.unblocked_at = CURRENT_TIMESTAMP;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_block_expiration_trigger
    BEFORE INSERT OR UPDATE ON user_blocks
    FOR EACH ROW
    WHEN (NEW.blocked_until IS NOT NULL)
    EXECUTE FUNCTION check_block_expiration();

COMMENT ON FUNCTION check_block_expiration IS 'Автоматическая деактивация истёкших временных блокировок';

-- +goose StatementEnd

-- +goose Down
-- +goose StatementBegin

-- Удаляем триггеры
DROP TRIGGER IF EXISTS check_block_expiration_trigger ON user_blocks;
DROP TRIGGER IF EXISTS log_request_status_change_trigger ON requests;
DROP TRIGGER IF EXISTS sync_user_block_status_trigger ON user_blocks;
DROP TRIGGER IF EXISTS update_volunteer_rating_on_update ON ratings;
DROP TRIGGER IF EXISTS update_volunteer_rating_on_insert ON ratings;
DROP TRIGGER IF EXISTS update_complaints_updated_at ON complaints;
DROP TRIGGER IF EXISTS update_ratings_updated_at ON ratings;
DROP TRIGGER IF EXISTS update_volunteer_responses_updated_at ON volunteer_responses;
DROP TRIGGER IF EXISTS update_requests_updated_at ON requests;
DROP TRIGGER IF EXISTS update_users_updated_at ON users;

-- Удаляем функции
DROP FUNCTION IF EXISTS check_block_expiration;
DROP FUNCTION IF EXISTS log_request_status_change;
DROP FUNCTION IF EXISTS sync_user_block_status;
DROP FUNCTION IF EXISTS update_volunteer_rating;
DROP FUNCTION IF EXISTS update_updated_at_column;

-- +goose StatementEnd