import sys
from sqlalchemy import asc, nulls_last
from ..database import SessionLocal
from ..models.stream import Stream


def renumber_all_streams():
    """Reassign stream numbers based on chronological order (oldest = #1).

    Sorts by stream_date (or created_at as fallback), then assigns
    consecutive numbers starting from 1.
    """
    try:
        db = SessionLocal()
        try:
            # Fetch all streams ordered by stream_date, then created_at
            streams = db.query(Stream).order_by(
                nulls_last(asc(Stream.stream_date)),
                asc(Stream.created_at)
            ).all()

            for idx, stream in enumerate(streams, start=1):
                stream.stream_number = idx

            db.commit()

            sys.stderr.write(f"[NUMBERING] Reassigned stream numbers: {len(streams)} streams (#1 = oldest)\n")
            sys.stderr.flush()
        finally:
            db.close()
    except Exception as e:
        sys.stderr.write(f"[NUMBERING ERROR] Failed to renumber streams: {e}\n")
        sys.stderr.flush()