// schema.ts =========================================
import { pgTable, text, varchar } from 'drizzle-orm/pg-core';

export const roles = pgTable('roles', {
  id: text('id').primaryKey(),
  name: varchar('name', { length: 255 }).notNull(),
});

export const userRoles = pgTable('user_roles', {
  userId: text('user_id').notNull(),
  roleId: text('role_id').notNull(),
});

// role-db.ts =======================================
import { eq } from 'drizzle-orm';
import { roles, userRoles } from './schema';
import { db } from './db';
import { create } from "../exceptions/exceptions";


export async function getAllIncludingUserToRoles(throwIfNotFound: boolean = true) {
  let resources = await db.select()
    .from(roles)
    .leftJoin(userRoles, eq(roles.id, userRoles.roleId))
    .execute();

  if (throwIfNotFound && resources.length === 0) {
    throw create(`No roles were found including user roles`);
  }

  return resources;
}

export async function anyRoleExists() {
  const result = await db.select({ count: sql`count(*)` })
    .from(roles)
    .execute();
  return result[0].count > 0;
}

export async function getAll() {
  return db.select().from(roles).execute();
}