PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS inventory_locations (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  position INTEGER NOT NULL,
  deleted_at TEXT
);

CREATE TABLE IF NOT EXISTS inventory_counts (
  id TEXT PRIMARY KEY,
  title TEXT NOT NULL,
  counted_at TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'open',
  notes TEXT,
  created_at TEXT NOT NULL,
  deleted_at TEXT
);

CREATE TABLE IF NOT EXISTS inventory_count_items (
  count_id TEXT NOT NULL REFERENCES inventory_counts(id) ON DELETE CASCADE,
  location_id TEXT REFERENCES inventory_locations(id),
  ingredient_id TEXT NOT NULL REFERENCES ingredients(id),
  quantity REAL NOT NULL,
  unit_id TEXT NOT NULL REFERENCES units(id),
  unit_cost REAL,
  extended_cost REAL,
  PRIMARY KEY (count_id, location_id, ingredient_id)
);

-- Seed baseline standard locations if table is empty
INSERT OR IGNORE INTO inventory_locations (id, name, position, deleted_at) VALUES
  ('loc_walk_in', 'Walk-in Cooler', 1, NULL),
  ('loc_dry_storage', 'Dry Storage', 2, NULL),
  ('loc_freezer', 'Freezer', 3, NULL),
  ('loc_bar', 'Bar & Service', 4, NULL),
  ('loc_line', 'Prep Line', 5, NULL);