CREATE TABLE IF NOT EXISTS `YOUR_PROJECT_ID.YOUR_DATASET.tools` (
  tool_id STRING NOT NULL,
  slug STRING NOT NULL,
  name STRING NOT NULL,
  summary STRING NOT NULL,
  category STRING NOT NULL,
  repo_url STRING NOT NULL,
  -- Lowercase owner/repo, used to reject duplicate submissions (not enforced by BigQuery).
  repo_key STRING,
  website_url STRING,
  submitted_by STRING NOT NULL,
  -- pending | approved | rejected. New submissions are pending until a moderator approves them.
  status STRING,
  created_at TIMESTAMP NOT NULL
)
PARTITION BY DATE(created_at)
CLUSTER BY category, slug;

CREATE TABLE IF NOT EXISTS `YOUR_PROJECT_ID.YOUR_DATASET.tool_ratings` (
  tool_id STRING NOT NULL,
  voter_id STRING NOT NULL,
  rating INT64 NOT NULL,
  created_at TIMESTAMP NOT NULL,
  updated_at TIMESTAMP NOT NULL
)
PARTITION BY DATE(created_at)
CLUSTER BY tool_id, voter_id;

-- ---------------------------------------------------------------------------
-- Migration for an existing deployment (run once, BEFORE deploying this version).
-- BigQuery cannot add NOT NULL columns, so both are nullable; the app treats a
-- NULL status as 'approved' until the backfill has run.
-- ---------------------------------------------------------------------------
-- ALTER TABLE `YOUR_PROJECT_ID.YOUR_DATASET.tools` ADD COLUMN IF NOT EXISTS repo_key STRING;
-- ALTER TABLE `YOUR_PROJECT_ID.YOUR_DATASET.tools` ADD COLUMN IF NOT EXISTS status STRING;
-- UPDATE `YOUR_PROJECT_ID.YOUR_DATASET.tools`
-- SET
--   status = COALESCE(status, 'approved'),
--   repo_key = LOWER(REGEXP_EXTRACT(repo_url, r'^https://github\.com/([^/]+/[^/]+?)(?:\.git)?/?$'))
-- WHERE TRUE;