CREATE TABLE Offices (
       name CHAR(30),
       address VARCHAR(255) NOT NULL,
	   PRIMARY KEY (name)
);

CREATE TABLE Customers (
       ssno CHAR(11),
	   name CHAR(30) NOT NULL,
	   card_id CHAR(16) NOT NULL,
	   PIN CHAR(4) NOT NULL CHECK (PIN LIKE '[0-9][0-9][0-9][0-9]'),
	   address VARCHAR(255) NOT NULL,
	   phone CHAR(16),
       email CHAR(50),
	   PRIMARY KEY (ssno)
);

CREATE TABLE Publishers (	
       name CHAR(30),
       address VARCHAR(255) NOT NULL,
	   PRIMARY KEY (name)
);

CREATE TABLE Creators (
		id CHAR(16),
		name CHAR(30) NOT NULL,
		PRIMARY KEY (id)
);

CREATE TABLE Items (
		id CHAR(16),
		iteminfo_id CHAR(16) NOT NULL,
		access_state CHAR(9) NOT NULL CHECK (access_state IN ('reserved', 'borrowed', 'free', 'noreserve', 'noborrow')),
		reserve_time INTEGER NOT NULL,
		borrow_time INTEGER NOT NULL,
		home_office_name CHAR(30) NOT NULL,
		current_office_name CHAR(30) NOT NULL,
		PRIMARY KEY (id),
		FOREIGN KEY (iteminfo_id) REFERENCES ItemInfos(id)
			ON UPDATE CASCADE,
		FOREIGN KEY (home_office_name) REFERENCES Offices(name)
			ON UPDATE CASCADE,
		FOREIGN KEY (current_office_name) REFERENCES Offices(name)
			ON UPDATE CASCADE,
);

CREATE TABLE ItemInfos (
		id CHAR(16),
		title CHAR(50) NOT NULL,
		description VARCHAR(255),
		PRIMARY KEY (id)
);

CREATE TABLE CDs (
		id CHAR(16),
		play_length INTEGER CHECK ((play_length IS NULL) OR (play_length > 0)),
		PRIMARY KEY (id),
		FOREIGN KEY (id) REFERENCES ItemInfos(id)
			ON UPDATE CASCADE
);

CREATE TABLE DVDs (
		id CHAR(16),
		play_length INTEGER CHECK ((play_length IS NULL) OR (play_length > 0)),
		PRIMARY KEY (id),
		FOREIGN KEY (id) REFERENCES ItemInfos(id)
			ON UPDATE CASCADE
);

CREATE TABLE Books (
		id CHAR(16),
		ISBN CHAR(13) NOT NULL UNIQUE,
		pages INTEGER CHECK ((pages IS NULL) OR (pages > 0)),
		PRIMARY KEY (id),
		FOREIGN KEY (id) REFERENCES ItemInfos(id)
			ON UPDATE CASCADE
);

CREATE TABLE Magazines (
		id CHAR(16),
		magazine_number INTEGER,
		PRIMARY KEY (id),
		FOREIGN KEY (id) REFERENCES ItemInfos(id)
			ON UPDATE CASCADE
);

CREATE TABLE Shipments (
		id CHAR(16),
		shipment_date TEXT NOT NULL,
		ship_to_name CHAR(30) NOT NULL,
		ship_from_name CHAR(30) NOT NULL,
		PRIMARY KEY (id),
		FOREIGN KEY (ship_to_name) REFERENCES Offices(name)
			ON UPDATE CASCADE,
		FOREIGN KEY (ship_from_name) REFERENCES Offices(name)
			ON UPDATE CASCADE,
);

CREATE TABLE Payments (
		id CHAR(16),
		price INTEGER NOT NULL,
		pending INTEGER NOT NULL,
		payment_type CHAR(11) NOT NULL CHECK ((payment_type IS NULL) OR (payment_type IN ('fine', 'reservation'))),
		expire_date TEXT NOT NULL,
		pay_date TEXT,
		customer_ssno CHAR(11) NOT NULL,
		PRIMARY KEY (id),
		FOREIGN KEY (customer_ssno) REFERENCES Customers(ssno)
);

CREATE TABLE ReservedBy (
		item_id CHAR(16),
		customer_ssno CHAR(11) NOT NULL,
		reserve_date TEXT NOT NULL,
		office_name CHAR(30) NOT NULL,
		PRIMARY KEY (item_id),
		FOREIGN KEY (customer_ssno) REFERENCES Customers(ssno),
		FOREIGN KEY (office_name) REFERENCES Offices(name)
			ON UPDATE CASCADE
);

CREATE TABLE BorrowedBy (
		item_id CHAR(16),
		customer_ssno CHAR(11) NOT NULL,
		borrow_date TEXT NOT NULL,
		return_date TEXT NOT NULL,
		PRIMARY KEY (item_id),
		FOREIGN KEY (customer_ssno) REFERENCES Customers(ssno)
);

CREATE TABLE Created (
		creator_id CHAR(16),
		iteminfo_id CHAR(16),
		PRIMARY KEY (creator_id, iteminfo_id),
		FOREIGN KEY (creator_id) REFERENCES Creators(id)
			ON UPDATE CASCADE,
		FOREIGN KEY (iteminfo_id) REFERENCES ItemInfos(id)
			ON UPDATE CASCADE
);

CREATE TABLE ShippedIn (
		item_id CHAR(16),
		shipment_id CHAR(16),
		PRIMARY KEY (item_id, shipment_id),
		FOREIGN KEY (item_id) REFERENCES Items(id)
			ON UPDATE CASCADE,
		FOREIGN KEY (shipment_id) REFERENCES Shipments(id)
			ON UPDATE CASCADE
);

CREATE TABLE Published (
	publisher_name CHAR(30),
	iteminfo_id CHAR(16),
	publish_date TEXT NOT NULL,
	PRIMARY KEY (publisher_name, iteminfo_id),
	FOREIGN KEY (publisher_name) REFERENCES Publishers(name)
		ON UPDATE CASCADE,
	FOREIGN KEY (iteminfo_id) REFERENCES ItemInfos(id)
		ON UPDATE CASCADE
);