MonkeysScript.sql

show data_directory;

-- Заменить все на varchar

-- Таблица "Пользователи" 
create table users (user_id int generated by default as identity primary key,
					user_login text not null unique, 
					user_password text not null, 
					user_mail text, 
					user_telegram text, 
					user_FIO text);

create index users_user_login_index on users(user_login);
create index users_user_mail_index on users(user_mail);
create index users_user_telegram_index on users(user_telegram);

-- select * from users;
-- drop table users;
-- delete from users;


insert into users(user_id, user_login, user_password)
	values
	(nextval('users_user_id_seq'),'Петя', '1234')
	
	
	
-- Таблица "Папки"
create table folders(folder_id int generated by default as identity primary key,
					user_id int,
					folder_name text not null,
					folder_access int not null default 1,
					constraint fk_user
      					foreign key(user_id) 
	  						references  users(user_id)
	  							on delete cascade,
	  				check (folder_access >= 1 and folder_access <= 3));
	  			
create index folders_folder_name_index on folders(folder_name);
create index folders_folder_access_index on folders(folder_access);

-- select * from folders;	
-- drop table folders;




-- Таблица "Приватный доступ"
create table private_access(folder_id int not null,
							user_id int not null,
							constraint fk_folder
      							foreign key(folder_id) 
	  								references folders(folder_id)
	  									on delete cascade,
							constraint fk_user
      							foreign key(user_id) 
	  								references  users(user_id)
	  									on delete cascade);
	  								
create index private_access_user_id_index on private_access(user_id);
create index private_access_folder_id_index on private_access(folder_id);
	  								
-- select * from private_access;
-- drop table private_access;




-- Таблица "Сохраненные папки", мб переименовать в favorite
create table saved_folders(user_id int not null,
							folder_id int not null,
							constraint fk_user
								foreign key(user_id)
									references users(user_id)
										on delete cascade,
							constraint fk_folder
								foreign key(folder_id)
									references folders(folder_id)
										on delete cascade);

create index saved_folders_user_id_index on saved_folders(user_id);	

-- select * from saved_folders;
-- drop table saved_folders;



-- Избранные файлы, переименовать в favorite
create table saved_files(user_id int not null,
							folder_id int not null,
							file_path text not null,
							constraint fk_user
								foreign key(user_id)
									references users(user_id)
										on delete cascade,
							constraint fk_folder
								foreign key(folder_id)
									references folders(folder_id)
										on delete cascade);
									
create index saved_files_user_id_index on saved_files(user_id);							
									
-- select * from saved_files;
-- drop table saved_files;