Arsenal_IQ/backend/database/init.sql

CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE IF NOT EXISTS users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash VARCHAR(255),
  name VARCHAR(255) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);

CREATE TABLE IF NOT EXISTS profiles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  name VARCHAR(255) NOT NULL,
  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (user_id, name)
);

CREATE INDEX IF NOT EXISTS idx_profiles_user_id ON profiles(user_id);

CREATE TABLE IF NOT EXISTS auth_sessions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  active_profile_id UUID REFERENCES profiles(id) ON DELETE SET NULL,
  token_hash VARCHAR(255) NOT NULL UNIQUE,
  expires_at TIMESTAMPTZ NOT NULL,
  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_auth_sessions_user_id ON auth_sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_auth_sessions_expires_at ON auth_sessions(expires_at);

CREATE TABLE IF NOT EXISTS auth_provider_configs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  provider_key VARCHAR(100) NOT NULL UNIQUE,
  display_name VARCHAR(255) NOT NULL,
  protocol VARCHAR(50) NOT NULL DEFAULT 'oidc',
  client_id TEXT,
  client_secret TEXT,
  authorization_endpoint TEXT,
  token_endpoint TEXT,
  userinfo_endpoint TEXT,
  issuer TEXT,
  scopes TEXT NOT NULL DEFAULT 'openid profile email',
  enabled BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS auth_identities (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  provider_key VARCHAR(100) NOT NULL REFERENCES auth_provider_configs(provider_key) ON DELETE CASCADE,
  provider_subject TEXT NOT NULL,
  email VARCHAR(255),
  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (provider_key, provider_subject)
);

CREATE TABLE IF NOT EXISTS oauth_states (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  provider_key VARCHAR(100) NOT NULL REFERENCES auth_provider_configs(provider_key) ON DELETE CASCADE,
  state_code VARCHAR(255) NOT NULL UNIQUE,
  redirect_uri TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS calibers (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  profile_id UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
  name VARCHAR(40) NOT NULL,
  is_default BOOLEAN NOT NULL DEFAULT FALSE,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (profile_id, name)
);

CREATE INDEX IF NOT EXISTS idx_calibers_profile_id ON calibers(profile_id);

CREATE TABLE IF NOT EXISTS firearms (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  profile_id UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
  manufacturer VARCHAR(120) NOT NULL,
  model VARCHAR(120) NOT NULL,
  category VARCHAR(80) NOT NULL,
  caliber VARCHAR(40) NOT NULL,
  serial_number VARCHAR(120) NOT NULL,
  purchase_price NUMERIC(10, 2) NOT NULL DEFAULT 0,
  acquired_on DATE,
  image_url TEXT,
  notes TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_firearms_profile_id ON firearms(profile_id);

CREATE TABLE IF NOT EXISTS ammo_inventory (
  profile_id UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
  caliber_id UUID NOT NULL REFERENCES calibers(id) ON DELETE CASCADE,
  rounds_on_hand INT NOT NULL DEFAULT 0 CHECK (rounds_on_hand >= 0),
  cost_per_round NUMERIC(10, 2) NOT NULL DEFAULT 0,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (profile_id, caliber_id)
);

INSERT INTO auth_provider_configs (
  provider_key,
  display_name,
  protocol,
  authorization_endpoint,
  token_endpoint,
  userinfo_endpoint,
  issuer,
  scopes,
  enabled
)
VALUES
  (
    'google',
    'Google',
    'oidc',
    'https://accounts.google.com/o/oauth2/v2/auth',
    'https://oauth2.googleapis.com/token',
    'https://openidconnect.googleapis.com/v1/userinfo',
    'https://accounts.google.com',
    'openid profile email',
    FALSE
  ),
  (
    'entra',
    'Microsoft Entra ID',
    'oidc',
    '',
    '',
    'https://graph.microsoft.com/oidc/userinfo',
    '',
    'openid profile email',
    FALSE
  ),
  (
    'oidc',
    'Custom OIDC',
    'oidc',
    '',
    '',
    '',
    '',
    'openid profile email',
    FALSE
  )
ON CONFLICT (provider_key) DO NOTHING;