-- =============================================================================
-- MIGRATION 014: Partner admin panel support
-- PURPOSE:
--   (1) Add created_at to profile tables (tutor, professor, superadmin)
--   (2) vw_partner_list  — extends vw_user_identity_roles with student_count
--   (3) fn_admin_partner_detail — full partner JSON for the admin detail panel
-- =============================================================================

-- ─────────────────────────────────────────────────────────────────────────────
-- 1. CREATED_AT ON PROFILE TABLES
-- ─────────────────────────────────────────────────────────────────────────────
ALTER TABLE public.tutor_profiles
    ADD COLUMN IF NOT EXISTS created_at TIMESTAMPTZ DEFAULT NOW();

ALTER TABLE public.professor_profiles
    ADD COLUMN IF NOT EXISTS created_at TIMESTAMPTZ DEFAULT NOW();

ALTER TABLE public.superadmin_profiles
    ADD COLUMN IF NOT EXISTS created_at TIMESTAMPTZ DEFAULT NOW();

-- ─────────────────────────────────────────────────────────────────────────────
-- 2. VW_PARTNER_LIST
--    Adds student_count (active students per tutor) to vw_user_identity_roles.
-- ─────────────────────────────────────────────────────────────────────────────
CREATE OR REPLACE VIEW public.vw_partner_list AS
SELECT
    v.partner_id,
    v.name,
    v.surname,
    v.email,
    v.phone,
    v.country,
    v.zinzino_range,
    v.is_tutor,
    v.is_professor,
    v.is_superadmin,
    v.is_student,
    COALESCE(sc.student_count, 0)::INT AS student_count
FROM public.vw_user_identity_roles v
LEFT JOIN (
    SELECT tutor_id, COUNT(*)::INT AS student_count
    FROM   public.student_profiles
    WHERE  status = 'ACTIVE'
    GROUP  BY tutor_id
) sc ON sc.tutor_id = v.partner_id;

GRANT SELECT ON public.vw_partner_list TO "elfi1953_stc-eu";

-- ─────────────────────────────────────────────────────────────────────────────
-- 3. FN_ADMIN_PARTNER_DETAIL
--    Returns a single JSONB document with all data needed by the detail panel:
--      partner   — base identity fields
--      roles     — boolean flags + created_at per profile table
--      tutor     — student_count + students[] (all statuses)
--      professor — assigned levels[] + session_count
--      student   — enrollment row (tutor, level, status, course)
-- ─────────────────────────────────────────────────────────────────────────────
CREATE OR REPLACE FUNCTION public.fn_admin_partner_detail(p_partner_id BIGINT)
RETURNS JSONB
LANGUAGE plpgsql STABLE
AS $$
DECLARE
    v_partner   JSONB;
    v_roles     JSONB;
    v_tutor     JSONB;
    v_professor JSONB;
    v_student   JSONB;
BEGIN
    -- Base partner data
    SELECT jsonb_build_object(
        'partner_id',    p.partner_id,
        'name',          p.name,
        'surname',       p.surname,
        'email',         p.email::TEXT,
        'phone',         p.phone::TEXT,
        'country',       p.country::TEXT,
        'zinzino_range', p.zinzino_range
    ) INTO v_partner
    FROM public.partners p
    WHERE p.partner_id = p_partner_id;

    IF v_partner IS NULL THEN
        RETURN jsonb_build_object('error', 'Partner no encontrado');
    END IF;

    -- Role flags and activation dates
    SELECT jsonb_build_object(
        'is_tutor',         COALESCE(tp.status = 'ACTIVE', false),
        'tutor_since',      tp.created_at,
        'is_professor',     COALESCE(pp.status = 'ACTIVE', false),
        'professor_since',  pp.created_at,
        'is_superadmin',    COALESCE(sa.status = 'ACTIVE', false),
        'superadmin_since', sa.created_at,
        'is_student',       EXISTS (
            SELECT 1 FROM public.student_profiles WHERE partner_id = p_partner_id
        )
    ) INTO v_roles
    FROM (SELECT 1) _d
    LEFT JOIN public.tutor_profiles      tp ON tp.partner_id = p_partner_id
    LEFT JOIN public.professor_profiles  pp ON pp.partner_id = p_partner_id
    LEFT JOIN public.superadmin_profiles sa ON sa.partner_id = p_partner_id;

    -- Tutor: student list (all statuses)
    SELECT jsonb_build_object(
        'student_count', COUNT(*) FILTER (WHERE sp.status::TEXT = 'ACTIVE'),
        'students', COALESCE(
            jsonb_agg(
                jsonb_build_object(
                    'partner_id', ps.partner_id,
                    'name',       ps.name,
                    'surname',    ps.surname,
                    'email',      ps.email::TEXT,
                    'level_id',   sp.level_id,
                    'level_name', COALESCE(l.name, 'Nivel ' || sp.level_id),
                    'status',     sp.status::TEXT
                ) ORDER BY ps.surname, ps.name
            ), '[]'::jsonb
        )
    ) INTO v_tutor
    FROM   public.student_profiles sp
    JOIN   public.partners  ps ON ps.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_partner_id;

    -- Professor: assigned levels + session count
    v_professor := jsonb_build_object(
        'levels', COALESCE(
            (SELECT jsonb_agg(
                jsonb_build_object(
                    'level_id',   COALESCE(cpl.level_id, 0),
                    'level_name', COALESCE(l.name, 'Todos los niveles')
                ) ORDER BY COALESCE(l.order_index, 0)
             )
             FROM   public.course_professor_levels cpl
             LEFT JOIN public.levels l ON l.level_id = cpl.level_id
             WHERE  cpl.professor_id = p_partner_id),
            '[]'::jsonb
        ),
        'session_count', (
            SELECT COUNT(*) FROM public.sessions WHERE created_by = p_partner_id
        )
    );

    -- Student: enrollment row
    SELECT jsonb_build_object(
        'tutor_id',    sp.tutor_id,
        'tutor_name',  TRIM(COALESCE(pt.name, '') || ' ' || COALESCE(pt.surname, '')),
        'tutor_email', COALESCE(pt.email::TEXT, ''),
        'level_id',    sp.level_id,
        'level_name',  COALESCE(l.name, 'Nivel ' || sp.level_id),
        'status',      sp.status::TEXT,
        'course_name', COALESCE(c.name, 'STC Core'),
        'enrolled_at', sp.updated_at
    ) INTO v_student
    FROM   public.student_profiles sp
    LEFT JOIN public.partners pt ON pt.partner_id = sp.tutor_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
    WHERE  sp.partner_id = p_partner_id
    LIMIT 1;

    RETURN jsonb_build_object(
        'partner',   v_partner,
        'roles',     v_roles,
        'tutor',     COALESCE(v_tutor,     '{}'::jsonb),
        'professor', COALESCE(v_professor, '{}'::jsonb),
        'student',   v_student
    );
END;
$$;

GRANT EXECUTE ON FUNCTION public.fn_admin_partner_detail(BIGINT) TO "elfi1953_stc-eu";