drop table if exists borrowed_book;
drop table if exists book;
drop table if exists user_account;

create table user_account (
  uid serial primary key,
  username text unique not null,
  name text,
  password text
);

insert into user_account (username, name) values ('kamal', 'Kamal Wickramanayake');
insert into user_account (username, name) values ('tharindu', 'Tharindu Fernando');

create table book (
  bookid serial primary key,
  name text,
  author text
);

insert into book (name, author) values ('Tasty Cooking', 'Bhagya Ratnayake');
insert into book (name, author) values ('Smiling Kitty', 'Seema Farwin');


create table borrowed_book (
  uid integer references user_account(uid) on delete restrict,
  bookid integer references book(bookid) on delete restrict,
  borrowed_time timestamp
);

-- Warning! We assume uid and bookid to be consecutive integers starting from 1
-- uid 1 borrowed bookid 1
insert into borrowed_book (uid, bookid) values (1, 1);
-- uid 1 borrowed bookid 2
insert into borrowed_book (uid, bookid) values (1, 2);
-- uid 2 borrowed bookid 2
insert into borrowed_book (uid, bookid) values (2, 2);

-- List all borrowed book data
select * from borrowed_book ;

-- Print the book names of bookes borrowed by user with the username 'kamal'
select b.name from book as b, user_account as u, borrowed_book as bw where u.username = 'kamal' and u.uid = bw.uid and bw.bookid = b.bookid;