ballistic-builder/db-backup/ddl.sql

-- DROP SCHEMA public;

CREATE SCHEMA public AUTHORIZATION pg_database_owner;

COMMENT ON SCHEMA public IS 'standard public schema';

-- DROP SEQUENCE public.accountsid_seq;

CREATE SEQUENCE public.accountsid_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.brands_id_seq;

CREATE SEQUENCE public.brands_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.build_components_id_seq;

CREATE SEQUENCE public.build_components_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.build_id_seq;

CREATE SEQUENCE public.build_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.categories_id_seq;

CREATE SEQUENCE public.categories_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.component_type_id_seq;

CREATE SEQUENCE public.component_type_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.lipseycatalog_id_seq;

CREATE SEQUENCE public.lipseycatalog_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.manufacturer_id_seq;

CREATE SEQUENCE public.manufacturer_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.productfeeds_id_seq;

CREATE SEQUENCE public.productfeeds_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.products_id_seq;

CREATE SEQUENCE public.products_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.resellers_id_seq;

CREATE SEQUENCE public.resellers_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;
-- DROP SEQUENCE public.states_id_seq;

CREATE SEQUENCE public.states_id_seq
	INCREMENT BY 1
	MINVALUE 1
	MAXVALUE 2147483647
	START 1
	CACHE 1
	NO CYCLE;-- public.bal_accounts definition

-- Drop table

-- DROP TABLE public.bal_accounts;

CREATE TABLE public.bal_accounts (
	id int4 GENERATED ALWAYS AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE) NOT NULL,
	first_name varchar(40) NULL,
	last_name varchar(40) NULL,
	updated_at timestamp DEFAULT now() NOT NULL,
	created_at timestamp DEFAULT now() NOT NULL,
	deleted_at timestamp NULL,
	email varchar(100) NULL,
	username varchar(50) NOT NULL,
	password_hash varchar(255) NOT NULL,
	"uuid" uuid DEFAULT gen_random_uuid() NULL,
	CONSTRAINT bal_accounts_password_hash_unique UNIQUE (password_hash),
	CONSTRAINT bal_accounts_pkey PRIMARY KEY (id),
	CONSTRAINT bal_accounts_username_unique UNIQUE (username),
	CONSTRAINT bal_accounts_uuid_unique UNIQUE (uuid)
);


-- public.bal_resellers definition

-- Drop table

-- DROP TABLE public.bal_resellers;

CREATE TABLE public.bal_resellers (
	id int4 GENERATED ALWAYS AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE) NOT NULL,
	"name" varchar(100) NOT NULL,
	website_url varchar(255) NULL,
	contact_email varchar(100) NULL,
	updated_at timestamp DEFAULT now() NOT NULL,
	created_at timestamp DEFAULT now() NOT NULL,
	deleted_at timestamp NULL,
	"uuid" uuid DEFAULT gen_random_uuid() NULL,
	CONSTRAINT bal_resellers_pkey PRIMARY KEY (id),
	CONSTRAINT bal_resellers_uuid_unique UNIQUE (uuid)
);


-- public.brands definition

-- Drop table

-- DROP TABLE public.brands;

CREATE TABLE public.brands (
	id int4 GENERATED ALWAYS AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE) NOT NULL,
	"name" varchar(100) NOT NULL,
	updated_at timestamp DEFAULT now() NOT NULL,
	created_at timestamp DEFAULT now() NOT NULL,
	deleted_at timestamp NULL,
	"uuid" uuid DEFAULT gen_random_uuid() NULL,
	CONSTRAINT brands_pkey PRIMARY KEY (id),
	CONSTRAINT brands_uuid_unique UNIQUE (uuid)
);


-- public.builds definition

-- Drop table

-- DROP TABLE public.builds;

CREATE TABLE public.builds (
	id int4 GENERATED ALWAYS AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE) NOT NULL,
	account_id int4 NOT NULL,
	"name" varchar(255) NOT NULL,
	description text NULL,
	updated_at timestamp DEFAULT now() NOT NULL,
	created_at timestamp DEFAULT now() NOT NULL,
	deleted_at timestamp NULL,
	"uuid" uuid DEFAULT gen_random_uuid() NULL,
	CONSTRAINT builds_pkey PRIMARY KEY (id),
	CONSTRAINT builds_uuid_unique UNIQUE (uuid)
);
CREATE INDEX builds_id_idx ON public.builds USING btree (id);
CREATE INDEX builds_uuid_idx ON public.builds USING btree (uuid);
COMMENT ON TABLE public.builds IS 'Accounts own the builds, one accountto many builds';


-- public.builds_components definition

-- Drop table

-- DROP TABLE public.builds_components;

CREATE TABLE public.builds_components (
	id int4 GENERATED ALWAYS AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE) NOT NULL,
	build_id int4 NOT NULL,
	product_id int4 NOT NULL,
	updated_at timestamp DEFAULT now() NOT NULL,
	created_at timestamp DEFAULT now() NOT NULL,
	deleted_at timestamp NULL,
	"uuid" uuid DEFAULT gen_random_uuid() NULL,
	CONSTRAINT builds_components_pkey PRIMARY KEY (id),
	CONSTRAINT builds_components_uuid_unique UNIQUE (uuid)
);


-- public.categories definition

-- Drop table

-- DROP TABLE public.categories;

CREATE TABLE public.categories (
	id int4 GENERATED ALWAYS AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE) NOT NULL,
	"name" varchar(100) NOT NULL,
	parent_category_id int4 NULL,
	updated_at timestamp DEFAULT now() NOT NULL,
	created_at timestamp DEFAULT now() NOT NULL,
	deleted_at timestamp NULL,
	"uuid" uuid DEFAULT gen_random_uuid() NULL,
	CONSTRAINT categories_pkey PRIMARY KEY (id)
);


-- public.compartment definition

-- Drop table

-- DROP TABLE public.compartment;

CREATE TABLE public.compartment (
	id uuid DEFAULT gen_random_uuid() NOT NULL,
	"name" varchar(100) NOT NULL, -- name of compartment
	description varchar(300) NULL,
	updated_at timestamp DEFAULT now() NOT NULL,
	created_at timestamp DEFAULT now() NOT NULL,
	deleted_at timestamp NULL,
	CONSTRAINT compartment_pk PRIMARY KEY (id)
);
COMMENT ON TABLE public.compartment IS 'compartment will be the ar  parent category';

-- Column comments

COMMENT ON COLUMN public.compartment."name" IS 'name of compartment';


-- public.component_type definition

-- Drop table

-- DROP TABLE public.component_type;

CREATE TABLE public.component_type (
	id int4 GENERATED ALWAYS AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE) NOT NULL,
	"name" varchar(100) NOT NULL,
	updated_at timestamp DEFAULT now() NOT NULL,
	created_at timestamp DEFAULT now() NOT NULL,
	deleted_at timestamp NULL,
	"uuid" uuid DEFAULT gen_random_uuid() NULL,
	CONSTRAINT component_type_pkey PRIMARY KEY (id),
	CONSTRAINT component_type_uuid_unique UNIQUE (uuid)
);


-- public.lipseycatalog definition

-- Drop table

-- DROP TABLE public.lipseycatalog;

CREATE TABLE public.lipseycatalog (
	id int4 GENERATED ALWAYS AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE) NOT NULL,
	itemno varchar(20) NOT NULL,
	description1 text NULL,
	description2 text NULL,
	upc varchar(20) NULL,
	manufacturermodelno varchar(30) NULL,
	msrp float8 NULL,
	model text NULL,
	calibergauge text NULL,
	manufacturer text NULL,
	"type" text NULL,
	"action" text NULL,
	barrellength text NULL,
	capacity text NULL,
	finish text NULL,
	overalllength text NULL,
	receiver text NULL,
	safety text NULL,
	sights text NULL,
	stockframegrips text NULL,
	magazine text NULL,
	weight text NULL,
	imagename text NULL,
	chamber text NULL,
	drilledandtapped text NULL,
	rateoftwist text NULL,
	itemtype text NULL,
	additionalfeature1 text NULL,
	additionalfeature2 text NULL,
	additionalfeature3 text NULL,
	shippingweight text NULL,
	boundbookmanufacturer text NULL,
	boundbookmodel text NULL,
	boundbooktype text NULL,
	nfathreadpattern text NULL,
	nfaattachmentmethod text NULL,
	nfabaffletype text NULL,
	silencercanbedisassembled text NULL,
	silencerconstructionmaterial text NULL,
	nfadbreduction text NULL,
	silenceroutsidediameter text NULL,
	"nfaform3Caliber" text NULL,
	opticmagnification text NULL,
	maintubesize text NULL,
	adjustableobjective text NULL,
	objectivesize text NULL,
	opticadjustments text NULL,
	illuminatedreticle text NULL,
	reticle text NULL,
	"exclusive" text NULL,
	quantity varchar(10) DEFAULT NULL::character varying NULL,
	allocated text NULL,
	onsale text NULL,
	price float8 NULL,
	currentprice float8 NULL,
	retailmap float8 NULL,
	fflrequired text NULL,
	sotrequired text NULL,
	exclusivetype text NULL,
	scopecoverincluded text NULL,
	special text NULL,
	sightstype text NULL,
	"case" text NULL,
	choke text NULL,
	dbreduction text NULL,
	"family" text NULL,
	finishtype text NULL,
	frame text NULL,
	griptype varchar(30) NULL,
	handgunslidematerial text NULL,
	countryoforigin varchar(4) NULL,
	itemlength text NULL,
	itemwidth text NULL,
	itemheight text NULL,
	packagelength float8 NULL,
	packagewidth float8 NULL,
	packageheight float8 NULL,
	itemgroup varchar(40) NULL,
	updated_at timestamp DEFAULT now() NOT NULL,
	created_at timestamp DEFAULT now() NOT NULL,
	deleted_at timestamp NULL,
	CONSTRAINT lipseycatalog_pkey PRIMARY KEY (id)
);


-- public.manufacturer definition

-- Drop table

-- DROP TABLE public.manufacturer;

CREATE TABLE public.manufacturer (
	id int4 GENERATED ALWAYS AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE) NOT NULL,
	"name" varchar(100) NOT NULL,
	updated_at timestamp DEFAULT now() NOT NULL,
	created_at timestamp DEFAULT now() NOT NULL,
	deleted_at timestamp NULL,
	"uuid" uuid DEFAULT gen_random_uuid() NULL,
	CONSTRAINT manufacturer_pkey PRIMARY KEY (id),
	CONSTRAINT manufacturer_uuid_unique UNIQUE (uuid)
);


-- public.product_feeds definition

-- Drop table

-- DROP TABLE public.product_feeds;

CREATE TABLE public.product_feeds (
	id int4 GENERATED ALWAYS AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE) NOT NULL,
	reseller_id int4 NOT NULL,
	feed_url varchar(255) NOT NULL,
	last_update timestamp NULL,
	updated_at timestamp DEFAULT now() NOT NULL,
	created_at timestamp DEFAULT now() NOT NULL,
	deleted_at timestamp NULL,
	"uuid" uuid DEFAULT gen_random_uuid() NULL,
	CONSTRAINT product_feeds_pkey PRIMARY KEY (id),
	CONSTRAINT product_feeds_uuid_unique UNIQUE (uuid)
);


-- public.products definition

-- Drop table

-- DROP TABLE public.products;

CREATE TABLE public.products (
	id int4 GENERATED ALWAYS AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE) NOT NULL,
	"name" varchar(255) NOT NULL,
	description text NOT NULL,
	price numeric NOT NULL,
	reseller_id int4 NOT NULL,
	category_id int4 NOT NULL,
	stock_qty int4 DEFAULT 0 NULL,
	updated_at timestamp DEFAULT now() NOT NULL,
	created_at timestamp DEFAULT now() NOT NULL,
	deleted_at timestamp NULL,
	CONSTRAINT products_pkey PRIMARY KEY (id)
);


-- public.states definition

-- Drop table

-- DROP TABLE public.states;

CREATE TABLE public.states (
	id int4 GENERATED BY DEFAULT AS IDENTITY( INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 START 1 CACHE 1 NO CYCLE) NOT NULL,
	state varchar(50) NULL,
	abbreviation varchar(50) NULL,
	CONSTRAINT states_pk PRIMARY KEY (id)
);