#!/usr/bin/env python3
"""
Café Amazon Deployment Data Extraction Pipeline
------------------------------------------------
Extracts, cleans, normalizes, and validates Café Amazon store deployment data
from Excel ('Data AMZ.xlsx') and exports structured JSON for WMS database ingestion.

Usage:
    python database/data/extract_amz.py
    python database/data/extract_amz.py --excel "path/to/Data AMZ.xlsx" --output "database/data/amz_data.json"
"""

import argparse
import json
import os
import re
import sys
import openpyxl

DEFAULT_EXCEL_PATH = r"c:\Users\Teng visal\Desktop\Data AMZ.xlsx"
DEFAULT_OUTPUT_PATH = os.path.join(os.path.dirname(os.path.abspath(__file__)), "amz_data.json")

# Master IDs with identical equipment relocations across different store sites
RELOCATION_IDS = {'AMZ410', 'AMZ063', 'AMZ134', 'AMZ154', 'AMZ171', 'AMZ367', 'AMZ392'}

# Master IDs where one row has full equipment and another has none
EMPTY_PAIR_IDS = {'AMZ080', 'AMZ272'}

# Master IDs with conflicting physical equipment sets that require code disambiguation
CONFLICTING_PAIR_IDS = {'AMZ092', 'AMZ130'}


def clean_serial(val):
    """
    Cleans and normalizes equipment serial numbers.
    - Converts numbers (integers, float integers) to string.
    - Strips whitespace and non-breaking spaces.
    - Strips enclosing quotes.
    - Converts null representations ('N/A', 'NONE', '-', 'NULL', '') to None.
    - Preserves composite serials intact (e.g. '2036SY00UKH9(2036SY00PAU8)').
    """
    if val is None:
        return None
    if isinstance(val, int):
        s = str(val)
    elif isinstance(val, float):
        if val.is_integer():
            s = str(int(val))
        else:
            s = str(val)
    else:
        s = str(val)

    # Normalize whitespace
    s = s.strip().replace('\u00a0', ' ').strip()

    # Strip enclosing quotes if present
    while (len(s) >= 2) and ((s.startswith('"') and s.endswith('"')) or (s.startswith("'") and s.endswith("'"))):
        s = s[1:-1].strip()

    if not s or s.upper() in ['N/A', 'NONE', '-', 'NULL']:
        return None

    return s


def extract_province(shop_name):
    """
    Extracts Cambodian province from Shop Name using targeted keyword patterns.
    Falls back to 'Phnom Penh'.
    """
    if not shop_name:
        return 'Phnom Penh'

    rules = [
        (r'Siem\s*Reap|Apsara|Dom\s*Dek|Krolanh', 'Siem Reap'),
        (r'Sihanou[kv]|Sihanovk|Klang\s*Leu|Mittapheap|Otres|Outres|Ochheuteal|Veal\s*Renh|Ream|Kampong\s*Seila', 'Preah Sihanouk'),
        (r'Battambang|Battambong|\bBTB\b|Banan|Thmor\s*Kol|Moung\s*Russey', 'Battambang'),
        (r'Bantea?y\s*Meanchey|Poi\s*Pet|Poipet|Banteay\s*Neang', 'Banteay Meanchey'),
        (r'Ka[mn]pong\s*Cham|Skun', 'Kampong Cham'),
        (r'Kampong\s*Thom|Kampong\s*Thmor', 'Kampong Thom'),
        (r'Kampong\s*Chhnang|Kampong\s*Tralach', 'Kampong Chhnang'),
        (r'Kampong\s*Speu|Oudong', 'Kampong Speu'),
        (r'Pursat|Pramaoy|Veal\s*Veng|Kra\s*Kor', 'Pursat'),
        (r'Takeo|Bek\s*Kus|Angtasom|Tunle\s*Baty', 'Takeo'),
        (r'Kampot', 'Kampot'),
        (r'Kep|Damnak\s*Chang\s*Aeur', 'Kep'),
        (r'Koh\s*Kong', 'Koh Kong'),
        (r'Kandal|Sa\s*Ang|Roka\s*Koang|Ang\s*Snoul|Preak\s*Tameak|Takhmao', 'Kandal'),
        (r'Kratie|\bSnoul\b', 'Kratie'),
        (r'Svay\s*Rieng|Bavet|Chi\s*Pu', 'Svay Rieng'),
        (r'Prey\s*Veng|Kamchay\s*Mea|Nek\s*Leung', 'Prey Veng'),
        (r'Ratanak\s*Kiri|Ratanakiri', 'Ratanakiri'),
        (r'Steung\s*Treng|Stung\s*Treng', 'Stung Treng'),
        (r'Soung|Krung\s*Soung|Ponhea\s*Kraek|Tbong\s*Khmum|Tboung\s*Khmum', 'Tbong Khmum'),
        (r'Ou\s*Smach|Oddar\s*Meanchey|Otdar\s*Meanchey', 'Oddar Meanchey'),
        (r'Pailin', 'Pailin'),
        (r'Preah\s*Vihear', 'Preah Vihear'),
        (r'Mondulkiri', 'Mondulkiri'),
    ]

    for pattern, prov in rules:
        if re.search(pattern, shop_name, re.IGNORECASE):
            return prov

    return 'Phnom Penh'


def extract_amz_data(excel_path):
    """
    Parses the Café Amazon Excel workbook and transforms all rows into validated dict records.
    """
    if not os.path.exists(excel_path):
        raise FileNotFoundError(f"Excel file not found at: {excel_path}")

    wb = openpyxl.load_workbook(excel_path, data_only=True)
    sheet = wb.active
    rows = list(sheet.iter_rows(values_only=True))

    if not rows:
        raise ValueError(f"Excel file is empty: {excel_path}")

    # Track seen master IDs for relocation and disambiguation resolution
    seen_master_ids = {}
    records = []

    # Rows 2 to 328 in Excel (index 1 to 327)
    data_rows = rows[1:]

    for idx, row in enumerate(data_rows, start=1):
        no = int(row[0]) if row[0] is not None else idx

        # 1. Shop ID normalization & Toul Kork Concept Store POS 1, 2, 3 assignment
        raw_shop_id = row[1]
        if raw_shop_id is None or str(raw_shop_id).strip() == '':
            if no == 311:
                shop_id = 'AMZ-TKCS-01'
            elif no == 312:
                shop_id = 'AMZ-TKCS-02'
            elif no == 313:
                shop_id = 'AMZ-TKCS-03'
            else:
                shop_id = f'AMZ-UNKNOWN-{no:03d}'
        else:
            shop_id = str(raw_shop_id).strip()

        # 2. Master ID normalization
        raw_master_id = row[2]
        if raw_master_id is None or str(raw_master_id).strip() == '' or str(raw_master_id).strip().upper() in ['NONE', 'N/A', 'NULL']:
            original_master_id = None
        else:
            original_master_id = str(raw_master_id).strip()

        # 3. Store name & type
        shop_name = str(row[3]).strip().replace('\u00a0', ' ') if row[3] is not None else ''
        shop_type = str(row[4]).strip() if row[4] is not None else ''
        province = extract_province(shop_name)

        # 4. Equipment serials
        pos_amazon = clean_serial(row[5])
        slip_printer = clean_serial(row[6])
        laser_printer = clean_serial(row[7])
        ups = clean_serial(row[8])
        cash_drawer = clean_serial(row[9])
        keyboard = clean_serial(row[10])
        mouse = clean_serial(row[11])
        barcode_scanner = clean_serial(row[12])

        # 5. Relocation & Code disambiguation resolution
        is_relocation = False
        relocated_from_shop_id = None
        relocated_from_shop_name = None

        if original_master_id is None:
            # 25 rows with missing Master ID
            code = f"AMZ-SHOP-{shop_id}"
        elif original_master_id == 'AMZxxx':
            # 14 rows with placeholder 'AMZxxx'
            code = f"AMZxxx-{shop_id}"
        elif original_master_id in RELOCATION_IDS:
            code = original_master_id
            if original_master_id in seen_master_ids:
                first_info = seen_master_ids[original_master_id]
                is_relocation = True
                relocated_from_shop_id = first_info['shop_id']
                relocated_from_shop_name = first_info['shop_name']
            else:
                seen_master_ids[original_master_id] = {'shop_id': shop_id, 'shop_name': shop_name}
        elif original_master_id in CONFLICTING_PAIR_IDS:
            if original_master_id in seen_master_ids:
                code = f"{original_master_id}-{shop_id}"
            else:
                code = original_master_id
                seen_master_ids[original_master_id] = {'shop_id': shop_id, 'shop_name': shop_name}
        elif original_master_id in EMPTY_PAIR_IDS:
            # Both rows preserved with original code; earlier row has equipment, later row has none
            code = original_master_id
        else:
            code = original_master_id

        record = {
            "no": no,
            "shop_id": shop_id,
            "code": code,
            "original_master_id": original_master_id,
            "shop_name": shop_name,
            "shop_type": shop_type,
            "province": province,
            "pos_amazon": pos_amazon,
            "slip_printer": slip_printer,
            "laser_printer": laser_printer,
            "ups": ups,
            "cash_drawer": cash_drawer,
            "keyboard": keyboard,
            "mouse": mouse,
            "barcode_scanner": barcode_scanner,
            "is_relocation": is_relocation,
            "relocated_from_shop_id": relocated_from_shop_id,
            "relocated_from_shop_name": relocated_from_shop_name
        }
        records.append(record)

    return records


def verify_records(records):
    """
    Runs automated self-verification checks on extracted records.
    """
    expected_keys = [
        "no", "shop_id", "code", "original_master_id", "shop_name", "shop_type",
        "province", "pos_amazon", "slip_printer", "laser_printer", "ups",
        "cash_drawer", "keyboard", "mouse", "barcode_scanner", "is_relocation",
        "relocated_from_shop_id", "relocated_from_shop_name"
    ]

    assert len(records) == 327, f"Expected 327 records, found {len(records)}"

    for idx, r in enumerate(records, start=1):
        assert list(r.keys()) == expected_keys, f"Record {idx} keys mismatch: {list(r.keys())}"
        assert isinstance(r["no"], int), f"Record {idx} 'no' must be int"
        assert isinstance(r["shop_id"], str) and r["shop_id"], f"Record {idx} 'shop_id' empty"
        assert isinstance(r["code"], str) and r["code"], f"Record {idx} 'code' empty"
        assert isinstance(r["shop_name"], str) and r["shop_name"], f"Record {idx} 'shop_name' empty"
        assert isinstance(r["province"], str) and r["province"], f"Record {idx} 'province' empty"
        assert isinstance(r["is_relocation"], bool), f"Record {idx} 'is_relocation' must be bool"

        if r["is_relocation"]:
            assert r["relocated_from_shop_id"], f"Record {idx} relocation missing source shop_id"
            assert r["relocated_from_shop_name"], f"Record {idx} relocation missing source shop_name"
        else:
            assert r["relocated_from_shop_id"] is None, f"Record {idx} non-relocation has non-null relocated_from_shop_id"
            assert r["relocated_from_shop_name"] is None, f"Record {idx} non-relocation has non-null relocated_from_shop_name"

    # Verify Toul Kork Concept Store rows
    tkcs_rows = [r for r in records if r["no"] in [311, 312, 313]]
    assert len(tkcs_rows) == 3
    assert [r["shop_id"] for r in tkcs_rows] == ['AMZ-TKCS-01', 'AMZ-TKCS-02', 'AMZ-TKCS-03']
    assert [r["code"] for r in tkcs_rows] == ['AMZ422', 'AMZ423', 'AMZ424']

    # Verify missing Master IDs
    none_masters = [r for r in records if r["original_master_id"] is None]
    assert len(none_masters) == 25, f"Expected 25 None master IDs, got {len(none_masters)}"
    assert all(r["code"] == f"AMZ-SHOP-{r['shop_id']}" for r in none_masters)

    # Verify AMZxxx Master IDs
    xxx_masters = [r for r in records if r["original_master_id"] == 'AMZxxx']
    assert len(xxx_masters) == 14, f"Expected 14 AMZxxx master IDs, got {len(xxx_masters)}"
    assert all(r["code"] == f"AMZxxx-{r['shop_id']}" for r in xxx_masters)

    # Verify Relocations
    relocations = [r for r in records if r["is_relocation"]]
    assert len(relocations) == 7, f"Expected 7 relocations, got {len(relocations)}"

    # Verify Conflicting Pairs
    conflict_disambiguated = [r for r in records if r["code"] in ['AMZ092-DD0092', 'AMZ130-DD0130']]
    assert len(conflict_disambiguated) == 2, f"Expected 2 disambiguated conflict rows, got {len(conflict_disambiguated)}"

    # Verify Empty Pairs
    empty_pairs = [r for r in records if r["code"] in ['AMZ080', 'AMZ272'] and r["no"] in [287, 288]]
    assert len(empty_pairs) == 2
    for ep in empty_pairs:
        assert all(ep[k] is None for k in ['pos_amazon', 'slip_printer', 'laser_printer', 'ups', 'cash_drawer', 'keyboard', 'mouse', 'barcode_scanner'])

    # Verify Province Extractions for known edge cases
    by_no = {r["no"]: r for r in records}
    expected_provinces = {
        92: 'Kampong Cham',
        119: 'Stung Treng',
        130: 'Ratanakiri',
        148: 'Preah Sihanouk',
        161: 'Banteay Meanchey',
        164: 'Banteay Meanchey',
        190: 'Tbong Khmum',
        203: 'Tbong Khmum',
        322: 'Oddar Meanchey',
    }
    for row_no, expected_prov in expected_provinces.items():
        actual_prov = by_no[row_no]["province"]
        assert actual_prov == expected_prov, (
            f"Row {row_no} ({by_no[row_no]['shop_name']}): expected province '{expected_prov}', got '{actual_prov}'"
        )


def main():
    parser = argparse.ArgumentParser(description="Extract Café Amazon deployment data to JSON")
    parser.add_argument("--excel", default=DEFAULT_EXCEL_PATH, help="Path to Data AMZ.xlsx")
    parser.add_argument("--output", default=DEFAULT_OUTPUT_PATH, help="Path to output amz_data.json")
    args = parser.parse_args()

    print("==================================================================")
    print(" Café Amazon Data Extraction Pipeline ('Data AMZ.xlsx')")
    print("==================================================================")
    print(f"Source Excel : {args.excel}")
    print(f"Target Output: {args.output}")

    records = extract_amz_data(args.excel)
    print(f"\n[OK] Extracted {len(records)} records from Excel.")

    verify_records(records)
    print("[OK] All self-verification assertions passed (327 records, schema validated).")

    # Ensure output directory exists
    os.makedirs(os.path.dirname(os.path.abspath(args.output)), exist_ok=True)

    # Write out UTF-8 formatted JSON
    with open(args.output, "w", encoding="utf-8") as f:
        json.dump(records, f, ensure_ascii=False, indent=2)

    file_size_kb = os.path.getsize(args.output) / 1024
    print(f"[OK] Successfully wrote {len(records)} records to {args.output} ({file_size_kb:.1f} KB)")

    # Print summary breakdown
    provinces = {}
    shop_types = {}
    for r in records:
        provinces[r["province"]] = provinces.get(r["province"], 0) + 1
        shop_types[r["shop_type"]] = shop_types.get(r["shop_type"], 0) + 1

    print("\n--- Summary Breakdown ---")
    print(f"Total Stores Processed: {len(records)}")
    print(f"Store Types: COCO={shop_types.get('COCO', 0)}, DODO={shop_types.get('DODO', 0)}")
    print(f"Synthetic Master IDs: 25 (None -> AMZ-SHOP-{{shop_id}}), 14 (AMZxxx -> AMZxxx-{{shop_id}})")
    print(f"Concept Store IDs Assigned: 3 (AMZ-TKCS-01, AMZ-TKCS-02, AMZ-TKCS-03)")
    print(f"Equipment Relocations Flagged: 7 (AMZ410, AMZ063, AMZ134, AMZ154, AMZ171, AMZ367, AMZ392)")
    print(f"Conflicting Codes Disambiguated: 2 (AMZ092-DD0092, AMZ130-DD0130)")
    print(f"Empty Equipment Rows Preserved: 2 (AMZ272/CC0292, AMZ080/CC0293)")

    print("\nStores by Province:")
    for prov, count in sorted(provinces.items(), key=lambda x: -x[1]):
        print(f"  {prov:20s}: {count}")

    print("==================================================================")


if __name__ == "__main__":
    main()
