/**
 * Starter-Paket: nur Konfigurations-/Strukturdaten für Team-Verteilung.
 * Keine Personen-, Assignment- oder Meeting-Daten (GDPR).
 * Produktiv (`AssigmentMonitor`) wird nie angefasst — Skripte nutzen dieselben Pfad-Guards.
 */
import { writeFileSync, readFileSync, mkdirSync, existsSync } from 'node:fs'
import { dirname, join, resolve, sep } from 'node:path'
import { fileURLToPath } from 'node:url'
import { TABLE_DEFINITIONS, TABLE_NAMES } from './tableRegistry'
import { getAllRows, insertRowWithId } from './crud'
import { db } from '../database'

const __dirname = dirname(fileURLToPath(import.meta.url))
export const PROJECT_ROOT = resolve(join(__dirname, '../..'))
export const STARTER_DIR = join(PROJECT_ROOT, 'docs', 'starter')
export const STARTER_FILE = join(STARTER_DIR, 'starter-structure.json')

/** Tabellen mit Stammdaten / Config — IDs bleiben stabil (FK zwischen Struktur-Tabellen). */
export const STARTER_STRUCTURE_TABLES = [
  'feedbackDimensions',
  'feedbackCategories',
  'feedbackCriterionItems',
  'feedbackDimensionPrompts',
  'assignmentTypes',
  'phaseTemplates',
  'checklistItemDefs',
  'appSettings',
  'criterionLevelDescriptions',
] as const

const STRUCTURE_SET = new Set<string>(STARTER_STRUCTURE_TABLES)

export interface StarterPackage {
  kind: 'assignment-monitor-starter'
  version: 1
  exportedAt: string
  note: string
  tables: Record<string, Array<Record<string, unknown>>>
}

/** Verweigert jeden Pfad außerhalb V2 und jeden Produktiv-DB-Pfad. */
export function assertV2DevOnly(resolvedPath: string, label: string): void {
  const pathNorm = resolvedPath.toLowerCase()
  const rootNorm = PROJECT_ROOT.toLowerCase()
  const rootPrefix = rootNorm.endsWith(sep) ? rootNorm : rootNorm + sep

  if (!pathNorm.startsWith(rootPrefix) && pathNorm !== rootNorm) {
    throw new Error(`[${label}] SAFETY: Pfad außerhalb AssignmentMonitorV2:\n  ${resolvedPath}`)
  }
  if (
    /[/\\]assigmentmonitor[/\\]/.test(pathNorm) &&
    !/[/\\]assigmentmonitorv2[/\\]/.test(pathNorm)
  ) {
    throw new Error(`[${label}] SAFETY: Produktiv-Pfad blockiert:\n  ${resolvedPath}`)
  }
}

export function assertStarterEnv(): void {
  const apiPort = process.env.API_PORT?.trim() || '4001'
  const vitePort = process.env.PORT?.trim() || '5174'
  const sqliteFilename = process.env.SQLITE_FILENAME?.trim() || 'assignment-monitor-v2-dev.sqlite3'

  if (apiPort === '4000') {
    throw new Error('[starter] SAFETY: API_PORT=4000 ist der Produktiv-Port — abgebrochen.')
  }
  if (vitePort === '5173') {
    throw new Error('[starter] SAFETY: PORT=5173 ist der Produktiv-Port — abgebrochen.')
  }
  if (sqliteFilename === 'assignment-monitor.sqlite3') {
    throw new Error('[starter] SAFETY: Produktiv-DB-Dateiname blockiert.')
  }
  if (sqliteFilename.includes('/') || sqliteFilename.includes('\\') || sqliteFilename.includes('..')) {
    throw new Error(`[starter] SAFETY: SQLITE_FILENAME ungültig: "${sqliteFilename}"`)
  }
}

function sanitizeStructureRow(tableName: string, row: Record<string, unknown>): Record<string, unknown> {
  const copy = { ...row }
  // Destillierte Muster stammen aus echten Protokollen — nicht mitverteilen.
  if (tableName === 'feedbackCriterionItems' && 'learnedPatternHints' in copy) {
    copy.learnedPatternHints = null
  }
  return copy
}

/** Liest Struktur aus der aktuellen V2-Dev-DB (leere Laufzeit-Tabellen im Payload). */
export function buildStarterPackageFromDb(): StarterPackage {
  assertStarterEnv()
  assertV2DevOnly(PROJECT_ROOT, 'starter:export')

  const tables: Record<string, Array<Record<string, unknown>>> = {}
  for (const name of TABLE_NAMES) {
    if (STRUCTURE_SET.has(name)) {
      tables[name] = getAllRows<Record<string, unknown>>(name).map(r => sanitizeStructureRow(name, r))
    } else {
      tables[name] = []
    }
  }

  return {
    kind: 'assignment-monitor-starter',
    version: 1,
    exportedAt: new Date().toISOString(),
    note: 'Nur Struktur/Config — keine Personen, Assignments oder Meetings. Nur für AssignmentMonitorV2.',
    tables,
  }
}

export function writeStarterPackage(pkg: StarterPackage, targetPath = STARTER_FILE): string {
  assertV2DevOnly(targetPath, 'starter:export')
  mkdirSync(dirname(targetPath), { recursive: true })
  writeFileSync(targetPath, JSON.stringify(pkg, null, 2), 'utf8')
  return targetPath
}

export function readStarterPackage(sourcePath = STARTER_FILE): StarterPackage {
  assertV2DevOnly(sourcePath, 'starter:import')
  if (!existsSync(sourcePath)) {
    throw new Error(`[starter] Datei fehlt: ${sourcePath}`)
  }
  const raw = JSON.parse(readFileSync(sourcePath, 'utf8')) as StarterPackage
  if (raw.kind !== 'assignment-monitor-starter' || raw.version !== 1 || !raw.tables) {
    throw new Error('[starter] Ungültiges Starter-Paket (kind/version/tables).')
  }
  return raw
}

/**
 * Ersetzt den kompletten DB-Inhalt durch das Starter-Paket (Struktur befüllt, Rest leer).
 * Absichtlich voller Wipe — für frische Team-Installationen.
 */
export function importStarterPackage(pkg: StarterPackage): { structureRows: number } {
  assertStarterEnv()
  assertV2DevOnly(PROJECT_ROOT, 'starter:import')

  let structureRows = 0
  db.exec('BEGIN')
  try {
    for (const table of TABLE_DEFINITIONS) {
      db.prepare(`DELETE FROM "${table.name}"`).run()
    }
    for (const table of TABLE_DEFINITIONS) {
      const rows = pkg.tables[table.name] ?? []
      for (const row of rows) {
        const { id, ...rest } = row
        if (typeof id !== 'number') continue
        insertRowWithId(table.name, id, sanitizeStructureRow(table.name, rest))
        if (STRUCTURE_SET.has(table.name)) structureRows++
      }
    }
    db.exec('COMMIT')
  } catch (err) {
    db.exec('ROLLBACK')
    throw err
  }
  return { structureRows }
}

export function summarizeStarter(pkg: StarterPackage): Record<string, number> {
  const counts: Record<string, number> = {}
  for (const name of STARTER_STRUCTURE_TABLES) {
    counts[name] = pkg.tables[name]?.length ?? 0
  }
  return counts
}