
DROP TABLE IF EXISTS member;
DROP TABLE IF EXISTS book;
DROP TABLE IF EXISTS reading;
DROP TABLE IF EXISTS category;
DROP TABLE IF EXISTS category_book;


CREATE TABLE member
(
	id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	name TEXT NOT NULL,
	email_address TEXT NOT NULL
);


CREATE TABLE book
(
	id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	title TEXT NOT NULL,
	author TEXT NOT NULL,
	genre TEXT NOT NULL,
	CONSTRAINT ck_genre CHECK (genre IN ('Unspecified', 'Fiction', 'NonFiction', 'HistoricalFiction')) 
);


CREATE TABLE reading
(
	member_id INTEGER NOT NULL FOREIGN KEY REFERENCES member(id),
	book_id INTEGER NOT NULL FOREIGN KEY REFERENCES book(id)
);


CREATE TABLE category
(
	id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	name TEXT NOT NULL,
	parent_id INTEGER FOREIGN KEY REFERENCES category(id)
);


CREATE TABLE category_book
(
	category_id INTEGER NOT NULL FOREIGN KEY REFERENCES category(id),
	book_id INTEGER NOT NULL FOREIGN KEY REFERENCES book(id)
);

