QWE-Telco/TelcoPortal-source/import_excel.py

"""
One-time migration: load the mobile phone repository from the source Excel file into the
SQLite database. Run this once; after that the app reads/writes only the SQLite database and
the Excel file can be forgotten.

Usage:
    python import_excel.py "/path/to/Mobilne telefony 20260901.xlsx" [--mapping mapping.csv] [--reset]

Expected Excel columns (Slovak headers, in this exact order in the source file):
    T.C.                    -> Phone Number
    Meno uzivatela           -> raw text, NOT auto-split into Owner/Venue (see README)
    Uzivatelsky balik        -> Plan
    Viazanost do:             -> Contract Expiration
    Moznost vymeny od:        -> Eligibility for Replacement (blank / '---' = eligible now)

Optional --mapping CSV (phone_number,current_owner,venue) backfills Current Owner / Venue for
matching phone numbers - this is the recommended way to fix up ownership since the Excel column
doesn't cleanly separate the two for most rows.
"""

import argparse
import csv
import sys
from datetime import date, datetime

import openpyxl

from app import create_app
from models import Phone, db

EXPECTED_HEADERS = [
    "T.Č.",
    "Meno užívatela",
    "Užívateľský balík",
    "Viazanosť do:",
    "Možnost výmeny od:",
]


def normalize_phone(value):
    """Excel stores the subscriber number without the leading 0 (numeric cell). Re-add it."""
    if value is None:
        return None
    s = str(value).strip()
    if s.isdigit() and not s.startswith("0"):
        s = "0" + s
    return s or None


def normalize_date(value):
    if value is None:
        return None
    if isinstance(value, datetime):
        return value.date()
    if isinstance(value, date):
        return value
    s = str(value).strip()
    if not s or s == "---":
        return None
    for fmt in ("%Y-%m-%d", "%d.%m.%Y", "%d/%m/%Y"):
        try:
            return datetime.strptime(s, fmt).date()
        except ValueError:
            continue
    print(f"  warning: could not parse date '{value}', leaving blank", file=sys.stderr)
    return None


def load_mapping(path):
    mapping = {}
    if not path:
        return mapping
    with open(path, newline="", encoding="utf-8-sig") as f:
        reader = csv.DictReader(f)
        for row in reader:
            phone = normalize_phone(row.get("phone_number"))
            if not phone:
                continue
            mapping[phone] = {
                "current_owner": (row.get("current_owner") or "").strip() or None,
                "venue": (row.get("venue") or "").strip() or None,
            }
    return mapping


def main():
    parser = argparse.ArgumentParser(description=__doc__, formatter_class=argparse.RawDescriptionHelpFormatter)
    parser.add_argument("excel_path", help="Path to the source .xlsx file")
    parser.add_argument("--mapping", help="Optional CSV with phone_number,current_owner,venue to backfill Owner/Venue")
    parser.add_argument("--reset", action="store_true", help="Delete all existing phone rows before importing")
    args = parser.parse_args()

    mapping = load_mapping(args.mapping)

    wb = openpyxl.load_workbook(args.excel_path, data_only=True)
    ws = wb.active

    rows = list(ws.iter_rows(values_only=True))
    header = [str(h).strip() if h is not None else "" for h in rows[0]]
    print(f"Sheet '{ws.title}' header: {header}")

    app = create_app()
    with app.app_context():
        if args.reset:
            deleted = Phone.query.delete()
            db.session.commit()
            print(f"Deleted {deleted} existing phone rows (--reset).")

        created, updated, skipped = 0, 0, 0
        for row in rows[1:]:
            if row is None or all(v is None for v in row):
                continue
            raw_phone, raw_name, raw_plan, raw_expiration, raw_eligible = (list(row) + [None] * 5)[:5]

            phone_number = normalize_phone(raw_phone)
            if not phone_number:
                skipped += 1
                continue

            plan = str(raw_plan).strip() if raw_plan else None
            contract_expiration = normalize_date(raw_expiration)
            eligible_for_replacement = normalize_date(raw_eligible)

            backfill = mapping.get(phone_number, {})
            current_owner = backfill.get("current_owner")
            venue = backfill.get("venue")
            # No mapping supplied for this number yet: keep the raw Excel text around in
            # Current Owner so nothing is lost, and leave Venue for manual/edit-later cleanup.
            if not mapping and raw_name:
                current_owner = str(raw_name).strip()

            existing = Phone.query.filter_by(phone_number=phone_number).one_or_none()
            if existing:
                existing.plan = plan
                existing.contract_expiration = contract_expiration
                existing.eligible_for_replacement = eligible_for_replacement
                if backfill:
                    existing.current_owner = current_owner
                    existing.venue = venue
                updated += 1
            else:
                db.session.add(Phone(
                    phone_number=phone_number,
                    plan=plan,
                    contract_expiration=contract_expiration,
                    eligible_for_replacement=eligible_for_replacement,
                    current_owner=current_owner,
                    venue=venue,
                ))
                created += 1

        db.session.commit()
        print(f"Import complete: {created} created, {updated} updated, {skipped} skipped (no phone number).")


if __name__ == "__main__":
    main()