"""
report_generator.py - Marine Maintenance Pro
Genera reporte PDF con traduccion automatica via Claude API
"""
import os, re, json, urllib.request
from io import BytesIO
from reportlab.lib.pagesizes import letter
from reportlab.lib import colors
from reportlab.lib.units import inch
from reportlab.lib.styles import ParagraphStyle
from reportlab.lib.enums import TA_CENTER, TA_RIGHT
from reportlab.platypus import (SimpleDocTemplate, Paragraph, Spacer, Table,
    TableStyle, HRFlowable, Image as RLImage, KeepTogether)
from reportlab.platypus import Flowable
from PIL import Image as PILImage

# Colors
NAVY     = colors.HexColor('#0a1628')
CYAN     = colors.HexColor('#00b4d8')
LIGHT_BG = colors.HexColor('#f0f4f8')
GRAY     = colors.HexColor('#8a9bb0')
WHITE    = colors.white
WARN     = colors.HexColor('#f4a261')
SUCCESS  = colors.HexColor('#2ec4b6')
STATUS_COLORS = {'open':CYAN,'in_progress':WARN,'completed':SUCCESS,'cancelled':GRAY}

# UI Labels
T = {
    'es': {
        'work_order':'ORDEN DE TRABAJO','vessel_data':'Datos de la Embarcacion',
        'vessel':'Embarcacion','registration':'Matricula','type':'Tipo','year':'Ano',
        'make_model':'Marca / Modelo','engine_hours':'Horas Motor','owner':'Propietario',
        'captain':'Capitan','status':'Estado','start_date':'Fecha Inicio',
        'end_date':'Fecha Cierre','technician':'Tecnico','system':'Sistema',
        'scope':'Scope / Alcance','description':'Descripcion del Trabajo',
        'root_cause':'Causa Tecnica de la Falla','repairs':'Reparaciones Realizadas',
        'system':'Sistema',
        'equipment_worked':'Equipos Trabajados','equip_name':'Equipo','serial':'N Serie',
        'hrs':'Hrs','work_done':'Trabajo Realizado','parts':'Repuestos y Materiales',
        'part':'Repuesto','desc':'Descripcion','qty':'Cant.','unit_price':'P. Unit.',
        'total':'Total','costs':'Resumen de Costos','labor':'Mano de Obra',
        'parts_cost':'Repuestos y Materiales','before':'ANTES','after':'DESPUES',
        'evidence':'Evidencia Fotografica','signatures':'Firmas y Aprobacion',
        'tech_sign':'Tecnico Responsable','client_sign':'Capitan / Propietario',
        'generated':'Generado automaticamente por Marine Maintenance Pro',
        'status_open':'Abierta','status_in_progress':'En Progreso',
        'status_completed':'Completada','status_cancelled':'Cancelada',
    },
    'en': {
        'work_order':'WORK ORDER','vessel_data':'Vessel Information',
        'vessel':'Vessel','registration':'Registration','type':'Type','year':'Year',
        'make_model':'Make / Model','engine_hours':'Engine Hours','owner':'Owner',
        'captain':'Captain','status':'Status','start_date':'Start Date',
        'end_date':'End Date','technician':'Technician','system':'System',
        'scope':'Scope','description':'Work Description',
        'root_cause':'Technical Root Cause','repairs':'Repairs Performed',
        'system':'System',
        'equipment_worked':'Equipment Worked On','equip_name':'Equipment','serial':'Serial No.',
        'hrs':'Hrs','work_done':'Work Performed','parts':'Parts & Materials Used',
        'part':'Part / Material','desc':'Description','qty':'Qty','unit_price':'Unit Price',
        'total':'Total','costs':'Cost Summary','labor':'Labor',
        'parts_cost':'Parts & Materials','before':'BEFORE','after':'AFTER',
        'evidence':'Photo Evidence','signatures':'Signatures & Approval',
        'tech_sign':'Responsible Technician','client_sign':'Captain / Owner',
        'generated':'Automatically generated by Marine Maintenance Pro',
        'status_open':'Open','status_in_progress':'In Progress',
        'status_completed':'Completed','status_cancelled':'Cancelled',
        'lump_sum_label':'Fixed Price (all inclusive)',
    }
}

def t(lang, key):
    return T.get(lang, T['es']).get(key, key)

def status_label(lang, status):
    return t(lang, f'status_{status}')

# ── Auto-translate via Ollama ─────────────────────────────────────────────────
def translate_text(text, model='llama3.1:8b'):
    """Translate a single text string ES->EN via Ollama."""
    if not text or not text.strip():
        return text
    try:
        prompt = (
            f"Translate this Spanish text to professional English. "
            f"Keep marine and electrical technical terms accurate. "
            f"Return ONLY the translated text, nothing else:\n\n{text}"
        )
        payload = json.dumps({
            "model": model,
            "prompt": prompt,
            "stream": False,
            "options": {"temperature": 0.1}
        }).encode('utf-8')
        req = urllib.request.Request(
            "http://localhost:11434/api/generate",
            data=payload,
            headers={"Content-Type": "application/json"},
            method="POST"
        )
        with urllib.request.urlopen(req, timeout=25) as resp:
            data = json.loads(resp.read().decode('utf-8'))
        result = data.get('response', text).strip()
        # Remove common Ollama preambles
        for prefix in ['Here is the translated text:', 'Here is the translation:',
                       'Translation:', 'Translated text:', 'Here\'s the translation:']:
            if result.lower().startswith(prefix.lower()):
                result = result[len(prefix):].strip()
        return result
    except Exception as e:
        print(f"[translate] Ollama error: {e}")
        return text

def translate_content(texts_dict, target_lang='en'):
    """Translate ALL fields in a single Ollama call for speed."""
    if target_lang != 'en':
        return texts_dict
    
    # Filter non-empty values
    to_translate = {k: v for k, v in texts_dict.items() if v and str(v).strip()}
    if not to_translate:
        return texts_dict

    try:
        # Build numbered list for single batch translation
        keys = list(to_translate.keys())
        lines = '\n'.join(f"{i+1}. {to_translate[k]}" for i, k in enumerate(keys))
        
        prompt = (
            "Translate the following numbered items from Spanish to English. "
            "Keep marine/nautical/technical terminology accurate. "
            "Return ONLY the numbered list with translations, same format, nothing else:\n\n"
            + lines
        )
        payload = json.dumps({
            "model": "llama3.1:8b",
            "prompt": prompt,
            "stream": False,
            "options": {"temperature": 0.1}
        }).encode('utf-8')
        
        req = urllib.request.Request(
            "http://localhost:11434/api/generate",
            data=payload, headers={"Content-Type": "application/json"}, method="POST")
        
        with urllib.request.urlopen(req, timeout=60) as resp:
            data = json.loads(resp.read().decode('utf-8'))
        
        response = data.get('response', '').strip()
        
        # Parse numbered response back
        result = dict(texts_dict)  # start with originals as fallback
        for line in response.split('\n'):
            line = line.strip()
            if not line:
                continue
            # Match "1. text" or "1) text"
            import re
            m = re.match(r'^(\d+)[.)]\s*(.+)$', line)
            if m:
                idx = int(m.group(1)) - 1
                if 0 <= idx < len(keys):
                    result[keys[idx]] = m.group(2).strip()
        
        return result

    except Exception as e:
        print(f"[translate_batch] Ollama error: {e}")
        return texts_dict  # fallback: original text

# ── Styles ────────────────────────────────────────────────────────────────────
def make_styles():
    s = {}
    s['label'] = ParagraphStyle('label', fontName='Helvetica-Bold', fontSize=8,
        textColor=GRAY, spaceAfter=2, leading=10)
    s['value'] = ParagraphStyle('value', fontName='Helvetica', fontSize=9,
        textColor=NAVY, spaceAfter=2, leading=12)
    s['body']  = ParagraphStyle('body', fontName='Helvetica', fontSize=9,
        textColor=NAVY, spaceAfter=3, leading=14,
        leftIndent=0, rightIndent=0, borderPad=0)
    s['small'] = ParagraphStyle('small', fontName='Helvetica', fontSize=8,
        textColor=GRAY, spaceAfter=2, leading=10)
    s['mono']  = ParagraphStyle('mono', fontName='Courier', fontSize=7,
        textColor=NAVY, leading=10)
    s['sign']  = ParagraphStyle('sign', fontName='Helvetica', fontSize=8,
        textColor=GRAY, alignment=TA_CENTER)
    s['photo'] = ParagraphStyle('photo', fontName='Helvetica-Bold', fontSize=8,
        alignment=TA_CENTER, spaceAfter=2)
    return s

class SectionHeader(Flowable):
    def __init__(self, text, width=None):
        Flowable.__init__(self)
        self.text = text
        self._fixed_width = width
        self.height = 16

    def wrap(self, availWidth, availHeight):
        self._width = self._fixed_width if self._fixed_width else availWidth
        return self._width, self.height

    def draw(self):
        self.canv.setFillColor(NAVY)
        self.canv.rect(0, 0, self._width, self.height, fill=1, stroke=0)
        self.canv.setFillColor(CYAN)
        self.canv.rect(0, 0, 4, self.height, fill=1, stroke=0)
        self.canv.setFillColor(WHITE)
        self.canv.setFont('Helvetica-Bold', 9)
        self.canv.drawString(12, 4, self.text.upper())

def resize_image(path, max_w, max_h):
    try:
        with PILImage.open(path) as img:
            iw, ih = img.size
        ratio = min(max_w/iw, max_h/ih)
        return iw*ratio, ih*ratio
    except:
        return max_w, max_h

def text_block(paragraphs, W):
    """Single flowing text cell — no forced page breaks between paragraphs."""
    # Join all paragraphs into one cell so text flows naturally
    tbl = Table([[paragraphs]], colWidths=[W])
    tbl.setStyle(TableStyle([
        ('LEFTPADDING',   (0,0), (-1,-1), 10),
        ('RIGHTPADDING',  (0,0), (-1,-1), 10),
        ('TOPPADDING',(0,0),(-1,-1),4),
        ('BOTTOMPADDING',(0,0),(-1,-1),4),
        ('VALIGN',        (0,0), (-1,-1), 'TOP'),
        ('BOX',           (0,0), (-1,-1), 0.5, colors.HexColor('#d0dae6')),
    ]))
    return tbl

def section_block(header_flowable, content_flowables):
    """Header stays with content start, but content flows freely across pages.
    
    Strategy: use KeepTogether ONLY for header + a tiny anchor spacer (not the
    whole content block). This prevents orphan headers without forcing large
    tables to jump pages looking for space.
    """
    if not content_flowables:
        return [header_flowable, Spacer(1,2)]
    
    first = content_flowables[0]
    rest  = content_flowables[1:]
    
    # If first content is a Table, we can't split KeepTogether with it safely
    # Instead just keep header + 2pt spacer together (very small, almost never jumps)
    # and let the table itself flow/split naturally
    result = [KeepTogether([header_flowable, Spacer(1,2)]), first]
    result.extend(rest)
    return result

def generate_work_order_pdf(order, vessel, photos, parts_used, wo_equipment,
                             upload_folder, sig_folder,
                             company_name="Marine Maintenance Pro",
                             company_info="", company_logo=None, lang='es'):

    # ── Translate content if EN ───────────────────────────────────────────────
    if lang == 'en':
        content_to_translate = {
            'scope':        order.get('scope') or '',
            'description':  order.get('description') or '',
            'root_cause':   order.get('root_cause') or '',
            'repairs_done': order.get('repairs_done') or '',
            'system_name':  order.get('system_name') or '',
        }
        # Translate equipment names AND descriptions
        for i, e in enumerate(wo_equipment):
            content_to_translate[f'equip_name_{i}'] = e.get('equip_name') or ''
            content_to_translate[f'equip_desc_{i}'] = e.get('description') or ''
        # Translate photo captions
        for i, p in enumerate(photos):
            if p.get('caption'):
                content_to_translate[f'photo_cap_{i}'] = p['caption']

        translated = translate_content(content_to_translate, 'en')

        order = dict(order)
        order['scope']        = translated.get('scope', order.get('scope',''))
        order['description']  = translated.get('description', order.get('description',''))
        order['root_cause']   = translated.get('root_cause', order.get('root_cause',''))
        order['repairs_done'] = translated.get('repairs_done', order.get('repairs_done',''))
        order['system_name']  = translated.get('system_name', order.get('system_name',''))

        wo_equipment = [dict(e) for e in wo_equipment]
        for i, e in enumerate(wo_equipment):
            e['equip_name']  = translated.get(f'equip_name_{i}', e.get('equip_name',''))
            e['description'] = translated.get(f'equip_desc_{i}', e.get('description',''))

        photos = [dict(p) for p in photos]
        for i, p in enumerate(photos):
            if p.get('caption'):
                p['caption'] = translated.get(f'photo_cap_{i}', p['caption'])

    # ── Build PDF ─────────────────────────────────────────────────────────────
    buf = BytesIO()
    W = 7.5 * inch
    doc = SimpleDocTemplate(buf, pagesize=letter,
        leftMargin=0.75*inch, rightMargin=0.75*inch,
        topMargin=0.75*inch, bottomMargin=0.75*inch)
    S = make_styles()
    story = []

    # ── HEADER ───────────────────────────────────────────────────────────────
    story.append(HRFlowable(width=W, thickness=3, color=CYAN, spaceAfter=4))
    if company_logo and os.path.exists(company_logo):
        try:
            lw, lh = resize_image(company_logo, 1.6*inch, 0.65*inch)
            logo_cell = RLImage(company_logo, width=lw, height=lh)
        except:
            logo_cell = Paragraph(f'<b>{company_name}</b>',
                ParagraphStyle('ch', fontName='Helvetica-Bold', fontSize=16, textColor=NAVY))
    else:
        logo_cell = Paragraph(f'<b>{company_name}</b>',
            ParagraphStyle('ch', fontName='Helvetica-Bold', fontSize=16, textColor=NAVY, leading=20))

    hdr = Table([[logo_cell,
        Paragraph(f'<b>{t(lang,"work_order")}</b><br/>'
                  f'<font color="#00b4d8" size="18"><b>{order.get("order_number","")}</b></font>',
            ParagraphStyle('on', fontName='Helvetica-Bold', fontSize=9,
                textColor=GRAY, alignment=TA_RIGHT, leading=22))
    ]], colWidths=[W*0.6, W*0.4])
    hdr.setStyle(TableStyle([('VALIGN',(0,0),(-1,-1),'TOP')]))
    story.append(hdr)
    if company_info:
        story.append(Paragraph(company_info,
            ParagraphStyle('ci', fontName='Helvetica', fontSize=8, textColor=GRAY, spaceAfter=2)))
    story.append(HRFlowable(width=W, thickness=1, color=LIGHT_BG, spaceAfter=4))

    # ── STATUS ROW ───────────────────────────────────────────────────────────
    sk = order.get('status','open')
    sc = STATUS_COLORS.get(sk, GRAY)
    meta = Table([
        [Paragraph(f'<b>{t(lang,"status")}</b>',S['label']),
         Paragraph(f'<b>{t(lang,"start_date")}</b>',S['label']),
         Paragraph(f'<b>{t(lang,"end_date")}</b>',S['label']),
         Paragraph(f'<b>{t(lang,"technician")}</b>',S['label'])],
        [Paragraph(f'<font color="{sc.hexval()}"><b>{status_label(lang,sk)}</b></font>',S['value']),
         Paragraph(str(order.get('start_date') or '—'),S['value']),
         Paragraph(str(order.get('end_date') or '—'),S['value']),
         Paragraph(str(order.get('technician') or '—'),S['value'])],
    ], colWidths=[W/4]*4)
    meta.setStyle(TableStyle([
        ('BACKGROUND',(0,0),(-1,0),LIGHT_BG),
        ('GRID',(0,0),(-1,-1),0.5,colors.HexColor('#d0dae6')),
        ('TOPPADDING',(0,0),(-1,-1),3),('BOTTOMPADDING',(0,0),(-1,-1),3),
        ('LEFTPADDING',(0,0),(-1,-1),10),('RIGHTPADDING',(0,0),(-1,-1),10),
    ]))
    story.append(KeepTogether([meta]))
    story.append(Spacer(1,5))

    # ── VESSEL ───────────────────────────────────────────────────────────────
    vt = Table([
        [Paragraph(f'<b>{t(lang,"vessel")}</b>',S['label']),
         Paragraph(f'<b>{t(lang,"registration")}</b>',S['label']),
         Paragraph(f'<b>{t(lang,"type")}</b>',S['label']),
         Paragraph(f'<b>{t(lang,"year")}</b>',S['label'])],
        [Paragraph(str(vessel.get('name') or '—'),S['value']),
         Paragraph(str(vessel.get('registration') or '—'),S['value']),
         Paragraph(str(vessel.get('vessel_type') or '—'),S['value']),
         Paragraph(str(vessel.get('year') or '—'),S['value'])],
        [Paragraph(f'<b>{t(lang,"make_model")}</b>',S['label']),
         Paragraph(f'<b>{t(lang,"engine_hours")}</b>',S['label']),
         Paragraph(f'<b>{t(lang,"captain")}</b>',S['label']),
         Paragraph(f'<b>{t(lang,"owner")}</b>',S['label'])],
        [Paragraph(f'{vessel.get("make") or ""} {vessel.get("model") or ""}'.strip() or '—',S['value']),
         Paragraph(f'{vessel.get("engine_hours") or 0} h',S['value']),
         Paragraph(str(vessel.get('captain_name') or '—'),S['value']),
         Paragraph(str(vessel.get('owner_name') or '—'),S['value'])],
    ], colWidths=[W/4]*4)
    vt.setStyle(TableStyle([
        ('BACKGROUND',(0,0),(-1,0),LIGHT_BG),('BACKGROUND',(0,2),(-1,2),LIGHT_BG),
        ('GRID',(0,0),(-1,-1),0.5,colors.HexColor('#d0dae6')),
        ('TOPPADDING',(0,0),(-1,-1),3),('BOTTOMPADDING',(0,0),(-1,-1),3),
        ('LEFTPADDING',(0,0),(-1,-1),10),('RIGHTPADDING',(0,0),(-1,-1),10),
    ]))
    story += section_block(SectionHeader(f'{t(lang,"vessel_data")}'), [vt])
    story.append(Spacer(1,5))

    # ── SCOPE ────────────────────────────────────────────────────────────────
    sys_name = order.get('system_name') or ''
    scope    = order.get('scope') or ''
    if sys_name or scope:
        rows = []
        if sys_name:
            rows.append([Paragraph(f'<b>{t(lang,"system")}</b>', S['label']),
                         Paragraph(sys_name, S['value'])])
        if scope:
            rows.append([Paragraph(f'<b>{t(lang,"scope")}</b>', S['label']),
                         Paragraph(scope, S['value'])])
        st = Table(rows, colWidths=[W/4, W - W/4])
        st.setStyle(TableStyle([
            ('BACKGROUND', (0,0), (0,-1), LIGHT_BG),
            ('GRID', (0,0), (-1,-1), 0.5, colors.HexColor('#d0dae6')),
            ('TOPPADDING',(0,0),(-1,-1),3),
            ('BOTTOMPADDING',(0,0),(-1,-1),3),
            ('LEFTPADDING', (0,0), (-1,-1), 8),
            ('RIGHTPADDING', (0,0), (-1,-1), 8),
            ('VALIGN', (0,0), (-1,-1), 'TOP'),
        ]))
        story += section_block(SectionHeader(f'{t(lang,"scope")}'), [st])
        story.append(Spacer(1,4))

    # ── DESCRIPTION ──────────────────────────────────────────────────────────
    desc = order.get('description') or ''
    if desc:
        story += section_block(SectionHeader(f'{t(lang,"description")}'),
                               [text_block([Paragraph(desc.replace('\n', '<br/>'), S['body'])], W)])
        story.append(Spacer(1,4))

    # ── ROOT CAUSE ───────────────────────────────────────────────────────────
    root_cause = order.get('root_cause') or ''
    if root_cause:
        story += section_block(SectionHeader(f'{t(lang,"root_cause")}'),
                               [text_block([Paragraph(root_cause.replace('\n', '<br/>'), S['body'])], W)])
        story.append(Spacer(1,4))

    # ── REPAIRS ──────────────────────────────────────────────────────────────
    repairs = order.get('repairs_done') or ''
    if repairs:
        story += section_block(SectionHeader(f'{t(lang,"repairs")}'),
                               [text_block([Paragraph(repairs.replace('\n', '<br/>'), S['body'])], W)])
        story.append(Spacer(1,4))

    # ── EQUIPMENT WORKED ─────────────────────────────────────────────────────
    if wo_equipment:
        hdr_row = [
            Paragraph(f'<b>{t(lang,"equip_name")}</b>',S['label']),
            Paragraph(f'<b>{t(lang,"serial")}</b>',S['label']),
            Paragraph(f'<b>{t(lang,"hrs")}</b>',S['label']),
            Paragraph(f'<b>{t(lang,"work_done")}</b>',S['label']),
        ]
        rows = [hdr_row]
        for e in wo_equipment:
            name_str = str(e.get('equip_name') or '—')
            brand = f'{e.get("make") or ""} {e.get("model") or ""}'.strip()
            if brand:
                name_str += f'<br/><font size="7" color="#8a9bb0">{brand}</font>'
            rows.append([
                Paragraph(name_str, S['value']),
                Paragraph(str(e.get('serial_number') or '—'), S['mono']),
                Paragraph(f'{e.get("labor_hours") or 0}h', S['value']),
                Paragraph(str(e.get('description') or '—'), S['value']),
            ])
        eqt = Table(rows, colWidths=[W*0.23, W*0.17, W*0.07, W*0.53])
        eqt.setStyle(TableStyle([
            ('BACKGROUND',(0,0),(-1,0),NAVY),('TEXTCOLOR',(0,0),(-1,0),WHITE),
            ('ROWBACKGROUNDS',(0,1),(-1,-1),[WHITE,LIGHT_BG]),
            ('GRID',(0,0),(-1,-1),0.5,colors.HexColor('#d0dae6')),
            ('TOPPADDING',(0,0),(-1,-1),2),('BOTTOMPADDING',(0,0),(-1,-1),2),
            ('LEFTPADDING',(0,0),(-1,-1),8),('RIGHTPADDING',(0,0),(-1,-1),8),
            ('VALIGN',(0,0),(-1,-1),'TOP'),
        ]))
        story += section_block(SectionHeader(f'{t(lang,"equipment_worked")}'), [eqt])
        story.append(Spacer(1,5))

    # ── PARTS (only for labor_materials) ─────────────────────────────────────
    billing = order.get('billing_type', 'labor_materials')
    if billing == 'labor_materials':
        parts_content = []
        if parts_used:
            hdr_row = [
                Paragraph(f'<b>{t(lang,"part")}</b>',S['label']),
                Paragraph(f'<b>{t(lang,"desc")}</b>',S['label']),
                Paragraph(f'<b>{t(lang,"qty")}</b>',S['label']),
                Paragraph(f'<b>{t(lang,"unit_price")}</b>',S['label']),
                Paragraph(f'<b>{t(lang,"total")}</b>',S['label']),
            ]
            rows = [hdr_row]
            for p in parts_used:
                rows.append([
                    Paragraph(str(p.get('part_name') or '—'),S['value']),
                    Paragraph(str(p.get('description') or ''),S['small']),
                    Paragraph(str(p.get('quantity') or 0),S['value']),
                    Paragraph(f'${float(p.get("unit_cost") or 0):.2f}',S['value']),
                    Paragraph(f'${float(p.get("total_cost") or 0):.2f}',S['value']),
                ])
            ptt = Table(rows, colWidths=[W*0.28,W*0.30,W*0.10,W*0.15,W*0.17])
            ptt.setStyle(TableStyle([
                ('BACKGROUND',(0,0),(-1,0),NAVY),('TEXTCOLOR',(0,0),(-1,0),WHITE),
                ('ROWBACKGROUNDS',(0,1),(-1,-1),[WHITE,LIGHT_BG]),
                ('GRID',(0,0),(-1,-1),0.5,colors.HexColor('#d0dae6')),
                ('ALIGN',(2,0),(-1,-1),'RIGHT'),
                ('TOPPADDING',(0,0),(-1,-1),2),('BOTTOMPADDING',(0,0),(-1,-1),2),
                ('LEFTPADDING',(0,0),(-1,-1),8),('RIGHTPADDING',(0,0),(-1,-1),8),
                ('VALIGN',(0,0),(-1,-1),'TOP'),
            ]))
            parts_content = [ptt]
        else:
            parts_content = [Paragraph('—', S['small'])]
        story += section_block(SectionHeader(f'{t(lang,"parts")}'), parts_content)
        story.append(Spacer(1,5))

    # ── COSTS ────────────────────────────────────────────────────────────────
    lh  = float(order.get('labor_hours') or 0)
    lr  = float(order.get('labor_rate') or 0)
    lc  = lh * lr
    pc  = float(order.get('total_parts_cost') or 0) if billing == 'labor_materials' else 0
    tot = lc + pc

    # Build cost rows based on billing type
    if billing == 'lump_sum':
        cost_rows = [
            [Paragraph(t(lang,'lump_sum_label') if lang=='en' else 'Precio fijo (todo incluido)', S['label']),
             Paragraph(f'{lh} h', S['value']),
             Paragraph(f'${tot:.2f}', S['value'])],
            [Paragraph(f'<b>{t(lang,"total").upper()}</b>',
                 ParagraphStyle('tb',fontName='Helvetica-Bold',fontSize=10,textColor=WHITE)),
             Paragraph('',S['value']),
             Paragraph(f'<b>${tot:.2f}</b>',
                 ParagraphStyle('tv',fontName='Helvetica-Bold',fontSize=11,textColor=CYAN,alignment=TA_RIGHT))],
        ]
    elif billing == 'labor_only':
        cost_rows = [
            [Paragraph(t(lang,'labor'), S['label']),
             Paragraph(f'{lh} h x ${lr:.2f}/h', S['value']),
             Paragraph(f'${lc:.2f}', S['value'])],
            [Paragraph(f'<b>{t(lang,"total").upper()}</b>',
                 ParagraphStyle('tb',fontName='Helvetica-Bold',fontSize=10,textColor=WHITE)),
             Paragraph('',S['value']),
             Paragraph(f'<b>${lc:.2f}</b>',
                 ParagraphStyle('tv',fontName='Helvetica-Bold',fontSize=11,textColor=CYAN,alignment=TA_RIGHT))],
        ]
    else:  # labor_materials
        cost_rows = [
            [Paragraph(t(lang,'labor'),S['label']),
             Paragraph(f'{lh} h x ${lr:.2f}/h',S['value']),
             Paragraph(f'${lc:.2f}',S['value'])],
            [Paragraph(t(lang,'parts_cost'),S['label']),
             Paragraph('',S['value']),
             Paragraph(f'${pc:.2f}',S['value'])],
            [Paragraph(f'<b>{t(lang,"total").upper()}</b>',
                 ParagraphStyle('tb',fontName='Helvetica-Bold',fontSize=10,textColor=WHITE)),
             Paragraph('',S['value']),
             Paragraph(f'<b>${tot:.2f}</b>',
                 ParagraphStyle('tv',fontName='Helvetica-Bold',fontSize=11,textColor=CYAN,alignment=TA_RIGHT))],
        ]
    ct = Table(cost_rows, colWidths=[W*0.42, W*0.33, W*0.25])
    ct.setStyle(TableStyle([
        ('BACKGROUND',(0,0),(-1,-2),LIGHT_BG),('BACKGROUND',(0,-1),(-1,-1),NAVY),
        ('GRID',(0,0),(-1,-1),0.5,colors.HexColor('#d0dae6')),
        ('ALIGN',(2,0),(-1,-1),'RIGHT'),
        ('TOPPADDING',(0,0),(-1,-1),3),('BOTTOMPADDING',(0,0),(-1,-1),3),
        ('LEFTPADDING',(0,0),(-1,-1),10),('RIGHTPADDING',(0,0),(-1,-1),10),
    ]))
    story += section_block(SectionHeader(f'{t(lang,"costs")}'), [ct])
    story.append(Spacer(1,7))

    # ── PHOTOS ───────────────────────────────────────────────────────────────
    before_photos = [p for p in photos if p.get('photo_type')=='before']
    after_photos  = [p for p in photos if p.get('photo_type')=='after']

    def photo_section(photo_list, label):
        if not photo_list: return []
        MAX_W = (W - 0.3*inch) / 2
        MAX_H = 2.4 * inch
        photo_tables = []
        for row in [photo_list[i:i+2] for i in range(0,len(photo_list),2)]:
            cells = []
            for ph in row:
                fp = os.path.join(upload_folder, ph['filename'])
                if os.path.exists(fp):
                    try:
                        # Compress image to reduce PDF size before inserting
                        from io import BytesIO as _BytesIO
                        with PILImage.open(fp) as _img:
                            _img = _img.convert('RGB')
                            # Resize to max 1200px on longest side
                            _img.thumbnail((1200, 1200), PILImage.LANCZOS)
                            _buf = _BytesIO()
                            _img.save(_buf, format='JPEG', quality=72, optimize=True)
                            _buf.seek(0)
                        pw, ph_h = resize_image(fp, MAX_W, MAX_H)
                        img = RLImage(_buf, width=pw, height=ph_h)
                        cap = ph.get('caption') or ''
                        cell = [img]
                        if cap: cell += [Spacer(1,3), Paragraph(cap, S['photo'])]
                        cells.append(cell)
                    except: cells.append([Paragraph('[Error]', S['small'])])
                else: cells.append([Paragraph('[Not found]', S['small'])])
            while len(cells) < 2: cells.append([Paragraph('', S['small'])])
            pt = Table([cells], colWidths=[W/2-0.1*inch, W/2-0.1*inch])
            pt.setStyle(TableStyle([
                ('VALIGN',(0,0),(-1,-1),'TOP'),('ALIGN',(0,0),(-1,-1),'CENTER'),
                ('TOPPADDING',(0,0),(-1,-1),2),('BOTTOMPADDING',(0,0),(-1,-1),4),
            ]))
            photo_tables.append(pt)
        return section_block(SectionHeader(f'{t(lang,"evidence")} — {label}'), photo_tables)

    if before_photos or after_photos:
        story += photo_section(before_photos, t(lang,'before'))
        if after_photos: story.append(Spacer(1,3))
        story += photo_section(after_photos, t(lang,'after'))
        story.append(Spacer(1,7))

    # ── SIGNATURES ───────────────────────────────────────────────────────────
    story.append(HRFlowable(width=W, thickness=1, color=LIGHT_BG, spaceAfter=7))

    def sig_cell(sig_filename, label, name):
        content = []
        if sig_filename and sig_folder:
            fp = os.path.join(sig_folder, sig_filename)
            if os.path.exists(fp):
                try:
                    sw, sh = resize_image(fp, W/2-0.3*inch, 0.8*inch)
                    content.append(RLImage(fp, width=sw, height=sh))
                except: pass
        if not content:
            content.append(Paragraph('_'*38, S['sign']))
        content += [Spacer(1,3), Paragraph(f'{label}<br/>{name}', S['sign'])]
        return content

    tech_name   = order.get('technician') or ''
    client_name = vessel.get('captain_name') or vessel.get('owner_name') or ''
    sig_tbl = Table([[
        sig_cell(order.get('signature_tech'), t(lang,'tech_sign'), tech_name),
        sig_cell(order.get('signature_client'), t(lang,'client_sign'), client_name),
    ]], colWidths=[W/2, W/2])
    sig_tbl.setStyle(TableStyle([
        ('ALIGN',(0,0),(-1,-1),'CENTER'),('VALIGN',(0,0),(-1,-1),'BOTTOM'),
        ('TOPPADDING',(0,0),(-1,-1),2),('BOTTOMPADDING',(0,0),(-1,-1),2),
    ]))
    story += section_block(SectionHeader(f'{t(lang,"signatures")}'), [sig_tbl])

    # ── FOOTER ───────────────────────────────────────────────────────────────
    story.append(Spacer(1,8))
    story.append(HRFlowable(width=W, thickness=1, color=LIGHT_BG, spaceAfter=2))
    story.append(Paragraph(
        f'{company_name} | {order.get("order_number","")} | {t(lang,"generated")}',
        ParagraphStyle('footer', fontName='Helvetica', fontSize=7,
            textColor=GRAY, alignment=TA_CENTER)))

    doc.build(story)
    buf.seek(0)
    return buf