sysops
81 lines
5.8 KiB
SQL
81 lines
5.8 KiB
SQL
-- Foodlinkk 037: Export Intel phase B — tenders + expanded caterer matrix
|
|
|
|
CREATE TABLE IF NOT EXISTS export_tenders (
|
|
id SERIAL PRIMARY KEY,
|
|
external_id VARCHAR(128) UNIQUE NOT NULL,
|
|
title VARCHAR(512) NOT NULL,
|
|
description TEXT,
|
|
country_iso2 CHAR(2),
|
|
issuer_name VARCHAR(255),
|
|
cpv_codes JSONB DEFAULT '[]'::jsonb,
|
|
deadline_at TIMESTAMPTZ,
|
|
url VARCHAR(512),
|
|
source VARCHAR(64) DEFAULT 'ted',
|
|
status VARCHAR(32) DEFAULT 'open',
|
|
raw JSONB DEFAULT '{}'::jsonb,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_export_tenders_country ON export_tenders(country_iso2);
|
|
CREATE INDEX IF NOT EXISTS idx_export_tenders_status ON export_tenders(status);
|
|
|
|
-- Additional global caterer brands
|
|
INSERT INTO export_caterer_brands (code, name, parent_brand_code, hq_country_iso2, website, tier) VALUES
|
|
('newrest', 'Newrest Group', NULL, 'FR', 'https://www.newrest.eu', 1),
|
|
('ssp', 'SSP Group', NULL, 'GB', 'https://www.foodtravelexperts.com', 1),
|
|
('dussmann', 'Dussmann', NULL, 'DE', 'https://www.dussmann.de', 1),
|
|
('apetito', 'apetito', NULL, 'DE', 'https://www.apetito.de', 1),
|
|
('westbury', 'Westbury Street Holdings', NULL, 'GB', 'https://www.westburystreet.co.uk', 1),
|
|
('baxterstorey', 'BaxterStorey', 'westbury', 'GB', 'https://www.baxterstorey.com', 2),
|
|
('chco', 'CH&CO', 'compass', 'GB', 'https://www.chandco.uk', 1),
|
|
('levy', 'Levy', 'compass', 'US', 'https://www.levyrestaurants.com', 1),
|
|
('transguard', 'Transguard Group', NULL, 'AE', 'https://www.transguardgroup.com', 2),
|
|
('ecs', 'ECS Catering', NULL, 'NL', 'https://www.ecs-catering.nl', 2)
|
|
ON CONFLICT (code) DO NOTHING;
|
|
|
|
-- EU + GCC presence expansion
|
|
INSERT INTO export_caterer_presence (brand_code, country_iso2, local_legal_name, service_lines, procurement_model, supplier_entry_point)
|
|
SELECT v.brand_code, v.country_iso2, v.local_name, v.service_lines::jsonb, v.procurement_model, v.supplier_entry_point
|
|
FROM (VALUES
|
|
('compass', 'DE', 'Compass Group Germany', '["workplace","healthcare"]', 'central_national', 'national_procurement'),
|
|
('sodexo', 'DE', 'Sodexo Deutschland', '["workplace","education"]', 'central_national', 'tender_portal'),
|
|
('compass', 'BE', 'Compass Group Belgium', '["workplace"]', 'central_national', 'national_procurement'),
|
|
('sodexo', 'BE', 'Sodexo Belgium', '["workplace","healthcare"]', 'central_national', 'tender_portal'),
|
|
('elior', 'FR', 'Elior Group', '["workplace","education","healthcare"]', 'central_national', 'national_procurement'),
|
|
('aramark', 'US', 'Aramark', '["workplace","sports_leisure","education"]', 'central_national', 'national_procurement'),
|
|
('compass', 'US', 'Compass Group USA', '["workplace","sports_leisure"]', 'central_national', 'national_procurement'),
|
|
('sodexo', 'US', 'Sodexo USA', '["workplace","healthcare"]', 'central_national', 'tender_portal'),
|
|
('iss', 'DE', 'ISS Deutschland', '["integrated_fm","workplace"]', 'central_national', 'national_procurement'),
|
|
('iss', 'GB', 'ISS UK', '["integrated_fm","workplace"]', 'central_national', 'national_procurement'),
|
|
('dussmann', 'DE', 'Dussmann Service Deutschland', '["workplace","healthcare"]', 'central_national', 'national_procurement'),
|
|
('apetito', 'DE', 'apetito group', '["healthcare","hospital_patient"]', 'central_national', 'category_manager'),
|
|
('apetito', 'NL', 'apetito Nederland', '["healthcare"]', 'central_national', 'category_manager'),
|
|
('newrest', 'FR', 'Newrest France', '["travel_hospitality"]', 'central_national', 'national_procurement'),
|
|
('ssp', 'GB', 'SSP UK', '["travel_hospitality"]', 'central_national', 'national_procurement'),
|
|
('transguard', 'AE', 'Transguard Group', '["integrated_fm","workplace"]', 'central_national', 'national_procurement'),
|
|
('sodexo', 'SA', 'Sodexo Saudi Arabia', '["workplace","healthcare"]', 'central_national', 'tender_portal'),
|
|
('compass', 'AU', 'Compass Group Australia', '["workplace","sports_leisure"]', 'central_national', 'national_procurement'),
|
|
('sodexo', 'AU', 'Sodexo Australia', '["workplace","healthcare"]', 'central_national', 'tender_portal')
|
|
) AS v(brand_code, country_iso2, local_name, service_lines, procurement_model, supplier_entry_point)
|
|
WHERE NOT EXISTS (
|
|
SELECT 1 FROM export_caterer_presence p WHERE p.brand_code = v.brand_code AND p.country_iso2 = v.country_iso2
|
|
);
|
|
|
|
-- More territories (EU core)
|
|
INSERT INTO export_territories (code, country_iso2, region_code, name_nl, name_en, lat, lon, map_zoom, bbox, sync_priority) VALUES
|
|
('it', 'IT', 'europe', 'Italië', 'Italy', 41.87, 12.57, 6, '[36.6,6.6,47.1,18.5]'::jsonb, 2),
|
|
('es', 'ES', 'europe', 'Spanje', 'Spain', 40.46, -3.75, 6, '[36.0,-9.3,43.8,4.3]'::jsonb, 2),
|
|
('pl', 'PL', 'europe', 'Polen', 'Poland', 51.92, 19.15, 6, '[49.0,14.1,54.9,24.2]'::jsonb, 2),
|
|
('at', 'AT', 'europe', 'Oostenrijk', 'Austria', 47.52, 14.55, 7, '[46.4,9.5,49.0,17.2]'::jsonb, 2),
|
|
('ch', 'CH', 'europe', 'Zwitserland', 'Switzerland', 46.82, 8.23, 7, '[45.8,5.9,47.8,10.5]'::jsonb, 2),
|
|
('se', 'SE', 'europe', 'Zweden', 'Sweden', 62.0, 15.0, 5, '[55.3,11.1,69.1,24.2]'::jsonb, 2),
|
|
('no', 'NO', 'europe', 'Noorwegen', 'Norway', 64.58, 17.5, 5, '[57.9,4.5,71.2,31.1]'::jsonb, 2),
|
|
('dk', 'DK', 'europe', 'Denemarken', 'Denmark', 56.0, 10.0, 7, '[54.5,8.0,57.8,15.2]'::jsonb, 2),
|
|
('kw', 'KW', 'gcc', 'Koeweit', 'Kuwait', 29.31, 47.48, 8, '[28.5,46.5,30.1,48.5]'::jsonb, 2),
|
|
('om', 'OM', 'gcc', 'Oman', 'Oman', 21.0, 57.0, 6, '[16.5,52.0,26.4,59.8]'::jsonb, 2),
|
|
('bh', 'BH', 'gcc', 'Bahrein', 'Bahrain', 26.07, 50.55, 9, '[25.8,50.45,26.3,50.7]'::jsonb, 2),
|
|
('jm', 'JM', 'caribbean', 'Jamaica', 'Jamaica', 18.11, -77.3, 8, '[17.7,-78.4,18.5,-76.2]'::jsonb, 2),
|
|
('tt', 'TT', 'caribbean', 'Trinidad en Tobago', 'Trinidad and Tobago', 10.5, -61.3, 8, '[10.0,-61.9,11.4,-60.5]'::jsonb, 2)
|
|
ON CONFLICT (code) DO NOTHING;
|