import { eq, and } from "drizzle-orm";
import { db } from "../db";
import { formFolders, directoryFolders, forms, dataTables } from "@shared/schema";

// ===================== FORM FOLDERS =====================

export async function getFormFolders(organizationId: number) {
  return await db
    .select()
    .from(formFolders)
    .where(eq(formFolders.organizationId, organizationId))
    .orderBy(formFolders.position, formFolders.id);
}

export async function createFormFolder(data: {
  organizationId: number;
  name: string;
  parentId?: number | null;
  position?: number;
}) {
  const [folder] = await db
    .insert(formFolders)
    .values({
      organizationId: data.organizationId,
      name: data.name,
      parentId: data.parentId ?? null,
      position: data.position ?? 0,
    })
    .returning();
  return folder;
}

export async function updateFormFolder(
  id: number,
  organizationId: number,
  updates: { name?: string; parentId?: number | null; position?: number }
) {
  const [folder] = await db
    .update(formFolders)
    .set({ ...updates, updatedAt: new Date() })
    .where(and(eq(formFolders.id, id), eq(formFolders.organizationId, organizationId)))
    .returning();
  return folder;
}

export async function deleteFormFolder(id: number, organizationId: number) {
  // Move child folders and forms to root
  await db
    .update(formFolders)
    .set({ parentId: null, updatedAt: new Date() })
    .where(and(eq(formFolders.parentId, id), eq(formFolders.organizationId, organizationId)));
  await db
    .update(forms)
    .set({ folderId: null, updatedAt: new Date() })
    .where(and(eq(forms.folderId, id), eq(forms.organizationId, organizationId)));
  await db
    .delete(formFolders)
    .where(and(eq(formFolders.id, id), eq(formFolders.organizationId, organizationId)));
}

export async function moveFormToFolder(formId: number, organizationId: number, folderId: number | null) {
  const [form] = await db
    .update(forms)
    .set({ folderId, updatedAt: new Date() })
    .where(and(eq(forms.id, formId), eq(forms.organizationId, organizationId)))
    .returning();
  return form;
}

export async function moveFormFolder(
  id: number,
  organizationId: number,
  parentId: number | null,
  position: number
) {
  const allFolders = await getFormFolders(organizationId);
  const target = allFolders.find((f) => f.id === id);
  if (!target) return null;

  const siblings = allFolders
    .filter((f) => f.id !== id && (f.parentId ?? null) === (parentId ?? null))
    .sort((a, b) => a.position - b.position);

  const reordered = [
    ...siblings.slice(0, position),
    target,
    ...siblings.slice(position),
  ];

  for (let i = 0; i < reordered.length; i++) {
    await updateFormFolder(reordered[i].id, organizationId, {
      parentId,
      position: i,
    });
  }

  return target;
}

export async function reorderFormFolders(
  organizationId: number,
  updates: Array<{ id: number; parentId: number | null; position: number }>
) {
  if (updates.length === 0) return;
  for (const update of updates) {
    await updateFormFolder(update.id, organizationId, {
      parentId: update.parentId,
      position: update.position,
    });
  }
}

// ===================== DIRECTORY FOLDERS =====================

export async function getDirectoryFolders(organizationId: number) {
  return await db
    .select()
    .from(directoryFolders)
    .where(eq(directoryFolders.organizationId, organizationId))
    .orderBy(directoryFolders.position, directoryFolders.id);
}

export async function createDirectoryFolder(data: {
  organizationId: number;
  name: string;
  parentId?: number | null;
  position?: number;
}) {
  const [folder] = await db
    .insert(directoryFolders)
    .values({
      organizationId: data.organizationId,
      name: data.name,
      parentId: data.parentId ?? null,
      position: data.position ?? 0,
    })
    .returning();
  return folder;
}

export async function updateDirectoryFolder(
  id: number,
  organizationId: number,
  updates: { name?: string; parentId?: number | null; position?: number }
) {
  const [folder] = await db
    .update(directoryFolders)
    .set({ ...updates, updatedAt: new Date() })
    .where(and(eq(directoryFolders.id, id), eq(directoryFolders.organizationId, organizationId)))
    .returning();
  return folder;
}

export async function deleteDirectoryFolder(id: number, organizationId: number) {
  await db
    .update(directoryFolders)
    .set({ parentId: null, updatedAt: new Date() })
    .where(and(eq(directoryFolders.parentId, id), eq(directoryFolders.organizationId, organizationId)));
  await db
    .update(dataTables)
    .set({ folderId: null, updatedAt: new Date() })
    .where(and(eq(dataTables.folderId, id), eq(dataTables.organizationId, organizationId)));
  await db
    .delete(directoryFolders)
    .where(and(eq(directoryFolders.id, id), eq(directoryFolders.organizationId, organizationId)));
}

export async function moveDirectoryToFolder(tableId: number, organizationId: number, folderId: number | null) {
  const [table] = await db
    .update(dataTables)
    .set({ folderId, updatedAt: new Date() })
    .where(and(eq(dataTables.id, tableId), eq(dataTables.organizationId, organizationId)))
    .returning();
  return table;
}

export async function moveDirectoryFolder(
  id: number,
  organizationId: number,
  parentId: number | null,
  position: number
) {
  const allFolders = await getDirectoryFolders(organizationId);
  const target = allFolders.find((f) => f.id === id);
  if (!target) return null;

  const siblings = allFolders
    .filter((f) => f.id !== id && (f.parentId ?? null) === (parentId ?? null))
    .sort((a, b) => a.position - b.position);

  const reordered = [
    ...siblings.slice(0, position),
    target,
    ...siblings.slice(position),
  ];

  for (let i = 0; i < reordered.length; i++) {
    await updateDirectoryFolder(reordered[i].id, organizationId, {
      parentId,
      position: i,
    });
  }

  return target;
}

export async function reorderDirectoryFolders(
  organizationId: number,
  updates: Array<{ id: number; parentId: number | null; position: number }>
) {
  if (updates.length === 0) return;
  for (const update of updates) {
    await updateDirectoryFolder(update.id, organizationId, {
      parentId: update.parentId,
      position: update.position,
    });
  }
}