CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Keep auth bootstrap aligned with the canonical Athena Auth provision script.
\ir ../services/athena-auth/sql/provision.sql

-- Monolith compatibility tables still referenced by Athena runtime, migrations,
-- and provisioning expectations.

CREATE TABLE IF NOT EXISTS athena.account (
  id text NOT NULL,
  account_id text NOT NULL,
  provider_id text NOT NULL,
  user_id text NOT NULL,
  access_token text,
  refresh_token text,
  id_token text,
  access_token_expires_at timestamp with time zone,
  refresh_token_expires_at timestamp with time zone,
  scope text,
  password text,
  created_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp with time zone NOT NULL
);

ALTER TABLE athena.sessions
  ADD COLUMN IF NOT EXISTS ipv4_address text;

CREATE TABLE IF NOT EXISTS athena.apikey (
  id text NOT NULL,
  name text,
  start text,
  prefix text,
  key text NOT NULL,
  user_id text NOT NULL,
  refill_interval integer,
  refill_amount integer,
  last_refill_at timestamp with time zone,
  enabled boolean,
  rate_limit_enabled boolean,
  rate_limit_time_window integer,
  rate_limit_max integer,
  request_count bigint DEFAULT 0,
  remaining integer,
  last_request timestamp with time zone,
  expires_at timestamp with time zone,
  created_at timestamp with time zone NOT NULL,
  updated_at timestamp with time zone NOT NULL,
  permissions text,
  metadata text
);

ALTER TABLE athena.apikey
  ADD COLUMN IF NOT EXISTS request_count bigint DEFAULT 0;

ALTER TABLE athena.apikey
  ALTER COLUMN request_count TYPE bigint USING request_count::bigint;

CREATE INDEX IF NOT EXISTS idx_apikey_user_id ON athena.apikey (user_id);
CREATE INDEX IF NOT EXISTS idx_apikey_key ON athena.apikey (key);

CREATE TABLE IF NOT EXISTS athena.audit_log (
  id bigint NOT NULL,
  created_at timestamp with time zone NOT NULL DEFAULT (now() AT TIME ZONE 'utc'::text),
  user_id text,
  username text,
  action text,
  time numeric,
  route text,
  request text,
  status text,
  message text,
  author_user_id text,
  email text,
  domain text DEFAULT 'suitsbooks.nl'::text,
  organization_id text,
  new_status text,
  old_value text,
  new_value text,
  "table" text,
  resource_id text,
  resource json,
  diff_resource json,
  user_object json,
  ipv4 text,
  audit_log_id uuid NOT NULL DEFAULT gen_random_uuid(),
  ipv4_address text
);

CREATE TABLE IF NOT EXISTS athena.user_permission_scopes (
  id bigint NOT NULL,
  created_at timestamp with time zone DEFAULT now(),
  user_id text,
  scope text,
  global boolean DEFAULT false,
  organization_id text,
  user_permission_scope_id uuid DEFAULT gen_random_uuid(),
  enabled boolean DEFAULT false
);

CREATE INDEX IF NOT EXISTS idx_user_permission_scopes_user_enabled
  ON athena.user_permission_scopes (user_id, enabled);
