Route /data/user_input

user_input is the country code (CODE_ISO3) that is use in the web address. For now year is 2024 (as a string) This data is created in the app.py file and loaded in data.js as a json. data.fsi returns the result of query1 and so on

Query 1 : Last year FSI (could be removed)

SELECT e.iso3, e.country, year_fsi, total,
    c1_security_apparatus, c2_factionalized_elites, c3_group_grievance, e1_economy,
    e2_economic_inequality, e3_human_flight_brain_drain, p1_state_legitimacy, p2_public_services,
    p3_human_rights, s1_demographic_pressures, s2_refugees_idps, x1_external_intervention
    FROM  draft_schema.fsi_exports as f 
    LEFT JOIN draft_schema.equivalency_table as e 
    ON f.country=e.fsi_name 
    WHERE (e.iso3 LIKE :user_input and f.year_fsi="""+year+""")""")

Query 2 : All year FSI

SELECT e.iso3, f.country, year_fsi, total,
    c1_security_apparatus, c2_factionalized_elites, c3_group_grievance, e1_economy,
    e2_economic_inequality, e3_human_flight_brain_drain, p1_state_legitimacy, p2_public_services,
    p3_human_rights, s1_demographic_pressures, s2_refugees_idps, x1_external_intervention
    FROM  draft_schema.fsi_exports as f 
    LEFT JOIN draft_schema.equivalency_table as e 
    ON f.country=e.fsi_name 
    WHERE e.iso3 LIKE :user_input""")

Query 2 : Last year FSI and region average

    query2a = text("""SELECT e.iso3, e.region, f.country, year_fsi, total,
    c1_security_apparatus, c2_factionalized_elites, c3_group_grievance, e1_economy,
    e2_economic_inequality, e3_human_flight_brain_drain, p1_state_legitimacy, p2_public_services,
    p3_human_rights, s1_demographic_pressures, s2_refugees_idps, x1_external_intervention
FROM draft_schema.fsi_exports AS f
LEFT JOIN draft_schema.equivalency_table AS e
ON f.country = e.fsi_name
WHERE e.iso3 LIKE :user_input AND f.year_fsi = 2024

UNION ALL

SELECT 'AVG' AS iso3, e.region, CONCAT(e.region, ' Average') AS country, 2024 AS year_fsi, AVG(total) AS total,
    AVG(c1_security_apparatus) AS c1_security_apparatus, AVG(c2_factionalized_elites) AS c2_factionalized_elites, AVG(c3_group_grievance) AS c3_group_grievance, AVG(e1_economy) AS e1_economy,
    AVG(e2_economic_inequality) AS e2_economic_inequality, AVG(e3_human_flight_brain_drain) AS e3_human_flight_brain_drain, AVG(p1_state_legitimacy) AS p1_state_legitimacy, AVG(p2_public_services) AS p2_public_services,
    AVG(p3_human_rights) AS p3_human_rights, AVG(s1_demographic_pressures) AS s1_demographic_pressures, AVG(s2_refugees_idps) AS s2_refugees_idps, AVG(x1_external_intervention) AS x1_external_intervention
FROM draft_schema.fsi_exports AS f
LEFT JOIN draft_schema.equivalency_table AS e
ON f.country = e.fsi_name
WHERE e.region = (SELECT region FROM draft_schema.equivalency_table WHERE iso3 LIKE :user_input)
AND f.year_fsi = 2024
group by e.region""")

Query 3 : acled_index

select * from  draft_schema.acled_index as a where a.iso3 LIKE :user_input

Query 4 : inform_index (unused)

select * from draft_schema.inform_index where iso3 LIKE :user_input

Query 5 : narrative

SELECT current_text FROM draft_schema.narrative WHERE narrative.iso3 LIKE :user_input

Query 6 : timeline

select json_text FROM draft_schema.timeline WHERE timeline.iso3 LIKE :user_input

Query 7 : acled_index_def

select * from draft_schema.acled_index_def

Query 8 : IHL

SELECT iso3, oecd, cahra, country,region, wbfcs FROM draft_schema.equivalency_table WHERE equivalency_table.iso3 LIKE :user_input

Query 9 : RULAC

SELECT appear FROM draft_schema.rulac AS r LEFT JOIN draft_schema.equivalency_table AS e ON r.country = e.fsi_name WHERE e.iso3 LIKE :user_input

Query 10 : FSI definitions

select * from draft_schema.fsi_definitions

Query 10b : FSI categories

select * from draft_schema.fsi_categories

Query 11 : ACLED events data

    query11 = text("SELECT iso,event_date,event_id_cnty,disorder_type,event_type,sub_event_type,admin1,admin2,admin3,location,actor1,actor2,assoc_actor_1,assoc_actor_2,inter1,inter2,interaction,civilian_targeting,fatalities,longitude,latitude,notes,source_scale,geom,ST_AsGeoJSON(geom) FROM draft_schema.acled_events LEFT JOIN draft_schema.equivalency_table ON acled_events.iso =equivalency_table.iso3166num where equivalency_table.iso3 like :user_input")
    gdf = gpd.read_postgis(query11, engine, geom_col='geom',params={"user_input": f"%{user_input}%"})
    gdf['event_date'] = gdf['event_date'].astype(str)
    #"gdf:\n%s", gdf.head(2))
    df11 = gdf.to_json()
    ```
## Query 12 : Salient narrative
```sql    
SELECT current_text FROM draft_schema.salient WHERE salient.iso3 LIKE :user_input

Query 13 : Due diligence

SELECT sector,current_text FROM draft_schema.due_diligence WHERE due_diligence.iso3 LIKE :user_input

Query 14 : ACLED actors (unused, for network graph)

SELECT actor1,actor2,assoc_actor_1,assoc_actor_2,inter1,inter2,fatalities FROM draft_schema.acled_events LEFT JOIN draft_schema.equivalency_table ON acled_events.iso =equivalency_table.iso3166num where equivalency_table.iso3 like :user_input

Actors information is separated in 6 columns: actor1,actor2,assoc_actor_1,assoc_actor_2,inter1,inter2. The inter columns give information about the category of actors but for actors present in associate actors columns, the information may be missing. These columns also can have more than one actor. So the challenge is to build back the relation between an event and all the actors.

# Create a DataFrame with actors and interactions
actors = []
# Collect all actors names 
for row in df14:
    if row['actor1']:
        actors.append({'actor': row['actor1']})
    if row['actor2']:
        actors.append({'actor': row['actor2']})
    if row['assoc_actor_1']:
        # if there is a ";" there is more than an actor, divide the string
        if ";" in row['assoc_actor_1']:
            for part in row['assoc_actor_1'].split(';'):
                actors.append({'actor': part.strip()})
        else:
            actors.append({'actor': row['assoc_actor_1']})
    if row['assoc_actor_2']:
        if ";" in row['assoc_actor_2']:
            for part in row['assoc_actor_2'].split(';'):
                actors.append({'actor': part.strip()})
        else:
            actors.append({'actor': row['assoc_actor_2']})

actors_df = pd.DataFrame(actors).drop_duplicates()
interactions = []
# Create list
for row in df14:
    interactions.append({'actor': row['actor1'], 'category': row['inter1']})
    if row['actor2']:
        interactions.append({'actor': row['actor2'], 'category': row['inter2']})
    if row['assoc_actor_1']:
        interactions.append({'actor': row['assoc_actor_1'], 'category': 'unknown'})
    if row['assoc_actor_2']:
        interactions.append({'actor': row['assoc_actor_2'], 'category': 'unknown'})

interactions_df = pd.DataFrame(interactions).drop_duplicates()

# Remove "unknown" interactions if there is another interaction for the same actor
known_interactions = interactions_df[interactions_df['category'] != 'unknown']
unknown_interactions = interactions_df[interactions_df['category'] == 'unknown']
filtered_unknown_interactions = unknown_interactions[~unknown_interactions['actor'].isin(known_interactions['actor'])]
interactions_df = pd.concat([known_interactions, filtered_unknown_interactions]).drop_duplicates()

data14 = pd.merge(actors_df, interactions_df, on='actor', how='left')

# Change NaN values in the 'interaction' column to 'unknown'
data14['category'] = data14['category'].fillna('unknown')
data14 = data14.to_dict(orient='records')

Query 15 : Extract information for describing outliers Admin level 1

SELECT a.admin1, EXTRACT(YEAR FROM a.event_date) AS year_acled,
    COUNT(a.*) AS event_count, sum(a.fatalities) as fatalities_count
    FROM draft_schema.acled_events AS a
    LEFT JOIN draft_schema.equivalency_table AS e
    ON a.iso = e.iso3166num
    WHERE e.iso3 LIKE :user_input GROUP BY a.admin1, year_acled
    ORDER BY year_acled, a.admin1;""")

df15 = pd.read_sql(query15, engine, params={"user_input": f"%{user_input}%"})
df15_grouped = df15.groupby('admin1').apply(lambda x: {
    'year_acled': x['year_acled'].tolist(),
    'event_count': x['event_count'].tolist(),
    'fatalities_count': x['fatalities_count'].tolist()
}).to_dict()

Query 16 : Extract information for describing outliers sub event type

SELECT a.sub_event_type, EXTRACT(YEAR FROM a.event_date) AS year_acled,
COUNT(a.*) AS event_count, sum(a.fatalities) as fatalities_count
FROM draft_schema.acled_events AS a
LEFT JOIN draft_schema.equivalency_table AS e
ON a.iso = e.iso3166num
WHERE e.iso3 LIKE :user_input GROUP BY a.sub_event_type, year_acled
ORDER BY year_acled, a.sub_event_type;""")

Fetch data

This data is not directly load with the rest. It contains geospatial information used on the webmap (ICCA and Views dataset) that has been entered manually, usually after creating a geojson file and loading it in the database. The python code is transofrming the collected data in a geodatabase that is then passed as json.

def load_icca_point(user_input):
    query = text("""SELECT s.iso3,name_eng, objectives, manag_auth, habit_type,comm_name, threats, geom,ST_AsGeoJSON(geom) 
    FROM draft_schema.icca_point as s 
    LEFT JOIN draft_schema.equivalency_table as e 
    ON s.iso3 =e.iso3
    where e.iso3 like :user_input""")
    gdf = gpd.read_postgis(query, engine, geom_col='geom',params={"user_input": f"%{user_input}%"})
    geojson = gdf.to_json()
    return jsonify(geojson)
    ```

## Javascript call
```js
fetch('/load_icca_point/'+data.iso3)
    .then(response => {
        // Log the response object
        return response.json();
    })
    .then(data => {
        console.log('ICCA Data:', data)
        ...
    })