import db from "@/db/db"; 
import { users, userRoles,userView,roles, type User, type UserRole, type UserView, Role  } from "@/db/schema/users";
import { companies, type Company } from "@/db/schema/companies";
import {
  eq,
  inArray,
  asc,
  desc,
  and,
  count,
  gt,
  sql,
  SQL,
} from "drizzle-orm";
import { PgTransaction } from "drizzle-orm/pg-core";
import { Vendor, vendors } from "@/db/schema/vendors";

// ============================================================
// 타입
// ============================================================

export type NewUser = typeof users.$inferInsert;       // User insert 시 필요한 타입
export type NewUserRole = typeof userRoles.$inferInsert; // UserRole insert 시 필요한 타입
export type NewCompany = typeof companies.$inferInsert;   // Company insert 시 필요한 타입



export async function selectUsersWithCompanyAndRoles(
  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

  // 1) 쿼리 빌더 생성
  const queryBuilder = tx
    .select()
    .from(userView)
    .where(where)
    .orderBy(...(orderBy ?? []))
    .offset(offset)
    .limit(limit)

  const rows = await queryBuilder
  return rows
}


/** 총 개수 count */
export async function countUsers(
  tx: PgTransaction<any, any, any>,
  where?: any
) {
  const res = await tx.select({ count: count() }).from(userView).where(where);
  return res[0]?.count ?? 0;
}

export async function groupByCompany(
  tx: PgTransaction<any, any, any>,
) {
  return tx
    .select({
      companyId: users.companyId,
      count: count(),
    })
    .from(users)
    .groupBy(users.companyId)
    .having(gt(count(), 0));
}

export async function groupByRole(tx: PgTransaction<any, any, any>) {
  return tx
    .select({
      roleId: userRoles.roleId, 
      count: sql<number>`COUNT(*)`.as("count"),
    })
    .from(users)
    .leftJoin(userRoles, eq(userRoles.userId, users.id))
    .leftJoin(roles, eq(roles.id, userRoles.roleId))
    .groupBy(userRoles.roleId, roles.id, roles.name)
    .having(gt(sql<number>`COUNT(*)` /* 또는 count()와 동일 */, 0));
}

export async function insertUser(
  tx: PgTransaction<any, any, any>,
  data: NewUser
) {
  return tx.insert(users).values(data).returning();
}

export async function insertUserRole(
  tx: PgTransaction<any, any, any>,
  data: NewUserRole
) {
  return tx.insert(userRoles).values(data).returning();
}

export async function updateUser(
  tx: PgTransaction<any, any, any>,
  userId: number,
  data: Partial<User>
) {
  return tx
    .update(users)
    .set(data)
    .where(eq(users.id, userId))
    .returning();
}

/** 복수 업데이트 */
export async function updateUsers(
  tx: PgTransaction<any, any, any>,
ids: number[],
data: Partial<User>
) {
return tx
  .update(users)
  .set(data)
  .where(inArray(users.id, ids))
  .returning({ companyId: users.companyId });
}

export async function deleteRolesByUserId(
  tx: PgTransaction<any, any, any>,
  userId: number
) {
  return tx.delete(userRoles).where(eq(userRoles.userId, userId));
}


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

export async function deleteUserById(
  tx: PgTransaction<any, any, any>,
  userId: number
) {
  return tx.delete(users).where(eq(users.id, userId));
}


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

export async function findAllCompanies(): Promise<Vendor[]> {
  return db.select().from(vendors).orderBy(asc(vendors.vendorName));
}

export async function findAllRoles(): Promise<Role[]> {
  return db.select().from(roles).where(eq(roles.domain ,'partners')).orderBy(asc(roles.name));
}

export const getUserById = async (id: number): Promise<UserView | null> => {
  const userFouned = await db.select().from(userView).where(eq(userView.user_id, id)).execute();
  if (userFouned.length === 0) return null;

  const user = userFouned[0];
  return user
};