CREATE TABLE IF NOT EXISTS units (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(255),
  short VARCHAR(255),
  UNIQUE(name),
  UNIQUE(short)
);

CREATE TABLE IF NOT EXISTS food (
  id BIGSERIAL PRIMARY KEY,
  version BIGINT,
  name VARCHAR(255),
  unit_id BIGINT REFERENCES units(id),
  UNIQUE(name, version)
);

CREATE TABLE IF NOT EXISTS nutritional_kind (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(255),
  unit_id BIGINT REFERENCES units(id),
  UNIQUE (name)
);

CREATE TABLE IF NOT EXISTS food_base (
  food_id BIGINT REFERENCES food(id),
  kind_id BIGINT REFERENCES nutritional_kind(id),
  value FLOAT,
  PRIMARY KEY (food_id, kind_id)
);

INSERT INTO units(name, short) VALUES('Gram', 'g');
INSERT INTO units(name, short) VALUES('Milliliter', 'ml');

CREATE TABLE IF NOT EXISTS tracking (
  id BIGSERIAL PRIMARY KEY,
  food_id BIGINT REFERENCES food(id),
  quantity FLOAT,
  ts BIGINT
);