import { Injectable, NotFoundException } from '@nestjs/common';
import { InjectDataSource } from '@nestjs/typeorm';
import { DataSource } from 'typeorm';

@Injectable()
export class GisService {
    constructor(
        @InjectDataSource('sqliteConnection')
        private dataSource: DataSource,

        @InjectDataSource('emsConnection')
        private emsDataSource: DataSource
    ) { }

    async getTypeRoles(): Promise<any[]> {
        const result = await this.emsDataSource.query(`
            SELECT * FROM New_Gis..TypeRoles;
            `)

        return result
    }

    async getBoundsByEntityType(entity_type: 'region' | 'district' | 'city'): Promise<any[]> {
        const result = await this.dataSource.query(`
            SELECT entity_id, entity_type, geometry FROM bounds_general
            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: string, published_at: string, deleted_at: string | null }) => {
                    return {
                        ...(JSON.parse(bound.geometry)),
                        properties: {
                            id: bound.id,
                            entity_id: bound.entity_id,
                            entity_type: bound.entity_type
                        }
                    }
                })

                return geometries
            } else {
                throw new NotFoundException('not found')
            }
        } else {
            throw new NotFoundException('not found')
        }
    }

    async getBoundsByEntityTypeAndId(entity_type: 'region' | 'district' | 'city', entity_id: number): Promise<any[]> {
        const result = await this.dataSource.query(`
            SELECT entity_id, entity_type, geometry FROM bounds_general
            WHERE entity_type = $1
            AND entity_id = $2
            `, [entity_type, entity_id])
        if (Array.isArray(result)) {
            if (result.length > 0) {
                return JSON.parse(result[0].geometry)
            } else {
                throw new NotFoundException('not found')
            }
        } else {
            throw new NotFoundException('not found')
        }
    }

    async getBoundsByEntityTypeAndList(
        entity_type: 'region' | 'district' | 'city',
        list: number[],
    ): Promise<any[]> {
        if (!list || list.length === 0) {
            throw new NotFoundException('No entity IDs provided');
        }

        // Build placeholders (?, ?, ?) for SQLite IN clause
        const placeholders = list.map(() => '?').join(', ');

        const result = await this.dataSource.query(
            `
            SELECT entity_id, entity_type, geometry 
            FROM bounds_general
            WHERE entity_type = ?
            AND entity_id IN (${placeholders})
            `,
            [entity_type, ...list],
        );

        if (!Array.isArray(result) || result.length === 0) {
            throw new NotFoundException('Not found');
        }

        return result.map(
            (bound: {
                id: string;
                entity_id: number;
                entity_type: string;
                geometry: string;
                published_at: string;
                deleted_at: string | null;
            }) => ({
                ...(JSON.parse(bound.geometry)),
                properties: {
                    id: bound.id,
                    entity_id: bound.entity_id,
                    entity_type: bound.entity_type,
                },
            }),
        );
    }

    async getImages(offset: number, limit: number, city_id: number): Promise<any[]> {
        const result = await this.emsDataSource.query(`
            SELECT * FROM images
            ${city_id ? `WHERE city_id = ${city_id}` : ''}
            ORDER BY city_id 
            OFFSET ${Number(offset) || 0} ROWS
            FETCH NEXT ${Number(limit) || 10} ROWS ONLY;
            `)
        return result
    }

    // async getFigures(offset: number, limit: number, year: number, city_id: number): Promise<any[]> {
    //     const result = await this.emsDataSource.query(`
    //         SELECT o.*, f.[figure_type_id], f.[left], f.[top], f.[width], f.[height], f.[angle], f.[points], f.[label_left], f.[label_top], f.[label_angle], f.[label_size] FROM New_Gis..figures f
    //         JOIN nGeneral..vObjects o ON o.object_id = f.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;
    //     `)
    //     return result
    // }

    // async getLines(year: number, city_id: number): Promise<any[]> {
    //     const result = await this.emsDataSource.query(
    //         `
    //         SELECT o.[object_id], o.[id_city], o.[id_parent], o.[type], o.[planning], o.[activity], o.[kvr], o.[jur], o.[fuel], o.[boiler_id],
    //         l.[x1], l.[y1], l.[x2], l.[y2], l.[points], l.[label_offset], l.[group_id], l.[show_label], l.[forced_lengths], l.[label_sizes], l.[label_angles], l.[label_positions], l.[year]
    //         FROM New_Gis..lines l
    //         JOIN nGeneral..vObjects o ON l.object_id = o.object_id WHERE o.id_city = ${city_id} AND l.year = ${year};
    //         `
    //     )
    //     return result
    // }

    async getFigures(offset: number, limit: number, year: number, city_id: number): Promise<any[]> {
        // Get original figures from EMS
        const originalFigures = await this.emsDataSource.query(`
        SELECT o.[object_id], o.[id_city], o.[id_parent], o.[type], o.[planning], o.[activity], o.[kvr], o.[jur], o.[fuel], o.[boiler_id], f.[figure_type_id], f.[left], f.[top], f.[width], f.[height], f.[angle], f.[points], f.[label_left], f.[label_top], f.[label_angle], f.[label_size], f.[year] FROM New_Gis..figures f
        JOIN nGeneral..vObjects o ON o.object_id = f.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;
    `)

        // Get modified figures from SQLite for the same year
        const modifiedFigures = await this.dataSource.query(
            `SELECT * FROM figures WHERE year = ?`,
            [year]
        )

        // Create a lookup map using object_id + year as key
        const modifiedMap = new Map()
        modifiedFigures.forEach(fig => {
            const key = `${fig.object_id}_${fig.year}`
            modifiedMap.set(key, fig)
        })

        // Replace original values with modified ones
        const mergedResult = originalFigures.map(original => {
            const key = `${original.object_id}_${year}`
            const modified = modifiedMap.get(key)

            if (modified) {
                return {
                    ...original,
                    modified: modified.feature
                }
            }

            return original
        })

        return mergedResult
    }

    async getLines(year: number, city_id: number): Promise<any[]> {
        // Get original lines from EMS
        const originalLines = await this.emsDataSource.query(`
        SELECT o.[object_id], o.[id_city], o.[id_parent], o.[type], o.[planning], o.[activity], o.[kvr], o.[jur], o.[fuel], o.[boiler_id],
        l.[x1], l.[y1], l.[x2], l.[y2], l.[points], l.[label_offset], l.[group_id], l.[show_label], l.[forced_lengths], l.[label_sizes], l.[label_angles], l.[label_positions], l.[year]
        FROM New_Gis..lines l
        JOIN nGeneral..vObjects o ON l.object_id = o.object_id WHERE o.id_city = ${city_id} AND l.year = ${year};
    `)

        // Get modified lines from SQLite for the same year
        const modifiedLines = await this.dataSource.query(
            `SELECT * FROM lines WHERE year = ?`,
            [year]
        )

        // Create lookup map with object_id + year
        const modifiedMap = new Map()
        modifiedLines.forEach(line => {
            const key = `${line.object_id}_${line.year}`
            modifiedMap.set(key, line)
        })

        // Replace original with modified
        const mergedResult = originalLines.map(original => {
            const key = `${original.object_id}_${year}`
            const modified = modifiedMap.get(key)

            if (modified) {
                return {
                    ...original,
                    modified: modified.feature
                }
            }

            return original
        })

        return mergedResult
    }

    async getRegionBorders(): Promise<any[]> {
        const result = await this.emsDataSource.query(
            `
            SELECT * FROM New_Gis..visual_regions
            `
        )

        return result
    }

    async updateFeaturesBatch(features: any[]) {
        let figures: any[] = []
        let lines: any[] = []

        features.map(feature => {
            if (feature.type === 'figure') {
                figures.push(feature)
            } else if (feature.type === 'line') {
                lines.push(feature)
            }
        })

        console.log('Figures to update:', figures.length)
        console.log('Lines to update:', lines.length)

        // Update figures
        if (figures.length > 0) {
            const figurePlaceholders = figures.map(() =>
                `(?, ?, ?)`
            ).join(', ')

            const figureParams = figures.flatMap(fig => [
                fig.object_id,
                fig.year, // Default year if not provided
                JSON.stringify(fig.feature)
            ])

            await this.dataSource.query(`
            INSERT OR REPLACE INTO figures 
            (object_id, year, feature)
            VALUES ${figurePlaceholders}
        `, figureParams)
        }

        // Update lines
        if (lines.length > 0) {
            const linePlaceholders = lines.map(() =>
                `(?, ?, ?)`
            ).join(', ')

            const lineParams = lines.flatMap(line => [
                line.object_id,
                line.year,
                JSON.stringify(line.feature)
            ])

            await this.dataSource.query(`
            INSERT OR REPLACE INTO lines 
            (object_id, year, feature)
            VALUES ${linePlaceholders}
        `, lineParams)
        }

        return {
            success: true,
            message: `Updated ${figures.length} figures and ${lines.length} lines`
        }
    }
}
