Error: column reference "id" is ambiguous
    at async Object.getExams (rsc://React/Server/D:%5CDev%5Cjecert%5C.next%5Cserver%5Cchunks%5Cssr%5C%5Broot%20of%20the%20server%5D__fa4e8b._.js?0:171:24)
    at async AppPage (rsc://React/Server/D:%5CDev%5Cjecert%5C.next%5Cserver%5Cchunks%5Cssr%5C%5Broot%20of%20the%20server%5D__fa4e8b._.js?1:424:19)
    at resolveErrorDev (http://localhost:3000/_next/static/chunks/node_modules_next_dist_compiled_107ce8._.js:3662:65)
    at processFullStringRow (http://localhost:3000/_next/static/chunks/node_modules_next_dist_compiled_107ce8._.js:3824:23)
    at processFullBinaryRow (http://localhost:3000/_next/static/chunks/node_modules_next_dist_compiled_107ce8._.js:3812:9)
    at progress (http://localhost:3000/_next/static/chunks/node_modules_next_dist_compiled_107ce8._.js:3932:102)

Error: column reference "id" is ambiguous
    at async Object.getExams (rsc://React/Server/D:%5CDev%5Cjecert%5C.next%5Cserver%5Cchunks%5Cssr%5C%5Broot%20of%20the%20server%5D__fa4e8b._.js?0:171:24)
    at async AppPage (rsc://React/Server/D:%5CDev%5Cjecert%5C.next%5Cserver%5Cchunks%5Cssr%5C%5Broot%20of%20the%20server%5D__fa4e8b._.js?1:424:19)
    at resolveErrorDev (http://localhost:3000/_next/static/chunks/node_modules_next_dist_compiled_107ce8._.js:3662:65)
    at processFullStringRow (http://localhost:3000/_next/static/chunks/node_modules_next_dist_compiled_107ce8._.js:3824:23)
    at processFullBinaryRow (http://localhost:3000/_next/static/chunks/node_modules_next_dist_compiled_107ce8._.js:3812:9)
    at progress (http://localhost:3000/_next/static/chunks/node_modules_next_dist_compiled_107ce8._.js:3932:102)

import { db } from "@/db"
import { exam, examProvider } from "@/db/schema"
import { ExamWithProvider } from "@/types"
import { desc, eq } from "drizzle-orm"
import { sql } from "drizzle-orm"

export const ExamService = {
  getExams: async (): Promise<ExamWithProvider[]> => {
    const rankedExams = db
      .select({
        examId: exam.id, // Renamed to be explicit
        name: exam.name,
        slug: exam.slug,
        description: exam.description,
        image: exam.image,
        createdAt: exam.createdAt,
        updatedAt: exam.updatedAt,
        provider: {
          providerId: examProvider.id, // Renamed to be explicit
          name: examProvider.name,
          displayName: examProvider.displayName,
          slug: examProvider.slug,
        },
        rowNumber: sql<number>`ROW_NUMBER() OVER (
          PARTITION BY ${examProvider.id}
          ORDER BY ${exam.updatedAt} DESC
        )`.as("row_number"),
      })
      .from(exam)
      .innerJoin(examProvider, eq(exam.providerId, examProvider.id))

    const result = await db
      .select()
      .from(rankedExams.as("ranked"))
      .where(sql`row_number <= 6`)

    // Transform back to match ExamWithProvider type
    return result.map(
      ({
        rowNumber,
        examId,
        provider: { providerId, ...providerRest },
        ...rest
      }) => ({
        id: examId,
        ...rest,
        provider: {
          id: providerId,
          ...providerRest,
        },
      })
    )
  },
}

import { db } from "@/db"
import { exam, examProvider } from "@/db/schema"
import { ExamWithProvider } from "@/types"
import { desc, eq } from "drizzle-orm"
import { sql } from "drizzle-orm"

export const ExamService = {
  getExams: async (): Promise<ExamWithProvider[]> => {
    const rankedExams = db
      .select({
        examId: exam.id, // Renamed to be explicit
        name: exam.name,
        slug: exam.slug,
        description: exam.description,
        image: exam.image,
        createdAt: exam.createdAt,
        updatedAt: exam.updatedAt,
        provider: {
          providerId: examProvider.id, // Renamed to be explicit
          name: examProvider.name,
          displayName: examProvider.displayName,
          slug: examProvider.slug,
        },
        rowNumber: sql<number>`ROW_NUMBER() OVER (
          PARTITION BY ${examProvider.id}
          ORDER BY ${exam.updatedAt} DESC
        )`.as("row_number"),
      })
      .from(exam)
      .innerJoin(examProvider, eq(exam.providerId, examProvider.id))

    const result = await db
      .select()
      .from(rankedExams.as("ranked"))
      .where(sql`row_number <= 6`)

    // Transform back to match ExamWithProvider type
    return result.map(
      ({
        rowNumber,
        examId,
        provider: { providerId, ...providerRest },
        ...rest
      }) => ({
        id: examId,
        ...rest,
        provider: {
          id: providerId,
          ...providerRest,
        },
      })
    )
  },
}

export const examProvider = pgTable("exam_provider", {
  id: uuid("id").defaultRandom().primaryKey(),
  name: varchar("name").notNull(),
  displayName: varchar("displayName").notNull(),
  slug: varchar("slug").notNull().unique(),
  createdAt: timestamp("createdAt").notNull().defaultNow(),
  updatedAt: timestamp("updatedAt")
    .notNull()
    .defaultNow()
    .$onUpdate(() => sql`CURRENT_TIMESTAMP`),
})

export const exam = pgTable("exam", {
  id: uuid("id").defaultRandom().primaryKey(),
  name: varchar("name").notNull(),
  displayName: varchar("displayName").notNull(),
  slug: varchar("slug").notNull().unique(),
  description: text("description"),
  image: varchar("image"),
  providerId: uuid("providerId")
    .notNull()
    .references(() => examProvider.id),
  createdAt: timestamp("createdAt").notNull().defaultNow(),
  updatedAt: timestamp("updatedAt")
    .notNull()
    .defaultNow()
    .$onUpdate(() => sql`CURRENT_TIMESTAMP`),
})

export const examProvider = pgTable("exam_provider", {
  id: uuid("id").defaultRandom().primaryKey(),
  name: varchar("name").notNull(),
  displayName: varchar("displayName").notNull(),
  slug: varchar("slug").notNull().unique(),
  createdAt: timestamp("createdAt").notNull().defaultNow(),
  updatedAt: timestamp("updatedAt")
    .notNull()
    .defaultNow()
    .$onUpdate(() => sql`CURRENT_TIMESTAMP`),
})

export const exam = pgTable("exam", {
  id: uuid("id").defaultRandom().primaryKey(),
  name: varchar("name").notNull(),
  displayName: varchar("displayName").notNull(),
  slug: varchar("slug").notNull().unique(),
  description: text("description"),
  image: varchar("image"),
  providerId: uuid("providerId")
    .notNull()
    .references(() => examProvider.id),
  createdAt: timestamp("createdAt").notNull().defaultNow(),
  updatedAt: timestamp("updatedAt")
    .notNull()
    .defaultNow()
    .$onUpdate(() => sql`CURRENT_TIMESTAMP`),
})