-- Database schema for athena-auth

-- Users table
CREATE TABLE IF NOT EXISTS public.users (
    id TEXT PRIMARY KEY,
    email TEXT UNIQUE,
    name TEXT,
    email_verified BOOLEAN NOT NULL DEFAULT FALSE,
    image TEXT,
    username TEXT UNIQUE,
    display_username TEXT,
    two_factor_enabled BOOLEAN NOT NULL DEFAULT FALSE,
    role TEXT,
    banned BOOLEAN NOT NULL DEFAULT FALSE,
    ban_reason TEXT,
    ban_expires TIMESTAMPTZ,
    last_sign_in_at TIMESTAMPTZ,
    metadata JSONB NOT NULL DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL 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
);

-- FIXME: existing drift is user_permission_scopes table, we migrate to this something like grants, or rights
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
);

-- Accounts table
-- FIXME: existing drift is account table, we migrate to this table.
CREATE TABLE IF NOT EXISTS public.accounts (
    id TEXT PRIMARY KEY,
    account_id TEXT NOT NULL,
    provider_id TEXT NOT NULL,
    user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    access_token TEXT,
    refresh_token TEXT,
    id_token TEXT,
    access_token_expires_at TIMESTAMPTZ,
    refresh_token_expires_at TIMESTAMPTZ,
    scope TEXT,
    password TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (provider_id, account_id)
);

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
);

-- Verification tokens table
CREATE TABLE IF NOT EXISTS public.verifications (
    id TEXT PRIMARY KEY,
    identifier TEXT NOT NULL,
    value TEXT NOT NULL,
    expires_at TIMESTAMPTZ NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- API Keys table (can also be named 'public' as mentioned in requirements)
CREATE TABLE IF NOT EXISTS public.api_keys (
    id TEXT PRIMARY KEY,
    user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    key_hash TEXT NOT NULL UNIQUE,
    name TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    last_used_at TIMESTAMPTZ,
    expires_at TIMESTAMPTZ,
    is_active BOOLEAN DEFAULT true
);
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
);


-- Emails table

CREATE TABLE IF NOT EXISTS public.emails (
  id text 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,
  from_address text,
  from_name text,
  text_body text,
  provider text,
  metadata json
);

CREATE TABLE IF NOT EXISTS public.email_send_failures (
  id text NOT NULL,
  user_id text,
  recipient_email text NOT NULL,
  flow text NOT NULL,
  provider text,
  error_message text NOT NULL,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamp with time zone NOT NULL DEFAULT now()
);


-- Audit log

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(),
  ipv4_address text
);

-- event log
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
);

-- multi tenant organization
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.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
);


-- organization
-- FIXME: existing drift is organization table, we migrate to this table.
CREATE TABLE 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,
  updated_at timestamp with time zone
);

-- passkey
-- FIXME: existing drift is passkey table, we migrate to this table.
CREATE TABLE IF NOT EXISTS public.passkey (
  id text NOT NULL,
  name text,
  public_key text NOT NULL,
  user_id text NOT NULL,
  credential_id text NOT NULL,
  counter integer NOT NULL,
  device_type text NOT NULL,
  backed_up boolean NOT NULL,
  transports text,
  created_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
  aaguid text
);
CREATE TABLE IF NOT EXISTS public.passkeys (
  id text NOT NULL,
  name text NOT NULL,
  public_key text NOT NULL,
  user_id text NOT NULL,
  credential_id text NOT NULL,
  counter bigint NOT NULL DEFAULT 0,
  device_type text NOT NULL DEFAULT 'singleDevice'::text,
  backed_up boolean NOT NULL DEFAULT false,
  transports text,
  created_at timestamp with time zone NOT NULL DEFAULT now()
);

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
);

-- sessions
CREATE TABLE IF NOT EXISTS public.sessions (
  id text NOT NULL,
  expires_at timestamp with time zone NOT NULL,
  token text NOT NULL,
  ip_address text,
  user_agent text,
  user_id text NOT NULL,
  impersonated_by text,
  active_organization_id text,
  active boolean NOT NULL DEFAULT true,
  created_at timestamp with time zone NOT NULL DEFAULT now(),
  updated_at timestamp with time zone NOT NULL DEFAULT now(),
  ipv4_address text
);

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 indexes for better query performance
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
CREATE INDEX IF NOT EXISTS idx_users_last_sign_in_at ON users(last_sign_in_at);
CREATE INDEX IF NOT EXISTS idx_accounts_user_id ON accounts(user_id);
CREATE INDEX IF NOT EXISTS idx_accounts_provider_account ON accounts(provider_id, account_id);
CREATE INDEX IF NOT EXISTS idx_verifications_identifier ON verifications(identifier);
CREATE INDEX IF NOT EXISTS idx_verifications_expires_at ON verifications(expires_at);
CREATE INDEX IF NOT EXISTS idx_api_keys_user_id ON api_keys(user_id);
CREATE INDEX IF NOT EXISTS idx_api_keys_key_hash ON api_keys(key_hash);
CREATE INDEX IF NOT EXISTS idx_api_keys_is_active ON api_keys(is_active);

-- Optional session intelligence cache/projection tables
CREATE TABLE IF NOT EXISTS public.ip_profiles (
  id text PRIMARY KEY,
  ip_address text NOT NULL UNIQUE,
  provider text NOT NULL,
  country_code text,
  country_name text,
  region_name text,
  city text,
  latitude double precision,
  longitude double precision,
  timezone text,
  isp text,
  organization text,
  asn text,
  is_vpn boolean,
  is_proxy boolean,
  is_tor boolean,
  is_hosting boolean,
  raw_payload jsonb NOT NULL DEFAULT '{}'::jsonb,
  lookup_expires_at timestamp with time zone,
  first_seen_at timestamp with time zone NOT NULL DEFAULT now(),
  last_seen_at timestamp with time zone NOT NULL DEFAULT now(),
  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.session_ip_profiles (
  id text PRIMARY KEY,
  session_id text NOT NULL UNIQUE REFERENCES public.sessions(id) ON DELETE CASCADE,
  session_token text NOT NULL,
  user_id text NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
  ip_address text NOT NULL,
  ip_profile_id text REFERENCES public.ip_profiles(id) ON DELETE SET NULL,
  country_code text,
  country_name text,
  is_vpn boolean,
  is_proxy boolean,
  is_tor boolean,
  is_hosting boolean,
  country_changed boolean NOT NULL DEFAULT false,
  previous_country_code text,
  provider text,
  created_at timestamp with time zone NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_ip_profiles_lookup_expires_at ON ip_profiles(lookup_expires_at);
CREATE INDEX IF NOT EXISTS idx_ip_profiles_country_code ON ip_profiles(country_code);
CREATE INDEX IF NOT EXISTS idx_ip_profiles_last_seen_at ON ip_profiles(last_seen_at DESC);
CREATE UNIQUE INDEX IF NOT EXISTS idx_session_ip_profiles_session_token ON session_ip_profiles(session_token);
CREATE INDEX IF NOT EXISTS idx_session_ip_profiles_user_created ON session_ip_profiles(user_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_session_ip_profiles_country_code ON session_ip_profiles(country_code);
CREATE INDEX IF NOT EXISTS idx_session_ip_profiles_ip_address ON session_ip_profiles(ip_address);
CREATE INDEX IF NOT EXISTS idx_session_ip_profiles_country_changed ON session_ip_profiles(country_changed);
