---

create table products (id serial primary key, name varchar(50) not null, price decimal(10, 2) not null, quantity int not null default 0, category varchar(50));
create table orders (id serial primary key, product_id int references products(id), quantity int not null, order_date date default current_date);
insert into products (name, price, quantity, category) values ('laptop', 1200.00, 10, 'electronics'), ('mouse', 25.50, 50, 'electronics'), ('keyboard', 350.00, 5, 'electronics'), ('desk', 350.00, 5, 'furniture'), ('chair', 150.00, 15, 'furniture');
insert into orders values (1, 1, 3, '2024.07.07'), (2, 2, 5, '2026.09.09'), (3, 4, 8, '2025.01.01'), (4, 3, 1, '2007.03.02'), (5, 5, 4, '2026.05.20');
create or replace function get_membership_price(p_club_id int, p_birth_date date) returns decimal language plpgsql as $$ declare base_price decimal; age int; final_price decimal; begin select price into base_price from products where id = p_club_id; age := extract(year from age(current_date, p_birth_date)); if age < 18 then final_price := base_price * 0.70; elseif age between 18 and 65 then final_price := base_price; else final_price := base_price * 0.50; end if; return final_price; end $$;
create or replace function calculate_calories_burned(p_workout_id int, p_duration_minutes int) returns integer language plpgsql strict as $$ declare calories_per_hour int; calories_burned numeric; begin select quantity into calories_per_hour from products where id = p_workout_id; calories_burned := (p_duration_minutes / 60) * calories_per_hour; return calories_burned; end $$;
create or replace function get_occupancy_percent(p_club_id int) returns decimal language plpgsql as $$ declare max_cap int; active_count int; percent_occupied numeric; begin select quantity into max_cap from products where id = p_club_id; select quantity into active_count from products where id = p_club_id; if max_cap = 0 then return 0; end if; percent_occupied:= (2 / max_cap) * 100; return active_count; end $$;