-- SportsClaw Blog Posts & SEO Agent Jobs
-- Migration 001

CREATE TABLE IF NOT EXISTS sc_blog_posts (
  id BIGSERIAL PRIMARY KEY,
  slug VARCHAR(500) UNIQUE NOT NULL,
  title VARCHAR(500) NOT NULL,
  meta_description VARCHAR(320) NOT NULL,
  h1 VARCHAR(500),
  content TEXT NOT NULL,
  excerpt TEXT,
  quick_facts JSONB,
  faq_schema JSONB,
  data_tables JSONB,
  sport VARCHAR(50),
  content_type VARCHAR(50) NOT NULL,
  league VARCHAR(50),
  game_date DATE,
  featured_image VARCHAR(500),
  og_image VARCHAR(500),
  schema_markup JSONB,
  status VARCHAR(20) NOT NULL DEFAULT 'draft',
  published_at TIMESTAMPTZ,
  page_views INTEGER NOT NULL DEFAULT 0,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS sc_blog_posts_slug_idx ON sc_blog_posts(slug);
CREATE INDEX IF NOT EXISTS sc_blog_posts_status_idx ON sc_blog_posts(status);
CREATE INDEX IF NOT EXISTS sc_blog_posts_sport_idx ON sc_blog_posts(sport);
CREATE INDEX IF NOT EXISTS sc_blog_posts_content_type_idx ON sc_blog_posts(content_type);
CREATE INDEX IF NOT EXISTS sc_blog_posts_published_at_idx ON sc_blog_posts(published_at);
CREATE INDEX IF NOT EXISTS sc_blog_posts_game_date_idx ON sc_blog_posts(game_date);

CREATE TABLE IF NOT EXISTS sc_seo_agent_jobs (
  id BIGSERIAL PRIMARY KEY,
  agent_type VARCHAR(100) NOT NULL,
  job_type VARCHAR(100) NOT NULL,
  config JSONB,
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  result JSONB,
  error TEXT,
  started_at TIMESTAMPTZ,
  completed_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
