67 lines
3.4 KiB
SQL
67 lines
3.4 KiB
SQL
|
-- RSS bookmarks, promo campaigns, wholesaler contacts
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS rss_bookmarks (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
rss_item_id INTEGER NOT NULL REFERENCES rss_items(id) ON DELETE CASCADE,
|
||
|
|
title VARCHAR(512),
|
||
|
|
link VARCHAR(1024),
|
||
|
|
feed_name VARCHAR(255),
|
||
|
|
notes TEXT,
|
||
|
|
tags TEXT[] DEFAULT '{}',
|
||
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
|
|
UNIQUE(rss_item_id)
|
||
|
|
);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_rss_bookmarks_created ON rss_bookmarks(created_at DESC);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS promo_campaigns (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
chain VARCHAR(100),
|
||
|
|
supermarket_id INTEGER REFERENCES supermarkets(id) ON DELETE SET NULL,
|
||
|
|
title VARCHAR(255) NOT NULL,
|
||
|
|
folder_path VARCHAR(512),
|
||
|
|
folder_label VARCHAR(255),
|
||
|
|
description TEXT,
|
||
|
|
image_url VARCHAR(512),
|
||
|
|
valid_from DATE,
|
||
|
|
valid_to DATE,
|
||
|
|
status VARCHAR(32) DEFAULT 'active',
|
||
|
|
promo_type VARCHAR(64) DEFAULT 'folder',
|
||
|
|
tags TEXT[] DEFAULT '{}',
|
||
|
|
source VARCHAR(100) DEFAULT 'manual',
|
||
|
|
metadata JSONB DEFAULT '{}'::jsonb,
|
||
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
||
|
|
);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_promo_campaigns_chain ON promo_campaigns(chain);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_promo_campaigns_status ON promo_campaigns(status);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_promo_campaigns_dates ON promo_campaigns(valid_from, valid_to);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS wholesaler_contacts (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
wholesaler_id INTEGER NOT NULL REFERENCES wholesalers(id) ON DELETE CASCADE,
|
||
|
|
role VARCHAR(64) NOT NULL DEFAULT 'contact',
|
||
|
|
full_name VARCHAR(255),
|
||
|
|
phone VARCHAR(32),
|
||
|
|
email VARCHAR(255),
|
||
|
|
linkedin_url VARCHAR(512),
|
||
|
|
source VARCHAR(100) DEFAULT 'manual',
|
||
|
|
confidence NUMERIC(3,2) DEFAULT 0.50,
|
||
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
||
|
|
);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_wholesaler_contacts_wid ON wholesaler_contacts(wholesaler_id);
|
||
|
|
|
||
|
|
ALTER TABLE wholesalers ADD COLUMN IF NOT EXISTS linkedin_url VARCHAR(512);
|
||
|
|
|
||
|
|
-- Seed example promo folders (user can edit/add via UI)
|
||
|
|
INSERT INTO promo_campaigns (chain, title, folder_path, folder_label, description, valid_from, valid_to, status, promo_type, tags)
|
||
|
|
SELECT v.chain, v.title, v.folder_path, v.folder_label, v.description, v.valid_from, v.valid_to, 'active', 'folder', v.tags
|
||
|
|
FROM (VALUES
|
||
|
|
('Albert Heijn', 'Bonus folder', '/nas/marketing/reclame/ah-bonus', 'AH Bonus week', 'Wekelijkse AH Bonus acties — koppeling met kant-en-klaar promoties', CURRENT_DATE, CURRENT_DATE + 7, ARRAY['bonus','kant-en-klaar']),
|
||
|
|
('Jumbo', 'Aanbiedingen folder', '/nas/marketing/reclame/jumbo-folder', 'Jumbo folder', 'Jumbo reclamefolder — check halal/kant-en-klaar paginas', CURRENT_DATE, CURRENT_DATE + 14, ARRAY['folder','promo']),
|
||
|
|
('Lidl', 'Lidl folder', '/nas/marketing/reclame/lidl', 'Lidl acties', 'Lidl weekacties en seizoenspromoties', CURRENT_DATE, CURRENT_DATE + 7, ARRAY['folder']),
|
||
|
|
('Plus', 'Plus folder', '/nas/marketing/reclame/plus', 'Plus aanbiedingen', 'Plus supermarkt reclame — regionale varianten', CURRENT_DATE, CURRENT_DATE + 7, ARRAY['folder']),
|
||
|
|
('Dirk', 'Dirk aanbiedingen', '/nas/marketing/reclame/dirk', 'Dirk folder', 'Dirk van den Broek weekfolder', CURRENT_DATE, CURRENT_DATE + 7, ARRAY['folder'])
|
||
|
|
) AS v(chain, title, folder_path, folder_label, description, valid_from, valid_to, tags)
|
||
|
|
WHERE NOT EXISTS (SELECT 1 FROM promo_campaigns LIMIT 1);
|