schema.ts

// schema
export const artists = mysqlTable(
  "artists",
  {
    id: serial("id").primaryKey(),
    createdAt: timestamp("created_at")
      .notNull()
      .default(sql`CURRENT_TIMESTAMP`),
    updatedAt: timestamp("updated_at")
      .notNull()
      .default(sql`CURRENT_TIMESTAMP`),
    nameEn: varchar("name_en", { length: 50 }).notNull(),
    nameKr: varchar("name_kr", { length: 50 }).notNull(),
    debut: date("debut").notNull(),
    companyId: int("company_id").notNull(),
    isGroup: boolean("is_group").notNull().default(true),
    image: varchar("image", { length: 255 }).notNull(),
    twitter: varchar("twitter", { length: 255 }).notNull(),
    instagram: varchar("instagram", { length: 255 }).notNull(),
    youtube: varchar("youtube", { length: 255 }).notNull(),
    website: varchar("website", { length: 255 }).notNull(),
    spotifyId: varchar("spotify_id", { length: 32 }),
  },
  (table) => ({
    nameEnIndex: index("artists__name_en__idx").on(table.nameEn),
  }),
)

export const members = mysqlTable("members", {
  id: serial("id").primaryKey(),
  createdAt: timestamp("created_at")
    .notNull()
    .default(sql`CURRENT_TIMESTAMP`),
  updatedAt: timestamp("updated_at")
    .notNull()
    .default(sql`CURRENT_TIMESTAMP`),
  nameEn: varchar("name_en", { length: 50 }).notNull(),
  nameKr: varchar("name_kr", { length: 50 }).notNull(),
  stageNameEn: varchar("stage_name_en", { length: 50 }).notNull(),
  stageNameKr: varchar("stage_name_kr", { length: 50 }).notNull(),
  image: varchar("image", { length: 255 }).notNull(),
  instagram: varchar("instagram", { length: 255 }).notNull(),
})

export const artistsToMembers = mysqlTable(
  "artist_to_member",
  {
    id: serial("id").primaryKey(),
    memberId: int("member_id").notNull(),
    artistId: int("artist_id").notNull(),
  },
  (table) => ({
    memberArtistIndex: index("artist_to_member__artist_id__member_id__idx").on(
      table.memberId,
      table.artistId,
    ),
  }),
)

export const albums = mysqlTable(
  "albums",
  {
    id: serial("id").primaryKey(),
    createdAt: timestamp("created_at")
      .notNull()
      .default(sql`CURRENT_TIMESTAMP`),
    updatedAt: timestamp("updated_at")
      .notNull()
      .default(sql`CURRENT_TIMESTAMP`),
    artistId: int("artist_id").notNull(),
    name: varchar("name", { length: 50 }).notNull(),
    region: mysqlEnum("region", ["en", "kr", "jp", "other"]).notNull(),
    releaseDate: date("release_date").notNull(),
    image: varchar("image", { length: 255 }).notNull(),
    spotifyId: varchar("spotify_id", { length: 32 }),
  },
  (table) => ({
    artistIndex: index("albums__artist_id__idx").on(table.artistId),
    nameIndex: index("albums__name__idx").on(table.name),
  }),
)

export const albumVersions = mysqlTable(
  "album_versions",
  {
    id: serial("id").primaryKey(),
    createdAt: timestamp("created_at")
      .notNull()
      .default(sql`CURRENT_TIMESTAMP`),
    updatedAt: timestamp("updated_at")
      .notNull()
      .default(sql`CURRENT_TIMESTAMP`),
    name: varchar("name", { length: 50 }).notNull(),
    albumId: int("album_id").notNull(),
    image: varchar("image", { length: 255 }).notNull(),
  },
  (table) => ({
    albumIndex: index("album_versions__album_id__idx").on(table.albumId),
  }),
)

export const photocardSets = mysqlTable(
  "photocard_sets",
  {
    id: serial("id").primaryKey(),
    createdAt: timestamp("created_at")
      .notNull()
      .default(sql`CURRENT_TIMESTAMP`),
    updatedAt: timestamp("updated_at")
      .notNull()
      .default(sql`CURRENT_TIMESTAMP`),
    name: varchar("name", { length: 50 }).notNull(),
    type: mysqlEnum("type", ["album", "pob"]).notNull(),
    image: varchar("image", { length: 255 }).notNull(),
    artistId: int("artist_id").notNull(),
    albumId: int("album_id").notNull(),
  },
  (table) => ({
    artistIndex: index("photocard_sets__artist_id__idx").on(table.artistId),
    albumIndex: index("photocard_sets__album_id__idx").on(table.albumId),
  }),
)

export const photocardSetToAlbumVersions = mysqlTable(
  "photocard_set_to_album_version",
  {
    id: serial("id").primaryKey(),
    photocardSetId: int("photocard_set_id").notNull(),
    albumVersionId: int("album_version_id").notNull(),
  },
  (table) => ({
    photocardSetAlbumVersionIndex: index("album_version_id__photocard_set_id__idx").on(
      table.photocardSetId,
      table.albumVersionId,
    ),
  }),
)

// relations
export const artistRelations = relations(artists, ({ one, many }) => ({
  albums: many(albums),
  members: many(artistsToMembers),
  photocardSets: many(photocardSets),
}))

export const albumRelations = relations(albums, ({ one, many }) => ({
  artist: one(artists, {
    fields: [albums.artistId],
    references: [artists.id],
  }),
  versions: many(albumVersions),
  photocardSets: many(photocardSets),
}))

export const memberRelations = relations(members, ({ many }) => ({
  artists: many(artistsToMembers),
}))

export const artistsToMembersRelations = relations(artistsToMembers, ({ one }) => ({
  artist: one(artists, {
    fields: [artistsToMembers.artistId],
    references: [artists.id],
  }),
  member: one(members, {
    fields: [artistsToMembers.memberId],
    references: [members.id],
  }),
}))

export const albumVersionRelations = relations(albumVersions, ({ one, many }) => ({
  album: one(albums, {
    fields: [albumVersions.albumId],
    references: [albums.id],
  }),
  photocardSets: many(photocardSetToAlbumVersions),
}))

export const photocardSetRelations = relations(photocardSets, ({ one, many }) => ({
  artist: one(artists, {
    fields: [photocardSets.artistId],
    references: [artists.id],
  }),
  album: one(albums, {
    fields: [photocardSets.albumId],
    references: [albums.id],
  }),
  versions: many(photocardSetToAlbumVersions),
}))

export const photocardSetToAlbumVersionsRelations = relations(
  photocardSetToAlbumVersions,
  ({ one }) => ({
    photocardSet: one(photocardSets, {
      fields: [photocardSetToAlbumVersions.photocardSetId],
      references: [photocardSets.id],
    }),
    albumVersion: one(albumVersions, {
      fields: [photocardSetToAlbumVersions.albumVersionId],
      references: [albumVersions.id],
    }),
  }),
)


// statements
export const fetchArtistWithContent = db.query.artists
  .findFirst({
    where: (artists, { eq }) => eq(artists.id, placeholder("id")),
    with: {
      albums: true,
      members: true,
    },
  })
  .prepare()

export const fetchAlbumWithContent = db.query.albums
  .findFirst({
    where: (albums, { eq }) => eq(albums.id, placeholder("id")),
    with: {
      artist: true,
      versions: {
        with: {
          photocardSets: true,
        },
      },
      photocardSets: true,
    },
  })
  .prepare()