#!/usr/bin/env python3
"""
🌱 Canteen Seed Data Import — CSV Parser & Header Mapper

Reads the raw Cantaloupe Excel export (Machine_List.xlsx, 1,848 machines × 62 columns)
and parses it into structured machine records mapping to DB columns.

Usage:
    from parser import parse_excel, parse_excel_to_json
    machines = parse_excel('seed-data/Machine_List.xlsx')
    for m in machines[:3]:
        print(json.dumps(m, indent=2, default=str))
"""

import re
import json
from datetime import datetime

import openpyxl


# ─── Disney Property Mapping (Section 1) ──────────────────────────────────────

DISNEY_PROPERTY_MAP = {
    "D-ART OF ANIMATION": "Art of Animation Resort",
    "D-All Star Music": "All-Star Music Resort",
    "D-All Star Sports": "All-Star Sports Resort",
    "D-BAY LAKE TOWER": "Bay Lake Tower at Contemporary",
    "D-BLIZZARD BEACH": "Blizzard Beach Water Park",
    "D-Boardwalk": "Disney's BoardWalk Inn",
    "D-Celebration": "Celebration (Admin)",
    "D-ContemporaryHotel": "Disney's Contemporary Resort",
    "D-DISNEY VENDING": "Disney Support Services",
    "D-DISNEY WORLD SS": "Disney World Support Services",
    "D-DRC": "Disney Regional Center",
    "D-Disney Springs": "Disney Springs",
    "D-FORT WILDERNESS": "Fort Wilderness Resort & Campground",
    "D-GRAND FLORIDIAN": "Disney's Grand Floridian Resort",
    "D-POLYNESIAN RESORT": "Disney's Polynesian Village Resort",
    "D-POP CENTURY": "Disney's Pop Century Resort",
    "D-PORT ORLEANS FrQt": "Port Orleans French Quarter",
    "D-Port Orleans Rvsd": "Port Orleans Riverside",
    "D-RIVIERA RESORT": "Disney's Riviera Resort",
    "D-Saratoga Springs": "Saratoga Springs Resort & Spa",
    "D-Treehouse Villas": "Treehouse Villas (Saratoga Springs)",
    "D-WIDE WORLD SPORTS": "ESPN Wide World of Sports",
    "D-WILDERNESS LODGE": "Disney's Wilderness Lodge",
    "D-Winter Summerland": "Winter Summerland Mini Golf",
    "D-YACHT AND BEACH": "Disney's Yacht & Beach Club Resorts",
}

# Additional Disney customer prefix patterns (from data)
DISNEY_CUSTOMER_PREFIXES = [
    "D-All Star Movie",
    "D-Animal Kngdm Lodge",
    "D-Animal Kingdom",
    "D-ART OF ANIMATION",
    "D-All Star Music",
    "D-All Star Sports",
    "D-BAY LAKE TOWER",
    "D-BLIZZARD BEACH",
    "D-Boardwalk",
    "D-Celebration",
    "D-ContemporaryHotel",
    "D-Coronado Springs",
    "D-DISNEY VENDING",
    "D-DISNEY WORLD SS",
    "D-DRC",
    "D-Disney Springs",
    "D-Epcot",
    "D-FORT WILDERNESS",
    "D-GRAND FLORIDIAN",
    "D-Gran Destino",
    "D-Hollywood Studios",
    "D-Magic Kingdom",
    "D-POLYNESIAN RESORT",
    "D-POP CENTURY",
    "D-PORT ORLEANS FrQt",
    "D-Port Orleans Rvsd",
    "D-RIVIERA RESORT",
    "D-Saratoga Springs",
    "D-Treehouse Villas",
    "D-WIDE WORLD SPORTS",
    "D-WILDERNESS LODGE",
    "D-Winter Summerland",
    "D-YACHT AND BEACH",
]


# ─── Disney Resort Area Mapping (Section 2) ────────────────────────────────────
#
# Maps cleaned disney_park values to their Walt Disney World resort area.
# Based on official WDW geography: resorts grouped by proximity to parks
# and transportation access (monorail, Skyliner, boat, bus).
#
# Areas:
#   Magic Kingdom Resort Area   — Monorail loop & Seven Seas Lagoon
#   Epcot Resort Area           — Crescent Lake, Skyliner to Epcot/HS
#   Animal Kingdom Resort Area  — Near Animal Kingdom park
#   Disney Springs Resort Area  — Near shopping/dining district
#   Wide World of Sports Area   — All-Star/Pop Century corridor (SE)
#   Parks & Attractions         — Theme parks, ESPN, cast areas
#   Water Parks                 — Blizzard Beach, Typhoon Lagoon, golf
#   Admin & Support             — Corporate/admin locations

DISNEY_AREA_MAP = {
    # ──── Magic Kingdom Resort Area ─────────────────────────────────────────────
    # Hotels along the monorail loop and Seven Seas Lagoon / Bay Lake
    "Disney's Contemporary Resort": "Magic Kingdom Resort Area",
    "Disney's Polynesian Village Resort": "Magic Kingdom Resort Area",
    "Disney's Grand Floridian Resort": "Magic Kingdom Resort Area",
    "Bay Lake Tower at Contemporary": "Magic Kingdom Resort Area",
    "Disney's Wilderness Lodge": "Magic Kingdom Resort Area",
    "Fort Wilderness Resort & Campground": "Magic Kingdom Resort Area",
    "D-Island Tower Polynesian": "Magic Kingdom Resort Area",

    # ──── Epcot Resort Area ─────────────────────────────────────────────────────
    # Hotels along Crescent Lake — walk/Skyliner/boat to Epcot & Hollywood Studios
    "Disney's BoardWalk Inn": "Epcot Resort Area",
    "Disney's Yacht & Beach Club Resorts": "Epcot Resort Area",
    "Disney's Riviera Resort": "Epcot Resort Area",

    # ──── Animal Kingdom Resort Area ────────────────────────────────────────────
    # Near Animal Kingdom park (Jambo House, Kidani Village)
    "D-Animal Kngdm Lodge": "Animal Kingdom Resort Area",
    "D-Kidani Village GUEST": "Animal Kingdom Resort Area",
    "D-Coronado Springs": "Animal Kingdom Resort Area",
    "D-Gran Destino": "Animal Kingdom Resort Area",   # At Coronado Springs

    # ──── Disney Springs Resort Area ────────────────────────────────────────────
    # Near the shopping/dining district
    "Saratoga Springs Resort & Spa": "Disney Springs Resort Area",
    "Treehouse Villas (Saratoga Springs)": "Disney Springs Resort Area",
    "Disney Springs": "Disney Springs Resort Area",
    "Port Orleans Riverside": "Disney Springs Resort Area",
    "Port Orleans French Quarter": "Disney Springs Resort Area",
    "D-Caribbean Beach Guest": "Disney Springs Resort Area",
    "D-CARIBBEAN BEACH CAST": "Disney Springs Resort Area",
    "D-OLD KEY WEST GUEST": "Disney Springs Resort Area",
    "D-OLD KEY WEST CAST": "Disney Springs Resort Area",

    # ──── Wide World of Sports Resort Area ──────────────────────────────────────
    # All-Star Resorts & Pop Century / Art of Animation corridor (southeastern)
    "Art of Animation Resort": "Wide World of Sports Resort Area",
    "Disney's Pop Century Resort": "Wide World of Sports Resort Area",
    "All-Star Sports Resort": "Wide World of Sports Resort Area",
    "All-Star Music Resort": "Wide World of Sports Resort Area",
    "D-All Star Movie": "Wide World of Sports Resort Area",

    # ──── Parks & Attractions ───────────────────────────────────────────────────
    # Theme parks, cast-member areas, sports complex
    "D-Magic Kingdom": "Parks & Attractions",
    "D-Hollywood Studios": "Parks & Attractions",
    "D-Animal Kingdom": "Parks & Attractions",
    "D-Epcot": "Parks & Attractions",
    "ESPN Wide World of Sports": "Parks & Attractions",
    "D-ESPN 2 CAST": "Parks & Attractions",

    # ──── Water Parks ───────────────────────────────────────────────────────────
    "Blizzard Beach Water Park": "Water Parks",
    "D-TYPHOON LAGOON CAST": "Water Parks",
    "Winter Summerland Mini Golf": "Water Parks",
    "D-FANTASIA GOLF Guest": "Water Parks",

    # ──── Admin & Support ───────────────────────────────────────────────────────
    "Celebration (Admin)": "Admin & Support",
    "Disney Regional Center": "Admin & Support",
    "Disney World Support Services": "Admin & Support",
    "Disney Support Services": "Admin & Support",
}


# ─── Make Normalization Table (Section 5) ─────────────────────────────────────

MAKE_NORMALIZATION = {
    "Crane Co": "Crane",
    "Crane Nat": "Crane",
    "NATIONAL": "Crane",
    "Crane National": "Crane",
    "Dixie Narco": "DN",
    "Royal Vendors": "Royal",
    "Vendo Co": "Vendo",
    "U Select It": "USI",
    "Automatic Merchandising": "AMS",
    "Automatic Products": "AP",
}


# ─── Known suffixes to strip from Place (Pattern A) ───────────────────────────

PLACE_SUFFIXES = [
    "Breakroom", "Br", "Break Room", "BREAKROOM",
    "BREAK AREA", "Break Area",
    "Vending Area", "Vending",
    "Break",
    "Breakroom ",  # trailing space
]

# Floor extraction patterns
FLOOR_PATTERNS = [
    (re.compile(r'(\d+)\s*(?:ST|ND|RD|TH)\s*(?:FL|FLOOR)', re.IGNORECASE), lambda m: m.group(1)),
    (re.compile(r'(?:FL|FLOOR)\s*(\d+)', re.IGNORECASE), lambda m: m.group(1)),
    (re.compile(r'(\d+)\s*[Tt]'), lambda m: str(int(m.group(1)))),  # 5T, 6T (Contemporary Tower)
    (re.compile(r'(\d+)\s*[Ff][Ll]'), lambda m: m.group(1)),  # 2 fl
    (re.compile(r'(\d+)[Nn]'), lambda m: str(int(m.group(1)))),  # 2N
]

# Building extraction patterns
BUILDING_PATTERNS = [
    re.compile(r'BLDG\s*#?\s*(\d+(?:\s*\w+)?)', re.IGNORECASE),
    re.compile(r'Building\s+(\d+(?:\s*\w+)?)', re.IGNORECASE),
    re.compile(r'Bldg\s+(\d+(?:\s*\w+)?)', re.IGNORECASE),
    re.compile(r'DAAR\s+(\d+(?:-\d+)?)', re.IGNORECASE),
    re.compile(r'TOWER\s+(\d+)', re.IGNORECASE),
    re.compile(r'Loop\s+(\d+)', re.IGNORECASE),
    re.compile(r'(\w+\s+(?:VILLAS?|BLDG|BUILDING))', re.IGNORECASE),
]

# Suite extraction patterns
SUITE_PATTERNS = [
    (re.compile(r'(?:Suite|SUITE|suite|Ste|STE|ste)\s*#?\s*(\d+)'), lambda m: m.group(1)),
    (re.compile(r'(\d+)\s*(?:Suite|SUITE|suite|Ste|STE|ste)'), lambda m: m.group(1)),
]

# Room extraction patterns
ROOM_PATTERNS = [
    (re.compile(r'(?:Room|ROOM|room)\s*#?\s*(\d+)'), lambda m: f"Room {m.group(1)}"),
    (re.compile(r'(?:RM|Rm)\s*#?\s*(\d+)'), lambda m: f"Room {m.group(1)}"),
    (re.compile(r'Hotel by (Room \d+)', re.IGNORECASE), lambda m: m.group(1)),
    # Room-type locations (not breakrooms)
    (re.compile(r'(?<!Break)(?:Mailroom|MAILROOM|Showroom|SHOWROOM|Game Room|Game Room|Green Room|Emergency Room|Waiting Room|WAITINGROOM|WaitRoom|ICU WAITING ROOM|CARDIO WAITINGROOM|Conf Room|Driver.s Room|Laundry Room)'), lambda m: m.group(0).strip()),
]

# Trailer extraction patterns
TRAILER_PATTERNS = [
    (re.compile(r'(?:Trailer|TRAILER|TRLR)\s*#?\s*(\d+)'), lambda m: f"Trailer {m.group(1)}"),
    (re.compile(r'(?:Trailer|TRAILER)\s+(\w+)'), lambda m: f"Trailer {m.group(1)}"),
    (re.compile(r'MASH Trailer', re.IGNORECASE), lambda m: "Trailer MASH"),
    (re.compile(r'Safari Trailer', re.IGNORECASE), lambda m: "Trailer Safari"),
    (re.compile(r'^Trailer$', re.IGNORECASE), lambda m: "Trailer"),
]


# ─── Helper: safe string ──────────────────────────────────────────────────────

def _s(val):
    """Convert value to string, handling None."""
    if val is None:
        return ""
    return str(val).strip()


def _num(val):
    """Convert to float or None."""
    if val is None:
        return None
    try:
        return float(val)
    except (ValueError, TypeError):
        return None


def _int_or_none(val):
    """Convert to int or None."""
    if val is None:
        return None
    try:
        return int(val)
    except (ValueError, TypeError):
        return None


def _bool_from_yesno(val):
    """Convert Yes/No to boolean."""
    if val is None:
        return None
    s = str(val).strip().lower()
    if s in ("yes", "true", "1", "y"):
        return True
    if s in ("no", "false", "0", "n"):
        return False
    return None


# ─── 1. Device / Telemetry Parser (Section 7) ─────────────────────────────────

def parse_device(device_val):
    """
    Parse Device (Telemetry ID) column.
    
    Returns dict with telemetry_provider and card_reader_brand.
    """
    raw = _s(device_val)
    result = {
        "telemetry_provider": None,
        "card_reader_brand": None,
    }
    
    if not raw:
        return result
    
    # Extract suffix in parentheses
    m = re.search(r'\(([^)]+)\)', raw)
    if m:
        suffix = m.group(1).strip()
        suffix_upper = suffix.upper()
        
        if suffix_upper == "EPORT":
            result["telemetry_provider"] = "ePort"
            result["card_reader_brand"] = "Cantaloupe"
        elif suffix_upper == "NAYAX":
            result["telemetry_provider"] = "NAYAX"
            result["card_reader_brand"] = "Nayax"
        elif suffix_upper == "CMS":
            result["telemetry_provider"] = "CMS"
            result["card_reader_brand"] = "CMS"
        else:
            # Unknown suffix
            result["telemetry_provider"] = suffix
            result["card_reader_brand"] = "Unknown"
    else:
        # No parentheses - check for known prefixes
        if raw.startswith("VJ") or raw.startswith("K3CT") or raw.startswith("VK"):
            result["telemetry_provider"] = "ePort"
            result["card_reader_brand"] = "Cantaloupe"
        elif raw.isdigit():
            result["telemetry_provider"] = "Unknown"
            result["card_reader_brand"] = "Unknown"
        else:
            result["telemetry_provider"] = "Unknown"
            result["card_reader_brand"] = "Unknown"
    
    return result


# ─── 2. Type / Class / Make / Model Parser (Section 5) ────────────────────────

def normalize_make(make_raw):
    """Normalize make to canonical form."""
    if not make_raw:
        return None
    make = make_raw.strip()
    return MAKE_NORMALIZATION.get(make, make)


def parse_type(type_val, class_fallback=None, make_fallback=None, model_fallback=None):
    """
    Parse Type column into class, make, model, is_glass_front.
    
    Format: CLASS (MAKE MODEL)
    Special: MAKE - MODEL  (NATIONAL - 168 SERIES, VENDO - 721, etc.)
    
    Falls back to standalone Class/Make/Model columns if Type doesn't parse.
    """
    raw = _s(type_val)
    
    is_gf = False
    cls = None
    make = None
    model = None
    
    if not raw or raw == "Unknown" or raw == "":
        # Fallback to standalone columns
        cls = _s(class_fallback) if class_fallback else "Unknown"
        make = _s(make_fallback) if make_fallback else "Unknown"
        model = _s(model_fallback) if model_fallback else "Unknown"
        # Check standalone class for GF prefix
        if cls and cls.upper().startswith("GF "):
            is_gf = True
            cls = cls[3:].strip()
        return {
            "class": cls or "Unknown",
            "make": normalize_make(make) or "Unknown",
            "model": model or "Unknown",
            "is_glass_front": is_gf,
        }
    
    # ── Pattern 1: CLASS (MAKE MODEL) ──
    m = re.match(r'^(GF\s+)?(\w+(?:/\w+)?)\s*\(([^)]+)\)$', raw)
    if m:
        gf_prefix = m.group(1) is not None
        cls = m.group(2).strip()
        inside = m.group(3).strip()
        
        # Parse inside: MAKE MODEL (first word = make, rest = model)
        parts = inside.split(None, 1)
        make = parts[0] if parts else "Unknown"
        model = parts[1] if len(parts) > 1 else "Unknown"
        
        # Handle special cases inside parens
        if not parts:
            make = "Unknown"
            model = "Unknown"
        elif len(parts) == 1:
            make = parts[0]
            model = "Unknown"
        else:
            make = parts[0]
            model = parts[1]
        
        if gf_prefix:
            is_gf = True
        
        # Check the raw class for GF prefix too
        if raw.upper().startswith("GF "):
            is_gf = True
            cls = raw.split(None, 1)[1].split("(")[0].strip()
    else:
        # ── Pattern 2: MAKE - MODEL (dash-separated) ──
        m2 = re.match(r'^(GF\s+)?(\w+(?:\s+\w+)?)\s*-\s*(.+)$', raw)
        if m2:
            gf_prefix = m2.group(1) is not None
            prefix = m2.group(2).strip().upper()
            model_raw = m2.group(3).strip()
            
            # Map prefix to make + class
            prefix_info = _map_dash_prefix(prefix, model_raw, raw)
            if prefix_info:
                cls = prefix_info["class"]
                make = prefix_info["make"]
                model = prefix_info["model"]
            else:
                # Unknown prefix - try standalone columns
                cls = _s(class_fallback) if class_fallback else "Unknown"
                make = _s(make_fallback) if make_fallback else prefix
                model = model_raw
        else:
            # ── Pattern 3: No parens, no dash ──
            # Try standalone columns
            cls = _s(class_fallback) if class_fallback else "Unknown"
            make = _s(make_fallback) if make_fallback else "Unknown"
            model = _s(model_fallback) if model_fallback else "Unknown"
            
            # Check raw for GF prefix
            if raw.upper().startswith("GF "):
                is_gf = True
                cls = raw[3:].strip()
            
            # Handle special Type-only values like "Bev", "Snack", "Food"
            simple_class_match = re.match(r'^(GF\s+)?(Snack|Bev|Food|Snack/Bev|Unknown)$', raw, re.IGNORECASE)
            if simple_class_match:
                if simple_class_match.group(1):
                    is_gf = True
                cls = simple_class_match.group(2)
                # Keep standalone make/model
                if not make_fallback:
                    make = "Unknown"
                if not model_fallback:
                    model = "Unknown"
    
    # Normalize class
    if cls:
        cls = cls.strip()
        # Strip GF if still present in class name
        if cls.upper().startswith("GF "):
            is_gf = True
            cls = cls[3:].strip()
        # Class normalization
        cls_normalized = {
            "Snack/Food": "Snack",
        }.get(cls, cls)
        cls = cls_normalized
    
    # Fallback gaps from standalone columns
    if not cls or cls == "Unknown" or cls == "":
        cls = _s(class_fallback) if class_fallback else "Unknown"
    if not make or make == "Unknown" or make == "":
        make = _s(make_fallback) if make_fallback else "Unknown"
    if not model or model == "Unknown" or model == "":
        model = _s(model_fallback) if model_fallback else "Unknown"
    
    return {
        "class": cls or "Unknown",
        "make": normalize_make(make) or "Unknown",
        "model": model or "Unknown",
        "is_glass_front": is_gf,
    }


def _map_dash_prefix(prefix, model_raw, original_raw):
    """
    Map dash-separated type prefixes to class/make/model.
    Based on actual data analysis of Type column patterns.
    """
    prefix = prefix.strip()
    model_raw = model_raw.strip()
    
    # Class mapping for dash patterns (from data observations)
    CLASS_FOR_PREFIX = {
        "NATIONAL": None,  # varies
        "VENDO": "Bev",
        "DIXIE NARCO": None,  # varies
        "ROYAL": "Bev",
        "CRANE": None,  # varies
        "USI": "Snack",
        "AUTOMATIC PRODUCTS": "Snack",
        "WITTERN": "Snack",
        "PEPSI": "Bev",
        "UNKNOWN": None,
    }
    
    # Make mapping for dash patterns
    MAKE_FOR_PREFIX = {
        "NATIONAL": "Crane",
        "VENDO": "Vendo",
        "DIXIE NARCO": "DN",
        "ROYAL": "Royal",
        "CRANE": "Crane",
        "USI": "USI",
        "AUTOMATIC PRODUCTS": "VE",  # based on actual data
        "WITTERN": "USI",  # based on actual data
        "PEPSI": "DN",  # based on actual data
        "UNKNOWN": "Unknown",
    }
    
    # Model normalization for specific patterns
    MODEL_MAP = {
        "168 SERIES": "15x/16x",
        "167 SERIES": "15x/16x",
        "158 SERIES": "15x/16x",
        "181 SERIES": "Merchant",
        "3000 SERIES": "Evoke",
        "121TC SERIES": "Unknown",
        "LCM2 SERIES": "Unknown",
        "SNACK AP MISC": "Unknown",
        "MISC SNACK": "Misc Snack",
        "PEPSI LOANER COMBO": "Loaner Combo",
        "CAN BEV MISC": "Can Bev Misc",
        "501 SERIES": "501E",
        "5800 SERIES": "BevMax 4",
        "276 SERIES": "276E",
        "720 HVV SERIES": "HVV (720)",
        "BEV MAX 5800": "BevMax 5800",
        "CAN/B DN 501E SERIES": "501E",
        "472 SERIES": "472",
        'BEV MAX 4 W/7" SCREE': "BevMax 4",
    }
    
    # Check if original has GF prefix
    is_gf = original_raw.upper().startswith("GF ")
    
    # Determine class based on prefix + model
    cls = None
    if prefix in CLASS_FOR_PREFIX:
        cls = CLASS_FOR_PREFIX[prefix]
    
    # For NATIONAL, class depends on model
    if prefix == "NATIONAL":
        if model_raw in ("471", "472", "472 SERIES"):
            cls = "Food" if is_gf else "Snack"
        elif model_raw in ("186", "187", "168 SERIES", "167 SERIES", "158 SERIES", "181 SERIES"):
            cls = "Snack"
        else:
            cls = "Snack"
    
    # For DIXIE NARCO, class depends on model
    if prefix == "DIXIE NARCO":
        if is_gf or model_raw in ("3800", "5800 SERIES", "BEV MAX 5800"):
            cls = "Bev"
        elif model_raw in ("501 SERIES", "276 SERIES", "720 HVV SERIES", "CAN/B DN 501E SERIES"):
            cls = "Bev"
        else:
            cls = "Bev"
    
    # For CRANE
    if prefix == "CRANE":
        if model_raw in ("180-36",):
            cls = "Snack"
        elif model_raw in ("429D GPL",):
            cls = "Food"
        elif 'BEV' in model_raw.upper():
            cls = "Snack"  # BevMax models used as snack machines sometimes
        else:
            cls = "Snack"
    
    # For WITTERN
    if prefix == "WITTERN":
        cls = "Snack"
    
    # Normalize model
    model = MODEL_MAP.get(model_raw, model_raw)
    
    # For ROYAL, normalize model
    if prefix == "ROYAL":
        if model_raw in ("550", "660"):
            model = "GIII"
    
    # For VENDO, normalize model
    if prefix == "VENDO":
        if model_raw in ("621", "721", "821"):
            model = f"621/721/821"
    
    make = MAKE_FOR_PREFIX.get(prefix, prefix)
    
    return {
        "class": cls or "Unknown",
        "make": make,
        "model": model,
    }


# ─── 3. Place Parser (Section 2) ──────────────────────────────────────────────

# Person prefix + address pattern for Pattern B (Disney only)
# Requires: FirstName LastInitial - Number Street...
PATTERN_B_RE = re.compile(
    r'^([A-Z][a-z]+)\s+([A-Z])\.?\s*-\s+(\d)'
)

# Address pattern for Pattern B
ADDRESS_PATTERN_RE = re.compile(
    r'^\d+\s+\w+(?:\s+\w+)?\s*(?:\w+)?'
)

# Common breakroom/vending suffixes to strip
KNOWN_PLACE_SUFFIXES = [
    "Breakroom", "Br", "Break Room", "BREAKROOM", "BREAK AREA",
    "Vending Area", "Vending", "Break", "BREAK",
    "Breakroom ", "breakroom",
]


def parse_place(place_val, customer_val=None):
    """
    Parse Place column into location fields.
    
    Pattern A (simple, 75%): Venue-VenueBreakroom → place
    Pattern B (Disney, 25%): PersonName - Address-G/C-Zone FLOOR
    
    Returns dict with: place, building, floor, zone, zone_type
    """
    raw = _s(place_val)
    result = {
        "place": raw or None,
        "building": None,
        "floor": None,
        "zone": None,
        "zone_type": None,
        "suite": None,
        "room": None,
        "trailer": None,
    }
    
    if not raw:
        return result
    
    cust = _s(customer_val) if customer_val else ""
    is_disney = cust.upper().startswith("D-")
    
    # ── Try Pattern B first (has person name + address structure) ──
    # Only for Disney customers
    if is_disney:
        pattern_b_result = _try_pattern_b(raw)
        if pattern_b_result:
            return pattern_b_result
    
    # ── Try simplified Pattern B (G/C- prefix without person name) ──
    # E.g., "Gran Destino-G-3rd Floor", "Cast Services-G-AK LODGE..."
    simplified_b_result = _try_simplified_pattern_b(raw, is_disney)
    if simplified_b_result:
        return simplified_b_result
    
    # ── Pattern A (dash-separated, last segment = place) ──
    return _parse_pattern_a(raw)


def _try_pattern_b(raw):
    """
    Try Pattern B: PersonName - Address-G/C-Zone FLOOR
    Returns parsed dict or None.
    """
    # Check for person prefix + address pattern
    m = PATTERN_B_RE.match(raw)
    if not m:
        return None
    
    # Strip person prefix (FirstName + Initial + space + dash + space)
    first_name = m.group(1)
    last_init = m.group(2)
    prefix_end = raw.index(m.group(0)) + len(m.group(0)) - 1  # Keep the first digit
    
    remainder = raw[prefix_end:].strip()
    
    # Strip address (starts with digits, up to a dash or G- or end)
    addr_match = re.match(r'^(\d+\s+[\w\s]+?)(?:\s*-\s*G-|,\s*G-|\s*-|$)', remainder)
    if addr_match:
        remainder = remainder[addr_match.end():].strip()
    else:
        # Try simpler: just find the first -G- or - after the address
        g_idx = remainder.find('-G-')
        dash_idx = remainder.find(' - ')
        if g_idx >= 0:
            remainder = remainder[g_idx:].strip()
        elif dash_idx >= 0:
            remainder = remainder[dash_idx + 3:].strip()
    
    # Now parse the remainder for G/C- prefix, zone, floor, building
    return _parse_remainder(remainder)


def _try_simplified_pattern_b(raw, is_disney):
    """
    Try simplified Pattern B: No person prefix but has G/C- segments.
    E.g., "Gran Destino-G-3rd Floor", "Cast Services-G-AK LODGE ZEBRA 5TH REAR"
    """
    # Check if it has G- or C- after a dash
    if "-G-" not in raw and " -G-" not in raw and not raw.strip().startswith("C-"):
        return None
    
    # If it starts with C- (Cast area), handle specifically
    if raw.strip().startswith("C-"):
        return _parse_cast_place(raw)
    
    # Check for "xxx -G- yyy" or "xxx-G-yyy"
    m = re.search(r'-G-(.+)$', raw)
    if not m:
        return None
    
    # Split at the -G-
    prefix_part = raw[:m.start()].strip()
    g_part = m.group(1).strip()
    
    # If prefix has address pattern, try to strip it
    prefix = _strip_address(prefix_part)
    
    # Parse G-part for floor, building, zone
    return _parse_g_part(g_part, prefix)


def _parse_cast_place(raw):
    """
    Parse C- prefixed places like:
    "C-MK EASTGATE SECURITY-Be Our Guest"
    "C-MK EASTGATE SECURITY-Space Mountain"
    "C- RIDE AND SHOW-C- RIDE AND SHOW"
    """
    # Strip leading C-
    content = re.sub(r'^C-\s*', '', raw)
    
    parts = content.split('-')
    parts = [p.strip() for p in parts if p.strip()]
    
    if not parts:
        return {
            "place": raw,
            "building": None,
            "floor": None,
            "zone": None,
            "zone_type": "cast",
            "suite": None,
            "room": None,
            "trailer": None,
        }
    
    # Last part is the zone/area
    last = parts[-1]
    
    return {
        "place": last,
        "building": parts[0] if len(parts) > 1 else None,
        "floor": None,
        "zone": last,
        "zone_type": "cast",
        "suite": None,
        "room": None,
        "trailer": None,
    }


def _parse_g_part(g_part, prefix):
    """
    Parse the G-prefixed portion for floor, building, zone.
    E.g., "REAR 6TH FL", "BUILDING 9 LOBBY", "COZY CONE POOL", "3rd Floor"
    """
    result = {
        "place": prefix or g_part,
        "building": None,
        "floor": None,
        "zone": None,
        "zone_type": "guest",
        "suite": None,
        "room": None,
        "trailer": None,
    }
    
    # Extract floor
    floor_info = _extract_floor(g_part)
    
    # Extract building
    building_info = _extract_building(g_part)
    
    # The remainder after removing floor and building is the zone
    remainder = g_part
    if floor_info["raw"]:
        remainder = remainder.replace(floor_info["raw"], "", 1).strip()
    if building_info["raw"]:
        remainder = remainder.replace(building_info["raw"], "", 1).strip()
    
    # Clean multiple spaces
    remainder = re.sub(r'\s+', ' ', remainder).strip()
    
    result["floor"] = floor_info["floor"]
    result["building"] = building_info["building"]
    result["zone"] = remainder if remainder else None
    
    # If we have a prefix (venue name), use it as place
    if prefix:
        result["place"] = prefix
    
    return result


def _parse_remainder(remainder):
    """
    Parse the remainder after stripping person and address.
    Contains G/C- prefix, zone, floor, building.
    """
    result = {
        "place": remainder,
        "building": None,
        "floor": None,
        "zone": None,
        "zone_type": None,
        "suite": None,
        "room": None,
        "trailer": None,
    }
    
    if not remainder:
        return result
    
    # Check for G- or C- prefix
    zone_type = None
    text = remainder
    if text.upper().startswith("G-"):
        zone_type = "guest"
        text = text[2:].strip()
    elif text.upper().startswith("C-"):
        zone_type = "cast"
        text = text[2:].strip()
    
    result["zone_type"] = zone_type
    
    # Extract floor from text
    floor_info = _extract_floor(text)
    
    # Extract building from text (after floor removal)
    text_no_floor = text
    if floor_info["raw"]:
        text_no_floor = text_no_floor.replace(floor_info["raw"], "", 1).strip()
    
    building_info = _extract_building(text_no_floor)
    
    # After removing floor and building, remaining is zone
    zone_remainder = text_no_floor
    if building_info["raw"]:
        zone_remainder = zone_remainder.replace(building_info["raw"], "", 1).strip()
    
    # Clean up
    zone_remainder = re.sub(r'\s+', ' ', zone_remainder).strip()
    
    result["floor"] = floor_info["floor"]
    result["building"] = building_info["building"]
    result["zone"] = zone_remainder if zone_remainder else None
    
    # Set place to the original G/C- stripped content, or use zone
    if result["zone"]:
        result["place"] = result["zone"]
    elif result["building"]:
        result["place"] = result["building"]
    else:
        result["place"] = text
    
    return result


def _strip_address(text):
    """
    Try to strip address pattern from start of text.
    E.g., "Gran Destino" from "Gran Destino-G-3rd Floor"
    """
    # If text ends with -G- (stripped), the prefix is the venue
    return text


def _parse_pattern_a(raw):
    """
    Parse Pattern A: dash-separated, last segment = place.
    
    Per the handbook:
    1. Split on `-`
    2. Take the last non-empty segment
    3. Strip known suffixes (Breakroom, Br, Break Room, BREAKROOM, Vending, Break, etc.)
    4. Extract floor info if present
    5. Extract building info if present
    6. Remaining text is the place name
    """
    result = {
        "place": None,
        "building": None,
        "floor": None,
        "zone": None,
        "zone_type": None,
    }
    
    if not raw:
        return result
    
    text = raw.strip()
    
    # Check for C- or G- prefix on the overall string (area marker)
    zone_type = None
    if text.upper().startswith("C-"):
        zone_type = "cast"
        text = text[2:].strip()
    elif text.upper().startswith("G-"):
        zone_type = "guest"
        text = text[2:].strip()
    
    result["zone_type"] = zone_type
    
    # Split by dash
    parts = [p.strip() for p in text.split('-') if p.strip()]
    
    if not parts:
        result["place"] = text or raw
        return result
    
    # Take the last segment
    last = parts[-1]
    
    # Strip known suffixes
    stripped = _strip_place_suffix(last)
    
    # If stripping left empty, the last segment was entirely a suffix.
    # Use the second-to-last segment as the place.
    if not stripped:
        if len(parts) >= 2:
            stripped = _strip_place_suffix(parts[-2])
            last = parts[-2]
        else:
            stripped = last
    
    # Extract floor info
    floor_info = _extract_floor(stripped)
    text_no_floor = stripped
    if floor_info["raw"]:
        text_no_floor = stripped.replace(floor_info["raw"], "", 1).strip()
    
    # Extract building info
    building_info = _extract_building(text_no_floor)
    text_no_building = text_no_floor
    if building_info["raw"]:
        text_no_building = text_no_floor.replace(building_info["raw"], "", 1).strip()
    
    # Extract suite info
    suite_info = _extract_suite(text_no_building)
    text_no_extra = text_no_building
    if suite_info["raw"]:
        text_no_extra = text_no_building.replace(suite_info["raw"], "", 1).strip()
    
    # Extract room info
    room_info = _extract_room(text_no_extra)
    text_no_extra2 = text_no_extra
    if room_info["raw"]:
        text_no_extra2 = text_no_extra.replace(room_info["raw"], "", 1).strip()
    
    # Extract trailer info
    trailer_info = _extract_trailer(text_no_extra2)
    text_no_extra3 = text_no_extra2
    if trailer_info["raw"]:
        text_no_extra3 = text_no_extra2.replace(trailer_info["raw"], "", 1).strip()
    
    # Clean up
    remainder = re.sub(r'\s+', ' ', text_no_extra3).strip()
    
    # If everything was consumed by extractions (last segment was
    # purely floor/building/suite/trailer info), fall back to
    # the second-to-last segment as the place
    if not remainder and len(parts) >= 2:
        second_last = _strip_place_suffix(parts[-2])
        second_last = re.sub(r'\s+', ' ', second_last).strip() if second_last else None
        if second_last:
            remainder = second_last
    
    result["floor"] = floor_info["floor"]
    result["building"] = building_info["building"]
    result["suite"] = suite_info["suite"]
    result["room"] = room_info["room"]
    result["trailer"] = trailer_info["trailer"]
    result["zone"] = remainder if remainder else None
    result["place"] = remainder if remainder else last
    
    return result



def _strip_place_suffix(text):
    """Strip known breakroom/vending suffixes from place text.
    
    Only strips when the suffix is at the END of the text (last word(s)),
    so 'Breakroom Main' is NOT stripped but 'Turano Breakroom' IS stripped
    to 'Turano'.
    """
    if not text:
        return text
    
    text_lower = text.lower().strip()
    
    # Suffix phrases to strip from the END (ordered longest-first to avoid partial matches)
    suffix_phrases = [
        "breakroom", "break room", "break area",
        "vending area",
        "breakro",  # truncated form
    ]
    
    # Single-word suffix tokens (strip as last word)
    suffix_words = [
        "breakroom", "break", "vending", "br",
        "breakro",  # truncated
    ]
    
    # Try stripping as the entire remaining text first (phrase-level)
    for suffix in suffix_phrases:
        sl = suffix.lower()
        if text_lower.endswith(sl):
            # Make sure the suffix is the last thing (not part of a compound name)
            # Check that the suffix isn't preceded by another word without a space
            before = text_lower[:-len(sl)].strip()
            if before and not before[-1].isalnum():
                # Good, there's a space/separator before the suffix
                return text[:-(len(suffix) + (1 if text_lower[-len(sl)-1:-len(sl)] == ' ' else 0))].strip()
            elif not before:
                # Text IS entirely the suffix
                return ""
    
    # Try stripping just the last word if it's a suffix
    words = text.split()
    if len(words) >= 1:
        last_word = words[-1].lower().strip('.,;:!?')
        if last_word in suffix_words:
            return ' '.join(words[:-1])
    
    # Try stripping last two words: "Breakroom Main" won't match but "Vending Area" will
    if len(words) >= 2:
        last_two = ' '.join(w.lower().strip('.,;:!?') for w in words[-2:])
        if last_two in suffix_phrases:
            return ' '.join(words[:-2])
    
    return text


def _extract_floor(text):
    """Extract floor number from text. Returns dict with floor and raw match."""
    for pattern, extractor in FLOOR_PATTERNS:
        m = pattern.search(text)
        if m:
            try:
                floor_num = int(extractor(m))
                return {"floor": floor_num, "raw": m.group(0)}
            except (ValueError, IndexError):
                continue
    return {"floor": None, "raw": None}


def _extract_building(text):
    """Extract building info from text. Returns dict with building and raw match."""
    for pattern in BUILDING_PATTERNS:
        m = pattern.search(text)
        if m:
            return {"building": m.group(0).strip(), "raw": m.group(0)}
    return {"building": None, "raw": None}


def _extract_suite(text):
    """Extract suite number from text. Returns dict with suite and raw match."""
    for pattern, extractor in SUITE_PATTERNS:
        m = pattern.search(text)
        if m:
            return {"suite": extractor(m), "raw": m.group(0)}
    return {"suite": None, "raw": None}


def _extract_room(text):
    """Extract room info from text. Returns dict with room and raw match."""
    for pattern, extractor in ROOM_PATTERNS:
        m = pattern.search(text)
        if m:
            return {"room": extractor(m), "raw": m.group(0)}
    return {"room": None, "raw": None}


def _extract_trailer(text):
    """Extract trailer info from text. Returns dict with trailer and raw match."""
    for pattern, extractor in TRAILER_PATTERNS:
        m = pattern.search(text)
        if m:
            return {"trailer": extractor(m), "raw": m.group(0)}
    return {"trailer": None, "raw": None}


# ─── 4. Customer Parser (Section 1) ───────────────────────────────────────────

def parse_customer(customer_val):
    """
    Parse Customer column into company and disney_park.
    
    Disney customers (D- prefix) get mapped to a Disney property.
    Non-Disney customers map directly to company.
    """
    raw = _s(customer_val)
    result = {
        "company": None,
        "disney_park": None,
        "disney_area": None,
    }
    
    if not raw:
        return result
    
    raw_upper = raw.upper()
    
    # Check for Disney prefix
    if raw_upper.startswith("D-"):
        result["company"] = "Disney"
        
        # Find the best matching Disney property
        # Try exact match first
        if raw in DISNEY_PROPERTY_MAP:
            result["disney_park"] = DISNEY_PROPERTY_MAP[raw]
        else:
            # Try case-insensitive prefix matching
            matched = False
            for prefix, park_name in DISNEY_PROPERTY_MAP.items():
                if raw_upper.startswith(prefix.upper()):
                    result["disney_park"] = park_name
                    matched = True
                    break
            
            if not matched:
                # Try prefix matching against the raw customer
                for prefix in DISNEY_CUSTOMER_PREFIXES:
                    if raw_upper.startswith(prefix.upper()):
                        result["disney_park"] = prefix  # use as-is
                        break
                
                if not result["disney_park"]:
                    result["disney_park"] = raw
    else:
        result["company"] = raw
    
    # Resolve Disney resort area from disney_park
    if result["disney_park"] and result["disney_park"] in DISNEY_AREA_MAP:
        result["disney_area"] = DISNEY_AREA_MAP[result["disney_park"]]
    
    return result


# ─── 5. Serial Number Validator (Section 4) ───────────────────────────────────

# OCR character confusion table
OCR_CORRECTIONS = {
    'S': '5',
    's': '5',
    'I': '1',
    'l': '1',  # lowercase L also often confused with 1
    'O': '0',
    'o': '0',
    'B': '8',
    'b': '8',
    'G': '6',
    'g': '6',
}


def validate_serial(serial_val):
    """
    Validate and OCR-correct serial number.
    
    Returns dict with: serial_number (corrected), is_valid, is_placeholder
    """
    raw = _s(serial_val)
    result = {
        "serial_number": None,
        "is_valid": False,
        "is_placeholder": False,
    }
    
    if not raw:
        return result
    
    # Step 1: Strip non-alphanumeric characters (dashes, spaces, dots)
    cleaned = re.sub(r'[^a-zA-Z0-9]', '', raw)
    
    if not cleaned:
        return result
    
    # Step 2: Check for placeholder values
    # Placeholder: < 5 chars, or obvious placeholders
    if len(cleaned) < 5:
        result["is_placeholder"] = True
        return result
    
    # Check for known placeholder values
    if cleaned.lower() in ("pending", "n/a", "none", "null", "unknown"):
        result["is_placeholder"] = True
        return result
    
    if cleaned == '*' or cleaned == 'S' or cleaned == 'a':
        result["is_placeholder"] = True
        return result
    
    # Step 3: Apply OCR correction
    corrected = []
    for ch in cleaned:
        corrected.append(OCR_CORRECTIONS.get(ch, ch))
    corrected_str = ''.join(corrected)
    
    # Step 4: Check if all chars are the same (invalid)
    if len(set(corrected_str)) == 1:
        result["is_valid"] = False
        result["is_placeholder"] = True
        return result
    
    # Step 5: Length validation (valid serials are 9-14 chars after cleaning)
    is_valid = 9 <= len(corrected_str) <= 14
    
    result["serial_number"] = corrected_str
    result["is_valid"] = is_valid
    
    return result


# ─── 6. Alert Parser (Section 8) ──────────────────────────────────────────────

def parse_alerts(alert_val, coil_alerts_val=None, product_alerts_val=None):
    """
    Parse Alerts column into structured alert data.
    
    Returns dict with:
    - has_alert: bool
    - severity: info | critical | none
    - service_scheduled: bool
    - out_of_touch: bool
    - out_of_touch_hours: int | None
    - has_not_dexed: bool
    - not_dexed_duration: str | None
    - coil_alert_count: int
    - product_alert_count: int
    - raw_text: str | None
    """
    result = {
        "has_alert": False,
        "severity": "none",
        "service_scheduled": False,
        "service_is_today": False,
        "out_of_touch": False,
        "out_of_touch_hours": None,
        "has_not_dexed": False,
        "not_dexed_duration": None,
        "coil_alert_count": 0,
        "product_alert_count": 0,
        "raw_text": None,
    }
    
    # Process coil and product alerts
    coil = _int_or_none(coil_alerts_val)
    product = _int_or_none(product_alerts_val)
    if coil and coil > 0:
        result["coil_alert_count"] = coil
    if product and product > 0:
        result["product_alert_count"] = product
    
    # Process Alert text
    raw = _s(alert_val)
    
    if not raw:
        # Check if there are numeric alerts
        if result["coil_alert_count"] > 0 or result["product_alert_count"] > 0:
            result["has_alert"] = True
            result["severity"] = "info"
        return result
    
    result["raw_text"] = raw
    result["has_alert"] = True
    
    # Check for service scheduled
    if "scheduled for service today" in raw.lower():
        result["service_scheduled"] = True
        result["service_is_today"] = True
        result["severity"] = "info"
    
    if "scheduled for service on" in raw.lower():
        result["service_scheduled"] = True
        result["severity"] = "info"
    
    # Check for out of touch
    oot_match = re.search(r'Out of touch for ([\d.]+)\s*(hours?|days?)', raw, re.IGNORECASE)
    if oot_match:
        result["out_of_touch"] = True
        value = float(oot_match.group(1))
        unit = oot_match.group(2).lower()
        if "hour" in unit:
            result["out_of_touch_hours"] = value
        elif "day" in unit:
            result["out_of_touch_hours"] = value * 24
        result["severity"] = "critical"
    
    # Check for Has not DEXed
    dex_match = re.search(r'Has not DEXed in ([\d.]+)\s*(hours?|days?)', raw, re.IGNORECASE)
    if dex_match:
        result["has_not_dexed"] = True
        result["not_dexed_duration"] = dex_match.group(0)
        if result["severity"] == "none":
            result["severity"] = "info"
    
    return result


# ─── 7. Sales Parser ──────────────────────────────────────────────────────────

def parse_sales(raw):
    """
    Parse sales-related columns from the raw dict.
    
    Returns dict with yearly_sales, monthly_sales, weekly_sales,
    days_since_restock, daily_avg_sales, today_sales, yesterday_sales,
    sales_since_restock.
    """
    return {
        "yearly_sales": _num(raw.get("Yearly Sales")),
        "monthly_sales": _num(raw.get("Monthly Sales")),
        "weekly_sales": _num(raw.get("Weekly Sales")),
        "daily_avg_sales": _num(raw.get("Daily Average Sales")),
        "today_sales": _num(raw.get("Today Sales")),
        "yesterday_sales": _num(raw.get("Yesterday Sales")),
        "sales_since_restock": _num(raw.get("Sales (Restock)")),
        "days_since_restock": _int_or_none(raw.get("Days Since Restock")),
    }


# ─── 8. Priority Scorer (Section 9) ──────────────────────────────────────────

def compute_priority(yearly_sales):
    """
    Compute priority tier from yearly sales.
    
    Low: ≤ $2,000
    Mid: $2,001 - $15,000
    High: $15,001+
    """
    if yearly_sales is None:
        return "Unknown"
    if yearly_sales <= 2000:
        return "Low"
    elif yearly_sales <= 15000:
        return "Mid"
    else:
        return "High"


# ─── 9. Machine ID → Class Correction ───────────────────────────────────────

def correct_class_by_machine_id(machine):
    """
    Post-processing: correct class based on machine_id prefix patterns.
    
    Rules from data analysis:
    - 90xxx-99xxx → Bev (Beverage machine ID range)
    - 60xxx-73xxx → Snack or Food (Snack/Food machine ID range)
    
    Only corrects when the model is clearly a beverage machine (BevMax, Media)
    or the class is Unknown and the ID range gives a strong signal.
    """
    mid = str(machine.get("machine_id", ""))
    cls = machine.get("class", "Unknown")
    model = machine.get("model", "")
    make = machine.get("make", "")
    
    # Determine ID range
    is_bev_range = mid.startswith(("90", "91", "92", "93", "94", "95", "96", "97", "98", "99"))
    is_snack_range = mid.startswith(("60", "61", "62", "63", "64", "65", "66", "67", "68", "69", "70", "71", "72", "73"))
    
    # Rule 1: In Bev range but classed as Snack with BevMax/Media model → fix to Bev
    if is_bev_range and cls in ("Snack", "Snack/Bev") and model and ("BevMax" in model or "Media" in model):
        machine["class"] = "Bev"
        machine["class_corrected"] = True
        machine["class_correction_reason"] = f"Model '{model}' in Bev ID range"
    
    # Rule 2: Unknown class in Bev range → set to Bev
    elif is_bev_range and cls == "Unknown":
        machine["class"] = "Bev"
        machine["class_corrected"] = True
        machine["class_correction_reason"] = "Unknown in Bev ID range"
    
    # Rule 3: Unknown class in Snack/Food range → set to Snack
    elif is_snack_range and cls == "Unknown":
        machine["class"] = "Snack"
        machine["class_corrected"] = True
        machine["class_correction_reason"] = "Unknown in Snack ID range"
    
    return machine


# ─── 10. Main Parser ───────────────────────────────────────────────────────────

# Full column mapping: Excel column index (1-based) → DB field name
COLUMN_MAPPING = {
    1: "Device",
    2: "Location",
    3: "Asset ID",
    4: "Place",
    5: "Type",
    6: "City",
    7: "Address",
    8: "Last Contact Time",
    9: "Last Dex Report Time",
    10: "Last Restock",
    11: "Coil Alerts",
    12: "Product Alerts",
    13: "Sales (Restock)",
    14: "Daily Average Sales",
    15: "Today Sales",
    16: "Yesterday Sales",
    17: "Weekly Sales",
    18: "Monthly Sales",
    19: "Yearly Sales",
    20: "Days Since Restock",
    21: "Prepick Group",
    22: "Customer",
    23: "Management Company",
    24: "Management Account",
    25: "Machine Management Code",
    26: "Route",
    27: "Subroute",
    28: "Changer Par",
    29: "Acquired From",
    30: "Purchase Date",
    31: "Purchase Price",
    32: "Depreciation Years",
    33: "Post-Depreciation Monthly Cost",
    34: "State",
    35: "Postal Code",
    36: "Deployed",
    37: "Pulled Date",
    38: "Serial Number",
    39: "Class",
    40: "Make",
    41: "Model",
    42: "Cash Discount",
    43: "Tax Jurisdiction",
    44: "Commission Plan",
    45: "Barcode",
    46: "Non-Revenue",
    47: "Added Date",
    48: "Phone",
    49: "Fax",
    50: "Email",
    51: "Has Cashless",
    52: "Branch",
    53: "Location Code",
    54: "Customer Code",
    55: "Last Inventory",
    56: "Asset Family",
    57: "Status",
    58: "Alerts",
    59: "Valid Address",
    60: "Business Type",
    61: "Primary Consumer Type",
    62: "Machine Branding",
}


def parse_excel(filepath):
    """
    Parse the Excel file and return a list of machine record dicts.
    
    Each record maps raw columns to DB fields, with parsing applied
    to Device, Type, Place, Customer, Serial Number, Status, and Alerts.
    """
    wb = openpyxl.load_workbook(filepath, read_only=False, data_only=True)
    ws = wb.active
    
    # Compute dimensions
    max_col = ws.max_column or 62
    max_row = ws.max_row or 1849
    
    # Build column index map from header row
    headers = [ws.cell(1, c).value for c in range(1, max_col + 1)]
    col_map = {}
    for i, h in enumerate(headers, 1):
        col_map[h] = i
    
    machines = []
    
    for row_idx in range(2, max_row + 1):
        row = [ws.cell(row_idx, c).value for c in range(1, ws.max_column + 1)]
        
        # Build raw data dict with field names as keys
        raw = {}
        for col_idx, field_name in COLUMN_MAPPING.items():
            raw[field_name] = row[col_idx - 1] if col_idx - 1 < len(row) else None
        
        # ── Parse each field ──
        
        # Device → telemetry
        device_info = parse_device(raw["Device"])
        
        # Type → class, make, model, is_glass_front
        type_info = parse_type(
            raw["Type"],
            class_fallback=raw.get("Class"),
            make_fallback=raw.get("Make"),
            model_fallback=raw.get("Model"),
        )
        
        # Place → location fields
        place_info = parse_place(raw["Place"], raw["Customer"])
        
        # Customer → company + disney_park
        customer_info = parse_customer(raw["Customer"])
        
        # Serial Number → validate + OCR correct
        serial_info = validate_serial(raw["Serial Number"])
        
        # Status → remote_pricing_status
        status_raw = _s(raw.get("Status"))
        remote_pricing_status = status_raw if status_raw else None
        
        # Alerts → parsed structure
        alerts_info = parse_alerts(
            raw.get("Alerts"),
            coil_alerts_val=raw.get("Coil Alerts"),
            product_alerts_val=raw.get("Product Alerts"),
        )
        
        # Sales data
        sales_info = parse_sales(raw)
        
        # Compute priority from yearly sales
        priority = compute_priority(sales_info["yearly_sales"])
        
        # Has Cashless
        has_cashless = _bool_from_yesno(raw.get("Has Cashless"))
        
        # Deployed
        deployed = _bool_from_yesno(raw.get("Deployed"))
        
        # Build machine record
        machine = {
            # Identity
            "machine_id": _s(raw.get("Asset ID")),
            "serial_number": serial_info["serial_number"],
            "serial_is_valid": serial_info["is_valid"],
            "serial_is_placeholder": serial_info["is_placeholder"],
            
            # Type info
            "class": type_info["class"],
            "make": type_info["make"],
            "model": type_info["model"],
            "is_glass_front": type_info["is_glass_front"],
            
            # Location
            "place": place_info["place"],
            "building_name": place_info["building"],
            "floor": place_info["floor"],
            "suite": place_info["suite"],
            "room": place_info["room"],
            "trailer": place_info["trailer"],
            "zone": place_info["zone"],
            "zone_type": place_info["zone_type"],
            "address": _s(raw.get("Address")),
            "location_area": _s(raw.get("City")),
            "state": _s(raw.get("State")),
            "postal_code": _s(raw.get("Postal Code")),
            
            # Customer
            "company": customer_info["company"],
            "disney_park": customer_info["disney_park"],
            "disney_area": customer_info["disney_area"],
            
            # Telemetry
            "telemetry_provider": device_info["telemetry_provider"],
            "card_reader_brand": device_info["card_reader_brand"],
            
            # Pricing
            "remote_pricing_status": remote_pricing_status,
            
            # Alerts
            "alerts": alerts_info,
            
            # Sales & Priority
            "yearly_sales": sales_info["yearly_sales"],
            "monthly_sales": sales_info["monthly_sales"],
            "weekly_sales": sales_info["weekly_sales"],
            "daily_avg_sales": sales_info["daily_avg_sales"],
            "today_sales": sales_info["today_sales"],
            "yesterday_sales": sales_info["yesterday_sales"],
            "sales_since_restock": sales_info["sales_since_restock"],
            "days_since_restock": sales_info["days_since_restock"],
            "priority": priority,
            
            # Other
            "prepick_group": _s(raw.get("Prepick Group")),
            "has_cashless": has_cashless,
            "deployed": deployed,
            
            # Dates
            "install_date": raw.get("Added Date"),
            "dex_report_date": raw.get("Last Dex Report Time"),
            "pulled_date": raw.get("Pulled Date"),
            "last_contact_time": raw.get("Last Contact Time"),
            "last_restock": raw.get("Last Restock"),
            
            # Raw reference (for debugging)
            "coil_alert_count": _int_or_none(raw.get("Coil Alerts")),
            "product_alert_count": _int_or_none(raw.get("Product Alerts")),
            "route": _s(raw.get("Route")),
            "subroute": _s(raw.get("Subroute")),
            "branch": _s(raw.get("Branch")),
            "asset_family": _s(raw.get("Asset Family")),
            "valid_address": _s(raw.get("Valid Address")),
        }
        
        # Apply machine ID → class correction
        machine = correct_class_by_machine_id(machine)
        
        machines.append(machine)
    
    wb.close()
    return machines


def parse_excel_to_json(filepath, pretty=True):
    """Parse Excel and return JSON string of all machines."""
    machines = parse_excel(filepath)
    indent = 2 if pretty else None
    return json.dumps(machines, indent=indent, default=str, ensure_ascii=False)


# ─── CLI / Test ──────────────────────────────────────────────────────────────

if __name__ == "__main__":
    import sys
    
    filepath = sys.argv[1] if len(sys.argv) > 1 else "seed-data/Machine_List.xlsx"
    
    print(f"📄 Parsing {filepath}...")
    machines = parse_excel(filepath)
    
    print(f"✅ Parsed {len(machines)} machines\n")
    
    print("=" * 60)
    print("FIRST 3 MACHINES (JSON)")
    print("=" * 60)
    
    for i, m in enumerate(machines[:3]):
        print(f"\n--- Machine {i + 1} ---")
        print(json.dumps(m, indent=2, default=str, ensure_ascii=False))
    
    print("\n" + "=" * 60)
    print("SUMMARY STATISTICS")
    print("=" * 60)
    
    # Count non-null values
    fields = [
        "telemetry_provider", "company", "disney_park",
        "class", "is_glass_front", "remote_pricing_status",
        "priority", "yearly_sales",
    ]
    for field in fields:
        count = sum(1 for m in machines if m.get(field) is not None and m.get(field) != "")
        print(f"  {field:30s}: {count:5d} / {len(machines)}")
    
    # Telemetry provider distribution
    from collections import Counter
    telem_dist = Counter(m.get("telemetry_provider") or "None" for m in machines)
    print("\n  Telemetry Provider Distribution:")
    for provider, count in telem_dist.most_common():
        print(f"    {provider:15s}: {count}")
    
    # Disney count
    disney_count = sum(1 for m in machines if m.get("disney_park"))
    print(f"\n  Disney machines: {disney_count}")
    
    # Serial validation stats
    valid_serials = sum(1 for m in machines if m.get("serial_is_valid"))
    placeholder_serials = sum(1 for m in machines if m.get("serial_is_placeholder"))
    print(f"  Valid serials: {valid_serials}")
    print(f"  Placeholder serials: {placeholder_serials}")
    
    # Priority distribution
    priority_dist = Counter(m.get("priority") or "Unknown" for m in machines)
    print("\n  Priority Distribution:")
    for pri, count in priority_dist.most_common():
        print(f"    {pri:10s}: {count}")
    
    # Glass front count
    gf_count = sum(1 for m in machines if m.get("is_glass_front"))
    print(f"\n  Glass Front machines: {gf_count}")
    
    # Remote pricing status distribution
    rp_dist = Counter(m.get("remote_pricing_status") or "None" for m in machines)
    print("\n  Remote Pricing Status:")
    for status, count in rp_dist.most_common():
        print(f"    {status:20s}: {count}")
    
    print("\n✅ Done!")