CREATE TYPE menu_item_category AS ENUM (
  'dessert',
  'main_course',
  'beverages',
  'appetizer',
  'snack'
);

CREATE TABLE menu_items (
  "id" serial primary key not null,
  "location_id" integer references "locations"("id") not null,
  "name" varchar not null,
  "price" integer,
  "category" menu_item_category,
  "description" text,
  "is_available" boolean default(true),
  "is_deleted" boolean default(false),
  "submitted_by" integer references "users"("id") not null,
  "created_at" timestamp default(now()),
  "updated_at" timestamp default(now())
);

CREATE TABLE menu_item_price_history (
  "id" serial primary key not null,
  "menu_item_id" integer references "menu_items"("id") not null,
  "price" integer not null,
  "recorded_at" timestamp default(now())
);

CREATE OR REPLACE FUNCTION log_menu_item_price_change()
RETURNS TRIGGER AS $$
BEGIN
  IF OLD.price IS DISTINCT FROM NEW.price THEN
    INSERT INTO menu_item_price_history (menu_item_id, price, recorded_at)
    VALUES (OLD.id, OLD.price, now());
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER menu_item_price_change_trigger
BEFORE UPDATE ON menu_items
FOR EACH ROW EXECUTE FUNCTION log_menu_item_price_change();

-- For menu item images, use the existing polymorphic `images` table:
-- image_type = 'menu_items', image_of = menu_item_id

CREATE TABLE menu_item_reviews (
  "id" serial primary key not null,
  "menu_item_id" integer references "menu_items"("id") not null,
  "submitted_by" integer references "users"("id") not null,
  "score" smallint not null,
  "comments" text,
  "is_hidden" boolean default(false),
  "created_at" timestamp default(now()),
  "updated_at" timestamp default(now()),
  UNIQUE("menu_item_id", "submitted_by")
);