app.py

"""
Standorte Map Visualizer
========================
Visualizes standorte data from any xlsx file with auto-detected columns.

Expected sheet layout (sheet name: "Standorte"):
  Row 1 — section names (e.g. "Betonproduktionsstandort"), one per group of columns
  Row 2 — column headers per section; the app recognises:
           "Ort"/"Stadt"/"Standort"           → location name used for city filter
           "Bundesland"/"State"/"Bundesstaat" → federal state used for state filter
           "Name"/"Bezeichnung"/"Firma"       → marker label
           "Link"/"Website"/"URL"             → popup hyperlink
           "Koordinaten" (or contains it)     → "lat, lon" coordinate string
           any other header                   → shown as extra field in popup
  Row 3+ — data

Requirements:
    pip install gradio folium openpyxl

Run:
    python app.py
"""

import gradio as gr
import folium
import openpyxl
import state_lookup


COLOR_PALETTE = [
    "#3388ff", "#e74c3c", "#27ae60", "#9b59b6", "#e67e22",
    "#003580", "#8B0000", "#006400", "#5F9EA0", "#FF69B4",
    "#FF8C00", "#2E8B57", "#DC143C", "#4169E1", "#DAA520",
]

_cached_filepath = None
_cached_section_configs = None
_cached_data = None


def parse_coords(coord_str):
    """Parse 'lat, lon' string → (float, float) or None."""
    if not coord_str:
        return None
    try:
        parts = str(coord_str).split(",")
        if len(parts) == 2:
            lat, lon = float(parts[0].strip()), float(parts[1].strip())
            if 47.0 <= lat <= 55.5 and 5.5 <= lon <= 15.5:
                return lat, lon
    except (ValueError, AttributeError):
        pass
    return None


def _cell(row, col):
    """Safe row accessor; returns None if col is None or out of range."""
    return row[col] if col is not None and len(row) > col else None


def detect_sections(header_row1, header_row2):
    """
    Auto-detect section boundaries and column roles from the two header rows.

    Row 1 non-empty cells mark where a new section begins.
    Row 2 cell text determines the role of each column within its section.

    Returns {section_name: {col_ort, col_state, col_name, col_link, col_coords, extra_cols, color}}
    """
    section_starts = [
        (col, str(val).strip())
        for col, val in enumerate(header_row1)
        if val and str(val).strip()
    ]

    section_configs = {}
    for i, (start_col, section_name) in enumerate(section_starts):
        end_col = section_starts[i + 1][0] if i + 1 < len(section_starts) else len(header_row2)

        col_ort = col_state = col_name_field = col_link = col_coords = None
        extra_cols = {}

        for col in range(start_col, end_col):
            if col >= len(header_row2) or not header_row2[col]:
                continue
            original = str(header_row2[col]).strip()
            h = original.lower()

            if h in ("ort", "stadt", "location", "standort"):
                col_ort = col
            elif h in ("bundesland", "state", "bundesstaat"):
                col_state = col
            elif h in ("name", "bezeichnung", "firma", "unternehmen"):
                col_name_field = col
            elif h in ("link", "website", "url", "homepage", "webseite"):
                col_link = col
            elif "koordinat" in h or h in ("coords", "coordinates", "lat/lon", "lat, lon"):
                col_coords = col
            else:
                extra_cols[original] = col

        if col_coords is None:
            continue

        section_configs[section_name] = {
            "col_ort":    col_ort,
            "col_state":  col_state,
            "col_name":   col_name_field,
            "col_link":   col_link,
            "col_coords": col_coords,
            "extra_cols": extra_cols,
            "color":      COLOR_PALETTE[i % len(COLOR_PALETTE)],
        }

    return section_configs


def load_data(filepath):
    """Parse the xlsx and return (section_configs, {section: [records]}). Results are cached."""
    global _cached_filepath, _cached_section_configs, _cached_data
    if filepath == _cached_filepath and _cached_data is not None:
        return _cached_section_configs, _cached_data

    wb = openpyxl.load_workbook(filepath, read_only=True)
    if "Standorte" not in wb.sheetnames:
        raise ValueError("Sheet 'Standorte' not found in the uploaded file.")
    ws = wb["Standorte"]
    rows = list(ws.iter_rows(values_only=True))
    if len(rows) < 3:
        raise ValueError("File must have at least 2 header rows and one data row.")

    section_configs = detect_sections(rows[0], rows[1])
    if not section_configs:
        raise ValueError(
            "Could not detect any sections. "
            "Row 1 must have section names and row 2 must include a coordinate column "
            "(header containing 'Koordinat' or named 'Coords'/'Coordinates')."
        )

    data_rows = rows[2:]
    result = {}
    for section, cfg in section_configs.items():
        records = []
        for row in data_rows:
            ort       = _cell(row, cfg["col_ort"])
            name      = _cell(row, cfg["col_name"])
            link      = _cell(row, cfg["col_link"])
            coord_raw = _cell(row, cfg["col_coords"])

            if not ort and not name:
                continue
            coords = parse_coords(coord_raw)
            if coords is None:
                continue

            if cfg["col_state"] is not None:
                state = _cell(row, cfg["col_state"]) or ""
            else:
                state = state_lookup.get_state(coords[0], coords[1])

            record = {
                "Ort":        ort   or "",
                "Bundesland": state,
                "Name":       name  or "",
                "Link":       link  or "",
                "lat":        coords[0],
                "lon":        coords[1],
            }
            for label, col in cfg["extra_cols"].items():
                record[label] = _cell(row, col) or ""
            records.append(record)
        result[section] = records

    _cached_filepath        = filepath
    _cached_section_configs = section_configs
    _cached_data            = result
    return section_configs, result


def get_all_cities(all_data):
    cities = set()
    for records in all_data.values():
        for rec in records:
            if rec["Ort"]:
                cities.add(rec["Ort"].strip())
    return sorted(cities)


def get_all_states(all_data):
    states = set()
    for records in all_data.values():
        for rec in records:
            if rec.get("Bundesland"):
                states.add(rec["Bundesland"].strip())
    return sorted(states)


def build_popup(record, section_cfg):
    html = f"<b>{record.get('Name','')}</b><br><i>{record.get('Ort','')}</i>"
    bundesland = record.get("Bundesland", "")
    if bundesland:
        html += f"<br><span style='color:#666;font-size:11px'>{bundesland}</span>"
    link = record.get("Link", "")
    if link:
        html += f'<br><a href="{link}" target="_blank">🔗 Website</a>'
    for label in section_cfg["extra_cols"]:
        val = record.get(label, "")
        if val:
            html += f"<br><b>{label}:</b><br>{val}"
    return html


def build_map_legend(section_configs):
    items = "".join(
        f'<span style="color:{cfg["color"]}">●</span> {section}<br>\n'
        for section, cfg in section_configs.items()
    )
    return (
        '<div style="position:fixed;bottom:30px;left:30px;z-index:1000;'
        'background:white;padding:12px 16px;border-radius:8px;'
        'border:1px solid #ccc;font-size:13px;line-height:1.8;'
        'box-shadow:2px 2px 6px rgba(0,0,0,0.2);">'
        f'<b>Legende</b><br>{items}</div>'
    )


def build_sidebar_legend(section_configs):
    if not section_configs:
        return "<p><em>Upload a file to see the colour legend.</em></p>"
    items = "".join(
        f'<div style="display:flex;align-items:center;margin-bottom:5px;">'
        f'<span style="width:13px;height:13px;border-radius:50%;background:{cfg["color"]};'
        f'display:inline-block;margin-right:8px;flex-shrink:0;"></span>'
        f'<span>{section}</span></div>'
        for section, cfg in section_configs.items()
    )
    return (
        f'<div style="font-size:13px;line-height:1.8;">'
        f'<b>Colour Legend</b><br>{items}'
        f'<p style="font-size:11px;color:#888;margin-top:6px;">'
        f'Click any marker for details.</p></div>'
    )


# ── Event handlers ────────────────────────────────────────────────────────────

def on_file_upload(filepath):
    empty = (
        gr.update(choices=[], value=[]),
        gr.update(choices=[], value=[]),
        "<p><em>Upload a file to see the colour legend.</em></p>",
        gr.update(choices=[], value=[]),
    )
    if filepath is None:
        return empty
    try:
        section_configs, all_data = load_data(filepath)
        cities   = get_all_cities(all_data)
        sections = list(section_configs.keys())
        states   = get_all_states(all_data)
        return (
            gr.update(choices=cities,   value=[]),
            gr.update(choices=sections, value=sections),
            build_sidebar_legend(section_configs),
            gr.update(choices=states,   value=[]),
        )
    except Exception as e:
        return (
            gr.update(choices=[], value=[]),
            gr.update(choices=[], value=[]),
            f"<p style='color:red'><em>Error reading file: {e}</em></p>",
            gr.update(choices=[], value=[]),
        )


def select_all_cities(filepath):
    if filepath is None:
        return gr.update(value=[])
    try:
        _, all_data = load_data(filepath)
        return gr.update(value=get_all_cities(all_data))
    except Exception:
        return gr.update(value=[])


def select_all_states(filepath):
    if filepath is None:
        return gr.update(value=[])
    try:
        _, all_data = load_data(filepath)
        return gr.update(value=get_all_states(all_data))
    except Exception:
        return gr.update(value=[])


def generate_map(
    filepath,
    selected_sections,
    city_filter_enabled,
    selected_cities,
    state_filter_enabled,
    selected_states,
):
    if filepath is None:
        return "<p style='color:red'>Please upload an xlsx file.</p>"
    if not selected_sections:
        return "<p style='color:orange'>Please select at least one data type to display.</p>"

    try:
        section_configs, all_data = load_data(filepath)
    except Exception as e:
        return f"<p style='color:red'>Error reading file: {e}</p>"

    filter_cities = None
    if city_filter_enabled and selected_cities:
        filter_cities = {c.strip() for c in selected_cities}

    filter_states = None
    if state_filter_enabled and selected_states:
        filter_states = {s.strip() for s in selected_states}

    m = folium.Map(location=[51.2, 10.4], zoom_start=6, tiles="CartoDB positron")

    total_points   = 0
    section_counts = {}

    for section in selected_sections:
        if section not in all_data:
            continue
        cfg     = section_configs[section]
        records = all_data[section]

        if filter_cities is not None:
            records = [r for r in records if r["Ort"].strip() in filter_cities]

        if filter_states is not None:
            records = [r for r in records if r.get("Bundesland", "").strip() in filter_states]

        layer = folium.FeatureGroup(name=section, show=True)
        for rec in records:
            folium.CircleMarker(
                location=[rec["lat"], rec["lon"]],
                radius=3,
                color=cfg["color"],
                fill=True,
                fill_color=cfg["color"],
                fill_opacity=0.75,
                tooltip=rec["Name"] or rec["Ort"],
                popup=folium.Popup(build_popup(rec, cfg), max_width=320),
            ).add_to(layer)
            total_points += 1

        section_counts[section] = len(records)
        layer.add_to(m)

    folium.LayerControl(collapsed=False).add_to(m)
    m.get_root().html.add_child(folium.Element(build_map_legend(section_configs)))

    stats_text = " | ".join(f"{s}: {n}" for s, n in section_counts.items())
    notes = []
    if filter_cities:
        notes.append(f"🏙️ {len(filter_cities)} cities")
    if filter_states:
        notes.append(f"🗺️ {len(filter_states)} states")
    filter_note = (
        f"<br><span style='color:#888'>{' | '.join(notes)} filtered</span>"
        if notes else ""
    )
    m.get_root().html.add_child(folium.Element(f"""
    <div style="position:fixed;top:10px;right:10px;z-index:1000;
                background:rgba(255,255,255,0.9);padding:8px 14px;border-radius:6px;
                border:1px solid #ccc;font-size:12px;
                box-shadow:2px 2px 6px rgba(0,0,0,0.15);">
        📍 {total_points} Standorte gesamt{filter_note}<br>
        <span style="color:#555">{stats_text}</span>
    </div>"""))

    return m._repr_html_()


# ──────────────────────────────────────────────
# Gradio UI
# ──────────────────────────────────────────────

with gr.Blocks(title="Standorte Karte Deutschland", theme=gr.themes.Soft()) as demo:

    gr.Markdown("""
    # 🗺️ Standorte in Deutschland
    Upload the xlsx file — sections and columns are detected automatically from the header rows.
    Choose data types, optionally filter by state or city, then generate the map.
    """)

    with gr.Row():

        # ── Left control panel ───────────────────────────────────────────────
        with gr.Column(scale=1):

            file_input = gr.File(
                label="📂 Upload xlsx File",
                file_types=[".xlsx"],
            )

            section_selector = gr.CheckboxGroup(
                choices=[],
                value=[],
                label="📋 Select Data Types",
            )

            # State filter
            with gr.Accordion("🗺️ State Filter (optional)", open=False):
                state_filter_toggle = gr.Checkbox(
                    label="Enable state filter",
                    value=False,
                )
                gr.Markdown(
                    "_Filter by federal state (Bundesland). "
                    "Requires a 'Bundesland' column in your Excel file._"
                )
                state_selector = gr.Dropdown(
                    choices=[],
                    value=[],
                    multiselect=True,
                    label="Select States",
                    allow_custom_value=False,
                    interactive=True,
                )
                with gr.Row():
                    select_all_states_btn = gr.Button("✅ Select All", size="sm")
                    clear_states_btn      = gr.Button("🗑️ Clear",      size="sm")

            # City filter
            with gr.Accordion("🏙️ City Filter (optional)", open=False):
                city_filter_toggle = gr.Checkbox(
                    label="Enable city filter",
                    value=False,
                )
                gr.Markdown(
                    "_Type to search, or scroll to browse. "
                    "Only selected cities will appear on the map. "
                    "Leave empty (or disable) to show all cities._"
                )
                city_selector = gr.Dropdown(
                    choices=[],
                    value=[],
                    multiselect=True,
                    label="Select Cities",
                    allow_custom_value=False,
                    interactive=True,
                )
                with gr.Row():
                    select_all_btn = gr.Button("✅ Select All", size="sm")
                    clear_btn      = gr.Button("🗑️ Clear",      size="sm")

            run_btn = gr.Button("🗺️ Generate Map", variant="primary")

            legend_html = gr.HTML(
                value="<p><em>Upload a file to see the colour legend.</em></p>"
            )

        # ── Map output ───────────────────────────────────────────────────────
        with gr.Column(scale=3):
            map_output = gr.HTML(
                value="<p style='color:#888;padding:40px;text-align:center;'>"
                      "Upload a file and click <b>Generate Map</b> to begin.</p>",
            )

    # ── Event wiring ─────────────────────────────────────────────────────────

    file_input.change(
        fn=on_file_upload,
        inputs=[file_input],
        outputs=[city_selector, section_selector, legend_html, state_selector],
    )

    select_all_states_btn.click(
        fn=select_all_states,
        inputs=[file_input],
        outputs=[state_selector],
    )

    clear_states_btn.click(
        fn=lambda: gr.update(value=[]),
        inputs=[],
        outputs=[state_selector],
    )

    select_all_btn.click(
        fn=select_all_cities,
        inputs=[file_input],
        outputs=[city_selector],
    )

    clear_btn.click(
        fn=lambda: gr.update(value=[]),
        inputs=[],
        outputs=[city_selector],
    )

    run_btn.click(
        fn=generate_map,
        inputs=[
            file_input,
            section_selector,
            city_filter_toggle,
            city_selector,
            state_filter_toggle,
            state_selector,
        ],
        outputs=[map_output],
    )


if __name__ == "__main__":
    demo.launch(share=False, server_port=7860, inbrowser=True)