#!/usr/bin/env python3
"""Generate database/data/migrasi/harga_obat.json from docs/migrasi/harga_obat.xlsx."""

from __future__ import annotations

import json
import re
import sys
from pathlib import Path

try:
    from openpyxl import load_workbook
except ImportError:
    print('openpyxl is required. Install with: pip install openpyxl', file=sys.stderr)
    sys.exit(1)

ROOT = Path(__file__).resolve().parents[1]
DEFAULT_INPUT = ROOT / 'docs' / 'migrasi' / 'harga_obat.xlsx'
DEFAULT_OUTPUT = ROOT / 'database' / 'data' / 'migrasi' / 'harga_obat.json'

FIELD_ALIASES: dict[str, list[str]] = {
    'kode_obat': ['KODE'],
    'nama_obat': ['NAMA'],
    'harga_modal': ['MODAL (INCL. PPN)', 'MODAL (INCL PPN)', 'MODAL'],
    'harga_modal_satuan': ['MODAL SATUAN'],
    'stok_awal': ['STOK AWAL'],
    'harga_otc': ['HARGA OTC'],
    'margin_otc': ['% HARGA UMUM', '% OTC'],
    'harga_reg': ['HARGA REGULER', 'HARGA REG'],
    'margin_reg': ['% REG'],
    'harga_vip': ['HARGA VIP'],
    'margin_vip': ['% VIP'],
}


def clean_str(value) -> str | None:
    if value is None:
        return None
    text = str(value).strip()
    if not text or text.lower() == 'nan':
        return None
    return text


def clean_float(value) -> float:
    if value is None:
        return 0.0
    if isinstance(value, (int, float)):
        return float(value)
    text = str(value).strip().replace(',', '')
    if not text or text.startswith('='):
        return 0.0
    try:
        return float(text)
    except ValueError:
        return 0.0


def clean_int(value) -> int:
    return int(round(clean_float(value)))


def margin_to_percent(value) -> int:
    """Excel sering menyimpan margin sebagai rasio (0.68); DB memakai persen bulat (69)."""
    n = clean_float(value)
    if 0 < n <= 1.5:
        return int(round(n * 100))
    return int(round(n))


def round_uang(value) -> int:
    """Bulatkan Rupiah / modal / harga ke bilangan bulat."""
    return int(round(clean_float(value)))


def normalize_header(value) -> str:
    if value is None:
        return ''
    return re.sub(r'\s+', ' ', str(value).strip()).upper()


def build_header_index(headers: list) -> dict[str, int]:
    normalized = [normalize_header(header) for header in headers]
    header_index: dict[str, int] = {}

    for field, aliases in FIELD_ALIASES.items():
        for alias in aliases:
            alias_norm = normalize_header(alias)
            for index, header in enumerate(normalized):
                if header == alias_norm:
                    header_index[field] = index
                    break
            if field in header_index:
                break

    return header_index


def cell(row: tuple, index: dict[str, int], field: str):
    if field not in index:
        return None
    i = index[field]
    return row[i] if i < len(row) else None


def main() -> int:
    input_path = Path(sys.argv[1]) if len(sys.argv) > 1 else DEFAULT_INPUT
    output_path = Path(sys.argv[2]) if len(sys.argv) > 2 else DEFAULT_OUTPUT

    if not input_path.is_file():
        print(f'File tidak ditemukan: {input_path}', file=sys.stderr)
        return 1

    wb = load_workbook(input_path, read_only=True, data_only=True)
    ws = wb[wb.sheetnames[0]]
    rows_iter = ws.iter_rows(values_only=True)
    headers = next(rows_iter, None)
    if not headers:
        print('Sheet kosong.', file=sys.stderr)
        return 1

    index = build_header_index(list(headers))
    required = ['kode_obat', 'harga_otc', 'harga_reg', 'harga_vip']
    missing = [f for f in required if f not in index]
    if missing:
        print(f'Kolom wajib tidak ditemukan: {missing}', file=sys.stderr)
        print(f'Header: {[normalize_header(h) for h in headers]}', file=sys.stderr)
        return 1

    items = []
    for row in rows_iter:
        kode = clean_str(cell(row, index, 'kode_obat'))
        if not kode:
            continue

        items.append({
            'kode_obat': kode,
            'nama_obat': clean_str(cell(row, index, 'nama_obat')),
            'harga_modal': round_uang(cell(row, index, 'harga_modal')),
            'harga_modal_satuan': round_uang(cell(row, index, 'harga_modal_satuan')),
            'stok_awal': clean_int(cell(row, index, 'stok_awal')),
            'harga_otc': round_uang(cell(row, index, 'harga_otc')),
            'harga_reg': round_uang(cell(row, index, 'harga_reg')),
            'harga_vip': round_uang(cell(row, index, 'harga_vip')),
            'margin_otc': margin_to_percent(cell(row, index, 'margin_otc')),
            'margin_reg': margin_to_percent(cell(row, index, 'margin_reg')),
            'margin_vip': margin_to_percent(cell(row, index, 'margin_vip')),
        })

    wb.close()

    output_path.parent.mkdir(parents=True, exist_ok=True)
    output_path.write_text(
        json.dumps(items, ensure_ascii=False, indent=2) + '\n',
        encoding='utf-8',
    )
    print(f'OK: {len(items)} baris -> {output_path}')
    return 0


if __name__ == '__main__':
    raise SystemExit(main())