-- Migration: Create articles table for megido72-portal.com
-- Description: Main content table for game strategy guides (41 fields, 10 indexes)

CREATE TABLE IF NOT EXISTS articles (
  -- Core fields
  id SERIAL PRIMARY KEY,
  external_id VARCHAR(255) UNIQUE,
  title TEXT NOT NULL,
  slug VARCHAR(500) NOT NULL UNIQUE,
  content TEXT,
  excerpt TEXT,
  category VARCHAR(255),
  
  -- Images
  card_image TEXT,
  hero_image TEXT,
  
  -- Author
  author VARCHAR(255),
  author_profile_url TEXT,
  author_bio TEXT,
  
  -- Dates
  publication_date TIMESTAMP,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW(),
  
  -- SEO
  meta_title VARCHAR(255),
  meta_description TEXT,
  seo_title VARCHAR(255),
  snippet_intro TEXT,
  
  -- Metrics
  reading_time INTEGER,
  word_count INTEGER,
  views_count INTEGER DEFAULT 0,
  likes_count INTEGER DEFAULT 0,
  
  -- Flags
  featured BOOLEAN DEFAULT false,
  is_active BOOLEAN DEFAULT true,
  
  -- Structured data (JSONB)
  schema_json JSONB,
  schema_faq JSONB,
  table_of_contents JSONB,
  faq_items JSONB,
  key_takeaways JSONB,
  aio_box JSONB,
  hero_image_data JSONB,
  author_data JSONB,
  
  -- Media
  images JSONB,
  videos JSONB,
  
  -- Metadata
  content_hash VARCHAR(64),
  processing_time INTEGER,
  
  -- Language
  language VARCHAR(10) DEFAULT 'ja',
  keywords TEXT
);

-- Create indexes for performance
CREATE INDEX idx_articles_slug ON articles(slug);
CREATE INDEX idx_articles_external_id ON articles(external_id);
CREATE INDEX idx_articles_category ON articles(category);
CREATE INDEX idx_articles_publication_date ON articles(publication_date DESC);
CREATE INDEX idx_articles_featured ON articles(featured) WHERE featured = true;
CREATE INDEX idx_articles_is_active ON articles(is_active) WHERE is_active = true;
CREATE INDEX idx_articles_reading_time ON articles(reading_time);
CREATE INDEX idx_articles_content_hash ON articles(content_hash);
CREATE INDEX idx_articles_language ON articles(language);
CREATE INDEX idx_articles_word_count ON articles(word_count);

-- Comments for documentation
COMMENT ON TABLE articles IS 'Main articles table for megido72-portal.com - game strategy guides';
COMMENT ON COLUMN articles.category IS 'One of: ストーリー攻略, ボス攻略, 高難易度攻略, 編成・スキル解説, 初心者ガイド';
COMMENT ON COLUMN articles.slug IS 'URL slug - supports Japanese characters';
