"""
build_detailed_sales.py - Preserve full ERP granularity for analytics.
=====================================================================
Reads the same Rekapitulacija upload files as update_sales.py and writes
data/sales_detailed.csv with one row per ERP transaction line. Unlike
sales_clean.csv (SKU x week aggregate), this file keeps customer, store,
document, daily granularity, sales rep, manufacturer and discount details.

APPEND-SAFE: re-running with the same files does not duplicate — rows are
keyed by (dokument, sku, date, source_country) and dropped if already
present in the existing sales_detailed.csv.

Inputs:  data/upload_Rekapitulacija*.xlsx (CRO uses "€" currency suffix;
         AT and SLO use "EUR" suffix in column names)
Output:  data/sales_detailed.csv
Usage:   python build_detailed_sales.py
         (also chained automatically at the end of update_sales.py)

NOTE: This file is for analytics only. It does NOT feed the forecasting
pipeline. sales_clean.csv remains the single source of truth for forecasts.
"""

import io
import os
import re
import sys
from datetime import datetime
from pathlib import Path

import openpyxl
import pandas as pd

# Fix Windows console encoding for Croatian / Slovenian / German characters
if sys.stdout.encoding and sys.stdout.encoding.lower() not in ('utf-8', 'utf8'):
    try:
        sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding='utf-8', errors='replace')
        sys.stderr = io.TextIOWrapper(sys.stderr.buffer, encoding='utf-8', errors='replace')
    except Exception:
        pass

BASE_DIR = Path(os.path.dirname(os.path.abspath(__file__)))
# Match update_sales.py / recalc_uplift_erp.py layout detection
DATA_DIR = BASE_DIR / 'data'
if not DATA_DIR.exists() or not (DATA_DIR / 'sales_clean.csv').exists():
    DATA_DIR = BASE_DIR

OUT_PATH = DATA_DIR / 'sales_detailed.csv'

# ---------- Country detection from filename ----------
# Croatian template = "RekapitulacijaSveUkupno*" (always CRO).
# Slovenian template = "RekapitulacijaVsegaSkupaj*" (used for both AT and SLO).
# Specific country is appended either concatenated ("...Skupajat.xlsx") or
# delimited ("...Skupaj__at_..." / "..._SLO_...").
def detect_country(fname: str) -> str:
    f = fname.lower()
    if 'skupajat' in f:
        return 'at'
    if 'skupajslo' in f:
        return 'slo'
    if 'sveukupnocro' in f or 'sveukupnohrv' in f:
        return 'cro'
    if re.search(r'(?:^|[_\W])at(?:[_\W.]|$)', f):
        return 'at'
    if re.search(r'(?:^|[_\W])slo(?:[_\W.]|$)', f):
        return 'slo'
    if re.search(r'(?:^|[_\W])(?:cro|hrv)(?:[_\W.]|$)', f):
        return 'cro'
    # Templates with no explicit country marker — fall back on template default
    if 'sveukupno' in f:
        return 'cro'
    if 'vsegaskupaj' in f:
        return 'slo'
    return 'unknown'


# ---------- Header alias -> canonical column mapping ----------
# Lookup uses str(header_cell).strip().lower(). Each canonical name maps
# 1+ source-language variants. Add new aliases here if a template changes.
COLUMN_ALIASES = {
    'dokument': 'dokument',
    'datum': 'datum',
    'partner': 'partner',
    'naziv partnera': 'naziv_partnera',
    'artikal': 'sku',
    'artikl': 'sku',
    'naziv': 'naziv',
    'proizvođač': 'proizvodjac',
    'proizvodač': 'proizvodjac',
    'proizvodac': 'proizvodjac',
    'mj.troška': 'mj_troska',
    'mj.troska': 'mj_troska',
    'mjesto troška': 'mj_troska',
    'naziv mjesta troška': 'naziv_mj_troska',
    'naziv mjesta troska': 'naziv_mj_troska',
    'jedinica': 'jedinica',
    'naziv jedinice': 'naziv_jedinice',
    'tip dok.': 'tip_dok',
    'tip dok': 'tip_dok',
    'kategorija artikla': 'kategorija_artikla',
    'grupacija artikla': 'grupacija_artikla',
    'naziv grupacije': 'naziv_grupacije',
    'podkat': 'podkat',
    'podkategorija': 'podkategorija',
    'komercijalist': 'komercijalist',
    'količina': 'kolicina',
    'kolicina': 'kolicina',
    # CRO uses "€", AT/SLO use "EUR" — both map to the same canonical column.
    'nabavna vrijednost €': 'nabavna_vrijednost_eur',
    'nabavna vrijednost eur': 'nabavna_vrijednost_eur',
    'ruc €': 'ruc_eur',
    'ruc eur': 'ruc_eur',
    '% ruc': 'ruc_pct',
    'porezna osnovica €': 'porezna_osnovica_eur',
    'porezna osnovica eur': 'porezna_osnovica_eur',
    'pdv €': 'pdv_eur',
    'pdv eur': 'pdv_eur',
    'vrijednost €': 'vrijednost_eur',
    'vrijednost eur': 'vrijednost_eur',
    'odobreni rabat €': 'odobreni_rabat_eur',
    'odobreni rabat eur': 'odobreni_rabat_eur',
    'država': 'drzava',
    'drzava': 'drzava',
    # Loyalty card ID — populated when the customer used a loyalty card. A
    # digit string = card ID, "nonLoyalty"/empty = no card. We carry through
    # as raw text and derive a boolean flag `has_loyalty` at DB-load time
    # (db/migrate_sales.py).
    'loyalty kartica': 'loyalty_kartica',
    'loyalty card': 'loyalty_kartica',
}

# Required columns — abort a file if any of these are missing.
REQUIRED_CANON = {'dokument', 'datum', 'sku'}

# Output column order (stable for downstream analytics).
OUTPUT_COLS = [
    'source_country', 'source_file', 'date', 'year', 'week',
    'dokument', 'partner', 'naziv_partnera',
    'sku', 'naziv', 'proizvodjac',
    'mj_troska', 'naziv_mj_troska', 'jedinica', 'naziv_jedinice',
    'tip_dok', 'kategorija_artikla', 'grupacija_artikla', 'naziv_grupacije',
    'podkat', 'podkategorija', 'komercijalist',
    'kolicina', 'nabavna_vrijednost_eur', 'ruc_eur', 'ruc_pct',
    'porezna_osnovica_eur', 'pdv_eur', 'vrijednost_eur',
    'odobreni_rabat_eur', 'loyalty_kartica', 'drzava',
]


def find_rekap_files():
    """Locate Rekapitulacija transaction-line files.

    Priority (mirrors update_sales.find_update_files()):
      1. Files passed via sys.argv that look like Rekapitulacija templates.
         This is the path used when chained from update_sales.py — the
         user-uploaded files are forwarded via argv.
      2. Glob in DATA_DIR for canonical templates only:
           - *SveUkupno*   (Croatian, retail+wholesale, currency suffix "€")
           - *VsegaSkupaj* (Slovenian, used for AT and SLO, suffix "EUR")
         The bare "Rekapitulacija*.xlsx" pattern is intentionally NOT used
         here — it sweeps in unrelated reports (e.g. Rekapitulacijazaakciju
         which is a promo summary without Partner data).
    """
    def _is_rekap_name(name: str) -> bool:
        n = name.lower()
        return ('sveukupno' in n or 'vsegaskupaj' in n) and not name.startswith('~')

    # 1. argv
    argv_files = []
    for a in sys.argv[1:]:
        if a.startswith('--') or not a.lower().endswith(('.xlsx', '.xls')):
            continue
        p = Path(a)
        if p.exists() and _is_rekap_name(p.name):
            argv_files.append(p)
    if argv_files:
        # Dedupe preserving order
        seen, out = set(), []
        for f in argv_files:
            if f.name not in seen:
                seen.add(f.name)
                out.append(f)
        return out

    # 2. glob in DATA_DIR
    seen, out = set(), []
    for pat in ['upload_RekapitulacijaSveUkupno*.xlsx',
                'upload_RekapitulacijaVsegaSkupaj*.xlsx',
                'RekapitulacijaSveUkupno*.xlsx',
                'RekapitulacijaVsegaSkupaj*.xlsx']:
        for f in DATA_DIR.glob(pat):
            if not _is_rekap_name(f.name):
                continue
            if f.name not in seen:
                seen.add(f.name)
                out.append(f)
    return out


def read_one_file(fp: Path):
    """Parse one Rekapitulacija xlsx into a DataFrame with OUTPUT_COLS schema.
    Returns None on empty file."""
    print(f'  Reading {fp.name}...')
    wb = openpyxl.load_workbook(fp, read_only=True, data_only=True)
    sheet = wb.sheetnames[0]
    ws = wb[sheet]

    rows_iter = ws.iter_rows(values_only=True)
    header = None
    for row in rows_iter:
        if row and any(c == 'Dokument' for c in row):
            header = list(row)
            break
    if header is None:
        wb.close()
        raise ValueError(f'No header row containing "Dokument" in {fp.name}')

    # Map source-col index -> canonical name. Stops on first match to avoid
    # collisions if the same canonical comes from two aliases.
    idx_map = {}
    seen_canon = set()
    for i, h in enumerate(header):
        if h is None:
            continue
        canon = COLUMN_ALIASES.get(str(h).strip().lower())
        if canon and canon not in seen_canon:
            idx_map[i] = canon
            seen_canon.add(canon)

    missing = REQUIRED_CANON - seen_canon
    if missing:
        wb.close()
        raise ValueError(f'Missing required columns {missing} in {fp.name}')

    data_rows = []
    for row in rows_iter:
        if row is None:
            continue
        rec = {idx_map[i]: row[i] for i in idx_map if i < len(row)}
        if not rec.get('dokument'):
            continue
        data_rows.append(rec)
    wb.close()

    if not data_rows:
        return None
    df = pd.DataFrame(data_rows)

    # Normalize date -> ISO year/week + date string
    df['datum'] = pd.to_datetime(df['datum'], errors='coerce')
    df = df[df['datum'].notna()].copy()
    if len(df) == 0:
        return None
    iso = df['datum'].dt.isocalendar()
    df['year'] = iso['year'].astype(int)
    df['week'] = iso['week'].astype(int)
    df['date'] = df['datum'].dt.strftime('%Y-%m-%d')
    df = df.drop(columns=['datum'])

    df['source_country'] = detect_country(fp.name)
    df['source_file'] = fp.name

    # Backfill any output columns absent in this file with NA
    for c in OUTPUT_COLS:
        if c not in df.columns:
            df[c] = pd.NA

    df = df[OUTPUT_COLS]
    n = len(df)
    if n:
        print(f'    {n:,} rows | country={df["source_country"].iloc[0]} | '
              f'dates {df["date"].min()}-{df["date"].max()}')
    return df


def main():
    print(f"\n{'='*60}")
    print(f'  POLLEO DETAILED SALES BUILD')
    print(f'  {datetime.now().strftime("%Y-%m-%d %H:%M")}')
    print(f'{"="*60}')

    files = find_rekap_files()
    if not files:
        print(f'  No Rekapitulacija upload files found in {DATA_DIR}')
        return

    print(f'  Found {len(files)} file(s)')
    parsed = []
    for fp in files:
        try:
            df = read_one_file(fp)
            if df is not None and len(df) > 0:
                parsed.append(df)
        except Exception as e:
            print(f'  WARN: skipping {fp.name}: {e}')

    if not parsed:
        print('  No valid data parsed. Exiting.')
        return

    new_df = pd.concat(parsed, ignore_index=True)
    # Within-batch dedupe (same line could appear in two uploads e.g. partial
    # re-exports of overlapping weeks).
    key_cols = ['dokument', 'sku', 'date', 'source_country']
    before = len(new_df)
    new_df = new_df.drop_duplicates(subset=key_cols, keep='first')
    if len(new_df) < before:
        print(f'  Within-batch dedupe: {before:,} -> {len(new_df):,}')

    if OUT_PATH.exists():
        existing = pd.read_csv(
            OUT_PATH,
            dtype={'sku': str, 'dokument': str, 'date': str,
                   'partner': str, 'source_country': str},
            encoding='utf-8',
        )
        print(f'  Existing sales_detailed.csv: {len(existing):,} rows')

        # Vectorised dedupe: build a tuple-key column on both sides and isin().
        ex_key = (existing['dokument'].astype(str) + '|' +
                  existing['sku'].astype(str) + '|' +
                  existing['date'].astype(str) + '|' +
                  existing['source_country'].astype(str))
        existing_keys = set(ex_key.tolist())

        nd_key = (new_df['dokument'].astype(str) + '|' +
                  new_df['sku'].astype(str) + '|' +
                  new_df['date'].astype(str) + '|' +
                  new_df['source_country'].astype(str))
        keep_mask = ~nd_key.isin(existing_keys)
        before_dedupe = len(new_df)
        new_df = new_df[keep_mask]
        skipped = before_dedupe - len(new_df)
        if skipped:
            print(f'  Skipped {skipped:,} duplicates already on file')

        if len(new_df) == 0:
            print('  Nothing new to append. Done.')
            return

        new_df.to_csv(OUT_PATH, mode='a', header=False, index=False, encoding='utf-8')
        total = len(existing) + len(new_df)
    else:
        new_df.to_csv(OUT_PATH, index=False, encoding='utf-8')
        total = len(new_df)
        print('  Created new sales_detailed.csv')

    print(f'\n  Appended {len(new_df):,} rows | sales_detailed.csv now {total:,} rows total')


if __name__ == '__main__':
    try:
        main()
    except Exception as e:
        print(f'\n  ERROR: {e}')
        import traceback
        traceback.print_exc()