const express = require('express');

module.exports = function(pool) {
    const router = express.Router();

    // list all non-system tables with row estimates
    router.get('/tables', async (req, res) => {
        try {
            const result = await pool.query(`
                SELECT
                    t.table_schema  AS schema,
                    t.table_name    AS tname,
                    c.reltuples::bigint AS row_estimate
                FROM information_schema.tables t
                LEFT JOIN pg_class c ON c.relname = t.table_name
                LEFT JOIN pg_namespace n ON n.oid = c.relnamespace AND n.nspname = t.table_schema
                WHERE t.table_schema NOT IN ('pg_catalog', 'information_schema', 'pf')
                ORDER BY t.table_schema, t.table_name
            `);
            res.json(result.rows);
        } catch (err) {
            console.error(err);
            res.status(500).json({ error: err.message });
        }
    });

    // preview a table: column list + 5 sample rows
    router.get('/tables/:schema/:tname/preview', async (req, res) => {
        const { schema, tname } = req.params;
        if (!/^\w+$/.test(schema) || !/^\w+$/.test(tname)) {
            return res.status(400).json({ error: 'Invalid schema or table name' });
        }
        try {
            const cols = await pool.query(`
                SELECT column_name, data_type, is_nullable, ordinal_position
                FROM information_schema.columns
                WHERE table_schema = $1 AND table_name = $2
                ORDER BY ordinal_position
            `, [schema, tname]);

            const rows = await pool.query(
                `SELECT * FROM ${schema}.${tname} LIMIT 5`
            );

            res.json({ columns: cols.rows, rows: rows.rows });
        } catch (err) {
            console.error(err);
            res.status(500).json({ error: err.message });
        }
    });

    return router;
};