# PDF Generation Utility for Shelf Scribe
import os
from datetime import datetime
from reportlab.lib.pagesizes import letter
from reportlab.platypus import SimpleDocTemplate, Paragraph, Spacer, Table, TableStyle
from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle
from reportlab.lib import colors
from database import db_query


def generate_purchase_order_pdf(restock_items, supplier_name="Wholesale Supplier"):
    """
    Generates a professional purchase order PDF for the restock items.
    Returns the absolute path to the generated PDF.
    """
    pdf_filename = "restock_order.pdf"
    pdf_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), pdf_filename)

    if not restock_items:
        # Create a dummy pdf or return empty error
        doc = SimpleDocTemplate(pdf_path, pagesize=letter)
        styles = getSampleStyleSheet()
        story: list = [
            Paragraph("Shelf Scribe - Restock Order", styles["Heading1"]),
            Spacer(1, 20),
            Paragraph("No items need restocking today.", styles["Normal"]),
        ]
        doc.build(story)
        return pdf_path

    try:
        doc = SimpleDocTemplate(
            pdf_path,
            pagesize=letter,
            rightMargin=36,
            leftMargin=36,
            topMargin=36,
            bottomMargin=36,
        )
        story = []
        styles = getSampleStyleSheet()

        # Custom Title and Brand
        title_style = ParagraphStyle(
            "POTitle",
            parent=styles["Heading1"],
            fontName="Helvetica-Bold",
            fontSize=22,
            leading=26,
            textColor=colors.HexColor("#10b981"),  # Emerald Green
        )
        normal_style = styles["Normal"]

        story.append(Paragraph("SHELF SCRIBE - RESTOCK PURCHASE ORDER", title_style))
        story.append(Spacer(1, 10))

        date_str = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        metadata_text = f"""
        <b>Date:</b> {date_str}<br/>
        <b>Supplier Name:</b> {supplier_name}<br/>
        <b>Status:</b> Draft - Pending Confirmation<br/>
        """
        story.append(Paragraph(metadata_text, normal_style))
        story.append(Spacer(1, 20))

        # Table of items: Product Name, Qty, Unit Cost, Pack Size, Total Cost
        table_data = [
            [
                "#",
                "Item Description",
                "Qty Ordered",
                "Pack Size",
                "Supplier Unit Cost",
                "Estimated Cost",
            ]
        ]

        total_cost = 0.0
        for idx, item in enumerate(restock_items, 1):
            prod_id = item["product_id"]
            suggested_qty = item.get("suggested_quantity", 0)

            # Query details from database
            prod = db_query(
                "SELECT supplier_price, pack_size FROM products WHERE id = ?",
                (prod_id,),
            )
            if prod:
                price = prod[0]["supplier_price"]
                pack_size = prod[0]["pack_size"]
            else:
                price = 0.0
                pack_size = 1

            cost = suggested_qty * price
            total_cost += cost
            packs = suggested_qty // pack_size

            table_data.append(
                [
                    str(idx),
                    item["product_name"],
                    f"{suggested_qty} units ({packs} packs)",
                    f"size {pack_size}",
                    f"${price:.2f}",
                    f"${cost:.2f}",
                ]
            )

        # Add total row
        table_data.append(["", "", "", "", "Total Est. Cost:", f"${total_cost:.2f}"])

        t = Table(table_data, colWidths=[25, 230, 110, 60, 110, 80])
        t.setStyle(
            TableStyle(
                [
                    ("BACKGROUND", (0, 0), (-1, 0), colors.HexColor("#f8fafc")),
                    ("TEXTCOLOR", (0, 0), (-1, 0), colors.HexColor("#0f172a")),
                    ("ALIGN", (0, 0), (-1, -1), "LEFT"),
                    ("BOTTOMPADDING", (0, 0), (-1, 0), 8),
                    ("TOPPADDING", (0, 0), (-1, 0), 8),
                    ("GRID", (0, 0), (-1, -2), 0.5, colors.HexColor("#cbd5e1")),
                    ("FONTNAME", (0, 0), (-1, 0), "Helvetica-Bold"),
                    ("FONTNAME", (0, -1), (-1, -1), "Helvetica-Bold"),
                    ("BACKGROUND", (0, -1), (-1, -1), colors.HexColor("#f1f5f9")),
                    ("LINEABOVE", (0, -1), (-1, -1), 1.5, colors.HexColor("#10b981")),
                    ("BOTTOMPADDING", (0, -1), (-1, -1), 10),
                    ("TOPPADDING", (0, -1), (-1, -1), 10),
                ]
            )
        )

        story.append(t)
        story.append(Spacer(1, 30))
        story.append(
            Paragraph(
                "<i>Disclaimer: This document is an AI-assisted suggestion. The shop owner must confirm pricing and quantities before final order placement.</i>",
                normal_style,
            )
        )

        doc.build(story)
        return pdf_path
    except Exception as e:
        print(f"Error generating Purchase Order PDF: {e}")
        return pdf_path


def generate_shelf_labels_pdf(products_list):
    """
    Generates a print-ready sheet of shelf labels (A4/Letter size layout grid)
    using ReportLab flowables.
    """
    pdf_filename = "shelf_labels.pdf"
    pdf_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), pdf_filename)

    if not products_list:
        doc = SimpleDocTemplate(pdf_path, pagesize=letter)
        styles = getSampleStyleSheet()
        story: list = [Paragraph("No labels to print.", styles["Heading1"])]
        doc.build(story)
        return pdf_path

    try:
        # A4 or Letter, small margins to fit maximum cards
        doc = SimpleDocTemplate(
            pdf_path,
            pagesize=letter,
            rightMargin=24,
            leftMargin=24,
            topMargin=24,
            bottomMargin=24,
        )
        story = []
        styles = getSampleStyleSheet()

        # Grid layout: 2 columns of cards
        grid_data = []
        row = []

        for idx, prod in enumerate(products_list):
            category = prod.get("category", "General").upper()
            name = prod.get("name", "Product")
            price = prod.get("shelf_price", 0.0)
            pack_size = prod.get("pack_size", 1)

            # Simple card representation as nested ReportLab Table or Paragraph cells
            card_content = [
                Paragraph(
                    f"<font size='7' color='#64748b'><b>{category}</b> | PACK: {pack_size}</font>",
                    styles["Normal"],
                ),
                Spacer(1, 4),
                Paragraph(
                    f"<font size='10' color='#1e293b'><b>{name}</b></font>",
                    styles["Normal"],
                ),
                Spacer(1, 10),
                Table(
                    [
                        [
                            Paragraph(
                                f"<font size='18' color='#059669'><b>${price:.2f}</b></font>",
                                styles["Normal"],
                            ),
                            Paragraph(
                                "<font size='6' color='#94a3b8' face='Courier'>|||| | ||| ||||<br/>20000000</font>",
                                styles["Normal"],
                            ),
                        ]
                    ],
                    colWidths=[120, 100],
                ),
            ]

            card_table = Table([[card_content]], colWidths=[240])
            card_table.setStyle(
                TableStyle(
                    [
                        ("BOX", (0, 0), (-1, -1), 1.5, colors.HexColor("#475569")),
                        ("TOPPADDING", (0, 0), (-1, -1), 8),
                        ("BOTTOMPADDING", (0, 0), (-1, -1), 8),
                        ("LEFTPADDING", (0, 0), (-1, -1), 10),
                        ("RIGHTPADDING", (0, 0), (-1, -1), 10),
                        ("BACKGROUND", (0, 0), (-1, -1), colors.white),
                    ]
                )
            )

            row.append(card_table)

            # 2 columns per row
            if len(row) == 2:
                grid_data.append(row)
                row = []
                # Spacer row
                grid_data.append([Spacer(1, 10), Spacer(1, 10)])

        # Handle trailing odd item
        if row:
            row.append("")  # empty cell
            grid_data.append(row)

        grid_table = Table(grid_data, colWidths=[260, 260])
        grid_table.setStyle(
            TableStyle(
                [
                    ("ALIGN", (0, 0), (-1, -1), "CENTER"),
                    ("VALIGN", (0, 0), (-1, -1), "MIDDLE"),
                ]
            )
        )

        story.append(
            Paragraph("<b>SHELF SCRIBE PRINTABLE LABELS SHEET</b>", styles["Heading2"])
        )
        story.append(Spacer(1, 15))
        story.append(grid_table)

        doc.build(story)
        return pdf_path
    except Exception as e:
        print(f"Error generating Shelf Labels PDF: {e}")
        return pdf_path