foodlinkk-command-center/snapshots/2026-06-09/migrations/009_full_upgrade.sql

-- Foodlinkk 009: research pipeline + retail intelligence + recommendations
-- Safe: IF NOT EXISTS throughout

CREATE TABLE IF NOT EXISTS data_providers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) UNIQUE NOT NULL,
  provider_type VARCHAR(50),
  config JSONB DEFAULT '{}',
  refresh_interval INTERVAL,
  last_fetch_at TIMESTAMP,
  last_status VARCHAR(20),
  is_active BOOLEAN DEFAULT TRUE
);

CREATE TABLE IF NOT EXISTS data_snapshots (
  id SERIAL PRIMARY KEY,
  provider_id INTEGER REFERENCES data_providers(id),
  fetched_at TIMESTAMP DEFAULT NOW(),
  payload JSONB NOT NULL,
  record_count INTEGER,
  ttl_expires_at TIMESTAMP
);

CREATE TABLE IF NOT EXISTS research_briefs (
  id SERIAL PRIMARY KEY,
  domain VARCHAR(50),
  title VARCHAR(255),
  summary TEXT,
  key_findings JSONB DEFAULT '[]',
  source_snapshot_ids INTEGER[],
  generated_at TIMESTAMP DEFAULT NOW(),
  expires_at TIMESTAMP
);

CREATE TABLE IF NOT EXISTS market_trends (
  id SERIAL PRIMARY KEY,
  category VARCHAR(100) NOT NULL,
  trend_name VARCHAR(255) NOT NULL,
  description TEXT,
  source VARCHAR(255),
  confidence_score DECIMAL(3, 2),
  opportunity_score DECIMAL(3, 2),
  related_products TEXT[],
  action_items TEXT[],
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),
  data_source VARCHAR(100)
);

CREATE TABLE IF NOT EXISTS competitor_products (
  id SERIAL PRIMARY KEY,
  competitor VARCHAR(100) NOT NULL,
  product_name VARCHAR(255) NOT NULL,
  category VARCHAR(100),
  price DECIMAL(10, 2),
  launch_date DATE,
  halal_certified BOOLEAN DEFAULT FALSE,
  ingredients TEXT,
  market_position VARCHAR(100),
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),
  data_source VARCHAR(100)
);

CREATE TABLE IF NOT EXISTS design_projects (
  id SERIAL PRIMARY KEY,
  client_id INTEGER,
  project_type VARCHAR(50) NOT NULL,
  brief TEXT,
  concepts JSONB,
  selected_design TEXT,
  approval_status VARCHAR(20),
  files_generated TEXT[],
  brand_guidelines JSONB,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),
  data_source VARCHAR(100)
);

CREATE TABLE IF NOT EXISTS brand_assets (
  id SERIAL PRIMARY KEY,
  client_id INTEGER,
  asset_type VARCHAR(50) NOT NULL,
  file_path TEXT NOT NULL,
  usage_rights TEXT,
  version VARCHAR(20),
  created_at TIMESTAMP DEFAULT NOW(),
  data_source VARCHAR(100)
);

CREATE TABLE IF NOT EXISTS supermarkets (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  chain VARCHAR(100) NOT NULL,
  address TEXT NOT NULL,
  postcode VARCHAR(7) NOT NULL,
  city VARCHAR(100) NOT NULL,
  province VARCHAR(50),
  latitude DECIMAL(10, 8),
  longitude DECIMAL(11, 8),
  store_type VARCHAR(50),
  size_m2 INTEGER,
  opening_hours JSONB,
  phone VARCHAR(20),
  email VARCHAR(255),
  website VARCHAR(255),
  halal_certified BOOLEAN DEFAULT FALSE,
  halal_certifier VARCHAR(100),
  partnership_status VARCHAR(20) DEFAULT 'none',
  client_id INTEGER,
  deal_id INTEGER,
  halal_certificate_number VARCHAR(100),
  halal_expiry_date DATE,
  has_halal_section BOOLEAN DEFAULT FALSE,
  organic_section BOOLEAN DEFAULT FALSE,
  alcohol_section BOOLEAN DEFAULT FALSE,
  last_updated TIMESTAMP DEFAULT NOW(),
  data_source VARCHAR(100)
);

CREATE TABLE IF NOT EXISTS butcher_shops (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  address TEXT NOT NULL,
  postcode VARCHAR(7) NOT NULL,
  city VARCHAR(100) NOT NULL,
  province VARCHAR(50),
  latitude DECIMAL(10, 8),
  longitude DECIMAL(11, 8),
  halal_certified BOOLEAN DEFAULT FALSE,
  halal_certifier VARCHAR(100),
  specialty VARCHAR(100),
  opening_hours JSONB,
  phone VARCHAR(20),
  website VARCHAR(255),
  last_updated TIMESTAMP DEFAULT NOW(),
  data_source VARCHAR(100)
);

CREATE TABLE IF NOT EXISTS wholesalers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  address TEXT NOT NULL,
  postcode VARCHAR(7) NOT NULL,
  city VARCHAR(100) NOT NULL,
  province VARCHAR(50),
  latitude DECIMAL(10, 8),
  longitude DECIMAL(11, 8),
  halal_certified BOOLEAN DEFAULT FALSE,
  halal_certifier VARCHAR(100),
  product_categories TEXT[],
  minimum_order DECIMAL(10, 2),
  delivery_areas TEXT[],
  opening_hours JSONB,
  phone VARCHAR(20),
  email VARCHAR(255),
  website VARCHAR(255),
  last_updated TIMESTAMP DEFAULT NOW(),
  data_source VARCHAR(100)
);

CREATE TABLE IF NOT EXISTS area_analysis (
  id SERIAL PRIMARY KEY,
  postcode VARCHAR(7) NOT NULL UNIQUE,
  city VARCHAR(100),
  population INTEGER,
  households INTEGER,
  avg_household_size DECIMAL(3, 2),
  avg_income DECIMAL(10, 2),
  median_income DECIMAL(10, 2),
  education_level JSONB,
  age_distribution JSONB,
  ethnic_composition JSONB,
  religious_composition JSONB,
  unemployment_rate DECIMAL(5, 2),
  housing_type JSONB,
  car_ownership DECIMAL(5, 2),
  last_updated TIMESTAMP DEFAULT NOW(),
  data_source VARCHAR(100)
);

CREATE TABLE IF NOT EXISTS weather_data (
  id SERIAL PRIMARY KEY,
  location_id INTEGER,
  location_type VARCHAR(50),
  region VARCHAR(100),
  city VARCHAR(100),
  date DATE NOT NULL,
  temperature_c DECIMAL(4, 1),
  feels_like_c DECIMAL(4, 1),
  humidity INTEGER,
  wind_speed_kmh DECIMAL(5, 1),
  precipitation_mm DECIMAL(5, 1),
  weather_condition VARCHAR(50),
  uv_index DECIMAL(3, 1),
  data_source VARCHAR(100),
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS marketing_strategies (
  id SERIAL PRIMARY KEY,
  strategy_name VARCHAR(255) NOT NULL,
  strategy_type VARCHAR(50),
  description TEXT,
  target_audience TEXT[],
  product_categories TEXT[],
  weather_conditions TEXT[],
  temperature_range_min DECIMAL(4, 1),
  temperature_range_max DECIMAL(4, 1),
  seasons TEXT[],
  channels TEXT[],
  budget_range DECIMAL(10, 2),
  expected_roi DECIMAL(5, 2),
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS marketing_recommendations (
  id SERIAL PRIMARY KEY,
  strategy_id INTEGER,
  location_id INTEGER,
  location_type VARCHAR(50),
  weather_data_id INTEGER,
  recommendation_text TEXT NOT NULL,
  priority VARCHAR(20),
  action_items TEXT[],
  expected_impact TEXT,
  generated_at TIMESTAMP DEFAULT NOW(),
  expires_at TIMESTAMP,
  status VARCHAR(20),
  data_source VARCHAR(100)
);

CREATE TABLE IF NOT EXISTS ai_recommendations (
  id SERIAL PRIMARY KEY,
  recommendation_type VARCHAR(50),
  title VARCHAR(255) NOT NULL,
  description TEXT,
  priority VARCHAR(20),
  impact_score DECIMAL(3, 2),
  confidence_score DECIMAL(3, 2),
  data_sources JSONB,
  action_items TEXT[],
  estimated_value DECIMAL(10, 2),
  generated_by VARCHAR(100),
  related_entity_type VARCHAR(50),
  related_entity_id INTEGER,
  status VARCHAR(20) DEFAULT 'pending',
  expires_at TIMESTAMP,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS herman_conversations (
  id SERIAL PRIMARY KEY,
  user_id VARCHAR(100),
  session_id VARCHAR(100),
  message TEXT NOT NULL,
  role VARCHAR(20),
  context JSONB,
  timestamp TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS herman_actions (
  id SERIAL PRIMARY KEY,
  action_type VARCHAR(50),
  title VARCHAR(255) NOT NULL,
  description TEXT,
  target_agent VARCHAR(100),
  parameters JSONB,
  status VARCHAR(20),
  result JSONB,
  created_at TIMESTAMP DEFAULT NOW(),
  completed_at TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_data_snapshots_provider ON data_snapshots(provider_id);
CREATE INDEX IF NOT EXISTS idx_research_briefs_domain ON research_briefs(domain);
CREATE INDEX IF NOT EXISTS idx_supermarkets_chain ON supermarkets(chain);
CREATE INDEX IF NOT EXISTS idx_supermarkets_partnership ON supermarkets(partnership_status);
CREATE INDEX IF NOT EXISTS idx_ai_recommendations_status ON ai_recommendations(status);

INSERT INTO data_providers (name, provider_type, config, is_active) VALUES
  ('crm', 'internal', '{"description":"PostgreSQL CRM"}', true),
  ('weather', 'api', '{"url":"https://api.open-meteo.com/v1/forecast"}', true),
  ('social', 'internal', '{"table":"social_mentions"}', true),
  ('retail_manual', 'internal', '{"description":"Seed retail locations"}', true)
ON CONFLICT (name) DO NOTHING;

INSERT INTO supermarkets (name, chain, address, postcode, city, province, latitude, longitude, partnership_status, data_source)
VALUES
  ('Jumbo Bos en Lommer', 'Jumbo', 'Bos en Lommerweg 123', '1055RW', 'Amsterdam', 'Noord-Holland', 52.378100, 4.832000, 'active', 'seed'),
  ('AH Chai N Masala regio', 'Albert Heijn', 'Haarlemmerdijk 1', '1013EM', 'Amsterdam', 'Noord-Holland', 52.384500, 4.883200, 'proposal', 'seed')
ON CONFLICT DO NOTHING;

INSERT INTO ai_recommendations (recommendation_type, title, description, priority, impact_score, confidence_score, status, generated_by, action_items)
SELECT 'deal_action', 'Chai N Masala — margin sheet', 'Deal Q3 spice range deadline 20 jun — stuur margin sheet', 'high', 0.85, 0.90, 'pending', 'herman', ARRAY['Margin sheet versturen','Follow-up call plannen']
WHERE NOT EXISTS (SELECT 1 FROM ai_recommendations WHERE title = 'Chai N Masala — margin sheet');

INSERT INTO ai_recommendations (recommendation_type, title, description, priority, impact_score, confidence_score, status, generated_by, action_items)
SELECT 'retail_target', 'Van den Tweel Jumbo regio', 'Follow-up voorstel supermarkt placement Amsterdam-West', 'medium', 0.72, 0.80, 'pending', 'herman', ARRAY['Contact Van den Tweel','Update proposal deck']
WHERE NOT EXISTS (SELECT 1 FROM ai_recommendations WHERE title = 'Van den Tweel Jumbo regio');