-- enable foreign key support in sqlite
PRAGMA foreign_keys = ON;

-- part manufacturer
CREATE TABLE IF NOT EXISTS manufacturer (
	id INTEGER PRIMARY KEY AUTOINCREMENT, -- index
	name TEXT NOT NULL UNIQUE, -- manufacturer (expanded version, without legal form)
	nick TEXT, -- manufacturer nickname
	partof INTEGER, -- if the manufacturer has been acquired or is part of another
	homepage TEXT, -- URL to home page
	search TEXT, -- URL to search page
	FOREIGN KEY (partof) REFERENCES manufacturer (id)
);

-- part distributor
CREATE TABLE IF NOT EXISTS distributor (
	id INTEGER PRIMARY KEY AUTOINCREMENT, -- index
	name TEXT NOT NULL UNIQUE,
	homepage TEXT, -- URL to home page
	product_page TEXT -- URL to product page (%s is replace by sku)
);

-- the part itself
CREATE TABLE IF NOT EXISTS part (
	id INTEGER PRIMARY KEY AUTOINCREMENT, -- index
	name TEXT NOT NULL UNIQUE,
	description TEXT, -- a short (searchable) description
	details TEXT, -- even more part details than just in the description
	manufacturer INTEGER,
	mpn TEXT,
	family INTEGER, -- if this part is part of a part family
	datasheet TEXT, -- URL to datasheet
	package TEXT,
	page TEXT, -- URL to product page
	FOREIGN KEY (manufacturer) REFERENCES manufacturer (id),
	FOREIGN KEY (family) REFERENCES part (id)
);

-- a project (as part) can be an assembly of other parts
CREATE TABLE IF NOT EXISTS assembly (
	assembled INTEGER NOT NULL, -- the assembly project
	component INTEGER NOT NULL, -- the component part
	quantity INTEGER NOT NULL, -- quantity of component
	FOREIGN KEY (assembled) REFERENCES part (id),
	FOREIGN KEY (component) REFERENCES part (id),
	UNIQUE (assembled, component)
);

-- a part at a distributor
CREATE TABLE IF NOT EXISTS distribution (
	id INTEGER PRIMARY KEY AUTOINCREMENT, -- index
	part INTEGER NOT NULL, -- the part
	distributor INTEGER NOT NULL, -- the part distributor
	sku TEXT NOT NULL, -- distributor part number
	FOREIGN KEY (part) REFERENCES part (id),
	FOREIGN KEY (distributor) REFERENCES distributor (id)
);

-- part property
CREATE TABLE IF NOT EXISTS property (
	id INTEGER PRIMARY KEY AUTOINCREMENT, -- index
	name TEXT NOT NULL -- property name (min V, ...)
);

-- property value
CREATE TABLE IF NOT EXISTS properties (
	id INTEGER PRIMARY KEY AUTOINCREMENT, -- index
	part INTEGER NOT NULL, -- the part
	property INTEGER NOT NULL,
	value TEXT,
	FOREIGN KEY (part) REFERENCES part (id),
	FOREIGN KEY (property) REFERENCES property (id)
);

-- part location
CREATE TABLE IF NOT EXISTS location (
	id INTEGER PRIMARY KEY AUTOINCREMENT, -- index
	name TEXT NOT NULL UNIQUE,
	container TEXT -- container type
);

-- local stock
CREATE TABLE IF NOT EXISTS inventory (
	id INTEGER PRIMARY KEY AUTOINCREMENT, -- index
	part INTEGER NOT NULL,
	location INTEGER,
	quantity INTEGER NOT NULL,
	FOREIGN KEY (part) REFERENCES part (id),
	FOREIGN KEY (location) REFERENCES location (id),
	UNIQUE (part, location)
);