foodlinkk-command-center/migrations/036_export_intel.sql

-- Foodlinkk 036: Export Intel Global (schema + fase A seeds)

CREATE TABLE IF NOT EXISTS export_regions (
    code VARCHAR(32) PRIMARY KEY,
    name_nl VARCHAR(128) NOT NULL,
    name_en VARCHAR(128) NOT NULL,
    sort_order INT DEFAULT 0
);

CREATE TABLE IF NOT EXISTS export_territories (
    code VARCHAR(64) PRIMARY KEY,
    country_iso2 CHAR(2) NOT NULL,
    region_code VARCHAR(32) REFERENCES export_regions(code),
    name_nl VARCHAR(128) NOT NULL,
    name_en VARCHAR(128) NOT NULL,
    lat DOUBLE PRECISION,
    lon DOUBLE PRECISION,
    map_zoom INT DEFAULT 6,
    bbox JSONB,
    sync_priority INT DEFAULT 3,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS export_caterer_brands (
    code VARCHAR(64) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    parent_brand_code VARCHAR(64) REFERENCES export_caterer_brands(code),
    hq_country_iso2 CHAR(2),
    website VARCHAR(512),
    tier INT DEFAULT 2,
    notes TEXT,
    annual_report_url VARCHAR(512),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS export_caterer_presence (
    id SERIAL PRIMARY KEY,
    brand_code VARCHAR(64) NOT NULL REFERENCES export_caterer_brands(code),
    country_iso2 CHAR(2) NOT NULL,
    local_legal_name VARCHAR(255),
    service_lines JSONB DEFAULT '[]'::jsonb,
    regions JSONB DEFAULT '[]'::jsonb,
    site_count_estimate INT,
    procurement_model VARCHAR(32),
    product_categories_served JSONB DEFAULT '[]'::jsonb,
    supplier_entry_point VARCHAR(32),
    is_active BOOLEAN DEFAULT TRUE,
    last_verified_at TIMESTAMPTZ,
    sources JSONB DEFAULT '[]'::jsonb,
    entity_id INT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE (brand_code, country_iso2)
);

CREATE TABLE IF NOT EXISTS export_market_entities (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    entity_type VARCHAR(64) NOT NULL,
    country_iso2 CHAR(2) NOT NULL,
    territory_code VARCHAR(64) REFERENCES export_territories(code),
    lat DOUBLE PRECISION,
    lon DOUBLE PRECISION,
    address_line VARCHAR(512),
    city VARCHAR(128),
    postal_code VARCHAR(32),
    phone VARCHAR(64),
    email VARCHAR(255),
    website VARCHAR(512),
    product_interest JSONB DEFAULT '[]'::jsonb,
    volume_band VARCHAR(32),
    cold_chain BOOLEAN DEFAULT FALSE,
    halal_cert_notes TEXT,
    google_place_id VARCHAR(128),
    brand_code VARCHAR(64) REFERENCES export_caterer_brands(code),
    source VARCHAR(64) DEFAULT 'manual',
    sources JSONB DEFAULT '[]'::jsonb,
    confidence INT DEFAULT 50,
    geo_status VARCHAR(32) DEFAULT 'ok',
    canonical_id INT REFERENCES export_market_entities(id),
    pipeline_stage VARCHAR(32) DEFAULT 'identified',
    client_id INT REFERENCES clients(id) ON DELETE SET NULL,
    deal_id INT REFERENCES deals(id) ON DELETE SET NULL,
    metadata JSONB DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS export_entity_contacts (
    id SERIAL PRIMARY KEY,
    entity_id INT NOT NULL REFERENCES export_market_entities(id) ON DELETE CASCADE,
    presence_id INT REFERENCES export_caterer_presence(id) ON DELETE SET NULL,
    contact_type VARCHAR(32) DEFAULT 'person',
    role VARCHAR(64) DEFAULT 'general',
    name VARCHAR(255),
    title VARCHAR(255),
    department VARCHAR(128),
    email VARCHAR(255),
    email_alt VARCHAR(255),
    phone VARCHAR(64),
    mobile VARCHAR(64),
    whatsapp VARCHAR(64),
    fax VARCHAR(64),
    linkedin_url VARCHAR(512),
    website_contact_url VARCHAR(512),
    address_line VARCHAR(512),
    city VARCHAR(128),
    postal_code VARCHAR(32),
    language VARCHAR(8) DEFAULT 'en',
    is_primary BOOLEAN DEFAULT FALSE,
    opt_out BOOLEAN DEFAULT FALSE,
    source VARCHAR(64) DEFAULT 'manual',
    source_url VARCHAR(512),
    confidence INT DEFAULT 50,
    verified_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS export_gov_data_sources (
    id SERIAL PRIMARY KEY,
    country_iso2 CHAR(2) NOT NULL,
    name VARCHAR(255) NOT NULL,
    category VARCHAR(64) NOT NULL,
    url VARCHAR(512),
    api_url VARCHAR(512),
    notes TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS export_outreach_log (
    id SERIAL PRIMARY KEY,
    entity_id INT REFERENCES export_market_entities(id) ON DELETE SET NULL,
    presence_id INT REFERENCES export_caterer_presence(id) ON DELETE SET NULL,
    campaign_type VARCHAR(64) NOT NULL,
    email_message_id INT,
    deal_id INT REFERENCES deals(id) ON DELETE SET NULL,
    status VARCHAR(32) DEFAULT 'draft',
    template_id VARCHAR(64),
    sent_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_export_entities_country ON export_market_entities(country_iso2);
CREATE INDEX IF NOT EXISTS idx_export_entities_type ON export_market_entities(entity_type);
CREATE INDEX IF NOT EXISTS idx_export_contacts_entity ON export_entity_contacts(entity_id);
CREATE INDEX IF NOT EXISTS idx_export_contacts_email ON export_entity_contacts(email);
CREATE INDEX IF NOT EXISTS idx_export_presence_country ON export_caterer_presence(country_iso2);

-- Regions
INSERT INTO export_regions (code, name_nl, name_en, sort_order) VALUES
    ('caribbean', 'Caribisch', 'Caribbean', 1),
    ('gcc', 'GCC', 'GCC', 2),
    ('europe', 'Europa', 'Europe', 3),
    ('africa', 'Afrika', 'Africa', 4),
    ('asia', 'Asia', 'Asia', 5),
    ('americas', 'Amerika', 'Americas', 6),
    ('oceania', 'Oceanië', 'Oceania', 7)
ON CONFLICT (code) DO NOTHING;

-- Territories (fase A: key markets + Caribbean + GCC)
INSERT INTO export_territories (code, country_iso2, region_code, name_nl, name_en, lat, lon, map_zoom, bbox, sync_priority) VALUES
    ('nl', 'NL', 'europe', 'Nederland', 'Netherlands', 52.37, 4.90, 7, '[50.75,3.2,53.7,7.3]'::jsonb, 1),
    ('aw', 'AW', 'caribbean', 'Aruba', 'Aruba', 12.52, -70.03, 11, '[12.4,-70.1,12.65,-69.8]'::jsonb, 1),
    ('cw', 'CW', 'caribbean', 'Curaçao', 'Curaçao', 12.17, -68.99, 11, '[11.9,-69.2,12.4,-68.6]'::jsonb, 1),
    ('bq', 'BQ', 'caribbean', 'Bonaire', 'Bonaire', 12.15, -68.27, 12, '[12.0,-68.4,12.3,-68.2]'::jsonb, 1),
    ('sx', 'SX', 'caribbean', 'Sint Maarten', 'Sint Maarten', 18.04, -63.05, 11, '[17.9,-63.2,18.1,-62.9]'::jsonb, 1),
    ('bq-se', 'BQ', 'caribbean', 'Saba', 'Saba', 17.63, -63.23, 12, '[17.6,-63.3,17.7,-63.2]'::jsonb, 1),
    ('bq-bo', 'BQ', 'caribbean', 'Sint Eustatius', 'Sint Eustatius', 17.48, -62.98, 12, '[17.4,-63.1,17.5,-62.9]'::jsonb, 1),
    ('ae', 'AE', 'gcc', 'Verenigde Arabische Emiraten', 'United Arab Emirates', 24.45, 54.37, 7, '[22.5,51.5,26.1,56.5]'::jsonb, 1),
    ('de', 'DE', 'europe', 'Duitsland', 'Germany', 51.16, 10.45, 6, '[47.3,5.9,55.1,15.0]'::jsonb, 2),
    ('gb', 'GB', 'europe', 'Verenigd Koninkrijk', 'United Kingdom', 54.0, -2.0, 6, '[49.9,-8.6,60.9,1.8]'::jsonb, 2),
    ('fr', 'FR', 'europe', 'Frankrijk', 'France', 46.6, 2.35, 6, '[41.3,-5.1,51.1,9.6]'::jsonb, 2),
    ('be', 'BE', 'europe', 'België', 'Belgium', 50.64, 4.67, 7, '[49.5,2.5,51.5,6.4]'::jsonb, 2),
    ('us', 'US', 'americas', 'Verenigde Staten', 'United States', 39.8, -98.5, 4, '[24.5,-125,49.5,-66]'::jsonb, 2),
    ('sa', 'SA', 'gcc', 'Saudi-Arabië', 'Saudi Arabia', 24.0, 45.0, 5, '[16.0,34.5,32.2,55.7]'::jsonb, 2),
    ('qa', 'QA', 'gcc', 'Qatar', 'Qatar', 25.35, 51.18, 8, '[24.4,50.7,26.2,51.7]'::jsonb, 2),
    ('ma', 'MA', 'africa', 'Marokko', 'Morocco', 31.79, -7.09, 6, '[21.0,-17.0,36.0,-1.0]'::jsonb, 3),
    ('tr', 'TR', 'europe', 'Turkije', 'Turkey', 39.0, 35.0, 6, '[36.0,26.0,42.1,45.0]'::jsonb, 3),
    ('my', 'MY', 'asia', 'Maleisië', 'Malaysia', 4.21, 101.98, 6, '[0.8,99.6,7.4,119.3]'::jsonb, 3),
    ('id', 'ID', 'asia', 'Indonesia', 'Indonesia', -2.5, 118.0, 5, '[-11.0,95.0,6.0,141.0]'::jsonb, 3),
    ('au', 'AU', 'oceania', 'Australië', 'Australia', -25.27, 133.78, 4, '[-44.0,112.9,-10.0,154.0]'::jsonb, 3)
ON CONFLICT (code) DO NOTHING;

-- Global caterer brands (tier 1 + NL)
INSERT INTO export_caterer_brands (code, name, parent_brand_code, hq_country_iso2, website, tier) VALUES
    ('compass', 'Compass Group', NULL, 'GB', 'https://www.compass-group.com', 1),
    ('eurest', 'Eurest', 'compass', 'GB', 'https://www.eurest.nl', 1),
    ('sodexo', 'Sodexo', NULL, 'FR', 'https://www.sodexo.com', 1),
    ('aramark', 'Aramark', NULL, 'US', 'https://www.aramark.com', 1),
    ('elior', 'Elior Group', NULL, 'FR', 'https://www.eliorgroup.com', 1),
    ('iss', 'ISS A/S', NULL, 'DK', 'https://www.issworld.com', 1),
    ('albron', 'Albron', NULL, 'NL', 'https://www.albron.nl', 2),
    ('hutten', 'Hutten Catering', NULL, 'NL', 'https://www.hutten.nl', 2),
    ('appel', 'Appèl Catering', NULL, 'NL', 'https://www.appel.nl', 2),
    ('vermaat', 'Vermaat', NULL, 'NL', 'https://www.vermaat.nl', 2),
    ('restoplan', 'Restoplan', NULL, 'NL', 'https://www.restoplan.nl', 2),
    ('vitam', 'Vitam', NULL, 'NL', 'https://www.vitam.nl', 2)
ON CONFLICT (code) DO NOTHING;

-- Caterer presence (NL + sample international)
INSERT INTO export_caterer_presence (brand_code, country_iso2, local_legal_name, service_lines, procurement_model, supplier_entry_point) VALUES
    ('albron', 'NL', 'Albron Nederland BV', '["workplace","education","healthcare","travel_hospitality"]'::jsonb, 'central_national', 'national_procurement'),
    ('hutten', 'NL', 'Hutten Business Catering BV', '["workplace","events_banqueting"]'::jsonb, 'regional', 'category_manager'),
    ('appel', 'NL', 'Appèl B.V.', '["education","workplace"]'::jsonb, 'regional', 'category_manager'),
    ('vermaat', 'NL', 'Vermaat Bedrijfshoreca B.V.', '["workplace","events_banqueting"]'::jsonb, 'central_national', 'national_procurement'),
    ('eurest', 'NL', 'Eurest Nederland BV', '["workplace","integrated_fm"]'::jsonb, 'central_national', 'national_procurement'),
    ('sodexo', 'NL', 'Sodexo BV', '["workplace","education","healthcare"]'::jsonb, 'central_national', 'tender_portal'),
    ('iss', 'NL', 'ISS Facility Services', '["integrated_fm","workplace"]'::jsonb, 'central_national', 'national_procurement'),
    ('sodexo', 'AE', 'Sodexo Middle East', '["workplace","healthcare","travel_hospitality"]'::jsonb, 'central_national', 'national_procurement'),
    ('compass', 'GB', 'Compass Group UK', '["workplace","sports_leisure"]'::jsonb, 'central_national', 'national_procurement'),
    ('sodexo', 'FR', 'Sodexo France', '["workplace","education","healthcare"]'::jsonb, 'central_national', 'tender_portal'),
    ('aramark', 'US', 'Aramark', '["workplace","sports_leisure","education"]'::jsonb, 'central_national', 'national_procurement')
ON CONFLICT (brand_code, country_iso2) DO NOTHING;

-- Distributors (Caribbean fase 1 seeds)
INSERT INTO export_market_entities (name, entity_type, country_iso2, territory_code, city, website, source, confidence, product_interest, volume_band)
SELECT v.name, v.entity_type, v.country_iso2, v.territory_code, v.city, v.website, v.source, v.confidence, v.product_interest, v.volume_band
FROM (VALUES
    ('EWT / Anur Curaçao', 'distributor', 'CW', 'cw', 'Willemstad', 'https://ewtcuracao.com', 'manual', 80, '["chicken","shoarma","beef"]'::jsonb, 'container'),
    ('Caribbean Overseas', 'distributor', 'AW', 'aw', 'Oranjestad', 'https://caribbeanoverseas.com', 'manual', 75, '["chicken","shoarma"]'::jsonb, 'pallet'),
    ('Compra Aruba', 'distributor', 'AW', 'aw', 'Oranjestad', 'https://compra-aruba.com', 'manual', 75, '["chicken","beef"]'::jsonb, 'pallet'),
    ('Fresh Supplier Bonaire', 'distributor', 'BQ', 'bq', 'Kralendijk', 'https://freshsupplierbonaire.com', 'manual', 70, '["chicken","shoarma"]'::jsonb, 'pallet'),
    ('Ferrara Food Group', 'distributor', 'CW', 'cw', 'Willemstad', NULL, 'manual', 65, '["chicken","beef"]'::jsonb, 'pallet'),
    ('Pietersz Foodservice', 'wholesaler', 'NL', 'nl', 'Rotterdam', NULL, 'manual', 70, '["chicken","shoarma"]'::jsonb, 'pallet')
) AS v(name, entity_type, country_iso2, territory_code, city, website, source, confidence, product_interest, volume_band)
WHERE NOT EXISTS (
    SELECT 1 FROM export_market_entities e WHERE e.name = v.name AND e.country_iso2 = v.country_iso2
);

-- NL contract caterers as entities
INSERT INTO export_market_entities (name, entity_type, country_iso2, territory_code, city, website, brand_code, source, confidence, product_interest)
SELECT v.name, v.entity_type, v.country_iso2, v.territory_code, v.city, v.website, v.brand_code, v.source, v.confidence, v.product_interest
FROM (VALUES
    ('Albron Nederland', 'contract_caterer', 'NL', 'nl', 'Utrecht', 'https://www.albron.nl', 'albron', 'caterer_registry', 85, '["chicken","shoarma"]'::jsonb),
    ('Hutten Business Catering', 'contract_caterer', 'NL', 'nl', 'Eindhoven', 'https://www.hutten.nl', 'hutten', 'caterer_registry', 85, '["chicken","shoarma"]'::jsonb),
    ('Appèl Catering', 'contract_caterer', 'NL', 'nl', 'Veghel', 'https://www.appel.nl', 'appel', 'caterer_registry', 85, '["chicken","shoarma"]'::jsonb),
    ('Vermaat Bedrijfshoreca', 'contract_caterer', 'NL', 'nl', 'Hoofddorp', 'https://www.vermaat.nl', 'vermaat', 'caterer_registry', 85, '["chicken","shoarma"]'::jsonb),
    ('Sodexo Nederland', 'contract_caterer', 'NL', 'nl', 'Amsterdam', 'https://www.sodexo.nl', 'sodexo', 'caterer_registry', 80, '["chicken","shoarma","beef"]'::jsonb),
    ('Eurest Nederland', 'contract_caterer', 'NL', 'nl', 'Amsterdam', 'https://www.eurest.nl', 'eurest', 'caterer_registry', 80, '["chicken","shoarma"]'::jsonb)
) AS v(name, entity_type, country_iso2, territory_code, city, website, brand_code, source, confidence, product_interest)
WHERE NOT EXISTS (
    SELECT 1 FROM export_market_entities e WHERE e.name = v.name AND e.country_iso2 = v.country_iso2
);

-- Sample contacts (fase A — meer volgt in fase B enrichment)
INSERT INTO export_entity_contacts (entity_id, role, name, email, phone, source, confidence, is_primary)
SELECT e.id, 'sales', 'Sales', 'info@ewtcuracao.com', '+59997371234', 'manual', 70, TRUE
FROM export_market_entities e
WHERE e.name = 'EWT / Anur Curaçao'
  AND NOT EXISTS (SELECT 1 FROM export_entity_contacts c WHERE c.entity_id = e.id AND c.email = 'info@ewtcuracao.com')
LIMIT 1;

INSERT INTO export_entity_contacts (entity_id, role, name, email, source, confidence, is_primary)
SELECT e.id, 'procurement', 'Inkoop', 'inkoop@albron.nl', 'manual', 60, TRUE
FROM export_market_entities e
WHERE e.name = 'Albron Nederland'
  AND NOT EXISTS (SELECT 1 FROM export_entity_contacts c WHERE c.entity_id = e.id AND c.email = 'inkoop@albron.nl')
LIMIT 1;

INSERT INTO export_entity_contacts (entity_id, role, name, email, source, confidence, is_primary)
SELECT e.id, 'procurement', 'Inkoop', 'info@hutten.nl', 'manual', 55, TRUE
FROM export_market_entities e
WHERE e.name = 'Hutten Business Catering'
  AND NOT EXISTS (SELECT 1 FROM export_entity_contacts c WHERE c.entity_id = e.id AND c.email = 'info@hutten.nl')
LIMIT 1;

INSERT INTO export_entity_contacts (entity_id, role, name, email, source, confidence, is_primary)
SELECT e.id, 'procurement', 'Inkoop', 'info@appel.nl', 'manual', 55, TRUE
FROM export_market_entities e
WHERE e.name = 'Appèl Catering'
  AND NOT EXISTS (SELECT 1 FROM export_entity_contacts c WHERE c.entity_id = e.id AND c.email = 'info@appel.nl')
LIMIT 1;

-- Gov sources sample
INSERT INTO export_gov_data_sources (country_iso2, name, category, url)
SELECT v.country_iso2, v.name, v.category, v.url
FROM (VALUES
    ('NL', 'CBS Open Data BES', 'stats', 'https://opendata.cbs.nl'),
    ('AW', 'CBS Aruba', 'stats', 'https://www.cbs.aw'),
    ('CW', 'CBS Curaçao', 'stats', 'https://www.cbs.cw'),
    ('AE', 'Dubai Pulse', 'stats', 'https://www.dubaipulse.gov.ae'),
    ('NL', 'TenderNed', 'tenders', 'https://www.tenderned.nl'),
    ('EU', 'TED Europa', 'tenders', 'https://ted.europa.eu')
) AS v(country_iso2, name, category, url)
WHERE NOT EXISTS (
    SELECT 1 FROM export_gov_data_sources g WHERE g.name = v.name AND g.country_iso2 = v.country_iso2
);

-- Geo coordinates for map (fase A)
UPDATE export_market_entities SET lat = 12.108, lon = -68.933 WHERE name = 'EWT / Anur Curaçao';
UPDATE export_market_entities SET lat = 12.524, lon = -70.027 WHERE name = 'Caribbean Overseas';
UPDATE export_market_entities SET lat = 12.524, lon = -70.027 WHERE name = 'Compra Aruba';
UPDATE export_market_entities SET lat = 12.144, lon = -68.265 WHERE name = 'Fresh Supplier Bonaire';
UPDATE export_market_entities SET lat = 12.108, lon = -68.933 WHERE name = 'Ferrara Food Group';
UPDATE export_market_entities SET lat = 51.92, lon = 4.48 WHERE name = 'Pietersz Foodservice';
UPDATE export_market_entities SET lat = 52.09, lon = 5.12 WHERE name = 'Albron Nederland';
UPDATE export_market_entities SET lat = 51.44, lon = 5.48 WHERE name = 'Hutten Business Catering';
UPDATE export_market_entities SET lat = 51.61, lon = 5.55 WHERE name = 'Appèl Catering';
UPDATE export_market_entities SET lat = 52.30, lon = 4.69 WHERE name = 'Vermaat Bedrijfshoreca';
UPDATE export_market_entities SET lat = 52.37, lon = 4.90 WHERE name = 'Sodexo Nederland';
UPDATE export_market_entities SET lat = 52.37, lon = 4.90 WHERE name = 'Eurest Nederland';