-- migrations/002_add_extended_fields.sql
-- Extended fields for SEO, content structure, and Japanese language support

-- SEO-critical fields
ALTER TABLE articles ADD COLUMN IF NOT EXISTS seo_title VARCHAR(100);
ALTER TABLE articles ADD COLUMN IF NOT EXISTS snippet_intro TEXT;
ALTER TABLE articles ADD COLUMN IF NOT EXISTS reading_time INTEGER;
ALTER TABLE articles ADD COLUMN IF NOT EXISTS word_count INTEGER;
ALTER TABLE articles ADD COLUMN IF NOT EXISTS schema_json JSONB;
ALTER TABLE articles ADD COLUMN IF NOT EXISTS schema_faq JSONB;

-- Content structure fields
ALTER TABLE articles ADD COLUMN IF NOT EXISTS table_of_contents JSONB;
ALTER TABLE articles ADD COLUMN IF NOT EXISTS faq_items JSONB;
ALTER TABLE articles ADD COLUMN IF NOT EXISTS key_takeaways TEXT;
ALTER TABLE articles ADD COLUMN IF NOT EXISTS aio_box JSONB;
ALTER TABLE articles ADD COLUMN IF NOT EXISTS hero_image_data JSONB;
ALTER TABLE articles ADD COLUMN IF NOT EXISTS author_data JSONB;

-- Media fields
ALTER TABLE articles ADD COLUMN IF NOT EXISTS images JSONB;
ALTER TABLE articles ADD COLUMN IF NOT EXISTS videos JSONB;
ALTER TABLE articles ADD COLUMN IF NOT EXISTS content_hash VARCHAR(64);
ALTER TABLE articles ADD COLUMN IF NOT EXISTS processing_time INTEGER;

-- Japanese-specific fields
ALTER TABLE articles ADD COLUMN IF NOT EXISTS language VARCHAR(10) DEFAULT 'ja';
ALTER TABLE articles ADD COLUMN IF NOT EXISTS keywords TEXT;

-- Additional indexes for performance
CREATE INDEX IF NOT EXISTS idx_articles_reading_time ON articles(reading_time);
CREATE INDEX IF NOT EXISTS idx_articles_content_hash ON articles(content_hash);
CREATE INDEX IF NOT EXISTS idx_articles_language ON articles(language);
CREATE INDEX IF NOT EXISTS idx_articles_word_count ON articles(word_count);

-- Comments for Japanese-specific fields
COMMENT ON COLUMN articles.language IS 'Content language code (default: ja for Japanese)';
COMMENT ON COLUMN articles.keywords IS 'Japanese SEO keywords (comma-separated)';
COMMENT ON COLUMN articles.seo_title IS 'Optimized Japanese title for SEO (30-35 characters recommended)';
COMMENT ON COLUMN articles.meta_description IS 'Japanese meta description (80-120 characters recommended)';
