Spreadsheet Direct Verification

# Add to the INPUTS list if you have separate labor/assignment sheets
# Extend MARKER_CANDIDATES for production-specific columns
PRODUCTION_MARKER_CANDIDATES = MARKER_CANDIDATES | {"press", "team_member", "role", "changeover", "date", "week"}

# Production constraint thresholds (adjust per task)
MAX_DAILY_MINUTES_PER_PRESS = 450
MAX_WEEKLY_HOURS_PER_MEMBER = 37.5
REQUIRED_ROLES_PER_PRESS = {"operator", "water_spider"}  # or {"Operator", "Water Spider"}
MIN_CHANGEOVERS_PER_PRESS_PER_WEEK = 3

def validate_production_constraints(ws, headers):
    """Return a dict of constraint violations found in the worksheet."""
    violations = {
        "daily_minutes_exceeded": [],
        "weekly_hours_exceeded": [],
        "missing_roles": [],
        "insufficient_changeovers": [],
    }
    
    if not headers:
        return violations
    
    normalized_headers = [normalize(h).lower() for h in headers]
    
    # Find column indices
    press_idx = next((i for i, h in enumerate(normalized_headers) if "press" in h), None)
    member_idx = next((i for i, h in enumerate(normalized_headers) if "member" in h or "team" in h), None)
    role_idx = next((i for i, h in enumerate(normalized_headers) if "role" in h), None)
    minutes_idx = next((i for i, h in enumerate(normalized_headers) if "minute" in h or "labor" in h), None)
    date_idx = next((i for i, h in enumerate(normalized_headers) if "date" in h), None)
    changeover_idx = next((i for i, h in enumerate(normalized_headers) if "changeover" in h), None)
    
    # Aggregate data
    press_daily_minutes = {}  # (press, date) -> total minutes
    member_weekly_hours = {}  # (member, week) -> total hours
    press_roles = {}  # (press, date) -> set of roles
    press_changeovers = {}  # (press, week) -> count
    
    for row in ws.iter_rows(min_row=2, values_only=True):
        if not any(v is not None and str(v).strip() != "" for v in row):
            continue
        
        press = normalize(row[press_idx]) if press_idx is not None and press_idx < len(row) else None
        member = normalize(row[member_idx]) if member_idx is not None and member_idx < len(row) else None
        role = normalize(row[role_idx]).lower() if role_idx is not None and role_idx < len(row) else None
        minutes = float(row[minutes_idx]) if minutes_idx is not None and minutes_idx < len(row) and row[minutes_idx] else 0
        date_val = row[date_idx] if date_idx is not None and date_idx < len(row) else None
        is_changeover = row[changeover_idx] if changeover_idx is not None and changeover_idx < len(row) else None
        
        # Derive week from date if available
        week = None
        date_str = ""
        if date_val:
            try:
                from datetime import datetime
                if isinstance(date_val, datetime):
                    week = date_val.isocalendar()[1]
                date_str = str(date_val)
            except:
                date_str = str(date_val) if date_val else ""
                week = None
        else:
            date_str = ""
        
        # Aggregate daily minutes per press
        if press and minutes:
            key = (press, date_str)
            press_daily_minutes[key] = press_daily_minutes.get(key, 0) + minutes
        
        # Aggregate weekly hours per member (assume minutes -> hours)
        if member and minutes and week:
            key = (member, week)
            member_weekly_hours[key] = member_weekly_hours.get(key, 0) + minutes / 60
        
        # Track roles per press per date
        if press and role and date_str:
            key = (press, date_str)
            if key not in press_roles:
                press_roles[key] = set()
            press_roles[key].add(role)
        
        # Count changeovers per press per week
        if press and is_changeover and week:
            if is_truthy(is_changeover) or str(is_changeover).lower() in {"yes", "true", "1", "y", "x"}:
                key = (press, week)
                press_changeovers[key] = press_changeovers.get(key, 0) + 1
    
    # Check violations
    for (press, date), total in press_daily_minutes.items():
        if total > MAX_DAILY_MINUTES_PER_PRESS:
            violations["daily_minutes_exceeded"].append(f"{press} on {date}: {total} min")
    
    for (member, week), total in member_weekly_hours.items():
        if total > MAX_WEEKLY_HOURS_PER_MEMBER:
            violations["weekly_hours_exceeded"].append(f"{member} week {week}: {total:.1f} hrs")
    
    for (press, date), roles in press_roles.items():
        missing = REQUIRED_ROLES_PER_PRESS - roles
        if missing:
            violations["missing_roles"].append(f"{press} on {date}: missing {missing}")
    
    for (press, week), count in press_changeovers.items():
        if count < MIN_CHANGEOVERS_PER_PRESS_PER_WEEK:
            violations["insufficient_changeovers"].append(f"{press} week {week}: {count} changeovers")
    
    return violations

from pathlib import Path
from openpyxl import load_workbook

INPUTS = [
    "input.xlsx",
    "output.xlsx",
]

MARKER_CANDIDATES = {"mark", "marked", "flag", "selected", "include", "status"}

TRUTHY = {"x", "yes", "true", "1", "y"}

def normalize(value):
    if value is None:
        return ""
    return str(value).strip()

def is_truthy(value):
    return normalize(value).lower() in TRUTHY

def first_nonempty_row(ws, max_scan=10):
    for row in ws.iter_rows(min_row=1, max_row=min(ws.max_row, max_scan), values_only=True):
        if any(v is not None and str(v).strip() != "" for v in row):
            return list(row)
    return []

def data_row_count(ws):
    count = 0
    for row in ws.iter_rows(min_row=2, values_only=True):
        if any(v is not None and str(v).strip() != "" for v in row):
            count += 1
    return count

def marked_row_count(ws, headers):
    if not headers:
        return 0, None
    normalized_headers = [normalize(h).lower() for h in headers]
    marker_idx = None
    for i, h in enumerate(normalized_headers):
        if h in MARKER_CANDIDATES:
            marker_idx = i
            break
    if marker_idx is None:
        return 0, None

    count = 0
    for row in ws.iter_rows(min_row=2, values_only=True):
        if not any(v is not None and str(v).strip() != "" for v in row):
            continue
        value = row[marker_idx] if marker_idx < len(row) else None
        if is_truthy(value):
            count += 1
    return count, headers[marker_idx]

for file_str in INPUTS:
    path = Path(file_str)
    print(f"FILE: {path}")
    print(f"EXISTS: {path.exists()}")

    if not path.exists():
        print()
        continue

    wb = load_workbook(path, data_only=False)
    print(f"SHEETS: {wb.sheetnames}")

    for ws in wb.worksheets:
        headers = first_nonempty_row(ws)
        rows = data_row_count(ws)
        marked_count, marker_name = marked_row_count(ws, headers)

        print(f"  SHEET: {ws.title}")
        print(f"    HEADERS: {headers}")
        print(f"    DATA_ROWS: {rows}")
        if marker_name is not None:
            print(f"    MARKER_COLUMN: {marker_name}")
            print(f"    MARKED_ROWS: {marked_count}")
        else:
            print("    MARKER_COLUMN: <not found>")
    print()