FACT_rail.jl

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

Retrieve information about railway stations and 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 railway stations and lines in the EU. It calculates the number of railway stations and the total length of railway 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 railway 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 railway stations), and `lines` (total length of railway lines in meters) representing railway station and line information for the specified areas.

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

This example retrieves information about two EU railway stations and their associated railway lines based on specified station IDs and shapes.
"""
function eu_rail(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 FROM eu_stations WHERE ST_Intersects(ST_GeomFromText('"*shape*"'), SHAPE);"
        query2 = "SELECT SUM(ST_Length(ST_Intersection(SHAPE, ST_GeomFromText('"*shape*"')))) AS 'lines' FROM eu_lines 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

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

Retrieve information about railway stations and 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 railway stations and lines in the US. It calculates the number of railway stations and the total length of railway 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 railway 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 railway stations), and `lines` (total length of railway lines in meters) representing railway station and line information for the specified areas.

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

This example retrieves information about two US railway stations and their associated railway lines based on specified station IDs and shapes.
"""
function us_rail(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 FROM na_stations WHERE ST_Intersects(ST_GeomFromText('"*shape*"'), SHAPE);"
        query2 = "SELECT SUM(ST_Length(ST_Intersection(SHAPE, ST_GeomFromText('"*shape*"')))) AS 'lines' FROM na_lines 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_rail_stations_lines(table::String, selection::Dict; host::String="127.0.0.1") -> DataFrame

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

This function allows for the retrieval of railway station and line information, including the number of railway stations and total length of railway 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 railway station and line information (e.g., "eu_rail", "us_rail").
- `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 railway stations), and `lines` (total length of railway lines in meters) representing railway station and line information for the specified geographic areas.

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

This example retrieves railway station and line information for two EU railway stations based on specified station IDs and shapes using the "eu_rail" table.
"""
function get_rail_stations_lines(table::String, selection::Dict; host::String="127.0.0.1")
    conn_dict = Dict(
        "host" => host,
        "port" => 3311,
        "database" => "FACT_rail",
        "user" => "root",
        "password" => "devops"
    )

    table_dict = Dict(
        "eu_rail" => eu_rail,
        "us_rail" => us_rail
    )

    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
        lines = Float64[] # column to total length of 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