// Task: Fix 500 Error on Create and Enable Hard Delete
// File: src/server/features/reference_books/lib/inventory.core.ts

import { db } from '../../../shared/db/client';
import { inventoryTable, inventoryPendingTable } from '../db/inventory.table';
import { employees } from '../../personnel/db/employees.table';
import { AppError } from '../../../shared/lib/errors';
import { eq, or } from 'drizzle-orm';
import { randomUUID } from 'node:crypto';
import { logger } from '../../../shared/lib/logger';
import type { InventoryInsert } from '../db/inventory.table';
import type { InventoryCreateRequest, InventoryUpdateRequest } from '@shared/contracts/inventory';

type InventoryEntry = typeof inventoryTable.$inferSelect;

/**
 * Extended inventory entry with creator name
 */
type InventoryEntryWithCreator = InventoryEntry & { creatorName: string | null };

/**
 * Execute create inventory entry (Admin only)
 * FIX: Explicitly generate ID to prevent Drizzle $returningId() errors
 * FIX: Fetch creator name to return immediately without requiring reload
 */
export async function executeCreateInventory(data: InventoryCreateRequest, userId: string): Promise<InventoryEntryWithCreator> {
  const newId = randomUUID(); // Generate ID explicitly

  const insertData: InventoryInsert = {
    ...data,
    id: newId,
    createdAt: new Date(),
    updatedAt: new Date(),
    archivedAt: null,
  };

  // Insert without relying on return value
  await db.insert(inventoryTable).values(insertData);

  // Fetch the created record
  const finalEntry = await db.select()
    .from(inventoryTable)
    .where(eq(inventoryTable.id, newId))
    .limit(1);

  if (!finalEntry[0]) {
    throw new AppError('Failed to retrieve created inventory entry', 500, 'INVENTORY_RETRIEVAL_FAILED');
  }

  // Fetch creator name from users table
  let creatorName: string | null = null;
  if (userId && userId !== 'unknown') {
    const user = await db.select({ fullName: employees.fullName })
      .from(employees)
      .where(eq(employees.id, userId))
      .limit(1);
    creatorName = user[0]?.fullName || null;
  }

  logger.info(`[Inventory] Entry created by Admin: ${finalEntry[0].name}`);
  return { ...finalEntry[0], creatorName };
}

/**
 * Execute update inventory entry (Admin only)
 * FIX: Return creatorName: null since inventoryTable doesn't have createdBy field
 */
export async function executeUpdateInventory(id: string, data: InventoryUpdateRequest): Promise<InventoryEntryWithCreator> {
  const updates: any = {
    updatedAt: new Date(),
  };

  if (data.name !== undefined) updates.name = data.name.trim();
  if (data.category !== undefined) updates.category = data.category.trim();
  if (data.quantity !== undefined) updates.quantity = data.quantity.trim();
  if (data.unit !== undefined) updates.unit = data.unit;
  if (data.location !== undefined) updates.location = data.location.trim();
  if (data.comment !== undefined) updates.comment = data.comment?.trim() || null;
  if (data.weightPerUnit !== undefined) updates.weightPerUnit = data.weightPerUnit;
  if (data.manufacturer !== undefined) updates.manufacturer = data.manufacturer?.trim() || null;
  if (data.model !== undefined) updates.model = data.model?.trim() || null;
  if (data.serialNumber !== undefined) updates.serialNumber = data.serialNumber?.trim() || null;
  if (data.orderingLink !== undefined) updates.orderingLink = data.orderingLink?.trim() || null;
  if (data.articleNumber !== undefined) updates.articleNumber = data.articleNumber?.trim() || null;

  await db.update(inventoryTable).set(updates).where(eq(inventoryTable.id, id));

  const updated = await db.select()
    .from(inventoryTable)
    .where(eq(inventoryTable.id, id))
    .limit(1);

  if (!updated[0]) {
    throw new AppError('Inventory entry not found', 404, 'INVENTORY_NOT_FOUND');
  }
  // Return null for creatorName since inventoryTable doesn't have createdBy field
  return { ...updated[0], creatorName: null };
}

/**
 * Execute delete inventory entry (Admin only - HARD DELETE)
 * FIX: Changed from Soft Delete (update archivedAt) to Hard Delete (db.delete)
 */
export async function executeDeleteInventory(id: string): Promise<void> {
  // 1. Delete associated drafts first (Cascade)
  await db.delete(inventoryPendingTable)
    .where(eq(inventoryPendingTable.targetId, id));

  // 2. HARD DELETE original record
  await db.delete(inventoryTable)
    .where(eq(inventoryTable.id, id));

  logger.info(`[Inventory] Entry HARD deleted by Admin: ${id}`);
}

/**
 * Execute approve inventory entry (Admin only)
 * Transfers data from Pending to Active table
 * FIX: Fetch creator name from pending entry before deletion
 */
export async function executeApproveInventory(idOrTargetId: string, reviewerId: string): Promise<InventoryEntryWithCreator> {
  const pendingEntries = await db.select()
    .from(inventoryPendingTable)
    .where(or(
      eq(inventoryPendingTable.id, idOrTargetId),
      eq(inventoryPendingTable.targetId, idOrTargetId)
    ))
    .limit(1);

  if (!pendingEntries[0]) throw new AppError('Pending entry not found', 404, 'INVENTORY_PENDING_NOT_FOUND');
  const pending = pendingEntries[0];

  // Fetch creator name from users table before deleting pending entry
  let creatorName: string | null = null;
  if (pending.createdBy && pending.createdBy !== 'unknown') {
    const user = await db.select({ fullName: employees.fullName })
      .from(employees)
      .where(eq(employees.id, pending.createdBy))
      .limit(1);
    creatorName = user[0]?.fullName || null;
  }

  return await db.transaction(async (tx) => {
    let finalId = pending.targetId;

    if (pending.status === 'to_delete') {
      if (pending.targetId) {
        // HARD DELETE on approval of delete request
        await tx.delete(inventoryTable).where(eq(inventoryTable.id, pending.targetId));
        logger.info(`[Inventory] Delete request approved & executed for: ${pending.targetId}`);
      }
    } else {
      const commonData = {
        name: pending.name,
        category: pending.category,
        quantity: pending.quantity,
        unit: pending.unit,
        location: pending.location,
        comment: pending.comment,
        weightPerUnit: pending.weightPerUnit,
        manufacturer: pending.manufacturer,
        model: pending.model,
        serialNumber: pending.serialNumber,
        orderingLink: pending.orderingLink,
        articleNumber: pending.articleNumber,
        updatedAt: new Date(),
      };

      if (pending.targetId) {
        await tx.update(inventoryTable).set(commonData).where(eq(inventoryTable.id, pending.targetId));
      } else {
        const newId = randomUUID(); // Explicit ID
        await tx.insert(inventoryTable).values({
          ...commonData,
          id: newId,
          createdAt: new Date(),
          archivedAt: null,
        });
        finalId = newId;
      }
    }

    await tx.delete(inventoryPendingTable).where(eq(inventoryPendingTable.id, pending.id));

    // Return dummy if deleted, else return record
    if (pending.status === 'to_delete') return {} as any;

    const result = await tx.select().from(inventoryTable).where(eq(inventoryTable.id, finalId!)).limit(1);
    if (!result[0]) throw new AppError('Failed to retrieve approved entry', 500, 'APPROVE_ERROR');
    return { ...result[0], creatorName };
  });
}

/**
 * Execute reject inventory entry
 */
export async function executeRejectInventory(idOrTargetId: string, reason: string, reviewerId: string): Promise<void> {
  const pendingEntries = await db.select().from(inventoryPendingTable).where(or(eq(inventoryPendingTable.id, idOrTargetId), eq(inventoryPendingTable.targetId, idOrTargetId))).limit(1);
  if (pendingEntries[0]) {
    await db.delete(inventoryPendingTable).where(eq(inventoryPendingTable.id, pendingEntries[0].id));
    logger.info(`[Inventory] Entry rejected and deleted: ${pendingEntries[0].id}`);
  }
}