# تبدیل اکسل شعب به branches-data.json
# این ابزار برای اجرای محلی است و روی هاست لازم نیست.
# استفاده:
# python excel-to-branches-json.py tehranalborz.xlsx branches-data.json

import json
import re
import sys
import zipfile
import xml.etree.ElementTree as ET

def read_shared_strings(z):
    ns = {'a': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main'}
    try:
        root = ET.fromstring(z.read('xl/sharedStrings.xml'))
    except KeyError:
        return []
    strings = []
    for si in root.findall('a:si', ns):
        strings.append(''.join((t.text or '') for t in si.findall('.//a:t', ns)))
    return strings

def col_to_idx(cell_ref):
    m = re.match(r'([A-Z]+)', cell_ref or '')
    if not m:
        return 0
    idx = 0
    for ch in m.group(1):
        idx = idx * 26 + ord(ch) - 64
    return idx - 1

def read_sheet(z):
    ns = {'a': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main'}
    shared = read_shared_strings(z)
    root = ET.fromstring(z.read('xl/worksheets/sheet1.xml'))
    rows, max_col = [], 0

    for row in root.findall('.//a:sheetData/a:row', ns):
        values = {}
        for c in row.findall('a:c', ns):
            ci = col_to_idx(c.attrib.get('r', ''))
            t = c.attrib.get('t')
            v = c.find('a:v', ns)
            inline = c.find('a:is', ns)
            val = None

            if t == 's' and v is not None:
                val = shared[int(v.text)]
            elif t == 'inlineStr' and inline is not None:
                val = ''.join((node.text or '') for node in inline.findall('.//a:t', ns))
            elif v is not None:
                val = v.text

            values[ci] = val
            max_col = max(max_col, ci)

        if values:
            rows.append([values.get(i) for i in range(max_col + 1)])

    max_len = max(len(r) for r in rows) if rows else 0
    return [r + [None] * (max_len - len(r)) for r in rows]

def parse_coord(value):
    if not value:
        return None, None
    text = str(value).strip().replace('،', ',').replace(';', ',')
    m = re.search(r'(-?\d+(?:\.\d+)?)\s*,\s*(-?\d+(?:\.\d+)?)', text)
    if not m:
        return None, None
    lat, lng = float(m.group(1)), float(m.group(2))
    if not (-90 <= lat <= 90 and -180 <= lng <= 180):
        return None, None
    return lat, lng

def main():
    if len(sys.argv) < 3:
        print('Usage: python excel-to-branches-json.py input.xlsx output.json')
        sys.exit(1)

    with zipfile.ZipFile(sys.argv[1]) as z:
        rows = read_sheet(z)

    headers = rows[0]
    out = []

    for i, r in enumerate(rows[1:], start=2):
        if not any(x not in (None, '') for x in r):
            continue

        rec = dict(zip(headers, r[:len(headers)]))
        lat, lng = parse_coord(rec.get('مختصات'))

        out.append({
            "code": str(rec.get('کد') or '').strip(),
            "province": str(rec.get('استان') or '').strip(),
            "city": str(rec.get('شهر') or '').strip(),
            "district": str(rec.get('منطقه') or '').strip(),
            "name": str(rec.get('فروشگاه') or '').strip(),
            "address": str(rec.get('آدرس') or '').strip(),
            "status": str(rec.get('وضعیت') or '').strip(),
            "lat": lat,
            "lng": lng,
            "coord_note": str(rec.get('توضیحات مختصات') or '').strip(),
            "note": str(rec.get('توضیحات') or '').strip(),
            "source_row": i
        })

    with open(sys.argv[2], 'w', encoding='utf-8') as f:
        json.dump(out, f, ensure_ascii=False, indent=2)

    print(f'Done: {len(out)} rows')

if __name__ == '__main__':
    main()