-- Drop existing version so we can change the return type
DROP FUNCTION IF EXISTS public.fn_tutor_list_students(BIGINT, INT);

-- =============================================================================
-- MIGRATION 010: fn_tutor_list_students
-- PURPOSE: Returns all students (partners + guests) enrolled under a given tutor,
--          ordered by most recent enrollment first.
-- TABLES:  student_profiles (partners), guests (leads), levels (names), partners
-- NOTE:    All string return types are TEXT with explicit casts to avoid
--          varchar/text/bpchar mismatches that trigger "structure does not match".
-- =============================================================================

CREATE OR REPLACE FUNCTION public.fn_tutor_list_students(
    p_tutor_id  BIGINT,
    p_course_id INT DEFAULT 1
)
RETURNS TABLE (
    student_id        BIGINT,
    student_type      TEXT,
    student_name      TEXT,
    student_surname   TEXT,
    student_email     TEXT,
    student_phone     TEXT,
    student_country   TEXT,
    level_id          INT,
    level_name        TEXT,
    enrollment_status TEXT,
    enrolled_at       TIMESTAMPTZ
)
LANGUAGE plpgsql
STABLE
AS $BODY$
BEGIN
    RETURN QUERY

    -- ── Registered partners ───────────────────────────────────────────────────
    SELECT
        sp.partner_id::BIGINT                                       AS student_id,
        'PARTNER'::TEXT                                             AS student_type,
        p.name::TEXT                                                AS student_name,
        p.surname::TEXT                                             AS student_surname,
        p.email::TEXT                                               AS student_email,
        p.phone::TEXT                                               AS student_phone,
        TRIM(p.country::TEXT)                                       AS student_country,
        sp.level_id::INT                                            AS level_id,
        COALESCE(l.name::TEXT, 'Nivel ' || sp.level_id::TEXT)       AS level_name,
        sp.status::TEXT                                             AS enrollment_status,
        sp.updated_at::TIMESTAMPTZ                                  AS enrolled_at
    FROM   public.student_profiles sp
    JOIN   public.partners  p  ON p.partner_id = sp.partner_id
    LEFT JOIN public.levels l  ON l.level_id   = sp.level_id
                               AND l.course_id  = sp.course_id
    WHERE  sp.tutor_id  = p_tutor_id
      AND  sp.course_id = p_course_id

    UNION ALL

    -- ── Guest leads ───────────────────────────────────────────────────────────
    SELECT
        0::BIGINT                                                   AS student_id,
        'GUEST'::TEXT                                               AS student_type,
        g.name::TEXT                                                AS student_name,
        g.surname::TEXT                                             AS student_surname,
        g.email::TEXT                                               AS student_email,
        g.phone::TEXT                                               AS student_phone,
        TRIM(g.country::TEXT)                                       AS student_country,
        0::INT                                                      AS level_id,
        COALESCE(l0.name::TEXT, 'Invitados')                        AS level_name,
        'ACTIVE'::TEXT                                              AS enrollment_status,
        g.reg_date::TIMESTAMPTZ                                     AS enrolled_at
    FROM   public.guests g
    LEFT JOIN public.levels l0 ON l0.level_id = 0
    WHERE  g.tutor_id   = p_tutor_id
      AND  g.course_id  = p_course_id

    ORDER BY enrolled_at DESC NULLS LAST;
END;
$BODY$;

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