FACT_power.jl

"""
    eu_power(conn::MySQL.Connection, selection::Dict) -> DataFrame

Retrieve information about power stations and transmission lines in the European Union (EU) for specified geographic areas.

This function communicates with a MySQL database connection to execute SQL queries and retrieve information about power stations and transmission lines in the EU. It calculates the number of power stations, total capacity, and total length of transmission lines for the specified geographic areas based on their intersections with station and line shapes.

# Arguments
- `conn::MySQL.Connection`: The MySQL database connection to use for querying EU power data.
- `selection::Dict`: A dictionary specifying the selection criteria including `geo_id` (station IDs) and `shape_obj` (geometric shapes).

# Returns
- `DataFrame`: A DataFrame containing columns for `geo_id` (station ID), `region` (region identifier, always "EU"), `stations` (number of power stations), `capacity` (total capacity in MW), and `lines` (total length of transmission lines in meters) representing power station and transmission line information for the specified areas.

# Example
> conn = MySQL.Connection(user="user", password="password", host="localhost", db="eu_power_db")
> selection = Dict("geo_id" => ["STATION1", "STATION2"], "shape_obj" => ["POINT(1 2)", "POINT(3 4)"])
> df = eu_power(conn, selection)

This example retrieves information about two EU power stations and their associated transmission lines based on specified station IDs and shapes.
"""
function eu_power(conn_dict::Dict, selection::Dict)
    # getting valuable info from dictionary
    geo_id = selection["geo_id"]
    shape_obj = selection["shape_obj"]
    # object to be returned
    dftemp = DataFrame()
    # iterate over geo objects
    for (id, shape) in zip(geo_id, shape_obj)
        # prepare query
        query = "SELECT COUNT(*) as stations, SUM(capacity_g) as capacity FROM eu_gen WHERE ST_Intersects(ST_GeomFromText('"*shape*"'), SHAPE);"
        query2 = "SELECT SUM(ST_Length(ST_Intersection(SHAPE, ST_GeomFromText('"*shape*"')))) AS 'lines' FROM eu_trans WHERE ST_Intersects(SHAPE, ST_GeomFromText('"*shape*"'));"
        # create connection
        conn = establish_connection(conn_dict["host"], conn_dict["user"], conn_dict["password"], conn_dict["database"]; port=conn_dict["port"])
        # execute
        result = query_connection(conn, query)
        result2 = query_connection(conn, query2)
        # close connection
        close_connection(conn)
        result = hcat(result, result2)
        # Check if the result is empty
        if !isempty(result)
            # add geo_id col
            result[!, :geo_id] = fill(id, nrow(result))
            # copy results to be returned
            dftemp = vcat(dftemp, result)
        else
            println("No data returned from the query:")
            println(query)
        end
    end 
    #dftemp[!, :region] = fill("EU", nrow(dftemp))
    return dftemp
end

"""
    us_power(conn::MySQL.Connection, selection::Dict) -> DataFrame

Retrieve information about power stations and transmission lines in the United States (US) for specified geographic areas.

This function communicates with a MySQL database connection to execute SQL queries and retrieve information about power stations and transmission lines in the US. It calculates the number of power stations, total capacity, and total length of transmission lines for the specified geographic areas based on their intersections with station and line shapes.

# Arguments
- `conn::MySQL.Connection`: The MySQL database connection to use for querying US power data.
- `selection::Dict`: A dictionary specifying the selection criteria including `geo_id` (station IDs) and `shape_obj` (geometric shapes).

# Returns
- `DataFrame`: A DataFrame containing columns for `geo_id` (station ID), `region` (region identifier, always "US"), `stations` (number of power stations), `capacity` (total capacity in MW), and `lines` (total length of transmission lines in meters) representing power station and transmission line information for the specified areas.

# Example
> conn = MySQL.Connection(user="user", password="password", host="localhost", db="us_power_db")
> selection = Dict("geo_id" => ["STATION1", "STATION2"], "shape_obj" => ["POINT(1 2)", "POINT(3 4)"])
> df = us_power(conn, selection)

This example retrieves information about two US power stations and their associated transmission lines based on specified station IDs and shapes.
"""
function us_power(conn_dict::Dict, selection::Dict)
    # getting valuable info from dictionary
    geo_id = selection["geo_id"]
    shape_obj = selection["shape_obj"]
    # object to be returned
    dftemp = DataFrame()
    # iterate over geo objects
    for (id, shape) in zip(geo_id, shape_obj)
        # prepare query
        query = "SELECT COUNT(*) as stations, SUM(COALESCE(CASE WHEN modified_source_des LIKE '%Battery = %' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(modified_source_des, 'Battery = ', -1), ' MW', 1) ELSE 0 END) + COALESCE(CASE WHEN modified_source_des LIKE '%Solar = %' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(modified_source_des, 'Solar = ', -1), ' MW', 1) ELSE 0 END) + COALESCE(CASE WHEN modified_source_des LIKE '%Wind = %' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(modified_source_des, 'Wind = ', -1), ' MW', 1) ELSE 0 END) + COALESCE(CASE WHEN modified_source_des LIKE '%Natural Gas = %' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(modified_source_des, 'Natural Gas = ', -1), ' MW', 1) ELSE 0 END) + COALESCE(CASE WHEN modified_source_des LIKE '%Hydroelectric = %' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(modified_source_des, 'Hydroelectric = ', -1), ' MW', 1) ELSE 0 END) + COALESCE(CASE WHEN modified_source_des LIKE '%Coal = %' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(modified_source_des, 'Coal = ', -1), ' MW', 1) ELSE 0 END) + COALESCE(CASE WHEN modified_source_des LIKE '%Nuclear = %' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(modified_source_des, 'Nuclear = ', -1), ' MW', 1) ELSE 0 END) + COALESCE(CASE WHEN modified_source_des LIKE '%Petroleum = %' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(modified_source_des, 'Petroleum = ', -1), ' MW', 1) ELSE 0 END) + COALESCE(CASE WHEN modified_source_des LIKE '%Biomass = %' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(modified_source_des, 'Biomass = ', -1), ' MW', 1) ELSE 0 END) + COALESCE(CASE WHEN modified_source_des LIKE '%Pumped Storage = %' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(modified_source_des, 'Pumped Storage = ', -1), ' MW', 1) ELSE 0 END) + COALESCE(CASE WHEN modified_source_des LIKE '%Geothermal = %' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(modified_source_des, 'Geothermal = ', -1), ' MW', 1) ELSE 0 END)) as capacity FROM (SELECT ST_Intersects(ST_GeomFromText('"*shape*"'), SHAPE) as intersects, REPLACE(source_des, 'MW', ' MW') as modified_source_des FROM us_gen) as modified_table WHERE intersects;"
        query2 = "SELECT SUM(ST_Length(ST_Intersection(SHAPE, ST_GeomFromText('"*shape*"')))) AS 'lines' FROM us_trans WHERE ST_Intersects(SHAPE, ST_GeomFromText('"*shape*"'));"
        # create connection
        conn = establish_connection(conn_dict["host"], conn_dict["user"], conn_dict["password"], conn_dict["database"]; port=conn_dict["port"])
        # execute
        result = DataFrame()
        try
            # Execute session variable adjustments
            result = query_connection(conn, query)
        catch ex
            @error "Failed to set session variables for query $query with expection " exception=(ex, catch_backtrace())
        end
        # execute
        result2 = DataFrame()
        try
            # Execute session variable adjustments
            result2 = query_connection(conn, query2)
        catch ex
            @error "Failed to set session variables for query $query2 with expection " exception=(ex, catch_backtrace())
        end
        # close connection
        close_connection(conn)
        result = hcat(result, result2)
        # Check if the result is empty
        if !isempty(result)
            # add geo_id col
            result[!, :geo_id] = fill(id, nrow(result))
            # copy results to be returned
            dftemp = vcat(dftemp, result)
        else
            println("No data returned from the query:")
            println(query)
        end
    end 
    #dftemp[!, :region] = fill("EU", nrow(dftemp))
    return dftemp
end

"""
    get_power_stations_lines(table::String, selection::Dict; host::String="127.0.0.1") -> DataFrame

Retrieve power station and transmission line information for specified geographic areas using a service query.

This function allows for the retrieval of power station and transmission line information, including the number of power stations, total capacity, and total length of transmission lines, from a specific table using a service query and selection criteria. It communicates with the MySQL database and returns the results as a DataFrame.

# Arguments
- `table::String`: The name of the table to query for power station and transmission line information (e.g., "eu_power", "us_power").
- `selection::Dict`: A dictionary specifying the selection criteria for the query, including `geo_id` and `shape_obj`.
- `host::String` (optional): The host address of the MySQL database. Default is "127.0.0.1".

# Returns
- `DataFrame`: A DataFrame containing columns for `geo_id` (geographic ID), `region` (region identifier, "EU" for EU tables, "US" for US tables), `stations` (number of power stations), `capacity` (total capacity in MW), and `lines` (total length of transmission lines in meters) representing power station and transmission line information for the specified geographic areas.

# Example
> table = "eu_power"
> selection = Dict("geo_id" => ["STATION1", "STATION2"], "shape_obj" => ["POINT(1 2)", "POINT(3 4)"])
> df = get_power_stations_lines(table, selection, host="localhost")

This example retrieves power station and transmission line information for two EU power stations based on specified station IDs and shapes using the "eu_power" table.
"""
function get_power_stations_lines(table::String, selection::Dict; host::String="127.0.0.1")
    conn_dict = Dict(
        "host" => host,
        "port" => 3313,
        "database" => "FACT_power",
        "user" => "root",
        "password" => "devops"
    )

    table_dict = Dict(
        "eu_power" => eu_power,
        "us_power" => us_power
    )

    df_template = DataFrame(
        #region = String[], # column to store if EU or US
        geo_id = String[], # Column for Geo IDs
        stations = Int64[], # column for number of stations
        capacity = Float64[], # column to total capacity of stations
        lines = Float64[] # column to total length of transmission lines
    )

    # Check if the table is supported
    if !haskey(table_dict, table)
        error("Unsupported table: $table. Please check the available tables: $(keys(table_dict)).")
    end

    # data retrieval
    df = get_selected_objects_from_table(table, df_template, table_dict, selection, conn_dict) 

    return df
end