// src/server/features/notes/db/repository/commands.ts
// Command functions for note operations (delete, find by ID)

import { db } from '../../../../shared/db/client';
import { notes } from '../notes.table';
import { noteLayouts } from '../note_layouts.table';
import { noteHistory } from '../note_history.table';
import { noteComments } from '../note_comments.table';
import { eq, and, isNull } from 'drizzle-orm';
import { dayjs } from '../../../../shared/lib/dayjs';
import { loggerService } from '../../../../shared/lib/logger';

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

/**
 * Find a note by ID (without layout)
 */
export async function findById(id: string) {
  const result = await db
    .select()
    .from(notes)
    .where(and(eq(notes.id, id), isNull(notes.archivedAt)))
    .limit(1);

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

  const note = result[0];

  // 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,
  };
}

/**
 * Hard delete a note and all related data in a single transaction
 * Deletes: note, note_comments, note_history, note_layouts
 *
 * @param noteId - The ID of the note to delete
 */
export async function hardDeleteNote(noteId: string): Promise<void> {
  await db.transaction(async (tx) => {
    // Delete from note_layouts (personal layouts for all users)
    await tx
      .delete(noteLayouts)
      .where(eq(noteLayouts.noteId, noteId));
    
    // Delete from note_comments
    await tx
      .delete(noteComments)
      .where(eq(noteComments.noteId, noteId));
    
    // Delete from note_history
    await tx
      .delete(noteHistory)
      .where(eq(noteHistory.noteId, noteId));
    
    // Delete the note itself
    await tx
      .delete(notes)
      .where(eq(notes.id, noteId));
  });
  
  logger.info(`Note ${noteId} hard deleted with cascade cleanup`);
}