-- Migration: Add metadata columns to news_sources table
-- For regional news customization in the visualizer
-- Add region, logo, website, and description columns
ALTER TABLE news_sources
ADD COLUMN IF NOT EXISTS region TEXT DEFAULT 'national',
ADD COLUMN IF NOT EXISTS logo_url TEXT,
ADD COLUMN IF NOT EXISTS website_url TEXT,
ADD COLUMN IF NOT EXISTS description_en TEXT,
ADD COLUMN IF NOT EXISTS description_fr TEXT,
ADD COLUMN IF NOT EXISTS has_paywall BOOLEAN DEFAULT false,
ADD COLUMN IF NOT EXISTS credibility_tier TEXT DEFAULT 'standard';
-- Add check constraint for region values
ALTER TABLE news_sources
ADD CONSTRAINT news_sources_region_check
CHECK (region IN ('national', 'ON', 'QC', 'BC', 'AB', 'MB', 'SK', 'NS', 'NB', 'NL', 'PE', 'NT', 'YT', 'NU'));
-- Add check constraint for credibility tier
ALTER TABLE news_sources
ADD CONSTRAINT news_sources_credibility_check
CHECK (credibility_tier IN ('premium', 'standard', 'aggregator'));
-- Populate existing sources with regional data and metadata
UPDATE news_sources SET
region = 'national',
website_url = 'https://www.cbc.ca/news/politics',
description_en = 'Canada''s public broadcaster covering federal politics',
description_fr = 'Le radiodiffuseur public du Canada couvrant la politique fédérale',
has_paywall = false,
credibility_tier = 'premium'
WHERE id = 'cbc';
UPDATE news_sources SET
region = 'national',
website_url = 'https://www.theglobeandmail.com/politics/',
description_en = 'Canada''s national newspaper of record',
description_fr = 'Le journal national de référence du Canada',
has_paywall = true,
credibility_tier = 'premium'
WHERE id = 'globe';
UPDATE news_sources SET
region = 'national',
website_url = 'https://nationalpost.com/category/news/politics/',
description_en = 'National daily covering Canadian politics',
description_fr = 'Quotidien national couvrant la politique canadienne',
has_paywall = true,
credibility_tier = 'standard'
WHERE id = 'national_post';
UPDATE news_sources SET
region = 'national',
website_url = 'https://www.ctvnews.ca/politics',
description_en = 'Private broadcaster covering federal politics',
description_fr = 'Radiodiffuseur privé couvrant la politique fédérale',
has_paywall = false,
credibility_tier = 'standard'
WHERE id = 'ctv';
UPDATE news_sources SET
region = 'national',
website_url = 'https://www.ipolitics.ca/',
description_en = 'Dedicated federal politics publication',
description_fr = 'Publication dédiée à la politique fédérale',
has_paywall = true,
credibility_tier = 'premium'
WHERE id = 'ipolitics';
UPDATE news_sources SET
region = 'national',
website_url = 'https://www.parl.ca/legisinfo/',
description_en = 'Official parliamentary bill tracking service',
description_fr = 'Service officiel de suivi des projets de loi',
has_paywall = false,
credibility_tier = 'premium'
WHERE id = 'legisinfo';
-- Insert additional regional news sources (inactive until configured)
INSERT INTO news_sources (id, name, rss_url, is_active, region, website_url, description_en, description_fr, has_paywall, credibility_tier) VALUES
('toronto_star', 'Toronto Star', 'https://www.thestar.com/politics/federal-politics.rss.xml', false, 'ON', 'https://www.thestar.com/politics/federal', 'Ontario''s largest daily newspaper', 'Le plus grand quotidien de l''Ontario', true, 'standard'),
('ottawa_citizen', 'Ottawa Citizen', 'https://ottawacitizen.com/category/news/local-news/politics/feed/', false, 'ON', 'https://ottawacitizen.com/category/news/local-news/politics', 'Capital city newspaper covering Parliament Hill', 'Journal de la capitale couvrant la Colline du Parlement', true, 'standard'),
('montreal_gazette', 'Montreal Gazette', 'https://montrealgazette.com/category/news/local-news/politics/feed/', false, 'QC', 'https://montrealgazette.com/category/news/local-news/politics', 'English-language Quebec newspaper', 'Journal anglophone du Québec', true, 'standard'),
('vancouver_sun', 'Vancouver Sun', 'https://vancouversun.com/category/news/local-news/politics/feed/', false, 'BC', 'https://vancouversun.com/category/news/local-news/politics', 'British Columbia''s largest newspaper', 'Le plus grand journal de la Colombie-Britannique', true, 'standard'),
('calgary_herald', 'Calgary Herald', 'https://calgaryherald.com/category/news/politics/feed/', false, 'AB', 'https://calgaryherald.com/category/news/politics', 'Alberta daily covering provincial and federal politics', 'Quotidien albertain couvrant la politique provinciale et fédérale', true, 'standard'),
('edmonton_journal', 'Edmonton Journal', 'https://edmontonjournal.com/category/news/politics/feed/', false, 'AB', 'https://edmontonjournal.com/category/news/politics', 'Alberta capital''s leading newspaper', 'Principal journal de la capitale albertaine', true, 'standard'),
('winnipeg_free_press', 'Winnipeg Free Press', 'https://www.winnipegfreepress.com/local/rss.xml', false, 'MB', 'https://www.winnipegfreepress.com/local', 'Manitoba''s leading newspaper', 'Le principal journal du Manitoba', true, 'standard')
ON CONFLICT (id) DO UPDATE SET
region = EXCLUDED.region,
website_url = EXCLUDED.website_url,
description_en = EXCLUDED.description_en,
description_fr = EXCLUDED.description_fr,
has_paywall = EXCLUDED.has_paywall,
credibility_tier = EXCLUDED.credibility_tier;
-- Create index for region-based queries
CREATE INDEX IF NOT EXISTS idx_news_sources_region ON news_sources(region);
CREATE INDEX IF NOT EXISTS idx_news_sources_active_region ON news_sources(is_active, region);
-- Comments
COMMENT ON COLUMN news_sources.region IS 'Province code (ON, QC, BC, etc.) or "national" for national sources';
COMMENT ON COLUMN news_sources.has_paywall IS 'Whether the source requires a subscription to read full articles';
COMMENT ON COLUMN news_sources.credibility_tier IS 'Source tier: premium (established media), standard (recognized outlets), aggregator (news aggregators)';
