-- 018 — Conversión de invitado a partner por el tutor
-- Backup previo obligatorio antes de ejecutar.

CREATE OR REPLACE FUNCTION public.fn_tutor_convert_guest(
    p_tutor_id      bigint,
    p_guest_email   text,
    p_partner_id    bigint,
    p_zinzino_range text
)
RETURNS jsonb
LANGUAGE plpgsql
AS $function$
DECLARE
    v_name    text;
    v_surname text;
    v_email   text;
    v_phone   intl_phone;
    v_country iso_country;
BEGIN
    -- Verificar que el guest existe y pertenece al tutor
    SELECT name, surname, email, phone, country
    INTO v_name, v_surname, v_email, v_phone, v_country
    FROM public.guests
    WHERE email = p_guest_email AND tutor_id = p_tutor_id;

    IF NOT FOUND THEN
        RETURN jsonb_build_object('error', 'Invitado no encontrado o no pertenece a este tutor.');
    END IF;

    -- Verificar que el partner_id no existe ya en partners
    IF EXISTS (SELECT 1 FROM public.partners WHERE partner_id = p_partner_id) THEN
        RETURN jsonb_build_object('error', 'Este PartnerID ya está registrado en el sistema.');
    END IF;

    -- Insertar en partners
    INSERT INTO public.partners (partner_id, name, surname, email, phone, country, zinzino_range)
    VALUES (p_partner_id, v_name, v_surname, v_email::valid_email, v_phone, v_country, p_zinzino_range);

    -- Crear perfil de alumno (nivel 0, estado ACTIVE, mismo tutor)
    INSERT INTO public.student_profiles (partner_id, tutor_id, level_id, course_id, status, updated_at)
    VALUES (p_partner_id, p_tutor_id, 0, 1, 'ACTIVE', CURRENT_TIMESTAMP::valid_timestamp);

    -- Eliminar de guests
    DELETE FROM public.guests WHERE email = p_guest_email;

    RETURN jsonb_build_object(
        'success',       true,
        'partner_id',    p_partner_id,
        'student_name',  v_name || ' ' || COALESCE(v_surname, ''),
        'student_email', v_email
    );
END;
$function$;

-- Permisos
GRANT EXECUTE ON FUNCTION public.fn_tutor_convert_guest(bigint, text, bigint, text) TO "elfi1953_stc-eu";