import gc
import json
import sys
import time
import datetime

from rich import print_json

from pybarb.connection.connection import Connection
from pybarb.utils.logging_config import get_logger

logger = get_logger(__name__)


# ── Shared helper ──────────────────────────────────────────────────────────────

def _section(title: str) -> None:
    """Print a clearly visible section banner."""
    print(f"\n{'=' * 60}\n  {title}\n{'=' * 60}")


# ══════════════════════════════════════════════════════════════════════════════
#  Recommended connection demo
# ══════════════════════════════════════════════════════════════════════════════

def demo_connect() -> None:
    """
    Demonstrate connect() with a live expiry countdown and automatic refresh.

    This uses the configured token store and BARB_REFRESH_TOKEN fallback described
    in the connection setup above.

    Steps demonstrated:
      1. Connect using the recommended Connection.connect() method.
      2. Stored tokens are reused or refreshed as required.
      3. Make an initial API call to prove the token works.
      4. Shorten the token expiry to 35 s for this demonstration only.
      5. Live countdown bar shows remaining token lifetime.
      6. When the token enters the 30-second safety buffer, ensure_token_valid()
         fires automatically, posts a refresh_token grant, and rotates the token.
      7. A second API call proves the refreshed token works.

    """
    from pybarb.metadata.station import Station
    from pybarb.metrics.programme import ProgrammeRatings

    logger.info("=== Demo: connect() + Auto-Refresh ===")

    # ── Establish connection ───────────────────────────────────────────────────
    conn = Connection()
    conn.connect()

    logger.info("Connection established.")
    logger.info("expires_at           : %s", conn._expires_at)
    logger.info("refresh_token present: True")

    # ── API call 1 — prove the token works immediately ─────────────────────────
    logger.info("--- API call 1: Stations endpoint (original token) ---")
    station_client = Station(conn)
    stations = station_client.get_stations()
    logger.info("Stations endpoint returned %d station(s).", len(stations))
    if stations:
        print(f"\nFirst station: {stations[0]}\n")
    del station_client, stations

    # ── Shorten expiry for demo countdown ──────────────────────────────────────
    short_expiry = datetime.datetime.now(datetime.UTC) + datetime.timedelta(seconds=35)
    conn._expires_at = short_expiry
    token_before = conn.headers.get("Authorization", "") if conn.headers else ""

    logger.info(
        "Demo expiry shortened to 35 s (at %s). "
        "Refresh will fire when ≤30 s remain.",
        short_expiry,
    )

    # ── Live countdown ─────────────────────────────────────────────────────────
    print("=" * 60)
    print("  Countdown to token refresh  (buffer = 30 s)")
    print("=" * 60)

    refresh_triggered = False
    while True:
        remaining = (
            conn._expires_at - datetime.datetime.now(datetime.UTC)
        ).total_seconds()
        bar_len   = 30
        fill      = max(0, int((remaining / 35) * bar_len))
        bar       = "█" * fill + "░" * (bar_len - fill)
        sys.stdout.write(
            f"\r  Remaining: {max(0, remaining):5.1f}s  [{bar}]  "
            f"expired={conn.is_token_expired}   "
        )
        sys.stdout.flush()

        if conn.is_token_expired:
            print()  # newline after bar
            logger.info("Token entered expiry buffer — calling ensure_token_valid()...")
            conn.ensure_token_valid()
            refresh_triggered = True
            break

        if remaining <= 0:
            print()
            logger.warning("Token fully expired before refresh was triggered.")
            break

        time.sleep(0.5)

    # ── Refresh results ────────────────────────────────────────────────────────
    token_after = conn.headers.get("Authorization", "") if conn.headers else ""
    print("\n" + "=" * 60)
    logger.info("─── Refresh Results ─────────────────────────────────────")
    logger.info("Refresh triggered    : %s", refresh_triggered)
    logger.info("Token rotated        : %s", token_before != token_after)
    logger.info("expires_at after     : %s", conn._expires_at)
    logger.info("is_token_expired now : %s", conn.is_token_expired)
    if token_before != token_after:
        logger.info("✔ Auto-refresh SUCCESS — new access token obtained via refresh token.")
    else:
        logger.warning("⚠ Token value unchanged — server may reuse tokens.")
    print("=" * 60)

    # ── API call 2 — prove refreshed token works ───────────────────────────────
    logger.info("--- API call 2: Programme Ratings (refreshed token) ---")
    programme_ratings = ProgrammeRatings(conn)
    df = programme_ratings.get_programme_ratings_flat_dataframe(
        min_transmission_date='2026-05-06',
        max_transmission_date='2026-05-06',
        panel_code=50,
        consolidated=False,
        limit=500,
    )
    if not df.empty:
        print(df.head(5).to_string(index=False))
    else:
        print("No programme ratings data returned.")
    del programme_ratings, df
    gc.collect()


# ══════════════════════════════════════════════════════════════════════════════
#  Metadata demos
# ══════════════════════════════════════════════════════════════════════════════

def demo_panels(conn: Connection) -> None:
    """Demo: Panels metadata endpoint."""
    _section("PANELS")
    from pybarb.metadata.panels import Panels
    from pybarb.utils.dataframe_utils import to_dataframe

    panels = Panels(conn)
    panels_data = panels.get_panels()
    panel_df = to_dataframe(panels_data)
    print(panel_df.head(5).to_string(index=False))
    del panels, panels_data, panel_df
    gc.collect()


def demo_stations(conn: Connection) -> None:
    """Demo: Stations metadata endpoint."""
    _section("STATIONS")
    from pybarb.metadata.station import Station
    from pybarb.utils.dataframe_utils import to_dataframe

    stations = Station(conn)
    stations_data = stations.get_stations()
    stations_df = to_dataframe(stations_data)
    print(stations_df.head(5).to_string(index=False))
    del stations, stations_data, stations_df
    gc.collect()


def demo_viewing_stations(conn: Connection) -> None:
    """Demo: Viewing stations metadata endpoint."""
    _section("VIEWING STATIONS")
    from pybarb.metadata.viewing_stations import ViewingStations

    viewing_stations = ViewingStations(conn)
    data = viewing_stations.get_viewing_stations_flat_data_frame()
    print(data.head(5).to_string(index=False))
    del viewing_stations, data
    gc.collect()


def demo_households(conn: Connection) -> None:
    """Demo: Households metadata endpoint."""
    _section("HOUSEHOLDS")
    from pybarb.metadata.households import Households

    households = Households(conn)
    df = households.get_households_flat_dataframe(
        panel_end_date='2025-05-01',
        panel_start_date='2025-01-01',
    )
    print(df.head(5).to_string(index=False))
    del households, df
    gc.collect()


def demo_panel_members(conn: Connection) -> None:
    """Demo: Panel Members metadata endpoint."""
    _section("PANEL MEMBERS")
    from pybarb.metadata.panel_members import PanelMembers

    panelmember = PanelMembers(conn)
    df = panelmember.get_panel_members_flat_dataframe(
        last_updated_greater_than='2025-01-01',
        panel_start_date='2025-01-01',
        panel_end_date='2025-01-01',
        panel_code=1,
    )
    print(df.head(5).to_string(index=False))
    del panelmember, df
    gc.collect()


def demo_target_audience_categories(conn: Connection) -> None:
    """Demo: Target Audience Categories metadata endpoint."""
    _section("TARGET AUDIENCE CATEGORIES")
    from pybarb.metadata.target_audience_categories import TargetAudienceCategories

    tac = TargetAudienceCategories(conn)
    df = tac.get_target_audience_categories_flat_dataframe(
        max_date='2025-01-01',
        min_date='2025-01-01',
        panel_code=[1, 2, 3, 4, 5, 6, 7, 8, 9, 11],
    )
    print(df.head(5).to_string(index=False))
    del tac, df
    gc.collect()


def demo_spot_schedule_metadata(conn: Connection) -> None:
    """Demo: Spot Schedule metadata endpoint."""
    _section("SPOT SCHEDULE (METADATA)")
    from pybarb.metadata.spot_schedule import SpotSchedule

    spotschedule = SpotSchedule(conn)
    df = spotschedule.get_spot_schedule_flat_dataframe(
        max_scheduled_date='2025-01-01',
        min_scheduled_date='2025-01-01',
        station_code='30',
    )
    print(df.head(5).to_string(index=False))
    del spotschedule, df
    gc.collect()


def demo_programme_schedule_metadata(conn: Connection) -> None:
    """Demo: Programme Schedule metadata endpoint."""
    _section("PROGRAMME SCHEDULE (METADATA)")
    from pybarb.metadata.programme_schedule import ProgrammeSchedule

    prg_schedule = ProgrammeSchedule(conn)
    df = prg_schedule.get_programme_schedule_flat_dataframe(
        max_schedule_date='2024-01-01',
        min_schedule_date='2024-01-01',
        station_code=['10', '20'],
    )
    print(df.head(5).to_string(index=False))
    del prg_schedule, df
    gc.collect()


def demo_split_station_factor(conn: Connection) -> None:
    """Demo: Split Station Factor metadata endpoint."""
    _section("SPLIT STATION FACTOR")
    from pybarb.metadata.split_station_factor import SplitStationFactor
    from pybarb.utils.dataframe_utils import to_dataframe

    split_station_factor = SplitStationFactor(conn)
    split_station_factor_data = split_station_factor.get_split_station_factor()
    df = to_dataframe(split_station_factor_data)
    print(df.head(5).to_string(index=False))
    del split_station_factor, split_station_factor_data, df
    gc.collect()


def demo_transmission_log_programme_details(conn: Connection) -> None:
    """Demo: Transmission Log Programme Details metadata endpoint."""
    _section("TRANSMISSION LOG PROGRAMME DETAILS")
    from pybarb.metadata.transmission_log_programme_details import TransmissionLogProgrammeDetails
    from pybarb.utils.dataframe_utils import to_dataframe

    trns_log_prg_details = TransmissionLogProgrammeDetails(conn)
    trans_log_prg_details_data = trns_log_prg_details.list_transmission_log_programme_details('music')
    df = to_dataframe(trans_log_prg_details_data)
    print(df.head(5).to_string(index=False))
    del trns_log_prg_details, trans_log_prg_details_data, df
    gc.collect()


def demo_programme_content_details(conn: Connection) -> None:
    """Demo: Programme Content Details metadata endpoint."""
    _section("PROGRAMME CONTENT DETAILS")
    from pybarb.metadata.programme_content_details import ProgrammeContentDetails
    from pybarb.utils.dataframe_utils import to_dataframe

    prog_cont_details = ProgrammeContentDetails(conn)
    prg_cont_details_data = prog_cont_details.list_programme_content_details('music')
    df = to_dataframe(prg_cont_details_data)
    print(df.head(5).to_string(index=False))
    del prog_cont_details, prg_cont_details_data, df
    gc.collect()


def demo_spot_schedule_arrow(conn: Connection) -> None:
    """Demo: Spot Schedule Arrow stream metadata endpoint."""
    _section("SPOT SCHEDULE ARROW")
    from pybarb.metadata.spot_schedule_arrow import SpotScheduleArrow

    spotschedule_arrow = SpotScheduleArrow(conn)
    try:
        df = spotschedule_arrow.get_spot_schedule_dataframe(
            max_scheduled_date='2025-01-01',
            min_scheduled_date='2025-01-01',
            station_code='30'
        )
        print(df.head(1).to_string(index=False))
        del df
    except Exception as e:
        logger.error("SpotScheduleArrow failed: %s", e)
    del spotschedule_arrow
    gc.collect()


def demo_advertisers(conn: Connection) -> None:
    """Demo: Advertisers metadata endpoint."""
    _section("ADVERTISERS")
    from pybarb.metadata.advertisers import Advertisers

    advertisers = Advertisers(conn)

    # Note: Both min_scheduled_date and max_scheduled_date are optional parameters.
    # If not provided, it will fetch all available advertisers.

    # ── Raw JSON ──────────────────────────────────────────────────────────────
    data = advertisers.get_advertisers(
        min_scheduled_date='2026-08-01',
        max_scheduled_date='2026-08-31'
    )
    print("--- Advertisers JSON ---")
    print_json(data=data)

    # ── DataFrame ─────────────────────────────────────────────────────────────
    df = advertisers.get_advertisers_dataframe(
        min_scheduled_date='2026-08-01',
        max_scheduled_date='2026-08-31'
    )
    print(df.head(5).to_string(index=False))
    
    del data, df, advertisers
    gc.collect()


def demo_advertisers_without_params(conn: Connection) -> None:
    """Demo: Advertisers metadata endpoint without date parameters."""
    _section("ADVERTISERS (NO PARAMS)")
    from pybarb.metadata.advertisers import Advertisers

    advertisers = Advertisers(conn)

    # ── Raw JSON ──────────────────────────────────────────────────────────────
    data = advertisers.get_advertisers()
    print("--- Advertisers JSON (No Params) ---")
    print_json(data=data)

    # ── DataFrame ─────────────────────────────────────────────────────────────
    df = advertisers.get_advertisers_dataframe()
    print(df.head(5).to_string(index=False))
    
    del data, df, advertisers
    gc.collect()


# ══════════════════════════════════════════════════════════════════════════════
#  Metrics demos
# ══════════════════════════════════════════════════════════════════════════════

def demo_spot_impact(conn: Connection) -> None:
    """Demo: Spot Impact metrics endpoint."""
    _section("SPOT IMPACT")
    from pybarb.metrics.spot_impact import SpotImpact

    client = SpotImpact(conn)

    # ── Raw JSON ──────────────────────────────────────────────────────────────
    data = client.get_spot_impact(
        min_transmission_date='2026-05-10',
        max_transmission_date='2026-05-10',
        # station_code=30,
        deduplicate_spots=False,   # True by default; set False to retrieve raw API duplicates
        limit=9999,
        advertiser_name='POSTCODE LOTTERY'
        # is_staggercast_station_code='false',
    )
    print_json(data=data)

    # ── DataFrame ─────────────────────────────────────────────────────────────
    df = client.get_spot_impact_flat_dataframe(
        min_transmission_date='2026-05-10',
        max_transmission_date='2026-05-10',
        # station_code=30,
        deduplicate_spots=False,
        limit=9999,
        advertiser_name='POSTCODE LOTTERY'
        # is_staggercast_station_code='false',
    )
    print(f"Shape: {df.shape}")
    if not df.empty:
        print(df.head(5).to_string(index=False))   # FIX: head(5) only — was full df
        excel_path = "spot_impact_demo3.xlsx"
        df.to_excel(excel_path, index=False)
        print(f"Saved DataFrame to {excel_path}")
    else:
        print("No data available to save.")

    # ── Explicit cleanup ──────────────────────────────────────────────────────
    del data, df, client
    gc.collect()


def demo_station_audiences_metric(conn: Connection) -> None:
    """Demo: Station Audiences metrics endpoint."""
    _section("STATION AUDIENCES (METRIC)")
    from pybarb.metrics.station.station_audiences import StationAudiences

    client = StationAudiences(conn)

    # ── Raw JSON ──────────────────────────────────────────────────────────────
    data = client.get_station_audiences(
        last_updated_greater_than='2023-07-20',
        use_polling_days=True,
        limit=500,
        station_code=4934,
        panel_code=50,
        max_transmission_date='2026-07-24',
        min_transmission_date='2023-07-20',
        time_period_length=15,
        viewing_status='VOSDAL',
    )
    print_json(data=data)

    # ── DataFrame ─────────────────────────────────────────────────────────────
    df = client.get_station_audiences_flat_dataframe(
        last_updated_greater_than='2023-07-20',
        use_polling_days=True,
        limit=500,
        station_code=4934,
        panel_code=50,
        max_transmission_date='2026-07-24',
        min_transmission_date='2023-07-20',
        time_period_length=15,
        viewing_status='VOSDAL',
    )
    print(f"Shape: {df.shape}")
    print(df.head(5).to_string(index=False))

    # ── Explicit cleanup ──────────────────────────────────────────────────────
    del data, df, client
    gc.collect()


def demo_programme_ratings_metric(conn: Connection) -> None:
    """Demo: Programme Ratings metrics endpoint."""
    _section("PROGRAMME RATINGS (METRIC)")
    from pybarb.metrics.programme import ProgrammeRatings

    client = ProgrammeRatings(conn)

    # ── Raw JSON ──────────────────────────────────────────────────────────────
    # FIX: fetch raw JSON once; do NOT re-call the API for the DataFrame version
    data = client.get_programme_ratings(
        min_transmission_date='2026-05-11',
        max_transmission_date='2026-05-11',   # single day
        panel_code=50,
        consolidated=False,
        limit=1000,
    )
    print("--- Programme Ratings JSON ---")
    print_json(data=data)

    # ── DataFrame ─────────────────────────────────────────────────────────────
    df = client.get_programme_ratings_flat_dataframe(
        min_transmission_date='2026-05-11',
        max_transmission_date='2026-05-11',
        panel_code=50,
        consolidated=False,
        limit=1000,
    )
    print(f"Shape: {df.shape}")
    if not df.empty:
        print(df.head(5).to_string(index=False))   # FIX: head(5) only — was full df
        print("Columns:", list(df.columns))
    else:
        print("No data available.")

    # ── Explicit cleanup ──────────────────────────────────────────────────────
    del data, df, client
    gc.collect()


def demo_programme_reach(conn: Connection) -> None:
    """Demo: Programme Reach metrics endpoint."""
    _section("PROGRAMME REACH")
    from pybarb.metrics.programme.programme_reach import ProgrammeReach

    client = ProgrammeReach(conn)

    # ── Raw JSON ──────────────────────────────────────────────────────────────
    data = client.get_programme_reach(
        programme_ids=['BBC News_swmdnz'],
        audience_names=['All Women'],
        start_date='2026-01-01',
        end_date='2026-01-20',
    )
    print("--- Programme Reach JSON ---")
    print_json(data=data)

    # ── DataFrame ─────────────────────────────────────────────────────────────
    df = client.get_programme_reach_flat_dataframe(
        programme_ids=['BBC News_swmdnz'],
        audience_names=['All Women'],
        start_date='2026-01-01',
        end_date='2026-01-20',
    )
    print(f"Shape: {df.shape}")
    if not df.empty:
        print(df.head(5).to_string(index=False))   # FIX: head(5) only — was full df
    else:
        print("No data available.")

    # ── Explicit cleanup ──────────────────────────────────────────────────────
    del data, df, client
    gc.collect()


def demo_spot_reach(conn: Connection) -> None:
    """Demo: Spot Reach metrics endpoint."""
    _section("SPOT REACH")
    from pybarb.metrics.spot.spot_reach import SpotReach

    client = SpotReach(conn)

    # ── Raw JSON ──────────────────────────────────────────────────────────────
    data = client.get_spot_reach(
        clock_numbers=['CCELIVE001030'],
        audience_names=['All Individuals'],
        start_date='2026-01-01',
        end_date='2026-01-01',
    )
    print("--- Spot Reach JSON ---")
    print_json(data=data)

    # ── DataFrame ─────────────────────────────────────────────────────────────
    df = client.get_spot_reach_flat_dataframe(
        clock_numbers=['CCELIVE001030'],
        audience_names=['All Individuals'],
        start_date='2026-01-01',
        end_date='2026-01-01',
    )
    print(f"Shape: {df.shape}")
    if not df.empty:
        print(df.head(5).to_string(index=False))   # FIX: head(5) only — was full df
    else:
        print("No data available.")

    # ── Explicit cleanup ──────────────────────────────────────────────────────
    del data, df, client
    gc.collect()


# ══════════════════════════════════════════════════════════════════════════════
#  Bulk endpoint demos
# ══════════════════════════════════════════════════════════════════════════════

def demo_bulk_programme_schedule(conn: Connection) -> None:
    """Demo: Bulk Programme Schedule endpoint."""
    _section("BULK PROGRAMME SCHEDULE")
    from pybarb.bulk.programme_schedule import ProgrammeSchedule as BulkProgrammeSchedule

    client = BulkProgrammeSchedule(conn)

    # ── DataFrame (URL listing) ────────────────────────────────────────────────
    df = client.get_programme_schedule_dataframe(
        min_scheduled_date='2026-05-01',
        max_scheduled_date='2026-05-31',
        last_updated_greater_than='2026-05-20',
    )
    print(f"Shape: {df.shape}")
    if not df.empty:
        print(df.head(5).to_string(index=False))
    else:
        print("No data available.")

    # ── File download + load ───────────────────────────────────────────────────
    downloaded = client.download_programme_schedule_files(
        min_scheduled_date='2026-05-01',
        max_scheduled_date='2026-05-02',
    )
    print(f"Downloaded {len(downloaded)} file(s)")
    for f in downloaded:
        print(f"  - {f}")

    if downloaded:
        combined_df = client.load_parquet_files_to_dataframe(downloaded)
        print(f"Combined shape: {combined_df.shape}")
        if not combined_df.empty:
            print(combined_df.head(5).to_string(index=False))   # FIX: head(5) only

        flat_df = client.flatten_programme_schedule_dataframe(combined_df)
        print(f"Flattened shape: {flat_df.shape}")
        if not flat_df.empty:
            print(flat_df.head(5).to_string(index=False))   # FIX: head(5) only

        # ── Explicit cleanup ──────────────────────────────────────────────────
        del flat_df, combined_df

    del df, downloaded, client
    gc.collect()


def demo_bulk_station_audiences(conn: Connection) -> None:
    """Demo: Bulk Station Audiences endpoint."""
    _section("BULK STATION AUDIENCES")
    from pybarb.bulk.station_audiences import StationAudiences as BulkStationAudiences

    client = BulkStationAudiences(conn)

    # ── DataFrame (URL listing) ────────────────────────────────────────────────
    df = client.get_station_audiences_dataframe(
        min_transmission_date='2026-05-01',
        max_transmission_date='2026-05-31',
        time_period_length=5,
        viewing_status='live',
    )
    print(f"Shape: {df.shape}")
    if not df.empty:
        print(df.head(5).to_string(index=False))
    else:
        print("No data available.")

    # ── File download + load ───────────────────────────────────────────────────
    sa_downloaded = client.download_station_audiences_files(
        min_transmission_date='2026-05-29',
        max_transmission_date='2026-05-29',
        time_period_length=5,
        viewing_status='live',
    )
    print(f"Downloaded {len(sa_downloaded)} file(s)")
    for f in sa_downloaded:
        print(f"  - {f}")

    if sa_downloaded:
        sa_combined_df = client.load_parquet_files_to_dataframe(sa_downloaded)
        print(f"Combined shape: {sa_combined_df.shape}")
        if not sa_combined_df.empty:
            print(sa_combined_df.head(5).to_string(index=False))   # FIX: head(5) only

        sa_flat_df = client.flatten_station_audiences_dataframe(sa_combined_df)
        print(f"Flattened shape: {sa_flat_df.shape}")
        if not sa_flat_df.empty:
            print(sa_flat_df.head(5).to_string(index=False))   # FIX: head(5) only

        # ── Explicit cleanup ──────────────────────────────────────────────────
        del sa_flat_df, sa_combined_df

    del df, sa_downloaded, client
    gc.collect()


def demo_bulk_spot_impacts(conn: Connection) -> None:
    """Demo: Bulk Spot Impacts endpoint."""
    _section("BULK SPOT IMPACTS")
    from pybarb.bulk.spot_impacts import SpotImpacts

    client = SpotImpacts(conn)

    # ── DataFrame ─────────────────────────────────────────────────────────────
    try:
        df = client.get_spot_impacts_dataframe(
            min_transmission_date='2026-06-27',
            max_transmission_date='2026-06-27',   # single day
        )
        print(f"Shape: {df.shape}")
        if not df.empty:
            print(df.head(5).to_string(index=False))   # FIX: head(5) only
        else:
            print("No data available.")
        del df
    except Exception as e:
        print(f"Failed to fetch Spot Impacts DataFrame: {e}")

    # ── File download + load ───────────────────────────────────────────────────
    si_downloaded = []
    try:
        si_downloaded = client.download_spot_impacts_files(
            min_transmission_date='2026-06-27',
            max_transmission_date='2026-06-27',   # single day
        )
        print(f"Downloaded {len(si_downloaded)} file(s)")
        for f in si_downloaded[:5]:
            print(f"  - {f}")
        if len(si_downloaded) > 5:
            print(f"  ... and {len(si_downloaded) - 5} more.")
    except Exception as e:
        print(f"Failed to download Spot Impacts files: {e}")

    if si_downloaded:
        si_combined_df = client.load_parquet_files_to_dataframe(si_downloaded)
        print(f"Combined shape: {si_combined_df.shape}")
        if not si_combined_df.empty:
            print(si_combined_df.head(5).to_string(index=False))   # FIX: head(5) only

        si_flat_df = client.flatten_spot_impacts_dataframe(si_combined_df)
        print(f"Flattened shape: {si_flat_df.shape}")
        if not si_flat_df.empty:
            print(si_flat_df.head(5).to_string(index=False))   # FIX: head(5) only

        # ── Explicit cleanup ──────────────────────────────────────────────────
        del si_flat_df, si_combined_df

    del si_downloaded, client
    gc.collect()


def demo_bulk_programme_audience(conn: Connection) -> None:
    """Demo: Bulk Programme Audience endpoint."""
    _section("BULK PROGRAMME AUDIENCE")
    from pybarb.bulk.programme_audience import ProgrammeAudience

    client = ProgrammeAudience(conn)

    # ── DataFrame ─────────────────────────────────────────────────────────────
    try:
        df = client.get_programme_audience_dataframe(
            min_session_date='2026-05-01',
            max_session_date='2026-05-30',
            panel_code='927',
        )
        print(f"Shape: {df.shape}")
        if not df.empty:
            print(df.head(5).to_string(index=False))
        else:
            print("No data available.")
        del df
    except Exception as e:
        print(f"Failed to fetch Programme Audience DataFrame: {e}")

    # ── File download + load ───────────────────────────────────────────────────
    pa_downloaded = []
    try:
        pa_downloaded = client.download_programme_audience_files(
            min_session_date='2026-05-01',
            max_session_date='2026-05-30',
            panel_code='927',
        )
        print(f"Downloaded {len(pa_downloaded)} file(s)")
        for f in pa_downloaded[:5]:
            print(f"  - {f}")
        if len(pa_downloaded) > 5:
            print(f"  ... and {len(pa_downloaded) - 5} more.")
    except Exception as e:
        print(f"Failed to download Programme Audience files: {e}")

    if pa_downloaded:
        pa_combined_df = client.load_parquet_files_to_dataframe(pa_downloaded)
        print(f"Combined shape: {pa_combined_df.shape}")
        if not pa_combined_df.empty:
            print(pa_combined_df.head(5).to_string(index=False))   # FIX: head(5) only

        pa_flat_df = client.flatten_programme_audience_dataframe(pa_combined_df)
        print(f"Flattened shape: {pa_flat_df.shape}")
        if not pa_flat_df.empty:
            print(pa_flat_df.head(5).to_string(index=False))   # FIX: head(5) only

        # ── Explicit cleanup ──────────────────────────────────────────────────
        del pa_flat_df, pa_combined_df

    del pa_downloaded, client
    gc.collect()


def demo_bulk_viewing(conn: Connection) -> None:
    """Demo: Bulk Viewing endpoint."""
    _section("BULK VIEWING")
    from pybarb.bulk.viewing import Viewing

    client = Viewing(conn)

    # ── Raw JSON ──────────────────────────────────────────────────────────────
    data = client.get_viewing(
        min_session_date='2026-05-01',
        max_session_date='2026-05-01',
        panel_code='50',
    )
    print(f"Records: {len(data)}")

    # ── DataFrame (URL listing) ────────────────────────────────────────────────
    df = client.get_viewing_dataframe(
        min_session_date='2026-05-01',
        max_session_date='2026-05-01',
        panel_code='50',
    )
    print(f"Shape: {df.shape}")
    if not df.empty:
        print(df.head(5).to_string(index=False))
    else:
        print("No data available.")
    del data, df

    # ── File download + load ───────────────────────────────────────────────────
    downloaded_viewing = client.download_viewing_files(
        min_session_date='2026-05-01',
        max_session_date='2026-05-02',
        panel_code='50',
    )
    print(f"Downloaded {len(downloaded_viewing)} file(s)")
    for f in downloaded_viewing:
        print(f"  - {f}")

    if downloaded_viewing:
        combined_viewing_df = client.load_parquet_files_to_dataframe(downloaded_viewing)
        print(f"Combined shape: {combined_viewing_df.shape}")
        if not combined_viewing_df.empty:
            print(combined_viewing_df.head(5).to_string(index=False))   # FIX: head(5) only

        # FIX: sample before flattening to avoid cartesian-product memory explosion
        # (PANEL_VIEWERS × PROGRAMMES_VIEWED × SPOTS_VIEWED can multiply rows 10–30×)
        sample_for_flatten = combined_viewing_df.head(200)
        flat_viewing_df = client.flatten_viewing_dataframe(sample_for_flatten)
        print(f"Flattened shape (sample of 200 rows): {flat_viewing_df.shape}")
        print(f"Flattened columns ({len(flat_viewing_df.columns)}):")
        for col in flat_viewing_df.columns:
            print(f"  - {col}")
        print("Sample flattened data:")
        print(flat_viewing_df.head(5).to_string(index=False))   # FIX: head(5) only

        # ── Explicit cleanup ──────────────────────────────────────────────────
        del flat_viewing_df, sample_for_flatten, combined_viewing_df

    del downloaded_viewing, client
    gc.collect()


def demo_bulk_spot_schedule(conn: Connection) -> None:
    """Demo: Bulk Spot Schedule endpoint."""
    _section("BULK SPOT SCHEDULE")
    from pybarb.bulk.spot_schedule import SpotSchedule as BulkSpotSchedule

    client = BulkSpotSchedule(conn)

    # ── DataFrame ─────────────────────────────────────────────────────────────
    try:
        df = client.get_spot_schedule_dataframe(
            min_scheduled_date='2026-04-29',
            max_scheduled_date='2026-05-29',
        )
        print(f"Shape: {df.shape}")
        if not df.empty:
            print(df.head(5).to_string(index=False))   # FIX: head(5) only
        else:
            print("No data available.")
        del df
    except Exception as e:
        print(f"Failed to fetch Spot Schedule DataFrame: {e}")

    # ── File download + load ───────────────────────────────────────────────────
    ss_downloaded = []
    try:
        ss_downloaded = client.download_spot_schedule_files(
            min_scheduled_date='2026-04-29',
            max_scheduled_date='2026-04-29',   # already single day
        )
        print(f"Downloaded {len(ss_downloaded)} file(s)")
        for f in ss_downloaded[:5]:
            print(f"  - {f}")
        if len(ss_downloaded) > 5:
            print(f"  ... and {len(ss_downloaded) - 5} more.")
    except Exception as e:
        print(f"Failed to download Spot Schedule files: {e}")

    if ss_downloaded:
        ss_combined_df = client.load_parquet_files_to_dataframe(ss_downloaded)
        print(f"Combined shape: {ss_combined_df.shape}")
        if not ss_combined_df.empty:
            print(ss_combined_df.head(5).to_string(index=False))   # FIX: head(5) only

        ss_flat_df = client.flatten_spot_schedule_dataframe(ss_combined_df)
        print(f"Flattened shape: {ss_flat_df.shape}")
        if not ss_flat_df.empty:
            print(ss_flat_df.head(5).to_string(index=False))   # FIX: head(5) only

        # ── Explicit cleanup ──────────────────────────────────────────────────
        del ss_flat_df, ss_combined_df

    del ss_downloaded, client
    gc.collect()


def demo_bulk_programme_ratings(conn: Connection) -> None:
    """Demo: Bulk Programme Ratings endpoint."""
    _section("BULK PROGRAMME RATINGS")
    from pybarb.bulk.programme_ratings import ProgrammeRatings as BulkProgrammeRatings

    client = BulkProgrammeRatings(conn)

    # ── DataFrame ─────────────────────────────────────────────────────────────
    try:
        df = client.get_programme_ratings_dataframe(
            min_transmission_date='2026-05-11',
            max_transmission_date='2026-05-14',
        )
        print(f"Shape: {df.shape}")
        if not df.empty:
            print(df.head(5).to_string(index=False))   # FIX: head(5) only
        else:
            print("No data available.")
        del df
    except Exception as e:
        print(f"Failed to fetch Programme Ratings DataFrame: {e}")

    # ── File download + load ───────────────────────────────────────────────────
    pr_downloaded = []
    try:
        pr_downloaded = client.download_programme_ratings_files(
            min_transmission_date='2026-05-11',
            max_transmission_date='2026-05-11',   # already single day
        )
        print(f"Downloaded {len(pr_downloaded)} file(s)")
        for f in pr_downloaded[:5]:
            print(f"  - {f}")
        if len(pr_downloaded) > 5:
            print(f"  ... and {len(pr_downloaded) - 5} more.")
    except Exception as e:
        print(f"Failed to download Programme Ratings files: {e}")

    if pr_downloaded:
        pr_combined_df = client.load_parquet_files_to_dataframe(pr_downloaded)
        print(f"Combined shape: {pr_combined_df.shape}")
        if not pr_combined_df.empty:
            print(pr_combined_df.head(5).to_string(index=False))   # FIX: head(5) only

        pr_flat_df = client.flatten_programme_ratings_dataframe(pr_combined_df)
        print(f"Flattened shape: {pr_flat_df.shape}")
        if not pr_flat_df.empty:
            print(pr_flat_df.head(5).to_string(index=False))   # FIX: head(5) only

        # ── Explicit cleanup ──────────────────────────────────────────────────
        del pr_flat_df, pr_combined_df

    del pr_downloaded, client
    gc.collect()


def demo_bulk_spot_audience(conn: Connection) -> None:
    """Demo: Bulk Spot Audience endpoint."""
    _section("BULK SPOT AUDIENCE")
    from pybarb.bulk.spot_audience import SpotAudience as BulkSpotAudience

    client = BulkSpotAudience(conn)

    # ── DataFrame ─────────────────────────────────────────────────────────────
    try:
        df = client.get_spot_audience_dataframe(
            min_session_date='2026-02-01',
            max_session_date='2026-02-01',   # single day
            panel_code='48',
        )
        print(f"Shape: {df.shape}")
        if not df.empty:
            print(df.head(5).to_string(index=False))   # FIX: head(5) only
        else:
            print("No data available.")
        del df
    except Exception as e:
        print(f"Failed to fetch Spot Audience DataFrame: {e}")

    # ── File download + load ───────────────────────────────────────────────────
    bulk_sa_downloaded = []
    try:
        bulk_sa_downloaded = client.download_spot_audience_files(
            min_session_date='2026-02-01',
            max_session_date='2026-02-01',   # single day
            panel_code='48',
        )
        print(f"Downloaded {len(bulk_sa_downloaded)} file(s)")
        for f in bulk_sa_downloaded[:5]:
            print(f"  - {f}")
        if len(bulk_sa_downloaded) > 5:
            print(f"  ... and {len(bulk_sa_downloaded) - 5} more.")
    except Exception as e:
        print(f"Failed to download Spot Audience files: {e}")

    if bulk_sa_downloaded:
        bulk_sa_combined_df = client.load_parquet_files_to_dataframe(bulk_sa_downloaded)
        print(f"Combined shape: {bulk_sa_combined_df.shape}")
        if not bulk_sa_combined_df.empty:
            print(bulk_sa_combined_df.head(5).to_string(index=False))   # FIX: head(5) only

        bulk_sa_flat_df = client.flatten_spot_audience_dataframe(bulk_sa_combined_df)
        print(f"Flattened shape: {bulk_sa_flat_df.shape}")
        if not bulk_sa_flat_df.empty:
            print(bulk_sa_flat_df.head(5).to_string(index=False))   # FIX: head(5) only

        # ── Explicit cleanup ──────────────────────────────────────────────────
        del bulk_sa_flat_df, bulk_sa_combined_df

    del bulk_sa_downloaded, client
    gc.collect()


# ══════════════════════════════════════════════════════════════════════════════
#  Entry point
# ══════════════════════════════════════════════════════════════════════════════

def main() -> None:
    """
    Main demo runner.

    Memory strategy
    ───────────────
    • Each endpoint is wrapped in its own function so all local variables go
      out of scope automatically when the function returns.
    • Every function also calls `del` on its largest objects and then
      `gc.collect()` to prompt the garbage collector immediately.
    • Only `head(5)` is ever printed — never the full DataFrame.
    • Original date ranges from the initial implementation are preserved.
      Comment out sections you do not need to reduce total memory at runtime.
    """
    try:
        # ------------------------------------------------------------------
        # CONNECTION
        # ------------------------------------------------------------------
        # Token connection - reads BARB_REFRESH_TOKEN from .env
        conn = Connection()
        conn.connect()
        # demo_connect()                    # demonstrate automatic refresh

        # ------------------------------------------------------------------
        # METADATA ENDPOINTS
        # ------------------------------------------------------------------
        # demo_panels(conn)
        # demo_stations(conn)
        # demo_viewing_stations(conn)
        # demo_households(conn)
        # demo_panel_members(conn)
        # demo_target_audience_categories(conn)
        # demo_spot_schedule_metadata(conn)
        # demo_programme_schedule_metadata(conn)
        # demo_split_station_factor(conn)
        # demo_transmission_log_programme_details(conn)
        # demo_programme_content_details(conn)
        # demo_advertisers(conn)
        # demo_advertisers_without_params(conn)

        # ------------------------------------------------------------------
        # METRICS ENDPOINTS
        # ------------------------------------------------------------------
        # demo_spot_impact(conn)
        # demo_station_audiences_metric(conn)
        # demo_programme_ratings_metric(conn)
        # demo_programme_reach(conn)
        # demo_spot_reach(conn)

        # ------------------------------------------------------------------
        # BULK ENDPOINTS
        # ------------------------------------------------------------------
        # demo_bulk_programme_schedule(conn)
        # demo_bulk_station_audiences(conn)
        # demo_bulk_spot_impacts(conn)
        # demo_bulk_programme_audience(conn)
        # demo_bulk_viewing(conn)
        # demo_bulk_spot_schedule(conn)
        # demo_bulk_programme_ratings(conn)
        # demo_bulk_spot_audience(conn)

    except FileNotFoundError as e:
        logger.error("Error: %s", e)
    except json.JSONDecodeError:
        logger.error("Error: Failed to decode creds.json. Ensure it's valid JSON.")
    except Exception:
        logger.exception("Unexpected error occurred")


if __name__ == '__main__':
    main()