from fastapi import APIRouter, Depends, HTTPException, Query, Request
from fastapi.responses import StreamingResponse
from sqlalchemy import func
from sqlalchemy.orm import Session, joinedload
from typing import Optional
import io
import io as _io
import csv as csv_mod
import calendar as cal_mod
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.base import MIMEBase
from email import encoders
from datetime import date as dt_date, timedelta, datetime

from ..database import get_db
from ..models import (
    DailyShift, PumpReading, Pump, CreditCustomer,
    CreditSale, CashDenomination, ShiftCollectionCycle, TankDipReading,
    StockDelivery, User, NotificationPreference, SystemSetting,
)
from ..dependencies import get_current_user

router = APIRouter()


def _get_pdf_tools():
    try:
        from reportlab.lib.pagesizes import A4, landscape
        from reportlab.lib import colors
        from reportlab.platypus import SimpleDocTemplate, Table, TableStyle, Paragraph, Spacer
        from reportlab.lib.styles import getSampleStyleSheet
        from reportlab.lib.units import cm
        return A4, landscape, colors, SimpleDocTemplate, Table, TableStyle, Paragraph, Spacer, getSampleStyleSheet, cm
    except ImportError:
        raise HTTPException(status_code=500, detail="reportlab not installed — run: pip install reportlab")


@router.get("/reports/daily-summary")
def daily_summary_pdf(
    date: str,
    db: Session = Depends(get_db),
    _=Depends(get_current_user),
):
    A4, landscape, colors, SimpleDocTemplate, Table, TableStyle, Paragraph, Spacer, getSampleStyleSheet, cm = _get_pdf_tools()

    shifts = db.query(DailyShift).filter(DailyShift.record_date == date).all()

    buf = io.BytesIO()
    doc = SimpleDocTemplate(buf, pagesize=landscape(A4),
                            leftMargin=1.5*cm, rightMargin=1.5*cm,
                            topMargin=1.5*cm, bottomMargin=1.5*cm)
    styles = getSampleStyleSheet()
    story = []

    story.append(Paragraph(f"<b>Daily Sales Summary — {date}</b>", styles['Title']))
    story.append(Spacer(1, 0.4*cm))

    if not shifts:
        story.append(Paragraph("No shifts recorded for this date.", styles['Normal']))
    else:
        headers = ['Shift', 'Staff', 'Status', 'Sale (Rs.)', 'Cash', 'Card', 'Credit', 'Difference']
        data = [headers]
        for s in sorted(shifts, key=lambda x: x.shift_type):
            staff_name = s.staff_member.full_name if s.staff_member else '—'
            card_total = float(s.card_visa) + float(s.card_amex) + float(s.card_touch)
            data.append([
                s.shift_type, staff_name, s.status,
                f"{float(s.total_sale_calc):,.0f}",
                f"{float(s.cash_collected):,.0f}",
                f"{card_total:,.0f}",
                f"{float(s.credit_total):,.0f}",
                f"{float(s.difference):,.0f}",
            ])

        col_widths = [2*cm, 4*cm, 2.5*cm, 3.5*cm, 3*cm, 3*cm, 3*cm, 3*cm]
        t = Table(data, colWidths=col_widths, repeatRows=1)
        t.setStyle(TableStyle([
            ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#1e293b')),
            ('TEXTCOLOR', (0, 0), (-1, 0), colors.white),
            ('FONTSIZE', (0, 0), (-1, 0), 9),
            ('FONTSIZE', (0, 1), (-1, -1), 8),
            ('ROWBACKGROUNDS', (0, 1), (-1, -1), [colors.white, colors.HexColor('#f8fafc')]),
            ('GRID', (0, 0), (-1, -1), 0.3, colors.HexColor('#cbd5e1')),
            ('ALIGN', (3, 0), (-1, -1), 'RIGHT'),
            ('VALIGN', (0, 0), (-1, -1), 'MIDDLE'),
            ('PADDING', (0, 0), (-1, -1), 4),
        ]))
        story.append(t)
        story.append(Spacer(1, 0.5*cm))

        fuel_data = {}
        for s in shifts:
            for pr in db.query(PumpReading).filter(PumpReading.shift_id == s.id).all():
                pump = db.query(Pump).filter(Pump.id == pr.pump_id).first()
                ft = pump.fuel_type if pump else 'UNKNOWN'
                if ft not in fuel_data:
                    fuel_data[ft] = {'ltr': 0.0, 'rs': 0.0}
                fuel_data[ft]['ltr'] += float(pr.sale_ltr)
                fuel_data[ft]['rs'] += float(pr.sale_amount)

        if fuel_data:
            story.append(Paragraph("<b>Fuel Breakdown</b>", styles['Heading3']))
            fheaders = ['Fuel Type', 'Litres Sold', 'Amount (Rs.)']
            fdata = [fheaders]
            for ft, v in sorted(fuel_data.items()):
                fdata.append([ft, f"{v['ltr']:,.1f}", f"{v['rs']:,.0f}"])
            ft_table = Table(fdata, colWidths=[4*cm, 4*cm, 4*cm])
            ft_table.setStyle(TableStyle([
                ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#334155')),
                ('TEXTCOLOR', (0, 0), (-1, 0), colors.white),
                ('FONTSIZE', (0, 0), (-1, -1), 9),
                ('GRID', (0, 0), (-1, -1), 0.3, colors.HexColor('#cbd5e1')),
                ('ALIGN', (1, 0), (-1, -1), 'RIGHT'),
                ('PADDING', (0, 0), (-1, -1), 4),
            ]))
            story.append(ft_table)

    doc.build(story)
    buf.seek(0)
    return StreamingResponse(buf, media_type="application/pdf",
                             headers={"Content-Disposition": f'attachment; filename="daily_{date}.pdf"'})


@router.get("/reports/monthly-summary")
def monthly_summary_pdf(
    month: int,
    year: int,
    db: Session = Depends(get_db),
    _=Depends(get_current_user),
):
    A4, landscape, colors, SimpleDocTemplate, Table, TableStyle, Paragraph, Spacer, getSampleStyleSheet, cm = _get_pdf_tools()

    d_from = dt_date(year, month, 1)
    d_to = dt_date(year, month, cal_mod.monthrange(year, month)[1])

    shifts = db.query(DailyShift).filter(
        DailyShift.record_date.between(d_from, d_to)
    ).order_by(DailyShift.record_date, DailyShift.shift_type).all()

    buf = io.BytesIO()
    doc = SimpleDocTemplate(buf, pagesize=landscape(A4),
                            leftMargin=1.5*cm, rightMargin=1.5*cm,
                            topMargin=1.5*cm, bottomMargin=1.5*cm)
    styles = getSampleStyleSheet()
    story = []

    month_name = cal_mod.month_name[month]
    story.append(Paragraph(f"<b>Monthly Summary — {month_name} {year}</b>", styles['Title']))
    story.append(Spacer(1, 0.4*cm))

    if not shifts:
        story.append(Paragraph("No data for this period.", styles['Normal']))
    else:
        headers = ['Date', 'Shift', 'Staff', 'Sale (Rs.)', 'Cash', 'Card', 'Credit', 'Difference']
        data = [headers]
        totals = {'sale': 0.0, 'cash': 0.0, 'card': 0.0, 'credit': 0.0, 'diff': 0.0}
        for s in shifts:
            staff_name = s.staff_member.full_name if s.staff_member else '—'
            card = float(s.card_visa) + float(s.card_amex) + float(s.card_touch)
            sale = float(s.total_sale_calc)
            cash = float(s.cash_collected)
            credit = float(s.credit_total)
            diff = float(s.difference)
            totals['sale'] += sale; totals['cash'] += cash
            totals['card'] += card; totals['credit'] += credit; totals['diff'] += diff
            data.append([str(s.record_date), s.shift_type, staff_name,
                         f"{sale:,.0f}", f"{cash:,.0f}", f"{card:,.0f}",
                         f"{credit:,.0f}", f"{diff:,.0f}"])
        data.append(['', '', 'TOTAL',
                     f"{totals['sale']:,.0f}", f"{totals['cash']:,.0f}",
                     f"{totals['card']:,.0f}", f"{totals['credit']:,.0f}",
                     f"{totals['diff']:,.0f}"])

        col_widths = [2.5*cm, 2*cm, 4*cm, 3.5*cm, 3*cm, 3*cm, 3*cm, 3*cm]
        t = Table(data, colWidths=col_widths, repeatRows=1)
        t.setStyle(TableStyle([
            ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#1e293b')),
            ('TEXTCOLOR', (0, 0), (-1, 0), colors.white),
            ('BACKGROUND', (0, -1), (-1, -1), colors.HexColor('#fef9c3')),
            ('FONTNAME', (0, -1), (-1, -1), 'Helvetica-Bold'),
            ('FONTSIZE', (0, 0), (-1, 0), 9),
            ('FONTSIZE', (0, 1), (-1, -1), 8),
            ('ROWBACKGROUNDS', (0, 1), (-1, -2), [colors.white, colors.HexColor('#f8fafc')]),
            ('GRID', (0, 0), (-1, -1), 0.3, colors.HexColor('#cbd5e1')),
            ('ALIGN', (3, 0), (-1, -1), 'RIGHT'),
            ('VALIGN', (0, 0), (-1, -1), 'MIDDLE'),
            ('PADDING', (0, 0), (-1, -1), 4),
        ]))
        story.append(t)

    doc.build(story)
    buf.seek(0)
    return StreamingResponse(buf, media_type="application/pdf",
                             headers={"Content-Disposition": f'attachment; filename="monthly_{year}_{month:02d}.pdf"'})


@router.get("/reports/credit-outstanding")
def credit_outstanding_pdf(
    db: Session = Depends(get_db),
    _=Depends(get_current_user),
):
    A4, landscape, colors, SimpleDocTemplate, Table, TableStyle, Paragraph, Spacer, getSampleStyleSheet, cm = _get_pdf_tools()

    customers = db.query(CreditCustomer).filter(
        CreditCustomer.status == 'ACTIVE',
        CreditCustomer.current_balance > 0,
    ).order_by(CreditCustomer.current_balance.desc()).all()

    buf = io.BytesIO()
    doc = SimpleDocTemplate(buf, pagesize=A4,
                            leftMargin=2*cm, rightMargin=2*cm,
                            topMargin=2*cm, bottomMargin=2*cm)
    styles = getSampleStyleSheet()
    story = []

    story.append(Paragraph(f"<b>Credit Outstanding Report</b>", styles['Title']))
    story.append(Paragraph(f"Generated: {dt_date.today()}", styles['Normal']))
    story.append(Spacer(1, 0.5*cm))

    if not customers:
        story.append(Paragraph("No outstanding credit balances.", styles['Normal']))
    else:
        headers = ['#', 'Company', 'Contact', 'Phone', 'Credit Limit (Rs.)', 'Balance (Rs.)', 'Utilisation']
        data = [headers]
        grand_total = 0.0
        for i, c in enumerate(customers, 1):
            bal = float(c.current_balance)
            limit = float(c.credit_limit)
            util = f"{(bal/limit*100):.1f}%" if limit > 0 else '—'
            grand_total += bal
            data.append([str(i), c.company_name, c.contact_person or '—',
                          c.contact_phone or '—',
                          f"{limit:,.0f}", f"{bal:,.0f}", util])
        data.append(['', '', '', 'GRAND TOTAL', '', f"{grand_total:,.0f}", ''])

        col_widths = [0.8*cm, 5*cm, 3.5*cm, 3*cm, 3.5*cm, 3.5*cm, 2.5*cm]
        t = Table(data, colWidths=col_widths, repeatRows=1)
        t.setStyle(TableStyle([
            ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#1e293b')),
            ('TEXTCOLOR', (0, 0), (-1, 0), colors.white),
            ('BACKGROUND', (0, -1), (-1, -1), colors.HexColor('#fef9c3')),
            ('FONTNAME', (0, -1), (-1, -1), 'Helvetica-Bold'),
            ('FONTSIZE', (0, 0), (-1, 0), 9),
            ('FONTSIZE', (0, 1), (-1, -1), 8),
            ('ROWBACKGROUNDS', (0, 1), (-1, -2), [colors.white, colors.HexColor('#fff7ed')]),
            ('GRID', (0, 0), (-1, -1), 0.3, colors.HexColor('#cbd5e1')),
            ('ALIGN', (4, 0), (-1, -1), 'RIGHT'),
            ('VALIGN', (0, 0), (-1, -1), 'MIDDLE'),
            ('PADDING', (0, 0), (-1, -1), 4),
        ]))
        story.append(t)

    doc.build(story)
    buf.seek(0)
    return StreamingResponse(buf, media_type="application/pdf",
                             headers={"Content-Disposition": 'attachment; filename="credit_outstanding.pdf"'})


# ── Helpers ───────────────────────────────────────────────────────────────────

def _fmtd(s: str) -> str:
    try:
        return dt_date.fromisoformat(s).strftime('%d %B %Y')
    except Exception:
        return s


def _get_setting(db: Session, key: str, default: str = '') -> str:
    s = db.query(SystemSetting).filter(SystemSetting.setting_key == key).first()
    return s.setting_value if s else default


# ── Shared report data builder ────────────────────────────────────────────────

def _build_report_data(db: Session, d_from: dt_date, d_to: dt_date) -> dict:
    is_single_day = (d_from == d_to)
    SHIFT_ORDER   = {'EM': 0, 'DAY': 1, 'MS': 2, 'ES': 3}

    shifts = (
        db.query(DailyShift)
        .options(
            joinedload(DailyShift.staff_member),
            joinedload(DailyShift.pump_readings).joinedload(PumpReading.pump),
            joinedload(DailyShift.pump_readings).joinedload(PumpReading.pumper),
            joinedload(DailyShift.credit_sales).joinedload(CreditSale.customer),
            joinedload(DailyShift.cash_denominations).joinedload(CashDenomination.pumper),
            joinedload(DailyShift.collection_cycles),
        )
        .filter(DailyShift.record_date.between(d_from, d_to))
        .order_by(DailyShift.record_date, DailyShift.shift_type)
        .all()
    )

    summary = {
        'shifts_count': len(shifts),
        'total_sale': 0.0, 'total_litres': 0.0,
        'cash': 0.0, 'card_visa': 0.0, 'card_amex': 0.0, 'card_touch': 0.0,
        'credit': 0.0, 'other': 0.0, 'shortage': 0.0, 'advance': 0.0, 'difference': 0.0,
    }
    fuel_map:  dict = {}
    staff_map: dict = {}
    day_map:   dict = {}
    shift_list = []

    for s in shifts:
        sale   = float(s.total_sale_calc or 0)
        cash   = float(s.cash_collected  or 0)
        visa   = float(s.card_visa       or 0)
        amex   = float(s.card_amex       or 0)
        touch  = float(s.card_touch      or 0)
        credit = float(s.credit_total    or 0)
        other  = float(s.other_income    or 0)
        short  = float(s.shortage        or 0)
        adv    = float(s.advance         or 0)
        diff   = float(s.difference      or 0)

        for k, v in (('total_sale', sale), ('cash', cash), ('card_visa', visa),
                     ('card_amex', amex), ('card_touch', touch), ('credit', credit),
                     ('other', other), ('shortage', short), ('advance', adv), ('difference', diff)):
            summary[k] += v

        sid  = s.staff_id
        dstr = str(s.record_date)
        if dstr not in day_map:
            day_map[dstr] = {'date': dstr, 'total_sale': 0.0, 'total_ltr': 0.0, 'shortage': 0.0, 'shifts': 0}
        day_map[dstr]['total_sale'] += sale
        day_map[dstr]['shortage']   += short
        day_map[dstr]['shifts']     += 1

        if sid and sid not in staff_map:
            staff_map[sid] = {
                'staff_name': s.staff_member.full_name if s.staff_member else f'#{sid}',
                'shifts': 0, 'total_ltr': 0.0, 'total_sale': 0.0, 'shortage': 0.0,
            }
        if sid:
            staff_map[sid]['shifts']     += 1
            staff_map[sid]['total_sale'] += sale
            staff_map[sid]['shortage']   += short

        pump_out = []
        for pr in s.pump_readings:
            ft  = pr.pump.fuel_type if pr.pump else 'UNKNOWN'
            ltr = float(pr.sale_ltr    or 0)
            amt = float(pr.sale_amount or 0)
            rte = float(pr.fuel_rate   or 0)
            summary['total_litres'] += ltr
            day_map[dstr]['total_ltr'] += ltr
            if sid:
                staff_map[sid]['total_ltr'] += ltr
            if ft not in fuel_map:
                fuel_map[ft] = {'fuel_type': ft, 'total_ltr': 0.0, 'total_rs': 0.0, 'readings': 0, 'rate': rte}
            fuel_map[ft]['total_ltr'] += ltr
            fuel_map[ft]['total_rs']  += amt
            fuel_map[ft]['readings']  += 1
            if rte > 0:
                fuel_map[ft]['rate'] = rte
            pump_out.append({
                'pump_code':      pr.pump.pump_code if pr.pump else '?',
                'fuel_type':      ft,
                'pumper_name':    pr.pumper.full_name if pr.pumper else None,
                'starting_meter': float(pr.starting_meter or 0),
                'ending_meter':   float(pr.ending_meter   or 0),
                'testing_ltr':    float(pr.testing_ltr    or 0),
                'sale_ltr':       ltr,
                'fuel_rate':      rte,
                'sale_amount':    amt,
            })

        credit_out = [
            {
                'bill_no':       cs.bill_no or '—',
                'customer_name': cs.customer.company_name if cs.customer else '—',
                'vehicle_no':    cs.vehicle_no or '—',
                'amount':        float(cs.amount or 0),
            }
            for cs in s.credit_sales
        ]

        denom_out = [
            {
                'bag_number':  d.bag_number,
                'pumper_name': d.pumper.full_name if d.pumper else None,
                'note_5000': d.note_5000 or 0, 'note_2000': d.note_2000 or 0,
                'note_1000': d.note_1000 or 0, 'note_500':  d.note_500  or 0,
                'note_100':  d.note_100  or 0, 'note_50':   d.note_50   or 0,
                'note_20':   d.note_20   or 0, 'note_10':   d.note_10   or 0,
                'calculated_total': float(d.calculated_total or 0),
            }
            for d in sorted(s.cash_denominations, key=lambda x: x.bag_number)
        ]

        cycles_out = [
            {
                'cycle_number': cc.cycle_number,
                'cash_total':   float(getattr(cc, 'cash_total',   0) or 0),
                'card_visa':    float(getattr(cc, 'card_visa',    0) or 0),
                'card_amex':    float(getattr(cc, 'card_amex',    0) or 0),
                'card_touch':   float(getattr(cc, 'card_touch',   0) or 0),
                'credit_total': float(getattr(cc, 'credit_total', 0) or 0),
                'other_income': float(getattr(cc, 'other_income', 0) or 0),
                'shortage':     float(getattr(cc, 'shortage',     0) or 0),
                'advance':      float(getattr(cc, 'advance',      0) or 0),
                'is_final':     getattr(cc, 'is_final', False),
            }
            for cc in sorted(getattr(s, 'collection_cycles', []), key=lambda x: x.cycle_number)
        ]

        shift_list.append({
            'id': s.id, 'record_date': dstr, 'shift_type': s.shift_type,
            'staff_name': s.staff_member.full_name if s.staff_member else None,
            'status': s.status,
            'total_sale': sale, 'cash': cash,
            'card_visa': visa, 'card_amex': amex, 'card_touch': touch,
            'credit': credit, 'other': other, 'shortage': short,
            'advance': adv, 'difference': diff,
            'pump_readings': pump_out, 'credit_sales': credit_out,
            'denominations': denom_out, 'collection_cycles': cycles_out,
        })

    shift_list.sort(key=lambda s: (s['record_date'], SHIFT_ORDER.get(s['shift_type'], 99)))

    # Tank readings (today)
    tank_readings = []
    if is_single_day:
        for t in db.query(TankDipReading).filter(TankDipReading.read_date == d_from).all():
            tank_readings.append({
                'tank_name':  t.tank_name,
                'height_cm':  float(t.height_cm),
                'volume_ltr': float(t.volume_ltr),
            })

    # Stock deliveries
    stock_records = (
        db.query(StockDelivery)
        .filter(StockDelivery.delivery_date.between(d_from, d_to))
        .order_by(StockDelivery.delivery_date, StockDelivery.fuel_type)
        .all()
    )
    stock_list = [
        {
            'delivery_date': str(sd.delivery_date),
            'fuel_type':     sd.fuel_type,
            'liters':        float(sd.liters),
            'supplier':      sd.supplier   or '—',
            'invoice_no':    sd.invoice_no or '—',
            'rate_per_ltr':  float(sd.rate_per_ltr or 0),
            'total_cost':    float(sd.total_cost   or 0),
        }
        for sd in stock_records
    ]

    # Fuel reconciliation
    prev_date = d_from - timedelta(days=1)
    opening_dips = {
        t.tank_name: float(t.volume_ltr)
        for t in db.query(TankDipReading).filter(TankDipReading.read_date == prev_date).all()
    }
    closing_dips = {
        t.tank_name: float(t.volume_ltr)
        for t in db.query(TankDipReading).filter(TankDipReading.read_date == d_to).all()
    }
    delivery_by_fuel: dict = {}
    for sd in stock_records:
        delivery_by_fuel[sd.fuel_type] = delivery_by_fuel.get(sd.fuel_type, 0.0) + float(sd.liters)

    reconciliation = []
    for ft in ['LP92', 'EURO3', 'LAD', 'LADXM']:
        opening     = opening_dips.get(ft, 0.0)
        deliveries  = delivery_by_fuel.get(ft, 0.0)
        sales       = fuel_map.get(ft, {}).get('total_ltr', 0.0)
        theoretical = opening + deliveries - sales
        actual      = closing_dips.get(ft) if ft in closing_dips else None
        variance    = round(actual - theoretical, 1) if actual is not None else None
        if opening > 0 or deliveries > 0 or sales > 0:
            reconciliation.append({
                'fuel_type':       ft,
                'opening_ltr':     round(opening,     1),
                'deliveries_ltr':  round(deliveries,  1),
                'sales_ltr':       round(sales,        1),
                'theoretical_ltr': round(theoretical,  1),
                'actual_ltr':      round(actual, 1) if actual is not None else None,
                'variance_ltr':    variance,
            })

    fuel_list = []
    for ft in ['LP92', 'EURO3', 'LAD', 'LADXM']:
        if ft in fuel_map:
            fb = fuel_map[ft]
            avg = fb['total_rs'] / fb['total_ltr'] if fb['total_ltr'] > 0 else fb['rate']
            fuel_list.append({**fb, 'avg_rate': round(avg, 2)})

    return {
        'date_from': str(d_from), 'date_to': str(d_to),
        'is_single_day': is_single_day,
        'generated_at':  datetime.now().isoformat(),
        'summary':        summary,
        'fuel_breakdown': fuel_list,
        'payment_methods': {
            'cash': summary['cash'], 'card_visa': summary['card_visa'],
            'card_amex': summary['card_amex'], 'card_touch': summary['card_touch'],
            'credit': summary['credit'], 'other': summary['other'],
        },
        'shifts':              shift_list,
        'tank_readings':       tank_readings,
        'stock_deliveries':    stock_list,
        'fuel_reconciliation': reconciliation,
        'staff_performance':   sorted(staff_map.values(), key=lambda x: x['total_ltr'], reverse=True),
        'daily_trend':         sorted(day_map.values(), key=lambda x: x['date']),
    }


# ── Full JSON report ──────────────────────────────────────────────────────────

@router.get("/reports/full-report")
def full_report_json(
    date_from: str = Query(...),
    date_to:   str = Query(...),
    db: Session = Depends(get_db),
    _=Depends(get_current_user),
):
    try:
        d_from = dt_date.fromisoformat(date_from)
        d_to   = dt_date.fromisoformat(date_to)
    except ValueError:
        raise HTTPException(status_code=400, detail="Invalid date. Use YYYY-MM-DD")
    if d_to < d_from:
        raise HTTPException(status_code=400, detail="date_to must be >= date_from")
    return _build_report_data(db, d_from, d_to)


# ── Email HTML builder ────────────────────────────────────────────────────────

def _build_email_html(data: dict) -> str:
    s = data['summary']
    total_card = float(s.get('card_visa', 0)) + float(s.get('card_amex', 0)) + float(s.get('card_touch', 0))
    date_label = _fmtd(data['date_from']) if data['is_single_day'] else f"{_fmtd(data['date_from'])} &ndash; {_fmtd(data['date_to'])}"

    def rs(n): return f"Rs.&nbsp;{float(n or 0):,.0f}"
    def lt(n): return f"{float(n or 0):,.1f}&nbsp;L"

    TBL  = 'width:100%;border-collapse:collapse;margin-bottom:16px;font-size:12px;'
    TH   = 'background:#1e293b;color:#ffffff;padding:8px 10px;text-align:left;font-size:11px;white-space:nowrap;'
    TD0  = 'padding:7px 10px;border-bottom:1px solid #e2e8f0;background:#ffffff;'
    TD1  = 'padding:7px 10px;border-bottom:1px solid #e2e8f0;background:#f8fafc;'
    TOTD = 'padding:7px 10px;font-weight:700;border-top:2px solid #94a3b8;background:#fef9c3;'
    SEC  = 'font-size:11px;font-weight:700;text-transform:uppercase;letter-spacing:1px;color:#64748b;border-bottom:2px solid #e2e8f0;padding-bottom:6px;margin:20px 0 10px;'

    def tbl(headers, rows, totals=None):
        ths = ''.join(f'<th style="{TH}">{h}</th>' for h in headers)
        body = ''
        for i, row in enumerate(rows):
            td = TD0 if i % 2 == 0 else TD1
            tds = ''.join(f'<td style="{td}">{c}</td>' for c in row)
            body += f'<tr>{tds}</tr>'
        if totals:
            tds = ''.join(f'<td style="{TOTD}">{c}</td>' for c in totals)
            body += f'<tr>{tds}</tr>'
        return f'<table style="{TBL}"><thead><tr>{ths}</tr></thead><tbody>{body}</tbody></table>'

    def metric(label, value):
        return (
            f'<td style="padding:10px;border:1px solid #e2e8f0;background:#f8fafc;vertical-align:top;width:16%;">'
            f'<div style="font-size:10px;color:#64748b;margin-bottom:3px;">{label}</div>'
            f'<div style="font-size:14px;font-weight:700;color:#1e293b;">{value}</div></td>'
        )

    # Summary
    metrics = (
        f'<table style="width:100%;border-collapse:collapse;margin-bottom:16px;">'
        f'<tr>'
        f'{metric("Total Sale", rs(s["total_sale"]))}'
        f'{metric("Total Fuel", lt(s["total_litres"]))}'
        f'{metric("Cash", rs(s["cash"]))}'
        f'{metric("Card", rs(total_card))}'
        f'{metric("Credit", rs(s["credit"]))}'
        f'{metric("Shortage", rs(s["shortage"]))}'
        f'</tr></table>'
    )

    # Fuel breakdown
    fuel_html = ''
    if data['fuel_breakdown']:
        rows = [[f['fuel_type'], lt(f['total_ltr']), f"{float(f['avg_rate']):.2f}", rs(f['total_rs'])]
                for f in data['fuel_breakdown']]
        tot = ['TOTAL', lt(sum(f['total_ltr'] for f in data['fuel_breakdown'])), '',
               rs(sum(f['total_rs'] for f in data['fuel_breakdown']))]
        fuel_html = f'<p style="{SEC}">Fuel Breakdown</p>' + tbl(['Fuel', 'Litres Sold', 'Avg Rate (Rs.)', 'Amount (Rs.)'], rows, tot)

    # Shift overview
    shifts_html = ''
    SLABELS = {'EM': 'Early Morning', 'DAY': 'Day', 'MS': 'Morning', 'ES': 'Evening'}
    if data['shifts']:
        rows = [
            [sh['record_date'], SLABELS.get(sh['shift_type'], sh['shift_type']),
             sh['staff_name'] or '—', sh['status'],
             rs(sh['total_sale']), rs(sh['cash']),
             rs(float(sh['card_visa']) + float(sh['card_amex']) + float(sh['card_touch'])),
             rs(sh['credit']), rs(sh['shortage']), rs(sh['difference'])]
            for sh in data['shifts']
        ]
        tot = ['', '', '', 'TOTAL', rs(s['total_sale']), rs(s['cash']), rs(total_card),
               rs(s['credit']), rs(s['shortage']), rs(s['difference'])]
        title = 'Shift Overview' if data['is_single_day'] else 'All Shifts'
        shifts_html = f'<p style="{SEC}">{title}</p>' + tbl(
            ['Date', 'Shift', 'Staff', 'Status', 'Sale (Rs.)', 'Cash', 'Card', 'Credit', 'Shortage', 'Difference'],
            rows, tot)

    # Tank dip readings
    tank_html = ''
    if data['is_single_day'] and data['tank_readings']:
        rows = [[t['tank_name'], f"{float(t['height_cm']):.1f}", f"{float(t['volume_ltr']):,.1f}"]
                for t in data['tank_readings']]
        tot = ['TOTAL', '—', f"{sum(float(t['volume_ltr']) for t in data['tank_readings']):,.1f}"]
        tank_html = f'<p style="{SEC}">Tank Dip Readings</p>' + tbl(['Tank', 'Height (cm)', 'Volume (Ltrs)'], rows, tot)

    # Fuel reconciliation
    recon_html = ''
    if data.get('fuel_reconciliation'):
        rows = []
        for r in data['fuel_reconciliation']:
            var = r['variance_ltr']
            abs_var = abs(var) if var is not None else None
            var_str = ('+' if var and var >= 0 else '') + (f"{var:,.1f}" if var is not None else '—')
            var_color = '#dc2626' if abs_var is not None and abs_var >= 200 else \
                        '#ea580c' if abs_var is not None and abs_var >= 50 else '#16a34a'
            status = '✓ OK' if abs_var is not None and abs_var < 50 else \
                     '⚠ Check' if abs_var is not None and abs_var < 200 else \
                     '✗ Critical' if abs_var is not None else 'No dip data'
            rows.append([
                r['fuel_type'],
                f"{r['opening_ltr']:,.1f}",
                f"+{r['deliveries_ltr']:,.1f}",
                f"&minus;{r['sales_ltr']:,.1f}",
                f"{r['theoretical_ltr']:,.1f}",
                f"{r['actual_ltr']:,.1f}" if r['actual_ltr'] is not None else '—',
                f'<span style="color:{var_color};font-weight:700">{var_str}</span>',
                status,
            ])
        recon_html = f'<p style="{SEC}">Fuel Reconciliation</p>' + tbl(
            ['Fuel', 'Opening (L)', '+Deliveries', '&minus;Sales', 'Theoretical', 'Actual Dip', 'Variance', 'Status'],
            rows)

    # Stock deliveries
    stock_html = ''
    if data.get('stock_deliveries'):
        rows = [[sd['delivery_date'], sd['fuel_type'], f"{sd['liters']:,.1f}", sd['supplier'], sd['invoice_no']]
                for sd in data['stock_deliveries']]
        tot = ['TOTAL', '', f"{sum(sd['liters'] for sd in data['stock_deliveries']):,.1f}", '', '']
        stock_html = f'<p style="{SEC}">Stock Deliveries</p>' + tbl(
            ['Date', 'Fuel', 'Litres', 'Supplier', 'Invoice No'], rows, tot)

    # Staff performance
    staff_html = ''
    if data['staff_performance']:
        rows = [[st['staff_name'], st['shifts'], lt(st['total_ltr']), rs(st['total_sale']), rs(st['shortage'])]
                for st in data['staff_performance']]
        staff_html = f'<p style="{SEC}">Staff Performance</p>' + tbl(
            ['Staff', 'Shifts', 'Litres', 'Sale (Rs.)', 'Shortage (Rs.)'], rows)

    gen_time = datetime.now().strftime('%d %b %Y %H:%M')

    return f'''<!DOCTYPE html>
<html lang="en"><head><meta charset="utf-8">
<meta name="viewport" content="width=device-width,initial-scale=1">
<title>Daily Operations Report — Dunhinda Brothers</title></head>
<body style="font-family:Arial,Helvetica,sans-serif;background:#f1f5f9;padding:20px;margin:0;color:#1e293b;">
<div style="max-width:820px;margin:0 auto;background:#ffffff;border-radius:8px;overflow:hidden;border:1px solid #e2e8f0;">
  <div style="background:#1e293b;color:#ffffff;padding:28px 24px;text-align:center;">
    <h1 style="margin:0;font-size:22px;letter-spacing:3px;font-weight:900;">DUNHINDA BROTHERS</h1>
    <p style="margin:4px 0 0;font-size:12px;color:#94a3b8;">Daily Operations Report</p>
    <p style="margin:10px 0 0;font-size:17px;color:#fbbf24;font-weight:700;">{date_label}</p>
    <p style="margin:4px 0 0;font-size:11px;color:#64748b;">Generated: {gen_time}</p>
  </div>
  <div style="padding:24px;">
    <p style="{SEC}">Executive Summary</p>
    {metrics}
    {fuel_html}
    {shifts_html}
    {tank_html}
    {recon_html}
    {stock_html}
    {staff_html}
    <div style="margin-top:24px;padding-top:16px;border-top:1px solid #e2e8f0;text-align:center;font-size:11px;color:#94a3b8;">
      Dunhinda Brothers &mdash; Confidential Operations Report &mdash; {gen_time}<br>
      This report was generated automatically. Do not reply to this email.
    </div>
  </div>
</div>
</body></html>'''


# ── CSV builder ───────────────────────────────────────────────────────────────

def _build_csv(data: dict) -> str:
    buf = _io.StringIO()
    w   = csv_mod.writer(buf)
    s   = data['summary']
    total_card = float(s.get('card_visa', 0)) + float(s.get('card_amex', 0)) + float(s.get('card_touch', 0))
    date_label = data['date_from'] if data['is_single_day'] else f"{data['date_from']} to {data['date_to']}"

    w.writerow(['DUNHINDA BROTHERS — Daily Operations Report'])
    w.writerow([f'Period: {date_label}'])
    w.writerow([f'Generated: {data["generated_at"][:19]}'])
    w.writerow([])

    w.writerow(['EXECUTIVE SUMMARY'])
    w.writerow(['Shifts', 'Total Sale (Rs.)', 'Total Fuel (L)', 'Cash', 'Card Total', 'Credit', 'Other', 'Shortage', 'Advance', 'Difference'])
    w.writerow([s['shifts_count'], f"{s['total_sale']:.2f}", f"{s['total_litres']:.1f}",
                f"{s['cash']:.2f}", f"{total_card:.2f}", f"{s['credit']:.2f}",
                f"{s['other']:.2f}", f"{s['shortage']:.2f}", f"{s['advance']:.2f}", f"{s['difference']:.2f}"])
    w.writerow([])

    w.writerow(['SHIFT DETAILS'])
    w.writerow(['Date', 'Shift', 'Staff', 'Status', 'Sale (Rs.)', 'Cash', 'Card Visa', 'Card Amex', 'Card Touch', 'Credit', 'Other', 'Shortage', 'Advance', 'Difference'])
    for sh in data['shifts']:
        w.writerow([sh['record_date'], sh['shift_type'], sh['staff_name'] or '', sh['status'],
                    f"{sh['total_sale']:.2f}", f"{sh['cash']:.2f}",
                    f"{sh['card_visa']:.2f}", f"{sh['card_amex']:.2f}", f"{sh['card_touch']:.2f}",
                    f"{sh['credit']:.2f}", f"{sh['other']:.2f}", f"{sh['shortage']:.2f}",
                    f"{sh['advance']:.2f}", f"{sh['difference']:.2f}"])
    w.writerow([])

    if data['fuel_breakdown']:
        w.writerow(['FUEL BREAKDOWN'])
        w.writerow(['Fuel Type', 'Litres Sold', 'Avg Rate (Rs./L)', 'Amount (Rs.)'])
        for f in data['fuel_breakdown']:
            w.writerow([f['fuel_type'], f"{f['total_ltr']:.1f}", f"{f['avg_rate']:.2f}", f"{f['total_rs']:.2f}"])
        w.writerow([])

    if data['is_single_day'] and data['tank_readings']:
        w.writerow(['TANK DIP READINGS'])
        w.writerow(['Tank', 'Height (cm)', 'Volume (Ltrs)'])
        for t in data['tank_readings']:
            w.writerow([t['tank_name'], t['height_cm'], t['volume_ltr']])
        w.writerow([])

    if data.get('fuel_reconciliation'):
        w.writerow(['FUEL RECONCILIATION'])
        w.writerow(['Fuel Type', 'Opening (L)', 'Deliveries (L)', 'Sales (L)', 'Theoretical (L)', 'Actual Dip (L)', 'Variance (L)', 'Status'])
        for r in data['fuel_reconciliation']:
            var = r['variance_ltr']
            abs_var = abs(var) if var is not None else None
            status = 'OK' if abs_var is not None and abs_var < 50 else \
                     'Check' if abs_var is not None and abs_var < 200 else \
                     'Critical' if abs_var is not None else 'No dip data'
            w.writerow([r['fuel_type'], r['opening_ltr'], r['deliveries_ltr'], r['sales_ltr'],
                        r['theoretical_ltr'], r['actual_ltr'] if r['actual_ltr'] is not None else '',
                        var if var is not None else '', status])
        w.writerow([])

    if data.get('stock_deliveries'):
        w.writerow(['STOCK DELIVERIES'])
        w.writerow(['Date', 'Fuel Type', 'Litres', 'Supplier', 'Invoice No', 'Rate/Ltr', 'Total Cost'])
        for sd in data['stock_deliveries']:
            w.writerow([sd['delivery_date'], sd['fuel_type'], sd['liters'],
                        sd['supplier'], sd['invoice_no'],
                        sd['rate_per_ltr'] if sd['rate_per_ltr'] > 0 else '',
                        sd['total_cost'] if sd['total_cost'] > 0 else ''])
        w.writerow([])

    if data['staff_performance']:
        w.writerow(['STAFF PERFORMANCE'])
        w.writerow(['Staff Name', 'Shifts', 'Litres Dispensed', 'Sale (Rs.)', 'Shortage (Rs.)'])
        for st in data['staff_performance']:
            w.writerow([st['staff_name'], st['shifts'], f"{st['total_ltr']:.1f}",
                        f"{st['total_sale']:.2f}", f"{st['shortage']:.2f}"])

    return buf.getvalue()


# ── Email with attachment sender ──────────────────────────────────────────────

def _send_report_email(db: Session, to_email: str, subject: str, html: str, csv_content: str, csv_filename: str) -> None:
    smtp_host  = _get_setting(db, 'smtp_host')
    smtp_port  = int(_get_setting(db, 'smtp_port', '587'))
    smtp_user  = _get_setting(db, 'smtp_user')
    smtp_pass  = _get_setting(db, 'smtp_password')
    from_email = _get_setting(db, 'smtp_from_email') or smtp_user

    if not smtp_host:
        raise ValueError('SMTP host not configured in Settings')

    msg = MIMEMultipart('mixed')
    msg['Subject'] = subject
    msg['From']    = from_email
    msg['To']      = to_email

    alt = MIMEMultipart('alternative')
    alt.attach(MIMEText(html, 'html', 'utf-8'))
    msg.attach(alt)

    att = MIMEBase('text', 'csv; charset=utf-8')
    att.set_payload(('' + csv_content).encode('utf-8'))  # BOM for Excel compat
    encoders.encode_base64(att)
    att.add_header('Content-Disposition', 'attachment', filename=csv_filename)
    msg.attach(att)

    if smtp_port == 465:
        with smtplib.SMTP_SSL(smtp_host, smtp_port) as srv:
            if smtp_user and smtp_pass: srv.login(smtp_user, smtp_pass)
            srv.sendmail(from_email, [to_email], msg.as_string())
    elif smtp_port == 25:
        with smtplib.SMTP(smtp_host, smtp_port) as srv:
            srv.ehlo()
            if smtp_user and smtp_pass: srv.login(smtp_user, smtp_pass)
            srv.sendmail(from_email, [to_email], msg.as_string())
    else:
        with smtplib.SMTP(smtp_host, smtp_port) as srv:
            srv.ehlo(); srv.starttls()
            if smtp_user and smtp_pass: srv.login(smtp_user, smtp_pass)
            srv.sendmail(from_email, [to_email], msg.as_string())


# ── Send daily report endpoint ────────────────────────────────────────────────

@router.post("/reports/send-daily")
def send_daily_report(
    request:  Request,
    date:     Optional[str] = Query(None, description="YYYY-MM-DD, defaults to today"),
    cron_key: Optional[str] = Query(None, description="Pre-shared key for cron jobs"),
    db: Session = Depends(get_db),
):
    """Send the daily operations report by email to all OWNER/SUPER_ADMIN users
    who have email notifications enabled.
    Auth: valid JWT Bearer token (manual send) OR matching cron_key query param (scheduled)."""
    from ..auth import decode_token
    from jose import JWTError

    # ── Authenticate ────────────────────────────────────────────────────────
    authed = False
    auth_header = request.headers.get('Authorization', '')
    if auth_header.startswith('Bearer '):
        try:
            payload = decode_token(auth_header[7:])
            user = db.query(User).filter(
                User.id == int(payload.get('sub', 0)),
                User.is_active == True,
            ).first()
            if user:
                authed = True
        except (JWTError, Exception):
            pass

    if not authed:
        expected = _get_setting(db, 'report_cron_key', '')
        if expected and cron_key == expected:
            authed = True

    if not authed:
        raise HTTPException(status_code=401, detail='Unauthorized — provide Bearer token or valid cron_key')

    # ── Date ────────────────────────────────────────────────────────────────
    try:
        target = dt_date.fromisoformat(date) if date else dt_date.today()
    except ValueError:
        raise HTTPException(status_code=400, detail='Invalid date format. Use YYYY-MM-DD')

    # ── Build report ─────────────────────────────────────────────────────────
    report_data  = _build_report_data(db, target, target)
    html_body    = _build_email_html(report_data)
    csv_content  = _build_csv(report_data)
    date_str     = target.isoformat()
    csv_filename = f"dunhinda_report_{date_str}.csv"
    subject      = f"Daily Operations Report — {target.strftime('%d %b %Y')} — Dunhinda Brothers"

    # ── Find recipients ───────────────────────────────────────────────────────
    prefs = (
        db.query(NotificationPreference)
        .join(User, User.id == NotificationPreference.user_id)
        .filter(
            User.is_active == True,
            User.role.in_(['OWNER', 'SUPER_ADMIN']),
            NotificationPreference.email_enabled == True,
            NotificationPreference.email.isnot(None),
            NotificationPreference.email != '',
        )
        .all()
    )

    # ── Send ─────────────────────────────────────────────────────────────────
    sent_to, failed = [], []
    for pref in prefs:
        try:
            _send_report_email(db, pref.email, subject, html_body, csv_content, csv_filename)
            sent_to.append(pref.email)
        except Exception as exc:
            failed.append({'email': pref.email, 'error': str(exc)})

    return {
        'report_date': date_str,
        'sent_to':     sent_to,
        'failed':      failed,
        'recipients_found': len(prefs),
    }