create table public.apple_customers (
  id bigint generated by default as identity primary key,
  user_id uuid not null references public.profiles (id) on delete cascade,
  revenuecat_app_user_id text not null,
  original_app_user_id text,
  last_seen_environment text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (user_id),
  unique (revenuecat_app_user_id)
);

create table public.apple_subscriptions (
  id bigint generated by default as identity primary key,
  owner_user_id uuid not null references public.profiles (id) on delete cascade,
  revenuecat_app_user_id text not null,
  store_product_id text not null,
  plan text not null,
  status text not null,
  environment text not null,
  store text not null default 'app_store',
  original_transaction_id text not null,
  transaction_id text,
  current_period_start timestamptz,
  current_period_end timestamptz,
  cancel_at_period_end boolean not null default false,
  revoked_at timestamptz,
  raw_customer_id text,
  last_event_type text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (original_transaction_id)
);

create index idx_apple_subscriptions_owner_user_id
  on public.apple_subscriptions (owner_user_id);

create index idx_apple_subscriptions_status
  on public.apple_subscriptions (status);

create index idx_apple_subscriptions_current_period_end
  on public.apple_subscriptions (current_period_end);

create table public.apple_subscription_events (
  id bigint generated by default as identity primary key,
  event_id text not null unique,
  event_type text not null,
  environment text,
  payload jsonb not null,
  received_at timestamptz not null default now(),
  processed_at timestamptz
);
