import json
import csv
import openpyxl
from fuzzywuzzy import fuzz

upg_cols = [
    ['NAMA UPG', 'nama_upg'],
    ['INSTANSI', 'instansi'],
    ['UNIT KERJA', 'unit_kerja'],
    ['NAMA PETUGAS', 'nama_petugas'],
    ['EMAIL', 'email'],
    ['NO HO', 'no_ho'],
    ['STATUS', 'status']
]

wilayahs = list(csv.DictReader(open('./box/wilayah.csv')))

def prepare_upg():
    wb = openpyxl.load_workbook('./box/upg.xlsx')
    r1 = read_upg(wb['Sheet1'])
    r2 = read_upg(wb['PEMDA'])
    with open('./gratifikasi_upg.dat', 'w') as f:
        fieldnames = ['wilayah_id', 'wilayah_code', 'level'] + [e[1] for e in upg_cols]
        w = csv.DictWriter(f, fieldnames=fieldnames)
        w.writerows(r1 + r2)

def read_upg(ws):
    rows = [
        [e.value for e in row] for i, row in enumerate(ws.rows)
        if not ws.row_dimensions[i+1].hidden
    ]
    hidx = 2
    hrow = rows[hidx]
    hcols = { k:v for k,v in upg_cols }
    hmap = {}
    for i, c in enumerate(hrow):
        if c is None or c == 'NO' or c == '(empty)':
            continue
        hmap[hcols[c]] = i
    res = []
    last = None
    for row in rows[hidx+1:]:
        if (row[hmap['nama_petugas']] or '') == '':
            continue
        r = {
            h: row[hmap[h]] if h in hmap else ''
            for h in hcols.values()
        }
        # copy from last if empty
        for c in ['instansi', 'nama_upg']:
            if (r[c] or '').strip() == '':
                r[c] = last[c]
        wilayah = map_upg_wilayah(r['instansi'])
        r['wilayah_id'] = wilayah['id']
        r['wilayah_code'] = wilayah['code']
        r['level'] = wilayah['level']
        last = r
        res.append(r)
    return res

def map_upg_wilayah(instansi):
    level = 'provinsi' if instansi.startswith('PEMERINTAH PROVINSI') else 'kabupaten'
    v = instansi \
        .replace('  ', '') \
        .replace('PEMERINTAH DAERAH ', '') \
        .replace('PEMERINTAH PROVINSI ', 'PROVINSI ') \
        .replace('PEMERINTAH KABUPATEN ', 'KAB. ') \
        .replace('PEMERINTAH KOTA ', 'KOTA ')
    c, score = max([
        (c, score)
        for c in [e for e in wilayahs if e['level'] == level]
        for score in [fuzz.ratio(c['name'].lower(), v.lower())]
    ], key=lambda x:x[1])
    if score != 100:
        print(v, c['name'], score)
    return c

if __name__ == '__main__':
    prepare_upg()