import sqlite3, os

# Cek lokasi file database (bisa di instance/pengajuan.db atau di root pengajuan.db)
db_paths = ['instance/pengajuan.db', 'pengajuan.db']

for db_path in db_paths:
    if not os.path.exists(db_path):
        continue
    print(f"=== Memeriksa Database: {db_path} ===")
    conn = sqlite3.connect(db_path)
    c = conn.cursor()

    # 1. Kolom catatan_belanja_pemasok
    c.execute('PRAGMA table_info(catatan_belanja_pemasok)')
    cols_blj = [row[1] for row in c.fetchall()]
    if 'biaya_admin' not in cols_blj:
        c.execute('ALTER TABLE catatan_belanja_pemasok ADD COLUMN biaya_admin REAL DEFAULT 0')
        print('Added: biaya_admin di catatan_belanja_pemasok')
    if 'total_transfer' not in cols_blj:
        c.execute('ALTER TABLE catatan_belanja_pemasok ADD COLUMN total_transfer REAL DEFAULT 0')
        print('Added: total_transfer di catatan_belanja_pemasok')
    c.execute('UPDATE catatan_belanja_pemasok SET total_transfer = total_belanja WHERE total_transfer = 0 AND biaya_admin = 0')

    # 2. Kolom dapur
    c.execute('PRAGMA table_info(dapur)')
    cols_dapur = [row[1] for row in c.fetchall()]
    if 'bank_va' not in cols_dapur:
        c.execute('ALTER TABLE dapur ADD COLUMN bank_va VARCHAR(50) DEFAULT "BRI"')
        print('Added: bank_va di dapur')
    if 'nomor_va' not in cols_dapur:
        c.execute('ALTER TABLE dapur ADD COLUMN nomor_va VARCHAR(50) DEFAULT ""')
        print('Added: nomor_va di dapur')
    if 'saldo_va' not in cols_dapur:
        c.execute('ALTER TABLE dapur ADD COLUMN saldo_va REAL DEFAULT 0.0')
        print('Added: saldo_va di dapur')
    if 'no_wa' not in cols_dapur:
        c.execute('ALTER TABLE dapur ADD COLUMN no_wa VARCHAR(50) DEFAULT ""')
        print('Added: no_wa di dapur')

    # 3. Tabel mutasi_va
    c.execute('''
    CREATE TABLE IF NOT EXISTS mutasi_va (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        dapur_id INTEGER,
        tanggal DATE NOT NULL,
        waktu DATETIME,
        tipe VARCHAR(20) NOT NULL,
        kategori VARCHAR(50) NOT NULL,
        nominal FLOAT NOT NULL DEFAULT 0.0,
        saldo_sebelum FLOAT DEFAULT 0.0,
        saldo_sesudah FLOAT DEFAULT 0.0,
        keterangan TEXT DEFAULT '',
        ref_nomor VARCHAR(100) DEFAULT '',
        operator VARCHAR(100) DEFAULT '',
        created_at DATETIME,
        FOREIGN KEY (dapur_id) REFERENCES dapur (id)
    )
    ''')
    print('Tabel mutasi_va siap.')

    conn.commit()
    conn.close()

print('Migrasi database selesai sukses.')