tracker.py

# tracker.py
# Main CLI tool for the Data Plan Tracker.
#
# Usage:
#   python tracker.py add          - Add a new plan interactively
#   python tracker.py list         - Show all active plans in a table
#   python tracker.py renew <id>   - Update renewal date for a plan
#   python tracker.py edit <id>    - Edit any field of a plan
#   python tracker.py deactivate <id> - Mark a plan as inactive
#   python tracker.py summary      - Spending summary + upcoming renewals
#   python tracker.py check        - Daily check (used by Task Scheduler)
#   python tracker.py seed         - Add sample data to see how it looks

import sys
import os
from datetime import datetime, date

# --- Third-party library imports with friendly error messages ---
try:
    from colorama import Fore, Style, init as colorama_init
    colorama_init(autoreset=True)  # Reset colour after each print automatically
except ImportError:
    print("[!] colorama not installed. Run: pip install colorama")
    sys.exit(1)

try:
    from tabulate import tabulate
except ImportError:
    print("[!] tabulate not installed. Run: pip install tabulate")
    sys.exit(1)

# Our own modules
import database
import alerts

# --- Constants ---
VALID_TYPES = ["sim", "proxy", "vpn", "phone", "other"]
VALID_CYCLES = ["monthly", "28-day", "annual", "one-off"]
VALID_EXPENSE_CATEGORIES = ["software", "hosting", "tools", "entertainment", "utilities", "services", "other"]

# Colour thresholds for "days left" column
RED_DAYS = 7
YELLOW_DAYS = 14

# Monthly cost multiplier by billing cycle
MONTHLY_EQUIV = {
    "monthly": 1.0,
    "28-day": 1.0,
    "annual": 1.0 / 12.0,
    "one-off": 0.0,
}

# Phone number validation regex (Australian mobile format)
import re
PHONE_REGEX = re.compile(r'^(\+?61|0)4[0-9]{8}$')

# Global state for CLI flags (--quiet, --format, etc.)
GLOBAL_STATE = {
    "quiet": False,
    "silent": False,
    "format": "table",
}


def is_quiet() -> bool:
    """Check if quiet mode is enabled."""
    return GLOBAL_STATE.get("quiet", False)


def is_silent() -> bool:
    """Check if silent mode is enabled."""
    return GLOBAL_STATE.get("silent", False)


def output_format() -> str:
    """Get current output format."""
    return GLOBAL_STATE.get("format", "table")


def qprint(*args, **kwargs):
    """Print only if not in quiet/silent mode."""
    if not is_quiet():
        print(*args, **kwargs)


def eprint(*args, **kwargs):
    """Print to stderr (always, even in quiet mode)."""
    print(*args, file=sys.stderr, **kwargs)


def validate_phone_number(phone: str) -> tuple:
    """
    Validate and normalize an Australian mobile phone number.
    Returns (is_valid: bool, normalized: str, error_msg: str)
    """
    if not phone or not phone.strip():
        return True, "", ""  # Empty is valid (optional field)
    
    # Remove all whitespace and common separators
    cleaned = re.sub(r'[\s\-\.\(\)]', '', phone.strip())
    
    # Check for valid Australian mobile format
    # Formats accepted: 0412345678, 61412345678, +61412345678
    if not PHONE_REGEX.match(cleaned):
        return False, phone, "Invalid Australian mobile format. Use 04xx xxx xxx or +614xx xxx xxx"
    
    # Normalize to local format (04xx xxx xxx)
    if cleaned.startswith('+61'):
        normalized = '0' + cleaned[3:]
    elif cleaned.startswith('61'):
        normalized = '0' + cleaned[2:]
    else:
        normalized = cleaned
    
    # Format with spaces: 04xx xxx xxx
    formatted = f"{normalized[:4]} {normalized[4:7]} {normalized[7:]}"
    
    return True, formatted, ""


# ---------------------------------------------------------------------------
# Helpers
# ---------------------------------------------------------------------------

def colour_days(days: int) -> str:
    """Return the days-left number wrapped in the appropriate colour code."""
    text = str(days)
    if days <= RED_DAYS:
        return Fore.RED + text + Style.RESET_ALL
    elif days <= YELLOW_DAYS:
        return Fore.YELLOW + text + Style.RESET_ALL
    else:
        return Fore.GREEN + text + Style.RESET_ALL


def days_until(date_str: str) -> int:
    """Return number of days from today until date_str (YYYY-MM-DD). Negative = overdue."""
    try:
        renewal = datetime.strptime(date_str, "%Y-%m-%d").date()
        return (renewal - date.today()).days
    except Exception:
        return 9999  # No date set - treat as far away


def format_date_display(date_str: str) -> str:
    """Convert YYYY-MM-DD to DD/MM/YYYY for display. Returns original if invalid."""
    try:
        return datetime.strptime(date_str, "%Y-%m-%d").strftime("%d/%m/%Y")
    except Exception:
        return date_str or "-"


def parse_date_input(raw: str) -> str:
    """
    Accept dates in DD/MM/YYYY or YYYY-MM-DD format.
    Returns YYYY-MM-DD (ISO format) for storage, or raises ValueError.
    """
    raw = raw.strip()
    for fmt in ("%d/%m/%Y", "%Y-%m-%d"):
        try:
            return datetime.strptime(raw, fmt).strftime("%Y-%m-%d")
        except ValueError:
            continue
    raise ValueError(f"Date '{raw}' not recognised. Use DD/MM/YYYY or YYYY-MM-DD.")


def prompt(label: str, default: str = "", required: bool = False) -> str:
    """
    Show a prompt and return the user's input.
    If a default is provided it is shown in brackets and used when user presses Enter.
    """
    hint = f" [{default}]" if default else ""
    while True:
        value = input(f"  {label}{hint}: ").strip()
        if not value and default:
            return default
        if value:
            return value
        if required:
            print("    [!] This field is required.")
        else:
            return ""


def prompt_choice(label: str, choices: list, default: str = "") -> str:
    """Prompt user to pick from a list of valid choices."""
    choices_str = "/".join(choices)
    hint = f" [{default}]" if default else f" ({choices_str})"
    while True:
        value = input(f"  {label}{hint}: ").strip().lower()
        if not value and default:
            return default
        if value in choices:
            return value
        print(f"    [!] Must be one of: {choices_str}")


def prompt_yn(label: str, default: bool = False) -> bool:
    """Prompt for a yes/no answer. Returns True for yes."""
    hint = "[Y/n]" if default else "[y/N]"
    value = input(f"  {label} {hint}: ").strip().lower()
    if not value:
        return default
    return value in ("y", "yes")


def prompt_float(label: str, default: float = 0.0) -> float:
    """Prompt for a decimal number (cost). Returns default on blank/invalid."""
    hint = f" [{default:.2f}]" if default else ""
    while True:
        raw = input(f"  {label}{hint}: ").strip()
        if not raw:
            return default
        try:
            return float(raw)
        except ValueError:
            print("    [!] Enter a number, e.g. 29.90")


# ---------------------------------------------------------------------------
# Commands
# ---------------------------------------------------------------------------

def cmd_add():
    """Interactively collect details for a new plan and save it to the database."""
    print("\n--- Add New Plan ---")
    print("Press Enter to skip optional fields.\n")

    data = {}
    data["name"] = prompt("Plan name (e.g. Boost SIM #3)", required=True)
    data["type"] = prompt_choice("Type", VALID_TYPES)
    data["provider"] = prompt("Provider (e.g. Boost, Oxylabs)")
    data["assigned_vm"] = prompt("Assigned VM (e.g. VM-01)")
    data["cost"] = prompt_float("Cost per cycle (AUD)", default=0.0)
    data["billing_cycle"] = prompt_choice("Billing cycle", VALID_CYCLES, default="monthly")

    # Calculate smart default based on billing cycle
    from datetime import datetime, timedelta
    cycle_days = {
        "monthly": 30,
        "28-day": 28,
        "annual": 365,
        "one-off": 0
    }
    default_days = cycle_days.get(data["billing_cycle"], 30)
    default_date = ""
    if default_days > 0:
        default_date_obj = datetime.now() + timedelta(days=default_days)
        default_date = default_date_obj.strftime("%d/%m/%Y")
    
    raw_date = prompt(f"Next renewal date (DD/MM/YYYY) [{default_date}]")
    if raw_date:
        try:
            data["next_renewal"] = parse_date_input(raw_date)
        except ValueError as e:
            print(f"    [!] {e} - renewal date not saved. Edit later with: python tracker.py edit <id>")
            data["next_renewal"] = ""
    elif default_date:
        # User pressed Enter - use the default
        data["next_renewal"] = parse_date_input(default_date)
    else:
        data["next_renewal"] = ""

    # Suggest auto-renew based on billing cycle
    default_auto = data["billing_cycle"] != "one-off"
    data["auto_renew"] = 1 if prompt_yn("Auto-renews?", default=default_auto) else 0
    data["notes"] = prompt("Notes (optional)")

    # Validate before saving
    is_valid, validation_issues = validate_plan_consistency(data, is_new=True)
    if validation_issues:
        print("\n[*] Validation notes:")
        for issue in validation_issues:
            print(f"    - {issue}")
    if not is_valid:
        print("\n[!] Please fix the above issues before saving.")
        return

    plan_id = database.add_plan(data)
    print(f"\n[*] Plan saved with ID {plan_id}: {data['name']}")


def cmd_list(show_all: bool = False):
    """Display plans in a colour-coded table, grouped by type."""
    plans = database.get_all_plans(active_only=not show_all)

    if not plans:
        if show_all:
            print("\nNo plans in the database. Add one with: python tracker.py add")
        else:
            print("\nNo active plans found. Add one with: python tracker.py add")
        return

    if show_all:
        active_count = sum(1 for p in plans if p.get("active"))
        inactive_count = len(plans) - active_count
        print(f"\n  Showing all plans ({active_count} active, {inactive_count} inactive)")

    # Group plans by type for cleaner display
    by_type = {}
    for plan in plans:
        t = plan.get("type", "other")
        by_type.setdefault(t, []).append(plan)

    for plan_type, group in sorted(by_type.items()):
        print(f"\n  --- {plan_type.upper()} ---")
        rows = []
        for p in group:
            renewal = p.get("next_renewal", "")
            is_active = bool(p.get("active", 1))

            if renewal and is_active:
                days = days_until(renewal)
                days_str = colour_days(days)
                renewal_display = format_date_display(renewal)
            elif renewal:
                days_str = "-"
                renewal_display = format_date_display(renewal)
            else:
                days_str = "-"
                renewal_display = "-"

            auto = "Yes" if p.get("auto_renew") else "No"
            cost = f"${p.get('cost', 0.0):.2f}"
            status = "active" if is_active else Fore.RED + "inactive" + Style.RESET_ALL

            row = [
                p["id"],
                p.get("name", ""),
                p.get("provider", "") or "-",
                p.get("assigned_vm", "") or "-",
                cost,
                p.get("billing_cycle", "") or "-",
                renewal_display,
                days_str,
                auto,
            ]
            if show_all:
                row.append(status)
            rows.append(row)

        headers = ["ID", "Name", "Provider", "VM", "Cost", "Cycle",
                   "Next Renewal", "Days Left", "Auto-renew"]
        if show_all:
            headers.append("Status")
        print(tabulate(rows, headers=headers, tablefmt="simple"))

    label = "plans (all)" if show_all else "active plans"
    print(f"\n  Total {label}: {len(plans)}")


def calculate_next_renewal(current_date: str, billing_cycle: str) -> str:
    """Calculate the next renewal date based on billing cycle."""
    from datetime import datetime, timedelta
    
    try:
        current = datetime.strptime(current_date, "%Y-%m-%d")
    except (ValueError, TypeError):
        return None
    
    if billing_cycle == "one-off":
        return None
    
    if billing_cycle == "monthly":
        try:
            from dateutil.relativedelta import relativedelta
            next_date = current + relativedelta(months=+1)
        except ImportError:
            next_date = current + timedelta(days=30)
    elif billing_cycle == "28-day":
        next_date = current + timedelta(days=28)
    elif billing_cycle == "annual":
        try:
            from dateutil.relativedelta import relativedelta
            next_date = current + relativedelta(months=+12)
        except ImportError:
            next_date = current + timedelta(days=365)
    else:
        # Default to monthly for unknown cycles
        try:
            from dateutil.relativedelta import relativedelta
            next_date = current + relativedelta(months=+1)
        except ImportError:
            next_date = current + timedelta(days=30)
    
    return next_date.strftime("%Y-%m-%d")


def cmd_renew(plan_id: int, auto_advance: bool = False):
    """Update the next_renewal date for a single plan."""
    plan = database.get_plan_by_id(plan_id)
    if not plan:
        print(f"[!] No plan found with ID {plan_id}")
        # Suggest similar IDs
        all_plans = database.get_all_plans(active_only=False)
        existing_ids = [p['id'] for p in all_plans]
        if existing_ids:
            closest = min(existing_ids, key=lambda x: abs(x - plan_id))
            print(f"    Did you mean ID {closest}? Run 'python tracker.py list' to see all plans")
        return

    current_renewal = plan.get("next_renewal", "")
    billing_cycle = plan.get("billing_cycle", "monthly")
    
    print(f"\n  Plan: {plan['name']} (current renewal: {format_date_display(current_renewal)})")
    
    # Offer auto-calculation based on billing cycle
    if current_renewal and billing_cycle:
        calculated = calculate_next_renewal(current_renewal, billing_cycle)
        if calculated and not auto_advance:
            use_auto = prompt_yn(f"Auto-calculate next renewal ({format_date_display(calculated)})?", default=True)
            if use_auto:
                database.update_plan(plan_id, {"next_renewal": calculated})
                # Log the renewal for history tracking
                _log_plan_change(plan_id, "renewal", f"Renewed from {current_renewal} to {calculated}")
                print(f"[*] Renewal date auto-updated to {format_date_display(calculated)}")
                return
    
    # Manual date entry
    raw_date = prompt("New renewal date (DD/MM/YYYY)", required=True)
    try:
        new_date = parse_date_input(raw_date)
    except ValueError as e:
        print(f"[!] {e}")
        return

    database.update_plan(plan_id, {"next_renewal": new_date})
    _log_plan_change(plan_id, "renewal", f"Manually updated to {new_date}")
    print(f"[*] Renewal date updated to {format_date_display(new_date)}")


def cmd_edit(plan_id: int):
    """Edit any field of a plan interactively."""
    plan = database.get_plan_by_id(plan_id)
    if not plan:
        print(f"[!] No plan found with ID {plan_id}")
        # Suggest similar IDs
        all_plans = database.get_all_plans(active_only=False)
        existing_ids = [p['id'] for p in all_plans]
        if existing_ids:
            closest = min(existing_ids, key=lambda x: abs(x - plan_id))
            print(f"    Did you mean ID {closest}? Run 'python tracker.py list' to see all plans")
        return

    print(f"\n--- Edit Plan: {plan['name']} ---")
    print("Press Enter to keep current value.\n")

    updates = {}
    history_logs = []  # Track changes for history

    # Go through each editable field and offer to update it
    new_name = prompt("Plan name", default=plan.get("name", ""))
    if new_name != plan.get("name", ""):
        updates["name"] = new_name
        history_logs.append(("name", plan.get("name", ""), new_name))

    new_type = prompt_choice("Type", VALID_TYPES, default=plan.get("type", "other"))
    if new_type != plan.get("type"):
        updates["type"] = new_type
        history_logs.append(("type", plan.get("type", ""), new_type))

    new_provider = prompt("Provider", default=plan.get("provider", "") or "")
    if new_provider != (plan.get("provider") or ""):
        updates["provider"] = new_provider
        history_logs.append(("provider", plan.get("provider", ""), new_provider))

    new_vm = prompt("Assigned VM", default=plan.get("assigned_vm", "") or "")
    if new_vm != (plan.get("assigned_vm") or ""):
        updates["assigned_vm"] = new_vm
        history_logs.append(("assigned_vm", plan.get("assigned_vm", ""), new_vm))

    current_cost = plan.get("cost", 0.0) or 0.0
    new_cost = prompt_float("Cost (AUD)", default=current_cost)
    if new_cost != current_cost:
        updates["cost"] = new_cost
        history_logs.append(("cost", str(current_cost), str(new_cost)))
        # Special logging for cost changes
        change_pct = ((new_cost - current_cost) / current_cost * 100) if current_cost else 0
        change_dir = "increased" if new_cost > current_cost else "decreased" if new_cost < current_cost else "changed"
        print(f"    [!] Cost {change_dir} by {abs(change_pct):.1f}% (${current_cost:.2f} → ${new_cost:.2f})")

    new_cycle = prompt_choice("Billing cycle", VALID_CYCLES, default=plan.get("billing_cycle", "monthly"))
    if new_cycle != plan.get("billing_cycle"):
        updates["billing_cycle"] = new_cycle
        history_logs.append(("billing_cycle", plan.get("billing_cycle", ""), new_cycle))

    current_renewal = format_date_display(plan.get("next_renewal", ""))
    raw_date = prompt("Next renewal date (DD/MM/YYYY)", default=current_renewal)
    if raw_date and raw_date != current_renewal:
        try:
            new_renewal = parse_date_input(raw_date)
            updates["next_renewal"] = new_renewal
            history_logs.append(("next_renewal", plan.get("next_renewal", ""), new_renewal))
        except ValueError as e:
            print(f"    [!] {e} - renewal date not changed")

    current_auto = bool(plan.get("auto_renew", 0))
    new_auto = prompt_yn("Auto-renews?", default=current_auto)
    if new_auto != current_auto:
        updates["auto_renew"] = 1 if new_auto else 0
        history_logs.append(("auto_renew", str(current_auto), str(new_auto)))

    new_notes = prompt("Notes", default=plan.get("notes", "") or "")
    if new_notes != (plan.get("notes") or ""):
        updates["notes"] = new_notes
        history_logs.append(("notes", "updated", "updated"))  # Don't log full notes content

    if updates:
        database.update_plan(plan_id, updates)
        # Log changes to history
        for field, old_val, new_val in history_logs:
            database.log_plan_change(plan_id, field, str(old_val), str(new_val))
        # Log to audit log
        _log_plan_change(plan_id, "edit", f"Updated {len(updates)} field(s): {', '.join(updates.keys())}")
        print(f"\n[*] Plan {plan_id} updated ({len(updates)} field(s) changed)")
    else:
        print("\n[*] No changes made")


def cmd_provider_report():
    """Show spending breakdown by provider."""
    plans = database.get_all_plans(active_only=True)
    
    if not plans:
        print("\nNo active plans found.")
        return
    
    # Group by provider
    by_provider = {}
    for p in plans:
        prov = (p.get("provider") or "Unknown").strip()
        if not prov:
            prov = "Unknown"
        
        cost = p.get("cost", 0.0) or 0.0
        cycle = p.get("billing_cycle", "monthly") or "monthly"
        monthly_cost = cost * MONTHLY_EQUIV.get(cycle, 1.0)
        
        by_provider.setdefault(prov, {"count": 0, "monthly": 0.0, "annual": 0.0, "plans": []})
        by_provider[prov]["count"] += 1
        by_provider[prov]["monthly"] += monthly_cost
        by_provider[prov]["annual"] += monthly_cost * 12
        by_provider[prov]["plans"].append(p)
    
    print("\n=== Provider Spend Report ===\n")
    
    # Sort by monthly spend descending
    sorted_providers = sorted(by_provider.items(), key=lambda x: x[1]["monthly"], reverse=True)
    
    rows = []
    total_monthly = 0.0
    for prov, stats in sorted_providers:
        rows.append([
            prov,
            stats["count"],
            f"${stats['monthly']:.2f}",
            f"${stats['annual']:.2f}",
        ])
        total_monthly += stats["monthly"]
    
    headers = ["Provider", "Plans", "Monthly", "Annual"]
    print(tabulate(rows, headers=headers, tablefmt="simple"))
    
    print(f"\n  Total monthly spend: AUD ${total_monthly:.2f}")
    print(f"  Total annual spend:  AUD ${total_monthly * 12:.2f}")
    print(f"  Providers tracked:   {len(by_provider)}")
    
    # Show top provider's plans
    if sorted_providers:
        top_provider = sorted_providers[0]
        print(f"\n  Top provider: {top_provider[0]} ({top_provider[1]['count']} plans)")


def cmd_budget_report():
    """Show budget variance report comparing actual spend vs budget."""
    from datetime import datetime
    
    # Load budget config
    config = alerts.load_config()
    monthly_budget = config.get("monthly_budget")
    annual_budget = config.get("annual_budget")
    
    plans = database.get_all_plans(active_only=True)
    expenses = database.get_all_expenses(active_only=True)
    
    # Calculate actual monthly spend
    actual_monthly = 0.0
    for p in plans:
        cost = p.get("cost", 0.0) or 0.0
        cycle = p.get("billing_cycle", "monthly") or "monthly"
        actual_monthly += cost * MONTHLY_EQUIV.get(cycle, 1.0)
    
    for e in expenses:
        cost = e.get("cost", 0.0) or 0.0
        cycle = e.get("billing_cycle", "monthly") or "monthly"
        actual_monthly += cost * MONTHLY_EQUIV.get(cycle, 1.0)
    
    actual_annual = actual_monthly * 12
    
    print("\n=== Budget Variance Report ===\n")
    
    # Monthly budget analysis
    if monthly_budget and monthly_budget > 0:
        variance = actual_monthly - monthly_budget
        variance_pct = (variance / monthly_budget) * 100
        remaining = monthly_budget - actual_monthly
        
        print(f"  Monthly Budget:    ${monthly_budget:,.2f}")
        print(f"  Actual Monthly:    ${actual_monthly:,.2f}")
        print(f"  Variance:          ${variance:+,.2f} ({variance_pct:+.1f}%)")
        print(f"  Remaining:         ${remaining:,.2f}")
        
        if variance_pct > 10:
            print(f"  [!] OVER BUDGET by {variance_pct:.1f}%")
        elif variance_pct > 0:
            print(f"  [*] Near budget limit (+{variance_pct:.1f}%)")
        else:
            print(f"  [OK] Under budget ({abs(variance_pct):.1f}% remaining)")
        print()
    
    # Annual budget analysis
    if annual_budget and annual_budget > 0:
        variance = actual_annual - annual_budget
        variance_pct = (variance / annual_budget) * 100
        remaining = annual_budget - actual_annual
        
        print(f"  Annual Budget:     ${annual_budget:,.2f}")
        print(f"  Projected Annual:  ${actual_annual:,.2f}")
        print(f"  Variance:          ${variance:+,.2f} ({variance_pct:+.1f}%)")
        print(f"  Remaining:         ${remaining:,.2f}")
        
        if variance_pct > 10:
            print(f"  [!] OVER ANNUAL BUDGET by {variance_pct:.1f}%")
        elif variance_pct > 0:
            print(f"  [*] Near annual budget limit (+{variance_pct:.1f}%)")
        else:
            print(f"  [OK] Under annual budget ({abs(variance_pct):.1f}% remaining)")
        print()
    
    # Spend breakdown by category
    print("  Spend by Category:")
    by_type = {}
    for p in plans:
        t = p.get("type", "other")
        cost = p.get("cost", 0.0) or 0.0
        cycle = p.get("billing_cycle", "monthly") or "monthly"
        monthly = cost * MONTHLY_EQUIV.get(cycle, 1.0)
        by_type[t] = by_type.get(t, 0.0) + monthly
    
    for e in expenses:
        cat = e.get("category") or "other"
        cost = e.get("cost", 0.0) or 0.0
        cycle = e.get("billing_cycle", "monthly") or "monthly"
        monthly = cost * MONTHLY_EQUIV.get(cycle, 1.0)
        by_type[cat] = by_type.get(cat, 0.0) + monthly
    
    total = sum(by_type.values())
    for cat, amount in sorted(by_type.items(), key=lambda x: x[1], reverse=True):
        pct = (amount / total * 100) if total else 0
        print(f"    {cat:12} ${amount:>8.2f} ({pct:>5.1f}%)")
    
    print(f"\n  Total Monthly:     ${actual_monthly:,.2f}")
    print()
    
    if not monthly_budget and not annual_budget:
        print("  [!] No budget configured. Set monthly_budget or annual_budget in config.json")
        print()


def cmd_date_range_report(from_date: str = None, to_date: str = None):
    """Show plans with renewals within a date range."""
    from datetime import datetime, timedelta
    
    # Parse dates
    if not from_date:
        # Default to today
        from_dt = datetime.now()
    else:
        try:
            from_dt = datetime.strptime(from_date, "%Y-%m-%d")
        except ValueError:
            try:
                from_dt = datetime.strptime(from_date, "%d/%m/%Y")
            except ValueError:
                print(f"[!] Invalid from_date format. Use YYYY-MM-DD or DD/MM/YYYY")
                return
    
    if not to_date:
        # Default to 30 days from from_date
        to_dt = from_dt + timedelta(days=30)
    else:
        try:
            to_dt = datetime.strptime(to_date, "%Y-%m-%d")
        except ValueError:
            try:
                to_dt = datetime.strptime(to_date, "%d/%m/%Y")
            except ValueError:
                print(f"[!] Invalid to_date format. Use YYYY-MM-DD or DD/MM/YYYY")
                return
    
    plans = database.get_all_plans(active_only=True)
    
    # Filter plans with renewals in range
    matching = []
    for p in plans:
        renewal = p.get("next_renewal")
        if renewal:
            try:
                renewal_dt = datetime.strptime(renewal, "%Y-%m-%d")
                if from_dt <= renewal_dt <= to_dt:
                    matching.append(p)
            except ValueError:
                pass
    
    # Sort by renewal date
    matching.sort(key=lambda p: p.get("next_renewal", ""))
    
    print(f"\n=== Renewals from {from_dt.strftime('%d/%m/%Y')} to {to_dt.strftime('%d/%m/%Y')} ===\n")
    
    if not matching:
        print("  No renewals found in this date range.")
        return
    
    total_cost = sum(p.get("cost", 0) or 0 for p in matching)
    
    rows = []
    for p in matching:
        days = days_until(p.get("next_renewal", ""))
        rows.append([
            p['id'],
            p.get('name', '')[:25],
            p.get('provider', '-')[:15],
            format_date_display(p.get('next_renewal', '')),
            colour_days(days) if days < 9999 else '-',
            f"${p.get('cost', 0):.2f}",
        ])
    
    headers = ["ID", "Name", "Provider", "Renewal", "Days", "Cost"]
    print(tabulate(rows, headers=headers, tablefmt="simple"))
    
    print(f"\n  Total: {len(matching)} renewal(s), ${total_cost:.2f}")
    
    # Group by month
    by_month = {}
    for p in matching:
        renewal = p.get("next_renewal", "")
        if renewal:
            month_key = renewal[:7]  # YYYY-MM
            month_label = datetime.strptime(month_key, "%Y-%m").strftime("%B %Y")
            by_month.setdefault(month_label, []).append(p)
    
    if len(by_month) > 1:
        print("\n  By Month:")
        for month, plans in sorted(by_month.items()):
            month_cost = sum(p.get("cost", 0) or 0 for p in plans)
            print(f"    {month}: {len(plans)} plan(s), ${month_cost:.2f}")
    print()


def cmd_forecast(months: int = 6):
    """Forecast cash flow for upcoming months based on renewal dates."""
    from datetime import datetime, timedelta
    from collections import defaultdict
    
    plans = database.get_all_plans(active_only=True)
    
    # Group renewals by month
    monthly_renewals = defaultdict(list)
    
    for p in plans:
        renewal = p.get("next_renewal")
        if renewal:
            try:
                renewal_dt = datetime.strptime(renewal, "%Y-%m-%d")
                month_key = renewal_dt.strftime("%Y-%m")
                monthly_renewals[month_key].append(p)
            except ValueError:
                pass
    
    print(f"\n=== Cash Flow Forecast (Next {months} Months) ===\n")
    
    today = datetime.now()
    forecast_data = []
    
    for i in range(months):
        month_dt = today + timedelta(days=30*i)
        month_key = month_dt.strftime("%Y-%m")
        month_label = month_dt.strftime("%B %Y")
        
        renewals = monthly_renewals.get(month_key, [])
        total_cost = sum(p.get("cost", 0) or 0 for p in renewals)
        
        forecast_data.append({
            "month": month_label,
            "count": len(renewals),
            "cost": total_cost,
        })
    
    # Display forecast
    max_cost = max(f["cost"] for f in forecast_data) if forecast_data else 1
    
    for f in forecast_data:
        bar_length = int((f["cost"] / max_cost) * 20) if max_cost > 0 else 0
        bar = "#" * bar_length
        warning = " [!]" if f["cost"] > 200 else ""
        print(f"  {f['month']:12} {bar:<20} ${f['cost']:>7.2f} ({f['count']} plans){warning}")
    
    # Identify expensive months
    expensive = [f for f in forecast_data if f["cost"] > 200]
    if expensive:
        print(f"\n  [!] Expensive months (> $200): {len(expensive)}")
        for e in expensive:
            print(f"      {e['month']}: ${e['cost']:.2f}")
    
    total_forecast = sum(f["cost"] for f in forecast_data)
    print(f"\n  Total forecasted spend: ${total_forecast:.2f}")
    print(f"  Average per month: ${total_forecast/months:.2f}" if months > 0 else "")
    print()


def cmd_stats():
    """Show detailed statistics about plans and expenses."""
    plans = database.get_all_plans(active_only=False)
    active_plans = [p for p in plans if p.get("active", 1)]
    inactive_plans = [p for p in plans if not p.get("active", 1)]
    
    expenses = database.get_all_expenses(active_only=False)
    active_expenses = [e for e in expenses if e.get("active", 1)]
    
    print("\n=== Data Plan Tracker Statistics ===\n")
    
    # Overview
    print("  OVERVIEW")
    print(f"    Total plans (all time):     {len(plans)}")
    print(f"    Active plans:               {len(active_plans)}")
    print(f"    Inactive plans:             {len(inactive_plans)}")
    print(f"    Active expenses:            {len(active_expenses)}")
    print()
    
    # Plan types breakdown
    if active_plans:
        print("  PLANS BY TYPE")
        by_type = {}
        for p in active_plans:
            t = p.get("type", "other")
            by_type[t] = by_type.get(t, 0) + 1
        for t, count in sorted(by_type.items()):
            print(f"    {t:12} {count} plan(s)")
        print()
    
    # Financials
    if active_plans:
        print("  FINANCIALS (active plans)")
        monthly = 0.0
        for p in active_plans:
            cost = p.get("cost", 0.0) or 0.0
            cycle = p.get("billing_cycle", "monthly") or "monthly"
            monthly += cost * MONTHLY_EQUIV.get(cycle, 1.0)
        
        for e in active_expenses:
            cost = e.get("cost", 0.0) or 0.0
            cycle = e.get("billing_cycle", "monthly") or "monthly"
            monthly += cost * MONTHLY_EQUIV.get(cycle, 1.0)
        
        print(f"    Monthly spend:  AUD ${monthly:.2f}")
        print(f"    Annual spend:   AUD ${monthly * 12:.2f}")
        print()
    
    # Renewals analysis
    if active_plans:
        print("  UPCOMING RENEWALS")
        expiring_7 = sum(1 for p in active_plans if 0 <= days_until(p.get("next_renewal", "")) <= 7)
        expiring_14 = sum(1 for p in active_plans if 0 <= days_until(p.get("next_renewal", "")) <= 14)
        expiring_30 = sum(1 for p in active_plans if 0 <= days_until(p.get("next_renewal", "")) <= 30)
        overdue = sum(1 for p in active_plans if days_until(p.get("next_renewal", "")) < 0)
        no_date = sum(1 for p in active_plans if not p.get("next_renewal"))
        
        print(f"    Next 7 days:    {expiring_7}")
        print(f"    Next 14 days:   {expiring_14}")
        print(f"    Next 30 days:   {expiring_30}")
        if overdue:
            print(f"    OVERDUE:        {overdue} ⚠️")
        if no_date:
            print(f"    No date set:    {no_date}")
        print()
    
    # Phone numbers
    with_numbers = [p for p in active_plans if p.get("phone_number")]
    with_num_expiry = [p for p in active_plans if p.get("number_expiry")]
    
    if with_numbers:
        print("  PHONE NUMBERS")
        print(f"    Plans with numbers:     {len(with_numbers)}")
        print(f"    With expiry tracking:   {len(with_num_expiry)}")
        
        nums_expiring = sum(1 for p in with_num_expiry 
                          if 0 <= days_until(p.get("number_expiry", "")) <= 30)
        nums_overdue = sum(1 for p in with_num_expiry 
                         if days_until(p.get("number_expiry", "")) < 0)
        
        if nums_expiring:
            print(f"    Expiring soon:        {nums_expiring} 📞")
        if nums_overdue:
            print(f"    OVERDUE:              {nums_overdue} ⚠️")
        print()
    
    # Auto-renew analysis
    auto_renew_count = sum(1 for p in active_plans if p.get("auto_renew"))
    manual_count = len(active_plans) - auto_renew_count
    
    if active_plans:
        print("  AUTO-RENEW STATUS")
        print(f"    Auto-renew:     {auto_renew_count}")
        print(f"    Manual renew:   {manual_count}")
        print()
    
    # VM assignments
    vms_used = set(p.get("assigned_vm", "").strip() for p in active_plans if p.get("assigned_vm"))
    unassigned = sum(1 for p in active_plans if not p.get("assigned_vm"))
    
    print("  VM ASSIGNMENTS")
    print(f"    Unique VMs:     {len(vms_used)}")
    print(f"    Unassigned:     {unassigned} plan(s)")
    print()


def cmd_duplicates():
    """Detect and report potential duplicate plans."""
    plans = database.get_all_plans(active_only=False)
    
    print("\n=== Duplicate Detection ===\n")
    
    duplicates_found = []
    
    # Check 1: Exact phone number duplicates
    phone_map = {}
    for p in plans:
        phone = (p.get("phone_number") or "").strip()
        if phone:
            if phone in phone_map:
                duplicates_found.append({
                    "type": "Same phone number",
                    "plans": [phone_map[phone], p],
                    "detail": phone
                })
            else:
                phone_map[phone] = p
    
    # Check 2: Same name + provider (case insensitive)
    name_provider_map = {}
    for p in plans:
        name = (p.get("name") or "").lower().strip()
        provider = (p.get("provider") or "").lower().strip()
        key = f"{name}|{provider}"
        if name and provider:
            if key in name_provider_map:
                duplicates_found.append({
                    "type": "Same name + provider",
                    "plans": [name_provider_map[key], p],
                    "detail": f"{p.get('name')} ({p.get('provider')})"
                })
            else:
                name_provider_map[key] = p
    
    # Check 3: Same provider + VM + cost (likely duplicate entry)
    provider_vm_cost_map = {}
    for p in plans:
        provider = (p.get("provider") or "").lower().strip()
        vm = (p.get("assigned_vm") or "").lower().strip()
        cost = p.get("cost", 0)
        key = f"{provider}|{vm}|{cost}"
        if provider and vm:
            if key in provider_vm_cost_map:
                # Only flag if names are also similar
                other = provider_vm_cost_map[key]
                other_name = (other.get("name") or "").lower()
                this_name = (p.get("name") or "").lower()
                # Simple similarity: first 5 chars match
                if other_name[:5] == this_name[:5]:
                    duplicates_found.append({
                        "type": "Similar plan (same provider/VM/cost)",
                        "plans": [other, p],
                        "detail": f"${cost} at {p.get('provider')} on {p.get('assigned_vm')}"
                    })
            else:
                provider_vm_cost_map[key] = p
    
    # Report results
    if duplicates_found:
        print(f"[!] Found {len(duplicates_found)} potential duplicate(s):\n")
        for dup in duplicates_found:
            print(f"  {dup['type']}: {dup['detail']}")
            for p in dup['plans']:
                status = " (inactive)" if not p.get("active", 1) else ""
                print(f"    - [{p['id']}] {p.get('name')}{status}")
            print()
        print("  Tip: Review these plans and consider merging or deactivating duplicates.")
    else:
        print("[OK] No potential duplicates found.")
    
    print()


def cmd_idle():
    """Find idle or orphaned resources (cost savings opportunities)."""
    plans = database.get_all_plans(active_only=True)
    
    print("\n=== Idle/Orphaned Resource Detection ===\n")
    
    findings = []
    
    # Find 1: Plans without VM assignment
    unassigned = [p for p in plans if not p.get("assigned_vm")]
    if unassigned:
        monthly_cost = sum(monthly_cost_calc(p) for p in unassigned)
        findings.append({
            "category": "Unassigned Plans",
            "count": len(unassigned),
            "monthly_cost": monthly_cost,
            "plans": unassigned,
            "suggestion": "Assign to a VM or consider deactivating"
        })
    
    # Find 2: Plans without renewal date
    no_renewal = [p for p in plans if not p.get("next_renewal")]
    if no_renewal:
        monthly_cost = sum(monthly_cost_calc(p) for p in no_renewal)
        findings.append({
            "category": "No Renewal Date Set",
            "count": len(no_renewal),
            "monthly_cost": monthly_cost,
            "plans": no_renewal,
            "suggestion": "Set renewal dates for proper tracking"
        })
    
    # Find 3: Plans with no cost (breaks spend calculations)
    no_cost = [p for p in plans if p.get("cost", 0) == 0]
    if no_cost:
        findings.append({
            "category": "No Cost Set",
            "count": len(no_cost),
            "monthly_cost": 0,
            "plans": no_cost,
            "suggestion": "Add cost for accurate spend tracking"
        })
    
    # Find 4: High cost per VM
    from collections import defaultdict
    by_vm = defaultdict(list)
    for p in plans:
        vm = p.get("assigned_vm", "(unassigned)")
        by_vm[vm].append(p)
    
    high_cost_vms = []
    for vm, vm_plans in by_vm.items():
        vm_monthly = sum(monthly_cost_calc(p) for p in vm_plans)
        if vm_monthly > 100:  # Over $100/month per VM
            high_cost_vms.append((vm, vm_monthly, len(vm_plans)))
    
    if high_cost_vms:
        print("[!] HIGH COST VMs (>$100/month):")
        for vm, cost, count in sorted(high_cost_vms, key=lambda x: x[1], reverse=True):
            print(f"   {vm}: ${cost:.2f}/mo ({count} plans)")
        print()
    
    # Report findings
    if findings:
        print("[?] POTENTIAL SAVINGS OPPORTUNITIES:\n")
        for f in findings:
            print(f"  {f['category']}: {f['count']} plan(s) - ${f['monthly_cost']:.2f}/mo")
            print(f"    Suggestion: {f['suggestion']}")
            for p in f['plans'][:3]:  # Show first 3
                print(f"      - [{p['id']}] {p.get('name', 'Unknown')}")
            if len(f['plans']) > 3:
                print(f"      ... and {len(f['plans']) - 3} more")
            print()
    else:
        print("[OK] No obvious idle resources found.")
    
    total_monthly = sum(f['monthly_cost'] for f in findings)
    print(f"  Total potential savings: ${total_monthly:.2f}/month")
    print()


def monthly_cost_calc(plan: dict) -> float:
    """Calculate monthly cost for a plan."""
    cost = plan.get("cost", 0.0) or 0.0
    cycle = plan.get("billing_cycle", "monthly") or "monthly"
    return cost * MONTHLY_EQUIV.get(cycle, 1.0)


def cmd_menu():
    """Interactive menu mode for the CLI."""
    import sys
    # Check if running in non-interactive mode (no TTY)
    if not sys.stdin.isatty():
        print("[!] Menu mode requires an interactive terminal.")
        print("[*] Use direct commands instead:")
        print("    python tracker.py list")
        print("    python tracker.py summary")
        print("    python tracker.py check")
        return
    
    while True:
        print("\n" + "="*50)
        print("  DATA PLAN TRACKER - Interactive Menu")
        print("="*50)
        print()
        print("  [1] List all plans")
        print("  [2] Add new plan")
        print("  [3] Edit plan")
        print("  [4] Renew plan")
        print("  [5] Summary")
        print("  [6] Search plans")
        print("  [7] Check expiring")
        print("  [8] Stats")
        print("  [9] History")
        print("  [10] Export data")
        print("  [11] Audit")
        print("  [12] Help")
        print("  [0] Exit")
        print()
        
        try:
            choice = input("  Enter choice [0-12]: ").strip()
        except (EOFError, KeyboardInterrupt):
            print("\n  Goodbye!")
            break
        
        if choice == "0" or choice.lower() in ("q", "quit", "exit"):
            print("\n  Goodbye!")
            break
        elif choice == "1":
            show_all = input("  Show all plans including inactive? [y/N]: ").strip().lower() == "y"
            cmd_list(show_all=show_all)
        elif choice == "2":
            cmd_add()
        elif choice == "3":
            try:
                plan_id = int(input("  Enter plan ID: ").strip())
                cmd_edit(plan_id)
            except ValueError:
                print("  [!] Invalid plan ID")
        elif choice == "4":
            try:
                plan_id = int(input("  Enter plan ID: ").strip())
                cmd_renew(plan_id)
            except ValueError:
                print("  [!] Invalid plan ID")
        elif choice == "5":
            cmd_summary()
        elif choice == "6":
            term = input("  Search term: ").strip()
            if term:
                cmd_search(term)
        elif choice == "7":
            cmd_check()
        elif choice == "8":
            cmd_stats()
        elif choice == "9":
            plan_id = input("  Plan ID (leave blank for all): ").strip()
            if plan_id:
                try:
                    cmd_history(int(plan_id))
                except ValueError:
                    cmd_history()
            else:
                cmd_history()
        elif choice == "10":
            format = input("  Format [json/csv]: ").strip().lower() or "json"
            cmd_export(format=format)
        elif choice == "11":
            cmd_audit()
        elif choice == "12":
            print_help()
        else:
            print("  [!] Invalid choice")
        
        try:
            input("\n  Press Enter to continue...")
        except (EOFError, KeyboardInterrupt):
            print("\n  Goodbye!")
            break


def validate_plan_consistency(data: dict, is_new: bool = True) -> tuple[bool, list]:
    """
    Cross-field validation for plan data.
    Returns (is_valid, list_of_issues).
    Issues are strings describing what's wrong.
    """
    issues = []
    
    # Rule 1: SIM plans should have phone numbers (warning only)
    if data.get("type") == "sim" and not data.get("phone_number"):
        issues.append("SIM plan is missing a phone number (recommended but optional)")
    
    # Rule 2: Auto-renew requires a renewal date
    if data.get("auto_renew") and not data.get("next_renewal"):
        issues.append("Auto-renew is enabled but no renewal date is set")
    
    # Rule 3: One-off plans shouldn't auto-renew
    if data.get("billing_cycle") == "one-off" and data.get("auto_renew"):
        issues.append("One-off plans should not have auto-renew enabled")
    
    # Rule 4: Phone number without expiry date (warning for tracking)
    if data.get("phone_number") and not data.get("number_expiry"):
        issues.append("Phone number is set but expiry tracking is not configured (optional)")
    
    # Rule 5: Cost validation
    cost = data.get("cost")
    if cost is not None and cost < 0:
        issues.append(f"Cost cannot be negative (got ${cost})")
    
    # Rule 6: Date format validation
    for field in ["next_renewal", "number_expiry"]:
        val = data.get(field)
        if val and val.strip():
            try:
                datetime.strptime(val, "%Y-%m-%d")
            except ValueError:
                issues.append(f"Invalid date format in {field}: '{val}' (expected YYYY-MM-DD)")
    
    # Issues starting with "SIM plan", "Phone number" are warnings (not blocking)
    # Issues with "cannot", "should not", "Invalid" are errors (blocking)
    blocking = [i for i in issues if any(kw in i for kw in ["cannot", "should not", "required", "Invalid", "but no"])]
    
    return len(blocking) == 0, issues


def cmd_audit():
    """Run data quality audit on plans and expenses."""
    plans = database.get_all_plans(active_only=False)
    expenses = database.get_all_expenses(active_only=False)
    
    print("\n=== Data Quality Audit ===\n")
    
    issues = []
    warnings = []
    
    # Check 1: Plans missing required fields
    for p in plans:
        if not p.get("name") or not p.get("name").strip():
            issues.append(f"Plan ID {p['id']}: Missing name")
        if not p.get("type"):
            issues.append(f"Plan '{p.get('name', p['id'])}': Missing type")
        
        # SIM plans should have phone numbers
        if p.get("type") == "sim" and not p.get("phone_number"):
            warnings.append(f"SIM plan '{p.get('name', p['id'])}': Missing phone number")
        
        # Check for auto-renew with no renewal date
        if p.get("auto_renew") and not p.get("next_renewal"):
            warnings.append(f"Plan '{p.get('name', p['id'])}': Auto-renew enabled but no renewal date")
        
        # Check for invalid costs
        if p.get("cost", 0) < 0:
            issues.append(f"Plan '{p.get('name', p['id'])}': Negative cost (${p['cost']})")
    
    # Check 2: Duplicate phone numbers
    phone_numbers = {}
    for p in plans:
        if p.get("phone_number"):
            phone = p.get("phone_number").strip()
            if phone:
                if phone in phone_numbers:
                    issues.append(f"Duplicate phone number '{phone}': {p.get('name')} and {phone_numbers[phone]}")
                else:
                    phone_numbers[phone] = p.get("name", p['id'])
    
    # Check 3: Inactive plans older than 90 days
    from datetime import datetime
    for p in plans:
        if not p.get("active", 1):
            created = p.get("created_at", "")
            if created:
                try:
                    created_dt = datetime.strptime(created, "%Y-%m-%d %H:%M:%S")
                    days_old = (datetime.now() - created_dt).days
                    if days_old > 90:
                        warnings.append(f"Inactive plan '{p.get('name', p['id'])}': Created {days_old} days ago - consider permanent deletion")
                except Exception:
                    pass
    
    # Check 4: Invalid date formats
    for p in plans:
        for field in ["next_renewal", "number_expiry"]:
            val = p.get(field, "")
            if val:
                try:
                    datetime.strptime(val, "%Y-%m-%d")
                except ValueError:
                    issues.append(f"Plan '{p.get('name', p['id'])}': Invalid date format in {field} ('{val}')")
    
    # Check 5: Expenses without names
    for e in expenses:
        if not e.get("name") or not e.get("name").strip():
            issues.append(f"Expense ID {e['id']}: Missing name")
    
    # Report results
    if issues:
        print(f"[!] {len(issues)} ISSUE(S) FOUND:")
        for issue in issues:
            print(f"   {issue}")
        print()
    
    if warnings:
        print(f"[*] {len(warnings)} WARNING(S):")
        for warning in warnings:
            print(f"   {warning}")
        print()
    
    if not issues and not warnings:
        print("[OK] All checks passed! No data quality issues found.")
    
    print(f"\n  Summary: {len(plans)} plans, {len(expenses)} expenses checked")
    print(f"  Issues: {len(issues)}, Warnings: {len(warnings)}")
    print()


def cmd_bulk_renew(filter_provider: str = None, filter_vm: str = None, filter_type: str = None, days: int = 28, dry_run: bool = False):
    """Batch renew plans matching the given filters."""
    from datetime import datetime, timedelta
    
    plans = database.get_all_plans(active_only=True)
    
    # Apply filters
    matching = []
    for p in plans:
        if filter_provider and filter_provider.lower() not in (p.get("provider") or "").lower():
            continue
        if filter_vm and filter_vm.lower() not in (p.get("assigned_vm") or "").lower():
            continue
        if filter_type and p.get("type") != filter_type.lower():
            continue
        matching.append(p)
    
    if not matching:
        print("[*] No plans match the given filters.")
        return
    
    print(f"\n=== Bulk Renew ===")
    print(f"  Filter: provider={filter_provider}, vm={filter_vm}, type={filter_type}")
    print(f"  {len(matching)} plan(s) will be renewed (+{days} days)")
    print()
    
    if dry_run:
        print("[DRY RUN] The following plans would be renewed:")
        for p in matching:
            current = p.get("next_renewal", "N/A")
            print(f"  [{p['id']}] {p.get('name')} - current: {current}")
        return
    
    confirm = prompt_yn("Proceed with bulk renew?", default=False)
    if not confirm:
        print("[*] Cancelled")
        return
    
    updated = 0
    for p in matching:
        current_renewal = p.get("next_renewal")
        if current_renewal:
            try:
                current_dt = datetime.strptime(current_renewal, "%Y-%m-%d")
                new_dt = current_dt + timedelta(days=days)
                new_renewal = new_dt.strftime("%Y-%m-%d")
                database.update_plan(p["id"], {"next_renewal": new_renewal})
                updated += 1
                print(f"  Updated [{p['id']}] {p.get('name')}: {current_renewal} → {new_renewal}")
            except Exception as e:
                print(f"  [!] Failed to update [{p['id']}]: {e}")
        else:
            print(f"  [!] Skipped [{p['id']}] {p.get('name')}: no renewal date set")
    
    print(f"\n[*] {updated}/{len(matching)} plan(s) renewed successfully")


def cmd_bulk_deactivate(filter_vm: str = None, filter_provider: str = None, filter_type: str = None, dry_run: bool = False):
    """Bulk deactivate plans matching the given filters."""
    plans = database.get_all_plans(active_only=True)
    
    matching = []
    for p in plans:
        if filter_vm and filter_vm.lower() not in (p.get("assigned_vm") or "").lower():
            continue
        if filter_provider and filter_provider.lower() not in (p.get("provider") or "").lower():
            continue
        if filter_type and p.get("type") != filter_type.lower():
            continue
        matching.append(p)
    
    if not matching:
        print("[*] No active plans match the given filters.")
        return
    
    print(f"\n=== Bulk Deactivate ===")
    print(f"  Filter: vm={filter_vm}, provider={filter_provider}, type={filter_type}")
    print(f"  {len(matching)} plan(s) will be deactivated")
    print()
    
    if dry_run:
        print("[DRY RUN] The following plans would be deactivated:")
        for p in matching:
            print(f"  [{p['id']}] {p.get('name')} ({p.get('provider')})")
        return
    
    confirm = prompt_yn("Proceed with bulk deactivate?", default=False)
    if not confirm:
        print("[*] Cancelled")
        return
    
    deactivated = 0
    for p in matching:
        database.deactivate_plan(p["id"])
        deactivated += 1
        print(f"  Deactivated [{p['id']}] {p.get('name')}")
    
    print(f"\n[*] {deactivated} plan(s) deactivated successfully")


def cmd_bulk_reassign(from_vm: str, to_vm: str, dry_run: bool = False):
    """Reassign all plans from one VM to another."""
    plans = database.get_all_plans(active_only=True)
    
    matching = [p for p in plans if (p.get("assigned_vm") or "").lower() == from_vm.lower()]
    
    if not matching:
        print(f"[*] No active plans assigned to VM '{from_vm}'.")
        return
    
    print(f"\n=== Bulk VM Reassign ===")
    print(f"  Moving {len(matching)} plan(s) from '{from_vm}' to '{to_vm}'")
    print()
    
    if dry_run:
        print("[DRY RUN] The following plans would be reassigned:")
        for p in matching:
            print(f"  [{p['id']}] {p.get('name')}")
        return
    
    confirm = prompt_yn("Proceed with bulk reassign?", default=False)
    if not confirm:
        print("[*] Cancelled")
        return
    
    reassigned = 0
    for p in matching:
        database.update_plan(p["id"], {"assigned_vm": to_vm})
        reassigned += 1
        print(f"  Reassigned [{p['id']}] {p.get('name')} → {to_vm}")
    
    print(f"\n[*] {reassigned} plan(s) reassigned successfully")


def cmd_deactivate(plan_id: int):
    """Mark a plan as inactive (it stays in the database but won't appear in lists)."""
    plan = database.get_plan_by_id(plan_id)
    if not plan:
        print(f"[!] No plan found with ID {plan_id}")
        return

    confirm = prompt_yn(f"Deactivate '{plan['name']}'? (it will be hidden but not deleted)", default=False)
    if confirm:
        database.deactivate_plan(plan_id)
        print(f"[*] Plan '{plan['name']}' deactivated")
    else:
        print("[*] Cancelled")


def cmd_restore(plan_id: int):
    """Reactivate a previously deactivated plan."""
    plan = database.get_plan_by_id(plan_id)
    if not plan:
        print(f"[!] No plan found with ID {plan_id}")
        return
    if plan.get("active"):
        print(f"[*] Plan '{plan['name']}' is already active")
        return
    database.restore_plan(plan_id)
    print(f"[*] Plan '{plan['name']}' restored to active")


def cmd_delete(plan_id: int):
    """Permanently delete a plan from the database (cannot be undone)."""
    plan = database.get_plan_by_id(plan_id)
    if not plan:
        print(f"[!] No plan found with ID {plan_id}")
        return

    print(f"\n  Plan: {plan['name']} ({plan.get('provider', '')} | {plan.get('type', '')})")
    confirm = prompt_yn("Permanently delete this plan? This CANNOT be undone", default=False)
    if confirm:
        database.delete_plan(plan_id)
        print(f"[*] Plan '{plan['name']}' permanently deleted")
    else:
        print("[*] Cancelled")


# ---------------------------------------------------------------------------
# Expense Commands
# ---------------------------------------------------------------------------

VALID_EXPENSE_CATEGORIES = ["software", "hosting", "tools", "entertainment", "utilities", "services", "other"]


def cmd_expense_add():
    """Interactively add a new expense."""
    print("\n--- Add New Expense ---")
    print("Press Enter to skip optional fields.\n")

    data = {}
    data["name"] = prompt("Expense name (e.g. ChatGPT Plus)", required=True)
    data["category"] = prompt_choice("Category", VALID_EXPENSE_CATEGORIES, default="other")
    data["cost"] = prompt_float("Cost per cycle (AUD)", default=0.0)
    data["billing_cycle"] = prompt_choice("Billing cycle", VALID_CYCLES, default="monthly")
    data["notes"] = prompt("Notes (optional)")

    expense_id = database.add_expense(data)
    print(f"\n[*] Expense saved with ID {expense_id}: {data['name']}")


def cmd_expense_list(show_all: bool = False):
    """Display all expenses in a table, grouped by category."""
    expenses = database.get_all_expenses(active_only=not show_all)

    if not expenses:
        print("\nNo expenses found. Add one with: python tracker.py expense add")
        return

    # Group by category
    by_category = {}
    for exp in expenses:
        cat = exp.get("category", "other") or "other"
        by_category.setdefault(cat, []).append(exp)

    print(f"\n{'='*60}")
    print("  EXPENSE LIST")
    print(f"{'='*60}")

    total_monthly = 0.0

    for category, items in sorted(by_category.items()):
        print(f"\n  --- {category.upper()} ---")
        rows = []
        cat_monthly = 0.0
        for e in items:
            cost = e.get("cost", 0.0) or 0.0
            cycle = e.get("billing_cycle", "monthly") or "monthly"
            monthly_cost = cost * MONTHLY_EQUIV.get(cycle, 1.0)
            cat_monthly += monthly_cost
            total_monthly += monthly_cost

            is_active = bool(e.get("active", 1))
            status = "" if is_active else Fore.RED + " (inactive)" + Style.RESET_ALL

            rows.append([
                e["id"],
                e.get("name", "") + status,
                f"${cost:.2f}",
                e.get("billing_cycle", "-"),
                f"${monthly_cost:.2f}/mo",
            ])

        headers = ["ID", "Name", "Cost", "Cycle", "Monthly Equiv"]
        print(tabulate(rows, headers=headers, tablefmt="simple"))
        print(f"  Category total: ${cat_monthly:.2f}/mo")

    print(f"\n{'='*60}")
    print(f"  Total estimated monthly spend: AUD ${total_monthly:.2f}")
    print(f"  Total estimated annual spend:  AUD ${total_monthly * 12:.2f}")


def cmd_expense_edit(expense_id: int):
    """Edit an existing expense."""
    expense = database.get_expense_by_id(expense_id)
    if not expense:
        print(f"[!] No expense found with ID {expense_id}")
        return

    print(f"\n--- Edit Expense: {expense['name']} ---")
    print("Press Enter to keep current value.\n")

    updates = {}

    new_name = prompt("Name", default=expense.get("name", ""))
    if new_name != expense.get("name", ""):
        updates["name"] = new_name

    new_category = prompt_choice("Category", VALID_EXPENSE_CATEGORIES, default=expense.get("category", "other"))
    if new_category != expense.get("category"):
        updates["category"] = new_category

    current_cost = expense.get("cost", 0.0) or 0.0
    new_cost = prompt_float("Cost (AUD)", default=current_cost)
    if new_cost != current_cost:
        updates["cost"] = new_cost

    new_cycle = prompt_choice("Billing cycle", VALID_CYCLES, default=expense.get("billing_cycle", "monthly"))
    if new_cycle != expense.get("billing_cycle"):
        updates["billing_cycle"] = new_cycle

    new_notes = prompt("Notes", default=expense.get("notes", "") or "")
    if new_notes != (expense.get("notes") or ""):
        updates["notes"] = new_notes

    if updates:
        database.update_expense(expense_id, updates)
        print(f"\n[*] Expense {expense_id} updated ({len(updates)} field(s) changed)")
    else:
        print("\n[*] No changes made")


def cmd_expense_delete(expense_id: int):
    """Permanently delete an expense."""
    expense = database.get_expense_by_id(expense_id)
    if not expense:
        print(f"[!] No expense found with ID {expense_id}")
        return

    print(f"\n  Expense: {expense['name']} ({expense.get('category', '')})")
    confirm = prompt_yn("Permanently delete this expense? This CANNOT be undone", default=False)
    if confirm:
        database.delete_expense(expense_id)
        print(f"[*] Expense '{expense['name']}' permanently deleted")
    else:
        print("[*] Cancelled")


def cmd_summary():
    """Show total spend by type and list upcoming renewals."""
    plans = database.get_all_plans(active_only=True)

    if not plans:
        print("\nNo active plans. Add one with: python tracker.py add")
        return

    print("\n=== Spend Summary (active plans) ===\n")

    by_type = {}
    for plan in plans:
        t = plan.get("type", "other")
        cost = plan.get("cost", 0.0) or 0.0
        cycle = plan.get("billing_cycle", "monthly") or "monthly"
        monthly_cost = cost * MONTHLY_EQUIV.get(cycle, 1.0)
        by_type.setdefault(t, {"count": 0, "monthly": 0.0})
        by_type[t]["count"] += 1
        by_type[t]["monthly"] += monthly_cost

    total_monthly = 0.0
    summary_rows = []
    for t, stats in sorted(by_type.items()):
        summary_rows.append([
            t.upper(),
            stats["count"],
            f"AUD ${stats['monthly']:.2f}/mo"
        ])
        total_monthly += stats["monthly"]

    print(tabulate(summary_rows, headers=["Type", "Count", "Est. Monthly Cost"], tablefmt="simple"))
    print(f"\n  Total estimated monthly spend: AUD ${total_monthly:.2f}")
    print(f"  Total estimated annual spend:  AUD ${total_monthly * 12:.2f}")

    # Upcoming renewals in next 30 days
    print("\n=== Upcoming Renewals (next 30 days) ===\n")
    upcoming = [p for p in plans if p.get("next_renewal") and days_until(p["next_renewal"]) <= 30]
    upcoming.sort(key=lambda p: p.get("next_renewal", ""))

    if not upcoming:
        print("  No renewals due in the next 30 days.")
    else:
        rows = []
        for p in upcoming:
            days = days_until(p["next_renewal"])
            rows.append([
                p["id"],
                p.get("name", ""),
                p.get("provider", "") or "-",
                format_date_display(p.get("next_renewal", "")),
                colour_days(days),
                f"AUD ${p.get('cost', 0.0):.2f}",
            ])
        print(tabulate(rows, headers=["ID", "Name", "Provider", "Due Date", "Days Left", "Cost"],
                       tablefmt="simple"))


def cmd_check():
    """
    Daily check command - designed to run silently via Task Scheduler.
    Finds expiring plans and fires all configured alerts.
    Also auto-advances next_renewal dates for auto-renew plans whose date
    is today or in the past.
    Prints a dashboard to stdout (captured in Task Scheduler log).
    """
    # Load warning threshold from config
    config = alerts.load_config()
    warning_days = config.get("alert_days_warning", 14)

    # Auto-advance auto-renew plans whose renewal date is today or past
    advanced = database.advance_auto_renew_plans()
    if advanced and not is_quiet():
        print(f"[*] Auto-advanced {advanced} auto-renew plan(s)")
        print()

    all_plans = database.get_all_plans(active_only=True)
    expiring = database.get_expiring_plans(within_days=warning_days)

    alerts.run_all_alerts(expiring_plans=expiring, all_plans=all_plans)


def cmd_seed():
    """Add sample plans so you can see how the tool looks immediately after setup."""
    print("\n[*] Adding sample plans...")

    samples = [
        {
            "name": "Boost SIM #1",
            "type": "sim",
            "provider": "Boost Mobile",
            "assigned_vm": "VM-01",
            "cost": 30.00,
            "billing_cycle": "28-day",
            "next_renewal": "2026-03-25",
            "auto_renew": 0,
            "notes": "Basic data SIM for scraper VM",
        },
        {
            "name": "Oxylabs Residential",
            "type": "proxy",
            "provider": "Oxylabs",
            "assigned_vm": "VM-02",
            "cost": 99.00,
            "billing_cycle": "monthly",
            "next_renewal": "2026-04-01",
            "auto_renew": 1,
            "notes": "Residential proxy pool - 10GB/month",
        },
        {
            "name": "Telstra Prepaid SIM",
            "type": "sim",
            "provider": "Telstra",
            "assigned_vm": "VM-03",
            "cost": 55.00,
            "billing_cycle": "28-day",
            "next_renewal": "2026-03-18",
            "auto_renew": 0,
            "notes": "High priority - Telstra coverage needed for TAB scraping",
        },
        {
            "name": "NordVPN Annual",
            "type": "vpn",
            "provider": "NordVPN",
            "assigned_vm": "",
            "cost": 89.99,
            "billing_cycle": "annual",
            "next_renewal": "2027-01-15",
            "auto_renew": 1,
            "notes": "Shared across all VMs for backup connectivity",
        },
    ]

    for sample in samples:
        plan_id = database.add_plan(sample)
        print(f"  Added: [{plan_id}] {sample['name']}")

    print(f"\n[*] {len(samples)} sample plans added. Run: python tracker.py list")


# ---------------------------------------------------------------------------
# Search, Export, Backup
# ---------------------------------------------------------------------------

def _log_plan_change(plan_id: int, action: str, details: str = ""):
    """Helper to log plan changes to audit log."""
    try:
        database.log_audit(action, plan_id=plan_id, details=details)
    except Exception:
        pass  # Don't let logging failures break operations


def cmd_history(plan_id: int = None):
    """Show change history for a plan or all plans."""
    if plan_id:
        plan = database.get_plan_by_id(plan_id)
        if not plan:
            print(f"[!] No plan found with ID {plan_id}")
            return
        print(f"\n=== Change History for [{plan_id}] {plan['name']} ===\n")
        history = database.get_plan_history(plan_id)
    else:
        print("\n=== Recent Cost Changes (All Plans) ===\n")
        history = database.get_cost_changes()
    
    if not history:
        print("  No history records found.")
        return
    
    rows = []
    for h in history:
        rows.append([
            h['timestamp'][:16],  # Trim seconds
            h.get('plan_id', '-'),
            h['field_changed'],
            h.get('old_value', '-')[:20],
            h.get('new_value', '-')[:20],
        ])
    
    headers = ["Timestamp", "Plan ID", "Field", "Old Value", "New Value"]
    print(tabulate(rows, headers=headers, tablefmt="simple"))
    print(f"\n  Total records: {len(history)}")


def cmd_audit_log(limit: int = 50):
    """Show recent audit log entries."""
    print(f"\n=== Recent Audit Log (last {limit} entries) ===\n")
    
    logs = database.get_audit_logs(limit=limit)
    
    if not logs:
        print("  No audit log entries found.")
        return
    
    rows = []
    for log in logs:
        entity = f"Plan {log['plan_id']}" if log['plan_id'] else f"Expense {log['expense_id']}" if log['expense_id'] else "System"
        rows.append([
            log['timestamp'][:16],
            log['action'],
            entity,
            (log.get('details') or '-')[:30],
        ])
    
    headers = ["Timestamp", "Action", "Entity", "Details"]
    print(tabulate(rows, headers=headers, tablefmt="simple"))
    print(f"\n  Showing {len(logs)} entries")


def cmd_alert_history(limit: int = 50):
    """Show recent alert history entries."""
    print(f"\n=== Recent Alert History (last {limit} entries) ===\n")
    
    logs = database.get_alert_logs(limit=limit)
    
    if not logs:
        print("  No alert history entries found.")
        return
    
    rows = []
    for log in logs:
        entity = f"Plan {log['plan_id']}" if log['plan_id'] else "Broadcast"
        status = "[OK]" if log.get('delivered') else "[FAIL]"
        rows.append([
            log['timestamp'][:16],
            log['alert_type'],
            entity,
            status,
            (log.get('message_summary') or '-')[:40],
        ])
    
    headers = ["Timestamp", "Type", "Entity", "Status", "Summary"]
    print(tabulate(rows, headers=headers, tablefmt="simple"))
    print(f"\n  Showing {len(logs)} entries")


def fuzzy_match_score(term: str, text: str) -> float:
    """Calculate fuzzy match score between term and text (0-1)."""
    if not text:
        return 0.0
    term_lower = term.lower()
    text_lower = text.lower()
    
    # Exact substring match = highest score
    if term_lower in text_lower:
        return 1.0
    
    # Use SequenceMatcher for fuzzy matching
    from difflib import SequenceMatcher
    return SequenceMatcher(None, term_lower, text_lower).ratio()


def find_similar_plans(term: str, plans: list, threshold: float = 0.5) -> list:
    """Find plans similar to the search term using fuzzy matching."""
    scored = []
    for p in plans:
        # Check multiple fields
        scores = [
            fuzzy_match_score(term, p.get("name", "")),
            fuzzy_match_score(term, p.get("provider", "")),
            fuzzy_match_score(term, p.get("assigned_vm", "")),
            fuzzy_match_score(term, p.get("phone_number", "")),
        ]
        best_score = max(scores)
        if best_score >= threshold:
            scored.append((best_score, p))
    
    # Sort by score descending
    scored.sort(key=lambda x: x[0], reverse=True)
    return [p for _, p in scored]


def suggest_similar_plan(term: str, plans: list) -> str:
    """Suggest the closest matching plan name for typos."""
    from difflib import get_close_matches
    names = [p.get("name", "") for p in plans if p.get("name")]
    matches = get_close_matches(term, names, n=1, cutoff=0.5)
    return matches[0] if matches else None


def cmd_search(term: str):
    """Search plans by name or provider with fuzzy matching."""
    all_plans = database.get_all_plans(active_only=False)
    term_lower = term.lower()
    
    # First try exact substring match
    exact_matches = [
        p for p in all_plans
        if term_lower in (p.get("name", "") or "").lower()
        or term_lower in (p.get("provider", "") or "").lower()
        or term_lower in (p.get("assigned_vm", "") or "").lower()
        or term_lower in (p.get("phone_number", "") or "").lower()
    ]
    
    if exact_matches:
        matches = exact_matches
    else:
        # Fall back to fuzzy matching
        matches = find_similar_plans(term, all_plans, threshold=0.4)
    
    if not matches:
        print(f"\n[*] No plans found matching '{term}'")
        # Offer suggestions
        suggestion = suggest_similar_plan(term, all_plans)
        if suggestion:
            print(f"    Did you mean: '{suggestion}'?")
            print(f"    Try: python tracker.py search '{suggestion}'")
        print(f"    Run 'python tracker.py list' to see all plans")
        return
    
    print(f"\n[*] Found {len(matches)} plan(s) matching '{term}':\n")
    
    rows = []
    for p in matches:
        renewal = p.get("next_renewal", "")
        is_active = bool(p.get("active", 1))
        
        if renewal and is_active:
            days = days_until(renewal)
            days_str = colour_days(days)
            renewal_display = format_date_display(renewal)
        else:
            days_str = "-"
            renewal_display = format_date_display(renewal) if renewal else "-"
        
        status = "" if is_active else Fore.RED + " (inactive)" + Style.RESET_ALL
        
        rows.append([
            p["id"],
            p.get("name", "") + status,
            p.get("type", "") or "-",
            p.get("provider", "") or "-",
            renewal_display,
            days_str,
            f"${p.get('cost', 0.0):.2f}",
        ])
    
    headers = ["ID", "Name", "Type", "Provider", "Renewal", "Days", "Cost"]
    print(tabulate(rows, headers=headers, tablefmt="simple"))


def cmd_template(rows: int = 5):
    """Generate a CSV template file for bulk import."""
    import csv
    from datetime import datetime, timedelta
    
    filename = f"import_template_{rows}rows.csv"
    
    headers = ["name", "type", "provider", "assigned_vm", "cost", "billing_cycle", 
               "next_renewal", "auto_renew", "phone_number", "number_expiry", "notes"]
    
    # Generate sample data
    sample_rows = []
    for i in range(rows):
        sample_rows.append({
            "name": f"Sample Plan #{i+1}",
            "type": "sim",
            "provider": "Boost Mobile",
            "assigned_vm": "VM-01",
            "cost": "30.00",
            "billing_cycle": "monthly",
            "next_renewal": (datetime.now() + timedelta(days=30)).strftime("%Y-%m-%d"),
            "auto_renew": "No",
            "phone_number": "",
            "number_expiry": "",
            "notes": "Sample data - replace with real values"
        })
    
    with open(filename, "w", newline="", encoding="utf-8") as f:
        writer = csv.DictWriter(f, fieldnames=headers)
        writer.writeheader()
        writer.writerows(sample_rows)
    
    print(f"[*] Created import template: {filename}")
    print(f"    with {rows} sample row(s)")
    print()
    print("  Columns:")
    for h in headers:
        print(f"    - {h}")
    print()
    print("  Type options: sim, proxy, vpn, phone, other")
    print("  Billing cycle options: monthly, 28-day, annual, one-off")
    print("  Auto-renew: Yes or No")
    print("  Dates: YYYY-MM-DD format")


def cmd_calendar(path: str = None, days: int = 90):
    """Generate iCalendar (.ics) file with renewal dates."""
    from datetime import datetime, timedelta
    
    plans = database.get_all_plans(active_only=True)
    
    if not plans:
        print("\n[*] No active plans to export to calendar.")
        return
    
    # Build ICS content
    ics_lines = [
        "BEGIN:VCALENDAR",
        "VERSION:2.0",
        "PRODID:-//Data Plan Tracker//EN",
        "CALSCALE:GREGORIAN",
        "METHOD:PUBLISH",
        "X-WR-CALNAME:Data Plan Renewals",
    ]
    
    timestamp = datetime.now().strftime("%Y%m%dT%H%M%SZ")
    event_count = 0
    
    for p in plans:
        renewal = p.get("next_renewal")
        if not renewal:
            continue
        
        try:
            renewal_dt = datetime.strptime(renewal, "%Y-%m-%d")
            # Only include renewals within specified days
            if (renewal_dt - datetime.now()).days > days:
                continue
            
            plan_name = p.get("name", "Unknown Plan")
            provider = p.get("provider", "Unknown Provider")
            cost = p.get("cost", 0)
            vm = p.get("assigned_vm", "")
            
            # Create unique event ID
            event_id = f"{p['id']}-{renewal}@dataplantracker"
            
            # Event start (all-day event)
            start_date = renewal_dt.strftime("%Y%m%d")
            # End date (next day for all-day event)
            end_date = (renewal_dt + timedelta(days=1)).strftime("%Y%m%d")
            
            # Summary
            summary = f"Renew: {plan_name}"
            if provider:
                summary = f"Renew: {plan_name} ({provider})"
            
            # Description
            description_parts = [
                f"Plan: {plan_name}",
                f"Provider: {provider}",
                f"Cost: ${cost:.2f}",
            ]
            if vm:
                description_parts.append(f"VM: {vm}")
            description_parts.append(f"Auto-renew: {'Yes' if p.get('auto_renew') else 'No'}")
            description = "\\n".join(description_parts)
            
            # Build event
            ics_lines.extend([
                "BEGIN:VEVENT",
                f"UID:{event_id}",
                f"DTSTAMP:{timestamp}",
                f"DTSTART;VALUE=DATE:{start_date}",
                f"DTEND;VALUE=DATE:{end_date}",
                f"SUMMARY:{summary}",
                f"DESCRIPTION:{description}",
                "STATUS:CONFIRMED",
                "BEGIN:VALARM",
                "ACTION:DISPLAY",
                "DESCRIPTION:Reminder",
                "TRIGGER:-P1D",  # 1 day before
                "END:VALARM",
                "END:VEVENT",
            ])
            event_count += 1
            
            # Also add phone number expiry if set
            num_expiry = p.get("number_expiry")
            if num_expiry:
                try:
                    num_dt = datetime.strptime(num_expiry, "%Y-%m-%d")
                    if (num_dt - datetime.now()).days <= days:
                        num_start = num_dt.strftime("%Y%m%d")
                        num_end = (num_dt + timedelta(days=1)).strftime("%Y%m%d")
                        phone = p.get("phone_number", "Unknown")
                        num_event_id = f"{p['id']}-num-{num_expiry}@dataplantracker"
                        
                        ics_lines.extend([
                            "BEGIN:VEVENT",
                            f"UID:{num_event_id}",
                            f"DTSTAMP:{timestamp}",
                            f"DTSTART;VALUE=DATE:{num_start}",
                            f"DTEND;VALUE=DATE:{num_end}",
                            f"SUMMARY:Number Expiry: {phone}",
                            f"DESCRIPTION:Phone number {phone} for {plan_name} expires\\nProvider: {provider}",
                            "STATUS:CONFIRMED",
                            "BEGIN:VALARM",
                            "ACTION:DISPLAY",
                            "DESCRIPTION:Reminder",
                            "TRIGGER:-P7D",  # 7 days before
                            "END:VALARM",
                            "END:VEVENT",
                        ])
                        event_count += 1
                except ValueError:
                    pass
                    
        except ValueError:
            continue
    
    ics_lines.append("END:VCALENDAR")
    
    if event_count == 0:
        print(f"\n[*] No renewals found in the next {days} days.")
        return
    
    # Write file
    timestamp_str = datetime.now().strftime("%Y%m%d_%H%M%S")
    default_path = f"renewals_{timestamp_str}.ics"
    export_path = path or default_path
    
    with open(export_path, "w", encoding="utf-8") as f:
        f.write("\r\n".join(ics_lines))
    
    print(f"\n[*] Exported {event_count} event(s) to {export_path}")
    print(f"    Import this file to Google Calendar, Outlook, or Apple Calendar")
    print(f"    Includes {days}-day lookahead with automatic reminders")


def cmd_export(path: str = None, format: str = "json"):
    """Export all plans and expenses to a JSON or CSV file."""
    import json
    import csv
    from datetime import datetime
    
    timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
    
    if format == "csv":
        export_path = path or f"plans_export_{timestamp}.csv"
        plans = database.get_all_plans(active_only=False)
        
        with open(export_path, "w", newline="", encoding="utf-8") as f:
            if plans:
                fieldnames = ["id", "name", "type", "provider", "assigned_vm", 
                            "cost", "billing_cycle", "next_renewal", "auto_renew",
                            "phone_number", "number_expiry", "notes", "active"]
                writer = csv.DictWriter(f, fieldnames=fieldnames, extrasaction='ignore')
                writer.writeheader()
                for p in plans:
                    row = {k: p.get(k, "") for k in fieldnames}
                    row["auto_renew"] = "Yes" if p.get("auto_renew") else "No"
                    row["active"] = "Yes" if p.get("active", 1) else "No"
                    writer.writerow(row)
        
        print(f"[*] Exported {len(plans)} plans to {export_path} (CSV)")
    else:
        export_path = path or f"data_export_{timestamp}.json"
        
        data = {
            "export_date": datetime.now().isoformat(),
            "plans": database.get_all_plans(active_only=False),
            "expenses": database.get_all_expenses(active_only=False),
        }
        
        with open(export_path, "w") as f:
            json.dump(data, f, indent=2, default=str)
        
        print(f"[*] Exported {len(data['plans'])} plans and {len(data['expenses'])} expenses to {export_path} (JSON)")


def cmd_backup(path: str = None):
    """Create a timestamped backup of the database file."""
    import shutil
    from datetime import datetime
    
    timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
    default_path = f"plans_backup_{timestamp}.db"
    backup_path = path or default_path
    
    source = database.DB_PATH
    shutil.copy2(source, backup_path)
    print(f"[*] Database backed up to {backup_path}")
    
    # Also export JSON as additional backup
    json_path = backup_path.replace(".db", ".json")
    cmd_export(json_path)


def cmd_import(path: str, dry_run: bool = False):
    """Import plans and expenses from a JSON or CSV file with validation."""
    import json
    import csv
    import os
    
    if not os.path.exists(path):
        print(f"[!] File not found: {path}")
        return
    
    imported_plans = 0
    imported_expenses = 0
    skipped = 0
    validation_errors = []
    
    def validate_plan(data: dict, source: str) -> tuple:
        """Validate plan data. Returns (is_valid, errors)."""
        errors = []
        
        # Required fields
        if not data.get('name') or not str(data['name']).strip():
            errors.append(f"Missing required field: name")
        
        # Type validation
        plan_type = data.get('type', 'other').lower()
        if plan_type not in VALID_TYPES:
            errors.append(f"Invalid type '{plan_type}'. Must be one of: {', '.join(VALID_TYPES)}")
        
        # Billing cycle validation
        cycle = data.get('billing_cycle', 'monthly').lower()
        if cycle not in VALID_CYCLES:
            errors.append(f"Invalid billing_cycle '{cycle}'. Must be one of: {', '.join(VALID_CYCLES)}")
        
        # Cost validation
        try:
            cost = float(data.get('cost', 0) or 0)
            if cost < 0:
                errors.append(f"Negative cost: ${cost}")
        except (ValueError, TypeError):
            errors.append(f"Invalid cost value: {data.get('cost')}")
        
        # Date validation
        for field in ['next_renewal', 'number_expiry']:
            val = data.get(field, '')
            if val:
                try:
                    datetime.strptime(val, "%Y-%m-%d")
                except ValueError:
                    errors.append(f"Invalid date format in {field}: '{val}' (expected YYYY-MM-DD)")
        
        # Phone number validation
        if data.get('phone_number'):
            is_valid, normalized, error = validate_phone_number(data['phone_number'])
            if not is_valid:
                errors.append(f"Invalid phone number: {error}")
            else:
                data['phone_number'] = normalized  # Use normalized format
        
        return len(errors) == 0, errors
    
    if path.endswith('.json'):
        with open(path, 'r') as f:
            data = json.load(f)
        
        plans = data.get('plans', [])
        expenses = data.get('expenses', [])
        
        print(f"[*] Found {len(plans)} plans and {len(expenses)} expenses in JSON")
        
        # Validate all plans first
        valid_plans = []
        for idx, p in enumerate(plans):
            is_valid, errors = validate_plan(p, f"JSON plan #{idx+1}")
            if is_valid:
                valid_plans.append(p)
            else:
                validation_errors.extend([f"Plan {p.get('name', f'#{idx+1}')}: {e}" for e in errors])
        
        if validation_errors:
            print(f"[!] Found {len(validation_errors)} validation error(s):")
            for err in validation_errors[:10]:  # Show first 10
                print(f"    {err}")
            if len(validation_errors) > 10:
                print(f"    ... and {len(validation_errors) - 10} more")
            print()
            if not dry_run:
                print("[!] Import aborted due to validation errors. Fix issues or use --dry-run to preview.")
                return
        
        if dry_run:
            print(f"[DRY RUN] Would import {len(valid_plans)} plans and {len(expenses)} expenses")
            return
        
        for p in valid_plans:
            p.pop('id', None)
            p.pop('created_at', None)
            p.setdefault('active', 1)
            database.add_plan(p)
            imported_plans += 1
        
        for e in expenses:
            e.pop('id', None)
            e.pop('created_at', None)
            e.setdefault('active', 1)
            database.add_expense(e)
            imported_expenses += 1
    
    elif path.endswith('.csv'):
        with open(path, 'r', newline='', encoding='utf-8') as f:
            reader = csv.DictReader(f)
            rows = list(reader)
        
        print(f"[*] Found {len(rows)} rows in CSV")
        
        valid_rows = []
        for idx, row in enumerate(rows):
            data = {
                'name': row.get('name', row.get('Name', '')),
                'type': row.get('type', row.get('Type', 'other')).lower(),
                'provider': row.get('provider', row.get('Provider', '')),
                'assigned_vm': row.get('assigned_vm', row.get('VM', row.get('assigned_vm', ''))),
                'cost': row.get('cost', row.get('Cost', 0)),
                'billing_cycle': row.get('billing_cycle', row.get('Cycle', 'monthly')).lower(),
                'next_renewal': row.get('next_renewal', row.get('Renewal', '')),
                'auto_renew': 1 if str(row.get('auto_renew', '')).lower() in ('yes', 'true', '1') else 0,
                'notes': row.get('notes', row.get('Notes', '')),
                'phone_number': row.get('phone_number', row.get('Phone', '')),
                'number_expiry': row.get('number_expiry', ''),
            }
            
            is_valid, errors = validate_plan(data, f"CSV row #{idx+1}")
            if is_valid and data['name']:
                valid_rows.append(data)
            else:
                if not data['name']:
                    errors.append("Missing name")
                validation_errors.extend([f"Row {idx+1}: {e}" for e in errors])
                skipped += 1
        
        if validation_errors:
            print(f"[!] Found {len(validation_errors)} validation error(s):")
            for err in validation_errors[:10]:
                print(f"    {err}")
            if len(validation_errors) > 10:
                print(f"    ... and {len(validation_errors) - 10} more")
            print()
        
        if dry_run:
            print(f"[DRY RUN] Would import {len(valid_rows)} valid rows from CSV")
            return
        
        for data in valid_rows:
            database.add_plan(data)
            imported_plans += 1
    else:
        print("[!] Unsupported file format. Use .json or .csv")
        return
    
    print(f"[*] Import complete: {imported_plans} plans, {imported_expenses} expenses added")
    if skipped:
        print(f"    ({skipped} rows skipped due to validation errors)")


# ---------------------------------------------------------------------------
# Entry point
# ---------------------------------------------------------------------------

def print_help():
    print("""
Data Plan Tracker - track SIM cards, proxies, and VPN plans

Usage:
  python tracker.py add              Add a new plan (interactive)
  python tracker.py list             Show all active plans
  python tracker.py list all         Show all plans including inactive
  python tracker.py renew <id>       Update renewal date for a plan
  python tracker.py edit <id>        Edit any field of a plan
  python tracker.py deactivate <id>  Mark a plan as inactive
  python tracker.py restore <id>     Reactivate a deactivated plan
  python tracker.py delete <id>      Permanently delete a plan
  python tracker.py summary          Spend summary + upcoming renewals
  python tracker.py providers        Show spending breakdown by provider
  python tracker.py stats            Show detailed statistics
  python tracker.py budget           Show budget variance report
  python tracker.py forecast [n]     Cash flow forecast (default 6 months)
  python tracker.py date-range <from> <to>  Renewals in date range
  python tracker.py audit            Data quality audit
  python tracker.py check            Daily check (used by Task Scheduler)
  python tracker.py seed             Add sample data to see how it looks
  python tracker.py search <term>    Search plans by name or provider
  python tracker.py export [path] [--format json|csv]  Export all data
  python tracker.py backup [path]    Create timestamped backup of database
  python tracker.py import <path>    Import plans from JSON/CSV (with validation)
  python tracker.py template [n]     Generate CSV import template (default 5 rows)

  python tracker.py bulk-renew --provider <name> --days <n>  Batch renew by filter
  python tracker.py bulk-deactivate --vm <name>              Bulk deactivate by VM
  python tracker.py bulk-reassign --from <vm> --to <vm>      Bulk reassign VMs

  python tracker.py duplicates       Detect potential duplicate plans
  python tracker.py idle             Find idle/orphaned resources
  python tracker.py history [id]     Show change history (all or specific plan)
  python tracker.py audit-log        Show recent audit log
  python tracker.py alert-history [n]  Show recent alert history (default 50)

  python tracker.py menu             Interactive menu mode

Shortcuts: ls=list, rm=delete, ren=renew, a=add, exp=export, imp=import

  python tracker.py expense add      Add a new expense
  python tracker.py expense list     Show all expenses
  python tracker.py expense edit <id> Edit an expense
  python tracker.py expense delete <id> Delete an expense

  python tracker.py help             Show this message
""")


def main():
    # Always make sure the database and table exist before doing anything
    database.init_db()

    args = sys.argv[1:]  # Everything after "python tracker.py"

    # Parse global flags before command processing
    quiet_mode = False
    silent_mode = False
    output_format = "table"  # table, json, csv
    
    # Extract and remove flags from args
    filtered_args = []
    i = 0
    while i < len(args):
        arg = args[i]
        if arg in ("--quiet", "-q"):
            quiet_mode = True
            i += 1
        elif arg == "--silent":
            silent_mode = True
            quiet_mode = True
            i += 1
        elif arg in ("--format", "-f") and i + 1 < len(args):
            output_format = args[i + 1].lower()
            i += 2
        elif arg in ("--json", "-j"):
            output_format = "json"
            i += 1
        elif arg in ("--csv"):
            output_format = "csv"
            i += 1
        else:
            filtered_args.append(arg)
            i += 1
    
    # Store in global state for command access
    global GLOBAL_STATE
    GLOBAL_STATE = {
        "quiet": quiet_mode,
        "silent": silent_mode,
        "format": output_format,
    }
    
    args = filtered_args

    if not args:
        if not silent_mode:
            print_help()
        sys.exit(0)

    # Command aliases for faster CLI workflows
    COMMAND_ALIASES = {
        'ls': 'list',
        'rm': 'delete',
        'del': 'delete',
        'ren': 'renew',
        'a': 'add',
        'exp': 'export',
        'imp': 'import',
        'sum': 'summary',
        'stat': 'stats',
        'prov': 'providers',
        'bud': 'budget',
        'fore': 'forecast',
        'hist': 'history',
        'srch': 'search',
        'sr': 'search',
        'dup': 'duplicates',
        'aud': 'audit',
        'deact': 'deactivate',
        'chk': 'check',
        'cal': 'calendar',
        '?': 'help',
        'h': 'help',
    }

    raw_command = args[0].lower()
    command = COMMAND_ALIASES.get(raw_command, raw_command)

    if command == "add":
        cmd_add()

    elif command == "list":
        show_all = len(args) > 1 and args[1].lower() == "all"
        cmd_list(show_all=show_all)

    elif command == "renew":
        if len(args) < 2:
            print("[!] Usage: python tracker.py renew <id>")
        else:
            try:
                cmd_renew(int(args[1]))
            except ValueError:
                print(f"[!] ID must be a number, got: {args[1]}")

    elif command == "edit":
        if len(args) < 2:
            print("[!] Usage: python tracker.py edit <id>")
        else:
            try:
                cmd_edit(int(args[1]))
            except ValueError:
                print(f"[!] ID must be a number, got: {args[1]}")

    elif command == "deactivate":
        if len(args) < 2:
            print("[!] Usage: python tracker.py deactivate <id>")
        else:
            try:
                cmd_deactivate(int(args[1]))
            except ValueError:
                print(f"[!] ID must be a number, got: {args[1]}")

    elif command == "restore":
        if len(args) < 2:
            print("[!] Usage: python tracker.py restore <id>")
        else:
            try:
                cmd_restore(int(args[1]))
            except ValueError:
                print(f"[!] ID must be a number, got: {args[1]}")

    elif command == "delete":
        if len(args) < 2:
            print("[!] Usage: python tracker.py delete <id>")
        else:
            try:
                cmd_delete(int(args[1]))
            except ValueError:
                print(f"[!] ID must be a number, got: {args[1]}")

    elif command == "summary":
        cmd_summary()

    elif command == "check":
        cmd_check()

    elif command == "seed":
        cmd_seed()

    elif command == "providers":
        cmd_provider_report()

    elif command == "stats":
        cmd_stats()

    elif command == "budget":
        cmd_budget_report()

    elif command == "forecast":
        try:
            months = int(args[1]) if len(args) > 1 else 6
        except ValueError:
            months = 6
        cmd_forecast(months)

    elif command == "date-range":
        if len(args) < 3:
            print("[!] Usage: python tracker.py date-range <from> <to>")
            print("    Dates: YYYY-MM-DD or DD/MM/YYYY")
        else:
            cmd_date_range_report(args[1], args[2])

    elif command == "audit":
        cmd_audit()

    elif command == "duplicates":
        cmd_duplicates()

    elif command == "idle":
        cmd_idle()

    elif command == "history":
        if len(args) > 1:
            try:
                cmd_history(int(args[1]))
            except ValueError:
                cmd_history()
        else:
            cmd_history()

    elif command == "audit-log":
        limit = 50
        if len(args) > 1:
            try:
                limit = int(args[1])
            except ValueError:
                pass
        cmd_audit_log(limit)

    elif command == "alert-history":
        limit = 50
        if len(args) > 1:
            try:
                limit = int(args[1])
            except ValueError:
                pass
        cmd_alert_history(limit)

    elif command == "menu":
        cmd_menu()

    elif command == "search":
        if len(args) < 2:
            print("[!] Usage: python tracker.py search <term>")
        else:
            cmd_search(args[1])

    elif command == "export":
        export_path = None
        export_format = "json"
        skip_next = False
        
        for i, arg in enumerate(args[1:], 1):
            if skip_next:
                skip_next = False
                continue
            if arg == "--format":
                if i + 1 < len(args):
                    export_format = args[i + 1].lower()
                    skip_next = True
            elif not arg.startswith("--") and export_path is None:
                export_path = arg
        
        cmd_export(export_path, format=export_format)

    elif command == "backup":
        backup_path = args[1] if len(args) > 1 else None
        cmd_backup(backup_path)

    elif command == "import":
        if len(args) < 2:
            print("[!] Usage: python tracker.py import <path> [--dry-run]")
        else:
            dry_run = "--dry-run" in args
            cmd_import(args[1], dry_run=dry_run)

    elif command == "calendar":
        path = None
        days = 90
        for i, arg in enumerate(args[1:], 1):
            if arg == "--days" and i + 1 < len(args):
                try:
                    days = int(args[i + 1])
                except ValueError:
                    days = 90
            elif not arg.startswith("--"):
                path = arg
        cmd_calendar(path, days)

    elif command == "template":
        try:
            rows = int(args[1]) if len(args) > 1 else 5
        except ValueError:
            rows = 5
        cmd_template(rows)

    elif command == "bulk-renew":
        # Parse arguments
        filter_provider = None
        filter_vm = None
        filter_type = None
        days = 28
        dry_run = "--dry-run" in args
        
        i = 1
        while i < len(args):
            if args[i] == "--provider" and i + 1 < len(args):
                filter_provider = args[i + 1]
                i += 2
            elif args[i] == "--vm" and i + 1 < len(args):
                filter_vm = args[i + 1]
                i += 2
            elif args[i] == "--type" and i + 1 < len(args):
                filter_type = args[i + 1]
                i += 2
            elif args[i] == "--days" and i + 1 < len(args):
                try:
                    days = int(args[i + 1])
                except ValueError:
                    pass
                i += 2
            elif args[i] == "--dry-run":
                i += 1
            else:
                i += 1
        
        if not filter_provider and not filter_vm and not filter_type:
            print("[!] Usage: python tracker.py bulk-renew --provider <name>|--vm <name>|--type <type> [--days N] [--dry-run]")
        else:
            cmd_bulk_renew(filter_provider, filter_vm, filter_type, days, dry_run)

    elif command == "bulk-deactivate":
        filter_vm = None
        filter_provider = None
        filter_type = None
        dry_run = "--dry-run" in args
        
        i = 1
        while i < len(args):
            if args[i] == "--vm" and i + 1 < len(args):
                filter_vm = args[i + 1]
                i += 2
            elif args[i] == "--provider" and i + 1 < len(args):
                filter_provider = args[i + 1]
                i += 2
            elif args[i] == "--type" and i + 1 < len(args):
                filter_type = args[i + 1]
                i += 2
            elif args[i] == "--dry-run":
                i += 1
            else:
                i += 1
        
        if not filter_vm and not filter_provider and not filter_type:
            print("[!] Usage: python tracker.py bulk-deactivate --vm <name>|--provider <name>|--type <type> [--dry-run]")
        else:
            cmd_bulk_deactivate(filter_vm, filter_provider, filter_type, dry_run)

    elif command == "bulk-reassign":
        from_vm = None
        to_vm = None
        dry_run = "--dry-run" in args
        
        i = 1
        while i < len(args):
            if args[i] == "--from" and i + 1 < len(args):
                from_vm = args[i + 1]
                i += 2
            elif args[i] == "--to" and i + 1 < len(args):
                to_vm = args[i + 1]
                i += 2
            elif args[i] == "--dry-run":
                i += 1
            else:
                i += 1
        
        if not from_vm or not to_vm:
            print("[!] Usage: python tracker.py bulk-reassign --from <vm> --to <vm> [--dry-run]")
        else:
            cmd_bulk_reassign(from_vm, to_vm, dry_run)

    elif command == "expense":
        if len(args) < 2:
            print("[!] Usage: python tracker.py expense <add|list|edit|delete>")
        else:
            subcommand = args[1].lower()
            if subcommand == "add":
                cmd_expense_add()
            elif subcommand == "list":
                show_all = len(args) > 2 and args[2].lower() == "all"
                cmd_expense_list(show_all=show_all)
            elif subcommand == "edit":
                if len(args) < 3:
                    print("[!] Usage: python tracker.py expense edit <id>")
                else:
                    try:
                        cmd_expense_edit(int(args[2]))
                    except ValueError:
                        print(f"[!] ID must be a number, got: {args[2]}")
            elif subcommand == "delete":
                if len(args) < 3:
                    print("[!] Usage: python tracker.py expense delete <id>")
                else:
                    try:
                        cmd_expense_delete(int(args[2]))
                    except ValueError:
                        print(f"[!] ID must be a number, got: {args[2]}")
            else:
                print(f"[!] Unknown expense subcommand: {subcommand}")
                print("    Valid: add, list, edit, delete")

    elif command in ("help", "--help", "-h"):
        print_help()
        sys.exit(0)

    else:
        if not is_silent():
            print(f"[!] Unknown command: {command}")
            print_help()
        sys.exit(1)


if __name__ == "__main__":
    try:
        main()
        sys.exit(0)
    except KeyboardInterrupt:
        if not is_silent():
            print("\n[!] Interrupted by user")
        sys.exit(130)
    except Exception as e:
        if not is_silent():
            print(f"\n[!] Fatal error: {e}")
        sys.exit(1)