import express, { Request, Response } from 'express';
import { pgQuery } from '../../utils/postgres';
const router = express.Router()

router.get('/rows/:table_name', async (req: Request, res: Response) => {
    try {
        const { table_name } = req.params
        const { offset, limit } = req.query

        const result = await pgQuery(
            `
            SELECT * FROM ${table_name}
            OFFSET ${offset || 0} LIMIT ${limit || 10}
            `
        )

        if (Array.isArray(result)) {
            if (result.length > 0) {
                res.status(200).json(result)
            } else {
                res.status(404).json('not found')
            }
        } else {
            res.status(404).json('not found')
        }
    } catch (err) {
        res.status(500)
    }
})

router.get('/columns/:table_name', async (req: Request, res: Response) => {
    try {
        const { table_name } = req.params

        const result = await pgQuery(
            `
            SELECT column_name, data_type
            FROM information_schema.columns
            WHERE table_name = '${table_name}'
            `
        )

        if (Array.isArray(result)) {
            if (result.length > 0) {
                res.status(200).json(result)
            } else {
                res.status(404).json('not found')
            }
        } else {
            res.status(404).json('not found')
        }
    } catch (err) {
        res.status(500)
    }
})

router.get('/tables', async (req: Request, res: Response) => {
    try {
        const result = await pgQuery(
            `
            SELECT tablename FROM pg_tables WHERE schemaname = 'public';
            `
        )

        if (Array.isArray(result)) {
            if (result.length > 0) {
                res.status(200).json(result)
            } else {
                res.status(404).json('not found')
            }
        } else {
            res.status(404).json('not found')
        }
    } catch (err) {
        res.status(500)
    }
})

router.get('/figures/import', async (req: Request, res: Response) => {
    try {
        const { entity_type } = req.params

        const result = await pgQuery(
            `
            SELECT * FROM bounds
            WHERE entity_type = $1
            `,
            [entity_type]
        )

        if (Array.isArray(result)) {
            if (result.length > 0) {
                const geometries = result.map((bound: { id: number, entity_id: number, entity_type: string, geometry: JSON, published_at: string, deleted_at: string | null }) => {
                    return {
                        ...bound.geometry,
                        properties: {
                            id: bound.id,
                            entity_id: bound.entity_id,
                            entity_type: bound.entity_type
                        }
                    }
                })

                res.status(200).json(geometries)
            } else {
                res.status(404).json('not found')
            }
        } else {
            res.status(404).json('not found')
        }
    } catch (err) {
        res.status(500)
    }
})

router.get('/bounds/:entity_type/:entity_id', async (req: Request, res: Response) => {
    try {
        const { entity_type, entity_id } = req.params

        const result = await pgQuery(
            `
            SELECT * FROM bounds
            WHERE entity_type = $1 
            AND entity_id = $2
            `,
            [entity_type, entity_id]
        )

        if (Array.isArray(result)) {
            if (result.length > 0) {
                res.status(200).json(result[0].geometry)
            } else {
                res.status(404).json('not found')
            }
        } else {
            res.status(404).json('not found')
        }
    } catch (err) {
        res.status(500)
    }
})

export default router