-- =============================================================================
-- VIEW: vw_student_management_master
-- PURPOSE: Unified student roster combining registered partners (student_profiles)
--          and guest leads (guests) for management screens.
-- STUDENT TYPES:
--   'PARTNER' = registered Zinzino partner with active student profile
--   'GUEST'   = lead without Zinzino ID, stored in guests table
-- NOTE: guest student_id is hardcoded to 0 (guests table has no surrogate PK)
-- =============================================================================

CREATE OR REPLACE VIEW public.vw_student_management_master AS
SELECT
    sp.partner_id                        AS student_id,
    'PARTNER'::text                      AS student_type,
    p.name                               AS student_name,
    p.surname                            AS student_surname,
    p.email                              AS student_email,
    p.phone                              AS student_phone,
    p.country                            AS student_country,
    p.zinzino_range                      AS student_zinzino_range,
    sp.level_id,
    sp.status                            AS enrollment_status,
    sp.remarks,
    sp.updated_at,
    sp.tutor_id,
    pt.name                              AS tutor_name,
    pt.surname                           AS tutor_surname,
    pt.email                             AS tutor_email
FROM student_profiles sp
JOIN  partners p  ON sp.partner_id = p.partner_id
LEFT JOIN partners pt ON sp.tutor_id   = pt.partner_id

UNION ALL

SELECT
    0                                    AS student_id,
    'GUEST'::text                        AS student_type,
    g.name                               AS student_name,
    g.surname                            AS student_surname,
    g.email                              AS student_email,
    g.phone                              AS student_phone,
    g.country                            AS student_country,
    'Guest'::character varying           AS student_zinzino_range,
    0                                    AS level_id,
    'ACTIVE'::text                       AS enrollment_status,
    NULL::character varying              AS remarks,
    NULL::timestamp without time zone    AS updated_at,
    g.tutor_id,
    pt.name                              AS tutor_name,
    pt.surname                           AS tutor_surname,
    pt.email                             AS tutor_email
FROM guests g
LEFT JOIN partners pt ON g.tutor_id = pt.partner_id;