#!/usr/bin/env python3

# usage: python prepare.py ~/jejak_kasus.xlsx > ~/jejak_kasus.csv
# foto column should be added manually

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

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'))

fpath = sys.argv[1]

def get_header(rows):
    r1 = rows[0]
    r2 = rows[1]
    h = [r2[i] if r2[i] or '' != '' else r1[i] for i in range(len(r1))]
    return h

def read_provinsis():
    with conn.cursor(cursor_factory=dbapi.extras.DictCursor) as cur:
        cur.execute('select * from jaga.master_wilayah where parent_id = 0')
        return cur.fetchall()

def read_rampasan(wb):
    # returns rampasan by nama
    rampasans = defaultdict(list)
    rows = [[e.value for e in row] for row in wb['Barang Rampasan']]
    header = rows[0]
    colmap_src = [
        ['nama', 'Nama Terpidana'],
        ['putusan', 'Nomor Putusan'],
        ['uraian', 'Uraian Barang Rampasan'],
        ['keterangan', 'Keterangan (BMN, Lelang, PSP/Hibah)'],
        ['file', 'Dokumen Foto (Nama File)']
    ]
    colmap = {
        k: header.index(v)
        for k, v in colmap_src
    }
    maxcol = max(colmap.values())
    for r in rows[1:]:
        if len(r) < maxcol + 1 or r[colmap['nama']] is None:
            continue
        row = { k: trim(r[i]) for k, i in colmap.items() }
        rampasans[row['nama']].append(row)
    return rampasans

def trim(v):
    if isinstance(v, str):
        return v.replace('_x000D_', '').strip()
    else:
        return v

if __name__ == '__main__':
    wb = openpyxl.load_workbook(fpath, read_only=True, data_only=True)
    rampasans = read_rampasan(wb)
    rows = [[e.value for e in row] for row in wb['Filtered'].rows]
    header = get_header(rows)
    colmap_src = [
        ['id', 'No'],
        ['wilayah_id', None],
        ['foto', 'Foto'],
        ['nama', 'Nama Terdakwa'],
        ['jenis_kelamin', 'Jenis Kelamin'],
        ['pendidikan', 'Pendidikan'],
        ['profesi', 'Profesi'],
        ['institusi', 'Institusi'],
        ['waktu_inkracht', 'Waktu Inkracht'],
        ['area_korupsi', 'Area korupsi'],
        ['jenis_tpk', 'Jenis TPK'],
        ['tingkat', 'Tingkat'],
        ['nomor', 'Nomor'],
        ['tanggal', 'Tanggal'],
        ['pidana', 'Pidana Penjara'],
        ['pidana_tambahan', 'Pidana Tambahan'],
        ['denda', 'Denda'],
        ['denda_tambahan', 'Tambahan Denda'],
        ['uang_pengganti', 'Uang Pengganti'],
        ['uang_pengganti_tambahan', 'Tambahan Uang Pengganti'],
        ['uang_pengganti_tambahan2', 'Tambahan Uang Pengganti (2)'],
        ['biaya_perkara', 'Biaya Perkara'],
        ['summary', 'Summary'],
        ['rampasans', None]
    ]
    colmap = {
        k: header.index(v)
        for k, v in colmap_src
        if v is not None
    }
    provinsis = read_provinsis()
    cols = [k for k, v in colmap_src]
    w = csv.DictWriter(sys.stdout, fieldnames=cols)
    w.writeheader()
    for r in rows[2:]:
        wilayah = r[colmap['area_korupsi']]
        # remap bad data
        wilayah = {
            'Kota Pekanbaru': 'Riau',
            'Garut': 'Jawa Barat',
            'Kota Palembang': 'Sumatera Selatan',
        }.get(wilayah, wilayah)
        c, score = max([
            (e, fuzz.ratio(wilayah, e['name'].replace('Provinsi ', '')))
            for e in provinsis
        ], key=lambda e: e[1])
        wilayah_id = c['id']
        if score == 100:
            pass
        elif score > 80:
            print('from', wilayah, 'to', c['name'], 'score', score, file=sys.stderr)
        else:
            print('No wilayah: ', wilayah, file=sys.stderr)
            wilayah_id = 0
        row = { k: trim(r[i]) for k, i in colmap.items() }
        row['wilayah_id'] = wilayah_id
        nama = row['nama']
        if nama in rampasans:
            row['rampasans'] = json.dumps(rampasans[nama])
            del rampasans[nama]
        else:
            row['rampasans'] = '[]'
        w.writerow(row)

    for e in rampasans:
        print('Rampasan not taken:', e, file=sys.stderr)