-- Migrate data from old news table to new articles table
INSERT INTO articles (title, url, summary, published_at, added_at, source_type, processing_status)
SELECT title,
       url,
       summary,
       published,
       datetime(created_at, 'unixepoch'),
       'rss',
       CASE
           WHEN summary IS NOT NULL AND summary != '' THEN 'completed'
           ELSE 'pending'
           END
FROM news;

-- Create geographic tags from existing country data
INSERT OR IGNORE INTO tags (name, category, description, usage_count)
SELECT DISTINCT country,
                'geographic',
                'Geographic location: ' || country,
                COUNT(*)
FROM news
WHERE country IS NOT NULL
  AND country != ''
GROUP BY country;

-- Link articles to their geographic tags
INSERT OR IGNORE INTO article_tags (article_id, tag_id, ai_generated, confidence_score)
SELECT a.id,
       t.id,
       0,  -- Not AI generated, migrated from legacy data
       1.0 -- Full confidence for existing data
FROM articles a
         JOIN news n ON a.url = n.url AND a.title = n.title
         JOIN tags t ON t.name = n.country AND t.category = 'geographic'
WHERE n.country IS NOT NULL
  AND n.country != '';

-- Create category tags if category column exists in news table
INSERT OR IGNORE INTO tags (name, category, description, usage_count)
SELECT DISTINCT n.category,
                'content',
                'Content category: ' || n.category,
                COUNT(*)
FROM news n
WHERE n.category IS NOT NULL
  AND n.category != ''
GROUP BY n.category;

-- Link articles to their category tags
INSERT OR IGNORE INTO article_tags (article_id, tag_id, ai_generated, confidence_score)
SELECT a.id,
       t.id,
       0,  -- Not AI generated, migrated from legacy data
       1.0 -- Full confidence for existing data
FROM articles a
         JOIN news n ON a.url = n.url AND a.title = n.title
         JOIN tags t ON t.name = n.category AND t.category = 'content'
WHERE n.category IS NOT NULL
  AND n.category != '';

-- Record migration in legacy_migration table for countries
INSERT INTO legacy_migration (old_filter_type, old_value, new_tag_ids)
SELECT 'country',
       n.country,
       '[' || GROUP_CONCAT(t.id) || ']'
FROM (SELECT DISTINCT country FROM news WHERE country IS NOT NULL AND country != '') n
         JOIN tags t ON t.name = n.country AND t.category = 'geographic'
GROUP BY n.country;

-- Record migration in legacy_migration table for categories (if they exist)
INSERT INTO legacy_migration (old_filter_type, old_value, new_tag_ids)
SELECT 'category',
       n.category,
       '[' || GROUP_CONCAT(t.id) || ']'
FROM (SELECT DISTINCT category FROM news WHERE category IS NOT NULL AND category != '') n
         JOIN tags t ON t.name = n.category AND t.category = 'content'
GROUP BY n.category;

-- Update tag usage counts
UPDATE tags
SET usage_count = (SELECT COUNT(*)
                   FROM article_tags
                   WHERE tag_id = tags.id);