-- GPUaaS v1 physical schema draft (PostgreSQL)
-- API-first + phase-2 readiness + config-driven policy model

create extension if not exists pgcrypto;

create table if not exists platform_iam_organizations (
  id uuid primary key default gen_random_uuid(),
  type text not null default 'personal' check (type in ('personal','enterprise')),
  name text not null,
  slug text not null unique,
  default_currency text not null default 'USD' check (default_currency ~ '^[A-Z]{3}$'),
  stripe_customer_id text null,
  created_at timestamptz not null default now()
);

alter table if exists platform_iam_organizations
  add column if not exists default_currency text not null default 'USD';

do $$
begin
  if not exists (
    select 1 from pg_constraint
    where conname = 'organizations_default_currency_check'
      and conrelid = 'platform_iam_organizations'::regclass
  ) then
    alter table platform_iam_organizations
      add constraint organizations_default_currency_check
    check (default_currency ~ '^[A-Z]{3}$') not valid;
  end if;
end $$;

create table if not exists platform_iam_departments (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references platform_iam_organizations(id) on delete cascade,
  slug text not null,
  display_name text not null,
  is_default boolean not null default false,
  lifecycle_state text not null default 'active' check (lifecycle_state in ('active','disabled')),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (id, org_id)
);

create unique index if not exists ux_platform_iam_departments_org_slug
  on platform_iam_departments(org_id, slug);

create unique index if not exists ux_platform_iam_departments_org_default
  on platform_iam_departments(org_id)
  where is_default;

create table if not exists platform_iam_projects (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references platform_iam_organizations(id),
  department_id uuid null,
  name text not null,
  slug text not null,
  created_at timestamptz not null default now(),
  unique (org_id, slug),
  unique (id, org_id)
);

alter table if exists platform_iam_projects
  add column if not exists department_id uuid null;
alter table if exists platform_iam_projects
  add column if not exists deleted_at timestamptz null;
alter table if exists platform_iam_projects
  add column if not exists deleted_by_user_id uuid null;
alter table if exists platform_iam_projects
  add column if not exists delete_reason text null;

alter table if exists platform_iam_projects
  drop constraint if exists projects_org_id_slug_key;

create unique index if not exists ux_projects_org_slug_active
  on platform_iam_projects(org_id, slug)
  where deleted_at is null;

create index if not exists ix_platform_iam_projects_department
  on platform_iam_projects(department_id);

create or replace function platform_iam_assign_default_project_department()
returns trigger
language plpgsql
as $$
declare
  v_department_id uuid;
begin
  if new.department_id is null then
    select id into v_department_id
    from platform_iam_departments
    where org_id = new.org_id
      and is_default
      and lifecycle_state = 'active'
    limit 1;

    if v_department_id is null then
      insert into platform_iam_departments (org_id, slug, display_name, is_default, lifecycle_state)
      values (new.org_id, 'default', 'Default Department', true, 'active')
      on conflict (org_id, slug) do update
        set display_name = excluded.display_name,
            is_default = true,
            lifecycle_state = 'active',
            updated_at = now()
      returning id into v_department_id;
    end if;

    new.department_id := v_department_id;
  end if;

  if not exists (
    select 1
    from platform_iam_departments d
    where d.id = new.department_id
      and d.org_id = new.org_id
      and d.lifecycle_state = 'active'
  ) then
    raise exception 'project department must belong to project organization and be active'
      using errcode = '23514';
  end if;

  return new;
end;
$$;

drop trigger if exists trg_platform_iam_projects_default_department on platform_iam_projects;
create trigger trg_platform_iam_projects_default_department
before insert or update of org_id, department_id on platform_iam_projects
for each row
execute function platform_iam_assign_default_project_department();

insert into platform_iam_departments (org_id, slug, display_name, is_default, lifecycle_state)
select o.id, 'default', 'Default Department', true, 'active'
from platform_iam_organizations o
where not exists (
  select 1
  from platform_iam_departments d
  where d.org_id = o.id
    and d.is_default
);

update platform_iam_projects p
set department_id = d.id
from platform_iam_departments d
where d.org_id = p.org_id
  and d.is_default
  and p.department_id is null;

do $$
begin
  if not exists (
    select 1 from pg_constraint
    where conname = 'projects_department_org_fkey'
      and conrelid = 'platform_iam_projects'::regclass
  ) then
    alter table platform_iam_projects
      add constraint projects_department_org_fkey
      foreign key (department_id, org_id) references platform_iam_departments(id, org_id)
      not valid;
  end if;
end $$;

alter table if exists platform_iam_projects
  alter column department_id set not null;

create table if not exists platform_iam_users (
  id uuid primary key default gen_random_uuid(),
  -- Transitional field; authorization should resolve org access via tenant_memberships.
  org_id uuid null references platform_iam_organizations(id),
  username text not null unique,
  oidc_issuer text null,
  oidc_subject text null,
  password_hash text null,
  role text not null check (role in ('user','admin')),
  -- Transitional field; billing customer identity is tenant-rooted on platform_iam_organizations.
  stripe_customer_id text null,
  last_low_balance_notified_at timestamptz null,
  theme_preference text not null default 'system' check (theme_preference in ('system','light','dark')),
  created_at timestamptz not null default now(),
  check (
    (oidc_issuer is null and oidc_subject is null)
    or (oidc_issuer is not null and oidc_subject is not null)
  ),
  check (
    (oidc_issuer is not null and oidc_subject is not null)
    or password_hash is not null
  )
);

alter table if exists platform_iam_users
  add column if not exists theme_preference text not null default 'system';

do $$
begin
  if not exists (
    select 1 from pg_constraint
    where conname = 'projects_deleted_by_user_id_fkey'
      and conrelid = 'platform_iam_projects'::regclass
  ) then
    alter table platform_iam_projects
      add constraint projects_deleted_by_user_id_fkey
      foreign key (deleted_by_user_id) references platform_iam_users(id);
  end if;
end $$;

do $$
begin
  if not exists (
    select 1 from pg_constraint
    where conname = 'users_theme_preference_check'
      and conrelid = 'platform_iam_users'::regclass
  ) then
    alter table platform_iam_users
      add constraint users_theme_preference_check
      check (theme_preference in ('system','light','dark')) not valid;
  end if;
end $$;

alter table if exists platform_iam_users
  validate constraint users_theme_preference_check;

create unique index if not exists ux_platform_iam_users_oidc_identity
  on platform_iam_users(oidc_issuer, oidc_subject)
  where oidc_issuer is not null and oidc_subject is not null;

create table if not exists platform_iam_user_identity_links (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references platform_iam_users(id) on delete cascade,
  provider_type text not null check (provider_type in ('oidc','saml','social')),
  provider_name text not null,
  issuer text not null,
  subject text not null,
  external_username text null,
  email text null,
  email_verified boolean not null default false,
  link_state text not null default 'active' check (link_state in ('active','pending_conflict','revoked')),
  is_primary boolean not null default false,
  link_reason text null,
  linked_by_user_id uuid null references platform_iam_users(id),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  revoked_at timestamptz null,
  revoked_by_user_id uuid null references platform_iam_users(id),
  revoke_reason text null,
  check (btrim(provider_name) <> ''),
  check (btrim(issuer) <> ''),
  check (btrim(subject) <> '')
);

create unique index if not exists ux_platform_iam_user_identity_links_provider_subject_active
  on platform_iam_user_identity_links(provider_name, issuer, subject)
  where link_state = 'active' and revoked_at is null;

create unique index if not exists ux_platform_iam_user_identity_links_primary_active
  on platform_iam_user_identity_links(user_id)
  where is_primary = true and link_state = 'active' and revoked_at is null;

create index if not exists ix_user_identity_links_user_active
  on platform_iam_user_identity_links(user_id, provider_name)
  where link_state = 'active' and revoked_at is null;

create table if not exists platform_iam_account_sessions (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references platform_iam_users(id) on delete cascade,
  provider text not null default 'oidc' check (provider in ('oidc','personal_token','service_account','password','unknown')),
  provider_session_id text null,
  latest_token_id text not null,
  device_label text not null default 'Unknown device',
  user_agent text null,
  browser text null,
  os text null,
  ip_address inet null,
  location_label text null,
  signed_in_at timestamptz not null default now(),
  last_seen_at timestamptz not null default now(),
  expires_at timestamptz null,
  revoked_at timestamptz null,
  revoked_by_user_id uuid null references platform_iam_users(id),
  revoke_reason text null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  check (btrim(latest_token_id) <> ''),
  check (provider_session_id is null or btrim(provider_session_id) <> '')
);

create unique index if not exists ux_platform_iam_account_sessions_user_provider_session
  on platform_iam_account_sessions(user_id, provider_session_id)
  where provider_session_id is not null;

create unique index if not exists ux_platform_iam_account_sessions_user_token
  on platform_iam_account_sessions(user_id, latest_token_id)
  where provider_session_id is null;

create index if not exists ix_account_sessions_user_seen
  on platform_iam_account_sessions(user_id, last_seen_at desc);

create index if not exists ix_account_sessions_active
  on platform_iam_account_sessions(user_id, revoked_at, expires_at);

create table if not exists platform_access_tenant_memberships (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references platform_iam_organizations(id) on delete cascade,
  user_id uuid not null references platform_iam_users(id) on delete cascade,
  role text not null check (role in ('owner','admin','member','billing_viewer')),
  created_at timestamptz not null default now(),
  deleted_at timestamptz null,
  deleted_by_user_id uuid null references platform_iam_users(id),
  delete_reason text null
);

create table if not exists platform_access_project_memberships (
  id uuid primary key default gen_random_uuid(),
  project_id uuid not null references platform_iam_projects(id) on delete cascade,
  user_id uuid not null references platform_iam_users(id) on delete cascade,
  role text not null check (role in ('owner','admin','member','viewer')),
  created_at timestamptz not null default now(),
  deleted_at timestamptz null,
  deleted_by_user_id uuid null references platform_iam_users(id),
  delete_reason text null
);

create unique index if not exists ux_platform_access_tenant_memberships_org_user_active
  on platform_access_tenant_memberships(org_id, user_id)
  where deleted_at is null;

create unique index if not exists ux_platform_access_tenant_memberships_user_active
  on platform_access_tenant_memberships(user_id)
  where deleted_at is null;

create unique index if not exists ux_platform_access_project_memberships_project_user_active
  on platform_access_project_memberships(project_id, user_id)
  where deleted_at is null;

create index if not exists ix_platform_access_tenant_memberships_user_org_active
  on platform_access_tenant_memberships(user_id, org_id)
  where deleted_at is null;

create index if not exists ix_platform_access_project_memberships_user_project_active
  on platform_access_project_memberships(user_id, project_id)
  where deleted_at is null;

create table if not exists platform_iam_tenant_identity_providers (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references platform_iam_organizations(id) on delete cascade,
  provider_type text not null check (provider_type in ('oidc','saml')),
  provider_name text not null,
  enabled boolean not null default false,
  issuer text null,
  client_id text null,
  client_secret_enc jsonb null,
  authorization_endpoint text null,
  token_endpoint text null,
  jwks_uri text null,
  saml_entity_id text null,
  saml_sso_url text null,
  saml_x509_cert_enc jsonb null,
  created_by_user_id uuid not null references platform_iam_users(id),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  deleted_at timestamptz null
);

create unique index if not exists ux_tenant_identity_providers_org_provider_name_active
  on platform_iam_tenant_identity_providers(org_id, provider_name)
  where deleted_at is null;

create unique index if not exists ux_tenant_identity_providers_org_issuer_active
  on platform_iam_tenant_identity_providers(org_id, issuer)
  where deleted_at is null and provider_type = 'oidc' and issuer is not null;

create unique index if not exists ux_tenant_identity_providers_org_saml_entity_active
  on platform_iam_tenant_identity_providers(org_id, saml_entity_id)
  where deleted_at is null and provider_type = 'saml' and saml_entity_id is not null;

create table if not exists platform_iam_tenant_federation_domain_bindings (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references platform_iam_organizations(id) on delete cascade,
  domain text not null,
  idp_id uuid not null references platform_iam_tenant_identity_providers(id) on delete cascade,
  verification_state text not null check (verification_state in ('pending','verified','failed')),
  created_by_user_id uuid not null references platform_iam_users(id),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  deleted_at timestamptz null
);

create unique index if not exists ux_tenant_federation_domain_bindings_domain_active
  on platform_iam_tenant_federation_domain_bindings(domain)
  where deleted_at is null;

create table if not exists platform_iam_auth_federation_states (
  id uuid primary key default gen_random_uuid(),
  provider_type text not null check (provider_type in ('oidc','saml')),
  org_id uuid null references platform_iam_organizations(id) on delete cascade,
  provider_id uuid null references platform_iam_tenant_identity_providers(id) on delete cascade,
  redirect_uri text not null,
  code_challenge text null,
  code_challenge_method text null,
  identity_hint text null,
  tenant_hint text null,
  nonce text not null,
  issued_at timestamptz not null default now(),
  expires_at timestamptz not null,
  consumed_at timestamptz null
);

create index if not exists ix_auth_federation_states_expires_at
  on platform_iam_auth_federation_states(expires_at);

create table if not exists platform_access_user_project_defaults (
  user_id uuid primary key references platform_iam_users(id) on delete cascade,
  org_id uuid not null references platform_iam_organizations(id) on delete cascade,
  project_id uuid not null references platform_iam_projects(id) on delete cascade,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create unique index if not exists ux_platform_access_user_project_defaults_user_org
  on platform_access_user_project_defaults(user_id, org_id);

create table if not exists platform_access_role_definitions (
  id uuid primary key default gen_random_uuid(),
  scope_type text not null check (scope_type in ('platform','tenant','project')),
  scope_id uuid null,
  name text not null,
  is_builtin boolean not null default false,
  is_assignable_to_service_accounts boolean not null default false,
  state text not null default 'active' check (state in ('active','disabled','deprecated')),
  current_version_id uuid null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  check (
    (scope_type = 'platform' and scope_id is null)
    or (scope_type in ('tenant','project') and scope_id is not null)
  )
);

create unique index if not exists ux_role_definitions_platform_name
  on platform_access_role_definitions(name)
  where scope_type = 'platform' and scope_id is null;

create unique index if not exists ux_role_definitions_scoped_name
  on platform_access_role_definitions(scope_type, scope_id, name)
  where scope_id is not null;

create table if not exists platform_access_role_definition_versions (
  id uuid primary key default gen_random_uuid(),
  role_definition_id uuid not null references platform_access_role_definitions(id) on delete cascade,
  version integer not null check (version >= 1),
  permissions jsonb not null,
  change_reason text null,
  created_by_user_id uuid null references platform_iam_users(id),
  created_at timestamptz not null default now(),
  unique (role_definition_id, version)
);

do $$
begin
  if not exists (
    select 1
    from pg_constraint
    where conname = 'fk_role_definitions_current_version'
  ) then
    alter table platform_access_role_definitions
      add constraint fk_role_definitions_current_version
      foreign key (current_version_id) references platform_access_role_definition_versions(id);
  end if;
end$$;

create table if not exists platform_role_bindings (
  id uuid primary key default gen_random_uuid(),
  principal_type text not null check (principal_type in ('user','service_account')),
  principal_id uuid not null,
  role_definition_id uuid not null references platform_access_role_definitions(id) on delete cascade,
  created_at timestamptz not null default now(),
  deleted_at timestamptz null,
  deleted_by_user_id uuid null references platform_iam_users(id),
  delete_reason text null
);

create table if not exists platform_access_tenant_role_bindings (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references platform_iam_organizations(id) on delete cascade,
  principal_type text not null check (principal_type in ('user','service_account')),
  principal_id uuid not null,
  role_version_id uuid not null references platform_access_role_definition_versions(id) on delete cascade,
  created_at timestamptz not null default now(),
  deleted_at timestamptz null,
  deleted_by_user_id uuid null references platform_iam_users(id),
  delete_reason text null
);

create table if not exists platform_access_project_role_bindings (
  id uuid primary key default gen_random_uuid(),
  project_id uuid not null references platform_iam_projects(id) on delete cascade,
  principal_type text not null check (principal_type in ('user','service_account')),
  principal_id uuid not null,
  role_version_id uuid not null references platform_access_role_definition_versions(id) on delete cascade,
  created_at timestamptz not null default now(),
  deleted_at timestamptz null,
  deleted_by_user_id uuid null references platform_iam_users(id),
  delete_reason text null
);

create unique index if not exists ux_platform_role_bindings_active
  on platform_role_bindings(principal_type, principal_id, role_definition_id)
  where deleted_at is null;

create unique index if not exists ux_tenant_role_bindings_active
  on platform_access_tenant_role_bindings(tenant_id, principal_type, principal_id, role_version_id)
  where deleted_at is null;

create unique index if not exists ux_project_role_bindings_active
  on platform_access_project_role_bindings(project_id, principal_type, principal_id, role_version_id)
  where deleted_at is null;

create index if not exists ix_platform_role_bindings_principal_active
  on platform_role_bindings(principal_type, principal_id)
  where deleted_at is null;

create index if not exists ix_tenant_role_bindings_principal_active
  on platform_access_tenant_role_bindings(principal_type, principal_id, tenant_id)
  where deleted_at is null;

create index if not exists ix_project_role_bindings_principal_active
  on platform_access_project_role_bindings(principal_type, principal_id, project_id)
  where deleted_at is null;

create table if not exists gpuaas_access_ssh_public_keys (
  id uuid primary key default gen_random_uuid(),
  user_id uuid null references platform_iam_users(id),
  project_id uuid null references platform_iam_projects(id) on delete cascade,
  created_by_service_account_id uuid null,
  name text not null,
  public_key text not null,
  fingerprint text not null,
  is_default boolean not null default false,
  last_used_at timestamptz null,
  created_at timestamptz not null default now(),
  revoked_at timestamptz null,
  constraint chk_gpuaas_access_ssh_public_keys_owner_scope check (num_nonnulls(user_id, project_id) = 1),
  constraint chk_gpuaas_access_ssh_public_keys_project_default check (project_id is null or is_default = false)
);

alter table gpuaas_access_ssh_public_keys alter column user_id drop not null;
alter table gpuaas_access_ssh_public_keys add column if not exists project_id uuid null references platform_iam_projects(id) on delete cascade;
alter table gpuaas_access_ssh_public_keys add column if not exists created_by_service_account_id uuid null;
alter table gpuaas_access_ssh_public_keys drop constraint if exists chk_gpuaas_access_ssh_public_keys_owner_scope;
alter table gpuaas_access_ssh_public_keys add constraint chk_gpuaas_access_ssh_public_keys_owner_scope check (num_nonnulls(user_id, project_id) = 1);
alter table gpuaas_access_ssh_public_keys drop constraint if exists chk_gpuaas_access_ssh_public_keys_project_default;
alter table gpuaas_access_ssh_public_keys add constraint chk_gpuaas_access_ssh_public_keys_project_default check (project_id is null or is_default = false);

create unique index if not exists ux_gpuaas_access_ssh_public_keys_fingerprint_active
  on gpuaas_access_ssh_public_keys(user_id, fingerprint)
  where revoked_at is null and user_id is not null;

create unique index if not exists ux_project_ssh_public_keys_fingerprint_active
  on gpuaas_access_ssh_public_keys(project_id, fingerprint)
  where revoked_at is null and project_id is not null;

create unique index if not exists ux_gpuaas_access_ssh_public_keys_one_default_active
  on gpuaas_access_ssh_public_keys(user_id)
  where revoked_at is null and is_default and user_id is not null and project_id is null;

do $$
begin
  if to_regclass('public.user_posix_identities') is not null
     and to_regclass('public.platform_iam_user_posix_identities') is null then
    alter table public.user_posix_identities rename to platform_iam_user_posix_identities;
  end if;

  if to_regclass('public.platform_iam_user_posix_identities') is not null
     and exists (
       select 1
       from pg_constraint c
       join pg_class rel on rel.oid = c.conrelid
       join pg_class ref on ref.oid = c.confrelid
       join pg_namespace n on n.oid = rel.relnamespace
       where n.nspname = 'public'
         and rel.relname = 'platform_iam_user_posix_identities'
         and c.contype = 'f'
         and c.conname = 'user_posix_identities_user_id_fkey'
         and ref.relname = 'users'
     ) then
    alter table public.platform_iam_user_posix_identities
      drop constraint user_posix_identities_user_id_fkey;
  end if;

  if to_regclass('public.platform_iam_user_posix_identities') is not null then
    delete from public.platform_iam_user_posix_identities p
    where not exists (
      select 1
      from public.platform_iam_users u
      where u.id = p.user_id
    );
  end if;

  if to_regclass('public.platform_iam_user_posix_identities') is not null
     and not exists (
       select 1
       from pg_constraint c
       join pg_class rel on rel.oid = c.conrelid
       join pg_class ref on ref.oid = c.confrelid
       join pg_namespace n on n.oid = rel.relnamespace
       where n.nspname = 'public'
         and rel.relname = 'platform_iam_user_posix_identities'
         and c.contype = 'f'
         and ref.relname = 'platform_iam_users'
     ) then
    alter table public.platform_iam_user_posix_identities
      add constraint fk_platform_iam_user_posix_identities_user
      foreign key (user_id) references public.platform_iam_users(id) on delete cascade;
  end if;
end $$;

create table if not exists platform_iam_user_posix_identities (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null unique references platform_iam_users(id) on delete cascade,
  username_on_node text not null unique check (username_on_node ~ '^[a-z_][a-z0-9_-]{0,31}$'),
  uid integer not null unique check (uid between 10000 and 199999),
  gid integer not null unique check (gid between 10000 and 199999),
  supplemental_gids integer[] not null default '{}',
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists gpuaas_inventory_sku_catalog (
  sku text primary key,
  vendor text not null,
  display_name text not null,
  family_id text null,
  family_version text null,
  resource_class text not null default 'gpu_baremetal' check (resource_class in ('gpu_baremetal','gpu_slice','cpu_vm','cpu_baremetal','control_plane')),
  billing_unit text not null default 'gpu_hour' check (billing_unit in ('gpu_hour','node_hour','vcpu_hour')),
  scheduling_unit text not null default 'node' check (scheduling_unit in ('node','slice','vm')),
  unit_price_minor bigint not null default 0 check (unit_price_minor >= 0),
  currency text not null default 'USD' check (currency ~ '^[A-Z]{3}$'),
  vcpu_count integer null check (vcpu_count is null or vcpu_count >= 0),
  memory_mib integer null check (memory_mib is null or memory_mib >= 0),
  accelerator_vendor text null,
  accelerator_model text null,
  accelerator_count integer null check (accelerator_count is null or accelerator_count >= 0),
  accelerator_memory_mib integer null check (accelerator_memory_mib is null or accelerator_memory_mib >= 0),
  allowed_accelerator_counts integer[] not null default '{}',
  active boolean not null default true,
  -- Historical columns retained for already-deployed databases during this
  -- pre-customer clean break. Active code must use the resource model above.
  gpus_total integer not null default 8 check (gpus_total > 0),
  gpu_hourly_price_minor bigint not null default 0 check (gpu_hourly_price_minor >= 0),
  capacity_shape text not null default 'baremetal' check (capacity_shape in ('baremetal','gpu_slice')),
  allowed_gpu_counts integer[] not null default '{}',
  resource_profile jsonb not null default '{}'::jsonb
);

create table if not exists gpuaas_inventory_node_family_versions (
  family_id text not null,
  family_version text not null,
  display_name text not null,
  substrate text not null check (substrate in ('bare_metal_gpu','gpu_slice_vm','compute_vm','cpu_baremetal','control_plane_vm')),
  resource_class text not null check (resource_class in ('gpu_baremetal','gpu_slice','cpu_vm','cpu_baremetal','control_plane')),
  runtime_provider text not null check (runtime_provider in ('manual','maas_baremetal','proxmox_vm','maas_lxd_vm','gpu_slice_vm')),
  trust_tier text not null check (trust_tier in ('dpu_full','smartnic_offload','host_software')),
  isolation_boundary text not null check (isolation_boundary in ('bare_metal','vm','microvm')),
  mobility text not null check (mobility in ('none','cold','live')),
  failure_recovery text not null check (failure_recovery in ('reprovision','cold_migrate','live_migrate','operator_repair')),
  billing_units text[] not null default '{}',
  scheduling_units text[] not null default '{}',
  supported_image_profiles text[] not null default '{}',
  operational_evidence_required text[] not null default '{}',
  max_affected_tenants_per_host_failure integer null check (max_affected_tenants_per_host_failure is null or max_affected_tenants_per_host_failure > 0),
  shared_failure_resources text[] not null default '{}',
  capabilities jsonb not null default '{}'::jsonb,
  hardware_requirements jsonb not null default '{}'::jsonb,
  placement_policy_hooks jsonb not null default '{}'::jsonb,
  storage_attachment jsonb not null default '{}'::jsonb,
  status text not null default 'active' check (status in ('active','deprecated','disabled')),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  primary key (family_id, family_version)
);

create index if not exists ix_node_family_versions_status_resource
  on gpuaas_inventory_node_family_versions(status, resource_class);

create table if not exists gpuaas_inventory_provider_capacity (
  provider_id text primary key,
  contract_version text not null default 'v1',
  runtime_provider text not null check (runtime_provider in ('manual','maas_baremetal','proxmox_vm','maas_lxd_vm','gpu_slice_vm')),
  environment_profile text not null,
  observed_at timestamptz not null,
  health_status text not null check (health_status in ('healthy','degraded','unhealthy','unknown')),
  health_messages text[] not null default '{}',
  capacity_pools jsonb not null default '[]'::jsonb,
  refresh_policy jsonb not null default '{}'::jsonb,
  evidence_refs jsonb not null default '{}'::jsonb,
  raw_evidence jsonb not null default '{}'::jsonb,
  updated_at timestamptz not null default now()
);

alter table if exists gpuaas_inventory_provider_capacity
  add column if not exists refresh_policy jsonb not null default '{}'::jsonb;

create index if not exists ix_provider_capacity_inventory_provider_health
  on gpuaas_inventory_provider_capacity(runtime_provider, health_status, observed_at desc);

create table if not exists gpuaas_inventory_provider_resources (
  id uuid primary key default gen_random_uuid(),
  provider_id text not null,
  provider_object_type text not null,
  provider_object_id text not null,
  gpuaas_resource_type text not null,
  gpuaas_resource_id uuid null,
  allocation_id uuid null,
  owner_type text not null default 'unknown' check (owner_type in ('allocation','warm_pool','operator','unknown')),
  owner_id text null,
  family_id text null,
  family_version text null,
  sku text null,
  network_profile text null,
  storage_profile text null,
  correlation_id text not null,
  lifecycle_state text not null check (lifecycle_state in ('requested','creating','bootstrapping','ready','bound','draining','deleting','quarantined','orphaned','deleted')),
  observed_state text not null default 'unknown',
  desired_state jsonb not null default '{}'::jsonb,
  retry_count integer not null default 0 check (retry_count >= 0),
  next_retry_at timestamptz null,
  last_error_code text null,
  last_error_message text null,
  evidence_refs jsonb not null default '{}'::jsonb,
  raw_evidence jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique(provider_id, provider_object_type, provider_object_id)
);

create index if not exists ix_provider_resources_lifecycle
  on gpuaas_inventory_provider_resources(lifecycle_state, updated_at desc);

create index if not exists ix_provider_resources_owner
  on gpuaas_inventory_provider_resources(owner_type, owner_id);

create index if not exists ix_provider_resources_allocation
  on gpuaas_inventory_provider_resources(allocation_id)
  where allocation_id is not null;

alter table if exists gpuaas_inventory_sku_catalog
  add column if not exists family_id text null;
alter table if exists gpuaas_inventory_sku_catalog
  add column if not exists family_version text null;
alter table if exists gpuaas_inventory_sku_catalog
  add column if not exists resource_class text not null default 'gpu_baremetal';
alter table if exists gpuaas_inventory_sku_catalog
  add column if not exists billing_unit text not null default 'gpu_hour';
alter table if exists gpuaas_inventory_sku_catalog
  add column if not exists scheduling_unit text not null default 'node';
alter table if exists gpuaas_inventory_sku_catalog
  add column if not exists unit_price_minor bigint not null default 0;
alter table if exists gpuaas_inventory_sku_catalog
  add column if not exists vcpu_count integer null;
alter table if exists gpuaas_inventory_sku_catalog
  add column if not exists memory_mib integer null;
alter table if exists gpuaas_inventory_sku_catalog
  add column if not exists accelerator_vendor text null;
alter table if exists gpuaas_inventory_sku_catalog
  add column if not exists accelerator_model text null;
alter table if exists gpuaas_inventory_sku_catalog
  add column if not exists accelerator_count integer null;
alter table if exists gpuaas_inventory_sku_catalog
  add column if not exists accelerator_memory_mib integer null;
alter table if exists gpuaas_inventory_sku_catalog
  add column if not exists allowed_accelerator_counts integer[] not null default '{}';
alter table if exists gpuaas_inventory_sku_catalog
  add column if not exists gpus_total integer not null default 8 check (gpus_total > 0);
alter table if exists gpuaas_inventory_sku_catalog
  add column if not exists capacity_shape text not null default 'baremetal';
alter table if exists gpuaas_inventory_sku_catalog
  add column if not exists allowed_gpu_counts integer[] not null default '{}';
alter table if exists gpuaas_inventory_sku_catalog
  add column if not exists resource_profile jsonb not null default '{}'::jsonb;

alter table if exists gpuaas_inventory_sku_catalog
  alter column gpu_hourly_price_minor set default 0;

update gpuaas_inventory_sku_catalog
set
  resource_class = case
    when COALESCE(resource_profile ->> 'resource_class', '') in ('cpu_vm','cpu_node') then 'cpu_vm'
    when capacity_shape = 'gpu_slice' then 'gpu_slice'
    else 'gpu_baremetal'
  end,
  billing_unit = case
    when COALESCE(resource_profile ->> 'resource_class', '') in ('cpu_vm','cpu_node') then 'node_hour'
    else 'gpu_hour'
  end,
  scheduling_unit = case
    when COALESCE(resource_profile ->> 'resource_class', '') in ('cpu_vm','cpu_node') then 'vm'
    when capacity_shape = 'gpu_slice' then 'slice'
    else 'node'
  end,
  unit_price_minor = COALESCE(NULLIF(unit_price_minor, 0), gpu_hourly_price_minor, 0),
  vcpu_count = COALESCE(vcpu_count, case when (resource_profile ->> 'vcpu_count') ~ '^[0-9]+$' then NULLIF((resource_profile ->> 'vcpu_count')::integer, 0) end),
  memory_mib = COALESCE(memory_mib, case when (resource_profile ->> 'memory_mib') ~ '^[0-9]+$' then NULLIF((resource_profile ->> 'memory_mib')::integer, 0) end),
  accelerator_count = COALESCE(accelerator_count, case when (resource_profile ->> 'accelerator_count') ~ '^[0-9]+$' then NULLIF((resource_profile ->> 'accelerator_count')::integer, 0) end, case when capacity_shape in ('baremetal','gpu_slice') then gpus_total else 0 end),
  allowed_accelerator_counts = case
    when cardinality(allowed_accelerator_counts) > 0 then allowed_accelerator_counts
    else allowed_gpu_counts
  end;

update gpuaas_inventory_sku_catalog
set family_id = case
    when resource_class = 'cpu_vm' then 'compute-vm-proxmox-standard'
    when resource_class = 'gpu_slice' then 'slice-nvidia-h200-host'
    when upper(coalesce(accelerator_vendor, vendor)) = 'AMD' then 'training-amd-mi300x-smartnic'
    else 'training-nvidia-h200-host'
  end,
  family_version = coalesce(nullif(family_version, ''), 'v1')
where family_id is null or family_version is null;

alter table gpuaas_inventory_sku_catalog drop constraint if exists sku_catalog_capacity_shape_check;
alter table gpuaas_inventory_sku_catalog
  add constraint sku_catalog_capacity_shape_check
  check (capacity_shape in ('baremetal','gpu_slice'));
alter table gpuaas_inventory_sku_catalog drop constraint if exists sku_catalog_resource_class_check;
alter table gpuaas_inventory_sku_catalog
  add constraint sku_catalog_resource_class_check
  check (resource_class in ('gpu_baremetal','gpu_slice','cpu_vm','cpu_baremetal','control_plane'));
alter table gpuaas_inventory_sku_catalog drop constraint if exists sku_catalog_billing_unit_check;
alter table gpuaas_inventory_sku_catalog
  add constraint sku_catalog_billing_unit_check
  check (billing_unit in ('gpu_hour','node_hour','vcpu_hour'));
alter table gpuaas_inventory_sku_catalog drop constraint if exists sku_catalog_scheduling_unit_check;
alter table gpuaas_inventory_sku_catalog
  add constraint sku_catalog_scheduling_unit_check
  check (scheduling_unit in ('node','slice','vm'));
alter table gpuaas_inventory_sku_catalog drop constraint if exists sku_catalog_unit_price_minor_check;
alter table gpuaas_inventory_sku_catalog
  add constraint sku_catalog_unit_price_minor_check
  check (unit_price_minor >= 0);
alter table gpuaas_inventory_sku_catalog drop constraint if exists sku_catalog_vcpu_count_check;
alter table gpuaas_inventory_sku_catalog
  add constraint sku_catalog_vcpu_count_check
  check (vcpu_count is null or vcpu_count >= 0);
alter table gpuaas_inventory_sku_catalog drop constraint if exists sku_catalog_memory_mib_check;
alter table gpuaas_inventory_sku_catalog
  add constraint sku_catalog_memory_mib_check
  check (memory_mib is null or memory_mib >= 0);
alter table gpuaas_inventory_sku_catalog drop constraint if exists sku_catalog_accelerator_count_check;
alter table gpuaas_inventory_sku_catalog
  add constraint sku_catalog_accelerator_count_check
  check (accelerator_count is null or accelerator_count >= 0);
alter table gpuaas_inventory_sku_catalog drop constraint if exists sku_catalog_accelerator_memory_mib_check;
alter table gpuaas_inventory_sku_catalog
  add constraint sku_catalog_accelerator_memory_mib_check
  check (accelerator_memory_mib is null or accelerator_memory_mib >= 0);

create table if not exists gpuaas_inventory_os_images (
  id uuid primary key default gen_random_uuid(),
  slug text not null unique check (slug ~ '^[a-z0-9][a-z0-9._-]{1,127}$'),
  display_name text not null,
  target text not null check (target in ('baremetal','vm_slice','compute_vm')),
  image_format text not null check (image_format in ('raw','qcow2','iso','maas')),
  source_uri text not null,
  digest_sha256 text not null check (digest_sha256 ~ '^[a-f0-9]{64}$'),
  default_username text not null check (default_username ~ '^[a-z_][a-z0-9_-]{0,31}$'),
  accelerator_vendor text null,
  driver_strategy text not null default 'preinstalled' check (driver_strategy in ('preinstalled','cloud-init','none')),
  compatible_skus text[] not null default '{}',
  status text not null default 'active' check (status in ('active','disabled','deprecated')),
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

alter table if exists gpuaas_inventory_os_images
  add column if not exists target text not null default 'vm_slice';
alter table if exists gpuaas_inventory_os_images
  add column if not exists image_format text not null default 'raw';
alter table if exists gpuaas_inventory_os_images
  add column if not exists source_uri text not null default '';
alter table if exists gpuaas_inventory_os_images
  add column if not exists digest_sha256 text not null default repeat('0', 64);
alter table if exists gpuaas_inventory_os_images
  add column if not exists default_username text not null default 'ubuntu';
alter table if exists gpuaas_inventory_os_images
  add column if not exists accelerator_vendor text null;
alter table if exists gpuaas_inventory_os_images
  add column if not exists driver_strategy text not null default 'preinstalled';
alter table if exists gpuaas_inventory_os_images
  add column if not exists compatible_skus text[] not null default '{}';
alter table if exists gpuaas_inventory_os_images
  add column if not exists status text not null default 'active';
alter table if exists gpuaas_inventory_os_images
  add column if not exists metadata jsonb not null default '{}'::jsonb;
alter table if exists gpuaas_inventory_os_images drop constraint if exists os_images_target_check;
alter table if exists gpuaas_inventory_os_images add constraint os_images_target_check
  check (target in ('baremetal','vm_slice','compute_vm'));
alter table if exists gpuaas_inventory_os_images drop constraint if exists os_images_image_format_check;
alter table if exists gpuaas_inventory_os_images add constraint os_images_image_format_check
  check (image_format in ('raw','qcow2','iso','maas'));
alter table if exists gpuaas_inventory_os_images drop constraint if exists os_images_driver_strategy_check;
alter table if exists gpuaas_inventory_os_images add constraint os_images_driver_strategy_check
  check (driver_strategy in ('preinstalled','cloud-init','none'));
alter table if exists gpuaas_inventory_os_images drop constraint if exists os_images_status_check;
alter table if exists gpuaas_inventory_os_images add constraint os_images_status_check
  check (status in ('active','disabled','deprecated'));
create index if not exists ix_os_images_target_status
  on gpuaas_inventory_os_images(target, status);

create table if not exists gpuaas_maas_sites (
  id uuid primary key default gen_random_uuid(),
  name text not null unique,
  region_code text not null,
  api_base_url text not null,
  api_token_vault_path text not null unique,
  default_power_creds_vault_path text not null unique,
  pxe_iface text not null,
  pxe_vlan_vid integer not null check (pxe_vlan_vid between 1 and 4094),
  node_pxe_iface text not null,
  distro_series text not null default 'ubuntu/noble',
  architecture text not null default 'amd64/generic',
  upstream_dns_servers text[] not null default '{}',
  status text not null default 'active' check (status in ('active', 'disabled')),
  credentials_configured_at timestamptz null,
  last_probe_at timestamptz null,
  last_probe_ok boolean null,
  last_probe_error text null,
  last_probe_version text null,
  last_probe_subversion text null,
  last_probe_capabilities jsonb null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  disabled_at timestamptz null
);

comment on column gpuaas_maas_sites.last_probe_capabilities is
  'Last successful or failed MAAS probe capability payload captured from the remote MAAS version endpoint.';

create table if not exists gpuaas_maas_site_policies (
  site_id uuid primary key references gpuaas_maas_sites(id) on delete cascade,
  fabric_mode text not null default 'roce' check (fabric_mode in ('ethernet', 'roce', 'ib')),
  strict_pxe_preflight boolean not null default true,
  enable_phase2_roce boolean not null default true,
  require_hw_sync boolean not null default true,
  hardware_sync_interval text not null default '15m',
  release_fallback_no_erase boolean not null default true,
  enable_deploy_retry_on_datasource_failure boolean not null default true,
  max_deploy_retry_attempts integer not null default 1 check (max_deploy_retry_attempts between 0 and 10),
  auto_claim_single_new_machine boolean not null default false,
  batch_max_parallel integer not null default 10 check (batch_max_parallel between 1 and 100),
  deploy_user text not null default 'hpcadmin',
  deploy_password_vault_path text not null unique,
  deploy_ssh_iface text not null default 'eno8303',
  site_bootstrap_bundle_ref text null,
  commissioning_bundle_ref text null,
  post_deploy_validation_bundle_ref text null,
  fabric_controller_bundle_ref text null,
  extra_cloud_init_bundle_path text null,
  enrollment_token_ttl_seconds integer not null default 7200 check (enrollment_token_ttl_seconds between 60 and 86400),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table if not exists gpuaas_maas_site_profiles (
  id uuid primary key default gen_random_uuid(),
  site_id uuid not null references gpuaas_maas_sites(id) on delete cascade,
  name text not null,
  status text not null default 'active' check (status in ('active', 'disabled')),
  is_default boolean not null default false,
  pxe_iface text null,
  pxe_vlan_vid integer null check (pxe_vlan_vid between 1 and 4094),
  node_pxe_iface text null,
  distro_series text not null default 'ubuntu/noble',
  architecture text not null default 'amd64/generic',
  fabric_mode text not null default 'roce' check (fabric_mode in ('ethernet', 'roce', 'ib')),
  strict_pxe_preflight boolean not null default true,
  enable_phase2_roce boolean not null default true,
  require_hw_sync boolean not null default true,
  hardware_sync_interval text not null default '15m',
  release_fallback_no_erase boolean not null default true,
  enable_deploy_retry_on_datasource_failure boolean not null default true,
  max_deploy_retry_attempts integer not null default 1 check (max_deploy_retry_attempts between 0 and 10),
  auto_claim_single_new_machine boolean not null default false,
  batch_max_parallel integer not null default 10 check (batch_max_parallel between 1 and 100),
  deploy_user text null,
  deploy_password_vault_path text null,
  deploy_ssh_iface text null,
  site_bootstrap_bundle_ref text null,
  commissioning_bundle_ref text null,
  post_deploy_validation_bundle_ref text null,
  fabric_controller_bundle_ref text null,
  extra_cloud_init_bundle_path text null,
  enrollment_token_ttl_seconds integer not null default 7200 check (enrollment_token_ttl_seconds between 60 and 86400),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  disabled_at timestamptz null,
  unique (site_id, name)
);

create unique index if not exists ux_maas_site_profiles_default
  on gpuaas_maas_site_profiles(site_id)
  where is_default;

alter table if exists gpuaas_maas_site_profiles
  add column if not exists pxe_iface text null,
  add column if not exists pxe_vlan_vid integer null,
  add column if not exists node_pxe_iface text null,
  add column if not exists distro_series text not null default 'ubuntu/noble',
  add column if not exists architecture text not null default 'amd64/generic';

alter table if exists gpuaas_maas_site_policies
  add column if not exists fabric_mode text not null default 'roce',
  add column if not exists site_bootstrap_bundle_ref text null;

alter table if exists gpuaas_maas_site_profiles
  add column if not exists fabric_mode text not null default 'roce',
  add column if not exists site_bootstrap_bundle_ref text null;

alter table if exists gpuaas_maas_site_policies
  add column if not exists commissioning_bundle_ref text null,
  add column if not exists post_deploy_validation_bundle_ref text null,
  add column if not exists fabric_controller_bundle_ref text null;

alter table if exists gpuaas_maas_site_profiles
  add column if not exists commissioning_bundle_ref text null,
  add column if not exists post_deploy_validation_bundle_ref text null,
  add column if not exists fabric_controller_bundle_ref text null;

do $$
begin
  if not exists (
    select 1
    from pg_constraint
    where conname = 'maas_site_policies_fabric_mode_check'
      and conrelid = 'gpuaas_maas_site_policies'::regclass
  ) then
    alter table gpuaas_maas_site_policies
      add constraint maas_site_policies_fabric_mode_check
      check (fabric_mode in ('ethernet', 'roce', 'ib')) not valid;
  end if;
end $$;

alter table if exists gpuaas_maas_site_policies
  validate constraint maas_site_policies_fabric_mode_check;

do $$
begin
  if not exists (
    select 1
    from pg_constraint
    where conname = 'maas_site_profiles_fabric_mode_check'
      and conrelid = 'gpuaas_maas_site_profiles'::regclass
  ) then
    alter table gpuaas_maas_site_profiles
      add constraint maas_site_profiles_fabric_mode_check
      check (fabric_mode in ('ethernet', 'roce', 'ib')) not valid;
  end if;
end $$;

alter table if exists gpuaas_maas_site_profiles
  validate constraint maas_site_profiles_fabric_mode_check;

update gpuaas_maas_site_policies
set site_bootstrap_bundle_ref = extra_cloud_init_bundle_path
where site_bootstrap_bundle_ref is null
  and extra_cloud_init_bundle_path is not null;

update gpuaas_maas_site_profiles
set site_bootstrap_bundle_ref = extra_cloud_init_bundle_path
where site_bootstrap_bundle_ref is null
  and extra_cloud_init_bundle_path is not null;

update gpuaas_maas_site_policies
set extra_cloud_init_bundle_path = null
where site_bootstrap_bundle_ref is not null
  and extra_cloud_init_bundle_path is not distinct from site_bootstrap_bundle_ref;

update gpuaas_maas_site_profiles
set extra_cloud_init_bundle_path = null
where site_bootstrap_bundle_ref is not null
  and extra_cloud_init_bundle_path is not distinct from site_bootstrap_bundle_ref;

alter table if exists gpuaas_maas_site_policies
  add column if not exists deploy_user text null,
  add column if not exists deploy_password_vault_path text null,
  add column if not exists deploy_ssh_iface text null;

alter table if exists gpuaas_maas_site_profiles
  add column if not exists deploy_user text null,
  add column if not exists deploy_password_vault_path text null,
  add column if not exists deploy_ssh_iface text null;

alter table if exists gpuaas_maas_site_profiles
  alter column deploy_user drop not null,
  alter column deploy_user drop default,
  alter column deploy_password_vault_path drop not null,
  alter column deploy_ssh_iface drop not null,
  alter column deploy_ssh_iface drop default;

update gpuaas_maas_site_policies p
set deploy_user = coalesce(nullif(p.deploy_user, ''), nullif(dp.deploy_user, ''), 'hpcadmin'),
    deploy_ssh_iface = coalesce(nullif(p.deploy_ssh_iface, ''), nullif(dp.deploy_ssh_iface, ''), 'eno8303'),
    updated_at = now()
from gpuaas_maas_sites s
left join gpuaas_maas_site_profiles dp on dp.site_id = s.id and dp.is_default = true
where s.id = p.site_id
  and (
    nullif(p.deploy_user, '') is null
    or nullif(p.deploy_ssh_iface, '') is null
  );

alter table if exists gpuaas_maas_site_profiles
  drop constraint if exists maas_site_profiles_deploy_password_vault_path_key;

alter table if exists gpuaas_maas_site_profiles
  drop column if exists deploy_user,
  drop column if exists deploy_password_vault_path,
  drop column if exists deploy_ssh_iface;

alter table if exists gpuaas_maas_site_profiles
  drop constraint if exists maas_site_profiles_pxe_vlan_vid_check;

alter table if exists gpuaas_maas_site_profiles
  add constraint maas_site_profiles_pxe_vlan_vid_check
  check (pxe_vlan_vid is null or pxe_vlan_vid between 1 and 4094);

create index if not exists ix_maas_site_profiles_site_status
  on gpuaas_maas_site_profiles(site_id, status, created_at desc);

create table if not exists gpuaas_maas_power_credential_overrides (
  id uuid primary key default gen_random_uuid(),
  site_id uuid not null references gpuaas_maas_sites(id) on delete cascade,
  selector_type text not null check (selector_type in ('hostname', 'ipmi_ip', 'pxe_mac')),
  selector_value text not null,
  vault_path text not null unique,
  status text not null default 'active' check (status in ('active', 'disabled')),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (site_id, selector_type, selector_value)
);

create index if not exists ix_maas_power_credential_overrides_site_status
  on gpuaas_maas_power_credential_overrides(site_id, status, selector_type);

create table if not exists gpuaas_maas_roce_assignments (
  id uuid primary key default gen_random_uuid(),
  site_id uuid not null references gpuaas_maas_sites(id) on delete cascade,
  hostname text not null,
  interface text not null,
  ipv4_cidr text not null,
  status text not null default 'enabled' check (status in ('enabled', 'disabled')),
  disabled_at timestamptz null,
  disabled_by_user_id uuid null references platform_iam_users(id) on delete set null,
  disable_reason text null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (site_id, hostname, interface)
);

alter table if exists gpuaas_maas_roce_assignments
  add column if not exists status text not null default 'enabled' check (status in ('enabled', 'disabled'));

alter table if exists gpuaas_maas_roce_assignments
  add column if not exists disabled_at timestamptz null;

alter table if exists gpuaas_maas_roce_assignments
  add column if not exists disabled_by_user_id uuid null references platform_iam_users(id) on delete set null;

alter table if exists gpuaas_maas_roce_assignments
  add column if not exists disable_reason text null;

create index if not exists ix_maas_roce_assignments_site_hostname
  on gpuaas_maas_roce_assignments(site_id, hostname);

create index if not exists ix_maas_roce_assignments_site_status_hostname
  on gpuaas_maas_roce_assignments(site_id, status, hostname);

create table if not exists gpuaas_maas_discovery_candidate_reviews (
  site_id uuid not null references gpuaas_maas_sites(id) on delete cascade,
  maas_system_id text not null,
  review_state text not null check (review_state in ('active', 'ignored', 'suppressed')),
  review_reason text null,
  updated_by_user_id uuid null references platform_iam_users(id) on delete set null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  primary key (site_id, maas_system_id)
);

create index if not exists ix_maas_discovery_candidate_reviews_site_state
  on gpuaas_maas_discovery_candidate_reviews(site_id, review_state, updated_at desc);

create table if not exists gpuaas_node_decommissions (
  decommission_id uuid primary key,
  node_id uuid not null,
  site_id uuid null references gpuaas_maas_sites(id) on delete set null,
  maas_system_id text null,
  mode text not null check (mode in ('soft_reset', 'reimage', 'full_decommission', 'storage_cleanup')),
  status text not null check (status in ('pending', 'running', 'completed', 'failed_retryable', 'failed_manual_intervention', 'cancelled', 'compensating', 'reconciled')),
  current_stage text not null,
  current_attempt integer not null default 0 check (current_attempt >= 0),
  requested_by_user_id uuid null references platform_iam_users(id) on delete set null,
  error_code text null,
  error_message text null,
  error_details jsonb not null default '{}'::jsonb,
  workflow_id text not null unique,
  workflow_run_id text null,
  requested_at timestamptz not null default now(),
  started_at timestamptz null,
  completed_at timestamptz null,
  updated_at timestamptz not null default now()
);

create index if not exists ix_node_decommissions_site_status_requested
  on gpuaas_node_decommissions(site_id, status, requested_at desc);

create index if not exists ix_node_decommissions_node_requested
  on gpuaas_node_decommissions(node_id, requested_at desc);

update gpuaas_node_decommissions
set current_stage = 'completed'
where status = 'completed'
  and current_stage = 'wait_node_enrollment';

do $$
begin
  if to_regclass('public.gpuaas_node_decommissions') is not null
     and exists (
       select 1
       from pg_constraint
       where conname = 'node_decommissions_node_id_fkey'
         and conrelid = 'public.gpuaas_node_decommissions'::regclass
     ) then
    alter table public.gpuaas_node_decommissions
      drop constraint node_decommissions_node_id_fkey;
  end if;
end $$;

create table if not exists gpuaas_node_decommission_events (
  id uuid primary key default gen_random_uuid(),
  decommission_id uuid not null references gpuaas_node_decommissions(decommission_id) on delete cascade,
  stage text not null,
  attempt integer not null default 0 check (attempt >= 0),
  status text not null check (status in ('started', 'succeeded', 'failed', 'compensated', 'skipped')),
  message text null,
  details jsonb not null default '{}'::jsonb,
  occurred_at timestamptz not null default now()
);

create index if not exists ix_node_decommission_events_decommission_occurred
  on gpuaas_node_decommission_events(decommission_id, occurred_at desc);

create table if not exists gpuaas_nodes (
  id uuid primary key default gen_random_uuid(),
  -- null => shared/unassigned pool node; non-null => tenant-dedicated node
  org_id uuid null references platform_iam_organizations(id),
  onboarding_mode text not null default 'manual' check (onboarding_mode in ('manual','maas')),
  region_code text not null default 'region-1',
  host text not null,
  hostname text null,
  port integer not null default 22 check (port between 1 and 65535),
  ssh_username text not null,
  access_method text not null default 'password',
  access_secret_enc jsonb not null,
  sku text not null references gpuaas_inventory_sku_catalog(sku),
  gpus_total integer not null check (gpus_total >= 0),
  status text not null check (status in ('registered','bootstrap_issued','enrolling','active','cordoned','offline','quarantined','draining','retired','removing')),
  status_changed_at timestamptz null,
  agent_version text null,
  agent_commit text null,
  agent_built_at text null,
  agent_reported_at timestamptz null,
  created_at timestamptz not null default now()
);

alter table if exists gpuaas_nodes add column if not exists agent_version text null;
alter table if exists gpuaas_nodes add column if not exists agent_commit text null;
alter table if exists gpuaas_nodes add column if not exists agent_built_at text null;
alter table if exists gpuaas_nodes add column if not exists agent_reported_at timestamptz null;
alter table if exists gpuaas_nodes drop constraint if exists nodes_gpus_total_check;
alter table if exists gpuaas_nodes add constraint nodes_gpus_total_check check (gpus_total >= 0);

do $$
begin
  if to_regclass('public.gpuaas_nodes') is null then
    return;
  end if;

  if exists (
    select 1
    from pg_constraint
    where conrelid = 'public.gpuaas_nodes'::regclass
      and conname = 'nodes_status_check'
  ) then
    alter table public.gpuaas_nodes
      drop constraint nodes_status_check;
  end if;

alter table public.gpuaas_nodes
    add constraint nodes_status_check
    check (status in ('registered','bootstrap_issued','enrolling','active','cordoned','offline','quarantined','draining','retired','removing'));
end $$;

create table if not exists gpuaas_node_image_cache (
  id uuid primary key default gen_random_uuid(),
  node_id uuid not null references gpuaas_nodes(id) on delete cascade,
  image_slug text not null references gpuaas_inventory_os_images(slug) on delete cascade,
  artifact_ref text not null,
  digest_sha256 text not null check (digest_sha256 ~ '^[a-f0-9]{64}$'),
  local_path text not null,
  status text not null default 'missing' check (status in ('missing','syncing','verified','failed')),
  size_bytes bigint null check (size_bytes is null or size_bytes >= 0),
  verified_at timestamptz null,
  last_error text null,
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (node_id, image_slug, digest_sha256)
);

alter table if exists gpuaas_node_image_cache
  add column if not exists artifact_ref text not null default '';
alter table if exists gpuaas_node_image_cache
  add column if not exists digest_sha256 text not null default repeat('0', 64);
alter table if exists gpuaas_node_image_cache
  add column if not exists local_path text not null default '';
alter table if exists gpuaas_node_image_cache
  add column if not exists status text not null default 'missing';
alter table if exists gpuaas_node_image_cache
  add column if not exists size_bytes bigint null;
alter table if exists gpuaas_node_image_cache
  add column if not exists verified_at timestamptz null;
alter table if exists gpuaas_node_image_cache
  add column if not exists last_error text null;
alter table if exists gpuaas_node_image_cache
  add column if not exists metadata jsonb not null default '{}'::jsonb;
alter table if exists gpuaas_node_image_cache drop constraint if exists node_image_cache_digest_sha256_check;
alter table if exists gpuaas_node_image_cache add constraint node_image_cache_digest_sha256_check
  check (digest_sha256 ~ '^[a-f0-9]{64}$');
alter table if exists gpuaas_node_image_cache drop constraint if exists node_image_cache_status_check;
alter table if exists gpuaas_node_image_cache add constraint node_image_cache_status_check
  check (status in ('missing','syncing','verified','failed'));
create index if not exists ix_node_image_cache_node_status
  on gpuaas_node_image_cache(node_id, status);
create index if not exists ix_node_image_cache_image_verified
  on gpuaas_node_image_cache(image_slug, digest_sha256)
  where status = 'verified';

create table if not exists gpuaas_node_resource_slots (
  id uuid primary key default gen_random_uuid(),
  node_id uuid not null references gpuaas_nodes(id) on delete cascade,
  parent_slot_id uuid null references gpuaas_node_resource_slots(id) on delete set null,
  slot_index integer not null check (slot_index >= 0),
  capacity_shape text not null default 'gpu_slice' check (capacity_shape in ('gpu_slice')),
  status text not null default 'disabled' check (status in ('available','reserved','provisioning','active','releasing','cleanup','cleanup_blocked','failed','disabled')),
  sharing_model text null check (sharing_model is null or sharing_model in ('exclusive_device')),
  profile_name text null,
  accelerator_vendor text null,
  accelerator_model text null,
  gpu_index integer null check (gpu_index is null or gpu_index >= 0),
  gpu_uuid text null,
  pci_address text null,
  numa_node integer null,
  nvme_device text null,
  nvme_wwn text null,
  fabric_kind text null check (fabric_kind is null or fabric_kind in ('ib','infiniband','roce','ethernet','none','any')),
  fabric_device text null,
  vcpu_count integer null check (vcpu_count is null or vcpu_count > 0),
  memory_mib integer null check (memory_mib is null or memory_mib > 0),
  gpu_memory_mib integer null check (gpu_memory_mib is null or gpu_memory_mib > 0),
  compute_milli integer null check (compute_milli is null or compute_milli > 0),
  max_claims integer null check (max_claims is null or max_claims > 0),
  mac_address text null,
  private_ip inet null,
  health_state text not null default 'unknown' check (health_state in ('unknown','healthy','degraded','failed')),
  health_detail jsonb not null default '{}'::jsonb,
  capacity_metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (node_id, slot_index)
);

do $$
begin
  if to_regclass('public.gpuaas_node_resource_slots') is null then
    return;
  end if;

  if exists (select 1 from information_schema.columns where table_schema = 'public' and table_name = 'gpuaas_node_resource_slots' and column_name = 'shape')
     and not exists (select 1 from information_schema.columns where table_schema = 'public' and table_name = 'gpuaas_node_resource_slots' and column_name = 'capacity_shape') then
    alter table public.gpuaas_node_resource_slots rename column shape to capacity_shape;
  end if;
  if exists (select 1 from information_schema.columns where table_schema = 'public' and table_name = 'gpuaas_node_resource_slots' and column_name = 'gpu_model')
     and not exists (select 1 from information_schema.columns where table_schema = 'public' and table_name = 'gpuaas_node_resource_slots' and column_name = 'accelerator_model') then
    alter table public.gpuaas_node_resource_slots rename column gpu_model to accelerator_model;
  end if;
  if exists (select 1 from information_schema.columns where table_schema = 'public' and table_name = 'gpuaas_node_resource_slots' and column_name = 'gpu_pci_addr')
     and not exists (select 1 from information_schema.columns where table_schema = 'public' and table_name = 'gpuaas_node_resource_slots' and column_name = 'pci_address') then
    alter table public.gpuaas_node_resource_slots rename column gpu_pci_addr to pci_address;
  end if;
  if exists (select 1 from information_schema.columns where table_schema = 'public' and table_name = 'gpuaas_node_resource_slots' and column_name = 'network_fabric')
     and not exists (select 1 from information_schema.columns where table_schema = 'public' and table_name = 'gpuaas_node_resource_slots' and column_name = 'fabric_kind') then
    alter table public.gpuaas_node_resource_slots rename column network_fabric to fabric_kind;
  end if;
  if exists (select 1 from information_schema.columns where table_schema = 'public' and table_name = 'gpuaas_node_resource_slots' and column_name = 'network_device')
     and not exists (select 1 from information_schema.columns where table_schema = 'public' and table_name = 'gpuaas_node_resource_slots' and column_name = 'fabric_device') then
    alter table public.gpuaas_node_resource_slots rename column network_device to fabric_device;
  end if;
  if exists (select 1 from information_schema.columns where table_schema = 'public' and table_name = 'gpuaas_node_resource_slots' and column_name = 'health_metadata')
     and not exists (select 1 from information_schema.columns where table_schema = 'public' and table_name = 'gpuaas_node_resource_slots' and column_name = 'health_detail') then
    alter table public.gpuaas_node_resource_slots rename column health_metadata to health_detail;
  end if;
end $$;
alter table if exists gpuaas_node_resource_slots add column if not exists parent_slot_id uuid null references gpuaas_node_resource_slots(id) on delete set null;
alter table if exists gpuaas_node_resource_slots add column if not exists capacity_shape text not null default 'gpu_slice';
alter table if exists gpuaas_node_resource_slots add column if not exists sharing_model text null;
alter table if exists gpuaas_node_resource_slots add column if not exists profile_name text null;
alter table if exists gpuaas_node_resource_slots add column if not exists accelerator_vendor text null;
alter table if exists gpuaas_node_resource_slots add column if not exists accelerator_model text null;
alter table if exists gpuaas_node_resource_slots add column if not exists gpu_index integer null;
alter table if exists gpuaas_node_resource_slots add column if not exists gpu_uuid text null;
alter table if exists gpuaas_node_resource_slots add column if not exists pci_address text null;
alter table if exists gpuaas_node_resource_slots add column if not exists numa_node integer null;
alter table if exists gpuaas_node_resource_slots add column if not exists nvme_device text null;
alter table if exists gpuaas_node_resource_slots add column if not exists nvme_wwn text null;
alter table if exists gpuaas_node_resource_slots add column if not exists fabric_kind text null;
alter table if exists gpuaas_node_resource_slots add column if not exists fabric_device text null;
alter table if exists gpuaas_node_resource_slots add column if not exists vcpu_count integer null;
alter table if exists gpuaas_node_resource_slots add column if not exists memory_mib integer null;
alter table if exists gpuaas_node_resource_slots add column if not exists gpu_memory_mib integer null;
alter table if exists gpuaas_node_resource_slots add column if not exists compute_milli integer null;
alter table if exists gpuaas_node_resource_slots add column if not exists max_claims integer null;
alter table if exists gpuaas_node_resource_slots add column if not exists mac_address text null;
alter table if exists gpuaas_node_resource_slots add column if not exists private_ip inet null;
alter table if exists gpuaas_node_resource_slots add column if not exists health_state text not null default 'unknown';
alter table if exists gpuaas_node_resource_slots add column if not exists health_detail jsonb not null default '{}'::jsonb;
alter table if exists gpuaas_node_resource_slots add column if not exists capacity_metadata jsonb not null default '{}'::jsonb;
alter table if exists gpuaas_node_resource_slots drop constraint if exists node_resource_slots_capacity_shape_check;
alter table if exists gpuaas_node_resource_slots
  add constraint node_resource_slots_capacity_shape_check
  check (capacity_shape in ('gpu_slice'));
alter table if exists gpuaas_node_resource_slots drop constraint if exists node_resource_slots_status_check;
alter table if exists gpuaas_node_resource_slots
  add constraint node_resource_slots_status_check
  check (status in ('available','reserved','provisioning','active','releasing','cleanup','cleanup_blocked','failed','disabled'));
alter table if exists gpuaas_node_resource_slots drop constraint if exists node_resource_slots_sharing_model_check;
alter table if exists gpuaas_node_resource_slots
  add constraint node_resource_slots_sharing_model_check
  check (sharing_model is null or sharing_model in ('exclusive_device'));
alter table if exists gpuaas_node_resource_slots drop constraint if exists node_resource_slots_gpu_index_check;
alter table if exists gpuaas_node_resource_slots
  add constraint node_resource_slots_gpu_index_check
  check (gpu_index is null or gpu_index >= 0);
alter table if exists gpuaas_node_resource_slots drop constraint if exists node_resource_slots_fabric_kind_check;
alter table if exists gpuaas_node_resource_slots
  add constraint node_resource_slots_fabric_kind_check
  check (fabric_kind is null or fabric_kind in ('ib','infiniband','roce','ethernet','none','any'));
alter table if exists gpuaas_node_resource_slots drop constraint if exists node_resource_slots_vcpu_count_check;
alter table if exists gpuaas_node_resource_slots
  add constraint node_resource_slots_vcpu_count_check
  check (vcpu_count is null or vcpu_count > 0);
alter table if exists gpuaas_node_resource_slots drop constraint if exists node_resource_slots_memory_mib_check;
alter table if exists gpuaas_node_resource_slots
  add constraint node_resource_slots_memory_mib_check
  check (memory_mib is null or memory_mib > 0);
alter table if exists gpuaas_node_resource_slots drop constraint if exists node_resource_slots_gpu_memory_mib_check;
alter table if exists gpuaas_node_resource_slots
  add constraint node_resource_slots_gpu_memory_mib_check
  check (gpu_memory_mib is null or gpu_memory_mib > 0);
alter table if exists gpuaas_node_resource_slots drop constraint if exists node_resource_slots_compute_milli_check;
alter table if exists gpuaas_node_resource_slots
  add constraint node_resource_slots_compute_milli_check
  check (compute_milli is null or compute_milli > 0);
alter table if exists gpuaas_node_resource_slots drop constraint if exists node_resource_slots_max_claims_check;
alter table if exists gpuaas_node_resource_slots
  add constraint node_resource_slots_max_claims_check
  check (max_claims is null or max_claims > 0);
alter table if exists gpuaas_node_resource_slots drop constraint if exists node_resource_slots_health_state_check;
alter table if exists gpuaas_node_resource_slots
  add constraint node_resource_slots_health_state_check
  check (health_state in ('unknown','healthy','degraded','failed'));

create index if not exists ix_node_resource_slots_node_status
  on gpuaas_node_resource_slots(node_id, status, slot_index);

drop index if exists ix_node_resource_slots_shape_status;
create index if not exists ix_node_resource_slots_capacity_shape_status
  on gpuaas_node_resource_slots(capacity_shape, status);

create index if not exists ix_node_resource_slots_parent
  on gpuaas_node_resource_slots(parent_slot_id)
  where parent_slot_id is not null;

create table if not exists gpuaas_node_onboardings (
  onboarding_id uuid primary key,
  batch_id uuid null,
  node_id uuid null references gpuaas_nodes(id) on delete set null,
  site_id uuid not null references gpuaas_maas_sites(id) on delete restrict,
  profile_id uuid null references gpuaas_maas_site_profiles(id) on delete restrict,
  sku_id text not null,
  hostname text not null,
  ipmi_ip inet not null,
  pxe_mac text null,
  maas_system_id text null,
  requested_by_user_id uuid null references platform_iam_users(id) on delete set null,
  current_stage text not null,
  current_attempt integer not null default 0 check (current_attempt >= 0),
  status text not null check (status in ('pending', 'running', 'completed', 'failed_retryable', 'failed_manual_intervention', 'cancelled', 'compensating', 'reconciled')),
  error_code text null,
  error_message text null,
  error_details jsonb not null default '{}'::jsonb,
  workflow_id text not null unique,
  workflow_run_id text null,
  requested_at timestamptz not null default now(),
  started_at timestamptz null,
  completed_at timestamptz null,
  updated_at timestamptz not null default now()
);

alter table if exists gpuaas_node_onboardings
  add column if not exists profile_id uuid null references gpuaas_maas_site_profiles(id) on delete restrict;

alter table if exists gpuaas_node_onboardings
  add column if not exists sku_id text;

update gpuaas_node_onboardings o
set sku_id = n.sku
from gpuaas_nodes n
where o.sku_id is null
  and o.node_id = n.id;

update gpuaas_node_onboardings
set sku_id = ''
where sku_id is null;

alter table if exists gpuaas_node_onboardings
  alter column sku_id set default '';

alter table if exists gpuaas_node_onboardings
  alter column sku_id set not null;

update gpuaas_node_onboardings
set current_stage = 'completed'
where status = 'completed'
  and current_stage = 'wait_node_enrollment';

create index if not exists ix_node_onboardings_site_status_requested
  on gpuaas_node_onboardings(site_id, status, requested_at desc);

create index if not exists ix_node_onboardings_batch
  on gpuaas_node_onboardings(batch_id, requested_at desc)
  where batch_id is not null;

create table if not exists gpuaas_node_onboarding_events (
  id uuid primary key default gen_random_uuid(),
  onboarding_id uuid not null references gpuaas_node_onboardings(onboarding_id) on delete cascade,
  stage text not null,
  attempt integer not null default 0 check (attempt >= 0),
  status text not null check (status in ('started', 'succeeded', 'failed', 'compensated', 'skipped')),
  message text null,
  details jsonb not null default '{}'::jsonb,
  occurred_at timestamptz not null default now()
);

create index if not exists ix_node_onboarding_events_onboarding_occurred
  on gpuaas_node_onboarding_events(onboarding_id, occurred_at desc);

create table if not exists gpuaas_node_maas_state (
  node_id uuid primary key references gpuaas_nodes(id) on delete cascade,
  site_id uuid not null references gpuaas_maas_sites(id) on delete restrict,
  maas_system_id text not null,
  last_maas_status text null,
  last_maas_power_state text null,
  last_maas_ips text[] not null default '{}',
  management_interface text null,
  management_ip text null,
  management_subnet_cidr text null,
  last_reconciled_at timestamptz null,
  drift_detected boolean not null default false,
  drift_details jsonb not null default '{}'::jsonb,
  drift_resolved_at timestamptz null
);

alter table if exists gpuaas_node_maas_state
  add column if not exists management_interface text null;

alter table if exists gpuaas_node_maas_state
  add column if not exists management_ip text null;

alter table if exists gpuaas_node_maas_state
  add column if not exists management_subnet_cidr text null;

create index if not exists ix_node_maas_state_site_drift
  on gpuaas_node_maas_state(site_id, drift_detected, last_reconciled_at desc);

create table if not exists gpuaas_allocations (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references platform_iam_organizations(id),
  project_id uuid not null,
  requested_by_user_id uuid not null references platform_iam_users(id),
  node_id uuid null references gpuaas_nodes(id),
  username_on_node text not null check (username_on_node ~ '^[a-z_][a-z0-9_-]{0,31}$'),
  host_snapshot text null,
  hostname_snapshot text null,
  port_snapshot integer null check (port_snapshot between 1 and 65535),
  region_code text not null default 'region-1',
  scheduler_type text not null default 'bare_metal' check (scheduler_type in ('bare_metal','slurm','k8s','ray')),
  scheduler_ref text null,
  scheduler_metadata jsonb null,
  sku_snapshot text not null,
  gpus_total_snapshot integer not null check (gpus_total_snapshot >= 0),
  family_id_snapshot text null,
  family_version_snapshot text null,
  resource_class_snapshot text not null default 'gpu_baremetal' check (resource_class_snapshot in ('gpu_baremetal','gpu_slice','cpu_vm','cpu_baremetal','control_plane')),
  billing_unit_snapshot text not null default 'gpu_hour' check (billing_unit_snapshot in ('gpu_hour','node_hour','vcpu_hour')),
  scheduling_unit_snapshot text not null default 'node' check (scheduling_unit_snapshot in ('node','slice','vm')),
  unit_count_snapshot integer not null default 1 check (unit_count_snapshot > 0),
  resource_shape_snapshot jsonb not null default '{}'::jsonb,
  pricing_mode text not null default 'on_demand' check (pricing_mode in ('on_demand','spot','reserved')),
  billing_unit_price_minor bigint null check (billing_unit_price_minor is null or billing_unit_price_minor >= 0),
  billing_currency text null check (billing_currency is null or billing_currency ~ '^[A-Z]{3}$'),
  billing_pricing_plan_id uuid null,
  billing_pricing_plan_version text null,
  billing_pricing_snapshot jsonb not null default '{}'::jsonb,
  capacity_shape text not null default 'baremetal' check (capacity_shape in ('baremetal','gpu_slice')),
  placement_status text not null default 'unplaced' check (placement_status in ('unplaced','pending_capacity','reserved','provisioning','active','releasing','released','failed')),
  placement_metadata jsonb not null default '{}'::jsonb,
  status text not null check (status in ('requested','provisioning','active','restarting','restart_failed','releasing','released','failed','release_failed')),
  provisioning_started_at timestamptz null,
  active_at timestamptz null,
  ssh_private_key_enc jsonb null,
  ssh_public_key text null,
  failure_reason text null,
  created_at timestamptz not null default now(),
  restart_failed_at timestamptz null,
  restart_failed_reason text null,
  released_at timestamptz null,
  release_failed_at timestamptz null,
  release_failed_reason text null,
  check (
    scheduler_type <> 'bare_metal'
    or status in ('requested','provisioning','failed','released')
    or capacity_shape <> 'baremetal'
    or node_id is not null
  ),
  check (
    status not in ('provisioning','active','restarting','restart_failed','releasing','released','release_failed')
    or provisioning_started_at is not null
  ),
  check (
    status not in ('active','restarting','restart_failed','release_failed')
    or active_at is not null
  ),
  check (
    status not in ('active','restarting','restart_failed','release_failed')
    or (host_snapshot is not null and port_snapshot is not null)
  ),
  check (
    active_at is null
    or provisioning_started_at is null
    or active_at >= provisioning_started_at
  ),
  constraint fk_allocations_project_org
    foreign key (project_id, org_id) references platform_iam_projects(id, org_id)
);

alter table if exists gpuaas_allocations
  drop constraint if exists allocations_gpus_total_snapshot_check;

alter table if exists gpuaas_allocations
  add constraint allocations_gpus_total_snapshot_check
  check (gpus_total_snapshot >= 0);

create table if not exists gpuaas_allocation_ssh_public_keys (
  id uuid primary key default gen_random_uuid(),
  allocation_id uuid not null references gpuaas_allocations(id) on delete cascade,
  ssh_public_key_id uuid not null references gpuaas_access_ssh_public_keys(id),
  created_at timestamptz not null default now(),
  unique (allocation_id, ssh_public_key_id)
);

create index if not exists ix_gpuaas_allocation_ssh_public_keys_allocation
  on gpuaas_allocation_ssh_public_keys(allocation_id, created_at);

create table if not exists gpuaas_allocation_access_grants (
  id uuid primary key default gen_random_uuid(),
  allocation_id uuid not null references gpuaas_allocations(id) on delete cascade,
  project_id uuid not null references platform_iam_projects(id) on delete cascade,
  grantee_user_id uuid not null references platform_iam_users(id) on delete cascade,
  ssh_public_key_id uuid not null references gpuaas_access_ssh_public_keys(id),
  status text not null default 'active' check (status in ('active','revoked')),
  runtime_sync_status text not null default 'pending' check (runtime_sync_status in ('pending','queued','not_required','failed')),
  runtime_sync_task_id uuid null,
  runtime_sync_error text null,
  reason text null,
  created_by_user_id uuid null references platform_iam_users(id),
  created_by_service_account_id uuid null,
  revoked_by_user_id uuid null references platform_iam_users(id),
  revoked_by_service_account_id uuid null,
  created_at timestamptz not null default now(),
  revoked_at timestamptz null,
  constraint chk_gpuaas_allocation_access_grants_create_actor check (num_nonnulls(created_by_user_id, created_by_service_account_id) = 1),
  constraint chk_gpuaas_allocation_access_grants_revoke_actor check (
    revoked_at is null
    or num_nonnulls(revoked_by_user_id, revoked_by_service_account_id) = 1
  )
);

create unique index if not exists ux_gpuaas_allocation_access_grants_active_grantee
  on gpuaas_allocation_access_grants(allocation_id, grantee_user_id)
  where revoked_at is null;

create index if not exists ix_gpuaas_allocation_access_grants_allocation_status
  on gpuaas_allocation_access_grants(allocation_id, status, created_at);

create index if not exists ix_gpuaas_allocation_access_grants_grantee_active
  on gpuaas_allocation_access_grants(grantee_user_id, allocation_id)
  where revoked_at is null;

create table if not exists gpuaas_managed_runtime_bundles (
  id uuid primary key default gen_random_uuid(),
  slug text not null,
  display_name text not null,
  version text not null,
  support_level text not null default 'supported' check (support_level in ('supported','preview','deprecated')),
  status text not null default 'active' check (status in ('active','deprecated','disabled')),
  install_root text not null check (install_root ~ '^/opt/gpuaas/runtimes/[a-z0-9][a-z0-9._-]*/[A-Za-z0-9][A-Za-z0-9._+-]*$'),
  activation_hint text not null,
  description text not null default '',
  compatible_accelerators text[] not null default '{}',
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (slug, version)
);

create index if not exists ix_gpuaas_managed_runtime_bundles_status_slug
  on gpuaas_managed_runtime_bundles(status, slug, version);

create table if not exists gpuaas_allocation_runtime_bundles (
  id uuid primary key default gen_random_uuid(),
  allocation_id uuid not null references gpuaas_allocations(id) on delete cascade,
  project_id uuid not null references platform_iam_projects(id) on delete cascade,
  bundle_id uuid not null references gpuaas_managed_runtime_bundles(id),
  status text not null default 'pending' check (status in ('pending','queued','active','failed','removed')),
  apply_task_id uuid null,
  failure_reason text null,
  requested_by_user_id uuid null references platform_iam_users(id),
  requested_by_service_account_id uuid null,
  requested_at timestamptz not null default now(),
  applied_at timestamptz null,
  removed_at timestamptz null,
  updated_at timestamptz not null default now(),
  constraint chk_allocation_runtime_bundle_actor check (num_nonnulls(requested_by_user_id, requested_by_service_account_id) = 1)
);

create unique index if not exists ux_gpuaas_allocation_runtime_bundles_current
  on gpuaas_allocation_runtime_bundles(allocation_id)
  where removed_at is null;

create index if not exists ix_gpuaas_allocation_runtime_bundles_project_status
  on gpuaas_allocation_runtime_bundles(project_id, status, requested_at desc);

comment on column gpuaas_allocations.scheduler_metadata is
  'Scheduler integration metadata. Any credential-bearing values must be stored as envelope-encrypted payloads (see Encryption_Envelope_Spec.md).';

alter table gpuaas_allocations add column if not exists capacity_shape text not null default 'baremetal';
alter table gpuaas_allocations add column if not exists placement_status text not null default 'unplaced';
alter table gpuaas_allocations add column if not exists placement_metadata jsonb not null default '{}'::jsonb;
alter table gpuaas_allocations add column if not exists restart_failed_at timestamptz null;
alter table gpuaas_allocations add column if not exists restart_failed_reason text null;
alter table gpuaas_allocations drop constraint if exists allocations_capacity_shape_check;
alter table gpuaas_allocations add constraint allocations_capacity_shape_check
  check (capacity_shape in ('baremetal','gpu_slice'));
alter table gpuaas_allocations drop constraint if exists allocations_placement_status_check;
alter table gpuaas_allocations add constraint allocations_placement_status_check
  check (placement_status in ('unplaced','pending_capacity','reserved','provisioning','active','releasing','released','failed'));
alter table gpuaas_allocations drop constraint if exists allocations_status_check;
alter table gpuaas_allocations add constraint allocations_status_check
  check (status in ('requested','provisioning','active','restarting','restart_failed','releasing','released','failed','release_failed'));

drop index if exists ux_allocations_node_active;
create unique index if not exists ux_allocations_node_active_baremetal
  on gpuaas_allocations(node_id)
  where status = 'active'
    and capacity_shape = 'baremetal';

alter table gpuaas_allocations
  drop constraint if exists allocations_check;

alter table gpuaas_allocations
  add constraint allocations_check
  check (
    scheduler_type <> 'bare_metal'
    or status in ('requested','provisioning','failed','released')
    or capacity_shape <> 'baremetal'
    or node_id is not null
);

alter table gpuaas_allocations add column if not exists pricing_mode text not null default 'on_demand';
alter table gpuaas_allocations add column if not exists billing_unit_price_minor bigint null;
alter table gpuaas_allocations add column if not exists billing_currency text null;
alter table gpuaas_allocations add column if not exists billing_pricing_plan_id uuid null;
alter table gpuaas_allocations add column if not exists billing_pricing_plan_version text null;
alter table gpuaas_allocations add column if not exists billing_pricing_snapshot jsonb not null default '{}'::jsonb;
alter table gpuaas_allocations add column if not exists family_id_snapshot text null;
alter table gpuaas_allocations add column if not exists family_version_snapshot text null;
alter table gpuaas_allocations add column if not exists resource_class_snapshot text not null default 'gpu_baremetal';
alter table gpuaas_allocations add column if not exists billing_unit_snapshot text not null default 'gpu_hour';
alter table gpuaas_allocations add column if not exists scheduling_unit_snapshot text not null default 'node';
alter table gpuaas_allocations add column if not exists unit_count_snapshot integer not null default 1;
alter table gpuaas_allocations add column if not exists resource_shape_snapshot jsonb not null default '{}'::jsonb;
alter table gpuaas_allocations drop constraint if exists allocations_resource_class_snapshot_check;
alter table gpuaas_allocations add constraint allocations_resource_class_snapshot_check
  check (resource_class_snapshot in ('gpu_baremetal','gpu_slice','cpu_vm','cpu_baremetal','control_plane'));
alter table gpuaas_allocations drop constraint if exists allocations_billing_unit_snapshot_check;
alter table gpuaas_allocations add constraint allocations_billing_unit_snapshot_check
  check (billing_unit_snapshot in ('gpu_hour','node_hour','vcpu_hour'));
alter table gpuaas_allocations drop constraint if exists allocations_scheduling_unit_snapshot_check;
alter table gpuaas_allocations add constraint allocations_scheduling_unit_snapshot_check
  check (scheduling_unit_snapshot in ('node','slice','vm'));
alter table gpuaas_allocations drop constraint if exists allocations_unit_count_snapshot_check;
alter table gpuaas_allocations add constraint allocations_unit_count_snapshot_check
  check (unit_count_snapshot > 0);

update gpuaas_allocations a
set
  family_id_snapshot = coalesce(nullif(a.family_id_snapshot, ''), sc.family_id),
  family_version_snapshot = coalesce(nullif(a.family_version_snapshot, ''), sc.family_version),
  resource_class_snapshot = coalesce(nullif(a.resource_class_snapshot, ''), sc.resource_class, case when a.capacity_shape = 'gpu_slice' then 'gpu_slice' else 'gpu_baremetal' end),
  billing_unit_snapshot = coalesce(nullif(a.billing_unit_snapshot, ''), sc.billing_unit, 'gpu_hour'),
  scheduling_unit_snapshot = coalesce(nullif(a.scheduling_unit_snapshot, ''), sc.scheduling_unit, case when a.capacity_shape = 'gpu_slice' then 'slice' else 'node' end),
  unit_count_snapshot = greatest(coalesce(nullif(a.unit_count_snapshot, 0), a.gpus_total_snapshot, 1), 1),
  billing_unit_price_minor = coalesce(a.billing_unit_price_minor, sc.unit_price_minor, sc.gpu_hourly_price_minor, 0),
  billing_currency = coalesce(nullif(a.billing_currency, ''), sc.currency, 'USD'),
  resource_shape_snapshot = case
    when a.resource_shape_snapshot <> '{}'::jsonb then a.resource_shape_snapshot
    else jsonb_strip_nulls(jsonb_build_object(
      'resource_class', coalesce(sc.resource_class, case when a.capacity_shape = 'gpu_slice' then 'gpu_slice' else 'gpu_baremetal' end),
      'billing_unit', coalesce(sc.billing_unit, 'gpu_hour'),
      'scheduling_unit', coalesce(sc.scheduling_unit, case when a.capacity_shape = 'gpu_slice' then 'slice' else 'node' end),
      'unit_count', greatest(coalesce(nullif(a.unit_count_snapshot, 0), a.gpus_total_snapshot, 1), 1),
      'vcpu_count', sc.vcpu_count,
      'memory_mib', sc.memory_mib,
      'accelerator_vendor', sc.accelerator_vendor,
      'accelerator_model', sc.accelerator_model,
      'accelerator_count', sc.accelerator_count,
      'accelerator_memory_mib', sc.accelerator_memory_mib
    ))
  end
from gpuaas_inventory_sku_catalog sc
where sc.sku = a.sku_snapshot;

create table if not exists gpuaas_allocation_resource_claims (
  id uuid primary key default gen_random_uuid(),
  allocation_id uuid not null references gpuaas_allocations(id) on delete cascade,
  node_id uuid not null references gpuaas_nodes(id) on delete restrict,
  slot_id uuid null references gpuaas_node_resource_slots(id) on delete restrict,
  claim_kind text not null check (claim_kind in ('node_exclusive','slot')),
  status text not null default 'reserved' check (status in ('reserved','provisioning','active','releasing','released','failed')),
  resource_snapshot jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  released_at timestamptz null,
  check (
    (claim_kind = 'node_exclusive' and slot_id is null)
    or (claim_kind = 'slot' and slot_id is not null)
  )
);

create index if not exists ix_gpuaas_allocation_resource_claims_allocation
  on gpuaas_allocation_resource_claims(allocation_id, status);

create index if not exists ix_gpuaas_allocation_resource_claims_node_status
  on gpuaas_allocation_resource_claims(node_id, status);

create unique index if not exists ux_gpuaas_allocation_resource_claims_active_node_exclusive
  on gpuaas_allocation_resource_claims(node_id)
  where claim_kind = 'node_exclusive'
    and status in ('reserved','provisioning','active','releasing');

create unique index if not exists ux_gpuaas_allocation_resource_claims_active_slot
  on gpuaas_allocation_resource_claims(slot_id)
  where claim_kind = 'slot'
    and status in ('reserved','provisioning','active','releasing');

create table if not exists gpuaas_node_tasks (
  id uuid primary key default gen_random_uuid(),
  node_id uuid not null references gpuaas_nodes(id),
  task_type text not null,
  params jsonb not null default '{}'::jsonb,
  correlation_id text not null,
  issued_at timestamptz not null default now(),
  expires_at timestamptz not null,
  signature text not null,
  status text not null default 'queued' check (status in ('queued','dispatched','completed','failed','rejected','expired')),
  dispatched_at timestamptz null,
  claimed_by_remote_ip text null,
  claimed_by_forwarded_for text null,
  claimed_by_user_agent text null,
  claimed_by_instance_id text null,
  claimed_by_tls_node_id text null,
  claimed_by_tls_serial text null,
  completed_at timestamptz null,
  completed_by_remote_ip text null,
  completed_by_forwarded_for text null,
  completed_by_user_agent text null,
  completed_by_instance_id text null,
  completed_by_tls_node_id text null,
  completed_by_tls_serial text null,
  error text null,
  output jsonb null,
  temporal_task_token bytea null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  check (expires_at > issued_at)
);

alter table if exists gpuaas_node_tasks add column if not exists claimed_by_remote_ip text null;
alter table if exists gpuaas_node_tasks add column if not exists claimed_by_forwarded_for text null;
alter table if exists gpuaas_node_tasks add column if not exists claimed_by_user_agent text null;
alter table if exists gpuaas_node_tasks add column if not exists claimed_by_instance_id text null;
alter table if exists gpuaas_node_tasks add column if not exists claimed_by_tls_node_id text null;
alter table if exists gpuaas_node_tasks add column if not exists claimed_by_tls_serial text null;
alter table if exists gpuaas_node_tasks add column if not exists completed_by_remote_ip text null;
alter table if exists gpuaas_node_tasks add column if not exists completed_by_forwarded_for text null;
alter table if exists gpuaas_node_tasks add column if not exists completed_by_user_agent text null;
alter table if exists gpuaas_node_tasks add column if not exists completed_by_instance_id text null;
alter table if exists gpuaas_node_tasks add column if not exists completed_by_tls_node_id text null;
alter table if exists gpuaas_node_tasks add column if not exists completed_by_tls_serial text null;

create table if not exists platform_iam_service_accounts (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references platform_iam_organizations(id),
  department_id uuid null,
  project_id uuid not null,
  name text not null,
  slug text not null,
  description text null,
  purpose text not null default 'operator' check (purpose in ('operator','controller','automation')),
  state text not null default 'active' check (state in ('active','disabled','deleted')),
  created_by_user_id uuid not null references platform_iam_users(id),
  created_at timestamptz not null default now(),
  disabled_at timestamptz null,
  deleted_at timestamptz null,
  unique (project_id, slug),
  constraint fk_service_accounts_project_org
    foreign key (project_id, org_id) references platform_iam_projects(id, org_id)
);

alter table if exists platform_iam_service_accounts
  add column if not exists department_id uuid null;

create or replace function platform_iam_assign_service_account_department()
returns trigger
language plpgsql
as $$
declare
  v_department_id uuid;
begin
  select department_id into v_department_id
  from platform_iam_projects
  where id = new.project_id
    and org_id = new.org_id
    and deleted_at is null;

  if v_department_id is null then
    raise exception 'service account project must belong to organization and have department attribution'
      using errcode = '23514';
  end if;

  if new.department_id is null then
    new.department_id := v_department_id;
  end if;

  if new.department_id <> v_department_id then
    raise exception 'service account department must match project department'
      using errcode = '23514';
  end if;

  return new;
end;
$$;

drop trigger if exists trg_platform_iam_service_accounts_department on platform_iam_service_accounts;
create trigger trg_platform_iam_service_accounts_department
before insert or update of org_id, project_id, department_id on platform_iam_service_accounts
for each row
execute function platform_iam_assign_service_account_department();

update platform_iam_service_accounts sa
set department_id = p.department_id
from platform_iam_projects p
where p.id = sa.project_id
  and p.org_id = sa.org_id
  and sa.department_id is null;

do $$
begin
  if not exists (
    select 1
    from pg_constraint
    where conname = 'fk_service_accounts_department_org'
      and conrelid = 'platform_iam_service_accounts'::regclass
  ) then
    alter table platform_iam_service_accounts
      add constraint fk_service_accounts_department_org
      foreign key (department_id, org_id) references platform_iam_departments(id, org_id)
      not valid;
  end if;
end $$;

alter table if exists platform_iam_service_accounts
  alter column department_id set not null;

create unique index if not exists ux_platform_iam_service_accounts_project_slug
  on platform_iam_service_accounts(project_id, slug);

create index if not exists ix_platform_iam_service_accounts_department
  on platform_iam_service_accounts(department_id);

alter table platform_iam_service_accounts add column if not exists purpose text not null default 'operator';
do $$
begin
  alter table platform_iam_service_accounts
    add constraint service_accounts_purpose_check
    check (purpose in ('operator','controller','automation'));
exception
  when duplicate_object then null;
end $$;

create table if not exists platform_secret_service_account_credentials (
  id uuid primary key default gen_random_uuid(),
  service_account_id uuid not null references platform_iam_service_accounts(id) on delete cascade,
  key_id text not null,
  public_jwk jsonb not null,
  private_key_enc jsonb not null,
  algorithm text not null,
  state text not null check (state in ('active','rotated','revoked')),
  created_at timestamptz not null default now(),
  expires_at timestamptz null,
  revoked_at timestamptz null,
  unique (service_account_id, key_id)
);

do $$
begin
  if not exists (
    select 1
    from pg_constraint
    where conname = 'fk_gpuaas_access_ssh_public_keys_created_by_service_account'
      and conrelid = 'gpuaas_access_ssh_public_keys'::regclass
  ) then
    alter table gpuaas_access_ssh_public_keys
      add constraint fk_gpuaas_access_ssh_public_keys_created_by_service_account
      foreign key (created_by_service_account_id)
      references platform_iam_service_accounts(id)
      on delete set null;
  end if;
end $$;

create table if not exists platform_secret_access_credentials (
  id uuid primary key default gen_random_uuid(),
  display_name text not null,
  description text null,
  scope_type text not null check (scope_type in ('platform','tenant','project')),
  scope_id uuid null,
  resource_type text null,
  resource_id text null,
  operational_domain text not null check (operational_domain in ('platform-control','platform-ops','tenant-runtime')),
  credential_kind text not null check (credential_kind in ('ssh_key','password','token','certificate_bundle')),
  usage_tags jsonb not null default '[]'::jsonb,
  principal_username text null,
  custody_backend text not null check (custody_backend in ('vault')),
  custody_instance text not null,
  vault_path text null,
  delivery_mode text not null check (delivery_mode in ('vault_ref','vault_wrapped_token')),
  status text not null check (status in ('active','disabled','rotation_pending','expired','deleted')),
  last_validated_at timestamptz null,
  last_used_at timestamptz null,
  rotation_due_at timestamptz null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  deleted_at timestamptz null,
  check (
    (scope_type = 'platform' and scope_id is null)
    or (scope_type in ('tenant','project') and scope_id is not null)
  ),
  check (
    vault_path is null
    or length(trim(vault_path)) > 0
  ),
  check (jsonb_typeof(usage_tags) = 'array')
);

create unique index if not exists ux_access_credentials_vault_path
  on platform_secret_access_credentials(vault_path)
  where vault_path is not null;

create index if not exists ix_access_credentials_scope_active
  on platform_secret_access_credentials(scope_type, scope_id, status, created_at desc)
  where deleted_at is null;

create index if not exists ix_access_credentials_operational_domain_active
  on platform_secret_access_credentials(operational_domain, custody_instance, status, created_at desc)
  where deleted_at is null;

create table if not exists platform_secret_access_credential_bindings (
  id uuid primary key default gen_random_uuid(),
  credential_id uuid not null references platform_secret_access_credentials(id) on delete cascade,
  resource_type text not null,
  resource_id text not null,
  purpose text not null,
  usage_tags jsonb not null default '[]'::jsonb,
  created_at timestamptz not null default now(),
  deleted_at timestamptz null,
  check (length(trim(resource_type)) > 0),
  check (length(trim(resource_id)) > 0),
  check (length(trim(purpose)) > 0),
  check (jsonb_typeof(usage_tags) = 'array')
);

create unique index if not exists ux_platform_secret_access_credential_bindings_active_target
  on platform_secret_access_credential_bindings(credential_id, resource_type, resource_id, purpose)
  where deleted_at is null;

create index if not exists ix_access_credential_bindings_resource_active
  on platform_secret_access_credential_bindings(resource_type, resource_id, created_at desc)
  where deleted_at is null;

create table if not exists gpuaas_node_agent_lifecycles (
  lifecycle_id uuid primary key default gen_random_uuid(),
  node_id uuid not null references gpuaas_nodes(id) on delete cascade,
  requested_by_user_id uuid null references platform_iam_users(id) on delete set null,
  mode text not null check (mode in ('reimage','manual_install','rebootstrap')),
  scenario text not null check (scenario in ('bootstrap_install','in_place_upgrade','repair_reinstall','certificate_repair','drift_reconcile')),
  status text not null check (status in ('pending','running','completed','failed_retryable','failed_manual_intervention','cancelled')),
  desired_agent_version text null,
  reported_agent_version text null,
  safety_policy text not null check (safety_policy in ('idle_only','drain_then_upgrade','force')),
  error_code text null,
  error_message text null,
  error_details jsonb not null default '{}'::jsonb,
  correlation_id text not null,
  requested_at timestamptz not null default now(),
  started_at timestamptz null,
  completed_at timestamptz null,
  updated_at timestamptz not null default now()
);

alter table if exists gpuaas_node_agent_lifecycles
  drop constraint if exists node_agent_lifecycles_mode_check;
alter table if exists gpuaas_node_agent_lifecycles
  add constraint node_agent_lifecycles_mode_check
  check (mode in ('reimage','manual_install','rebootstrap'));

alter table if exists gpuaas_node_agent_lifecycles
  drop constraint if exists node_agent_lifecycles_scenario_check;
alter table if exists gpuaas_node_agent_lifecycles
  add constraint node_agent_lifecycles_scenario_check
  check (scenario in ('bootstrap_install','in_place_upgrade','repair_reinstall','certificate_repair','drift_reconcile'));

create index if not exists ix_node_agent_lifecycles_node_requested
  on gpuaas_node_agent_lifecycles(node_id, requested_at desc);

create index if not exists ix_node_agent_lifecycles_status_requested
  on gpuaas_node_agent_lifecycles(status, requested_at desc);

-- App control plane (Phase 1 baseline): platform catalog, project entitlements,
-- and app instance lifecycle records.
create table if not exists appplatform_catalog (
  id uuid primary key default gen_random_uuid(),
  slug text not null unique check (slug ~ '^[a-z0-9][a-z0-9-]{1,62}$'),
  display_name text not null,
  category text not null,
  publisher text not null,
  status text not null check (status in ('active', 'deprecated', 'disabled')),
  description text null,
  logo_url text null,
  tags text[] null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

-- Idempotent column additions for existing databases (applied after initial schema creation).
alter table appplatform_catalog add column if not exists description text null;
alter table appplatform_catalog add column if not exists logo_url text null;
alter table appplatform_catalog add column if not exists tags text[] null;

create table if not exists appplatform_versions (
  id uuid primary key default gen_random_uuid(),
  app_id uuid not null references appplatform_catalog(id) on delete cascade,
  version text not null,
  runtime_backend text not null check (runtime_backend in ('k8s', 'rke2', 'slurm', 'ray', 'bare_metal')),
  manifest jsonb not null default '{}'::jsonb,
  status text not null check (status in ('active', 'deprecated', 'disabled')),
  created_at timestamptz not null default now(),
  unique (app_id, version),
  unique (id, app_id)
);

-- Keep existing databases aligned when the runtime backend catalog expands.
alter table appplatform_versions drop constraint if exists appplatform_versions_runtime_backend_check;
alter table appplatform_versions add constraint appplatform_versions_runtime_backend_check
  check (runtime_backend in ('k8s', 'rke2', 'slurm', 'ray', 'bare_metal'));

create table if not exists appplatform_project_entitlements (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references platform_iam_organizations(id) on delete cascade,
  project_id uuid not null references platform_iam_projects(id) on delete cascade,
  app_id uuid not null references appplatform_catalog(id) on delete cascade,
  enabled boolean not null default true,
  policy_overrides jsonb not null default '{}'::jsonb,
  updated_by_user_id uuid null references platform_iam_users(id),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (project_id, app_id),
  constraint fk_appplatform_project_entitlements_project_org
    foreign key (project_id, org_id) references platform_iam_projects(id, org_id)
);

create table if not exists appplatform_instances (
  id uuid primary key default gen_random_uuid(),
  resource_name text not null unique,
  org_id uuid not null references platform_iam_organizations(id) on delete cascade,
  project_id uuid not null references platform_iam_projects(id) on delete cascade,
  app_id uuid not null references appplatform_catalog(id) on delete restrict,
  app_version_id uuid not null references appplatform_versions(id) on delete restrict,
  app_artifact_id uuid null,
  display_name text not null,
  operating_mode text not null default 'tenant_dedicated' check (operating_mode in ('tenant_dedicated', 'platform_managed')),
  control_plane_scope text not null default 'project' check (control_plane_scope in ('project', 'tenant', 'platform')),
  tenant_boundary_mode text not null default 'tenant_isolated' check (tenant_boundary_mode in ('tenant_isolated', 'shared_service')),
  status text not null check (status in ('requested', 'deploying', 'running', 'stopping', 'stopped', 'starting', 'restarting', 'upgrading', 'rolling_back', 'failed', 'decommissioning', 'decommissioned', 'deleting', 'deleted')),
  requested_by_user_id uuid null references platform_iam_users(id),
  requested_by_service_account_id uuid null references platform_iam_service_accounts(id) on delete set null,
  operator_service_account_id uuid null references platform_iam_service_accounts(id) on delete set null,
  placement_intent jsonb not null default '{}'::jsonb,
  config jsonb not null default '{}'::jsonb,
  runtime_state jsonb not null default '{}'::jsonb,
  failure_reason text null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  deleted_at timestamptz null,
  constraint fk_appplatform_instances_project_org
    foreign key (project_id, org_id) references platform_iam_projects(id, org_id),
  constraint fk_appplatform_instances_version_app
    foreign key (app_version_id, app_id) references appplatform_versions(id, app_id)
);

alter table appplatform_instances add column if not exists operating_mode text not null default 'tenant_dedicated';
alter table appplatform_instances add column if not exists control_plane_scope text not null default 'project';
alter table appplatform_instances add column if not exists tenant_boundary_mode text not null default 'tenant_isolated';
alter table appplatform_instances add column if not exists requested_by_service_account_id uuid null references platform_iam_service_accounts(id) on delete set null;
alter table appplatform_instances add column if not exists placement_intent jsonb not null default '{}'::jsonb;
alter table appplatform_instances alter column requested_by_user_id drop not null;
alter table appplatform_instances drop constraint if exists appplatform_instances_status_check;
alter table appplatform_instances add constraint appplatform_instances_status_check
  check (status in ('requested', 'deploying', 'running', 'stopping', 'stopped', 'starting', 'restarting', 'upgrading', 'rolling_back', 'failed', 'decommissioning', 'decommissioned', 'deleting', 'deleted'));

do $$
begin
  if not exists (
    select 1
    from pg_constraint
    where conname = 'appplatform_instances_requested_by_identity_check'
      and conrelid = 'public.appplatform_instances'::regclass
  ) then
    alter table public.appplatform_instances
      add constraint appplatform_instances_requested_by_identity_check
      check (num_nonnulls(requested_by_user_id, requested_by_service_account_id) = 1);
  end if;
end $$;

-- Tenant-owned shared app runtimes (reserved contract-first baseline).
-- These tables are additive schema direction for tenant-owned shared mode and
-- are not yet wired into runtime services.
create table if not exists appplatform_shared_runtimes (
  id uuid primary key default gen_random_uuid(),
  resource_name text not null unique,
  org_id uuid not null references platform_iam_organizations(id) on delete cascade,
  app_id uuid not null references appplatform_catalog(id) on delete restrict,
  app_version_id uuid not null references appplatform_versions(id) on delete restrict,
  display_name text not null,
  operating_mode text not null default 'tenant_dedicated' check (operating_mode in ('tenant_dedicated')),
  control_plane_scope text not null default 'tenant' check (control_plane_scope in ('tenant')),
  status text not null check (status in ('requested', 'deploying', 'running', 'upgrading', 'rolling_back', 'failed', 'deleting', 'deleted')),
  operator_identity_ref text null,
  placement_intent jsonb not null default '{}'::jsonb,
  config jsonb not null default '{}'::jsonb,
  runtime_state jsonb not null default '{}'::jsonb,
  failure_reason text null,
  created_by_user_id uuid null references platform_iam_users(id) on delete set null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  deleted_at timestamptz null,
  constraint fk_appplatform_shared_runtimes_version_app
    foreign key (app_version_id, app_id) references appplatform_versions(id, app_id)
);

create index if not exists ix_appplatform_shared_runtimes_org_status_created
  on appplatform_shared_runtimes(org_id, status, created_at desc)
  where deleted_at is null;

create table if not exists appplatform_shared_runtime_attachments (
  id uuid primary key default gen_random_uuid(),
  shared_runtime_id uuid not null references appplatform_shared_runtimes(id) on delete cascade,
  org_id uuid not null references platform_iam_organizations(id) on delete cascade,
  project_id uuid not null,
  status text not null check (status in ('requested', 'attached', 'detaching', 'detached', 'failed')),
  allow_worker_contribution boolean not null default false,
  allow_job_submission boolean not null default false,
  policy jsonb not null default '{}'::jsonb,
  runtime_state jsonb not null default '{}'::jsonb,
  failure_reason text null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  deleted_at timestamptz null,
  unique (shared_runtime_id, project_id),
  constraint fk_shared_runtime_attachments_project_org
    foreign key (project_id, org_id) references platform_iam_projects(id, org_id)
);

create index if not exists ix_shared_runtime_attachments_runtime_status_created
  on appplatform_shared_runtime_attachments(shared_runtime_id, status, created_at desc)
  where deleted_at is null;

create index if not exists ix_shared_runtime_attachments_org_project_status
  on appplatform_shared_runtime_attachments(org_id, project_id, status, created_at desc)
  where deleted_at is null;

create table if not exists appplatform_shared_runtime_workers (
  id uuid primary key default gen_random_uuid(),
  shared_runtime_id uuid not null references appplatform_shared_runtimes(id) on delete cascade,
  org_id uuid not null references platform_iam_organizations(id) on delete cascade,
  source_project_id uuid null references platform_iam_projects(id) on delete set null,
  attachment_id uuid null references appplatform_shared_runtime_attachments(id) on delete set null,
  component_key text not null,
  contribution_mode text not null check (contribution_mode in ('tenant_owned', 'project_contributed')),
  allocation_id uuid not null references gpuaas_allocations(id) on delete restrict,
  bound_node_id uuid null references gpuaas_nodes(id) on delete set null,
  status text not null check (status in ('requested', 'provisioning', 'ready', 'draining', 'removed', 'failed')),
  runtime_state jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  deleted_at timestamptz null
);

create unique index if not exists ux_shared_runtime_workers_runtime_allocation_active
  on appplatform_shared_runtime_workers(shared_runtime_id, allocation_id)
  where deleted_at is null;

create index if not exists ix_shared_runtime_workers_runtime_status_created
  on appplatform_shared_runtime_workers(shared_runtime_id, status, created_at desc)
  where deleted_at is null;

create index if not exists ix_shared_runtime_workers_org_project_status
  on appplatform_shared_runtime_workers(org_id, source_project_id, status, created_at desc)
  where deleted_at is null;

create table if not exists appplatform_shared_runtime_worker_operations (
  id uuid primary key default gen_random_uuid(),
  shared_runtime_id uuid not null references appplatform_shared_runtimes(id) on delete cascade,
  org_id uuid not null references platform_iam_organizations(id) on delete cascade,
  action text not null check (action in ('add', 'drain', 'remove')),
  attachment_id uuid null references appplatform_shared_runtime_attachments(id) on delete set null,
  source_project_id uuid null references platform_iam_projects(id) on delete set null,
  requested_allocation_ids jsonb not null default '[]'::jsonb,
  status text not null check (status in ('accepted', 'in_progress', 'succeeded', 'failed', 'cancelled')),
  correlation_id text not null,
  requested_by_user_id uuid null references platform_iam_users(id) on delete set null,
  last_error_code text null,
  last_error_message text null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  started_at timestamptz null,
  completed_at timestamptz null
);

create index if not exists ix_shared_runtime_worker_ops_runtime_status_created
  on appplatform_shared_runtime_worker_operations(shared_runtime_id, status, created_at desc);

create table if not exists appplatform_shared_runtime_operator_credentials (
  id uuid primary key default gen_random_uuid(),
  shared_runtime_id uuid not null references appplatform_shared_runtimes(id) on delete cascade,
  key_id text not null,
  public_jwk jsonb not null,
  private_key_enc jsonb not null,
  algorithm text not null,
  state text not null check (state in ('active', 'rotated', 'revoked')),
  created_at timestamptz not null default now(),
  expires_at timestamptz null,
  revoked_at timestamptz null,
  unique (shared_runtime_id, key_id)
);

create index if not exists ix_appplatform_shared_runtime_operator_credentials_runtime_state
  on appplatform_shared_runtime_operator_credentials(shared_runtime_id, state, created_at desc);

create table if not exists appplatform_artifacts (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references platform_iam_organizations(id) on delete cascade,
  project_id uuid not null references platform_iam_projects(id) on delete cascade,
  app_slug text not null check (app_slug ~ '^[a-z0-9][a-z0-9-]{1,62}$'),
  app_version text not null,
  artifact_name text not null,
  artifact_kind text not null check (artifact_kind in ('oci', 'blob')),
  source_type text not null check (source_type in ('oci_registry', 's3', 'gcs', 'azure_blob', 'https', 'huggingface', 'artifact_store')),
  publish_intent_id uuid null,
  repository text not null,
  source_uri text null,
  digest text not null check (digest ~ '^sha256:[a-f0-9]{64}$'),
  digest_algorithm text not null default 'sha256' check (digest_algorithm in ('sha256')),
  tag text null,
  media_type text not null,
  platform_os text not null default 'linux' check (platform_os in ('linux')),
  platform_arch text not null check (platform_arch in ('amd64','arm64')),
  platform_variant text null,
  lifecycle_state text not null check (lifecycle_state in ('published', 'promoted', 'deprecated', 'retired')),
  trust_state text not null check (trust_state in ('unverified', 'verified', 'failed_verification', 'revoked')),
  verification_method text null,
  verification_key_id text null,
  provenance_digest text null,
  verification_details jsonb null,
  verified_at timestamptz null,
  promoted_channel text null,
  target_environment text null,
  requested_by_user_id uuid null references platform_iam_users(id),
  registered_by_service_account_id uuid null references platform_iam_service_accounts(id) on delete set null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint fk_appplatform_artifacts_project_org
    foreign key (project_id, org_id) references platform_iam_projects(id, org_id),
  constraint uq_appplatform_artifacts_project_digest
    unique (project_id, digest)
);

create table if not exists appplatform_artifact_publish_intents (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references platform_iam_organizations(id) on delete cascade,
  project_id uuid not null references platform_iam_projects(id) on delete cascade,
  app_slug text not null check (app_slug ~ '^[a-z0-9][a-z0-9-]{1,62}$'),
  app_version text not null,
  artifact_name text not null,
  artifact_kind text not null check (artifact_kind in ('oci', 'blob')),
  source_type text not null check (source_type in ('oci_registry', 's3', 'gcs', 'azure_blob', 'https', 'huggingface', 'artifact_store')),
  repository text not null,
  upload_method text not null check (upload_method in ('oci_push', 'blob_upload', 'external_register')),
  tag text null,
  platform_os text not null default 'linux' check (platform_os in ('linux')),
  platform_arch text not null check (platform_arch in ('amd64','arm64')),
  platform_variant text null,
  digest_required boolean not null default true,
  channel text null,
  expires_at timestamptz not null,
  requested_by_user_id uuid null references platform_iam_users(id),
  requested_by_service_account_id uuid null references platform_iam_service_accounts(id) on delete set null,
  state text not null default 'issued' check (state in ('issued', 'consumed', 'expired', 'revoked')),
  created_at timestamptz not null default now(),
  constraint fk_appplatform_artifact_publish_intents_project_org
    foreign key (project_id, org_id) references platform_iam_projects(id, org_id),
  constraint appplatform_artifact_publish_intents_requested_by_identity_check
    check (num_nonnulls(requested_by_user_id, requested_by_service_account_id) = 1)
);

alter table appplatform_artifacts add column if not exists publish_intent_id uuid null references appplatform_artifact_publish_intents(id) on delete set null;
alter table appplatform_artifacts add column if not exists verification_method text null;
alter table appplatform_artifacts add column if not exists verification_key_id text null;
alter table appplatform_artifacts add column if not exists provenance_digest text null;
alter table appplatform_artifacts add column if not exists verification_details jsonb null;
alter table appplatform_artifacts add column if not exists verified_at timestamptz null;
alter table appplatform_artifacts add column if not exists platform_os text not null default 'linux';
alter table appplatform_artifacts add column if not exists platform_arch text not null default 'amd64';
alter table appplatform_artifacts add column if not exists platform_variant text null;
alter table appplatform_artifacts drop constraint if exists appplatform_artifacts_platform_os_check;
alter table appplatform_artifacts add constraint appplatform_artifacts_platform_os_check
  check (platform_os in ('linux'));
alter table appplatform_artifacts drop constraint if exists appplatform_artifacts_platform_arch_check;
alter table appplatform_artifacts add constraint appplatform_artifacts_platform_arch_check
  check (platform_arch in ('amd64','arm64'));
alter table appplatform_artifact_publish_intents add column if not exists platform_os text not null default 'linux';
alter table appplatform_artifact_publish_intents add column if not exists platform_arch text not null default 'amd64';
alter table appplatform_artifact_publish_intents add column if not exists platform_variant text null;
alter table appplatform_artifact_publish_intents drop constraint if exists appplatform_artifact_publish_intents_platform_os_check;
alter table appplatform_artifact_publish_intents add constraint appplatform_artifact_publish_intents_platform_os_check
  check (platform_os in ('linux'));
alter table appplatform_artifact_publish_intents drop constraint if exists appplatform_artifact_publish_intents_platform_arch_check;
alter table appplatform_artifact_publish_intents add constraint appplatform_artifact_publish_intents_platform_arch_check
  check (platform_arch in ('amd64','arm64'));
alter table appplatform_instances add column if not exists app_artifact_id uuid null references appplatform_artifacts(id) on delete set null;

alter table appplatform_artifact_publish_intents alter column requested_by_user_id drop not null;
alter table appplatform_artifact_publish_intents add column if not exists requested_by_service_account_id uuid null references platform_iam_service_accounts(id) on delete set null;
do $$
begin
  if not exists (
    select 1
    from pg_constraint
    where conname = 'appplatform_artifact_publish_intents_requested_by_identity_check'
  ) then
    alter table appplatform_artifact_publish_intents
      add constraint appplatform_artifact_publish_intents_requested_by_identity_check
      check (num_nonnulls(requested_by_user_id, requested_by_service_account_id) = 1);
  end if;
end $$;

create index if not exists ix_appplatform_catalog_status_slug
  on appplatform_catalog (status, slug);

create index if not exists ix_appplatform_versions_app_status_created
  on appplatform_versions (app_id, status, created_at desc);

create index if not exists ix_appplatform_project_entitlements_project
  on appplatform_project_entitlements (project_id);

create index if not exists ix_appplatform_instances_project_status_created
  on appplatform_instances (project_id, status, created_at desc);

create index if not exists ix_appplatform_instances_org_status
  on appplatform_instances (org_id, status);

create table if not exists appplatform_instance_proxy_routes (
  id uuid primary key default gen_random_uuid(),
  app_instance_id uuid not null references appplatform_instances(id) on delete cascade,
  org_id uuid not null references platform_iam_organizations(id) on delete cascade,
  project_id uuid not null references platform_iam_projects(id) on delete cascade,
  endpoint_name text not null,
  protocol text not null check (protocol in ('http', 'https', 'ws', 'wss')),
  exposure_mode text not null default 'platform_proxy' check (exposure_mode in ('platform_proxy')),
  route_path text not null,
  route_url text null,
  target_kind text not null default 'app_instance_endpoint' check (target_kind in ('app_instance_endpoint')),
  target_host text null,
  target_port integer not null check (target_port > 0 and target_port <= 65535),
  target_metadata jsonb not null default '{}'::jsonb,
  status text not null default 'pending' check (status in ('pending', 'active', 'failed', 'deleting', 'deleted')),
  failure_reason text null,
  last_reconciled_at timestamptz null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  deleted_at timestamptz null
);

create unique index if not exists ux_appplatform_proxy_routes_instance_endpoint_active
  on appplatform_instance_proxy_routes(app_instance_id, endpoint_name)
  where deleted_at is null;

create index if not exists ix_app_proxy_routes_project_status_created
  on appplatform_instance_proxy_routes(project_id, status, created_at desc)
  where deleted_at is null;

create index if not exists ix_app_proxy_routes_instance_status
  on appplatform_instance_proxy_routes(app_instance_id, status, created_at desc)
  where deleted_at is null;

create table if not exists appplatform_instance_members (
  id uuid primary key default gen_random_uuid(),
  app_instance_id uuid not null references appplatform_instances(id) on delete cascade,
  org_id uuid not null references platform_iam_organizations(id) on delete cascade,
  project_id uuid not null references platform_iam_projects(id) on delete cascade,
  component_key text not null,
  status text not null check (status in ('requested', 'reconciling', 'ready', 'draining', 'deleting', 'failed', 'deleted')),
  bound_node_id uuid null references gpuaas_nodes(id) on delete set null,
  adapter_detail jsonb null,
  last_operation_id uuid null,
  last_correlation_id text null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  deleted_at timestamptz null,
  constraint fk_appplatform_instance_members_project_org
    foreign key (project_id, org_id) references platform_iam_projects(id, org_id)
);

create index if not exists ix_appplatform_instance_members_instance_status
  on appplatform_instance_members (app_instance_id, status, created_at desc);

create index if not exists ix_appplatform_instance_members_project_component
  on appplatform_instance_members (project_id, component_key, created_at desc);

create table if not exists appplatform_instance_member_operations (
  id uuid primary key default gen_random_uuid(),
  app_instance_id uuid not null references appplatform_instances(id) on delete cascade,
  org_id uuid not null references platform_iam_organizations(id) on delete cascade,
  project_id uuid not null references platform_iam_projects(id) on delete cascade,
  action text not null check (action in ('add', 'drain', 'remove', 'replace')),
  component_key text null,
  target_member_id uuid null references appplatform_instance_members(id) on delete set null,
  requested_count integer null,
  allocation_intent jsonb null,
  status text not null check (status in ('accepted', 'in_progress', 'succeeded', 'failed', 'cancelled')),
  correlation_id text not null,
  requested_by_user_id uuid null references platform_iam_users(id),
  requested_by_service_account_id uuid null references platform_iam_service_accounts(id) on delete set null,
  last_error_code text null,
  last_error_message text null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  started_at timestamptz null,
  completed_at timestamptz null,
  constraint fk_appplatform_instance_member_operations_project_org
    foreign key (project_id, org_id) references platform_iam_projects(id, org_id),
  constraint appplatform_instance_member_operations_requested_by_identity_check
    check (num_nonnulls(requested_by_user_id, requested_by_service_account_id) = 1),
  constraint appplatform_instance_member_operations_requested_count_positive
    check (requested_count is null or requested_count > 0)
);

create index if not exists ix_appplatform_instance_member_operations_instance_created
  on appplatform_instance_member_operations (app_instance_id, created_at desc);

create index if not exists ix_appplatform_instance_member_operations_project_status
  on appplatform_instance_member_operations (project_id, status, created_at desc);

do $$
begin
  if not exists (
    select 1
    from pg_constraint
    where conname = 'fk_appplatform_instance_members_last_operation'
      and conrelid = 'public.appplatform_instance_members'::regclass
  ) then
    alter table public.appplatform_instance_members
      add constraint fk_appplatform_instance_members_last_operation
      foreign key (last_operation_id) references appplatform_instance_member_operations(id) on delete set null;
  end if;
end $$;

create index if not exists ix_appplatform_artifacts_project_state_created
  on appplatform_artifacts (project_id, lifecycle_state, created_at desc);

create index if not exists ix_appplatform_artifacts_org_slug_version
  on appplatform_artifacts (org_id, app_slug, app_version);

create index if not exists ix_appplatform_artifact_publish_intents_project_expires
  on appplatform_artifact_publish_intents (project_id, expires_at desc);

create index if not exists ix_node_tasks_node_queued
  on gpuaas_node_tasks(node_id, created_at)
  where status = 'queued';

create index if not exists ix_node_tasks_status_expires
  on gpuaas_node_tasks(status, expires_at);

create index if not exists ix_node_tasks_allocation_id
  on gpuaas_node_tasks ((params->>'allocation_id'))
  where params ? 'allocation_id';

create table if not exists platform_billing_usage_records (
  id uuid primary key default gen_random_uuid(),
  org_id uuid null references platform_iam_organizations(id),
  department_id uuid null references platform_iam_departments(id),
  project_id uuid null references platform_iam_projects(id),
  allocation_id uuid null references gpuaas_allocations(id),
  app_instance_id uuid null references appplatform_instances(id),
  requested_by_user_id uuid not null references platform_iam_users(id),
  usage_source text not null default 'allocation' check (usage_source in ('allocation','app_runtime')),
  usage_unit text not null default 'gpu_hour',
  region_code text null,
  sku text null references gpuaas_inventory_sku_catalog(sku),
  start_time timestamptz not null,
  end_time timestamptz null,
  last_billed_at timestamptz null,
  control_plane_component boolean not null default false,
  operating_mode text null check (operating_mode is null or operating_mode in ('tenant_dedicated','platform_managed')),
  control_plane_scope text null check (control_plane_scope is null or control_plane_scope in ('project','tenant','platform')),
  runtime_backend text null check (runtime_backend is null or runtime_backend in ('k8s','rke2','slurm','ray','bare_metal')),
  correlation_id text null,
  metadata jsonb not null default '{}'::jsonb,
  accrued_cost_minor bigint not null default 0 check (accrued_cost_minor >= 0),
  currency text not null default 'USD' check (currency ~ '^[A-Z]{3}$'),
  check (end_time is null or end_time > start_time),
  check (last_billed_at is null or last_billed_at >= start_time),
  check (usage_source <> 'allocation' or (region_code is not null and sku is not null)),
  check (
    (usage_source = 'allocation' and allocation_id is not null)
    or (usage_source = 'app_runtime' and app_instance_id is not null)
  )
);

alter table platform_billing_usage_records alter column allocation_id drop not null;
alter table platform_billing_usage_records alter column region_code drop not null;
alter table platform_billing_usage_records alter column sku drop not null;
alter table platform_billing_usage_records add column if not exists department_id uuid null references platform_iam_departments(id);
alter table platform_billing_usage_records add column if not exists app_instance_id uuid null references appplatform_instances(id);
alter table platform_billing_usage_records add column if not exists usage_source text not null default 'allocation';
alter table platform_billing_usage_records add column if not exists usage_unit text not null default 'gpu_hour';
alter table platform_billing_usage_records add column if not exists control_plane_component boolean not null default false;
alter table platform_billing_usage_records add column if not exists operating_mode text null;
alter table platform_billing_usage_records add column if not exists control_plane_scope text null;
alter table platform_billing_usage_records add column if not exists runtime_backend text null;
alter table platform_billing_usage_records add column if not exists correlation_id text null;
alter table platform_billing_usage_records add column if not exists metadata jsonb not null default '{}'::jsonb;

do $$
begin
  if not exists (
    select 1
    from pg_constraint
    where conname = 'ck_platform_billing_usage_records_allocation_requires_region_sku'
      and conrelid = 'platform_billing_usage_records'::regclass
  ) then
    alter table platform_billing_usage_records
      add constraint ck_platform_billing_usage_records_allocation_requires_region_sku
      check (usage_source <> 'allocation' or (region_code is not null and sku is not null));
  end if;
end $$;

create table if not exists platform_billing_rated_usage_lines (
  id uuid primary key default gen_random_uuid(),
  usage_record_id uuid not null references platform_billing_usage_records(id),
  org_id uuid null references platform_iam_organizations(id),
  project_id uuid null references platform_iam_projects(id),
  requested_by_user_id uuid not null references platform_iam_users(id),
  usage_source text not null check (usage_source in ('allocation','app_runtime')),
  usage_unit text not null default 'gpu_hour',
  bill_from timestamptz not null,
  bill_to timestamptz not null,
  unit_quantity_millis bigint not null check (unit_quantity_millis >= 0),
  unit_price_minor bigint not null check (unit_price_minor >= 0),
  units bigint not null default 1 check (units >= 0),
  amount_minor bigint not null check (amount_minor >= 0),
  currency text not null default 'USD' check (currency ~ '^[A-Z]{3}$'),
  pricing_source text not null default 'sku_catalog',
  pricing_plan_id uuid null,
  pricing_plan_version text null,
  pricing_snapshot jsonb not null default '{}'::jsonb,
  fx_snapshot jsonb null,
  rating_key text not null unique,
  ledger_entry_id uuid null,
  created_at timestamptz not null default now(),
  check (bill_to >= bill_from)
);

alter table if exists platform_billing_rated_usage_lines
  add column if not exists fx_snapshot jsonb null;

create table if not exists platform_billing_ledger_entries (
  id uuid primary key default gen_random_uuid(),
  org_id uuid null references platform_iam_organizations(id),
  project_id uuid null references platform_iam_projects(id),
  requested_by_user_id uuid not null references platform_iam_users(id),
  entry_type text not null check (entry_type in ('usage_debit','stripe_credit','admin_credit','refund_credit','adjustment','chargeback_debit','chargeback_reversal')),
  amount_minor bigint not null check (amount_minor <> 0),
  currency text not null default 'USD' check (currency ~ '^[A-Z]{3}$'),
  reference_type text null,
  reference_id text null,
  metadata jsonb null,
  created_at timestamptz not null default now()
);

alter table if exists platform_billing_ledger_entries
  drop constraint if exists platform_billing_ledger_entries_entry_type_check;

alter table if exists platform_billing_ledger_entries
  add constraint platform_billing_ledger_entries_entry_type_check
  check (entry_type in ('usage_debit','stripe_credit','admin_credit','refund_credit','adjustment','chargeback_debit','chargeback_reversal'));

create table if not exists platform_billing_fx_rate_snapshots (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references platform_iam_organizations(id) on delete cascade,
  source_currency text not null check (source_currency ~ '^[A-Z]{3}$'),
  target_currency text not null check (target_currency ~ '^[A-Z]{3}$'),
  rate_numerator numeric(38, 18) not null check (rate_numerator > 0),
  rate_denominator numeric(38, 18) not null default 1 check (rate_denominator > 0),
  provider text not null default 'manual',
  provider_rate_id text null,
  captured_at timestamptz not null,
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  check (source_currency <> target_currency)
);

create index if not exists ix_platform_billing_fx_rate_snapshots_org_pair_time
  on platform_billing_fx_rate_snapshots(org_id, source_currency, target_currency, captured_at desc);

create or replace function prevent_platform_billing_ledger_entries_mutation()
returns trigger
language plpgsql
as $$
begin
  if current_setting('gpuaas.allow_ledger_mutation', true) = 'test_cleanup' then
    return old;
  end if;
  raise exception 'platform_billing_ledger_entries are immutable; corrections must be additive'
    using errcode = '55000';
end;
$$;

drop trigger if exists platform_billing_ledger_entries_immutable on platform_billing_ledger_entries;
create trigger platform_billing_ledger_entries_immutable
before update or delete on platform_billing_ledger_entries
for each row execute function prevent_platform_billing_ledger_entries_mutation();

create table if not exists platform_payments_refund_requests (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references platform_iam_organizations(id),
  -- beneficiary of the refund (refund target)
  user_id uuid not null references platform_iam_users(id),
  -- actor attribution (who initiated/admin-approved the refund request)
  requested_by_user_id uuid null references platform_iam_users(id),
  amount_minor bigint not null check (amount_minor > 0),
  currency text not null default 'USD' check (currency ~ '^[A-Z]{3}$'),
  reason text not null,
  payment_reference text null,
  mode text not null default 'auto_policy' check (mode in ('auto_policy','force_internal_credit')),
  outcome text null check (outcome in ('provider_refund','internal_credit')),
  status text not null default 'accepted' check (status in ('accepted','completed','failed')),
  policy_applied jsonb not null default '{}'::jsonb,
  provider_refund_id text null,
  internal_ledger_entry_id uuid null references platform_billing_ledger_entries(id),
  correlation_id text not null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  check (status <> 'completed' or outcome is not null),
  check (
    outcome is null
    or (outcome = 'internal_credit' and internal_ledger_entry_id is not null)
    or (outcome = 'provider_refund' and internal_ledger_entry_id is null)
  )
);

create table if not exists platform_payments_stripe_events (
  id text primary key,
  event_type text not null,
  payload jsonb not null,
  processed_at timestamptz not null default now()
);

-- Tracks each Stripe checkout session lifecycle from initiation through credit posting.
-- Provides reconciliation visibility and idempotent session deduplication.
create table if not exists platform_payments_sessions (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references platform_iam_organizations(id),
  initiated_by_user_id uuid not null references platform_iam_users(id),
  stripe_checkout_session_id text not null unique,
  stripe_payment_intent_id text null,               -- populated after checkout.session.completed
  idempotency_key text null,                        -- from X-Idempotency-Key header at creation
  requested_amount_minor bigint not null check (requested_amount_minor > 0),
  credited_amount_minor bigint null check (credited_amount_minor > 0),   -- from webhook payload; should equal requested
  currency text not null default 'USD' check (currency ~ '^[A-Z]{3}$'),
  status text not null default 'initiated'
    check (status in ('initiated','checkout_completed','credited','failed_reconcile','expired')),
  initiated_at timestamptz not null default now(),
  checkout_completed_at timestamptz null,
  credited_at timestamptz null,
  failed_at timestamptz null,
  failure_reason text null,
  stripe_event_id text null references platform_payments_stripe_events(id),   -- webhook that triggered the credit
  ledger_entry_id uuid null references platform_billing_ledger_entries(id),  -- resulting credit ledger entry
  correlation_id text not null,
  created_at timestamptz not null default now()
);

alter table if exists platform_payments_sessions
  add column if not exists checkout_url text null;

create index if not exists ix_platform_payments_sessions_user_time
  on platform_payments_sessions(initiated_by_user_id, created_at desc);
create index if not exists ix_platform_payments_sessions_status_time
  on platform_payments_sessions(status, created_at desc);
create unique index if not exists ix_platform_payments_sessions_idempotency
  on platform_payments_sessions(org_id, initiated_by_user_id, idempotency_key)
  where idempotency_key is not null;

create table if not exists platform_payments_disputes (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references platform_iam_organizations(id),
  user_id uuid null references platform_iam_users(id),
  payment_session_id uuid null references platform_payments_sessions(id),
  provider text not null default 'stripe' check (provider in ('stripe')),
  provider_dispute_id text not null,
  provider_charge_id text null,
  provider_payment_intent_id text null,
  status text not null default 'opened'
    check (status in ('opened','needs_response','evidence_due','evidence_submitted','won','lost','closed')),
  provider_status text null,
  reason text null,
  amount_minor bigint not null check (amount_minor > 0),
  currency text not null default 'USD' check (currency ~ '^[A-Z]{3}$'),
  evidence_due_at timestamptz null,
  evidence_submitted_at timestamptz null,
  closed_at timestamptz null,
  provider_event_id text null references platform_payments_stripe_events(id),
  chargeback_ledger_entry_id uuid null references platform_billing_ledger_entries(id),
  reversal_ledger_entry_id uuid null references platform_billing_ledger_entries(id),
  correlation_id text null,
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (provider, provider_dispute_id)
);

create index if not exists ix_platform_payments_disputes_org_status
  on platform_payments_disputes(org_id, status, created_at desc);

create index if not exists ix_platform_payments_disputes_payment_session
  on platform_payments_disputes(payment_session_id)
  where payment_session_id is not null;

create table if not exists platform_billing_provider_customers (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references platform_iam_organizations(id) on delete cascade,
  provider text not null check (provider in ('stripe')),
  provider_customer_id text not null,
  status text not null default 'active' check (status in ('active','disabled','deleted')),
  is_default boolean not null default true,
  source text not null default 'api' check (source in ('api','backfill','webhook','admin')),
  created_by_user_id uuid null references platform_iam_users(id) on delete set null,
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  deleted_at timestamptz null,
  unique (provider, provider_customer_id)
);

create unique index if not exists ux_platform_billing_provider_customers_org_provider_default_active
  on platform_billing_provider_customers(org_id, provider)
  where is_default and deleted_at is null and status = 'active';

create index if not exists ix_platform_billing_provider_customers_org_provider
  on platform_billing_provider_customers(org_id, provider)
  where deleted_at is null;

create table if not exists platform_billing_pricing_plans (
  id uuid primary key default gen_random_uuid(),
  org_id uuid null references platform_iam_organizations(id),
  name text not null,
  active boolean not null default true,
  rules jsonb not null,
  created_at timestamptz not null default now()
);

create table if not exists platform_billing_accounts (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references platform_iam_organizations(id) on delete cascade,
  account_type text not null default 'tenant'
    check (account_type in ('tenant','reseller','end_customer')),
  display_name text not null,
  parent_billing_account_id uuid null references platform_billing_accounts(id),
  reseller_org_id uuid null references platform_iam_organizations(id),
  invoice_recipient_org_id uuid null references platform_iam_organizations(id),
  default_currency text not null default 'USD' check (default_currency ~ '^[A-Z]{3}$'),
  wholesale_pricing_plan_id uuid null references platform_billing_pricing_plans(id),
  customer_pricing_plan_id uuid null references platform_billing_pricing_plans(id),
  tax_responsibility text not null default 'platform'
    check (tax_responsibility in ('platform','reseller','end_customer')),
  credit_risk_owner text not null default 'platform'
    check (credit_risk_owner in ('platform','reseller')),
  status text not null default 'active' check (status in ('active','disabled','deleted')),
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  deleted_at timestamptz null,
  check (
    (account_type = 'end_customer' and (parent_billing_account_id is not null or reseller_org_id is not null))
    or account_type in ('tenant','reseller')
  )
);

create unique index if not exists ux_platform_billing_accounts_org_active
  on platform_billing_accounts(org_id)
  where deleted_at is null and status = 'active';

create index if not exists ix_platform_billing_accounts_reseller_active
  on platform_billing_accounts(reseller_org_id, org_id)
  where deleted_at is null and status = 'active';

insert into platform_billing_provider_customers (org_id, provider, provider_customer_id, source)
select id, 'stripe', trim(stripe_customer_id), 'backfill'
from platform_iam_organizations
where nullif(trim(stripe_customer_id), '') is not null
on conflict (provider, provider_customer_id) do nothing;

insert into platform_billing_provider_customers (org_id, provider, provider_customer_id, source, metadata)
select resolved.org_id,
       'stripe',
       resolved.provider_customer_id,
       'backfill',
       jsonb_build_object('legacy_source', 'users.stripe_customer_id', 'legacy_user_id', resolved.user_id::text)
from (
  select u.id as user_id,
         coalesce(
           u.org_id,
           (
             select tm.org_id
             from platform_access_tenant_memberships tm
             where tm.user_id = u.id and tm.deleted_at is null
             order by tm.created_at asc
             limit 1
           )
         ) as org_id,
         trim(u.stripe_customer_id) as provider_customer_id
  from platform_iam_users u
  where nullif(trim(u.stripe_customer_id), '') is not null
) resolved
where resolved.org_id is not null
  and not exists (
    select 1
    from platform_billing_provider_customers bpc
    where bpc.org_id = resolved.org_id
      and bpc.provider = 'stripe'
      and bpc.is_default
      and bpc.deleted_at is null
      and bpc.status = 'active'
  )
on conflict (provider, provider_customer_id) do nothing;

create table if not exists platform_billing_reconciliation_runs (
  id uuid primary key default gen_random_uuid(),
  requested_by_user_id uuid null references platform_iam_users(id),
  status text not null default 'completed' check (status in ('running','completed','failed')),
  checked_at timestamptz not null default now(),
  completed_at timestamptz null,
  missing_open_usage_count bigint not null default 0 check (missing_open_usage_count >= 0),
  orphan_open_usage_count bigint not null default 0 check (orphan_open_usage_count >= 0),
  unbilled_closed_usage_count bigint not null default 0 check (unbilled_closed_usage_count >= 0),
  sample_allocation_id uuid null references gpuaas_allocations(id),
  sample_usage_id uuid null references platform_billing_usage_records(id),
  failure_reason text null,
  correlation_id text not null,
  created_at timestamptz not null default now()
);

create index if not exists ix_platform_billing_reconciliation_runs_time
  on platform_billing_reconciliation_runs(created_at desc);

create table if not exists platform_audit_logs (
  id uuid primary key default gen_random_uuid(),
  -- Nullable only for platform-scoped events with no tenant context (bootstrap/startup/infra relay diagnostics).
  org_id uuid null references platform_iam_organizations(id),
  actor_user_id uuid null references platform_iam_users(id),
  actor_service_account_id uuid null references platform_iam_service_accounts(id),
  actor_role text not null check (actor_role in ('user','admin','service_account','system')),
  action text not null,
  target_type text not null,
  target_id text null,
  result text not null check (result in ('success','failure')),
  correlation_id text not null,
  metadata jsonb null,
  occurred_at timestamptz not null default now()
);

create or replace function prevent_platform_audit_logs_mutation()
returns trigger
language plpgsql
as $$
begin
  if current_setting('gpuaas.allow_audit_log_mutation', true) in ('maintenance', 'test_cleanup') then
    return old;
  end if;
  raise exception 'platform_audit_logs are append-only; use audit maintenance procedure for approved mutation'
    using errcode = '55000';
end;
$$;

drop trigger if exists platform_audit_logs_append_only on platform_audit_logs;
create trigger platform_audit_logs_append_only
before update or delete on platform_audit_logs
for each row execute function prevent_platform_audit_logs_mutation();

create table if not exists platform_status_admin_ack_suppressions (
  id uuid primary key default gen_random_uuid(),
  scope text not null default 'item' check (scope in ('item','class','environment')),
  signal_key text not null,
  target_type text not null,
  target_id text not null default '',
  state text not null check (state in ('acknowledged','suppressed','cleared')),
  ack_fingerprint text not null,
  reason text null,
  expires_at timestamptz null,
  actor_user_id uuid null references platform_iam_users(id),
  actor_role text not null check (actor_role in ('user','admin','service_account','system')),
  correlation_id text not null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  cleared_at timestamptz null,
  check (state <> 'suppressed' or expires_at is not null)
);

create unique index if not exists ux_platform_status_admin_ack_suppressions_target
  on platform_status_admin_ack_suppressions(scope, signal_key, target_type, target_id);

create index if not exists ix_admin_ack_suppressions_state
  on platform_status_admin_ack_suppressions(state, expires_at);

create index if not exists ix_admin_ack_suppressions_actor_time
  on platform_status_admin_ack_suppressions(actor_user_id, updated_at desc);

do $$
begin
  if to_regclass('public.provisioning_attention_reviews') is not null
     and to_regclass('public.gpuaas_provisioning_attention_reviews') is null then
    alter table public.provisioning_attention_reviews rename to gpuaas_provisioning_attention_reviews;
  end if;
end $$;

create table if not exists gpuaas_provisioning_attention_reviews (
  id uuid primary key default gen_random_uuid(),
  attention_key text not null unique,
  target_type text not null check (target_type in ('node_onboarding','node_decommission','node_agent_lifecycle')),
  target_id text not null,
  action_family text not null,
  workflow_id text null,
  review_state text not null check (review_state in ('taken_care_of','ignored')),
  review_note text not null,
  reviewed_by_user_id uuid not null references platform_iam_users(id),
  actor_role text not null check (actor_role in ('user','admin','service_account','system')),
  correlation_id text not null,
  reviewed_at timestamptz not null default now(),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists ix_provisioning_attention_reviews_target
  on gpuaas_provisioning_attention_reviews(target_type, target_id, action_family, reviewed_at desc);

create index if not exists ix_provisioning_attention_reviews_actor_time
  on gpuaas_provisioning_attention_reviews(reviewed_by_user_id, reviewed_at desc);

create table if not exists platform_api_idempotency_keys (
  id uuid primary key default gen_random_uuid(),
  org_id uuid null references platform_iam_organizations(id),
  request_scope text not null,
  idempotency_key text not null,
  request_hash text not null,
  response_code integer null,
  response_body jsonb null,
  replayable boolean not null default false,
  resource_type text null,
  resource_id text null,
  created_at timestamptz not null default now(),
  expires_at timestamptz not null, -- default TTL target: 24h (policy key: idempotency.key_ttl_seconds)
  check (
    response_body is null
    or (response_code is not null and response_code < 500)
  ),
  check (
    replayable = false
    or (response_code is not null and response_code < 500 and response_body is not null)
  )
);

create unique index if not exists ux_platform_api_idempotency_keys_tenant_scope
  on platform_api_idempotency_keys(org_id, request_scope, idempotency_key)
  where org_id is not null;

create unique index if not exists ux_platform_api_idempotency_keys_platform_scope
  on platform_api_idempotency_keys(request_scope, idempotency_key)
  where org_id is null;

create table if not exists platform_outbox_events (
  id uuid primary key default gen_random_uuid(),
  aggregate_type text not null,
  aggregate_id text not null,
  event_type text not null,
  payload jsonb not null,
  correlation_id text not null,
  trace_parent text null,
  trace_state text null,
  status text not null default 'pending' check (status in ('pending','published','failed')),
  retry_count integer not null default 0,
  last_attempted_at timestamptz null,
  occurred_at timestamptz not null default now(),
  published_at timestamptz null,
  check (status <> 'published' or published_at is not null)
);

create table if not exists platform_policy_definitions (
  policy_key text primary key,
  description text not null,
  value_type text not null check (value_type in ('integer','string','boolean','json','duration')),
  default_value jsonb not null,
  bounds jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

create table if not exists platform_policy_values (
  id uuid primary key default gen_random_uuid(),
  policy_key text not null references platform_policy_definitions(policy_key),
  -- `user` scope is retained for forward compatibility but is out of MVP policy evaluation.
  -- `plan` scope is retained for Phase-2 pricing/subscription policy layering.
  scope_type text not null check (scope_type in ('global','plan','org','department','project','user')),
  scope_id uuid null,
  value jsonb not null,
  effective_at timestamptz not null default now(),
  created_by_user_id uuid null references platform_iam_users(id),
  reason text null,
  correlation_id text null,
  created_at timestamptz not null default now(),
  check (
    (scope_type = 'global' and scope_id is null)
    or (scope_type <> 'global' and scope_id is not null)
  )
);

alter table if exists platform_policy_values
  drop constraint if exists platform_policy_values_scope_type_check;

alter table if exists platform_policy_values
  add constraint platform_policy_values_scope_type_check
  check (scope_type in ('global','plan','org','department','project','user')) not valid;

create unique index if not exists ux_platform_policy_values_global
  on platform_policy_values(policy_key, effective_at)
  where scope_type = 'global';

create unique index if not exists ux_platform_policy_values_scoped
  on platform_policy_values(policy_key, scope_type, scope_id, effective_at)
  where scope_id is not null;

create table if not exists platform_policy_change_events (
  id uuid primary key default gen_random_uuid(),
  policy_value_id uuid not null references platform_policy_values(id),
  before_value jsonb null,
  after_value jsonb not null,
  changed_by_user_id uuid null references platform_iam_users(id),
  correlation_id text not null,
  changed_at timestamptz not null default now()
);

create table if not exists platform_billing_pricing_plan_versions (
  id uuid primary key default gen_random_uuid(),
  plan_id uuid not null references platform_billing_pricing_plans(id),
  version text not null,
  pricing_mode text not null default 'on_demand' check (pricing_mode in ('on_demand','spot','reserved')),
  rules jsonb not null,
  active boolean not null default true,
  effective_at timestamptz not null default now(),
  created_at timestamptz not null default now(),
  unique (plan_id, version)
);

create table if not exists platform_billing_subscriptions (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references platform_iam_organizations(id),
  plan_id uuid not null references platform_billing_pricing_plans(id),
  status text not null check (status in ('pending','active','cancelled','expired')),
  starts_at timestamptz not null,
  ends_at timestamptz null,
  created_at timestamptz not null default now(),
  check (ends_at is null or ends_at > starts_at)
);

create table if not exists platform_billing_commit_contracts (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references platform_iam_organizations(id),
  commitment_minor bigint not null check (commitment_minor >= 0),
  currency text not null default 'USD' check (currency ~ '^[A-Z]{3}$'),
  starts_at timestamptz not null,
  ends_at timestamptz null,
  terms jsonb not null,
  created_at timestamptz not null default now(),
  check (ends_at is null or ends_at > starts_at)
);

create table if not exists platform_evidence_bundles (
  bundle_id uuid primary key default gen_random_uuid(),
  source_commit text not null,
  release_branch text null,
  environment_profile text not null,
  product_scope text not null,
  change_summary text not null,
  gate_state text not null default 'missing' check (gate_state in ('pass','fail','partial','blocked','missing','not_applicable')),
  artifact_digests jsonb not null default '[]'::jsonb,
  migration_status text null,
  capacity_posture text null,
  residual_risk text null,
  rollback_plan text null,
  correlation_ids jsonb not null default '[]'::jsonb,
  approvals jsonb not null default '[]'::jsonb,
  metadata jsonb not null default '{}'::jsonb,
  created_by_user_id uuid null references platform_iam_users(id),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  check (artifact_digests = '[]'::jsonb or jsonb_typeof(artifact_digests) = 'array'),
  check (correlation_ids = '[]'::jsonb or jsonb_typeof(correlation_ids) = 'array'),
  check (approvals = '[]'::jsonb or jsonb_typeof(approvals) = 'array')
);

create table if not exists platform_evidence_items (
  evidence_item_id uuid primary key default gen_random_uuid(),
  bundle_id uuid not null references platform_evidence_bundles(bundle_id) on delete cascade,
  evidence_type text not null,
  producer text not null,
  source_uri text null,
  artifact_path text null,
  result text not null check (result in ('pass','fail','partial','blocked','missing','not_applicable')),
  started_at timestamptz null,
  completed_at timestamptz null,
  correlation_id text null,
  owner text not null,
  retention_class text not null,
  proves_invariants jsonb not null default '[]'::jsonb,
  details jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  check (completed_at is null or started_at is null or completed_at >= started_at),
  check (proves_invariants = '[]'::jsonb or jsonb_typeof(proves_invariants) = 'array')
);

create table if not exists platform_release_gate_results (
  id uuid primary key default gen_random_uuid(),
  bundle_id uuid not null references platform_evidence_bundles(bundle_id) on delete cascade,
  gate_id text not null,
  label text not null,
  result text not null check (result in ('pass','fail','partial','blocked','missing','not_applicable')),
  owner text not null,
  evidence_item_ids jsonb not null default '[]'::jsonb,
  missing_reason text null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (bundle_id, gate_id),
  check (evidence_item_ids = '[]'::jsonb or jsonb_typeof(evidence_item_ids) = 'array')
);

create table if not exists platform_product_invariant_results (
  id uuid primary key default gen_random_uuid(),
  bundle_id uuid not null references platform_evidence_bundles(bundle_id) on delete cascade,
  invariant_id text not null,
  product_area text not null,
  result text not null check (result in ('pass','fail','partial','blocked','missing','not_applicable')),
  evidence_item_ids jsonb not null default '[]'::jsonb,
  missing_reason text null,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (bundle_id, invariant_id),
  check (evidence_item_ids = '[]'::jsonb or jsonb_typeof(evidence_item_ids) = 'array')
);

create table if not exists platform_status_component_status (
  component_id text not null,
  component_type text not null,
  environment_profile text not null,
  status text not null check (status in ('healthy','degraded','unhealthy','unknown')),
  freshness_seconds bigint not null check (freshness_seconds >= 0),
  evidence_href text null,
  checked_at timestamptz null,
  details jsonb not null default '{}'::jsonb,
  updated_at timestamptz not null default now(),
  primary key (component_id, environment_profile)
);

create table if not exists platform_guard_report_summaries (
  guard_id text not null,
  source_commit text not null,
  mode text not null check (mode in ('report_only','warning','blocking','blocking_new','blocking_all')),
  generated_at timestamptz not null,
  artifact_path text null,
  findings jsonb not null default '[]'::jsonb,
  allowed_debt jsonb not null default '[]'::jsonb,
  graduation_criteria text not null,
  created_at timestamptz not null default now(),
  primary key (guard_id, source_commit),
  check (findings = '[]'::jsonb or jsonb_typeof(findings) = 'array'),
  check (allowed_debt = '[]'::jsonb or jsonb_typeof(allowed_debt) = 'array')
);

create table if not exists platform_billing_budget_policies (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references platform_iam_organizations(id),
  project_id uuid null references platform_iam_projects(id),
  scope_type text not null check (scope_type in ('tenant','project')),
  display_name text not null,
  period text not null default 'monthly' check (period in ('monthly','quarterly','annual','custom')),
  amount_minor bigint not null check (amount_minor > 0),
  currency text not null default 'USD' check (currency ~ '^[A-Z]{3}$'),
  threshold_percent integer not null default 80 check (threshold_percent between 1 and 100),
  enforcement_mode text not null default 'notify_only' check (enforcement_mode in ('notify_only','block_launch','suspend')),
  active boolean not null default true,
  starts_at timestamptz not null default now(),
  ends_at timestamptz null,
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  check (ends_at is null or ends_at > starts_at),
  check ((scope_type = 'tenant' and project_id is null) or (scope_type = 'project' and project_id is not null))
);

create table if not exists platform_billing_budget_events (
  id uuid primary key default gen_random_uuid(),
  budget_policy_id uuid not null references platform_billing_budget_policies(id),
  org_id uuid not null references platform_iam_organizations(id),
  project_id uuid null references platform_iam_projects(id),
  threshold_key text not null,
  period_start timestamptz not null,
  period_end timestamptz not null,
  spent_minor bigint not null check (spent_minor >= 0),
  budget_minor bigint not null check (budget_minor > 0),
  currency text not null default 'USD' check (currency ~ '^[A-Z]{3}$'),
  first_seen_at timestamptz not null default now(),
  last_seen_at timestamptz not null default now(),
  seen_count integer not null default 1 check (seen_count > 0),
  status text not null default 'open' check (status in ('open','acknowledged','resolved')),
  correlation_id text null,
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (budget_policy_id, threshold_key, period_start, period_end),
  check (period_end > period_start)
);

create table if not exists platform_billing_tax_profiles (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references platform_iam_organizations(id) on delete cascade,
  legal_name text not null,
  tax_jurisdiction text null,
  tax_treatment text not null default 'not_configured'
    check (tax_treatment in ('not_configured','standard','tax_exempt','reverse_charge')),
  seller_tax_registration text null,
  customer_tax_registration text null,
  exemption_reason text null,
  reverse_charge_reason text null,
  evidence jsonb not null default '{}'::jsonb,
  active boolean not null default true,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create unique index if not exists ux_platform_billing_tax_profiles_org_active
  on platform_billing_tax_profiles(org_id)
  where active;

create table if not exists platform_billing_invoice_headers (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references platform_iam_organizations(id),
  period_start timestamptz not null,
  period_end timestamptz not null,
  status text not null check (status in ('draft','issued','due','paid','partially_paid','void','write_off')),
  currency text not null default 'USD' check (currency ~ '^[A-Z]{3}$'),
  total_minor bigint not null default 0,
  tax_total_minor bigint not null default 0,
  tax_jurisdiction text null,
  tax_treatment text not null default 'not_configured'
    check (tax_treatment in ('not_configured','standard','tax_exempt','reverse_charge')),
  seller_tax_registration text null,
  customer_tax_registration text null,
  tax_snapshot jsonb not null default '{}'::jsonb,
  correlation_id text null,
  issued_at timestamptz null,
  due_at timestamptz null,
  finalized_at timestamptz null,
  voided_at timestamptz null,
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  check (period_end > period_start)
);

alter table if exists platform_billing_invoice_headers
  add column if not exists tax_total_minor bigint not null default 0,
  add column if not exists tax_jurisdiction text null,
  add column if not exists tax_treatment text not null default 'not_configured',
  add column if not exists seller_tax_registration text null,
  add column if not exists customer_tax_registration text null,
  add column if not exists tax_snapshot jsonb not null default '{}'::jsonb,
  add column if not exists correlation_id text null,
  add column if not exists issued_at timestamptz null,
  add column if not exists due_at timestamptz null,
  add column if not exists finalized_at timestamptz null,
  add column if not exists voided_at timestamptz null,
  add column if not exists metadata jsonb not null default '{}'::jsonb;

create table if not exists platform_billing_invoice_lines (
  id uuid primary key default gen_random_uuid(),
  invoice_id uuid not null references platform_billing_invoice_headers(id),
  line_type text not null check (line_type in ('rated_usage','credit','adjustment','tax','fee','payment')),
  description text not null,
  amount_minor bigint not null,
  currency text not null default 'USD' check (currency ~ '^[A-Z]{3}$'),
  rated_usage_line_id uuid null references platform_billing_rated_usage_lines(id),
  ledger_entry_id uuid null references platform_billing_ledger_entries(id),
  payment_session_id uuid null references platform_payments_sessions(id),
  fx_snapshot jsonb null,
  tax_category text null,
  tax_rate_bps integer null check (tax_rate_bps is null or tax_rate_bps between 0 and 10000),
  tax_jurisdiction text null,
  tax_subject_line_id uuid null references platform_billing_invoice_lines(id),
  tax_exempt_reason text null,
  reverse_charge_reason text null,
  tax_evidence jsonb not null default '{}'::jsonb,
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now()
);

alter table if exists platform_billing_invoice_lines
  add column if not exists rated_usage_line_id uuid null references platform_billing_rated_usage_lines(id),
  add column if not exists ledger_entry_id uuid null references platform_billing_ledger_entries(id),
  add column if not exists payment_session_id uuid null references platform_payments_sessions(id),
  add column if not exists fx_snapshot jsonb null,
  add column if not exists tax_category text null,
  add column if not exists tax_rate_bps integer null,
  add column if not exists tax_jurisdiction text null,
  add column if not exists tax_subject_line_id uuid null references platform_billing_invoice_lines(id),
  add column if not exists tax_exempt_reason text null,
  add column if not exists reverse_charge_reason text null,
  add column if not exists tax_evidence jsonb not null default '{}'::jsonb;

create table if not exists platform_billing_financial_restrictions (
  id uuid primary key default gen_random_uuid(),
  scope_type text not null check (scope_type in ('tenant','project','user')),
  scope_id uuid not null,
  state text not null check (state in ('healthy','at_risk','restricted','suspended','collections_hold')),
  reason text not null,
  effects jsonb not null default '{}'::jsonb,
  starts_at timestamptz not null default now(),
  ends_at timestamptz null,
  created_by_user_id uuid null references platform_iam_users(id),
  correlation_id text null,
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (scope_type, scope_id),
  check (ends_at is null or ends_at > starts_at)
);

create table if not exists platform_storage_objects (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references platform_iam_organizations(id),
  project_id uuid not null,
  created_by_user_id uuid not null references platform_iam_users(id),
  path text not null,
  object_type text not null check (object_type in ('file','dir')),
  size_bytes bigint null,
  updated_at timestamptz not null default now(),
  unique (project_id, path),
  constraint fk_storage_objects_project_org
    foreign key (project_id, org_id) references platform_iam_projects(id, org_id)
);

create unique index if not exists ux_platform_storage_objects_project_path
  on platform_storage_objects(project_id, path);

create table if not exists platform_storage_buckets (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references platform_iam_organizations(id),
  project_id uuid not null,
  bucket_id text not null check (bucket_id ~ '^[A-Za-z0-9][A-Za-z0-9:_-]{0,127}$'),
  purpose text not null check (purpose in ('workspace','dataset','checkpoint','artifact','generic')),
  quota_bytes bigint null check (quota_bytes is null or quota_bytes > 0),
  encryption_mode text not null check (encryption_mode in ('project_default','kms_managed')),
  retention_mode text not null check (retention_mode in ('keep_forever','keep_30d','keep_90d','scheduled_delete')),
  access_audience text not null check (access_audience in ('project_members','service_accounts','workload_bound')),
  service_account_id text null,
  mount_intent text null,
  access_protocol text not null default 'wekafs' check (access_protocol in ('wekafs','posix','s3','nfs','smb','csi')),
  provider_backend text not null default 'local_dev' check (provider_backend in ('local_dev','weka','vast','ddn','nvme_pool','s3_compatible','unknown')),
  provider_filesystem text null,
  provider_instance_id text null,
  status text not null default 'active' check (status in ('creating','active','failed','deleting','deleted')),
  created_by_user_id uuid not null references platform_iam_users(id),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  unique (project_id, bucket_id),
  constraint fk_storage_buckets_project_org
    foreign key (project_id, org_id) references platform_iam_projects(id, org_id)
);

alter table if exists platform_storage_buckets
  add column if not exists provider_filesystem text null;

alter table if exists platform_storage_buckets
  add column if not exists provider_instance_id text null;

alter table if exists platform_storage_buckets
  add column if not exists access_protocol text not null default 'wekafs';

create table if not exists platform_storage_grants (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references platform_iam_organizations(id),
  owner_project_id uuid not null,
  bucket_id text not null check (bucket_id ~ '^[A-Za-z0-9][A-Za-z0-9:_-]{0,127}$'),
  subject_kind text not null check (subject_kind in ('project','user','service_account','workload','tenant_group')),
  subject_id text not null,
  subject_project_id uuid null,
  prefixes jsonb not null default '[]'::jsonb check (jsonb_typeof(prefixes) = 'array'),
  permissions jsonb not null check (jsonb_typeof(permissions) = 'array' and jsonb_array_length(permissions) > 0),
  provider_backend text not null default 'local_dev' check (provider_backend in ('local_dev','weka','vast','ddn','nvme_pool','s3_compatible','unknown')),
  provider_policy_ref text null,
  expires_at timestamptz null,
  reason text null,
  created_by_user_id uuid not null references platform_iam_users(id),
  created_at timestamptz not null default now(),
  revoked_at timestamptz null,
  revoked_by_user_id uuid null references platform_iam_users(id),
  revoke_reason text null,
  updated_at timestamptz not null default now(),
  constraint fk_storage_grants_project_org
    foreign key (owner_project_id, org_id) references platform_iam_projects(id, org_id)
);

create table if not exists platform_storage_credential_sessions (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references platform_iam_organizations(id),
  project_id uuid not null,
  bucket_id text not null check (bucket_id ~ '^[A-Za-z0-9][A-Za-z0-9:_-]{0,127}$'),
  grant_id uuid null references platform_storage_grants(id) on delete set null,
  subject_kind text not null check (subject_kind in ('project','user','service_account','workload','tenant_group')),
  subject_id text not null,
  subject_project_id uuid null,
  client_kind text not null check (client_kind in ('s3_cli','sdk','workload_mount')),
  credential_type text not null check (credential_type in ('s3_session','provider_session','vault_wrapped_secret')),
  provider_backend text not null check (provider_backend in ('local_dev','weka','vast','ddn','nvme_pool','s3_compatible','unknown')),
  provider_session_ref text null,
  provider_policy_ref text null,
  prefixes jsonb not null default '[]'::jsonb check (jsonb_typeof(prefixes) = 'array'),
  permissions jsonb not null check (jsonb_typeof(permissions) = 'array' and jsonb_array_length(permissions) > 0),
  status text not null default 'active' check (status in ('active','expired','revocation_pending','revoked','failed')),
  issued_by_user_id uuid null references platform_iam_users(id),
  issued_by_service_account_id uuid null references platform_iam_service_accounts(id),
  issued_at timestamptz not null default now(),
  expires_at timestamptz not null,
  revoked_at timestamptz null,
  revoked_by_user_id uuid null references platform_iam_users(id),
  revoke_reason text null,
  source_workflow_id text null,
  idempotency_key text null,
  audit_log_id uuid null references platform_audit_logs(id) on delete set null,
  metadata jsonb not null default '{}'::jsonb check (jsonb_typeof(metadata) = 'object'),
  updated_at timestamptz not null default now(),
  check (issued_by_user_id is not null or issued_by_service_account_id is not null),
  constraint fk_storage_credential_sessions_project_org
    foreign key (project_id, org_id) references platform_iam_projects(id, org_id),
  constraint fk_storage_credential_sessions_bucket
    foreign key (project_id, bucket_id) references platform_storage_buckets(project_id, bucket_id)
);

create table if not exists platform_storage_attachments (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references platform_iam_organizations(id),
  project_id uuid not null,
  bucket_id text not null check (bucket_id ~ '^[A-Za-z0-9][A-Za-z0-9:_-]{0,127}$'),
  allocation_id uuid null references gpuaas_allocations(id) on delete set null,
  workload_instance_id uuid null references appplatform_instances(id) on delete set null,
  node_id uuid null references gpuaas_nodes(id) on delete set null,
  mount_path text not null check (mount_path ~ '^/[A-Za-z0-9._/@:+-]+$' and mount_path not like '%..%'),
  access_mode text not null check (access_mode in ('read_only','read_write')),
  write_policy text not null default 'single_writer' check (write_policy in ('single_writer','multi_writer')),
  provider_backend text not null default 'local_dev' check (provider_backend in ('local_dev','weka','vast','ddn','nvme_pool','s3_compatible','unknown')),
  provider_filesystem text null,
  provider_mount_ref text null,
  state text not null default 'requested' check (state in ('requested','prechecking','grant_applying','grant_applied','mounting','mounted','failed','detaching','detached','detach_failed')),
  workflow_id text null,
  failure_code text null,
  failure_message text null,
  requested_by_user_id uuid null references platform_iam_users(id),
  requested_at timestamptz not null default now(),
  mounted_at timestamptz null,
  detached_at timestamptz null,
  updated_at timestamptz not null default now(),
  check (allocation_id is not null or workload_instance_id is not null),
  constraint fk_storage_attachments_project_org
    foreign key (project_id, org_id) references platform_iam_projects(id, org_id),
  constraint fk_storage_attachments_bucket
    foreign key (project_id, bucket_id) references platform_storage_buckets(project_id, bucket_id)
);

-- Useful indexes
alter table platform_audit_logs add column if not exists actor_service_account_id uuid null references platform_iam_service_accounts(id);

create index if not exists ix_platform_billing_usage_records_user_time on platform_billing_usage_records(requested_by_user_id, start_time desc);
create index if not exists ix_platform_billing_usage_records_org_time on platform_billing_usage_records(org_id, start_time desc);
create index if not exists ix_platform_billing_usage_records_department_time on platform_billing_usage_records(department_id, start_time desc)
  where department_id is not null;
create index if not exists ix_platform_billing_usage_records_app_instance_time on platform_billing_usage_records(app_instance_id, start_time desc);
create index if not exists ix_platform_billing_usage_records_source_time on platform_billing_usage_records(usage_source, start_time desc);
create index if not exists ix_platform_billing_rated_usage_lines_usage_time on platform_billing_rated_usage_lines(usage_record_id, bill_from desc);
create index if not exists ix_platform_billing_rated_usage_lines_user_time on platform_billing_rated_usage_lines(requested_by_user_id, bill_from desc);
create index if not exists ix_platform_billing_rated_usage_lines_org_time on platform_billing_rated_usage_lines(org_id, bill_from desc);
create index if not exists ix_platform_billing_pricing_plan_versions_plan_effective on platform_billing_pricing_plan_versions(plan_id, effective_at desc);
create index if not exists ix_platform_evidence_bundles_scope_updated on platform_evidence_bundles(environment_profile, product_scope, updated_at desc);
create index if not exists ix_platform_evidence_bundles_gate_state on platform_evidence_bundles(gate_state, updated_at desc);
create index if not exists ix_platform_evidence_items_bundle_type_result on platform_evidence_items(bundle_id, evidence_type, result, completed_at desc);
create index if not exists ix_platform_evidence_items_correlation on platform_evidence_items(correlation_id) where correlation_id is not null;
create index if not exists ix_platform_release_gate_results_bundle_result on platform_release_gate_results(bundle_id, result);
create index if not exists ix_platform_product_invariant_results_bundle_result on platform_product_invariant_results(bundle_id, result);
create index if not exists ix_platform_component_status_env_status on platform_status_component_status(environment_profile, status, updated_at desc);
create index if not exists ix_platform_guard_report_summaries_guard_time on platform_guard_report_summaries(guard_id, generated_at desc);
create index if not exists ix_platform_billing_budget_policies_scope_active on platform_billing_budget_policies(scope_type, org_id, project_id, active);
create index if not exists ix_platform_billing_budget_events_policy_seen on platform_billing_budget_events(budget_policy_id, last_seen_at desc);
create index if not exists ix_platform_billing_invoice_headers_org_period on platform_billing_invoice_headers(org_id, period_start desc, period_end desc);
create index if not exists ix_platform_billing_invoice_lines_invoice on platform_billing_invoice_lines(invoice_id, line_type);
create index if not exists ix_platform_billing_invoice_lines_rated_usage on platform_billing_invoice_lines(rated_usage_line_id) where rated_usage_line_id is not null;
create index if not exists ix_platform_billing_financial_restrictions_state on platform_billing_financial_restrictions(state, updated_at desc);
create index if not exists ix_platform_billing_financial_restrictions_scope_state on platform_billing_financial_restrictions(scope_type, scope_id, state);
create index if not exists ix_platform_billing_ledger_entries_user_time on platform_billing_ledger_entries(requested_by_user_id, created_at desc);
create index if not exists ix_platform_billing_ledger_entries_org_time on platform_billing_ledger_entries(org_id, created_at desc);
create index if not exists ix_allocations_user_status on gpuaas_allocations(requested_by_user_id, status);
create index if not exists ix_allocations_org_status on gpuaas_allocations(org_id, status);
create index if not exists ix_allocations_node_id on gpuaas_allocations(node_id);
create index if not exists ix_nodes_org_region_status on gpuaas_nodes(org_id, region_code, status);
create index if not exists ix_platform_payments_refund_requests_user_time on platform_payments_refund_requests(user_id, created_at desc);
create index if not exists ix_audit_logs_org_time on platform_audit_logs(org_id, occurred_at desc);
create index if not exists ix_audit_logs_actor_time on platform_audit_logs(actor_user_id, occurred_at desc);
create index if not exists ix_audit_logs_actor_service_account_time on platform_audit_logs(actor_service_account_id, occurred_at desc);
create index if not exists ix_audit_logs_target on platform_audit_logs(target_type, target_id, occurred_at desc);
create index if not exists ix_audit_logs_correlation on platform_audit_logs(correlation_id);
create index if not exists ix_outbox_status_time on platform_outbox_events(status, occurred_at);
create index if not exists ix_outbox_aggregate on platform_outbox_events(aggregate_type, aggregate_id);
create index if not exists ix_idempotency_expires_at on platform_api_idempotency_keys(expires_at);
create index if not exists ix_policy_values_lookup on platform_policy_values(policy_key, scope_type, scope_id, effective_at desc);
create index if not exists ix_storage_buckets_project_status on platform_storage_buckets(project_id, status, updated_at desc);
create index if not exists ix_storage_grants_bucket_active on platform_storage_grants(owner_project_id, bucket_id, revoked_at, created_at desc);
create index if not exists ix_storage_grants_subject_active on platform_storage_grants(subject_kind, subject_id, subject_project_id, revoked_at);
create index if not exists ix_storage_credential_sessions_bucket_time on platform_storage_credential_sessions(project_id, bucket_id, issued_at desc);
create index if not exists ix_storage_credential_sessions_subject_active on platform_storage_credential_sessions(subject_kind, subject_id, subject_project_id, status, expires_at desc);
create index if not exists ix_storage_credential_sessions_grant on platform_storage_credential_sessions(grant_id, status, expires_at desc) where grant_id is not null;
create index if not exists ix_storage_credential_sessions_expiry on platform_storage_credential_sessions(status, expires_at);
create index if not exists ix_storage_attachments_bucket_state on platform_storage_attachments(project_id, bucket_id, state, updated_at desc);
create index if not exists ix_storage_attachments_allocation on platform_storage_attachments(allocation_id, state) where allocation_id is not null;
create index if not exists ix_storage_attachments_workload on platform_storage_attachments(workload_instance_id, state) where workload_instance_id is not null;
create index if not exists ix_storage_attachments_node_state on platform_storage_attachments(node_id, state) where node_id is not null;
create index if not exists ix_storage_objects_project_path on platform_storage_objects(project_id, path);

do $$
begin
  if exists (
    select 1
    from pg_constraint
    where conname = 'audit_logs_actor_role_check'
      and conrelid = 'public.platform_audit_logs'::regclass
  ) then
    alter table public.platform_audit_logs drop constraint audit_logs_actor_role_check;
  end if;
  alter table public.platform_audit_logs
    add constraint audit_logs_actor_role_check
    check (actor_role in ('user','admin','service_account','system'));
end $$;
