const arrayFormatting = require("../util/databaseArrayFormatting.js")

module.exports = async (dbConn, id) => {
    const res = await dbConn.query(
            `SELECT regions.id AS region_id,
                regions.region AS name,
                countries.country AS country,
                regions.description AS description,
                regions.lon AS lon,
                regions.lat AS lat,
                rcma.temperature_mean AS temperature_mean,
                rcma.temperature_mean_min AS temperature_mean_min,
                rcma.temperature_mean_max AS temperature_mean_max,
                rcma.precipitation AS precipitation,
                rcma.rain_days AS rain_days,
                rcma.sun_hours AS sun_hours,
                rcma.humidity AS humidity,
                regions_byt.average_per_day_costs AS average_per_day_costs,
                rtma.avg_price_relative AS avg_price_relative,
                regions_byt.accommodation_costs AS accommodation_costs,
                regions_byt.food_costs AS food_costs,
                regions_byt.water_costs AS water_costs,
                regions_byt.local_transportation_costs AS local_transportation_costs,
                regions_byt.entertainment_costs AS entertainment_costs,
                regions_byt.alcohol_costs AS alcohol_costs
        FROM regions
        LEFT JOIN countries ON regions.country_id = countries.id
        LEFT JOIN (SELECT rcma.region_id, 
                GROUP_CONCAT(rcma.temperature_mean ORDER BY rcma.month SEPARATOR ', ') AS temperature_mean, 
                GROUP_CONCAT(rcma.temperature_mean_min ORDER BY rcma.month SEPARATOR ', ') AS temperature_mean_min, 
                GROUP_CONCAT(rcma.temperature_mean_max ORDER BY rcma.month SEPARATOR ', ') AS temperature_mean_max, 
                GROUP_CONCAT(rcma.precipitation ORDER BY rcma.month SEPARATOR ', ') AS precipitation, 
                GROUP_CONCAT(rcma.rain_days ORDER BY rcma.month SEPARATOR ', ') AS rain_days, 
                GROUP_CONCAT(rcma.sun_hours ORDER BY rcma.month SEPARATOR ', ') AS sun_hours, 
                GROUP_CONCAT(rcma.humidity ORDER BY rcma.month SEPARATOR ', ') AS humidity
              FROM region_climate_monthly_avg AS rcma
              GROUP BY rcma.region_id) rcma ON rcma.region_id = regions.id
        LEFT JOIN regions_byt ON regions.id = regions_byt.region_id
        LEFT JOIN (SELECT rtma.region_id,
              GROUP_CONCAT(rtma.avg_price_relative ORDER BY rtma.month SEPARATOR ', ') AS avg_price_relative
              FROM regions_trivago_monthly_avg AS rtma
              GROUP BY rtma.region_id) rtma
        ON regions.id = rtma.region_id
        WHERE regions_byt.travelstyle = 1
        AND regions.id = ?`,
        [id]
    );
    const region = res[0]

    region.avg_price_relative = arrayFormatting(region.avg_price_relative);
    region.temperature_mean = arrayFormatting(region.temperature_mean);
    region.temperature_mean_min = arrayFormatting(region.temperature_mean_min);
    region.temperature_mean_max = arrayFormatting(region.temperature_mean_max);
    region.precipitation = arrayFormatting(region.precipitation);
    region.rain_days = arrayFormatting(region.rain_days);
    region.sun_hours = arrayFormatting(region.sun_hours);
    region.humidity = arrayFormatting(region.humidity);

    return region;
};