"""
LPO MSG Parser
--------------
Parses a folder of Outlook .msg files, extracts LPO fields from the HTML body,
and writes results to a timestamped Excel file in the same folder.

Usage:
    python parse_lpo.py /path/to/msg/folder

Dependencies:
    pip install extract-msg beautifulsoup4 openpyxl
"""

import sys
from pathlib import Path
from datetime import datetime

import extract_msg
from bs4 import BeautifulSoup
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter


# ─────────────────────────────────────────
# HTML Field Extractor — DOM table navigation
# ─────────────────────────────────────────

def cell_text(td):
    """Return clean text from a <td>, stripping whitespace and non-breaking spaces."""
    if td is None:
        return ''
    return td.get_text(separator=' ').replace('\xa0', ' ').strip()


def direct_rows(table):
    """
    Return only the <tr> elements that are DIRECT children of this table
    (or its direct <tbody>/<thead>), not rows from nested child tables.
    This prevents find_all('tr') from pulling in rows from nested tables.
    """
    rows = []
    for child in table.children:
        if getattr(child, 'name', None) in ('tbody', 'thead'):
            for row in child.children:
                if getattr(row, 'name', None) == 'tr':
                    rows.append(row)
        elif getattr(child, 'name', None) == 'tr':
            rows.append(child)
    return rows


def direct_cells(row):
    """
    Return only the <td>/<th> elements that are DIRECT children of this row,
    not cells from any nested tables inside a cell.
    """
    return [c for c in row.children if getattr(c, 'name', None) in ('td', 'th')]


def parse_lpo_html(html_bytes):
    """
    Parse the HTML body of an LPO email using DOM table structure.
    Returns a dict with lpo_number, ref_number, inv_number, inv_amount.
    Any field not found is None.

    Strategy: find the SPECIFIC <td> we care about first (by its unique
    content), then call .find_parent('table') to get exactly the table
    it directly belongs to. Then use direct_rows/direct_cells to walk
    only that table's own rows — never descending into nested tables.
    """
    result = {
        'lpo_number': None,
        'ref_number': None,
        'inv_number': None,
        'inv_amount': None,
    }

    try:
        html = html_bytes.decode('utf-8', errors='replace')
    except Exception:
        return result

    soup = BeautifulSoup(html, 'html.parser')

    # ── LPO No. and Ref No. ───────────────────────────────────────────
    # Find the specific <td> that contains BOTH "LPO No." and "RefNo"
    # as direct text (not via nested tables). Then walk up to its table.
    lpo_td = None
    for td in soup.find_all('td'):
        # Use direct text only — get_text() on a td with no nested tables
        # gives us the raw label text
        t = td.get_text()
        if 'LPO No.' in t and 'RefNo' in t:
            lpo_td = td
            break

    if lpo_td:
        bold_tags = lpo_td.find_all('b')
        if len(bold_tags) >= 1:
            result['lpo_number'] = bold_tags[0].get_text(strip=True) or None
        if len(bold_tags) >= 2:
            result['ref_number'] = bold_tags[1].get_text(strip=True) or None

    # ── Itinerary: Ticket No. and Total Amount ────────────────────────
    # The itinerary table is uniquely identified by having a <td> with
    # BOTH rowspan="2" AND the text "Ticket No." as its own direct content.
    # find_parent('table') gives us exactly that table — not an ancestor.
    ticket_header_td = None
    for td in soup.find_all('td', attrs={'rowspan': '2'}):
        if 'Ticket No' in td.get_text():
            ticket_header_td = td
            break

    if ticket_header_td:
        itinerary_table = ticket_header_td.find_parent('table')
        rows = direct_rows(itinerary_table)

        # DEBUG — remove after confirming fix
        import os
        if os.environ.get('LPO_DEBUG'):
            print(f'  [DEBUG] itinerary direct rows: {len(rows)}')
            for i, row in enumerate(rows):
                cells = direct_cells(row)
                print(f'  [DEBUG] row {i}: {len(cells)} cells | Total Amount={"Total Amount" in row.get_text()}')
                print(f'  [DEBUG]   cell_texts: {[cell_text(c) for c in cells]}')

        # Compute visual column index of Ticket No. using colspan accumulation
        # on the specific header row that contains the Ticket No. cell
        ticket_visual_col = None
        for row in rows:
            cells = direct_cells(row)
            for idx, cell in enumerate(cells):
                if 'Ticket No' in cell.get_text():
                    ticket_visual_col = sum(
                        int(s.get('colspan', 1)) for s in cells[:idx]
                    )
                    break
            if ticket_visual_col is not None:
                break

        # Walk direct rows: find data rows and Total Amount row.
        # NOTE: Total Amount row comes AFTER the data row, so we must
        # never break early — always walk all rows.
        for row in rows:
            cells = direct_cells(row)
            row_text = row.get_text()

            # Total Amount row — scan reversed cells for the first
            # one that parses as a float (skips "AED", empty cells, etc.)
            if 'Total Amount' in row_text:
                for td in reversed(cells):
                    text = cell_text(td)
                    if text:
                        try:
                            result['inv_amount'] = float(text.replace(',', ''))
                            break  # only break on successful parse
                        except ValueError:
                            continue  # not a number, try next cell
                continue

            # Skip header rows (grey #CDCDCD background)
            if any('CDCDCD' in c.get('style', '') for c in cells):
                continue

            # Data row: enough non-empty cells — capture inv_number
            # but do NOT break, so we continue to the Total Amount row
            if result['inv_number'] is None and ticket_visual_col is not None:
                non_empty = [cell_text(c) for c in cells if cell_text(c)]
                if len(non_empty) >= 3:
                    if ticket_visual_col < len(cells):
                        inv_raw = cell_text(cells[ticket_visual_col])
                        result['inv_number'] = inv_raw if inv_raw else None

    return result


# ─────────────────────────────────────────
# MSG Reader
# ─────────────────────────────────────────

def read_msg(msg_path):
    """
    Open a .msg file and return (subject, html_bytes).
    Falls back gracefully if htmlBody is not available.
    """
    with extract_msg.openMsg(str(msg_path)) as msg:
        subject = msg.subject or ''
        html_bytes = msg.htmlBody  # bytes or None
    return subject, html_bytes


# ─────────────────────────────────────────
# Excel Writer
# ─────────────────────────────────────────

HEADERS = ['Email Heading', 'LPO #', 'Ref No', 'Inv #', 'Inv Amount (AED)', 'File Name', 'Parsed At', 'Status']
COL_WIDTHS = [55, 18, 40, 15, 18, 45, 20, 12]

HEADER_FILL   = PatternFill('solid', start_color='1F4E79')
HEADER_FONT   = Font(bold=True, color='FFFFFF', name='Arial', size=10)
DATA_FONT     = Font(name='Arial', size=10)
ALT_FILL      = PatternFill('solid', start_color='D9E1F2')
ERROR_FILL    = PatternFill('solid', start_color='FCE4D6')
THIN_BORDER   = Border(
    left=Side(style='thin', color='BFBFBF'),
    right=Side(style='thin', color='BFBFBF'),
    top=Side(style='thin', color='BFBFBF'),
    bottom=Side(style='thin', color='BFBFBF'),
)
AMOUNT_FORMAT = '#,##0.00'


def get_excel_path(folder: Path) -> Path:
    stamp = datetime.now().strftime('%Y%m%d')
    return folder / f'{stamp}_invoices.xlsx'


def load_existing_headings(excel_path: Path) -> set:
    """Return set of email headings already in the Excel file."""
    if not excel_path.exists():
        return set()
    try:
        wb = load_workbook(excel_path, read_only=True, data_only=True)
        ws = wb.active
        headings = set()
        for row in ws.iter_rows(min_row=2, values_only=True):
            if row[0]:
                headings.add(str(row[0]).strip())
        wb.close()
        return headings
    except Exception:
        return set()


def init_or_load_workbook(excel_path: Path):
    """Load existing workbook or create a new one with headers."""
    if excel_path.exists():
        wb = load_workbook(excel_path)
        ws = wb.active
        return wb, ws

    wb = Workbook()
    ws = wb.active
    ws.title = 'LPO Invoices'
    ws.freeze_panes = 'A2'

    # Write headers
    for col, (header, width) in enumerate(zip(HEADERS, COL_WIDTHS), start=1):
        cell = ws.cell(row=1, column=col, value=header)
        cell.font = HEADER_FONT
        cell.fill = HEADER_FILL
        cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True)
        cell.border = THIN_BORDER
        ws.column_dimensions[get_column_letter(col)].width = width

    ws.row_dimensions[1].height = 30
    wb.save(excel_path)
    return wb, ws


def append_row(ws, row_data: dict, row_num: int, status: str = 'OK'):
    fill = ERROR_FILL if status == 'ERROR' else (ALT_FILL if row_num % 2 == 0 else None)

    values = [
        row_data.get('subject', ''),
        row_data.get('lpo_number', ''),
        row_data.get('ref_number', ''),
        row_data.get('inv_number', ''),
        row_data.get('inv_amount'),        # numeric or None
        row_data.get('filename', ''),
        datetime.now().strftime('%Y-%m-%d %H:%M'),
        status,
    ]

    for col, value in enumerate(values, start=1):
        cell = ws.cell(row=row_num, column=col)
        cell.font = DATA_FONT
        cell.border = THIN_BORDER
        cell.alignment = Alignment(vertical='center', wrap_text=False)
        if fill:
            cell.fill = fill
        if col == 5:
            # Inv Amount — true float, formatted as number
            cell.value = value
            if isinstance(value, float):
                cell.number_format = AMOUNT_FORMAT
                cell.alignment = Alignment(horizontal='right', vertical='center')
        else:
            # All other columns forced to text — prevents Excel from
            # auto-interpreting values like "inv/-11209115" as dates/numbers
            cell.value = str(value) if value is not None else ''
            cell.number_format = '@'  # @ = Text format in Excel


# ─────────────────────────────────────────
# Main
# ─────────────────────────────────────────

def process_folder(folder_path: str):
    folder = Path(folder_path).resolve()
    if not folder.is_dir():
        print(f'ERROR: {folder} is not a valid directory.')
        sys.exit(1)

    msg_files = sorted(folder.glob('*.msg'))
    if not msg_files:
        print('No .msg files found in folder.')
        return

    excel_path = get_excel_path(folder)
    existing_headings = load_existing_headings(excel_path)

    print(f'Excel output : {excel_path}')
    print(f'MSG files    : {len(msg_files)} found')
    print(f'Already in Excel: {len(existing_headings)} entries\n')

    wb, ws = init_or_load_workbook(excel_path)
    next_row = ws.max_row + 1

    skipped = 0
    parsed  = 0
    errors  = 0

    for msg_path in msg_files:
        print(f'Processing: {msg_path.name}')

        # ── Read subject first to check for duplicates ────────────
        try:
            subject, html_bytes = read_msg(msg_path)
        except Exception as e:
            print(f'  ERROR reading file: {e}')
            append_row(ws, {
                'subject':    msg_path.name,
                'filename':   msg_path.name,
            }, next_row, status='ERROR')
            next_row += 1
            errors += 1
            wb.save(excel_path)
            continue

        subject = subject.strip()

        # ── Skip if already in Excel ──────────────────────────────
        if subject in existing_headings:
            print(f'  SKIP — already in Excel')
            skipped += 1
            continue

        # ── Parse HTML ────────────────────────────────────────────
        if not html_bytes:
            print(f'  WARNING — no HTML body found, skipping')
            append_row(ws, {
                'subject':  subject,
                'filename': msg_path.name,
            }, next_row, status='NO HTML')
            next_row += 1
            errors += 1
            wb.save(excel_path)
            continue

        fields = parse_lpo_html(html_bytes)

        # Report any missing fields
        missing = [k for k, v in fields.items() if v is None]
        if missing:
            print(f'  WARNING — fields not found: {", ".join(missing)}')

        row_data = {
            'subject':    subject,
            'filename':   msg_path.name,
            **fields,
        }

        status = 'PARTIAL' if missing else 'OK'
        append_row(ws, row_data, next_row, status=status)
        next_row += 1
        parsed += 1

        # Save after every file so progress is not lost
        wb.save(excel_path)
        print(f'  OK — LPO:{fields["lpo_number"]} | Ref:{fields["ref_number"]} | Inv:{fields["inv_number"]} | Amount:{fields["inv_amount"]}')

    wb.close()

    print(f'\n─── Done ───────────────────────────')
    print(f'Parsed  : {parsed}')
    print(f'Skipped : {skipped}')
    print(f'Errors  : {errors}')
    print(f'Excel   : {excel_path}')


if __name__ == '__main__':
    process_folder('invoices')