# prepare the data

import os
import csv
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'))

overseas = set(['Cina', 'Korea', 'Papua Nugini', 'Perancis', 'Rusia', 'Arab Saudi'])

def prepare_gratifikasi():
    provinsis = read_master_provinsi()
    with open('./box/gratifikasi.csv') as f:
        rows = list(csv.DictReader(f))
    fo = open('./laporan_gratifikasi.dat', 'w')
    w = csv.writer(fo)
    for row in rows:
        _prov = row['provinsi'].strip()
        prov = {
            'Nangroe Aceh Darussalam': 'Aceh',
            'D.I Yogyakarta': 'Di Yogyakarta'
        }.get(_prov, _prov)
        prov_code = 'NULL'
        if prov not in overseas:
            try:
                p = next(e for e in provinsis if e['provinsi_name'].lower() == prov.lower())
                prov_code = p['provinsi_code']
            except StopIteration:
                print('null provinsi_code:', prov)
        # fix wrowng code manually
        prov_code = {'92': '94'}.get(prov_code, prov_code)
        w.writerow([
            row['year'],
            row['jenis'],
            row['provinsi'],
            prov_code,
            row['jumlah']
        ])
    fo.close()

def read_master_provinsi():
    with conn.cursor(cursor_factory=dbapi.extras.DictCursor) as cur:
        cur.execute('select * from jaga.master_provinsi')
        return cur.fetchall()

if __name__ == '__main__':
    prepare_gratifikasi()