#!/usr/bin/env python3
"""Generate database/data/obat.json from seederObat.xlsx for master_obat seeding."""

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 / 'database' / 'data' / 'seederObat.xlsx'
DEFAULT_OUTPUT = ROOT / 'database' / 'data' / 'obat.json'

FIELD_ALIASES: dict[str, list[str]] = {
    'kode_obat': ['KODE OBAT', 'KODE'],
    'nama_obat': ['NAMA OBAT', 'NAMA PRODUK'],
    'kategori_obat': ['KATEGORI OBAT'],
    'kemasan_terkecil': ['KEMASAN TERKECIL'],
    'isi_kemasan': ['ISI KEMASAN TERKECIL', 'ISI KEMASAN'],
}


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_int(value) -> int:
    if value is None:
        return 0

    if isinstance(value, (int, float)):
        return int(round(value))

    text = str(value).strip().replace(',', '')
    if not text or text.startswith('='):
        return 0

    try:
        return int(round(float(text)))
    except ValueError:
        digits = ''.join(ch for ch in text if ch.isdigit())
        return int(digits) if digits else 0


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[str]) -> 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 generate(input_path: Path, output_path: Path) -> None:
    workbook = load_workbook(input_path, read_only=True, data_only=True)
    worksheet = workbook.active

    rows = worksheet.iter_rows(values_only=True)
    headers = [normalize_header(cell) for cell in next(rows)]
    header_index = build_header_index(headers)

    required = {'kode_obat', 'nama_obat', 'kategori_obat', 'kemasan_terkecil', 'isi_kemasan'}
    missing = required - set(header_index)
    if missing:
        raise ValueError(
            f'Missing required columns: {", ".join(sorted(missing))}. '
            f'Headers found: {", ".join(headers)}'
        )

    output_rows = []
    for row in rows:
        kode = clean_str(row[header_index['kode_obat']])
        nama = clean_str(row[header_index['nama_obat']])
        if not kode or not nama:
            continue

        isi = clean_int(row[header_index['isi_kemasan']])
        if isi <= 0:
            continue

        output_rows.append({
            'kode_obat': kode,
            'nama_obat': nama,
            'kategori_obat': clean_str(row[header_index['kategori_obat']]),
            'kemasan_terkecil': clean_str(row[header_index['kemasan_terkecil']]),
            'isi_kemasan': isi,
        })

    workbook.close()

    output_path.parent.mkdir(parents=True, exist_ok=True)
    output_path.write_text(json.dumps(output_rows, ensure_ascii=False, indent=2) + '\n', encoding='utf-8')


def main() -> None:
    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.exists():
        print(f'Input file not found: {input_path}', file=sys.stderr)
        sys.exit(1)

    generate(input_path, output_path)
    print(f'Wrote {len(json.loads(output_path.read_text(encoding="utf-8")))} rows to {output_path}')


if __name__ == '__main__':
    main()