"""Data store for the Promo Calendar app — single CSV with all promos."""
from __future__ import annotations

from datetime import datetime, timedelta
from pathlib import Path
import io
from uuid import uuid4

import pandas as pd
import streamlit as st

DATA_DIR = Path(__file__).parent / "data"
DATA_DIR.mkdir(parents=True, exist_ok=True)
PROMO_FILE = DATA_DIR / "promo_calendar.csv"

# Source palette — same hex colours as the HTML preview
SOURCES = {
    "B2C — MP (retail)": "#7C6FEE",
    "B2C — WEB":         "#E8734A",
    "B2B — FMCG":        "#34D399",
    "B2B — FITNESS":     "#38BDF8",
}

# Source → originating department (who proposes it).
# Marketing owns web; Nabava (CM team) owns the rest. Used to group
# proposals by dept on the calendar dashboard.
SOURCE_TO_DEPT = {
    "B2C — MP (retail)": "Nabava",
    "B2C — WEB":         "Marketing",
    "B2B — FMCG":        "Nabava",
    "B2B — FITNESS":     "Nabava",
}
DEPTS = ["Nabava", "Marketing"]

# Per-role default source when the user adds a promo.
ROLE_DEFAULT_SOURCE = {
    "Category Manager":  "B2C — MP (retail)",
    "Marketing":         "B2C — WEB",
    "Direktor nabave":   "B2C — MP (retail)",
}
ROLES = ["Category Manager", "Marketing", "Direktor nabave"]

STATUSES = ["💡 idea", "✅ approved", "🔧 preparing", "🟢 live",
            "✓ done", "📊 analyzed"]

OUTCOMES = ["📦 Rješavanje lagera", "💰 Veći RUC",
            "👥 Novi kupci", "🚀 Traffic driver"]

TYPES = ["Univerzalna", "Kampanja", "Wholesale", "Web",
         "Loyalty", "Rok istek", "Partner / Passport",
         "Otvaranje", "Dani centra", "VISA"]

PROMO_COLS = [
    "id", "name", "source", "type", "outcome", "status",
    "start_year", "start_week", "end_year", "end_week",
    "skus", "units", "category", "owner", "notes",
    "approval_log",     # newline-separated audit trail
    "acknowledged_conflicts",  # comma-separated other-promo IDs
    "created_at", "updated_at",
]


def append_log(promo_id: str, actor: str, action: str, comment: str = "") -> bool:
    """Append a line to the promo's approval_log + bump updated_at."""
    df = load_promos()
    mask = df["id"].astype(str) == str(promo_id)
    if not mask.any():
        return False
    ts = datetime.now().strftime("%Y-%m-%d %H:%M")
    line = f"[{ts}] {actor or 'unknown'} · {action}"
    if comment:
        line += f" — {comment}"
    cur = str(df.loc[mask, "approval_log"].iloc[0] or "")
    new_log = (cur + "\n" + line).strip() if cur else line
    df.loc[mask, "approval_log"] = new_log
    df.loc[mask, "updated_at"] = ts
    save_promos(df)
    return True


@st.cache_data(ttl=10)
def load_promos() -> pd.DataFrame:
    if not PROMO_FILE.exists():
        return pd.DataFrame(columns=PROMO_COLS)
    df = pd.read_csv(PROMO_FILE)
    for c in PROMO_COLS:
        if c not in df.columns:
            df[c] = "" if c not in (
                "start_year", "start_week", "end_year", "end_week", "units"
            ) else 0
    return df[PROMO_COLS]


def save_promos(df: pd.DataFrame):
    df = df.copy()
    for c in PROMO_COLS:
        if c not in df.columns:
            df[c] = "" if c not in (
                "start_year", "start_week", "end_year", "end_week", "units"
            ) else 0
    df = df[PROMO_COLS]
    df.to_csv(PROMO_FILE, index=False)
    load_promos.clear()


def add_promo(row: dict) -> str:
    df = load_promos()
    new_id = uuid4().hex[:8]
    now = datetime.now().strftime("%Y-%m-%d %H:%M")
    actor = row.get("owner", "").strip() or "unknown"
    rec = {
        "id": new_id,
        "name": row.get("name", "").strip(),
        "source": row.get("source", ""),
        "type": row.get("type", ""),
        "outcome": row.get("outcome", ""),
        "status": row.get("status", "💡 idea"),
        "start_year": int(row.get("start_year", 0) or 0),
        "start_week": int(row.get("start_week", 0) or 0),
        "end_year": int(row.get("end_year", 0) or 0),
        "end_week": int(row.get("end_week", 0) or 0),
        "skus": row.get("skus", "").strip(),
        "units": int(row.get("units", 0) or 0),
        "category": row.get("category", "").strip(),
        "owner": row.get("owner", "").strip(),
        "notes": row.get("notes", "").strip(),
        "approval_log": f"[{now}] {actor} · Created — initial status {row.get('status', 'idea')}",
        "created_at": now,
        "updated_at": now,
    }
    df = pd.concat([df, pd.DataFrame([rec])], ignore_index=True)
    save_promos(df)
    return new_id


def update_promo(promo_id: str, fields: dict) -> bool:
    df = load_promos()
    mask = df["id"].astype(str) == str(promo_id)
    if not mask.any():
        return False
    for k, v in fields.items():
        if k in PROMO_COLS:
            df.loc[mask, k] = v
    df.loc[mask, "updated_at"] = datetime.now().strftime("%Y-%m-%d %H:%M")
    save_promos(df)
    return True


def delete_promo(promo_id: str) -> bool:
    df = load_promos()
    mask = df["id"].astype(str) == str(promo_id)
    if not mask.any():
        return False
    df = df[~mask]
    save_promos(df)
    return True


# ---------- Conflict resolution helpers ----------

def acknowledge_conflict(id_a: str, id_b: str) -> bool:
    """Mark a conflict pair as intentionally acknowledged on BOTH promos so
    detect_conflicts() will skip it. Idempotent (no dup writes)."""
    df = load_promos()
    if "acknowledged_conflicts" not in df.columns:
        df["acknowledged_conflicts"] = ""

    def _add(promo_id, other_id):
        mask = df["id"].astype(str) == str(promo_id)
        if not mask.any():
            return
        cur = str(df.loc[mask, "acknowledged_conflicts"].iloc[0] or "")
        ack_set = {x.strip() for x in cur.split(",") if x.strip()}
        ack_set.add(str(other_id))
        df.loc[mask, "acknowledged_conflicts"] = ",".join(sorted(ack_set))

    _add(id_a, id_b)
    _add(id_b, id_a)
    save_promos(df)
    return True


def exclude_skus_from_promo(promo_id: str, skus_to_exclude: set,
                            cat_map: dict | None = None) -> tuple[bool, list]:
    """Remove the given SKUs from a promo. If the promo targets only a
    category (no explicit SKU list), expand the category to its SKU set
    minus the excluded ones, and write it to the SKUs field. Returns
    (success, remaining_sku_list)."""
    df = load_promos()
    mask = df["id"].astype(str) == str(promo_id)
    if not mask.any():
        return False, []
    cur = df.loc[mask].iloc[0].to_dict()
    excl = {str(s).strip() for s in skus_to_exclude if str(s).strip()}

    current_skus = [s.strip() for s in str(cur.get("skus", "") or "").split(",") if s.strip()]
    if not current_skus:
        cat = str(cur.get("category", "") or "").strip()
        if cat and cat_map and cat in cat_map:
            current_skus = sorted(cat_map[cat])

    remaining = [s for s in current_skus if s not in excl]
    df.loc[mask, "skus"] = ", ".join(remaining)
    df.loc[mask, "updated_at"] = datetime.now().strftime("%Y-%m-%d %H:%M")
    save_promos(df)
    return True, remaining


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

def yw_to_int(year: int, week: int) -> int:
    """Encode (year, week) as comparable int."""
    return int(year) * 100 + int(week)


def parse_week_range(text: str) -> tuple[int, int]:
    """`'20-23'` → (20, 23). `'21'` → (21, 21)."""
    text = (text or "").strip()
    if not text:
        return (0, 0)
    if "-" in text:
        a, b = text.split("-", 1)
        return (int(a.strip()), int(b.strip()))
    return (int(text), int(text))


def cw_label(year: int, week: int) -> str:
    return f"CW{int(week)}"


def horizon_weeks(weeks_back: int = 4, weeks_fwd: int = 13) -> list[tuple[int, int]]:
    iso = datetime.now().isocalendar()
    start = datetime.strptime(f"{iso[0]}-W{iso[1]:02d}-1", "%G-W%V-%u")
    out = []
    for j in range(-weeks_back, weeks_fwd + 1):
        d = start + timedelta(days=7 * j)
        i = d.isocalendar()
        out.append((int(i[0]), int(i[1])))
    return out


@st.cache_data(ttl=60)
def load_category_to_skus() -> dict:
    """Return {category_name: set(sku_str)} from the main project's
    sku_category_map.csv. Used to expand a category-only promo proposal
    into the implied SKU list for conflict detection."""
    main_data = Path(__file__).parent.parent / "data" / "sku_category_map.csv"
    out: dict[str, set] = {}
    if not main_data.exists():
        return out
    try:
        df = pd.read_csv(main_data)
        df.columns = [str(c).strip().lower() for c in df.columns]
        if "sku" in df.columns and "category" in df.columns:
            for _, r in df.iterrows():
                cat = str(r.get("category", "") or "").strip()
                sku = str(r.get("sku", "") or "").strip()
                if cat and sku:
                    out.setdefault(cat, set()).add(sku)
    except Exception:
        pass
    return out


def _resolve_skus(row: dict, cat_map: dict) -> tuple[set, bool]:
    """Return (sku_set, from_category). If the promo has explicit SKUs,
    use them. Otherwise expand the category to its SKU set."""
    skus = {s.strip() for s in str(row.get("skus", "")).split(",") if s.strip()}
    if skus:
        return skus, False
    cat = str(row.get("category", "") or "").strip()
    if cat and cat in cat_map:
        return set(cat_map[cat]), True
    return set(), False


def detect_conflicts(df: pd.DataFrame) -> list[dict]:
    """Find SKU-level conflicts between promos with overlapping weeks
    AND different sources. Catches:
      • SKU overlap — both promos list the same SKU
      • Category vs SKU — one promo targets a category that contains
        the other promo's SKU (e.g. marketing 10% on cat 'cokoladice'
        + nabava 15% on one chocolate in that category)
    Returns list of conflict dicts (one per overlapping SKU, per pair)."""
    if df is None or len(df) == 0:
        return []
    cat_map = load_category_to_skus()
    rows = df.to_dict("records")
    enriched = []
    for i, r in enumerate(rows):
        s_yw = yw_to_int(r.get("start_year", 0), r.get("start_week", 0))
        e_yw = yw_to_int(r.get("end_year", 0), r.get("end_week", 0))
        if e_yw < s_yw or s_yw == 0:
            continue
        # Skip rejected/done — only live, pending, approved should conflict
        status = str(r.get("status", "") or "").lower()
        if "done" in status or "analyzed" in status:
            continue
        sku_set, from_cat = _resolve_skus(r, cat_map)
        ack_raw = str(r.get("acknowledged_conflicts", "") or "")
        ack_set = {x.strip() for x in ack_raw.split(",") if x.strip()}
        enriched.append({
            "i": i, "id": str(r.get("id", "")),
            "name": r["name"], "source": r["source"],
            "status": r.get("status", ""),
            "category": str(r.get("category", "") or "").strip(),
            "start": s_yw, "end": e_yw,
            "sw": int(r.get("start_week", 0)),
            "ew": int(r.get("end_week", 0)),
            "sy": int(r.get("start_year", 0)),
            "skus": sku_set,
            "from_category": from_cat,
            "ack": ack_set,
        })
    conflicts = []
    seen_pairs = set()
    for i in range(len(enriched)):
        for j in range(i + 1, len(enriched)):
            a, b = enriched[i], enriched[j]
            if a["start"] > b["end"] or b["start"] > a["end"]:
                continue
            if a["source"] == b["source"]:
                continue
            # Skip pairs that one side already acknowledged as intentional
            if b["id"] in a["ack"] or a["id"] in b["ack"]:
                continue
            overlap_skus = a["skus"] & b["skus"]
            if not overlap_skus:
                continue
            kind = "sku_overlap"
            if a["from_category"] and not b["from_category"]:
                kind = "category_vs_sku"
            elif b["from_category"] and not a["from_category"]:
                kind = "category_vs_sku"
            elif a["from_category"] and b["from_category"]:
                kind = "category_vs_category"
            ov_sw = max(a["sw"], b["sw"])
            ov_ew = min(a["ew"], b["ew"])
            ov_y = max(a["sy"], b["sy"])
            for sku in sorted(overlap_skus):
                key = (sku, a["i"], b["i"])
                if key in seen_pairs:
                    continue
                seen_pairs.add(key)
                conflicts.append({
                    "sku": sku,
                    "kind": kind,
                    "weeks": f"CW{ov_sw}" + (f"–CW{ov_ew}" if ov_ew > ov_sw else ""),
                    "year": ov_y,
                    "id_a": a["id"], "id_b": b["id"],
                    "source_a": a["source"], "name_a": a["name"],
                    "dept_a": SOURCE_TO_DEPT.get(a["source"], "?"),
                    "source_b": b["source"], "name_b": b["name"],
                    "dept_b": SOURCE_TO_DEPT.get(b["source"], "?"),
                    "category_a": a["category"] if a["from_category"] else "",
                    "category_b": b["category"] if b["from_category"] else "",
                })
    return conflicts


def import_excel(uploaded_bytes: bytes) -> dict:
    """Try to import promos from any uploaded Excel.
    Smart column detection: looks for headers matching known synonyms
    in the first 5 rows, picks them up case-insensitively."""
    out = {"added": 0, "skipped": 0, "errors": []}
    try:
        df = pd.read_excel(io.BytesIO(uploaded_bytes))
    except Exception as e:
        out["errors"].append(f"Could not read Excel: {e}")
        return out
    df.columns = [str(c).strip().lower() for c in df.columns]

    aliases = {
        "name":       {"name", "promo", "naziv", "promotion", "kampanja"},
        "source":     {"source", "izvor", "channel"},
        "type":       {"type", "tip", "category_type"},
        "outcome":    {"outcome", "cilj", "objective"},
        "status":     {"status", "stanje"},
        "skus":       {"skus", "sku", "artikli", "products"},
        "category":   {"category", "kategorija"},
        "start_week": {"start_week", "start", "from", "od_tjedna", "pocetak"},
        "end_week":   {"end_week", "end", "to", "do_tjedna", "kraj"},
        "units":      {"units", "qty", "kolicina", "quantity"},
        "owner":      {"owner", "vlasnik", "responsible"},
        "notes":      {"notes", "biljeske", "napomena"},
    }
    col_map = {}
    for canon, names in aliases.items():
        for c in df.columns:
            if c in names:
                col_map[canon] = c
                break

    if "name" not in col_map:
        out["errors"].append(
            "Missing required column 'name' (or one of its aliases: "
            "promo, naziv, promotion).")
        return out

    iso = datetime.now().isocalendar()
    cy = int(iso[0])

    for _, row in df.iterrows():
        try:
            name = str(row.get(col_map["name"], "")).strip()
            if not name or name.lower() in ("nan", "none"):
                continue
            sw, ew = 0, 0
            if "start_week" in col_map:
                sw = int(float(row.get(col_map["start_week"], 0) or 0))
            if "end_week" in col_map:
                ew = int(float(row.get(col_map["end_week"], 0) or 0))
            if ew < sw:
                ew = sw
            rec = {
                "name": name,
                "source": str(row.get(col_map.get("source", ""), "B2C — MP (retail)") or "B2C — MP (retail)"),
                "type": str(row.get(col_map.get("type", ""), "Univerzalna") or "Univerzalna"),
                "outcome": str(row.get(col_map.get("outcome", ""), "") or ""),
                "status": str(row.get(col_map.get("status", ""), "💡 idea") or "💡 idea"),
                "start_year": cy if sw else 0,
                "start_week": sw,
                "end_year": cy if ew else 0,
                "end_week": ew,
                "skus": str(row.get(col_map.get("skus", ""), "") or ""),
                "units": int(float(row.get(col_map.get("units", ""), 0) or 0)),
                "category": str(row.get(col_map.get("category", ""), "") or ""),
                "owner": str(row.get(col_map.get("owner", ""), "") or ""),
                "notes": str(row.get(col_map.get("notes", ""), "") or ""),
            }
            add_promo(rec)
            out["added"] += 1
        except Exception as e:
            out["skipped"] += 1
            out["errors"].append(f"Row '{name[:30]}…' failed: {e}")
    return out