---

create table fuels_type (id_fuel_type serial primary key, name_fuel_type varchar(50));
create table fuels (id_fuel serial primary key, fuel_type_id int references fuels_type(id_fuel_type), name_fuel varchar(50), price_fuel decimal (10, 2));
create table cars (id_car serial primary key, number_car varchar(50), gas_tank_volume int, engine_fuel_type_id int references fuels_type(id_fuel_type));
create table refuels (id_ref serial primary key, car_id int references cars(id_car), fuel_id int references fuels(id_fuel), date_time_ref timestamp, sum_price decimal(10, 2));
insert into fuels_type values (1, 'дизель'), (2, 'бензин');
insert into fuels values (1, 1, 'ДТ-Л', 65.00), (2, 2, 'АИ92', 70.00), (3, 2, 'АИ95', 75.00);
insert into cars values (1, 'A222BC', 50, 1), (2, 'D333EF', 30, 2), (3, 'K444NM', 70, 2);
insert into refuels values (1, 1, 1, '2025-09-25 04:17:15', 3250.00), (2, 2, 3, '2025-09-25 04:17:15', 2250.00);

create or replace function f1(p_car_id int, p_id_fuel int) returns decimal(10, 2) language plpgsql as $$ declare v_sum decimal; v_price_fuel decimal; v_gas_tank_volume decimal; begin select price_fuel into v_price_fuel from fuels where id_fuel = p_id_fuel; select gas_tank_volume into v_gas_tank_volume from cars where id_car = p_car_id; v_sum := v_price_fuel * v_gas_tank_volume; return v_sum; end $$;
create or replace procedure p1(p_car_id int, p_id_fuel int) language plpgsql as $$ declare v_total_sum decimal(10, 2); v_number_car varchar(50); v_name_fuel varchar(50); begin select c.number_car, f.name_fuel into v_number_car, v_name_fuel from cars c, fuels f where c.id_car = p_car_id and f.id_fuel = p_id_fuel; v_total_sum := f1(p_car_id, p_id_fuel); insert into refuels (car_id, fuel_id, date_time_ref, sum_price) values (p_car_id, p_id_fuel, current_timestamp, v_total_sum); raise notice 'Заправка успешна'; end $$;
create or replace procedure p2(p_car_id int, p_id_fuel int) language plpgsql as $$ declare v_car_fuel_type int; v_number_car varchar(50); v_fuel_type int; v_name_fuel varchar(50); v_total_sum decimal(10, 2); begin select engine_fuel_type_id, number_car into v_car_fuel_type, v_number_car from cars where id_car = p_car_id; select fuel_type_id, name_fuel into v_fuel_type, v_name_fuel from fuels where id_fuel = p_id_fuel; if v_car_fuel_type <> v_fuel_type then raise exception 'Неподходяший вид топлива!'; end if; v_total_sum := f1(p_car_id, p_id_fuel); insert into refuels (car_id, fuel_id, date_time_ref, sum_price) values (p_car_id, p_id_fuel, current_timestamp, v_total_sum); raise notice 'Транзакция успешно завершено! Авто: %, Топливо: %, Сумма: % руб.', v_number_car, v_name_fuel, v_total_sum; end $$;
create or replace function f2() returns trigger as $$ begin new.sum_price := new.sum_price * 0.95; return new; end; $$ language plpgsql;
create trigger t1 before insert on refuels for each row execute function f2();
=========================================================================
create table penalties (id_pen serial primary key, name_pen varchar(50), size_pen int);
create table positions (id_pos serial primary key, name_pos varchar(50), salary_pos int);
create table employees (id_em serial primary key, fname_emp varchar(50), lname_emp varchar(50), pos_id int references positions(id_pos));
create table calculations (id_calc serial primary key, emp_id int references employees(id_em), pen_id int references penalties(id_pen), date_calc date, salary_rest int);
insert into penalties values (1, 'Систематические опоздания', 2000), (2, 'Нарушение техники безопасности', 3000);
insert into positions values (1, 'Программист', 200000), (2, 'Тестировщик', 120000);
insert into employees values (1, 'Иван', 'Иванов', 1), (2, 'Иван', 'Николаев', 1), (3, 'Светлана', 'Котова', 2);
insert into calculations values (1, 1, 1, '2025-09-22', 198000), (2, 1, 2, '2025-09-22', 195000), (3, 2, 1, '2025-09-21', 198000);

create or replace function f1(p_id_em int, p_id_pen int) returns int language plpgsql as $$ declare v_basy int; v_size_pen int; v_salary_pos int; begin select p.salary_pos into v_salary_pos from employees e join positions p on e.pos_id = p.id_pos where e.id_em = p_id_em; select sum(pen.size_pen) into v_size_pen from calculations c join penalties pen on c.pen_id = pen.id_pen where c.emp_id = p_id_em; select size_pen into v_basy from penalties where id_pen = p_id_pen; return v_salary_pos - v_size_pen - v_basy; end $$;
create or replace procedure p1(p_id_em int, p_id_pen int) language plpgsql as $$ declare v_salary_pos int; v_sum int; begin select p.salary_pos into v_salary_pos from employees e join positions p on e.pos_id = p.id_pos where e.id_em = p_id_em; if v_salary_pos > 150000 then v_sum := f1(p_id_em, p_id_pen); insert into calculations (emp_id, pen_id, date_calc, salary_rest) values (p_id_em, p_id_pen, current_date, v_sum); raise notice 'Запись добавлено!'; commit; else raise notice 'Сумма должна быть больше 150000'; rollback; end if; end $$;
create table log_calc (id_log serial primary key, emp_id int references employees(id_em), total_penalties int, type_opration varchar(50));
create or replace function f2() returns trigger as $$ declare v_sum int; begin select count(*) into v_sum from calculations where emp_id = new.emp_id; insert into log_calc (emp_id, total_penalties, type_opration) values (new.emp_id, v_sum, tg_op); return new; end; $$ language plpgsql;
create trigger t1 after insert on calculations for each row execute function f2();