"""Pertamina POS Excel/CSV parser — reads .xlsx or .csv export into row dicts."""

from __future__ import annotations

import csv
import io
from datetime import date, datetime, time
from decimal import Decimal, InvalidOperation
from typing import Any

import openpyxl

PRODUK_MAP: dict[str, str] = {
    "PERTALITE": "RON90",
    "BIO_SOLAR": "CN48",
    "BIOSOLAR": "CN48",
    "PERTAMAX": "RON92",
    "DEXLITE": "CN51",
    "PERTADEX": "CN53",
    "PERTAMAX TURBO": "RON98",
    "PERTAMAX GREEN": "RON95",
}

REQUIRED_COLUMNS = {
    "Tanggal", "Jam", "No dispenser", "Produk",
    "Volume (Liter)", "Penjualan (rupiah)",
}


def parse_dispenser(no_dispenser: str) -> tuple[str | None, str | None]:
    """Split "PULAU 1 - A2 MIX" -> ("PULAU 1", "A2"). Strips " MIX" suffix from nozzle."""
    raw = no_dispenser.strip()
    if " - " not in raw:
        return raw, None
    parts = raw.split(" - ", 1)
    island = parts[0].strip()
    nozzle = parts[1].strip().removesuffix(" MIX").strip()
    return island, nozzle


def validate_produk_match(produk_raw: str, produk_kode: str) -> bool:
    """Check raw produk string matches expected produk kode. Raises ValueError with 'mismatch' if not."""
    key = produk_raw.strip().upper()
    mapped = PRODUK_MAP.get(key)
    if mapped is None or mapped.upper() != produk_kode.upper():
        raise ValueError(
            f"produk mismatch: file berisi '{produk_raw}' "
            f"tapi produk yang dipilih kode '{produk_kode}'"
        )
    return True


def _normalize_spbu_no(s: str) -> str:
    """Strip dots, dashes, spaces for loose comparison. '31.17201.01' -> '311720101'"""
    return "".join(c for c in s if c.isalnum()).upper()


def parse_xlsx_to_rows(file_bytes: bytes) -> list[dict[str, Any]]:
    """Parse Pertamina .xlsx bytes -> list of row dicts with keys matching pos_transaksi_pertamina fields.

    Validation applied per-row:
    - Skip rows with volume = 0 (counted in skipped_invalid)
    - Skip rows with nilai = 0 but volume > 0, or nilai > 0 but volume = 0 (counted in skipped_invalid)

    Validation applied file-wide (raises ValueError):
    - All rows must have the same produk_raw (no mixed products)
    - Returns no_spbu_from_file so caller can validate against SPBU nomor_pertamina
    """
    wb = openpyxl.load_workbook(io.BytesIO(file_bytes), read_only=True, data_only=True)
    ws = wb.active
    rows_iter = ws.iter_rows(values_only=True)

    header_row = next(rows_iter, None)
    if header_row is None:
        raise ValueError("File Excel kosong")

    headers = [str(h).strip() if h is not None else "" for h in header_row]

    missing = REQUIRED_COLUMNS - set(headers)
    if missing:
        raise ValueError(f"Kolom tidak ditemukan: {', '.join(sorted(missing))}")

    def col_idx(name: str) -> int:
        return headers.index(name)

    results: list[dict[str, Any]] = []
    skipped_invalid = 0
    produk_raw_set: set[str] = set()
    no_spbu_from_file: str | None = None

    for raw_row in rows_iter:
        if all(v is None for v in raw_row):
            continue

        def get(name: str) -> Any:
            idx = col_idx(name)
            return raw_row[idx] if idx < len(raw_row) else None

        def get_opt(name: str) -> str | None:
            idx = headers.index(name) if name in headers else -1
            if idx < 0 or idx >= len(raw_row):
                return None
            v = raw_row[idx]
            return str(v).strip() if v is not None else None

        tanggal_val = get("Tanggal")
        if isinstance(tanggal_val, datetime):
            tanggal = tanggal_val.date()
        elif isinstance(tanggal_val, date):
            tanggal = tanggal_val
        else:
            continue

        jam_val = get("Jam")
        if isinstance(jam_val, time):
            jam = jam_val
        elif isinstance(jam_val, str):
            try:
                parts = jam_val.strip().split(":")
                jam = time(int(parts[0]), int(parts[1]), int(parts[2]) if len(parts) > 2 else 0)
            except (ValueError, IndexError):
                continue
        else:
            continue

        try:
            volume = Decimal(str(get("Volume (Liter)") or "0"))
            nilai = Decimal(str(get("Penjualan (rupiah)") or "0"))
        except InvalidOperation:
            continue

        produk_raw = str(get("Produk") or "").strip()
        produk_raw_set.add(produk_raw.upper())

        no_dispenser = str(get("No dispenser") or "").strip()
        island_raw, nozzle_raw = parse_dispenser(no_dispenser)

        # Capture SPBU number from file (first occurrence wins)
        spbu_no = get_opt("No spbu")
        if spbu_no and no_spbu_from_file is None:
            no_spbu_from_file = spbu_no

        # Flag suspicious rows (volume=0 or nilai=0) — kept in DB, highlighted in UI
        is_suspicious = volume <= 0 or nilai <= 0
        if is_suspicious:
            skipped_invalid += 1  # reuse counter as "suspicious_count"

        results.append({
            "tanggal": tanggal,
            "jam": jam,
            "code_mor": get_opt("Code_mor"),
            "province": get_opt("Province"),
            "city": get_opt("City"),
            "no_spbu_pertamina": spbu_no,
            "no_nozzle": get_opt("No nozzle"),
            "no_dispenser": no_dispenser,
            "produk_raw": produk_raw,
            "volume": volume,
            "nilai": nilai,
            "operator_name": get_opt("Operator"),
            "no_hp": get_opt("No HP"),
            "delivery_type": get_opt("Delivery Type"),
            "plat_nomor": get_opt("Plat Nomor"),
            "nik": get_opt("NIK"),
            "instansi": get_opt("Instansi"),
            "island_raw": island_raw,
            "nozzle_raw": nozzle_raw,
        })

    if not results:
        raise ValueError("Tidak ada data valid dalam file Excel (semua baris dilewati)")

    # Validation #6: mixed products in one file
    if len(produk_raw_set) > 1:
        raise ValueError(
            f"File mengandung lebih dari satu jenis produk: {', '.join(sorted(produk_raw_set))}. "
            "Upload satu produk per file."
        )

    # Attach metadata to first row so service can read it without a second pass
    results[0]["_meta"] = {
        "skipped_invalid": skipped_invalid,
        "no_spbu_from_file": no_spbu_from_file,
    }

    return results


def parse_csv_to_rows(file_bytes: bytes) -> list[dict[str, Any]]:
    """Parse Pertamina POS .csv bytes -> same row dict format as parse_xlsx_to_rows.

    Expects same columns as the xlsx export (semicolon or comma separated):
        Tanggal, Jam, No dispenser, Produk, Volume (Liter), Penjualan (rupiah)
    Optional: No spbu, Code_mor, Province, City, No nozzle, Operator,
              No HP, Delivery Type, Plat Nomor, NIK, Instansi

    Tanggal: dd/mm/yyyy or yyyy-mm-dd (auto-detected)
    Jam: HH:MM or HH:MM:SS
    """
    try:
        raw = file_bytes.decode("utf-8-sig")  # handles BOM from Excel CSV export
    except UnicodeDecodeError:
        raw = file_bytes.decode("latin-1")

    lines = raw.strip().splitlines()
    if not lines:
        raise ValueError("File CSV kosong")

    sep = ";" if lines[0].count(";") >= lines[0].count(",") else ","
    reader = csv.reader(io.StringIO(raw.strip()), delimiter=sep)
    rows = list(reader)

    if len(rows) < 2:
        raise ValueError("File CSV harus memiliki minimal 1 baris data")

    headers = [h.strip() for h in rows[0]]
    headers_lower = [h.lower() for h in headers]

    missing = REQUIRED_COLUMNS - set(headers)
    if missing:
        raise ValueError(f"Kolom tidak ditemukan: {', '.join(sorted(missing))}")

    def col_idx(name: str) -> int:
        return headers.index(name)

    def opt_idx(name: str) -> int:
        return headers.index(name) if name in headers else -1

    def get_val(row: list[str], name: str) -> str | None:
        idx = col_idx(name)
        v = row[idx].strip() if idx < len(row) else ""
        return v if v else None

    def get_opt(row: list[str], name: str) -> str | None:
        idx = opt_idx(name)
        if idx < 0 or idx >= len(row):
            return None
        v = row[idx].strip()
        return v if v else None

    def parse_tanggal(s: str) -> date | None:
        s = s.strip()
        for fmt in ("%d/%m/%Y", "%Y-%m-%d", "%d-%m-%Y", "%d/%m/%y"):
            try:
                return datetime.strptime(s, fmt).date()
            except ValueError:
                continue
        return None

    def parse_jam(s: str) -> time | None:
        s = s.strip()
        parts = s.split(":")
        try:
            return time(int(parts[0]), int(parts[1]), int(parts[2]) if len(parts) > 2 else 0)
        except (ValueError, IndexError):
            return None

    results: list[dict[str, Any]] = []
    skipped_invalid = 0
    produk_raw_set: set[str] = set()
    no_spbu_from_file: str | None = None

    for i, row in enumerate(rows[1:], start=2):
        if all(not c.strip() for c in row):
            continue

        tanggal_str = get_val(row, "Tanggal")
        if not tanggal_str:
            continue
        tanggal = parse_tanggal(tanggal_str)
        if tanggal is None:
            continue

        jam_str = get_val(row, "Jam")
        if not jam_str:
            continue
        jam = parse_jam(jam_str)
        if jam is None:
            continue

        try:
            vol_str = (get_val(row, "Volume (Liter)") or "0").replace(",", ".")
            nil_str = (get_val(row, "Penjualan (rupiah)") or "0").replace(",", ".")
            volume = Decimal(vol_str)
            nilai = Decimal(nil_str)
        except InvalidOperation:
            continue

        produk_raw = (get_val(row, "Produk") or "").strip()
        produk_raw_set.add(produk_raw.upper())

        no_dispenser = (get_val(row, "No dispenser") or "").strip()
        island_raw, nozzle_raw = parse_dispenser(no_dispenser)

        spbu_no = get_opt(row, "No spbu")
        if spbu_no and no_spbu_from_file is None:
            no_spbu_from_file = spbu_no

        is_suspicious = volume <= 0 or nilai <= 0
        if is_suspicious:
            skipped_invalid += 1

        results.append({
            "tanggal": tanggal,
            "jam": jam,
            "code_mor": get_opt(row, "Code_mor"),
            "province": get_opt(row, "Province"),
            "city": get_opt(row, "City"),
            "no_spbu_pertamina": spbu_no,
            "no_nozzle": get_opt(row, "No nozzle"),
            "no_dispenser": no_dispenser,
            "produk_raw": produk_raw,
            "volume": volume,
            "nilai": nilai,
            "operator_name": get_opt(row, "Operator"),
            "no_hp": get_opt(row, "No HP"),
            "delivery_type": get_opt(row, "Delivery Type"),
            "plat_nomor": get_opt(row, "Plat Nomor"),
            "nik": get_opt(row, "NIK"),
            "instansi": get_opt(row, "Instansi"),
            "island_raw": island_raw,
            "nozzle_raw": nozzle_raw,
        })

    if not results:
        raise ValueError("Tidak ada data valid dalam file CSV (semua baris dilewati)")

    if len(produk_raw_set) > 1:
        raise ValueError(
            f"File mengandung lebih dari satu jenis produk: {', '.join(sorted(produk_raw_set))}. "
            "Upload satu produk per file."
        )

    results[0]["_meta"] = {
        "skipped_invalid": skipped_invalid,
        "no_spbu_from_file": no_spbu_from_file,
    }

    return results