-- Migration: Create categories table for megido72-portal.com
-- Description: Store category information with Japanese and English names

CREATE TABLE IF NOT EXISTS categories (
  id SERIAL PRIMARY KEY,
  external_id VARCHAR(255) UNIQUE,
  japanese VARCHAR(255) NOT NULL,
  english VARCHAR(255) NOT NULL,
  slug VARCHAR(255) NOT NULL UNIQUE,
  description TEXT,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Create indexes for faster lookups
CREATE INDEX idx_categories_slug ON categories(slug);
CREATE INDEX idx_categories_japanese ON categories(japanese);

-- Insert megido72-portal.com game strategy categories
INSERT INTO categories (japanese, english, slug, description) VALUES
('ストーリー攻略', 'Story Guides', 'ストーリー攻略', 'メインストーリーの攻略情報'),
('ボス攻略', 'Boss Guides', 'ボス攻略', 'ボス戦の攻略情報'),
('高難易度攻略', 'High Difficulty Guides', '高難易度攻略', '高難易度クエストの攻略情報'),
('編成・スキル解説', 'Team Building & Skills', '編成・スキル解説', 'チーム編成とスキルの解説'),
('初心者ガイド', 'Beginner Guides', '初心者ガイド', '初心者向けのガイド情報')
ON CONFLICT (slug) DO NOTHING;

-- Comment for documentation
COMMENT ON TABLE categories IS 'Categories table for megido72-portal.com - game strategy guide categories';
