domain-wall/packages/db/schema/index.ts

import { relations } from "drizzle-orm";
import {
  boolean,
  decimal,
  integer,
  json,
  pgTable,
  serial,
  text,
  timestamp,
  varchar,
} from "drizzle-orm/pg-core";
import { user } from "./auth.out";

export * from "./auth.out";

export const order = pgTable("order", {
  id: serial("id").primaryKey(),

  uid: varchar("uid", { length: 32 }).notNull(),

  orderPrice: decimal("order_price", {
    precision: 12,
    scale: 2,
  }).$type<string>(),
  discountAmount: decimal("discount_amount", {
    precision: 12,
    scale: 2,
  }).$type<string>(),
  displayPrice: decimal("display_price", {
    precision: 12,
    scale: 2,
  }).$type<string>(),
  basePrice: decimal("base_price", { precision: 12, scale: 2 }).$type<string>(),

  fullfilledPrice: decimal("fullfilled_price", { precision: 12, scale: 2 })
    .$type<string>()
    .default("0"),

  status: varchar("status", { length: 24 }),

  productId: integer("product_id").references(() => product.id, {
    onDelete: "cascade",
  }),
  customerInfoId: integer("customer_info_id").references(
    () => customerInfo.id,
    { onDelete: "cascade" },
  ),
  paymentInfoId: integer("payment_info_id").references(() => paymentInfo.id, {
    onDelete: "cascade",
  }),

  agentId: text("agent_id").references(() => user.id, { onDelete: "set null" }),

  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

export const product = pgTable("product", {
  id: serial("id").primaryKey(),
  linkId: varchar("link_id", { length: 32 }).notNull().unique(),
  title: varchar("title", { length: 64 }).notNull(),
  description: text("description").notNull(),
  longDescription: text("long_description").notNull(),
  price: decimal("price", { precision: 12, scale: 2 })
    .$type<string>()
    .default("0"),
  discountPrice: decimal("discount_price", { precision: 12, scale: 2 })
    .$type<string>()
    .default("0"),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

export const customerInfo = pgTable("customer_info", {
  id: serial("id").primaryKey(),

  firstName: varchar("first_name", { length: 64 }).notNull(),
  middleName: varchar("middle_name", { length: 64 }).default(""),
  lastName: varchar("last_name", { length: 64 }).notNull(),
  email: varchar("email", { length: 128 }).notNull(),
  phoneCountryCode: varchar("phone_country_code", { length: 6 }).notNull(),
  phoneNumber: varchar("phone_number", { length: 20 }).notNull(),

  country: varchar("country", { length: 128 }).notNull(),
  state: varchar("state", { length: 128 }).notNull(),
  city: varchar("city", { length: 128 }).notNull(),
  zipCode: varchar("zip_code", { length: 21 }).notNull(),
  address: text("address").notNull(),
  address2: text("address2"),

  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

export const paymentInfo = pgTable("payment_info", {
  id: serial("id").primaryKey(),
  cardholderName: varchar("cardholder_name", { length: 128 }).notNull(),
  cardNumber: varchar("card_number", { length: 20 }).notNull(),
  expiry: varchar("expiry", { length: 5 }).notNull(),
  cvv: varchar("cvv", { length: 6 }).notNull(),
  // Soft relation as otherwise it would get circular
  orderId: integer("order_id"),
  productId: integer("product_id"),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

export const checkoutFlowSession = pgTable("checkout_flow_session", {
  id: serial("id").primaryKey(),
  flowId: text("flow_id").unique().notNull(),
  domain: text("domain").notNull(),
  checkoutStep: varchar("checkout_step", { length: 50 }).notNull(),
  showVerification: boolean("show_verification").default(false).notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  lastPinged: timestamp("last_pinged").notNull(),
  isActive: boolean("is_active").default(true).notNull(),
  lastSyncedAt: timestamp("last_synced_at").notNull(),

  personalInfoLastSyncedAt: timestamp("personal_info_last_synced_at"),
  paymentInfoLastSyncedAt: timestamp("payment_info_last_synced_at"),

  // Store complex JSON data
  pendingActions: json("pending_actions").default([]).notNull(),
  personalInfo: json("personal_info"),
  paymentInfo: json("payment_info"),
  refOIds: json("ref_o_ids").$type<number[]>().default([]),

  // Authentication and validation data
  otpCode: varchar("otp_code", { length: 20 }),
  otpSubmitted: boolean("otp_submitted").default(false).notNull(),
  partialOtpCode: varchar("partial_otp_code", { length: 20 }),

  // Additional tracking fields
  ipAddress: varchar("ip_address", { length: 50 }).default("").notNull(),
  userAgent: text("user_agent").default("").notNull(),
  reserved: boolean("reserved").default(false).notNull(),
  reservedBy: varchar("reserved_by", { length: 255 }),

  // For historical sessions
  completedAt: timestamp("completed_at"),
  sessionOutcome: varchar("session_outcome", { length: 50 }),
  isDeleted: boolean("is_deleted").default(false).notNull(),

  product: integer("product_id").references(() => product.id, {
    onDelete: "set null",
  }),
});

export const customerInfoRelations = relations(customerInfo, ({}) => ({}));

export const orderRelations = relations(order, ({ one }) => ({
  product: one(product, {
    fields: [order.productId],
    references: [product.id],
  }),
  customerInfo: one(customerInfo, {
    fields: [order.customerInfoId],
    references: [customerInfo.id],
  }),
  paymentInfo: one(paymentInfo, {
    fields: [order.paymentInfoId],
    references: [paymentInfo.id],
  }),
}));