"""Per-post detail, auto-parsed from LinkedIn's "Export" button on an individual
post's own analytics page ("SinglePostAnalytics_<Full Name>_<id>.xlsx") - the one
and only sheet, "Post analytics", is a key/value list rather than a table: Post URL
and date up top, then Discovery (Impressions, Members reached), Profile activity
(Profile viewers/Followers gained from this post), and Engagement (Social
engagements plus the Reactions/Comments/Reposts/Saves/Sends/Link/Premium-button
breakdown that's all blended into one "Engagements" number in the Aggregate
Analytics file). A "Post viewer demographics" section follows for posts with
enough reach to populate it.

Started as a hand-maintained dict; replaced once it turned out every individual
post's analytics page has its own Export button giving this exact breakdown - no
manual reading-off-the-screen needed after all. Matched to a post by the file's own
"Post URL" row, not by the numeric ID in the filename (confirmed those two IDs
don't actually match - the filename's id and the post's own activity id in the URL
are a different LinkedIn numbering scheme), so the matching is self-describing and
doesn't depend on guessing a naming convention.
"""

import os

import pandas as pd


def _is_single_post_export(fname):
    lower = fname.lower()
    if lower.startswith("~$") or lower.startswith("."):
        return False
    if "singlepostanalytics_" not in lower:
        return False
    return lower.endswith((".xlsx", ".xls"))


def _find_files(data_dir, upload_dir):
    candidates = []
    for folder in (data_dir, upload_dir):
        if not os.path.isdir(folder):
            continue
        for fname in os.listdir(folder):
            if _is_single_post_export(fname):
                candidates.append(os.path.join(folder, fname))
    return sorted(set(candidates), key=os.path.getmtime)


def _parse_one(fp):
    raw = pd.read_excel(fp, sheet_name="Post analytics", header=None)
    kv = {}
    for _, row in raw.iterrows():
        key, val = row[0], row[1]
        if pd.notna(key) and pd.notna(val):
            kv[str(key).strip()] = val

    def _int(key):
        v = kv.get(key)
        try:
            return int(v)
        except (TypeError, ValueError):
            return None

    url = kv.get("Post URL")
    if not url:
        return None, None
    return url, {
        "impressions": _int("Impressions"),
        "members_reached": _int("Members reached"),
        "profile_viewers": _int("Profile viewers from this post"),
        "followers_gained": _int("Followers gained from this post"),
        "social_engagements": _int("Social engagements"),
        "reactions": _int("Reactions"),
        "comments": _int("Comments"),
        "reposts": _int("Reposts"),
        "saves": _int("Saves"),
        "sends": _int("Sends on LinkedIn"),
        "link_engagements": _int("Link engagements"),
        "premium_button_engagements": _int("Premium custom button engagements"),
        "source_file": os.path.basename(fp),
    }


def load_post_details(data_dir, upload_dir):
    """{post_url: {...}} across every SinglePostAnalytics file found - if the same
    post was exported more than once, the most recently modified file wins."""
    details = {}
    for fp in _find_files(data_dir, upload_dir):
        url, parsed = _parse_one(fp)
        if url:
            details[url] = parsed
    return details