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

class WarehouseReportService:
    @staticmethod
    def generate_report():
        # 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:
            pdf.cell(60, 10, str(item['material_name']), border=1)
            pdf.cell(40, 10, str(item['color_name']), border=1)
            pdf.cell(30, 10, f"{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"{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")
        os.makedirs(report_dir, exist_ok=True)
        filepath = os.path.join(report_dir, filename)
        
        pdf.output(filepath)
        
        return os.path.join("uploads", "reports", filename).replace("\\", "/")

warehouse_report_service = WarehouseReportService()