export const patrons = pgTable(
  "patron",
  {
    birthDate: date("birth_date", { mode: "string" }),
    createdAt: timestamp("created_at", { mode: "date" }).notNull().defaultNow(),
    email: text("email"),
    firstName: text("first_name").notNull(),
    gender: text("gender", { enum: ["FEMALE", "MALE"] }),
    id: text("id").primaryKey().default(defaultUUID),
    lastName: text("last_name").notNull(),
    oneRosterSourcedId: text("one_roster_sourced_id"),
    organizationId: text("organization_id").notNull(),
    preferredName: text("preferred_name"),
  },
  (t) => ({
    id_by_organization: uniqueIndex().on(t.id, t.organizationId),
    one_roster_id_by_org: uniqueIndex().on(
      t.oneRosterSourcedId,
      t.organizationId,
    ),
  }),
);

export const patronBarcodes = pgTable(
  "patron_barcode",
  {
    barcode: text("barcode").notNull(),
    createdAt: timestamp("created_at", { mode: "date" }).notNull().defaultNow(),
    organizationId: text("organization_id").notNull(),
    patronId: text("patron_id").notNull(),
    pin: text("pin").notNull(),
  },
  (t) => ({
    barcode_by_org: uniqueIndex().on(t.barcode, t.organizationId),
    patron_by_org: uniqueIndex().on(t.patronId, t.organizationId),
    patron_org: foreignKey({
      columns: [t.patronId, t.organizationId],
      foreignColumns: [patrons.id, patrons.organizationId],
    }),
  }),
);

export const patronGroups = pgTable(
  "patron_group",
  {
    createdAt: timestamp("created_at", { mode: "date" }).notNull().defaultNow(),
    id: text("id").primaryKey().default(defaultUUID),
    name: text("name").notNull(),
    oneRosterSourcedId: text("one_roster_sourced_id"),
    organizationId: text("organization_id").notNull(),
  },
  (t) => ({
    id_by_organization: uniqueIndex().on(t.id, t.organizationId),
    name_by_organization: uniqueIndex().on(t.name, t.organizationId),
    one_roster_id_by_org: uniqueIndex().on(
      t.oneRosterSourcedId,
      t.organizationId,
    ),
  }),
);

export const patronGroupAssignments = pgTable(
  "patron_group_assignment",
  {
    createdAt: timestamp("created_at", { mode: "date" }).notNull().defaultNow(),
    groupId: text("group_id").notNull(),
    oneRosterSourcedId: text("one_roster_sourced_id"),
    organizationId: text("organization_id").notNull(),
    patronId: text("patron_id").notNull(),
  },
  (t) => ({
    one_roster_id_by_org: uniqueIndex().on(
      t.oneRosterSourcedId,
      t.organizationId,
    ),
    org_group: foreignKey({
      columns: [t.organizationId, t.groupId],
      foreignColumns: [patronGroups.organizationId, patronGroups.id],
    }),
    org_patron: foreignKey({
      columns: [t.organizationId, t.patronId],
      foreignColumns: [patrons.organizationId, patrons.id],
    }),
  }),
);

const results = await db
      .select()
      .from(schema.patronGroups)
      .leftJoin(
        schema.patronGroupAssignments,
        eq(schema.patronGroups.id, schema.patronGroupAssignments.groupId),
      )
      .leftJoin(
        schema.patrons,
        eq(schema.patronGroupAssignments.patronId, schema.patrons.id),
      )
      .leftJoin(
        schema.patronBarcodes,
        eq(schema.patrons.id, schema.patronBarcodes.patronId),
      )
      .where(eq(schema.patronGroups.id, input.groupId));