# Bangun import_brambo.sql dari export BRAMBO lama (sheet Jurnal + 'agustus 2026' + Supplier).
# Pakai:  python3 gen_import.py brambo.xlsx > import_brambo.sql
import sys, re, openpyxl, datetime as D

wb = openpyxl.load_workbook(sys.argv[1], data_only=True)
def rows(n):
    ws = wb[n]; h = [c.value for c in ws[1]]
    return [{h[i]: r[i] for i in range(len(h)) if h[i]} for r in ws.iter_rows(min_row=2, values_only=True) if r[0] is not None]
ARSIP = [s for s in wb.sheetnames if s.lower().startswith(('agustus', 'arsip'))]
J = []
for n in ARSIP: J += rows(n)          # arsip lebih lama dulu
J += rows('Jurnal')
S = rows('Supplier')

def q(v):
    if v is None: return 'NULL'
    return "'" + str(v).replace('\\', '\\\\').replace("'", "''") + "'"
def ts(v):
    if isinstance(v, D.datetime): return v
    m = re.match(r'(\d+)/(\d+)/(\d{4})\s+(\d+)[.:](\d+)[.:](\d+)', str(v))   # '14/10/2026 00.23:45'
    if not m: raise ValueError('timestamp tak dikenal: %r' % v)
    d, mo, y, h, mi, s = map(int, m.groups()); return D.datetime(y, mo, d, h, mi, s)

# nama master -> nama tab di spreadsheet Payment Suplier bsi (hanya yang yakin; sisanya dikonfirmasi saat impor nota)
TAB = {'Vara':'VARA','OJ':'KAOS ORANG JOGJA','Nur Cahyo':'NUR CAHYO','Ulya':'ULYA','Daster234':'DASTER 234','Sobo Jaya':'SOBO JAYA',
 'Putri Tanjung':'PUTRI TANJUNG','Jogja Handmade':'TAS JOGJA HANDMADE','Suminto':'TAS SUMINTO','Pak Hasan':'PAK HASAN','Batik Riry':'BATIK RIRY',
 'Prima Jaya':'TAS PRIMA JAYA','Joist':'JOIST','Handicraft':'TAS HANDICRAFT','Sitinggil':'BATIK SITINGGIL','Batik MR':'BATIK MR','Batik Lima':'BATIK LIMA',
 'Pak Damiri':'PAK DAMIRI','Pak Ari':'PAK ARY  ENZO BATIK','Bintang Firdaus':'BINTANG FIRDAUS','Alesha':'ALESHA','Daster SS Paradise':'DASTER SS PARADISE',
 'Nadika Batik':'NADIKA BATIK','Azra/Daster Melva':'BATIK AZRA','Udeng Pak Rozak':'PAK ROZAK','Pak Mustofa / Nadifa':'PAK MUSTOFA MUKENA NADIFA',
 'Batik Mangkunagari':'MANGKUNAGARI','Sinar Rejo Sari':'SINAR REJO SARI','Jehan Arum / Pak Adib':'JEHAN ARUM','Aneka':'ANEKA',
 'Aksesoris Madura Dream Catcher':'AKSESORIS MADURA Dream Catcher','Mbak Siti Klewer':'MBA SITI KLEWER','Pak Husen Sarung':'PAK HUSEN SARUNG GOYOR',
 'Pamor':'PAMOR','Mukena Sogan Pak Saiful':'MUKENA SOGAN  PAK SAIFUL','Aksesoris Padang':'AKSESORIS PADANG','Kaos Oblong Istimewa':'KAOS OBLONG ISTIMEWA',
 'Batik Fatma':'BATIK FATMA','Tas Rajut Kulon Progo':'TAS RAJUT KULON PROGO','Virenda':'BATIK VIRENDA','Tas Bandung':'TAS BELANJA BANDUNG',
 'Rajut Bandung':'TAS RAJUT BANDUNG','Gmp Tas Rajut':'TAS RAJUT GMP','Tas Kang Basir':'TAS BELACU PAK BASIR','Tas Belacu Hitam Aisyah':'TAS HITAM AISYAH  PAK ROSYID'}
ALIAS = {'Pak Mustofa': 'Pak Mustofa / Nadifa'}      # nama di jurnal yang tidak ada di master
PIC = {'Malik': 'pic01', 'Annisa': 'pic02'}

out = ['-- Impor data BRAMBO lama. Jalankan SEKALI di database yang jurnalnya masih kosong (phpMyAdmin > Import).',
       '-- Prasyarat: schema.sql sudah diimpor dan `php tools/seed.php` sudah dijalankan (akun pic01/pic02 ada).',
       'SET NAMES utf8mb4;', 'START TRANSACTION;']
seen = set()
for s in S:
    n = str(s['Nama']).strip()
    if n.lower() in seen: continue          # 'Aneka' dobel di master -> satu saja
    seen.add(n.lower())
    out.append('INSERT IGNORE INTO suppliers (nama, tab_lama, aktif) VALUES (%s, %s, %d);' % (q(n), q(TAB.get(n)), 1 if s['Aktif'] else 0))

kosong = 0; refs = []
for r in J:
    jenis = r['Jenis']; nom = int(round(r['Nominal'])); sal = int(round(r['SaldoSetelah']))
    kat = r['Kategori'] or ('Setoran Toko' if jenis == 'Masuk' else '')
    sup = r['Supplier']; sup = ALIAS.get(sup, sup)
    ket = re.sub(r'\s+', ' ', (r['Keterangan'] or '')).strip()[:500]
    t = ts(r['Timestamp']); tgl = r['TanggalTransaksi']
    tgl = tgl.date() if isinstance(tgl, D.datetime) else tgl
    out.append("INSERT IGNORE INTO jurnal (kode,brangkas_id,tanggal,created_at,jenis,nominal,kategori,supplier_id,keterangan,pic_user_id,saldo_setelah,dibatalkan) VALUES (%s,1,%s,%s,%s,%d,%s,%s,%s,(SELECT id FROM users WHERE nik=%s),%d,%d);" % (
        q(r['ID']), q(tgl), q(t.strftime('%Y-%m-%d %H:%M:%S.%f')[:23]), q(jenis), nom, q(kat),
        ('(SELECT id FROM suppliers WHERE nama=%s)' % q(sup)) if sup else 'NULL', q(ket), q(PIC[r['PIC']]), sal, 1 if r['Dibatalkan'] else 0))
    if r['RefKoreksiId']: refs.append((r['ID'], r['RefKoreksiId']))
for kid, oid in refs:
    out.append('UPDATE jurnal k JOIN jurnal o ON o.kode=%s SET k.ref_koreksi_id=o.id WHERE k.kode=%s;' % (q(oid), q(kid)))
out.append('UPDATE brangkas SET saldo=%d WHERE id=1;' % int(round(J[-1]['SaldoSetelah'])))
out.append('COMMIT;')
out.append("-- Cek hasil: harusnya %d baris jurnal, saldo akhir %d" % (len(J), int(round(J[-1]['SaldoSetelah']))))
out.append("SELECT COUNT(*) AS baris_jurnal, (SELECT saldo FROM brangkas WHERE id=1) AS saldo_brangkas, (SELECT COUNT(*) FROM suppliers) AS supplier FROM jurnal;")
print('\n'.join(out))
sys.stderr.write('jurnal=%d supplier=%d koreksi=%d tab_terpetakan=%d\n' % (len(J), len(seen), len(refs), sum(1 for n in seen if any(n == k.lower() for k in TAB))))