CREATE OR REPLACE FUNCTION public.fn_enroll_partner_system(p_data jsonb)
 RETURNS jsonb
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $function$
DECLARE
    v_t_id      BIGINT;
    v_t_email   valid_email;
    v_s_id      BIGINT;
    v_s_email   valid_email;
    v_level_id  INT;
    v_course_id INT;
    v_status    text;
    v_msg_key   text;
    v_log_msg   character varying;
    v_res_id    INT;
    v_proc_at   valid_timestamp;
    v_log_s_id  bigint;

BEGIN
    v_t_id      := (p_data->'tutor'->>'id')::BIGINT;
    v_t_email   := (p_data->'tutor'->>'email')::valid_email;
    v_s_id      := (p_data->'student'->>'id')::BIGINT;
    v_s_email   := (p_data->'student'->>'email')::valid_email;
    v_course_id := COALESCE((p_data->>'course_id')::INT, 1);

    -- ── Validar e insertar tutor ──────────────────────────────────────────────
    v_msg_key := public.fn_upsert_partner(
        v_t_id, v_t_email,
        p_data->'tutor'->>'name',
        p_data->'tutor'->>'surname',
        (p_data->'tutor'->>'phone')::intl_phone,
        (p_data->'tutor'->>'country')::iso_country,
        p_data->'tutor'->>'zinzino_range',
        'ERR_TUTOR_MISMATCH'
    );
    IF v_msg_key IS NOT NULL THEN
        v_status := 'REJECTED';
    ELSE
        INSERT INTO public.tutor_profiles (partner_id, status)
        VALUES (v_t_id, 'ACTIVE'::text)
        ON CONFLICT (partner_id) DO NOTHING;
    END IF;

    -- ── Validar e insertar alumno ─────────────────────────────────────────────
    IF v_status IS NULL THEN
        v_level_id := CASE WHEN v_s_id = 0 THEN 0 ELSE (p_data->>'level_id')::INT END;

        IF v_s_id = 0 THEN
            IF EXISTS(SELECT 1 FROM public.partners WHERE email = v_s_email) THEN
                v_status  := 'REJECTED';
                v_msg_key := 'ERR_ALREADY_PARTNER';
            ELSIF NOT EXISTS(SELECT 1 FROM public.guests WHERE email = v_s_email) THEN
                INSERT INTO public.guests (email, name, surname, phone, country, zinzino_range, tutor_id, course_id)
                VALUES (v_s_email,
                        p_data->'student'->>'name',
                        p_data->'student'->>'surname',
                        (p_data->'student'->>'phone')::intl_phone,
                        (p_data->'student'->>'country')::iso_country,
                        p_data->'student'->>'zinzino_range',
                        v_t_id, v_course_id);
            END IF;
        ELSE
            DELETE FROM public.guests WHERE email = v_s_email;

            v_msg_key := public.fn_upsert_partner(
                v_s_id, v_s_email,
                p_data->'student'->>'name',
                p_data->'student'->>'surname',
                (p_data->'student'->>'phone')::intl_phone,
                (p_data->'student'->>'country')::iso_country,
                p_data->'student'->>'zinzino_range',
                'ERR_STUDENT_MISMATCH'
            );
            IF v_msg_key IS NOT NULL THEN
                v_status := 'REJECTED';
            END IF;
        END IF;
    END IF;

    -- ── Fraude + cupo + inscripción ───────────────────────────────────────────
    IF v_status IS NULL THEN
        v_log_s_id := NULLIF(v_s_id, 0);

        IF (v_s_id > 0 AND EXISTS(
                SELECT 1 FROM public.student_profiles
                WHERE partner_id = v_s_id AND tutor_id != v_t_id))
           OR
           (v_s_id = 0 AND EXISTS(
                SELECT 1 FROM public.guests
                WHERE email = v_s_email AND tutor_id != v_t_id))
        THEN
            v_status  := 'REJECTED';
            v_msg_key := 'ERR_FRAUD_STUDENT';
        ELSE
            v_status  := public.fn_check_enrollment_quota(v_t_id, v_level_id, v_course_id);
            v_msg_key := CASE v_status WHEN 'ACTIVE' THEN 'SUCCESS_ADMITTED' ELSE 'ERR_QUOTA_FULL' END;

            IF v_s_id > 0 THEN
                INSERT INTO public.student_profiles (partner_id, remarks, level_id, tutor_id, status, course_id, updated_at)
                VALUES (v_s_id,
                        p_data->'student'->>'remarks',
                        v_level_id, v_t_id, v_status, v_course_id,
                        CURRENT_TIMESTAMP::valid_timestamp);
            END IF;
        END IF;
    END IF;

    -- ── Log de registro ───────────────────────────────────────────────────────
    SELECT message_text INTO v_log_msg
    FROM public.registration_messages
    WHERE message_key = v_msg_key AND language = 'es';

    IF v_log_msg IS NULL THEN
        v_log_msg := 'Transaction dropped due to severe structural identity mismatch.';
    END IF;

    INSERT INTO public.registrations_true (tutor_id, student_id, level_id, course_id, result_status, detalles, processed_at)
    VALUES (v_t_id, v_log_s_id, v_level_id, v_course_id, COALESCE(v_status, 'REJECTED'), v_log_msg, CURRENT_TIMESTAMP::valid_timestamp)
    RETURNING result_id, processed_at INTO v_res_id, v_proc_at;

    RETURN jsonb_build_object(
        'status',       COALESCE(v_status, 'REJECTED'),
        'msg_key',      COALESCE(v_msg_key, 'ERR_UNKNOWN'),
        'result_id',    v_res_id,
        'course_id',    v_course_id,
        'level_id',     v_level_id,
        'message',      v_log_msg,
        'processed_at', v_proc_at::TEXT
    );
END;
$function$
;