CREATE TABLE CUSTOMER
(
	ID                           BIGINT GENERATED BY DEFAULT AS IDENTITY,
	FIRST_NAME                   VARCHAR NOT NULL,
	LAST_NAME                    VARCHAR NOT NULL,
	EMAIL                        VARCHAR NOT NULL UNIQUE,
	ADDRESS_STREET               VARCHAR NOT NULL,
	ADDRESS_CITY                 VARCHAR NOT NULL,
	ADDRESS_STATE_PROVINCE       VARCHAR,
	ADDRESS_POSTAL_CODE          VARCHAR NOT NULL,
	ADDRESS_COUNTRY              VARCHAR NOT NULL,
	CREDIT_CARD_TYPE             VARCHAR NOT NULL,
	CREDIT_CARD_NUMBER           VARCHAR NOT NULL,
	CREDIT_CARD_EXPIRATION_MONTH INTEGER NOT NULL,
	CREDIT_CARD_EXPIRATION_YEAR  INTEGER NOT NULL,
	PRIMARY KEY (ID)
);

CREATE TABLE BOOK_ORDER
(
	ID                           BIGINT GENERATED BY DEFAULT AS IDENTITY,
	ORDER_DATE                   TIMESTAMP     NOT NULL,
	AMOUNT                       NUMERIC(7, 2) NOT NULL,
	STATUS                       VARCHAR       NOT NULL,
	CUSTOMER_ID                  BIGINT        NOT NULL,
	ADDRESS_CITY                 VARCHAR       NOT NULL,
	ADDRESS_COUNTRY              VARCHAR       NOT NULL,
	ADDRESS_POSTAL_CODE          VARCHAR       NOT NULL,
	ADDRESS_STATE_PROVINCE       VARCHAR,
	ADDRESS_STREET               VARCHAR       NOT NULL,
	CREDIT_CARD_TYPE             VARCHAR       NOT NULL,
	CREDIT_CARD_NUMBER           VARCHAR       NOT NULL,
	CREDIT_CARD_EXPIRATION_MONTH INTEGER       NOT NULL,
	CREDIT_CARD_EXPIRATION_YEAR  INTEGER       NOT NULL,
	PRIMARY KEY (ID),
	CONSTRAINT FK_CUSTOMER_ID FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (ID)
);

CREATE TABLE ORDER_ITEM
(
	ID       BIGINT GENERATED BY DEFAULT AS IDENTITY,
	ISBN     VARCHAR(10) NOT NULL,
	QUANTITY INTEGER     NOT NULL,
	ORDER_ID BIGINT,
	PRIMARY KEY (ID),
	CONSTRAINT FK_ISBN FOREIGN KEY (ISBN) REFERENCES BOOK (ISBN),
	CONSTRAINT FK_ORDER_ID FOREIGN KEY (ORDER_ID) REFERENCES BOOK_ORDER (ID)
);