-- 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;