-- select 
-- 	* 
-- from 
-- 	participant_request 
-- where
-- 	name = 'Дмитрий'
-- 	and surname = 'Малышев'
-- 	and id_competition = (
-- 		select id_competition from competition where title = 'Мега кубок №36' limit 1
-- 	)


create or replace procedure create_participant_request(
    p_name varchar(100),
    p_surname varchar(100),
    p_gender varchar(10),
    p_competition_title varchar(100),
    p_division_title varchar(100)
)
language plpgsql
as $$
declare
    v_id_sportsman integer;
    v_id_competition integer;
    v_id_division integer;
begin
    -- проверка существования дивизиона
    select id_division into v_id_division
    from division
    where title = p_division_title
    limit 1;

    if not found then
        raise exception 'Дивизиона % не существует', p_division_title;
    end if;

    -- проверка существования соревнования
    select id_competition into v_id_competition
    from competition
    where title = p_competition_title
    limit 1;

    -- если соревнование не существует, создать его
    if not found then
        insert into competition (title)
        values (p_competition_title)
        returning id_competition into v_id_competition;
    end if;

    -- проверка существования спортсмена
    select id_sportsman into v_id_sportsman
    from sportsman
    where name = p_name and surname = p_surname and gender = p_gender
    limit 1;

    -- если спортсмен не существует, создать его
    if not found then
        insert into sportsman (name, surname, gender)
        values (p_name, p_surname, p_gender)
        returning id_sportsman into v_id_sportsman;
    end if;

    -- создание заявки участника
    insert into participant_request (name, surname, gender, is_registered, id_competition, id_sportsman, id_division)
    values (p_name, p_surname, p_gender, false, v_id_competition, v_id_sportsman, v_id_division);
end;
$$;