import {db, messages, DBTransaction} from '@db/index';
import {
  eq,
  sql,
  desc,
  lt,
  and,
  isNull,
  or,
  asc,
  gt,
  gte,
  lte,
  ne,
  SQL,
} from 'drizzle-orm';
import {DeliverStatus, MessageStatus} from '@modules/chats/interfaces';
import {
  LOCAL_INITIAL_VERSION,
  MESSAGES_BATCH_SIZE,
} from '@modules/chats/constants';
import {flattenMessageAssets} from '@modules/chats/utils';
import {
  FileMessagesLogger,
  LogStatus,
} from '@modules/chats/instances/FileMessagesLogger';
import {persistedUserId} from '@services/localStorage';
import {asyncMap} from '@shared/utils';

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

// Prevent duplicates in logging
const processedMap = new Set<string>();

const logSavedMessages = (values: InsertMessage[]) => {
  try {
    for (const message of values) {
      if (processedMap.has(message.ts)) {
        continue;
      }
      FileMessagesLogger.logMessageTs(message.ts, LogStatus.SAVED);
      processedMap.add(message.ts);
    }
  } catch (e) {
    // do nothing
  }
  if (processedMap.size > 200) {
    processedMap.clear();
  }
};

export const insertRecalculatedMessages = async (values: InsertMessage[]) => {
  if (!values.length) {
    return;
  }

  return await db.instance
    .insert(messages)
    .values(values)
    .onConflictDoUpdate({
      target: messages.id,
      set: {
        height: sql`excluded.height`,
        associated_font_scale: sql`excluded.associated_font_scale`,
      },
    });
};

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

  const builder = tx ?? db.instance;
  return builder
    .insert(messages)
    .values(values)
    .onConflictDoUpdate({
      target: messages.id,
      set: {
        ts: sql`excluded.ts`,
        text: sql`excluded.text`,
        formatted_text: sql`excluded.formatted_text`,
        sender_name: sql`excluded.sender_name`,
        latest_event_ts: sql`excluded.latest_event_ts`,
        sender_profile_picture: sql`excluded.sender_profile_picture`,
        version: sql`excluded.version`,
        status: sql`excluded.status`,
        read_at: sql`excluded.read_at`,
        received_at: sql`excluded.received_at`,
        height: sql`excluded.height`,
        associated_font_scale: sql`excluded.associated_font_scale`,
        blocks: sql`excluded.blocks`,
        edited: sql`excluded.edited`,
        hidden: sql`excluded.hidden`,
        highlighted_spans: sql`excluded.highlighted_spans`,
        deleted_from_ui: sql`excluded.deleted_from_ui`,
      },
      // Update message if the version is a local version (-1), the incoming version is newer,
      // or when deliver status fields are being set for the first time.
      setWhere: sql`
        messages.version = ${LOCAL_INITIAL_VERSION}
        OR ${messages.version} < excluded.version
        OR (excluded.read_at IS NOT NULL AND ${isNull(messages.read_at)})
        OR (excluded.received_at IS NOT NULL AND ${isNull(
          messages.received_at,
        )})
      `,
    })
    .then(res => {
      // if duplicate (update) - rowsAffected is 0
      if (res.rowsAffected) {
        logSavedMessages(values);
      }
      return res;
    });
};

export const updateDeliverStatusInternal = async ({
  fromMessageTs,
  toMessageTs,
  channelId,
  deliverStatus,
}: {
  fromMessageTs: string;
  toMessageTs: string;
  channelId: string;
  deliverStatus: DeliverStatus;
}) => {
  await db.instance
    .update(messages)
    .set({
      read_at: deliverStatus.read_at,
      received_at: deliverStatus.received_at,
    })
    .where(
      and(
        eq(messages.conversation_id, channelId),
        gte(messages.ts, fromMessageTs),
        lte(messages.ts, toMessageTs),
        eq(messages.user_id, persistedUserId!),
      ),
    );
};

export const updateMessageStatus = async ({
  id,
  status,
}: {
  id: string;
  status?: string;
}) => {
  return await db.instance
    .update(messages)
    .set({status})
    .where(eq(messages.id, id));
};

export const updateMessagesStatusBatch = async (
  updates: {
    id: string;
    status?: string;
  }[],
) => {
  if (!updates.length) return;

  await db.instance.transaction(async tx => {
    await asyncMap(updates, async ({id, status}) => {
      await tx.update(messages).set({status}).where(eq(messages.id, id));
    });
  });
};

export const getPendingMessagesDB = async () => {
  const result = await db.instance.query.messages.findMany({
    where: and(
      eq(messages.status, MessageStatus.Sending),
      eq(messages.version, LOCAL_INITIAL_VERSION),
    ),
    orderBy: [asc(messages.ts)],
    with: {
      messagesToAssets: {with: {asset: true}},
      reactions: true,
    },
  });

  return result.map(flattenMessageAssets);
};

export const getLatestServerMessageTs = async (
  channel: string,
): Promise<string | undefined> => {
  const result = await db.instance
    .select({ts: messages.ts})
    .from(messages)
    .where(
      and(
        eq(messages.conversation_id, channel),
        ne(messages.version, LOCAL_INITIAL_VERSION),
      ),
    )
    .orderBy(desc(messages.ts))
    .limit(1);

  return result[0]?.ts;
};

export const getLatestMessageForGroupMsg = async (groupId: string) => {
  const result = await db.instance
    .select()
    .from(messages)
    .orderBy(desc(messages.ts))
    .where(eq(messages.conversation_id, groupId))
    .limit(1);

  return result?.[0];
};

export const getLatestMessagesForGroupMsg = async (
  groupId: string,
  limit = 100,
) => {
  const latestMessages = await db.instance.query.messages.findMany({
    where: eq(messages.conversation_id, groupId),
    orderBy: desc(messages.ts),
    limit: limit,
    with: {
      messagesToAssets: {with: {asset: true}},

      reactions: true,
    },
  });
  latestMessages.reverse();

  return latestMessages.map(flattenMessageAssets);
};

export const getFirstMessagesForGroupMsg = async (groupId: string) => {
  const messagesAbove = await db.instance.query.messages.findMany({
    where: eq(messages.conversation_id, groupId),
    with: {
      messagesToAssets: {with: {asset: true}},
      reactions: true,
    },
    orderBy: desc(messages.ts),
    limit: MESSAGES_BATCH_SIZE,
  });
  messagesAbove.reverse();
  return messagesAbove.map(flattenMessageAssets);
};

export const getPositionsOfLastRead = async (
  params: {groupId: string; ts: string}[],
): Promise<{groupId: string; index: number}[]> => {
  const values = params
    .map(({groupId, ts}) => `SELECT '${groupId}' as group_id, '${ts}' as ts`)
    .join(' UNION ');

  const query = `
  WITH input(group_id, ts) AS (${values})
  SELECT m.conversation_id, MAX(m.position) as position
  FROM messages m
  JOIN input i ON i.group_id = m.conversation_id
  WHERE m.ts <= i.ts
    AND (m.status IS NULL OR m.status = 'SEND')
  GROUP BY m.conversation_id
`;

  try {
    const res = await db.withOpClient(client => client.executeRaw(query));

    return (
      res?.map(el => ({
        groupId: el[0],
        index: el[1],
      })) || []
    );
  } catch (e) {
    return [];
  }
};

export const getMessageById = async (
  id: string,
  {withReactions = false}: {withReactions?: boolean} = {},
) => {
  const message = await db.instance.query.messages.findFirst({
    where: eq(messages.id, id),
    with: {
      messagesToAssets: {with: {asset: true}},
      reactions: withReactions ? true : undefined,
    },
  });
  return message ? flattenMessageAssets(message) : null;
};

export const getMessageByTs = async (ts: string) => {
  return await db.instance.query.messages.findFirst({
    where: eq(messages.ts, ts),
  });
};

export const getMessageByTsWithAssets = async (ts: string) => {
  const message = await db.instance.query.messages.findFirst({
    where: eq(messages.ts, ts),
    with: {
      messagesToAssets: {with: {asset: true}},
    },
  });
  return message ? flattenMessageAssets(message) : null;
};

// ============================================
// TS-based queries (for new pagination system)
// ============================================

/**
 * Get messages above (older than) a given timestamp
 * @param minTs - Optional minimum timestamp to limit the query (don't fetch messages older than this)
 */
export const getMessagesAboveByTs = async ({
  conversationId,
  ts,
  limit = MESSAGES_BATCH_SIZE,
  ignoreSending,
  minTs,
}: {
  conversationId: string;
  ts: string;
  limit?: number;
  ignoreSending?: boolean;
  minTs?: string;
}) => {
  const conditions: SQL<unknown>[] = [
    eq(messages.conversation_id, conversationId),
    lt(messages.ts, ts),
  ];

  // Limit to range boundary if provided
  if (minTs) {
    conditions.push(gte(messages.ts, minTs));
  }

  if (ignoreSending) {
    conditions.push(
      or(
        isNull(messages.status),
        ne(messages.status, MessageStatus.Sending),
      ) as SQL<unknown>,
    );
  }

  const result = await db.instance.query.messages.findMany({
    where: and(...conditions),
    with: {
      messagesToAssets: {with: {asset: true}},
      reactions: true,
    },
    orderBy: desc(messages.ts),
    limit,
  });

  // Reverse to get ascending order (oldest first)
  result.reverse();
  return result.map(flattenMessageAssets);
};

/**
 * Get messages below (newer than) a given timestamp
 * @param maxTs - Optional maximum timestamp to limit the query (don't fetch messages newer than this)
 */
export const getMessagesBelowByTs = async ({
  conversationId,
  ts,
  limit = MESSAGES_BATCH_SIZE,
  ignoreSending,
  maxTs,
}: {
  conversationId: string;
  ts: string;
  limit?: number;
  ignoreSending?: boolean;
  maxTs?: string;
}) => {
  const conditions: SQL<unknown>[] = [
    eq(messages.conversation_id, conversationId),
    gt(messages.ts, ts),
  ];

  // Limit to range boundary if provided
  if (maxTs) {
    conditions.push(lte(messages.ts, maxTs));
  }

  if (ignoreSending) {
    conditions.push(
      or(
        isNull(messages.status),
        ne(messages.status, MessageStatus.Sending),
      ) as SQL<unknown>,
    );
  }

  const result = await db.instance.query.messages.findMany({
    where: and(...conditions),
    with: {
      messagesToAssets: {with: {asset: true}},
      reactions: true,
    },
    orderBy: asc(messages.ts),
    limit,
  });

  return result.map(flattenMessageAssets);
};

/**
 * Get the latest messages for a conversation (ordered by ts)
 */
export const getLatestMessagesByTs = async (
  conversationId: string,
  limit = MESSAGES_BATCH_SIZE,
) => {
  const result = await db.instance.query.messages.findMany({
    where: eq(messages.conversation_id, conversationId),
    orderBy: desc(messages.ts),
    limit,
    with: {
      messagesToAssets: {with: {asset: true}},
      reactions: true,
    },
  });

  // Reverse to get ascending order (oldest first)
  result.reverse();
  return result.map(flattenMessageAssets);
};

/**
 * Get messages around a specific timestamp (for jumping to replies)
 * @param minTs - Optional minimum ts to limit results (for range boundary detection)
 * @param maxTs - Optional maximum ts to limit results (for range boundary detection)
 */
export const getMessagesAroundTs = async ({
  conversationId,
  ts,
  limitBefore = Math.floor(MESSAGES_BATCH_SIZE / 2),
  limitAfter = Math.floor(MESSAGES_BATCH_SIZE / 2),
  minTs,
  maxTs,
}: {
  conversationId: string;
  ts: string;
  limitBefore?: number;
  limitAfter?: number;
  minTs?: string;
  maxTs?: string;
}) => {
  // Build where conditions for before query
  const beforeConditions = [
    eq(messages.conversation_id, conversationId),
    lte(messages.ts, ts),
  ];
  if (minTs) {
    beforeConditions.push(gte(messages.ts, minTs));
  }

  // Get messages before (including the target)
  const messagesBefore = await db.instance.query.messages.findMany({
    where: and(...beforeConditions),
    with: {
      messagesToAssets: {with: {asset: true}},
      reactions: true,
    },
    orderBy: desc(messages.ts),
    limit: limitBefore + 1, // +1 to include the target message
  });

  // Build where conditions for after query
  const afterConditions = [
    eq(messages.conversation_id, conversationId),
    gt(messages.ts, ts),
  ];
  if (maxTs) {
    afterConditions.push(lte(messages.ts, maxTs));
  }

  // Get messages after
  const messagesAfter = await db.instance.query.messages.findMany({
    where: and(...afterConditions),
    with: {
      messagesToAssets: {with: {asset: true}},
      reactions: true,
    },
    orderBy: asc(messages.ts),
    limit: limitAfter,
  });

  // Combine and sort
  const combined = [...messagesBefore.reverse(), ...messagesAfter];
  return combined.map(flattenMessageAssets);
};

/**
 * Check if a message with given ts exists in the database
 */
export const messageExistsByTs = async (
  conversationId: string,
  ts: string,
): Promise<boolean> => {
  const result = await db.instance
    .select({count: sql<number>`count(*)`})
    .from(messages)
    .where(
      and(eq(messages.conversation_id, conversationId), eq(messages.ts, ts)),
    );

  return (result[0]?.count ?? 0) > 0;
};

/**
 * Get the oldest message ts for a conversation
 */
export const getOldestMessageTs = async (
  conversationId: string,
): Promise<string | null> => {
  const result = await db.instance
    .select({ts: messages.ts})
    .from(messages)
    .where(eq(messages.conversation_id, conversationId))
    .orderBy(asc(messages.ts))
    .limit(1);

  return result[0]?.ts ?? null;
};

/**
 * Get the newest message ts for a conversation
 */
export const getNewestMessageTs = async (
  conversationId: string,
): Promise<string | null> => {
  const result = await db.instance
    .select({ts: messages.ts})
    .from(messages)
    .where(eq(messages.conversation_id, conversationId))
    .orderBy(desc(messages.ts))
    .limit(1);

  return result[0]?.ts ?? null;
};

export const updateMessageHeightInDb = async (
  id: string,
  height: number,
): Promise<void> => {
  if (height > 0) {
    await db.instance.update(messages).set({height}).where(eq(messages.id, id));
  }
};

export const getAllMessagesTsInChat = async (conversationId: string) => {
  const result = await db.instance
    .select({ts: messages.ts})
    .from(messages)
    .where(eq(messages.conversation_id, conversationId));

  return result.map(r => r.ts);
};
