CREATE TABLE IF NOT EXISTS hwlab_users (
  id TEXT PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  username TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL DEFAULT '',
  password_hash TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'active',
  role TEXT NOT NULL DEFAULT 'user',
  email_verified BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (status IN ('active', 'disabled', 'pending')),
  CHECK (role IN ('user', 'admin'))
);

CREATE TABLE IF NOT EXISTS hwlab_user_sessions (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES hwlab_users(id) ON DELETE CASCADE,
  token_hash TEXT NOT NULL UNIQUE,
  expires_at TIMESTAMPTZ NOT NULL,
  revoked_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS hwlab_user_sessions_user_id_idx ON hwlab_user_sessions(user_id);
CREATE INDEX IF NOT EXISTS hwlab_user_sessions_token_hash_idx ON hwlab_user_sessions(token_hash);

CREATE TABLE IF NOT EXISTS hwlab_api_keys (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES hwlab_users(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  key_prefix TEXT NOT NULL,
  key_hash TEXT NOT NULL UNIQUE,
  scopes_json JSONB NOT NULL DEFAULT '["api"]'::jsonb,
  status TEXT NOT NULL DEFAULT 'active',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  last_used_at TIMESTAMPTZ,
  revoked_at TIMESTAMPTZ,
  CHECK (status IN ('active', 'revoked'))
);

CREATE INDEX IF NOT EXISTS hwlab_api_keys_user_id_idx ON hwlab_api_keys(user_id);
CREATE INDEX IF NOT EXISTS hwlab_api_keys_key_hash_idx ON hwlab_api_keys(key_hash);

CREATE TABLE IF NOT EXISTS hwlab_credit_accounts (
  user_id TEXT PRIMARY KEY REFERENCES hwlab_users(id) ON DELETE CASCADE,
  balance_credits BIGINT NOT NULL DEFAULT 0,
  reserved_credits BIGINT NOT NULL DEFAULT 0,
  plan_id TEXT NOT NULL DEFAULT 'default',
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (balance_credits >= 0),
  CHECK (reserved_credits >= 0)
);

CREATE TABLE IF NOT EXISTS hwlab_credit_ledger (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES hwlab_users(id) ON DELETE CASCADE,
  delta_credits BIGINT NOT NULL,
  balance_after BIGINT NOT NULL,
  kind TEXT NOT NULL,
  reason TEXT NOT NULL DEFAULT '',
  idempotency_key TEXT UNIQUE,
  metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS hwlab_credit_ledger_user_id_idx ON hwlab_credit_ledger(user_id);

CREATE TABLE IF NOT EXISTS hwlab_billing_reservations (
  id TEXT PRIMARY KEY,
  user_id TEXT NOT NULL REFERENCES hwlab_users(id) ON DELETE CASCADE,
  service_id TEXT NOT NULL,
  estimated_credits BIGINT NOT NULL,
  estimated_tokens BIGINT NOT NULL DEFAULT 0,
  status TEXT NOT NULL DEFAULT 'reserved',
  idempotency_key TEXT UNIQUE,
  metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
  expires_at TIMESTAMPTZ NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (estimated_credits >= 0),
  CHECK (estimated_tokens >= 0),
  CHECK (status IN ('reserved', 'completed', 'cancelled', 'expired'))
);

CREATE INDEX IF NOT EXISTS hwlab_billing_reservations_user_id_idx ON hwlab_billing_reservations(user_id);

CREATE TABLE IF NOT EXISTS hwlab_usage_records (
  id TEXT PRIMARY KEY,
  reservation_id TEXT REFERENCES hwlab_billing_reservations(id) ON DELETE SET NULL,
  user_id TEXT NOT NULL REFERENCES hwlab_users(id) ON DELETE CASCADE,
  service_id TEXT NOT NULL,
  unit TEXT NOT NULL DEFAULT 'token',
  quantity BIGINT NOT NULL DEFAULT 0,
  credits BIGINT NOT NULL DEFAULT 0,
  idempotency_key TEXT UNIQUE,
  metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (quantity >= 0),
  CHECK (credits >= 0)
);

CREATE INDEX IF NOT EXISTS hwlab_usage_records_user_id_idx ON hwlab_usage_records(user_id);
CREATE INDEX IF NOT EXISTS hwlab_usage_records_service_id_idx ON hwlab_usage_records(service_id);