detect_columns.py

#!/usr/bin/env python3
"""
Column Auto-Detection Script for SDR Status Tracker

Reads header rows from the Google Sheet and generates column_config.json
with detected week configurations and monthly column positions.

Usage:
    python detect_columns.py           # Generate config
    python detect_columns.py --dry-run # Preview without writing file
"""
import os
import re
import json
import argparse
from datetime import datetime
from google.oauth2 import service_account
from googleapiclient.discovery import build


# Configuration
SHEET_ID = os.environ.get("GOOGLE_SHEET_ID", "1af6-2KsRqeTQxdw5KVRp2WCrM6RT7HIcl70m-GgGZB4")
SHEET_NAME = "DAILY - for SDR to add data🌟"
CONFIG_FILE = "column_config.json"


def get_sheets_service():
    """Create Google Sheets API service using service account credentials."""
    creds_json = os.environ.get("GOOGLE_CREDENTIALS")
    if not creds_json:
        raise RuntimeError("GOOGLE_CREDENTIALS environment variable not set")

    creds_dict = json.loads(creds_json)
    credentials = service_account.Credentials.from_service_account_info(
        creds_dict,
        scopes=["https://www.googleapis.com/auth/spreadsheets.readonly"]
    )
    return build("sheets", "v4", credentials=credentials)


def fetch_header_rows(service):
    """Fetch the first 4 rows from the sheet (headers)."""
    result = service.spreadsheets().values().get(
        spreadsheetId=SHEET_ID,
        range=f"'{SHEET_NAME}'!A1:AZ4"  # Wide range to capture all columns
    ).execute()
    return result.get("values", [])


def detect_week_markers(row1):
    """
    Detect WEEK markers from Row 1.
    Returns list of (week_num, start_col) tuples.
    Pattern: "WEEK 2", "WEEK 3", etc.
    """
    week_pattern = re.compile(r"WEEK\s*(\d+)", re.IGNORECASE)
    weeks = []

    for col_idx, cell in enumerate(row1):
        if cell:
            match = week_pattern.search(str(cell))
            if match:
                week_num = int(match.group(1))
                weeks.append((week_num, col_idx))

    return weeks


def detect_daily_columns(row4, week_start_col, next_week_start=None):
    """
    Detect daily columns within a week block.
    Daily columns contain day-of-month numbers (1-31) in Row 4.
    Returns (daily_start, daily_end, target_col, pct_col).
    """
    # Search range: from week_start to next_week_start (or end of row)
    end_col = next_week_start if next_week_start else len(row4)

    daily_start = None
    daily_end = None
    target_col = None
    pct_col = None

    for col_idx in range(week_start_col, end_col):
        if col_idx >= len(row4):
            break

        cell = str(row4[col_idx]).strip().lower() if col_idx < len(row4) else ""

        # Check if it's a day number (1-31)
        if cell.isdigit() and 1 <= int(cell) <= 31:
            if daily_start is None:
                daily_start = col_idx
            daily_end = col_idx

        # Check for TARGET column
        if "target" in cell or cell == "t":
            target_col = col_idx

        # Check for PERCENTAGE column (often contains % or "pct" or just a number)
        if "%" in cell or "pct" in cell.lower():
            pct_col = col_idx

    # If no explicit pct_col found, it's usually right after target
    if target_col is not None and pct_col is None:
        pct_col = target_col + 1

    return daily_start, daily_end, target_col, pct_col


def detect_monthly_columns(row4):
    """
    Detect monthly TARGET and ACTUAL columns.
    These are typically labeled "TARGET" and "ACTUAL" (or similar) near the end.
    """
    target_col = None
    actual_col = None

    for col_idx, cell in enumerate(row4):
        cell_str = str(cell).strip().lower() if cell else ""

        # Look for monthly target (usually labeled differently from weekly)
        if "monthly" in cell_str or (col_idx > 25 and "target" in cell_str):
            if target_col is None:
                target_col = col_idx

        # Look for monthly actual
        if "actual" in cell_str or (col_idx > 25 and col_idx == target_col + 1):
            actual_col = col_idx

    # Default fallback based on current known structure
    if target_col is None:
        target_col = 32  # Column AG
    if actual_col is None:
        actual_col = 33  # Column AH

    return target_col, actual_col


def detect_columns():
    """
    Main detection function.
    Returns the detected configuration as a dictionary.
    """
    print("Connecting to Google Sheets API...")
    service = get_sheets_service()

    print(f"Fetching headers from sheet: {SHEET_NAME}")
    headers = fetch_header_rows(service)

    if len(headers) < 4:
        raise RuntimeError(f"Expected at least 4 header rows, got {len(headers)}")

    row1 = headers[0] if len(headers) > 0 else []
    row4 = headers[3] if len(headers) > 3 else []

    print(f"Row 1 has {len(row1)} columns")
    print(f"Row 4 has {len(row4)} columns")

    # Detect week markers
    week_markers = detect_week_markers(row1)
    print(f"Detected {len(week_markers)} week markers: {week_markers}")

    # Detect columns for each week
    weeks_config = []
    for i, (week_num, start_col) in enumerate(week_markers):
        # Get next week's start column (or None for last week)
        next_start = week_markers[i + 1][1] if i + 1 < len(week_markers) else None

        daily_start, daily_end, target_col, pct_col = detect_daily_columns(
            row4, start_col, next_start
        )

        week_config = {
            "week_num": week_num,
            "daily_start": daily_start,
            "daily_end": daily_end,
            "target_col": target_col,
            "pct_col": pct_col
        }
        weeks_config.append(week_config)

        print(f"  Week {week_num}: daily={daily_start}-{daily_end}, target={target_col}, pct={pct_col}")

    # Detect monthly columns
    monthly_target, monthly_actual = detect_monthly_columns(row4)
    print(f"Monthly columns: target={monthly_target}, actual={monthly_actual}")

    config = {
        "generated_at": datetime.utcnow().isoformat() + "Z",
        "sheet_id": SHEET_ID,
        "sheet_name": SHEET_NAME,
        "weeks": weeks_config,
        "monthly": {
            "target_col": monthly_target,
            "actual_col": monthly_actual
        }
    }

    return config


def main():
    parser = argparse.ArgumentParser(description="Detect column mappings from Google Sheet")
    parser.add_argument("--dry-run", action="store_true", help="Preview config without writing file")
    args = parser.parse_args()

    try:
        config = detect_columns()

        print("\n" + "=" * 50)
        print("Detected Configuration:")
        print("=" * 50)
        print(json.dumps(config, indent=2))

        if args.dry_run:
            print("\n[DRY RUN] Config not written to file")
        else:
            # Write config file
            with open(CONFIG_FILE, "w") as f:
                json.dump(config, f, indent=2)
            print(f"\nConfig written to {CONFIG_FILE}")

        return 0

    except Exception as e:
        print(f"Error: {e}")
        return 1


if __name__ == "__main__":
    exit(main())