---

create or replace function get_expiring_subscriptions(p_days_ahead int, p_date date) returns table(member_name varchar, end_date date, club_name varchar) language plpgsql as $$ begin return query select (m.first_name || ' ' || m.last_name)::varchar, s.end_date, c.name from subscriptions s join members m on s.member_id = m.id join clubs c on m.club_id = c.id where s.end_date >= p_date and s.end_date <= (p_date + p_days_ahead) and s.is_paid = false; end $$;
create or replace function get_club_full_report(p_id int) returns table(club_name varchar, active_members bigint, occupancy_percent decimal, subscription_revenue decimal, training_revenue decimal, total_revenue decimal) language plpgsql as $$ declare v_club_name varchar; v_max_capacity int; v_active_members bigint; v_sub_rev decimal; v_train_rev decimal; begin select name, max_capacity into v_club_name, v_max_capacity from clubs where id = p_id; select count(*) into v_active_members from members where club_id = p_id and is_active = true; select sum(s.price_paid) into v_sub_rev from subscriptions s join members m on s.member_id = m.id where m.club_id = p_id and s.is_paid = true; select sum(pt.price) into v_train_rev from personal_trainings pt join members m on pt.member_id = m.id where m.club_id = p_id; return query select v_club_name, v_active_members, round((v_active_members * 100.0 / v_max_capacity), 1), v_sub_rev, v_train_rev, v_sub_rev + v_train_rev; end $$;