---

create table users (
    id_user int primary key auto_increment,
    name varchar(100),
    age int
);

create table movies (
    id_movie int primary key auto_increment,
    title varchar(100),
    genre varchar(50),
    duration int
);

create table ratings (
    id_rating int primary key auto_increment,
    id_user int,
    id_movie int,
    score int,
    foreign key (id_user) references users(id_user),
    foreign key (id_movie) references movies(id_movie)
);

create table views (
    id_view int primary key auto_increment,
    id_user int,
    id_movie int,
    view_date date,
    watch_time int,
    foreign key (id_user) references users(id_user),
    foreign key (id_movie) references movies(id_movie)
);

create table subscriptions (
    id_sub int primary key auto_increment,
    id_user int,
    type varchar(50),
    price decimal(8,2),
    start_date date,
    status varchar(45),
    date_of_last_renewal timestamp,
    foreign key (id_user) references users(id_user)
);

insert into users (name, age) values
('иван петров', 25),
('анна смирнова', 30),
('сергей кузнецов', 22),
('мария иванова', 28),
('дмитрий соколов', 35);

insert into movies (title, genre, duration) values
('интерстеллар', 'фантастика', 169),
('зеленая миля', 'драма', 189),
('матрица', 'фантастика', 136),
('гладиатор', 'боевик', 155),
('король лев', 'мультфильм', 88);

insert into ratings (id_user, id_movie, score) values
(1, 1, 9),
(1, 3, 8),
(2, 2, 10),
(3, 4, 7),
(4, 5, 9),
(5, 1, 8);

insert into views (id_user, id_movie, view_date, watch_time) values
(1, 1, '2026-01-10', 169),
(1, 3, '2026-02-15', 136),
(2, 2, '2026-01-20', 189),
(3, 4, '2026-03-01', 155),
(4, 5, '2026-03-05', 88),
(5, 1, '2026-04-01', 169);

insert into subscriptions (id_user, type, price, start_date, status, date_of_last_renewal) values
(1, 'премиум', 599.00, '2026-01-01', 'активна', '2026-05-01 10:00:00'),
(1, 'базовая', 199.00, '2025-06-01', 'завершена', '2025-12-01 10:00:00'),
(2, 'премиум', 599.00, '2026-02-01', 'активна', '2026-05-01 10:00:00'),
(3, 'базовая', 199.00, '2026-03-01', 'активна', '2026-05-01 10:00:00'),
(4, 'премиум', 599.00, '2025-11-01', 'активна', '2026-05-01 10:00:00'),
(5, 'базовая', 199.00, '2026-01-15', 'завершена', '2026-04-15 10:00:00');

drop function if exists get_total_subscriptions;

delimiter $$

create function get_total_subscriptions(p_id_user int)
returns decimal(10,2)
deterministic
reads sql data
begin
    declare total decimal(10,2);

    select coalesce(sum(price), 0)
    into total
    from subscriptions
    where id_user = p_id_user;

    return total;
end$$

delimiter ;

drop procedure if exists fill_user_income;

delimiter $$

create procedure fill_user_income()
begin
    drop table if exists user_income;

    create table user_income (
        id_user int primary key,
        spent_amount decimal(10,2)
    );

    insert into user_income (id_user, spent_amount)
    select id_user, get_total_subscriptions(id_user)
    from users;

    select *
    from user_income
    order by spent_amount desc;
end$$

delimiter ;

call fill_user_income();