import {
  documentTemplateCategories,
  documentTemplateFolders,
  documentFolderMappings,
  documentTemplates,
  templateVariables,
  organizationAssets,
  documentGenerations,
  documentPackages,
  documentPackageItems,
  documentSignatures,
  type DocumentTemplateCategory,
  type InsertDocumentTemplateCategory,
  type DocumentTemplateFolder,
  type InsertDocumentTemplateFolder,
  type DocumentFolderMapping,
  type InsertDocumentFolderMapping,
  type DocumentTemplate,
  type InsertDocumentTemplate,
  type TemplateVariable,
  type InsertTemplateVariable,
  type OrganizationAsset,
  type InsertOrganizationAsset,
  type DocumentGeneration,
  type InsertDocumentGeneration,
  type DocumentPackage,
  type InsertDocumentPackage,
  type DocumentPackageItem,
  type InsertDocumentPackageItem,
  type DocumentSignature,
  type InsertDocumentSignature,
} from "@shared/schema";
import { db } from "../db";
import { eq, and, desc, asc, sql, isNull } from "drizzle-orm";

// ── Document Template Categories (legacy) ────────────────────────────────────

export async function listDocumentTemplateCategories(organizationId: number): Promise<DocumentTemplateCategory[]> {
  return db
    .select()
    .from(documentTemplateCategories)
    .where(eq(documentTemplateCategories.organizationId, organizationId))
    .orderBy(asc(documentTemplateCategories.sortOrder), asc(documentTemplateCategories.name));
}

export async function getDocumentTemplateCategory(id: number, organizationId: number): Promise<DocumentTemplateCategory | undefined> {
  const [category] = await db
    .select()
    .from(documentTemplateCategories)
    .where(and(eq(documentTemplateCategories.id, id), eq(documentTemplateCategories.organizationId, organizationId)));
  return category;
}

export async function createDocumentTemplateCategory(data: InsertDocumentTemplateCategory): Promise<DocumentTemplateCategory> {
  const [category] = await db.insert(documentTemplateCategories).values(data).returning();
  return category;
}

export async function updateDocumentTemplateCategory(
  id: number,
  organizationId: number,
  updates: Partial<DocumentTemplateCategory>,
): Promise<DocumentTemplateCategory | undefined> {
  const [category] = await db
    .update(documentTemplateCategories)
    .set(updates)
    .where(and(eq(documentTemplateCategories.id, id), eq(documentTemplateCategories.organizationId, organizationId)))
    .returning();
  return category;
}

export async function deleteDocumentTemplateCategory(id: number, organizationId: number): Promise<void> {
  await db
    .delete(documentTemplateCategories)
    .where(and(eq(documentTemplateCategories.id, id), eq(documentTemplateCategories.organizationId, organizationId)));
}

// ── Document Template Folders ─────────────────────────────────────────────────

export async function listDocumentTemplateFolders(organizationId: number): Promise<DocumentTemplateFolder[]> {
  return db
    .select()
    .from(documentTemplateFolders)
    .where(eq(documentTemplateFolders.organizationId, organizationId))
    .orderBy(asc(documentTemplateFolders.position), asc(documentTemplateFolders.name));
}

export async function getDocumentTemplateFolder(id: number, organizationId: number): Promise<DocumentTemplateFolder | undefined> {
  const [folder] = await db
    .select()
    .from(documentTemplateFolders)
    .where(and(eq(documentTemplateFolders.id, id), eq(documentTemplateFolders.organizationId, organizationId)));
  return folder;
}

export async function createDocumentTemplateFolder(data: InsertDocumentTemplateFolder): Promise<DocumentTemplateFolder> {
  const [folder] = await db.insert(documentTemplateFolders).values(data).returning();
  return folder;
}

export async function updateDocumentTemplateFolder(
  id: number,
  organizationId: number,
  updates: Partial<DocumentTemplateFolder>,
): Promise<DocumentTemplateFolder | undefined> {
  const [folder] = await db
    .update(documentTemplateFolders)
    .set({ ...updates, updatedAt: new Date() })
    .where(and(eq(documentTemplateFolders.id, id), eq(documentTemplateFolders.organizationId, organizationId)))
    .returning();
  return folder;
}

export async function deleteDocumentTemplateFolder(id: number, organizationId: number): Promise<void> {
  await db
    .delete(documentTemplateFolders)
    .where(and(eq(documentTemplateFolders.id, id), eq(documentTemplateFolders.organizationId, organizationId)));
}

// ── Document Folder Mappings ──────────────────────────────────────────────────

export async function listDocumentFolderMappings(folderId: number): Promise<DocumentFolderMapping[]> {
  return db
    .select()
    .from(documentFolderMappings)
    .where(eq(documentFolderMappings.folderId, folderId))
    .orderBy(asc(documentFolderMappings.sortOrder), asc(documentFolderMappings.id));
}

export async function getDocumentFolderMapping(id: number, folderId: number): Promise<DocumentFolderMapping | undefined> {
  const [mapping] = await db
    .select()
    .from(documentFolderMappings)
    .where(and(eq(documentFolderMappings.id, id), eq(documentFolderMappings.folderId, folderId)));
  return mapping;
}

export async function createDocumentFolderMapping(data: InsertDocumentFolderMapping): Promise<DocumentFolderMapping> {
  const [mapping] = await db.insert(documentFolderMappings).values(data).returning();
  return mapping;
}

export async function updateDocumentFolderMapping(
  id: number,
  folderId: number,
  updates: Partial<DocumentFolderMapping>,
): Promise<DocumentFolderMapping | undefined> {
  const [mapping] = await db
    .update(documentFolderMappings)
    .set(updates)
    .where(and(eq(documentFolderMappings.id, id), eq(documentFolderMappings.folderId, folderId)))
    .returning();
  return mapping;
}

export async function deleteDocumentFolderMapping(id: number, folderId: number): Promise<void> {
  await db
    .delete(documentFolderMappings)
    .where(and(eq(documentFolderMappings.id, id), eq(documentFolderMappings.folderId, folderId)));
}

export async function deleteDocumentFolderMappingsByFolder(folderId: number): Promise<void> {
  await db.delete(documentFolderMappings).where(eq(documentFolderMappings.folderId, folderId));
}

// ── Document Templates ───────────────────────────────────────────────────────

export interface TemplateListFilters {
  organizationId: number;
  status?: string;
  categoryId?: number;
  folderId?: number;
  formId?: number;
}

export async function listDocumentTemplates(filters: TemplateListFilters): Promise<DocumentTemplate[]> {
  const conditions = [eq(documentTemplates.organizationId, filters.organizationId)];
  if (filters.status) conditions.push(eq(documentTemplates.status, filters.status));
  if (filters.categoryId !== undefined) conditions.push(eq(documentTemplates.categoryId, filters.categoryId));
  if (filters.folderId !== undefined) {
    if (filters.folderId === null) {
      conditions.push(isNull(documentTemplates.folderId));
    } else {
      conditions.push(eq(documentTemplates.folderId, filters.folderId));
    }
  }
  if (filters.formId !== undefined) conditions.push(eq(documentTemplates.formId, filters.formId));

  return db
    .select()
    .from(documentTemplates)
    .where(and(...conditions))
    .orderBy(desc(documentTemplates.updatedAt));
}

export async function getDocumentTemplate(id: number, organizationId: number): Promise<DocumentTemplate | undefined> {
  const [template] = await db
    .select()
    .from(documentTemplates)
    .where(and(eq(documentTemplates.id, id), eq(documentTemplates.organizationId, organizationId)));
  return template;
}

export async function createDocumentTemplate(data: InsertDocumentTemplate): Promise<DocumentTemplate> {
  const [template] = await db.insert(documentTemplates).values(data).returning();
  return template;
}

export async function updateDocumentTemplate(
  id: number,
  organizationId: number,
  updates: Partial<DocumentTemplate>,
): Promise<DocumentTemplate | undefined> {
  const [template] = await db
    .update(documentTemplates)
    .set({ ...updates, updatedAt: new Date() })
    .where(and(eq(documentTemplates.id, id), eq(documentTemplates.organizationId, organizationId)))
    .returning();
  return template;
}

export async function deleteDocumentTemplate(id: number, organizationId: number): Promise<void> {
  await db
    .delete(documentTemplates)
    .where(and(eq(documentTemplates.id, id), eq(documentTemplates.organizationId, organizationId)));
}

// ── Template Variables ───────────────────────────────────────────────────────

export async function listTemplateVariables(templateId: number): Promise<TemplateVariable[]> {
  return db
    .select()
    .from(templateVariables)
    .where(eq(templateVariables.templateId, templateId))
    .orderBy(asc(templateVariables.sortOrder), asc(templateVariables.id));
}

export async function getTemplateVariable(id: number, templateId: number): Promise<TemplateVariable | undefined> {
  const [variable] = await db
    .select()
    .from(templateVariables)
    .where(and(eq(templateVariables.id, id), eq(templateVariables.templateId, templateId)));
  return variable;
}

export async function createTemplateVariable(data: InsertTemplateVariable): Promise<TemplateVariable> {
  const [variable] = await db.insert(templateVariables).values(data).returning();
  return variable;
}

export async function updateTemplateVariable(
  id: number,
  templateId: number,
  updates: Partial<TemplateVariable>,
): Promise<TemplateVariable | undefined> {
  const [variable] = await db
    .update(templateVariables)
    .set(updates)
    .where(and(eq(templateVariables.id, id), eq(templateVariables.templateId, templateId)))
    .returning();
  return variable;
}

export async function deleteTemplateVariable(id: number, templateId: number): Promise<void> {
  await db
    .delete(templateVariables)
    .where(and(eq(templateVariables.id, id), eq(templateVariables.templateId, templateId)));
}

export async function deleteTemplateVariablesByTemplate(templateId: number): Promise<void> {
  await db.delete(templateVariables).where(eq(templateVariables.templateId, templateId));
}

// ── Organization Assets ──────────────────────────────────────────────────────

export interface AssetListFilters {
  organizationId: number;
  type?: string;
  isActive?: boolean;
}

export async function listOrganizationAssets(filters: AssetListFilters): Promise<OrganizationAsset[]> {
  const conditions = [eq(organizationAssets.organizationId, filters.organizationId)];
  if (filters.type) conditions.push(eq(organizationAssets.type, filters.type));
  if (filters.isActive !== undefined) conditions.push(eq(organizationAssets.isActive, filters.isActive));

  return db
    .select()
    .from(organizationAssets)
    .where(and(...conditions))
    .orderBy(desc(organizationAssets.createdAt));
}

export async function getOrganizationAsset(id: number, organizationId: number): Promise<OrganizationAsset | undefined> {
  const [asset] = await db
    .select()
    .from(organizationAssets)
    .where(and(eq(organizationAssets.id, id), eq(organizationAssets.organizationId, organizationId)));
  return asset;
}

export async function createOrganizationAsset(data: InsertOrganizationAsset): Promise<OrganizationAsset> {
  const [asset] = await db.insert(organizationAssets).values(data).returning();
  return asset;
}

export async function updateOrganizationAsset(
  id: number,
  organizationId: number,
  updates: Partial<OrganizationAsset>,
): Promise<OrganizationAsset | undefined> {
  const [asset] = await db
    .update(organizationAssets)
    .set(updates)
    .where(and(eq(organizationAssets.id, id), eq(organizationAssets.organizationId, organizationId)))
    .returning();
  return asset;
}

export async function deleteOrganizationAsset(id: number, organizationId: number): Promise<void> {
  await db
    .delete(organizationAssets)
    .where(and(eq(organizationAssets.id, id), eq(organizationAssets.organizationId, organizationId)));
}

export async function findOrganizationAssetByFieldValue(
  organizationId: number,
  type: string,
  formId: number,
  fieldCode: string,
  fieldValue: string,
): Promise<OrganizationAsset | undefined> {
  const [asset] = await db
    .select()
    .from(organizationAssets)
    .where(
      and(
        eq(organizationAssets.organizationId, organizationId),
        eq(organizationAssets.type, type),
        eq(organizationAssets.formId, formId),
        eq(organizationAssets.fieldCode, fieldCode),
        eq(organizationAssets.fieldValue, fieldValue),
        eq(organizationAssets.isActive, true),
      ),
    )
    .orderBy(desc(organizationAssets.createdAt));
  return asset;
}

// ── Document Generations ─────────────────────────────────────────────────────

export interface GenerationListFilters {
  organizationId: number;
  templateId?: number;
  taskId?: number;
  status?: string;
  limit?: number;
  offset?: number;
}

export async function listDocumentGenerations(filters: GenerationListFilters): Promise<DocumentGeneration[]> {
  const conditions = [eq(documentGenerations.organizationId, filters.organizationId)];
  if (filters.templateId !== undefined) conditions.push(eq(documentGenerations.templateId, filters.templateId));
  if (filters.taskId !== undefined) conditions.push(eq(documentGenerations.taskId, filters.taskId));
  if (filters.status) conditions.push(eq(documentGenerations.status, filters.status));

  return db
    .select()
    .from(documentGenerations)
    .where(and(...conditions))
    .orderBy(desc(documentGenerations.createdAt))
    .limit(filters.limit || 100)
    .offset(filters.offset || 0);
}

export async function countDocumentGenerations(filters: Omit<GenerationListFilters, 'limit' | 'offset'>): Promise<number> {
  const conditions = [eq(documentGenerations.organizationId, filters.organizationId)];
  if (filters.templateId !== undefined) conditions.push(eq(documentGenerations.templateId, filters.templateId));
  if (filters.taskId !== undefined) conditions.push(eq(documentGenerations.taskId, filters.taskId));
  if (filters.status) conditions.push(eq(documentGenerations.status, filters.status));

  const [result] = await db
    .select({ total: sql<number>`count(*)::int` })
    .from(documentGenerations)
    .where(and(...conditions));
  return result?.total || 0;
}

export async function getDocumentGeneration(id: number, organizationId: number): Promise<DocumentGeneration | undefined> {
  const [generation] = await db
    .select()
    .from(documentGenerations)
    .where(and(eq(documentGenerations.id, id), eq(documentGenerations.organizationId, organizationId)));
  return generation;
}

export async function createDocumentGeneration(data: InsertDocumentGeneration): Promise<DocumentGeneration> {
  const [generation] = await db.insert(documentGenerations).values(data).returning();
  return generation;
}

export async function updateDocumentGeneration(
  id: number,
  organizationId: number,
  updates: Partial<DocumentGeneration>,
): Promise<DocumentGeneration | undefined> {
  const [generation] = await db
    .update(documentGenerations)
    .set(updates)
    .where(and(eq(documentGenerations.id, id), eq(documentGenerations.organizationId, organizationId)))
    .returning();
  return generation;
}

export async function deleteDocumentGeneration(id: number, organizationId: number): Promise<void> {
  await db
    .delete(documentGenerations)
    .where(and(eq(documentGenerations.id, id), eq(documentGenerations.organizationId, organizationId)));
}

// ── Document Packages ────────────────────────────────────────────────────────

export async function listDocumentPackages(organizationId: number): Promise<DocumentPackage[]> {
  return db
    .select()
    .from(documentPackages)
    .where(eq(documentPackages.organizationId, organizationId))
    .orderBy(desc(documentPackages.createdAt));
}

export async function getDocumentPackage(id: number, organizationId: number): Promise<DocumentPackage | undefined> {
  const [pkg] = await db
    .select()
    .from(documentPackages)
    .where(and(eq(documentPackages.id, id), eq(documentPackages.organizationId, organizationId)));
  return pkg;
}

export async function createDocumentPackage(data: InsertDocumentPackage): Promise<DocumentPackage> {
  const [pkg] = await db.insert(documentPackages).values(data).returning();
  return pkg;
}

export async function updateDocumentPackage(
  id: number,
  organizationId: number,
  updates: Partial<DocumentPackage>,
): Promise<DocumentPackage | undefined> {
  const [pkg] = await db
    .update(documentPackages)
    .set(updates)
    .where(and(eq(documentPackages.id, id), eq(documentPackages.organizationId, organizationId)))
    .returning();
  return pkg;
}

export async function deleteDocumentPackage(id: number, organizationId: number): Promise<void> {
  await db
    .delete(documentPackages)
    .where(and(eq(documentPackages.id, id), eq(documentPackages.organizationId, organizationId)));
}

// ── Document Package Items ───────────────────────────────────────────────────

export async function listDocumentPackageItems(packageId: number): Promise<DocumentPackageItem[]> {
  return db
    .select()
    .from(documentPackageItems)
    .where(eq(documentPackageItems.packageId, packageId))
    .orderBy(asc(documentPackageItems.sortOrder), asc(documentPackageItems.id));
}

export async function createDocumentPackageItem(data: InsertDocumentPackageItem): Promise<DocumentPackageItem> {
  const [item] = await db.insert(documentPackageItems).values(data).returning();
  return item;
}

export async function updateDocumentPackageItem(
  id: number,
  packageId: number,
  updates: Partial<DocumentPackageItem>,
): Promise<DocumentPackageItem | undefined> {
  const [item] = await db
    .update(documentPackageItems)
    .set(updates)
    .where(and(eq(documentPackageItems.id, id), eq(documentPackageItems.packageId, packageId)))
    .returning();
  return item;
}

export async function deleteDocumentPackageItem(id: number, packageId: number): Promise<void> {
  await db
    .delete(documentPackageItems)
    .where(and(eq(documentPackageItems.id, id), eq(documentPackageItems.packageId, packageId)));
}

export async function deleteDocumentPackageItemsByPackage(packageId: number): Promise<void> {
  await db.delete(documentPackageItems).where(eq(documentPackageItems.packageId, packageId));
}

// ── Document Signatures ──────────────────────────────────────────────────────

export async function listDocumentSignatures(generationId: number): Promise<DocumentSignature[]> {
  return db
    .select()
    .from(documentSignatures)
    .where(eq(documentSignatures.documentGenerationId, generationId))
    .orderBy(desc(documentSignatures.createdAt));
}

export async function createDocumentSignature(data: InsertDocumentSignature): Promise<DocumentSignature> {
  const [signature] = await db.insert(documentSignatures).values(data).returning();
  return signature;
}

export async function updateDocumentSignature(
  id: number,
  generationId: number,
  updates: Partial<DocumentSignature>,
): Promise<DocumentSignature | undefined> {
  const [signature] = await db
    .update(documentSignatures)
    .set(updates)
    .where(and(eq(documentSignatures.id, id), eq(documentSignatures.documentGenerationId, generationId)))
    .returning();
  return signature;
}