-- SEO indexing infrastructure tables

-- Key-value store for tracking script state (last run times, etc.)
CREATE TABLE IF NOT EXISTS kv_store (
  key TEXT PRIMARY KEY,
  value TEXT NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Log all SEO URL submissions (IndexNow, Google Indexing API)
CREATE TABLE IF NOT EXISTS seo_submissions (
  id SERIAL PRIMARY KEY,
  url_count INTEGER NOT NULL DEFAULT 0,
  service TEXT NOT NULL,        -- 'indexnow' or 'google_indexing'
  status TEXT,                  -- 'accepted', 'error'
  response_code INTEGER,
  submitted_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_seo_submissions_date ON seo_submissions(submitted_at);
CREATE INDEX IF NOT EXISTS idx_seo_submissions_service ON seo_submissions(service, submitted_at);
