// repository.ts
import { sql, and, eq, inArray ,desc,asc} from "drizzle-orm";
import type { PgTransaction } from "drizzle-orm/pg-core";
import { roles, users, userRoles, Role, roleView, RoleView } from "@/db/schema/users"; // 수정
import db from "@/db/db"; 
import { companies } from "@/db/schema/companies";

export type NewRole = typeof roles.$inferInsert;       // User insert 시 필요한 타입


// (A) SELECT roles + userCount
export async function selectRolesWithUserCount(
  tx: PgTransaction<any, any, any>,
  params: {
    where?: any
    orderBy?: (ReturnType<typeof asc> | ReturnType<typeof desc>)[]
    offset?: number,
    limit?: number,
  }
) {

  const { where, orderBy, offset = 0, limit = 10 } = params

  const query = tx
    .select()
    .from(roleView)
    .where(where)
    .orderBy(...(orderBy ?? []))
    .offset(offset)
    .limit(limit)

  const rows = await query
  return rows
}
// (B) countRoles
export async function countRoles(
tx: PgTransaction<any, any, any>,
  where?: ReturnType<typeof and>
) {
  // COUNT(*) from roles
  const [{ count }] = await tx
    .select({ count: sql<number>`COUNT(*)`.as("count") })
    .from(roles)
    .where(where ?? undefined);

  return count; // number
}

export async function insertRole(
  tx: PgTransaction<any, any, any>,
  data: NewRole
) {
  return tx.insert(roles).values(data).returning();
}

export const getRoleById = async (id: number): Promise<Role | null> => {
  const roleFouned = await db.select().from(roles).where(eq(roles.id, id)).execute();
  if (roleFouned.length === 0) return null;

  const role = roleFouned[0];
  return role
};


export async function updateRole(
  tx: PgTransaction<any, any, any>,
  roleId: number,
  data: Partial<Role>
) {
  return tx
    .update(roles)
    .set(data)
    .where(eq(roles.id, roleId))
    .returning();
}


export async function deleteRolesByIds(
  tx: PgTransaction<any, any, any>,
  ids: number[]
) {
  return tx.delete(roles).where(inArray(roles.id, ids));
}

export async function deleteUserRolesByIds(
  tx: PgTransaction<any, any, any>,
  ids: number[]
) {
  return tx.delete(userRoles).where(inArray(userRoles.roleId, ids));
}

export async function findAllRoleView(domain?: "evcp" | "partners"): Promise<RoleView[]> {
  return db.select().from(roleView).where(eq(roleView.domain,domain)).orderBy(asc(roleView.name));
}