import express, { Request, Response } from 'express';
import { tediousQuery } from '../../utils/tedious';
import { GeneralDB, GisDB } from '../../constants/db';
import { pgQuery } from '../../utils/postgres';

const router = express.Router()

router.get('/migrate/lines', async (req: Request, res: Response) => {
    try {
        const result = await tediousQuery(
            `
            SELECT * FROM ${GisDB}..lines
            `
        )
    } catch (error) {
        res.status(500)
    }
})

// ✅
router.get('/type-roles', async (req: Request, res: Response) => {
    try {
        const result = await tediousQuery(
            `
            SELECT * FROM ${GisDB}..TypeRoles;
            `
        )
        res.status(200).json(result)
    } catch (err) {
        res.status(500)
    }
})


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

        const result = await pgQuery(
            `
            SELECT entity_id, entity_type, ST_AsGeoJSON(geometry)::JSON AS geometry FROM bounds
            WHERE entity_type = $1
            `,
            [entity_type]
        )

        if (Array.isArray(result)) {
            if (result.length > 0) {
                const geometries = result.map((bound: { id: string, 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 entity_id, entity_type, ST_AsGeoJSON(geometry)::JSON AS geometry 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)
    }
})

// ✅
router.get('/images/all', async (req: Request, res: Response) => {
    try {
        const { offset, limit, city_id } = req.query

        const result = await tediousQuery(
            `
            SELECT * FROM ${GisDB}..images
            ${city_id ? `WHERE city_id = ${city_id}` : ''}
            ORDER BY city_id 
            OFFSET ${Number(offset) || 0} ROWS
            FETCH NEXT ${Number(limit) || 10} ROWS ONLY;
            `
        )
        res.status(200).json(result)
    } catch (err) {
        res.status(500)
    }
})

// ✅
// Get figures by year and city id
router.get('/figures/all', async (req: Request, res: Response) => {
    try {
        const { offset, limit, object_id, year, city_id } = req.query

        const result = await tediousQuery(
            `
            SELECT * FROM ${GisDB}..figures f
            JOIN ${GeneralDB}..vObjects o ON f.object_id = o.object_id WHERE o.id_city = ${city_id} AND f.year = ${year}
            ORDER BY f.year
            OFFSET ${Number(offset) || 0} ROWS
            FETCH NEXT ${Number(limit) || 10} ROWS ONLY;
            `
        )
        res.status(200).json(result)
    } catch (err) {
        res.status(500)
    }
})

// ✅
// Get lines by year and city id
router.get('/lines/all', async (req: Request, res: Response) => {
    try {
        const { offset, limit, object_id, year, city_id } = req.query

        const result = await tediousQuery(
            `
            SELECT * FROM ${GisDB}..lines l
            JOIN ${GeneralDB}..vObjects o ON l.object_id = o.object_id WHERE o.id_city = ${city_id} AND l.year = ${year};
            `
        )
        res.status(200).json(result)
    } catch (err) {
        res.status(500)
    }
})

// ✅
router.get('/regions/borders', async (req: Request, res: Response) => {
    try {
        const result = await tediousQuery(
            `
            SELECT * FROM ${GisDB}..visual_regions`
        )
        res.status(200).json(result)
    } catch (err) {
        res.status(500)
    }
})

export default router