// lib/users/repository.ts
import db from '@/db/db';
import { users, otps, type User, Role, roles, userRoles } from '@/db/schema/users';
import { Otp } from '@/types/user';
import { eq,and ,asc} from 'drizzle-orm';

// 모든 사용자 조회
export const getAllUsers = async (): Promise<User[]> => {
  const usersRes = await db.select().from(users).execute();
  return usersRes
};

export async function getRoleAssignedUsers(roleId: number) {
  const rows = await db
    .select()
    .from(userRoles)
    .where(eq(userRoles.roleId, roleId))
  return rows.map((r) => r.userId)  // [1, 2, 5, ...]
}


// ID로 사용자 조회 (기본적으로 활성 사용자만)
export const getUserById = async (
  id: number, 
  options: GetUserOptions = {}
): Promise<User | null> => {
  const { includeInactive = false } = options
  
  let whereCondition = eq(users.id, id)
  
  // 기본적으로 활성 사용자만 조회
  if (!includeInactive) {
    whereCondition = and(
      eq(users.id, id),
      eq(users.isActive, true)
    )!
  }
  
  const usersRes = await db
    .select()
    .from(users)
    .where(whereCondition)
    .execute()
    
  if (usersRes.length === 0) return null
  
  const user = usersRes[0]
  return user
}

// Email로 사용자 조회 (기본적으로 활성 사용자만)
export const getUserByEmail = async (
  email: string, 
  options: GetUserOptions = {}
): Promise<User | null> => {
  const { includeInactive = false } = options
  
  let whereCondition = eq(users.email, email)
  
  // 기본적으로 활성 사용자만 조회
  if (!includeInactive) {
    whereCondition = and(
      eq(users.email, email),
      eq(users.isActive, true)
    )!
  }
  
  const usersRes = await db
    .select()
    .from(users)
    .where(whereCondition)
    .execute()
    
  if (usersRes.length === 0) return null
  
  const user = usersRes[0]
  return user
}


// 새 사용자 생성
export const createUser = async (name: string, email: string): Promise<User> => {
  const usersRes = await db.insert(users).values({ name, email }).returning();
  const user = usersRes[0];
  return user
};

// SAML 사용자 생성 (domain과 추가 정보 포함)
export const createSAMLUser = async (
  name: string, 
  email: string, 
  domain: 'evcp' | 'partners' = 'evcp',
): Promise<User> => {
  const usersRes = await db.insert(users).values({ 
    name, 
    email, 
    domain,
  }).returning();
  const user = usersRes[0];
  return user
};

// 사용자 업데이트
export const updateUser = async (id: number, data: Partial<User>): Promise<User | null> => {
  // 전화번호가 있는 경우 trim 처리
  if (data.phone !== undefined) {
    data.phone = data.phone?.trim();
  }
  
  const usersRes = await db.update(users).set(data).where(eq(users.id, id)).returning();
  if (usersRes.length === 0) return null;
  const user = usersRes[0];
  return user
};

// 사용자 삭제
export const deleteUser = async (id: number): Promise<boolean> => {
  const result = await db.delete(users).where(eq(users.id, id)).execute();
  return (result.rowCount ?? 0) > 0; // null일 경우 0으로 처리
};


// 새 otp 생성
export const createOtp = async ( email: string, code:string, createdAt:Date, otpToken:string, otpExpires:Date ): Promise<Otp> => {
  const otp = await db.insert(otps).values({ email, code, createdAt, otpToken,otpExpires  }).returning();
  return otp[0]
};


export const findOtpByEmail = async (email: string): Promise<Otp | null> => {
  const [otpRecord] = await db
    .select()
    .from(otps)
    .where(eq(otps.email, email))

  return otpRecord ?? null
}

export const updateOtp = async (
  email: string,
  code: string,
  createdAt: Date,
  otpToken: string,
  otpExpires: Date
): Promise<Otp> => {
  const rows = await db
    .update(otps)
    .set({
      code,
      createdAt,
      otpToken,
      otpExpires,
    })
    .where(eq(otps.email, email))
    .returning();

  return rows[0];
};

// Email 및 토큰으로 opt 조회
export const getOtpByEmailAndToken = async (email: string, token:string): Promise<Otp | null> => {
  const opts = await db.select().from(otps).where(eq(otps.email, email)).execute();
  if (opts.length === 0) return null;

  const otp = opts[0];
  return otp
};


export const getOtpByEmailAndCode = async (
  email: string,
  code: string
): Promise<Otp | null> => {

  const [otp] = await db
    .select()
    .from(otps)
    .where(
      and(eq(otps.email, email), eq(otps.code, code))
    );

  return otp ?? null;
};

export const getOtpByEmail = async (
  email: string,
): Promise<User | null> => {

  const [user] = await db
    .select()
    .from(users)
    .where(
      eq(users.email, email)
    );

  return user ?? null;
};

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