-- ============================================================================= -- FUNCTION: fn_enroll_partner_system -- PURPOSE: Atomic enrollment engine. Validates and registers a tutor-student -- dupla, routing students to partners or guests based on their ID. -- FIX LOG: -- BLOCK 3: Added IF v_s_id > 0 guard — prevents INSERT INTO student_profiles -- with partner_id = 0 (FK violation for guest students) -- BLOCK 4: Used NULLIF(v_s_id, 0) for student_id — prevents FK violation -- in registrations_true for guest students -- ADDED: course_id support (defaults to 1 until multi-course is active) -- Returns level_id, course_id, processed_at for frontend result screen -- HOOKS: MOODLE_ENROLL_HOOK, MOODLE_USER_SYNC_HOOK (future integration) -- ============================================================================= CREATE OR REPLACE FUNCTION public.fn_enroll_partner_system(p_data jsonb) RETURNS jsonb LANGUAGE plpgsql SECURITY DEFINER AS $BODY$ DECLARE -- ========================================================================= -- JSON PAYLOAD EXTRACTION VARIABLES -- ========================================================================= 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; -- ========================================================================= -- BIDIRECTIONAL LOCK TRACKING VARIABLES (ANTI-FRAUD) -- ========================================================================= v_db_t_id BIGINT; v_db_t_email valid_email; v_db_s_id BIGINT; v_db_s_email valid_email; -- ========================================================================= -- OPERATIONAL & QUOTA COUNTER VARIABLES -- ========================================================================= v_used INT; v_limit INT; v_status text; v_msg_key text; v_log_msg character varying; v_res_id INT; v_proc_at valid_timestamp; BEGIN -- BLOCK 0: SANITIZE AND EXTRACT INCOMING FRONTEND PAYLOAD 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); -- defaults to STC Core -- BLOCK 1: TUTOR IDENTITY ADMISSION (INDEX-ALIGNED DOUBLE-LOCK) SELECT partner_id, email INTO v_db_t_id, v_db_t_email FROM partners WHERE partner_id = v_t_id OR email = v_t_email; IF v_db_t_id IS NOT NULL OR v_db_t_email IS NOT NULL THEN IF v_db_t_id != v_t_id OR v_db_t_email != v_t_email THEN v_status := 'REJECTED'; v_msg_key := 'ERR_TUTOR_MISMATCH'; ELSE INSERT INTO tutor_profiles (partner_id, status) VALUES (v_t_id, 'ACTIVE'::text) ON CONFLICT (partner_id) DO NOTHING; -- MOODLE_USER_SYNC_HOOK: sync existing tutor to Moodle user if not yet synced END IF; ELSE INSERT INTO partners (partner_id, name, surname, email, phone, country, zinzino_range) VALUES (v_t_id, p_data->'tutor'->>'name', p_data->'tutor'->>'surname', v_t_email, (p_data->'tutor'->>'phone')::intl_phone, (p_data->'tutor'->>'country')::iso_country, p_data->'tutor'->>'zinzino_range'); INSERT INTO tutor_profiles (partner_id, status) VALUES (v_t_id, 'ACTIVE'::text); -- MOODLE_USER_SYNC_HOOK: create new tutor as Moodle user END IF; -- BLOCK 2: STUDENT / GUEST DEMOGRAPHIC INSULATION ROUTING IF v_status IS NULL THEN IF v_s_id = 0 THEN v_level_id := 0; -- business rule: guests always start at level 0 ELSE v_level_id := (p_data->>'level_id')::INT; END IF; IF v_s_id = 0 THEN -- CASE A: GUEST LEAD — no Zinzino partner_id yet IF EXISTS(SELECT 1 FROM partners WHERE email = v_s_email) THEN v_status := 'REJECTED'; v_msg_key := 'ERR_ALREADY_PARTNER'; ELSE IF NOT EXISTS(SELECT 1 FROM guests WHERE email = v_s_email) THEN INSERT INTO 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); -- MOODLE_USER_SYNC_HOOK: pre-register guest in Moodle as pending user END IF; END IF; ELSE -- CASE B: REGISTERED COMMERCIAL PARTNER (partner_id > 0) IF EXISTS(SELECT 1 FROM guests WHERE email = v_s_email) THEN DELETE FROM guests WHERE email = v_s_email; -- MOODLE_USER_SYNC_HOOK: promote guest to full Moodle user END IF; SELECT partner_id, email INTO v_db_s_id, v_db_s_email FROM partners WHERE partner_id = v_s_id OR email = v_s_email; IF v_db_s_id IS NOT NULL OR v_db_s_email IS NOT NULL THEN IF v_db_s_id != v_s_id OR v_db_s_email != v_s_email THEN v_status := 'REJECTED'; v_msg_key := 'ERR_STUDENT_MISMATCH'; END IF; ELSE INSERT INTO partners (partner_id, name, surname, email, phone, country, zinzino_range) VALUES (v_s_id, p_data->'student'->>'name', p_data->'student'->>'surname', v_s_email, (p_data->'student'->>'phone')::intl_phone, (p_data->'student'->>'country')::iso_country, p_data->'student'->>'zinzino_range'); -- MOODLE_USER_SYNC_HOOK: create new partner-student as Moodle user END IF; END IF; END IF; -- BLOCK 3: FRAUD MONITORING, QUOTA EVALUATION & ALLOCATION IF v_status IS NULL THEN -- Bidirectional fraud check IF (v_s_id > 0 AND EXISTS ( SELECT 1 FROM student_profiles WHERE partner_id = v_s_id AND tutor_id != v_t_id )) OR (v_s_id = 0 AND EXISTS ( SELECT 1 FROM guests WHERE email = v_s_email AND tutor_id != v_t_id )) THEN v_status := 'REJECTED'; v_msg_key := 'ERR_FRAUD_STUDENT'; ELSE -- Real-time quota assessment SELECT COUNT(*) INTO v_used FROM student_profiles WHERE tutor_id = v_t_id AND level_id = v_level_id AND status = 'ACTIVE' AND course_id = v_course_id; SELECT tutor_quota_limit INTO v_limit FROM levels WHERE level_id = v_level_id AND course_id = v_course_id; IF v_used < v_limit THEN v_status := 'ACTIVE'; v_msg_key := 'SUCCESS_ADMITTED'; ELSE v_status := 'PENDING'; v_msg_key := 'ERR_QUOTA_FULL'; END IF; -- FIX: only insert into student_profiles for registered partners (v_s_id > 0) -- guests are stored in the guests table (BLOCK 2) IF v_s_id > 0 THEN INSERT INTO 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); -- MOODLE_ENROLL_HOOK: enroll student in Moodle course v_course_id END IF; END IF; END IF; -- BLOCK 4: AUDIT LOG & RESPONSE PACKAGER SELECT message_text INTO v_log_msg FROM registration_messages WHERE message_key = v_msg_key AND language = 'es'; -- i18n: pass language param when multi-language is active IF v_log_msg IS NULL THEN v_log_msg := 'Transaction dropped due to severe structural identity mismatch.'; END IF; -- FIX: NULLIF(v_s_id, 0) stores NULL for guests instead of 0 (FK constraint) INSERT INTO registrations_true (tutor_id, student_id, level_id, course_id, result_status, detalles, processed_at) VALUES (v_t_id, NULLIF(v_s_id, 0), 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'), '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; $BODY$;