#!/usr/bin/env python3
# -*- coding: utf-8 -*-
from __future__ import annotations

import argparse
import csv
import datetime as dt
import html
import json
import pathlib
import re
import zipfile
import textwrap
from collections import Counter, OrderedDict
from dataclasses import asdict, dataclass, field
from typing import Any, Dict, List, Optional, Sequence, Tuple

VERSION = "6.1-mix"

@dataclass
class Section:
    title: str
    anchor: str
    lines: List[str] = field(default_factory=list)

@dataclass
class SqlEntry:
    section: str
    ranking_metric: str
    ranking_value: Optional[float]
    executions: Optional[int]
    per_exec: Optional[float]
    pct_total: Optional[float]
    total_value: Optional[float]
    aux_value: Optional[float]
    hash_value: str
    module: str = ""
    sql_text: str = ""
    sql_preview: str = ""

@dataclass
class WaitEvent:
    section: str
    event: str
    waits: Optional[int] = None
    time_s: Optional[float] = None
    avg_ms: Optional[float] = None
    pct_time: Optional[float] = None

SECTION_PREFIXES = (
    "Database", "Host Name", "Snapshot", "Cache Sizes", "Load Profile", "Instance Efficiency Indicators",
    "Shared Pool Statistics", "Top 5 Timed Events", "Host CPU", "Instance CPU", "Memory Statistics",
    "Time Model System Stats", "Foreground Wait Events", "Background Wait Events", "Wait Events (fg and bg)",
    "Wait Event Histogram", "SQL ordered by", "Instance Activity Stats", "OS Statistics", "OS Statistics - detail",
    "IO Stat by Function", "Tablespace IO Stats", "File IO Stats", "File Read Histogram Stats",
    "Instance Recovery Stats", "Memory Dynamic Components", "Buffer Pool Advisory", "Buffer Pool Statistics",
    "Buffer wait Statistics", "PGA Aggr Target Stats", "PGA Aggr Target Histogram", "PGA Memory Advisory",
    "Process Memory Summary Stats", "Top Process Memory", "Enqueue activity", "Undo Segment Summary",
    "Undo Segment Stats", "Latch Activity", "Segment Statistics", "Library Cache Activity", "Dictionary Cache Stats",
)

SQL_SECTION_RE = re.compile(r"^SQL ordered by\s+(.+?)(?:\s+DB/Inst:|\s+for DB:|\s*$)", re.I)
NUMERIC_RE = re.compile(r"^[#\d,]+(?:\.\d+)?$")
TOP5_ROW_RE = re.compile(r"^(?P<event>.{1,41}?)\s{2,}(?:(?P<waits>[\d,]+)\s+)?(?P<time_s>[\d,]+)\s+(?:(?P<avg_ms>[#\d,]+)\s+)?(?P<pct>[\d.]+)\s*$")
WAIT_ROW_RE = re.compile(r"^(?P<event>.{1,32}?)\s{2,}(?P<waits>[\d,]+)\s+(?P<timeouts>[\d,]+)\s+(?P<time_s>[\d,]+)\s+(?P<avg_ms>[#\d,]+)\s+(?P<wait_txn>[\d.]+)(?:\s+(?P<pct_time>[\d.]+))?\s*$")
SKIP_SQL_PREFIXES = (
    "SQL ordered by", "->", "CPU                  CPU per", "CPU per", "Elapsed", "Gets", "Reads",
    "Executions", "Parse Calls", "Sharable", "Buffer Gets", "Physical", "Rows", "------", "Captured SQL",
)


def read_text(path: pathlib.Path) -> str:
    for enc in ("utf-8", "cp1252", "latin-1"):
        try:
            return path.read_text(encoding=enc)
        except UnicodeDecodeError:
            pass
    return path.read_text(encoding="utf-8", errors="replace")


def normalize_text(text: str) -> str:
    text = text.replace("\r\n", "\n").replace("\r", "\n")
    # Keep the page-break meaning as a line break. This avoids gluing section titles to previous SQL text.
    text = text.replace("\x0c", "\n")
    text = re.sub(r"\n{4,}", "\n\n\n", text)
    return text.rstrip() + "\n"


def to_float(value: Optional[str]) -> Optional[float]:
    if not value:
        return None
    value = value.replace(",", "")
    if "#" in value:
        return None
    try:
        return float(value)
    except ValueError:
        return None


def to_int(value: Optional[str]) -> Optional[int]:
    f = to_float(value)
    return None if f is None else int(f)


def slugify(text: str) -> str:
    value = re.sub(r"[^a-zA-Z0-9]+", "-", text.strip().lower()).strip("-")
    return value or "section"


def clean_title(title: str) -> str:
    title = re.sub(r"\s+", " ", title.strip())
    title = re.sub(r"\s+DB/Inst:", " DB/Inst:", title)
    title = re.sub(r"\s+Snaps?:", " Snaps:", title)
    title = re.sub(r"Snaps: (\d+)\s+-\s*(\d+)", r"Snaps: \1-\2", title)
    return title.strip()


def split_inline_top5_title(line: str) -> Tuple[str, str]:
    """Separate the Top 5 title from the inline Avg/%Total header fragment.

    The title is moved to the HTML <h2>, but the rest of the original
    Statspack header must keep its absolute column position. Therefore the
    returned body line replaces the removed title with spaces instead of
    shifting the inline column headers to the left.
    """
    prefix = "Top 5 Timed Events"
    stripped = line.strip()
    if not stripped.startswith(prefix):
        return line, ""
    idx = line.find(prefix)
    remainder = line[idx + len(prefix):].rstrip()
    return prefix, (" " * (idx + len(prefix))) + remainder


def split_inline_memory_title(line: str) -> Tuple[str, str]:
    """Separate Memory Statistics from inline Begin/End column headings.

    Keep Begin/End at the same columns as the original text report by
    replacing the removed title with spaces.
    """
    prefix = "Memory Statistics"
    stripped = line.strip()
    if not stripped.startswith(prefix):
        return line, ""
    idx = line.find(prefix)
    remainder = line[idx + len(prefix):].rstrip()
    return prefix, (" " * (idx + len(prefix))) + remainder

def strip_redundant_report_suffix(title: str) -> str:
    """Remove generic Statspack/AWR suffixes from section titles."""
    top5_title, _top5_suffix = split_inline_top5_title(title)
    if top5_title == "Top 5 Timed Events":
        return top5_title
    memory_title, _memory_suffix = split_inline_memory_title(title)
    if memory_title == "Memory Statistics":
        return memory_title
    t = clean_title(title)
    m = re.match(r"SQL ordered by\s+(.+?)(?:\s+DB/Inst:|\s+for DB:|$)", t, re.I)
    if m:
        return "SQL ordered by " + m.group(1).strip()
    t = re.sub(r"\s+DB/Inst:\s+\S+/\S+\s+(?:Snaps?:\s*\d+\s*-\s*\d+|End\s+Snaps?:\s*\d+|End\s+Snap:\s*\d+).*$", "", t, flags=re.I)
    t = re.sub(r"\s+DB/Inst:.*$", "", t, flags=re.I)
    t = re.sub(r"\s+for DB:.*$", "", t, flags=re.I)
    return clean_title(t)


def title_for_nav(title: str) -> str:
    return strip_redundant_report_suffix(title)


def is_underline(line: str) -> bool:
    s = line.strip()
    return bool(s) and set(s) <= {"~", "-", " "} and (s.count("~") >= 3 or s.count("-") >= 8)


def is_title_line(line: str, next_line: str = "") -> bool:
    s = clean_title(line)
    if not s or len(s) > 180 or s.startswith("->"):
        return False
    if s.startswith("Database Buffers"):
        return False
    if any(s.startswith(p) for p in SECTION_PREFIXES):
        return True
    if next_line and is_underline(next_line):
        return any(s.startswith(p) for p in SECTION_PREFIXES)
    return False


def looks_like_sql_metric_line(line: str) -> bool:
    toks = line.split()
    if len(toks) < 4:
        return False
    return bool(re.match(r"^[\d,]+(?:\.\d+)?$", toks[0]) and re.match(r"^\d{6,}$", toks[-1]))


def strip_repeated_sql_page_header(lines: Sequence[str]) -> List[str]:
    """Remove repeated SQL-section notes/table header from continuation pages."""
    out = list(lines)
    i = 0
    n = len(out)
    while i < n and not out[i].strip():
        i += 1
    while i < n and out[i].strip().startswith('->'):
        i += 1
    while i < n and not out[i].strip():
        i += 1
    j = i
    found_dash = False
    while j < n and j < i + 8:
        st = out[j].strip()
        if st and set(st) <= {'-', ' '} and st.count('-') >= 8:
            found_dash = True
            j += 1
            break
        j += 1
    if found_dash:
        while j < n and not out[j].strip():
            j += 1
        return out[j:]
    return out



INITIAL_OVERVIEW_PREFIXES = (
    "Database", "Host Name", "Snapshot", "Cache Sizes", "Load Profile",
    "Instance Efficiency Indicators", "Shared Pool Statistics",
)


def is_initial_overview_section(title: str) -> bool:
    return any(title.startswith(prefix) for prefix in INITIAL_OVERVIEW_PREFIXES)


def extract_initial_overview_lines(text: str) -> List[str]:
    """Build the initial Overview preserving original Statspack column spacing.

    The normal section title cleanup is useful for navigation, but it collapses
    spaces in the first Statspack headers. For the Overview we read that part
    directly from the original report so headers and values stay aligned.
    """
    lines = normalize_text(text).splitlines()
    out: List[str] = []
    started = False
    for line in lines:
        stripped = line.strip()
        if not started:
            if stripped.startswith("Database"):
                started = True
            else:
                continue
        if started and stripped.startswith("Top 5 Timed Events"):
            break
        if stripped == "STATSPACK report for":
            continue
        if is_underline(line):
            continue
        out.append(line.rstrip())

    # Trim outer blank lines and compact excessive blank lines while preserving one
    # blank line between logical blocks.
    while out and not out[0].strip():
        out.pop(0)
    while out and not out[-1].strip():
        out.pop()

    compacted: List[str] = []
    blank = False
    for line in out:
        if not line.strip():
            if not blank:
                compacted.append("")
            blank = True
        else:
            compacted.append(line)
            blank = False
    return compacted


def compact_initial_overview(sections: List[Section], overview_lines: Optional[List[str]] = None) -> List[Section]:
    """Group the initial Statspack identification/load sections into one Overview."""
    if not sections:
        return sections
    new_sections: List[Section] = []
    consumed_initial = False

    for sec in sections:
        if sec.title == "Overview":
            # Drop the low-value intro line: STATSPACK report for
            continue
        if is_initial_overview_section(sec.title):
            consumed_initial = True
            continue
        new_sections.append(sec)

    if consumed_initial:
        return [Section("Overview", "overview", overview_lines or []), *new_sections]
    return sections

def parse_sections_for_html(text: str, merge_repeated: bool = True) -> List[Section]:
    lines = normalize_text(text).splitlines()
    raw_sections: List[Section] = []
    intro: List[str] = []
    i = 0
    while i < len(lines):
        nxt = lines[i + 1] if i + 1 < len(lines) else ""
        if is_title_line(lines[i], nxt):
            break
        intro.append(lines[i])
        i += 1
    raw_sections.append(Section("Overview", "overview", intro))
    while i < len(lines):
        nxt = lines[i + 1] if i + 1 < len(lines) else ""
        if is_title_line(lines[i], nxt):
            raw_title_line = lines[i]
            title = title_for_nav(raw_title_line)
            _top5_title, top5_inline_suffix = split_inline_top5_title(raw_title_line)
            _memory_title, memory_inline_suffix = split_inline_memory_title(raw_title_line)
            # Keep the underline out of the body, but keep the section body unchanged otherwise.
            # Exception: Memory Statistics uses the underline as the separator for the Begin/End
            # columns, so it must remain in the rendered preformatted text.
            if title == "Memory Statistics":
                i += 1
            else:
                i += 2 if i + 1 < len(lines) and is_underline(lines[i + 1]) else 1
            body: List[str] = []
            if title == "Top 5 Timed Events" and top5_inline_suffix.strip():
                body.append(top5_inline_suffix.rstrip())
            if title == "Memory Statistics" and memory_inline_suffix.strip():
                body.append(memory_inline_suffix.rstrip())
            while i < len(lines):
                nxt = lines[i + 1] if i + 1 < len(lines) else ""
                if is_title_line(lines[i], nxt):
                    break
                body.append(lines[i])
                i += 1
            raw_sections.append(Section(title, slugify(title), body))
        else:
            raw_sections[-1].lines.append(lines[i])
            i += 1

    if merge_repeated:
        ordered: "OrderedDict[str, Section]" = OrderedDict()
        for sec in raw_sections:
            key = sec.title
            if key not in ordered:
                ordered[key] = Section(sec.title, slugify(sec.title), list(sec.lines))
            else:
                if key.startswith("SQL ordered by"):
                    continuation = strip_repeated_sql_page_header(sec.lines)
                    first = next((x for x in continuation if x.strip()), "")
                    if first and looks_like_sql_metric_line(first):
                        ordered[key].lines.extend([""])
                    ordered[key].lines.extend(continuation)
                else:
                    # ------------ordered[key].lines.extend(["", "          ---- continuación de página Statspack ----", ""])
                    ordered[key].lines.extend(sec.lines)
        sections = list(ordered.values())
    else:
        sections = raw_sections

    sections = compact_initial_overview(sections, extract_initial_overview_lines(text))

    seen: Counter[str] = Counter()
    for sec in sections:
        base = slugify(sec.title)
        sec.anchor = base
        seen[base] += 1
        if seen[base] > 1:
            sec.anchor = f"{base}-{seen[base]}"
    return sections


# -------------------------- render HTML fiel -------------------------------

def split_blocks(lines: Sequence[str]) -> List[List[str]]:
    blocks: List[List[str]] = []
    cur: List[str] = []
    for line in lines:
        if not line.strip():
            if cur:
                blocks.append(cur)
                cur = []
        else:
            cur.append(line.rstrip("\n"))
    if cur:
        blocks.append(cur)
    return blocks


def render_pre(block: Sequence[str]) -> str:
    return "<pre>" + html.escape("\n".join(block)) + "</pre>"


def join_statspack_sql_lines(lines: Sequence[str]) -> str:
    """Rebuild SQL text split by Statspack page-width wrapping.

    Statspack can split identifiers/keywords across lines, for example ES\nTADO.
    For the HTML view, keep metric rows untouched and normalize only SQL text.
    """
    clean: List[str] = []
    for line in lines:
        st = line.rstrip()
        if not st.strip():
            continue
        if "continuación de página Statspack" in st or "continuacion de pagina Statspack" in st:
            continue
        if st.strip().startswith("----"):
            continue
        clean.append(st)
    if not clean:
        return ""

    out = clean[0].strip()
    for raw in clean[1:]:
        nxt = raw.strip()
        if not nxt:
            continue
        prev = out.rstrip()
        prev_char = prev[-1:]
        next_char = nxt[:1]
        current_visual_len = len(prev.split("\n")[-1])

        if next_char in ".,)]" or prev_char in ".([":
            sep = ""
        elif prev_char in ",;+-*/=<>":
            sep = " "
        elif prev_char.isalnum() and next_char.isalnum():
            sep = "" if current_visual_len >= 55 else " "
        else:
            sep = " "
        out = prev + sep + nxt

    return re.sub(r"\s+", " ", out).strip()


def wrap_sql_for_display(sql: str, width: int = 150) -> List[str]:
    """Make SQL readable: normalized text plus controlled wrapping."""
    if not sql:
        return []
    sql = re.sub(r"\s+", " ", sql).strip()
    sql = re.sub(
        r"\b(FROM|WHERE|GROUP BY|ORDER BY|HAVING|UNION ALL|UNION|INNER JOIN|LEFT JOIN|RIGHT JOIN|FULL JOIN|JOIN|VALUES|SET)\b",
        r"\n\1",
        sql,
        flags=re.I,
    )
    out: List[str] = []
    for part in sql.splitlines():
        part = part.strip()
        if not part:
            continue
        out.extend(textwrap.wrap(part, width=width, break_long_words=False, break_on_hyphens=False) or [part])
    return out


def render_sql_pre(block: Sequence[str]) -> str:
    """Render one SQL ordered-by entry with metric header/row and cleaned SQL text."""
    metric_idx = next((i for i, line in enumerate(block) if looks_like_sql_metric_line(line)), None)
    if metric_idx is None:
        return render_pre(block)

    header_and_metric = [line.rstrip() for line in block[:metric_idx + 1]]
    rest = list(block[metric_idx + 1:])
    module_line = ""
    sql_lines: List[str] = []
    for line in rest:
        st = line.rstrip()
        if not st.strip():
            continue
        if st.strip().startswith("Module:"):
            module_line = st.strip()
            continue
        if "continuación de página Statspack" in st or "continuacion de pagina Statspack" in st:
            continue
        sql_lines.append(st)

    display_lines = header_and_metric
    if module_line:
        display_lines.append("")
        display_lines.append(module_line)
    sql_clean = join_statspack_sql_lines(sql_lines)
    if sql_clean:
        display_lines.append("")
        display_lines.extend(wrap_sql_for_display(sql_clean))
    return render_pre(display_lines)


def render_notes(block: Sequence[str]) -> str:
    return "".join(f"<p class='note'>{html.escape(line.strip())}</p>" for line in block)


def is_sql_section(title: str) -> bool:
    return title.lower().startswith("sql ordered by")


def is_sql_header_block(block: Sequence[str]) -> bool:
    joined = "\n".join(block)
    return "Hash Value" in joined or any(set(line.strip()) <= {"-", " "} and line.strip().count("-") >= 8 for line in block)


def sql_header_end_index(block: Sequence[str]) -> Optional[int]:
    """Return the index after the SQL metric header, if present."""
    saw_hash = False
    for i, line in enumerate(block):
        if "Hash Value" in line:
            saw_hash = True
        st = line.strip()
        if saw_hash and st and set(st) <= {"-", " "} and st.count("-") >= 8:
            return i + 1
    return None


def merge_sql_render_blocks(blocks: Sequence[Sequence[str]]) -> List[List[str]]:
    """Merge SQL fragments and repeat the metric header for each SQL entry.

    Statspack prints the SQL section column header once, but this renderer shows
    each SQL entry in its own <pre>. Repeating the header in every metric block
    keeps CPU/Elapsed/Gets/etc. readable while scrolling.
    """
    merged: List[List[str]] = []
    last_header: List[str] = []
    for raw in blocks:
        block = [x for x in raw]
        if not block:
            continue

        header_end = sql_header_end_index(block)
        if header_end is not None:
            last_header = block[:header_end]
            block = block[header_end:]
            while block and not block[0].strip():
                block = block[1:]
            if not block:
                continue

        is_note = all(line.strip().startswith("->") for line in block)
        starts_metric = looks_like_sql_metric_line(block[0])
        is_separator_only = all((not line.strip()) or set(line.strip()) <= {"-", " "} for line in block)
        is_header = is_sql_header_block(block)

        if is_header and not starts_metric:
            last_header = block
            continue

        if starts_metric and last_header:
            block = list(last_header) + [""] + block

        if merged and not is_note and not starts_metric and not is_header and not is_separator_only:
            merged[-1].append("")
            merged[-1].extend(block)
        else:
            merged.append(block)
    return merged


def render_block(block: Sequence[str], section_title: str, html_tables: str) -> str:
    if all(line.strip().startswith("->") for line in block):
        return render_notes(block)
    if is_sql_section(section_title) and any(looks_like_sql_metric_line(line) for line in block):
        return render_sql_pre(block)
    # Conservative default: preserve the original Statspack layout. This avoids broken tables in complex sections.
    if html_tables == "none":
        return render_pre(block)
    # Only convert the very stable Top 5 table. Everything else remains faithful preformatted text.
    if html_tables in {"safe", "all"} and section_title == "Top 5 Timed Events":
        parsed = parse_top5_block(block)
        if parsed:
            return render_html_table(parsed, top_rows=5)
    return render_pre(block)


def parse_top5_block(block: Sequence[str]) -> Optional[Dict[str, Any]]:
    rows = []
    for line in block:
        m = TOP5_ROW_RE.match(line)
        if m and "Event" not in m.group("event") and "---" not in m.group("event"):
            rows.append([m.group("event").strip(), m.group("waits") or "", m.group("time_s") or "", m.group("avg_ms") or "", m.group("pct") or ""])
    if not rows:
        return None
    return {"headers": ["Event", "Waits", "Time (s)", "Avg ms", "%Total Call Time"], "rows": rows}


def render_html_table(table: Dict[str, Any], top_rows: int = 0) -> str:
    thead = "".join(f"<th>{html.escape(h)}</th>" for h in table["headers"])
    trs = []
    for idx, row in enumerate(table["rows"]):
        cls = " class='toprow'" if idx < top_rows else ""
        tds = "".join(f"<td>{html.escape(str(c))}</td>" for c in row)
        trs.append(f"<tr{cls}>{tds}</tr>")
    return "<div class='table-wrap'><table><thead><tr>" + thead + "</tr></thead><tbody>" + "".join(trs) + "</tbody></table></div>"


def extract_meta_line_based(text: str) -> Dict[str, str]:
    lines = normalize_text(text).splitlines()
    meta: Dict[str, str] = {}
    for i, line in enumerate(lines[:120]):
        s = line.strip()
        if s.startswith("Database") and i + 2 < len(lines):
            row = lines[i + 2].split()
            if len(row) >= 7:
                # Standard Statspack line: DB Id, Instance, Inst Num, Startup date, Startup time, Release, RAC.
                meta.update({"db_id": row[0], "db_name": row[1], "instance": row[1], "inst_num": row[2], "startup": " ".join(row[3:5]), "release": row[5], "rac": row[6]})
        elif s.startswith("Host Name") and i + 2 < len(lines):
            row_line = lines[i + 2]
            parts = row_line.split()
            if len(parts) >= 7:
                host = parts[0]
                memory = parts[-1]
                sockets = parts[-2]
                cores = parts[-3]
                cpus = parts[-4]
                platform = " ".join(parts[1:-4])
                meta.update({"host": host, "platform": platform, "cpus": cpus, "cores": cores, "sockets": sockets, "memory_gb": memory})
        elif s.startswith("Begin Snap:"):
            m = re.match(r"Begin Snap:\s+(\d+)\s+(.{18})\s+(\d+)\s+([\d.]+)", line)
            if m:
                meta.update({"begin_snap": m.group(1), "begin_time": m.group(2).strip(), "begin_sessions": m.group(3), "begin_cursors_per_session": m.group(4)})
        elif s.startswith("End Snap:"):
            m = re.match(r"End Snap:\s+(\d+)\s+(.{18})\s+(\d+)\s+([\d.]+)", s)
            if m:
                meta.update({"end_snap": m.group(1), "end_time": m.group(2).strip(), "end_sessions": m.group(3), "end_cursors_per_session": m.group(4)})
        elif s.startswith("Elapsed:"):
            m = re.search(r"Elapsed:\s+([\d.]+).*?Av Act Sess:\s+([\d.]+)", line)
            if m:
                meta.update({"elapsed": m.group(1), "aas": m.group(2)})
        elif s.startswith("DB time:"):
            m = re.search(r"DB time:\s+([\d.]+).*?DB CPU:\s+([\d.]+)", line)
            if m:
                meta.update({"db_time": m.group(1), "db_cpu": m.group(2)})
    return meta


def extract_kpi_cards(text: str) -> List[Tuple[str, str]]:
    patterns = [
        ("Avg Active Sessions", r"Av Act Sess:\s+([\d.,]+)"),
        ("DB Time min", r"DB time:\s+([\d.,]+)"),
        ("DB CPU min", r"DB CPU:\s+([\d.,]+)"),
        ("Logical reads/s", r"Logical reads:\s+([\d,]+(?:\.\d+)?)"),
        ("Physical reads/s", r"Physical reads:\s+([\d,]+(?:\.\d+)?)"),
        ("Parses/s", r"Parses:\s+([\d,]+(?:\.\d+)?)"),
        ("Executes/s", r"Executes:\s+([\d,]+(?:\.\d+)?)"),
        ("Transactions/s", r"Transactions:\s+([\d,]+(?:\.\d+)?)"),
    ]
    out = []
    for label, pat in patterns:
        m = re.search(pat, text)
        if m:
            out.append((label, m.group(1)))
    return out


def build_full_html(title: str, source: str, text: str, sections: List[Section], html_tables: str) -> str:
    meta = extract_meta_line_based(text)
    toc = "".join(f"<li><a href='#{sec.anchor}'>{html.escape(sec.title)}</a></li>" for sec in sections)
    cards_html = "".join(f"<div class='kpi'><span>{html.escape(k)}</span><strong>{html.escape(v)}</strong></div>" for k, v in extract_kpi_cards(text))
    section_html = []
    for sec in sections:
        blocks = split_blocks(sec.lines)
        if is_sql_section(sec.title):
            blocks = merge_sql_render_blocks(blocks)
        rendered = "".join(render_block(b, sec.title, html_tables) for b in blocks)
        section_html.append(f"<section id='{sec.anchor}' class='section'><h2>{html.escape(sec.title)}</h2>{rendered}</section>")
    db_line = " | ".join(x for x in [meta.get("db_name"), meta.get("instance"), meta.get("release"), f"DBID {meta.get('db_id')}" if meta.get("db_id") else ""] if x) or "Statspack"
    host_line = " | ".join(x for x in [meta.get("host"), meta.get("platform"), f"{meta.get('cpus')} CPU" if meta.get("cpus") else "", f"{meta.get('memory_gb')} GB" if meta.get("memory_gb") else ""] if x) or "Host no detectado"
    snap_line = " | ".join(x for x in [f"Snaps {meta.get('begin_snap')} - {meta.get('end_snap')}" if meta.get("begin_snap") else "", f"{meta.get('begin_time')} -> {meta.get('end_time')}" if meta.get("begin_time") else "", f"Elapsed {meta.get('elapsed')} min" if meta.get("elapsed") else ""] if x) or "Snapshots no detectados"
    css = """
:root{--bg:#f4f6fa;--panel:#fff;--ink:#172033;--muted:#65738a;--line:#dce3ef;--head:#eef3fb;--accent:#0b67c2;--pre:#101827;--preink:#e7edf8;--warn:#fff7dc}*{box-sizing:border-box}html{scroll-behavior:smooth}body{margin:0;background:var(--bg);color:var(--ink);font-family:Arial,Helvetica,sans-serif;line-height:1.42}a{color:var(--accent);text-decoration:none}a:hover{text-decoration:underline}.page{max-width:1760px;margin:0 auto;padding:20px}.hero{background:linear-gradient(135deg,#fff,#edf4ff);border:1px solid var(--line);border-radius:18px;padding:20px;margin-bottom:18px;box-shadow:0 3px 14px #0001}.hero h1{margin:0 0 8px 0;font-size:30px}.muted{color:var(--muted)}.small{font-size:13px}.kpis{display:grid;grid-template-columns:repeat(auto-fit,minmax(145px,1fr));gap:10px;margin-top:14px}.kpi{background:#fff;border:1px solid var(--line);border-radius:12px;padding:10px}.kpi span{display:block;color:var(--muted);font-size:12px}.kpi strong{display:block;font-size:22px;margin-top:4px}.layout{display:grid;grid-template-columns:310px minmax(0,1fr);gap:18px}.toc{position:sticky;top:14px;align-self:start;max-height:calc(100vh - 28px);overflow:auto;background:var(--panel);border:1px solid var(--line);border-radius:16px;padding:14px}.toc h2{margin:0 0 10px 0;font-size:19px}.toc ul{margin:0;padding-left:18px}.toc li{margin:7px 0;font-size:13px}.section{background:var(--panel);border:1px solid var(--line);border-radius:16px;padding:16px;margin-bottom:16px;box-shadow:0 3px 12px #0000000d;scroll-margin-top:16px}.section h2{margin:0 0 12px 0;padding-bottom:8px;border-bottom:1px solid var(--line);color:#0b3d75;font-size:21px}pre{margin:0 0 13px 0;padding:14px;border-radius:12px;background:var(--pre);color:var(--preink);border:1px solid #202b48;font-family:Consolas,Menlo,Monaco,'Courier New',monospace;font-size:12.5px;white-space:pre;overflow:auto;line-height:1.35}.note{color:var(--muted);font-style:italic;margin:6px 0 10px 0}.table-wrap{overflow:auto;border:1px solid var(--line);border-radius:12px;margin-bottom:13px}table{width:100%;border-collapse:collapse;background:#fff;font-size:12.5px}th,td{border-bottom:1px solid #e8edf5;padding:7px 9px;text-align:left;vertical-align:top;white-space:nowrap}th{background:var(--head);position:sticky;top:0;color:#20324a}tr.toprow{background:var(--warn)}.badge{display:inline-block;border:1px solid #bad8ff;background:#e9f3ff;color:#1d4f86;border-radius:999px;padding:4px 8px;margin:2px;font-size:12px}@media(max-width:1100px){.layout{grid-template-columns:1fr}.toc{position:static;max-height:none}pre{white-space:pre-wrap}}
"""
    return "".join([
        "<!doctype html><html lang='es'><head><meta charset='utf-8'><meta name='viewport' content='width=device-width, initial-scale=1'>",
        f"<title>{html.escape(title)}</title><style>{css}</style></head><body><div class='page'>",
        "<header class='hero'>",
        f"<h1>{html.escape(title)}</h1>",
        f"<p><span class='badge'>{html.escape(db_line)}</span><span class='badge'>{html.escape(host_line)}</span><span class='badge'>{html.escape(snap_line)}</span></p>",
        f"<div class='kpis'>{cards_html}</div></header>",
        f"<div class='layout'><nav class='toc'><h2>Index</h2><ul>{toc}</ul></nav><main>{''.join(section_html)}</main></div>",
        "</div></body></html>",
    ])


# ---------------------- extracción auxiliar -------------------------------

def parse_metadata(lines: List[str]) -> Dict[str, Any]:
    meta: Dict[str, Any] = {"version": VERSION, "generated_at": dt.datetime.now().isoformat(timespec="seconds")}
    line_meta = extract_meta_line_based("\n".join(lines))
    mapping = {
        "db_name": "db_name", "db_id": "db_id", "instance": "instance", "inst_num": "inst_num",
        "startup": "startup_time", "release": "release", "rac": "rac", "host": "host", "platform": "platform",
        "begin_snap": "begin_snap", "begin_time": "begin_snap_time", "begin_sessions": "begin_sessions",
        "begin_cursors_per_session": "begin_cursors_per_session", "end_snap": "end_snap", "end_time": "end_snap_time",
        "end_sessions": "end_sessions", "end_cursors_per_session": "end_cursors_per_session", "elapsed": "elapsed_minutes",
        "aas": "aas", "db_time": "db_time_minutes", "db_cpu": "db_cpu_minutes", "cpus": "cpus", "cores": "cores", "sockets": "sockets", "memory_gb": "memory_gb",
    }
    for k, outk in mapping.items():
        if k not in line_meta:
            continue
        if outk in {"begin_snap", "end_snap", "begin_sessions", "end_sessions", "cpus", "cores", "sockets"}:
            meta[outk] = to_int(line_meta[k])
        elif outk in {"begin_cursors_per_session", "end_cursors_per_session", "elapsed_minutes", "aas", "db_time_minutes", "db_cpu_minutes", "memory_gb"}:
            meta[outk] = to_float(line_meta[k])
        else:
            meta[outk] = line_meta[k]
    return meta


def parse_kpis(lines: List[str]) -> Dict[str, Any]:
    kpis: Dict[str, Any] = {}
    capture = False
    for line in lines:
        if line.startswith("Load Profile"):
            capture = True
            continue
        if capture and line.startswith("Instance Efficiency"):
            break
        if capture:
            m = re.match(r"\s*([A-Za-z0-9 /()]+?):\s+([\d,]+(?:\.\d+)?)", line)
            if m:
                key = m.group(1).strip().lower().replace(" ", "_").replace("/", "_").replace("(", "").replace(")", "")
                kpis[key] = to_float(m.group(2))
    return kpis


def metric_row_for_section(section: str, line: str) -> Optional[Dict[str, Any]]:
    toks = line.strip().split()
    if not toks or not all(NUMERIC_RE.match(t) for t in toks):
        return None
    if section in {"CPU", "Elapsed time", "Gets", "Reads"} and len(toks) == 7:
        return {"ranking_value": to_float(toks[0]), "executions": to_int(toks[1]), "per_exec": to_float(toks[2]), "pct_total": to_float(toks[3]), "total_value": to_float(toks[4]), "aux_value": to_float(toks[5]), "hash_value": toks[6]}
    if section == "Executions" and len(toks) >= 6:
        return {"ranking_value": to_float(toks[0]), "executions": to_int(toks[0]), "per_exec": to_float(toks[2]), "pct_total": None, "total_value": to_float(toks[1]), "aux_value": to_float(toks[4]), "hash_value": toks[-1]}
    if section in {"Parse Calls", "Sharable Memory"} and len(toks) >= 4:
        return {"ranking_value": to_float(toks[0]), "executions": to_int(toks[1]), "per_exec": None, "pct_total": to_float(toks[2]) if len(toks) == 4 else None, "total_value": to_float(toks[1]), "aux_value": None, "hash_value": toks[-1]}
    return None


def section_name(raw: str) -> str:
    raw = re.split(r"\s+DB/Inst:|\s+for DB:", raw, maxsplit=1)[0].strip()
    raw = raw.replace("Elapsed Time", "Elapsed time")
    return raw


def is_sql_text_noise(line: str) -> bool:
    s = line.strip()
    if not s or any(s.startswith(p) for p in SKIP_SQL_PREFIXES):
        return True
    if "Hash Value" in s and not re.search(r"\b(select|update|insert|delete|begin|declare|call)\b", s, re.I):
        return True
    return False


def clean_sql_lines(lines: List[str]) -> Tuple[str, str, str]:
    module = ""
    sql_lines: List[str] = []
    for s in (ln.rstrip() for ln in lines):
        if s.strip().startswith("Module:"):
            module = s.split("Module:", 1)[1].strip()
            continue
        if is_sql_text_noise(s):
            continue
        if all(NUMERIC_RE.match(t) for t in s.split()) and len(s.split()) >= 4:
            continue
        sql_lines.append(s)
    sql_text = "\n".join(sql_lines).strip()
    preview = re.sub(r"\s+", " ", sql_text).strip()
    if len(preview) > 300:
        preview = preview[:297] + "..."
    return module, sql_text, preview


def parse_sql_entries(lines: List[str]) -> List[SqlEntry]:
    entries: List[SqlEntry] = []
    current_section: Optional[str] = None
    current_metric: Optional[Dict[str, Any]] = None
    current_sql: List[str] = []

    def flush() -> None:
        nonlocal current_metric, current_sql
        if current_section and current_metric:
            module, sql_text, preview = clean_sql_lines(current_sql)
            entries.append(SqlEntry(current_section, current_section, current_metric.get("ranking_value"), current_metric.get("executions"), current_metric.get("per_exec"), current_metric.get("pct_total"), current_metric.get("total_value"), current_metric.get("aux_value"), str(current_metric.get("hash_value", "")), module, sql_text, preview))
        current_metric = None
        current_sql = []

    for line in lines:
        msec = SQL_SECTION_RE.match(line.strip())
        if msec:
            flush()
            current_section = section_name(msec.group(1))
            continue
        if current_section:
            if is_title_line(line):
                flush()
                current_section = None
                continue
            mr = metric_row_for_section(current_section, line)
            if mr:
                flush()
                current_metric = mr
                current_sql = []
            elif current_metric:
                current_sql.append(line)
    flush()
    seen = set()
    out: List[SqlEntry] = []
    for e in entries:
        key = (e.section, e.hash_value, e.ranking_value, e.executions, e.sql_text)
        if key not in seen:
            seen.add(key)
            out.append(e)
    return out


def parse_wait_events(lines: List[str]) -> List[WaitEvent]:
    events: List[WaitEvent] = []
    current: Optional[str] = None
    for line in lines:
        s = line.strip()
        if s.startswith("Top 5 Timed Events"):
            current = "Top 5 Timed Events"
            continue
        if s.startswith("Foreground Wait Events"):
            current = "Foreground Wait Events"
            continue
        if s.startswith("Background Wait Events"):
            current = "Background Wait Events"
            continue
        if s.startswith(("Wait Event Histogram", "SQL ordered by", "Instance Activity Stats")):
            current = None
        if not current:
            continue
        if current == "Top 5 Timed Events":
            m = TOP5_ROW_RE.match(line)
            if m and "Event" not in m.group("event") and "---" not in m.group("event"):
                events.append(WaitEvent(current, m.group("event").strip(), to_int(m.group("waits")), to_float(m.group("time_s")), to_float(m.group("avg_ms")), to_float(m.group("pct"))))
        else:
            m = WAIT_ROW_RE.match(line)
            if m and "Event" not in m.group("event") and "---" not in m.group("event"):
                events.append(WaitEvent(current, m.group("event").strip(), to_int(m.group("waits")), to_float(m.group("time_s")), to_float(m.group("avg_ms")), to_float(m.group("pct_time"))))
    seen = set()
    out: List[WaitEvent] = []
    for w in events:
        key = (w.section, w.event, w.waits, w.time_s, w.avg_ms, w.pct_time)
        if key not in seen:
            seen.add(key)
            out.append(w)
    return out


def build_structured_data(text: str, source_file: pathlib.Path) -> Dict[str, Any]:
    lines = normalize_text(text).splitlines()
    sql_entries = parse_sql_entries(lines)
    waits = parse_wait_events(lines)
    meta = parse_metadata(lines)
    meta["source_file"] = str(source_file)
    return {"metadata": meta, "kpis": parse_kpis(lines), "sql_entries": [asdict(e) for e in sql_entries], "wait_events": [asdict(w) for w in waits], "summary": {"sql_entries_total": len(sql_entries), "sql_entries_by_section": dict(Counter(e.section for e in sql_entries)), "wait_events_total": len(waits)}}


def write_json(path: pathlib.Path, data: Dict[str, Any]) -> None:
    path.parent.mkdir(parents=True, exist_ok=True)
    path.write_text(json.dumps(data, ensure_ascii=False, indent=2), encoding="utf-8")


def write_sql_csv(path: pathlib.Path, entries: List[Dict[str, Any]]) -> None:
    path.parent.mkdir(parents=True, exist_ok=True)
    fields = ["section", "hash_value", "module", "ranking_value", "executions", "per_exec", "pct_total", "total_value", "aux_value", "sql_preview", "sql_text"]
    with path.open("w", encoding="utf-8-sig", newline="") as f:
        w = csv.DictWriter(f, fieldnames=fields, delimiter=";")
        w.writeheader()
        for e in entries:
            w.writerow({k: e.get(k, "") for k in fields})


def write_waits_csv(path: pathlib.Path, waits: List[Dict[str, Any]]) -> None:
    path.parent.mkdir(parents=True, exist_ok=True)
    fields = ["section", "event", "waits", "time_s", "avg_ms", "pct_time"]
    with path.open("w", encoding="utf-8-sig", newline="") as f:
        w = csv.DictWriter(f, fieldnames=fields, delimiter=";")
        w.writeheader()
        for e in waits:
            w.writerow({k: e.get(k, "") for k in fields})


def parse_args() -> argparse.Namespace:
    p = argparse.ArgumentParser(description="Statspack a HTML completo navegable + JSON/CSV auxiliar")
    p.add_argument("--lst-input", required=True, help="Informe Statspack .lst/.txt")
    p.add_argument("--output-dir", default=".", help="Directorio de salida")
    p.add_argument("--basename", help="Nombre base de salida")
    p.add_argument("--title", default="Oracle Statspack Report", help="Título del HTML")
    p.add_argument("--html-output", help="Ruta HTML completo")
    p.add_argument("--json-output", help="Ruta JSON auxiliar")
    p.add_argument("--sql-csv-output", help="Ruta CSV SQL auxiliar")
    p.add_argument("--waits-csv-output", help="Ruta CSV waits auxiliar")
    p.add_argument("--no-aux", action="store_true", help="No generar JSON/CSV auxiliares")
    p.add_argument("--no-merge", action="store_true", help="No unir secciones repetidas del Statspack")
    p.add_argument("--html-tables", choices=["none", "safe"], default="none", help="Conversión HTML de tablas. Por defecto 'none' preserva todo como texto plano preformateado; 'safe' solo convierte tablas muy estables.")
    p.add_argument("--zip-output", help="Ruta ZIP opcional con todas las salidas")
    return p.parse_args()


def make_zip(zip_path: pathlib.Path, paths: Sequence[pathlib.Path]) -> None:
    zip_path.parent.mkdir(parents=True, exist_ok=True)
    with zipfile.ZipFile(zip_path, "w", zipfile.ZIP_DEFLATED) as zf:
        for p in paths:
            if p.exists():
                zf.write(p, arcname=p.name)


def main() -> int:
    args = parse_args()
    src = pathlib.Path(args.lst_input)
    outdir = pathlib.Path(args.output_dir)
    basename = args.basename or src.stem
    html_path = pathlib.Path(args.html_output) if args.html_output else outdir / f"{basename}_full.html"
    json_path = pathlib.Path(args.json_output) if args.json_output else outdir / f"{basename}_statspack.json"
    sql_csv_path = pathlib.Path(args.sql_csv_output) if args.sql_csv_output else outdir / f"{basename}_top_sql.csv"
    waits_csv_path = pathlib.Path(args.waits_csv_output) if args.waits_csv_output else outdir / f"{basename}_waits.csv"
    text = read_text(src)
    sections = parse_sections_for_html(text, merge_repeated=not args.no_merge)
    html_path.parent.mkdir(parents=True, exist_ok=True)
    html_path.write_text(build_full_html(args.title, str(src), text, sections, args.html_tables), encoding="utf-8")
    print(f"HTML completo: {html_path}")
    print(f"Secciones HTML: {len(sections)}")
    written = [html_path]
    if not args.no_aux:
        data = build_structured_data(text, src)
        write_json(json_path, data)
        write_sql_csv(sql_csv_path, data["sql_entries"])
        write_waits_csv(waits_csv_path, data["wait_events"])
        print(f"JSON auxiliar: {json_path}")
        print(f"SQL CSV auxiliar: {sql_csv_path}")
        print(f"WAITS CSV auxiliar: {waits_csv_path}")
        print(f"SQL entries: {data['summary']['sql_entries_total']}")
        print(f"Wait events: {data['summary']['wait_events_total']}")
        written.extend([json_path, sql_csv_path, waits_csv_path])
    if args.zip_output:
        zip_path = pathlib.Path(args.zip_output)
        make_zip(zip_path, [pathlib.Path(__file__), *written])
        print(f"ZIP: {zip_path}")
    return 0


if __name__ == "__main__":
    raise SystemExit(main())
