import uuid
from pathlib import Path
from typing import Optional, Mapping

from pyexcel import get_book

from ee.money import Money
from ee.part import Part, PartDb
from ee.xml import types

__all__ = [
    "from_excel",
]


def from_excel(path: Path, sheet_name: Optional[str]) -> PartDb:
    wb = get_book(file_name=str(path))

    db = PartDb()

    sheet = wb.sheet_by_index(0) if sheet_name is None else wb.sheet_by_name(sheet_name)

    sheet = list(sheet)

    header: Mapping[str, int] = {name.lower(): idx for idx, name in enumerate(sheet[0]) if len(name.strip())}

    mpn_idx = header.get("mpn", None)
    desc_idx = header.get("description", None)
    price_idx = header.get("price", None)
    url_idx = header.get("url", None)

    for idx, row in enumerate(sheet[1:], 1):
        mpn = row[mpn_idx] if mpn_idx is not None else ""
        desc = row[desc_idx].strip() if desc_idx is not None else ""
        price = row[price_idx] if price_idx is not None else None
        url = row[url_idx] if url_idx is not None else None

        if len(mpn) == 0 and len(desc) == 0:
            print("Bad part, line #{}. MPN or description is required".format(idx))
            continue

        uri = "urn:uuid:{}".format(uuid.uuid5(uuid.NAMESPACE_URL, url))
        part = Part(types.Part(uri=uri))

        if mpn:
            part.add_mpn(mpn)

        part.xml.descriptionProp = desc

        if price:
            part.add_price_break(1, Money(price))
        if url:
            part.get_links().append(types.Link(url=url, relation="canonical"))

        db.add_entry(part, False)

    return db