<?php
/**
 * VAT Return — reconciles Output VAT (account 8110) against Input VAT
 * (account 8210) for a period, cross-referenced against the source
 * receipts (customer EFD receipts) and payments (supplier EFD bills).
 *
 * Formula (Tanzania VAT return):
 *   Net VAT payable to TRA = Output VAT − Input VAT
 * If negative → refundable / credit brought forward.
 */
class VatReturnController extends Controller
{
    public function indexAction()
    {
        $from = (string)($_GET['from'] ?? date('Y-m-01'));  // default: month-to-date
        $to   = (string)($_GET['to']   ?? date('Y-m-d'));

        $db = new DB();

        // Output VAT (from account 8110 credits within period) --------------
        $outputLines = $db->mySelect(
            "SELECT je.c0d AS je_id, je.je_no, je.entry_date, je.source_type, je.source_ref,
                    je.narrative AS je_narr, jl.credit AS vat_credit, jl.narrative AS line_narr
               FROM journal_lines jl
               JOIN journal_entries je ON je.c0d = jl.journal_entry_id
              WHERE jl.account_code = '8110'
                AND je.deleted = 0 AND je.status = 'POSTED'
                AND je.entry_date BETWEEN :f AND :t
              ORDER BY je.entry_date ASC, je.c0d ASC",
            [':f' => $from, ':t' => $to]
        ) ?: [];
        $outputVat = 0.0; foreach ($outputLines as $l) $outputVat += (float)$l['vat_credit'];

        // Input VAT (from account 8210 debits within period) ----------------
        $inputLines = $db->mySelect(
            "SELECT je.c0d AS je_id, je.je_no, je.entry_date, je.source_type, je.source_ref,
                    je.narrative AS je_narr, jl.debit AS vat_debit, jl.narrative AS line_narr,
                    jl.lpo_id, jl.payment_id
               FROM journal_lines jl
               JOIN journal_entries je ON je.c0d = jl.journal_entry_id
              WHERE jl.account_code = '8210'
                AND je.deleted = 0 AND je.status = 'POSTED'
                AND je.entry_date BETWEEN :f AND :t
              ORDER BY je.entry_date ASC, je.c0d ASC",
            [':f' => $from, ':t' => $to]
        ) ?: [];
        $inputVat = 0.0; foreach ($inputLines as $l) $inputVat += (float)$l['vat_debit'];

        // EFD receipt reconciliation — sum of receipts.efd_output_vat -------
        $efdRcp = $db->mySelect(
            "SELECT c0d, receipt_no, receipt_date, receipt_type, customer_name, amount, efd_output_vat,
                    efd_serial, efd_receipt_number, efd_z_number
               FROM receipts
              WHERE deleted = 0
                AND receipt_type = 'EFD_VAT'
                AND receipt_date BETWEEN :f AND :t
              ORDER BY receipt_date ASC, c0d ASC",
            [':f' => $from, ':t' => $to]
        ) ?: [];
        $efdVatSum = 0.0; foreach ($efdRcp as $r) $efdVatSum += (float)$r['efd_output_vat'];

        // Supplier EFD payables reconciliation -----------------------------
        $supEfd = $db->mySelect(
            "SELECT l.c0d, l.lpo_no, l.payee_name, l.supplier_efd_number, l.supplier_tin,
                    l.supplier_efd_date, l.net_amount, l.vat_amount, l.amount, l.supplier_receipt_type,
                    l.cash_out_status
               FROM lpos l
              WHERE l.deleted = 0
                AND l.supplier_receipt_type = 'SUPPLIER_EFD_VAT'
                AND l.vat_amount > 0
                AND (l.supplier_efd_date BETWEEN :f AND :t OR (l.supplier_efd_date IS NULL AND l.edited_on BETWEEN :f AND :t2))
              ORDER BY l.supplier_efd_date ASC, l.c0d ASC",
            [':f' => $from, ':t' => $to, ':t2' => $to . ' 23:59:59']
        ) ?: [];
        $supVatSum = 0.0; foreach ($supEfd as $r) $supVatSum += (float)$r['vat_amount'];

        // Reconciliation warnings -----------------------------------------
        $warnings = [];
        if (abs($outputVat - $efdVatSum) > 0.5) {
            $warnings[] = 'Output VAT ledger (' . number_format($outputVat) . ') does not match sum of EFD receipts (' . number_format($efdVatSum) . '). Missing EFD receipts or unposted invoices.';
        }
        if (abs($inputVat - $supVatSum) > 0.5) {
            $warnings[] = 'Input VAT ledger (' . number_format($inputVat) . ') does not match sum of supplier EFD tax invoices (' . number_format($supVatSum) . '). Some captured supplier VAT may not be paid yet.';
        }

        $this->view->setLayout($this->roleLayout());
        $this->view->render('VatReturn/index', [
            'page_title'    => 'VAT Return',
            'from'          => $from,
            'to'            => $to,
            'output_vat'    => $outputVat,
            'input_vat'     => $inputVat,
            'net_payable'   => round($outputVat - $inputVat, 2),
            'output_lines'  => $outputLines,
            'input_lines'   => $inputLines,
            'efd_receipts'  => $efdRcp,
            'sup_efd_bills' => $supEfd,
            'efd_vat_sum'   => $efdVatSum,
            'sup_vat_sum'   => $supVatSum,
            'warnings'      => $warnings,
        ]);
    }
}
