#!/usr/bin/env python3
"""
Convert JNE Excel files to CSV for database import.
Handles both HTML-formatted .xls files and proper .xlsx files.
"""

import pandas as pd
import os
import sys

BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))  # marszone/
DATA_DIR = BASE_DIR  # marszone/ (files are now inside marszone)
OUTPUT_DIR = os.path.join(BASE_DIR, 'writable', 'uploads', 'jne_import')

os.makedirs(OUTPUT_DIR, exist_ok=True)

def convert_branch():
    """Convert branch.xls (HTML format) to CSV"""
    path = os.path.join(DATA_DIR, 'branch.xls')
    print(f"📦 Reading branch.xls from {path}...")
    
    dfs = pd.read_html(path)
    df = dfs[0]
    
    # First row is header
    df.columns = ['branch_code', 'branch_name']
    df = df.iloc[1:]  # Skip header row
    df = df.dropna(subset=['branch_code'])
    df['branch_code'] = df['branch_code'].str.strip()
    df['branch_name'] = df['branch_name'].str.strip()
    
    out = os.path.join(OUTPUT_DIR, 'jne_branches.csv')
    df.to_csv(out, index=False)
    print(f"   ✅ {len(df)} branches → {out}")
    return len(df)

def convert_origin():
    """Convert list_origin.xls (HTML format) to CSV"""
    path = os.path.join(DATA_DIR, 'list_origin.xls')
    print(f"📦 Reading list_origin.xls from {path}...")
    
    dfs = pd.read_html(path)
    df = dfs[0]
    
    # First row is header
    df.columns = ['origin_code', 'origin_name']
    df = df.iloc[1:]  # Skip header row
    df = df.dropna(subset=['origin_code'])
    df['origin_code'] = df['origin_code'].str.strip()
    df['origin_name'] = df['origin_name'].str.strip()
    
    out = os.path.join(OUTPUT_DIR, 'jne_origins.csv')
    df.to_csv(out, index=False)
    print(f"   ✅ {len(df)} origins → {out}")
    return len(df)

def convert_dest():
    """Convert list_dest.xlsx to CSV"""
    path = os.path.join(DATA_DIR, 'list_dest.xlsx')
    print(f"📦 Reading list_dest.xlsx from {path}...")
    
    df = pd.read_excel(path, engine='openpyxl')
    
    # Rename columns to match our DB schema
    df.columns = ['country_name', 'province_name', 'city_name', 'district_name', 'subdistrict_name', 'zip_code', 'tariff_code']
    
    # Clean data
    df = df.dropna(subset=['tariff_code'])
    for col in ['province_name', 'city_name', 'district_name', 'subdistrict_name', 'tariff_code']:
        df[col] = df[col].astype(str).str.strip()
    df['zip_code'] = df['zip_code'].astype(str).str.strip()
    
    # Drop country column (all INDONESIA)
    df = df.drop(columns=['country_name'])
    
    out = os.path.join(OUTPUT_DIR, 'jne_destinations.csv')
    df.to_csv(out, index=False)
    print(f"   ✅ {len(df)} destinations → {out}")
    
    # Also create a unique district-level tariff code map
    district_df = df.groupby(['province_name', 'city_name', 'district_name']).agg({
        'tariff_code': 'first',
        'zip_code': 'first'
    }).reset_index()
    
    out2 = os.path.join(OUTPUT_DIR, 'jne_dest_districts.csv')
    district_df.to_csv(out2, index=False)
    print(f"   ✅ {len(district_df)} unique districts → {out2}")
    
    return len(df)

if __name__ == '__main__':
    print("=" * 60)
    print("JNE Excel → CSV Converter")
    print("=" * 60)
    
    b = convert_branch()
    o = convert_origin()
    d = convert_dest()
    
    print(f"\n{'=' * 60}")
    print(f"✅ Done! Total: {b} branches, {o} origins, {d} destinations")
    print(f"CSV files saved to: {OUTPUT_DIR}")
    print(f"{'=' * 60}")