"""
build_erp_promo.py - Convert ERP rabatne.xlsx to erp_promo_calendar.csv
Usage:  python build_erp_promo.py [path_to_rabatne.xlsx]
Output: erp_promo_calendar.csv in the same directory as the script

Reads promotion data exported from the Gath ERP system and converts date ranges
to ISO calendar weeks, filtering out non-promo types (headers, permanent pricing,
test entries, loyalty programs, and external channel deals).

The resulting CSV is used by forecast_engine.py to replace/supplement statistical
promo detection with ground-truth ERP promo flags.
"""

import sys, os, csv
from datetime import datetime, timedelta
from collections import defaultdict

# Fix Windows console encoding for Croatian characters
if sys.stdout.encoding != 'utf-8':
    sys.stdout.reconfigure(encoding='utf-8', errors='replace')

try:
    import openpyxl
except ImportError:
    print("ERROR: openpyxl not installed. Run: pip install openpyxl")
    sys.exit(1)

# ---------- Configuration ----------

# Promo types to EXCLUDE (not real temporary promos)
EXCLUDE_TYPES = {
    # Permanent / ongoing pricing
    'TRAJNA HR', 'TRAJNA 08/01', 'C4 TRAJNO', 'GADGETI TRAJNO',
    'HUAWEI TRAJNO', 'POLAR, GOPRO TR', 'REHBAND TRAJNA',
    # Test entries
    'TEST 2026', 'Test VP Sanja', 'test 1+-50', 'test loy 40', 'test pulse nis',
    # Loyalty / membership (store-wide, not real promos)
    'POLLEO20',
    # External channels (not our retail/wholesale)
    'LIDL HRVATSKA', 'KONZUM', 'BIPA',
    # Internal / HR
    'VJEKO ZAPOSL',
    # Slovenia-only tiers (if not in our plan scope)
    'LVL1 SLO', 'LVL2 SLO', 'LVL3 SLO', 'LVL4 SLO', 'LVL5 SLO',
}

# Also exclude any type containing these substrings (case-insensitive)
EXCLUDE_SUBSTRINGS = ['TRAJN']  # catches any future "TRAJNA" variants


def should_exclude(vrsta):
    """Check if promo type should be excluded."""
    v = vrsta.strip()
    if v in EXCLUDE_TYPES:
        return True
    for sub in EXCLUDE_SUBSTRINGS:
        if sub.upper() in v.upper():
            return True
    return False


def get_weeks_in_range(d_from, d_to):
    """Return set of (year, week) tuples covered by a date range."""
    weeks = set()
    current = d_from
    while current <= d_to:
        iso = current.isocalendar()
        weeks.add((iso[0], iso[1]))
        current += timedelta(days=1)
    return weeks


def main():
    # Determine input file
    if len(sys.argv) > 1:
        xlsx_path = sys.argv[1]
    else:
        xlsx_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'rabatne.xlsx')

    # Determine output path (optional second argument)
    if len(sys.argv) > 2:
        out_path = sys.argv[2]
    else:
        # Default: data/ subdir if it exists, otherwise script directory
        data_dir = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'data')
        if os.path.isdir(data_dir):
            out_path = os.path.join(data_dir, 'erp_promo_calendar.csv')
        else:
            out_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'erp_promo_calendar.csv')

    if not os.path.exists(xlsx_path):
        print(f"ERROR: File not found: {xlsx_path}")
        sys.exit(1)

    print(f"Reading: {xlsx_path}")
    wb = openpyxl.load_workbook(xlsx_path, read_only=True)
    ws = wb[wb.sheetnames[0]]

    # Parse rows
    total = 0
    skipped_no_sku = 0
    skipped_excluded = 0
    skipped_bad_date = 0
    promos = []

    for r in ws.iter_rows(min_row=2, values_only=True):
        total += 1
        vrsta, code, opis, rabat, od, do_ = r[0], r[1], r[2], r[3], r[4], r[5]

        # Skip rows without SKU (header/group rows)
        if code is None:
            skipped_no_sku += 1
            continue

        vrsta_str = str(vrsta).strip()

        # Skip excluded types
        if should_exclude(vrsta_str):
            skipped_excluded += 1
            continue

        # Parse dates
        try:
            date_from = datetime.strptime(str(od).strip(), '%d.%m.%Y')
            date_to = datetime.strptime(str(do_).strip(), '%d.%m.%Y')
        except (ValueError, AttributeError):
            skipped_bad_date += 1
            continue

        promos.append({
            'sku': str(code).strip(),
            'promo_type': vrsta_str,
            'date_from': date_from,
            'date_to': date_to,
        })

    wb.close()
    print(f"  Total rows: {total:,}")
    print(f"  Skipped (no SKU): {skipped_no_sku:,}")
    print(f"  Skipped (excluded type): {skipped_excluded:,}")
    print(f"  Skipped (bad dates): {skipped_bad_date:,}")
    print(f"  Valid promo entries: {len(promos):,}")

    # Convert to calendar weeks
    promo_calendar = defaultdict(lambda: defaultdict(set))
    for p in promos:
        weeks = get_weeks_in_range(p['date_from'], p['date_to'])
        for yw in weeks:
            promo_calendar[p['sku']][yw].add(p['promo_type'])

    # Write CSV
    rows_out = []
    for sku in sorted(promo_calendar.keys()):
        for (year, week) in sorted(promo_calendar[sku].keys()):
            types = promo_calendar[sku][(year, week)]
            rows_out.append({
                'sku': sku,
                'year': year,
                'week': week,
                'promo_types': '; '.join(sorted(types)),
                'is_erp_promo': 1,
            })

    with open(out_path, 'w', newline='', encoding='utf-8') as f:
        writer = csv.DictWriter(f, fieldnames=['sku', 'year', 'week', 'promo_types', 'is_erp_promo'])
        writer.writeheader()
        writer.writerows(rows_out)

    unique_skus = len(promo_calendar)
    unique_weeks = len(set((r['year'], r['week']) for r in rows_out))
    print(f"\nOutput: {out_path}")
    print(f"  {len(rows_out):,} promo-week rows")
    print(f"  {unique_skus:,} unique SKUs")
    print(f"  {unique_weeks} unique calendar weeks")

    # Show date coverage
    all_yw = [(r['year'], r['week']) for r in rows_out]
    if all_yw:
        print(f"  Range: {min(all_yw)[0]}-CW{min(all_yw)[1]:02d} -> {max(all_yw)[0]}-CW{max(all_yw)[1]:02d}")


if __name__ == '__main__':
    main()