begin;

create table if not exists public.beta_invites (
  invite_id text primary key,
  code_hash text not null unique,
  status text not null default 'active' check (
    status in ('active', 'used_up', 'revoked', 'expired')
  ),
  max_uses integer not null default 1 check (max_uses >= 0),
  used_count integer not null default 0 check (used_count >= 0),
  grant_plan_code text not null,
  grant_months integer not null default 1 check (grant_months > 0),
  created_by text,
  created_at timestamptz not null default now(),
  expires_at timestamptz not null,
  check (used_count <= max_uses)
);

create table if not exists public.beta_invite_redemptions (
  redemption_id text primary key,
  invite_id text not null references public.beta_invites(invite_id),
  user_id text not null references public.users(user_id),
  subscription_id text,
  redeemed_at timestamptz not null default now()
);

create table if not exists public.price_plans (
  plan_code text primary key,
  display_name text not null,
  provider text not null default 'stripe' check (provider in ('stripe', 'manual')),
  provider_price_id text,
  currency text not null default 'usd',
  list_price_cents integer not null check (list_price_cents >= 0),
  active_price_cents integer not null check (active_price_cents >= 0),
  billing_interval text not null check (billing_interval in ('month')),
  included_build_count integer not null default 1 check (included_build_count >= 0),
  included_revision_count integer not null default 3 check (included_revision_count >= 0),
  included_package_download_count integer not null default 1 check (
    included_package_download_count >= 0
  ),
  included_runtime_access boolean not null default true,
  model_cost_cap_cents integer not null default 500 check (model_cost_cap_cents >= 0),
  status text not null default 'active' check (status in ('active', 'archived')),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

alter table public.beta_invites
  add constraint beta_invites_grant_plan_fk
  foreign key (grant_plan_code)
  references public.price_plans(plan_code);

insert into public.price_plans (
  plan_code,
  display_name,
  provider,
  currency,
  list_price_cents, active_price_cents,
  billing_interval,
  included_build_count,
  included_revision_count,
  included_package_download_count,
  included_runtime_access,
  model_cost_cap_cents,
  status
)
values (
  'sixxie_monthly_launch',
  'Sixxie Monthly Launch',
  'stripe',
  'usd',
  1699, 999,
  'month',
  1,
  3,
  1,
  true,
  500,
  'active'
)
on conflict (plan_code) do update set
  display_name = excluded.display_name,
  provider = excluded.provider,
  currency = excluded.currency,
  list_price_cents = excluded.list_price_cents,
  active_price_cents = excluded.active_price_cents,
  billing_interval = excluded.billing_interval,
  included_build_count = excluded.included_build_count,
  included_revision_count = excluded.included_revision_count,
  included_package_download_count = excluded.included_package_download_count,
  included_runtime_access = excluded.included_runtime_access,
  model_cost_cap_cents = excluded.model_cost_cap_cents,
  status = excluded.status,
  updated_at = now();

create table if not exists public.subscriptions (
  subscription_id text primary key,
  user_id text not null references public.users(user_id),
  plan_code text not null references public.price_plans(plan_code),
  provider text not null check (provider in ('stripe', 'invite', 'manual')),
  provider_customer_id text,
  provider_subscription_id text,
  source text not null check (source in ('checkout', 'invite', 'manual')),
  status text not null check (
    status in ('trialing', 'active', 'past_due', 'canceled', 'expired', 'gifted')
  ),
  current_period_start timestamptz not null,
  current_period_end timestamptz not null,
  access_ends_at timestamptz not null,
  cancel_at_period_end boolean not null default false,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  canceled_at timestamptz,
  expired_at timestamptz
);

alter table public.beta_invite_redemptions
  add constraint beta_invite_redemptions_subscription_fk
  foreign key (subscription_id)
  references public.subscriptions(subscription_id)
  deferrable initially deferred;

create table if not exists public.pet_profiles (
  pet_id text primary key,
  user_id text not null references public.users(user_id),
  pet_name text not null,
  species text,
  breed text,
  personality_notes text,
  typical_gestures text,
  memory_notes text,
  status text not null default 'draft' check (
    status in ('draft', 'ready_for_media', 'generation_allowed', 'deleted')
  ),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  deleted_at timestamptz
);

create table if not exists public.consent_records (
  consent_id text primary key,
  user_id text not null references public.users(user_id),
  pet_id text not null references public.pet_profiles(pet_id),
  purpose text not null check (
    purpose in ('image_generation', 'visual_review', 'package_download')
  ),
  boundary_version text not null,
  privacy_version text not null,
  external_processor text not null check (
    external_processor in ('openai', 'qwen', 'apimart')
  ),
  confirmed_items jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  revoked_at timestamptz
);

create table if not exists public.pet_media (
  media_id text primary key,
  user_id text not null references public.users(user_id),
  pet_id text not null references public.pet_profiles(pet_id),
  media_type text not null check (media_type in ('photo', 'video')),
  storage_ref text not null,
  storage_provider text not null default 'aliyun_oss',
  storage_region text not null,
  bucket text not null,
  object_key text not null,
  original_filename text,
  mime_type text not null,
  size_bytes bigint not null check (size_bytes >= 0),
  sha256 text not null,
  source_quality text not null default 'unchecked' check (
    source_quality in ('unchecked', 'passed', 'failed')
  ),
  quality_failure_reason text,
  deletion_status text not null default 'active' check (
    deletion_status in ('active', 'deletion_requested', 'deleted')
  ),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  deleted_at timestamptz,
  check (storage_provider in ('aliyun_oss'))
);

create table if not exists public.webhook_events (
  webhook_event_id text primary key,
  provider text not null check (provider in ('stripe')),
  provider_event_id text not null,
  event_type text not null,
  status text not null default 'received' check (
    status in ('received', 'processed', 'ignored', 'failed')
  ),
  safe_payload jsonb not null default '{}'::jsonb,
  processed_at timestamptz,
  created_at timestamptz not null default now()
);

create table if not exists public.entitlements (
  entitlement_id text primary key,
  user_id text not null references public.users(user_id),
  subscription_id text references public.subscriptions(subscription_id),
  order_id text references public.orders(order_id),
  pet_id text references public.pet_profiles(pet_id),
  type text not null check (
    type in (
      'custom_pet_build',
      'revision_credit',
      'package_download',
      'package_runtime_access'
    )
  ),
  status text not null default 'active' check (
    status in ('inactive', 'active', 'consumed', 'revoked', 'expired')
  ),
  quantity_total integer not null check (quantity_total >= 0),
  quantity_used integer not null default 0 check (quantity_used >= 0),
  source_type text not null check (source_type in ('stripe', 'invite', 'manual')),
  source_id text,
  starts_at timestamptz not null,
  expires_at timestamptz not null,
  consumed_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  check (quantity_used <= quantity_total)
);

create table if not exists public.quota_buckets (
  quota_id text primary key,
  user_id text references public.users(user_id),
  pet_id text references public.pet_profiles(pet_id),
  scope_type text not null check (
    scope_type in ('user', 'pet', 'user_pet', 'provider', 'global')
  ),
  scope_id text not null,
  quota_type text not null check (
    quota_type in (
      'model_cost_cents',
      'build_count',
      'revision_count',
      'download_count'
    )
  ),
  limit_value integer not null check (limit_value >= 0),
  used_value integer not null default 0 check (used_value >= 0),
  reserved_value integer not null default 0 check (reserved_value >= 0),
  resets_at timestamptz,
  status text not null default 'active' check (
    status in ('active', 'locked', 'expired')
  ),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists public.usage_ledger (
  usage_id text primary key,
  quota_id text not null references public.quota_buckets(quota_id),
  user_id text not null references public.users(user_id),
  pet_id text references public.pet_profiles(pet_id),
  build_id text,
  entry_type text not null check (
    entry_type in ('reserve', 'consume', 'release', 'refund', 'adjustment')
  ),
  amount_value integer not null check (amount_value >= 0),
  provider text check (provider is null or provider in ('openai', 'qwen', 'apimart')),
  model text,
  provider_request_id text,
  idempotency_key text not null,
  safe_metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

create table if not exists public.pet_builds (
  build_id text primary key,
  user_id text not null references public.users(user_id),
  pet_id text not null references public.pet_profiles(pet_id),
  parent_build_id text references public.pet_builds(build_id),
  build_type text not null default 'initial' check (build_type in ('initial', 'revision')),
  revision_index integer not null default 0 check (revision_index >= 0 and revision_index <= 3),
  status text not null default 'draft' check (
    status in (
      'draft',
      'consent_granted',
      'payment_or_invite_verified',
      'quota_reserved',
      'queued',
      'processing',
      'preview_ready',
      'approved',
      'revision_requested',
      'rejected',
      'package_ready',
      'downloaded',
      'failed',
      'deleted'
    )
  ),
  consent_id text references public.consent_records(consent_id),
  entitlement_id text references public.entitlements(entitlement_id),
  input_media_refs jsonb not null default '[]'::jsonb,
  image_provider text not null default 'openai',
  image_model text not null,
  check (image_provider in ('openai', 'qwen', 'apimart')),
  cost_cap_cents integer not null default 500 check (cost_cap_cents >= 0),
  cost_estimate_cents integer not null default 0 check (cost_estimate_cents >= 0),
  cost_actual_cents integer not null default 0 check (cost_actual_cents >= 0),
  output_package_ref text,
  user_approved boolean not null default false,
  approval_answers jsonb not null default '{}'::jsonb,
  approved_at timestamptz,
  failure_code text,
  failure_reason text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  completed_at timestamptz,
  deleted_at timestamptz
);

alter table public.usage_ledger
  add constraint usage_ledger_build_fk
  foreign key (build_id)
  references public.pet_builds(build_id)
  deferrable initially deferred;

create table if not exists public.provider_call_metrics (
  provider_call_id text primary key,
  user_id text not null references public.users(user_id),
  pet_id text references public.pet_profiles(pet_id),
  build_id text references public.pet_builds(build_id),
  provider text not null check (provider in ('openai', 'qwen', 'apimart')),
  model text not null,
  provider_request_id text,
  purpose text not null check (
    purpose in ('canonical_image', 'action_frame', 'visual_review', 'revision')
  ),
  status text not null check (
    status in ('started', 'succeeded', 'failed', 'timed_out')
  ),
  latency_ms integer check (latency_ms is null or latency_ms >= 0),
  estimated_cost_cents integer not null default 0 check (estimated_cost_cents >= 0),
  actual_cost_cents integer not null default 0 check (actual_cost_cents >= 0),
  sanitized_failure_code text,
  safe_metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  completed_at timestamptz
);

create table if not exists public.pet_packages (
  package_id text primary key,
  user_id text not null references public.users(user_id),
  pet_id text not null references public.pet_profiles(pet_id),
  build_id text not null references public.pet_builds(build_id),
  status text not null default 'created' check (
    status in ('created', 'validated', 'approved_for_download', 'revoked', 'deleted')
  ),
  runtime_version text not null,
  asset_manifest_ref text not null,
  animation_manifest_ref text not null,
  package_storage_ref text not null,
  storage_provider text not null default 'aliyun_oss',
  storage_region text not null,
  bucket text not null,
  object_key text not null,
  spritesheet_ref text not null,
  spritesheet_sha256 text not null,
  pet_json_sha256 text not null,
  package_sha256 text not null,
  allowed_actions jsonb not null default '["idle","sleep","walk","look","sit","tail_wag","small_react"]'::jsonb,
  user_approved boolean not null default false,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  revoked_at timestamptz,
  deleted_at timestamptz,
  check (storage_provider in ('aliyun_oss'))
);

create table if not exists public.deletion_requests (
  deletion_request_id text primary key,
  user_id text not null references public.users(user_id),
  pet_id text references public.pet_profiles(pet_id),
  target_type text not null check (
    target_type in ('account', 'pet', 'media', 'build', 'package')
  ),
  target_id text not null,
  status text not null default 'requested' check (
    status in ('requested', 'processing', 'completed', 'failed')
  ),
  requested_at timestamptz not null default now(),
  completed_at timestamptz,
  failure_code text,
  safe_metadata jsonb not null default '{}'::jsonb
);

create table if not exists public.events (
  event_id text primary key,
  user_id text references public.users(user_id),
  pet_id text references public.pet_profiles(pet_id),
  build_id text references public.pet_builds(build_id),
  event_name text not null,
  event_properties jsonb not null default '{}'::jsonb,
  occurred_at timestamptz not null default now()
);

create table if not exists public.audit_logs (
  audit_log_id text primary key,
  actor_type text not null check (actor_type in ('user', 'admin', 'system', 'worker')),
  actor_id text,
  action text not null,
  target_type text not null,
  target_id text not null,
  safe_metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

create unique index if not exists webhook_events_provider_event_uidx
  on public.webhook_events (provider, provider_event_id);

create unique index if not exists usage_ledger_idempotency_uidx
  on public.usage_ledger (idempotency_key);

create unique index if not exists beta_invite_redemptions_user_invite_uidx
  on public.beta_invite_redemptions (user_id, invite_id);

create unique index if not exists subscriptions_provider_subscription_uidx
  on public.subscriptions (provider, provider_subscription_id)
  where provider_subscription_id is not null;

create index if not exists entitlements_user_type_status_idx
  on public.entitlements (user_id, type, status, expires_at);

create index if not exists pet_packages_runtime_access_idx
  on public.pet_packages (user_id, package_id, status, user_approved);

create index if not exists provider_call_metrics_build_idx
  on public.provider_call_metrics (build_id, provider, status);

create index if not exists pet_media_user_pet_idx
  on public.pet_media (user_id, pet_id, deletion_status);

create index if not exists pet_builds_user_pet_status_idx
  on public.pet_builds (user_id, pet_id, status);

create index if not exists quota_buckets_scope_idx
  on public.quota_buckets (scope_type, scope_id, quota_type, status);
alter table public.beta_invites enable row level security;
alter table public.beta_invite_redemptions enable row level security;
alter table public.price_plans enable row level security;
alter table public.subscriptions enable row level security;
alter table public.webhook_events enable row level security;
alter table public.entitlements enable row level security;
alter table public.quota_buckets enable row level security;
alter table public.usage_ledger enable row level security;
alter table public.pet_profiles enable row level security;
alter table public.consent_records enable row level security;
alter table public.pet_media enable row level security;
alter table public.pet_builds enable row level security;
alter table public.provider_call_metrics enable row level security;
alter table public.pet_packages enable row level security;
alter table public.deletion_requests enable row level security;
alter table public.events enable row level security;
alter table public.audit_logs enable row level security;

create policy users_own_select on public.users
  for select to authenticated
  using (user_id = public.current_app_user_id());

create policy api_sessions_own_select on public.api_sessions
  for select to authenticated
  using (user_id = public.current_app_user_id());

create policy beta_invite_redemptions_own_select on public.beta_invite_redemptions
  for select to authenticated
  using (user_id = public.current_app_user_id());

create policy subscriptions_own_select on public.subscriptions
  for select to authenticated
  using (user_id = public.current_app_user_id());

create policy orders_own_select on public.orders
  for select to authenticated
  using (user_id = public.current_app_user_id());

create policy entitlements_own_select on public.entitlements
  for select to authenticated
  using (user_id = public.current_app_user_id());

create policy quota_buckets_own_select on public.quota_buckets
  for select to authenticated
  using (user_id = public.current_app_user_id());

create policy usage_ledger_own_select on public.usage_ledger
  for select to authenticated
  using (user_id = public.current_app_user_id());

create policy pet_profiles_own_select on public.pet_profiles
  for select to authenticated
  using (user_id = public.current_app_user_id());

create policy consent_records_own_select on public.consent_records
  for select to authenticated
  using (user_id = public.current_app_user_id());

create policy pet_media_own_select on public.pet_media
  for select to authenticated
  using (user_id = public.current_app_user_id());

create policy pet_builds_own_select on public.pet_builds
  for select to authenticated
  using (user_id = public.current_app_user_id());

create policy provider_call_metrics_own_select on public.provider_call_metrics
  for select to authenticated
  using (user_id = public.current_app_user_id());

create policy pet_packages_own_select on public.pet_packages
  for select to authenticated
  using (user_id = public.current_app_user_id());

create policy deletion_requests_own_select on public.deletion_requests
  for select to authenticated
  using (user_id = public.current_app_user_id());

create policy events_own_select on public.events
  for select to authenticated
  using (user_id = public.current_app_user_id());

commit;
