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

export const ConsoleRolesRepository = (ctx: RepositoryContext) => {
  const { datastore } = new BaseRepository(ctx);
  const CreateEntry = async (entry: {
    name: string;
    description: string;
    privileges?: {
      featureId: string;
      create: boolean;
      read: boolean;
      update: boolean;
      delete: boolean;
    }[];
  }) => {
    try {
      const newRole = await datastore
        .insertInto("console_roles")
        .values({
          name: entry.name,
          description: entry.description,
        })
        .returningAll()
        .executeTakeFirstOrThrow();

      if (newRole && (entry?.privileges ?? [])?.length > 0) {
        // create privileges
        for (const privilege of entry?.privileges ?? []) {
          // check for existing privilege
          const existing = await datastore
            .selectFrom("console_role_privileges")
            .selectAll()
            .where("console_feature_id", "=", privilege.featureId)
            .where("console_role_id", "=", newRole.id)
            .where("read", "=", privilege.read)
            .where("create", "=", privilege.create)
            .where("update", "=", privilege.update)
            .where("delete", "=", privilege.delete)
            .executeTakeFirst();

          if (!existing) {
            if (
              privilege.create ||
              privilege.read ||
              privilege.update ||
              privilege.delete
            ) {
              await datastore
                .insertInto("console_role_privileges")
                .values({
                  console_feature_id: privilege.featureId,
                  console_role_id: newRole.id,
                  create: privilege.create,
                  read: privilege.read,
                  update: privilege.update,
                  delete: privilege.delete,
                })
                .execute();
            }
          }
        }
      }
      return newRole;
    } catch (err) {
      logError(err);
      throw new DatabaseError({
        error: err,
        message: "Failed to create role and privilege",
      });
    }
  };

  const FindEntryById = async (id: string) => {
    try {
      const role = await datastore
        .selectFrom("console_roles")
        .where("id", "=", id)
        .selectAll()
        .executeTakeFirstOrThrow();
      if (role) {
        const rolePrivileges = await datastore
          .selectFrom("console_role_privileges as crp")
          .where("crp.console_role_id", "=", role.id)
          .innerJoin(
            "console_features as cf",
            "cf.id",
            "crp.console_feature_id",
          )
          .select([
            "crp.id",
            "crp.console_feature_id",
            "crp.console_role_id",
            "crp.create",
            "crp.read",
            "crp.update",
            "crp.delete",
            "crp.is_active",
            "cf.name",
            "cf.slug",
          ])
          .execute();
        if (rolePrivileges?.length > 0) {
          return {
            ...role,
            privileges: rolePrivileges,
          };
        }
        return {
          ...role,
          privileges: [],
        };
      }
    } catch (err) {
      logError(err);
      throw new DatabaseError({
        error: err,
        message: "Failed to find role",
      });
    }
  };

  const UpdateEntryById = async (
    id: string,
    entry: {
      name?: string;
      description?: string;
      privileges?: {
        featureId: string;
        create: boolean;
        read: boolean;
        update: boolean;
        delete: boolean;
      }[];
    },
  ) => {
    try {
      const updated = await datastore
        .updateTable("console_roles")
        .set(() => {
          const updates: Record<string, unknown> = {
            updated_at: new Date().toISOString().replace(/\.\d{3}Z$/, "Z"),
          };
          if (entry.name !== undefined) {
            updates.name = entry.name;
          }
          if (entry.description !== undefined) {
            updates.description = entry.description;
          }
          return updates;
        })
        .where("id", "=", id)
        .returningAll()
        .executeTakeFirstOrThrow();

      if (entry?.privileges && entry?.privileges?.length > 0) {
        for (const privilege of entry.privileges) {
          const existing = await datastore
            .selectFrom("console_role_privileges")
            .where("console_feature_id", "=", privilege.featureId)
            .where("console_role_id", "=", updated.id)
            .select(["id"])
            .executeTakeFirst();

          if (existing) {
            await datastore
              .updateTable("console_role_privileges")
              .set(() => {
                const updates: Record<string, unknown> = {
                  updated_at: new Date()
                    .toISOString()
                    .replace(/\.\d{3}Z$/, "Z"),
                };
                if (privilege.create !== undefined) {
                  updates.create = privilege.create;
                }
                if (privilege.read !== undefined) {
                  updates.read = privilege.read;
                }
                if (privilege.update !== undefined) {
                  updates.update = privilege.update;
                }
                if (privilege.delete !== undefined) {
                  updates.delete = privilege.delete;
                }
                return updates;
              })
              .where("id", "=", existing.id)
              .execute();
          } else {
            if (
              privilege.create ||
              privilege.read ||
              privilege.update ||
              privilege.delete
            ) {
              await datastore
                .insertInto("console_role_privileges")
                .values({
                  console_feature_id: privilege.featureId,
                  console_role_id: updated.id,
                  create: privilege.create,
                  delete: privilege.delete,
                  read: privilege.read,
                  update: privilege.update,
                })
                .execute();
            }
          }
        }
      }

      if (updated) {
        // remove console user sessions on update to avoid stale permissions
        const usersWithRole = await datastore
          .selectFrom("console_user_roles")
          .where("console_role_id", "=", updated.id)
          .select(["console_role_id", "console_user_id"])
          .execute();
        if (usersWithRole?.length > 0) {
          const userIds = usersWithRole.map((u) => u.console_user_id);
          await datastore
            .deleteFrom("console_user_sessions")
            .where("console_user_id", "in", userIds)
            .execute();
        }
      }

      return updated;
    } catch (err) {
      logError(err);
      throw new DatabaseError({
        error: err,
        message: "Failed to update role and privilege",
      });
    }
  };

  const FindEntries = async (entry?: {
    page?: number;
    pageSize?: number;
    searchTerm?: string;
  }) => {
    try {
      let offset;
      if (entry?.pageSize && entry?.page) {
        offset = (entry.page - 1) * entry.pageSize;
      }
      let baseQuery = datastore
        .selectFrom("console_roles")
        .selectAll()
        .where((eb) => {
          if (entry?.searchTerm) {
            const search = `%${entry?.searchTerm}%`;
            return eb.or([eb("console_roles.name", "ilike", search)]);
          }
          return eb.val(true);
        });

      let total = 0;
      let totalPages = 0;
      if (entry?.pageSize) {
        baseQuery = baseQuery.limit(entry.pageSize);
        if (offset !== undefined) {
          baseQuery = baseQuery.offset(offset);
          const totalResult = await baseQuery
            .clearSelect()
            .select((eb) =>
              eb
                .fn("count", [
                  eb.fn("distinct", [eb.ref("console_roles.name")]),
                ])
                .as("total"),
            )
            .executeTakeFirst();
          total = Number(totalResult?.total ?? 0);
          totalPages = Math.ceil(total / entry?.pageSize);
        }
      }
      const records = await baseQuery.execute();

      /*
       * Modules granted and users holding each role.
       *
       * The list used to show only a name, a description and a date, which says
       * nothing about what a role does or whether it is in use. Two grouped
       * queries for the page, not one per row.
       */
      const roleIds = records.map((role) => String(role.id));
      let privileges: {
        console_role_id: string;
        name: string;
        slug: string;
        create: boolean;
        read: boolean;
        update: boolean;
        delete: boolean;
      }[] = [];
      let userCounts: { console_role_id: string; total: string | number }[] =
        [];

      if (roleIds.length > 0) {
        privileges = (await datastore
          .selectFrom("console_role_privileges as privilege")
          .innerJoin(
            "console_features as feature",
            "feature.id",
            "privilege.console_feature_id",
          )
          .select([
            "privilege.console_role_id",
            "feature.name",
            "feature.slug",
            "privilege.create",
            "privilege.read",
            "privilege.update",
            "privilege.delete",
          ])
          .where("privilege.console_role_id", "in", roleIds)
          .where("privilege.is_active", "=", true)
          .orderBy("feature.name", "asc")
          .execute()) as typeof privileges;

        userCounts = (await datastore
          .selectFrom("console_user_roles")
          .select((eb) => ["console_role_id", eb.fn.countAll().as("total")])
          .where("console_role_id", "in", roleIds)
          .groupBy("console_role_id")
          .execute()) as typeof userCounts;
      }

      const privilegesByRole = new Map<string, typeof privileges>();
      for (const privilege of privileges) {
        const list =
          privilegesByRole.get(String(privilege.console_role_id)) ?? [];
        list.push(privilege);
        privilegesByRole.set(String(privilege.console_role_id), list);
      }
      const usersByRole = new Map(
        userCounts.map((row) => [
          String(row.console_role_id),
          Number(row.total),
        ]),
      );

      return {
        roles: records.map((role) => ({
          ...role,
          privileges: (privilegesByRole.get(String(role.id)) ?? []).map(
            (privilege) => ({
              name: privilege.name,
              slug: privilege.slug,
              create: privilege.create,
              read: privilege.read,
              update: privilege.update,
              delete: privilege.delete,
            }),
          ),
          userCount: usersByRole.get(String(role.id)) ?? 0,
        })),
        ...(entry?.page && entry?.pageSize
          ? {
              pagination: {
                page: entry.page,
                limit: entry.pageSize,
                total,
                totalPages,
                hasNextPage: entry.page < totalPages,
                hasPrevPage: entry.page > 1,
              },
            }
          : {}),
      };
    } catch (err) {
      logError(err);
      throw new DatabaseError({
        error: err,
        message: "Failed to fetch roles",
      });
    }
  };

  const FindEntryByRoleName = async (name: string) => {
    try {
      const role = await datastore
        .selectFrom("console_roles")
        .where("name", "=", name)
        .selectAll()
        .executeTakeFirst();
      if (role) {
        const rolePrivileges = await datastore
          .selectFrom("console_role_privileges as crp")
          .where("crp.console_role_id", "=", role.id)
          .innerJoin(
            "console_features as cf",
            "cf.id",
            "crp.console_feature_id",
          )
          .select([
            "crp.id",
            "crp.console_feature_id",
            "crp.console_role_id",
            "crp.create",
            "crp.read",
            "crp.update",
            "crp.delete",
            "crp.is_active",
            "cf.name",
            "cf.slug",
          ])
          .execute();
        if (rolePrivileges?.length > 0) {
          return {
            ...role,
            privileges: rolePrivileges,
          };
        }
        return {
          ...role,
          privileges: [],
        };
      }
    } catch (err) {
      logError(err);
      throw new DatabaseError({
        error: err,
        message: "Failed to find role",
      });
    }
  };

  const FindByRoleNameAndNotId = async (id: string, name: string) => {
    try {
      const record = await datastore
        .selectFrom("console_roles")
        .where("console_roles.name", "=", name)
        .where("console_roles.id", "!=", id)
        .selectAll()
        .executeTakeFirst();
      return record;
    } catch (err) {
      logError(err);
      throw new DatabaseError({
        error: err,
        message: "Failed to find role",
      });
    }
  };

  return {
    CreateEntry,
    FindEntryById,
    UpdateEntryById,
    FindEntries,
    FindEntryByRoleName,
    FindByRoleNameAndNotId,
  };
};

export type TConsoleRolesRepository = ReturnType<typeof ConsoleRolesRepository>;