DROP TABLE foo_metadata IF EXISTS;
DROP TABLE foo_reference IF EXISTS;
DROP TABLE products IF EXISTS;

CREATE TABLE foo_metadata
(
  product_id int NOT NULL,
  element_id varchar(1000) NOT NULL,
  metadata_value varchar(2500) NOT NULL
);

CREATE TABLE foo_reference
(
  product_id int NOT NULL,
  product_orig_reference varchar(2000) NOT NULL,
  product_datastore_reference varchar(2000), 
  product_reference_filesize int NOT NULL,
  product_reference_mimetype varchar(50)
);

CREATE TABLE products (
  product_id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1) PRIMARY KEY,
  product_structure varchar(20) NOT NULL, 
  product_type_id varchar(255) NOT NULL, 
  product_name varchar(255) NOT NULL, 
  product_transfer_status varchar(255) NOT NULL
);