foodlinkk-command-center/migrations/011_retail_360.sql

-- Retail 360: notes, media, milestones, ownership, RSS, city demographics

CREATE TABLE IF NOT EXISTS entity_notes (
    id SERIAL PRIMARY KEY,
    entity_type VARCHAR(32) NOT NULL,
    entity_id INTEGER NOT NULL,
    title VARCHAR(255),
    body TEXT NOT NULL,
    note_type VARCHAR(32) DEFAULT 'general',
    created_by VARCHAR(64) DEFAULT 'team',
    pinned BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_entity_notes_entity ON entity_notes(entity_type, entity_id);

CREATE TABLE IF NOT EXISTS entity_media (
    id SERIAL PRIMARY KEY,
    entity_type VARCHAR(32) NOT NULL,
    entity_id INTEGER NOT NULL,
    filename VARCHAR(255) NOT NULL,
    content_type VARCHAR(64) DEFAULT 'image/jpeg',
    storage_path TEXT NOT NULL,
    caption TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_entity_media_entity ON entity_media(entity_type, entity_id);

CREATE TABLE IF NOT EXISTS sales_milestones (
    id SERIAL PRIMARY KEY,
    supermarket_id INTEGER REFERENCES supermarkets(id) ON DELETE CASCADE,
    client_id INTEGER REFERENCES clients(id) ON DELETE SET NULL,
    deal_id INTEGER REFERENCES deals(id) ON DELETE SET NULL,
    milestone_type VARCHAR(64) NOT NULL DEFAULT 'custom',
    title VARCHAR(255) NOT NULL,
    status VARCHAR(32) DEFAULT 'pending',
    target_date DATE,
    completed_at TIMESTAMPTZ,
    value_eur NUMERIC(12,2),
    notes TEXT,
    sort_order INTEGER DEFAULT 0,
    created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_milestones_store ON sales_milestones(supermarket_id);

CREATE TABLE IF NOT EXISTS ownership_changes (
    id SERIAL PRIMARY KEY,
    entity_type VARCHAR(32) DEFAULT 'supermarket',
    entity_id INTEGER,
    chain VARCHAR(100),
    previous_owner VARCHAR(255),
    new_owner VARCHAR(255) NOT NULL,
    change_type VARCHAR(64) DEFAULT 'acquisition',
    effective_date DATE,
    source VARCHAR(255),
    notes TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_ownership_chain ON ownership_changes(chain);

CREATE TABLE IF NOT EXISTS city_demographics (
    id SERIAL PRIMARY KEY,
    city VARCHAR(100) NOT NULL,
    province VARCHAR(50),
    gemeente_code VARCHAR(16),
    population INTEGER,
    households INTEGER,
    avg_income NUMERIC(12,2),
    muslim_proxy_pct NUMERIC(5,2),
    data_source VARCHAR(100) DEFAULT 'cbs',
    last_updated TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(city, province)
);
CREATE INDEX IF NOT EXISTS idx_city_demo_city ON city_demographics(city);

CREATE TABLE IF NOT EXISTS rss_feeds (
    id SERIAL PRIMARY KEY,
    name VARCHAR(128) NOT NULL UNIQUE,
    url TEXT NOT NULL,
    category VARCHAR(64) DEFAULT 'general',
    is_active BOOLEAN DEFAULT TRUE,
    last_fetch_at TIMESTAMPTZ,
    last_status VARCHAR(32)
);

CREATE TABLE IF NOT EXISTS rss_items (
    id SERIAL PRIMARY KEY,
    feed_id INTEGER NOT NULL REFERENCES rss_feeds(id) ON DELETE CASCADE,
    title TEXT NOT NULL,
    link TEXT,
    summary TEXT,
    published_at TIMESTAMPTZ,
    fetched_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(feed_id, link)
);
CREATE INDEX IF NOT EXISTS idx_rss_items_published ON rss_items(published_at DESC);

ALTER TABLE calendar_events ADD COLUMN IF NOT EXISTS supermarket_id INTEGER REFERENCES supermarkets(id) ON DELETE SET NULL;
ALTER TABLE wholesalers ADD COLUMN IF NOT EXISTS external_id VARCHAR(64);
CREATE UNIQUE INDEX IF NOT EXISTS idx_wholesalers_external ON wholesalers(external_id) WHERE external_id IS NOT NULL;

INSERT INTO rss_feeds (name, url, category) VALUES
    ('Foodlog NL', 'https://www.foodlog.nl/feed/', 'kant-en-klaar'),
    ('RetailDetail', 'https://www.retaildetail.nl/feed/', 'retail'),
    ('VMT Food', 'https://www.vmt.nl/feed/', 'food-industry'),
    ('Nu.nl Economie', 'https://www.nu.nl/rss/Economie', 'economie'),
    ('Google Halal Food NL', 'https://news.google.com/rss/search?q=halal+food+supermarkt+nederland&hl=nl&gl=NL&ceid=NL:nl', 'halal')
ON CONFLICT (name) DO NOTHING;

INSERT INTO data_providers (name, provider_type, config, is_active) VALUES
    ('rss_feeds', 'api', '{"type": "rss"}', true),
    ('wholesale_osm', 'scrape', '{"chains": ["Sligro", "Hanos", "Makro", "Bidfood"]}', true)
ON CONFLICT (name) DO UPDATE SET is_active = true;