---

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 $$;
create or replace function get_top_clients_by_calories(p_id int, start_date date, end_date date) returns table(member_id int, first_name varchar, last_name varchar, total_calories decimal) language plpgsql as $$ begin return query select m.id as member_id, m.first_name, m.last_name, sum((v.duration_minutes / 60.0) * w.calories_per_hour) as total_calories from visits v join members m on v.member_id = m.id join workouts w on v.workout_id = w.id where v.visit_date between start_date and end_date group by m.id, m.first_name, m.last_name order by total_calories desc limit p_id; end $$;
create table employees (id serial primary key, first_name varchar(50), last_name varchar(50), salary decimal(10, 2), vacation_start date, vacation_end date);
insert into employees values (1, 'Иван', 'Иванов', 70000.00, '2026-06-01', '2026-06-15');
create or replace function get_top_clients_by_calories(p_id int, start_date date, end_date date) returns table(member_id int, first_name varchar, last_name varchar, total_calories decimal) language plpgsql as $$ begin return query select m.id as member_id, m.first_name, m.last_name, sum((v.duration_minutes / 60.0) * w.calories_per_hour) as total_calories from visits v join members m on v.member_id = m.id join workouts w on v.workout_id = w.id where v.visit_date between start_date and end_date group by m.id, m.first_name, m.last_name order by total_calories desc limit p_id; end $$;
create or replace view v_employee_vacations as select id, first_name, last_name, salary as monthly_salary, vacation_start, vacation_end, calculate_vacation_pay(salary, vacation_start, vacation_end) as vacation_pay from employees;
create table employes (id serial primary key, first_name varchar(50), last_name varchar(50), salary decimal(10, 2), sick_start date, sick_end date);
insert into employes values (1, 'Петр', 'Иванов', 67430.14, '2024-03-01', '2024-03-15');
create or replace function calculate_sick_leave(p_salary decimal, p_start_date date, p_end_date date) returns decimal language plpgsql as $$ declare v_daily decimal; v_days int; begin if p_start_date is null or p_end_date is null then return 0.00; end if; v_days := p_end_date - p_start_date + 1; v_daily := p_salary / 29.3; return v_daily * v_days; end $$;
create or replace view v_employee_stick_leaves as select id, first_name, salary, sick_start, sick_end, calculate_sick_leave(salary, sick_start, sick_end) as sick_leave_amount from employes;