import { DatabaseError } from "@/lib/error";
import { logError } from "@/lib/logger";
import type { RepositoryContext } from "../datastore/repository";
import { BaseRepository } from "../datastore/repository";

export const ConsoleUserRepository = (ctx: RepositoryContext) => {
  const { datastore } = new BaseRepository(ctx);
  const CreateEntry = async (entry: {
    firstName: string;
    lastName: string;
    email: string;
    hashedPassword?: string;
    passwordHashAlgo?: string;
    passwordIterations?: number;
    passwordSalt?: string;
    username: string;
    isSuperAdmin?: boolean;
  }) => {
    try {
      const newEntry = await datastore
        .insertInto("console_users")
        .values({
          first_name: entry.firstName,
          last_name: entry.lastName,
          email: entry.email,
          username: entry.username,
          login_attempts: 0,
          hashed_password: entry.hashedPassword,
          password_hash_algo: entry.passwordHashAlgo,
          password_iterations: entry.passwordIterations,
          password_salt: entry.passwordSalt,
          is_super_admin: entry.isSuperAdmin ?? false,
        })
        .returning(["last_name", "first_name", "email", "id"])
        .executeTakeFirstOrThrow();

      return newEntry;
    } catch (err) {
      logError(err);
      throw new DatabaseError({
        error: err,
        message: "Failed to create new user",
      });
    }
  };
  const FindEntryByEmailOrUsername = async (emailOrUsername: string) => {
    try {
      const userRecord = await datastore
        .selectFrom("console_users")
        .selectAll()
        .where((eb) => {
          return eb.and([
            eb.or([
              eb("console_users.email", "=", emailOrUsername),
              eb("console_users.username", "=", emailOrUsername),
            ]),
          ]);
        })
        .executeTakeFirst();
      return userRecord;
    } catch (err) {
      logError(err);
      throw new DatabaseError({
        error: err,
        message: "Failed to fetch user details",
      });
    }
  };

  const GetEntries = async () => {
    try {
      const records = await datastore
        .selectFrom("console_users")
        .select(["id", "email", "first_name", "last_name", "username"])
        .execute();
      return records;
    } catch (err) {
      logError(err);
      throw new DatabaseError({
        error: err,
        message: "Failed to fetch user details",
      });
    }
  };

  const SearchEntries = async (entry?: {
    page?: number;
    pageSize?: number;
    searchTerm?: string;
    excludeUserIds?: string[];
    includeUserIds?: string[];
    onlyActive?: boolean;
    /** true = active only, false = inactive only, undefined = both. */
    isActive?: boolean;
    /** Console role ids; a user matches if they hold any of them. */
    roleIds?: string[];
    isSuperAdmin?: boolean;
  }) => {
    try {
      let offset;
      if (entry?.pageSize && entry?.page) {
        offset = (entry.page - 1) * entry.pageSize;
      }
      let baseQuery = datastore
        .selectFrom("console_users")
        .select([
          "id",
          "email",
          "first_name",
          "last_name",
          "username",
          "is_active",
          "is_deleted",
          // Selected because the users table has a "Created At" column. Without it the
          // console read `undefined` and `moment(undefined)` renders *now*, so every
          // user appeared to have been created at page load.
          "created_at",
          "is_super_admin",
          "avatar_url",
          "phone",
          "job_title",
          "last_login_at",
        ])
        .where("is_deleted", "=", false)
        .where((eb) => {
          if (entry?.searchTerm) {
            const search = `%${entry?.searchTerm}%`;

            return eb.or([
              eb("console_users.email", "ilike", search),
              eb("console_users.first_name", "ilike", search),
              eb("console_users.last_name", "ilike", search),
              eb("console_users.username", "ilike", search),
            ]);
          }
          // Return true condition if no search term (so it doesn't filter everything out)
          return eb.val(true);
        });

      if (entry?.excludeUserIds && entry.excludeUserIds.length > 0) {
        baseQuery = baseQuery.where("id", "not in", entry.excludeUserIds);
      }

      if (entry?.includeUserIds) {
        baseQuery = baseQuery.where("id", "in", entry.includeUserIds);
      }

      if (entry?.onlyActive) {
        baseQuery = baseQuery.where("is_active", "=", true);
      }

      if (typeof entry?.isActive === "boolean") {
        baseQuery = baseQuery.where("is_active", "=", entry.isActive);
      }

      if (typeof entry?.isSuperAdmin === "boolean") {
        baseQuery = baseQuery.where("is_super_admin", "=", entry.isSuperAdmin);
      }

      // Role filter as EXISTS rather than a join: a user holding two of the
      // selected roles must still be one row.
      if (entry?.roleIds?.length) {
        const roleIds = entry.roleIds;
        baseQuery = baseQuery.where((eb) =>
          eb.exists(
            eb
              .selectFrom("console_user_roles as user_role")
              .select("user_role.id")
              .whereRef("user_role.console_user_id", "=", "console_users.id")
              .where("user_role.console_role_id", "in", roleIds),
          ),
        );
      }

      /*
       * The total is counted before the window is applied.
       *
       * It used to be counted from `baseQuery` after `.limit()` and `.offset()` had
       * already been added, which produced
       *
       *   SELECT count(DISTINCT email) ... LIMIT 50 OFFSET 50
       *
       * An aggregate returns a single row, so OFFSET 50 skipped it and
       * `executeTakeFirst()` came back undefined — total 0, totalPages 0,
       * hasNextPage false. Page 1 looked right and every later page reported an
       * empty result set, which read as pagination being broken.
       */
      let total = 0;
      let totalPages = 0;
      if (offset !== undefined && entry?.pageSize) {
        const totalResult = await baseQuery
          .clearSelect()
          .select((eb) =>
            eb
              .fn("count", [eb.fn("distinct", [eb.ref("console_users.email")])])
              .as("total"),
          )
          .executeTakeFirst();
        total = Number(totalResult?.total ?? 0);
        totalPages = Math.ceil(total / entry?.pageSize);
      }

      /*
       * A stable order, so paging is actually a partition of the set.
       *
       * LIMIT/OFFSET without ORDER BY lets Postgres return rows in any order it
       * likes, and it need not be the same order twice — the same user can appear on
       * two pages while another appears on none. Email is unique, so it breaks ties
       * that created_at alone would leave open.
       */
      baseQuery = baseQuery
        .orderBy("created_at", "desc")
        .orderBy("email", "asc");

      if (entry?.pageSize) {
        baseQuery = baseQuery.limit(entry.pageSize ?? null);
      }
      if (offset !== undefined) {
        baseQuery = baseQuery.offset(offset ?? null);
      }

      const records = await baseQuery.execute();
      const result = {
        users: records,
        ...(entry?.page && entry?.pageSize
          ? {
              pagination: {
                page: entry.page,
                limit: entry.pageSize,
                total,
                totalPages,
                hasNextPage: entry.page < totalPages,
                hasPrevPage: entry.page > 1,
              },
            }
          : {}),
      };
      return result;
    } catch (err) {
      logError(err);
      throw new DatabaseError({
        error: err,
        message: "Failed to fetch user details",
      });
    }
  };

  /**
   * Roles held by a set of console users.
   *
   * One query for the whole page rather than one per row — the users list shows
   * role badges, and a request per user made the page O(rows).
   */
  /**
   * Profile fields a user maintains themselves.
   *
   * Separate from `UpdateEntry`, which is the admin path and can change roles,
   * activation and super-admin status — none of which a user may do to their own
   * record.
   */
  /**
   * Last sign-in per user, from the session table.
   *
   * `console_users.last_login_at` exists but nothing writes it, and back-filling
   * it would mean touching the sign-in path. The sessions are already the record
   * of who signed in and when, so read them instead — one grouped query for the
   * page.
   */
  const FindLastLoginForUsers = async (userIds: string[]) => {
    if (userIds.length === 0) return [];
    try {
      return await datastore
        .selectFrom("console_user_sessions")
        .select((eb) => [
          "console_user_id",
          eb.fn.max("created_at").as("last_login_at"),
        ])
        .where("console_user_id", "in", userIds)
        .groupBy("console_user_id")
        .execute();
    } catch (err) {
      logError(err);
      throw new DatabaseError({
        error: err,
        message: "Failed to fetch last login times",
      });
    }
  };

  const UpdateProfile = async (
    id: string,
    entry: {
      first_name?: string;
      last_name?: string;
      phone?: string | null;
      job_title?: string | null;
      avatar_url?: string | null;
    },
  ) => {
    try {
      return await datastore
        .updateTable("console_users")
        .set({ ...entry, updated_at: new Date() })
        .where("id", "=", id)
        .returning([
          "id",
          "email",
          "username",
          "first_name",
          "last_name",
          "phone",
          "job_title",
          "avatar_url",
          "is_super_admin",
          "is_active",
        ])
        .executeTakeFirstOrThrow();
    } catch (err) {
      logError(err);
      throw new DatabaseError({
        error: err,
        message: "Failed to update profile",
      });
    }
  };

  const FindRolesForUsers = async (userIds: string[]) => {
    if (userIds.length === 0) return [];
    try {
      return await datastore
        .selectFrom("console_user_roles as user_role")
        .innerJoin(
          "console_roles as role",
          "role.id",
          "user_role.console_role_id",
        )
        .select([
          "user_role.console_user_id",
          "role.id as role_id",
          "role.name as role_name",
        ])
        .where("user_role.console_user_id", "in", userIds)
        .orderBy("role.name", "asc")
        .execute();
    } catch (err) {
      logError(err);
      throw new DatabaseError({
        error: err,
        message: "Failed to fetch roles for users",
      });
    }
  };

  const UpdateEntry = async (
    id: string,
    entry: {
      first_name?: string;
      last_name?: string;
      is_active?: boolean;
      is_delete?: boolean;
      isSuperAdmin?: boolean;
      hashedPassword?: string;
      passwordHashAlgo?: string;
      passwordIterations?: number;
      passwordSalt?: string;
    },
  ) => {
    try {
      const updated = await datastore
        .updateTable("console_users")
        .set(() => {
          const updates: Record<string, unknown> = {
            updated_at: new Date().toISOString().replace(/\.\d{3}Z$/, "Z"),
          };
          if (entry.first_name !== undefined) {
            updates.first_name = entry.first_name;
          }
          if (entry.last_name !== undefined) {
            updates.last_name = entry.last_name;
          }
          if (entry.is_active !== undefined) {
            updates.is_active = entry.is_active;
          }
          if (entry.is_delete !== undefined) {
            updates.is_delete = entry.is_delete;
          }
          if (entry.isSuperAdmin !== undefined) {
            updates.is_super_admin = entry.isSuperAdmin;
          }
          if (entry.hashedPassword !== undefined) {
            updates.hashed_password = entry.hashedPassword;
          }
          if (entry.passwordHashAlgo !== undefined) {
            updates.password_hash_algo = entry.passwordHashAlgo;
          }
          if (entry.passwordIterations !== undefined) {
            updates.password_iterations = entry.passwordIterations;
          }
          if (entry.passwordSalt !== undefined) {
            updates.password_salt = entry.passwordSalt;
          }
          return updates;
        })
        .where("id", "=", id)
        .returning([
          "console_users.first_name",
          "console_users.id",
          "console_users.last_name",
          "console_users.is_super_admin",
        ])
        .executeTakeFirstOrThrow();

      return updated;
    } catch (err) {
      logError(err);
      throw new DatabaseError({
        error: err,
        message: "Failed to fetch user details",
      });
    }
  };

  const FindEntryById = async (id: string) => {
    try {
      const userRecord = await datastore
        .selectFrom("console_users")
        .select([
          "id",
          "email",
          "first_name",
          "last_name",
          "username",
          "created_at",
          "updated_at",
          "is_active",
          "is_deleted",
          "is_super_admin",
          // Profile fields, so /me can back the profile page.
          "avatar_url",
          "phone",
          "job_title",
          "last_login_at",
          "console_users.updated_at",
        ])
        .where("id", "=", id)
        .executeTakeFirstOrThrow();

      let userRolesWithPrivileges = [];
      if (userRecord?.id) {
        const userRoles = await datastore
          .selectFrom("console_user_roles as cur")
          .innerJoin("console_roles as cr", "cr.id", "cur.console_role_id")
          .select([
            "cur.id",
            "cur.console_user_id",
            "cur.console_role_id",
            "cur.created_at",
            "cr.description",
            "cr.name",
          ])
          .where("console_user_id", "=", userRecord?.id)
          .execute();

        for (const role of userRoles) {
          const rolePrivileges = await datastore
            .selectFrom("console_role_privileges as crp")
            .where("crp.console_role_id", "=", role.console_role_id)
            .innerJoin(
              "console_features as cf",
              "cf.id",
              "crp.console_feature_id",
            )
            .selectAll(["crp", "cf"])
            .execute();
          userRolesWithPrivileges.push({
            ...role,
            privileges: rolePrivileges,
          });
        }
      }
      return {
        ...userRecord,
        roles: userRolesWithPrivileges,
      };
    } catch (err) {
      logError(err);
      throw new DatabaseError({
        error: err,
        message: "Failed to fetch user details",
      });
    }
  };

  const FindSuperAdmins = async () => {
    try {
      const records = await datastore
        .selectFrom("console_users")
        .where("is_super_admin", "=", true)
        .select(["id", "email", "username"])
        .execute();
      return records;
    } catch (err) {
      logError(err);
      throw new DatabaseError({
        error: err,
        message: "Failed to fetch super admins",
      });
    }
  };

  return {
    CreateEntry,
    FindEntryByEmailOrUsername,
    GetEntries,
    SearchEntries,
    FindRolesForUsers,
    FindLastLoginForUsers,
    UpdateProfile,
    UpdateEntry,
    FindEntryById,
    FindSuperAdmins,
  };
};

export type TConsoleUserRepository = ReturnType<typeof ConsoleUserRepository>;