"""Summarize and rank the Steam sales estimates produced by salesModel.py.

This is the ranking script used for the supporting market analysis published by
Emergence Interactive. The reported analysis was run on March 1, 2026. It
reads the per-game CSV files in ``DATA_DIR``, removes byte-for-byte duplicate
files, reports dataset diagnostics and percentile thresholds, and evaluates a
conservative sensitivity case for titles without usable sales estimates.

The constants below record the price, platform-fee, revenue-target, and input
directory assumptions used for that analysis. Adjust ``DATA_DIR`` only if the
downloaded data package is extracted to a different location.

Requirements: Python 3.10+ and pandas.
Run: ``python rankGames.py``
"""

import hashlib
import re
from bisect import bisect_left, bisect_right
from math import ceil
from pathlib import Path

import pandas as pd

PRICE_USD = 19.99
PLATFORM_FEE = 0.30
REVENUE_TARGET_USD = 1_000_000

DATA_DIR = Path(
    "Download 2026-02-25T18-23-42-967Z/openWorldSurvivalCraft/games"
)

rows = []

for f in DATA_DIR.glob("*.csv"):
    try:
        file_df = pd.read_csv(f)

        if file_df.empty or "cum_sales_mid" not in file_df.columns:
            continue

        # Remove commas if present, then convert to numeric.
        sales_column = pd.to_numeric(
            file_df["cum_sales_mid"].astype(str).str.replace(",", ""),
            errors="coerce"
        ).dropna()

        if sales_column.empty:
            continue

        final_sales = sales_column.iloc[-1]

        # Extract the Steam app ID from the filename.
        match = re.search(
            r"_([0-9]+)(?:\s*\(\d+\))?\.csv$",
            f.name
        )
        appid = match.group(1) if match else "?"

        if len(rows) < 5:
            possible_date_columns = [
                column for column in file_df.columns
                if column.lower() in ("date", "timestamp", "datetime")
            ]

            print(f"\nFILE: {f.name}")
            print(f"Rows: {len(file_df)}")

            if possible_date_columns:
                date_column = possible_date_columns[0]

                print(
                    file_df[
                        [date_column, "cum_sales_mid"]
                    ].head(3).to_string(index=False)
                )

                print("...")

                print(
                    file_df[
                        [date_column, "cum_sales_mid"]
                    ].tail(3).to_string(index=False)
                )
            else:
                print(
                    file_df["cum_sales_mid"]
                    .iloc[[0, -1]]
                    .to_string(index=False)
                )

        available_sales_columns = [
            column for column in (
                "cum_sales_low",
                "cum_sales_mid",
                "cum_sales_high"
            )
            if column in file_df.columns
        ]

        record = {
            "file": f.name,
            "appid": appid,
            "sales": final_sales
        }

        for column in available_sales_columns:
            values = pd.to_numeric(
                file_df[column].astype(str).str.replace(",", ""),
                errors="coerce"
            ).dropna()

            record[column] = values.iloc[-1] if not values.empty else None

        rows.append(record)

    except Exception as error:
        print(f"Skipped {f.name}: {error}")

df = pd.DataFrame(rows)

if df.empty:
    raise RuntimeError("No valid sales data found.")


############################### DIAGNOSTIC ###############################
print("\nDATASET DIAGNOSTICS")
print(f"CSV files found: {len(list(DATA_DIR.glob('*.csv')))}")
print(f"Valid game records: {len(df)}")
print(f"Unmatched app IDs: {(df['appid'] == '?').sum()}")

known_appids = df[df["appid"] != "?"]

print(f"Unique recognized app IDs: {known_appids['appid'].nunique()}")
print(
    f"Records with duplicated app IDs: "
    f"{known_appids.duplicated('appid', keep=False).sum()}"
)

duplicate_records = known_appids[
    known_appids.duplicated("appid", keep=False)
].sort_values(["appid", "sales"])

if not duplicate_records.empty:
    print("\nDUPLICATED APP IDs")
    print(
        duplicate_records[
            ["appid", "file", "sales"]
        ].to_string(index=False)
    )

print("\nCURRENT SALES THRESHOLDS")
print(
    df["sales"]
    .quantile([0.50, 0.75, 0.80, 0.90, 0.95])
    .rename(index={
        0.50: "50th",
        0.75: "75th",
        0.80: "80th",
        0.90: "90th",
        0.95: "95th"
    })
)

print("\nSAMPLE UNMATCHED FILENAMES")
print(
    df.loc[df["appid"] == "?", "file"]
    .head(30)
    .to_string(index=False)
)

def file_hash(filename: str) -> str:
    """Return a SHA-256 hash used to identify duplicate CSV contents."""
    path = DATA_DIR / filename
    return hashlib.sha256(path.read_bytes()).hexdigest()

df["file_hash"] = df["file"].apply(file_hash)

print("\nFILE-HASH DIAGNOSTICS")
print(f"Valid records: {len(df)}")
print(f"Unique file contents: {df['file_hash'].nunique()}")
print(
    f"Records belonging to duplicated-content groups: "
    f"{df.duplicated('file_hash', keep=False).sum()}"
)

unique_df = df.drop_duplicates("file_hash", keep="first").copy()

valid_sales = sorted(
    unique_df["sales"]
    .dropna()
    .tolist()
)

all_appids = set()
unmatched_files = []

for f in DATA_DIR.glob("*.csv"):
    match = re.search(
        r"_([0-9]+)(?:\s*\(\d+\))?\.csv$",
        f.name
    )

    if match:
        all_appids.add(match.group(1))
    else:
        unmatched_files.append(f.name)

valid_appids = set(
    unique_df.loc[
        unique_df["appid"] != "?",
        "appid"
    ]
)

excluded_appids = all_appids - valid_appids
excluded_game_count = len(excluded_appids)

print(f"Unique tagged titles: {len(all_appids)}")
print(f"Titles with usable sales data: {len(valid_appids)}")
print(f"Titles without usable sales data: {excluded_game_count}")
print(f"Unmatched filenames: {len(unmatched_files)}")

# Sensitivity case: assign excluded titles one nominal sale, placing them below
# the observed sales range.
all_title_sales = sorted(
    valid_sales + [1] * excluded_game_count
)

print("\nPERCENTILE RANKS — GAMES WITH USABLE SALES DATA")

# Estimate the percentile associated with one million units sold.
UNIT_TARGET = 1_000_000


def report_million_unit_threshold(label: str, dataset: list[float]) -> None:
    """Report the position of the one-million-unit threshold in a dataset."""
    total_games = len(dataset)

    first_at_or_above = bisect_left(dataset, UNIT_TARGET)

    games_below = first_at_or_above
    games_at_or_above = total_games - first_at_or_above

    cutoff_percentile = 100 * games_below / total_games
    upper_tail_share = 100 * games_at_or_above / total_games

    print(f"\n{label}")
    print(f"Total titles: {total_games}")
    print(f"Titles below 1,000,000 units: {games_below}")
    print(f"Titles at or above 1,000,000 units: {games_at_or_above}")
    print(
        f"One million units marks approximately the "
        f"{cutoff_percentile:.1f}th-percentile cutoff."
    )
    print(
        f"Approximately {upper_tail_share:.1f}% of titles "
        f"sold at least one million units."
    )


print("\nONE-MILLION-UNIT SALES THRESHOLD")

report_million_unit_threshold(
    "Games with usable sales estimates",
    valid_sales
)

report_million_unit_threshold(
    "All-title sensitivity case",
    all_title_sales
)

million_plus_games = (
    unique_df.loc[
        unique_df["sales"] >= UNIT_TARGET,
        ["file", "appid", "sales"]
    ]
    .sort_values("sales", ascending=False)
)

print("\nTITLES AT OR ABOVE ONE MILLION UNITS")
print(million_plus_games.to_string(index=False))

# Estimate the unit sales required for $1 million in revenue.
gross_revenue_units = ceil(
    REVENUE_TARGET_USD / PRICE_USD
)

net_revenue_per_unit = PRICE_USD * (1 - PLATFORM_FEE)

net_revenue_units = ceil(
    REVENUE_TARGET_USD / net_revenue_per_unit
)

print("\n$1 MILLION REVENUE THRESHOLDS")
print(
    f"$1M gross revenue requires approximately "
    f"{gross_revenue_units:,} sales."
)
print(
    f"$1M net revenue requires approximately "
    f"{net_revenue_units:,} sales."
)

def report_percentile(label: str, target: int, dataset: list[float]) -> None:
    """Report the percentile rank and upper-tail share for a sales target."""
    count = bisect_right(dataset, target)
    percentile = 100 * count / len(dataset)
    upper_share = 100 - percentile

    print(
        f"{label}: {target:,} sales corresponds to "
        f"percentile rank {percentile:.1f}; "
        f"approximately the top {upper_share:.1f}% exceed it."
    )

print("\nGAMES WITH USABLE SALES DATA")

report_percentile(
    "$1M gross revenue",
    gross_revenue_units,
    valid_sales
)

report_percentile(
    "$1M net revenue",
    net_revenue_units,
    valid_sales
)

print("\nALL-TITLE SENSITIVITY CASE")

report_percentile(
    "$1M gross revenue",
    gross_revenue_units,
    all_title_sales
)

report_percentile(
    "$1M net revenue",
    net_revenue_units,
    all_title_sales
)


for target in (12_000, 22_900, 100_000, 250_000):
    count = bisect_right(valid_sales, target)
    percentile = 100 * count / len(valid_sales)

    print(
        f"{target:,} sales: percentile rank {percentile:.1f} "
        f"({count} of {len(valid_sales)} at or below)"
    )

print(
    "\nPERCENTILE RANKS — ALL TITLES, "
    "EXCLUDED TITLES ASSIGNED A NOMINAL VALUE OF ONE"
)

for target in (12_000, 22_900, 100_000, 250_000):
    count = bisect_right(all_title_sales, target)
    percentile = 100 * count / len(all_title_sales)

    print(
        f"{target:,} sales: percentile rank {percentile:.1f} "
        f"({count} of {len(all_title_sales)} at or below)"
    )

print("\nALL-TITLE SALES THRESHOLDS")

print(
    pd.Series(all_title_sales)
    .quantile([0.50, 0.75, 0.80, 0.90, 0.95])
)

############################### END DIAGNOSTIC ###############################

# Retain descending and ascending views for optional interactive inspection.
df = unique_df.sort_values("sales", ascending=False)
sales = sorted(unique_df["sales"].tolist())