CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE SCHEMA IF NOT EXISTS auth;
CREATE SCHEMA IF NOT EXISTS public;
CREATE SCHEMA IF NOT EXISTS athena;

CREATE TABLE IF NOT EXISTS public.user (
  id text NOT NULL,
  name text,
  email text NOT NULL,
  email_verified boolean NOT NULL,
  image text,
  created_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
  role text,
  banned boolean,
  ban_reason text,
  ban_expires timestamp with time zone,
  username text,
  display_username text,
  two_factor_enabled boolean NOT NULL DEFAULT false,
  two_factor_secret text,
  two_factor_backup_codes text,
  expo_push_token text,
  balance numeric DEFAULT 0.00
);

CREATE TABLE IF NOT EXISTS public.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 TABLE IF NOT EXISTS public.verification (
  id text NOT NULL,
  identifier text NOT NULL,
  value text NOT NULL,
  expires_at timestamp with time zone NOT NULL,
  created_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS public.workspaces (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  user_id text NOT NULL,
  name text NOT NULL DEFAULT 'My Workspace'::text,
  slug text NOT NULL,
  logo_url text,
  created_at timestamp without time zone NOT NULL DEFAULT now(),
  updated_at timestamp without time zone DEFAULT now()
);

CREATE TABLE IF NOT EXISTS public.two_factor (
  id text NOT NULL,
  secret text NOT NULL,
  backup_codes text NOT NULL,
  user_id text NOT NULL
);

CREATE TABLE IF NOT EXISTS public.organization (
  id text NOT NULL,
  name text NOT NULL,
  slug text NOT NULL,
  logo text,
  created_at timestamp with time zone NOT NULL,
  metadata text,
  vat_id text,
  company_number text,
  street text,
  postal_code text,
  city text,
  country_code text,
  org_uuid text DEFAULT (gen_random_uuid())::text,
  chatroom bigint,
  formation boolean NOT NULL DEFAULT false,
  stub boolean NOT NULL DEFAULT false
);

CREATE TABLE IF NOT EXISTS passkeys (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    public_key TEXT NOT NULL,
    user_id TEXT NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    credential_id TEXT NOT NULL UNIQUE,
    counter BIGINT NOT NULL DEFAULT 0,
    device_type TEXT NOT NULL DEFAULT 'singleDevice',
    backed_up BOOLEAN NOT NULL DEFAULT FALSE,
    transports TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_passkeys_user_id ON passkeys (user_id);

CREATE INDEX IF NOT EXISTS idx_passkeys_credential_id ON passkeys (credential_id);


CREATE TABLE IF NOT EXISTS public.sso_provider (
  id text NOT NULL,
  issuer text NOT NULL,
  oidc_config text,
  saml_config text,
  user_id text NOT NULL,
  provider_id text NOT NULL,
  organization_id text,
  domain text NOT NULL
);


CREATE TABLE IF NOT EXISTS public.session (
  id text NOT NULL,
  expires_at timestamp with time zone NOT NULL,
  token text NOT NULL,
  created_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp with time zone NOT NULL,
  ip_address text,
  user_agent text,
  user_id text NOT NULL,
  impersonated_by text,
  active_organization_id text,
  two_factor_verified boolean
);

CREATE TABLE IF NOT EXISTS public.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
);

CREATE TABLE IF NOT EXISTS public.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 integer,
  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
);

CREATE TABLE IF NOT EXISTS public.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()
);

CREATE TABLE IF NOT EXISTS public.blacklisted_emails (
  id integer NOT NULL DEFAULT nextval('blacklisted_emails_id_seq'::regclass),
  value character varying NOT NULL,
  type USER-DEFINED NOT NULL,
  reason text,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  updated_at timestamp with time zone NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS public.emails (
  id bigint NOT NULL,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  email_id uuid NOT NULL DEFAULT gen_random_uuid(),
  subject text,
  recipient_email text,
  sender_email text,
  body json,
  status text,
  time numeric,
  user_id text,
  email_code text,
  title text,
  html_body text,
  template_id text,
  replace_data json,
  opened boolean,
  delivered boolean,
  request_id text,
  request_message text,
  data_code text,
  data_message text,
  data_additional_info json,
  resource_type_ref text,
  resource_id_ref text,
  locale text DEFAULT 'en-US'::text,
  message text,
  organization_id text,
  recipient_name text,
  has_attachments boolean,
  attachments jsonb,
  language text DEFAULT 'en'::text,
  view_key text,
  starred boolean DEFAULT false
);

CREATE TABLE IF NOT EXISTS public.event_log (
  id bigint NOT NULL,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  directive text,
  time numeric,
  path text,
  content text,
  server text,
  api_version text,
  severity text,
  message text,
  json json,
  user_id text,
  organization_id text,
  status text,
  ref_id text
);


CREATE TABLE IF NOT EXISTS public.invitation (
  id text NOT NULL,
  organization_id text NOT NULL,
  email text NOT NULL,
  role text,
  status text NOT NULL,
  expires_at timestamp without time zone NOT NULL,
  created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
  inviter_id text NOT NULL,
  customer_id text
);

CREATE TABLE IF NOT EXISTS public.logs_auth (
  id uuid NOT NULL DEFAULT gen_random_uuid(),
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  level text NOT NULL DEFAULT 'info'::text,
  request_id text,
  method text NOT NULL,
  path text NOT NULL,
  status_code integer,
  duration_ms integer,
  actor_user_id text,
  organization_id text,
  error_code text,
  error_message text,
  metadata jsonb
);

CREATE TABLE IF NOT EXISTS public.member (
  id text NOT NULL,
  organization_id text NOT NULL,
  user_id text NOT NULL,
  role text NOT NULL,
  created_at timestamp with time zone NOT NULL,
  customer_id uuid
);