import { Injectable, Logger } from '@nestjs/common';
import { InjectDataSource } from '@nestjs/typeorm';
import { DataSource } from 'typeorm';
import { CreateExpenseDto } from './dto/create-expense';
import { CreateLimitDto } from './dto/create-limit';
import { CreateTransferDto } from './dto/create-transfer';
import { GetFuelsDTO } from './dto/get-fuels';
import { GetLimitsDTO } from './dto/get-limits';
import { GetCitySettingsDTO } from './dto/get-city-settings';

@Injectable()
export class FuelService {
    constructor(
        @InjectDataSource('fuelConnection') private dataSource: DataSource,
        @InjectDataSource('generalConnection') private generalDataSource: DataSource,
        @InjectDataSource('worldstoneConnection') private wsDataSource: DataSource
    ) { }

    // GET

    async getColumnDataTypes(table_name: string): Promise<any[]> {
        const result = await this.dataSource.query(`
            SELECT
                COLUMN_NAME,
                DATA_TYPE
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_NAME = @0
            `, [table_name])
        return result
    }

    async getFuelTypes() {
        const result = await this.wsDataSource.query(`
            SELECT * FROM TFuels WHERE NOT id = 0
            `)
        return result
    }

    async getFuels(id_fuels: GetFuelsDTO['id_fuels']) {
        const result = await this.wsDataSource.query(`
            SELECT * FROM dFuelsParameters
            WHERE id_fuels = @0
            `, [Number(id_fuels)])
        return result
    }

    async getBoilers(city_id: number, offset?: number, limit?: number): Promise<any[]> {
        // const result = await this.wsDataSource.query(`
        //     SELECT * FROM isWorldstone..vBoilers"
        //     WHERE id_city = ${city_id}
        //     ORDER BY id_object
        //     OFFSET ${offset || 0} ROWS
        //     FETCH NEXT ${limit || 100} ROWS ONLY
        //     `)

        // return result

        const result = await this.wsDataSource.query(`
            SELECT 
                b.*,
                (
                    SELECT fp.*
                    FROM isFuels.dbo.BoilersFuels bf
                    INNER JOIN isWorldstone.dbo.dFuelsParameters fp ON bf.id_fuels = fp.id
                    WHERE bf.id_boilers = b.id_object
                    FOR JSON PATH
                ) AS id_fuels
            FROM isWorldstone.dbo.vBoilers b
            WHERE id_city = @0
            ORDER BY id_object
            OFFSET @1 ROWS
            FETCH NEXT @2 ROWS ONLY
            `, [city_id, Number(offset || 0), Number(limit || 100)])

        if (Array.isArray(result)) {
            return result.map(row => ({
                ...row,
                id_fuels: JSON.parse(row.id_fuels)
            }))
        } else {
            return result
        }

    }

    async getBoilersFuelsExpenses(): Promise<any[]> {
        const result = await this.dataSource.query(`
            SELECT *
            FROM "BoilersFuelsExpenses";
            `)
        return result
    }

    async getCitySettings(getCitySettings: GetCitySettingsDTO): Promise<any[]> {
        const { city_id } = getCitySettings

        const result = await this.wsDataSource.query(`
            SELECT * FROM isWorldstone..vCitySettings
            WHERE id_city = @0
            `, [Number(city_id)])

        return result
    }

    async getBoilersFuelsLimits(getLimitsDTO: GetLimitsDTO): Promise<any[]> {
        const { city_id, year, offset, limit } = getLimitsDTO

        // const result = await this.wsDataSource.query(`
        //     SELECT 
        //         b.*,
        //         (
        //             SELECT 
        //                 bf.id AS id_fuels_entry,
        //                 df.id AS fuel_id,
        //                 df.name,

        //                 -- вложенный JSON: лимиты по месяцам
        //                 (
        //                     SELECT 
        //                         l.month,
        //                         l.year,
        //                         l.value
        //                     FROM isFuels..BoilersFuelsLimits l
        //                     WHERE l.id_fuel = bf.id
        //                     ORDER BY l.year, l.month
        //                     FOR JSON PATH
        //                 ) AS limits

        //             FROM isFuels..BoilersFuels bf
        //             JOIN isWorldstone..dFuelsParameters df
        //                 ON df.id = bf.id_fuels
        //             WHERE bf.id_boilers = b.id_object
        //             FOR JSON PATH
        //         ) AS fuels
        //     FROM isWorldstone..vBoilers b
        //     WHERE id_city = @0
        //     ORDER BY id_object
        //     OFFSET @1 ROWS
        //     FETCH NEXT @2 ROWS ONLY
        //     ;
        //     `, [city_id, Number(offset || 0), Number(limit || 100)])

        const result = await this.wsDataSource.query(`
            WITH Src AS (
                SELECT
                    b.id_object AS id_boiler,
                    b.name      AS boiler_name,
                    bf.id       AS id_boiler_fuel,
                    bf.id_fuels AS id_fuel_type,
                    fp.name     AS fuel_name,
                    l.month,
                    l.value,
                    l.year
                FROM isWorldstone..vBoilers b
                LEFT JOIN isFuels..BoilersFuels bf
                    ON bf.id_boilers = b.id_object
                LEFT JOIN isWorldstone..dFuelsParameters fp
                    ON fp.id = bf.id_fuels
                LEFT JOIN isFuels..BoilersFuelsLimits l
                    ON l.id_boiler = bf.id_boilers
                AND l.id_fuel   = bf.id
                AND (
                        (l.month BETWEEN 7 AND 12 AND l.year = @1)     -- second half
                        OR
                        (l.month BETWEEN 1 AND 6  AND l.year = @2)       -- first half next year
                )
                WHERE b.id_city = @0
            ),

            Renamed AS (
                SELECT
                    id_boiler,
                    boiler_name,
                    id_boiler_fuel,
                    id_fuel_type,
                    fuel_name,
                    CASE month
                        WHEN 7  THEN 'jul'
                        WHEN 8  THEN 'aug'
                        WHEN 9  THEN 'sep'
                        WHEN 10 THEN 'oct'
                        WHEN 11 THEN 'nov'
                        WHEN 12 THEN 'dec'
                        WHEN 1  THEN 'jan'
                        WHEN 2  THEN 'feb'
                        WHEN 3  THEN 'mar'
                        WHEN 4  THEN 'apr'
                        WHEN 5  THEN 'may'
                        WHEN 6  THEN 'jun'
                    END AS MonthName,
                    value
                FROM Src
            )

            SELECT *
            FROM Renamed
            PIVOT (
                MAX(value)
                FOR MonthName IN (
                    [jul], [aug], [sep], [oct], [nov], [dec],
                    [jan], [feb], [mar], [apr], [may], [jun]
                )
            ) AS P
            ORDER BY id_boiler, id_boiler_fuel;
            `, [city_id, year, year + 1])
        return result
    }

    async getFuelsTransfer(): Promise<any[]> {
        const result = await this.dataSource.query(`
            SELECT * FROM "FuelsTransfer";
            `)
        return result
    }

    // ADD
    async addBoilersFuelsExpense(createExpenseDto: CreateExpenseDto): Promise<any[]> {
        const result = await this.dataSource.query(`
            INSERT INTO dbo.BoilersFuelsExpenses (id_boiler, id_fuel, date, value) VALUES ($1, $2, $3, $4)
            `, [createExpenseDto.id_boiler, createExpenseDto.id_fuel, createExpenseDto.date, createExpenseDto.value])
        return result
    }

    async addBoilersFuelsLimit(createLimitDto: CreateLimitDto): Promise<any[]> {
        const logger = new Logger('INFO'); // 'MyContext' is an optional string for log context
        logger.log(createLimitDto)

        return [createLimitDto]
        // const result = await this.dataSource.query(`
        //     INSERT INTO isFuels..BoilersFuelsLimits (id_boiler, id_fuel, value, month, year) VALUES ($1, $2, $3, $4, $5)
        //     `, [createLimitDto.id_boiler, createLimitDto.id_fuel, createLimitDto.value, createLimitDto.month, createLimitDto.year])
        // return result
    }

    async addFuelsTransfer(createTransferDto: CreateTransferDto): Promise<any[]> {
        const result = await this.dataSource.query(`
            INSERT INTO dbo.FuelsTransfer (id_out, id_in, id_fuel, date, value) VALUES ($1, $2, $3, $4, $5)
            `, [createTransferDto.id_out, createTransferDto.id_in, createTransferDto.id_fuel, createTransferDto.date, createTransferDto.value])
        return result
    }
}
