-- =============================================================================
-- MIGRATION 009: Levels management system
-- PURPOSE:  (1) Adds metadata columns to levels table (name, description,
--               order_index, active) — safe to run even if some already exist
--           (2) Seeds Level 0 (system-reserved, required for guest enrollment)
--           (3) fn_admin_list_levels   — returns levels with enrollment counts
--           (4) fn_admin_upsert_level  — creates or updates a level
--           (5) fn_admin_toggle_level  — activates / deactivates a level
-- NOTE:     Level 0 is hardcoded in fn_enroll_partner_system for guest leads.
--           It cannot be deactivated or renamed via the admin functions.
-- =============================================================================


-- ─────────────────────────────────────────────────────────────────────────────
-- 1. ENSURE LEVELS TABLE HAS ALL NEEDED COLUMNS
--    CREATE TABLE IF NOT EXISTS handles the case where it doesn't exist at all.
--    ALTER TABLE ADD COLUMN IF NOT EXISTS handles partial schemas.
-- ─────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS public.levels (
    level_id          INT          PRIMARY KEY,
    course_id         INT          NOT NULL REFERENCES public.courses(course_id),
    name              VARCHAR(100) NOT NULL DEFAULT 'Nivel',
    description       TEXT,
    tutor_quota_limit INT          NOT NULL DEFAULT 10,
    order_index       SMALLINT     NOT NULL DEFAULT 0,
    active            BOOLEAN      NOT NULL DEFAULT true
);

ALTER TABLE public.levels ADD COLUMN IF NOT EXISTS name        VARCHAR(100) NOT NULL DEFAULT 'Nivel';
ALTER TABLE public.levels ADD COLUMN IF NOT EXISTS description TEXT;
ALTER TABLE public.levels ADD COLUMN IF NOT EXISTS order_index SMALLINT     NOT NULL DEFAULT 0;
ALTER TABLE public.levels ADD COLUMN IF NOT EXISTS active      BOOLEAN      NOT NULL DEFAULT true;


-- ─────────────────────────────────────────────────────────────────────────────
-- 2. SEED LEVEL 0  (system-reserved — always active, quota 999)
--    ON CONFLICT: keep existing data, just ensure it stays active
-- ─────────────────────────────────────────────────────────────────────────────
INSERT INTO public.levels (level_id, course_id, name, description, tutor_quota_limit, order_index, active)
VALUES (0, 1, 'Invitados',
        'Nivel de entrada para contactos invitados. Reservado por el sistema, no eliminar.',
        999, 0, true)
ON CONFLICT (level_id) DO UPDATE
    SET active = true;   -- always keep level 0 active


-- ─────────────────────────────────────────────────────────────────────────────
-- 3. fn_admin_list_levels
--    Returns all levels for a course (or all courses), with real-time
--    enrollment counts so the admin can see level usage at a glance.
-- ─────────────────────────────────────────────────────────────────────────────
CREATE OR REPLACE FUNCTION public.fn_admin_list_levels(
    p_course_id INT DEFAULT NULL
)
RETURNS TABLE (
    level_id          INT,
    course_id         INT,
    course_name       VARCHAR,
    name              VARCHAR,
    description       TEXT,
    tutor_quota_limit INT,
    order_index       SMALLINT,
    active            BOOLEAN,
    enrolled_count    BIGINT
)
LANGUAGE plpgsql
STABLE
AS $BODY$
BEGIN
    RETURN QUERY
    SELECT
        l.level_id,
        l.course_id,
        c.name::VARCHAR          AS course_name,
        l.name,
        l.description,
        l.tutor_quota_limit,
        l.order_index,
        l.active,
        COUNT(sp.partner_id)     AS enrolled_count
    FROM   public.levels l
    JOIN   public.courses c  ON c.course_id = l.course_id
    LEFT JOIN public.student_profiles sp
           ON  sp.level_id  = l.level_id
           AND sp.course_id = l.course_id
           AND sp.status    = 'ACTIVE'
    WHERE  (p_course_id IS NULL OR l.course_id = p_course_id)
    GROUP BY l.level_id, l.course_id, c.name, l.name, l.description,
             l.tutor_quota_limit, l.order_index, l.active
    ORDER BY l.course_id, l.order_index, l.level_id;
END;
$BODY$;


-- ─────────────────────────────────────────────────────────────────────────────
-- 4. fn_admin_upsert_level
--    Creates a new level (level_id omitted) or updates an existing one.
--    New level IDs are auto-assigned as MAX(level_id where level_id > 0) + 1.
--    Level 0 can only have its quota updated (name and course are fixed).
-- ─────────────────────────────────────────────────────────────────────────────
CREATE OR REPLACE FUNCTION public.fn_admin_upsert_level(
    p_data JSONB
)
RETURNS JSONB
LANGUAGE plpgsql
AS $BODY$
DECLARE
    v_level_id  INT;
    v_course_id INT;
    v_name      VARCHAR(100);
    v_desc      TEXT;
    v_quota     INT;
    v_order     SMALLINT;
    v_is_new    BOOLEAN;
BEGIN
    v_level_id  := (p_data->>'level_id')::INT;           -- NULL → create new
    v_course_id := COALESCE((p_data->>'course_id')::INT, 1);
    v_name      := TRIM(p_data->>'name');
    v_desc      := p_data->>'description';
    v_quota     := COALESCE((p_data->>'tutor_quota_limit')::INT, 10);
    v_order     := COALESCE((p_data->>'order_index')::SMALLINT, 0);

    -- ── Validation ────────────────────────────────────────────────────────────
    IF v_name IS NULL OR v_name = '' THEN
        RETURN jsonb_build_object('success', false, 'error', 'El nombre del nivel es obligatorio.');
    END IF;
    IF v_quota < 1 OR v_quota > 9999 THEN
        RETURN jsonb_build_object('success', false, 'error', 'La cuota debe estar entre 1 y 9999 alumnos.');
    END IF;

    -- ── CREATE ────────────────────────────────────────────────────────────────
    IF v_level_id IS NULL THEN
        v_is_new := true;
        SELECT COALESCE(MAX(level_id), 0) + 1 INTO v_level_id
        FROM   public.levels
        WHERE  level_id > 0;

        INSERT INTO public.levels
               (level_id, course_id, name, description, tutor_quota_limit, order_index, active)
        VALUES (v_level_id, v_course_id, v_name, v_desc, v_quota, v_order, true);

    -- ── UPDATE level 0 (quota only — name and course are system-reserved) ─────
    ELSIF v_level_id = 0 THEN
        v_is_new := false;
        UPDATE public.levels
        SET    tutor_quota_limit = v_quota,
               description       = COALESCE(v_desc, description)
        WHERE  level_id = 0;

    -- ── UPDATE regular level ──────────────────────────────────────────────────
    ELSE
        v_is_new := false;
        UPDATE public.levels
        SET    name              = v_name,
               description      = v_desc,
               tutor_quota_limit = v_quota,
               order_index       = v_order
        WHERE  level_id = v_level_id
          AND  course_id = v_course_id;

        IF NOT FOUND THEN
            RETURN jsonb_build_object('success', false, 'error', 'Nivel no encontrado.');
        END IF;
    END IF;

    RETURN jsonb_build_object(
        'success',  true,
        'action',   CASE WHEN v_is_new THEN 'created' ELSE 'updated' END,
        'level_id', v_level_id
    );
EXCEPTION WHEN OTHERS THEN
    RETURN jsonb_build_object('success', false, 'error', SQLERRM);
END;
$BODY$;


-- ─────────────────────────────────────────────────────────────────────────────
-- 5. fn_admin_toggle_level
--    Activates or deactivates a level. Prevents deactivating level 0 or
--    any level that still has active enrolled students.
-- ─────────────────────────────────────────────────────────────────────────────
CREATE OR REPLACE FUNCTION public.fn_admin_toggle_level(
    p_level_id  INT,
    p_course_id INT DEFAULT 1
)
RETURNS JSONB
LANGUAGE plpgsql
AS $BODY$
DECLARE
    v_current_active BOOLEAN;
    v_enrolled       INT;
BEGIN
    -- Level 0 is system-reserved
    IF p_level_id = 0 THEN
        RETURN jsonb_build_object(
            'success', false,
            'error',   'El nivel de Invitados (0) es reservado del sistema y no puede desactivarse.'
        );
    END IF;

    SELECT active INTO v_current_active
    FROM   public.levels
    WHERE  level_id = p_level_id AND course_id = p_course_id;

    IF NOT FOUND THEN
        RETURN jsonb_build_object('success', false, 'error', 'Nivel no encontrado.');
    END IF;

    -- If deactivating, check for active enrollments first
    IF v_current_active THEN
        SELECT COUNT(*) INTO v_enrolled
        FROM   public.student_profiles
        WHERE  level_id  = p_level_id
          AND  course_id = p_course_id
          AND  status    = 'ACTIVE';

        IF v_enrolled > 0 THEN
            RETURN jsonb_build_object(
                'success', false,
                'error',   'No se puede desactivar: hay ' || v_enrolled || ' alumnos activos en este nivel.'
            );
        END IF;
    END IF;

    UPDATE public.levels
    SET    active = NOT v_current_active
    WHERE  level_id = p_level_id AND course_id = p_course_id;

    RETURN jsonb_build_object(
        'success',  true,
        'level_id', p_level_id,
        'active',   NOT v_current_active
    );
EXCEPTION WHEN OTHERS THEN
    RETURN jsonb_build_object('success', false, 'error', SQLERRM);
END;
$BODY$;


-- ─────────────────────────────────────────────────────────────────────────────
-- 6. GRANTS
-- ─────────────────────────────────────────────────────────────────────────────
GRANT SELECT, INSERT, UPDATE ON public.levels TO "elfi1953_stc-eu";
GRANT EXECUTE ON FUNCTION public.fn_admin_list_levels(INT)            TO "elfi1953_stc-eu";
GRANT EXECUTE ON FUNCTION public.fn_admin_upsert_level(JSONB)          TO "elfi1953_stc-eu";
GRANT EXECUTE ON FUNCTION public.fn_admin_toggle_level(INT, INT)       TO "elfi1953_stc-eu";