CREATE TABLE IF NOT EXISTS rp_pages (
  id VARCHAR(64) PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  slug VARCHAR(255) NOT NULL UNIQUE,
  status VARCHAR(32) NOT NULL,
  seo_json LONGTEXT NOT NULL,
  blocks_json LONGTEXT NOT NULL,
  theme_id VARCHAR(128) NOT NULL,
  created_at VARCHAR(40) NOT NULL,
  updated_at VARCHAR(40) NOT NULL,
  published_at VARCHAR(40)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rp_media_assets (
  id VARCHAR(64) PRIMARY KEY,
  filename VARCHAR(255) NOT NULL,
  mime_type VARCHAR(128) NOT NULL,
  url TEXT NOT NULL,
  alt TEXT,
  size_bytes INTEGER NOT NULL,
  width INTEGER,
  height INTEGER,
  created_at VARCHAR(40) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rp_settings (
  setting_key VARCHAR(191) PRIMARY KEY,
  setting_value LONGTEXT NOT NULL,
  updated_at VARCHAR(40) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rp_users (
  id VARCHAR(64) PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role_id VARCHAR(64) NOT NULL,
  created_at VARCHAR(40) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rp_revisions (
  id VARCHAR(64) PRIMARY KEY,
  entity_type VARCHAR(64) NOT NULL,
  entity_id VARCHAR(64) NOT NULL,
  snapshot_json LONGTEXT NOT NULL,
  created_by VARCHAR(64) NOT NULL,
  created_at VARCHAR(40) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rp_redirects (
  id VARCHAR(64) PRIMARY KEY,
  from_path VARCHAR(255) NOT NULL UNIQUE,
  to_path VARCHAR(255) NOT NULL,
  status_code INTEGER NOT NULL,
  created_at VARCHAR(40) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rp_form_submissions (
  id VARCHAR(64) PRIMARY KEY,
  form_id VARCHAR(128) NOT NULL,
  page_id VARCHAR(64),
  payload_json LONGTEXT NOT NULL,
  created_at VARCHAR(40) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rp_plugins (
  id VARCHAR(128) PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  version VARCHAR(64) NOT NULL,
  enabled TINYINT(1) NOT NULL DEFAULT 0,
  manifest_json LONGTEXT NOT NULL,
  installed_at VARCHAR(40) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rp_themes (
  id VARCHAR(128) PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  version VARCHAR(64) NOT NULL,
  active TINYINT(1) NOT NULL DEFAULT 0,
  manifest_json LONGTEXT NOT NULL,
  installed_at VARCHAR(40) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rp_seo_audits (
  id VARCHAR(128) PRIMARY KEY,
  page_id VARCHAR(64) NOT NULL,
  scores_json LONGTEXT NOT NULL,
  issues_json LONGTEXT NOT NULL,
  analyzed_at VARCHAR(40) NOT NULL,
  INDEX rp_seo_audits_page_id (page_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rp_seo_fix_proposals (
  id VARCHAR(128) PRIMARY KEY,
  page_id VARCHAR(64) NOT NULL,
  issue_code VARCHAR(128) NOT NULL,
  proposal_json LONGTEXT NOT NULL,
  status VARCHAR(32) NOT NULL,
  created_at VARCHAR(40) NOT NULL,
  applied_at VARCHAR(40),
  INDEX rp_seo_fixes_page_id (page_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rp_seo_schema_templates (
  id VARCHAR(128) PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  schema_type VARCHAR(64) NOT NULL,
  template_json LONGTEXT NOT NULL,
  created_at VARCHAR(40) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rp_seo_link_reports (
  page_id VARCHAR(64) PRIMARY KEY,
  links_json LONGTEXT NOT NULL,
  checked_at VARCHAR(40) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rp_ai_connections (
  id VARCHAR(128) PRIMARY KEY,
  provider_id VARCHAR(128) NOT NULL,
  name VARCHAR(255) NOT NULL,
  auth_mode VARCHAR(32) NOT NULL,
  scope_type VARCHAR(32) NOT NULL,
  scope_id VARCHAR(128) NOT NULL,
  policy_json LONGTEXT NOT NULL,
  endpoint TEXT,
  encrypted_credentials LONGTEXT,
  status VARCHAR(32) NOT NULL,
  last_checked_at VARCHAR(40),
  created_by VARCHAR(64) NOT NULL,
  created_at VARCHAR(40) NOT NULL,
  updated_at VARCHAR(40) NOT NULL,
  INDEX rp_ai_connections_provider (provider_id),
  INDEX rp_ai_connections_scope (scope_type, scope_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rp_ai_site_profiles (
  site_id VARCHAR(128) PRIMARY KEY,
  profile_json LONGTEXT NOT NULL,
  updated_at VARCHAR(40) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rp_ai_conversations (
  id VARCHAR(128) PRIMARY KEY,
  site_id VARCHAR(128) NOT NULL,
  user_id VARCHAR(64) NOT NULL,
  title VARCHAR(255) NOT NULL,
  created_at VARCHAR(40) NOT NULL,
  updated_at VARCHAR(40) NOT NULL,
  INDEX rp_ai_conversations_site (site_id, updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rp_ai_messages (
  id VARCHAR(128) PRIMARY KEY,
  conversation_id VARCHAR(128) NOT NULL,
  role VARCHAR(32) NOT NULL,
  content_json LONGTEXT NOT NULL,
  created_at VARCHAR(40) NOT NULL,
  INDEX rp_ai_messages_conversation (conversation_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rp_ai_jobs (
  id VARCHAR(128) PRIMARY KEY,
  site_id VARCHAR(128) NOT NULL,
  request_json LONGTEXT NOT NULL,
  status VARCHAR(32) NOT NULL,
  provider_id VARCHAR(128) NOT NULL,
  model_id VARCHAR(255) NOT NULL,
  progress INTEGER NOT NULL DEFAULT 0,
  message TEXT NOT NULL,
  proposal_id VARCHAR(128),
  error TEXT,
  created_at VARCHAR(40) NOT NULL,
  updated_at VARCHAR(40) NOT NULL,
  expires_at VARCHAR(40) NOT NULL,
  INDEX rp_ai_jobs_site (site_id, updated_at),
  INDEX rp_ai_jobs_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rp_ai_proposals (
  id VARCHAR(128) PRIMARY KEY,
  run_id VARCHAR(128) NOT NULL,
  site_id VARCHAR(128) NOT NULL,
  page_id VARCHAR(64),
  base_revision_id VARCHAR(255) NOT NULL,
  proposal_json LONGTEXT NOT NULL,
  status VARCHAR(32) NOT NULL,
  created_by VARCHAR(64) NOT NULL,
  created_at VARCHAR(40) NOT NULL,
  applied_at VARCHAR(40),
  applied_by VARCHAR(64),
  INDEX rp_ai_proposals_site (site_id, created_at),
  INDEX rp_ai_proposals_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rp_ai_usage_events (
  id VARCHAR(128) PRIMARY KEY,
  site_id VARCHAR(128) NOT NULL,
  user_id VARCHAR(64) NOT NULL,
  connection_id VARCHAR(128) NOT NULL,
  provider_id VARCHAR(128) NOT NULL,
  model_id VARCHAR(255) NOT NULL,
  task_type VARCHAR(64) NOT NULL,
  input_tokens INTEGER NOT NULL DEFAULT 0,
  output_tokens INTEGER NOT NULL DEFAULT 0,
  images INTEGER NOT NULL DEFAULT 0,
  provider_cost_micros BIGINT NOT NULL DEFAULT 0,
  billable_cost_micros BIGINT NOT NULL DEFAULT 0,
  created_at VARCHAR(40) NOT NULL,
  INDEX rp_ai_usage_site (site_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rp_ai_budgets (
  id VARCHAR(128) PRIMARY KEY,
  scope_type VARCHAR(32) NOT NULL,
  scope_id VARCHAR(128) NOT NULL,
  period VARCHAR(32) NOT NULL,
  limit_micros BIGINT NOT NULL,
  spent_micros BIGINT NOT NULL DEFAULT 0,
  reserved_micros BIGINT NOT NULL DEFAULT 0,
  hard_limit TINYINT(1) NOT NULL DEFAULT 1,
  resets_at VARCHAR(40) NOT NULL,
  UNIQUE KEY rp_ai_budgets_scope (scope_type, scope_id, period)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rp_ai_asset_provenance (
  media_id VARCHAR(64) PRIMARY KEY,
  provider_id VARCHAR(128) NOT NULL,
  model_id VARCHAR(255) NOT NULL,
  prompt TEXT NOT NULL,
  run_id VARCHAR(128) NOT NULL,
  created_by VARCHAR(64) NOT NULL,
  created_at VARCHAR(40) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rp_ai_credit_ledger (
  id VARCHAR(128) PRIMARY KEY,
  workspace_id VARCHAR(128) NOT NULL,
  amount_micros BIGINT NOT NULL,
  balance_micros BIGINT NOT NULL,
  reason VARCHAR(32) NOT NULL,
  external_id VARCHAR(255),
  created_at VARCHAR(40) NOT NULL,
  UNIQUE KEY rp_ai_credit_external (external_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;