src/excel_reporter.py

import os
from openpyxl import Workbook
from openpyxl.styles import (
    Font, PatternFill, Alignment, Border, Side, GradientFill
)
from openpyxl.utils import get_column_letter
from openpyxl.formatting.rule import ColorScaleRule, DataBarRule
from openpyxl.worksheet.hyperlink import Hyperlink
from colorama import Fore, Style


SCORE_COLORS = {
    (8, 10): ("1A7A1A", "E8F8E8"),  # Green - dark text, light bg
    (6,  7): ("7A5A00", "FFF8E0"),  # Amber
    (4,  5): ("7A3A00", "FFF0E0"),  # Orange
    (0,  3): ("7A1A1A", "FFF0F0"),  # Red
}

PRIORITY_COLORS = {
    "High": "C6EFCE",
    "Medium": "FFEB9C",
    "Low": "FFCCCC",
}

HEADERS = [
    ("Rank",                  5),
    ("Job Title",             28),
    ("Company",               20),
    ("Location",              18),
    ("Platform",              12),
    ("Salary Range",          18),
    ("Experience Required",   18),
    ("Posted Date",           14),
    ("Relevance Score",       16),
    ("ATS Before (%)",        14),
    ("ATS After (%)",         14),
    ("ATS Improvement",       14),
    ("Skills Match %",        14),
    ("Experience Match",      18),
    ("Application Priority",  18),
    ("Matching Skills",       35),
    ("Missing Skills",        30),
    ("Key Strengths",         30),
    ("Recommendation",        45),
    ("ATS Keywords",          35),
    ("Resume Generated",      16),
    ("Resume Path",           40),
    ("Job URL",               50),
    ("Application Status",    20),
    ("Notes",                 30),
]


class ExcelReporter:
    def __init__(self, output_path: str):
        self.output_path = output_path
        os.makedirs(os.path.dirname(output_path), exist_ok=True)

    def generate(self, assessed_jobs: list[dict]) -> str:
        print(f"\n{Fore.CYAN}Generating Excel report...{Style.RESET_ALL}")
        wb = Workbook()

        self._create_summary_sheet(wb, assessed_jobs)
        self._create_all_jobs_sheet(wb, assessed_jobs)
        self._create_top_matches_sheet(wb, assessed_jobs)
        self._create_tracker_sheet(wb, assessed_jobs)

        wb.save(self.output_path)
        print(f"{Fore.GREEN}Report saved: {self.output_path}{Style.RESET_ALL}")

        # Also emit a flat CSV next to the workbook for easy import / persistence.
        try:
            self.csv_path = self._write_csv(assessed_jobs)
            print(f"{Fore.GREEN}CSV saved: {self.csv_path}{Style.RESET_ALL}")
        except Exception as e:
            self.csv_path = ""
            print(f"{Fore.YELLOW}CSV export skipped: {e}{Style.RESET_ALL}")

        return self.output_path

    def _write_csv(self, jobs: list[dict]) -> str:
        """Write a flat one-row-per-job CSV alongside the Excel report."""
        import csv

        csv_path = os.path.splitext(self.output_path)[0] + ".csv"
        columns = [
            ("rank", "Rank"),
            ("title", "Job Title"),
            ("company", "Company"),
            ("location", "Location"),
            ("platform", "Platform"),
            ("salary", "Salary"),
            ("relevance_score", "Relevance Score"),
            ("application_priority", "Application Priority"),
            ("ats_score_before", "ATS Before (%)"),
            ("ats_score_after", "ATS After (%)"),
            ("ats_improvement", "ATS Improvement"),
            ("independent_jd_match", "Independent Score"),
            ("status", "Status"),
            ("resume_generated", "Resume Generated"),
            ("matching_skills", "Matching Skills"),
            ("missing_skills", "Missing Skills"),
            ("ats_keywords", "ATS Keywords"),
            ("recommendation", "Recommendation"),
            ("resume_path", "Resume Path"),
            ("resume_pdf_path", "Resume PDF Path"),
            ("url", "Job URL"),
        ]

        def _cell(v):
            if isinstance(v, (list, tuple)):
                return ", ".join(str(x) for x in v)
            if isinstance(v, dict):
                return "; ".join(f"{k}={val}" for k, val in v.items())
            return "" if v is None else str(v)

        with open(csv_path, "w", encoding="utf-8-sig", newline="") as f:
            w = csv.writer(f)
            w.writerow([h for _, h in columns])
            for i, job in enumerate(jobs, 1):
                row = []
                for key, _ in columns:
                    row.append(str(i) if key == "rank" else _cell(job.get(key, "")))
                w.writerow(row)
        return csv_path

    # ──────────────────────────────────────────────────────────────
    # SHEET 1: SUMMARY DASHBOARD
    # ──────────────────────────────────────────────────────────────
    def _create_summary_sheet(self, wb: Workbook, jobs: list[dict]):
        ws = wb.active
        ws.title = "📊 Summary"
        ws.sheet_view.showGridLines = False

        total = len(jobs)
        high = sum(1 for j in jobs if j.get("relevance_score", 0) >= 8)
        medium = sum(1 for j in jobs if 6 <= j.get("relevance_score", 0) <= 7)
        low = sum(1 for j in jobs if j.get("relevance_score", 0) < 6)
        avg_score = sum(j.get("relevance_score", 0) for j in jobs) / total if total else 0
        resumes_gen = sum(1 for j in jobs if j.get("resume_generated") == "Yes")

        # Title
        ws.merge_cells("B2:F2")
        ws["B2"] = "JOB SEARCH RESULTS — DASHBOARD"
        ws["B2"].font = Font(name="Calibri", size=18, bold=True, color="1648BE")
        ws["B2"].alignment = Alignment(horizontal="center")

        stats = [
            ("Total Jobs Found",        total,          "4472C4", "FFFFFF"),
            ("High Priority (8-10)",    high,           "70AD47", "FFFFFF"),
            ("Good Match (6-7)",        medium,         "ED7D31", "FFFFFF"),
            ("Lower Priority (<6)",     low,            "FF0000", "FFFFFF"),
            ("Avg Relevance Score",     f"{avg_score:.1f}/10", "7030A0", "FFFFFF"),
            ("Resumes Generated",       resumes_gen,    "1648BE", "FFFFFF"),
        ]

        row = 4
        for label, value, bg, fg in stats:
            ws.merge_cells(f"B{row}:C{row}")
            ws[f"B{row}"] = label
            ws[f"B{row}"].font = Font(name="Calibri", size=11, bold=True, color=fg)
            ws[f"B{row}"].fill = PatternFill("solid", fgColor=bg)
            ws[f"B{row}"].alignment = Alignment(horizontal="left", indent=1)

            ws[f"D{row}"] = value
            ws[f"D{row}"].font = Font(name="Calibri", size=14, bold=True, color=bg)
            ws[f"D{row}"].alignment = Alignment(horizontal="center")
            row += 1

        # Platform breakdown
        row += 1
        ws[f"B{row}"] = "Jobs by Platform"
        ws[f"B{row}"].font = Font(name="Calibri", size=12, bold=True, color="1648BE")
        row += 1
        from collections import Counter
        platform_counts = Counter(j.get("platform", "Unknown") for j in jobs)
        for platform, count in platform_counts.most_common():
            ws[f"B{row}"] = platform
            ws[f"C{row}"] = count
            ws[f"B{row}"].font = Font(name="Calibri", size=11)
            ws[f"C{row}"].font = Font(name="Calibri", size=11, bold=True)
            row += 1

        # Column widths
        ws.column_dimensions["B"].width = 28
        ws.column_dimensions["C"].width = 20
        ws.column_dimensions["D"].width = 15

    # ──────────────────────────────────────────────────────────────
    # SHEET 2: ALL JOBS
    # ──────────────────────────────────────────────────────────────
    def _create_all_jobs_sheet(self, wb: Workbook, jobs: list[dict]):
        ws = wb.create_sheet("📋 All Jobs")
        self._write_jobs_sheet(ws, jobs, "ALL JOBS — RANKED BY RELEVANCE SCORE")

    # ──────────────────────────────────────────────────────────────
    # SHEET 3: TOP MATCHES (score >= 6)
    # ──────────────────────────────────────────────────────────────
    def _create_top_matches_sheet(self, wb: Workbook, jobs: list[dict]):
        top = [j for j in jobs if j.get("relevance_score", 0) >= 6]
        ws = wb.create_sheet("⭐ Top Matches")
        self._write_jobs_sheet(ws, top, f"TOP MATCHES (Score ≥ 6) — {len(top)} Jobs")

    # ──────────────────────────────────────────────────────────────
    # SHEET 4: APPLICATION TRACKER
    # ──────────────────────────────────────────────────────────────
    def _create_tracker_sheet(self, wb: Workbook, jobs: list[dict]):
        ws = wb.create_sheet("✅ Application Tracker")
        ws.sheet_view.showGridLines = False

        tracker_headers = [
            ("Rank", 6), ("Job Title", 28), ("Company", 20), ("Platform", 12),
            ("Score", 8), ("Priority", 12), ("Applied Date", 14), ("Status", 20),
            ("HR Contact", 22), ("Interview Date", 15), ("Offer/Feedback", 30), ("Job URL", 50),
        ]

        # Header row
        self._write_header_row(ws, tracker_headers, row=1)

        top = [j for j in jobs if j.get("relevance_score", 0) >= 6]
        status_options = ["Not Applied", "Applied", "Shortlisted", "Interview Scheduled",
                          "Offer Received", "Rejected", "On Hold"]

        for idx, job in enumerate(top, 1):
            r = idx + 1
            score = job.get("relevance_score", 0)
            color = self._score_row_color(score)

            cells = [
                idx,
                job.get("title", ""),
                job.get("company", ""),
                job.get("platform", ""),
                score,
                job.get("application_priority", ""),
                "",  # Applied Date (to be filled)
                "Not Applied",
                "",  # HR Contact
                "",  # Interview Date
                "",  # Offer/Feedback
                job.get("url", ""),
            ]
            for col, value in enumerate(cells, 1):
                cell = ws.cell(row=r, column=col, value=value)
                cell.fill = PatternFill("solid", fgColor=color)
                cell.font = Font(name="Calibri", size=10)
                cell.alignment = Alignment(wrap_text=True, vertical="top")
                if col == len(cells) and value:  # URL column
                    cell.hyperlink = value
                    cell.font = Font(name="Calibri", size=10, color="0563C1", underline="single")
                    cell.value = "Open Job"

        for col_idx, (_, width) in enumerate(tracker_headers, 1):
            ws.column_dimensions[get_column_letter(col_idx)].width = width
        ws.row_dimensions[1].height = 30
        ws.freeze_panes = "A2"

    # ──────────────────────────────────────────────────────────────
    # SHARED HELPERS
    # ──────────────────────────────────────────────────────────────
    def _write_jobs_sheet(self, ws, jobs: list[dict], title: str):
        ws.sheet_view.showGridLines = False

        # Title row
        ws.merge_cells(f"A1:{get_column_letter(len(HEADERS))}1")
        ws["A1"] = title
        ws["A1"].font = Font(name="Calibri", size=14, bold=True, color="FFFFFF")
        ws["A1"].fill = PatternFill("solid", fgColor="1648BE")
        ws["A1"].alignment = Alignment(horizontal="center")
        ws.row_dimensions[1].height = 28

        # Header row
        self._write_header_row(ws, HEADERS, row=2)

        for idx, job in enumerate(jobs, 1):
            r = idx + 2
            score = job.get("relevance_score", 0)
            row_color = self._score_row_color(score)

            def _pct(val):
                if val is None or val == "":
                    return "—"
                return f"{int(val)}%"

            ats_b   = job.get("ats_score_before")
            ats_a   = job.get("ats_score_after")
            improv  = job.get("ats_improvement", 0) or 0
            improv_str = f"+{improv}pp" if improv > 0 else ("—" if improv == 0 else f"{improv}pp")

            values = [
                idx,                                        # col 1  Rank
                job.get("title", ""),                       # col 2  Job Title
                job.get("company", ""),                     # col 3  Company
                job.get("location", ""),                    # col 4  Location
                job.get("platform", ""),                    # col 5  Platform
                job.get("salary", "Not specified"),         # col 6  Salary Range
                job.get("experience_required", ""),         # col 7  Experience Required
                job.get("posted_date", ""),                 # col 8  Posted Date
                score,                                      # col 9  Relevance Score
                _pct(ats_b),                                # col 10 ATS Before (%)
                _pct(ats_a),                                # col 11 ATS After (%)
                improv_str,                                 # col 12 ATS Improvement
                job.get("skills_match_percentage", ""),     # col 13 Skills Match %
                job.get("experience_match", ""),            # col 14 Experience Match
                job.get("application_priority", ""),        # col 15 Application Priority
                job.get("matching_skills", ""),             # col 16 Matching Skills
                job.get("missing_skills", ""),              # col 17 Missing Skills
                job.get("key_strengths", ""),               # col 18 Key Strengths
                job.get("recommendation", ""),              # col 19 Recommendation
                job.get("ats_keywords", ""),                # col 20 ATS Keywords
                job.get("resume_generated", "No"),          # col 21 Resume Generated
                job.get("resume_path", ""),                 # col 22 Resume Path
                job.get("url", ""),                         # col 23 Job URL
                "Not Applied",                              # col 24 Application Status
                "",                                         # col 25 Notes
            ]

            for col, value in enumerate(values, 1):
                cell = ws.cell(row=r, column=col, value=value)
                cell.fill = PatternFill("solid", fgColor=row_color)
                cell.font = Font(name="Calibri", size=10)
                cell.alignment = Alignment(wrap_text=True, vertical="top")
                cell.border = Border(
                    bottom=Side(style="thin", color="D9D9D9"),
                    right=Side(style="thin", color="D9D9D9"),
                )

                # Score cell — colored badge (col 9)
                if col == 9:
                    score_bg, score_fg = self._score_cell_style(score)
                    cell.fill = PatternFill("solid", fgColor=score_bg)
                    cell.font = Font(name="Calibri", size=11, bold=True, color=score_fg)
                    cell.alignment = Alignment(horizontal="center", vertical="center")

                # ATS columns center-aligned (cols 10-12)
                if col in (10, 11, 12):
                    cell.alignment = Alignment(horizontal="center", vertical="center")

                # URL → hyperlink (col 23)
                if col == 23 and value:
                    cell.hyperlink = str(value)
                    cell.font = Font(name="Calibri", size=10, color="0563C1", underline="single")
                    cell.value = "View Job"

                # Priority coloring (col 15)
                if col == 15 and value in PRIORITY_COLORS:
                    cell.fill = PatternFill("solid", fgColor=PRIORITY_COLORS[value])
                    cell.font = Font(name="Calibri", size=10, bold=True)

            ws.row_dimensions[r].height = 50

        # Column widths
        for col_idx, (_, width) in enumerate(HEADERS, 1):
            ws.column_dimensions[get_column_letter(col_idx)].width = width

        ws.freeze_panes = "A3"
        ws.auto_filter.ref = f"A2:{get_column_letter(len(HEADERS))}{len(jobs) + 2}"

    def _write_header_row(self, ws, headers: list, row: int):
        for col, (label, _) in enumerate(headers, 1):
            cell = ws.cell(row=row, column=col, value=label)
            cell.font = Font(name="Calibri", size=11, bold=True, color="FFFFFF")
            cell.fill = PatternFill("solid", fgColor="1648BE")
            cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
            cell.border = Border(
                bottom=Side(style="medium", color="FFFFFF"),
                right=Side(style="thin", color="8EAADB"),
            )
        ws.row_dimensions[row].height = 35

    def _score_row_color(self, score: int) -> str:
        if score >= 8:
            return "F0FFF0"
        elif score >= 6:
            return "FFFDE7"
        elif score >= 4:
            return "FFF3E0"
        return "FFF8F8"

    def _score_cell_style(self, score: int) -> tuple[str, str]:
        if score >= 8:
            return ("1A7A1A", "FFFFFF")
        elif score >= 6:
            return ("FF8C00", "FFFFFF")
        elif score >= 4:
            return ("CC5500", "FFFFFF")
        return ("CC0000", "FFFFFF")