const express = require('express');
const Database = require('better-sqlite3');
const { decrypt } = require('../utils/crypto');
const auth = require('../middleware/authMiddleware');

const router = express.Router();
const db = new Database('plusfit.db');

router.get('/export', auth, (req, res) => {

    const users = db.prepare(`
        SELECT
            u.*,
            v.betrag,
            v.name AS vertragsname
        FROM users u
        JOIN vertragsarten v
            ON u.vertragsvariante = v.id
        WHERE
            u.status = 'aktiv'          -- 🔐 WICHTIGSTER FILTER
            AND u.gesperrt = 0          -- optional, zusätzlich
            AND v.aktiv = 1
            AND u.iban IS NOT NULL
            AND u.mandatsreferenz IS NOT NULL
    `).all();

    if (users.length === 0) {
        return res.send('Keine aktiven SEPA-Lastschriften vorhanden');
    }

    const now = new Date();
    const msgId = `PLUSFIT-${now.getTime()}`;
    const date = now.toISOString().slice(0, 10);

    const totalSum = users.reduce(
        (sum, u) => sum + Number(u.betrag),
        0
    );

    let xml = `<?xml version="1.0" encoding="UTF-8"?>
<Document xmlns="urn:iso:std:iso:20022:tech:xsd:pain.008.001.02">
<CstmrDrctDbtInitn>

<GrpHdr>
<MsgId>${msgId}</MsgId>
<CreDtTm>${now.toISOString()}</CreDtTm>
<NbOfTxs>${users.length}</NbOfTxs>
<CtrlSum>${totalSum.toFixed(2)}</CtrlSum>
<InitgPty>
<Nm>Plusfit</Nm>
</InitgPty>
</GrpHdr>

<PmtInf>
<PmtInfId>PMT-${date}</PmtInfId>
<PmtMtd>DD</PmtMtd>
<NbOfTxs>${users.length}</NbOfTxs>
<CtrlSum>${totalSum.toFixed(2)}</CtrlSum>

<PmtTpInf>
<SvcLvl><Cd>SEPA</Cd></SvcLvl>
<LclInstrm><Cd>CORE</Cd></LclInstrm>
<SeqTp>RCUR</SeqTp>
</PmtTpInf>

<ReqdColltnDt>${date}</ReqdColltnDt>

<Cdtr>
<Nm>Plusfit</Nm>
</Cdtr>

<CdtrAcct>
<Id><IBAN>DE12345678901234567890</IBAN></Id>
</CdtrAcct>

<CdtrAgt>
<FinInstnId><BIC>GENODEF1XXX</BIC></FinInstnId>
</CdtrAgt>

<ChrgBr>SLEV</ChrgBr>
`;

    users.forEach(u => {
        const iban = decrypt(u.iban);

        xml += `
<DrctDbtTxInf>
<PmtId>
<EndToEndId>${u.vertragsnummer}</EndToEndId>
</PmtId>

<InstdAmt Ccy="EUR">${Number(u.betrag).toFixed(2)}</InstdAmt>

<DrctDbtTx>
<MndtRltdInf>
<MndtId>${u.mandatsreferenz}</MndtId>
<DtOfSgntr>${date}</DtOfSgntr>
</MndtRltdInf>
</DrctDbtTx>

<Dbtr>
<Nm>${u.kontoinhaber || `${u.vorname} ${u.nachname}`}</Nm>
</Dbtr>

<DbtrAcct>
<Id><IBAN>${iban}</IBAN></Id>
</DbtrAcct>

<RmtInf>
<Ustrd>Mitgliedsbeitrag ${u.vertragsname}</Ustrd>
</RmtInf>
</DrctDbtTxInf>
`;
    });

    xml += `
</PmtInf>
</CstmrDrctDbtInitn>
</Document>`;

    res.setHeader('Content-Type', 'application/xml');
    res.setHeader('Content-Disposition', 'attachment; filename=plusfit_sepa.xml');
    res.send(xml);
});

module.exports = router;