---

create database if not exists museum;
use museum;

create table category (
    id int primary key auto_increment,
    name varchar(50) not null unique
);

create table visitor (
    id int primary key auto_increment,
    name varchar(50) not null,
    surname varchar(50) not null
);

create table device (
    id int primary key auto_increment,
    name varchar(100) not null,
    category int not null,
    price decimal(10, 2) not null,
    status varchar(20),
    foreign key (category) references category(id)
);

create table session (
    id int primary key auto_increment,
    id_visitor int not null,
    id_device int not null,
    data_vid datetime not null,
    data_voz datetime,
    oplata int default 0,
    foreign key (id_visitor) references visitor(id),
    foreign key (id_device) references device(id)
);

insert into category (name) values 
('робототехника и vr'),
('высокое напряжение'),
('экспериментальный');

insert into visitor (name, surname) values 
('иван', 'иванов'),
('петр', 'петров'),
('сидор', 'сидоров'),
('алексей', 'кузнецов');

insert into device (name, category, price, status) values 
('vr-шлем экспедиция на марс', 1, 250.00, 'проверка'),
('робот-собака борис', 1, 400.00, null),
('катушка тесла', 2, 300.00, null),
('квантовый компьютер', 3, 500.00, null),
('генератор ван де граафа', 2, 200.00, null),
('плазменный излучатель', 2, 350.00, null);

insert into session (id_visitor, id_device, data_vid, data_voz, oplata) values 
(1, 1, '2026-06-17 10:00:00', null, 0),
(2, 2, '2026-06-17 11:00:00', '2026-06-17 14:00:00', 0),
(1, 3, '2026-06-17 13:00:00', null, 0),
(3, 3, '2026-06-17 14:30:00', null, 0),
(2, 3, '2026-06-17 12:00:00', '2026-06-17 14:00:00', 0),
(4, 4, '2026-06-17 09:00:00', '2026-06-17 11:00:00', 0),
(3, 5, '2026-06-17 16:00:00', null, 0);

create view unpaid_high_voltage_rentals as
select 
    v.name as "имя",
    v.surname as "фамилия",
    s.id_device as "id девайса",
    d.name as "название девайса",
    s.data_vid as "время начала",
    'не оплачено' as "статус оплаты"
from 
    session s
join 
    device d on s.id_device = d.id
join 
    category c on d.category = c.id
join 
    visitor v on s.id_visitor = v.id
where 
    s.data_voz is null
    and c.name = 'высокое напряжение'
    and s.oplata = 0;