CREATE TABLE units (
  name varchar(20) NOT NULL,
  base_unit varchar(20),
  factor bigint,

  PRIMARY KEY (name),
  FOREIGN KEY (base_unit) REFERENCES units (name)
);

INSERT INTO units (name, base_unit, factor) VALUES ('mg', NULL, NULL);
INSERT INTO units (name, base_unit, factor) VALUES ('g', 'mg', 1000);
INSERT INTO units (name, base_unit, factor) VALUES ('kg', 'g', 1000);
INSERT INTO units (name, base_unit, factor) VALUES ('t', 'kg', 1000);
INSERT INTO units (name, base_unit, factor) VALUES ('ml', NULL, NULL);
INSERT INTO units (name, base_unit, factor) VALUES ('L', 'ml', 1000);
INSERT INTO units (name, base_unit, factor) VALUES ('Stck', NULL, NULL);
ALTER TABLE units ADD COLUMN active boolean;
UPDATE units SET active = 't';
ALTER TABLE units ALTER COLUMN active SET DEFAULT 't';
ALTER TABLE units ALTER COLUMN active SET NOT NULL;

ALTER TABLE units ADD COLUMN tmp numeric(20, 5);
UPDATE units SET tmp = factor;
ALTER TABLE units DROP COLUMN factor;
ALTER TABLE units RENAME tmp TO factor;

ALTER TABLE units ADD COLUMN type varchar(20);
UPDATE units SET type = 'dimension';
ALTER TABLE units ALTER COLUMN type SET NOT NULL;

-- Einheitennamen duerfen 20 Zeichen lang sein.

ALTER TABLE parts ADD COLUMN tmp varchar(20);
UPDATE parts SET tmp = unit;
ALTER TABLE parts DROP COLUMN unit;
ALTER TABLE parts RENAME tmp TO unit;

ALTER TABLE invoice ADD COLUMN tmp varchar(20);
UPDATE invoice SET tmp = unit;
ALTER TABLE invoice DROP COLUMN unit;
ALTER TABLE invoice RENAME tmp TO unit;

ALTER TABLE orderitems ADD COLUMN tmp varchar(20);
UPDATE orderitems SET tmp = unit;
ALTER TABLE orderitems DROP COLUMN unit;
ALTER TABLE orderitems RENAME tmp TO unit;

-- Spalte "active" wird nicht mehr benoetigt, weil Einheiten nicht mehr deaktiviert
-- werden koennen.

ALTER TABLE units DROP COLUMN active;