import os
import sys
import logging
import platform
import traceback
from datetime import datetime
from openpyxl import load_workbook, Workbook

# ─────────────────────────────────────────────
#   MAIN CONFIGURATION
# ─────────────────────────────────────────────

# Choose mode: "background" or "foreground"
MODE = "background"

# Folder to store log files
LOG_DIR = os.path.join(os.path.expanduser("~"), "logs_hapus_formula")

# List of drives/folders to scan.
# Can be full drives or specific folders, freely combined.
# Paths that don't exist or aren't connected will be skipped automatically.
SCAN_PATHS = [
    "D:\\",
    "E:\\",
    "F:\\",
    "G:\\",
    "H:\\",
    "I:\\",
    "J:\\",
    "K:\\",
    os.path.expanduser("~"),  # auto → C:\Users\<currently logged in username>
]

# File extensions to scan
TARGET_EXT = (".xlsx", ".xls")

# ─────────────────────────────────────────────

# Magic bytes for real format identification
MAGIC_ZIP = b"PK\x03\x04"          # ZIP → xlsx/xlsm format
MAGIC_OLE = b"\xd0\xcf\x11\xe0"    # OLE2 Compound → old xls format


def setup_logging():
    os.makedirs(LOG_DIR, exist_ok=True)
    timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
    log_file = os.path.join(LOG_DIR, f"remove_formula_{timestamp}.log")

    handlers = [logging.FileHandler(log_file, encoding="utf-8")]
    if MODE == "foreground":
        handlers.append(logging.StreamHandler(sys.stdout))

    logging.basicConfig(
        level=logging.INFO,
        format="%(asctime)s [%(levelname)s] %(message)s",
        datefmt="%Y-%m-%d %H:%M:%S",
        handlers=handlers,
    )
    return log_file


def detect_real_format(filepath):
    """
    Read first 4 magic bytes to determine the actual file format.
    Returns: "xlsx" | "xls" | "unknown"
    """
    try:
        with open(filepath, "rb") as f:
            header = f.read(4)
        if header[:4] == MAGIC_ZIP:
            return "xlsx"
        elif header[:4] == MAGIC_OLE:
            return "xls"
        else:
            return "unknown"
    except Exception:
        return "unknown"


def scan_files():
    """
    Scan only folders/drives listed in SCAN_PATHS.
    Collects both normal files and leftover _tmp files.
    _tmp files are included so they can be processed directly without renaming.
    """
    normal_files = []   # files without _tmp → need rename before processing
    tmp_files    = []   # files with _tmp    → process directly

    logging.info("=" * 60)
    logging.info("Paths to scan:")
    for p in SCAN_PATHS:
        status = "OK" if os.path.exists(p) else "NOT FOUND — skipped"
        logging.info(f"  {p}  [{status}]")
    logging.info("=" * 60)

    for scan_root in SCAN_PATHS:
        if not os.path.exists(scan_root):
            continue

        for root, dirs, files in os.walk(scan_root, topdown=True):
            for fname in files:
                if not fname.lower().endswith(TARGET_EXT):
                    continue

                full_path    = os.path.join(root, fname)
                name_no_ext  = os.path.splitext(fname)[0]

                if name_no_ext.endswith("_tmp"):
                    # Leftover _tmp file from a previous interrupted run
                    tmp_files.append(full_path)
                else:
                    normal_files.append(full_path)

    logging.info(f"Normal files found  : {len(normal_files)}")
    logging.info(f"Leftover _tmp files : {len(tmp_files)}")
    logging.info(f"Total               : {len(normal_files) + len(tmp_files)}\n")

    if tmp_files:
        logging.info("Leftover _tmp files (will be processed directly):")
        for f in tmp_files:
            logging.info(f"  {f}")
        logging.info("")

    return normal_files, tmp_files


def hapus_formula_xlsx(input_path, output_path):
    """Remove formulas from xlsx file, save to output_path."""
    wb_values = load_workbook(input_path, data_only=True)
    wb_format = load_workbook(input_path)

    total = 0
    for sheet_name in wb_format.sheetnames:
        ws_val = wb_values[sheet_name]
        ws_fmt = wb_format[sheet_name]
        for row in ws_fmt.iter_rows():
            for cell in row:
                if isinstance(cell.value, str) and cell.value.startswith("="):
                    total += 1
                    cell.value = ws_val[cell.coordinate].value

    wb_format.save(output_path)
    return total


def xls_to_xlsx(xls_path, xlsx_path):
    """Convert .xls (OLE) → .xlsx using xlrd. Formula values are already evaluated."""
    import xlrd

    wb_xls  = xlrd.open_workbook(xls_path, formatting_info=True)
    wb_new  = Workbook()
    wb_new.remove(wb_new.active)

    xf_list = wb_xls.xf_list
    fmt_map = wb_xls.format_map

    for sheet_idx in range(wb_xls.nsheets):
        ws_xls = wb_xls.sheet_by_index(sheet_idx)
        ws_new = wb_new.create_sheet(title=ws_xls.name)

        for row in range(ws_xls.nrows):
            for col in range(ws_xls.ncols):
                cell      = ws_xls.cell(row, col)
                xlsx_cell = ws_new.cell(row + 1, col + 1)

                ctype = cell.ctype
                if ctype == xlrd.XL_CELL_TEXT:
                    xlsx_cell.value = cell.value
                elif ctype == xlrd.XL_CELL_NUMBER:
                    xlsx_cell.value = cell.value
                elif ctype == xlrd.XL_CELL_DATE:
                    from xlrd import xldate_as_datetime
                    try:
                        xlsx_cell.value = xldate_as_datetime(cell.value, wb_xls.datemode)
                    except Exception:
                        xlsx_cell.value = cell.value
                elif ctype == xlrd.XL_CELL_BOOLEAN:
                    xlsx_cell.value = bool(cell.value)
                elif ctype == xlrd.XL_CELL_ERROR:
                    xlsx_cell.value = None
                else:
                    xlsx_cell.value = cell.value

                # Copy number format
                try:
                    xf_idx  = ws_xls.cell_xf_index(row, col)
                    xf      = xf_list[xf_idx]
                    fmt_idx = xf.format_key
                    if fmt_idx in fmt_map:
                        fmt_str = fmt_map[fmt_idx].format_str
                        if fmt_str and fmt_str != "General":
                            xlsx_cell.number_format = fmt_str
                except Exception:
                    pass

        # Copy column widths
        for col_idx in range(ws_xls.ncols):
            col_info = ws_xls.colinfo_map.get(col_idx)
            if col_info and col_info.width > 0:
                col_letter = ws_new.cell(1, col_idx + 1).column_letter
                ws_new.column_dimensions[col_letter].width = col_info.width / 256

    wb_new.save(xlsx_path)


def proses_file(original_path, already_tmp=False):
    """
    Process flow per file:

    already_tmp=False (normal file, no _tmp suffix):
      1. Check filename → no _tmp → rename to _tmp first
      2. Process _tmp → save to original name
      3. Success → delete _tmp | Fail → rollback

    already_tmp=True (leftover _tmp from previous interrupted run):
      1. Filename already has _tmp → process directly, no rename needed
      2. Save to original name (strip _tmp from filename)
      3. Success → delete _tmp | Fail → leave _tmp as-is for manual check
    """
    if already_tmp:
        # Derive original path by removing _tmp suffix
        tmp_path  = original_path
        base_tmp, ext = os.path.splitext(original_path)
        # Strip _tmp from base name  e.g. "data_tmp" → "data"
        base_orig = base_tmp[:-4] if base_tmp.endswith("_tmp") else base_tmp
        logging.info(f"  [_TMP] Filename already has _tmp — processing directly.")
    else:
        base_orig, ext = os.path.splitext(original_path)
        tmp_path       = f"{base_orig}_tmp{ext}"

    tmp_xlsx_conv = f"{base_orig}_tmp_conv.xlsx"

    # ── Detect real format ──────────────────────────────────────
    source_for_detection = original_path if not already_tmp else tmp_path
    real_fmt = detect_real_format(source_for_detection)
    if real_fmt == "unknown":
        logging.warning(f"  [SKIP] Unrecognized format (not a valid xlsx/xls): {source_for_detection}")
        return False

    # ── Step 1: Rename original → _tmp (only if not already _tmp) ──
    if not already_tmp:
        try:
            os.rename(original_path, tmp_path)
            logging.info(f"  [RENAME] {os.path.basename(original_path)} → {os.path.basename(tmp_path)}")
        except Exception as e:
            logging.error(f"  FAILED to rename '{original_path}': {e}")
            return False

    # ── Step 2: Process ────────────────────────────────────────
    try:
        if real_fmt == "xls":
            xls_to_xlsx(tmp_path, tmp_xlsx_conv)
            output_path = f"{base_orig}.xlsx"
            jumlah      = hapus_formula_xlsx(tmp_xlsx_conv, output_path)
            os.remove(tmp_xlsx_conv)
            label = f"converted xls→xlsx, {jumlah} formulas removed"
        else:
            output_path = f"{base_orig}{ext}"
            jumlah      = hapus_formula_xlsx(tmp_path, output_path)
            label       = f"{jumlah} formulas removed"

        # Step 3: Success → delete _tmp
        os.remove(tmp_path)
        logging.info(f"  [OK] {output_path} — {label}")
        return True

    except Exception as e:
        logging.error(f"  [FAILED] {original_path}: {e}")
        logging.error(traceback.format_exc())

        # Rollback — clean up partial output, restore _tmp to original name
        for cleanup in [f"{base_orig}{ext}", f"{base_orig}.xlsx", tmp_xlsx_conv]:
            if os.path.exists(cleanup) and cleanup != tmp_path:
                try:
                    os.remove(cleanup)
                except Exception:
                    pass

        if not already_tmp and os.path.exists(tmp_path):
            os.rename(tmp_path, original_path)
            logging.warning(f"  [ROLLBACK] File restored: {original_path}")
        elif already_tmp:
            logging.warning(f"  [NOTICE] _tmp file left as-is for manual check: {tmp_path}")

        return False


def main():
    log_file = setup_logging()
    logging.info(f"Mode      : {MODE.upper()}")
    logging.info(f"Log file  : {log_file}")
    logging.info(f"OS        : {platform.system()} {platform.release()}")
    logging.info("")

    normal_files, tmp_files = scan_files()

    total = len(normal_files) + len(tmp_files)
    if total == 0:
        logging.info("No Excel files found. Script finished.")
        return

    berhasil = gagal = 0
    counter  = 0

    logging.info("-" * 60)

    # Process leftover _tmp files first
    for fpath in tmp_files:
        counter += 1
        logging.info(f"[{counter}/{total}] {fpath}")
        if proses_file(fpath, already_tmp=True):
            berhasil += 1
        else:
            gagal += 1

    # Process normal files
    for fpath in normal_files:
        counter += 1
        logging.info(f"[{counter}/{total}] {fpath}")
        if proses_file(fpath, already_tmp=False):
            berhasil += 1
        else:
            gagal += 1

    logging.info("-" * 60)
    logging.info(f"DONE — Success: {berhasil} | Failed: {gagal} | Total: {total}")
    logging.info(f"Full log: {log_file}")
    logging.info("=" * 60)


# ─────────────────────────────────────────────
#   ENTRY POINT — background vs foreground
# ─────────────────────────────────────────────

if __name__ == "__main__":
    if MODE == "background" and platform.system() == "Windows":
        import subprocess
        pythonw = os.path.join(os.path.dirname(sys.executable), "pythonw.exe")
        if not os.path.exists(pythonw):
            pythonw = sys.executable

        already_background = os.path.basename(sys.executable).lower() == "pythonw.exe"
        if not already_background:
            subprocess.Popen(
                [pythonw, os.path.abspath(__file__)],
                creationflags=subprocess.DETACHED_PROCESS | subprocess.CREATE_NO_WINDOW,
                close_fds=True,
            )
            sys.exit(0)
        else:
            main()
    else:
        main()