-- migrations/001_create_articles_table.sql
-- Core articles table for ballers.jp Japanese sports management media

CREATE TABLE IF NOT EXISTS articles (
  id SERIAL PRIMARY KEY,
  external_id VARCHAR(255) UNIQUE,
  title VARCHAR(255) NOT NULL,
  slug VARCHAR(255) NOT NULL UNIQUE,
  content TEXT NOT NULL,
  excerpt TEXT,
  category VARCHAR(100),
  card_image VARCHAR(500),
  hero_image VARCHAR(500),
  author VARCHAR(255),
  author_profile_url VARCHAR(500),
  author_bio TEXT,
  publication_date TIMESTAMP,
  meta_title VARCHAR(255),
  meta_description TEXT,
  views_count INTEGER NOT NULL DEFAULT 0,
  likes_count INTEGER NOT NULL DEFAULT 0,
  featured BOOLEAN NOT NULL DEFAULT false,
  is_active BOOLEAN NOT NULL DEFAULT true,
  created_at TIMESTAMP DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMP DEFAULT NOW() NOT NULL
);

-- Essential 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;

-- Comment for documentation
COMMENT ON TABLE articles IS 'Main articles table for ballers.jp - stores all blog posts from Bubble.io';
COMMENT ON COLUMN articles.slug IS 'URL slug - supports Japanese characters';
COMMENT ON COLUMN articles.category IS 'One of: スポーツクラブ運営, 社会人, ジュニア, 女子チーム, モチベーション管理';
