import {eq, and, desc, sql, isNotNull, inArray, count} from 'drizzle-orm';
import {db, DBTransaction} from '@db/index';
import {
  assets,
  conversation_properties,
  conversations,
  group_memberships,
  messages,
  messagesToAssets,
  reactions,
} from '@db/schema';
import {flattenMessageAssets} from '@modules/chats/utils/mappers/assetMapper';
import {
  CONVERSATION_MESSAGES_LIMIT,
  LOCAL_INITIAL_VERSION,
  PRIORITY_CONVERSATION_COUNT,
} from '@modules/chats/constants';
import {fileHandler} from '@modules/chats/instances/FileHandler';

import {InsertConversation, InsertConversationProperties} from '../interfaces';

export const getConversationById = async (id: string) => {
  const conversation = await db.instance.query.conversations.findFirst({
    where: eq(conversations.id, id),
    with: {
      properties: true,
    },
  });
  return conversation;
};

export const getFullConversationById = async (id: string) => {
  const conversation = await db.instance.query.conversations.findFirst({
    where: eq(conversations.id, id),
    with: {
      latest_message: true,
      creator: true,
      user: true,
      properties: true,
    },
  });

  return conversation;
};

export const insertConversations = (
  values: InsertConversation[],
  tx?: DBTransaction,
) => {
  if (!values.length) {
    return;
  }
  const builder = tx ?? db.instance;
  return builder
    .insert(conversations)
    .values(values)
    .onConflictDoUpdate({
      target: conversations.id,
      set: {
        name: sql`excluded.name`,
        latest_message_id: sql`excluded.latest_message_id`,
        mentioned: sql`excluded.mentioned`,
        is_channel: sql`excluded.is_channel`,
        num_members: sql`excluded.num_members`,
        version: sql`excluded.version`,
        user_id: sql`excluded.user_id`,
        is_im: sql`excluded.is_im`,
        is_user_deleted: sql`excluded.is_user_deleted`,
        priority: sql`excluded.priority`,
        is_private: sql`excluded.is_private`,
        picture_url: sql`excluded.picture_url`,
        creator_id: sql`excluded.creator_id`,
        description: sql`excluded.description`,
        is_active: sql`excluded.is_active`,
        updated: sql`excluded.updated`,
      },
      // Update conversation if the version is less than the excluded version
      setWhere: sql`${conversations.version} < excluded.version`,
    });
};

export const insertConversation = (
  values: InsertConversation,
  tx?: DBTransaction,
) => {
  const builder = tx ?? db.instance;
  return builder
    .insert(conversations)
    .values(values)
    .onConflictDoUpdate({
      target: conversations.id,
      set: values,
      setWhere: sql`${conversations.version} < ${values.version || 0}`,
    });
};

export const getLatestByConversationIds = async (ids: string[]) => {
  return await db.instance.query.conversations.findMany({
    where: inArray(conversations.id, ids),
    with: {latest_message: true},
  });
};

export const updateConversationLatest = async (
  id: string,
  latestMessageId: string,
) => {
  return await db.instance
    .update(conversations)
    .set({
      latest_message_id: latestMessageId,
    })
    .where(eq(conversations.id, id));
};

export const updateConversationLatestInBatch = async (
  params: {
    id: string;
    latestMessageId: string;
    ts: string;
  }[],
) => {
  if (params.length === 0) return;

  const lastMessagesFromDB = await getLatestByConversationIds(
    params.map(param => param.id),
  );

  const tsMapper = new Map(
    lastMessagesFromDB.map(conversation => [
      conversation.id,
      conversation.latest_message?.ts,
    ]),
  );

  return await db.instance.transaction(async tx => {
    for (const {id, latestMessageId, ts} of params) {
      try {
        const latestTs = tsMapper.get(id);
        if (latestTs && latestTs >= ts) {
          continue;
        }

        await tx
          .update(conversations)
          .set({
            latest_message_id: latestMessageId,
          })
          .where(and(eq(conversations.id, id)));
      } catch (error) {
        continue;
      }
    }
  });
};

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

  const builder = tx ?? db.instance;
  return await builder
    .insert(conversation_properties)
    .values(values)
    .onConflictDoUpdate({
      target: conversation_properties.conversation_id,
      set: {
        version: sql`excluded.version`,
        notification_type: sql`excluded.notification_type`,
        muted_until: sql`excluded.muted_until`,
        pinned_at: sql`excluded.pinned_at`,
        marked_unread_at: sql`excluded.marked_unread_at`,
        conversation_id: sql`excluded.conversation_id`,
      },
      // Update conversation if the version is less than the excluded version
      setWhere: sql`${conversation_properties.version} < excluded.version OR ${conversation_properties.version} = ${LOCAL_INITIAL_VERSION}`,
    });
};

export const updateConversation = ({
  id,
  latestMessageId,
  mentioned,
}: {
  id: string;
  latestMessageId?: string;
  mentioned?: boolean;
}) => {
  const newValues: Partial<InsertConversation> = {};
  if (latestMessageId) {
    newValues.latest_message_id = latestMessageId;
  }

  if (mentioned !== undefined) {
    newValues.mentioned = mentioned;
  }

  if (Object.keys(newValues).length === 0) {
    return;
  }

  return db.instance
    .update(conversations)
    .set(newValues)
    .where(eq(conversations.id, id));
};

export const getAllConversationsFromDB = async (userId: number) => {
  const result = await db.instance.transaction(async tx => {
    const tsOrCreated = sql`coalesce((
      select ${messages.ts}
      from ${messages}
      where ${messages.id} = ${conversations.latest_message_id}), 
    ${conversations.id})`.as('ts_or_created');

    const firstPage = await tx.query.conversations.findMany({
      where: and(
        isNotNull(conversations.latest_message_id),
        eq(conversations.is_active, true),
      ),
      extras: {
        tsOrCreated,
      },
      with: {
        messages: {
          limit: CONVERSATION_MESSAGES_LIMIT,
          orderBy: desc(messages.ts),
          with: {
            messagesToAssets: {with: {asset: true}},
            reactions: true,
          },
        },
        properties: true,
        user: true,
        groupMemberships: {
          where: eq(group_memberships.user_id, userId),
        },
      },
      orderBy: desc(tsOrCreated),
      limit: PRIORITY_CONVERSATION_COUNT,
    });

    const lastFromFirstPage = firstPage.at(-1);
    if (!lastFromFirstPage) {
      return firstPage;
    }

    const rest = await tx.query.conversations.findMany({
      where: and(
        isNotNull(conversations.latest_message_id),
        eq(conversations.is_active, true),
      ),
      extras: {
        tsOrCreated,
      },
      with: {
        messages: {
          limit: 1,
          orderBy: desc(messages.ts),
          with: {
            messagesToAssets: {with: {asset: true}},
            reactions: true,
          },
        },
        properties: true,
        user: true,
        groupMemberships: {
          where: eq(group_memberships.user_id, userId),
        },
      },
      orderBy: desc(tsOrCreated),
    });

    return [...firstPage, ...rest.slice(PRIORITY_CONVERSATION_COUNT)];
  });

  const conversationsWithAssets = result.map(conversation => {
    const latestMessage = conversation.messages[0];
    return {
      ...conversation,
      messages: conversation.messages.map(flattenMessageAssets),
      latest_message: latestMessage
        ? flattenMessageAssets(latestMessage)
        : null,
    };
  });

  return conversationsWithAssets.sort((a, b) => {
    const aTs = a.latest_message?.ts || a.id;
    const bTs = b.latest_message?.ts || b.id;
    return bTs.localeCompare(aTs);
  });
};

export const deleteConversationCascade = async (conversationId: string) => {
  return await db.instance.transaction(async tx => {
    // 1. Get all message IDs for the conversation
    const messageData = await tx
      .select({messageId: messages.id})
      .from(messages)
      .where(eq(messages.conversation_id, conversationId));

    const messageIds = messageData.map(row => row.messageId);

    if (messageIds.length > 0) {
      // 2. Delete reactions for all messages in batch
      await tx
        .delete(reactions)
        .where(inArray(reactions.message_id, messageIds));

      // 3. Get all assetsIds that are associated to the messages from the conversation
      const assetsFromMessages = (
        await tx
          .select({
            assetId: messagesToAssets.asset_id,
          })
          .from(messagesToAssets)
          .where(inArray(messagesToAssets.message_id, messageIds))
      ).map(row => row.assetId);
      // 4. Get all assetsIds that we can delete because they do not belong to other conversations
      const assetsToDelete = (
        await tx
          .select({
            assetId: messagesToAssets.asset_id,
          })
          .from(messagesToAssets)
          .where(inArray(messagesToAssets.asset_id, assetsFromMessages))
          .groupBy(messagesToAssets.asset_id)
          .having(sql`count(*) = 1`)
      ).map(row => row.assetId);
      // 5. Delete asset relations before deleting orphan assets
      await tx
        .delete(messagesToAssets)
        .where(inArray(messagesToAssets.message_id, messageIds));
      await tx.delete(assets).where(inArray(assets.id, assetsToDelete));
      for (const assetId of assetsToDelete) {
        fileHandler.clearAsset(assetId);
      }
    }

    // 6. Delete messages in batch
    await tx
      .delete(messages)
      .where(eq(messages.conversation_id, conversationId));

    // 7. Delete group memberships in batch
    await tx
      .delete(group_memberships)
      .where(eq(group_memberships.conversation_id, conversationId));

    // 8. Delete the conversation properties
    await tx
      .delete(conversation_properties)
      .where(eq(conversation_properties.conversation_id, conversationId));

    // 9. Delete the conversation
    await tx.delete(conversations).where(eq(conversations.id, conversationId));
  });
};

export const getPinnedConversationsCount = async () => {
  const result = await db.instance
    .select({count: count()})
    .from(conversation_properties)
    .where(isNotNull(conversation_properties.pinned_at));

  return result[0].count;
};
