import {
  and,
  asc,
  desc,
  eq,
  inArray,
  like,
  ne,
  not,
  notInArray,
  or,
  sql,
} from 'drizzle-orm';
import {group_memberships, users} from '@db/schema';
import {db, DBTransaction} from '@db/index';
import {ChatUserStatus} from '@modules/chats/interfaces';

import {InsertGroupMembership} from '../interfaces';

export const insertMemberships = async (
  values: InsertGroupMembership[],
  tx?: DBTransaction,
) => {
  if (!values.length) {
    return;
  }

  const builder = tx ?? db.instance;
  return await builder
    .insert(group_memberships)
    .values(values)
    .onConflictDoUpdate({
      target: [group_memberships.user_id, group_memberships.conversation_id],
      set: {
        version: sql`excluded.version`,
        is_admin: sql`excluded.is_admin`,
      },
      setWhere: sql`${group_memberships.version} < excluded.version`,
    });
};

export const selectGroupMemberships = async (channel: string) => {
  return await db.instance
    .select({
      user_id: group_memberships.user_id,
      conversation_id: group_memberships.conversation_id,
      version: group_memberships.version,
    })
    .from(group_memberships)
    .where(sql`${group_memberships.conversation_id} = ${channel}`);
};

export const getOtherMemberships = ({
  channel,
  ignoreUserId,
  limit = 3,
}: {
  channel: string;
  ignoreUserId: number;
  limit?: number;
}) => {
  return db.instance
    .select({group_memberships, users})
    .from(group_memberships)
    .innerJoin(users, eq(users.id, group_memberships.user_id))
    .where(
      and(
        eq(group_memberships.conversation_id, channel),
        ne(group_memberships.user_id, ignoreUserId),
      ),
    )
    .orderBy(
      desc(group_memberships.is_admin),
      sql`LOWER(${users.first_name})`,
      sql`LOWER(${users.last_name})`,
      asc(users.id),
    )
    .limit(limit);
};

export const getUserMembership = async (channel: string, userId: number) => {
  const result = await db.instance
    .select({group_memberships, users})
    .from(group_memberships)
    .innerJoin(users, eq(users.id, group_memberships.user_id))
    .where(
      and(
        eq(group_memberships.conversation_id, channel),
        eq(group_memberships.user_id, userId),
      ),
    )
    .limit(1);

  return result.at(0) || null;
};

export const getNumMembers = async (channel: string) => {
  const result = await db.instance
    .select({count: sql<number>`COUNT(*)`})
    .from(group_memberships)
    .where(and(eq(group_memberships.conversation_id, channel)));

  return result.at(0)?.count || 0;
};

export const getAdminNumMembers = (channel: string) => {
  return db.instance
    .select({count: sql<number>`COUNT(*)`})
    .from(group_memberships)
    .where(
      and(
        eq(group_memberships.conversation_id, channel),
        eq(group_memberships.is_admin, true),
      ),
    );
};

export const getUsersFromChannel = ({
  page = 1,
  search,
  limit = 20,
  channel,
}: {
  limit?: number;
  search?: string;
  page?: number;
  channel: string;
}) => {
  const conditions = [];
  const offset = (page - 1) * limit;

  if (search) {
    const lowerSearch = `%${search.toLowerCase()}%`;
    conditions.push(
      or(
        like(
          sql`LOWER(${users.first_name} || ' ' || ${users.last_name})`,
          lowerSearch,
        ),
        like(users.employee_internal_id, lowerSearch),
        like(users.email, lowerSearch),
      ),
    );
  }
  //Drizzle translate empty conditions to SQL as NULL causing the OR
  //to be FALSE and when there are no conditions it should evaluate to TRUE, that's why
  // I am adding a condition that is always true
  conditions.push(sql`1 = 1`);

  return db.instance
    .select({
      users,
      group_memberships,
    })
    .from(group_memberships)
    .innerJoin(users, eq(group_memberships.user_id, users.id))
    .where(and(...conditions, eq(group_memberships.conversation_id, channel)))
    .orderBy(
      sql`LOWER(${users.first_name})`,
      sql`LOWER(${users.last_name})`,
      asc(users.id),
    )
    .limit(limit)
    .offset(offset);
};

export const getChannelUsersForMentions = async ({
  page = 1,
  search,
  limit = 20,
  channel,
  excludeUserIds,
}: {
  limit?: number;
  search?: string;
  page?: number;
  channel: string;
  excludeUserIds?: number[];
}) => {
  // Use a transaction to ensure a consistent snapshot
  return await db.instance.transaction(async tx => {
    const conditions = [];
    const offset = (page - 1) * limit;

    if (search) {
      const lowerSearch = `%${search.toLowerCase()}%`;
      conditions.push(
        or(
          // it will search for the first_name, last_name, and full_name + last_name
          like(
            sql`LOWER(${users.first_name} || ' ' || ${users.last_name})`,
            lowerSearch,
          ),
          like(users.employee_internal_id, lowerSearch),
          like(users.email, lowerSearch),
        ),
      );
    }

    if (excludeUserIds?.length) {
      conditions.push(not(inArray(users.id, excludeUserIds)));
    }

    // Drizzle translate empty conditions to SQL as NULL causing the OR
    // to be FALSE and when there are no conditions it should evaluate to TRUE, that's why
    // I am adding a condition that is always true
    conditions.push(sql`1 = 1`);

    const result = await tx
      .select({
        id: users.id,
        firstName: users.first_name,
        lastName: users.last_name,
        pictureAssetUrl: users.picture_asset_url,
      })
      .from(group_memberships)
      .innerJoin(users, eq(group_memberships.user_id, users.id))
      .where(
        and(
          ...conditions,
          eq(group_memberships.conversation_id, channel),
          inArray(users.status, [
            ChatUserStatus.Active,
            ChatUserStatus.Unclaimed,
          ]),
        ),
      )
      .orderBy(
        sql`LOWER(${users.first_name})`,
        sql`LOWER(${users.last_name})`,
        asc(users.id),
      )
      .limit(limit)
      .offset(offset);

    return result.map(user => ({
      id: user.id,
      firstName: user.firstName,
      lastName: user.lastName,
      profilePicture: user.pictureAssetUrl || null,
    }));
  });
};

export const deleteUserAllMemberships = async (
  userId: number,
  tx?: DBTransaction,
) => {
  const builder = tx ?? db.instance;
  return await builder
    .delete(group_memberships)
    .where(eq(group_memberships.user_id, userId));
};

export const deleteUserMembership = async (
  userId: number,
  channelId: string,
) => {
  return await db.instance
    .delete(group_memberships)
    .where(
      and(
        eq(group_memberships.user_id, userId),
        eq(group_memberships.conversation_id, channelId),
      ),
    );
};

export const deleteOrphanedMemberships = async (
  channel: string,
  validUserIds: number[],
  tx?: DBTransaction,
) => {
  if (validUserIds.length === 0) {
    return;
  }

  const builder = tx ?? db.instance;
  return await builder
    .delete(group_memberships)
    .where(
      and(
        eq(group_memberships.conversation_id, channel),
        notInArray(group_memberships.user_id, validUserIds),
      ),
    );
};

export const deleteChannelMemberships = async (channel: string) => {
  return await db.instance
    .delete(group_memberships)
    .where(eq(group_memberships.conversation_id, channel));
};

export const getAdminsFromConversation = async (channel: string) => {
  const result = await db.instance
    .select({
      is_admin: group_memberships.is_admin,
      user_id: group_memberships.user_id,
    })
    .from(group_memberships)
    .where(
      and(
        eq(group_memberships.conversation_id, channel),
        eq(group_memberships.is_admin, true),
      ),
    );
  return result;
};

export const getUserIdsFromChannel = async (channel: string) => {
  const result = await db.instance
    .select({user_id: group_memberships.user_id})
    .from(group_memberships)
    .where(eq(group_memberships.conversation_id, channel));
  return result.map(row => row.user_id);
};
