// commerce-vc1 — PostgreSQL 16 schema (blueprint)
// Source of truth for names/enums: docs/blueprint/_BRIEF.md
// Conventions: UUID PK gen_random_uuid(); invoice line totals bigint cents; rated_calls/wallet numeric(14,4) cents;
// per-minute rates *_cents_per_min numeric(12,4); schema-qualified;
// tenant tables carry reseller_id; business entities have deleted_at.
// Immutable snapshots: no UPDATE after publish (see table notes).
// ON DELETE: Restrict unless the note says Cascade / SetNull.

Project commerce_vc1 {
  database_type: 'PostgreSQL'
  Note: '''
    Database: commerce_vc1 (LAB/PROD). Schemas = bounded contexts.
    Eloquent MUST qualify schema.table (no dynamic search_path).
    Laravel infra (jobs, cache, sessions, password_reset_tokens) stays in public.
    Partition parents: rating.usage_records, rating.rated_calls (RANGE month).
    Rate storage PADRÃO PROPOSTO: numeric(12,4) cents per minute (D-DB-01).
    Composite tenant integrity (D-DB-18): tenant-owned tables UNIQUE (id, reseller_id);
    child FKs composite (parent_id, reseller_id) → (id, reseller_id). RLS optional MVP.
  '''
}

// ---------------------------------------------------------------------------
// Enums — exact values from _BRIEF.md
// ---------------------------------------------------------------------------

Enum reseller_status {
  draft
  onboarding
  active
  suspended
  terminated
}

Enum reseller_billing_mode {
  vc1_invoices
  reseller_invoices
}

Enum customer_status {
  lead
  trial
  active
  suspended
  cancelled
}

Enum customer_kind {
  end_customer
  reseller_self
}

Enum profile_type {
  pessoa
  empresa
}

Enum subscription_status {
  trial
  active
  blocked_credit
  blocked_limit
  blocked_overdue
  blocked_manual
  cancelled
}

Enum plan_kind {
  package
  commitment
}

Enum plan_franchise_mode {
  separate
  pooled
}

Enum plan_overage_policy {
  charge
  block
}

Enum payment_mode {
  prepaid
  postpaid
}

Enum proposal_stage {
  draft
  sent
  viewed
  trial_active
  negotiating
  accepted
  pending_signature
  contracted
  expired
  lost
  cancelled
}

Enum kanban_column {
  lead
  negotiating
  won
  lost
}

Enum invoice_party {
  reseller
  customer
}

Enum invoice_status {
  draft
  issued
  paid
  overdue
  cancelled
  written_off
  reissued
}

Enum invoice_issuer {
  vc1
  reseller
}

Enum payout_status {
  calculating
  pending_commission_invoice
  approved
  paid
  netted_to_invoice
  settled_zero
}

Enum fiscal_document_status {
  pending
  requested
  issued
  failed
  imported
  authorized
  rejected
  cancelled
}

Enum destination {
  landline_br
  mobile_br
  other
}

Enum actor_scope {
  vc1
  reseller
  customer
}

Enum contact_role {
  legal
  financial
  technical
  commercial
  signer
  proposal_recipient
}

Enum ledger_entry_type {
  deposit
  debit
  expire
  adjust
  refund
}

Enum block_reason {
  credit_zero
  limit_100
  overdue
  overage
  manual
  lab
}

Enum limit_action {
  alert
  block
}

Enum payment_method_kind {
  boleto
  pix
  card
  manual_transfer
}

Enum outbox_status {
  pending
  published
  failed
}

Enum notification_channel {
  email
  sms
}

Enum file_purpose {
  stfc_license
  portability
  contract
  branding
  fiscal
  other
}

Enum cycle_status {
  open
  rating
  closed
  invoiced
}

Enum sip_family {
  origination_750 [note: '750 — origination / rating safety quota']
  termination_850 [note: '850']
  extra_950 [note: '950']
}

Enum contract_status {
  draft
  pending_signature
  signed
  cancelled
  superseded
}

Enum trial_status {
  pending
  active
  converted
  expired
  cancelled
}

Enum statement_status {
  draft
  issued
  superseded
}

Enum webhook_status {
  received
  processed
  ignored
  failed
}

Enum reconciliation_status {
  running
  clean
  drifted
  failed
}

Enum dunning_kind {
  reminder
  overdue
  block_warning
  written_off
}

Enum channel_change_kind {
  increase
  decrease
  trial_set
}

Enum invoice_line_kind {
  did
  channel
  landline_minutes
  mobile_minutes
  package_monthly
  overage
  commitment_topup
  pro_rata_did
  pro_rata_channel
  shortfall
  negative_margin
  adjustment
  tax
}

Enum payout_line_kind {
  paid_margin
  shortfall
  negative_margin
  netting_to_invoice
  manual_adjust
}

// ---------------------------------------------------------------------------
// identity
// ---------------------------------------------------------------------------

Table identity.users {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [note: 'NULL = VC1 staff. Tenant users MUST set it. ON DELETE Restrict']
  customer_id uuid [note: 'NULL unless end-customer login. ON DELETE SetNull']
  email varchar(255) [not null]
  password_hash varchar(255) [not null, note: 'bcrypt/argon; never migrate WP hashes']
  full_name varchar(255) [not null]
  actor_scope actor_scope [not null]
  is_active boolean [not null, default: true]
  email_verified_at timestamptz
  last_login_at timestamptz
  last_login_ip varchar(45)
  two_factor_secret text [note: 'encrypted; NULL = off']
  two_factor_confirmed_at timestamptz
  locale varchar(16) [not null, default: 'pt_BR']
  created_at timestamptz [not null, default: `now()`]
  updated_at timestamptz [not null, default: `now()`]
  deleted_at timestamptz

  indexes {
    (reseller_id, email) [unique, name: 'uq_users_reseller_email_live', note: 'partial WHERE deleted_at IS NULL; unique (email) WHERE reseller_id IS NULL AND deleted_at IS NULL']
    reseller_id [name: 'idx_users_reseller']
    customer_id [name: 'idx_users_customer']
    actor_scope [name: 'idx_users_scope']
  }

  Note: 'Login for VC1 Filament, reseller panel, customer /app. Replaces public.users (bigint).'
}

Table identity.roles {
  id uuid [pk, default: `gen_random_uuid()`]
  slug varchar(64) [not null, unique, note: 'Exact brief slugs: vc1_admin, vc1_finance, vc1_ops, vc1_support, reseller_owner, reseller_admin, reseller_finance, reseller_seller, customer_admin, customer_user']
  name varchar(128) [not null]
  actor_scope actor_scope [not null]
  created_at timestamptz [not null, default: `now()`]
  updated_at timestamptz [not null, default: `now()`]
}

Table identity.user_roles {
  id uuid [pk, default: `gen_random_uuid()`]
  user_id uuid [not null, note: 'ON DELETE Cascade']
  role_id uuid [not null, note: 'ON DELETE Restrict']
  reseller_id uuid [note: 'NULL for VC1 roles. ON DELETE Cascade']
  created_at timestamptz [not null, default: `now()`]

  indexes {
    (user_id, role_id, reseller_id) [unique, name: 'uq_user_roles']
    (reseller_id, user_id) [name: 'idx_user_roles_reseller']
  }
}

Table identity.permissions {
  id uuid [pk, default: `gen_random_uuid()`]
  slug varchar(128) [not null, unique]
  name varchar(255) [not null]
  created_at timestamptz [not null, default: `now()`]
}

Table identity.role_permissions {
  role_id uuid [not null, note: 'ON DELETE Cascade']
  permission_id uuid [not null, note: 'ON DELETE Cascade']

  indexes {
    (role_id, permission_id) [unique, name: 'uq_role_permissions']
  }
}

Table identity.api_tokens {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [note: 'NULL = VC1 automation. ON DELETE Cascade']
  user_id uuid [note: 'Sanctum tokenable; ON DELETE Cascade']
  name varchar(128) [not null]
  token_prefix varchar(12) [not null]
  token_hash varchar(64) [not null, unique, note: 'sha256 of secret; plaintext never stored']
  abilities jsonb [not null, default: `'[]'`]
  last_used_at timestamptz
  expires_at timestamptz
  created_at timestamptz [not null, default: `now()`]
  updated_at timestamptz [not null, default: `now()`]
  deleted_at timestamptz

  indexes {
    (reseller_id, user_id) [name: 'idx_api_tokens_reseller_user']
    expires_at [name: 'idx_api_tokens_expires']
  }

  Note: 'Replaces public.personal_access_tokens. Reseller public API lives on api.vc1.com.br.'
}

Ref: identity.users.reseller_id > tenancy.resellers.id
Ref: identity.users.(customer_id, reseller_id) > customers.customers.(id, reseller_id)
Ref: identity.user_roles.user_id > identity.users.id
Ref: identity.user_roles.role_id > identity.roles.id
Ref: identity.user_roles.reseller_id > tenancy.resellers.id
Ref: identity.role_permissions.role_id > identity.roles.id
Ref: identity.role_permissions.permission_id > identity.permissions.id
Ref: identity.api_tokens.reseller_id > tenancy.resellers.id
Ref: identity.api_tokens.user_id > identity.users.id

// ---------------------------------------------------------------------------
// tenancy
// ---------------------------------------------------------------------------

Table tenancy.resellers {
  id uuid [pk, default: `gen_random_uuid()`]
  slug varchar(63) [not null, note: 'Host {slug}.vc1.com.br. CHECK not in reserved slugs']
  legal_name varchar(255) [not null]
  trade_name varchar(255)
  document varchar(14) [not null, note: 'CNPJ digits only']
  status reseller_status [not null, default: 'draft']
  billing_mode reseller_billing_mode [not null]
  vc1_tier_id uuid [note: 'Contracted VC1 band. ON DELETE Restrict']
  price_base_version_id uuid [note: 'Version whose rates were cloned. ON DELETE Restrict']
  commitment_cents bigint [not null, note: 'Monthly minimum with VC1']
  fidelity_months int [not null, default: 0]
  payment_mode payment_mode [not null, default: 'postpaid']
  due_day smallint [not null, default: 10, note: 'Invoice due day 1..28; cycle is always calendar month America/Sao_Paulo (D-DB-13)']
  pending_billing_mode reseller_billing_mode [note: 'Scheduled mode change; applied day-1 job']
  billing_mode_effective_at date [note: 'First day of cycle when pending_billing_mode applies']
  pending_billing_amendment_envelope_id uuid [note: 'meSign aditivo reference']
  legacy_project_id bigint [note: 'easy_cicerone.projetos_clientes.id']
  legacy_domain_sippulse varchar(255)
  payment_gateway_ref varchar(64) [note: 'Not used in vc1_invoices; mePay is VC1 gateway']
  onboarded_at timestamptz
  suspended_at timestamptz
  terminated_at timestamptz
  created_at timestamptz [not null, default: `now()`]
  updated_at timestamptz [not null, default: `now()`]
  deleted_at timestamptz

  indexes {
    slug [unique, name: 'uq_resellers_slug_live', note: 'partial WHERE deleted_at IS NULL']
    document [unique, name: 'uq_resellers_document_live', note: 'partial WHERE deleted_at IS NULL']
    legacy_project_id [unique, name: 'uq_resellers_legacy_project', note: 'partial WHERE legacy_project_id IS NOT NULL']
    status [name: 'idx_resellers_status']
    billing_mode [name: 'idx_resellers_billing_mode']
  }

  Note: '''
    CHECK billing_mode=reseller_invoices ⇒ exists verified STFC license.
    CHECK slug NOT IN reserved (admin,cliente,api,sales,lab,www,sip,mail,status,docs,app,static).
    Tenant root. 1 row ↔ 1 projetos_clientes.
  '''
}

Table tenancy.reseller_domains {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [not null, note: 'ON DELETE Cascade']
  host varchar(255) [not null, note: '{slug}.vc1.com.br or future vanity']
  is_primary boolean [not null, default: true]
  verified_at timestamptz
  created_at timestamptz [not null, default: `now()`]
  updated_at timestamptz [not null, default: `now()`]
  deleted_at timestamptz

  indexes {
    host [unique, name: 'uq_reseller_domains_host_live', note: 'partial WHERE deleted_at IS NULL']
    (reseller_id, is_primary) [name: 'idx_reseller_domains_primary', note: 'partial unique WHERE is_primary AND deleted_at IS NULL']
  }
}

Table tenancy.reseller_branding {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [not null, unique, note: 'ON DELETE Cascade']
  trade_name varchar(255) [not null]
  logo_file_id uuid [note: 'ON DELETE SetNull']
  primary_color varchar(7) [note: '#RRGGBB']
  secondary_color varchar(7)
  support_email varchar(255)
  support_phone varchar(32)
  sender_email varchar(255)
  sender_name varchar(128)
  extra jsonb [not null, default: `'{}'`]
  created_at timestamptz [not null, default: `now()`]
  updated_at timestamptz [not null, default: `now()`]
}

Table tenancy.stfc_licenses {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [not null, note: 'ON DELETE Cascade']
  license_number varchar(64) [not null]
  document_file_id uuid [note: 'ON DELETE Restrict while verified']
  valid_from date
  valid_until date
  verified_at timestamptz
  verified_by_user_id uuid [note: 'ON DELETE SetNull']
  notes text
  created_at timestamptz [not null, default: `now()`]
  updated_at timestamptz [not null, default: `now()`]
  deleted_at timestamptz

  indexes {
    (reseller_id, license_number) [unique, name: 'uq_stfc_license_number_live', note: 'partial WHERE deleted_at IS NULL']
    (reseller_id) [name: 'idx_stfc_reseller']
  }

  Note: 'Required for billing_mode=reseller_invoices. Verified by VC1 staff.'
}

Table tenancy.reseller_settings {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [not null, unique, note: 'ON DELETE Cascade']
  default_payment_mode payment_mode [not null, default: 'postpaid']
  default_overage_policy plan_overage_policy [not null, default: 'charge']
  alert_limit_percent smallint [not null, default: 80]
  block_limit_percent smallint [not null, default: 100]
  allow_negative_margin boolean [not null, default: true]
  typed jsonb [not null, default: `'{}'`]
  created_at timestamptz [not null, default: `now()`]
  updated_at timestamptz [not null, default: `now()`]
}

Table tenancy.reseller_contracts {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [not null, note: 'ON DELETE Restrict']
  price_base_version_id uuid [not null, note: 'ON DELETE Restrict']
  vc1_tier_id uuid [not null, note: 'ON DELETE Restrict']
  commitment_cents bigint [not null]
  fidelity_months int [not null, default: 0]
  billing_mode reseller_billing_mode [not null]
  snapshot jsonb [not null, note: 'Immutable copy of rates + legal text at signature']
  signed_at timestamptz
  effective_from date [not null]
  effective_until date
  created_at timestamptz [not null, default: `now()`]
  updated_at timestamptz [not null, default: `now()`]
  deleted_at timestamptz

  indexes {
    (reseller_id, effective_from) [name: 'idx_reseller_contracts_window']
  }

  Note: 'VC1 ↔ reseller commercial contract. Snapshot immutable after signed_at.'
}

Ref: tenancy.resellers.vc1_tier_id > pricing.vc1_tiers.id
Ref: tenancy.resellers.price_base_version_id > pricing.price_base_versions.id
Ref: tenancy.reseller_domains.reseller_id > tenancy.resellers.id
Ref: tenancy.reseller_branding.reseller_id > tenancy.resellers.id
Ref: tenancy.reseller_branding.logo_file_id > files.objects.id
Ref: tenancy.stfc_licenses.reseller_id > tenancy.resellers.id
Ref: tenancy.stfc_licenses.document_file_id > files.objects.id
Ref: tenancy.stfc_licenses.verified_by_user_id > identity.users.id
Ref: tenancy.reseller_settings.reseller_id > tenancy.resellers.id
Ref: tenancy.reseller_contracts.reseller_id > tenancy.resellers.id
Ref: tenancy.reseller_contracts.price_base_version_id > pricing.price_base_versions.id
Ref: tenancy.reseller_contracts.vc1_tier_id > pricing.vc1_tiers.id

// ---------------------------------------------------------------------------
// pricing
// ---------------------------------------------------------------------------

Table pricing.price_bases {
  id uuid [pk, default: `gen_random_uuid()`]
  name varchar(128) [not null]
  code varchar(32) [not null, unique]
  cadence_min_s int [not null, default: 30]
  cadence_inc_s int [not null, default: 6]
  is_active boolean [not null, default: true]
  created_at timestamptz [not null, default: `now()`]
  updated_at timestamptz [not null, default: `now()`]
  deleted_at timestamptz

  Note: 'VC1 price book. Versions are immutable; editor always inserts a new version.'
}

Table pricing.price_base_versions {
  id uuid [pk, default: `gen_random_uuid()`]
  price_base_id uuid [not null, note: 'ON DELETE Restrict']
  version_number int [not null]
  effective_from timestamptz [not null]
  effective_until timestamptz
  published_at timestamptz [note: 'NULL = draft; after publish the row is immutable']
  published_by_user_id uuid [note: 'ON DELETE SetNull']
  change_reason text
  created_at timestamptz [not null, default: `now()`]

  indexes {
    (price_base_id, version_number) [unique, name: 'uq_price_base_version']
    (price_base_id, effective_from) [name: 'idx_price_base_versions_from']
  }

  Note: 'IMMUTABLE after published_at. No updated_at. CHECK cadence 30/6 on parent.'
}

Table pricing.vc1_tiers {
  id uuid [pk, default: `gen_random_uuid()`]
  price_base_id uuid [not null, note: 'ON DELETE Restrict']
  code varchar(32) [not null, note: 'Stable band code inside a base, e.g. T1']
  name varchar(128) [not null]
  sort_order int [not null, default: 0]
  created_at timestamptz [not null, default: `now()`]
  deleted_at timestamptz

  indexes {
    (price_base_id, code) [unique, name: 'uq_vc1_tiers_code']
  }

  Note: 'Stable band identity. Rates live on price_base_version_rows. locked_tier_id points here.'
}

Table pricing.price_base_version_rows {
  id uuid [pk, default: `gen_random_uuid()`]
  price_base_version_id uuid [not null, note: 'ON DELETE Cascade of unpublished drafts only; published versions Restrict']
  vc1_tier_id uuid [not null, note: 'ON DELETE Restrict']
  min_commitment_cents bigint [not null]
  unit_did_cents bigint [not null]
  unit_channel_cents bigint [not null]
  unit_landline_cents_per_min numeric(12,4) [not null, note: 'Centesimos de real por minuto']
  unit_mobile_cents_per_min numeric(12,4) [not null]
  fidelity_default_months int [not null, default: 0]
  created_at timestamptz [not null, default: `now()`]

  indexes {
    (price_base_version_id, vc1_tier_id) [unique, name: 'uq_price_base_version_row']
    (price_base_version_id, min_commitment_cents) [name: 'idx_pbase_row_commit']
  }

  Note: 'IMMUTABLE snapshot of unit rates. CHECK min_commitment_cents > 0; CHECK rates >= 0.'
}

Table pricing.reseller_tier_tables {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [not null, note: 'ON DELETE Cascade']
  source_price_base_version_id uuid [not null, note: 'ON DELETE Restrict']
  markup_pct numeric(8,4) [not null, default: 0, note: 'Default markup applied on clone']
  is_current boolean [not null, default: true]
  created_at timestamptz [not null, default: `now()`]
  updated_at timestamptz [not null, default: `now()`]
  deleted_at timestamptz

  indexes {
    (reseller_id, is_current) [name: 'idx_reseller_tier_tables_current', note: 'partial unique WHERE is_current AND deleted_at IS NULL']
    reseller_id [name: 'idx_reseller_tier_tables_reseller']
    (id, reseller_id) [unique, name: 'uq_pricing_reseller_tier_tables_id_reseller']
  }
}

Table pricing.reseller_tier_rows {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [not null, note: 'ON DELETE Cascade']
  reseller_tier_table_id uuid [not null, note: 'ON DELETE Cascade']
  vc1_tier_id uuid [not null, note: 'ON DELETE Restrict']
  source_version_row_id uuid [not null, note: 'ON DELETE Restrict']
  markup_pct numeric(8,4) [not null, default: 0]
  unit_did_cents bigint [not null]
  unit_channel_cents bigint [not null]
  unit_landline_cents_per_min numeric(12,4) [not null]
  unit_mobile_cents_per_min numeric(12,4) [not null]
  min_commitment_cents bigint [not null]
  below_cost boolean [not null, default: false, note: 'True when any sell unit < VC1 cost; alert, never block']
  created_at timestamptz [not null, default: `now()`]
  updated_at timestamptz [not null, default: `now()`]

  indexes {
    (reseller_id, reseller_tier_table_id, vc1_tier_id) [unique, name: 'uq_reseller_tier_row']
    (reseller_id, below_cost) [name: 'idx_reseller_tier_rows_below_cost']
    (id, reseller_id) [unique, name: 'uq_pricing_reseller_tier_rows_id_reseller']
  }

  Note: 'Reseller may edit row-by-row after clone. Selling below cost sets below_cost=true.'
}

Ref: pricing.price_base_versions.price_base_id > pricing.price_bases.id
Ref: pricing.price_base_versions.published_by_user_id > identity.users.id
Ref: pricing.vc1_tiers.price_base_id > pricing.price_bases.id
Ref: pricing.price_base_version_rows.price_base_version_id > pricing.price_base_versions.id
Ref: pricing.price_base_version_rows.vc1_tier_id > pricing.vc1_tiers.id
Ref: pricing.reseller_tier_tables.reseller_id > tenancy.resellers.id
Ref: pricing.reseller_tier_tables.source_price_base_version_id > pricing.price_base_versions.id
Ref: pricing.reseller_tier_rows.reseller_id > tenancy.resellers.id
Ref: pricing.reseller_tier_rows.(reseller_tier_table_id, reseller_id) > pricing.reseller_tier_tables.(id, reseller_id)
Ref: pricing.reseller_tier_rows.vc1_tier_id > pricing.vc1_tiers.id
Ref: pricing.reseller_tier_rows.source_version_row_id > pricing.price_base_version_rows.id

// ---------------------------------------------------------------------------
// catalog
// ---------------------------------------------------------------------------

Table catalog.destinations {
  code destination [pk]
  name varchar(64) [not null]
  is_billable boolean [not null, default: true]
  blocked_by_default boolean [not null, default: false, note: 'other = true (PADRÃO PROPOSTO D-DB-08)']
  created_at timestamptz [not null, default: `now()`]
}

Table catalog.suggested_packages {
  id uuid [pk, default: `gen_random_uuid()`]
  code varchar(32) [not null, unique]
  name varchar(128) [not null]
  description text
  kind plan_kind [not null, default: 'package']
  template jsonb [not null, note: 'Quotas, price, trial, overage used when reseller clones']
  is_active boolean [not null, default: true]
  created_at timestamptz [not null, default: `now()`]
  updated_at timestamptz [not null, default: `now()`]
  deleted_at timestamptz

  Note: 'VC1-owned templates. Clone copies into catalog.plans with reseller_id set.'
}

Table catalog.plans {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [not null, note: 'ON DELETE Restrict']
  suggested_package_id uuid [note: 'ON DELETE SetNull']
  code varchar(64) [not null]
  name varchar(128) [not null]
  kind plan_kind [not null]
  is_active boolean [not null, default: true]
  current_version_id uuid [note: 'ON DELETE Restrict; set after first version']
  negative_margin_acknowledged_at timestamptz
  created_at timestamptz [not null, default: `now()`]
  updated_at timestamptz [not null, default: `now()`]
  deleted_at timestamptz

  indexes {
    (reseller_id, code) [unique, name: 'uq_plans_reseller_code_live', note: 'partial WHERE deleted_at IS NULL']
    (reseller_id, kind) [name: 'idx_plans_reseller_kind']
    (id, reseller_id) [unique, name: 'uq_catalog_plans_id_reseller']
  }

  Note: 'Head entity. Commercial fields live on plan_versions (immutable).'
}

Table catalog.plan_versions {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [not null, note: 'ON DELETE Restrict']
  plan_id uuid [not null, note: 'ON DELETE Restrict']
  version_number int [not null]
  kind plan_kind [not null]
  franchise_mode plan_franchise_mode
  overage_policy plan_overage_policy [not null, default: 'charge']
  payment_mode payment_mode [not null]
  sell_price_cents bigint [note: 'NULL if kind=commitment']
  commitment_cents bigint [note: 'NULL if kind=package']
  locked_tier_id uuid [note: 'Always the contracted band; no auto-upgrade. ON DELETE Restrict']
  reseller_tier_row_id uuid [note: 'Row used to price this version. ON DELETE Restrict']
  unit_did_cents bigint
  unit_channel_cents bigint
  unit_landline_cents_per_min numeric(12,4)
  unit_mobile_cents_per_min numeric(12,4)
  overage_landline_cents_per_min numeric(12,4) [note: 'Package overage; snapshotted on subscription']
  overage_mobile_cents_per_min numeric(12,4)
  cost_did_cents bigint
  cost_channel_cents bigint
  cost_landline_cents_per_min numeric(12,4)
  cost_mobile_cents_per_min numeric(12,4)
  fidelity_months int [not null, default: 0]
  below_cost boolean [not null, default: false]
  snapshot jsonb [not null, note: 'Full freeze of quotas + rates + trial rules']
  published_at timestamptz [not null, default: `now()`]
  created_at timestamptz [not null, default: `now()`]

  indexes {
    (plan_id, version_number) [unique, name: 'uq_plan_version']
    (reseller_id, plan_id) [name: 'idx_plan_versions_reseller']
    (id, reseller_id) [unique, name: 'uq_catalog_plan_versions_id_reseller']
  }

  Note: '''
    IMMUTABLE. CHECK kind=package ⇒ sell_price_cents NOT NULL AND franchise_mode NOT NULL.
    CHECK kind=commitment ⇒ commitment_cents NOT NULL AND locked_tier_id NOT NULL.
    No updated_at.
  '''
}

Table catalog.plan_quotas {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [not null, note: 'ON DELETE Restrict']
  plan_version_id uuid [not null, unique, note: 'ON DELETE Cascade']
  included_dids int [not null, default: 0]
  included_channels int [not null, default: 0]
  franchise_mode plan_franchise_mode [not null]
  franchise_landline_seconds int [not null, default: 0]
  franchise_mobile_seconds int [not null, default: 0]
  franchise_total_seconds int [not null, default: 0]
  created_at timestamptz [not null, default: `now()`]

  Note: 'CHECK separate ⇒ total=0 and (landline+mobile)>=0; pooled ⇒ total>0 and landline=mobile=0. Unused minutes expire monthly (not stored across cycles).'
}

Table catalog.plan_trial_rules {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [not null, note: 'ON DELETE Restrict']
  plan_version_id uuid [not null, unique, note: 'ON DELETE Cascade']
  enabled boolean [not null, default: false]
  duration_days int [not null, default: 0]
  franchise_landline_seconds int [not null, default: 0]
  franchise_mobile_seconds int [not null, default: 0]
  franchise_total_seconds int [not null, default: 0]
  included_dids int [not null, default: 0]
  included_channels int [not null, default: 0]
  created_at timestamptz [not null, default: `now()`]

  Note: 'Reseller defines trial per plan. duration_days=0 AND enabled=false ⇒ no trial.'
}

Ref: catalog.plans.reseller_id > tenancy.resellers.id
Ref: catalog.plans.suggested_package_id > catalog.suggested_packages.id
Ref: catalog.plans.(current_version_id, reseller_id) > catalog.plan_versions.(id, reseller_id)
Ref: catalog.plan_versions.reseller_id > tenancy.resellers.id
Ref: catalog.plan_versions.(plan_id, reseller_id) > catalog.plans.(id, reseller_id)
Ref: catalog.plan_versions.locked_tier_id > pricing.vc1_tiers.id
Ref: catalog.plan_versions.(reseller_tier_row_id, reseller_id) > pricing.reseller_tier_rows.(id, reseller_id)
Ref: catalog.plan_quotas.reseller_id > tenancy.resellers.id
Ref: catalog.plan_quotas.(plan_version_id, reseller_id) > catalog.plan_versions.(id, reseller_id)
Ref: catalog.plan_trial_rules.reseller_id > tenancy.resellers.id
Ref: catalog.plan_trial_rules.(plan_version_id, reseller_id) > catalog.plan_versions.(id, reseller_id)

// ---------------------------------------------------------------------------
// customers
// ---------------------------------------------------------------------------

Table customers.customers {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [not null, note: 'ON DELETE Restrict']
  kind customer_kind [not null, default: 'end_customer']
  status customer_status [not null, default: 'lead']
  profile_type profile_type [not null]
  legal_name varchar(255) [not null]
  trade_name varchar(255)
  document varchar(14) [note: 'CPF/CNPJ digits; unique per reseller when present']
  email varchar(255)
  phone varchar(20)
  payment_mode payment_mode
  legacy_org_id bigint [note: 'easy_cicerone.organizacoes.id']
  legacy_tronco_id bigint [note: 'easy_cicerone.projetos_clientes_tronco.id']
  source_proposal_id uuid [note: 'ON DELETE SetNull']
  created_at timestamptz [not null, default: `now()`]
  updated_at timestamptz [not null, default: `now()`]
  deleted_at timestamptz

  indexes {
    (id, reseller_id) [unique, name: 'uq_customers_id_reseller']
    (reseller_id, document) [unique, name: 'uq_customers_document_live', note: 'partial WHERE document IS NOT NULL AND deleted_at IS NULL']
    (reseller_id, kind) [name: 'idx_customers_kind', note: 'partial unique (reseller_id) WHERE kind=reseller_self AND deleted_at IS NULL']
    (reseller_id, status) [name: 'idx_customers_reseller_status']
    legacy_org_id [unique, name: 'uq_customers_legacy_org', note: 'partial WHERE legacy_org_id IS NOT NULL']
    legacy_tronco_id [unique, name: 'uq_customers_legacy_tronco', note: 'partial WHERE legacy_tronco_id IS NOT NULL']
  }

  Note: 'kind=reseller_self is the reseller own-usage subscriber used in VC1 minimum aggregate.'
}

Table customers.addresses {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [not null, note: 'ON DELETE Restrict']
  customer_id uuid [not null, note: 'ON DELETE Cascade']
  kind varchar(16) [not null, default: 'billing', note: 'billing|main']
  zip varchar(8)
  street varchar(255)
  number varchar(32)
  complement varchar(128)
  neighborhood varchar(128)
  city varchar(128)
  state char(2)
  ibge_city_code varchar(7)
  is_primary boolean [not null, default: true]
  created_at timestamptz [not null, default: `now()`]
  updated_at timestamptz [not null, default: `now()`]
  deleted_at timestamptz

  indexes {
    (reseller_id, customer_id) [name: 'idx_addresses_customer']
  }
}

Table customers.contacts {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [not null, note: 'ON DELETE Restrict']
  customer_id uuid [not null, note: 'ON DELETE Cascade']
  role contact_role [not null]
  full_name varchar(255) [not null]
  email varchar(255)
  phone varchar(20)
  document varchar(11) [note: 'CPF digits']
  created_at timestamptz [not null, default: `now()`]
  updated_at timestamptz [not null, default: `now()`]
  deleted_at timestamptz

  indexes {
    (reseller_id, customer_id, role) [unique, name: 'uq_contacts_role_live', note: 'partial WHERE deleted_at IS NULL']
    (reseller_id, email) [name: 'idx_contacts_email']
  }

  Note: 'Replaces sales-mework360 people + customer_people (one person per role per customer).'
}

Table customers.customer_users {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [not null, note: 'ON DELETE Restrict']
  customer_id uuid [not null, note: 'ON DELETE Cascade']
  user_id uuid [not null, note: 'ON DELETE Cascade']
  is_admin boolean [not null, default: false]
  created_at timestamptz [not null, default: `now()`]
  deleted_at timestamptz

  indexes {
    (customer_id, user_id) [unique, name: 'uq_customer_users']
    (reseller_id, user_id) [name: 'idx_customer_users_reseller']
  }
}

Table customers.subscriptions {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [not null, note: 'ON DELETE Restrict']
  customer_id uuid [not null, note: 'ON DELETE Restrict']
  plan_id uuid [not null, note: 'ON DELETE Restrict']
  plan_version_id uuid [not null, note: 'Frozen commercial terms. ON DELETE Restrict']
  status subscription_status [not null, default: 'trial']
  payment_mode payment_mode [not null]
  overage_landline_cents_per_min numeric(12,4)
  overage_mobile_cents_per_min numeric(12,4)
  did_count int [not null, default: 0]
  channel_count int [not null, default: 0]
  trial_ends_at timestamptz
  started_at timestamptz
  cancelled_at timestamptz
  cancel_reason text
  created_at timestamptz [not null, default: `now()`]
  updated_at timestamptz [not null, default: `now()`]
  deleted_at timestamptz

  indexes {
    (id, reseller_id) [unique, name: 'uq_subscriptions_id_reseller']
    (reseller_id, status) [name: 'idx_subscriptions_reseller_status']
    (reseller_id, customer_id) [name: 'idx_subscriptions_customer']
    (reseller_id, plan_version_id) [name: 'idx_subscriptions_plan_version']
  }

  Note: 'did_count/channel_count are denormalized live totals; source of truth is telco.dids / channel_allocations.'
}

Table customers.portability_requests {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [not null, note: 'ON DELETE Restrict']
  customer_id uuid [not null, note: 'ON DELETE Restrict']
  subscription_id uuid [note: 'ON DELETE SetNull']
  numbers jsonb [not null, default: `'[]'`]
  donor_operator varchar(128)
  status varchar(32) [not null, default: 'draft']
  legacy_portability_id bigint [note: 'projetos_tronco_portabilidade.id']
  submitted_at timestamptz
  completed_at timestamptz
  created_at timestamptz [not null, default: `now()`]
  updated_at timestamptz [not null, default: `now()`]
  deleted_at timestamptz

  indexes {
    (reseller_id, customer_id) [name: 'idx_portability_customer']
    (reseller_id, status) [name: 'idx_portability_status']
    (id, reseller_id) [unique, name: 'uq_customers_portability_requests_id_reseller']
  }
}

Table customers.portability_files {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [not null, note: 'ON DELETE Restrict']
  customer_id uuid [not null, note: 'ON DELETE Restrict']
  portability_request_id uuid [not null, note: 'ON DELETE Cascade']
  file_id uuid [not null, note: 'ON DELETE Restrict']
  retained_until date [not null, note: 'LGPD TTL; private disk only — never public folder']
  created_at timestamptz [not null, default: `now()`]
  deleted_at timestamptz

  indexes {
    (reseller_id, portability_request_id) [name: 'idx_portability_files']
    retained_until [name: 'idx_portability_files_ttl']
  }
}

Ref: customers.customers.reseller_id > tenancy.resellers.id
Ref: customers.customers.(source_proposal_id, reseller_id) > sales.proposals.(id, reseller_id)
Ref: customers.addresses.reseller_id > tenancy.resellers.id
Ref: customers.addresses.(customer_id, reseller_id) > customers.customers.(id, reseller_id)
Ref: customers.contacts.reseller_id > tenancy.resellers.id
Ref: customers.contacts.(customer_id, reseller_id) > customers.customers.(id, reseller_id)
Ref: customers.customer_users.reseller_id > tenancy.resellers.id
Ref: customers.customer_users.(customer_id, reseller_id) > customers.customers.(id, reseller_id)
Ref: customers.customer_users.user_id > identity.users.id
Ref: customers.subscriptions.reseller_id > tenancy.resellers.id
Ref: customers.subscriptions.(customer_id, reseller_id) > customers.customers.(id, reseller_id)
Ref: customers.subscriptions.(plan_id, reseller_id) > catalog.plans.(id, reseller_id)
Ref: customers.subscriptions.(plan_version_id, reseller_id) > catalog.plan_versions.(id, reseller_id)
Ref: customers.portability_requests.reseller_id > tenancy.resellers.id
Ref: customers.portability_requests.(customer_id, reseller_id) > customers.customers.(id, reseller_id)
Ref: customers.portability_requests.(subscription_id, reseller_id) > customers.subscriptions.(id, reseller_id)
Ref: customers.portability_files.reseller_id > tenancy.resellers.id
Ref: customers.portability_files.(customer_id, reseller_id) > customers.customers.(id, reseller_id)
Ref: customers.portability_files.(portability_request_id, reseller_id) > customers.portability_requests.(id, reseller_id)
Ref: customers.portability_files.file_id > files.objects.id

// ---------------------------------------------------------------------------
// sales
// ---------------------------------------------------------------------------

Table sales.proposals {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [not null, note: 'ON DELETE Restrict']
  customer_id uuid [note: 'ON DELETE SetNull']
  seller_user_id uuid [note: 'ON DELETE SetNull']
  public_code varchar(80) [not null, note: 'Global random base32 >=10 chars; path /p/{code}']
  proposal_number varchar(32) [not null]
  stage proposal_stage [not null, default: 'draft']
  kanban_column kanban_column [not null, default: 'lead']
  profile_type profile_type [not null]
  document varchar(14)
  legal_name varchar(255)
  contact_name varchar(255)
  contact_email varchar(255)
  contact_phone varchar(20)
  plan_id uuid [note: 'ON DELETE Restrict']
  plan_version_id uuid [note: 'ON DELETE Restrict']
  payment_mode payment_mode
  snapshot jsonb [note: 'IMMUTABLE after stage leaves draft: prices, quotas, branding, legal']
  public_access_username varchar(32)
  public_access_password_hash varchar(255)
  public_access_attempts smallint [not null, default: 0]
  public_access_blocked_until timestamptz
  pin_hash varchar(255) [note: '6-digit PIN bcrypt']
  views_count int [not null, default: 0]
  viewed_at timestamptz
  sent_at timestamptz
  accepted_at timestamptz
  expires_at timestamptz
  contracted_at timestamptz
  lost_reason text
  created_at timestamptz [not null, default: `now()`]
  updated_at timestamptz [not null, default: `now()`]
  deleted_at timestamptz

  indexes {
    public_code [unique, name: 'uq_proposals_public_code_global', note: 'partial WHERE deleted_at IS NULL']
    (reseller_id, proposal_number) [unique, name: 'uq_proposals_number']
    (reseller_id, stage) [name: 'idx_proposals_stage']
    (reseller_id, kanban_column) [name: 'idx_proposals_kanban']
    (reseller_id, seller_user_id) [name: 'idx_proposals_seller']
    (reseller_id, customer_id) [name: 'idx_proposals_customer']
    (id, reseller_id) [unique, name: 'uq_sales_proposals_id_reseller']
  }

  Note: 'Fork of public.proposals. Totals NEVER trusted from the browser — snapshot is server-priced. After sent, snapshot is immutable.'
}

Table sales.proposal_items {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [not null, note: 'ON DELETE Restrict']
  proposal_id uuid [not null, note: 'ON DELETE Cascade']
  kind invoice_line_kind [not null]
  quantity int [not null, default: 1]
  unit_sell_cents bigint [not null, default: 0]
  unit_cost_cents bigint [not null, default: 0]
  label varchar(255) [not null]
  metadata jsonb [not null, default: `'{}'`]
  created_at timestamptz [not null, default: `now()`]

  indexes {
    (reseller_id, proposal_id) [name: 'idx_proposal_items_proposal']
  }

  Note: 'Line snapshot at emit. Frozen with parent snapshot jsonb.'
}

Table sales.proposal_events {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [not null, note: 'ON DELETE Restrict']
  proposal_id uuid [not null, note: 'ON DELETE Cascade']
  event_type varchar(64) [not null]
  payload jsonb
  actor_type varchar(20) [not null, note: 'system|operator|customer']
  actor_user_id uuid [note: 'ON DELETE SetNull']
  created_at timestamptz [not null, default: `now()`]

  indexes {
    (reseller_id, proposal_id, created_at) [name: 'idx_proposal_events_timeline']
  }

  Note: 'Append-only. No updated_at / deleted_at.'
}

Table sales.trials {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [not null, note: 'ON DELETE Restrict']
  proposal_id uuid [not null, note: 'ON DELETE Restrict']
  customer_id uuid [not null, note: 'ON DELETE Restrict']
  subscription_id uuid [note: 'ON DELETE SetNull']
  plan_id uuid [not null, note: 'Denormalized; ON DELETE Restrict']
  plan_version_id uuid [not null, note: 'ON DELETE Restrict']
  status trial_status [not null, default: 'pending']
  starts_at timestamptz
  ends_at timestamptz
  franchise_landline_seconds int [not null, default: 0]
  franchise_mobile_seconds int [not null, default: 0]
  converted_at timestamptz
  created_at timestamptz [not null, default: `now()`]
  updated_at timestamptz [not null, default: `now()`]

  indexes {
    (reseller_id, status) [name: 'idx_trials_status']
    (reseller_id, proposal_id) [unique, name: 'uq_trials_proposal']
    (reseller_id, customer_id, plan_id) [unique, name: 'uq_trials_one_per_customer_plan']
    (id, reseller_id) [unique, name: 'uq_sales_trials_id_reseller']
  }
}

Table sales.contracts {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [not null, note: 'ON DELETE Restrict']
  proposal_id uuid [not null, unique, note: 'ON DELETE Restrict']
  customer_id uuid [not null, note: 'ON DELETE Restrict']
  subscription_id uuid [note: 'ON DELETE SetNull']
  status contract_status [not null, default: 'draft']
  issuer invoice_issuer [not null, note: 'vc1 if billing_mode=vc1_invoices']
  snapshot jsonb [not null, note: 'IMMUTABLE legal+commercial freeze including L1 support clause']
  effective_at timestamptz
  signed_at timestamptz
  cancelled_at timestamptz
  created_at timestamptz [not null, default: `now()`]
  updated_at timestamptz [not null, default: `now()`]
  deleted_at timestamptz

  indexes {
    (reseller_id, status) [name: 'idx_contracts_status']
    (reseller_id, customer_id) [name: 'idx_contracts_customer']
    (id, reseller_id) [unique, name: 'uq_sales_contracts_id_reseller']
  }

  Note: 'Replaces public.sales as the signed commercial artifact. Attestation lives on signature_envelopes.'
}

Table sales.signature_envelopes {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [not null, note: 'ON DELETE Restrict']
  contract_id uuid [not null, note: 'ON DELETE Restrict']
  mesign_envelope_id varchar(64) [not null]
  document_sha256 varchar(64)
  status varchar(32) [not null, default: 'sent']
  sign_url text
  webhook_last_event varchar(64)
  completed_at timestamptz
  archived_at timestamptz
  created_at timestamptz [not null, default: `now()`]
  updated_at timestamptz [not null, default: `now()`]

  indexes {
    mesign_envelope_id [unique, name: 'uq_signature_envelopes_mesign']
    (reseller_id, contract_id) [name: 'idx_signature_envelopes_contract']
  }
}

Ref: sales.proposals.reseller_id > tenancy.resellers.id
Ref: sales.proposals.(customer_id, reseller_id) > customers.customers.(id, reseller_id)
Ref: sales.proposals.seller_user_id > identity.users.id
Ref: sales.proposals.(plan_id, reseller_id) > catalog.plans.(id, reseller_id)
Ref: sales.proposals.(plan_version_id, reseller_id) > catalog.plan_versions.(id, reseller_id)
Ref: sales.proposal_items.reseller_id > tenancy.resellers.id
Ref: sales.proposal_items.(proposal_id, reseller_id) > sales.proposals.(id, reseller_id)
Ref: sales.proposal_events.reseller_id > tenancy.resellers.id
Ref: sales.proposal_events.(proposal_id, reseller_id) > sales.proposals.(id, reseller_id)
Ref: sales.proposal_events.actor_user_id > identity.users.id
Ref: sales.trials.reseller_id > tenancy.resellers.id
Ref: sales.trials.(proposal_id, reseller_id) > sales.proposals.(id, reseller_id)
Ref: sales.trials.(customer_id, reseller_id) > customers.customers.(id, reseller_id)
Ref: sales.trials.(subscription_id, reseller_id) > customers.subscriptions.(id, reseller_id)
Ref: sales.trials.(plan_version_id, reseller_id) > catalog.plan_versions.(id, reseller_id)
Ref: sales.trials.(plan_id, reseller_id) > catalog.plans.(id, reseller_id)
Ref: sales.contracts.reseller_id > tenancy.resellers.id
Ref: sales.contracts.(proposal_id, reseller_id) > sales.proposals.(id, reseller_id)
Ref: sales.contracts.(customer_id, reseller_id) > customers.customers.(id, reseller_id)
Ref: sales.contracts.(subscription_id, reseller_id) > customers.subscriptions.(id, reseller_id)
Ref: sales.signature_envelopes.reseller_id > tenancy.resellers.id
Ref: sales.signature_envelopes.(contract_id, reseller_id) > sales.contracts.(id, reseller_id)

// ---------------------------------------------------------------------------
// telco
// ---------------------------------------------------------------------------

Table telco.sip_accounts {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [not null, note: 'ON DELETE Restrict']
  subscription_id uuid [not null, note: 'ON DELETE Restrict']
  customer_id uuid [not null, note: 'ON DELETE Restrict']
  family sip_family [not null]
  username varchar(64) [not null, note: 'e.g. 75037219']
  sip_domain varchar(255) [not null]
  accountcode varchar(128) [not null, note: 'sippulse_reports.cdrs_report.accountcode']
  call_limit int [not null, default: 1]
  legacy_rate_plan_id int [note: 'SipPulse rate_plan id — network safety, not commercial rating']
  legacy_subscriber_ref varchar(128)
  blocked_at timestamptz
  block_reason block_reason
  created_at timestamptz [not null, default: `now()`]
  updated_at timestamptz [not null, default: `now()`]
  deleted_at timestamptz

  indexes {
    (reseller_id, username, sip_domain) [unique, name: 'uq_sip_accounts_user_domain_live', note: 'partial WHERE deleted_at IS NULL']
    (reseller_id, accountcode) [unique, name: 'uq_sip_accounts_accountcode_live', note: 'partial WHERE deleted_at IS NULL']
    (reseller_id, subscription_id) [name: 'idx_sip_accounts_subscription']
    (id, reseller_id) [unique, name: 'uq_telco_sip_accounts_id_reseller']
  }

  Note: 'Mirror of SipPulse subscriber. Password never stored (ProvisioningPort / generate_login_link).'
}

Table telco.dids {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [not null, note: 'ON DELETE Restrict']
  subscription_id uuid [not null, note: 'ON DELETE Restrict']
  customer_id uuid [not null, note: 'ON DELETE Restrict']
  e164 varchar(16) [not null]
  city varchar(128)
  area_code varchar(4)
  activated_at date [not null]
  deactivated_at date
  legacy_did_id bigint [note: 'projetos_tronco_numeros.id or SipPulse did.id']
  portability_request_id uuid [note: 'ON DELETE SetNull']
  created_at timestamptz [not null, default: `now()`]
  updated_at timestamptz [not null, default: `now()`]
  deleted_at timestamptz

  indexes {
    e164 [unique, name: 'uq_dids_e164_live', note: 'partial WHERE deleted_at IS NULL']
    (reseller_id, subscription_id) [name: 'idx_dids_subscription']
    (reseller_id, customer_id) [name: 'idx_dids_customer']
    (id, reseller_id) [unique, name: 'uq_telco_dids_id_reseller']
  }
}

Table telco.channel_allocations {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [not null, note: 'ON DELETE Restrict']
  subscription_id uuid [not null, unique, note: 'ON DELETE Restrict']
  sip_account_id uuid [not null, note: 'ON DELETE Restrict']
  quantity int [not null]
  effective_from date [not null]
  created_at timestamptz [not null, default: `now()`]
  updated_at timestamptz [not null, default: `now()`]

  indexes {
    (reseller_id, subscription_id) [name: 'idx_channel_alloc_sub']
    (id, reseller_id) [unique, name: 'uq_telco_channel_allocations_id_reseller']
  }

  Note: 'Current simultaneous call limit. History in channel_changes.'
}

Table telco.channel_changes {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [not null, note: 'ON DELETE Restrict']
  subscription_id uuid [not null, note: 'ON DELETE Restrict']
  kind channel_change_kind [not null]
  from_quantity int [not null]
  to_quantity int [not null]
  effective_on date [not null]
  pro_rata boolean [not null, default: true]
  actor_user_id uuid [note: 'ON DELETE SetNull']
  created_at timestamptz [not null, default: `now()`]

  indexes {
    (reseller_id, subscription_id, effective_on) [name: 'idx_channel_changes']
  }

  Note: 'Append-only. Mid-cycle adds bill remaining days / days_in_cycle.'
}

Table telco.block_events {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [not null, note: 'ON DELETE Restrict']
  subscription_id uuid [not null, note: 'ON DELETE Restrict']
  sip_account_id uuid [note: 'ON DELETE SetNull']
  reason block_reason [not null]
  action varchar(16) [not null, note: 'block|unblock']
  actor_user_id uuid [note: 'ON DELETE SetNull']
  connector_call_id uuid [note: 'ON DELETE SetNull']
  created_at timestamptz [not null, default: `now()`]

  indexes {
    (reseller_id, subscription_id, created_at) [name: 'idx_block_events']
  }

  Note: 'Append-only audit of TelcoPort blockSubscriber / alterar_status_boqueio.'
}

Ref: telco.sip_accounts.reseller_id > tenancy.resellers.id
Ref: telco.sip_accounts.(subscription_id, reseller_id) > customers.subscriptions.(id, reseller_id)
Ref: telco.sip_accounts.(customer_id, reseller_id) > customers.customers.(id, reseller_id)
Ref: telco.dids.reseller_id > tenancy.resellers.id
Ref: telco.dids.(subscription_id, reseller_id) > customers.subscriptions.(id, reseller_id)
Ref: telco.dids.(customer_id, reseller_id) > customers.customers.(id, reseller_id)
Ref: telco.dids.(portability_request_id, reseller_id) > customers.portability_requests.(id, reseller_id)
Ref: telco.channel_allocations.reseller_id > tenancy.resellers.id
Ref: telco.channel_allocations.(subscription_id, reseller_id) > customers.subscriptions.(id, reseller_id)
Ref: telco.channel_allocations.(sip_account_id, reseller_id) > telco.sip_accounts.(id, reseller_id)
Ref: telco.channel_changes.reseller_id > tenancy.resellers.id
Ref: telco.channel_changes.(subscription_id, reseller_id) > customers.subscriptions.(id, reseller_id)
Ref: telco.channel_changes.actor_user_id > identity.users.id
Ref: telco.block_events.reseller_id > tenancy.resellers.id
Ref: telco.block_events.(subscription_id, reseller_id) > customers.subscriptions.(id, reseller_id)
Ref: telco.block_events.(sip_account_id, reseller_id) > telco.sip_accounts.(id, reseller_id)
Ref: telco.block_events.actor_user_id > identity.users.id
Ref: telco.block_events.connector_call_id > integration.connector_calls.id

// ---------------------------------------------------------------------------
// rating
// ---------------------------------------------------------------------------

Table rating.cycles {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [not null, note: 'ON DELETE Restrict']
  period_start date [not null]
  period_end date [not null]
  status cycle_status [not null, default: 'open']
  closed_at timestamptz
  created_at timestamptz [not null, default: `now()`]
  updated_at timestamptz [not null, default: `now()`]

  indexes {
    (reseller_id, period_start) [unique, name: 'uq_cycles_reseller_month']
    (reseller_id, status) [name: 'idx_cycles_status']
    (id, reseller_id) [unique, name: 'uq_rating_cycles_id_reseller']
  }

  Note: 'Calendar month America/Sao_Paulo [period_start, period_end) for all resellers (D-DB-13).'
}

Table rating.destination_prefixes {
  id uuid [pk, default: `gen_random_uuid()`]
  prefix varchar(16) [not null]
  destination destination [not null]
  country_iso2 char(2) [not null, default: 'BR']
  description varchar(128)
  created_at timestamptz [not null, default: `now()`]
  deleted_at timestamptz

  indexes {
    (prefix) [unique, name: 'uq_destination_prefixes_live', note: 'partial WHERE deleted_at IS NULL']
    destination [name: 'idx_destination_prefixes_dest']
  }

  Note: 'Classifies B-number into landline_br / mobile_br / other. Prefix list A VERIFICAR (ANATEL).'
}

Table rating.cdr_imports {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [note: 'NULL for global poller batch. ON DELETE Restrict']
  source varchar(32) [not null, default: 'sippulse_reports']
  cursor_after_id bigint [note: 'Last sippulse_reports.cdrs_report.id ingested']
  cursor_after_time timestamptz
  rows_read int [not null, default: 0]
  rows_rated int [not null, default: 0]
  rows_skipped int [not null, default: 0]
  started_at timestamptz [not null, default: `now()`]
  finished_at timestamptz
  error text
  created_at timestamptz [not null, default: `now()`]

  indexes {
    (reseller_id, started_at) [name: 'idx_cdr_imports_reseller']
    (source, cursor_after_id) [name: 'idx_cdr_imports_cursor']
  }
}

Table rating.usage_records {
  id uuid [not null, default: `gen_random_uuid()`]
  reseller_id uuid [not null]
  subscription_id uuid [not null]
  customer_id uuid [not null]
  cycle_id uuid [not null]
  destination destination [not null]
  period_start date [not null, note: 'Partition key (month)']
  raw_seconds bigint [not null, default: 0]
  billed_seconds bigint [not null, default: 0, note: 'Sum of 30/6 billed seconds']
  call_count int [not null, default: 0]
  sell_cents bigint [not null, default: 0]
  cost_cents bigint [not null, default: 0]
  franchise_seconds_applied bigint [not null, default: 0]
  overage_seconds bigint [not null, default: 0]
  created_at timestamptz [not null, default: `now()`]
  updated_at timestamptz [not null, default: `now()`]

  indexes {
    (id, period_start) [pk]
    (reseller_id, cycle_id, subscription_id, destination) [unique, name: 'uq_usage_records_grain']
    (reseller_id, period_start) [name: 'idx_usage_records_reseller_month']
    (subscription_id, period_start) [name: 'idx_usage_records_sub']
  }

  Note: '''
    PARTITION BY RANGE (period_start) monthly.
    Grain: one row per subscription × destination × cycle.
    ON DELETE Restrict on FKs (logical; PG partitions use same).
  '''
}

Table rating.rated_calls {
  id uuid [not null, default: `gen_random_uuid()`]
  reseller_id uuid [not null]
  subscription_id uuid [not null]
  customer_id uuid [not null]
  sip_account_id uuid
  cycle_id uuid [not null]
  usage_record_id uuid
  started_at timestamptz [not null, note: 'Partition key']
  legacy_cdr_id bigint [not null, note: 'sippulse_reports.cdrs_report.id']
  accountcode varchar(128) [not null]
  source_number varchar(32)
  destination_number varchar(32)
  destination destination [not null]
  raw_seconds int [not null, note: 'cdrs_report.duration answered seconds; 30/6 applied here']
  billed_seconds int [not null]
  sell_rate_cents_per_min numeric(12,4) [not null]
  cost_rate_cents_per_min numeric(12,4) [not null]
  sell_amount_cents numeric(14,4) [not null]
  cost_amount_cents numeric(14,4) [not null]
  switch_price_cents bigint [note: 'cdrs_report.price audit only']
  switch_duration int [note: 'cdrs_report.duration raw']
  switch_duration_block int [note: 'cdrs_report.duration_block; never use for 30/6']
  service_type varchar(64) [note: 'cdrs_report.service_type']
  rated_at timestamptz [not null, default: `now()`]
  created_at timestamptz [not null, default: `now()`]

  indexes {
    (id, started_at) [pk]
    (legacy_cdr_id, started_at) [unique, name: 'uq_rated_calls_legacy_cdr']
    (reseller_id, started_at) [name: 'idx_rated_calls_reseller_time']
    (reseller_id, subscription_id, started_at) [name: 'idx_rated_calls_sub']
    (accountcode, started_at) [name: 'idx_rated_calls_accountcode']
  }

  Note: '''
    PARTITION BY RANGE (started_at) monthly. IMMUTABLE (append-only).
    Commercial rating from raw seconds + 30/6; do NOT reuse switch price as sell.
    switch_* kept for reconciliation (R5).
  '''
}

Ref: rating.cycles.reseller_id > tenancy.resellers.id
Ref: rating.cdr_imports.reseller_id > tenancy.resellers.id
Ref: rating.usage_records.reseller_id > tenancy.resellers.id
Ref: rating.usage_records.(subscription_id, reseller_id) > customers.subscriptions.(id, reseller_id)
Ref: rating.usage_records.(customer_id, reseller_id) > customers.customers.(id, reseller_id)
Ref: rating.usage_records.(cycle_id, reseller_id) > rating.cycles.(id, reseller_id)
Ref: rating.rated_calls.reseller_id > tenancy.resellers.id
Ref: rating.rated_calls.(subscription_id, reseller_id) > customers.subscriptions.(id, reseller_id)
Ref: rating.rated_calls.(customer_id, reseller_id) > customers.customers.(id, reseller_id)
Ref: rating.rated_calls.(sip_account_id, reseller_id) > telco.sip_accounts.(id, reseller_id)
Ref: rating.rated_calls.(cycle_id, reseller_id) > rating.cycles.(id, reseller_id)

// ---------------------------------------------------------------------------
// billing
// ---------------------------------------------------------------------------

Table billing.credit_wallets {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [not null, note: 'ON DELETE Restrict']
  customer_id uuid [not null, note: 'ON DELETE Restrict']
  subscription_id uuid [note: 'ON DELETE SetNull']
  balance_cents numeric(14,4) [not null, default: 0]
  version int [not null, default: 0, note: 'Optimistic lock beside SELECT FOR UPDATE']
  created_at timestamptz [not null, default: `now()`]
  updated_at timestamptz [not null, default: `now()`]

  indexes {
    (reseller_id, customer_id) [unique, name: 'uq_credit_wallets_customer']
    (reseller_id) [name: 'idx_credit_wallets_reseller']
    (id, reseller_id) [unique, name: 'uq_billing_credit_wallets_id_reseller']
  }

  Note: 'Prepaid SoT. CHECK balance_cents >= 0. Concurrency: SELECT ... FOR UPDATE (D-DB-03). Wallet stores numeric(14,4) cents (D-DB-02).'
}

Table billing.credit_ledger {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [not null, note: 'ON DELETE Restrict']
  wallet_id uuid [not null, note: 'ON DELETE Restrict']
  customer_id uuid [not null, note: 'ON DELETE Restrict']
  entry_type ledger_entry_type [not null]
  amount_cents numeric(14,4) [not null, note: 'Signed: deposit>0 debit<0']
  unrecovered_cents numeric(14,4) [not null, default: 0, note: 'Portion of debit not covered by wallet; absorbed/reported']
  balance_after_cents numeric(14,4) [not null]
  source_type varchar(32) [note: 'rated_call|payment|manual|cycle_expire']
  source_id uuid
  idempotency_key varchar(128) [not null]
  created_at timestamptz [not null, default: `now()`]

  indexes {
    (wallet_id, created_at) [name: 'idx_credit_ledger_wallet']
    (reseller_id, customer_id, created_at) [name: 'idx_credit_ledger_customer']
    (wallet_id, idempotency_key) [unique, name: 'uq_credit_ledger_idempotency']
  }

  Note: 'Append-only. No UPDATE/DELETE. Unique idempotency prevents double debit of a CDR.'
}

Table billing.credit_limits {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [not null, note: 'ON DELETE Restrict']
  customer_id uuid [not null, note: 'ON DELETE Restrict']
  subscription_id uuid [note: 'ON DELETE SetNull']
  limit_cents bigint [not null]
  alert_percent smallint [not null, default: 80]
  block_percent smallint [not null, default: 100]
  created_at timestamptz [not null, default: `now()`]
  updated_at timestamptz [not null, default: `now()`]
  deleted_at timestamptz

  indexes {
    (reseller_id, customer_id) [unique, name: 'uq_credit_limits_customer_live', note: 'partial WHERE deleted_at IS NULL']
    (id, reseller_id) [unique, name: 'uq_billing_credit_limits_id_reseller']
  }

  Note: 'Postpaid only. Exposure = DIDs+channels+minutes in the open cycle.'
}

Table billing.limit_events {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [not null, note: 'ON DELETE Restrict']
  credit_limit_id uuid [not null, note: 'ON DELETE Restrict']
  subscription_id uuid [not null, note: 'ON DELETE Restrict']
  threshold_percent smallint [not null, note: '80 or 100']
  action limit_action [not null]
  exposure_cents bigint [not null]
  limit_cents bigint [not null]
  fired_at timestamptz [not null, default: `now()`]
  created_at timestamptz [not null, default: `now()`]

  indexes {
    (reseller_id, subscription_id, threshold_percent, fired_at) [name: 'idx_limit_events']
  }

  Note: 'Append-only. Alert at 80% (reseller+VC1), block at 100%.'
}

Table billing.payment_methods {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [not null, note: 'ON DELETE Restrict']
  party invoice_party [not null]
  customer_id uuid [note: 'ON DELETE Cascade when party=customer']
  kind payment_method_kind [not null]
  mepay_ref varchar(64)
  label varchar(128)
  is_default boolean [not null, default: false]
  created_at timestamptz [not null, default: `now()`]
  updated_at timestamptz [not null, default: `now()`]
  deleted_at timestamptz

  indexes {
    (reseller_id, party, customer_id) [name: 'idx_payment_methods_party']
  }
}

Table billing.invoices {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [not null, note: 'ON DELETE Restrict']
  cycle_id uuid [not null, note: 'ON DELETE Restrict']
  party invoice_party [not null]
  customer_id uuid [note: 'Required if party=customer. ON DELETE Restrict']
  subscription_id uuid [not null, note: 'Required when party=customer. ON DELETE Restrict']
  issuer invoice_issuer [not null]
  status invoice_status [not null, default: 'draft']
  number varchar(32)
  subtotal_cents bigint [not null, default: 0]
  commitment_cents bigint [not null, default: 0]
  charged_cents bigint [not null, default: 0, note: 'max(subtotal, commitment) for commitment plans']
  cost_cents bigint [not null, default: 0]
  margin_cents bigint [not null, default: 0]
  due_on date
  issued_at timestamptz
  paid_at timestamptz
  cancelled_at timestamptz
  mepay_invoice_id varchar(64) [note: 'mePay invoice id (inv_...)']
  mepay_charge_id varchar(64) [note: 'Latest charge on invoice; mirrors billing.payments when paid']
  payment_link_url text [note: 'Hosted checkout / boleto / Pix link from mePay charge']
  replaces_invoice_id uuid [note: 'Reissue chain. ON DELETE SetNull']
  snapshot jsonb [note: 'Header freeze at issue']
  created_at timestamptz [not null, default: `now()`]
  updated_at timestamptz [not null, default: `now()`]
  deleted_at timestamptz

  indexes {
    (id, reseller_id) [unique, name: 'uq_invoices_id_reseller']
    (cycle_id, reseller_id) [unique, name: 'uq_invoices_reseller_cycle', note: 'partial WHERE party=reseller AND status NOT IN (cancelled,reissued)']
    (cycle_id, subscription_id) [unique, name: 'uq_invoices_customer_cycle_sub', note: 'partial WHERE party=customer AND status NOT IN (cancelled,reissued)']
    (reseller_id, status) [name: 'idx_invoices_status']
    (reseller_id, customer_id) [name: 'idx_invoices_customer']
    (reseller_id, number) [unique, name: 'uq_invoices_number_live', note: 'partial WHERE number IS NOT NULL AND deleted_at IS NULL']
  }

  Note: '''
    CHECK NOT (party=customer AND issuer=reseller).
    party=customer + issuer=vc1 when billing_mode=vc1_invoices.
    reseller_invoices: VC1 only produces billing.statements for end customers, not invoices.
    party=reseller is always issuer=vc1 (VC1 bills the reseller).
    Reissue: new row with replaces_invoice_id; cancelled/reissued rows excluded from partial uniques uq_invoices_reseller_cycle and uq_invoices_customer_cycle_sub.
    After status=issued, monetary columns are immutable; cancel issues a new reversing invoice.
  '''
}

Table billing.invoice_lines {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [not null, note: 'ON DELETE Restrict']
  invoice_id uuid [not null, note: 'ON DELETE Restrict (never cascade money)']
  kind invoice_line_kind [not null]
  description varchar(255) [not null]
  quantity numeric(14,4) [not null, default: 1]
  unit_sell_cents bigint [not null, default: 0]
  amount_cents bigint [not null]
  cost_cents bigint [not null, default: 0]
  destination destination
  snapshot jsonb [not null, note: 'IMMUTABLE freeze of quantities, rates, pro-rata fraction']
  created_at timestamptz [not null, default: `now()`]

  indexes {
    (reseller_id, invoice_id) [name: 'idx_invoice_lines_invoice']
  }

  Note: 'IMMUTABLE. No updated_at. Insert-only; corrections via credit invoice.'
}

Table billing.statements {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [not null, note: 'ON DELETE Restrict']
  cycle_id uuid [not null, note: 'ON DELETE Restrict']
  customer_id uuid [not null, note: 'ON DELETE Restrict']
  subscription_id uuid [note: 'ON DELETE SetNull']
  status statement_status [not null, default: 'draft']
  subtotal_cents bigint [not null, default: 0]
  charged_cents bigint [not null, default: 0]
  cost_cents bigint [not null, default: 0]
  snapshot jsonb [not null]
  issued_at timestamptz
  created_at timestamptz [not null, default: `now()`]
  updated_at timestamptz [not null, default: `now()`]

  indexes {
    (reseller_id, cycle_id, subscription_id) [unique, name: 'uq_statements_cycle_subscription']
    (id, reseller_id) [unique, name: 'uq_billing_statements_id_reseller']
  }

  Note: 'Rated statement for reseller_invoices mode. VC1 does not issue NF to that customer.'
}

Table billing.payments {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [not null, note: 'ON DELETE Restrict']
  invoice_id uuid [not null, note: 'ON DELETE Restrict']
  payment_method_id uuid [note: 'ON DELETE SetNull']
  amount_cents bigint [not null]
  kind payment_method_kind [not null]
  mepay_charge_id varchar(64)
  paid_at timestamptz [not null]
  raw_payload jsonb
  created_at timestamptz [not null, default: `now()`]

  indexes {
    (reseller_id, invoice_id) [name: 'idx_payments_invoice']
    mepay_charge_id [unique, name: 'uq_payments_mepay', note: 'partial WHERE mepay_charge_id IS NOT NULL']
  }

  Note: 'Only invoices actually paid count toward payout margin and (PADRÃO PROPOSTO) reseller minimum in vc1_invoices.'
}

Table billing.dunning_events {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [not null, note: 'ON DELETE Restrict']
  invoice_id uuid [not null, note: 'ON DELETE Restrict']
  kind dunning_kind [not null]
  payload jsonb
  fired_at timestamptz [not null, default: `now()`]
  created_at timestamptz [not null, default: `now()`]

  indexes {
    (reseller_id, invoice_id, fired_at) [name: 'idx_dunning_events']
  }
}

Table billing.cycle_runs {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [note: 'NULL = platform-wide run. ON DELETE Restrict']
  cycle_id uuid [note: 'ON DELETE Restrict']
  phase varchar(32) [not null, note: 'import|rate|aggregate|invoice|payout']
  status varchar(16) [not null, default: 'running']
  started_at timestamptz [not null, default: `now()`]
  finished_at timestamptz
  stats jsonb [not null, default: `'{}'`]
  error text
  created_at timestamptz [not null, default: `now()`]

  indexes {
    (cycle_id, phase) [unique, name: 'uq_cycle_runs_phase', note: 'reseller_id NULL = platform run']
    (reseller_id, cycle_id, phase, started_at) [name: 'idx_cycle_runs']
  }
}

Table billing.payouts {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [not null, note: 'ON DELETE Restrict']
  cycle_id uuid [not null, note: 'ON DELETE Restrict']
  status payout_status [not null, default: 'calculating']
  paid_margin_cents bigint [not null, default: 0]
  shortfall_cents bigint [not null, default: 0]
  negative_margin_cents bigint [not null, default: 0]
  net_cents bigint [not null, default: 0, note: 'paid_margin_cents - shortfall_cents; negative_margin_cents informational only']
  netted_invoice_id uuid [note: 'When net_cents < 0. ON DELETE SetNull']
  commission_invoice_id uuid [note: 'ON DELETE SetNull']
  paid_at timestamptz
  paid_by_user_id uuid [note: 'Manual transfer until mePay payouts. ON DELETE SetNull']
  transfer_ref varchar(64)
  snapshot jsonb [not null, default: `'{}'`]
  created_at timestamptz [not null, default: `now()`]
  updated_at timestamptz [not null, default: `now()`]

  indexes {
    (reseller_id, cycle_id) [unique, name: 'uq_payouts_cycle']
    (reseller_id, status) [name: 'idx_payouts_status']
    (id, reseller_id) [unique, name: 'uq_billing_payouts_id_reseller']
  }

  Note: 'Only billing_mode=vc1_invoices. payout_raw=0 → status settled_zero (no commission/reseller invoice).'
}

Table billing.payout_adjustments {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [not null, note: 'ON DELETE Restrict']
  payout_id uuid [note: 'Target payout cycle. ON DELETE Restrict']
  source_type varchar(32) [not null, note: 'refund|chargeback|manual']
  source_id uuid
  amount_cents bigint [not null, note: 'Negative reduces payout; rolls to reseller invoice if still negative']
  snapshot jsonb [not null, default: `'{}'`]
  created_at timestamptz [not null, default: `now()`]

  indexes {
    (reseller_id, payout_id) [name: 'idx_payout_adjustments_payout']
  }

  Note: 'Settlement ledger for post-payout refunds/chargebacks (D-DOM-73).'
}

Table billing.payout_lines {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [not null, note: 'ON DELETE Restrict']
  payout_id uuid [not null, note: 'ON DELETE Restrict']
  kind payout_line_kind [not null]
  invoice_id uuid [note: 'ON DELETE SetNull']
  amount_cents bigint [not null]
  snapshot jsonb [not null, default: `'{}'`]
  created_at timestamptz [not null, default: `now()`]

  indexes {
    (reseller_id, payout_id) [name: 'idx_payout_lines']
  }

  Note: 'IMMUTABLE breakdown of the payout formula.'
}

Table billing.commission_invoices {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [not null, note: 'ON DELETE Restrict']
  payout_id uuid [not null, unique, note: 'ON DELETE Restrict']
  number varchar(64)
  amount_cents bigint [not null]
  file_id uuid [note: 'ON DELETE SetNull']
  received_at timestamptz
  created_at timestamptz [not null, default: `now()`]
  updated_at timestamptz [not null, default: `now()`]

  indexes {
    (reseller_id) [name: 'idx_commission_invoices_reseller']
    (id, reseller_id) [unique, name: 'uq_billing_commission_invoices_id_reseller']
  }

  Note: 'NF of commission issued BY the reseller TO VC1. Blocks payout until received.'
}

Ref: billing.credit_wallets.reseller_id > tenancy.resellers.id
Ref: billing.credit_wallets.(customer_id, reseller_id) > customers.customers.(id, reseller_id)
Ref: billing.credit_wallets.(subscription_id, reseller_id) > customers.subscriptions.(id, reseller_id)
Ref: billing.credit_ledger.reseller_id > tenancy.resellers.id
Ref: billing.credit_ledger.(wallet_id, reseller_id) > billing.credit_wallets.(id, reseller_id)
Ref: billing.credit_ledger.(customer_id, reseller_id) > customers.customers.(id, reseller_id)
Ref: billing.credit_limits.reseller_id > tenancy.resellers.id
Ref: billing.credit_limits.(customer_id, reseller_id) > customers.customers.(id, reseller_id)
Ref: billing.credit_limits.(subscription_id, reseller_id) > customers.subscriptions.(id, reseller_id)
Ref: billing.limit_events.reseller_id > tenancy.resellers.id
Ref: billing.limit_events.(credit_limit_id, reseller_id) > billing.credit_limits.(id, reseller_id)
Ref: billing.limit_events.(subscription_id, reseller_id) > customers.subscriptions.(id, reseller_id)
Ref: billing.payment_methods.reseller_id > tenancy.resellers.id
Ref: billing.payment_methods.(customer_id, reseller_id) > customers.customers.(id, reseller_id)
Ref: billing.invoices.reseller_id > tenancy.resellers.id
Ref: billing.invoices.(cycle_id, reseller_id) > rating.cycles.(id, reseller_id)
Ref: billing.invoices.(customer_id, reseller_id) > customers.customers.(id, reseller_id)
Ref: billing.invoices.(subscription_id, reseller_id) > customers.subscriptions.(id, reseller_id)
Ref: billing.invoice_lines.reseller_id > tenancy.resellers.id
Ref: billing.invoice_lines.(invoice_id, reseller_id) > billing.invoices.(id, reseller_id)
Ref: billing.statements.reseller_id > tenancy.resellers.id
Ref: billing.statements.(cycle_id, reseller_id) > rating.cycles.(id, reseller_id)
Ref: billing.statements.(customer_id, reseller_id) > customers.customers.(id, reseller_id)
Ref: billing.statements.(subscription_id, reseller_id) > customers.subscriptions.(id, reseller_id)
Ref: billing.payments.reseller_id > tenancy.resellers.id
Ref: billing.payments.(invoice_id, reseller_id) > billing.invoices.(id, reseller_id)
Ref: billing.payments.(payment_method_id, reseller_id) > billing.payment_methods.(id, reseller_id)
Ref: billing.dunning_events.reseller_id > tenancy.resellers.id
Ref: billing.dunning_events.(invoice_id, reseller_id) > billing.invoices.(id, reseller_id)
Ref: billing.cycle_runs.reseller_id > tenancy.resellers.id
Ref: billing.cycle_runs.(cycle_id, reseller_id) > rating.cycles.(id, reseller_id)
Ref: billing.payouts.reseller_id > tenancy.resellers.id
Ref: billing.payouts.(cycle_id, reseller_id) > rating.cycles.(id, reseller_id)
Ref: billing.payouts.(netted_invoice_id, reseller_id) > billing.invoices.(id, reseller_id)
Ref: billing.payouts.commission_invoice_id > billing.commission_invoices.id
Ref: billing.payouts.paid_by_user_id > identity.users.id
Ref: billing.payout_adjustments.reseller_id > tenancy.resellers.id
Ref: billing.payout_adjustments.(payout_id, reseller_id) > billing.payouts.(id, reseller_id)
Ref: billing.payout_lines.reseller_id > tenancy.resellers.id
Ref: billing.payout_lines.(payout_id, reseller_id) > billing.payouts.(id, reseller_id)
Ref: billing.payout_lines.(invoice_id, reseller_id) > billing.invoices.(id, reseller_id)
Ref: billing.commission_invoices.reseller_id > tenancy.resellers.id
Ref: billing.commission_invoices.(payout_id, reseller_id) > billing.payouts.(id, reseller_id)
Ref: billing.commission_invoices.file_id > files.objects.id

// ---------------------------------------------------------------------------
// fiscal
// ---------------------------------------------------------------------------

Table fiscal.fiscal_documents {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [not null, note: 'ON DELETE Restrict']
  invoice_id uuid [not null, note: 'ON DELETE Restrict']
  status fiscal_document_status [not null, default: 'pending']
  model varchar(8) [not null, default: '62', note: 'NFCom model 62']
  issuer_cnpj varchar(14) [not null, note: 'RLGR 57675896000126 when VC1 issues; reseller CNPJ when revenda emits']
  mepay_invoice_id varchar(64) [note: 'Copy of billing.invoices.mepay_invoice_id for traceability']
  mepay_fiscal_request_id varchar(64) [note: 'mePay async fiscal request id']
  access_key varchar(44) [note: '44-digit SEFAZ key when status=issued']
  protocol varchar(64)
  pdf_file_id uuid [note: 'DANFE/PDF in files.objects. ON DELETE SetNull']
  xml_file_id uuid [note: 'ON DELETE SetNull']
  authorized_at timestamptz [note: 'Set when status moves to issued (legacy name kept)']
  created_at timestamptz [not null, default: `now()`]
  updated_at timestamptz [not null, default: `now()`]

  indexes {
    (reseller_id, invoice_id) [name: 'idx_fiscal_documents_invoice']
    (invoice_id) [unique, name: 'uq_fiscal_active_per_invoice', note: 'partial WHERE status NOT IN (cancelled)']
    access_key [unique, name: 'uq_fiscal_access_key', note: 'partial WHERE access_key IS NOT NULL']
    (reseller_id, status) [name: 'idx_fiscal_documents_status']
    (id, reseller_id) [unique, name: 'uq_fiscal_fiscal_documents_id_reseller']
  }

  Note: 'issued ONLY after mePay webhook fiscal.issued (or poll fallback) with 44-digit access_key. Never mark issued on adhoc import alone.'
}

Table fiscal.fiscal_attempts {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [not null, note: 'ON DELETE Restrict']
  fiscal_document_id uuid [not null, note: 'ON DELETE Cascade']
  endpoint varchar(64) [not null, note: 'mepay_request_fiscal|mepay_fiscal_status|mepay_fiscal_download']
  http_status int
  request_hash varchar(64)
  response jsonb
  success boolean [not null, default: false]
  created_at timestamptz [not null, default: `now()`]

  indexes {
    (reseller_id, fiscal_document_id, created_at) [name: 'idx_fiscal_attempts']
  }

  Note: 'Append-only. Do not store mePay API keys or third-party issuer secrets.'
}

Ref: fiscal.fiscal_documents.reseller_id > tenancy.resellers.id
Ref: fiscal.fiscal_documents.(invoice_id, reseller_id) > billing.invoices.(id, reseller_id)
Ref: fiscal.fiscal_documents.pdf_file_id > files.objects.id
Ref: fiscal.fiscal_documents.xml_file_id > files.objects.id
Ref: fiscal.fiscal_attempts.reseller_id > tenancy.resellers.id
Ref: fiscal.fiscal_attempts.(fiscal_document_id, reseller_id) > fiscal.fiscal_documents.(id, reseller_id)

// ---------------------------------------------------------------------------
// integration
// ---------------------------------------------------------------------------

Table integration.outbox {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [note: 'ON DELETE Restrict']
  aggregate_type varchar(64) [not null]
  aggregate_id uuid [not null]
  event_name varchar(128) [not null]
  payload jsonb [not null]
  status outbox_status [not null, default: 'pending']
  attempts int [not null, default: 0]
  available_at timestamptz [not null, default: `now()`]
  published_at timestamptz
  last_error text
  created_at timestamptz [not null, default: `now()`]

  indexes {
    (status, available_at) [name: 'idx_outbox_poll']
    (reseller_id, aggregate_id) [name: 'idx_outbox_aggregate']
  }
}

Table integration.inbox_webhooks {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [note: 'ON DELETE Restrict']
  source varchar(32) [not null, note: 'mesign|mepay|dns']
  event_name varchar(128) [not null]
  idempotency_key varchar(128) [not null]
  status webhook_status [not null, default: 'received']
  headers jsonb
  payload jsonb [not null]
  processed_at timestamptz
  created_at timestamptz [not null, default: `now()`]

  indexes {
    (source, idempotency_key) [unique, name: 'uq_inbox_webhooks_idem']
    (reseller_id, created_at) [name: 'idx_inbox_webhooks_reseller']
  }

  Note: 'Dedupe: provider event_id when present; else sha256(envelope_id + event + payload) (D-INT-23). meSign timestamp/event_id VERIFICAR.'
}

Table integration.connector_calls {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [note: 'ON DELETE Restrict']
  port varchar(32) [not null, note: 'ProvisioningPort|TelcoPort|CdrPort|PaymentPort|FiscalPort|SignaturePort|DnsPort|NotificationPort']
  adapter varchar(64) [not null]
  method varchar(64) [not null]
  request_hash varchar(64)
  response_code int
  success boolean [not null, default: false]
  lab_guarded boolean [not null, default: false]
  error text
  duration_ms int
  created_at timestamptz [not null, default: `now()`]

  indexes {
    (reseller_id, port, created_at) [name: 'idx_connector_calls']
    (port, created_at) [name: 'idx_connector_calls_port']
  }

  Note: 'Never store SOAP passwords or API tokens. LabGuarded* sets lab_guarded=true.'
}

Table integration.legacy_id_map {
  id uuid [pk, default: `gen_random_uuid()`]
  entity_type varchar(64) [not null]
  legacy_id bigint [not null]
  entity_uuid uuid [not null]
  legacy_table varchar(128) [not null]
  created_at timestamptz [not null, default: `now()`]

  indexes {
    (entity_type, legacy_id) [unique, name: 'uq_legacy_id_map']
    entity_uuid [name: 'idx_legacy_id_map_uuid']
  }
}

Table integration.reconciliation_runs {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [note: 'ON DELETE Restrict']
  cycle_id uuid [note: 'ON DELETE Restrict']
  status reconciliation_status [not null, default: 'running']
  billed_seconds_sum bigint
  switch_duration_block_sum bigint
  sell_cents_sum bigint
  switch_price_cents_sum bigint
  drift_percent numeric(8,4)
  started_at timestamptz [not null, default: `now()`]
  finished_at timestamptz
  report jsonb
  created_at timestamptz [not null, default: `now()`]

  indexes {
    (reseller_id, cycle_id, started_at) [name: 'idx_reconciliation_runs']
  }
}

Table integration.lab_allowlist {
  id uuid [pk, default: `gen_random_uuid()`]
  kind varchar(32) [not null, note: 'legacy_project|legacy_org|sip_accountcode|did']
  legacy_id bigint
  accountcode varchar(128)
  e164 varchar(16)
  notes text
  created_at timestamptz [not null, default: `now()`]
  deleted_at timestamptz

  indexes {
    (kind, legacy_id) [unique, name: 'uq_lab_allowlist_legacy', note: 'partial WHERE legacy_id IS NOT NULL AND deleted_at IS NULL']
    (kind, accountcode) [unique, name: 'uq_lab_allowlist_accountcode', note: 'partial WHERE accountcode IS NOT NULL AND deleted_at IS NULL']
  }

  Note: 'LabGuarded* adapters refuse writes outside this list. Demo WL project + demo SIP subscribers only.'
}

Ref: integration.outbox.reseller_id > tenancy.resellers.id
Ref: integration.inbox_webhooks.reseller_id > tenancy.resellers.id
Ref: integration.connector_calls.reseller_id > tenancy.resellers.id
Ref: integration.reconciliation_runs.reseller_id > tenancy.resellers.id
Ref: integration.reconciliation_runs.(cycle_id, reseller_id) > rating.cycles.(id, reseller_id)

// ---------------------------------------------------------------------------
// audit
// ---------------------------------------------------------------------------

Table identity.impersonation_sessions {
  id uuid [pk, default: `gen_random_uuid()`]
  staff_user_id uuid [not null, note: 'ON DELETE Cascade']
  impersonated_user_id uuid [not null, note: 'ON DELETE Cascade']
  reseller_id uuid [note: 'ON DELETE SetNull']
  expires_at timestamptz [not null, note: 'TTL 30 min from start']
  ended_at timestamptz
  created_at timestamptz [not null, default: `now()`]

  indexes {
    (staff_user_id, created_at) [name: 'idx_impersonation_staff']
    expires_at [name: 'idx_impersonation_expires']
  }
}

Table audit.audit_log {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [note: 'ON DELETE Restrict']
  actor_user_id uuid [note: 'ON DELETE SetNull']
  impersonator_user_id uuid [note: 'Staff when impersonating']
  impersonation_session_id uuid [note: 'identity.impersonation_sessions.id']
  actor_scope actor_scope
  action varchar(128) [not null]
  auditable_type varchar(128) [not null]
  auditable_id uuid
  before jsonb
  after jsonb
  ip varchar(45)
  user_agent varchar(512)
  created_at timestamptz [not null, default: `now()`]

  indexes {
    (reseller_id, created_at) [name: 'idx_audit_log_reseller']
    (auditable_type, auditable_id) [name: 'idx_audit_log_auditable']
    (actor_user_id, created_at) [name: 'idx_audit_log_actor']
  }

  Note: 'Append-only. Financial/admin trail retention 5y; auth/access 12m (D-SEC-15).'
}

Ref: identity.impersonation_sessions.staff_user_id > identity.users.id
Ref: identity.impersonation_sessions.impersonated_user_id > identity.users.id
Ref: identity.impersonation_sessions.reseller_id > tenancy.resellers.id
Ref: audit.audit_log.impersonator_user_id > identity.users.id
Ref: audit.audit_log.impersonation_session_id > identity.impersonation_sessions.id

Ref: audit.audit_log.reseller_id > tenancy.resellers.id
Ref: audit.audit_log.actor_user_id > identity.users.id

// ---------------------------------------------------------------------------
// notifications
// ---------------------------------------------------------------------------

Table notifications.templates {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [note: 'NULL = VC1 default. ON DELETE Cascade']
  key varchar(64) [not null]
  channel notification_channel [not null]
  subject varchar(255)
  body text [not null]
  is_active boolean [not null, default: true]
  created_at timestamptz [not null, default: `now()`]
  updated_at timestamptz [not null, default: `now()`]
  deleted_at timestamptz

  indexes {
    (reseller_id, key, channel) [unique, name: 'uq_notification_templates', note: 'partial unique (key,channel) WHERE reseller_id IS NULL AND deleted_at IS NULL']
  }
}

Table notifications.messages {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [note: 'ON DELETE Restrict']
  customer_id uuid [note: 'ON DELETE SetNull']
  template_id uuid [note: 'ON DELETE SetNull']
  channel notification_channel [not null]
  recipient varchar(255) [not null]
  status varchar(16) [not null, default: 'queued']
  payload jsonb [not null, default: `'{}'`]
  provider_message_id varchar(128)
  error text
  sent_at timestamptz
  created_at timestamptz [not null, default: `now()`]

  indexes {
    (reseller_id, created_at) [name: 'idx_notification_messages']
    (status, created_at) [name: 'idx_notification_messages_status']
  }
}

Table notifications.opt_outs {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [note: 'ON DELETE Cascade']
  recipient varchar(255) [not null]
  channel notification_channel [not null]
  created_at timestamptz [not null, default: `now()`]

  indexes {
    (reseller_id, recipient, channel) [unique, name: 'uq_notification_opt_outs']
  }
}

Ref: notifications.templates.reseller_id > tenancy.resellers.id
Ref: notifications.messages.reseller_id > tenancy.resellers.id
Ref: notifications.messages.(customer_id, reseller_id) > customers.customers.(id, reseller_id)
Ref: notifications.messages.template_id > notifications.templates.id
Ref: notifications.opt_outs.reseller_id > tenancy.resellers.id

// ---------------------------------------------------------------------------
// files
// ---------------------------------------------------------------------------

Table files.objects {
  id uuid [pk, default: `gen_random_uuid()`]
  reseller_id uuid [note: 'NULL = VC1-owned. ON DELETE Restrict']
  purpose file_purpose [not null]
  disk varchar(32) [not null, default: 'private']
  path varchar(512) [not null]
  mime_type varchar(128) [not null]
  byte_size bigint [not null]
  sha256 varchar(64)
  original_name varchar(255)
  retained_until date
  created_by_user_id uuid [note: 'ON DELETE SetNull']
  created_at timestamptz [not null, default: `now()`]
  deleted_at timestamptz

  indexes {
    (reseller_id, purpose) [name: 'idx_files_reseller_purpose']
    retained_until [name: 'idx_files_retained_until']
    sha256 [name: 'idx_files_sha256']
  }

  Note: 'Private object storage only. Portability docs MUST set retained_until. Never public ACL.'
}

Ref: files.objects.reseller_id > tenancy.resellers.id
Ref: files.objects.created_by_user_id > identity.users.id