-- =============================================================================
-- MIGRATION 011: fn_student_get_enrollment
-- PURPOSE: Returns the enrollment data for the student dashboard.
--          Two paths:
--            · partner_id > 0  → query student_profiles (registered partner)
--            · partner_id = 0  → query guests by email (unregistered lead)
-- NOTE:    All string return types are TEXT with explicit casts to avoid
--          varchar/bpchar/text mismatches.
-- =============================================================================

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

CREATE OR REPLACE FUNCTION public.fn_student_get_enrollment(
    p_partner_id BIGINT,
    p_email      TEXT,
    p_course_id  INT DEFAULT 1
)
RETURNS TABLE (
    student_type      TEXT,
    student_name      TEXT,
    student_surname   TEXT,
    student_email     TEXT,
    level_id          INT,
    level_name        TEXT,
    enrollment_status TEXT,
    course_id         INT,
    course_name       TEXT,
    tutor_id          BIGINT,
    tutor_name        TEXT,
    tutor_surname     TEXT,
    tutor_email       TEXT,
    enrolled_at       TEXT
)
LANGUAGE plpgsql
STABLE
AS $BODY$
BEGIN

    -- ── Path A: registered partner ────────────────────────────────────────────
    IF p_partner_id > 0 THEN
        RETURN QUERY
        SELECT
            'PARTNER'::TEXT                                         AS student_type,
            p.name::TEXT                                            AS student_name,
            p.surname::TEXT                                         AS student_surname,
            p.email::TEXT                                           AS student_email,
            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.course_id::INT                                       AS course_id,
            COALESCE(c.name::TEXT, 'STC Core')                      AS course_name,
            sp.tutor_id::BIGINT                                     AS tutor_id,
            COALESCE(tp.name::TEXT,    '')                          AS tutor_name,
            COALESCE(tp.surname::TEXT, '')                          AS tutor_surname,
            COALESCE(tp.email::TEXT,   '')                          AS tutor_email,
            sp.updated_at::TEXT                                     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
        LEFT JOIN public.courses c  ON c.course_id   = sp.course_id
        LEFT JOIN public.partners tp ON tp.partner_id = sp.tutor_id
        WHERE  sp.partner_id = p_partner_id
          AND  sp.course_id  = p_course_id;

    -- ── Path B: guest — look up by email ──────────────────────────────────────
    ELSE
        RETURN QUERY
        SELECT
            'GUEST'::TEXT                                           AS student_type,
            g.name::TEXT                                            AS student_name,
            g.surname::TEXT                                         AS student_surname,
            g.email::TEXT                                           AS student_email,
            0::INT                                                  AS level_id,
            COALESCE(l0.name::TEXT, 'Introducción al STC')          AS level_name,
            'ACTIVE'::TEXT                                          AS enrollment_status,
            g.course_id::INT                                        AS course_id,
            COALESCE(c.name::TEXT, 'STC Core')                      AS course_name,
            g.tutor_id::BIGINT                                      AS tutor_id,
            COALESCE(tp.name::TEXT,    '')                          AS tutor_name,
            COALESCE(tp.surname::TEXT, '')                          AS tutor_surname,
            COALESCE(tp.email::TEXT,   '')                          AS tutor_email,
            g.reg_date::TEXT                                        AS enrolled_at
        FROM   public.guests g
        LEFT JOIN public.levels l0  ON l0.level_id  = 0
        LEFT JOIN public.courses c  ON c.course_id  = g.course_id
        LEFT JOIN public.partners tp ON tp.partner_id = g.tutor_id
        WHERE  LOWER(g.email) = LOWER(p_email)
          AND  g.course_id    = p_course_id
        ORDER BY g.reg_date DESC
        LIMIT 1;
    END IF;

END;
$BODY$;

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