#!/usr/bin/env python3
"""Generate database/data/pbf.json and pelanggan.json from seeder Excel file."""

from __future__ import annotations

import json
import sys
from pathlib import Path

import pandas as pd

ROOT = Path(__file__).resolve().parents[1]
DEFAULT_INPUT = ROOT / 'database' / 'data' / 'seeder.xlsx'
OUTPUT_PBF = ROOT / 'database' / 'data' / 'pbf.json'
OUTPUT_PELANGGAN = ROOT / 'database' / 'data' / 'pelanggan.json'


def format_phone(val) -> str | None:
    if pd.isna(val) or val == '' or val is None:
        return None

    if isinstance(val, float):
        if val != val:
            return None
        phone = str(int(val))
    else:
        phone = str(val).strip()
        if not phone or phone.lower() == 'nan':
            return None

    if phone.endswith('.0'):
        phone = phone[:-2]

    if 'e' in phone.lower():
        phone = str(int(float(phone)))

    if phone.startswith('8') and len(phone) >= 9:
        phone = '0' + phone

    return phone


def clean_str(val) -> str | None:
    if pd.isna(val) or val is None:
        return None

    text = str(val).strip()
    if not text or text.lower() == 'nan':
        return None

    return text


def normalize_jenis_harga(val) -> str:
    if pd.isna(val) or val is None:
        return 'OTC'

    jenis = str(val).strip().upper()
    if jenis in ('OTC', 'REG', 'VIP'):
        return jenis

    return 'OTC'


def generate(input_path: Path) -> None:
    workbook = pd.ExcelFile(input_path)

    pbf_rows = []
    for _, row in pd.read_excel(workbook, sheet_name='PBF').iterrows():
        nama = clean_str(row['NAMA SUPPLIER'])
        if not nama:
            continue

        pbf_rows.append({
            'nama_pbf': nama,
            'alamat': clean_str(row['ALAMAT']),
            'telepon': format_phone(row['NO TELP']),
            'kontak_sales': None,
            'keterangan': clean_str(row['KETERANGAN']),
        })

    pelanggan_rows = []
    for _, row in pd.read_excel(workbook, sheet_name='PELANGGAN').iterrows():
        kode = clean_str(row['KODE PELANGGAN'])
        nama = clean_str(row['NAMA PELANGGAN'])
        if not kode or not nama:
            continue

        top_val = row['TOP']
        top = 0 if pd.isna(top_val) else int(float(top_val))

        pelanggan_rows.append({
            'kode_pelanggan': kode,
            'nama_pelanggan': nama,
            'nama_faktur': clean_str(row['NAMA DI FAKTUR']),
            'area': clean_str(row['AREA']),
            'top': top,
            'jenis_harga': normalize_jenis_harga(row['HARGA']),
            'alamat': clean_str(row['ALAMAT']),
            'nomor_hp': format_phone(row['NO HP']),
            'keterangan': clean_str(row['KETERANGAN']),
        })

    OUTPUT_PBF.parent.mkdir(parents=True, exist_ok=True)

    OUTPUT_PBF.write_text(json.dumps(pbf_rows, ensure_ascii=False, indent=2) + '\n', encoding='utf-8')
    OUTPUT_PELANGGAN.write_text(json.dumps(pelanggan_rows, ensure_ascii=False, indent=2) + '\n', encoding='utf-8')

    print(f'Wrote {len(pbf_rows)} PBF rows to {OUTPUT_PBF}')
    print(f'Wrote {len(pelanggan_rows)} pelanggan rows to {OUTPUT_PELANGGAN}')


if __name__ == '__main__':
    source = Path(sys.argv[1]) if len(sys.argv) > 1 else DEFAULT_INPUT
    if not source.exists():
        raise SystemExit(f'Input file not found: {source}')

    generate(source)