// src/server/features/notes/db/repository/queries.ts
// Query functions for fetching notes with layout support

import crypto from 'node:crypto';
import { db } from '@serverShared/db/client';
import { notes } from '../notes.table';
import { noteLayouts } from '../note_layouts.table';
import { noteComments } from '../note_comments.table';
import { users } from '@features/personnel/db/employees.table';
import { eq, desc, and, isNull, or, sql, like, inArray } from 'drizzle-orm';
import { dayjs } from '@serverShared/lib/dayjs';
import { loggerService } from '@serverShared/lib/logger';

const notesLogger = loggerService.get('HTTP').child({ module: 'notes-repo-queries' });
const logger = loggerService.get('DB').child({ module: 'notes-repo-queries' });

/**
 * Get the last mention date for a specific user in a note
 * Searches both the note body and comments for <app-mention user-id="UUID"> pattern
 * Returns the maximum of the two dates (or null if no mentions found)
 *
 * @param noteId - The ID of the note to check
 * @param userId - The ID of the user to search for mentions
 * @returns ISO 8601 datetime string or null
 */
export async function getLastMentionDate(noteId: string, userId: string): Promise<string | null> {
  const mentionPattern = `%user-id="${userId}"%`;

  // Search in note body
  const noteResult = await db
    .select({ createdAt: notes.createdAt })
    .from(notes)
    .where(and(eq(notes.id, noteId), like(notes.content, mentionPattern)))
    .limit(1);

  const noteDate = noteResult[0]?.createdAt || null;

  // Search in comments (get the most recent comment with mention)
  const commentResult = await db
    .select({ createdAt: noteComments.createdAt })
    .from(noteComments)
    .where(and(eq(noteComments.noteId, noteId), like(noteComments.content, mentionPattern)))
    .orderBy(desc(noteComments.createdAt))
    .limit(1);

  const commentDate = commentResult[0]?.createdAt || null;

  // Return the maximum of the two dates
  if (!noteDate && !commentDate) {
    return null;
  }

  const maxDate = noteDate && commentDate
    ? (new Date(noteDate) > new Date(commentDate) ? noteDate : commentDate)
    : (noteDate || commentDate);

  return dayjs.utc(maxDate).toISOString();
}

/**
 * Batch query last mention dates for multiple notes
 * Performs a single query to get the most recent comment with mention for each note
 * Note body mentions are handled separately due to different table structure
 *
 * @param noteIds - Array of note IDs to query
 * @param userId - The ID of the user to search for mentions
 * @returns Map of noteId to last mention date (ISO 8601 string)
 */
export async function batchGetLastMentionDates(noteIds: string[], userId: string): Promise<Map<string, string>> {
  if (noteIds.length === 0) {
    return new Map();
  }

  const mentionPattern = `%user-id="${userId}"%`;
  const result = new Map<string, string>();

  // Batch query for comments with mentions
  const commentResults = await db
    .select({
      noteId: noteComments.noteId,
      lastMentionAt: sql<Date>`MAX(${noteComments.createdAt})`.as('lastMentionAt'),
    })
    .from(noteComments)
    .where(and(inArray(noteComments.noteId, noteIds), like(noteComments.content, mentionPattern)))
    .groupBy(noteComments.noteId);

  // Store comment mention dates
  for (const row of commentResults) {
    result.set(row.noteId, dayjs.utc(row.lastMentionAt).toISOString());
  }

  // For notes not found in comments, check note body individually
  // This is acceptable since it's a fallback path and the number of such notes should be small
  for (const noteId of noteIds) {
    if (!result.has(noteId)) {
      const noteResult = await db
        .select({ createdAt: notes.createdAt })
        .from(notes)
        .where(and(eq(notes.id, noteId), like(notes.content, mentionPattern)))
        .limit(1);

      if (noteResult[0]?.createdAt) {
        result.set(noteId, dayjs.utc(noteResult[0].createdAt).toISOString());
      }
    }
  }

  return result;
}

/**
 * Find all notes for a user with personal layout coordinates
 * Returns: user's own notes + all public notes from other users
 */
export async function findWithLayout(userId: string) {
  notesLogger.debug('[DEBUG] findWithLayout: Fetching notes for userId:', userId);

  const query = db
    .select({
      // Note fields
      id: notes.id,
      title: notes.title,
      content: notes.content,
      authorId: notes.authorId,
      authorName: users.fullName,
      userId: notes.userId,
      spaceId: notes.spaceId,
      priority: notes.priority,
      status: notes.status,
      isPublic: notes.isPublic,
      lastCommentAt: notes.lastCommentAt,
      lastCommentAuthorId: notes.lastCommentAuthorId,
      priorityUpdatedAt: notes.priorityUpdatedAt,
      lastPriorityAuthorId: notes.lastPriorityAuthorId,
      createdAt: notes.createdAt,
      updatedAt: notes.updatedAt,
      archivedAt: notes.archivedAt,
      // Layout fields - only from note_layouts (no COALESCE fallback)
      reminderAt: noteLayouts.reminderAt,
      lastViewedAt: noteLayouts.lastViewedAt,
      column: noteLayouts.column,
      positionY: noteLayouts.positionY,
      height: noteLayouts.height,
      // Status change reason from latest status_changed history entry
      statusChangeReason: sql<string>`(
        SELECT JSON_UNQUOTE(COALESCE(
          JSON_EXTRACT(payload, '$.commentText'),
          JSON_EXTRACT(payload, '$.reason')
        ))
        FROM note_history
        WHERE note_id = ${notes.id}
          AND action = 'status_changed'
        ORDER BY created_at DESC
        LIMIT 1
      )`,
    })
    .from(notes)
    .leftJoin(
      noteLayouts,
      and(
        eq(noteLayouts.noteId, notes.id),
        eq(noteLayouts.userId, userId)
      )
    )
    .leftJoin(users, eq(users.id, notes.authorId))
    .where(
      and(
        isNull(notes.archivedAt),
        or(
          eq(notes.userId, userId),
          eq(notes.isPublic, true)
        )
      )
    )
    .orderBy(notes.createdAt);

  const results = await query.execute();

  notesLogger.debug('[DEBUG] findWithLayout: Retrieved', results.length, 'notes from database');

  // Batch query for last mention dates (avoids N+1 problem)
  const noteIds = results.map(note => note.id);
  const mentionDates = await batchGetLastMentionDates(noteIds, userId);

  // Transform to match contract: layout field with all required fields
  // Day.js Protocol: Backend uses dayjs.utc() for all date operations
  return results.map(note => {
    return {
      ...note,
      createdAt: dayjs.utc(note.createdAt).toISOString(),
      updatedAt: dayjs.utc(note.updatedAt).toISOString(),
      archivedAt: note.archivedAt ? dayjs.utc(note.archivedAt).toISOString() : null,
      lastCommentAt: note.lastCommentAt ? dayjs.utc(note.lastCommentAt).toISOString() : null,
      lastCommentAuthorId: note.lastCommentAuthorId || null,
      priorityUpdatedAt: note.priorityUpdatedAt ? dayjs.utc(note.priorityUpdatedAt).toISOString() : null,
      lastPriorityAuthorId: note.lastPriorityAuthorId || null,
      lastMentionAt: mentionDates.get(note.id) || null, // Populated from batch query
      statusChangeReason: note.statusChangeReason || null, // Ensure null instead of undefined
      layout: (note.column !== null && note.positionY !== null && note.height !== null)
        ? {
            id: crypto.randomUUID(),
            noteId: note.id,
            userId: userId,
            column: note.column,
            positionY: note.positionY,
            height: note.height,
            reminderAt: note.reminderAt ? dayjs.utc(note.reminderAt).toISOString() : null,
            lastViewedAt: note.lastViewedAt ? dayjs.utc(note.lastViewedAt).toISOString() : null,
            createdAt: dayjs.utc(note.createdAt).toISOString(),
            updatedAt: dayjs.utc(note.updatedAt).toISOString(),
          }
        : null,
    };
  });
}

/**
 * Find a single note by ID with personal layout coordinates
 */
export async function findOneWithLayout(id: string, userId: string) {
  notesLogger.debug('[DEBUG] findOneWithLayout: Fetching note with id:', id, 'for userId:', userId);

  const query = db
    .select({
      // Note fields
      id: notes.id,
      title: notes.title,
      content: notes.content,
      authorId: notes.authorId,
      authorName: users.fullName,
      userId: notes.userId,
      spaceId: notes.spaceId,
      priority: notes.priority,
      status: notes.status,
      isPublic: notes.isPublic,
      lastCommentAt: notes.lastCommentAt,
      lastCommentAuthorId: notes.lastCommentAuthorId,
      priorityUpdatedAt: notes.priorityUpdatedAt,
      lastPriorityAuthorId: notes.lastPriorityAuthorId,
      createdAt: notes.createdAt,
      updatedAt: notes.updatedAt,
      archivedAt: notes.archivedAt,
      // Layout fields - only from note_layouts (no COALESCE fallback)
      reminderAt: noteLayouts.reminderAt,
      lastViewedAt: noteLayouts.lastViewedAt,
      column: noteLayouts.column,
      positionY: noteLayouts.positionY,
      height: noteLayouts.height,
      // Status change reason from latest status_changed history entry
      statusChangeReason: sql<string>`(
        SELECT JSON_UNQUOTE(COALESCE(
          JSON_EXTRACT(payload, '$.commentText'),
          JSON_EXTRACT(payload, '$.reason')
        ))
        FROM note_history
        WHERE note_id = ${notes.id}
          AND action = 'status_changed'
        ORDER BY created_at DESC
        LIMIT 1
      )`,
    })
    .from(notes)
    .leftJoin(
      noteLayouts,
      and(
        eq(noteLayouts.noteId, notes.id),
        eq(noteLayouts.userId, userId)
      )
    )
    .leftJoin(users, eq(users.id, notes.authorId))
    .where(and(eq(notes.id, id), isNull(notes.archivedAt)))
    .limit(1);

  const result = await query.execute();

  notesLogger.debug('[DEBUG] findOneWithLayout: Retrieved note with status:', result[0]?.status);

  if (!result || result.length === 0) {
    return null;
  }

  const note = result[0];

  // Get last mention date for this note
  const lastMentionAt = await getLastMentionDate(id, userId);

  // Transform to match contract: layout field with all required fields
  // Day.js Protocol: Backend uses dayjs.utc() for all date operations
  return {
    ...note,
    createdAt: dayjs.utc(note.createdAt).toISOString(),
    updatedAt: dayjs.utc(note.updatedAt).toISOString(),
    archivedAt: note.archivedAt ? dayjs.utc(note.archivedAt).toISOString() : null,
    lastCommentAt: note.lastCommentAt ? dayjs.utc(note.lastCommentAt).toISOString() : null,
    lastCommentAuthorId: note.lastCommentAuthorId || null,
    priorityUpdatedAt: note.priorityUpdatedAt ? dayjs.utc(note.priorityUpdatedAt).toISOString() : null,
    lastPriorityAuthorId: note.lastPriorityAuthorId || null,
    lastMentionAt, // Populated from getLastMentionDate
    statusChangeReason: note.statusChangeReason || null, // Ensure null instead of undefined
    layout: (note.column !== null && note.positionY !== null && note.height !== null)
      ? {
          id: crypto.randomUUID(),
          noteId: note.id,
          userId: userId,
          column: note.column,
          positionY: note.positionY,
          height: note.height,
          reminderAt: note.reminderAt ? dayjs.utc(note.reminderAt).toISOString() : null,
          lastViewedAt: note.lastViewedAt ? dayjs.utc(note.lastViewedAt).toISOString() : null,
          createdAt: dayjs.utc(note.createdAt).toISOString(),
          updatedAt: dayjs.utc(note.updatedAt).toISOString(),
        }
      : null,
  };
}