pragma foreign_keys = on;

create table if not exists users (
  user_id text primary key,
  email text,
  oauth_provider text,
  oauth_id text,
  status text not null default 'active' check (
    status in ('active', 'disabled', 'deletion_requested', 'deleted')
  ),
  region text,
  privacy_settings text not null default '{}',
  created_at text not null,
  updated_at text not null,
  deleted_at text
);

create table if not exists api_sessions (
  session_id text primary key,
  user_id text not null,
  device_id text not null,
  app_version text not null,
  channel text not null check (channel in ('dev', 'beta', 'production')),
  status text not null default 'active' check (
    status in ('active', 'revoked', 'expired')
  ),
  created_at text not null,
  expires_at text not null,
  revoked_at text,
  foreign key (user_id) references users(user_id)
);

create table if not exists 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 text not null,
  expires_at text not null,
  check (used_count <= max_uses)
);

create table if not exists price_plans (
  plan_code text primary key,
  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 integer not null default 1 check (included_runtime_access in (0, 1)),
  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 text not null,
  updated_at text not null
);

create table if not exists subscriptions (
  subscription_id text primary key,
  user_id text not null,
  plan_code text not null,
  provider text not null check (provider in ('stripe', 'invite', 'manual')),
  provider_subscription_id text,
  source text not null check (source in ('checkout', 'invite', 'manual')),
  status text not null check (
    status in ('active', 'cancel_at_period_end', 'past_due', 'canceled', 'expired')
  ),
  current_period_start text not null,
  current_period_end text not null,
  cancel_at_period_end integer not null default 0 check (cancel_at_period_end in (0, 1)),
  access_ends_at text not null,
  created_at text not null,
  updated_at text not null,
  canceled_at text,
  expired_at text,
  foreign key (user_id) references users(user_id),
  foreign key (plan_code) references price_plans(plan_code)
);

create table if not exists pet_profiles (
  pet_id text primary key,
  user_id text not null,
  pet_name text not null,
  species text,
  breed text,
  personality_notes text,
  typical_gestures text,
  memory_notes text,
  consent_status text not null default 'not_requested' check (
    consent_status in ('not_requested', 'granted', 'revoked')
  ),
  consent_version text,
  consent_at text,
  external_processor_consent integer not null default 0 check (
    external_processor_consent in (0, 1)
  ),
  external_processor_name text check (
    external_processor_name is null or external_processor_name in ('openai', 'qwen', 'apimart')
  ),
  external_processor_purpose text check (
    external_processor_purpose is null
    or external_processor_purpose in ('image_generation', 'visual_review')
  ),
  external_processor_model text,
  external_processor_consent_version text,
  external_processor_consent_at text,
  status text not null default 'draft' check (
    status in ('draft', 'ready_for_media', 'generation_allowed', 'deleted')
  ),
  created_at text not null,
  updated_at text not null,
  deleted_at text,
  foreign key (user_id) references users(user_id)
);

create table if not exists pet_media (
  media_id text primary key,
  pet_id text not null,
  user_id text not null,
  media_type text not null check (media_type in ('photo', 'video')),
  storage_ref text not null,
  original_filename text,
  mime_type text not null,
  source_quality text not null default 'unchecked' check (
    source_quality in ('unchecked', 'passed', 'failed')
  ),
  quality_failure_reason text,
  user_visible integer not null default 1 check (user_visible in (0, 1)),
  deletion_status text not null default 'active' check (
    deletion_status in ('active', 'deletion_requested', 'deleted')
  ),
  created_at text not null,
  updated_at text not null,
  deleted_at text,
  foreign key (pet_id) references pet_profiles(pet_id),
  foreign key (user_id) references users(user_id)
);

create table if not exists pet_builds (
  build_id text primary key,
  pet_id text not null,
  user_id text not null,
  status text not null default 'draft' check (
    status in (
      'draft',
      'consent_granted',
      'queued',
      'processing',
      'system_reviewing',
      'preview_ready',
      'approved',
      'revision_requested',
      'rejected',
      'package_ready',
      'downloaded',
      'failed',
      'deleted'
    )
  ),
  input_media_refs text not null default '[]',
  image_provider text not null check (image_provider in ('openai', 'qwen', 'apimart')),
  image_model text not null,
  review_provider text check (review_provider is null or review_provider in ('qwen')),
  review_model text,
  output_package_ref text,
  preview_ref text,
  allowed_actions text not null default '["idle","sleep","walk","look","sit","tail_wag"]',
  checksum_valid integer not null default 1 check (checksum_valid in (0, 1)),
  runtime_compatible integer not null default 1 check (runtime_compatible in (0, 1)),
  user_approved integer not null default 0 check (user_approved in (0, 1)),
  approval_answers text not null default '{}',
  approved_at text,
  revision_reason text,
  failure_code text,
  failure_reason text,
  cost_estimate_cents integer,
  cost_actual_cents integer,
  created_at text not null,
  updated_at text not null,
  completed_at text,
  deleted_at text,
  foreign key (pet_id) references pet_profiles(pet_id),
  foreign key (user_id) references users(user_id)
);

create table if not exists provider_call_metrics (
  metric_id text primary key,
  provider text not null check (provider in ('openai', 'qwen', 'apimart')),
  model text not null,
  purpose text not null check (purpose in ('image_generation', 'visual_review', 'revision')),
  build_id text,
  pet_id text,
  user_id text,
  status text not null check (status in ('started', 'succeeded', 'failed')),
  request_started_at text not null,
  request_finished_at text,
  cost_estimate_cents integer check (cost_estimate_cents is null or cost_estimate_cents >= 0),
  cost_actual_cents integer check (cost_actual_cents is null or cost_actual_cents >= 0),
  failure_code text,
  safe_metadata text not null default '{}',
  created_at text not null,
  foreign key (build_id) references pet_builds(build_id),
  foreign key (pet_id) references pet_profiles(pet_id),
  foreign key (user_id) references users(user_id)
);

create table if not exists pet_packages (
  package_id text primary key,
  build_id text not null,
  pet_id text not null,
  user_id text not null,
  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,
  spritesheet_ref text not null,
  spritesheet_sha256 text not null,
  pet_json_sha256 text not null,
  package_sha256 text,
  allowed_actions text not null default '["idle","sleep","walk","look","sit","tail_wag"]',
  user_approved integer not null default 0 check (user_approved in (0, 1)),
  approved_build_id text not null,
  created_at text not null,
  updated_at text not null,
  revoked_at text,
  deleted_at text,
  foreign key (build_id) references pet_builds(build_id),
  foreign key (pet_id) references pet_profiles(pet_id),
  foreign key (user_id) references users(user_id)
);

create table if not exists orders (
  order_id text primary key,
  user_id text not null,
  pet_id text,
  build_id text,
  provider text not null check (provider in ('stripe', 'manual')),
  provider_checkout_session_id text,
  provider_payment_intent_id text,
  subscription_id text,
  status text not null default 'created' check (
    status in (
      'created',
      'checkout_started',
      'paid',
      'payment_failed',
      'refunded',
      'partially_refunded',
      'disputed',
      'canceled'
    )
  ),
  currency text not null,
  amount_total_cents integer not null,
  product_code text not null,
  refund_reason text,
  created_at text not null,
  updated_at text not null,
  paid_at text,
  refunded_at text,
  foreign key (user_id) references users(user_id),
  foreign key (pet_id) references pet_profiles(pet_id),
  foreign key (build_id) references pet_builds(build_id),
  foreign key (subscription_id) references subscriptions(subscription_id)
);

create table if not exists webhook_events (
  webhook_event_id text primary key,
  provider text not null check (provider in ('stripe')),
  provider_event_id text not null unique,
  event_type text not null,
  status text not null default 'received' check (
    status in ('received', 'processed', 'ignored', 'failed')
  ),
  safe_payload_ref text,
  received_at text not null,
  processed_at text,
  failure_reason text
);

create table if not exists entitlements (
  entitlement_id text primary key,
  user_id text not null,
  order_id text,
  subscription_id text,
  pet_id text,
  build_id text,
  package_id text,
  type text not null check (
    type in ('custom_pet_build', 'revision_credit', 'package_download', 'package_runtime_access', 'action_pack')
  ),
  status text not null default 'inactive' check (
    status in ('inactive', 'active', 'consumed', 'revoked', 'expired')
  ),
  quantity_total integer not null default 1,
  quantity_used integer not null default 0,
  starts_at text not null,
  expires_at text,
  consumed_at text,
  revoked_at text,
  created_at text not null,
  updated_at text not null,
  foreign key (user_id) references users(user_id),
  foreign key (order_id) references orders(order_id),
  foreign key (subscription_id) references subscriptions(subscription_id),
  foreign key (pet_id) references pet_profiles(pet_id),
  foreign key (build_id) references pet_builds(build_id),
  foreign key (package_id) references pet_packages(package_id),
  check (quantity_total >= 0),
  check (quantity_used >= 0),
  check (quantity_used <= quantity_total)
);

create table if not exists quota_buckets (
  quota_id text primary key,
  user_id text not null,
  pet_id text,
  quota_type text not null check (
    quota_type in ('model_cost_cents', 'custom_pet_build', 'revision_credit', 'package_download')
  ),
  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),
  period_start text,
  period_end text,
  status text not null default 'active' check (status in ('active', 'expired', 'revoked')),
  created_at text not null,
  updated_at text not null,
  foreign key (user_id) references users(user_id),
  foreign key (pet_id) references pet_profiles(pet_id),
  check (used_value + reserved_value <= limit_value)
);

create table if not exists usage_ledger (
  usage_id text primary key,
  quota_id text,
  user_id text not null,
  pet_id text,
  build_id text,
  entitlement_id text,
  usage_type text not null check (
    usage_type in ('reserve', 'consume', 'release', 'refund')
  ),
  amount integer not null,
  balance_after integer not null,
  reason text not null,
  created_at text not null,
  foreign key (quota_id) references quota_buckets(quota_id),
  foreign key (user_id) references users(user_id),
  foreign key (pet_id) references pet_profiles(pet_id),
  foreign key (build_id) references pet_builds(build_id),
  foreign key (entitlement_id) references entitlements(entitlement_id)
);

create table if not exists deletion_requests (
  deletion_request_id text primary key,
  user_id text not null,
  target_type text not null check (target_type in ('account', 'pet', 'media', 'package')),
  target_id text not null,
  status text not null default 'requested' check (
    status in ('requested', 'processing', 'completed', 'failed')
  ),
  requested_at text not null,
  completed_at text,
  failure_reason text,
  foreign key (user_id) references users(user_id)
);

create table if not exists events (
  event_id text primary key,
  user_id text,
  pet_id text,
  build_id text,
  event_name text not null check (
    event_name in (
      'boundary_acknowledged',
      'pet_profile_created',
      'pet_media_uploaded',
      'pet_media_quality_passed',
      'pet_media_quality_failed',
      'pet_build_created',
      'pet_build_completed',
      'pet_build_failed',
      'pet_preview_viewed',
      'pet_build_approved',
      'pet_build_rejected',
      'pet_package_downloaded',
      'desktop_pet_loaded',
      'desktop_pet_paused',
      'desktop_pet_hidden',
      'desktop_pet_quit',
      'pet_media_deleted',
      'pet_package_deleted',
      'account_delete_requested',
      'checkout_started',
      'order_paid',
      'order_refunded',
      'entitlement_granted',
      'entitlement_consumed',
      'entitlement_revoked'
    )
  ),
  event_properties text not null default '{}',
  occurred_at text not null,
  foreign key (user_id) references users(user_id),
  foreign key (pet_id) references pet_profiles(pet_id),
  foreign key (build_id) references pet_builds(build_id)
);

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

create index if not exists idx_pet_profiles_user_id on pet_profiles(user_id);
create index if not exists idx_api_sessions_user_id on api_sessions(user_id);
create index if not exists idx_subscriptions_user_id on subscriptions(user_id);
create index if not exists idx_pet_media_pet_id on pet_media(pet_id);
create index if not exists idx_pet_builds_pet_id on pet_builds(pet_id);
create index if not exists idx_provider_call_metrics_build_id on provider_call_metrics(build_id);
create index if not exists idx_pet_packages_build_id on pet_packages(build_id);
create index if not exists idx_orders_user_id on orders(user_id);
create index if not exists idx_webhook_events_provider_event_id on webhook_events(provider_event_id);
create index if not exists idx_entitlements_user_id on entitlements(user_id);
create index if not exists idx_quota_buckets_user_pet on quota_buckets(user_id, pet_id);
create index if not exists idx_usage_ledger_user_pet on usage_ledger(user_id, pet_id);
create index if not exists idx_deletion_requests_user_id on deletion_requests(user_id);
create index if not exists idx_events_user_id on events(user_id);
create index if not exists idx_audit_logs_target on audit_logs(target_type, target_id);
