#!/usr/bin/php
<?php
require(__DIR__ . "/../../config/config.php");

define('FRONKDB_SQLDEBUG', false);
error_reporting(E_ALL & ~(E_NOTICE | E_STRICT | E_DEPRECATED));

require_once(LIBDIR."/mvcfronk/mfRouter/mfRouter.php");
require_once(LIBDIR."/mvcfronk/mfBase/mfBaseModel.php");
require_once(LIBDIR."/mvcfronk/mfBase/mfBaseController.php");

$layout = \Layout::singleton();
$me = new User(1);
define("INTERNAL_USER_ID", $me->id);
define("INTERNAL_USER_USERNAME", $me->username);

$db = FronkDB::singleton();

echo "========================================\n";
echo "Manual Invoice Test Data Generator\n";
echo "========================================\n\n";

echo "Do you want to truncate ManualInvoice tables first? (y/n): ";
$handle = fopen("php://stdin", "r");
$answer = trim(fgets($handle));
fclose($handle);

if (strtolower($answer) === 'y') {
    echo "Truncating tables...\n";
    $db->query("SET FOREIGN_KEY_CHECKS = 0");
    $db->query("TRUNCATE TABLE ManualInvoiceJournal");
    $db->query("TRUNCATE TABLE ManualInvoiceposition");
    $db->query("TRUNCATE TABLE ManualInvoice");
    $db->query("SET FOREIGN_KEY_CHECKS = 1");
    echo "Tables truncated.\n\n";
}

$vatgroups = [];
$res = $db->query("SELECT * FROM Vatgroup WHERE id IN (2, 3) ORDER BY id");
while ($row = $db->fetch_object($res)) {
    $vatgroups[$row->id] = $row;
}

if (empty($vatgroups)) {
    echo "ERROR: No VAT groups found!\n";
    exit(1);
}

echo "Found " . count($vatgroups) . " VAT group(s)\n";

$vatrates = [];
$res = $db->query("SELECT * FROM Vatrate WHERE vatgroup_id IN (2, 3)");
while ($row = $db->fetch_object($res)) {
    $vatrates[$row->vatgroup_id][$row->area] = $row;
}

$articlesByVatgroup = [];
$res = $db->query("SELECT * FROM WarehouseArticle WHERE vatgroup_id IN (2, 3) AND (isEndOfLife = 0 OR isEndOfLife IS NULL)");
while ($row = $db->fetch_object($res)) {
    if (!isset($articlesByVatgroup[$row->vatgroup_id])) {
        $articlesByVatgroup[$row->vatgroup_id] = [];
    }
    $articlesByVatgroup[$row->vatgroup_id][] = $row;
}

$totalArticles = array_sum(array_map('count', $articlesByVatgroup));
if ($totalArticles == 0) {
    echo "ERROR: No warehouse articles found!\n";
    exit(1);
}

echo "Found $totalArticles warehouse articles\n";

function getCustomersByRegion($db, $region) {
    $customers = [];
    switch ($region) {
        case 'domestic':
            $sql = "SELECT a.* FROM Address a
                    LEFT JOIN Country c ON a.country_id = c.id
                    WHERE a.customer_number > 0
                    AND (a.company IS NOT NULL OR a.firstname IS NOT NULL)
                    AND a.street IS NOT NULL AND a.zip IS NOT NULL AND a.city IS NOT NULL
                    AND (c.isocode = 'AT' OR a.uid LIKE 'ATU%' OR a.country_id IS NULL)
                    ORDER BY RAND() LIMIT 20";
            break;
        case 'eu':
            $sql = "SELECT a.* FROM Address a
                    JOIN Country c ON a.country_id = c.id
                    WHERE a.customer_number > 0
                    AND (a.company IS NOT NULL OR a.firstname IS NOT NULL)
                    AND a.street IS NOT NULL AND a.zip IS NOT NULL AND a.city IS NOT NULL
                    AND c.is_eu = 1 AND c.isocode != 'AT'
                    AND (a.uid IS NULL OR a.uid NOT LIKE 'ATU%')
                    ORDER BY RAND() LIMIT 20";
            break;
        case 'other':
            $sql = "SELECT a.* FROM Address a
                    JOIN Country c ON a.country_id = c.id
                    WHERE a.customer_number > 0
                    AND (a.company IS NOT NULL OR a.firstname IS NOT NULL)
                    AND a.street IS NOT NULL AND a.zip IS NOT NULL AND a.city IS NOT NULL
                    AND c.is_eu = 0
                    ORDER BY RAND() LIMIT 20";
            break;
    }
    $res = $db->query($sql);
    while ($row = $db->fetch_object($res)) {
        $customers[] = $row;
    }
    return $customers;
}

$domesticCustomers = getCustomersByRegion($db, 'domestic');
$euCustomers = getCustomersByRegion($db, 'eu');
$nonEuCustomers = getCustomersByRegion($db, 'other');

echo "Found customers: " . count($domesticCustomers) . " domestic, " . count($euCustomers) . " EU, " . count($nonEuCustomers) . " non-EU\n\n";

function getVatArea($address, $db) {
    $uid = preg_replace('/[^a-z0-9]/i', '', $address->uid ?? '');
    if (strtolower(substr($uid, 0, 3)) === 'atu') return 'domestic';
    if ($address->country_id) {
        $res = $db->query("SELECT * FROM Country WHERE id = " . intval($address->country_id));
        if ($country = $db->fetch_object($res)) {
            if ($country->isocode === TT_HOMECOUNTRY_ISOCODE) return 'domestic';
            if ($country->is_eu) return 'eu';
            return 'other';
        }
    }
    return 'domestic';
}

function getArticlePrice($article) {
    if ($article->cheapestSellPrice) {
        $prices = json_decode($article->cheapestSellPrice, true);
        if (is_array($prices)) {
            foreach ($prices as $p) {
                if (isset($p['price']) && $p['price'] > 0) return floatval($p['price']);
            }
        }
    }
    return rand(10, 500);
}

function createInvoice($db, $data) {
    $invoiceNumber = ManualInvoiceModel::getNextInvoiceNumber();
    $now = time();
    $sql = "INSERT INTO ManualInvoice (
        invoice_number, invoice_date, performance_period, introductory_text, external_reference, total_discount,
        owner_id, billingaddress_id, customer_number, fibu_account_number, fibu_payment_due, fibu_payment_skonto, fibu_payment_skonto_rate,
        sepa_date, fibu_cost_area, fibu_cost_account, fibu_cost_account_legacy, fibu_taxcode, tax_text,
        company, firstname, lastname, street, zip, city, country, email, uid,
        billing_type, bank_account_bank, bank_account_owner, bank_account_iban, bank_account_bic,
        total, total_gross, vatgroup_id, status, `lock`, exported, create_by, edit_by, `create`, edit
    ) VALUES (
        '{$db->escape($invoiceNumber)}', " . intval($data['invoice_date']) . ",
        '{$db->escape($data['performance_period'])}', '{$db->escape($data['introductory_text'])}',
        '{$db->escape($data['external_reference'])}', " . floatval($data['total_discount']) . ",
        " . intval($data['owner_id']) . ", " . intval($data['billingaddress_id']) . ", " . intval($data['customer_number']) . ",
        " . ($data['fibu_account_number'] ? intval($data['fibu_account_number']) : 'NULL') . ",
        " . ($data['fibu_payment_due'] ? intval($data['fibu_payment_due']) : 'NULL') . ",
        " . intval($data['fibu_payment_skonto']) . ", " . intval($data['fibu_payment_skonto_rate']) . ",
        " . ($data['sepa_date'] ? "'{$db->escape($data['sepa_date'])}'" : 'NULL') . ",
        " . ($data['fibu_cost_area'] ? "'{$db->escape($data['fibu_cost_area'])}'" : 'NULL') . ",
        " . ($data['fibu_cost_account'] ? intval($data['fibu_cost_account']) : 'NULL') . ",
        " . ($data['fibu_cost_account_legacy'] ? intval($data['fibu_cost_account_legacy']) : 'NULL') . ",
        " . ($data['fibu_taxcode'] ? intval($data['fibu_taxcode']) : 'NULL') . ",
        " . ($data['tax_text'] ? "'{$db->escape($data['tax_text'])}'" : 'NULL') . ",
        '{$db->escape($data['company'])}', '{$db->escape($data['firstname'])}', '{$db->escape($data['lastname'])}',
        '{$db->escape($data['street'])}', '{$db->escape($data['zip'])}', '{$db->escape($data['city'])}',
        '{$db->escape($data['country'])}', '{$db->escape($data['email'])}', '{$db->escape($data['uid'])}',
        '{$db->escape($data['billing_type'])}',
        '{$db->escape($data['bank_account_bank'])}', '{$db->escape($data['bank_account_owner'])}',
        '{$db->escape($data['bank_account_iban'])}', '{$db->escape($data['bank_account_bic'])}',
        0, 0, " . intval($data['vatgroup_id']) . ", 'erstellt', 0, 0, 1, 1, {$now}, {$now}
    )";
    $db->query($sql);
    return ['id' => $db->link->insert_id, 'invoice_number' => $invoiceNumber];
}

function createPosition($db, $invoiceId, $pos) {
    $now = time();
    $fibuAccount = $pos['fibu_cost_account'] ? intval($pos['fibu_cost_account']) : 'NULL';
    $fibuAccountLegacy = $pos['fibu_cost_account_legacy'] ? intval($pos['fibu_cost_account_legacy']) : 'NULL';
    $fibuTaxcode = $pos['fibu_taxcode'] ? intval($pos['fibu_taxcode']) : 'NULL';
    $sql = "INSERT INTO ManualInvoiceposition (
        manualinvoice_id, warehousearticle_id, warehousearticle_name, product_info,
        amount, unit, price, discount, vatrate, price_total, price_gross,
        fibu_cost_account, fibu_cost_account_legacy, fibu_taxcode, create_by, edit_by, `create`, edit
    ) VALUES (
        $invoiceId, " . intval($pos['warehousearticle_id']) . ", '{$db->escape($pos['warehousearticle_name'])}',
        '{$db->escape($pos['product_info'])}', {$pos['amount']}, '{$db->escape($pos['unit'])}',
        {$pos['price']}, {$pos['discount']}, {$pos['vatrate']},
        " . round($pos['price_total'], 2) . ", " . round($pos['price_gross'], 2) . ",
        $fibuAccount, $fibuAccountLegacy, $fibuTaxcode, 1, 1, $now, $now
    )";
    $db->query($sql);
}

$regions = [
    'domestic' => ['name' => 'Domestic (AT)', 'customers' => $domesticCustomers],
    'eu' => ['name' => 'EU', 'customers' => $euCustomers],
    'other' => ['name' => 'Non-EU', 'customers' => $nonEuCustomers]
];

$totalCreated = 0;
$errors = 0;

echo "Generating invoices...\n";
echo "----------------------------------------\n";

foreach ($vatgroups as $vatgroupId => $vatgroup) {
    $vgArticles = $articlesByVatgroup[$vatgroupId] ?? [];
    if (empty($vgArticles)) {
        echo "[Vatgroup: {$vatgroup->name}] SKIPPED - No articles\n";
        continue;
    }

    foreach ($regions as $regionKey => $regionData) {
        $customers = $regionData['customers'];
        if (empty($customers)) {
            echo "[Vatgroup: {$vatgroup->name}] [Region: {$regionData['name']}] SKIPPED - No customers\n";
            continue;
        }

        for ($i = 1; $i <= 4; $i++) {
            $customer = $customers[array_rand($customers)];
            $address = new Address($customer->id);
            if (!$address || !$address->id) { $errors++; continue; }

            $vatarea = getVatArea($address, $db);
            $vatrate = $vatrates[$vatgroupId][$vatarea] ?? null;
            $vatrateValue = $vatrate ? floatval($vatrate->rate) : 20.0;
            $fibuCostAccount = $vatrate ? intval($vatrate->account) : null;
            $fibuCostAccountLegacy = $vatrate ? ($vatrate->legacy_account ? intval($vatrate->legacy_account) : null) : null;
            $fibuTaxcode = $vatrate ? ($vatrate->taxcode ? intval($vatrate->taxcode) : null) : null;
            $taxText = $vatrate ? ($vatrate->invoice_text ?? '') : '';

            $numPositions = rand(1, 5);
            shuffle($vgArticles);
            $selectedArticles = array_slice($vgArticles, 0, min($numPositions, count($vgArticles)));
            $totalDiscount = rand(0, 1) ? rand(0, 10) : 0;

            $positions = [];
            foreach ($selectedArticles as $article) {
                $amount = rand(1, 10);
                $price = getArticlePrice($article);
                $discount = rand(0, 1) ? rand(0, 15) : 0;
                $priceTotal = $amount * $price * (1 - $discount / 100);
                $priceTotalAfterDiscount = $priceTotal * (1 - $totalDiscount / 100);
                $priceGross = $priceTotalAfterDiscount * (1 + $vatrateValue / 100);

                $positions[] = [
                    'warehousearticle_id' => $article->id,
                    'warehousearticle_name' => $article->articleNumber . ' | ' . $article->title,
                    'product_info' => $article->description ?? '',
                    'amount' => $amount,
                    'unit' => $article->unit ?: 'Stk.',
                    'price' => $price,
                    'discount' => $discount,
                    'vatrate' => $vatrateValue,
                    'price_total' => $priceTotal,
                    'price_gross' => $priceGross,
                    'fibu_cost_account' => $fibuCostAccount,
                    'fibu_cost_account_legacy' => $fibuCostAccountLegacy,
                    'fibu_taxcode' => $fibuTaxcode
                ];
            }

            $randomDaysAgo = rand(0, 60);
            $invoiceDate = strtotime("-{$randomDaysAgo} days");

            $invoiceData = [
                'invoice_date' => $invoiceDate,
                'owner_id' => $address->id,
                'billingaddress_id' => $address->id,
                'customer_number' => $address->customer_number ?: 0,
                'company' => $address->company ?: '',
                'firstname' => $address->firstname ?: '',
                'lastname' => $address->lastname ?: '',
                'street' => $address->street ?: '',
                'zip' => $address->zip ?: '',
                'city' => $address->city ?: '',
                'country' => 'Österreich',
                'email' => $address->email ?: '',
                'uid' => $address->uid ?: '',
                'fibu_account_number' => $address->fibu_account_number ?: 0,
                'fibu_payment_due' => $address->fibu_payment_due ?: 14,
                'fibu_payment_skonto' => $address->fibu_payment_skonto ?: 0,
                'fibu_payment_skonto_rate' => $address->fibu_payment_skonto_rate ?: 0,
                'fibu_cost_area' => $vatarea,
                'fibu_cost_account' => $fibuCostAccount,
                'fibu_cost_account_legacy' => $fibuCostAccountLegacy,
                'fibu_taxcode' => $fibuTaxcode,
                'billing_type' => $address->billing_type ?: 'invoice',
                'bank_account_bank' => $address->bank_account_bank ?: '',
                'bank_account_owner' => $address->bank_account_owner ?: '',
                'bank_account_iban' => $address->bank_account_iban ?: '',
                'bank_account_bic' => $address->bank_account_bic ?: '',
                'sepa_date' => $address->sepa_date ? (is_numeric($address->sepa_date) ? date('Y-m-d', $address->sepa_date) : $address->sepa_date) : null,
                'performance_period' => date('m/Y', $invoiceDate),
                'introductory_text' => "Test invoice for '{$vatgroup->name}' in '{$regionData['name']}'",
                'external_reference' => "TEST-VG{$vatgroupId}-{$regionKey}-{$i}",
                'total_discount' => $totalDiscount,
                'vatgroup_id' => $vatgroupId,
                'tax_text' => $taxText
            ];

            $result = createInvoice($db, $invoiceData);
            $invoiceId = $result['id'];
            $invoiceNumber = $result['invoice_number'];

            if (!$invoiceId) { echo "[Vatgroup: {$vatgroup->name}] [Region: {$regionData['name']}] Invoice #{$i}: ERROR\n"; $errors++; continue; }

            $total = 0;
            $totalGross = 0;
            foreach ($positions as $pos) {
                createPosition($db, $invoiceId, $pos);
                $total += $pos['price_total'];
                $totalGross += $pos['price_gross'];
            }

            $totalAfterRabatt = $total * (1 - $totalDiscount / 100);
            $db->query("UPDATE ManualInvoice SET total = " . round($totalAfterRabatt, 2) . ", total_gross = " . round($totalGross, 2) . " WHERE id = $invoiceId");

            $journalText = $db->escape("Test invoice created (Vatgroup: {$vatgroup->name}, Region: {$regionData['name']})");
            $db->query("INSERT INTO ManualInvoiceJournal (manualinvoiceId, text, statusChange, createBy, `create`) VALUES ($invoiceId, '$journalText', 'erstellt', 1, " . time() . ")");

            $customerName = trim(($address->company ?: '') . ' ' . ($address->firstname ?: '') . ' ' . ($address->lastname ?: '')) ?: 'Unknown';
            echo "[{$vatgroup->name}] [{$regionData['name']}] #{$i}: {$invoiceNumber} - {$customerName} - " . count($positions) . " pos - " . number_format($totalAfterRabatt, 2) . " EUR\n";
            $totalCreated++;
        }
    }
}

echo "\n----------------------------------------\n";
echo "Creating mixed VAT group invoices...\n";
echo "----------------------------------------\n";

foreach ($regions as $regionKey => $regionData) {
    $customers = $regionData['customers'];
    if (empty($customers)) { echo "[Mixed] [{$regionData['name']}] SKIPPED - No customers\n"; continue; }

    for ($i = 1; $i <= 4; $i++) {
        $customer = $customers[array_rand($customers)];
        $address = new Address($customer->id);
        if (!$address || !$address->id) { $errors++; continue; }

        $vatarea = getVatArea($address, $db);
        $positions = [];
        $usedVatgroups = [];

        foreach ($articlesByVatgroup as $vgId => $vgArticles) {
            if (empty($vgArticles)) continue;
            $article = $vgArticles[array_rand($vgArticles)];
            $articleVatrate = $vatrates[$vgId][$vatarea] ?? null;
            $posVatrateValue = $articleVatrate ? floatval($articleVatrate->rate) : 20.0;
            $posFibuCostAccount = $articleVatrate ? intval($articleVatrate->account) : null;
            $posFibuCostAccountLegacy = $articleVatrate ? ($articleVatrate->legacy_account ? intval($articleVatrate->legacy_account) : null) : null;
            $posFibuTaxcode = $articleVatrate ? ($articleVatrate->taxcode ? intval($articleVatrate->taxcode) : null) : null;

            $amount = rand(1, 5);
            $price = getArticlePrice($article);
            $discount = rand(0, 1) ? rand(0, 10) : 0;
            $priceTotal = $amount * $price * (1 - $discount / 100);
            $priceGross = $priceTotal * (1 + $posVatrateValue / 100);

            $positions[] = [
                'warehousearticle_id' => $article->id,
                'warehousearticle_name' => $article->articleNumber . ' | ' . $article->title,
                'product_info' => $article->description ?? '',
                'amount' => $amount,
                'unit' => $article->unit ?: 'Stk.',
                'price' => $price,
                'discount' => $discount,
                'vatrate' => $posVatrateValue,
                'price_total' => $priceTotal,
                'price_gross' => $priceGross,
                'fibu_cost_account' => $posFibuCostAccount,
                'fibu_cost_account_legacy' => $posFibuCostAccountLegacy,
                'fibu_taxcode' => $posFibuTaxcode
            ];
            $usedVatgroups[] = $vgId;
        }

        if (count($positions) < 2) { echo "[Mixed] [{$regionData['name']}] #{$i}: SKIPPED - Not enough vatgroups\n"; continue; }

        $primaryVatgroupId = $usedVatgroups[0];
        $primaryVatrate = $vatrates[$primaryVatgroupId][$vatarea] ?? null;
        $fibuCostAccount = $primaryVatrate ? intval($primaryVatrate->account) : null;
        $fibuCostAccountLegacy = $primaryVatrate ? ($primaryVatrate->legacy_account ? intval($primaryVatrate->legacy_account) : null) : null;
        $fibuTaxcode = $primaryVatrate ? ($primaryVatrate->taxcode ? intval($primaryVatrate->taxcode) : null) : null;
        $taxText = $primaryVatrate ? ($primaryVatrate->invoice_text ?? '') : '';

        $randomDaysAgo = rand(0, 60);
        $invoiceDate = strtotime("-{$randomDaysAgo} days");
        $totalDiscount = 0;

        $vatgroupNames = array_map(fn($vgId) => $vatgroups[$vgId]->name ?? "VG$vgId", $usedVatgroups);

        $invoiceData = [
            'invoice_date' => $invoiceDate,
            'owner_id' => $address->id,
            'billingaddress_id' => $address->id,
            'customer_number' => $address->customer_number ?: 0,
            'company' => $address->company ?: '',
            'firstname' => $address->firstname ?: '',
            'lastname' => $address->lastname ?: '',
            'street' => $address->street ?: '',
            'zip' => $address->zip ?: '',
            'city' => $address->city ?: '',
            'country' => 'Österreich',
            'email' => $address->email ?: '',
            'uid' => $address->uid ?: '',
            'fibu_account_number' => $address->fibu_account_number ?: 0,
            'fibu_payment_due' => $address->fibu_payment_due ?: 14,
            'fibu_payment_skonto' => $address->fibu_payment_skonto ?: 0,
            'fibu_payment_skonto_rate' => $address->fibu_payment_skonto_rate ?: 0,
            'fibu_cost_area' => $vatarea,
            'fibu_cost_account' => $fibuCostAccount,
            'fibu_cost_account_legacy' => $fibuCostAccountLegacy,
            'fibu_taxcode' => $fibuTaxcode,
            'billing_type' => $address->billing_type ?: 'invoice',
            'bank_account_bank' => $address->bank_account_bank ?: '',
            'bank_account_owner' => $address->bank_account_owner ?: '',
            'bank_account_iban' => $address->bank_account_iban ?: '',
            'bank_account_bic' => $address->bank_account_bic ?: '',
            'sepa_date' => $address->sepa_date ? (is_numeric($address->sepa_date) ? date('Y-m-d', $address->sepa_date) : $address->sepa_date) : null,
            'performance_period' => date('m/Y', $invoiceDate),
            'introductory_text' => "Mixed VAT group test (" . implode(' + ', $vatgroupNames) . ") in '{$regionData['name']}'",
            'external_reference' => "TEST-MIXED-{$regionKey}-{$i}",
            'total_discount' => $totalDiscount,
            'vatgroup_id' => $primaryVatgroupId,
            'tax_text' => $taxText
        ];

        $result = createInvoice($db, $invoiceData);
        $invoiceId = $result['id'];
        $invoiceNumber = $result['invoice_number'];

        if (!$invoiceId) { echo "[Mixed] [{$regionData['name']}] #{$i}: ERROR\n"; $errors++; continue; }

        $total = 0;
        $totalGross = 0;
        foreach ($positions as $pos) {
            createPosition($db, $invoiceId, $pos);
            $total += $pos['price_total'];
            $totalGross += $pos['price_gross'];
        }

        $db->query("UPDATE ManualInvoice SET total = " . round($total, 2) . ", total_gross = " . round($totalGross, 2) . " WHERE id = $invoiceId");

        $journalText = $db->escape("Mixed VAT group test invoice (" . implode(', ', $vatgroupNames) . ", {$regionData['name']})");
        $db->query("INSERT INTO ManualInvoiceJournal (manualinvoiceId, text, statusChange, createBy, `create`) VALUES ($invoiceId, '$journalText', 'erstellt', 1, " . time() . ")");

        $customerName = trim(($address->company ?: '') . ' ' . ($address->firstname ?: '') . ' ' . ($address->lastname ?: '')) ?: 'Unknown';
        echo "[Mixed] [{$regionData['name']}] #{$i}: {$invoiceNumber} - {$customerName} - " . count($positions) . " pos - " . number_format($total, 2) . " EUR\n";
        $totalCreated++;
    }
}

echo "\n========================================\n";
echo "Complete! Created: {$totalCreated} invoices, Errors: {$errors}\n";
echo "========================================\n";