import bcrypt from "bcrypt";
import User from "@/models/User";
import sequelize from "@/lib/sequelize";
import { QueryTypes } from "sequelize";

export enum Role {
  USER = "USER",
  ADMIN = "ADMIN",
  MODERATOR = "MODERATOR",
}

let usersTableColumnsCache: Set<string> | null = null;
let usersTableNameCache: "users" | "User" | "Users" | null = null;

async function getUsersTableName(): Promise<"users" | "User" | "Users"> {
  if (usersTableNameCache) {
    return usersTableNameCache;
  }

  const candidates: Array<"users" | "User" | "Users"> = ["users", "User", "Users"];

  for (const tableName of candidates) {
    const existsRows = (await sequelize.query(`SHOW TABLES LIKE :tableName`, {
      replacements: { tableName },
      type: QueryTypes.SELECT,
    })) as Array<Record<string, unknown>>;

    if (existsRows.length > 0) {
      usersTableNameCache = tableName;
      return tableName;
    }
  }

  usersTableNameCache = "users";
  return usersTableNameCache;
}

async function getUsersTableColumns(): Promise<Set<string>> {
  if (usersTableColumnsCache) {
    return usersTableColumnsCache;
  }

  const tableName = await getUsersTableName();

  const columns = (await sequelize.query(`SHOW COLUMNS FROM ${tableName}`, {
    type: QueryTypes.SELECT,
  })) as Array<{ Field: string }>;

  usersTableColumnsCache = new Set(columns.map((column) => column.Field));
  return usersTableColumnsCache;
}

function normalizeRole(role?: string | null, usertype?: string | null): Role {
  const rawValue = (role || usertype || "USER").toString().toUpperCase();
  const value = rawValue === "STUDENT" ? "USER" : rawValue;
  if (value === Role.ADMIN) return Role.ADMIN;
  if (value === Role.MODERATOR) return Role.MODERATOR;
  return Role.USER;
}

// Password hashing utility
export async function hashPassword(password: string): Promise<string> {
  const salt = await bcrypt.genSalt(10);
  return bcrypt.hash(password, salt);
}

// Password verification utility
export async function verifyPassword(password: string, hash: string): Promise<boolean> {
  return bcrypt.compare(password, hash);
}

// User login
export async function loginUser(email: string, password: string) {
  try {
    const loginIdentifier = email.trim().toLowerCase();
    const tableName = await getUsersTableName();
    const columns = await getUsersTableColumns();

    const conditions: string[] = [];

    if (columns.has("email")) {
      conditions.push("LOWER(email) = :login");
    }

    if (columns.has("name")) {
      conditions.push("LOWER(name) = :login");
    }

    if (columns.has("username")) {
      conditions.push("LOWER(username) = :login");
    }

    if (columns.has("usertype")) {
      conditions.push("LOWER(usertype) = :login");
    }

    if (columns.has("role")) {
      conditions.push("LOWER(role) = :login");
    }

    if (conditions.length === 0) {
      return null;
    }

    const rows = (await sequelize.query(
      `SELECT * FROM ${tableName} WHERE ${conditions.join(" OR ")} LIMIT 1`,
      {
        replacements: { login: loginIdentifier },
        type: QueryTypes.SELECT,
      }
    )) as Array<Record<string, unknown>>;

    const user = rows[0];

    if (!user) {
      return null;
    }

    const lockedUntil = columns.has("lockedUntil") ? (user.lockedUntil as Date | string | null) : null;
    if (lockedUntil && new Date(lockedUntil) > new Date()) {
      throw new Error("Account temporarily locked. Try again later.");
    }

    const isActive = columns.has("isActive") ? Boolean(user.isActive) : true;
    if (!isActive) {
      throw new Error("Account is inactive.");
    }

    const storedPassword = String(user.password || "");
    const bcryptMatch = storedPassword ? await verifyPassword(password, storedPassword) : false;
    const plainTextMatch = password === storedPassword;
    const isPasswordValid = bcryptMatch || plainTextMatch;

    if (!isPasswordValid) {
      const currentAttempts = columns.has("loginAttempts") ? Number(user.loginAttempts || 0) : 0;
      const nextAttempts = currentAttempts + 1;

      if (columns.has("loginAttempts")) {
        await sequelize.query(
          `UPDATE ${tableName} SET loginAttempts = :loginAttempts WHERE id = :id`,
          {
            replacements: { loginAttempts: nextAttempts, id: Number(user.id) },
            type: QueryTypes.UPDATE,
          }
        );
      }

      if (columns.has("lockedUntil") && nextAttempts >= 5) {
        const lockUntil = new Date(Date.now() + 15 * 60 * 1000); // 15 minutes
        await sequelize.query(
          `UPDATE ${tableName} SET lockedUntil = :lockedUntil WHERE id = :id`,
          {
            replacements: { lockedUntil: lockUntil, id: Number(user.id) },
            type: QueryTypes.UPDATE,
          }
        );
        throw new Error("Account locked due to too many failed login attempts.");
      }

      return null;
    }

    if (columns.has("loginAttempts") || columns.has("lockedUntil") || columns.has("lastLogin") || plainTextMatch) {
      const updates: string[] = [];
      const replacements: Record<string, unknown> = { id: Number(user.id) };

      if (columns.has("loginAttempts")) {
        updates.push("loginAttempts = :loginAttempts");
        replacements.loginAttempts = 0;
      }

      if (columns.has("lockedUntil")) {
        updates.push("lockedUntil = :lockedUntil");
        replacements.lockedUntil = null;
      }

    //   if (columns.has("lastLogin")) {
    //     updates.push("lastLogin = :lastLogin");
    //     replacements.lastLogin = new Date();
    //   }

      if (plainTextMatch) {
        updates.push("password = :password");
        replacements.password = await hashPassword(password);
      }

      if (updates.length > 0) {
        await sequelize.query(
          `UPDATE ${tableName} SET ${updates.join(", ")} WHERE id = :id`,
          {
            replacements,
            type: QueryTypes.UPDATE,
          }
        );
      }
    }

    return {
      id: Number(user.id),
      email: String(user.email),
      name: (user.name as string | null) || null,
      role: normalizeRole(user.role as string | null, user.usertype as string | null),
    };
  } catch (error) {
    throw error;
  }
}

// Register new user
export async function registerUser(
  email: string,
  password: string,
  name?: string,
  role: Role = Role.USER
) {
  try {
    const existingUser = await User.findOne({
      where: { email: email.toLowerCase() },
    });

    if (existingUser) {
      throw new Error("User already exists");
    }

    if (password.length < 8) {
      throw new Error("Password must be at least 8 characters long");
    }

    const hashedPassword = await hashPassword(password);

    const newUser = await User.create({
      email: email.toLowerCase(),
      password: hashedPassword,
      name: name || email.split("@")[0],
      role,
      isActive: true,
      loginAttempts: 0,
    });

    return {
      id: newUser.id,
      email: newUser.email,
      name: newUser.name,
      role: newUser.role,
    };
  } catch (error) {
    throw error;
  }
}

// Get user by ID
export async function getUserById(id: number) {
  try {
    const user = await User.findByPk(id, {
      attributes: ["id", "email", "name", "role", "isActive", "createdAt", "updatedAt"]
    });

    return user ? user.get({ plain: true }) : null;
  } catch (error) {
    throw error;
  }
}

// Get user by email
export async function getUserByEmail(email: string) {
  try {
    const user = await User.findOne({
      where: { email: email.toLowerCase() },
      attributes: ["id", "email", "name", "role", "isActive", "createdAt", "updatedAt"]
    });

    return user ? user.get({ plain: true }) : null;
  } catch (error) {
    throw error;
  }
}

// Update user
export async function updateUser(id: number, data: { name?: string; email?: string }) {
  try {
    const user = await User.findByPk(id);
    if (!user) throw new Error("User not found");

    const updatedUser = await user.update({
      ...data,
      email: data.email ? data.email.toLowerCase() : undefined,
    });

    return updatedUser.get({ plain: true });
  } catch (error) {
    throw error;
  }
}

// Change password
export async function changePassword(id: number, oldPassword: string, newPassword: string) {
  try {
    const user = await User.findByPk(id);

    if (!user) {
      throw new Error("User not found");
    }

    const isPasswordValid = await verifyPassword(oldPassword, user.password);

    if (!isPasswordValid) {
      throw new Error("Current password is incorrect");
    }

    if (newPassword.length < 8) {
      throw new Error("New password must be at least 8 characters long");
    }

    const hashedPassword = await hashPassword(newPassword);

    await user.update({ password: hashedPassword });

    return { success: true };
  } catch (error) {
    throw error;
  }
}

// Check if user has permission
export function hasRole(userRole: Role, requiredRoles: Role[]): boolean {
  return requiredRoles.includes(userRole);
}

// Get all users (admin only)
export async function getAllUsers(limit: number = 10, offset: number = 0) {
  try {
    const { count: total, rows: users } = await User.findAndCountAll({
      attributes: ["id", "email", "name", "role", "isActive", "lastLogin", "createdAt", "updatedAt"],
      offset,
      limit,
      order: [["createdAt", "DESC"]],
    });

    return { users: users.map(u => u.get({ plain: true })), total };
  } catch (error) {
    throw error;
  }
}

// Toggle user active status (admin only)
export async function toggleUserActiveStatus(id: number, isActive: boolean) {
  try {
    const user = await User.findByPk(id);
    if (!user) throw new Error("User not found");

    const updatedUser = await user.update({ isActive });

    return {
      id: updatedUser.id,
      email: updatedUser.email,
      name: updatedUser.name,
      role: updatedUser.role,
      isActive: updatedUser.isActive,
      createdAt: updatedUser.createdAt,
    };
  } catch (error) {
    throw error;
  }
}
