import { forums, posts, boards, modActions } from "@atbb/db";
import type { Database } from "@atbb/db";
import type { Logger } from "@atbb/logger";
import { eq, and, inArray, desc, count, max } from "drizzle-orm";
import { parseAtUri } from "../../lib/at-uri.js";
import type { PostRow } from "./serialize.js";

/**
 * Look up forum by AT-URI.
 * Returns null if forum doesn't exist.
 *
 * @param db Database instance
 * @param uri AT-URI like "at://did:plc:abc/space.atbb.forum.forum/self"
 */
export async function getForumByUri(
  db: Database,
  uri: string
): Promise<{ did: string; rkey: string; cid: string } | null> {
  const parsed = parseAtUri(uri);
  if (!parsed) {
    return null;
  }

  const { did, rkey } = parsed;

  const [forum] = await db
    .select({
      did: forums.did,
      rkey: forums.rkey,
      cid: forums.cid,
    })
    .from(forums)
    .where(and(eq(forums.did, did), eq(forums.rkey, rkey)))
    .limit(1);

  return forum ?? null;
}

/**
 * Look up board by AT-URI.
 * Returns null if board doesn't exist.
 *
 * @param db Database instance
 * @param uri AT-URI like "at://did:plc:abc/space.atbb.forum.board/3lbk9board"
 */
export async function getBoardByUri(
  db: Database,
  uri: string
): Promise<{ cid: string } | null> {
  const parsed = parseAtUri(uri);
  if (!parsed) {
    return null;
  }

  const { did, rkey } = parsed;

  const [board] = await db
    .select({
      cid: boards.cid,
    })
    .from(boards)
    .where(and(eq(boards.did, did), eq(boards.rkey, rkey)))
    .limit(1);

  return board ?? null;
}

/**
 * Look up multiple posts by ID in a single query.
 * Excludes deleted posts.
 * Returns a Map for O(1) lookup.
 */
export async function getPostsByIds(
  db: Database,
  ids: bigint[]
): Promise<Map<bigint, PostRow>> {
  if (ids.length === 0) {
    return new Map();
  }

  const results = await db
    .select()
    .from(posts)
    .where(and(inArray(posts.id, ids), eq(posts.bannedByMod, false)));

  return new Map(results.map((post) => [post.id, post]));
}

/**
 * Query active bans for a list of user DIDs.
 * A user is banned if their most recent modAction is "ban" (not "unban").
 *
 * @param db Database instance
 * @param dids Array of user DIDs to check
 * @returns Set of banned DIDs (subset of input)
 */
export async function getActiveBans(
  db: Database,
  dids: string[],
  logger?: Logger
): Promise<Set<string>> {
  if (dids.length === 0) {
    return new Set();
  }

  try {
    // Query ban/unban actions for these DIDs only (not other action types like mute)
    // We need the most recent ban/unban action per DID to determine current state
    const actions = await db
      .select({
        subjectDid: modActions.subjectDid,
        action: modActions.action,
        createdAt: modActions.createdAt,
      })
      .from(modActions)
      .where(
        and(
          inArray(modActions.subjectDid, dids),
          inArray(modActions.action, [
            "space.atbb.modAction.ban",
            "space.atbb.modAction.unban",
          ])
        )
      )
      .orderBy(desc(modActions.createdAt))
      .limit(dids.length * 100); // Defensive limit: at most 100 actions per user

    // Group by subjectDid and take most recent ban/unban action
    const mostRecentByDid = new Map<string, string>();
    for (const row of actions) {
      if (row.subjectDid && !mostRecentByDid.has(row.subjectDid)) {
        mostRecentByDid.set(row.subjectDid, row.action);
      }
    }

    // A user is banned if most recent ban/unban action is "ban"
    const banned = new Set<string>();
    for (const [did, action] of mostRecentByDid) {
      if (action === "space.atbb.modAction.ban") {
        banned.add(did);
      }
    }

    return banned;
  } catch (error) {
    logger?.error("Failed to query active bans", {
      operation: "getActiveBans",
      didCount: dids.length,
      error: error instanceof Error ? error.message : String(error),
    });
    throw error; // Let caller decide fail policy
  }
}

/**
 * Query moderation status for a topic (lock/pin).
 *
 * @param db Database instance
 * @param topicId Internal post ID of the topic (root post)
 * @returns { locked: boolean, pinned: boolean }
 */
export async function getTopicModStatus(
  db: Database,
  topicId: bigint,
  logger?: Logger
): Promise<{ locked: boolean; pinned: boolean }> {
  try {
    // Look up the topic to get its AT-URI
    const [topic] = await db
      .select({
        did: posts.did,
        rkey: posts.rkey,
      })
      .from(posts)
      .where(eq(posts.id, topicId))
      .limit(1);

    if (!topic) {
      return { locked: false, pinned: false };
    }

    const topicUri = `at://${topic.did}/space.atbb.post/${topic.rkey}`;

    // Query only lock/unlock/pin/unpin actions for this topic URI
    const actions = await db
      .select({
        action: modActions.action,
        createdAt: modActions.createdAt,
      })
      .from(modActions)
      .where(
        and(
          eq(modActions.subjectPostUri, topicUri),
          inArray(modActions.action, [
            "space.atbb.modAction.lock",
            "space.atbb.modAction.unlock",
            "space.atbb.modAction.pin",
            "space.atbb.modAction.unpin",
          ])
        )
      )
      .orderBy(desc(modActions.createdAt))
      .limit(100);

    if (actions.length === 0) {
      return { locked: false, pinned: false };
    }

    // Lock and pin are independent states - check most recent action for each
    // Find most recent lock/unlock action
    const mostRecentLockAction = actions.find(
      (a) =>
        a.action === "space.atbb.modAction.lock" ||
        a.action === "space.atbb.modAction.unlock"
    );

    // Find most recent pin/unpin action
    const mostRecentPinAction = actions.find(
      (a) =>
        a.action === "space.atbb.modAction.pin" ||
        a.action === "space.atbb.modAction.unpin"
    );

    return {
      locked:
        mostRecentLockAction?.action === "space.atbb.modAction.lock" || false,
      pinned:
        mostRecentPinAction?.action === "space.atbb.modAction.pin" || false,
    };
  } catch (error) {
    logger?.error("Failed to query topic moderation status", {
      operation: "getTopicModStatus",
      topicId: topicId.toString(),
      error: error instanceof Error ? error.message : String(error),
    });
    throw error; // Let caller decide fail policy
  }
}

/**
 * Query reply counts and last-reply timestamps for a list of topic post IDs.
 * Only non-moderated replies (bannedByMod = false) are counted.
 * Returns a Map from topic ID to { replyCount, lastReplyAt }.
 */
export async function getReplyStats(
  db: Database,
  topicIds: bigint[]
): Promise<Map<bigint, { replyCount: number; lastReplyAt: Date | null }>> {
  if (topicIds.length === 0) {
    return new Map();
  }

  const rows = await db
    .select({
      rootPostId: posts.rootPostId,
      replyCount: count(),
      lastReplyAt: max(posts.createdAt),
    })
    .from(posts)
    .where(
      and(
        inArray(posts.rootPostId, topicIds),
        eq(posts.bannedByMod, false)
      )
    )
    .groupBy(posts.rootPostId);

  const result = new Map<bigint, { replyCount: number; lastReplyAt: Date | null }>();
  for (const row of rows) {
    if (row.rootPostId !== null) {
      result.set(row.rootPostId, {
        replyCount: Number(row.replyCount),
        lastReplyAt: row.lastReplyAt ?? null,
      });
    }
  }
  return result;
}

/**
 * Query which posts in a list are currently hidden by moderator action.
 * A post is hidden if its most recent modAction is "delete" (not "undelete").
 *
 * @param db Database instance
 * @param postIds Array of post IDs to check
 * @returns Set of hidden post IDs (subset of input)
 */
export async function getHiddenPosts(
  db: Database,
  postIds: bigint[],
  logger?: Logger
): Promise<Set<bigint>> {
  if (postIds.length === 0) {
    return new Set();
  }

  try {
    // Look up URIs for these post IDs
    const postRecords = await db
      .select({
        id: posts.id,
        did: posts.did,
        rkey: posts.rkey,
      })
      .from(posts)
      .where(inArray(posts.id, postIds))
      .limit(1000); // Prevent memory exhaustion

    if (postRecords.length === 0) {
      return new Set();
    }

    // Build URI->ID mapping
    const uriToId = new Map<string, bigint>();
    const uris: string[] = [];
    for (const post of postRecords) {
      const uri = `at://${post.did}/space.atbb.post/${post.rkey}`;
      uriToId.set(uri, post.id);
      uris.push(uri);
    }

    // Query only delete/undelete actions for these URIs
    const actions = await db
      .select({
        subjectPostUri: modActions.subjectPostUri,
        action: modActions.action,
        createdAt: modActions.createdAt,
      })
      .from(modActions)
      .where(
        and(
          inArray(modActions.subjectPostUri, uris),
          inArray(modActions.action, [
            "space.atbb.modAction.delete",
            "space.atbb.modAction.undelete",
          ])
        )
      )
      .orderBy(desc(modActions.createdAt))
      .limit(uris.length * 10); // At most 10 delete/undelete actions per post

    // Group by URI and take most recent
    const mostRecentByUri = new Map<string, string>();
    for (const row of actions) {
      if (row.subjectPostUri && !mostRecentByUri.has(row.subjectPostUri)) {
        mostRecentByUri.set(row.subjectPostUri, row.action);
      }
    }

    // A post is hidden if most recent delete/undelete action is "delete"
    const hidden = new Set<bigint>();
    for (const [uri, action] of mostRecentByUri) {
      if (action === "space.atbb.modAction.delete") {
        const postId = uriToId.get(uri);
        if (postId !== undefined) {
          hidden.add(postId);
        }
      }
    }

    return hidden;
  } catch (error) {
    logger?.error("Failed to query hidden posts", {
      operation: "getHiddenPosts",
      postIdCount: postIds.length,
      error: error instanceof Error ? error.message : String(error),
    });
    throw error; // Let caller decide fail policy
  }
}