# -*- coding: utf-8 -*-
"""解析《经营管理指标日常播报》大区表 → 大区汇总 + 12直营城市播报指标"""
import json, re

LINES = open('南部大区城市看板/_daqu_raw.tsv', encoding='utf-8').read().split('\n')

def cells_of(i):  # 1-based row
    ln = LINES[i-1] if i - 1 < len(LINES) else ''
    c = ln.split('\t')
    return c + [''] * (48 - len(c))

def num(s):
    s = str(s).replace(',', '').strip()
    if s in ('', '----', '-', None) or s.startswith('#') or s.startswith('未'):
        return None
    try:
        return float(s)
    except ValueError:
        return None

# ---------- 1. 头部元信息 ----------
r1, r2 = cells_of(1), cells_of(2)
meta = {
    'title': re.sub(r'\s+', ' ', str(r1[1])),
    'as_of': str(r1[7])[:10],
    'mtd_end': str(r1[13]),
    'days_in_month': int(float(r1[14])),
    'time_progress': num(r2[3]),
}

# ---------- 2. 大区汇总块 (rows 4-8) ----------
def summary_row(i):
    c = cells_of(i)
    return {'name': c[2], 'mtd': num(c[3]), 't1': num(c[4]), 'dod': num(c[5]), 'gap': num(c[6])}

region = {
    '收入': summary_row(4), '毛利额': summary_row(5), '毛利率': summary_row(6),
}
c7, c8 = cells_of(7), cells_of(8)
region['单用户毛利额_本月水位'] = num(c7[8])
region['联营毛利额'] = {'mtd': num(c8[3]), 't1': num(c8[4]), 'dod': num(c8[5]),
                        'target': num(c8[8]), 'gap': num(c8[10])}

# ---------- 3. 经营奖块 (row1=标签, row2=数值, 0-based cols 19-32) ----------
award = {
    'q_targets': {'202607': num(r2[19]), '202608': num(r2[20]), '202609': num(r2[21]), '合计': num(r2[22])},
    'target_gap': num(r2[23]), 'rest_daily': num(r2[24]),
    'start_line': num(r2[25]), 'achieved': num(r2[26]),
    'time_progress': num(r2[27]), 'achieve_progress': num(r2[28]), 'gap_vs_time': num(r2[29]),
    'excess_gap': num(r2[30]), 'est_award': num(r2[31]),
}
c3 = cells_of(3)
award['users_target_avg'] = {'202607': num(c3[19]), '202608': num(c3[20]), '202609': num(c3[21]), '合计': num(c3[22])}
c5r, c6r = cells_of(5), cells_of(6)
award['achieve_gp'] = {'202607': num(c5r[19]), '202608': num(c5r[20]), '合计': num(c5r[22])}
award['gap_gp'] = {'202607': num(c6r[19]), '202608': num(c6r[20])}

# ---------- 4. 12 直营城市块 ----------
GROUP_BOUNDS = [(10, 37), (38, 65), (66, 93), (94, 121), (122, 147), (148, 171)]
METRICS = ['换电用户', '换电用户目标', '换电用户含单次', '收入', '换电收入', '其他收入', '客单价（去KA）',
           '成本', '租金', '电费', '-电费费率', '-单用户用电量', '全职人力&资产折旧', '安装成本',
           '其他成本', '营销费', '-营销费率', '本月单用户毛利额', '毛利额',
           '月度目标达成进度VS时间进度', '毛利额目标', '毛利额GAP', '月度目标达成需每日毛利额', '毛利率']

cities = {}
for gs, ge in GROUP_BOUNDS:
    c0 = cells_of(gs)
    for side, (cCity, cCat, cMTD, cT1, cDOD) in enumerate([(1,2,3,4,5), (6,7,8,9,10)]):
        name = c0[cCity].strip()
        if not name:
            continue
        d = {}
        for off in range(ge - gs + 1):
            c = cells_of(gs + off)
            cat = c[cCat].strip()
            if cat in METRICS:
                if cat == '毛利额目标':
                    d['毛利额目标'] = num(c[cMTD])
                    d['毛利率目标'] = num(c[cDOD]) if c[cT1] == '毛利率目标' else None
                elif cat == '毛利额GAP':
                    d['毛利额GAP'] = num(c[cMTD])
                elif cat == '月度目标达成进度VS时间进度':
                    d[cat] = num(c[cDOD])
                elif cat == '月度目标达成需每日毛利额':
                    d[cat] = num(c[cMTD])
                else:
                    d[cat] = {'mtd': num(c[cMTD]), 't1': num(c[cT1]), 'dod': num(c[cDOD])}
        # 注：大区表里的换电用户目标是公式残留（各组都是同一个值101600），不可靠，不提取
        d['换电用户目标_大区口径'] = None
        cities[name] = d

# ---------- 5. 大区明细: 日序列 ----------
from openpyxl import load_workbook
wb = load_workbook('media/fe9957ee57e841e1b9ae599a90742e16/经营管理指标日常播报-26年08.xlsx',
                   read_only=True, data_only=True)
COLMAP = {'users': 5, 'users_hd': 6, 'kedanjia': 7, 'kedanjia_noka': 8, 'kwh_user': 12,
          'rev': 23, 'cost': 24, 'dianfei': 25, 'btrydep': 26, 'cabtdep': 27, 'zujin': 29,
          'renli': 31, 'jianzhi': 32, 'daiyunying': 33, 'anzhuang': 34, 'qita_cost': 45,
          'yingxiao': 46, 'gp': 47, 'gpm': 48, 'gp_user': 49}
region_daily = []
for r in wb['大区明细'].iter_rows(min_row=2, values_only=True):
    if not r[1]:
        continue
    d = {'date': str(int(r[1]))}
    for k, j in COLMAP.items():
        d[k] = r[j] if isinstance(r[j], (int, float)) else None
    region_daily.append(d)
region_daily.sort(key=lambda x: x['date'])

# ---------- 6. 城市日序列（12 直营城市 sheet） ----------
city_daily = {}
CITY_SHEETS = ['广州', '佛山', '中山', '江门', '珠海', '深圳', '东莞', '惠州', '福州', '泉州', '厦门', '漳州']
CITY_FULL = [c + '市' for c in CITY_SHEETS]
for sheet, cname in zip(CITY_SHEETS, CITY_FULL):
    rows = []
    for r in wb[sheet].iter_rows(min_row=2, values_only=True):
        if not r[1]:
            continue
        d = {'date': str(int(r[1]))}
        for k, j in COLMAP.items():
            if j < len(r):
                d[k] = r[j] if isinstance(r[j], (int, float)) else None
        rows.append(d)
    rows.sort(key=lambda x: x['date'])
    # 去重（江门等 sheet 有重复日期行，保留后出现的）
    dedup = {}
    for d in rows:
        dedup[d['date']] = d
    city_daily[cname] = [dedup[k] for k in sorted(dedup)]

out = {'meta': meta, 'region': region, 'award': award, 'cities': cities,
       'region_daily': region_daily, 'city_daily': city_daily}
json.dump(out, open('南部大区城市看板/daqu_parsed.json', 'w', encoding='utf-8'),
          ensure_ascii=False, default=str)
print('meta:', meta)
print('region:', json.dumps(region, ensure_ascii=False, indent=1)[:600])
print('award:', json.dumps(award, ensure_ascii=False)[:400])
print('cities parsed:', list(cities.keys()))
for c in ['广州市', '深圳市', '漳州市']:
    print(c, '收入', cities[c].get('收入'), '毛利额目标', cities[c].get('毛利额目标'),
          '进度VS时间', cities[c].get('月度目标达成进度VS时间进度'))
print('region_daily:', len(region_daily), region_daily[-1])
for c in CITY_FULL:
    print(c, 'days:', len(city_daily[c]), city_daily[c][-1]['date'] if city_daily[c] else '-')