# -*- coding: utf-8 -*-
"""按 OSM 实测坐标重算辖区认领（就近 + 公平），结果写回 人员信息表.xlsx 的「负责辖区」列。

算法与 D:\\workbbbbbb\\build\\app.js 的 reassignByHome() 等价：
  距离 = 该人到「该辖区最近网点」的距离
  配额 = 18 个正常辖区尽量均分（floor、余数给各自第 N+1 近辖区最近的人）
  贪心后做 relocate/swap 局部搜索，目标 mx*40 + sum
"""
import io, json, math, os, shutil, time

LOG = []


def log(*a):
    LOG.append(' '.join(str(x) for x in a))

BUILD = r'D:\workbbbbbb\build'
XLSX = r'D:\workbbbbbb\workb\人员信息表.xlsx'

OFF = {'富阳辖区', '临安辖区', '建德辖区', '桐庐辖区', '淳安辖区', '信用小微事业部上海分部'}
EXTRA = {'离行外挂', '外派点'}

db = json.load(io.open(os.path.join(BUILD, 'geodb.json'), encoding='utf-8'))
org_area = {o['c']: o['r2'] for o in db['orgs']}
AREAS = sorted({o['r2'] for o in db['orgs']} - OFF - EXTRA)

import openpyxl
wb = openpyxl.load_workbook(XLSX)
ws = wb.worksheets[0]
hdr = {}
for c in ws[1]:
    v = str(c.value or '').strip()
    if v:
        hdr.setdefault(v, []).append(c.column)
AREA_COLS = sorted(hdr.get('负责辖区', []))
C_NAME = hdr['名称'][0]
C_HOME = hdr['住址'][0]
C_STAT = hdr['状态'][0] if '状态' in hdr else 2

people = []
for r in range(2, ws.max_row + 1):
    nm = str(ws.cell(r, C_NAME).value or '').strip()
    if not nm:
        continue
    home = str(ws.cell(r, C_HOME).value or '').strip()
    stat = str(ws.cell(r, C_STAT).value or '').strip()
    people.append({'r': r, 'n': nm, 'home': home, 'stat': stat})

def find_dist(home):
    src = db.get('drive') or db['dist']
    t = src.get(home)
    if t:
        return t, home
    for k in src:
        if k and (k in home or home in k):
            return src[k], k
    return None, None


# 每人 → 每辖区最近网点距离
M = {}
for p in people:
    t, used = find_dist(p['home'])
    if not t:
        raise SystemExit('住址没有距离数据: %s (%s)' % (p['n'], p['home']))
    if used != p['home']:
        log('住址归一: %s (%s) -> %s' % (p['n'], p['home'], used))
    best = {}
    for code, km in t.items():
        a = org_area.get(code)
        if not a or a in OFF or a in EXTRA:
            continue
        if a not in best or km < best[a]:
            best[a] = km
    missing = [a for a in AREAS if a not in best]
    if missing:
        raise SystemExit('%s 缺辖区距离: %s' % (p['n'], missing))
    M[p['n']] = best

free = [p['n'] for p in people if p['stat'] != '离职']
N = len(free)
K = len(AREAS)
base = K // N
rem = K % N
order = sorted(free, key=lambda n: sorted(M[n].values())[base] if base < K else 0)
quota = {n: base for n in free}
for n in order[:rem]:
    quota[n] += 1


def cost(assign):
    mx = max(len(v) for v in assign.values())
    s = sum(M[n][a] for n, v in assign.items() for a in v)
    return mx * 40 + s


def greedy():
    assign = {n: [] for n in free}
    left = {n: quota[n] for n in free}
    pairs = sorted(((M[n][a], n, a) for n in free for a in AREAS))
    taken = set()
    for d, n, a in pairs:
        if left[n] <= 0 or a in taken:
            continue
        assign[n].append(a)
        left[n] -= 1
        taken.add(a)
    for a in AREAS:
        if a in taken:
            continue
        cand = [n for n in free if left[n] > 0] or list(free)
        n = min(cand, key=lambda x: M[x][a])
        assign[n].append(a)
        left[n] -= 1
        taken.add(a)
    return assign


def improve(assign):
    """交换两人辖区不改变人数，故只需最小化距离和。每次施加全局最优的一次交换。"""
    for _ in range(500):
        best = 0.0
        pick = None
        for n1 in free:
            for n2 in free:
                if n2 <= n1:
                    continue
                for a in assign[n1]:
                    for b in assign[n2]:
                        d = M[n1][b] - M[n1][a] + M[n2][a] - M[n2][b]
                        if d < best - 1e-9:
                            best = d
                            pick = (n1, a, n2, b)
        if not pick:
            break
        n1, a, n2, b = pick
        assign[n1].remove(a)
        assign[n1].append(b)
        assign[n2].remove(b)
        assign[n2].append(a)
    return assign, cost(assign)


assign = greedy()
assign, c = improve(assign)

# 校验
cover = set()
for n, v in assign.items():
    cover |= set(v)
    if len(v) != quota[n]:
        raise SystemExit('配额不符 %s %s' % (n, v))
dup = sum(len(v) for v in assign.values())
counts = [len(assign[n]) for n in free]
log('areas=%d people=%d quotas=%s' % (K, N, counts))
log('covered=%d/%d dup=%d spread=%d cost=%.1f' % (len(cover), K, dup - len(cover), max(counts) - min(counts), c))
for n in free:
    v = sorted(assign[n], key=lambda a: M[n][a])
    assign[n] = v
    log('  %-6s %d  %s   (%.1f~%.1f km)' % (n, len(v), ' / '.join(v),
                                              M[n][v[0]], M[n][v[-1]]))

if len(cover) != K:
    raise SystemExit('覆盖不全，未写表')

stamp = time.strftime('%Y%m%d_%H%M')
bak = XLSX.replace('.xlsx', '_备份_%s.xlsx' % stamp)
shutil.copyfile(XLSX, bak)
log('backup=', os.path.basename(bak))

for p in people:
    vals = assign.get(p['n'], [])
    for i, col in enumerate(AREA_COLS):
        ws.cell(p['r'], col).value = vals[i] if i < len(vals) else None

wb.save(XLSX)
log('saved size=%d' % os.path.getsize(XLSX))
io.open(os.path.join(BUILD, '_ra.txt'), 'w', encoding='utf-8').write(chr(10).join(LOG))