import {
  getAllRows, getRowById, getRowsWhereEquals, insertRow, updateRow,
  deleteRowsWhereEquals, deleteRowsWhereIn, deleteRowById,
} from '../crud'
import type { Assignment, AssignmentType, Consultant, AssignmentFeedback } from '../../../src/db/types'
import { listMeetingInstancesForAssignment, hardDeleteMeetingInstance } from './meetings'

export const listAssignments = (): Assignment[] => getAllRows<Assignment>('assignments')
export const listAssignmentsNewestFirst = (): Assignment[] =>
  getAllRows<Assignment>('assignments').sort((a, b) => new Date(b.createdAt).getTime() - new Date(a.createdAt).getTime())
export const getAssignment = (id: number): Assignment | undefined => getRowById<Assignment>('assignments', id)
export const addAssignment = (a: Omit<Assignment, 'id'>): number => insertRow('assignments', a)
export const updateAssignment = (id: number, patch: Partial<Assignment>): number => updateRow('assignments', id, patch)

/** Erfüllt für's Löschen nötige Voraussetzung: jedes zugeordnete Institutee hat ein finales Feedback. */
export function isAssignmentFeedbackComplete(assignmentId: number): boolean {
  const a = getRowById<Assignment>('assignments', assignmentId)
  if (!a) return false
  const feedbacks = getRowsWhereEquals<AssignmentFeedback>('assignmentFeedbacks', 'assignmentId', assignmentId)
  return a.instituteeIds.every(iid => feedbacks.some(f => f.instituteeId === iid && f.status === 'final'))
}

export function softDeleteAssignment(id: number): number {
  const a = getRowById<Assignment>('assignments', id)
  if (!a) throw new Error('Assignment nicht gefunden')
  if (a.status !== 'done' || !isAssignmentFeedbackComplete(id)) {
    throw new Error('Löschen nur möglich, wenn das Assignment abgeschlossen ist und für alle Institutees finales Feedback vorliegt')
  }
  return updateRow('assignments', id, { deletedAt: new Date().toISOString() })
}

export const restoreAssignment = (id: number): number =>
  updateRow('assignments', id, { deletedAt: null })

export function archiveAssignment(id: number): number {
  const a = getRowById<Assignment>('assignments', id)
  if (!a) throw new Error('Assignment nicht gefunden')
  if (a.status !== 'done') throw new Error('Nur abgeschlossene Assignments können archiviert werden')
  return updateRow('assignments', id, { archivedAt: new Date().toISOString() })
}

export const unarchiveAssignment = (id: number): number =>
  updateRow('assignments', id, { archivedAt: null })

/** Cascade: meetingInstances (+ deren Kinder) → feedbackDrafts → assignmentFeedbacks (+ deren Kinder) → assignment. */
export function hardDeleteAssignment(id: number): void {
  for (const m of listMeetingInstancesForAssignment(id)) hardDeleteMeetingInstance(m.id!)
  deleteRowsWhereEquals('feedbackDrafts', 'assignmentId', id)
  const feedbackIds = getRowsWhereEquals<AssignmentFeedback>('assignmentFeedbacks', 'assignmentId', id).map(f => f.id!)
  deleteRowsWhereIn('feedbackCategoryRatings', 'assignmentFeedbackId', feedbackIds)
  deleteRowsWhereIn('feedbackDimensionTexts', 'assignmentFeedbackId', feedbackIds)
  deleteRowsWhereIn('feedbackCategoryTexts', 'assignmentFeedbackId', feedbackIds)
  deleteRowsWhereEquals('assignmentFeedbacks', 'assignmentId', id)
  deleteRowById('assignments', id)
}

const TRASH_RETENTION_MS = 30 * 24 * 60 * 60 * 1000

/** Lazy-Sweep statt Cron: läuft bei jedem Dashboard-Load mit, da der Server nicht dauerhaft läuft. */
export function purgeExpiredAssignmentTrash(): void {
  const now = Date.now()
  for (const a of getAllRows<Assignment>('assignments')) {
    if (a.deletedAt && now - new Date(a.deletedAt).getTime() > TRASH_RETENTION_MS) {
      hardDeleteAssignment(a.id!)
    }
  }
}

export interface AssignmentWithDetails extends Assignment {
  typeName: string
  instituteeNames: string[]
  /** Nur für status==='done' relevant: jedes Institutee hat ein finales Feedback — steuert den Löschen-Button in der Übersicht. */
  feedbackComplete: boolean
}

/** Für die Dashboard-Liste: Assignments mit aufgelöstem Typ- und Institutee-Namen, neueste zuerst. */
export function listAssignmentsWithDetails(): AssignmentWithDetails[] {
  purgeExpiredAssignmentTrash()
  const raw = listAssignmentsNewestFirst()
  const types = getAllRows<AssignmentType>('assignmentTypes')
  const consultants = getAllRows<Consultant>('consultants')
  const allFeedbacks = getAllRows<AssignmentFeedback>('assignmentFeedbacks')
  const typeMap = new Map<number, AssignmentType>(types.map(t => [t.id!, t]))
  const consMap = new Map<number, Consultant>(consultants.map(c => [c.id!, c]))
  return raw.map(a => ({
    ...a,
    typeName: typeMap.get(a.assignmentTypeId)?.name ?? '—',
    instituteeNames: a.instituteeIds.map(id => {
      const c = consMap.get(id)
      return c ? `${c.firstName} ${c.lastName}` : '?'
    }),
    feedbackComplete: a.instituteeIds.every(iid =>
      allFeedbacks.some(f => f.assignmentId === a.id && f.instituteeId === iid && f.status === 'final'),
    ),
  }))
}