-- 016 — Motivos de promoción de nivel + columnas en lifecycle_log
-- Backup previo obligatorio antes de ejecutar.

-- 1. Tabla de motivos
CREATE TABLE public.promotion_reasons (
    reason_id  serial  NOT NULL,
    label      text    NOT NULL,
    active     boolean NOT NULL DEFAULT true,
    sort_order integer NOT NULL DEFAULT 0,
    CONSTRAINT pk_promotion_reasons PRIMARY KEY (reason_id)
);

-- 2. Ampliar student_lifecycle_log con reason_id (FK nullable) y comments
ALTER TABLE public.student_lifecycle_log
    ADD COLUMN reason_id integer,
    ADD COLUMN comments  text;

ALTER TABLE public.student_lifecycle_log
    ADD CONSTRAINT fk_lifecycle_reason
        FOREIGN KEY (reason_id)
        REFERENCES public.promotion_reasons(reason_id)
        ON DELETE RESTRICT;

-- 3. Datos base
INSERT INTO public.promotion_reasons (label, active, sort_order) VALUES
    ('Completó todos los módulos del nivel',         true,  1),
    ('Domina los contenidos del nivel actual',        true,  2),
    ('Progresión acelerada acordada con el equipo',   true,  3),
    ('Cambio de disponibilidad horaria',              true,  4),
    ('Corrección de inscripción inicial',             true,  5),
    ('Otros',                                         true, 99);

-- 4. Funciones
CREATE OR REPLACE FUNCTION public.fn_tutor_list_promotion_reasons()
RETURNS TABLE(reason_id int, label text, sort_order int)
LANGUAGE sql STABLE
AS $function$
    SELECT reason_id, label, sort_order
    FROM public.promotion_reasons
    WHERE active = true
    ORDER BY sort_order, reason_id;
$function$;

CREATE OR REPLACE FUNCTION public.fn_tutor_promote_student(
    p_tutor_id     bigint,
    p_student_id   bigint,
    p_course_id    int,
    p_new_level_id int,
    p_reason_id    int,
    p_comments     text DEFAULT NULL
)
RETURNS jsonb
LANGUAGE plpgsql
AS $function$
DECLARE
    v_prev_level_id int;
    v_level_name    text;
    v_reason_label  text;
BEGIN
    SELECT sp.level_id INTO v_prev_level_id
    FROM public.student_profiles sp
    WHERE sp.partner_id = p_student_id
      AND sp.tutor_id   = p_tutor_id
      AND sp.course_id  = p_course_id;

    IF NOT FOUND THEN
        RETURN jsonb_build_object('error', 'Alumno no encontrado o no pertenece a este tutor.');
    END IF;

    IF v_prev_level_id = p_new_level_id THEN
        RETURN jsonb_build_object('error', 'El alumno ya está en ese nivel.');
    END IF;

    SELECT name INTO v_level_name
    FROM public.levels
    WHERE level_id = p_new_level_id AND course_id = p_course_id AND active = true;

    IF NOT FOUND THEN
        RETURN jsonb_build_object('error', 'El nivel destino no existe o está inactivo.');
    END IF;

    SELECT label INTO v_reason_label
    FROM public.promotion_reasons
    WHERE reason_id = p_reason_id AND active = true;

    IF NOT FOUND THEN
        RETURN jsonb_build_object('error', 'Motivo de promoción no válido.');
    END IF;

    UPDATE public.student_profiles
    SET level_id = p_new_level_id, updated_at = NOW()
    WHERE partner_id = p_student_id AND tutor_id = p_tutor_id AND course_id = p_course_id;

    INSERT INTO public.student_lifecycle_log
        (student_id, level_id, previous_level_id, new_level_id,
         actor_id, actor_role, action_type, reason, reason_id, comments)
    VALUES
        (p_student_id, v_prev_level_id, v_prev_level_id, p_new_level_id,
         p_tutor_id, 'TUTOR', 'LEVEL_CHANGE', v_reason_label, p_reason_id, p_comments);

    RETURN jsonb_build_object(
        'success',        true,
        'new_level_id',   p_new_level_id,
        'new_level_name', v_level_name
    );
END;
$function$;

CREATE OR REPLACE FUNCTION public.fn_admin_list_promotion_reasons()
RETURNS TABLE(reason_id int, label text, active boolean, sort_order int)
LANGUAGE sql STABLE
AS $function$
    SELECT reason_id, label, active, sort_order
    FROM public.promotion_reasons
    ORDER BY sort_order, reason_id;
$function$;

CREATE OR REPLACE FUNCTION public.fn_admin_upsert_promotion_reason(p_data jsonb)
RETURNS jsonb
LANGUAGE plpgsql
AS $function$
DECLARE
    v_id    int  := (p_data->>'reason_id')::int;
    v_label text := trim(p_data->>'label');
    v_order int  := COALESCE((p_data->>'sort_order')::int, 0);
BEGIN
    IF v_label IS NULL OR v_label = '' THEN
        RETURN jsonb_build_object('error', 'El campo label es obligatorio.');
    END IF;

    IF v_id IS NOT NULL THEN
        UPDATE public.promotion_reasons
        SET label = v_label, sort_order = v_order
        WHERE reason_id = v_id;

        IF NOT FOUND THEN
            RETURN jsonb_build_object('error', 'Motivo no encontrado.');
        END IF;
        RETURN jsonb_build_object('success', true, 'reason_id', v_id);
    ELSE
        INSERT INTO public.promotion_reasons (label, sort_order)
        VALUES (v_label, v_order)
        RETURNING reason_id INTO v_id;

        RETURN jsonb_build_object('success', true, 'reason_id', v_id);
    END IF;
END;
$function$;

CREATE OR REPLACE FUNCTION public.fn_admin_toggle_promotion_reason(p_reason_id int)
RETURNS jsonb
LANGUAGE plpgsql
AS $function$
DECLARE
    v_new_active boolean;
BEGIN
    UPDATE public.promotion_reasons
    SET active = NOT active
    WHERE reason_id = p_reason_id
    RETURNING active INTO v_new_active;

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

    RETURN jsonb_build_object('success', true, 'active', v_new_active);
END;
$function$;

-- 6. Permisos para el usuario de la app
GRANT SELECT, INSERT, UPDATE ON public.promotion_reasons TO "elfi1953_stc-eu";
GRANT USAGE, SELECT ON SEQUENCE public.promotion_reasons_reason_id_seq TO "elfi1953_stc-eu";
GRANT USAGE, SELECT ON SEQUENCE public.student_lifecycle_log_lifecycle_log_id_seq TO "elfi1953_stc-eu";
GRANT EXECUTE ON FUNCTION public.fn_tutor_list_promotion_reasons() TO "elfi1953_stc-eu";
GRANT EXECUTE ON FUNCTION public.fn_tutor_promote_student(bigint, bigint, int, int, int, text) TO "elfi1953_stc-eu";
GRANT EXECUTE ON FUNCTION public.fn_admin_list_promotion_reasons() TO "elfi1953_stc-eu";
GRANT EXECUTE ON FUNCTION public.fn_admin_upsert_promotion_reason(jsonb) TO "elfi1953_stc-eu";
GRANT EXECUTE ON FUNCTION public.fn_admin_toggle_promotion_reason(int) TO "elfi1953_stc-eu";