assessment-model/src/app/db/schema/users.ts

import {
  bigint,
  bigserial,
  text,
  timestamp,
  pgTable,
  primaryKey,
  integer,
  boolean,
  json,
  pgEnum,
  varchar,
} from "drizzle-orm/pg-core";
import { InferModel } from "drizzle-orm";

// -------------------------
// USERS
// -------------------------
export const user = pgTable("user", {
  id: bigserial("id", { mode: "bigint" }).primaryKey(),
  firstName: text("firstName"),
  email: text("email").notNull().unique(),
  emailVerified: timestamp("emailVerified", { mode: "date" }),
  oauthId: text("oauth_id"),
  oauthProvider: text("oauth_provider").$type<
    "google" | "credentials" | "azure-ad-b2c"
  >(),
  image: text("image"),
  onboarded: boolean("onboarded").default(false).notNull(),
  lastLogin: timestamp("last_login", { mode: "date" }),
  createdAt: timestamp("created_at", { precision: 6, withTimezone: true })
    .defaultNow()
    .notNull(),
  updatedAt: timestamp("updated_at", { precision: 6, withTimezone: true })
    .defaultNow()
    .notNull(),
});

// -------------------------
// ACCOUNTS (OAuth providers)
// -------------------------
export const accounts = pgTable(
  "account",
  {
    userId: bigint("userId", { mode: "bigint" })
      .notNull()
      .references(() => user.id, { onDelete: "cascade" }),
    type: text("type").$type<"oauth" | "email" | "credentials">().notNull(),
    provider: text("provider").notNull(),
    providerAccountId: text("providerAccountId").notNull(),
    refresh_token: text("refresh_token"),
    access_token: text("access_token"),
    expires_at: integer("expires_at"),
    token_type: text("token_type"),
    scope: text("scope"),
    id_token: text("id_token"),
    session_state: text("session_state"),
  },
  (account) => [
    primaryKey({ columns: [account.provider, account.providerAccountId] }),
  ]
);

export const sessions = pgTable("session", {
  sessionToken: text("sessionToken").primaryKey(),
  userId: bigint("userId", { mode: "bigint" })
    .notNull()
    .references(() => user.id, { onDelete: "cascade" }),
  expires: timestamp("expires", { mode: "date" }).notNull(),
});

export const verificationTokens = pgTable(
  "verificationToken",
  {
    identifier: text("identifier").notNull(),
    token: text("token").notNull(),
    expires: timestamp("expires", { mode: "date" }).notNull(),
    codeHash: text("code_hash"),
    attempts: integer("attempts").notNull().default(0),
  },
  (vt) => [primaryKey({ columns: [vt.identifier, vt.token] })]
);

export const authRateLimits = pgTable(
  "authRateLimits",
  {
    scope: text("scope").notNull(),
    key: text("key").notNull(),
    count: integer("count").notNull().default(0),
    windowStart: timestamp("window_start", { mode: "date" }).notNull(),
  },
  (rl) => [primaryKey({ columns: [rl.scope, rl.key] })]
);

export const UserType: [string, ...string[]] = [
  "private_landlord",
  "private_tenant",
  "social_landlord",
  "social_tenant",
  "homeowner",
  "other",
];

export const PropertyCount: [string, ...string[]] = [
  // Private landlord options
  "1",
  "2–5",
  "6–20",
  "21+",
  // Social landlord options
  "1–50",
  "51–100",
  "101–300",
  "301–1000",
  "1000+",
];

export const ReferralSource: [string, ...string[]] = [
  "search",
  "social_media",
  "NRLA",
  "partner",
  "word_of_mouth",
  "other",
];

export const Goal: [string, ...string[]] = [
  "access_funding",
  "net_zero",
  "improve_condition",
  "save_money",
  "other",
];

export const userTypeEnum = pgEnum("user_profiles_user_type", UserType);
export const propertyCountEnum = pgEnum(
  "user_profiles_property_count",
  PropertyCount
);
export const referralSourceEnum = pgEnum(
  "user_profiles_referral_source",
  ReferralSource
);

// ----------------------------
// MAIN TABLE
// ----------------------------
export const userProfiles = pgTable("user_profiles", {
  id: bigserial("id", { mode: "bigint" }).primaryKey(),

  userId: bigint("user_id", { mode: "bigint" })
    .notNull()
    .references(() => user.id, { onDelete: "cascade" }),

  // Profile
  userType: userTypeEnum("user_type").notNull(),
  propertyCount: propertyCountEnum("property_count"), // Nullable for homeowners / tenants

  // Goals (multi-select)
  goals: json("goals").$type<(typeof Goal)[number][]>(),

  // Referral
  referralSource: referralSourceEnum("referral_source"),
  nrlaMembershipId: varchar("nrla_membership_id", { length: 255 }),

  // Compliance
  acceptedPrivacy: boolean("accepted_privacy").notNull().default(false),
  acceptedPrivacyAt: timestamp("accepted_privacy_at", {
    withTimezone: true,
    precision: 6,
  }),

  // Marketing
  marketingOptIn: boolean("marketing_opt_in").default(false),
  marketingOptInAt: timestamp("marketing_opt_in_at", {
    withTimezone: true,
    precision: 6,
  }),

  // Basic user identity
  firstName: text("first_name"),
  lastName: text("last_name"),

  // Metadata
  createdAt: timestamp("created_at", {
    precision: 6,
    withTimezone: true,
  })
    .defaultNow()
    .notNull(),
  updatedAt: timestamp("updated_at", {
    precision: 6,
    withTimezone: true,
  })
    .defaultNow()
    .notNull(),
});

// -------------------------
// Types
// -------------------------
export type User = InferModel<typeof user, "select">;
export type NewUser = InferModel<typeof user, "insert">;

export type Account = InferModel<typeof accounts, "select">;
export type Session = InferModel<typeof sessions, "select">;
export type VerificationToken = InferModel<typeof verificationTokens, "select">;

export type UserProfile = InferModel<typeof userProfiles, "select">;
export type NewUserProfile = InferModel<typeof userProfiles, "insert">;