"""
Stock Locator — standalone item-location tool
================================================
Sold separately from MediVision AI. A shop keeps whatever POS/billing
software it already uses — this tool does exactly one thing: given a
purchase-bill Excel, tell staff which shelf/box each item goes in
(e.g. "A14", "B7", box "23"), so 100s of items don't have to be typed
or hunted for by hand on every delivery.

Two building blocks, both ported from the proven MediVision AI codebase
rather than reinvented:

  1. LICENSE — hardware-fingerprint lock, same shape as MediVision AI's
     Stage-20 license system (app.py). Tied to THIS machine's
     .machine_id + node name; copying the folder to another PC fails
     the hardware check. See stock_locator_activate.py (private, not
     shipped to customers) for generating a customer's activation code.

  2. MATCHING — alias -> exact -> bounded-fuzzy ladder, ported from
     wholesaler/wanted.py's demand-matching engine, single-tenant (no
     shop_id — one shop per install). Every confirmed fuzzy match or
     manually-typed location is remembered as an alias, so the exact
     same raw name from the exact same distributor bill matches
     instantly on every later upload — the whole point is that typing
     locations gets rarer over time, not that it happens every time.
"""
import os
import io
import re
import csv
import time
import json as _json
import shutil
import zipfile
import hashlib
import sqlite3
import platform as _platform
import uuid as _uuid
from datetime import datetime
from difflib import SequenceMatcher
from urllib.request import urlopen, Request as _UrlRequest

from flask import Flask, request, jsonify, render_template, redirect
from flask_cors import CORS

APP_DIR = os.path.dirname(os.path.abspath(__file__))
DB_PATH = os.path.join(APP_DIR, "stock_locator.db")
VERSION_PATH = os.path.join(APP_DIR, "VERSION")

app = Flask(__name__)
app.secret_key = os.environ.get("SECRET_KEY", "stock-locator-local-only")
CORS(app)


def get_conn():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn


def init_db():
    with get_conn() as conn:
        conn.execute("""
            CREATE TABLE IF NOT EXISTS app_license (
                id              INTEGER PRIMARY KEY,
                license_key     TEXT NOT NULL,
                shop_name       TEXT DEFAULT '',
                owner_name      TEXT DEFAULT '',
                hw_fingerprint  TEXT NOT NULL,
                machine_code    TEXT DEFAULT '',
                activation_code TEXT DEFAULT '',
                activated_at    TEXT DEFAULT (datetime('now')),
                expires_at      TEXT DEFAULT '2099-12-31',
                tier            TEXT DEFAULT 'standard',
                status          TEXT DEFAULT 'active'
            )
        """)
        conn.execute("""
            CREATE TABLE IF NOT EXISTS location_items (
                id          INTEGER PRIMARY KEY AUTOINCREMENT,
                name        TEXT NOT NULL,
                norm_name   TEXT NOT NULL UNIQUE,
                location    TEXT DEFAULT '',
                item_code   TEXT DEFAULT '',
                created_at  TEXT DEFAULT (datetime('now')),
                updated_at  TEXT DEFAULT (datetime('now'))
            )
        """)
        conn.execute("CREATE INDEX IF NOT EXISTS idx_location_items_norm ON location_items(norm_name)")
        conn.execute("""
            CREATE TABLE IF NOT EXISTS item_aliases (
                id                INTEGER PRIMARY KEY AUTOINCREMENT,
                alias_norm        TEXT NOT NULL UNIQUE,
                alias_raw         TEXT DEFAULT '',
                location_item_id  INTEGER NOT NULL,
                hits              INTEGER DEFAULT 1,
                created_at        TEXT DEFAULT (datetime('now'))
            )
        """)
        conn.execute("""
            CREATE TABLE IF NOT EXISTS purchase_import_log (
                id              INTEGER PRIMARY KEY AUTOINCREMENT,
                filename        TEXT DEFAULT '',
                item_count      INTEGER DEFAULT 0,
                matched_count   INTEGER DEFAULT 0,
                unmatched_count INTEGER DEFAULT 0,
                imported_at     TEXT DEFAULT (datetime('now'))
            )
        """)
        conn.commit()


# ══════════════════════════════════════════════════════════════════════
#  LICENSE — hardware fingerprint + activation
#  (ported from MediVision AI's app.py Stage-20 license system)
# ══════════════════════════════════════════════════════════════════════
# Must match the same constant in stock_locator_activate.py (the private
# vendor tool). Change this before your first real sale, then never again —
# every activation code ever issued is only valid against this exact value.
OWNER_SECRET = "STOCKLOCATOR-VENDOR-2026-CHANGE-ME"


def get_hw_fingerprint():
    """Stable hardware fingerprint. Persists a machine-id file on first run
    and reuses it forever, rather than uuid.getnode() (MAC) which returns a
    random value when the real MAC can't be read — that would change the
    fingerprint on every restart and break the license."""
    try:
        id_path = os.path.join(APP_DIR, ".machine_id")
        if os.path.exists(id_path):
            with open(id_path, "r") as f:
                machine_uuid = f.read().strip()
            if machine_uuid:
                raw = f"{machine_uuid}|{_platform.node()}"
                return hashlib.sha256(raw.encode()).hexdigest()[:32]
        machine_uuid = str(_uuid.uuid4())
        try:
            with open(id_path, "w") as f:
                f.write(machine_uuid)
        except Exception:
            pass
        raw = f"{machine_uuid}|{_platform.node()}"
        return hashlib.sha256(raw.encode()).hexdigest()[:32]
    except Exception:
        return hashlib.sha256((_platform.node() or "stock-locator").encode()).hexdigest()[:32]


def get_machine_code():
    """Short machine code shown to the shop for offline activation."""
    fp = get_hw_fingerprint()
    short = hashlib.sha256(fp.encode()).hexdigest()[:16].upper()
    return "-".join(short[i:i + 4] for i in range(0, 16, 4))


def compute_activation_code(machine_code, owner_secret=OWNER_SECRET):
    raw = f"{machine_code.upper().strip()}|{owner_secret}"
    code = hashlib.sha256(raw.encode()).hexdigest()[:16].upper()
    return "-".join(code[i:i + 4] for i in range(0, 16, 4))


def check_license():
    """Returns (is_valid, license_dict or None, reason)."""
    try:
        with get_conn() as conn:
            row = conn.execute("SELECT * FROM app_license WHERE id=1").fetchone()
        if not row:
            return (False, None, "not_activated")
        lic = dict(row)
        cur_fp = get_hw_fingerprint()
        if lic["hw_fingerprint"] != cur_fp:
            return (False, lic, "hardware_mismatch")
        try:
            exp = datetime.strptime(lic["expires_at"][:10], "%Y-%m-%d")
            if exp < datetime.now():
                return (False, lic, "expired")
        except Exception:
            pass
        expected = compute_activation_code(get_machine_code())
        if lic["activation_code"] != expected:
            return (False, lic, "tampered")
        return (True, lic, "ok")
    except Exception as e:
        return (False, None, f"error:{e}")


_LICENSE_FREE_PATHS = {
    "/license", "/api/license/info", "/api/license/activate",
    "/static/",
}
_first_run_ts = {"ts": None}


@app.before_request
def _license_gate():
    p = request.path or ""
    if any(p == s or p.startswith(s) for s in _LICENSE_FREE_PATHS):
        return None
    valid, lic, reason = check_license()
    if valid:
        return None
    # 7-day grace period so a prospective buyer can try it before paying
    if reason == "not_activated":
        if _first_run_ts["ts"] is None:
            _first_run_ts["ts"] = time.time()
        elapsed_days = (time.time() - _first_run_ts["ts"]) / 86400
        if elapsed_days < 7:
            return None
    if p.startswith("/api/"):
        return jsonify({
            "error": "license_required", "reason": reason,
            "machine_code": get_machine_code(), "activate_at": "/license",
        }), 403
    return redirect("/license")


@app.route("/license")
def license_page():
    return render_template("license.html")


@app.route("/api/license/info", methods=["GET"])
def api_license_info():
    valid, lic, reason = check_license()
    machine_code = get_machine_code()
    out = {
        "valid": valid, "reason": reason, "machine_code": machine_code,
        "hardware": _platform.node(), "system": _platform.platform(),
    }
    if lic:
        out["shop_name"] = lic.get("shop_name", "")
        out["owner_name"] = lic.get("owner_name", "")
        out["activated_at"] = lic.get("activated_at", "")
        out["expires_at"] = lic.get("expires_at", "")
    if reason == "not_activated" and _first_run_ts["ts"] is not None:
        elapsed = (time.time() - _first_run_ts["ts"]) / 86400
        out["grace_days_remaining"] = max(0, round(7 - elapsed, 1))
    return jsonify(out)


@app.route("/api/license/activate", methods=["POST"])
def api_license_activate():
    d = request.get_json(silent=True) or {}
    shop_name = str(d.get("shop_name", "")).strip()
    owner_name = str(d.get("owner_name", "")).strip()
    activation_code = str(d.get("activation_code", "")).strip().upper()
    if not shop_name or not activation_code:
        return jsonify({"error": "shop_name and activation_code required"}), 400

    machine_code = get_machine_code()
    expected = compute_activation_code(machine_code)
    if activation_code != expected:
        return jsonify({"error": "Invalid activation code for this machine",
                         "machine_code": machine_code}), 400

    hw = get_hw_fingerprint()
    key = f"SL-{machine_code}-LIC"
    with get_conn() as conn:
        conn.execute("DELETE FROM app_license")
        conn.execute("""INSERT INTO app_license
            (id, license_key, shop_name, owner_name, hw_fingerprint, machine_code,
             activation_code, status, expires_at)
            VALUES (1,?,?,?,?,?,?,'active','2099-12-31')""",
            (key, shop_name, owner_name, hw, machine_code, activation_code))
        conn.commit()
    return jsonify({"status": "ok", "license_key": key, "message": "License activated successfully"})


# ══════════════════════════════════════════════════════════════════════
#  NAME MATCHING — alias -> exact -> bounded fuzzy
#  (ported from wholesaler/wanted.py, single-tenant: no shop_id)
# ══════════════════════════════════════════════════════════════════════
_NOISE = re.compile(
    r"\b(tab|tabs|tablet|tablets|cap|caps|capsule|capsules|syp|syrup|susp|suspension|"
    r"inj|injection|oint|ointment|cream|gel|drops?|sachet|strip|strips|bottle|btl|"
    r"pcs|pc|nos|no|box|pkt|packet|pack)\b", re.I)

MAX_FUZZY_CANDIDATES = 300
MIN_BUCKET_SIZE = 8


def norm(s):
    """Normalise a product name so 'Dolo 650 Tab', 'dolo-650', 'DOLO650
    tablets' all collapse to the same key. Strength (650) is kept —
    it distinguishes real products. Dosage form (tab/syrup) is dropped."""
    s = (s or "").lower().strip()
    s = re.sub(r"[^\w\s%]+", " ", s)
    s = _NOISE.sub(" ", s)
    s = re.sub(r"\s+", " ", s).strip()
    return s


def _sim(a, b):
    return SequenceMatcher(None, a, b).ratio()


def load_location_catalog(conn):
    rows = conn.execute("SELECT id, name, location, item_code FROM location_items").fetchall()
    cat = []
    for r in rows:
        d = dict(r)
        d["_norm"] = norm(r["name"])
        cat.append(d)
    return cat


def build_catalog_index(catalog):
    """Bucket items by the first 1-2 characters of their normalised name so
    fuzzy matching only scores plausible candidates, not the whole catalog —
    keeps a 300-line purchase bill fast even against a large item master."""
    exact_norm = {}
    idx2, idx1 = {}, {}
    for it in catalog:
        if it["_norm"]:
            exact_norm.setdefault(it["_norm"], it)
            idx2.setdefault(it["_norm"][:2], []).append(it)
            idx1.setdefault(it["_norm"][:1], []).append(it)
    return {"exact_norm": exact_norm, "idx2": idx2, "idx1": idx1}


def _fuzzy_candidates(n, index):
    seen = {}
    for it in index["idx2"].get(n[:2], []):
        seen[it["id"]] = it
    if len(seen) < MIN_BUCKET_SIZE:
        for it in index["idx1"].get(n[:1], []):
            seen[it["id"]] = it
    return list(seen.values())[:MAX_FUZZY_CANDIDATES]


def match_line(conn, index, raw_name):
    """Return (location_item_id, match_type, confidence, suggestions[])."""
    n = norm(raw_name)
    if not n:
        return None, "none", 0.0, []

    row = conn.execute("SELECT location_item_id FROM item_aliases WHERE alias_norm=?", (n,)).fetchone()
    if row:
        return row["location_item_id"], "alias", 1.0, []

    it = index["exact_norm"].get(n)
    if it:
        return it["id"], "exact", 1.0, []

    scored = []
    for cand in _fuzzy_candidates(n, index):
        s = _sim(n, cand["_norm"])
        if n.split()[:1] and cand["_norm"].split()[:1] and n.split()[0] == cand["_norm"].split()[0]:
            s = min(1.0, s + 0.12)  # shared leading token is a strong hint
        if s > 0.55:
            scored.append((s, cand))
    scored.sort(key=lambda x: -x[0])

    if scored and scored[0][0] >= 0.88:
        best = scored[0]
        return best[1]["id"], "fuzzy", round(best[0], 3), [
            {"id": i["id"], "name": i["name"], "location": i["location"], "score": round(s, 3)}
            for s, i in scored[:3]]
    if scored:
        return None, "fuzzy", round(scored[0][0], 3), [
            {"id": i["id"], "name": i["name"], "location": i["location"], "score": round(s, 3)}
            for s, i in scored[:3]]
    return None, "none", 0.0, []


# ══════════════════════════════════════════════════════════════════════
#  FILE PARSERS — xlsx/xls/csv/docx/dbf -> plain row lists
#  (xlsx/xls/csv/dbf ported from MediVision AI's app.py)
# ══════════════════════════════════════════════════════════════════════
def _parse_xlsx_bytes(file_bytes):
    import openpyxl
    wb = openpyxl.load_workbook(io.BytesIO(file_bytes), data_only=True)
    ws = wb.active
    return [[cell.value for cell in row] for row in ws.iter_rows()]


def _parse_xls_bytes(file_bytes):
    import xlrd
    wb = xlrd.open_workbook(file_contents=file_bytes)
    ws = wb.sheet_by_index(0)
    return [[ws.cell_value(i, j) for j in range(ws.ncols)] for i in range(ws.nrows)]


def _parse_csv_bytes(file_bytes):
    text = file_bytes.decode("utf-8-sig", errors="replace")
    return [row for row in csv.reader(io.StringIO(text))]


_DOCX_LINE_SEP = re.compile(r"\t|\s{2,}|\s+-\s+|:\s*")


def _parse_docx(file_bytes):
    """Returns (rows, has_header). A table export (item | location columns)
    has a real header row like any spreadsheet, so it's handled the same way
    as xlsx/csv. A plain-text list (one 'name — location' line per paragraph)
    has NO header — every line is data — so it must NOT be run through
    header-row detection, or the first real item gets mistaken for a header
    and silently dropped."""
    from docx import Document
    doc = Document(io.BytesIO(file_bytes))
    if doc.tables:
        table = doc.tables[0]
        return [[cell.text.strip() for cell in row.cells] for row in table.rows], True
    rows = []
    for para in doc.paragraphs:
        text = para.text.strip()
        if not text:
            continue
        parts = _DOCX_LINE_SEP.split(text, maxsplit=1)
        rows.append([p.strip() for p in parts] if len(parts) == 2 else [text])
    return rows, False


def _parse_dbf_bytes(raw: bytes):
    """Pure-Python DBF III/IV/FoxPro reader. No external deps."""
    import struct
    if len(raw) < 32:
        raise ValueError("File too small to be a valid DBF")
    buf = io.BytesIO(raw)
    hdr = buf.read(32)
    num_rec = struct.unpack_from('<I', hdr, 4)[0]
    hdr_size = struct.unpack_from('<H', hdr, 8)[0]
    rec_size = struct.unpack_from('<H', hdr, 10)[0]
    if rec_size == 0 or hdr_size < 32:
        raise ValueError("Invalid DBF header")

    fields = []
    while buf.tell() < hdr_size - 1:
        fd = buf.read(32)
        if not fd or fd[0] in (0x0D, 0x00):
            break
        name = fd[0:11].replace(b'\x00', b'').decode('ascii', errors='replace').strip()
        ftype = chr(fd[11]) if fd[11] >= 32 else 'C'
        flen = fd[16]
        if name and flen > 0:
            fields.append({'name': name.upper(), 'type': ftype, 'length': flen})

    buf.seek(hdr_size)

    def _dec(b, t):
        s = ''
        for enc in ('cp1252', 'latin-1'):
            try:
                s = b.decode(enc).strip()
                break
            except Exception:
                s = ''
        if t in ('N', 'F'):
            try:
                return round(float(s), 4) if '.' in s else (int(s) if s.lstrip('-').isdigit() else None)
            except Exception:
                return None
        if t == 'D':
            return f"{s[:4]}-{s[4:6]}-{s[6:8]}" if (len(s) == 8 and s.isdigit()) else ''
        if t == 'L':
            return s.upper() in ('T', 'Y', '1')
        return s

    records = []
    for _ in range(min(num_rec, 60000)):
        rec = buf.read(rec_size)
        if not rec or len(rec) < rec_size:
            break
        if rec[0] == 0x2A:
            continue
        row, pos = {}, 1
        for f in fields:
            row[f['name']] = _dec(rec[pos:pos + f['length']], f['type'])
            pos += f['length']
        records.append(row)
    return fields, records


def _looks_like_header(row):
    """A header row is mostly text; a data row usually has at least one
    numeric-looking cell (qty/rate/mrp). Without this check, a keyword like
    'medicine' appearing inside a DATA row's item name (e.g. "BRAND NEW
    MEDICINE XYZ") gets mistaken for the header row itself."""
    cells = [c for c in row if c not in (None, "")]
    if not cells:
        return False
    numeric = sum(1 for c in cells if isinstance(c, (int, float)) or
                  (isinstance(c, str) and re.match(r'^-?\d+(\.\d+)?$', c.strip())))
    return numeric / len(cells) < 0.4


def _detect_header_row(rows):
    for i, row in enumerate(rows[:5]):
        if not _looks_like_header(row):
            continue
        row_str = " ".join(str(c).lower() for c in row if c)
        if any(k in row_str for k in ["item name", "name", "medicine", "drug", "location", "rack",
                                       "description", "particulars", "item"]):
            return i
    return 0


# ══════════════════════════════════════════════════════════════════════
#  LOCATION-MASTER IMPORT (xlsx/xls/csv/docx/dbf -> location_items)
# ══════════════════════════════════════════════════════════════════════
_LOC_NAME_HINTS = ["item name", "itemname", "medicine", "drug", "product", "description", "particulars", "name"]
_LOC_LOCATION_HINTS = ["location", "loc", "rack", "shelf", "box", "bin", "position"]

_LOC_NAME_FIELDS = ("ITEMNAME", "NAME", "MEDNAME", "DRUGNAME", "PRODNAME", "ITEMDESC", "I_DESC",
                     "IT_DESC", "DESCRIPTION", "DESC", "ITEM")
_LOC_LOCATION_FIELDS = ("LOCN", "LOCATION", "LOC", "RACK", "SHELF", "BOX", "BIN")
_LOC_CODE_FIELDS = ("I_CODE", "ITEM_CODE", "ICODE", "ITEMCODE")


def _guess_loc_columns(headers):
    name_idx = loc_idx = None
    for i, h in enumerate(headers):
        if name_idx is None and any(k in h for k in _LOC_NAME_HINTS):
            name_idx = i
    for i, h in enumerate(headers):
        if loc_idx is None and i != name_idx and any(k in h for k in _LOC_LOCATION_HINTS):
            loc_idx = i
    if name_idx is None:
        name_idx = 0
    if loc_idx is None and len(headers) > 1:
        loc_idx = 1 if name_idx != 1 else 0
    return name_idx, loc_idx


def _rows_to_location_items(rows):
    if not rows:
        return []
    header_row = _detect_header_row(rows)
    headers = [str(c or "").strip().lower() for c in rows[header_row]]
    name_idx, loc_idx = _guess_loc_columns(headers)
    items = []
    for row in rows[header_row + 1:]:
        if not row or name_idx >= len(row):
            continue
        name = str(row[name_idx] or "").strip()
        if not name or len(name) < 2:
            continue
        loc = ""
        if loc_idx is not None and loc_idx < len(row):
            loc = str(row[loc_idx] or "").strip()
        items.append({"name": name, "location": loc, "item_code": ""})
    return items


def _rows_to_location_items_no_header(rows):
    """For sources that never have a header row (plain-text docx lines) —
    every row is data, so skip header/column detection entirely."""
    items = []
    for row in rows:
        if not row:
            continue
        name = str(row[0] or "").strip()
        if not name or len(name) < 2:
            continue
        loc = str(row[1] or "").strip() if len(row) > 1 else ""
        items.append({"name": name, "location": loc, "item_code": ""})
    return items


def _parse_location_dbf(raw):
    fields, records = _parse_dbf_bytes(raw)
    field_names = [f["name"] for f in fields]
    name_field = next((n for n in field_names if n in _LOC_NAME_FIELDS), None) or (field_names[0] if field_names else None)
    loc_field = next((n for n in field_names if n in _LOC_LOCATION_FIELDS), None)
    if not loc_field and len(field_names) > 1:
        loc_field = next((f for f in field_names if f != name_field), None)
    code_field = next((n for n in field_names if n in _LOC_CODE_FIELDS), None)

    items = []
    for row in records:
        name = str(row.get(name_field) or "").strip() if name_field else ""
        if not name or len(name) < 2:
            continue
        loc = str(row.get(loc_field) or "").strip() if loc_field else ""
        code = str(row.get(code_field) or "").strip() if code_field else ""
        items.append({"name": name, "location": loc, "item_code": code})
    return items


def parse_location_file(filename, raw):
    """Returns (items, format_label). items is a list of
    {'name','location','item_code'} dicts, blanks already dropped."""
    fname = (filename or "").lower()
    if fname.endswith(".dbf"):
        return _parse_location_dbf(raw), "DBF"
    if fname.endswith(".docx"):
        rows, has_header = _parse_docx(raw)
        items = _rows_to_location_items(rows) if has_header else _rows_to_location_items_no_header(rows)
        return items, "Word (.docx)"
    if fname.endswith(".csv"):
        return _rows_to_location_items(_parse_csv_bytes(raw)), "CSV"
    if fname.endswith(".xlsx"):
        return _rows_to_location_items(_parse_xlsx_bytes(raw)), "Excel (.xlsx)"
    if fname.endswith(".xls"):
        return _rows_to_location_items(_parse_xls_bytes(raw)), "Excel (.xls)"
    raise ValueError(
        "Unsupported file type. Use .xlsx, .xls, .csv, .docx (Word) or .dbf. "
        "Old binary .doc isn't supported — in Word, use File > Save As > .docx."
    )


# Short-lived in-memory cache so preview -> commit doesn't need a re-upload.
# Fine for a single-user local tool; no need for a DB-backed session table.
_IMPORT_CACHE = {}
_IMPORT_TTL = 30 * 60


def _cache_put(token, items):
    _IMPORT_CACHE[token] = (time.time(), items)


def _cache_get(token):
    entry = _IMPORT_CACHE.get(token)
    if not entry:
        return None
    ts, items = entry
    if time.time() - ts > _IMPORT_TTL:
        del _IMPORT_CACHE[token]
        return None
    return items


def _cache_gc():
    now = time.time()
    for k in list(_IMPORT_CACHE.keys()):
        if now - _IMPORT_CACHE[k][0] > _IMPORT_TTL:
            del _IMPORT_CACHE[k]


@app.route("/locations")
def locations_page():
    return render_template("locations.html")


@app.route("/api/locations", methods=["GET"])
def api_locations_list():
    q = (request.args.get("q") or "").strip()
    page = max(1, int(request.args.get("page", 1)))
    per_page = 50
    with get_conn() as conn:
        if q:
            rows = conn.execute(
                "SELECT * FROM location_items WHERE name LIKE ? ORDER BY name LIMIT ? OFFSET ?",
                (f"%{q}%", per_page, (page - 1) * per_page)
            ).fetchall()
            total = conn.execute(
                "SELECT COUNT(*) FROM location_items WHERE name LIKE ?", (f"%{q}%",)
            ).fetchone()[0]
        else:
            rows = conn.execute(
                "SELECT * FROM location_items ORDER BY name LIMIT ? OFFSET ?",
                (per_page, (page - 1) * per_page)
            ).fetchall()
            total = conn.execute("SELECT COUNT(*) FROM location_items").fetchone()[0]
    return jsonify({"items": [dict(r) for r in rows], "total": total, "page": page, "per_page": per_page})


@app.route("/api/locations", methods=["POST"])
def api_locations_create():
    data = request.get_json(silent=True) or {}
    name = str(data.get("name", "")).strip()
    loc = str(data.get("location", "")).strip()
    if not name:
        return jsonify({"error": "name required"}), 400
    n = norm(name)
    with get_conn() as conn:
        existing = conn.execute("SELECT id FROM location_items WHERE norm_name=?", (n,)).fetchone()
        if existing:
            conn.execute("UPDATE location_items SET location=?, updated_at=datetime('now') WHERE id=?",
                         (loc, existing["id"]))
            item_id = existing["id"]
        else:
            cur = conn.execute("INSERT INTO location_items (name, norm_name, location) VALUES (?,?,?)",
                                (name, n, loc))
            item_id = cur.lastrowid
        conn.commit()
    return jsonify({"status": "ok", "id": item_id})


@app.route("/api/locations/<int:item_id>", methods=["PUT"])
def api_locations_update(item_id):
    data = request.get_json(silent=True) or {}
    loc = str(data.get("location", "")).strip()
    name = str(data.get("name", "")).strip()
    with get_conn() as conn:
        if name:
            conn.execute(
                "UPDATE location_items SET location=?, name=?, norm_name=?, updated_at=datetime('now') WHERE id=?",
                (loc, name, norm(name), item_id))
        else:
            conn.execute("UPDATE location_items SET location=?, updated_at=datetime('now') WHERE id=?",
                         (loc, item_id))
        conn.commit()
    return jsonify({"status": "ok"})


@app.route("/api/locations/import/preview", methods=["POST"])
def api_locations_import_preview():
    if "file" not in request.files:
        return jsonify({"error": "No file provided"}), 400
    f = request.files["file"]
    raw = f.read()
    if len(raw) < 16:
        return jsonify({"error": "Empty file"}), 400
    try:
        items, fmt = parse_location_file(f.filename, raw)
    except Exception as e:
        return jsonify({"error": str(e)}), 400
    if not items:
        return jsonify({"error": "Could not find any item rows in this file"}), 400

    token = hashlib.md5((raw[:256] + str(time.time()).encode())).hexdigest()[:16]
    _cache_put(token, items)
    _cache_gc()

    with_location = sum(1 for it in items if it["location"])
    return jsonify({
        "status": "ok", "token": token, "format": fmt,
        "total_rows": len(items), "with_location": with_location,
        "sample": items[:20],
    })


@app.route("/api/locations/import/commit", methods=["POST"])
def api_locations_import_commit():
    data = request.get_json(silent=True) or {}
    token = data.get("token", "")
    items = _cache_get(token)
    if items is None:
        return jsonify({"error": "Session expired — please re-upload the file"}), 400

    created = updated = skipped = 0
    with get_conn() as conn:
        for it in items:
            name = (it.get("name") or "").strip()
            loc = (it.get("location") or "").strip()
            if not name or not loc:
                skipped += 1
                continue
            n = norm(name)
            existing = conn.execute("SELECT id, location FROM location_items WHERE norm_name=?", (n,)).fetchone()
            if existing:
                if existing["location"] != loc:
                    conn.execute(
                        "UPDATE location_items SET location=?, updated_at=datetime('now') WHERE id=?",
                        (loc, existing["id"]))
                    updated += 1
            else:
                conn.execute(
                    "INSERT INTO location_items (name, norm_name, location, item_code) VALUES (?,?,?,?)",
                    (name, n, loc, it.get("item_code", "")))
                created += 1
        conn.commit()
    _IMPORT_CACHE.pop(token, None)
    return jsonify({"status": "ok", "created": created, "updated": updated, "skipped": skipped, "total": len(items)})


# ══════════════════════════════════════════════════════════════════════
#  PURCHASE-EXCEL -> LOCATION MATCHING (the core daily-use feature)
# ══════════════════════════════════════════════════════════════════════
_PURCHASE_NAME_ALIASES = ["itemdescription", "item description", "item name", "description",
                           "particulars", "product name", "medicine name", "item"]
_PURCHASE_QTY_ALIASES = ["qnty", "qty", "quantity", "stock"]


def _find_col(headers_lower, aliases):
    for a in aliases:
        for i, h in enumerate(headers_lower):
            if a == h:
                return i
    for a in aliases:
        for i, h in enumerate(headers_lower):
            if a in h:
                return i
    return None


def parse_purchase_rows(filename, raw):
    fname = (filename or "").lower()
    if fname.endswith(".xlsx"):
        rows = _parse_xlsx_bytes(raw)
    elif fname.endswith(".xls"):
        rows = _parse_xls_bytes(raw)
    elif fname.endswith(".csv"):
        rows = _parse_csv_bytes(raw)
    else:
        raise ValueError("Unsupported format. Use .xlsx, .xls or .csv for the purchase file.")
    if not rows:
        return []
    header_row = _detect_header_row(rows)
    headers = [str(c or "").strip().lower() for c in rows[header_row]]
    name_idx = _find_col(headers, _PURCHASE_NAME_ALIASES)
    qty_idx = _find_col(headers, _PURCHASE_QTY_ALIASES)
    if name_idx is None:
        name_idx = 0

    items = []
    for row in rows[header_row + 1:]:
        if not row or name_idx >= len(row):
            continue
        name = str(row[name_idx] or "").strip()
        if not name or len(name) < 2:
            continue
        qty = 0
        if qty_idx is not None and qty_idx < len(row) and row[qty_idx] not in (None, ""):
            try:
                qty = max(0, int(float(row[qty_idx])))
            except (ValueError, TypeError):
                qty = 0
        items.append({"name": name, "qty": qty})
    return items


@app.route("/purchase")
def purchase_page():
    return render_template("purchase.html")


@app.route("/api/purchase/match", methods=["POST"])
def api_purchase_match():
    if "file" not in request.files:
        return jsonify({"error": "No file provided"}), 400
    f = request.files["file"]
    raw = f.read()
    if len(raw) < 16:
        return jsonify({"error": "Empty file"}), 400
    try:
        lines = parse_purchase_rows(f.filename, raw)
    except Exception as e:
        return jsonify({"error": str(e)}), 400
    if not lines:
        return jsonify({"error": "Could not find any item rows in this file"}), 400

    with get_conn() as conn:
        catalog = load_location_catalog(conn)
        index = build_catalog_index(catalog)
        by_id = {c["id"]: c for c in catalog}
        results = []
        matched = unmatched = 0
        for line in lines:
            location_item_id, match_type, confidence, suggestions = match_line(conn, index, line["name"])
            loc_item = by_id.get(location_item_id) if location_item_id else None
            if loc_item:
                matched += 1
            else:
                unmatched += 1
            results.append({
                "raw_name": line["name"], "qty": line["qty"],
                "match_type": match_type, "confidence": confidence,
                "location_item_id": location_item_id,
                "location": loc_item["location"] if loc_item else "",
                "matched_name": loc_item["name"] if loc_item else "",
                "suggestions": suggestions,
            })
        conn.execute(
            "INSERT INTO purchase_import_log (filename, item_count, matched_count, unmatched_count) VALUES (?,?,?,?)",
            (f.filename or "", len(lines), matched, unmatched))
        conn.commit()

    return jsonify({"status": "ok", "total": len(lines), "matched": matched,
                     "unmatched": unmatched, "results": results})


@app.route("/api/purchase/resolve", methods=["POST"])
def api_purchase_resolve():
    """Confirm a fuzzy suggestion, or save a brand-new location for a line
    that matched nothing — either way, teaches an alias so the identical raw
    name matches instantly on the next upload."""
    data = request.get_json(silent=True) or {}
    raw_name = str(data.get("raw_name", "")).strip()
    location_item_id = data.get("location_item_id")
    new_location = str(data.get("new_location", "")).strip()
    if not raw_name:
        return jsonify({"error": "raw_name required"}), 400
    n = norm(raw_name)
    if not n:
        return jsonify({"error": "raw_name normalises to empty"}), 400

    with get_conn() as conn:
        if not location_item_id:
            if not new_location:
                return jsonify({"error": "location_item_id or new_location required"}), 400
            existing = conn.execute("SELECT id FROM location_items WHERE norm_name=?", (n,)).fetchone()
            if existing:
                location_item_id = existing["id"]
                conn.execute("UPDATE location_items SET location=?, updated_at=datetime('now') WHERE id=?",
                             (new_location, location_item_id))
            else:
                cur = conn.execute("INSERT INTO location_items (name, norm_name, location) VALUES (?,?,?)",
                                    (raw_name, n, new_location))
                location_item_id = cur.lastrowid

        loc_row = conn.execute("SELECT location FROM location_items WHERE id=?", (location_item_id,)).fetchone()
        conn.execute("""INSERT INTO item_aliases (alias_norm, alias_raw, location_item_id, hits)
                        VALUES (?,?,?,1)
                        ON CONFLICT(alias_norm) DO UPDATE SET
                            location_item_id=excluded.location_item_id, hits=hits+1""",
                     (n, raw_name, location_item_id))
        conn.commit()

    return jsonify({"status": "ok", "location_item_id": location_item_id,
                    "location": loc_row["location"] if loc_row else new_location})


# ══════════════════════════════════════════════════════════════════════
#  UPDATE CHECKER — pulls a version manifest from a URL you host on your
#  own VPS. Not configured out of the box (STOCK_LOCATOR_UPDATE_URL unset)
#  so a fresh install never makes an outbound call it wasn't told to make.
#
#  Host a version.json shaped like:
#    {
#      "version": "1.1.0",
#      "notes": "Added CSV export for locations",
#      "download_url": "https://your-domain.example/stocklocator/releases/1.1.0.zip"
#    }
#  The zip must contain updated files at the SAME relative paths as this
#  folder (app.py, wsgi.py, VERSION, templates/, static/) — it's extracted
#  directly on top of the install. Do NOT include stock_locator.db,
#  .machine_id, or .env in the zip — those are per-shop data, not app code.
# ══════════════════════════════════════════════════════════════════════
UPDATE_MANIFEST_URL = os.environ.get("STOCK_LOCATOR_UPDATE_URL", "").strip()
UPDATE_TIMEOUT = 8


def get_local_version():
    try:
        with open(VERSION_PATH, "r") as f:
            return f.read().strip() or "0.0.0"
    except Exception:
        return "0.0.0"


def _version_tuple(v):
    try:
        return tuple(int(p) for p in str(v).strip().split("."))
    except Exception:
        return (0,)


def _fetch_manifest():
    req = _UrlRequest(UPDATE_MANIFEST_URL, headers={"User-Agent": "stock-locator-update-check"})
    with urlopen(req, timeout=UPDATE_TIMEOUT) as resp:
        return _json.loads(resp.read().decode("utf-8"))


@app.route("/api/update/check", methods=["GET"])
def api_update_check():
    if not UPDATE_MANIFEST_URL:
        return jsonify({"update_available": False, "reason": "not_configured"})
    try:
        manifest = _fetch_manifest()
    except Exception as e:
        return jsonify({"update_available": False, "reason": f"check_failed: {e}"})

    local_v = get_local_version()
    latest_v = str(manifest.get("version", "0.0.0"))
    available = _version_tuple(latest_v) > _version_tuple(local_v)
    return jsonify({
        "update_available": available,
        "current_version": local_v,
        "latest_version": latest_v,
        "notes": manifest.get("notes", ""),
    })


@app.route("/api/update/apply", methods=["POST"])
def api_update_apply():
    if not UPDATE_MANIFEST_URL:
        return jsonify({"error": "Updates are not configured on this install"}), 400
    try:
        manifest = _fetch_manifest()
    except Exception as e:
        return jsonify({"error": f"Could not reach update server: {e}"}), 502

    download_url = manifest.get("download_url", "")
    if not download_url:
        return jsonify({"error": "Manifest has no download_url"}), 400

    try:
        req = _UrlRequest(download_url, headers={"User-Agent": "stock-locator-update-apply"})
        with urlopen(req, timeout=60) as resp:
            zip_bytes = resp.read()
    except Exception as e:
        return jsonify({"error": f"Download failed: {e}"}), 502

    # Back up current app files BEFORE touching anything, so a bad update
    # never leaves a shop with a broken, unrecoverable install.
    backup_dir = os.path.join(APP_DIR, "backups", f"pre_update_{int(time.time())}")
    try:
        os.makedirs(backup_dir, exist_ok=True)
        for name in ("app.py", "wsgi.py", "VERSION"):
            src = os.path.join(APP_DIR, name)
            if os.path.exists(src):
                shutil.copy2(src, os.path.join(backup_dir, name))
        for folder in ("templates", "static"):
            src = os.path.join(APP_DIR, folder)
            if os.path.isdir(src):
                shutil.copytree(src, os.path.join(backup_dir, folder), dirs_exist_ok=True)
    except Exception as e:
        return jsonify({"error": f"Backup failed — aborted before changing anything: {e}"}), 500

    try:
        with zipfile.ZipFile(io.BytesIO(zip_bytes)) as zf:
            zf.extractall(APP_DIR)
    except Exception as e:
        return jsonify({"error": f"Extract failed: {e}. Your previous files are safe in {backup_dir}"}), 500

    return jsonify({"status": "ok", "message": "Updated — please restart the app.", "backup": backup_dir})


@app.route("/")
def home():
    return redirect("/purchase")


init_db()

if __name__ == "__main__":
    app.run(host="0.0.0.0", port=5050, debug=False)
