CREATE TABLE employees(
	id INTEGER GENERATED ALWAYS AS IDENITY (START WITH 1000, INCREMENT BY 1),
	role_id INTEGER NOT NULL,
	store_id INTEGER NOT NULL,
	first_name VARCHAR(30) NOT NULL,
	last_name VARCHAR(30) NOT NULL,
	phone_number VARCHAR(30) NOT NULL,
	email VARCHAR(30) NOT NULL,
	address_id INTEGER NOT NULL,
	CONSTRAINT fk_role_id
	FOREIGN KEY (role_id)
	REFERENCES employee_types(id),
	CONSTRAINT fk_store_id
	FOREIGN KEY (store_id)
	REFERENCES stores(id),
	CONSTRAINT fk_address_id
	FOREIGN KEY(address_id)
	REFERENCES addresses(id)
	PRIMARY KEY employee_id
);

CREATE TABLE customers(
	id INTEGER GENERATED ALWAYS AS IDENITY (START WITH 10000, INCREMENT BY 1),
	first_name VARCHAR(30) NOT NULL,
	last_name VARCHAR(30) NOT NULL,
	address_id INTEGER NOT NULL,
	CONSTRAINT fk_address_id
	FOREIGN KEY(address_id)
	REFERENCES addresses(id),
	PRIMARY KEY cust_id
);

CREATE TABLE inventory(
	vin VARCHAR(30),
	store_id INTEGER NOT NULL,
	car_year INTEGER NOT NULL,
	make VARCHAR(30) NOT NULL,
	model VARCHAR(30) NOT NULL,
	color VARCHAR(30) NOT NULL,
	acquired_date DATE NOT NULL,
	acquired_mileage INTEGER NOT NULL,
	CONSTRAINT fk_store_id2
	FOREIGN KEY(store_id)
	REFERENCES stores(id),
	PRIMARY KEY vin
);

CREATE TABLE purchases(
	purch_id INTEGER,
	store_id INTEGER NOT NULL,
	vin VARCHAR(30) NOT NULL,
	cust_id INTEGER NOT NULL,
	purchase_price DECIMAL(10,2) NOT NULL,
	tax DECIMAL(10,2),
	title DECIMAL(10,2),
	license DECIMAL(10,2),
	sold_mileage INTEGER NOT NULL,
	purchase_date DATE NOT NULL,
	CONSTRAINT fk_store
	FOREIGN KEY(store_id)
	REFERENCES stores(id),
	CONSTRAINT fk_cust_id
	FOREIGN KEY(cust_id)
	REFERENCES customers(id),
	CONSTRAINT fk_vin
	FOREIGN KEY(vin)
	REFERENCES inventory(vin),
	PRIMARY KEY purch_id
);