CREATE TABLE IF NOT EXISTS accounts (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  slug text NOT NULL UNIQUE,
  type text NOT NULL DEFAULT 'individual',
  status text NOT NULL DEFAULT 'active',
  created_at timestamptz NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS accounts_status_idx ON accounts (status);

CREATE TABLE IF NOT EXISTS plugins (
  key text PRIMARY KEY,
  name text NOT NULL,
  permission_catalog text[] NOT NULL DEFAULT '{}'::text[],
  created_at timestamptz NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS subscription_packages (
  plugin_key text NOT NULL REFERENCES plugins(key) ON DELETE CASCADE,
  tier_key text NOT NULL,
  currency text NOT NULL,
  price_minor bigint NOT NULL,
  credit_allocation bigint NOT NULL DEFAULT 0,
  seats_config integer NOT NULL,
  paypal_plan_id text NOT NULL UNIQUE,
  created_at timestamptz NOT NULL DEFAULT NOW(),
  CONSTRAINT subscription_packages_plugin_tier_pk PRIMARY KEY (plugin_key, tier_key)
);

CREATE INDEX IF NOT EXISTS subscription_packages_plugin_idx ON subscription_packages (plugin_key);

CREATE TABLE IF NOT EXISTS subscriptions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  account_id uuid NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  plugin_key text NOT NULL REFERENCES plugins(key) ON DELETE CASCADE,
  tier_key text NOT NULL,
  status text NOT NULL DEFAULT 'pending',
  current_period_start timestamptz,
  current_period_end timestamptz,
  paypal_subscription_id text UNIQUE,
  grace_period_end_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT NOW(),
  updated_at timestamptz NOT NULL DEFAULT NOW()
);

CREATE UNIQUE INDEX IF NOT EXISTS subscriptions_account_plugin_uq ON subscriptions (account_id, plugin_key);

CREATE INDEX IF NOT EXISTS subscriptions_status_idx ON subscriptions (status);

CREATE TABLE IF NOT EXISTS sites (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  account_id uuid NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  plugin_key text NOT NULL REFERENCES plugins(key) ON DELETE CASCADE,
  display_url text NOT NULL,
  status text NOT NULL DEFAULT 'active',
  created_at timestamptz NOT NULL DEFAULT NOW(),
  disconnected_at timestamptz
);

CREATE INDEX IF NOT EXISTS sites_account_plugin_idx ON sites (account_id, plugin_key);

CREATE TABLE IF NOT EXISTS auth_users (
  id text PRIMARY KEY,
  email text NOT NULL UNIQUE,
  password_hash text,
  session_version integer NOT NULL DEFAULT 0,
  roles text NOT NULL DEFAULT '["user"]',
  status text NOT NULL DEFAULT 'active',
  created_at timestamptz NOT NULL
);

CREATE TABLE IF NOT EXISTS user_sessions (
  id text PRIMARY KEY,
  user_id text NOT NULL REFERENCES auth_users(id),
  refresh_token_hash text NOT NULL,
  previous_refresh_token_hash text,
  last_refreshed_at timestamptz,
  expires_at timestamptz NOT NULL,
  status text NOT NULL DEFAULT 'active'
);

CREATE TABLE IF NOT EXISTS service_tokens (
  id text PRIMARY KEY,
  user_id text NOT NULL REFERENCES auth_users(id),
  token_hash text NOT NULL UNIQUE,
  label text NOT NULL,
  expires_at timestamptz,
  status text NOT NULL DEFAULT 'active',
  created_at timestamptz NOT NULL,
  last_used_at timestamptz
);

CREATE TABLE IF NOT EXISTS api_keys (
  id text PRIMARY KEY,
  prefix text NOT NULL UNIQUE,
  secret_hash text NOT NULL,
  owner text NOT NULL,
  scopes text NOT NULL,
  created_at timestamptz NOT NULL,
  last_used_at timestamptz,
  revoked_at timestamptz,
  expires_at timestamptz
);

CREATE TABLE IF NOT EXISTS oauth_clients (
  id text PRIMARY KEY,
  name text NOT NULL,
  redirect_uris text NOT NULL,
  scopes text NOT NULL,
  confidential boolean NOT NULL DEFAULT false,
  client_secret_hash text,
  created_at timestamptz NOT NULL
);

CREATE TABLE IF NOT EXISTS oauth_codes (
  id text PRIMARY KEY,
  client_id text NOT NULL REFERENCES oauth_clients(id),
  redirect_uri text NOT NULL,
  scope text NOT NULL,
  code_challenge text NOT NULL,
  code_challenge_method text NOT NULL,
  code_hash text NOT NULL UNIQUE,
  created_at timestamptz NOT NULL,
  expires_at timestamptz NOT NULL,
  used_at timestamptz
);

CREATE TABLE IF NOT EXISTS oauth_tokens (
  id text PRIMARY KEY,
  client_id text NOT NULL REFERENCES oauth_clients(id),
  code_id text NOT NULL REFERENCES oauth_codes(id),
  access_token_prefix text NOT NULL UNIQUE,
  access_token_hash text NOT NULL UNIQUE,
  scope text NOT NULL,
  token_type text NOT NULL,
  created_at timestamptz NOT NULL,
  expires_at timestamptz NOT NULL
);

CREATE TABLE IF NOT EXISTS credentials (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  account_id uuid NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  site_id uuid NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
  provider text NOT NULL,
  api_key_id text REFERENCES api_keys(id) ON DELETE SET NULL,
  oauth_token_id text REFERENCES oauth_tokens(id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL DEFAULT NOW(),
  revoked_at timestamptz,
  last_used_at timestamptz
);

CREATE UNIQUE INDEX IF NOT EXISTS credentials_account_site_provider_uq ON credentials (account_id, site_id, provider);

CREATE TABLE IF NOT EXISTS members (
  account_id uuid NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  user_id text NOT NULL REFERENCES auth_users(id) ON DELETE CASCADE,
  role_key text NOT NULL,
  status text NOT NULL DEFAULT 'active',
  created_at timestamptz NOT NULL DEFAULT NOW(),
  CONSTRAINT members_account_user_pk PRIMARY KEY (account_id, user_id)
);

CREATE INDEX IF NOT EXISTS members_account_role_idx ON members (account_id, role_key);

CREATE TABLE IF NOT EXISTS entitlements (
  account text NOT NULL,
  capability text NOT NULL,
  tier text NOT NULL,
  granted_at timestamptz NOT NULL DEFAULT NOW(),
  expires_at timestamptz,
  CONSTRAINT entitlements_account_capability_pk PRIMARY KEY (account, capability)
);

CREATE TABLE IF NOT EXISTS entitlement_tier_grants (
  capability text NOT NULL,
  tier text NOT NULL,
  quota_key text NOT NULL DEFAULT '',
  quota_value integer,
  CONSTRAINT entitlement_tier_grants_capability_tier_quota_pk PRIMARY KEY (capability, tier, quota_key)
);

CREATE TABLE IF NOT EXISTS tenancy_roles (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid NOT NULL,
  name text NOT NULL,
  is_system_role boolean NOT NULL DEFAULT false
);

CREATE TABLE IF NOT EXISTS tenancy_permissions (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  key text NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS tenancy_role_permissions (
  role_id uuid NOT NULL REFERENCES tenancy_roles(id) ON DELETE CASCADE,
  permission_id uuid NOT NULL REFERENCES tenancy_permissions(id) ON DELETE CASCADE,
  PRIMARY KEY (role_id, permission_id)
);

CREATE TABLE IF NOT EXISTS audit_log (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  actor_id text,
  actor_type text,
  actor_label text,
  action text NOT NULL,
  entity_type text NOT NULL,
  entity_id text NOT NULL,
  metadata jsonb,
  ip text,
  tenant_id text,
  created_at timestamptz(3) NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS audit_log_tenant_created_idx ON audit_log (tenant_id, created_at DESC);

CREATE INDEX IF NOT EXISTS audit_log_tenant_entity_idx ON audit_log (tenant_id, entity_type, entity_id);

CREATE INDEX IF NOT EXISTS audit_log_tenant_actor_idx ON audit_log (tenant_id, actor_id);

CREATE TABLE IF NOT EXISTS ledger_entries (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  delta bigint NOT NULL,
  currency text,
  reason text NOT NULL,
  ref jsonb,
  idempotency_key text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT NOW()
);

CREATE UNIQUE INDEX IF NOT EXISTS ledger_entries_idempotency_key_uq ON ledger_entries (idempotency_key);

CREATE TABLE IF NOT EXISTS wallet_balances (
  owner_id text PRIMARY KEY,
  balance bigint NOT NULL DEFAULT 0,
  updated_at timestamptz NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS invoice_document (
  idempotency_key text PRIMARY KEY,
  document_id text NOT NULL,
  document_number text NOT NULL,
  document_url text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS invoice_references (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  account_id uuid NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
  subscription_id uuid NOT NULL REFERENCES subscriptions(id) ON DELETE CASCADE,
  morning_document_id text NOT NULL,
  document_number text NOT NULL,
  document_url text NOT NULL,
  doc_type text NOT NULL,
  amount bigint NOT NULL,
  vat_amount bigint NOT NULL,
  currency text NOT NULL,
  idempotency_key text NOT NULL UNIQUE,
  created_at timestamptz NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS invoice_references_account_idx ON invoice_references (account_id);

CREATE TABLE IF NOT EXISTS webhook_events (
  event_id text PRIMARY KEY,
  claimed_at timestamptz,
  processed_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS charge_intents (
  charge_key text PRIMARY KEY,
  amount bigint NOT NULL,
  currency text NOT NULL,
  refund_reserved_minor bigint NOT NULL DEFAULT 0,
  status text NOT NULL DEFAULT 'pending',
  provider_ref text,
  claimed_at timestamptz NOT NULL DEFAULT NOW(),
  settled_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS charge_intents_status_idx ON charge_intents (status);

CREATE TABLE IF NOT EXISTS refund_reservations (
  refund_key text PRIMARY KEY,
  charge_key text NOT NULL REFERENCES charge_intents(charge_key) ON DELETE CASCADE,
  wallet_key text NOT NULL,
  amount_minor bigint NOT NULL,
  currency text NOT NULL,
  status text NOT NULL DEFAULT 'pending',
  provider_refund_key text,
  completed_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT NOW()
);
