create table "CAT".CATALOGS
(
	ID BIGINT not null primary key,
	NAME VARCHAR(50),
	DESCRIPTION VARCHAR(260),
	LAST_UPDATE TIMESTAMP,
	DELETED SMALLINT
);

create table "CAT".CATEGORIES
(
	ID BIGINT not null primary key,
	NAME VARCHAR(50),
	DESCRIPTION VARCHAR(260),
	CATALOG_ID BIGINT,
	LAST_UPDATE TIMESTAMP,
	DELETED SMALLINT,
	PARENT_ID BIGINT,
	constraint fk_categories_catalog_id FOREIGN KEY (catalog_id) REFERENCES catalogs(id),
	#make categories recursive
	constraint fk_categories_parent_id FOREIGN KEY (parent_id) REFERENCES categories(id)

);

create table "CAT".PRODUCTS
(
	ID BIGINT not null primary key,
	NAME VARCHAR(50),
	DESCRIPTION VARCHAR(260),
	PRICE DECIMAL(9),
	CATEGORY_ID BIGINT,
	LAST_UPDATE TIMESTAMP,
	DELETED SMALLINT,
	constraint fk_products_category_id FOREIGN KEY (category_id) REFERENCES categories(id)
);

CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY(
    'derby.database.sqlAuthorization',
    'true');

GRANT ALL PRIVILEGES ON TABLE CAT.catalogs to cat;
GRANT ALL PRIVILEGES ON TABLE CAT.categories to cat;
GRANT ALL PRIVILEGES ON TABLE CAT.products to cat;

INSERT INTO catalogs (name, description, deleted) VALUES ('boltshop.com', 'boltshop.com catalog' ,0);
INSERT INTO catalogs (name, description, deleted) VALUES ('candysshop', 'candyshop catalog' ,0);
INSERT INTO categories (name, description, catalog_id, deleted) VALUES ('nuts', 'boltshop.com nuts catalog', 1, 0);
INSERT INTO categories (name, description, catalog_id, deleted) VALUES ('bolts', 'boltshop.com nuts catalog', 1, 0);
INSERT INTO categories (name, description, catalog_id, deleted) VALUES ('candy', 'candystore catalog 1', 2, 0);
INSERT INTO products (name, description, price, category_id, deleted) VALUES ('M3', 'M3 nuts', 0.20, 1, 0);
INSERT INTO products (name, description, price, category_id, deleted) VALUES ('M5', 'M5 nuts', 0.20, 1, 0);
INSERT INTO products (name, description, price, category_id, deleted) VALUES ('M6', 'M6 nuts', 0.20, 1, 0);
INSERT INTO products (name, description, price, category_id, deleted) VALUES ('M8', 'M8 nuts', 0.20, 1, 0);
INSERT INTO products (name, description, price, category_id, deleted) VALUES ('M12', 'M12 nuts', 0.20, 1, 0);

INSERT INTO products (name, description, price, category_id, deleted) VALUES ('M3', 'M3 bolts', 0.20, 2, 0);
INSERT INTO products (name, description, price, category_id, deleted) VALUES ('M5', 'M5 bolts', 0.20, 2, 0);
INSERT INTO products (name, description, price, category_id, deleted) VALUES ('M6', 'M6 bolts', 0.20, 2, 0);
INSERT INTO products (name, description, price, category_id, deleted) VALUES ('M8', 'M8 bolts', 0.20, 2, 0);
INSERT INTO products (name, description, price, category_id, deleted) VALUES ('M12', 'M12 bolts', 0.20, 2, 0);

INSERT INTO products (name, description, price, category_id, deleted) VALUES ('frog', 'delicious frog form candy', 0.10, 3, 0);
INSERT INTO products (name, description, price, category_id, deleted) VALUES ('cola bottle', 'little cola bottle candy', 0.10, 3, 0);
INSERT INTO products (name, description, price, category_id, deleted) VALUES ('sour sweet', 'sour & sweet candy', 0.10, 3, 0);
INSERT INTO products (name, description, price, category_id, deleted) VALUES ('candy bar', 'taix candy bar', 0.10, 3, 0);
INSERT INTO products (name, description, price, category_id, deleted) VALUES ('bubblegum', 'nice taste bubble gum', 0.10, 3, 0);
INSERT INTO products (name, description, price, category_id, deleted) VALUES ('rice bar', 'caramel and chocolate crispy rice bar', 0.10, 3, 0);
INSERT INTO products (name, description, price, category_id, deleted) VALUES ('mint', 'green mint', 0.10, 3, 0);
INSERT INTO products (name, description, price, category_id, deleted) VALUES ('sugar free', 'sugar free candy bar', 0.10, 3, 0);