-- =============================================================================
-- MIGRATION 012: fn_tutor_check_student
-- PURPOSE: Read-only pre-enrollment check ("aduana").
--   PATH A (partner_id > 0): look up by ID only — no email required.
--     · FOUND_PARTNER    — partner exists, free to enroll
--     · NEW_PARTNER      — PartnerID not in DB
--     · ALREADY_ENROLLED — already inscribed with this tutor
--     · DIFFERENT_TUTOR  — inscribed with a different tutor
--   PATH B (partner_id = 0): look up by email in partners then guests.
--     · FOUND_PARTNER    — email matches a partner, free to enroll
--     · NEW_CONTACT      — email free in both tables → guest lead
--     · ALREADY_ENROLLED — already inscribed with this tutor
--     · DIFFERENT_TUTOR  — inscribed with a different tutor
-- READS:   partners, student_profiles, guests
-- WRITES:  NONE (STABLE)
-- =============================================================================

DROP FUNCTION IF EXISTS public.fn_tutor_check_student(BIGINT, TEXT, BIGINT);

CREATE OR REPLACE FUNCTION public.fn_tutor_check_student(
    p_partner_id BIGINT,
    p_email      TEXT,
    p_tutor_id   BIGINT
)
RETURNS JSONB
LANGUAGE plpgsql
STABLE
AS $$
DECLARE
    v_partner RECORD;
    v_sp      RECORD;
    v_guest   RECORD;
BEGIN

    -- ── PATH A: PartnerID provided — look up by ID only ───────────────────────
    IF p_partner_id > 0 THEN

        SELECT partner_id, name, surname, email, phone, country, zinzino_range
        INTO   v_partner
        FROM   public.partners
        WHERE  partner_id = p_partner_id;

        IF FOUND THEN
            -- Already enrolled with this tutor?
            SELECT status INTO v_sp
            FROM   public.student_profiles
            WHERE  partner_id = p_partner_id
              AND  tutor_id   = p_tutor_id;

            IF FOUND THEN
                RETURN jsonb_build_object(
                    'status',             'ALREADY_ENROLLED',
                    'student_type',       'PARTNER',
                    'enrollment_status',  v_sp.status,
                    'message',            'Este partner ya está inscrito contigo (estado: ' || v_sp.status || ').',
                    'partner_data',       jsonb_build_object(
                        'partner_id',    v_partner.partner_id,
                        'name',          v_partner.name::TEXT,
                        'surname',       v_partner.surname::TEXT,
                        'email',         v_partner.email::TEXT,
                        'phone',         v_partner.phone::TEXT,
                        'country',       TRIM(v_partner.country::TEXT),
                        'zinzino_range', v_partner.zinzino_range::TEXT
                    )
                );
            END IF;

            -- Enrolled with a different tutor?
            IF EXISTS (
                SELECT 1 FROM public.student_profiles
                WHERE  partner_id = p_partner_id
                  AND  tutor_id  != p_tutor_id
            ) THEN
                RETURN jsonb_build_object(
                    'status',  'DIFFERENT_TUTOR',
                    'message', 'Este partner ya está registrado con otro tutor.'
                );
            END IF;

            -- Partner found and free → return data for auto-fill
            RETURN jsonb_build_object(
                'status',       'FOUND_PARTNER',
                'student_type', 'PARTNER',
                'message',      'Partner encontrado: ' || v_partner.name::TEXT || ' ' || v_partner.surname::TEXT || '. Datos cargados automáticamente.',
                'partner_data', jsonb_build_object(
                    'partner_id',    v_partner.partner_id,
                    'name',          v_partner.name::TEXT,
                    'surname',       v_partner.surname::TEXT,
                    'email',         v_partner.email::TEXT,
                    'phone',         v_partner.phone::TEXT,
                    'country',       TRIM(v_partner.country::TEXT),
                    'zinzino_range', v_partner.zinzino_range::TEXT
                )
            );

        ELSE
            -- PartnerID not in DB → new partner to register
            RETURN jsonb_build_object(
                'status',       'NEW_PARTNER',
                'student_type', 'PARTNER',
                'message',      'Nuevo partner. Completa los datos para registrarle.'
            );
        END IF;

    -- ── PATH B: No PartnerID → search by email in partners then guests ─────────
    ELSE

        -- Check partners table by email first
        SELECT partner_id, name, surname, email, phone, country, zinzino_range
        INTO   v_partner
        FROM   public.partners
        WHERE  LOWER(email::TEXT) = LOWER(p_email);

        IF FOUND THEN
            -- Already enrolled with this tutor?
            SELECT status INTO v_sp
            FROM   public.student_profiles
            WHERE  partner_id = v_partner.partner_id
              AND  tutor_id   = p_tutor_id;

            IF FOUND THEN
                RETURN jsonb_build_object(
                    'status',             'ALREADY_ENROLLED',
                    'student_type',       'PARTNER',
                    'enrollment_status',  v_sp.status,
                    'message',            'Este partner ya está inscrito contigo (estado: ' || v_sp.status || ').',
                    'partner_data',       jsonb_build_object(
                        'partner_id',    v_partner.partner_id,
                        'name',          v_partner.name::TEXT,
                        'surname',       v_partner.surname::TEXT,
                        'email',         v_partner.email::TEXT,
                        'phone',         v_partner.phone::TEXT,
                        'country',       TRIM(v_partner.country::TEXT),
                        'zinzino_range', v_partner.zinzino_range::TEXT
                    )
                );
            END IF;

            -- Enrolled with a different tutor?
            IF EXISTS (
                SELECT 1 FROM public.student_profiles
                WHERE  partner_id = v_partner.partner_id
                  AND  tutor_id  != p_tutor_id
            ) THEN
                RETURN jsonb_build_object(
                    'status',  'DIFFERENT_TUTOR',
                    'message', 'Este partner ya está registrado con otro tutor.'
                );
            END IF;

            -- Partner found by email, free → return data for auto-fill
            RETURN jsonb_build_object(
                'status',       'FOUND_PARTNER',
                'student_type', 'PARTNER',
                'message',      'Partner encontrado: ' || v_partner.name::TEXT || ' ' || v_partner.surname::TEXT || '. Datos cargados automáticamente.',
                'partner_data', jsonb_build_object(
                    'partner_id',    v_partner.partner_id,
                    'name',          v_partner.name::TEXT,
                    'surname',       v_partner.surname::TEXT,
                    'email',         v_partner.email::TEXT,
                    'phone',         v_partner.phone::TEXT,
                    'country',       TRIM(v_partner.country::TEXT),
                    'zinzino_range', v_partner.zinzino_range::TEXT
                )
            );
        END IF;

        -- Not in partners → check guests
        SELECT tutor_id INTO v_guest
        FROM   public.guests
        WHERE  LOWER(email::TEXT) = LOWER(p_email)
        LIMIT 1;

        IF FOUND THEN
            IF v_guest.tutor_id = p_tutor_id THEN
                RETURN jsonb_build_object(
                    'status',       'ALREADY_ENROLLED',
                    'student_type', 'GUEST',
                    'message',      'Este contacto ya está registrado como tu invitado.'
                );
            ELSE
                RETURN jsonb_build_object(
                    'status',  'DIFFERENT_TUTOR',
                    'message', 'Este contacto ya está registrado con otro tutor.'
                );
            END IF;
        END IF;

        -- Free → new guest contact
        RETURN jsonb_build_object(
            'status',       'NEW_CONTACT',
            'student_type', 'GUEST',
            'message',      'Nuevo contacto invitado. Completa los datos.'
        );
    END IF;

EXCEPTION WHEN OTHERS THEN
    RETURN jsonb_build_object(
        'status',  'ERROR',
        'message', 'Error interno: ' || SQLERRM
    );
END;
$$;

-- Grants
GRANT EXECUTE ON FUNCTION public.fn_tutor_check_student(BIGINT, TEXT, BIGINT) TO "elfi1953_stc-eu";