subtotaltitle/ajax/cleanup_subtotals.php

<?php
/**
 * Bereinigt verwaiste Subtotals und fehlerhafte Einträge
 */
define('NOTOKENRENEWAL', 1);
require '../../../main.inc.php';
require_once __DIR__.'/../lib/subtotaltitle.lib.php';
require_once __DIR__.'/../class/DocumentTypeHelper.class.php';

$facture_id = GETPOST('facture_id', 'int');
$docType = GETPOST('document_type', 'alpha');

if (!$facture_id || !$docType) {
    echo json_encode(['success' => false, 'error' => 'Missing parameters']);
    exit;
}

$tables = DocumentTypeHelper::getTableNames($docType);
if (!$tables) {
    echo json_encode(['success' => false, 'error' => 'Invalid document type']);
    exit;
}

$db->begin();
$deleted = 0;
$fixed = 0;

// 0. Sections dürfen KEINE parent_section haben - korrigiere das zuerst
$sql_fix_sections = "UPDATE ".MAIN_DB_PREFIX."facture_lines_manager";
$sql_fix_sections .= " SET parent_section = NULL";
$sql_fix_sections .= " WHERE line_type = 'section'";
$sql_fix_sections .= " AND parent_section IS NOT NULL";
$sql_fix_sections .= " AND ".$tables['fk_parent']." = ".(int)$facture_id;
$sql_fix_sections .= " AND document_type = '".$db->escape($docType)."'";
$resql_fix = $db->query($sql_fix_sections);
$sections_fixed = $resql_fix ? $db->affected_rows($resql_fix) : 0;
if ($sections_fixed > 0) {
    subtotaltitle_debug_log('🧹 ' . $sections_fixed . ' Sections mit falscher parent_section korrigiert');
    $fixed += $sections_fixed;
}

// 0b. parent_section = 0 sollte NULL sein
$sql_fix_zero = "UPDATE ".MAIN_DB_PREFIX."facture_lines_manager";
$sql_fix_zero .= " SET parent_section = NULL";
$sql_fix_zero .= " WHERE parent_section = 0";
$sql_fix_zero .= " AND ".$tables['fk_parent']." = ".(int)$facture_id;
$sql_fix_zero .= " AND document_type = '".$db->escape($docType)."'";
$resql_fix_zero = $db->query($sql_fix_zero);
$zero_fixed = $resql_fix_zero ? $db->affected_rows($resql_fix_zero) : 0;
if ($zero_fixed > 0) {
    subtotaltitle_debug_log('🧹 ' . $zero_fixed . ' Einträge mit parent_section=0 korrigiert');
    $fixed += $zero_fixed;
}

// 1. Lösche fehlerhafte "Produkte" in der Detail-Tabelle die eigentlich Zwischensummen sind
// (erkennbar an description LIKE 'Zwischensumme%' aber OHNE special_code 102)
$sql_bad = "SELECT rowid FROM ".MAIN_DB_PREFIX.$tables['lines_table'];
$sql_bad .= " WHERE ".$tables['fk_parent']." = ".(int)$facture_id;
$sql_bad .= " AND description LIKE 'Zwischensumme%'";
$sql_bad .= " AND (special_code IS NULL OR special_code != 102)";
$resql_bad = $db->query($sql_bad);

while ($obj = $db->fetch_object($resql_bad)) {
    // Lösche aus Detail-Tabelle
    $sql_del = "DELETE FROM ".MAIN_DB_PREFIX.$tables['lines_table']." WHERE rowid = ".(int)$obj->rowid;
    $db->query($sql_del);

    // Lösche auch aus Manager-Tabelle falls vorhanden
    $sql_del_mgr = "DELETE FROM ".MAIN_DB_PREFIX."facture_lines_manager";
    $sql_del_mgr .= " WHERE ".$tables['fk_line']." = ".(int)$obj->rowid;
    $db->query($sql_del_mgr);

    subtotaltitle_debug_log('🧹 Fehlerhaftes Zwischensummen-Produkt gelöscht: #' . $obj->rowid);
    $fixed++;
}

// 2. Lösche Subtotals deren Section show_subtotal = 0 hat
$sql = "SELECT sub.rowid, sub.".$tables['fk_line']." as detail_id";
$sql .= " FROM ".MAIN_DB_PREFIX."facture_lines_manager sub";
$sql .= " INNER JOIN ".MAIN_DB_PREFIX."facture_lines_manager sec ON sec.rowid = sub.parent_section";
$sql .= " WHERE sub.line_type = 'subtotal'";
$sql .= " AND sub.".$tables['fk_parent']." = ".(int)$facture_id;
$sql .= " AND sub.document_type = '".$db->escape($docType)."'";
$sql .= " AND (sec.show_subtotal = 0 OR sec.show_subtotal IS NULL)";
$resql = $db->query($sql);

while ($obj = $db->fetch_object($resql)) {
    // Auch aus Detail-Tabelle löschen falls vorhanden
    if ($obj->detail_id) {
        $sql_del_det = "DELETE FROM ".MAIN_DB_PREFIX.$tables['lines_table']." WHERE rowid = ".(int)$obj->detail_id;
        $db->query($sql_del_det);
    }

    // Aus Manager löschen
    $sql_del = "DELETE FROM ".MAIN_DB_PREFIX."facture_lines_manager WHERE rowid = ".(int)$obj->rowid;
    $db->query($sql_del);
    $deleted++;
}

if ($deleted > 0 || $fixed > 0) {
    subtotaltitle_debug_log('🧹 Cleanup: ' . $deleted . ' verwaiste Subtotals, ' . $fixed . ' fehlerhafte Produkte gelöscht');

    // line_order neu durchnummerieren
    $sql_reorder = "SELECT rowid FROM ".MAIN_DB_PREFIX."facture_lines_manager";
    $sql_reorder .= " WHERE ".$tables['fk_parent']." = ".(int)$facture_id;
    $sql_reorder .= " AND document_type = '".$db->escape($docType)."'";
    $sql_reorder .= " ORDER BY line_order";
    $resql = $db->query($sql_reorder);

    $new_order = 1;
    while ($obj = $db->fetch_object($resql)) {
        $sql_upd = "UPDATE ".MAIN_DB_PREFIX."facture_lines_manager";
        $sql_upd .= " SET line_order = ".$new_order;
        $sql_upd .= " WHERE rowid = ".(int)$obj->rowid;
        $db->query($sql_upd);
        $new_order++;
    }

    // Auch rang in Detail-Tabelle neu durchnummerieren
    $sql_sync = "SELECT ".$tables['fk_line']." FROM ".MAIN_DB_PREFIX."facture_lines_manager";
    $sql_sync .= " WHERE ".$tables['fk_parent']." = ".(int)$facture_id;
    $sql_sync .= " AND document_type = '".$db->escape($docType)."'";
    $sql_sync .= " AND ".$tables['fk_line']." IS NOT NULL";
    $sql_sync .= " ORDER BY line_order";
    $resql_sync = $db->query($sql_sync);

    $rang = 1;
    while ($obj = $db->fetch_object($resql_sync)) {
        $fk_line_value = $obj->{$tables['fk_line']};
        $sql_upd = "UPDATE ".MAIN_DB_PREFIX.$tables['lines_table'];
        $sql_upd .= " SET rang = ".$rang;
        $sql_upd .= " WHERE rowid = ".(int)$fk_line_value;
        $db->query($sql_upd);
        $rang++;
    }
}

$db->commit();

echo json_encode([
    'success' => true,
    'deleted' => $deleted,
    'fixed' => $fixed
]);