import sys
import os
import openpyxl
import xml.etree.ElementTree as ET
from xml.dom import minidom


def sanitize_tag(name):
    tag = "".join(c if c.isalnum() or c == "_" else "_" for c in str(name))
    if not tag or tag[0].isdigit():
        tag = "_" + tag
    return tag


def xlsx_to_xml(xlsx_path, xml_path, sheet_index=1, has_header=True):
    wb = openpyxl.load_workbook(xlsx_path, data_only=True)
    ws = wb.worksheets[sheet_index - 1]  # 1-based

    rows = list(ws.iter_rows(values_only=True))
    if not rows:
        raise ValueError("Worksheet is empty")

    if has_header:
        headers = [sanitize_tag(h) if h is not None else f"col{i}"
                   for i, h in enumerate(rows[0])]
        data_rows = rows[1:]
    else:
        headers = [f"col{i}" for i in range(len(rows[0]))]
        data_rows = rows

    root = ET.Element("rows")
    for row in data_rows:
        attrs = {h: ("" if v is None else str(v)) for h, v in zip(headers, row)}
        ET.SubElement(root, "row", attrs)

    xml_str = ET.tostring(root, encoding="unicode")
    pretty = minidom.parseString(xml_str).toprettyxml(indent="  ")
    # strip <?xml ... ?> declaration line
    pretty = "\n".join(line for line in pretty.splitlines() if not line.startswith("<?xml"))
    pretty = pretty.lstrip("\n")

    with open(xml_path, "w", encoding="utf-8") as f:
        f.write(pretty)

    return pretty


SQL_TEMPLATE = """declare @xml xml =
N'
{xml}
'

--insert into wms.location (locationid,warehouseid,locationcode,locationname, rack,shelf,section,ipAddress,locationtypeid,groupid)
SELECT
    newID(),
    [dbo].[fn_decodeData](r.value('@MAGAZYN', 'varchar(20)') ,'warehouse')        ,
    r.value('@LOKACJA', 'nvarchar(50)') ,
    r.value('@LOKACJA', 'nvarchar(50)') ,
    r.value('@REGAL',   'int')          ,
    r.value('@POLKA',   'int')          ,
    r.value('@SEKCJA_', 'int')          ,
    NULLIF(r.value('@IP',      'nvarchar(50)'), ''),
    'LOCATION',
    [dbo].[fn_decodeData](r.value('@MAGAZYN', 'varchar(20)') ,'warehouse-group')   
FROM @xml.nodes('/rows/row') AS x(r)
where   r.value('@LOKACJA', 'nvarchar(50)') not in (select locationcode from wms.location)

--select * from wms.location where cast(adddate as date) = cast(getutcdate() as date)
"""


def write_sql(xml_content, sql_path):
    # escape single quotes for T-SQL string literal
    safe_xml = xml_content.replace("'", "''")
    with open(sql_path, "w", encoding="utf-8") as f:
        f.write(SQL_TEMPLATE.format(xml=safe_xml.rstrip()))


if __name__ == "__main__":
    if len(sys.argv) < 2:
        print("Usage: python xlsx_to_xml.py <file.xlsx> [sheet_index]")
        print("Generuje xml + gotowy skrypt sql")
        sys.exit(1)

    xlsx_path = sys.argv[1]
    sheet_index = int(sys.argv[2]) if len(sys.argv) > 2 else 1
    xml_path = os.path.splitext(xlsx_path)[0] + ".xml"
    sql_path = os.path.splitext(xlsx_path)[0] + ".sql"

    xml_content = xlsx_to_xml(xlsx_path, xml_path, sheet_index=sheet_index)
    write_sql(xml_content, sql_path)
    print(f"Wrote {xml_path}")
    print(f"Wrote {sql_path}")