import { Injectable } from '@nestjs/common';
import { Prisma } from '@prisma/client';
import { PrismaService } from 'src/prisma/prisma.service';
import { UsersService } from 'src/users/users.service';

@Injectable()
export class AdminService {
  constructor(
    private prisma: PrismaService,
    private usersService: UsersService
  ) { }

  async getAllUsers(): Promise<any> {
    const result = await this.prisma.$queryRaw`
      SELECT status, user_id, suspended, u.suspended_date, email, firstname, lastname, last_logon_date
      FROM users u
      WHERE u.user_class_id = 1
      AND suspended <> 2
      ORDER BY firstname, lastname ASC
    `;
    return result;
  }

  async getMemberActivity(): Promise<any> {
    const result = await this.prisma.$queryRaw`
      SELECT a.updatedate, a.ip_address, u.firstname, u.lastname, group_name, a.actionid, description
      FROM auditlog a
      INNER JOIN users u
      ON u.user_id = a.user_id
      LEFT OUTER JOIN user_group ug
      ON ug.user_group_id = a.user_group_id
      ORDER BY a.updatedate DESC
      LIMIT 0, 50
    `;
    return result;
  }

  async getLiveUsers(): Promise<any> {
    const result = await this.prisma.$queryRaw`
      SELECT s.ip_address, s.user_agent, s.last_activity, s.user_id , concat(u.firstname,' ',u.lastname) AS username
      FROM ipw_sessions s
      LEFT OUTER JOIN users u
      ON u.user_id = s.user_id
      ORDER BY last_activity DESC
    `;
    return result;
  }

  /*
   * @description Assign a set of features to a IPW user
   * This is used specifically for IPW staff and auditors
   * @return null if no match or the record id
   */
  async addUserToRole(user_id: number, roles_id: number): Promise<void> {
    const users_has_group_id = await this.usersService.findUserHasGroupId(user_id, this.usersService.ipwGroupID);
    if (!users_has_group_id) return;
    await this.prisma.rbac_users_has_roles.create({
      data: {
        users_has_group_id,
        roles_id
      }
    });
    return;
  }

  async getAllMembers(filter: string, search: string): Promise<any> {
    // search query
    if (filter == "All") filter = "";
    const applyFilterValue = `${filter}%`;
    const applySearchValue = `%${search}%`;
    const applyFilterClause = filter.length > 0 && search.length === 0 ?
                              Prisma.sql`AND firstname LIKE ${applyFilterValue}` :
                              Prisma.empty;
    const applySearchClause = search.length > 0 ? Prisma.sql`
      AND (firstname LIKE ${applySearchValue})
      OR (lastname LIKE ${applySearchValue})
      OR (email LIKE ${applySearchValue})
    ` : Prisma.empty;
    const result = await this.prisma.$queryRaw`
      SELECT user_id, suspended, email, firstname, lastname, DATE_FORMAT(last_logon_date, '%D %M %Y') as logondate
      FROM users
      WHERE status = 1
      AND suspended <> 2
      AND user_class_id = 2
      ${applyFilterClause}
      ${applySearchClause}
      ORDER BY firstname ASC;
    `;
    return result;
  }

  /*
   * @description Remove a member from a group
   * NB: This is for removing member roles only, administrative accounts can have multiple roles assigned to the group
   */
  async removeGroupMembership(users_has_group_id: number): Promise<void> {
    // Get the users_has_role id for this users group association
    const rbac_users_has_roles = await this.prisma.rbac_users_has_roles.findFirst({
      where: {
        users_has_group_id
      },
      select: {
        id: true
      }
    });
    // Remove the users role for this group association
    if (rbac_users_has_roles) await this.prisma.rbac_users_has_roles.delete({
      where: {
        id: rbac_users_has_roles.id
      }
    });
    // Remove the users group association
    await this.prisma.users_has_group.delete({
      where: {
        users_has_group_id
      }
    });
    return;
  }

  /*
   * @description Add a member to a group
   */
  async addGroupMembership(user_id: number, user_group_id: number): Promise<void> {
    // Remove the users group association
    const tst = await this.prisma.users_has_group.create({
      data: {
        user_group_id,
        user_id
      }
    });
    return;
  }

  // async groupsAndRoles(user_id: number): Promise<any> {
  //   const result = await this.prisma.$queryRaw`
  //     SELECT uhg.users_has_group_id, ug.user_group_id, ug.group_name, ug.bis_id
  //     FROM users u
  //     INNER JOIN users_has_group uhg
  //     ON uhg.user_id = u.user_id
  //     INNER JOIN user_group ug
  //     ON ug.user_group_id = uhg.user_group_id
  //     WHERE u.user_id = ${user_id}
  //     AND ug.user_group_id NOT IN (1364,7361)
  //   `;
  //   // 1364 = IPW
  //   // 7361 = IPW SUSPENDED USERS
  //   return result;
  // }

  // async roleIdForGroups(users_has_group_ids: bigint[]): Promise<any> {
  //   if (users_has_group_ids.length === 0) return [];
  //   const result = await this.prisma.$queryRaw`
  //     SELECT ur.roles_id, uhg.users_has_group_id
  //     FROM rbac_users_has_roles ur
  //     INNER JOIN users_has_group uhg
  //     ON uhg.users_has_group_id = ur.users_has_group_id
  //     WHERE uhg.users_has_group_id
  //     IN (${users_has_group_ids.join(',')})
  //   `;
  //   return result;
  // }

  // async availableGroups(user_id: number): Promise<any> {
  //   const result = await this.prisma.$queryRaw`
  //     SELECT distinct ug.user_group_id, ug.group_name
  //     FROM user_group ug
  //     INNER JOIN users_has_group uhg
  //     ON uhg.user_group_id = ug.user_group_id
  //     WHERE uhg.user_group_id NOT IN (
  //       SELECT user_group_id
  //       FROM users_has_group
  //       WHERE user_id = ${user_id}
  //     )
  //     AND uhg.user_group_id NOT IN (
  //       SELECT user_group_id
  //       FROM users_has_group_pending
  //       WHERE user_id = ${user_id}
  //     )
  //     AND uhg.user_group_id NOT IN (1364,7361)
  //     ORDER BY ug.group_name ASC
  //   `;
  //   // 1364 = IPW
  //   // 7361 = IPW SUSPENDED USERS
  //   return result;
  // }
}