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

@Injectable()
export class GeneralService {
    constructor(
        @InjectDataSource('emsConnection')
        private dataSource: DataSource,

        @InjectDataSource('generalConnection')
        private generalDataSource: DataSource,

        @InjectDataSource('worldstoneConnection')
        private wsDataSource: DataSource
    ) { }

    async getRegions(): Promise<any[]> {
        const generalDatabase = 'isWorldstone'

        const result = await this.wsDataSource.query(`
            SELECT
                r.*,
                CASE WHEN r.capital IS NOT NULL THEN c.longitude END AS capital_longitude,
                CASE WHEN r.capital IS NOT NULL THEN c.width END AS capital_latitude
            FROM ${generalDatabase}..vRegions AS r
            LEFT JOIN ${generalDatabase}..vCities AS c
                ON r.capital = c.id;
            `)
        return result
    }

    async getDistricts(region_id: number): Promise<any[]> {
        const generalDatabase = '_isGeneral'

        const result = await this.generalDataSource.query(`
            SELECT c.*, d.name AS district_name
            FROM ${generalDatabase}..vCities c
            JOIN ${generalDatabase}..vDistricts d ON d.id_region = c.id_region AND d.id = c.id_district
            WHERE c.id_region = @0;
            `, [Number(region_id)])
        return result
    }

    async getCities(id_region: number, offset?: number, limit?: number, search?: string): Promise<any[]> {
        const generalDatabase = 'isWorldstone'

        if (search) {
            const result = await this.wsDataSource.query(`
            SELECT * FROM ${generalDatabase}..vCities
            WHERE id_region = @0 AND name LIKE '%@1%'
            ORDER BY id 
            OFFSET @2 ROWS 
            FETCH NEXT @3 ROWS ONLY;
            `, [id_region, search, Number(offset) || 0, Number(limit) || 10])
            return result
        } else {
            const result = await this.wsDataSource.query(`
            SELECT * FROM ${generalDatabase}..vCities
            WHERE id_region = @0
            ORDER BY id 
            OFFSET @1 ROWS 
            FETCH NEXT @2 ROWS ONLY;
            `, [id_region, Number(offset) || 0, Number(limit) || 10])
            return result
        }
    }

    async getTypes(): Promise<any[]> {
        const generalDatabase = '_isGeneral'

        const result = await this.dataSource.query(`
            SELECT * FROM ${generalDatabase}..TTypes
            ORDER BY id
            `)
        return result
    }

    async getObjects(offset?: number, limit?: number, city_id?: number): Promise<any[]> {
        const generalDatabase = '_isGeneral'

        const result = await this.generalDataSource.query(`
            SELECT * FROM ${generalDatabase}..vObjects
            ${city_id ? `WHERE id_city = ${Number(city_id)}` : ''}
            ORDER BY id_object
            OFFSET ${Number(offset) || 0} ROWS
            FETCH NEXT ${Number(limit) || 10} ROWS ONLY;
            `)
        return result
    }

    // TODO: GisDB
    async getObjectsList(city_id: number, year: number, planning: number, type?: number): Promise<any[]> {
        const generalDatabase = 'nGeneral'
        const gisDatabase = 'New_Gis'

        const result = await this.dataSource.query(type ? `
                WITH cte_split(type_id, split_value, caption_params) AS
                (
                -- anchor member
                SELECT DISTINCT
                    type_id,
                    CAST(LEFT(caption_params, CHARINDEX(',', caption_params + ',') - 1) AS VARCHAR(255)),  -- Explicitly casting to VARCHAR
                    STUFF(caption_params, 1, CHARINDEX(',', caption_params + ','), '')
                FROM ${gisDatabase}..caption_params
                WHERE city_id = -1 AND user_id = -1
                
                UNION ALL
                
                -- recursive member
                SELECT
                    type_id,
                    CAST(LEFT(caption_params, CHARINDEX(',', caption_params + ',') - 1) AS VARCHAR(255)),  -- Explicitly casting to VARCHAR
                    STUFF(caption_params, 1, CHARINDEX(',', caption_params + ','), '')
                FROM cte_split
                WHERE caption_params > ''
                )
                SELECT
                    o.object_id,
                o.type,
                o.id_city,
                o.year,
                o.planning,
                string_agg(cast(v.value as varchar), ',') as caption
                FROM ${generalDatabase}..vObjects o
                JOIN cte_split c ON o.type = c.type_id
                JOIN ${generalDatabase}..tParameters p ON p.id = split_value
                LEFT JOIN ${generalDatabase}..tValues v
                ON 
                    v.id_param = split_value 
                    AND v.id_object = o.object_id 
                    AND (v.date_po IS NULL)
                AND (v.date_s < DATEFROMPARTS(@0,01,01))

                WHERE
                    o.id_city = @1
                    AND o.year = @2
                    AND o.type = @3
                    AND
                    (
                    CASE
                        WHEN TRY_CAST(o.planning AS BIT) IS NOT NULL THEN TRY_CAST(o.planning AS BIT)
                        WHEN o.planning = 'TRUE' THEN 1
                        WHEN o.planning = 'FALSE' THEN 0
                        ELSE NULL
                    END
                    ) = @4
                GROUP BY object_id, type, id_city, year, planning;
                `:
            `
                SELECT 
                    ${generalDatabase}..tTypes.id AS id,
                    ${generalDatabase}..tTypes.name AS name,
                    COUNT(vo.type) AS count,
                    tr.r,
                    tr.g,
                    tr.b
                FROM 
                    ${generalDatabase}..vObjects vo
                JOIN
                    ${generalDatabase}..tTypes ON vo.type = ${generalDatabase}..tTypes.id
                LEFT JOIN ${gisDatabase}..TypeRoles tr ON tr.id = ${generalDatabase}..tTypes.id
                WHERE 
                    vo.id_city = @1 AND vo.year = @2
                    AND
                    (
                    CASE 
                        WHEN TRY_CAST(vo.planning AS BIT) IS NOT NULL THEN TRY_CAST(vo.planning AS BIT)
                        WHEN vo.planning = 'TRUE' THEN 1
                        WHEN vo.planning = 'FALSE' THEN 0
                        ELSE NULL
                    END
                    ) = @4
                GROUP BY 
                    ${generalDatabase}..tTypes.id,
                    ${generalDatabase}..tTypes.name,
                    tr.r,
                    tr.g,
                    tr.b;
                `, [Number(year) + 1, city_id, year, type, planning, ]
        )
        return result
    }

    async getObjectById(id: string): Promise<any[]> {
        const generalDatabase = '_isGeneral'

        const result = await this.generalDataSource.query(`
            SELECT * FROM ${generalDatabase}..vObjects
            WHERE id_object = @0
            `, [id])
        return result
    }

    async getValuesByObjectId(object_id: string): Promise<any[]> {
        const generalDatabase = 'nGeneral';

        const parameters = await this.dataSource.query(`
        SELECT 
            pt.id_param,
            p.name AS name,
            p.id_group,
            p.format,
            p.vtable,
            p.unit,
            p.exact_format
        FROM ${generalDatabase}..ParametersType pt
        JOIN ${generalDatabase}..tParameters p 
            ON pt.id_param = p.id
        WHERE pt.id_type = (
            SELECT TOP 1 CAST(value AS int)
            FROM ${generalDatabase}..tValues
            WHERE id_object = '${object_id}'
            AND id_param = 3
            ORDER BY date_s DESC
        )
        ORDER BY pt.id_order;
    `);

        const values = await this.dataSource.query(`
        SELECT 
            v.id_param,
            CAST(v.value AS varchar(max)) AS value,
            v.date_s,
            v.date_po,
            v.id_user
        FROM ${generalDatabase}..tValues v
        WHERE v.id_object = '${object_id}';
    `);

        const valuesByParam = values.reduce((acc, v) => {
            if (!acc[v.id_param]) acc[v.id_param] = [];
            acc[v.id_param].push({
                value: v.value,
                date_s: v.date_s,
                date_po: v.date_po,
                id_user: v.id_user
            });
            return acc;
        }, {} as Record<number, any[]>);

        const grouped = parameters
            .filter(p => p.id_group == null)
            .map(parent => ({
                id_param: parent.id_param,
                name: parent.name,
                format: parent.format,
                vtable: parent.vtable,
                unit: parent.unit,
                exact_format: parent.exact_format,
                values: valuesByParam[parent.id_param] || [],
                parameters: parameters
                    .filter(child => child.id_group === parent.id_param)
                    .map(child => ({
                        id_param: child.id_param,
                        name: child.name,
                        id_group: child.id_group,
                        format: child.format,
                        vtable: child.vtable,
                        unit: child.unit,
                        exact_format: child.exact_format,
                        values: valuesByParam[child.id_param] || []
                    }))
            }));

        return grouped;
    }



    async getParamsById(param_id: number): Promise<any[]> {
        const generalDatabase = 'nGeneral'

        const result = await this.dataSource.query(`
            SELECT * FROM ${generalDatabase}..TParameters
            WHERE id = '@0'
            `, [Number(param_id)])
        return result
    }

    tcbParamQuery = (vtable: string, id_city: number) => {
        const generalDatabase = 'nGeneral'

        switch (vtable) {
            case 'vStreets':
                return `SELECT * FROM ${generalDatabase}..${vtable} WHERE id_city = ${id_city};`
            case 'vBoilers':
                return `SELECT * FROM ${generalDatabase}..${vtable} WHERE id_city = ${id_city};`
            default:
                return `SELECT * FROM ${generalDatabase}..${vtable};`
        }
    }

    async getTCBParams(vtable: string, id_city: number, id?: number): Promise<any[]> {
        const generalDatabase = 'nGeneral'
        const query = id ? `
            SELECT * FROM ${generalDatabase}..${vtable} WHERE id = '${id}'
        ` : this.tcbParamQuery(vtable, id_city)

        const result = await this.dataSource.query(query)
        return result
    }

    async getSearchObjects(q: string, id_city: number, year: number) {
        const generalDatabase = 'nGeneral'
        const gisDatabase = 'New_Gis'

        const result = await this.dataSource.query(`
                WITH RankedValues AS (
                    SELECT
                        id_object,
                        date_s,
                        CAST(value AS varchar(max)) AS value,
                        ROW_NUMBER() OVER (PARTITION BY id_object ORDER BY date_s DESC) AS rn,
                        o.id_city AS id_city,
                        o.year AS year
                    FROM ${generalDatabase}..tValues
                    JOIN ${generalDatabase}..tObjects o ON o.id = id_object
                    WHERE CAST(value AS varchar(max)) LIKE '%@0%'
                    )
                SELECT
                    id_object,
                    date_s,
                    value,
                    id_city,
                    year
                FROM RankedValues
                WHERE rn = 1 AND id_city = @1 AND year = @2;
                `, [q, id_city, year])
        return result
    }
}
