Build webscraper to collect data from outlet menus

I have a list of appr. 50.000 outlets, of which I want to have the menu information scraped cleansed and organised (see FACT DIM below). Assignment is to receive a working and tested code for doing that, perphaps for a smal sample of 100 outlets or so to see that it's working.

Lump sum proposal please, needs to be ready asap.

Best regards,
Sander


--------------

CREATE TABLE public.outlet (
outlet_id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,
naam text NOT NULL,
plaats text,
website_url text,
segment text,
status text NOT NULL DEFAULT 'actief'::text CHECK (status = ANY (ARRAY['actief'::text, 'inactief'::text])),
created_at timestamp with time zone NOT NULL DEFAULT now(),
updated_at timestamp with time zone NOT NULL DEFAULT now(),
CONSTRAINT outlet_pkey PRIMARY KEY (outlet_id)
);
CREATE TABLE public.menu_source (
source_id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,
outlet_id bigint NOT NULL,
pagina_type text,
url text NOT NULL,
domain text,
bestandstype text CHECK (bestandstype = ANY (ARRAY['html'::text, 'pdf'::text, 'image'::text])),
confidence real,
is_active boolean NOT NULL DEFAULT true,
created_at timestamp with time zone NOT NULL DEFAULT now(),
CONSTRAINT menu_source_pkey PRIMARY KEY (source_id),
CONSTRAINT menu_source_outlet_id_fkey FOREIGN KEY (outlet_id) REFERENCES public.outlet(outlet_id)
);
CREATE TABLE public.crawl_run (
run_id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,
source_id bigint NOT NULL,
crawled_at timestamp with time zone NOT NULL DEFAULT now(),
http_status integer,
etag text,
last_modified text,
sha256 text,
parse_status text NOT NULL DEFAULT 'pending'::text CHECK (parse_status = ANY (ARRAY['pending'::text, 'ok'::text, 'partial'::text, 'failed'::text, 'not_modified'::text])),
parser_version text,
raw_ref text,
CONSTRAINT crawl_run_pkey PRIMARY KEY (run_id),
CONSTRAINT crawl_run_source_id_fkey FOREIGN KEY (source_id) REFERENCES public.menu_source(source_id)
);
CREATE TABLE public.menu_item (
menuitem_id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,
run_id bigint NOT NULL,
categorie text,
product text NOT NULL,
beschrijving text,
prijs numeric,
valuta text NOT NULL DEFAULT 'EUR'::text,
volume text,
merk text,
merkeigenaar text,
positie integer,
confidence real,
CONSTRAINT menu_item_pkey PRIMARY KEY (menuitem_id),
CONSTRAINT menu_item_run_id_fkey FOREIGN KEY (run_id) REFERENCES public.crawl_run(run_id)
);
CREATE TABLE public.change_event (
event_id bigint GENERATED ALWAYS AS IDENTITY NOT NULL,
outlet_id bigint NOT NULL,
source_id bigint,
oud_menuitem_id bigint,
nieuw_menuitem_id bigint,
detectiedatum timestamp with time zone NOT NULL DEFAULT now(),
event_type text NOT NULL CHECK (event_type = ANY (ARRAY['product_toegevoegd'::text, 'product_verwijderd'::text, 'prijs_verhoogd'::text, 'prijs_verlaagd'::text, 'brand_switch'::text, 'menu_redesign'::text])),
oude_waarde text,
nieuwe_waarde text,
ai_interpretatie text,
impact text CHECK (impact = ANY (ARRAY['hoog'::text, 'midden'::text, 'laag'::text])),
opportunity_score integer CHECK (opportunity_score >= 0 AND opportunity_score <= 100),
aanbevolen_actie text,
bevestigd_runs integer NOT NULL DEFAULT 1,
status text NOT NULL DEFAULT 'nieuw'::text CHECK (status = ANY (ARRAY['nieuw'::text, 'bekeken'::text, 'afgehandeld'::text, 'false_positive'::text])),
CONSTRAINT change_event_pkey PRIMARY KEY (event_id),
CONSTRAINT change_event_outlet_id_fkey FOREIGN KEY (outlet_id) REFERENCES public.outlet(outlet_id),
CONSTRAINT change_event_source_id_fkey FOREIGN KEY (source_id) REFERENCES public.menu_source(source_id),
CONSTRAINT change_event_oud_menuitem_id_fkey FOREIGN KEY (oud_menuitem_id) REFERENCES public.menu_item(menuitem_id),
CONSTRAINT change_event_nieuw_menuitem_id_fkey FOREIGN KEY (nieuw_menuitem_id) REFERENCES public.menu_item(menuitem_id)
);