import os
import db
import config
from fpdf import FPDF
from datetime import datetime

class WarehouseReportService:
    @staticmethod
    def generate_report():
        try:
            # Fetch all stock items with material names
            query = """
                SELECT w.*, m.name_en as material_name, m.name_ru as material_name_ru
                FROM warehouse_stock w
                JOIN materials m ON w.material_id = m.id
                ORDER BY m.name_en, w.color_name
            """
            stock = db.execute_query(query)
            
            pdf = FPDF(orientation='P', unit='mm', format='A4')
            pdf.add_page()
            
            # Header
            pdf.set_font("helvetica", "B", 16)
            pdf.cell(0, 10, "Warehouse Stock Report", ln=True, align='C')
            pdf.set_font("helvetica", "", 10)
            pdf.cell(0, 5, f"Date: {datetime.now().strftime('%d.%m.%Y %H:%M')}", ln=True, align='C')
            pdf.ln(10)
            
            # Table Header
            pdf.set_fill_color(240, 240, 240)
            pdf.set_font("helvetica", "B", 10)
            pdf.cell(60, 10, "Material", border=1, fill=True)
            pdf.cell(40, 10, "Color", border=1, fill=True)
            pdf.cell(30, 10, "Unit Mass", border=1, fill=True, align='C')
            pdf.cell(30, 10, "Units", border=1, fill=True, align='C')
            pdf.cell(30, 10, "Total (kg)", border=1, fill=True, align='C')
            pdf.ln()
            
            # Table Body
            pdf.set_font("helvetica", "", 10)
            total_weight = 0
            for item in stock:
                # Use name_en to avoid encoding issues with core fonts
                name = str(item.get('material_name') or "N/A").encode('latin-1', 'replace').decode('latin-1')
                color = str(item.get('color_name') or "N/A").encode('latin-1', 'replace').decode('latin-1')
                
                pdf.cell(60, 10, name, border=1)
                pdf.cell(40, 10, color, border=1)
                pdf.cell(30, 10, f"{float(item['unit_mass']):.3f}", border=1, align='C')
                pdf.cell(30, 10, str(item['units_count']), border=1, align='C')
                pdf.cell(30, 10, f"{float(item['quantity']):.3f}", border=1, align='C')
                pdf.ln()
                total_weight += float(item['quantity'])
                
            # Total
            pdf.set_font("helvetica", "B", 10)
            pdf.cell(160, 10, "GRAND TOTAL WEIGHT (kg):", border=1, align='R')
            pdf.cell(30, 10, f"{total_weight:.3f}", border=1, align='C')
            
            # Output
            filename = f"warehouse_report_{datetime.now().strftime('%Y%m%d_%H%M%S')}.pdf"
            report_dir = os.path.join(config.UPLOAD_DIR, "reports")
            if not os.path.exists(report_dir):
                os.makedirs(report_dir, exist_ok=True)
            filepath = os.path.join(report_dir, filename)
            
            pdf.output(filepath)
            
            return f"uploads/reports/{filename}"
        except Exception as e:
            print(f"REPORT ERROR: {str(e)}")
            raise e

warehouse_report_service = WarehouseReportService()