#!/usr/bin/env python3

# prepare UPG to be uploaded
# usage python3 prepare_upg ~/kontak_upg.xlsx > ~/kontak_upg.csv

import sys
import os
import json
import csv
import openpyxl
from fuzzywuzzy import fuzz
import psycopg2 as dbapi
import psycopg2.extras
from dotenv import load_dotenv

env = os.getenv
load_dotenv()
conn = dbapi.connect(user=env('DB_USERNAME'), password=env('DB_PASSWORD'),
                     host=env('DB_HOST'), port=env('DB_PORT'), dbname=env('DB_NAME'))

with conn.cursor(cursor_factory=dbapi.extras.DictCursor) as cur:
    cur.execute("select * from jaga.master_wilayah where level in ('provinsi', 'kabupaten')")
    wilayahs = cur.fetchall()

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

def prepare_upg():
    wb = openpyxl.load_workbook(sys.argv[1])
    rows = read_upg(wb['Sheet1'])
    fieldnames = ['wilayah_id', 'wilayah_code', 'level'] + [e[1] for e in upg_cols]
    w = csv.DictWriter(sys.stdout, fieldnames=fieldnames)
    w.writeheader()
    w.writerows(rows)

def read_upg(ws):
    rows = [
        [e.value for e in row] for i, row in enumerate(ws.rows)
    ]
    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'])
        if wilayah is None:
            # print('No wilayah: ', r['instansi'], file=sys.stderr)
            continue
        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 = resolve_level(instansi)
    if level is None:
        return None
    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, file=sys.stderr)
    return c

def resolve_level(instansi):
    if instansi.startswith('PEMERINTAH'):
        if instansi.startswith('PEMERINTAH PROVINSI'):
            return 'provinsi'
        else:
            return 'kabupaten'
    else:
        return None

if __name__ == '__main__':
    prepare_upg()