<?php $this->start('body');
    $banks = $this->data['banks'] ?? [];
?>

<div class="d-flex align-items-center justify-content-between mb-3">
    <div>
        <h5 class="mb-0" style="font-weight:600;color:#4a3f36;"><i class="fas fa-file-import me-2" style="color:#c9a875;"></i>Import Bank Statement</h5>
        <div class="text-muted small mt-1" style="color:#7a6f63;">Upload an Excel (.xlsx / .xls) file. First row is treated as headers.</div>
    </div>
    <a href="<?= PROOT; ?>BankRec" class="btn btn-sm" style="background:#faf6f0;border:1px solid #eae0d0;color:#4a3f36;">
        <i class="fas fa-arrow-left me-1"></i>Back
    </a>
</div>

<!-- ── Column mapping guidance ──────────────────────────────────────────── -->
<div class="alert py-2 mb-3" style="background:#f5efe6;border:1px solid #eae0d0;color:#4a3f36;">
    <i class="fas fa-info-circle me-1" style="color:#c9a875;"></i>
    Expected columns (headers in row 1, in this order):
    <strong>Date</strong> &middot;
    <strong>Description</strong> &middot;
    <strong>Reference</strong> &middot;
    <strong>Debit</strong> &middot;
    <strong>Credit</strong> &middot;
    <strong>Balance</strong> (optional)
</div>

<div class="row g-3">
    <div class="col-md-5">
        <div class="card border-0 shadow-sm h-100" style="background:#faf6f0;">
            <div class="card-header bg-white" style="border-bottom:1px solid #eae0d0;">
                <strong style="color:#4a3f36;"><i class="fas fa-cog me-2" style="color:#c9a875;"></i>Statement details</strong>
            </div>
            <div class="card-body p-3">
                <div class="mb-2">
                    <label class="form-label" style="color:#4a3f36;">Bank account</label>
                    <select id="bank_account_id" class="form-select form-select-sm">
                        <?php foreach ($banks as $b): ?>
                            <option value="<?= (int)$b['c0d']; ?>"><?= htmlspecialchars($b['account_code'] . ' — ' . $b['account_name']); ?></option>
                        <?php endforeach; ?>
                        <?php if (empty($banks)): ?><option value="0">(no bank COAs — set is_bank=1 in Chart of Accounts)</option><?php endif; ?>
                    </select>
                </div>
                <div class="mb-2">
                    <label class="form-label" style="color:#4a3f36;">Statement date</label>
                    <input type="date" id="statement_date" class="form-control form-control-sm" value="<?= date('Y-m-d'); ?>">
                </div>
                <div class="row g-2 mb-2">
                    <div class="col-6"><label class="form-label" style="color:#4a3f36;">Opening balance</label>
                        <input type="number" step="0.01" id="opening_balance" class="form-control form-control-sm" value="0"></div>
                    <div class="col-6"><label class="form-label" style="color:#4a3f36;">Closing balance</label>
                        <input type="number" step="0.01" id="closing_balance" class="form-control form-control-sm" value="0"></div>
                </div>
                <div class="mb-2">
                    <label class="form-label" style="color:#4a3f36;">Excel file</label>
                    <input type="file" id="xlsx_file" accept=".xlsx,.xls,.csv" class="form-control form-control-sm">
                </div>
                <button type="button" id="btn_preview" class="btn btn-sm mt-2 w-100" style="background:#c9a875;border-color:#c9a875;color:#4a3f36;font-weight:600;">
                    <i class="fas fa-eye me-1"></i>Parse &amp; preview
                </button>
                <button type="button" id="btn_import" class="btn btn-sm mt-2 w-100" disabled style="background:#7a9e6e;border-color:#7a9e6e;color:#fff;font-weight:600;">
                    <i class="fas fa-cloud-upload-alt me-1"></i>Import into system
                </button>
            </div>
        </div>
    </div>

    <div class="col-md-7">
        <div class="card border-0 shadow-sm h-100">
            <div class="card-header bg-white d-flex align-items-center justify-content-between" style="border-bottom:1px solid #eae0d0;">
                <strong style="color:#4a3f36;"><i class="fas fa-table me-2" style="color:#c9a875;"></i>Preview</strong>
                <span id="row_count" class="text-muted small" style="color:#7a6f63;">No file loaded</span>
            </div>
            <div id="preview_wrap" class="table-responsive" style="max-height:520px;overflow:auto;">
                <table class="table table-sm mb-0 align-middle">
                    <thead style="background:#f5efe6;position:sticky;top:0;">
                        <tr>
                            <th style="color:#4a3f36;">#</th>
                            <th style="color:#4a3f36;">Date</th>
                            <th style="color:#4a3f36;">Description</th>
                            <th style="color:#4a3f36;">Reference</th>
                            <th class="text-end" style="color:#4a3f36;">Debit</th>
                            <th class="text-end" style="color:#4a3f36;">Credit</th>
                            <th class="text-end" style="color:#4a3f36;">Balance</th>
                        </tr>
                    </thead>
                    <tbody id="preview_body">
                        <tr><td colspan="7" class="text-center py-4" style="color:#a89a8a;">Choose a file above and click <em>Parse &amp; preview</em>.</td></tr>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</div>

<?php $this->end(); ?>

<?php $this->start('section_js'); ?>
<script src="<?= PROOT; ?>js/Olibs/xlsx.full.min.js"></script>
<script>
(function () {
    var PROOT = '<?= PROOT; ?>';
    var parsed = []; // array of {txn_date, description, reference, debit, credit, running_balance}

    // Excel serial date → YYYY-MM-DD
    function excelDateToIso(v) {
        if (v == null || v === '') return '';
        if (typeof v === 'number' && v > 0 && v < 100000) {
            // SheetJS: use XLSX.SSF.parse_date_code
            var o = XLSX.SSF.parse_date_code(v);
            if (o && o.y) {
                return o.y + '-' + String(o.m).padStart(2, '0') + '-' + String(o.d).padStart(2, '0');
            }
        }
        if (typeof v === 'string') {
            var s = v.trim();
            // Try Date.parse for common formats
            var dt = new Date(s);
            if (!isNaN(dt.getTime())) {
                return dt.getFullYear() + '-' + String(dt.getMonth() + 1).padStart(2, '0') + '-' + String(dt.getDate()).padStart(2, '0');
            }
        }
        if (v instanceof Date) {
            return v.getFullYear() + '-' + String(v.getMonth() + 1).padStart(2, '0') + '-' + String(v.getDate()).padStart(2, '0');
        }
        return '';
    }

    function num(v) {
        if (v == null || v === '') return 0;
        if (typeof v === 'number') return v;
        var s = String(v).replace(/[,\s]/g, '');
        var n = parseFloat(s);
        return isNaN(n) ? 0 : n;
    }

    function fmt(n) { return n === 0 ? '—' : n.toLocaleString(); }

    function renderPreview(rows) {
        var $body = $('#preview_body').empty();
        if (!rows.length) {
            $body.append('<tr><td colspan="7" class="text-center py-4" style="color:#a89a8a;">No data rows found.</td></tr>');
            return;
        }
        rows.forEach(function (r, i) {
            var invalid = !r.txn_date;
            $body.append(
                '<tr' + (invalid ? ' style="background:#f8e6e0;"' : '') + '>' +
                    '<td class="text-muted small" style="color:#7a6f63;">' + (i + 1) + '</td>' +
                    '<td style="color:' + (invalid ? '#b56b58' : '#4a3f36') + ';">' + (r.txn_date || '⚠ invalid date') + '</td>' +
                    '<td class="text-muted small" style="color:#4a3f36;max-width:220px;overflow:hidden;text-overflow:ellipsis;white-space:nowrap;" title="' + $('<div/>').text(r.description || '').html() + '">' + $('<div/>').text(r.description || '').html() + '</td>' +
                    '<td class="text-muted small" style="color:#7a6f63;">' + $('<div/>').text(r.reference || '').html() + '</td>' +
                    '<td class="text-end" style="color:' + (r.debit > 0 ? '#8a4a3a' : '#a89a8a') + ';">' + fmt(r.debit) + '</td>' +
                    '<td class="text-end" style="color:' + (r.credit > 0 ? '#4a6b3f' : '#a89a8a') + ';">' + fmt(r.credit) + '</td>' +
                    '<td class="text-end text-muted small" style="color:#7a6f63;">' + (r.running_balance != null ? r.running_balance.toLocaleString() : '—') + '</td>' +
                '</tr>'
            );
        });
    }

    $('#btn_preview').on('click', function () {
        var file = document.getElementById('xlsx_file').files[0];
        if (!file) { rsToast('Choose an Excel file first.', 'warning'); return; }
        var reader = new FileReader();
        reader.onload = function (e) {
            try {
                var data = new Uint8Array(e.target.result);
                var wb = XLSX.read(data, { type: 'array', cellDates: true });
                var sheetName = wb.SheetNames[0];
                var sh = wb.Sheets[sheetName];
                var arr = XLSX.utils.sheet_to_json(sh, { header: 1, blankrows: false, raw: false, dateNF: 'yyyy-mm-dd' });
                if (arr.length < 2) { rsToast('Sheet is empty or missing data rows.', 'error'); return; }

                // arr[0] is header. Data starts at arr[1].
                var out = [];
                for (var i = 1; i < arr.length; i++) {
                    var row = arr[i];
                    if (!row || row.every(function (c) { return c == null || c === ''; })) continue;
                    out.push({
                        txn_date:        excelDateToIso(row[0]),
                        description:     String(row[1] || '').trim(),
                        reference:       String(row[2] || '').trim(),
                        debit:           num(row[3]),
                        credit:          num(row[4]),
                        running_balance: row[5] != null && row[5] !== '' ? num(row[5]) : null,
                    });
                }
                parsed = out;
                $('#row_count').text(parsed.length + ' row(s) parsed from ' + file.name);
                renderPreview(parsed);

                var invalidCount = parsed.filter(function (r) { return !r.txn_date; }).length;
                if (invalidCount > 0) {
                    rsToast(invalidCount + ' row(s) have invalid dates — fix in Excel before importing.', 'warning', 4500);
                    $('#btn_import').prop('disabled', true);
                } else {
                    rsToast('Parsed ' + parsed.length + ' row(s). Review and click Import.', 'success');
                    $('#btn_import').prop('disabled', false);
                }
            } catch (err) {
                rsToast('Failed to parse: ' + err.message, 'error', 5000);
            }
        };
        reader.readAsArrayBuffer(file);
    });

    $('#btn_import').on('click', function () {
        if (!parsed.length) return;
        var bankId = parseInt($('#bank_account_id').val(), 10) || 0;
        var stDate = $('#statement_date').val();
        if (bankId <= 0) { rsToast('Choose a bank account.', 'warning'); return; }
        if (!stDate)     { rsToast('Set the statement date.', 'warning'); return; }

        $.confirm({
            title: 'Import statement?',
            content: parsed.length + ' line(s) will be created for reconciliation. Continue?',
            type: 'orange',
            buttons: {
                yes: {
                    text: 'Import',
                    btnClass: 'btn-warning',
                    action: function () {
                        var $btn = $('#btn_import').prop('disabled', true).html('<i class="fas fa-spinner fa-spin me-1"></i>Importing…');
                        $.post(PROOT + 'BankRec/importAj', {
                            bank_account_id: bankId,
                            statement_date:  stDate,
                            opening_balance: $('#opening_balance').val() || 0,
                            closing_balance: $('#closing_balance').val() || 0,
                            filename:        document.getElementById('xlsx_file').files[0]?.name || 'statement.xlsx',
                            lines_json:      JSON.stringify(parsed),
                        }, 'json')
                        .done(function (res) {
                            if (res && res.ok) {
                                rsToast('Imported ' + res.lines + ' line(s). Redirecting…', 'success');
                                setTimeout(function () { location.href = PROOT + 'BankRec/view/' + res.statement_id; }, 700);
                            } else {
                                rsToast('Import failed: ' + (res.msg || 'unknown'), 'error', 5000);
                                $btn.prop('disabled', false).html('<i class="fas fa-cloud-upload-alt me-1"></i>Import into system');
                            }
                        })
                        .fail(function () {
                            rsToast('Server unreachable.', 'error', 5000);
                            $btn.prop('disabled', false).html('<i class="fas fa-cloud-upload-alt me-1"></i>Import into system');
                        });
                    }
                },
                cancel: { text: 'Cancel', btnClass: 'btn-light' }
            }
        });
    });
})();
</script>
<?php $this->end(); ?>
