ALTER TABLE menu_categories ADD COLUMN IF NOT EXISTS sbis_category_id text;
ALTER TABLE menu_categories ADD COLUMN IF NOT EXISTS sbis_category_name_snapshot text;
ALTER TABLE menu_categories ADD COLUMN IF NOT EXISTS category_sync_state text NOT NULL DEFAULT 'unlinked';
ALTER TABLE menu_categories ADD COLUMN IF NOT EXISTS archived_at timestamptz;
ALTER TABLE menu_items ADD COLUMN IF NOT EXISTS archived_at timestamptz;
ALTER TABLE sbis_catalog_items ADD COLUMN IF NOT EXISTS description text;
ALTER TABLE sbis_catalog_items ADD COLUMN IF NOT EXISTS image_url text;
ALTER TABLE sbis_catalog_items ADD COLUMN IF NOT EXISTS in_current_price boolean NOT NULL DEFAULT true;
ALTER TABLE sbis_catalog_items ADD COLUMN IF NOT EXISTS snapshot_id text;

CREATE TABLE IF NOT EXISTS sbis_catalog_snapshots (
  id serial PRIMARY KEY,
  snapshot_id text NOT NULL UNIQUE,
  received_at timestamptz NOT NULL DEFAULT now(),
  source_version text,
  is_complete boolean NOT NULL DEFAULT false,
  categories_count integer NOT NULL DEFAULT 0,
  items_count integer NOT NULL DEFAULT 0,
  duration_ms integer,
  error text
);

CREATE TABLE IF NOT EXISTS menu_item_sbis_links (
  id serial PRIMARY KEY,
  menu_item_id integer NOT NULL REFERENCES menu_items(id) ON DELETE CASCADE,
  sbis_id text NOT NULL REFERENCES sbis_catalog_items(sbis_id),
  linked_at timestamptz NOT NULL DEFAULT now(),
  linked_by text NOT NULL,
  local_fields_hash text,
  sbis_fields_hash text,
  last_pull_at timestamptz,
  last_push_at timestamptz,
  last_result text,
  last_error text,
  unlinked_at timestamptz,
  unlink_reason text
);
CREATE INDEX IF NOT EXISTS menu_item_sbis_links_item_idx ON menu_item_sbis_links(menu_item_id);
CREATE INDEX IF NOT EXISTS menu_item_sbis_links_sbis_idx ON menu_item_sbis_links(sbis_id);
CREATE UNIQUE INDEX IF NOT EXISTS menu_item_sbis_links_active_item_unique ON menu_item_sbis_links(menu_item_id) WHERE unlinked_at IS NULL;
CREATE UNIQUE INDEX IF NOT EXISTS menu_item_sbis_links_active_sbis_unique ON menu_item_sbis_links(sbis_id) WHERE unlinked_at IS NULL;

INSERT INTO menu_item_sbis_links (menu_item_id, sbis_id, linked_by)
SELECT i.id, i.source_external_id, 'migration-0004'
FROM menu_items i
JOIN sbis_catalog_items c ON c.sbis_id = i.source_external_id
WHERE i.source_external_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM menu_item_sbis_links l WHERE l.menu_item_id = i.id AND l.unlinked_at IS NULL)
  AND NOT EXISTS (SELECT 1 FROM menu_item_sbis_links l WHERE l.sbis_id = i.source_external_id AND l.unlinked_at IS NULL);
