-- Slowburn Supabase owner-run-all bundle v1
-- Optional one-paste convenience bundle for Tanveer-owned Supabase SQL Editor.
-- Source files remain authoritative: supabase/schema.sql, supabase/rls-policies.sql, supabase/verification-queries.sql.
-- Run only in the Slowburn Supabase project you own. Do not paste private keys, database passwords, payment secrets, or user exports.
-- This bundle creates schema, enables RLS policies, then runs read-only verification queries. It does not enable app auth, writes, uploads, billing, push, sync, or local data clear.

-- ============================================================
-- SECTION 1: schema from supabase/schema.sql
-- ============================================================
-- Slowburn Supabase schema v1
-- Purpose: first real backend foundation for profiles, quit plans, logs,
-- cost truth, body load, notifications, and subscriptions.

create extension if not exists pgcrypto;

create or replace function public.set_updated_at()
returns trigger
language plpgsql
set search_path = public, pg_temp
as $$
begin
  new.updated_at = now();
  return new;
end;
$$;

create table if not exists public.profiles (
  user_id uuid primary key references auth.users(id) on delete cascade,
  local_profile_id text,
  display_name text,
  country_code text not null default 'usa',
  region_name text,
  timezone text not null default 'America/New_York',
  currency text not null default 'USD',
  distance_unit text not null default 'mi',
  onboarding_completed_at timestamptz,
  last_synced_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint profiles_country_check check (country_code ~ '^[a-z][a-z0-9_]{1,31}$'),
  constraint profiles_distance_unit_check check (distance_unit in ('mi', 'km'))
);

create table if not exists public.migration_batches (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  local_profile_id text not null,
  dry_run_id text not null,
  dry_run_checksum text not null,
  status text not null default 'planned',
  row_counts jsonb not null default '{}'::jsonb,
  readback_gate_id text,
  export_snapshot_checksum text,
  started_at timestamptz,
  completed_at timestamptz,
  failed_at timestamptz,
  error_message text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (user_id, dry_run_id),
  constraint migration_batches_status_check check (status in ('planned', 'running', 'completed', 'failed', 'rolled_back'))
);

create table if not exists public.country_defaults (
  country_code text primary key,
  country_name text not null,
  currency text not null,
  locale text not null,
  primary_distance_unit text not null,
  alternate_distance_unit text not null,
  default_pack_size int not null default 20,
  default_pack_price numeric(10, 2) not null,
  region_label text not null,
  verified_launch_market boolean not null default true,
  global_manual_fallback boolean not null default false,
  active boolean not null default true,
  updated_at timestamptz not null default now(),
  constraint country_defaults_units_check check (primary_distance_unit in ('mi', 'km') and alternate_distance_unit in ('mi', 'km'))
);

create table if not exists public.region_price_defaults (
  id uuid primary key default gen_random_uuid(),
  country_code text not null references public.country_defaults(country_code),
  region_code text,
  region_name text not null,
  year int not null,
  pack_price numeric(10, 2) not null,
  pack_size int not null default 20,
  source_name text not null,
  source_url text,
  confidence_level text not null default 'source_review_needed',
  updated_at timestamptz not null default now(),
  constraint region_price_year_check check (year between 1950 and 2100),
  constraint region_price_confidence_check check (confidence_level in ('source_review_needed', 'official', 'survey', 'market_average', 'manual_admin'))
);

create table if not exists public.nicotine_plans (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  migration_batch_id uuid references public.migration_batches(id) on delete set null,
  baseline_cigarettes_per_day numeric(6, 2) not null,
  years_smoked numeric(5, 2) not null,
  pack_size int not null default 20,
  pack_price numeric(10, 2) not null,
  product_type text not null default 'regular',
  taper_mode text not null default 'standard',
  quit_mode text not null default 'taper',
  wake_time time not null default '07:00',
  sleep_time time not null default '23:00',
  active boolean not null default true,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint nicotine_plans_product_check check (product_type in ('regular', 'long')),
  constraint nicotine_plans_taper_check check (taper_mode in ('gentle', 'standard', 'aggressive')),
  constraint nicotine_plans_quit_mode_check check (quit_mode in ('taper', 'quit_today', 'maintenance'))
);

create table if not exists public.daily_token_plans (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  migration_batch_id uuid references public.migration_batches(id) on delete set null,
  plan_date date not null,
  target_tokens int not null,
  tomorrow_target_tokens int not null,
  target_gap_minutes int not null,
  over_plan_count int not null default 0,
  recovery_days int not null default 0,
  status text not null default 'active',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (user_id, plan_date),
  constraint daily_token_status_check check (status in ('active', 'completed', 'adjusted', 'paused'))
);

create table if not exists public.token_events (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  migration_batch_id uuid references public.migration_batches(id) on delete set null,
  client_event_key text,
  plan_date date not null,
  token_number int,
  event_type text not null,
  quantity int not null default 1,
  scheduled_at timestamptz,
  actual_smoked_at timestamptz,
  logged_at timestamptz not null default now(),
  event_source text not null default 'manual',
  notes text,
  created_at timestamptz not null default now(),
  constraint token_events_type_check check (event_type in ('smoked', 'skipped', 'over_plan_smoked', 'craving', 'override', 'snoozed')),
  constraint token_events_quantity_check check (quantity >= 0 and quantity <= 120),
  constraint token_events_source_check check (event_source in ('manual', 'full_day_total', 'off_plan_catchup', 'notification_response', 'migration'))
);

create table if not exists public.habit_logs (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  migration_batch_id uuid references public.migration_batches(id) on delete set null,
  client_event_key text,
  habit_type text not null,
  amount numeric(10, 2) not null,
  unit text not null,
  logged_at timestamptz not null default now(),
  context text,
  craving_level int,
  stress_level int,
  mood_level int,
  sleep_quality int,
  location_bucket text,
  created_at timestamptz not null default now(),
  constraint habit_logs_type_check check (habit_type in ('cigarette', 'vape', 'nicotine_pouch', 'alcohol', 'caffeine', 'other')),
  constraint habit_logs_scale_check check (
    (craving_level is null or craving_level between 0 and 10)
    and (stress_level is null or stress_level between 0 and 10)
    and (mood_level is null or mood_level between 0 and 10)
    and (sleep_quality is null or sleep_quality between 0 and 10)
  )
);

create table if not exists public.user_cost_overrides (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  migration_batch_id uuid references public.migration_batches(id) on delete set null,
  year int not null,
  country_code text not null references public.country_defaults(country_code),
  region_name text,
  cigarettes_per_day numeric(6, 2) not null,
  pack_price numeric(10, 2) not null,
  pack_size int not null default 20,
  historical_growth_low numeric(5, 4) not null default 0.0400,
  historical_growth_high numeric(5, 4) not null default 0.0600,
  confidence_status text not null default 'manual_verified',
  notes text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (user_id, year),
  constraint cost_override_year_check check (year between 1950 and 2100),
  constraint cost_override_confidence_check check (confidence_status in ('estimated', 'user_adjusted', 'partially_verified', 'manual_verified'))
);

create table if not exists public.daily_receipts (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  migration_batch_id uuid references public.migration_batches(id) on delete set null,
  receipt_date date not null,
  cigarettes_smoked int not null default 0,
  over_plan_smoked int not null default 0,
  cigarettes_skipped int not null default 0,
  cravings_logged int not null default 0,
  money_spent numeric(12, 2) not null default 0,
  money_protected numeric(12, 2) not null default 0,
  distance_added_km numeric(12, 5) not null default 0,
  load_score numeric(6, 2),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (user_id, receipt_date)
);

create table if not exists public.body_load_scores (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  migration_batch_id uuid references public.migration_batches(id) on delete set null,
  score_date date not null,
  nicotine_load numeric(6, 2) not null default 0,
  alcohol_load numeric(6, 2) not null default 0,
  caffeine_load numeric(6, 2) not null default 0,
  replacement_load numeric(6, 2) not null default 0,
  baseline_caffeine numeric(6, 2) not null default 0,
  baseline_alcohol numeric(6, 2) not null default 0,
  baseline_replacement numeric(6, 2) not null default 0,
  sleep_risk numeric(6, 2) not null default 0,
  money_load numeric(6, 2) not null default 0,
  total_score numeric(6, 2) not null default 0,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (user_id, score_date)
);

create table if not exists public.trigger_edges (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  source_type text not null,
  source_value text not null,
  target_type text not null,
  target_value text not null,
  strength numeric(5, 2) not null default 0,
  detection_count int not null default 1,
  last_detected_at timestamptz not null default now(),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint trigger_strength_check check (strength between 0 and 100)
);

create table if not exists public.trigger_locations (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  label text not null,
  location_bucket text not null,
  precision_mode text not null default 'bucket_only',
  geofence_radius_meters int,
  active boolean not null default true,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint trigger_locations_precision_check check (precision_mode in ('bucket_only', 'gps_opt_in')),
  constraint trigger_locations_radius_check check (geofence_radius_meters is null or geofence_radius_meters between 50 and 1000)
);

create table if not exists public.trigger_location_events (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  trigger_location_id uuid references public.trigger_locations(id) on delete set null,
  event_source text not null default 'manual_preview',
  entered_at timestamptz not null default now(),
  risk_score numeric(5, 2) not null default 0,
  intervention_sent boolean not null default false,
  created_at timestamptz not null default now(),
  constraint trigger_location_events_source_check check (event_source in ('manual_preview', 'gps_opt_in', 'notification_response')),
  constraint trigger_location_events_risk_check check (risk_score between 0 and 100)
);

create table if not exists public.trigger_interventions (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  trigger_edge_id uuid references public.trigger_edges(id) on delete set null,
  trigger_location_id uuid references public.trigger_locations(id) on delete set null,
  intervention_type text not null,
  delivery_channel text not null default 'in_app',
  status text not null default 'draft',
  scheduled_for timestamptz,
  payload jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint trigger_interventions_type_check check (intervention_type in ('delay', 'swap', 'guardrail', 'rescue', 'hold_gap')),
  constraint trigger_interventions_delivery_check check (delivery_channel in ('in_app', 'push', 'email')),
  constraint trigger_interventions_status_check check (status in ('draft', 'scheduled', 'sent', 'dismissed', 'blocked'))
);

create table if not exists public.substitution_alerts (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  migration_batch_id uuid references public.migration_batches(id) on delete set null,
  client_event_key text,
  alert_date date not null,
  reduced_habit text not null,
  increased_habit text not null,
  risk_score numeric(5, 2) not null,
  severity text not null,
  message text not null,
  dismissed_at timestamptz,
  created_at timestamptz not null default now(),
  constraint substitution_severity_check check (severity in ('low', 'medium', 'high'))
);

create table if not exists public.cross_habit_watch_reports (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  migration_batch_id uuid references public.migration_batches(id) on delete set null,
  report_date date not null default current_date,
  pattern_status text not null default 'baseline',
  smoking_delta_pct numeric(7, 2) not null default 0,
  caffeine_delta_pct numeric(7, 2) not null default 0,
  alcohol_delta_pct numeric(7, 2) not null default 0,
  replacement_delta_pct numeric(7, 2) not null default 0,
  risk_score numeric(5, 2) not null default 0,
  rolling_window_days int not null default 7,
  rolling_sample_count int not null default 0,
  rolling_confidence text not null default 'today_only',
  rolling_history jsonb not null default '[]'::jsonb,
  next_guardrail text,
  payload jsonb not null default '{}'::jsonb,
  status text not null default 'draft',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint cross_habit_watch_reports_status_check check (pattern_status in ('baseline', 'protected', 'watch', 'urgent')),
  constraint cross_habit_watch_reports_risk_check check (risk_score between 0 and 100),
  constraint cross_habit_watch_reports_window_check check (rolling_window_days between 1 and 30),
  constraint cross_habit_watch_reports_sample_check check (rolling_sample_count between 0 and rolling_window_days),
  constraint cross_habit_watch_reports_confidence_check check (rolling_confidence in ('today_only', 'thin', 'usable', 'strong')),
  constraint cross_habit_watch_reports_state_check check (status in ('draft', 'ready', 'dismissed', 'blocked'))
);

create table if not exists public.cross_habit_watch_events (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  cross_habit_watch_report_id uuid references public.cross_habit_watch_reports(id) on delete cascade,
  event_type text not null,
  detail text,
  created_at timestamptz not null default now(),
  constraint cross_habit_watch_events_type_check check (event_type in ('report_built', 'substitution_detected', 'detox_instruction_blocked', 'provider_call_blocked', 'public_share_blocked', 'billing_action_blocked'))
);

create table if not exists public.notification_devices (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  platform text not null,
  push_token text not null,
  permission_status text not null default 'granted',
  device_label text,
  last_seen_at timestamptz not null default now(),
  disabled_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (user_id, push_token),
  constraint notification_devices_platform_check check (platform in ('web', 'ios', 'android')),
  constraint notification_devices_permission_check check (permission_status in ('granted', 'denied', 'opted_out', 'expired'))
);

create table if not exists public.notification_jobs (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  migration_batch_id uuid references public.migration_batches(id) on delete set null,
  notification_type text not null,
  delivery_channel text not null default 'in_app',
  status text not null default 'queued',
  dedupe_key text,
  scheduled_for timestamptz not null,
  attempt_count int not null default 0,
  max_attempts int not null default 2,
  last_attempt_at timestamptz,
  sent_at timestamptz,
  response_type text,
  response_quantity int,
  responded_at timestamptz,
  cancelled_at timestamptz,
  cancellation_reason text,
  payload jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint notification_type_check check (notification_type in ('smoke_token', 'pre_craving', 'craving_rescue', 'substitution_watch', 'daily_receipt', 'relapse_recovery')),
  constraint notification_delivery_channel_check check (delivery_channel in ('push', 'in_app', 'email', 'sms')),
  constraint notification_status_check check (status in ('queued', 'sent', 'fallback', 'blocked', 'held', 'responded', 'cancelled')),
  constraint notification_response_check check (response_type is null or response_type in ('smoked', 'skipped', 'over_plan_smoked', 'craving', 'snoozed', 'dismissed')),
  constraint notification_response_quantity_check check (response_quantity is null or response_quantity between 0 and 120)
);

create table if not exists public.subscription_products (
  id uuid primary key default gen_random_uuid(),
  country_code text not null references public.country_defaults(country_code),
  product_id text not null,
  entitlement text not null,
  billing_cadence text not null,
  currency text not null,
  price_amount numeric(10, 2) not null,
  recovery_multiplier numeric(4, 2),
  buffer_ratio numeric(4, 2),
  min_buffer_months int,
  max_buffer_months int,
  continuation_entitlement text not null default 'plus',
  continuation_billing_cadence text not null default 'monthly',
  active boolean not null default true,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (country_code, product_id, billing_cadence),
  constraint subscription_products_entitlement_check check (entitlement in ('free', 'plus', 'premium', 'internal')),
  constraint subscription_products_cadence_check check (billing_cadence in ('monthly', 'one_time_recovery')),
  constraint subscription_products_continuation_check check (continuation_entitlement = 'plus' and continuation_billing_cadence = 'monthly'),
  constraint subscription_products_recovery_formula_check check (
    billing_cadence = 'monthly'
    or (
      recovery_multiplier = 0.75
      and buffer_ratio = 0.25
      and min_buffer_months = 1
      and max_buffer_months = 3
    )
  )
);

create table if not exists public.subscriptions (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  platform text not null,
  provider text not null default 'manual',
  product_id text not null,
  country_code text references public.country_defaults(country_code),
  currency text,
  price_amount numeric(10, 2),
  billing_cadence text,
  status text not null,
  eta_months int,
  buffer_months int,
  raw_calculated_price numeric(10, 2),
  clean_checkout_price numeric(10, 2),
  access_ends_at timestamptz,
  continuation_monthly_price numeric(10, 2),
  continuation_policy text not null default 'plus_monthly_or_reenroll',
  re_enrollment_choice text,
  history_visible_before_reenroll boolean not null default false,
  current_period_end timestamptz,
  canceled_at timestamptz,
  provider_customer_id text,
  provider_subscription_id text,
  entitlement text not null default 'free',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint subscriptions_platform_check check (platform in ('web', 'ios', 'android')),
  constraint subscriptions_provider_check check (provider in ('manual', 'stripe', 'apple', 'google')),
  constraint subscriptions_billing_cadence_check check (billing_cadence is null or billing_cadence in ('monthly', 'one_time_recovery')),
  constraint subscriptions_status_check check (status in ('active', 'past_due', 'canceled', 'expired')),
  constraint subscriptions_entitlement_check check (entitlement in ('free', 'plus', 'premium', 'internal')),
  constraint subscriptions_continuation_policy_check check (continuation_policy in ('plus_monthly_or_reenroll')),
  constraint subscriptions_reenrollment_choice_check check (re_enrollment_choice is null or re_enrollment_choice in ('continue_from_history', 'start_fresh'))
);

create table if not exists public.recovery_circles (
  id uuid primary key default gen_random_uuid(),
  country_code text not null references public.country_defaults(country_code),
  language_code text not null default 'en',
  timezone_group text not null,
  stage_label text not null,
  taper_mode text not null default 'standard',
  max_members int not null default 21,
  active boolean not null default true,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint recovery_circles_taper_check check (taper_mode in ('gentle', 'standard', 'aggressive')),
  constraint recovery_circles_size_check check (max_members between 4 and 24)
);

create table if not exists public.recovery_circle_members (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  circle_id uuid not null references public.recovery_circles(id) on delete cascade,
  anonymous_alias text not null,
  membership_status text not null default 'active',
  privacy_mode text not null default 'anonymous',
  joined_at timestamptz not null default now(),
  last_active_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (user_id, circle_id),
  constraint circle_membership_status_check check (membership_status in ('active', 'paused', 'left', 'blocked')),
  constraint circle_privacy_mode_check check (privacy_mode in ('anonymous'))
);

create table if not exists public.hold_gap_sessions (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  circle_id uuid references public.recovery_circles(id) on delete set null,
  session_status text not null default 'active',
  started_at timestamptz not null default now(),
  ended_at timestamptz,
  outcome text,
  support_count int not null default 0,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint hold_gap_status_check check (session_status in ('active', 'protected', 'logged_anyway', 'expired', 'cancelled')),
  constraint hold_gap_outcome_check check (outcome is null or outcome in ('gap_protected', 'logged_anyway', 'needs_followup')),
  constraint hold_gap_support_count_check check (support_count between 0 and 999)
);

create table if not exists public.recovery_twin_matches (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  twin_user_id uuid not null references auth.users(id) on delete cascade,
  match_status text not null default 'active',
  match_reason jsonb not null default '{}'::jsonb,
  matched_at timestamptz not null default now(),
  ended_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (user_id, twin_user_id),
  constraint recovery_twin_no_self_check check (user_id <> twin_user_id),
  constraint recovery_twin_status_check check (match_status in ('active', 'paused', 'ended', 'blocked'))
);

create table if not exists public.recovery_support_events (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  circle_id uuid references public.recovery_circles(id) on delete set null,
  hold_gap_session_id uuid references public.hold_gap_sessions(id) on delete set null,
  event_type text not null,
  prompt_key text,
  target_user_id uuid references auth.users(id) on delete set null,
  created_at timestamptz not null default now(),
  constraint recovery_support_event_type_check check (event_type in ('skip_circle_win', 'skip_circle_reaction', 'inspired_skip', 'presence_signal', 'prompt_sent', 'token_protected', 'hard_day_rebuilt', 'report', 'block'))
);

create table if not exists public.recovery_network_reports (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  circle_id uuid references public.recovery_circles(id) on delete set null,
  hold_gap_session_id uuid references public.hold_gap_sessions(id) on delete set null,
  reported_user_id uuid references auth.users(id) on delete set null,
  report_type text not null,
  report_status text not null default 'draft',
  detail text,
  payload jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint recovery_network_reports_type_check check (report_type in ('unsafe_content', 'harassment', 'self_harm_risk', 'smoking_glamorization', 'spam', 'other')),
  constraint recovery_network_reports_status_check check (report_status in ('draft', 'submitted', 'reviewing', 'resolved', 'dismissed'))
);

create table if not exists public.recovery_network_blocks (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  blocked_user_id uuid references auth.users(id) on delete set null,
  circle_id uuid references public.recovery_circles(id) on delete set null,
  block_status text not null default 'active',
  reason_key text,
  payload jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (user_id, blocked_user_id),
  constraint recovery_network_blocks_status_check check (block_status in ('active', 'removed'))
);

create table if not exists public.community_pulse_snapshots (
  id uuid primary key default gen_random_uuid(),
  country_code text references public.country_defaults(country_code),
  pulse_date date not null,
  active_circle_count int not null default 0,
  hold_gap_sessions int not null default 0,
  tokens_protected int not null default 0,
  hard_days_rebuilt int not null default 0,
  cigarettes_avoided int not null default 0,
  active boolean not null default true,
  created_at timestamptz not null default now(),
  unique (country_code, pulse_date),
  constraint community_pulse_counts_check check (
    active_circle_count >= 0
    and hold_gap_sessions >= 0
    and tokens_protected >= 0
    and hard_days_rebuilt >= 0
    and cigarettes_avoided >= 0
  )
);

create table if not exists public.coach_sessions (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  migration_batch_id uuid references public.migration_batches(id) on delete set null,
  session_type text not null,
  trigger_source text not null default 'manual',
  status text not null default 'draft',
  started_at timestamptz not null default now(),
  ended_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint coach_sessions_type_check check (session_type in ('daily_plan', 'craving_rescue', 'hard_day', 'substitution_watch', 'return_home')),
  constraint coach_sessions_trigger_check check (trigger_source in ('manual', 'token_event', 'craving_button', 'notification_response', 'substitution_alert')),
  constraint coach_sessions_status_check check (status in ('draft', 'active', 'completed', 'dismissed', 'escalated'))
);

create table if not exists public.coach_messages (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  coach_session_id uuid references public.coach_sessions(id) on delete cascade,
  role text not null,
  message_kind text not null,
  content text not null,
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  constraint coach_messages_role_check check (role in ('user', 'coach', 'system_summary')),
  constraint coach_messages_kind_check check (message_kind in ('structured_prompt', 'user_note', 'coach_plan', 'safety_resource', 'next_move'))
);

create table if not exists public.coach_safety_events (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  coach_session_id uuid references public.coach_sessions(id) on delete set null,
  event_type text not null,
  severity text not null default 'info',
  detail text,
  created_at timestamptz not null default now(),
  constraint coach_safety_events_type_check check (event_type in ('medical_boundary', 'support_resource_shown', 'blocked_claim', 'data_boundary', 'human_review_needed')),
  constraint coach_safety_events_severity_check check (severity in ('info', 'watch', 'urgent'))
);

create table if not exists public.care_contacts (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  display_label text not null,
  relationship_type text not null default 'other',
  contact_channel text not null default 'none',
  consent_status text not null default 'draft',
  share_level text not null default 'summary_only',
  notes text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint care_contacts_relationship_check check (relationship_type in ('partner', 'family', 'friend', 'clinician', 'other')),
  constraint care_contacts_channel_check check (contact_channel in ('none', 'email', 'sms', 'in_app')),
  constraint care_contacts_consent_check check (consent_status in ('draft', 'pending', 'accepted', 'revoked', 'blocked')),
  constraint care_contacts_share_level_check check (share_level in ('summary_only', 'checkins_only', 'custom'))
);

create table if not exists public.care_checkins (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  care_contact_id uuid references public.care_contacts(id) on delete set null,
  checkin_type text not null,
  status text not null default 'draft',
  message text not null,
  payload jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  sent_at timestamptz,
  updated_at timestamptz not null default now(),
  constraint care_checkins_type_check check (checkin_type in ('daily_summary', 'hard_day', 'token_protected', 'return_progress', 'support_request')),
  constraint care_checkins_status_check check (status in ('draft', 'queued', 'sent', 'cancelled', 'blocked'))
);

create table if not exists public.care_consent_events (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  care_contact_id uuid references public.care_contacts(id) on delete set null,
  event_type text not null,
  detail text,
  created_at timestamptz not null default now(),
  constraint care_consent_events_type_check check (event_type in ('consent_created', 'invite_previewed', 'accepted', 'revoked', 'share_level_changed', 'blocked'))
);

create table if not exists public.money_vault_goals (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  goal_name text not null default 'Future Me',
  target_amount numeric(12, 2),
  currency text not null,
  projection_rate_pct numeric(5, 2) not null default 0,
  status text not null default 'active',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint money_vault_goals_status_check check (status in ('active', 'paused', 'completed', 'archived')),
  constraint money_vault_goals_rate_check check (projection_rate_pct between 0 and 25)
);

create table if not exists public.money_vault_entries (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  money_vault_goal_id uuid references public.money_vault_goals(id) on delete set null,
  entry_type text not null,
  status text not null default 'draft',
  amount numeric(12, 2) not null,
  currency text not null,
  source_event_type text,
  payload jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint money_vault_entries_type_check check (entry_type in ('protected_amount', 'manual_transfer_preview', 'manual_transfer_confirmed', 'adjustment')),
  constraint money_vault_entries_status_check check (status in ('draft', 'confirmed', 'skipped', 'blocked')),
  constraint money_vault_entries_amount_check check (amount >= 0)
);

create table if not exists public.money_vault_safety_events (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  event_type text not null,
  detail text,
  created_at timestamptz not null default now(),
  constraint money_vault_safety_events_type_check check (event_type in ('projection_shown', 'bank_link_blocked', 'investment_claim_blocked', 'transfer_previewed', 'manual_transfer_confirmed', 'provider_call_blocked'))
);

create table if not exists public.hard_day_reviews (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  migration_batch_id uuid references public.migration_batches(id) on delete set null,
  review_date date not null,
  severity text not null default 'watch',
  over_plan_smoked int not null default 0,
  cravings_logged int not null default 0,
  trigger_summary jsonb not null default '{}'::jsonb,
  next_move text,
  status text not null default 'draft',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint hard_day_reviews_severity_check check (severity in ('none', 'watch', 'active', 'urgent')),
  constraint hard_day_reviews_status_check check (status in ('draft', 'saved', 'dismissed', 'blocked')),
  constraint hard_day_reviews_counts_check check (over_plan_smoked >= 0 and cravings_logged >= 0)
);

create table if not exists public.hard_day_review_events (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  hard_day_review_id uuid references public.hard_day_reviews(id) on delete cascade,
  event_type text not null,
  detail text,
  created_at timestamptz not null default now(),
  constraint hard_day_review_events_type_check check (event_type in ('review_built', 'next_move_chosen', 'support_resource_shown', 'reset_blocked', 'repricing_blocked', 'public_share_blocked'))
);

create table if not exists public.hard_day_recovery_actions (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  hard_day_review_id uuid references public.hard_day_reviews(id) on delete cascade,
  action_type text not null,
  status text not null default 'draft',
  scheduled_for timestamptz,
  completed_at timestamptz,
  payload jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint hard_day_recovery_actions_type_check check (action_type in ('clean_gap', 'craving_rescue', 'care_checkin_preview', 'coach_plan_preview', 'support_resource')),
  constraint hard_day_recovery_actions_status_check check (status in ('draft', 'scheduled', 'completed', 'dismissed', 'blocked'))
);

create index if not exists profiles_country_idx on public.profiles(country_code);
create unique index if not exists profiles_local_profile_id_idx on public.profiles(local_profile_id) where local_profile_id is not null;
create index if not exists migration_batches_user_status_idx on public.migration_batches(user_id, status, created_at desc);
create index if not exists nicotine_plans_user_active_idx on public.nicotine_plans(user_id, active);
create index if not exists nicotine_plans_user_migration_idx on public.nicotine_plans(user_id, migration_batch_id);
create index if not exists daily_token_plans_user_date_idx on public.daily_token_plans(user_id, plan_date desc);
create index if not exists daily_token_plans_user_migration_idx on public.daily_token_plans(user_id, migration_batch_id);
create index if not exists token_events_user_logged_idx on public.token_events(user_id, logged_at desc);
create index if not exists token_events_user_migration_idx on public.token_events(user_id, migration_batch_id);
create unique index if not exists token_events_user_client_event_key_idx on public.token_events(user_id, client_event_key) where client_event_key is not null;
create index if not exists habit_logs_user_logged_idx on public.habit_logs(user_id, logged_at desc);
create index if not exists habit_logs_user_migration_idx on public.habit_logs(user_id, migration_batch_id);
create unique index if not exists habit_logs_user_client_event_key_idx on public.habit_logs(user_id, client_event_key) where client_event_key is not null;
create index if not exists user_cost_overrides_user_year_idx on public.user_cost_overrides(user_id, year desc);
create index if not exists user_cost_overrides_user_migration_idx on public.user_cost_overrides(user_id, migration_batch_id);
create index if not exists daily_receipts_user_date_idx on public.daily_receipts(user_id, receipt_date desc);
create index if not exists daily_receipts_user_migration_idx on public.daily_receipts(user_id, migration_batch_id);
create index if not exists body_load_scores_user_date_idx on public.body_load_scores(user_id, score_date desc);
create index if not exists body_load_scores_user_migration_idx on public.body_load_scores(user_id, migration_batch_id);
create index if not exists trigger_edges_user_strength_idx on public.trigger_edges(user_id, strength desc);
create index if not exists trigger_locations_user_active_idx on public.trigger_locations(user_id, active);
create index if not exists trigger_location_events_user_entered_idx on public.trigger_location_events(user_id, entered_at desc);
create index if not exists trigger_interventions_user_status_idx on public.trigger_interventions(user_id, status, scheduled_for desc);
create index if not exists substitution_alerts_user_date_idx on public.substitution_alerts(user_id, alert_date desc);
create index if not exists substitution_alerts_user_migration_idx on public.substitution_alerts(user_id, migration_batch_id);
create unique index if not exists substitution_alerts_user_client_event_key_idx on public.substitution_alerts(user_id, client_event_key) where client_event_key is not null;
create index if not exists cross_habit_watch_reports_user_date_idx on public.cross_habit_watch_reports(user_id, report_date desc);
create index if not exists cross_habit_watch_reports_user_migration_idx on public.cross_habit_watch_reports(user_id, migration_batch_id);
create index if not exists cross_habit_watch_reports_user_status_idx on public.cross_habit_watch_reports(user_id, pattern_status, created_at desc);
create index if not exists cross_habit_watch_events_user_idx on public.cross_habit_watch_events(user_id, created_at desc);
create index if not exists cross_habit_watch_events_report_idx on public.cross_habit_watch_events(cross_habit_watch_report_id, created_at desc);
create index if not exists notification_devices_user_active_idx on public.notification_devices(user_id, disabled_at) where disabled_at is null;
create index if not exists notification_jobs_user_schedule_idx on public.notification_jobs(user_id, scheduled_for);
create index if not exists notification_jobs_user_migration_idx on public.notification_jobs(user_id, migration_batch_id);
create unique index if not exists notification_jobs_user_dedupe_key_idx on public.notification_jobs(user_id, dedupe_key) where dedupe_key is not null;
create index if not exists subscription_products_country_active_idx on public.subscription_products(country_code, active);
create index if not exists subscriptions_user_status_idx on public.subscriptions(user_id, status);
create index if not exists region_price_lookup_idx on public.region_price_defaults(country_code, region_name, year desc);
create index if not exists recovery_circles_country_stage_idx on public.recovery_circles(country_code, stage_label, active);
create index if not exists recovery_circle_members_user_idx on public.recovery_circle_members(user_id, membership_status);
create index if not exists recovery_circle_members_circle_idx on public.recovery_circle_members(circle_id, membership_status);
create index if not exists hold_gap_sessions_user_started_idx on public.hold_gap_sessions(user_id, started_at desc);
create index if not exists hold_gap_sessions_circle_status_idx on public.hold_gap_sessions(circle_id, session_status);
create index if not exists recovery_twin_matches_user_idx on public.recovery_twin_matches(user_id, match_status);
create index if not exists recovery_support_events_user_idx on public.recovery_support_events(user_id, created_at desc);
create index if not exists recovery_network_reports_user_status_idx on public.recovery_network_reports(user_id, report_status, created_at desc);
create index if not exists recovery_network_reports_reported_user_idx on public.recovery_network_reports(reported_user_id, report_status) where reported_user_id is not null;
create index if not exists recovery_network_blocks_user_status_idx on public.recovery_network_blocks(user_id, block_status, created_at desc);
create index if not exists recovery_network_blocks_blocked_user_idx on public.recovery_network_blocks(blocked_user_id) where blocked_user_id is not null;
create index if not exists community_pulse_country_date_idx on public.community_pulse_snapshots(country_code, pulse_date desc);
create index if not exists coach_sessions_user_started_idx on public.coach_sessions(user_id, started_at desc);
create index if not exists coach_sessions_user_migration_idx on public.coach_sessions(user_id, migration_batch_id);
create index if not exists coach_messages_user_session_idx on public.coach_messages(user_id, coach_session_id, created_at);
create index if not exists coach_safety_events_user_session_idx on public.coach_safety_events(user_id, coach_session_id, created_at desc);
create index if not exists care_contacts_user_status_idx on public.care_contacts(user_id, consent_status, updated_at desc);
create index if not exists care_checkins_user_status_idx on public.care_checkins(user_id, status, created_at desc);
create index if not exists care_checkins_contact_idx on public.care_checkins(care_contact_id, created_at desc);
create index if not exists care_consent_events_user_idx on public.care_consent_events(user_id, created_at desc);
create index if not exists money_vault_goals_user_status_idx on public.money_vault_goals(user_id, status, updated_at desc);
create index if not exists money_vault_entries_user_created_idx on public.money_vault_entries(user_id, created_at desc);
create index if not exists money_vault_entries_goal_idx on public.money_vault_entries(money_vault_goal_id, created_at desc);
create index if not exists money_vault_safety_events_user_idx on public.money_vault_safety_events(user_id, created_at desc);
create index if not exists hard_day_reviews_user_date_idx on public.hard_day_reviews(user_id, review_date desc);
create index if not exists hard_day_reviews_user_migration_idx on public.hard_day_reviews(user_id, migration_batch_id);
create index if not exists hard_day_review_events_user_idx on public.hard_day_review_events(user_id, created_at desc);
create index if not exists hard_day_recovery_actions_user_status_idx on public.hard_day_recovery_actions(user_id, status, scheduled_for desc);

-- Foreign-key covering indexes keep hosted advisor checks clean and make deletes/readbacks predictable.
create index if not exists body_load_scores_migration_batch_id_fk_idx on public.body_load_scores(migration_batch_id);
create index if not exists care_consent_events_care_contact_id_fk_idx on public.care_consent_events(care_contact_id);
create index if not exists coach_messages_coach_session_id_fk_idx on public.coach_messages(coach_session_id);
create index if not exists coach_safety_events_coach_session_id_fk_idx on public.coach_safety_events(coach_session_id);
create index if not exists coach_sessions_migration_batch_id_fk_idx on public.coach_sessions(migration_batch_id);
create index if not exists cross_habit_watch_reports_migration_batch_id_fk_idx on public.cross_habit_watch_reports(migration_batch_id);
create index if not exists daily_receipts_migration_batch_id_fk_idx on public.daily_receipts(migration_batch_id);
create index if not exists daily_token_plans_migration_batch_id_fk_idx on public.daily_token_plans(migration_batch_id);
create index if not exists habit_logs_migration_batch_id_fk_idx on public.habit_logs(migration_batch_id);
create index if not exists hard_day_recovery_actions_hard_day_review_id_fk_idx on public.hard_day_recovery_actions(hard_day_review_id);
create index if not exists hard_day_review_events_hard_day_review_id_fk_idx on public.hard_day_review_events(hard_day_review_id);
create index if not exists hard_day_reviews_migration_batch_id_fk_idx on public.hard_day_reviews(migration_batch_id);
create index if not exists nicotine_plans_migration_batch_id_fk_idx on public.nicotine_plans(migration_batch_id);
create index if not exists notification_jobs_migration_batch_id_fk_idx on public.notification_jobs(migration_batch_id);
create index if not exists recovery_network_blocks_circle_id_fk_idx on public.recovery_network_blocks(circle_id);
create index if not exists recovery_network_reports_circle_id_fk_idx on public.recovery_network_reports(circle_id);
create index if not exists recovery_network_reports_hold_gap_session_id_fk_idx on public.recovery_network_reports(hold_gap_session_id);
create index if not exists recovery_support_events_circle_id_fk_idx on public.recovery_support_events(circle_id);
create index if not exists recovery_support_events_hold_gap_session_id_fk_idx on public.recovery_support_events(hold_gap_session_id);
create index if not exists recovery_support_events_target_user_id_fk_idx on public.recovery_support_events(target_user_id);
create index if not exists recovery_twin_matches_twin_user_id_fk_idx on public.recovery_twin_matches(twin_user_id);
create index if not exists subscriptions_country_code_fk_idx on public.subscriptions(country_code);
create index if not exists substitution_alerts_migration_batch_id_fk_idx on public.substitution_alerts(migration_batch_id);
create index if not exists token_events_migration_batch_id_fk_idx on public.token_events(migration_batch_id);
create index if not exists trigger_interventions_trigger_edge_id_fk_idx on public.trigger_interventions(trigger_edge_id);
create index if not exists trigger_interventions_trigger_location_id_fk_idx on public.trigger_interventions(trigger_location_id);
create index if not exists trigger_location_events_trigger_location_id_fk_idx on public.trigger_location_events(trigger_location_id);
create index if not exists user_cost_overrides_country_code_fk_idx on public.user_cost_overrides(country_code);
create index if not exists user_cost_overrides_migration_batch_id_fk_idx on public.user_cost_overrides(migration_batch_id);

drop trigger if exists profiles_set_updated_at on public.profiles;
create trigger profiles_set_updated_at before update on public.profiles
for each row execute function public.set_updated_at();

drop trigger if exists migration_batches_set_updated_at on public.migration_batches;
create trigger migration_batches_set_updated_at before update on public.migration_batches
for each row execute function public.set_updated_at();

drop trigger if exists nicotine_plans_set_updated_at on public.nicotine_plans;
create trigger nicotine_plans_set_updated_at before update on public.nicotine_plans
for each row execute function public.set_updated_at();

drop trigger if exists daily_token_plans_set_updated_at on public.daily_token_plans;
create trigger daily_token_plans_set_updated_at before update on public.daily_token_plans
for each row execute function public.set_updated_at();

drop trigger if exists user_cost_overrides_set_updated_at on public.user_cost_overrides;
create trigger user_cost_overrides_set_updated_at before update on public.user_cost_overrides
for each row execute function public.set_updated_at();

drop trigger if exists daily_receipts_set_updated_at on public.daily_receipts;
create trigger daily_receipts_set_updated_at before update on public.daily_receipts
for each row execute function public.set_updated_at();

drop trigger if exists body_load_scores_set_updated_at on public.body_load_scores;
create trigger body_load_scores_set_updated_at before update on public.body_load_scores
for each row execute function public.set_updated_at();

drop trigger if exists trigger_edges_set_updated_at on public.trigger_edges;
create trigger trigger_edges_set_updated_at before update on public.trigger_edges
for each row execute function public.set_updated_at();

drop trigger if exists trigger_locations_set_updated_at on public.trigger_locations;
create trigger trigger_locations_set_updated_at before update on public.trigger_locations
for each row execute function public.set_updated_at();

drop trigger if exists trigger_interventions_set_updated_at on public.trigger_interventions;
create trigger trigger_interventions_set_updated_at before update on public.trigger_interventions
for each row execute function public.set_updated_at();

drop trigger if exists cross_habit_watch_reports_set_updated_at on public.cross_habit_watch_reports;
create trigger cross_habit_watch_reports_set_updated_at before update on public.cross_habit_watch_reports
for each row execute function public.set_updated_at();

drop trigger if exists notification_jobs_set_updated_at on public.notification_jobs;
create trigger notification_jobs_set_updated_at before update on public.notification_jobs
for each row execute function public.set_updated_at();

drop trigger if exists notification_devices_set_updated_at on public.notification_devices;
create trigger notification_devices_set_updated_at before update on public.notification_devices
for each row execute function public.set_updated_at();

drop trigger if exists subscriptions_set_updated_at on public.subscriptions;
create trigger subscriptions_set_updated_at before update on public.subscriptions
for each row execute function public.set_updated_at();

drop trigger if exists subscription_products_set_updated_at on public.subscription_products;
create trigger subscription_products_set_updated_at before update on public.subscription_products
for each row execute function public.set_updated_at();

drop trigger if exists recovery_circles_set_updated_at on public.recovery_circles;
create trigger recovery_circles_set_updated_at before update on public.recovery_circles
for each row execute function public.set_updated_at();

drop trigger if exists recovery_circle_members_set_updated_at on public.recovery_circle_members;
create trigger recovery_circle_members_set_updated_at before update on public.recovery_circle_members
for each row execute function public.set_updated_at();

drop trigger if exists hold_gap_sessions_set_updated_at on public.hold_gap_sessions;
create trigger hold_gap_sessions_set_updated_at before update on public.hold_gap_sessions
for each row execute function public.set_updated_at();

drop trigger if exists recovery_twin_matches_set_updated_at on public.recovery_twin_matches;
create trigger recovery_twin_matches_set_updated_at before update on public.recovery_twin_matches
for each row execute function public.set_updated_at();

drop trigger if exists recovery_network_reports_set_updated_at on public.recovery_network_reports;
create trigger recovery_network_reports_set_updated_at before update on public.recovery_network_reports
for each row execute function public.set_updated_at();

drop trigger if exists recovery_network_blocks_set_updated_at on public.recovery_network_blocks;
create trigger recovery_network_blocks_set_updated_at before update on public.recovery_network_blocks
for each row execute function public.set_updated_at();

drop trigger if exists coach_sessions_set_updated_at on public.coach_sessions;
create trigger coach_sessions_set_updated_at before update on public.coach_sessions
for each row execute function public.set_updated_at();

drop trigger if exists care_contacts_set_updated_at on public.care_contacts;
create trigger care_contacts_set_updated_at before update on public.care_contacts
for each row execute function public.set_updated_at();

drop trigger if exists care_checkins_set_updated_at on public.care_checkins;
create trigger care_checkins_set_updated_at before update on public.care_checkins
for each row execute function public.set_updated_at();

drop trigger if exists money_vault_goals_set_updated_at on public.money_vault_goals;
create trigger money_vault_goals_set_updated_at before update on public.money_vault_goals
for each row execute function public.set_updated_at();

drop trigger if exists money_vault_entries_set_updated_at on public.money_vault_entries;
create trigger money_vault_entries_set_updated_at before update on public.money_vault_entries
for each row execute function public.set_updated_at();

drop trigger if exists hard_day_reviews_set_updated_at on public.hard_day_reviews;
create trigger hard_day_reviews_set_updated_at before update on public.hard_day_reviews
for each row execute function public.set_updated_at();

drop trigger if exists hard_day_recovery_actions_set_updated_at on public.hard_day_recovery_actions;
create trigger hard_day_recovery_actions_set_updated_at before update on public.hard_day_recovery_actions
for each row execute function public.set_updated_at();

insert into public.country_defaults (
  country_code,
  country_name,
  currency,
  locale,
  primary_distance_unit,
  alternate_distance_unit,
  default_pack_size,
  default_pack_price,
  region_label,
  verified_launch_market,
  global_manual_fallback
) values
  ('usa', 'United States', 'USD', 'en-US', 'mi', 'km', 20, 10.40, 'State', true, false),
  ('canada', 'Canada', 'CAD', 'en-CA', 'km', 'mi', 20, 20.00, 'Province', true, false),
  ('uk', 'United Kingdom', 'GBP', 'en-GB', 'mi', 'km', 20, 18.00, 'Region', true, false),
  ('australia', 'Australia', 'AUD', 'en-AU', 'km', 'mi', 20, 50.00, 'State', true, false),
  ('newzealand', 'New Zealand', 'NZD', 'en-NZ', 'km', 'mi', 20, 42.00, 'Region', true, false),
  ('ireland', 'Ireland', 'EUR', 'en-IE', 'km', 'mi', 20, 18.00, 'County', true, false),
  ('india', 'India', 'INR', 'en-IN', 'km', 'mi', 20, 300.00, 'State', true, false),
  ('indonesia', 'Indonesia', 'IDR', 'id-ID', 'km', 'mi', 20, 35000.00, 'Region', false, true),
  ('bangladesh', 'Bangladesh', 'BDT', 'en-BD', 'km', 'mi', 20, 300.00, 'Region', false, true),
  ('japan', 'Japan', 'JPY', 'ja-JP', 'km', 'mi', 20, 600.00, 'Region', false, true),
  ('pakistan', 'Pakistan', 'PKR', 'en-PK', 'km', 'mi', 20, 500.00, 'Region', false, true),
  ('brazil', 'Brazil', 'BRL', 'pt-BR', 'km', 'mi', 20, 12.00, 'Region', false, true),
  ('philippines', 'Philippines', 'PHP', 'en-PH', 'km', 'mi', 20, 180.00, 'Region', false, true),
  ('mexico', 'Mexico', 'MXN', 'es-MX', 'km', 'mi', 20, 80.00, 'Region', false, true),
  ('southafrica', 'South Africa', 'ZAR', 'en-ZA', 'km', 'mi', 20, 60.00, 'Region', false, true),
  ('nigeria', 'Nigeria', 'NGN', 'en-NG', 'km', 'mi', 20, 2500.00, 'Region', false, true),
  ('singapore', 'Singapore', 'SGD', 'en-SG', 'km', 'mi', 20, 16.00, 'Region', false, true),
  ('malaysia', 'Malaysia', 'MYR', 'en-MY', 'km', 'mi', 20, 18.00, 'Region', false, true),
  ('thailand', 'Thailand', 'THB', 'th-TH', 'km', 'mi', 20, 150.00, 'Region', false, true),
  ('vietnam', 'Vietnam', 'VND', 'vi-VN', 'km', 'mi', 20, 35000.00, 'Region', false, true),
  ('uae', 'United Arab Emirates', 'AED', 'en-AE', 'km', 'mi', 20, 25.00, 'Region', false, true),
  ('saudiarabia', 'Saudi Arabia', 'SAR', 'en-SA', 'km', 'mi', 20, 30.00, 'Region', false, true),
  ('egypt', 'Egypt', 'EGP', 'ar-EG', 'km', 'mi', 20, 80.00, 'Region', false, true),
  ('kenya', 'Kenya', 'KES', 'en-KE', 'km', 'mi', 20, 350.00, 'Region', false, true),
  ('ghana', 'Ghana', 'GHS', 'en-GH', 'km', 'mi', 20, 35.00, 'Region', false, true),
  ('turkey', 'Turkey', 'TRY', 'tr-TR', 'km', 'mi', 20, 75.00, 'Region', false, true),
  ('southkorea', 'South Korea', 'KRW', 'ko-KR', 'km', 'mi', 20, 4500.00, 'Region', false, true),
  ('germany', 'Germany', 'EUR', 'de-DE', 'km', 'mi', 20, 16.00, 'Region', false, true),
  ('france', 'France', 'EUR', 'fr-FR', 'km', 'mi', 20, 16.00, 'Region', false, true),
  ('spain', 'Spain', 'EUR', 'es-ES', 'km', 'mi', 20, 16.00, 'Region', false, true),
  ('italy', 'Italy', 'EUR', 'it-IT', 'km', 'mi', 20, 16.00, 'Region', false, true),
  ('netherlands', 'Netherlands', 'EUR', 'nl-NL', 'km', 'mi', 20, 16.00, 'Region', false, true),
  ('sweden', 'Sweden', 'SEK', 'sv-SE', 'km', 'mi', 20, 80.00, 'Region', false, true),
  ('norway', 'Norway', 'NOK', 'nb-NO', 'km', 'mi', 20, 160.00, 'Region', false, true),
  ('denmark', 'Denmark', 'DKK', 'da-DK', 'km', 'mi', 20, 60.00, 'Region', false, true),
  ('switzerland', 'Switzerland', 'CHF', 'de-CH', 'km', 'mi', 20, 9.00, 'Region', false, true),
  ('portugal', 'Portugal', 'EUR', 'pt-PT', 'km', 'mi', 20, 16.00, 'Region', false, true),
  ('poland', 'Poland', 'PLN', 'pl-PL', 'km', 'mi', 20, 20.00, 'Region', false, true),
  ('global', 'Other country / Global', 'USD', 'en-US', 'km', 'mi', 20, 10.00, 'Country / region', false, true)
on conflict (country_code) do update set
  country_name = excluded.country_name,
  currency = excluded.currency,
  locale = excluded.locale,
  primary_distance_unit = excluded.primary_distance_unit,
  alternate_distance_unit = excluded.alternate_distance_unit,
  default_pack_size = excluded.default_pack_size,
  default_pack_price = excluded.default_pack_price,
  region_label = excluded.region_label,
  verified_launch_market = excluded.verified_launch_market,
  global_manual_fallback = excluded.global_manual_fallback,
  updated_at = now();

insert into public.subscription_products (
  country_code,
  product_id,
  entitlement,
  billing_cadence,
  currency,
  price_amount,
  recovery_multiplier,
  buffer_ratio,
  min_buffer_months,
  max_buffer_months,
  continuation_entitlement,
  continuation_billing_cadence
) values
  ('usa', 'slowburn_usa_plus_monthly', 'plus', 'monthly', 'USD', 4.99, null, null, null, null, 'plus', 'monthly'),
  ('usa', 'slowburn_usa_premium_monthly', 'premium', 'monthly', 'USD', 8.99, null, null, null, null, 'plus', 'monthly'),
  ('usa', 'slowburn_usa_plus_recovery_eta_dynamic', 'plus', 'one_time_recovery', 'USD', 4.99, 0.75, 0.25, 1, 3, 'plus', 'monthly'),
  ('usa', 'slowburn_usa_premium_recovery_eta_dynamic', 'premium', 'one_time_recovery', 'USD', 8.99, 0.75, 0.25, 1, 3, 'plus', 'monthly'),
  ('canada', 'slowburn_canada_plus_monthly', 'plus', 'monthly', 'CAD', 5.99, null, null, null, null, 'plus', 'monthly'),
  ('canada', 'slowburn_canada_premium_monthly', 'premium', 'monthly', 'CAD', 10.99, null, null, null, null, 'plus', 'monthly'),
  ('canada', 'slowburn_canada_plus_recovery_eta_dynamic', 'plus', 'one_time_recovery', 'CAD', 5.99, 0.75, 0.25, 1, 3, 'plus', 'monthly'),
  ('canada', 'slowburn_canada_premium_recovery_eta_dynamic', 'premium', 'one_time_recovery', 'CAD', 10.99, 0.75, 0.25, 1, 3, 'plus', 'monthly'),
  ('uk', 'slowburn_uk_plus_monthly', 'plus', 'monthly', 'GBP', 3.99, null, null, null, null, 'plus', 'monthly'),
  ('uk', 'slowburn_uk_premium_monthly', 'premium', 'monthly', 'GBP', 7.99, null, null, null, null, 'plus', 'monthly'),
  ('uk', 'slowburn_uk_plus_recovery_eta_dynamic', 'plus', 'one_time_recovery', 'GBP', 3.99, 0.75, 0.25, 1, 3, 'plus', 'monthly'),
  ('uk', 'slowburn_uk_premium_recovery_eta_dynamic', 'premium', 'one_time_recovery', 'GBP', 7.99, 0.75, 0.25, 1, 3, 'plus', 'monthly'),
  ('australia', 'slowburn_australia_plus_monthly', 'plus', 'monthly', 'AUD', 6.99, null, null, null, null, 'plus', 'monthly'),
  ('australia', 'slowburn_australia_premium_monthly', 'premium', 'monthly', 'AUD', 12.99, null, null, null, null, 'plus', 'monthly'),
  ('australia', 'slowburn_australia_plus_recovery_eta_dynamic', 'plus', 'one_time_recovery', 'AUD', 6.99, 0.75, 0.25, 1, 3, 'plus', 'monthly'),
  ('australia', 'slowburn_australia_premium_recovery_eta_dynamic', 'premium', 'one_time_recovery', 'AUD', 12.99, 0.75, 0.25, 1, 3, 'plus', 'monthly'),
  ('newzealand', 'slowburn_newzealand_plus_monthly', 'plus', 'monthly', 'NZD', 6.99, null, null, null, null, 'plus', 'monthly'),
  ('newzealand', 'slowburn_newzealand_premium_monthly', 'premium', 'monthly', 'NZD', 12.99, null, null, null, null, 'plus', 'monthly'),
  ('newzealand', 'slowburn_newzealand_plus_recovery_eta_dynamic', 'plus', 'one_time_recovery', 'NZD', 6.99, 0.75, 0.25, 1, 3, 'plus', 'monthly'),
  ('newzealand', 'slowburn_newzealand_premium_recovery_eta_dynamic', 'premium', 'one_time_recovery', 'NZD', 12.99, 0.75, 0.25, 1, 3, 'plus', 'monthly'),
  ('ireland', 'slowburn_ireland_plus_monthly', 'plus', 'monthly', 'EUR', 4.99, null, null, null, null, 'plus', 'monthly'),
  ('ireland', 'slowburn_ireland_premium_monthly', 'premium', 'monthly', 'EUR', 8.99, null, null, null, null, 'plus', 'monthly'),
  ('ireland', 'slowburn_ireland_plus_recovery_eta_dynamic', 'plus', 'one_time_recovery', 'EUR', 4.99, 0.75, 0.25, 1, 3, 'plus', 'monthly'),
  ('ireland', 'slowburn_ireland_premium_recovery_eta_dynamic', 'premium', 'one_time_recovery', 'EUR', 8.99, 0.75, 0.25, 1, 3, 'plus', 'monthly'),
  ('india', 'slowburn_india_plus_monthly', 'plus', 'monthly', 'INR', 149.00, null, null, null, null, 'plus', 'monthly'),
  ('india', 'slowburn_india_premium_monthly', 'premium', 'monthly', 'INR', 349.00, null, null, null, null, 'plus', 'monthly'),
  ('india', 'slowburn_india_plus_recovery_eta_dynamic', 'plus', 'one_time_recovery', 'INR', 149.00, 0.75, 0.25, 1, 3, 'plus', 'monthly'),
  ('india', 'slowburn_india_premium_recovery_eta_dynamic', 'premium', 'one_time_recovery', 'INR', 349.00, 0.75, 0.25, 1, 3, 'plus', 'monthly')
on conflict (country_code, product_id, billing_cadence) do update set
  entitlement = excluded.entitlement,
  currency = excluded.currency,
  price_amount = excluded.price_amount,
  recovery_multiplier = excluded.recovery_multiplier,
  buffer_ratio = excluded.buffer_ratio,
  min_buffer_months = excluded.min_buffer_months,
  max_buffer_months = excluded.max_buffer_months,
  continuation_entitlement = excluded.continuation_entitlement,
  continuation_billing_cadence = excluded.continuation_billing_cadence,
  active = true,
  updated_at = now();

-- ============================================================
-- SECTION 2: row-level security from supabase/rls-policies.sql
-- ============================================================
-- Slowburn Supabase row-level security v1
-- Run after schema.sql.

alter table public.profiles enable row level security;
alter table public.migration_batches enable row level security;
alter table public.nicotine_plans enable row level security;
alter table public.daily_token_plans enable row level security;
alter table public.token_events enable row level security;
alter table public.habit_logs enable row level security;
alter table public.user_cost_overrides enable row level security;
alter table public.daily_receipts enable row level security;
alter table public.body_load_scores enable row level security;
alter table public.trigger_edges enable row level security;
alter table public.trigger_locations enable row level security;
alter table public.trigger_location_events enable row level security;
alter table public.trigger_interventions enable row level security;
alter table public.substitution_alerts enable row level security;
alter table public.cross_habit_watch_reports enable row level security;
alter table public.cross_habit_watch_events enable row level security;
alter table public.notification_devices enable row level security;
alter table public.notification_jobs enable row level security;
alter table public.subscription_products enable row level security;
alter table public.subscriptions enable row level security;
alter table public.country_defaults enable row level security;
alter table public.region_price_defaults enable row level security;
alter table public.recovery_circles enable row level security;
alter table public.recovery_circle_members enable row level security;
alter table public.hold_gap_sessions enable row level security;
alter table public.recovery_twin_matches enable row level security;
alter table public.recovery_support_events enable row level security;
alter table public.recovery_network_reports enable row level security;
alter table public.recovery_network_blocks enable row level security;
alter table public.community_pulse_snapshots enable row level security;
alter table public.coach_sessions enable row level security;
alter table public.coach_messages enable row level security;
alter table public.coach_safety_events enable row level security;
alter table public.care_contacts enable row level security;
alter table public.care_checkins enable row level security;
alter table public.care_consent_events enable row level security;
alter table public.money_vault_goals enable row level security;
alter table public.money_vault_entries enable row level security;
alter table public.money_vault_safety_events enable row level security;
alter table public.hard_day_reviews enable row level security;
alter table public.hard_day_review_events enable row level security;
alter table public.hard_day_recovery_actions enable row level security;

-- Data API privileges: RLS controls rows, but authenticated users still need
-- explicit table privileges before owner-scoped policies can work.
grant usage on schema public to authenticated;

grant select, insert, update, delete on table
  public.profiles,
  public.migration_batches,
  public.nicotine_plans,
  public.daily_token_plans,
  public.token_events,
  public.habit_logs,
  public.user_cost_overrides,
  public.daily_receipts,
  public.body_load_scores,
  public.trigger_edges,
  public.trigger_locations,
  public.trigger_location_events,
  public.trigger_interventions,
  public.substitution_alerts,
  public.cross_habit_watch_reports,
  public.cross_habit_watch_events,
  public.notification_devices,
  public.notification_jobs,
  public.recovery_circle_members,
  public.hold_gap_sessions,
  public.recovery_twin_matches,
  public.recovery_support_events,
  public.recovery_network_reports,
  public.recovery_network_blocks,
  public.coach_sessions,
  public.coach_messages,
  public.coach_safety_events,
  public.care_contacts,
  public.care_checkins,
  public.care_consent_events,
  public.money_vault_goals,
  public.money_vault_entries,
  public.money_vault_safety_events,
  public.hard_day_reviews,
  public.hard_day_review_events,
  public.hard_day_recovery_actions
to authenticated;

grant select on table
  public.country_defaults,
  public.region_price_defaults,
  public.subscription_products,
  public.subscriptions,
  public.recovery_circles,
  public.community_pulse_snapshots
to authenticated;

drop policy if exists "profiles select own" on public.profiles;
drop policy if exists "profiles insert own" on public.profiles;
drop policy if exists "profiles update own" on public.profiles;
drop policy if exists "profiles delete own" on public.profiles;
drop policy if exists "migration batches select own" on public.migration_batches;
drop policy if exists "migration batches insert own" on public.migration_batches;
drop policy if exists "migration batches update own" on public.migration_batches;
drop policy if exists "migration batches delete own" on public.migration_batches;
drop policy if exists "country defaults readable" on public.country_defaults;
drop policy if exists "region defaults readable" on public.region_price_defaults;
drop policy if exists "nicotine plans select own" on public.nicotine_plans;
drop policy if exists "nicotine plans insert own" on public.nicotine_plans;
drop policy if exists "nicotine plans update own" on public.nicotine_plans;
drop policy if exists "nicotine plans delete own" on public.nicotine_plans;
drop policy if exists "daily token plans select own" on public.daily_token_plans;
drop policy if exists "daily token plans insert own" on public.daily_token_plans;
drop policy if exists "daily token plans update own" on public.daily_token_plans;
drop policy if exists "daily token plans delete own" on public.daily_token_plans;
drop policy if exists "token events select own" on public.token_events;
drop policy if exists "token events insert own" on public.token_events;
drop policy if exists "token events update own" on public.token_events;
drop policy if exists "token events delete own" on public.token_events;
drop policy if exists "habit logs select own" on public.habit_logs;
drop policy if exists "habit logs insert own" on public.habit_logs;
drop policy if exists "habit logs update own" on public.habit_logs;
drop policy if exists "habit logs delete own" on public.habit_logs;
drop policy if exists "cost overrides select own" on public.user_cost_overrides;
drop policy if exists "cost overrides insert own" on public.user_cost_overrides;
drop policy if exists "cost overrides update own" on public.user_cost_overrides;
drop policy if exists "cost overrides delete own" on public.user_cost_overrides;
drop policy if exists "daily receipts select own" on public.daily_receipts;
drop policy if exists "daily receipts insert own" on public.daily_receipts;
drop policy if exists "daily receipts update own" on public.daily_receipts;
drop policy if exists "daily receipts delete own" on public.daily_receipts;
drop policy if exists "body load scores select own" on public.body_load_scores;
drop policy if exists "body load scores insert own" on public.body_load_scores;
drop policy if exists "body load scores update own" on public.body_load_scores;
drop policy if exists "body load scores delete own" on public.body_load_scores;
drop policy if exists "trigger edges select own" on public.trigger_edges;
drop policy if exists "trigger edges insert own" on public.trigger_edges;
drop policy if exists "trigger edges update own" on public.trigger_edges;
drop policy if exists "trigger edges delete own" on public.trigger_edges;
drop policy if exists "trigger locations select own" on public.trigger_locations;
drop policy if exists "trigger locations insert own" on public.trigger_locations;
drop policy if exists "trigger locations update own" on public.trigger_locations;
drop policy if exists "trigger locations delete own" on public.trigger_locations;
drop policy if exists "trigger location events select own" on public.trigger_location_events;
drop policy if exists "trigger location events insert own" on public.trigger_location_events;
drop policy if exists "trigger location events update own" on public.trigger_location_events;
drop policy if exists "trigger location events delete own" on public.trigger_location_events;
drop policy if exists "trigger interventions select own" on public.trigger_interventions;
drop policy if exists "trigger interventions insert own" on public.trigger_interventions;
drop policy if exists "trigger interventions update own" on public.trigger_interventions;
drop policy if exists "trigger interventions delete own" on public.trigger_interventions;
drop policy if exists "substitution alerts select own" on public.substitution_alerts;
drop policy if exists "substitution alerts insert own" on public.substitution_alerts;
drop policy if exists "substitution alerts update own" on public.substitution_alerts;
drop policy if exists "substitution alerts delete own" on public.substitution_alerts;
drop policy if exists "cross habit watch reports select own" on public.cross_habit_watch_reports;
drop policy if exists "cross habit watch reports insert own" on public.cross_habit_watch_reports;
drop policy if exists "cross habit watch reports update own" on public.cross_habit_watch_reports;
drop policy if exists "cross habit watch reports delete own" on public.cross_habit_watch_reports;
drop policy if exists "cross habit watch events select own" on public.cross_habit_watch_events;
drop policy if exists "cross habit watch events insert own" on public.cross_habit_watch_events;
drop policy if exists "cross habit watch events update own" on public.cross_habit_watch_events;
drop policy if exists "cross habit watch events delete own" on public.cross_habit_watch_events;
drop policy if exists "notification devices select own" on public.notification_devices;
drop policy if exists "notification devices insert own" on public.notification_devices;
drop policy if exists "notification devices update own" on public.notification_devices;
drop policy if exists "notification devices delete own" on public.notification_devices;
drop policy if exists "notification jobs select own" on public.notification_jobs;
drop policy if exists "notification jobs insert own" on public.notification_jobs;
drop policy if exists "notification jobs update own" on public.notification_jobs;
drop policy if exists "notification jobs delete own" on public.notification_jobs;
drop policy if exists "subscription products readable" on public.subscription_products;
drop policy if exists "subscriptions select own" on public.subscriptions;
drop policy if exists "recovery circles readable" on public.recovery_circles;
drop policy if exists "community pulse readable" on public.community_pulse_snapshots;
drop policy if exists "recovery circle members select own" on public.recovery_circle_members;
drop policy if exists "recovery circle members insert own" on public.recovery_circle_members;
drop policy if exists "recovery circle members update own" on public.recovery_circle_members;
drop policy if exists "recovery circle members delete own" on public.recovery_circle_members;
drop policy if exists "hold gap sessions select own" on public.hold_gap_sessions;
drop policy if exists "hold gap sessions insert own" on public.hold_gap_sessions;
drop policy if exists "hold gap sessions update own" on public.hold_gap_sessions;
drop policy if exists "hold gap sessions delete own" on public.hold_gap_sessions;
drop policy if exists "recovery twin matches select own" on public.recovery_twin_matches;
drop policy if exists "recovery twin matches insert own" on public.recovery_twin_matches;
drop policy if exists "recovery twin matches update own" on public.recovery_twin_matches;
drop policy if exists "recovery twin matches delete own" on public.recovery_twin_matches;
drop policy if exists "recovery support events select own" on public.recovery_support_events;
drop policy if exists "recovery support events insert own" on public.recovery_support_events;
drop policy if exists "recovery network reports select own" on public.recovery_network_reports;
drop policy if exists "recovery network reports insert own" on public.recovery_network_reports;
drop policy if exists "recovery network reports update own" on public.recovery_network_reports;
drop policy if exists "recovery network reports delete own" on public.recovery_network_reports;
drop policy if exists "recovery network blocks select own" on public.recovery_network_blocks;
drop policy if exists "recovery network blocks insert own" on public.recovery_network_blocks;
drop policy if exists "recovery network blocks update own" on public.recovery_network_blocks;
drop policy if exists "recovery network blocks delete own" on public.recovery_network_blocks;
drop policy if exists "recovery support events update own" on public.recovery_support_events;
drop policy if exists "recovery support events delete own" on public.recovery_support_events;
drop policy if exists "coach sessions select own" on public.coach_sessions;
drop policy if exists "coach sessions insert own" on public.coach_sessions;
drop policy if exists "coach sessions update own" on public.coach_sessions;
drop policy if exists "coach sessions delete own" on public.coach_sessions;
drop policy if exists "coach messages select own" on public.coach_messages;
drop policy if exists "coach messages insert own" on public.coach_messages;
drop policy if exists "coach messages update own" on public.coach_messages;
drop policy if exists "coach messages delete own" on public.coach_messages;
drop policy if exists "coach safety events select own" on public.coach_safety_events;
drop policy if exists "coach safety events insert own" on public.coach_safety_events;
drop policy if exists "coach safety events update own" on public.coach_safety_events;
drop policy if exists "coach safety events delete own" on public.coach_safety_events;
drop policy if exists "care contacts select own" on public.care_contacts;
drop policy if exists "care contacts insert own" on public.care_contacts;
drop policy if exists "care contacts update own" on public.care_contacts;
drop policy if exists "care contacts delete own" on public.care_contacts;
drop policy if exists "care checkins select own" on public.care_checkins;
drop policy if exists "care checkins insert own" on public.care_checkins;
drop policy if exists "care checkins update own" on public.care_checkins;
drop policy if exists "care checkins delete own" on public.care_checkins;
drop policy if exists "care consent events select own" on public.care_consent_events;
drop policy if exists "care consent events insert own" on public.care_consent_events;
drop policy if exists "care consent events update own" on public.care_consent_events;
drop policy if exists "care consent events delete own" on public.care_consent_events;
drop policy if exists "money vault goals select own" on public.money_vault_goals;
drop policy if exists "money vault goals insert own" on public.money_vault_goals;
drop policy if exists "money vault goals update own" on public.money_vault_goals;
drop policy if exists "money vault goals delete own" on public.money_vault_goals;
drop policy if exists "money vault entries select own" on public.money_vault_entries;
drop policy if exists "money vault entries insert own" on public.money_vault_entries;
drop policy if exists "money vault entries update own" on public.money_vault_entries;
drop policy if exists "money vault entries delete own" on public.money_vault_entries;
drop policy if exists "money vault safety events select own" on public.money_vault_safety_events;
drop policy if exists "money vault safety events insert own" on public.money_vault_safety_events;
drop policy if exists "money vault safety events update own" on public.money_vault_safety_events;
drop policy if exists "money vault safety events delete own" on public.money_vault_safety_events;
drop policy if exists "hard day reviews select own" on public.hard_day_reviews;
drop policy if exists "hard day reviews insert own" on public.hard_day_reviews;
drop policy if exists "hard day reviews update own" on public.hard_day_reviews;
drop policy if exists "hard day reviews delete own" on public.hard_day_reviews;
drop policy if exists "hard day review events select own" on public.hard_day_review_events;
drop policy if exists "hard day review events insert own" on public.hard_day_review_events;
drop policy if exists "hard day review events update own" on public.hard_day_review_events;
drop policy if exists "hard day review events delete own" on public.hard_day_review_events;
drop policy if exists "hard day recovery actions select own" on public.hard_day_recovery_actions;
drop policy if exists "hard day recovery actions insert own" on public.hard_day_recovery_actions;
drop policy if exists "hard day recovery actions update own" on public.hard_day_recovery_actions;
drop policy if exists "hard day recovery actions delete own" on public.hard_day_recovery_actions;

create policy "profiles select own"
on public.profiles for select
using ((select auth.uid()) = user_id);

create policy "profiles insert own"
on public.profiles for insert
with check ((select auth.uid()) = user_id);

create policy "profiles update own"
on public.profiles for update
using ((select auth.uid()) = user_id)
with check ((select auth.uid()) = user_id);

create policy "profiles delete own"
on public.profiles for delete
using ((select auth.uid()) = user_id);

create policy "migration batches select own"
on public.migration_batches for select
using ((select auth.uid()) = user_id);

create policy "migration batches insert own"
on public.migration_batches for insert
with check ((select auth.uid()) = user_id);

create policy "migration batches update own"
on public.migration_batches for update
using ((select auth.uid()) = user_id)
with check ((select auth.uid()) = user_id);

create policy "migration batches delete own"
on public.migration_batches for delete
using ((select auth.uid()) = user_id);

create policy "country defaults readable"
on public.country_defaults for select
to authenticated
using (active = true);

create policy "region defaults readable"
on public.region_price_defaults for select
to authenticated
using (true);

create policy "nicotine plans select own"
on public.nicotine_plans for select
using ((select auth.uid()) = user_id);

create policy "nicotine plans insert own"
on public.nicotine_plans for insert
with check ((select auth.uid()) = user_id);

create policy "nicotine plans update own"
on public.nicotine_plans for update
using ((select auth.uid()) = user_id)
with check ((select auth.uid()) = user_id);

create policy "nicotine plans delete own"
on public.nicotine_plans for delete
using ((select auth.uid()) = user_id);

create policy "daily token plans select own"
on public.daily_token_plans for select
using ((select auth.uid()) = user_id);

create policy "daily token plans insert own"
on public.daily_token_plans for insert
with check ((select auth.uid()) = user_id);

create policy "daily token plans update own"
on public.daily_token_plans for update
using ((select auth.uid()) = user_id)
with check ((select auth.uid()) = user_id);

create policy "daily token plans delete own"
on public.daily_token_plans for delete
using ((select auth.uid()) = user_id);

create policy "token events select own"
on public.token_events for select
using ((select auth.uid()) = user_id);

create policy "token events insert own"
on public.token_events for insert
with check ((select auth.uid()) = user_id);

create policy "token events update own"
on public.token_events for update
using ((select auth.uid()) = user_id)
with check ((select auth.uid()) = user_id);

create policy "token events delete own"
on public.token_events for delete
using ((select auth.uid()) = user_id);

create policy "habit logs select own"
on public.habit_logs for select
using ((select auth.uid()) = user_id);

create policy "habit logs insert own"
on public.habit_logs for insert
with check ((select auth.uid()) = user_id);

create policy "habit logs update own"
on public.habit_logs for update
using ((select auth.uid()) = user_id)
with check ((select auth.uid()) = user_id);

create policy "habit logs delete own"
on public.habit_logs for delete
using ((select auth.uid()) = user_id);

create policy "cost overrides select own"
on public.user_cost_overrides for select
using ((select auth.uid()) = user_id);

create policy "cost overrides insert own"
on public.user_cost_overrides for insert
with check ((select auth.uid()) = user_id);

create policy "cost overrides update own"
on public.user_cost_overrides for update
using ((select auth.uid()) = user_id)
with check ((select auth.uid()) = user_id);

create policy "cost overrides delete own"
on public.user_cost_overrides for delete
using ((select auth.uid()) = user_id);

create policy "daily receipts select own"
on public.daily_receipts for select
using ((select auth.uid()) = user_id);

create policy "daily receipts insert own"
on public.daily_receipts for insert
with check ((select auth.uid()) = user_id);

create policy "daily receipts update own"
on public.daily_receipts for update
using ((select auth.uid()) = user_id)
with check ((select auth.uid()) = user_id);

create policy "daily receipts delete own"
on public.daily_receipts for delete
using ((select auth.uid()) = user_id);

create policy "body load scores select own"
on public.body_load_scores for select
using ((select auth.uid()) = user_id);

create policy "body load scores insert own"
on public.body_load_scores for insert
with check ((select auth.uid()) = user_id);

create policy "body load scores update own"
on public.body_load_scores for update
using ((select auth.uid()) = user_id)
with check ((select auth.uid()) = user_id);

create policy "body load scores delete own"
on public.body_load_scores for delete
using ((select auth.uid()) = user_id);

create policy "trigger edges select own"
on public.trigger_edges for select
using ((select auth.uid()) = user_id);

create policy "trigger edges insert own"
on public.trigger_edges for insert
with check ((select auth.uid()) = user_id);

create policy "trigger edges update own"
on public.trigger_edges for update
using ((select auth.uid()) = user_id)
with check ((select auth.uid()) = user_id);

create policy "trigger edges delete own"
on public.trigger_edges for delete
using ((select auth.uid()) = user_id);

create policy "trigger locations select own"
on public.trigger_locations for select
using ((select auth.uid()) = user_id);

create policy "trigger locations insert own"
on public.trigger_locations for insert
with check ((select auth.uid()) = user_id);

create policy "trigger locations update own"
on public.trigger_locations for update
using ((select auth.uid()) = user_id)
with check ((select auth.uid()) = user_id);

create policy "trigger locations delete own"
on public.trigger_locations for delete
using ((select auth.uid()) = user_id);

create policy "trigger location events select own"
on public.trigger_location_events for select
using ((select auth.uid()) = user_id);

create policy "trigger location events insert own"
on public.trigger_location_events for insert
with check ((select auth.uid()) = user_id);

create policy "trigger location events update own"
on public.trigger_location_events for update
using ((select auth.uid()) = user_id)
with check ((select auth.uid()) = user_id);

create policy "trigger location events delete own"
on public.trigger_location_events for delete
using ((select auth.uid()) = user_id);

create policy "trigger interventions select own"
on public.trigger_interventions for select
using ((select auth.uid()) = user_id);

create policy "trigger interventions insert own"
on public.trigger_interventions for insert
with check ((select auth.uid()) = user_id);

create policy "trigger interventions update own"
on public.trigger_interventions for update
using ((select auth.uid()) = user_id)
with check ((select auth.uid()) = user_id);

create policy "trigger interventions delete own"
on public.trigger_interventions for delete
using ((select auth.uid()) = user_id);

create policy "substitution alerts select own"
on public.substitution_alerts for select
using ((select auth.uid()) = user_id);

create policy "substitution alerts insert own"
on public.substitution_alerts for insert
with check ((select auth.uid()) = user_id);

create policy "substitution alerts update own"
on public.substitution_alerts for update
using ((select auth.uid()) = user_id)
with check ((select auth.uid()) = user_id);

create policy "substitution alerts delete own"
on public.substitution_alerts for delete
using ((select auth.uid()) = user_id);

create policy "cross habit watch reports select own"
on public.cross_habit_watch_reports for select
using ((select auth.uid()) = user_id);

create policy "cross habit watch reports insert own"
on public.cross_habit_watch_reports for insert
with check ((select auth.uid()) = user_id);

create policy "cross habit watch reports update own"
on public.cross_habit_watch_reports for update
using ((select auth.uid()) = user_id)
with check ((select auth.uid()) = user_id);

create policy "cross habit watch reports delete own"
on public.cross_habit_watch_reports for delete
using ((select auth.uid()) = user_id);

create policy "cross habit watch events select own"
on public.cross_habit_watch_events for select
using ((select auth.uid()) = user_id);

create policy "cross habit watch events insert own"
on public.cross_habit_watch_events for insert
with check ((select auth.uid()) = user_id);

create policy "cross habit watch events update own"
on public.cross_habit_watch_events for update
using ((select auth.uid()) = user_id)
with check ((select auth.uid()) = user_id);

create policy "cross habit watch events delete own"
on public.cross_habit_watch_events for delete
using ((select auth.uid()) = user_id);

create policy "notification devices select own"
on public.notification_devices for select
using ((select auth.uid()) = user_id);

create policy "notification devices insert own"
on public.notification_devices for insert
with check ((select auth.uid()) = user_id);

create policy "notification devices update own"
on public.notification_devices for update
using ((select auth.uid()) = user_id)
with check ((select auth.uid()) = user_id);

create policy "notification devices delete own"
on public.notification_devices for delete
using ((select auth.uid()) = user_id);

create policy "notification jobs select own"
on public.notification_jobs for select
using ((select auth.uid()) = user_id);

create policy "notification jobs insert own"
on public.notification_jobs for insert
with check ((select auth.uid()) = user_id);

create policy "notification jobs update own"
on public.notification_jobs for update
using ((select auth.uid()) = user_id)
with check ((select auth.uid()) = user_id);

create policy "notification jobs delete own"
on public.notification_jobs for delete
using ((select auth.uid()) = user_id);

create policy "subscription products readable"
on public.subscription_products for select
to authenticated
using (active = true);

create policy "subscriptions select own"
on public.subscriptions for select
using ((select auth.uid()) = user_id);

-- Subscription inserts/updates should normally come from trusted webhooks using
-- the service role. These user policies intentionally allow reads only.

create policy "recovery circles readable"
on public.recovery_circles for select
to authenticated
using (active = true);

create policy "community pulse readable"
on public.community_pulse_snapshots for select
to authenticated
using (active = true);

create policy "recovery circle members select own"
on public.recovery_circle_members for select
using ((select auth.uid()) = user_id);

create policy "recovery circle members insert own"
on public.recovery_circle_members for insert
with check ((select auth.uid()) = user_id);

create policy "recovery circle members update own"
on public.recovery_circle_members for update
using ((select auth.uid()) = user_id)
with check ((select auth.uid()) = user_id);

create policy "recovery circle members delete own"
on public.recovery_circle_members for delete
using ((select auth.uid()) = user_id);

create policy "hold gap sessions select own"
on public.hold_gap_sessions for select
using ((select auth.uid()) = user_id);

create policy "hold gap sessions insert own"
on public.hold_gap_sessions for insert
with check ((select auth.uid()) = user_id);

create policy "hold gap sessions update own"
on public.hold_gap_sessions for update
using ((select auth.uid()) = user_id)
with check ((select auth.uid()) = user_id);

create policy "hold gap sessions delete own"
on public.hold_gap_sessions for delete
using ((select auth.uid()) = user_id);

create policy "recovery twin matches select own"
on public.recovery_twin_matches for select
using ((select auth.uid()) = user_id);

create policy "recovery twin matches insert own"
on public.recovery_twin_matches for insert
with check ((select auth.uid()) = user_id);

create policy "recovery twin matches update own"
on public.recovery_twin_matches for update
using ((select auth.uid()) = user_id)
with check ((select auth.uid()) = user_id);

create policy "recovery twin matches delete own"
on public.recovery_twin_matches for delete
using ((select auth.uid()) = user_id);

create policy "recovery support events select own"
on public.recovery_support_events for select
using ((select auth.uid()) = user_id);

create policy "recovery support events insert own"
on public.recovery_support_events for insert
with check ((select auth.uid()) = user_id);

create policy "recovery support events update own"
on public.recovery_support_events for update
using ((select auth.uid()) = user_id)
with check ((select auth.uid()) = user_id);

create policy "recovery support events delete own"
on public.recovery_support_events for delete
using ((select auth.uid()) = user_id);

create policy "recovery network reports select own"
on public.recovery_network_reports for select
using ((select auth.uid()) = user_id);

create policy "recovery network reports insert own"
on public.recovery_network_reports for insert
with check ((select auth.uid()) = user_id);

create policy "recovery network reports update own"
on public.recovery_network_reports for update
using ((select auth.uid()) = user_id)
with check ((select auth.uid()) = user_id);

create policy "recovery network reports delete own"
on public.recovery_network_reports for delete
using ((select auth.uid()) = user_id);

create policy "recovery network blocks select own"
on public.recovery_network_blocks for select
using ((select auth.uid()) = user_id);

create policy "recovery network blocks insert own"
on public.recovery_network_blocks for insert
with check ((select auth.uid()) = user_id);

create policy "recovery network blocks update own"
on public.recovery_network_blocks for update
using ((select auth.uid()) = user_id)
with check ((select auth.uid()) = user_id);

create policy "recovery network blocks delete own"
on public.recovery_network_blocks for delete
using ((select auth.uid()) = user_id);

create policy "coach sessions select own"
on public.coach_sessions for select
using ((select auth.uid()) = user_id);

create policy "coach sessions insert own"
on public.coach_sessions for insert
with check ((select auth.uid()) = user_id);

create policy "coach sessions update own"
on public.coach_sessions for update
using ((select auth.uid()) = user_id)
with check ((select auth.uid()) = user_id);

create policy "coach sessions delete own"
on public.coach_sessions for delete
using ((select auth.uid()) = user_id);

create policy "coach messages select own"
on public.coach_messages for select
using ((select auth.uid()) = user_id);

create policy "coach messages insert own"
on public.coach_messages for insert
with check ((select auth.uid()) = user_id);

create policy "coach messages update own"
on public.coach_messages for update
using ((select auth.uid()) = user_id)
with check ((select auth.uid()) = user_id);

create policy "coach messages delete own"
on public.coach_messages for delete
using ((select auth.uid()) = user_id);

create policy "coach safety events select own"
on public.coach_safety_events for select
using ((select auth.uid()) = user_id);

create policy "coach safety events insert own"
on public.coach_safety_events for insert
with check ((select auth.uid()) = user_id);

create policy "coach safety events update own"
on public.coach_safety_events for update
using ((select auth.uid()) = user_id)
with check ((select auth.uid()) = user_id);

create policy "coach safety events delete own"
on public.coach_safety_events for delete
using ((select auth.uid()) = user_id);

create policy "care contacts select own"
on public.care_contacts for select
using ((select auth.uid()) = user_id);

create policy "care contacts insert own"
on public.care_contacts for insert
with check ((select auth.uid()) = user_id);

create policy "care contacts update own"
on public.care_contacts for update
using ((select auth.uid()) = user_id)
with check ((select auth.uid()) = user_id);

create policy "care contacts delete own"
on public.care_contacts for delete
using ((select auth.uid()) = user_id);

create policy "care checkins select own"
on public.care_checkins for select
using ((select auth.uid()) = user_id);

create policy "care checkins insert own"
on public.care_checkins for insert
with check ((select auth.uid()) = user_id);

create policy "care checkins update own"
on public.care_checkins for update
using ((select auth.uid()) = user_id)
with check ((select auth.uid()) = user_id);

create policy "care checkins delete own"
on public.care_checkins for delete
using ((select auth.uid()) = user_id);

create policy "care consent events select own"
on public.care_consent_events for select
using ((select auth.uid()) = user_id);

create policy "care consent events insert own"
on public.care_consent_events for insert
with check ((select auth.uid()) = user_id);

create policy "care consent events update own"
on public.care_consent_events for update
using ((select auth.uid()) = user_id)
with check ((select auth.uid()) = user_id);

create policy "care consent events delete own"
on public.care_consent_events for delete
using ((select auth.uid()) = user_id);

create policy "money vault goals select own"
on public.money_vault_goals for select
using ((select auth.uid()) = user_id);

create policy "money vault goals insert own"
on public.money_vault_goals for insert
with check ((select auth.uid()) = user_id);

create policy "money vault goals update own"
on public.money_vault_goals for update
using ((select auth.uid()) = user_id)
with check ((select auth.uid()) = user_id);

create policy "money vault goals delete own"
on public.money_vault_goals for delete
using ((select auth.uid()) = user_id);

create policy "money vault entries select own"
on public.money_vault_entries for select
using ((select auth.uid()) = user_id);

create policy "money vault entries insert own"
on public.money_vault_entries for insert
with check ((select auth.uid()) = user_id);

create policy "money vault entries update own"
on public.money_vault_entries for update
using ((select auth.uid()) = user_id)
with check ((select auth.uid()) = user_id);

create policy "money vault entries delete own"
on public.money_vault_entries for delete
using ((select auth.uid()) = user_id);

create policy "money vault safety events select own"
on public.money_vault_safety_events for select
using ((select auth.uid()) = user_id);

create policy "money vault safety events insert own"
on public.money_vault_safety_events for insert
with check ((select auth.uid()) = user_id);

create policy "money vault safety events update own"
on public.money_vault_safety_events for update
using ((select auth.uid()) = user_id)
with check ((select auth.uid()) = user_id);

create policy "money vault safety events delete own"
on public.money_vault_safety_events for delete
using ((select auth.uid()) = user_id);

create policy "hard day reviews select own"
on public.hard_day_reviews for select
using ((select auth.uid()) = user_id);

create policy "hard day reviews insert own"
on public.hard_day_reviews for insert
with check ((select auth.uid()) = user_id);

create policy "hard day reviews update own"
on public.hard_day_reviews for update
using ((select auth.uid()) = user_id)
with check ((select auth.uid()) = user_id);

create policy "hard day reviews delete own"
on public.hard_day_reviews for delete
using ((select auth.uid()) = user_id);

create policy "hard day review events select own"
on public.hard_day_review_events for select
using ((select auth.uid()) = user_id);

create policy "hard day review events insert own"
on public.hard_day_review_events for insert
with check ((select auth.uid()) = user_id);

create policy "hard day review events update own"
on public.hard_day_review_events for update
using ((select auth.uid()) = user_id)
with check ((select auth.uid()) = user_id);

create policy "hard day review events delete own"
on public.hard_day_review_events for delete
using ((select auth.uid()) = user_id);

create policy "hard day recovery actions select own"
on public.hard_day_recovery_actions for select
using ((select auth.uid()) = user_id);

create policy "hard day recovery actions insert own"
on public.hard_day_recovery_actions for insert
with check ((select auth.uid()) = user_id);

create policy "hard day recovery actions update own"
on public.hard_day_recovery_actions for update
using ((select auth.uid()) = user_id)
with check ((select auth.uid()) = user_id);

create policy "hard day recovery actions delete own"
on public.hard_day_recovery_actions for delete
using ((select auth.uid()) = user_id);

-- ============================================================
-- SECTION 3: read-only verification from supabase/verification-queries.sql
-- ============================================================
-- Slowburn Supabase verification queries v1
-- Run after supabase/schema.sql and supabase/rls-policies.sql.
-- This file is read-only: it uses SELECT statements only.
-- It should not create tables, change policies, update data, enable auth,
-- upload files, enable billing, register devices, or clear local data.

with expected_tables(table_name) as (
  values
    ('profiles'),
    ('migration_batches'),
    ('nicotine_plans'),
    ('daily_token_plans'),
    ('token_events'),
    ('habit_logs'),
    ('user_cost_overrides'),
    ('daily_receipts'),
    ('body_load_scores'),
    ('trigger_edges'),
    ('trigger_locations'),
    ('trigger_location_events'),
    ('trigger_interventions'),
    ('substitution_alerts'),
    ('country_defaults'),
    ('region_price_defaults'),
    ('cross_habit_watch_reports'),
    ('cross_habit_watch_events'),
    ('notification_devices'),
    ('notification_jobs'),
    ('subscription_products'),
    ('subscriptions'),
    ('recovery_circles'),
    ('recovery_circle_members'),
    ('hold_gap_sessions'),
    ('recovery_twin_matches'),
    ('community_pulse_snapshots'),
    ('recovery_support_events'),
    ('recovery_network_reports'),
    ('recovery_network_blocks'),
    ('coach_sessions'),
    ('coach_messages'),
    ('coach_safety_events'),
    ('care_contacts'),
    ('care_checkins'),
    ('care_consent_events'),
    ('money_vault_goals'),
    ('money_vault_entries'),
    ('money_vault_safety_events'),
    ('hard_day_reviews'),
    ('hard_day_review_events'),
    ('hard_day_recovery_actions')
)
select
  'table_inventory' as verification_check,
  e.table_name,
  case when t.table_name is null then 'missing' else 'present' end as status
from expected_tables e
left join information_schema.tables t
  on t.table_schema = 'public'
 and t.table_name = e.table_name
order by e.table_name;

with expected_tables(table_name) as (
  values
    ('profiles'), ('migration_batches'), ('nicotine_plans'), ('daily_token_plans'),
    ('token_events'), ('habit_logs'), ('user_cost_overrides'), ('daily_receipts'),
    ('body_load_scores'), ('trigger_edges'), ('trigger_locations'), ('trigger_location_events'),
    ('trigger_interventions'), ('substitution_alerts'), ('country_defaults'), ('region_price_defaults'),
    ('cross_habit_watch_reports'), ('cross_habit_watch_events'), ('notification_devices'),
    ('notification_jobs'), ('subscription_products'), ('subscriptions'), ('recovery_circles'),
    ('recovery_circle_members'), ('hold_gap_sessions'), ('recovery_twin_matches'),
    ('community_pulse_snapshots'), ('recovery_support_events'), ('recovery_network_reports'),
    ('recovery_network_blocks'), ('coach_sessions'), ('coach_messages'), ('coach_safety_events'),
    ('care_contacts'), ('care_checkins'), ('care_consent_events'), ('money_vault_goals'),
    ('money_vault_entries'), ('money_vault_safety_events'), ('hard_day_reviews'),
    ('hard_day_review_events'), ('hard_day_recovery_actions')
)
select
  'rls_enabled' as verification_check,
  e.table_name,
  coalesce(c.relrowsecurity, false) as rls_enabled
from expected_tables e
left join pg_class c on c.relname = e.table_name
left join pg_namespace n on n.oid = c.relnamespace and n.nspname = 'public'
order by e.table_name;

select
  'policy_inventory' as verification_check,
  tablename,
  policyname,
  cmd,
  roles,
  qual,
  with_check
from pg_policies
where schemaname = 'public'
order by tablename, policyname;

with user_owned_tables(table_name) as (
  values
    ('profiles'), ('migration_batches'), ('nicotine_plans'), ('daily_token_plans'),
    ('token_events'), ('habit_logs'), ('user_cost_overrides'), ('daily_receipts'),
    ('body_load_scores'), ('trigger_edges'), ('trigger_locations'), ('trigger_location_events'),
    ('trigger_interventions'), ('substitution_alerts'), ('cross_habit_watch_reports'),
    ('cross_habit_watch_events'), ('notification_devices'), ('notification_jobs'),
    ('recovery_circle_members'), ('hold_gap_sessions'), ('recovery_twin_matches'),
    ('recovery_support_events'), ('recovery_network_reports'), ('recovery_network_blocks'),
    ('coach_sessions'), ('coach_messages'), ('coach_safety_events'), ('care_contacts'),
    ('care_checkins'), ('care_consent_events'), ('money_vault_goals'), ('money_vault_entries'),
    ('money_vault_safety_events'), ('hard_day_reviews'), ('hard_day_review_events'),
    ('hard_day_recovery_actions')
)
select
  'owner_policy_summary' as verification_check,
  u.table_name,
  count(p.policyname) as policy_count,
  count(*) filter (
    where coalesce(p.qual, '') ilike '%auth.uid() = user_id%'
       or coalesce(p.with_check, '') ilike '%auth.uid() = user_id%'
  ) as owner_scoped_policy_count
from user_owned_tables u
left join pg_policies p
  on p.schemaname = 'public'
 and p.tablename = u.table_name
group by u.table_name
order by u.table_name;

-- This query should return zero rows. Authenticated role grants are required
-- in addition to RLS policies before the Data API can use owner-scoped rows.
with full_access_tables(table_name) as (
  values
    ('profiles'), ('migration_batches'), ('nicotine_plans'), ('daily_token_plans'),
    ('token_events'), ('habit_logs'), ('user_cost_overrides'), ('daily_receipts'),
    ('body_load_scores'), ('trigger_edges'), ('trigger_locations'), ('trigger_location_events'),
    ('trigger_interventions'), ('substitution_alerts'), ('cross_habit_watch_reports'),
    ('cross_habit_watch_events'), ('notification_devices'), ('notification_jobs'),
    ('recovery_circle_members'), ('hold_gap_sessions'), ('recovery_twin_matches'),
    ('recovery_support_events'), ('recovery_network_reports'), ('recovery_network_blocks'),
    ('coach_sessions'), ('coach_messages'), ('coach_safety_events'), ('care_contacts'),
    ('care_checkins'), ('care_consent_events'), ('money_vault_goals'), ('money_vault_entries'),
    ('money_vault_safety_events'), ('hard_day_reviews'), ('hard_day_review_events'),
    ('hard_day_recovery_actions')
),
read_only_tables(table_name) as (
  values
    ('country_defaults'),
    ('region_price_defaults'),
    ('subscription_products'),
    ('subscriptions'),
    ('recovery_circles'),
    ('community_pulse_snapshots')
),
expected_grants(table_name, privilege_type) as (
  select table_name, privilege_type
  from full_access_tables
  cross join (values ('SELECT'), ('INSERT'), ('UPDATE'), ('DELETE')) p(privilege_type)
  union all
  select table_name, 'SELECT'
  from read_only_tables
)
select
  'authenticated_table_grant_missing_should_be_empty' as verification_check,
  e.table_name,
  e.privilege_type
from expected_grants e
where not exists (
  select 1
  from information_schema.role_table_grants g
  where g.table_schema = 'public'
    and g.table_name = e.table_name
    and g.grantee = 'authenticated'
    and g.privilege_type = e.privilege_type
)
order by e.table_name, e.privilege_type;

-- This query should return zero rows. Catalog tables should not have client write policies.
select
  'catalog_write_policy_should_be_empty' as verification_check,
  tablename,
  policyname,
  cmd
from pg_policies
where schemaname = 'public'
  and tablename in (
    'country_defaults',
    'region_price_defaults',
    'subscription_products',
    'recovery_circles',
    'community_pulse_snapshots'
  )
  and cmd in ('INSERT', 'UPDATE', 'DELETE');

-- This query should return zero rows. Subscription rows should be user-readable, not client-writable.
select
  'subscription_write_policy_should_be_empty' as verification_check,
  tablename,
  policyname,
  cmd
from pg_policies
where schemaname = 'public'
  and tablename = 'subscriptions'
  and cmd in ('INSERT', 'UPDATE', 'DELETE');

