collect/backend/server.js

// /home/def/IPFSapp/backend/server.js
// collect backend — ESM, exposes ETI token info and DB stats in /api/health

import 'dotenv/config';
import express from 'express';
import cors from 'cors';
import { Pool } from 'pg';
import { ethers } from 'ethers';

const {
  PORT = '4310',
  HOST = '0.0.0.0',
  DATABASE_URL,

  CHAIN = 'etica-mainnet',
  RPC_URL = 'https://rpc.etica-stats.org',
  REQUIRED_CONFIRMATIONS = '3',
  EGAZ_PER_ETI = '25',

  PAY_TO = '0xc4d275cf97d6a4cc29ede86d16976e808d264732',

  IPFS_API = 'http://127.0.0.1:5001/api/v0',
  PUBLIC_GATEWAY = 'https://ipfs.outsidethebox.top',

  CORS_ORIGIN,

  // ETI ERC-20
  ETI_TOKEN_ADDRESS = '',
  ETI_TOKEN_DECIMALS = '18'
} = process.env;

if (!DATABASE_URL) { console.error('Missing DATABASE_URL'); process.exit(1); }

const pool = new Pool({ connectionString: DATABASE_URL });
async function sql(q, p = []) { return pool.query(q, p); }
const toInt = (x, d = 0) => { const n = parseInt(x, 10); return Number.isFinite(n) ? n : d; };
const nowIso = () => new Date().toISOString();

async function migrate() {
  await sql('SELECT 1');
  await sql(`ALTER TABLE IF EXISTS payments ADD COLUMN IF NOT EXISTS confs              INTEGER DEFAULT 0`);
  await sql(`ALTER TABLE IF EXISTS payments ADD COLUMN IF NOT EXISTS required_confs     INTEGER DEFAULT 3`);
  await sql(`ALTER TABLE IF EXISTS payments ADD COLUMN IF NOT EXISTS mined_block        BIGINT`);
  await sql(`ALTER TABLE IF EXISTS payments ADD COLUMN IF NOT EXISTS latest_block       BIGINT`);
  await sql(`ALTER TABLE IF EXISTS payments ADD COLUMN IF NOT EXISTS tx_hash            TEXT`);
  await sql(`ALTER TABLE IF EXISTS payments ADD COLUMN IF NOT EXISTS cid                TEXT`);
  await sql(`ALTER TABLE IF EXISTS payments ADD COLUMN IF NOT EXISTS path               TEXT`);
  await sql(`ALTER TABLE IF EXISTS payments ADD COLUMN IF NOT EXISTS label              TEXT`);
  await sql(`ALTER TABLE IF EXISTS payments ADD COLUMN IF NOT EXISTS filename           TEXT`);
  await sql(`ALTER TABLE IF EXISTS payments ADD COLUMN IF NOT EXISTS size_bytes         BIGINT`);
  await sql(`ALTER TABLE IF EXISTS payments ADD COLUMN IF NOT EXISTS expires_at         TIMESTAMPTZ DEFAULT (now() + interval '30 minutes')`);
  await sql(`ALTER TABLE IF EXISTS payments ADD COLUMN IF NOT EXISTS vpath              TEXT`);

  await sql(`
    CREATE TABLE IF NOT EXISTS counters (
      doc        TEXT PRIMARY KEY,
      cnt        BIGINT NOT NULL DEFAULT 0,
      updated_at TIMESTAMPTZ DEFAULT now()
    )
  `);

  await sql(`
    DO $$
    BEGIN
      IF EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'payments_status_chk') THEN
        BEGIN
          ALTER TABLE payments DROP CONSTRAINT payments_status_chk;
        EXCEPTION WHEN others THEN NULL;
        END;
      END IF;
    END$$;
  `);
  await sql(`
    ALTER TABLE payments
    ADD CONSTRAINT payments_status_chk
    CHECK (status = ANY (ARRAY['initial','pending','confirmed','paid','failed','expired','cancelled']))
  `);

  await sql(`
    DO $$
    BEGIN
      IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'payments_quote_id_fkey') THEN
        ALTER TABLE payments
        ADD CONSTRAINT payments_quote_id_fkey
        FOREIGN KEY (quote_id) REFERENCES upload_quotes(quote_id) ON DELETE SET NULL;
      END IF;
    END$$;
  `);

  await sql(`UPDATE payments SET confs = COALESCE(confs, 0)`);
  await sql(`UPDATE payments SET required_confs = COALESCE(required_confs, 3)`);
}

const app = express();
app.set('trust proxy', true);
if (CORS_ORIGIN) app.use(cors({ origin: CORS_ORIGIN, credentials: false }));
app.use(express.json({ limit: '50mb' }));
app.use(express.urlencoded({ extended: true }));

const ONE_BY_ONE_PNG = Buffer.from(
  'iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAQAAAC1HAwCAAAAC0lEQVR42mP8/wwAAgMBgN2nS8kAAAAASUVORK5CYII=',
  'base64'
);

// Pixel + counters
app.get('/pixel/:doc', async (req, res) => {
  try {
    const doc = String(req.params.doc || '').slice(0, 80);
    if (!doc) return res.status(400).end();
    await sql(
      `INSERT INTO counters(doc, cnt) VALUES ($1, 1)
       ON CONFLICT (doc) DO UPDATE SET cnt = counters.cnt + 1, updated_at = now()`,
      [doc]
    );
    res.setHeader('Content-Type', 'image/png');
    res.setHeader('Cache-Control', 'no-store');
    res.end(ONE_BY_ONE_PNG);
  } catch {
    res.setHeader('Content-Type', 'image/png');
    res.setHeader('Cache-Control', 'no-store');
    res.end(ONE_BY_ONE_PNG);
  }
});

app.get('/api/counter', async (req, res) => {
  try {
    const doc = String(req.query.doc || '').slice(0, 80);
    if (!doc) return res.status(400).json({ ok: false, error: 'doc required' });
    const { rows } = await sql(`SELECT cnt FROM counters WHERE doc=$1`, [doc]);
    res.json({ ok: true, doc, count: rows[0]?.cnt || 0 });
  } catch (e) {
    res.status(500).json({ ok: false, error: String(e) });
  }
});

// Metrics (optional)
try {
  const { default: registerMetrics } = await import('./metrics.js').catch(async () => (await import('./metrics.cjs')));
  if (typeof registerMetrics === 'function') registerMetrics(app, { query: sql });
} catch {}

// Health + DB stats (NEW db block)
app.get('/api/health', async (_req, res) => {
  try {
    let latestBlock = null;
    try {
      const provider = new ethers.JsonRpcProvider(RPC_URL);
      latestBlock = await provider.getBlockNumber();
    } catch {}

    // DB stats
    let totalUsers = null, totalUploads = null, dbBytes = null;
    try {
      const u = await sql(`SELECT COUNT(DISTINCT lower(address)) AS n FROM payments`);
      totalUsers = Number(u.rows[0]?.n ?? 0);
      const up = await sql(`SELECT COUNT(*) AS n FROM payments WHERE cid IS NOT NULL`);
      totalUploads = Number(up.rows[0]?.n ?? 0);
      const sz = await sql(`SELECT pg_database_size(current_database()) AS bytes`);
      dbBytes = Number(sz.rows[0]?.bytes ?? 0);
    } catch {}

    res.json({
      ok: true,
      chain: CHAIN,
      rpc: RPC_URL,
      latestBlock,
      requiredConfirmations: toInt(REQUIRED_CONFIRMATIONS, 3),
      payTo: PAY_TO,
      now: nowIso(),
      db: {
        totalUsers,
        totalUploads,
        dbBytes
      }
    });
  } catch (err) {
    res.status(500).json({ ok: false, error: String(err) });
  }
});

// Public config
app.get('/api/config/public', (_req, res) => {
  res.json({
    ok: true,
    chain: CHAIN,
    rpc: RPC_URL,
    requiredConfirmations: toInt(REQUIRED_CONFIRMATIONS, 3),
    payTo: PAY_TO,
    gateway: PUBLIC_GATEWAY,
    tierBytes: 20 * 1024 * 1024,
    priceEgazPerTier: 3,
    etiTokenAddress: ETI_TOKEN_ADDRESS || null,
    etiTokenDecimals: toInt(ETI_TOKEN_DECIMALS, 18)
  });
});

// Rates
app.get('/api/rates', async (_req, res) => {
  const egazPerEti = Number(EGAZ_PER_ETI) || 25;
  const etiPerEgaz = 1 / egazPerEti;
  let egazUsd = null, etiUsd = null, source = 'env';

  const fetchJson = async (url, timeoutMs = 4000) => {
    const ac = new AbortController();
    const t = setTimeout(() => ac.abort(), timeoutMs);
    try {
      const r = await fetch(url, { headers: { accept: 'application/json' }, signal: ac.signal });
      if (!r.ok) throw new Error(`HTTP ${r.status}`);
      return await r.json();
    } finally { clearTimeout(t); }
  };

  try {
    const cg = await fetchJson('https://api.coingecko.com/api/v3/simple/price?ids=egaz,etica&vs_currencies=usd');
    if (cg?.egaz?.usd != null) egazUsd = Number(cg.egaz.usd);
    if (cg?.etica?.usd != null) etiUsd  = Number(cg.etica.usd);
    if (egazUsd != null && etiUsd != null)      source = 'coingecko';
    else if (egazUsd != null) { etiUsd = Number((egazUsd * egazPerEti).toFixed(6)); source = 'coingecko+env'; }
    else if (etiUsd != null) { egazUsd = Number((etiUsd / egazPerEti).toFixed(6)); source = 'coingecko+env'; }
  } catch {}

  res.set('Cache-Control', 'no-store');
  res.json({ ok: true, source, egazUsd, etiUsd, egazPerEti, etiPerEgaz });
});

// Files/recent
app.get('/api/files', async (req, res) => {
  const address = (req.query.address || '').toString();
  const vpathPrefix = (req.query.vpathPrefix || '').toString();
  if (!address) return res.json({ ok: true, address: null, files: [] });
  try {
    let q = `
      SELECT
        quote_id      AS "quoteId",
        filename      AS "filename",
        COALESCE(size_bytes,0) AS "sizeBytes",
        address       AS "address",
        currency      AS "currency",
        tx_hash       AS "txHash",
        status        AS "status",
        created_at    AS "createdAt",
        expires_at    AS "expiresAt",
        cid           AS "cid",
        path          AS "path",
        label         AS "label",
        vpath         AS "vpath",
        NULL::text    AS "gateway"
      FROM payments
      WHERE lower(address) = lower($1)
    `;
    const params = [address];
    if (vpathPrefix) { q += ` AND vpath ILIKE $2 || '%'`; params.push(vpathPrefix); }
    q += ` ORDER BY created_at DESC LIMIT 500`;
    const { rows } = await sql(q, params);
    res.json({ ok: true, address: address.toLowerCase(), files: rows });
  } catch (err) {
    res.status(500).json({ ok: false, error: String(err) });
  }
});

app.get('/api/recent', async (req, res) => {
  try {
    const limit = Math.min(50, parseInt(req.query.limit || '5', 10) || 5);
    const { rows } = await sql(`
      SELECT
        created_at                 AS "createdAt",
        filename                   AS "filename",
        COALESCE(size_bytes,0)     AS "sizeBytes",
        address                    AS "address",
        currency                   AS "currency",
        status                     AS "status",
        cid                        AS "cid",
        vpath                      AS "vpath",
        path                       AS "path",
        label                      AS "label"
      FROM payments
      WHERE cid IS NOT NULL
      ORDER BY created_at DESC
      LIMIT $1
    `, [limit]);

    res.set('Cache-Control', 'no-store');
    res.json({ ok: true, items: rows });
  } catch (e) {
    res.status(500).json({ ok: false, error: String(e) });
  }
});

// Quote / Payments / Upload
app.post('/api/quote', async (req, res) => {
  try {
    const { address = null, sizeBytes = 0, currency = 'EGAZ', chain = CHAIN } = req.body || {};
    const size = Number(sizeBytes) || 0;

    const egazPerEti = Number(EGAZ_PER_ETI) || 25;
    const tierBytes  = 20 * 1024 * 1024;
    const tiers      = Math.max(1, Math.ceil(size / tierBytes));
    const priceEgaz  = Math.max(3, tiers * 3);
    const priceEti   = (priceEgaz / egazPerEti).toFixed(6);

    const quoteId = Number(BigInt(Date.now()) * 1000n + BigInt(Math.floor(Math.random() * 1000)));

    await sql(
      `INSERT INTO upload_quotes (
         quote_id, address, filename, size_bytes, chain, currency, tiers, tier_mb, price_egaz,
         required_confirmations, pay_to
       ) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11)
       ON CONFLICT (quote_id) DO NOTHING
       RETURNING quote_id`,
      [
        quoteId,
        address,
        req.body.filename || req.body.fileName || 'unknown',
        req.body.sizeBytes ?? req.body.size_bytes ?? 0,
        chain,
        currency,
        tiers,
        20,
        priceEgaz,
        toInt(REQUIRED_CONFIRMATIONS, 3),
        PAY_TO
      ]
    );

    res.json({
      ok: true,
      quoteId,
      size,
      tiers,
      tierBytes,
      priceEgaz: String(priceEgaz),
      priceEti: String(priceEti),
      currency,
      expiresAt: new Date(Date.now() + 30 * 60 * 1000).toISOString(),
      payTo: PAY_TO,
    });
  } catch (err) {
    res.status(500).json({ ok: false, error: String(err) });
  }
});

app.post('/api/payments/record', async (req, res) => {
  try {
    const {
      quoteId, address, currency = 'EGAZ', txHash = null, filename = null, sizeBytes = null,
      cid = null, path = null, label = null,
      vpath = (req.body.virtualPath || req.body.pathInside || req.body.path || null)
    } = req.body || {};

    if (!quoteId || !address) return res.status(400).json({ ok: false, error: 'quoteId and address required' });

    const q = await sql(`SELECT 1 FROM upload_quotes WHERE quote_id=$1`, [quoteId]);
    if (q.rowCount === 0) return res.status(400).json({ ok: false, error: 'unknown quoteId' });

    await sql(
      `INSERT INTO payments (quote_id, address, currency, tx_hash, status, filename, size_bytes, cid, path, label, vpath, created_at)
       VALUES ($1, lower($2), $3, $4, 'pending', $5, $6, $7, $8, $9, $10, now())
       ON CONFLICT (quote_id) DO UPDATE SET
         address=excluded.address, currency=excluded.currency, tx_hash=excluded.tx_hash,
         filename=excluded.filename, size_bytes=excluded.size_bytes,
         cid=excluded.cid, path=excluded.path, label=excluded.label, vpath=excluded.vpath
      `,
      [quoteId, address, currency, txHash, filename, sizeBytes, cid, path, label, vpath]
    );

    res.json({ ok: true });
  } catch (err) {
    res.status(500).json({ ok: false, error: String(err) });
  }
});

async function refreshConfirmations(quoteId) {
  const r0 = await sql(`SELECT quote_id, tx_hash, status, confs, required_confs, mined_block FROM payments WHERE quote_id=$1`, [quoteId]);
  if (r0.rowCount === 0) return null;
  const row = r0.rows[0];
  if (!row.tx_hash) return row;

  try {
    const provider = new ethers.JsonRpcProvider(RPC_URL);
    const latest = await provider.getBlockNumber();
    let mined = row.mined_block;
    let confs = 0;

    const receipt = await provider.getTransactionReceipt(row.tx_hash);
    if (receipt && receipt.blockNumber != null) mined = receipt.blockNumber;
    if (mined == null) {
      const tx = await provider.getTransaction(row.tx_hash);
      if (tx && tx.blockNumber != null) mined = tx.blockNumber;
    }

    if (mined != null) confs = Math.max(0, latest - Number(mined) + 1);
    const required = row.required_confs ?? toInt(REQUIRED_CONFIRMATIONS, 3);
    const newStatus = confs >= required ? (row.status === 'paid' ? 'paid' : 'confirmed') : row.status;

    await sql(
      `UPDATE payments SET confs=$2, latest_block=$3, mined_block=$4, status=$5 WHERE quote_id=$1`,
      [quoteId, confs, latest, mined, newStatus]
    );

    return { ...row, confs, latest_block: latest, mined_block: mined, status: newStatus };
  } catch {
    return row;
  }
}

app.get('/api/payments/status', async (req, res) => {
  try {
    const quoteId = toInt(req.query.quoteId);
    if (!quoteId) return res.status(400).json({ ok: false, error: 'quoteId required' });

    const r = await refreshConfirmations(quoteId) || {};
    const r2 = await sql(`SELECT * FROM payments WHERE quote_id=$1`, [quoteId]);
    if (r2.rowCount === 0) return res.status(404).json({ ok: false, error: 'not found' });
    const p = r2.rows[0];

    res.json({
      ok: true,
      quoteId: String(quoteId),
      status: p.status,
      txHash: p.tx_hash,
      confirmations: p.confs ?? 0,
      requiredConfirmations: p.required_confs ?? toInt(REQUIRED_CONFIRMATIONS, 3),
      cid: p.cid,
      path: p.path,
      expiresAt: p.expires_at,
      payTo: PAY_TO
    });
  } catch (err) {
    res.status(500).json({ ok: false, error: String(err) });
  }
});

app.post('/api/payments/confirm', async (_req, res) => { res.json({ ok: true }); });

app.post('/api/upload', async (req, res) => {
  try {
    const { quoteId, filename, contentBase64, vpath = null, label = null } = req.body || {};
    if (!quoteId || !filename || !contentBase64)
      return res.status(400).json({ ok: false, error: 'quoteId, filename, contentBase64 required' });

    const pr = await sql(`SELECT status FROM payments WHERE quote_id=$1`, [quoteId]);
    if (pr.rowCount === 0) return res.status(404).json({ ok: false, error: 'payment not found' });
    const status = pr.rows[0].status;
    if (!(status === 'confirmed' || status === 'paid'))
      return res.status(400).json({ ok: false, error: 'payment not confirmed' });

    const payload = Buffer.from(contentBase64, 'base64');
    const add = await fetch(`${IPFS_API}/add?pin=true&cid-version=1&wrap-with-directory=false`, {
      method: 'POST',
      body: (() => { const form = new FormData(); form.append('file', new Blob([payload]), filename); return form; })()
    });
    const addJson = await add.json();
    const cid = addJson.Hash;
    const ipfsPath = `/ipfs/${cid}`;

    await sql(
      `UPDATE payments
       SET cid=$2, path=$3, status='paid', label=COALESCE($4,label), vpath=COALESCE($5,vpath),
           expires_at = now() + interval '6 months'
       WHERE quote_id=$1`,
      [quoteId, cid, ipfsPath, label, vpath]
    );

    res.json({ ok: true, cid, path: ipfsPath });
  } catch (err) {
    res.status(500).json({ ok: false, error: String(err) });
  }
});

// Delete route
import registerDeleteFile from './routes/delete-file.js';
registerDeleteFile(app, {});

app.use((err, _req, res, _next) => {
  console.error('unhandled error:', err);
  res.status(500).json({ ok: false, error: String(err) });
});

(async () => {
  try {
    await migrate();
    app.listen(parseInt(PORT, 10), HOST, () => {
      console.log(`collect backend listening on ${HOST}:${PORT} (chain=${CHAIN})`);
    });
  } catch (err) { console.error('startup/migrate error', err); process.exit(1); }
})();