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

def prepare_jejak_kasus():
    with open('./box/jejak_kasus.csv') as f:
        rows = list(csv.reader(f))
    provinsis = read_master_provinsi()
    date_indexes = [
        rows[1].index('Tanggal')
    ]
    prov_i = rows[0].index('Jenis TPK')
    fo = open('./jejak_kasus.dat', 'w')
    w = csv.writer(fo)
    for row in rows[2:]:
        for i in date_indexes:
            s = row[i].split('/') # convert from DD/MM/YYYY to YYYY-MM-DD
            row[i] = s[2] + '-' + s[1] + '-' + s[0] if len(s) == 3 else 'NULL'
        _prov = row[prov_i]
        prov = {
            'Nangroe Aceh Darussalam': 'Aceh'
        }.get(_prov, _prov)
        try:
            p = next(e for e in provinsis if e['provinsi_name'].lower() == prov.lower())
            row = row[:prov_i+1] + [p['provinsi_code']] + row[prov_i+1:]
        except StopIteration:
            print('skipping wilayah:', prov)
            continue
        w.writerow(row)
    fo.close()

def prepare_gratifikasi():
    provinsis = read_master_provinsi()
    with open('./box/gratifikasi.csv') as f:
        rows = list(csv.reader(f))
    prov_i = rows[0].index('Wilayah')
    fo = open('./laporan_gratifikasi.dat', 'w')
    w = csv.writer(fo)
    for row in rows[1:]:
        _prov = row[prov_i]
        prov = {
            'D.I. Yogyakarta': 'Di Yogyakarta'
        }.get(_prov, _prov)
        prov_code = 'NULL'
        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)
        row = row[1:prov_i+1] + [prov_code] + row[prov_i+1:]
        w.writerow(row)
    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_jejak_kasus()
    prepare_gratifikasi()