// =============================================================================
// Bestellung (Order) Service
// =============================================================================

import pool from '../config/database';
import logger from '../utils/logger';
import fs from 'fs';
import notificationService from './notification.service';
import { permissionService } from './permission.service';
import ausruestungsanfrageService from './ausruestungsanfrage.service';
import buchhaltungService from './buchhaltung.service';

// ---------------------------------------------------------------------------
// Catalog (shared ausruestung_artikel via ausruestungsanfrageService)
// ---------------------------------------------------------------------------

async function getKatalogItems(filters?: { search?: string; kategorie?: string }) {
  try {
    return await ausruestungsanfrageService.getItems({ search: filters?.search, kategorie: filters?.kategorie, aktiv: true });
  } catch (error) {
    logger.error('BestellungService.getKatalogItems failed', { error });
    throw new Error('Katalogartikel konnten nicht geladen werden');
  }
}

async function getKatalogItem(id: number) {
  try {
    return await ausruestungsanfrageService.getItemById(id);
  } catch (error) {
    logger.error('BestellungService.getKatalogItem failed', { error, id });
    throw new Error('Katalogartikel konnte nicht geladen werden');
  }
}

async function getKatalogKategorien() {
  try {
    return await ausruestungsanfrageService.getKategorien();
  } catch (error) {
    logger.error('BestellungService.getKatalogKategorien failed', { error });
    throw new Error('Katalogkategorien konnten nicht geladen werden');
  }
}

// ---------------------------------------------------------------------------
// Members (all active users for member selector)
// ---------------------------------------------------------------------------

async function getAllMembers() {
  try {
    const result = await pool.query(
      `SELECT id, COALESCE(given_name || ' ' || family_name, name) AS name
       FROM users
       WHERE is_active = true
       ORDER BY name`,
    );
    return result.rows;
  } catch (error) {
    logger.error('BestellungService.getAllMembers failed', { error });
    throw new Error('Mitglieder konnten nicht geladen werden');
  }
}

// ---------------------------------------------------------------------------
// Vendors (Lieferanten)
// ---------------------------------------------------------------------------

async function getVendors() {
  try {
    const result = await pool.query(
      `SELECT * FROM lieferanten ORDER BY name`
    );
    return result.rows;
  } catch (error) {
    logger.error('BestellungService.getVendors failed', { error });
    throw new Error('Lieferanten konnten nicht geladen werden');
  }
}

async function getVendorById(id: number) {
  try {
    const result = await pool.query(
      `SELECT * FROM lieferanten WHERE id = $1`,
      [id]
    );
    return result.rows[0] || null;
  } catch (error) {
    logger.error('BestellungService.getVendorById failed', { error, id });
    throw new Error('Lieferant konnte nicht geladen werden');
  }
}

async function createVendor(data: { name: string; kontakt_name?: string; email?: string; telefon?: string; adresse?: string; website?: string; notizen?: string }, userId: string) {
  try {
    const result = await pool.query(
      `INSERT INTO lieferanten (name, kontakt_name, email, telefon, adresse, website, notizen, erstellt_von)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
       RETURNING *`,
      [data.name, data.kontakt_name || null, data.email || null, data.telefon || null, data.adresse || null, data.website || null, data.notizen || null, userId]
    );
    return result.rows[0];
  } catch (error) {
    logger.error('BestellungService.createVendor failed', { error });
    throw new Error('Lieferant konnte nicht erstellt werden');
  }
}

async function updateVendor(id: number, data: { name?: string; kontakt_name?: string; email?: string; telefon?: string; adresse?: string; website?: string; notizen?: string }, userId: string) {
  try {
    const result = await pool.query(
      `UPDATE lieferanten
       SET name = COALESCE($1, name),
           kontakt_name = COALESCE($2, kontakt_name),
           email = COALESCE($3, email),
           telefon = COALESCE($4, telefon),
           adresse = COALESCE($5, adresse),
           website = COALESCE($6, website),
           notizen = COALESCE($7, notizen),
           aktualisiert_am = NOW()
       WHERE id = $8
       RETURNING *`,
      [data.name, data.kontakt_name, data.email, data.telefon, data.adresse, data.website, data.notizen, id]
    );
    if (result.rows.length === 0) return null;

    await logAction(0, 'Lieferant aktualisiert', `Lieferant "${result.rows[0].name}" bearbeitet`, userId);
    return result.rows[0];
  } catch (error) {
    logger.error('BestellungService.updateVendor failed', { error, id });
    throw new Error('Lieferant konnte nicht aktualisiert werden');
  }
}

async function deleteVendor(id: number) {
  try {
    const result = await pool.query(
      `DELETE FROM lieferanten WHERE id = $1 RETURNING id`,
      [id]
    );
    return (result.rowCount ?? 0) > 0;
  } catch (error) {
    logger.error('BestellungService.deleteVendor failed', { error, id });
    throw new Error('Lieferant konnte nicht gelöscht werden');
  }
}

// ---------------------------------------------------------------------------
// Orders (Bestellungen)
// ---------------------------------------------------------------------------

async function getOrders(filters?: { status?: string; lieferant_id?: number; besteller_id?: string }) {
  try {
    const conditions: string[] = [];
    const params: unknown[] = [];
    let paramIndex = 1;

    if (filters?.status) {
      conditions.push(`b.status = $${paramIndex++}`);
      params.push(filters.status);
    }
    if (filters?.lieferant_id) {
      conditions.push(`b.lieferant_id = $${paramIndex++}`);
      params.push(filters.lieferant_id);
    }
    if (filters?.besteller_id) {
      conditions.push(`b.erstellt_von = $${paramIndex++}`);
      params.push(filters.besteller_id);
    }

    const whereClause = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';

    const result = await pool.query(
      `SELECT b.*,
              l.name AS lieferant_name,
              COALESCE(u.name, u.preferred_username, u.email) AS besteller_name,
              COALESCE(mu.name, mu.preferred_username, mu.email) AS mitglied_name,
              COALESCE(pos.total_cost, 0) AS total_cost,
              COALESCE(pos.items_count, 0) AS items_count,
              COALESCE(pos.total_received, 0) AS total_received,
              COALESCE(pos.total_ordered, 0) AS total_ordered
       FROM bestellungen b
       LEFT JOIN lieferanten l ON l.id = b.lieferant_id
       LEFT JOIN users u ON u.id = b.erstellt_von
       LEFT JOIN users mu ON mu.id = b.mitglied_id
       LEFT JOIN LATERAL (
         SELECT SUM(einzelpreis * menge) AS total_cost,
                COUNT(*) AS items_count,
                SUM(erhalten_menge) AS total_received,
                SUM(menge) AS total_ordered
         FROM bestellpositionen
         WHERE bestellung_id = b.id
       ) pos ON true
       ${whereClause}
       ORDER BY b.erstellt_am DESC`,
      params
    );
    return result.rows;
  } catch (error) {
    logger.error('BestellungService.getOrders failed', { error });
    throw new Error('Bestellungen konnten nicht geladen werden');
  }
}

async function getOrderById(id: number) {
  try {
    const orderResult = await pool.query(
      `SELECT b.*,
              l.name            AS lieferant_name,
              l.kontakt_name    AS lieferant_kontakt_name,
              l.email           AS lieferant_email,
              l.telefon         AS lieferant_telefon,
              l.adresse         AS lieferant_adresse,
              COALESCE(u.name, u.preferred_username, u.email) AS besteller_name,
              u.email           AS besteller_email,
              COALESCE(mp.telefon_mobil, mp.telefon_privat) AS besteller_telefon,
              mp.dienstgrad     AS besteller_dienstgrad,
              mu.id             AS mitglied_id,
              COALESCE(mu.name, mu.preferred_username, mu.email) AS mitglied_name,
              mmp.dienstgrad    AS mitglied_dienstgrad
       FROM bestellungen b
       LEFT JOIN lieferanten l ON l.id = b.lieferant_id
       LEFT JOIN users u ON u.id = COALESCE(b.besteller_id, b.erstellt_von)
       LEFT JOIN mitglieder_profile mp ON mp.user_id = COALESCE(b.besteller_id, b.erstellt_von)
       LEFT JOIN users mu ON mu.id = b.mitglied_id
       LEFT JOIN mitglieder_profile mmp ON mmp.user_id = b.mitglied_id
       WHERE b.id = $1`,
      [id]
    );
    if (orderResult.rows.length === 0) return null;

    const [positionen, dateien, erinnerungen, historie] = await Promise.all([
      pool.query(
        `SELECT bp.*, aa.bezeichnung AS artikel_bezeichnung
         FROM bestellpositionen bp
         LEFT JOIN ausruestung_artikel aa ON aa.id = bp.artikel_id
         WHERE bp.bestellung_id = $1 ORDER BY bp.id`,
        [id]
      ),
      pool.query(`SELECT * FROM bestellung_dateien WHERE bestellung_id = $1 ORDER BY hochgeladen_am DESC`, [id]),
      pool.query(`SELECT * FROM bestellung_erinnerungen WHERE bestellung_id = $1 ORDER BY faellig_am`, [id]),
      pool.query(`SELECT h.*, COALESCE(u.name, u.preferred_username, u.email) AS benutzer_name FROM bestellung_historie h LEFT JOIN users u ON u.id = h.erstellt_von WHERE h.bestellung_id = $1 ORDER BY h.erstellt_am DESC`, [id]),
    ]);

    return {
      bestellung: orderResult.rows[0],
      positionen: positionen.rows,
      dateien: dateien.rows,
      erinnerungen: erinnerungen.rows,
      historie: historie.rows,
    };
  } catch (error) {
    logger.error('BestellungService.getOrderById failed', { error, id });
    throw new Error('Bestellung konnte nicht geladen werden');
  }
}

async function createOrder(data: { bezeichnung: string; lieferant_id?: number; besteller_id?: string; mitglied_id?: string; notizen?: string; budget?: number; steuersatz?: number; positionen?: Array<{ bezeichnung: string; menge: number; einheit?: string }> }, userId: string) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');

    // Get next laufende_nummer for the current year
    const nrResult = await client.query(
      `SELECT COALESCE(MAX(laufende_nummer), 0) + 1 AS next_nr
       FROM bestellungen
       WHERE EXTRACT(YEAR FROM erstellt_am) = EXTRACT(YEAR FROM NOW())`
    );
    const laufendeNummer = nrResult.rows[0].next_nr;

    const bestellerId = data.besteller_id && data.besteller_id.trim() ? data.besteller_id.trim() : null;
    const result = await client.query(
      `INSERT INTO bestellungen (bezeichnung, lieferant_id, besteller_id, notizen, budget, steuersatz, mitglied_id, laufende_nummer, erstellt_von)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)
       RETURNING *`,
      [data.bezeichnung, data.lieferant_id || null, bestellerId, data.notizen || null, data.budget || null, data.steuersatz ?? 20, data.mitglied_id || null, laufendeNummer, userId]
    );
    const order = result.rows[0];

    if (data.positionen && data.positionen.length > 0) {
      for (const pos of data.positionen) {
        await client.query(
          `INSERT INTO bestellpositionen (bestellung_id, bezeichnung, menge, einheit)
           VALUES ($1, $2, $3, $4)`,
          [order.id, pos.bezeichnung, pos.menge, pos.einheit || 'Stk']
        );
      }
    }

    await client.query('COMMIT');
    await logAction(order.id, 'Bestellung erstellt', `Bestellung "${data.bezeichnung}" erstellt`, userId);
    return order;
  } catch (error) {
    await client.query('ROLLBACK');
    logger.error('BestellungService.createOrder failed', { error });
    throw new Error('Bestellung konnte nicht erstellt werden');
  } finally {
    client.release();
  }
}

async function updateOrder(id: number, data: { bezeichnung?: string; lieferant_id?: number; besteller_id?: string | null; notizen?: string; budget?: number; status?: string; steuersatz?: number; mitglied_id?: string | null }, userId: string) {
  try {
    // Check current order for status change detection
    const current = await pool.query(`SELECT * FROM bestellungen WHERE id = $1`, [id]);
    if (current.rows.length === 0) return null;

    const oldStatus = current.rows[0].status;
    const newStatus = data.status || oldStatus;

    let bestellt_am = current.rows[0].bestellt_am;
    let abgeschlossen_am = current.rows[0].abgeschlossen_am;

    if (newStatus !== oldStatus) {
      if (newStatus === 'bestellt' && !bestellt_am) {
        bestellt_am = new Date();
      }
      if (newStatus === 'abgeschlossen' && !abgeschlossen_am) {
        abgeschlossen_am = new Date();
      }
    }

    const result = await pool.query(
      `UPDATE bestellungen
       SET bezeichnung = COALESCE($1, bezeichnung),
           lieferant_id = COALESCE($2, lieferant_id),
           besteller_id = CASE WHEN $3::text IS NOT NULL AND $3::text != '' THEN $3::uuid ELSE besteller_id END,
           notizen = COALESCE($4, notizen),
           budget = COALESCE($5, budget),
           status = COALESCE($6, status),
           bestellt_am = $7,
           abgeschlossen_am = $8,
           steuersatz = COALESCE($9, steuersatz),
           mitglied_id = CASE WHEN $10::text IS NOT NULL AND $10::text != '' THEN $10::uuid ELSE mitglied_id END,
           aktualisiert_am = NOW()
       WHERE id = $11
       RETURNING *`,
      [data.bezeichnung, data.lieferant_id, data.besteller_id ?? null, data.notizen, data.budget, data.status, bestellt_am, abgeschlossen_am, data.steuersatz, data.mitglied_id ?? null, id]
    );
    if (result.rows.length === 0) return null;

    const changes: string[] = [];
    if (data.bezeichnung) changes.push(`Bezeichnung geändert`);
    if (data.lieferant_id) changes.push(`Lieferant geändert`);
    if (data.besteller_id) changes.push('Besteller geändert');
    if (data.status && data.status !== oldStatus) changes.push(`Status: ${oldStatus} → ${data.status}`);
    if (data.budget) changes.push(`Budget geändert`);
    if (data.steuersatz != null) changes.push(`Steuersatz: ${data.steuersatz}%`);
    if (data.mitglied_id) changes.push('Mitglied geändert');

    await logAction(id, 'Bestellung aktualisiert', changes.join(', ') || 'Bestellung bearbeitet', userId);
    return result.rows[0];
  } catch (error) {
    logger.error('BestellungService.updateOrder failed', { error, id });
    throw new Error('Bestellung konnte nicht aktualisiert werden');
  }
}

async function deleteOrder(id: number, _userId: string) {
  try {
    // Get file paths before deleting
    const filesResult = await pool.query(
      `SELECT dateipfad FROM bestellung_dateien WHERE bestellung_id = $1`,
      [id]
    );
    const filePaths = filesResult.rows.map((r: { dateipfad: string }) => r.dateipfad);

    const result = await pool.query(
      `DELETE FROM bestellungen WHERE id = $1 RETURNING id`,
      [id]
    );
    if ((result.rowCount ?? 0) === 0) return false;

    // Remove files from disk
    for (const filePath of filePaths) {
      try {
        if (filePath && fs.existsSync(filePath)) {
          fs.unlinkSync(filePath);
        }
      } catch (err) {
        logger.warn('Failed to delete file from disk', { filePath, error: err });
      }
    }

    return true;
  } catch (error) {
    logger.error('BestellungService.deleteOrder failed', { error, id });
    throw new Error('Bestellung konnte nicht gelöscht werden');
  }
}

const VALID_STATUS_TRANSITIONS: Record<string, string[]> = {
  entwurf:                  ['wartet_auf_genehmigung'],
  wartet_auf_genehmigung:   ['bereit_zur_bestellung', 'entwurf'],
  bereit_zur_bestellung:    ['bestellt'],
  bestellt:                 ['teillieferung', 'lieferung_pruefen'],
  teillieferung:            ['lieferung_pruefen'],
  lieferung_pruefen:        ['abgeschlossen'],
  abgeschlossen:            [],
};

async function updateOrderStatus(id: number, status: string, userId: string, force?: boolean, approverUserId?: string) {
  try {
    const current = await pool.query(`SELECT * FROM bestellungen WHERE id = $1`, [id]);
    if (current.rows.length === 0) return null;

    const order = current.rows[0];
    const oldStatus = order.status;
    if (!force) {
      const allowed = VALID_STATUS_TRANSITIONS[oldStatus] || [];
      if (!allowed.includes(status)) {
        throw new Error(`Ungültiger Statusübergang: ${oldStatus} → ${status}`);
      }
    }

    // Block transition to abgeschlossen if positions have missing prices
    if (status === 'abgeschlossen') {
      const posCheck = await pool.query(
        `SELECT COUNT(*) FROM bestellpositionen WHERE bestellung_id = $1 AND (einzelpreis IS NULL OR einzelpreis = 0)`,
        [id]
      );
      if (parseInt(posCheck.rows[0].count) > 0) {
        throw new Error('Alle Positionen müssen einen Einzelpreis haben, bevor die Bestellung abgeschlossen werden kann.');
      }
    }

    const updates: string[] = ['status = $1', 'aktualisiert_am = NOW()'];
    const params: unknown[] = [status];
    let paramIndex = 2;

    if (status === 'bestellt') {
      updates.push(`bestellt_am = COALESCE(bestellt_am, NOW())`);
    }
    if (status === 'abgeschlossen') {
      updates.push(`abgeschlossen_am = COALESCE(abgeschlossen_am, NOW())`);
    }
    if (status === 'bereit_zur_bestellung' && approverUserId) {
      updates.push(`genehmigt_von = $${paramIndex}`);
      params.push(approverUserId);
      paramIndex++;
      updates.push(`genehmigt_am = NOW()`);
    }

    params.push(id);
    const result = await pool.query(
      `UPDATE bestellungen SET ${updates.join(', ')} WHERE id = $${paramIndex} RETURNING *`,
      params
    );

    await logAction(id, 'Status geändert', `${oldStatus} → ${status}${force ? ' (manuell)' : ''}`, userId);

    // Notifications based on transition
    const orderLabel = order.laufende_nummer ? `#${order.laufende_nummer}` : `#${order.id}`;
    const bestellerId = order.besteller_id || order.erstellt_von;

    if (status === 'wartet_auf_genehmigung') {
      try {
        const approvers = await permissionService.getUsersWithPermission('bestellungen:approve');
        for (const user of approvers) {
          await notificationService.createNotification({
            user_id: user.id,
            typ: 'bestellung',
            titel: 'Bestellung wartet auf Genehmigung',
            nachricht: `Bestellung ${orderLabel} wurde zur Genehmigung eingereicht`,
            schwere: 'info',
            quell_typ: 'bestellung_genehmigung',
            quell_id: order.id.toString(),
            link: `/bestellungen/${id}`,
          });
        }
      } catch (err) {
        logger.error('Failed to notify approvers', { err, orderId: id });
      }
    } else if (status === 'bereit_zur_bestellung') {
      await notificationService.createNotification({
        user_id: bestellerId,
        typ: 'bestellung',
        titel: 'Bestellung genehmigt',
        nachricht: `Bestellung ${orderLabel} wurde genehmigt und ist bereit zur Bestellung`,
        schwere: 'info',
        quell_typ: 'bestellung_genehmigung',
        quell_id: order.id.toString(),
        link: `/bestellungen/${id}`,
      });
    } else if (status === 'entwurf' && oldStatus === 'wartet_auf_genehmigung') {
      await notificationService.createNotification({
        user_id: bestellerId,
        typ: 'bestellung',
        titel: 'Bestellung abgelehnt',
        nachricht: `Bestellung ${orderLabel} wurde abgelehnt und zurück in den Entwurf gesetzt`,
        schwere: 'warnung',
        quell_typ: 'bestellung_genehmigung',
        quell_id: order.id.toString(),
        link: `/bestellungen/${id}`,
      });
    }

    // Create pending buchhaltung transaction when order is completed
    if (status === 'abgeschlossen') {
      buchhaltungService.createPendingFromBestellung(id, userId).catch(err =>
        logger.error('createPendingFromBestellung failed (non-fatal)', { err, orderId: id })
      );
    }

    return result.rows[0];
  } catch (error) {
    logger.error('BestellungService.updateOrderStatus failed', { error, id });
    throw error;
  }
}

// ---------------------------------------------------------------------------
// Line Items (Bestellpositionen)
// ---------------------------------------------------------------------------

async function addLineItem(bestellungId: number, data: { bezeichnung: string; artikelnummer?: string; menge: number; einheit?: string; einzelpreis?: number; notizen?: string; spezifikationen?: string[]; artikel_id?: number }, userId: string) {
  try {
    const result = await pool.query(
      `INSERT INTO bestellpositionen (bestellung_id, bezeichnung, artikelnummer, menge, einheit, einzelpreis, notizen, spezifikationen, artikel_id)
       VALUES ($1, $2, $3, $4, $5, $6, $7, $8::jsonb, $9)
       RETURNING *`,
      [bestellungId, data.bezeichnung, data.artikelnummer || null, data.menge, data.einheit || 'Stk', data.einzelpreis || 0, data.notizen || null, JSON.stringify(data.spezifikationen || []), data.artikel_id || null]
    );
    await logAction(bestellungId, 'Position hinzugefügt', `"${data.bezeichnung}" x${data.menge}`, userId);
    return result.rows[0];
  } catch (error) {
    logger.error('BestellungService.addLineItem failed', { error, bestellungId });
    throw new Error('Position konnte nicht hinzugefügt werden');
  }
}

async function updateLineItem(id: number, data: { bezeichnung?: string; artikelnummer?: string; menge?: number; einheit?: string; einzelpreis?: number; notizen?: string; spezifikationen?: string[] }, userId: string) {
  try {
    const result = await pool.query(
      `UPDATE bestellpositionen
       SET bezeichnung = COALESCE($1, bezeichnung),
           artikelnummer = COALESCE($2, artikelnummer),
           menge = COALESCE($3, menge),
           einheit = COALESCE($4, einheit),
           einzelpreis = COALESCE($5, einzelpreis),
           notizen = COALESCE($6, notizen),
           spezifikationen = COALESCE($7::jsonb, spezifikationen)
       WHERE id = $8
       RETURNING *`,
      [data.bezeichnung, data.artikelnummer, data.menge, data.einheit, data.einzelpreis, data.notizen, data.spezifikationen ? JSON.stringify(data.spezifikationen) : null, id]
    );
    if (result.rows.length === 0) return null;

    const item = result.rows[0];
    await logAction(item.bestellung_id, 'Position aktualisiert', `"${item.bezeichnung}" bearbeitet`, userId);
    return item;
  } catch (error) {
    logger.error('BestellungService.updateLineItem failed', { error, id });
    throw new Error('Position konnte nicht aktualisiert werden');
  }
}

async function deleteLineItem(id: number, userId: string) {
  try {
    const item = await pool.query(`SELECT * FROM bestellpositionen WHERE id = $1`, [id]);
    if (item.rows.length === 0) return false;

    await pool.query(`DELETE FROM bestellpositionen WHERE id = $1`, [id]);
    await logAction(item.rows[0].bestellung_id, 'Position entfernt', `"${item.rows[0].bezeichnung}" entfernt`, userId);
    return true;
  } catch (error) {
    logger.error('BestellungService.deleteLineItem failed', { error, id });
    throw new Error('Position konnte nicht gelöscht werden');
  }
}

async function updateReceivedQuantity(id: number, menge: number, userId: string) {
  try {
    // First check if the item's order is in a valid status for receiving
    const itemCheck = await pool.query(
      `SELECT bp.bestellung_id, b.status
       FROM bestellpositionen bp
       JOIN bestellungen b ON b.id = bp.bestellung_id
       WHERE bp.id = $1`,
      [id]
    );
    if (itemCheck.rows.length > 0) {
      const orderStatus = itemCheck.rows[0].status;
      if (orderStatus !== 'bestellt' && orderStatus !== 'teillieferung') {
        throw Object.assign(new Error('Mengenaktualisierung nur bei Status bestellt oder Teillieferung möglich'), { statusCode: 400 });
      }
    }

    const result = await pool.query(
      `UPDATE bestellpositionen SET erhalten_menge = $1 WHERE id = $2 RETURNING *`,
      [menge, id]
    );
    if (result.rows.length === 0) return null;

    const item = result.rows[0];
    await logAction(item.bestellung_id, 'Liefermenge aktualisiert', `"${item.bezeichnung}": ${menge} von ${item.menge} erhalten`, userId);

    // Sync im_haus on linked internal request position
    if (item.anfrage_position_id) {
      const imHaus = Number(item.erhalten_menge) >= Number(item.menge);
      await pool.query(
        `UPDATE ausruestung_anfrage_positionen SET im_haus = $1 WHERE id = $2`,
        [imHaus, item.anfrage_position_id]
      );
    }

    // Check if all items for this order are fully received
    const allItems = await pool.query(
      `SELECT menge, erhalten_menge FROM bestellpositionen WHERE bestellung_id = $1`,
      [item.bestellung_id]
    );
    const allReceived = allItems.rows.every((r: { menge: number; erhalten_menge: number }) => r.erhalten_menge >= r.menge);
    const someReceived = allItems.rows.some((r: { menge: number; erhalten_menge: number }) => (r.erhalten_menge ?? 0) > 0);

    // Auto-update order status if currently 'bestellt'
    const order = await pool.query(`SELECT status FROM bestellungen WHERE id = $1`, [item.bestellung_id]);
    if (order.rows.length > 0 && (order.rows[0].status === 'bestellt' || order.rows[0].status === 'teillieferung')) {
      if (allReceived) {
        await pool.query(
          `UPDATE bestellungen SET status = 'lieferung_pruefen', aktualisiert_am = NOW() WHERE id = $1`,
          [item.bestellung_id]
        );
        await logAction(item.bestellung_id, 'Status geändert', 'Alle Positionen vollständig erhalten → lieferung_pruefen', userId);
      } else if (someReceived && order.rows[0].status === 'bestellt') {
        await pool.query(
          `UPDATE bestellungen SET status = 'teillieferung', aktualisiert_am = NOW() WHERE id = $1`,
          [item.bestellung_id]
        );
        await logAction(item.bestellung_id, 'Status geändert', 'Teillieferung eingegangen → teillieferung', userId);
      }
    }

    return item;
  } catch (error) {
    logger.error('BestellungService.updateReceivedQuantity failed', { error, id });
    throw error;
  }
}

// ---------------------------------------------------------------------------
// Files (Bestellung Dateien)
// ---------------------------------------------------------------------------

async function addFile(bestellungId: number, fileData: { dateiname: string; dateipfad: string; dateityp: string; dateigroesse: number }, userId: string) {
  try {
    const result = await pool.query(
      `INSERT INTO bestellung_dateien (bestellung_id, dateiname, dateipfad, dateityp, dateigroesse, hochgeladen_von)
       VALUES ($1, $2, $3, $4, $5, $6)
       RETURNING *`,
      [bestellungId, fileData.dateiname, fileData.dateipfad, fileData.dateityp, fileData.dateigroesse, userId]
    );
    await logAction(bestellungId, 'Datei hochgeladen', `"${fileData.dateiname}"`, userId);
    return result.rows[0];
  } catch (error) {
    logger.error('BestellungService.addFile failed', { error, bestellungId });
    throw new Error('Datei konnte nicht gespeichert werden');
  }
}

async function deleteFile(id: number, userId: string) {
  try {
    const fileResult = await pool.query(
      `SELECT * FROM bestellung_dateien WHERE id = $1`,
      [id]
    );
    if (fileResult.rows.length === 0) return null;

    const file = fileResult.rows[0];
    await pool.query(`DELETE FROM bestellung_dateien WHERE id = $1`, [id]);
    await logAction(file.bestellung_id, 'Datei gelöscht', `"${file.dateiname}"`, userId);

    return { dateipfad: file.dateipfad, dateiname: file.dateiname };
  } catch (error) {
    logger.error('BestellungService.deleteFile failed', { error, id });
    throw new Error('Datei konnte nicht gelöscht werden');
  }
}

async function getFilesByOrder(bestellungId: number) {
  try {
    const result = await pool.query(
      `SELECT * FROM bestellung_dateien WHERE bestellung_id = $1 ORDER BY hochgeladen_am DESC`,
      [bestellungId]
    );
    return result.rows;
  } catch (error) {
    logger.error('BestellungService.getFilesByOrder failed', { error, bestellungId });
    throw new Error('Dateien konnten nicht geladen werden');
  }
}

// ---------------------------------------------------------------------------
// Reminders (Bestellung Erinnerungen)
// ---------------------------------------------------------------------------

async function addReminder(bestellungId: number, data: { nachricht: string; faellig_am: string }, userId: string) {
  try {
    const result = await pool.query(
      `INSERT INTO bestellung_erinnerungen (bestellung_id, faellig_am, nachricht, erstellt_von)
       VALUES ($1, $2, $3, $4)
       RETURNING *`,
      [bestellungId, data.faellig_am, data.nachricht || null, userId]
    );
    await logAction(bestellungId, 'Erinnerung erstellt', `Erinnerung fällig am ${data.faellig_am}`, userId);
    return result.rows[0];
  } catch (error) {
    logger.error('BestellungService.addReminder failed', { error, bestellungId });
    throw new Error('Erinnerung konnte nicht erstellt werden');
  }
}

async function markReminderDone(id: number, userId: string) {
  try {
    const result = await pool.query(
      `UPDATE bestellung_erinnerungen SET erledigt = TRUE WHERE id = $1 RETURNING *`,
      [id]
    );
    if (result.rows.length === 0) return null;

    const reminder = result.rows[0];
    await logAction(reminder.bestellung_id, 'Erinnerung erledigt', `Erinnerung #${reminder.id}`, userId);
    return reminder;
  } catch (error) {
    logger.error('BestellungService.markReminderDone failed', { error, id });
    throw new Error('Erinnerung konnte nicht als erledigt markiert werden');
  }
}

async function deleteReminder(id: number) {
  try {
    const result = await pool.query(
      `DELETE FROM bestellung_erinnerungen WHERE id = $1 RETURNING id`,
      [id]
    );
    return (result.rowCount ?? 0) > 0;
  } catch (error) {
    logger.error('BestellungService.deleteReminder failed', { error, id });
    throw new Error('Erinnerung konnte nicht gelöscht werden');
  }
}

async function getDueReminders() {
  try {
    const result = await pool.query(
      `SELECT e.*, b.bezeichnung AS bestellung_bezeichnung, b.erstellt_von AS besteller_id
       FROM bestellung_erinnerungen e
       JOIN bestellungen b ON b.id = e.bestellung_id
       WHERE e.faellig_am <= NOW() AND e.erledigt = FALSE
       ORDER BY e.faellig_am`
    );
    return result.rows;
  } catch (error) {
    logger.error('BestellungService.getDueReminders failed', { error });
    throw new Error('Fällige Erinnerungen konnten nicht geladen werden');
  }
}

// ---------------------------------------------------------------------------
// Audit History (Bestellung Historie)
// ---------------------------------------------------------------------------

async function logAction(bestellungId: number, aktion: string, details: string, userId: string) {
  try {
    await pool.query(
      `INSERT INTO bestellung_historie (bestellung_id, erstellt_von, aktion, details)
       VALUES ($1, $2, $3, $4::jsonb)`,
      [bestellungId, userId, aktion, JSON.stringify({ text: details })]
    );
  } catch (error) {
    logger.error('BestellungService.logAction failed', { error, bestellungId, aktion });
    // Non-fatal — don't propagate
  }
}

async function getHistory(bestellungId: number) {
  try {
    const result = await pool.query(
      `SELECT h.*, COALESCE(u.name, u.preferred_username, u.email) AS benutzer_name
       FROM bestellung_historie h
       LEFT JOIN users u ON u.id = h.erstellt_von
       WHERE h.bestellung_id = $1
       ORDER BY h.erstellt_am DESC`,
      [bestellungId]
    );
    return result.rows;
  } catch (error) {
    logger.error('BestellungService.getHistory failed', { error, bestellungId });
    throw new Error('Historie konnte nicht geladen werden');
  }
}

export default {
  // Members
  getAllMembers,
  // Catalog
  getKatalogItems,
  getKatalogItem,
  getKatalogKategorien,
  // Vendors
  getVendors,
  getVendorById,
  createVendor,
  updateVendor,
  deleteVendor,
  // Orders
  getOrders,
  getOrderById,
  createOrder,
  updateOrder,
  deleteOrder,
  updateOrderStatus,
  // Line Items
  addLineItem,
  updateLineItem,
  deleteLineItem,
  updateReceivedQuantity,
  // Files
  addFile,
  deleteFile,
  getFilesByOrder,
  // Reminders
  addReminder,
  markReminderDone,
  deleteReminder,
  getDueReminders,
  // Audit
  logAction,
  getHistory,
};
