import io
import json
import pandas as pd
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
from sqlalchemy.orm import Session
from app.models.entities import Product, Retailer
from app.services.reports import data_quality
from app.services.audit import log_event

PRODUCT_COLUMNS = [
    "retailer_name", "retailer_brand", "store_location", "product_id", "retailer_product_code",
    "product_name", "brand", "category_level_1", "category_level_2", "category_level_3",
    "pack_size", "unit_of_measure", "price", "promo_price", "promotion_text", "barcode_or_gtin",
    "barcode_source", "image_url", "product_url", "availability_status", "source_type", "source_url",
    "date_captured", "confidence_score"
]


def products_df(db: Session) -> pd.DataFrame:
    rows = []
    for p in db.query(Product).order_by(Product.retailer_name, Product.product_name).all():
        rows.append({c: getattr(p, c) for c in PRODUCT_COLUMNS})
    return pd.DataFrame(rows, columns=PRODUCT_COLUMNS)


def export_csv(db: Session) -> bytes:
    df = products_df(db)
    log_event(db, event_type="export", source="products", source_type="csv", status="success", products_created=0, products_updated=0, products_skipped=0, compliance_decision="exported_internal_catalogue")
    return df.to_csv(index=False).encode("utf-8")


def export_json(db: Session) -> bytes:
    df = products_df(db)
    payload = json.loads(df.to_json(orient="records", date_format="iso"))
    log_event(db, event_type="export", source="products", source_type="json", status="success", compliance_decision="exported_internal_catalogue")
    return json.dumps(payload, indent=2).encode("utf-8")


def style_sheet(ws):
    header_fill = PatternFill("solid", fgColor="1F2937")
    header_font = Font(color="FFFFFF", bold=True)
    thin = Side(style="thin", color="E5E7EB")
    for cell in ws[1]:
        cell.fill = header_fill
        cell.font = header_font
        cell.alignment = Alignment(horizontal="center")
        cell.border = Border(bottom=thin)
    for row in ws.iter_rows(min_row=2):
        for cell in row:
            cell.border = Border(bottom=thin)
            if isinstance(cell.value, (int, float)):
                cell.alignment = Alignment(horizontal="right")
                cell.number_format = '#,##0.00'
            else:
                cell.alignment = Alignment(vertical="top", wrap_text=True)
    for col in ws.columns:
        max_len = min(max(len(str(cell.value or "")) for cell in col) + 2, 42)
        ws.column_dimensions[get_column_letter(col[0].column)].width = max(10, max_len)
    ws.freeze_panes = "A2"
    ws.auto_filter.ref = ws.dimensions


def export_excel(db: Session) -> bytes:
    output = io.BytesIO()
    products = products_df(db)
    categories = products.groupby(["retailer_name", "category_level_1", "category_level_2"], dropna=False).size().reset_index(name="product_count") if not products.empty else pd.DataFrame(columns=["retailer_name", "category_level_1", "category_level_2", "product_count"])
    promotions = products[(products["promo_price"].notna()) | (products["promotion_text"].astype(str).str.len() > 0)] if not products.empty else pd.DataFrame(columns=PRODUCT_COLUMNS)
    retailers = pd.DataFrame([{ "retailer_name": r.name, "base_url": r.base_url, "connector_type": r.connector_type, "compliance_status": r.compliance_status, "enabled": r.enabled, "last_run_status": r.last_run_status } for r in db.query(Retailer).all()])
    dq = pd.DataFrame([data_quality(db)])
    notes = pd.DataFrame([
        {"note": "Barcodes are populated only when visible on a public page or supplied by an authorised source."},
        {"note": "Public scraping stops on 401, 403, 429, CAPTCHA, login-wall or bot-protection indicators."},
        {"note": "Manual imports should only be loaded from files you are authorised to use."},
    ])
    with pd.ExcelWriter(output, engine="openpyxl") as writer:
        products.to_excel(writer, index=False, sheet_name="Products")
        categories.to_excel(writer, index=False, sheet_name="Categories")
        promotions.to_excel(writer, index=False, sheet_name="Promotions")
        retailers.to_excel(writer, index=False, sheet_name="Retailer Coverage")
        dq.to_excel(writer, index=False, sheet_name="Data Quality")
        notes.to_excel(writer, index=False, sheet_name="Source Notes")
        for ws in writer.book.worksheets:
            style_sheet(ws)
    log_event(db, event_type="export", source="products", source_type="excel", status="success", compliance_decision="exported_internal_catalogue")
    return output.getvalue()