create table bs_meta_permission
(
	permission_level integer primary key,
	name varchar(10)
);
insert into bs_meta_permission values (0,'超级管理员'),(1,'管理员'),(2,'图书馆借阅处'),(3,'读者');


create table bs_meta_account
(
	account_status integer primary key,
	name varchar(10)
);
insert into bs_meta_account values (0,'隐藏'),(1,'禁止登陆'),(2,'待验证'),(3,'正常');

create table bs_meta_book
(
	status integer primary key,
	name varchar(10)
);
insert into bs_meta_book values (0,'隐藏'),(1,'禁用'),(2,'正常');

create table bs_meta_bookstatus
(
	status integer primary key,
	name varchar(10)
);
insert into bs_meta_bookstatus values (0,'借出'),(1,'丢失'),(2,'在馆');

create table bs_user
(
	username varchar(10) primary key,
	password char(32), 
	nickname varchar(10),
	permission_level integer references bs_meta_permission(permission_level),
	account_status integer references bs_meta_account(account_status)
);
create table bs_level
(
	user_lv integer primary key,
	min_amount_limit integer,
	max_amount_limit integer,
	min_time_limit integer,
	max_time_limit integer,
	min_balance decimal(10,2)
);
create table bs_reader
(
	username varchar(10) references bs_user(username),
	realname varchar(10),
	realid char(18),
	phonenum varchar(15),
	user_lv integer references bs_level(user_lv),
	user_exp integer,
	borrow_limit integer,
	borrow_used integer,
	borrow_time_limit integer,
	bonus integer,
	balance decimal(10,2)
);
create table bs_booktype
(
	book_type varchar(10) primary key
);
create table bs_book
(
	class_id integer primary key,
	isbn varchar(13) unique,
	name varchar(20),
	book_type varchar(10) references bs_booktype(book_type),
	author varchar(10),
	publisher varchar(20),
	publish_time date,
	status integer references bs_meta_book(status)
);
create table bs_bookstatus
(
	book_id integer primary key,
	class_id integer references bs_book(class_id),
	position varchar(30),
	status integer references bs_meta_bookstatus(status)
);
create table bs_borrow
(
	username varchar(10) references bs_user(username),
	book_id integer references bs_bookstatus(book_id),
	borrow_time date not null,
	return_deadline date not null,
	return_time date
);
create table bs_session
(
	id char(32) primary key,
	last_time integer,
	username varchar(10) references bs_user(username)
);

DROP PROCEDURE IF EXISTS newbook;
DELIMITER //
create procedure newbook(
    IN isbn varchar(13),
    IN name varchar(20),
    IN book_type varchar(10),
    IN author varchar(10),
    IN publisher varchar(20),
    IN publish_time date,
    IN status integer
)
begin
    declare maxid integer;
    declare idcnt integer;
    declare result_code integer default 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET result_code = 1;
    
    start transaction;
    select count(class_id) into idcnt from bs_book ;
    select max(class_id) into maxid from bs_book;
    
    if (idcnt=0) then 
        set maxid=1;
    else 
        set maxid=maxid+1;
    end if;
    insert into bs_book values (maxid,isbn,name,book_type,author,publisher,publish_time,status);
    if result_code=1 then 
        rollback;
    else
        commit;
    end if;

    select result_code;
end
//
DELIMITER ;

DELIMITER //
create trigger clean_up after insert on bs_session
for each row
begin
delete from bs_session where last_time+600<unix_timestamp(now());
end
//
DELIMITER ;

create view bs_userreader as select * from bs_user natural join bs_reader;
create view bs_bookdup as (select name,book_id from bs_book A inner join bs_bookstatus B on A.class_id=B.class_id)
create view bs_userborrow as select username,book_id from bs_user natural join bs_borrow where return_time is null