#!/usr/bin/env python3
"""
insert.py — stream-parse a debi-output_<month>.txt file, validate every row,
and load it into the `transactions` table (MySQL / cPanel), skipping rows
already inserted and logging bad rows.

Designed to run ON THE SAME SERVER as the source files, so it can handle
multi-GB files without loading them into memory, and connect to MySQL over
localhost instead of the public internet.

Usage:
    # one file
    python3 insert.py --file /home/user/data/debi-output_november.txt

    # every debi-output_*.txt file in a folder
    python3 insert.py --dir /home/user/data --pattern "debi-output_*.txt"

    # parse + validate only, no DB writes
    python3 insert.py --file debi-output_november.txt --dry-run

    # long-running job on 5GB+ files -> run detached so an SSH disconnect
    # doesn't kill it, and keep a log:
    nohup python3 insert.py --dir /home/user/data > import.log 2>&1 &
    tail -f import.log

Requires:
    pip3 install pymysql --break-system-packages   # or: pip3 install --user pymysql

Reads DB connection settings from config.ini (see config.example.ini).
Safe to re-run: each raw line is deduped via a sha256 hash (UNIQUE row_hash).
"""

import argparse
import configparser
import glob
import hashlib
import os
import sys
import time
from datetime import datetime

try:
    import pymysql
except ImportError:
    pymysql = None

VALID_TYPES = {"Internet", "Utitlity", "Mobile", "Topup", "Voucher"}
EXPECTED_FIELDS = 8

DEFAULT_BATCH_SIZE = 5000       # rows per INSERT IGNORE executemany() call
PROGRESS_EVERY = 200_000        # print a progress line every N lines read


def load_db_config(path="config.ini"):
    cfg = configparser.ConfigParser()
    if not os.path.exists(path):
        sys.exit(f"[ERROR] config file not found: {path} (copy config.example.ini -> config.ini and fill it in)")
    cfg.read(path)
    section = cfg["mysql"]
    return {
        "host": section.get("host"),
        "port": section.getint("port", fallback=3306),
        "user": section.get("user"),
        "password": section.get("password"),
        "database": section.get("database"),
        "charset": "utf8mb4",
    }


def parse_timestamp(raw):
    """Parses '2025-11-01T17:26:48.267' (1-6 digit fractional seconds, or none)."""
    if "." in raw:
        base, frac = raw.split(".", 1)
        frac = (frac + "000000")[:6]
        return datetime.strptime(f"{base}.{frac}", "%Y-%m-%dT%H:%M:%S.%f")
    return datetime.strptime(raw, "%Y-%m-%dT%H:%M:%S")


def validate_row(fields):
    """Returns (parsed_dict, None) if valid, or (None, reason) if invalid."""
    if len(fields) != EXPECTED_FIELDS:
        return None, f"expected {EXPECTED_FIELDS} fields, got {len(fields)}"

    subscriber_raw, voucher_raw, amount_raw, channel_raw, ttype_raw, account_raw, time_raw, balance_raw = fields

    if not subscriber_raw.isdigit():
        return None, f"subscriber_id not numeric: '{subscriber_raw}'"
    subscriber_id = int(subscriber_raw)

    voucher_code = None if voucher_raw.strip().upper() == "NA" else voucher_raw.strip()

    try:
        amount = round(float(amount_raw), 2)
    except ValueError:
        return None, f"amount not numeric: '{amount_raw}'"
    if amount < 0:
        return None, f"amount negative: {amount}"

    channel_token = channel_raw.strip()
    if not channel_token:
        return None, "channel_token empty"

    transaction_type = ttype_raw.strip()
    if not transaction_type:
        return None, "transaction_type empty"

    account_token = account_raw.strip()
    if not account_token:
        return None, "account_token empty"

    try:
        transaction_time = parse_timestamp(time_raw.strip())
    except ValueError:
        return None, f"bad timestamp: '{time_raw}'"

    try:
        balance_after = round(float(balance_raw), 2)
    except ValueError:
        return None, f"balance_after not numeric: '{balance_raw}'"

    return {
        "subscriber_id": subscriber_id,
        "voucher_code": voucher_code,
        "amount": amount,
        "channel_token": channel_token,
        "transaction_type": transaction_type,
        "account_token": account_token,
        "transaction_time": transaction_time,
        "balance_after": balance_after,
    }, None


INSERT_SQL = """
INSERT IGNORE INTO transactions
    (subscriber_id, voucher_code, amount, channel_token, transaction_type,
     account_token, transaction_time, balance_after, source_file, row_hash)
VALUES
    (%(subscriber_id)s, %(voucher_code)s, %(amount)s, %(channel_token)s, %(transaction_type)s,
     %(account_token)s, %(transaction_time)s, %(balance_after)s, %(source_file)s, %(row_hash)s)
"""


def process_file(path, conn, batch_size, dry_run, reject_log):
    """Streams the file line by line: never holds more than `batch_size`
    parsed rows in memory, regardless of file size."""
    source_file = os.path.basename(path)
    batch = []

    total = valid = rejected = inserted = 0
    unknown_types_seen = set()
    t0 = time.time()

    def flush():
        nonlocal inserted, batch
        if not batch:
            return
        if not dry_run:
            with conn.cursor() as cur:
                cur.executemany(INSERT_SQL, batch)
                inserted += cur.rowcount
            conn.commit()
        batch = []

    with open(path, "r", encoding="utf-8", errors="replace") as f:
        for line_no, raw_line in enumerate(f, start=1):
            total += 1
            line = raw_line.rstrip("\r\n")
            if not line.strip():
                continue

            fields = line.split("#")
            parsed, reason = validate_row(fields)

            if reason:
                rejected += 1
                reject_log.write(f"{source_file}\t{line_no}\t{reason}\t{line}\n")
                continue

            if parsed["transaction_type"] not in VALID_TYPES:
                unknown_types_seen.add(parsed["transaction_type"])

            valid += 1
            parsed["source_file"] = source_file
            parsed["row_hash"] = hashlib.sha256(line.encode("utf-8")).hexdigest()
            batch.append(parsed)

            if len(batch) >= batch_size:
                flush()

            if total % PROGRESS_EVERY == 0:
                elapsed = time.time() - t0
                rate = total / elapsed if elapsed else 0
                print(f"  [{source_file}] {total:,} lines read | {valid:,} valid | "
                      f"{rejected:,} rejected | {inserted:,} inserted | {rate:,.0f} lines/s", flush=True)

    flush()  # last partial batch

    if unknown_types_seen:
        print(f"  [WARN] unrecognized transaction_type values kept: {sorted(unknown_types_seen)}", file=sys.stderr)

    elapsed = time.time() - t0
    print(f"  done: {total:,} lines | {valid:,} valid | {rejected:,} rejected | "
          f"{inserted:,} inserted | {valid - inserted:,} duplicates | {elapsed:,.1f}s")

    return total, valid, rejected, inserted


def main():
    ap = argparse.ArgumentParser(description="Validate and load debi-output_*.txt file(s) into MySQL")
    src = ap.add_mutually_exclusive_group(required=True)
    src.add_argument("--file", help="path to a single raw .txt export")
    src.add_argument("--dir", help="folder containing multiple monthly .txt exports")
    ap.add_argument("--pattern", default="debi-output_*.txt", help="glob pattern used with --dir")
    ap.add_argument("--config", default="config.ini", help="path to DB config file")
    ap.add_argument("--batch-size", type=int, default=DEFAULT_BATCH_SIZE)
    ap.add_argument("--dry-run", action="store_true", help="parse + validate only, do not touch the DB")
    ap.add_argument("--reject-log", default="rejected_rows.log", help="path to append rejected raw lines to")
    args = ap.parse_args()

    if args.file:
        files = [args.file]
    else:
        files = sorted(glob.glob(os.path.join(args.dir, args.pattern)))
        if not files:
            sys.exit(f"[ERROR] no files matching {args.pattern} in {args.dir}")

    for f in files:
        if not os.path.exists(f):
            sys.exit(f"[ERROR] file not found: {f}")

    conn = None
    if not args.dry_run:
        if pymysql is None:
            sys.exit("[ERROR] pymysql not installed. Run: pip3 install pymysql --break-system-packages")
        db_config = load_db_config(args.config)
        conn = pymysql.connect(**db_config)

    grand_total = grand_valid = grand_rejected = grand_inserted = 0
    try:
        with open(args.reject_log, "a", encoding="utf-8") as reject_log:
            for path in files:
                print(f"\n=== {path} ===")
                total, valid, rejected, inserted = process_file(
                    path, conn, args.batch_size, args.dry_run, reject_log
                )
                grand_total += total
                grand_valid += valid
                grand_rejected += rejected
                grand_inserted += inserted
    finally:
        if conn:
            conn.close()

    print(f"\n=== TOTAL across {len(files)} file(s) ===")
    print(f"lines: {grand_total:,} | valid: {grand_valid:,} | rejected: {grand_rejected:,} | "
          f"inserted: {grand_inserted:,} | duplicates: {grand_valid - grand_inserted:,}")
    if grand_rejected:
        print(f"rejected rows logged to: {args.reject_log}")


if __name__ == "__main__":
    main()