// schemas.ts
import {
  pgTable,
  varchar,
  timestamp,
  text,
  integer,
  uniqueIndex,
  foreignKey,
  boolean,
  index,
  primaryKey,
} from "drizzle-orm/pg-core";
import { relations, sql } from "drizzle-orm";

export const location = pgTable(
  "location",
  {
    id: text().primaryKey().notNull(),
    name: text().notNull(),
  },
);

export const bedAssignment = pgTable(
  "bed_assignment",
  {
    id: text().primaryKey().notNull(),
    startDate: text("start_date").notNull(),
    endDate: text("end_date"),
    clientId: text("client_id").notNull(),
    bedId: text("bed_id").notNull(),
  },
  (table) => [
    foreignKey({
      columns: [table.clientId],
      foreignColumns: [client.id],
      name: "bed_assignment_client_id_fkey",
    })
      .onUpdate("cascade")
      .onDelete("restrict"),
    foreignKey({
      columns: [table.bedId],
      foreignColumns: [bed.id],
      name: "bed_assignment_bed_id_fkey",
    })
      .onUpdate("cascade")
      .onDelete("restrict"),
  ],
);

export const client = pgTable(
  "client",
  {
    id: text().primaryKey().notNull(),
    locationId: text("location_id"),
  },
  (table) => [
    index("client_id_idx").using("btree", table.id.asc().nullsLast().op("text_ops")),
    foreignKey({
      columns: [table.locationId],
      foreignColumns: [location.id],
      name: "client_location_id_fkey",
    })
      .onUpdate("cascade")
      .onDelete("set null"),
  ],
);

export const bed = pgTable(
  "bed",
  {
    id: text().primaryKey().notNull(),
    locationId: text().notNull(),
  },
  (table) => [
    foreignKey({
      columns: [table.locationId],
      foreignColumns: [location.id],
      name: "bed_locationId_fkey",
    })
      .onUpdate("cascade")
      .onDelete("restrict"),
  ],
);

export const clientRelations = relations(client, ({ one, many }) => ({
  bedAssignments: many(bedAssignment),
  location: one(location, {
    fields: [client.locationId],
    references: [location.id],
  }),
}));

export const locationRelations = relations(location, ({ many }) => ({
  clients: many(client),
  beds: many(bed),
}));

export const bedRelations = relations(bed, ({ one, many }) => ({
  bedAssignments: many(bedAssignment),
  location: one(location, {
    fields: [bed.locationId],
    references: [location.id],
  }),
}));

export const bedAssignmentRelations = relations(bedAssignment, ({ one }) => ({
  client: one(client, {
    fields: [bedAssignment.clientId],
    references: [client.id],
  }),
  bed: one(bed, {
    fields: [bedAssignment.bedId],
    references: [bed.id],
  }),
}));

const db = drizzle({
  connection: {
    connectionString: process.env.DATABASE_URL,
    ssl: process.env.NODE_ENV === "production",
  },
  schema: {
    location,
    bedAssignment,
    client,
    bed,
    locationRelations,
    clientRelations,
    bedRelations,
    bedAssignmentRelations,
  },
});

(async () => {
  const result = await db.query.client.findMany({
    with: {
      bedAssignments: {
        with: {
          bed: {
            where: (bed, { eq }) => eq(bed.locationId, "some-location-id"),
            // ^ this should omit bed assignments that have a bed that don't match the location id
          }
        }
      }
    }
  });
})()

await db.query.posts.findMany({
   where: (posts, { eq }) => (eq(posts.id, 1)),
      with: {
         comments: {
            where: (comments, { lt }) => lt(comments.createdAt, new Date()),
         },
      },
});