-- Migration 015: Add full_content fields to zopk_news for knowledge base extraction
-- Date: 2026-01-16
-- Purpose: Store scraped article content for AI knowledge extraction

-- ============================================================
-- ADD NEW COLUMNS TO zopk_news
-- ============================================================

-- Full article content (scraped from source URL)
ALTER TABLE zopk_news ADD COLUMN IF NOT EXISTS full_content TEXT;

-- Content scraping metadata
ALTER TABLE zopk_news ADD COLUMN IF NOT EXISTS content_scraped_at TIMESTAMP;
ALTER TABLE zopk_news ADD COLUMN IF NOT EXISTS scrape_status VARCHAR(20) DEFAULT 'pending';
-- Status values: pending, scraped, failed, skipped

-- Scraping error tracking
ALTER TABLE zopk_news ADD COLUMN IF NOT EXISTS scrape_error TEXT;
ALTER TABLE zopk_news ADD COLUMN IF NOT EXISTS scrape_attempts INTEGER DEFAULT 0;

-- Content metadata (extracted during scraping)
ALTER TABLE zopk_news ADD COLUMN IF NOT EXISTS content_word_count INTEGER;
ALTER TABLE zopk_news ADD COLUMN IF NOT EXISTS content_language VARCHAR(10) DEFAULT 'pl';

-- Knowledge extraction status
ALTER TABLE zopk_news ADD COLUMN IF NOT EXISTS knowledge_extracted BOOLEAN DEFAULT FALSE;
ALTER TABLE zopk_news ADD COLUMN IF NOT EXISTS knowledge_extracted_at TIMESTAMP;

-- ============================================================
-- INDEXES FOR EFFICIENT QUERYING
-- ============================================================

-- Index for finding articles to scrape
CREATE INDEX IF NOT EXISTS idx_zopk_news_scrape_status ON zopk_news(scrape_status);

-- Index for finding articles ready for knowledge extraction
CREATE INDEX IF NOT EXISTS idx_zopk_news_knowledge_extracted ON zopk_news(knowledge_extracted);

-- Composite index for scraping pipeline
CREATE INDEX IF NOT EXISTS idx_zopk_news_scrape_pipeline
ON zopk_news(status, scrape_status, knowledge_extracted);

-- ============================================================
-- COMMENTS
-- ============================================================

COMMENT ON COLUMN zopk_news.full_content IS 'Full article text scraped from source URL (without HTML, ads, navigation)';
COMMENT ON COLUMN zopk_news.scrape_status IS 'pending=not scraped, scraped=success, failed=error, skipped=not scrapeable';
COMMENT ON COLUMN zopk_news.scrape_error IS 'Error message if scraping failed';
COMMENT ON COLUMN zopk_news.scrape_attempts IS 'Number of scraping attempts (for retry logic)';
COMMENT ON COLUMN zopk_news.content_word_count IS 'Word count of scraped content';
COMMENT ON COLUMN zopk_news.knowledge_extracted IS 'True if chunks/facts/entities extracted';

-- ============================================================
-- GRANT PERMISSIONS
-- ============================================================

GRANT ALL ON TABLE zopk_news TO nordabiz_app;
