<?php
// finance_vat_data.php — VAT backend: HTML tables, comparison, CSV
// Tenant-aware include
if (file_exists('../../../../' . ($_REQUEST['tenant'] ?? '') . '/config.php')) {
    require_once('../../../../' . $_REQUEST['tenant'] . '/config.php');
}
if (file_exists('/config.php')) {
    require_once('/config.php');
}
$DB = isset($con) ? $con : ($GLOBALS['con'] ?? null);
if (!$DB) {
    header('Content-Type: text/html; charset=UTF-8');
    echo '<div class="alert alert-danger">Database connection not available.</div>';
    exit;
}

// Inputs
$action = $_POST['action'] ?? $_GET['action'] ?? 'table';      // table | csv
$mode   = $_POST['mode']   ?? $_GET['mode']   ?? 'in';         // in | out | compare
$startS = trim($_POST['start'] ?? $_GET['start'] ?? '');
$endS   = trim($_POST['end']   ?? $_GET['end']   ?? '');

// Parse dd-mm-yyyy to timestamps (inclusive end)
$start = parse_dmy_to_ts($startS, true);
$end   = parse_dmy_to_ts($endS, false); // end-of-day
if (!$start || !$end) {
    if ($action === 'csv') {
        header('Content-Type: text/plain; charset=UTF-8');
        echo "Invalid date range";
    } else {
        echo '<div class="alert alert-danger">Invalid date range.</div>';
    }
    exit;
}

// Dispatch
switch ($action) {
    case 'csv':
        output_csv($DB, $mode, $start, $end);
        break;
    case 'table':
    default:
        output_html($DB, $mode, $start, $end, $startS, $endS);
        break;
}

// ===== Helpers =====

function parse_dmy_to_ts(string $dmy, bool $startOfDay): ?int {
    $dmy = str_replace('/', '-', trim($dmy));
    if (!preg_match('/^(\d{1,2})-(\d{1,2})-(\d{4})$/', $dmy, $m)) return null;
    $d = (int)$m[1]; $mo = (int)$m[2]; $y = (int)$m[3];
    if (!checkdate($mo, $d, $y)) return null;
    return $startOfDay ? mktime(0,0,0,$mo,$d,$y) : mktime(23,59,59,$mo,$d,$y);
}

function totals_from_string(?string $s): array {
    $parts = explode('|', $s ?? '0|0');
    $net = (float)str_replace(',', '', $parts[0] ?? '0');
    $vat = (float)str_replace(',', '', $parts[1] ?? '0');
    return [$net, $vat];
}

function fmt_money(float $n): string {
    return '£' . number_format($n, 2);
}

function fetch_in(mysqli $DB, int $start, int $end): array {
    // Receipts: paid invoices in range of date_paid
    $sql = "SELECT invNo, client, items, totals, date_paid, chequeNo
            FROM invoices
            WHERE status=3 AND date_paid>=? AND date_paid<=?
            ORDER BY ABS(invNo) ASC";
    $stmt = $DB->prepare($sql);
    $stmt->bind_param('ii', $start, $end);
    $stmt->execute();
    $res = $stmt->get_result();
    $rows = [];
    while ($r = $res->fetch_assoc()) {
        [$net, $vat] = totals_from_string($r['totals']);
        // Build DETAILS from items list if available
        $details = '';
        $items = @unserialize($r['items']);
        if (is_array($items)) {
            foreach ($items as $it) { $details .= trim(($it['service'] ?? '')) . ', '; }
            $details = rtrim($details, ', ');
        }
        $rows[] = [
            'date'   => (int)$r['date_paid'],
            'payee'  => (string)$r['client'], // can be swapped to getClientName(...) later
            'details'=> $details,
            'chq'    => (string)($r['chequeNo'] ?? ''),
            'net'    => $net,
            'vat'    => $vat
        ];
    }
    $stmt->close();
    return $rows;
}

function fetch_out(mysqli $DB, int $start, int $end): array {
    // Expenditure: expenses/outgoings with VAT in date range
    $sql = "SELECT id, payment_type, `desc`, chequeNo, totals, `date`
            FROM expenses
            WHERE (`payment_type`='Expenses' OR `payment_type`='Outgoings')
              AND `date`>=? AND `date`<=?
            ORDER BY ABS(id) ASC";
    $stmt = $DB->prepare($sql);
    $stmt->bind_param('ii', $start, $end);
    $stmt->execute();
    $res = $stmt->get_result();
    $rows = [];
    while ($r = $res->fetch_assoc()) {
        [$net, $vat] = totals_from_string($r['totals']);
        if ($vat <= 0) continue; // ignore non-VAT rows
        $rows[] = [
            'date'   => (int)$r['date'],
            'type'   => (string)$r['payment_type'],
            'details'=> (string)$r['desc'],
            'chq'    => (string)($r['chequeNo'] ?? ''),
            'net'    => $net,
            'vat'    => $vat
        ];
    }
    $stmt->close();
    return $rows;
}

function sum_rows(array $rows): array {
    $net = 0.0; $vat = 0.0;
    foreach ($rows as $r) { $net += (float)$r['net']; $vat += (float)$r['vat']; }
    return [$net, $vat];
}

function output_html(mysqli $DB, string $mode, int $start, int $end, string $startS, string $endS): void {
    header('Content-Type: text/html; charset=UTF-8');

    // Build payee id => name map once
    $payeeMap = [];
    $res = $DB->query("SELECT id, name FROM dd_payee");
    if ($res) {
        while ($row = $res->fetch_assoc()) {
            $payeeMap[(string)$row['id']] = $row['name'];
        }
        $res->free();
    }

    $getPayeeName = static function($payeeVal) use ($payeeMap): string {
        $key = (string)($payeeVal ?? '');
        return $payeeMap[$key] ?? (string)$payeeVal; // fallback if missing
    };

    if ($mode === 'compare') {
        $in  = fetch_in($DB, $start, $end);
        $out = fetch_out($DB, $start, $end);
        [$inNet, $inVat]   = sum_rows($in);
        [$outNet, $outVat] = sum_rows($out);

        echo '<div id="vatContent">';
        echo '<div class="row g-3 mb-3">';
        echo '  <div class="col-12"><p class="lead mb-0">VAT comparison for <strong>' . htmlspecialchars($startS) .
             '</strong> to <strong>' . htmlspecialchars($endS) . '</strong></p></div>';
        echo '  <div class="col-12 col-md-4"><div class="card"><div class="card-body">';
        echo '    <h6 class="mb-2">Inputs (Receipts)</h6>';
        echo '    <div>Ex-VAT: <strong>' . fmt_money($inNet) . '</strong></div>';
        echo '    <div>VAT: <strong>' . fmt_money($inVat) . '</strong></div>';
        echo '  </div></div></div>';
        echo '  <div class="col-12 col-md-4"><div class="card"><div class="card-body">';
        echo '    <h6 class="mb-2">Outputs (Expenditure)</h6>';
        echo '    <div>Ex-VAT: <strong>' . fmt_money($outNet) . '</strong></div>';
        echo '    <div>VAT: <strong>' . fmt_money($outVat) . '</strong></div>';
        echo '  </div></div></div>';
        echo '  <div class="col-12 col-md-4"><div class="card"><div class="card-body">';
        echo '    <h6 class="mb-2">Delta (In - Out)</h6>';
        echo '    <div>Ex-VAT: <strong>' . fmt_money($inNet - $outNet) . '</strong></div>';
        echo '    <div>VAT: <strong>' . fmt_money($inVat - $outVat) . '</strong></div>';
        echo '  </div></div></div>';
        echo '</div>';

        // Two compact tables
        echo '<div class="row g-3">';
        echo '  <div class="col-12 col-lg-6">';
        echo '    <div class="table-responsive"><table class="table table-sm table-striped">';
        echo '      <thead class="table-dark"><tr><th>DATE</th><th>PAYEE</th><th>DETAILS</th><th>CHQ NO</th><th>EX-VAT</th><th>VAT</th></tr></thead><tbody>';
        foreach ($in as $r) {
            $payeeName = $getPayeeName($r['payee'] ?? '');
            echo '<tr><td>'.date('d-m-Y',(int)$r['date']).'</td><td>'.htmlspecialchars($payeeName).'</td><td>'.htmlspecialchars($r['details']).'</td><td>'.htmlspecialchars($r['chq']).'</td><td>'.number_format((float)$r['net'],2).'</td><td>'.number_format((float)$r['vat'],2).'</td></tr>';
        }
        echo '      </tbody></table></div>';
        echo '  </div>';

        echo '  <div class="col-12 col-lg-6">';
        echo '    <div class="table-responsive"><table class="table table-sm table-striped">';
        echo '      <thead class="table-dark"><tr><th>DATE</th><th>TYPE</th><th>DETAILS</th><th>CHQ NO</th><th>EX-VAT</th><th>VAT</th></tr></thead><tbody>';
        foreach ($out as $r) {
            echo '<tr><td>'.date('d-m-Y',(int)$r['date']).'</td><td>'.htmlspecialchars($r['type']).'</td><td>'.htmlspecialchars($r['details']).'</td><td>'.htmlspecialchars($r['chq']).'</td><td>'.number_format((float)$r['net'],2).'</td><td>'.number_format((float)$r['vat'],2).'</td></tr>';
        }
        echo '      </tbody></table></div>';
        echo '  </div>';
        echo '</div>';
        echo '</div>';
        return;
    }

    if ($mode === 'out') {
        $rows = fetch_out($DB, $start, $end);
        [$net, $vat] = sum_rows($rows);
        echo '<div id="vatContent">';
        echo '<p class="lead">Outputs (Expenditure) for <strong>'.htmlspecialchars($startS).'</strong> to <strong>'.htmlspecialchars($endS).'</strong></p>';
        echo '<div class="table-responsive"><table class="table table-sm table-striped">';
        echo '<thead class="table-dark"><tr><th>DATE</th><th>TYPE</th><th>DETAILS</th><th>CHQ NO</th><th>EX-VAT</th><th>VAT</th></tr></thead><tbody>';
        foreach ($rows as $r) {
            echo '<tr><td>'.date('d-m-Y',(int)$r['date']).'</td><td>'.htmlspecialchars($r['type']).'</td><td>'.htmlspecialchars($r['details']).'</td><td>'.htmlspecialchars($r['chq']).'</td><td>'.number_format((float)$r['net'],2).'</td><td>'.number_format((float)$r['vat'],2).'</td></tr>';
        }
        echo '</tbody><tfoot class="table-dark">';
        echo '<tr><th colspan="4" class="text-end"></th><th>'.fmt_money($net).'</th><th>'.fmt_money($vat).'</th></tr>';
         echo '</tfoot></table></div></div>';
        return;
    }

    // default: 'in'
    $rows = fetch_in($DB, $start, $end);
    [$net, $vat] = sum_rows($rows);
    echo '<div id="vatContent">';
    echo '<p class="lead">Inputs (Receipts) for <strong>'.htmlspecialchars($startS).'</strong> to <strong>'.htmlspecialchars($endS).'</strong></p>';
    echo '<div class="table-responsive"><table class="table table-sm table-striped">';
    echo '<thead class="table-dark"><tr><th>DATE</th><th>PAYEE</th><th>DETAILS</th><th>CHQ NO</th><th>EX-VAT</th><th>VAT</th></tr></thead><tbody>';
    foreach ($rows as $r) {
        $payeeName = $getPayeeName($r['payee'] ?? '');
        echo '<tr><td>'.date('d-m-Y',(int)$r['date']).'</td><td>'.htmlspecialchars($payeeName).'</td><td>'.htmlspecialchars($r['details']).'</td><td>'.htmlspecialchars($r['chq']).'</td><td>'.number_format((float)$r['net'],2).'</td><td>'.number_format((float)$r['vat'],2).'</td></tr>';
    }
    echo '</tbody><tfoot class="table-dark">';
   echo '<tr><th colspan="4" class="text-end"></th><th>'.fmt_money($net).'</th><th>'.fmt_money($vat).'</th></tr>';
    echo '</tfoot></table></div></div>';
}


function output_csv(mysqli $DB, string $mode, int $start, int $end): void {
    $fn = 'vat_' . $mode . '_' . date('Ymd_His') . '.csv';
    header('Content-Type: text/csv; charset=UTF-8');
    header('Content-Disposition: attachment; filename="'.$fn.'"');

    $out = fopen('php://output', 'w');

    // Build payee id => name map once (for 'in' mode output)
    $payeeMap = [];
    $res = $DB->query("SELECT id, name FROM dd_payee");
    if ($res) {
        while ($row = $res->fetch_assoc()) {
            $payeeMap[(string)$row['id']] = $row['name'];
        }
        $res->free();
    }

    if ($mode === 'compare') {
        $in  = fetch_in($DB, $start, $end);
        $outRows = fetch_out($DB, $start, $end);
        [$inNet, $inVat] = sum_rows($in);
        [$outNet, $outVat] = sum_rows($outRows);

        fputcsv($out, ['Section','Ex-VAT','VAT']);
        fputcsv($out, ['Inputs', number_format($inNet,2), number_format($inVat,2)]);
        fputcsv($out, ['Outputs', number_format($outNet,2), number_format($outVat,2)]);
        fputcsv($out, ['Delta (In-Out)', number_format($inNet-$outNet,2), number_format($inVat-$outVat,2)]);
        fclose($out);
        return;
    }

    if ($mode === 'out') {
        fputcsv($out, ['DATE','TYPE','DETAILS','CHQ NO','EX-VAT','VAT']);
        $rows = fetch_out($DB, $start, $end);
        foreach ($rows as $r) {
            fputcsv($out, [
                date('d-m-Y', (int)$r['date']),
                $r['type'],
                $r['details'],
                $r['chq'],
                number_format((float)$r['net'], 2),
                number_format((float)$r['vat'], 2)
            ]);
        }
        fclose($out);
        return;
    }

    // default: 'in'
    fputcsv($out, ['DATE','PAYEE','DETAILS','CHQ NO','EX-VAT','VAT']);
    $rows = fetch_in($DB, $start, $end);
    foreach ($rows as $r) {
        $payeeId = isset($r['payee']) ? (string)$r['payee'] : '';
        $payeeName = $payeeMap[$payeeId] ?? $r['payee']; // fallback if missing

        fputcsv($out, [
            date('d-m-Y', (int)$r['date']),
            $payeeName,
            $r['details'],
            $r['chq'],
            number_format((float)$r['net'], 2),
            number_format((float)$r['vat'], 2)
        ]);
    }
    fclose($out);
}