import { db } from '../db/db_index'
import {
  users,
  userCreds,
  userDetails,
  addresses,
  cartItems,
  complaints,
  couponApplicableUsers,
  couponUsage,
  notifCreds,
  notifications,
  orderItems,
  orderStatus,
  orders,
  payments,
  refunds,
  productReviews,
  reservedCoupons,
} from '../db/schema'
import { eq } from 'drizzle-orm'

export async function getUserByEmail(email: string) {
  const [user] = await db.select().from(users).where(eq(users.email, email)).limit(1)
  return user || null
}

export async function getUserByMobile(mobile: string) {
  const [user] = await db.select().from(users).where(eq(users.mobile, mobile)).limit(1)
  return user || null
}

export async function getUserById(userId: number) {
  const [user] = await db.select().from(users).where(eq(users.id, userId)).limit(1)
  return user || null
}

export async function getUserCreds(userId: number) {
  const [creds] = await db.select().from(userCreds).where(eq(userCreds.userId, userId)).limit(1)
  return creds || null
}

export async function getUserDetails(userId: number) {
  const [details] = await db.select().from(userDetails).where(eq(userDetails.userId, userId)).limit(1)
  return details || null
}

export async function isUserSuspended(userId: number): Promise<boolean> {
  const details = await getUserDetails(userId)
  return details?.isSuspended ?? false
}

export async function createUserWithProfile(input: {
  name: string
  email: string
  mobile: string
  hashedPassword: string
  profileImage?: string | null
}) {
  return db.transaction(async (tx) => {
    // Create user
    const [user] = await tx.insert(users).values({
      name: input.name,
      email: input.email,
      mobile: input.mobile,
    }).returning()

    // Create user credentials
    await tx.insert(userCreds).values({
      userId: user.id,
      userPassword: input.hashedPassword,
    })

    // Create user details with profile image
    await tx.insert(userDetails).values({
      userId: user.id,
      profileImage: input.profileImage || null,
    })

    return user
  })
}

export async function getUserDetailsByUserId(userId: number) {
  const [details] = await db.select().from(userDetails).where(eq(userDetails.userId, userId)).limit(1)
  return details || null
}

export async function updateUserProfile(userId: number, data: {
  name?: string
  email?: string
  mobile?: string
  hashedPassword?: string
  profileImage?: string
  bio?: string
  dateOfBirth?: Date | null
  gender?: string
  occupation?: string
}) {
  return db.transaction(async (tx) => {
    // Update user table
    const userUpdate: any = {}
    if (data.name !== undefined) userUpdate.name = data.name
    if (data.email !== undefined) userUpdate.email = data.email
    if (data.mobile !== undefined) userUpdate.mobile = data.mobile

    if (Object.keys(userUpdate).length > 0) {
      await tx.update(users).set(userUpdate).where(eq(users.id, userId))
    }

    // Update password if provided
    if (data.hashedPassword) {
      await tx.update(userCreds).set({
        userPassword: data.hashedPassword,
      }).where(eq(userCreds.userId, userId))
    }

    // Update or insert user details
    const detailsUpdate: any = {}
    if (data.bio !== undefined) detailsUpdate.bio = data.bio
    if (data.dateOfBirth !== undefined) detailsUpdate.dateOfBirth = data.dateOfBirth
    if (data.gender !== undefined) detailsUpdate.gender = data.gender
    if (data.occupation !== undefined) detailsUpdate.occupation = data.occupation
    if (data.profileImage !== undefined) detailsUpdate.profileImage = data.profileImage
    detailsUpdate.updatedAt = new Date()

    const [existingDetails] = await tx.select().from(userDetails).where(eq(userDetails.userId, userId)).limit(1)

    if (existingDetails) {
      await tx.update(userDetails).set(detailsUpdate).where(eq(userDetails.userId, userId))
    } else {
      await tx.insert(userDetails).values({
        userId,
        ...detailsUpdate,
        createdAt: new Date(),
      })
    }

    // Return updated user
    const [user] = await tx.select().from(users).where(eq(users.id, userId)).limit(1)
    return user
  })
}

export async function createUserWithCreds(input: {
  name: string
  email: string
  mobile: string
  hashedPassword: string
}) {
  return db.transaction(async (tx) => {
    const [user] = await tx.insert(users).values({
      name: input.name,
      email: input.email,
      mobile: input.mobile,
    }).returning()

    await tx.insert(userCreds).values({
      userId: user.id,
      userPassword: input.hashedPassword,
    })

    return user
  })
}

export async function createUserWithMobile(mobile: string) {
  const [user] = await db.insert(users).values({
    name: null,
    email: null,
    mobile,
  }).returning()

  return user
}

export async function upsertUserPassword(userId: number, hashedPassword: string) {
  try {
    await db.insert(userCreds).values({
      userId,
      userPassword: hashedPassword,
    })
    return
  } catch (error: any) {
    if (error.code === '23505') {
      await db.update(userCreds).set({
        userPassword: hashedPassword,
      }).where(eq(userCreds.userId, userId))
      return
    }
    throw error
  }
}

export async function deleteUserAccount(userId: number) {
  await db.transaction(async (tx) => {
    await tx.delete(notifCreds).where(eq(notifCreds.userId, userId))
    await tx.delete(couponApplicableUsers).where(eq(couponApplicableUsers.userId, userId))
    await tx.delete(couponUsage).where(eq(couponUsage.userId, userId))
    await tx.delete(complaints).where(eq(complaints.userId, userId))
    await tx.delete(cartItems).where(eq(cartItems.userId, userId))
    await tx.delete(notifications).where(eq(notifications.userId, userId))
    await tx.delete(productReviews).where(eq(productReviews.userId, userId))

    await tx.update(reservedCoupons)
      .set({ redeemedBy: null })
      .where(eq(reservedCoupons.redeemedBy, userId))

    const userOrders = await tx
      .select({ id: orders.id })
      .from(orders)
      .where(eq(orders.userId, userId))

    for (const order of userOrders) {
      await tx.delete(orderItems).where(eq(orderItems.orderId, order.id))
      await tx.delete(orderStatus).where(eq(orderStatus.orderId, order.id))
      await tx.delete(payments).where(eq(payments.orderId, order.id))
      await tx.delete(refunds).where(eq(refunds.orderId, order.id))
      await tx.delete(couponUsage).where(eq(couponUsage.orderId, order.id))
      await tx.delete(complaints).where(eq(complaints.orderId, order.id))
    }

    await tx.delete(orders).where(eq(orders.userId, userId))
    await tx.delete(addresses).where(eq(addresses.userId, userId))
    await tx.delete(userDetails).where(eq(userDetails.userId, userId))
    await tx.delete(userCreds).where(eq(userCreds.userId, userId))
    await tx.delete(users).where(eq(users.id, userId))
  })
}
