create extension pgcrypto;

create domain email as varchar(600)
check (
    value ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'
);

create domain phone as varchar(50)
check (
	value ~ '^\+[0-9]{10,15}$'
);

create type rent_status as enum ('Новая', 'Подтверждена', 'Выдана', 'Завершена', 'Отменена');

create table cmd_type(
	id serial primary key,
	type varchar(300)
);

create table manufacturer(
	id serial primary key,
	name varchar(300)
);

create table pu_point(
	id serial primary key,
	name varchar(300),
	address varchar(600)
);

create table commodity(
	id serial primary key,
	inv_number char(4),
	supply_name varchar,
	supply_type int references cmd_type(id),
	rent decimal(10,2),
	manufacturer int references manufacturer(id),
	pick_up_point int references pu_point(id),
	img_path text
);

create table client(
	id serial primary key,
	name varchar(600),
	phone phone,
	email email
);

create table employee(
	id serial primary key,
	name varchar(600)
);

create table rent(
	id serial primary key,
	commodity int references commodity(id),
	client int references client(id),
	r_start date,
	r_end date,
	status rent_status,
	employee int references employee(id)
);

create table roles(
	id serial primary key,
	name varchar(300),
	level int
);

create table users(
	id serial primary key,
	name varchar(300),
	role int references roles(id),
	hash text not null
);

create table permission_map(
	id serial primary key,
	perm varchar(300),
	req_level int
);

insert into cmd_type(type) values
('Велосипед'), ('Самокат'), ('Лыжи'), ('Ролики'), ('Сноуборд');

insert into manufacturer(name) values
('Trek'), ('UrbanRide'), ('SnowFast'),
('SpeedRun'), ('Giant'), ('Arctic');

insert into pu_point(name, address) values
('Центральный', 'ул. Мира 15'),
('Северный', 'пр. Победы 10'),
('Южный', 'ул. Ленина 8');

insert into commodity(inv_number, supply_name,
					  supply_type, rent, manufacturer,
					  pick_up_point, img_path) values
('1001', 'Trek X200', 1, 900.00, 1, 1, 'assets/Велосипед TrekX200.jpg'),
('1002', 'Urban Pro', 2, 400.00, 2, 2, 'assets/Самокат Urban Pro.jpg'),
('1003', 'SnowFast 300', 1, 1200.00, 3, 1, 'assets/Лыжи SnowFast 300.jpg'),
('1004', 'SpeedRun', 1, 700.00, 4, 3, 'assets/Ролики SpeedRun.jpg'),
('1005', 'Giant ATX', 1, 850.00, 5, 2, 'assets/Велосипед Giant ATX.jpg'),
('1006', 'Arctic Pro', 1, 1500.00, 6, 3, 'assets/Сноуборд Arctic Pro.jpg');

insert into client(name, phone, email) values
('Иванов И.И.', '+79997776655', 'ivan@mail.ru'),
('Сидоров С.С.', '+79887776655', 'sid@mail.ru'),
('Кузнецов К.К.', '+7776655443', 'kuz@mail.ru'),
('Смирнова А.А.', '+79665554433', 'smirnova@mail.ru'),
('Васильев Д.Д.', '+79554443322', 'vasiliev@mail.ru');

insert into employee(name) values
('Петров П.П.'), ('Орлов А.А.');

insert into rent(commodity, client, r_start, r_end, status, employee) values
(1, 1, '2024-04-12', '2024-04-15', 'Новая', 1),
(2, 2, '2024-04-10', '2024-04-11', 'Подтверждена', 2),
(3, 1, '2024-04-20', '2024-04-25', 'Выдана', 1),
(4, 3, '2024-04-15', '2024-04-17', 'Завершена', 2),
(5, 4, '2024-04-18', '2024-04-20', 'Отменена', 1),
(6, 5, '2024-04-01', '2024-04-05', 'Новая', 2);

insert into roles(name, level) values
('admin', 100), ('employee', 50), ('client', 25), ('guest', 0);

insert into users(name, role, hash) values
('admin', 1, crypt('admin123', gen_salt('bf'))),
('employee', 2, crypt('employee123', gen_salt('bf'))),
('client1', 3, crypt('client123', gen_salt('bf'))),
('client2', 3, crypt('client321', gen_salt('bf')));

insert into permission_map(perm, req_level) values
('read.equipment', 0),
('read.requests', 50),
('create.equipment', 100),
('create.requests', 25),
('update.equipment', 100),
('update.request.status', 50),
('delete.equipment', 100);