"""Haulwright Fleet Operations. FastAPI + SQLite, no external service required.

No authentication is included by design for the pilot/training edition.
Do not publish with real customer/driver data on an unrestricted public URL.
"""
from __future__ import annotations

import base64
import csv
import hashlib
import io
import json
import os
import re
import sqlite3
import uuid
from contextlib import contextmanager
from datetime import date, datetime, timedelta, timezone
from html import escape
from pathlib import Path
from zoneinfo import ZoneInfo

from fastapi import FastAPI, HTTPException, Request
from fastapi.responses import FileResponse, JSONResponse, Response
from fastapi.staticfiles import StaticFiles
from PIL import Image, ImageDraw, ImageOps, UnidentifiedImageError

ROOT = Path(__file__).resolve().parent
DATA = Path(os.environ.get('HAULWRIGHT_DATA_DIR', ROOT / 'data')).resolve()
DB = DATA / 'haulwright.sqlite3'
UPLOADS = DATA / 'uploads'
MODE = os.environ.get('HAULWRIGHT_MODE', 'training').lower()
DATA.mkdir(parents=True, exist_ok=True)
UPLOADS.mkdir(parents=True, exist_ok=True)
LOCAL_TZ = ZoneInfo('America/Nassau')
app = FastAPI(title='Haulwright Fleet Operations', docs_url=None, redoc_url=None)

TABLE_FIELDS = {
    'customers': ['name', 'contact', 'phone', 'email', 'address', 'payment_terms', 'status', 'notes'],
    'trucks': ['code', 'registration', 'make_model', 'capacity', 'odometer', 'status', 'insurance_due', 'service_due_date', 'service_due_km', 'notes'],
    'drivers': ['name', 'phone', 'licence', 'licence_expiry', 'status', 'notes'],
    'loads': ['customer_id', 'truck_id', 'driver_id', 'pickup', 'destination', 'material', 'planned_start', 'planned_end', 'qty', 'unit', 'rate', 'notes'],
    'daily_logs': ['driver_id', 'truck_id', 'log_date', 'start_time', 'end_time', 'odo_start', 'odo_end', 'customer_id', 'load_id', 'destination', 'material', 'quantity', 'fuel_uplift', 'notes'],
    'fuel': ['truck_id', 'driver_id', 'purchase_date', 'vendor', 'litres', 'unit_price', 'odometer', 'notes'],
    'maintenance': ['truck_id', 'service_date', 'service_type', 'supplier', 'amount', 'next_due_date', 'next_due_km', 'status', 'notes'],
    'expenses': ['truck_id', 'expense_date', 'category', 'supplier', 'amount', 'notes'],
    'inspections': ['truck_id', 'driver_id', 'inspection_date', 'odometer', 'brakes', 'tyres', 'lights', 'hydraulics', 'notes'],
    'invoices': ['status', 'notes'],
}
REQUIRED = {
    'customers': ['name'],
    'trucks': ['code', 'registration'],
    'drivers': ['name'],
    'loads': ['customer_id', 'truck_id', 'driver_id', 'pickup', 'destination', 'material', 'planned_start', 'planned_end', 'qty', 'rate'],
    'daily_logs': ['driver_id', 'truck_id', 'log_date', 'start_time', 'end_time', 'odo_start', 'odo_end'],
    'fuel': ['truck_id', 'purchase_date', 'vendor', 'litres', 'unit_price'],
    'maintenance': ['truck_id', 'service_date', 'service_type', 'amount'],
    'expenses': ['truck_id', 'expense_date', 'category', 'amount'],
    'inspections': ['truck_id', 'driver_id', 'inspection_date', 'brakes', 'tyres', 'lights', 'hydraulics'],
}
NUMERIC = {'capacity', 'odometer', 'service_due_km', 'qty', 'rate', 'odo_start', 'odo_end', 'quantity', 'fuel_uplift', 'litres', 'unit_price', 'amount', 'next_due_km'}
INTEGER_IDS = {'customer_id', 'truck_id', 'driver_id', 'load_id'}
TABLES = list(TABLE_FIELDS)


def now_iso():
    return datetime.now(timezone.utc).isoformat(timespec='seconds')


def local_date(offset=0):
    return (datetime.now(LOCAL_TZ) + timedelta(days=offset)).date().isoformat()


def dbconnect():
    c = sqlite3.connect(str(DB), timeout=20, check_same_thread=False)
    c.row_factory = sqlite3.Row
    c.execute('PRAGMA foreign_keys = ON')
    c.execute('PRAGMA busy_timeout = 20000')
    return c


@contextmanager
def transaction():
    conn = dbconnect()
    try:
        conn.execute('BEGIN IMMEDIATE')
        yield conn
        conn.commit()
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()


def row(conn, table, identifier):
    result = conn.execute(f'SELECT * FROM {table} WHERE id=?', (identifier,)).fetchone()
    return dict(result) if result else None


def audit(conn, actor, action, entity, entity_id, reference='', before=None, after=None):
    conn.execute('INSERT INTO audit (created_at, actor, action, entity, entity_id, reference, before_json, after_json) VALUES (?,?,?,?,?,?,?,?)',
                 (now_iso(), actor, action, entity, entity_id, str(reference or ''), json.dumps(before, default=str), json.dumps(after, default=str)))


def actor_from(request):
    name = request.headers.get('X-Operator-Name', 'Training Operator').strip()
    return name[:80] if name else 'Unspecified Operator'


def seq(conn, prefix, year=None):
    year = year or datetime.now(LOCAL_TZ).year
    key = f'{prefix}:{year}'
    conn.execute('INSERT OR IGNORE INTO sequences (key, last_number) VALUES (?,0)', (key,))
    conn.execute('UPDATE sequences SET last_number=last_number+1 WHERE key=?', (key,))
    n = conn.execute('SELECT last_number FROM sequences WHERE key=?', (key,)).fetchone()[0]
    return f'{prefix}-{year}-{n:05d}'


def invoice_due_date(issue_date, payment_terms):
    """Compute a suggested due date from the customer's recorded payment terms."""
    match = re.search(r'(\d+)\s+days?', payment_terms or '', re.I)
    days = min(int(match.group(1)), 365) if match else 0
    return (date.fromisoformat(issue_date) + timedelta(days=days)).isoformat()


def error(message, status=422):
    raise HTTPException(status_code=status, detail=message)


def valid_entity(table):
    if table not in TABLE_FIELDS:
        error('Unknown record type', 404)


def clean(table, body, partial=False):
    valid_entity(table)
    if not isinstance(body, dict):
        error('Please submit a valid record')
    allowed = set(TABLE_FIELDS[table])
    unknown = set(body) - allowed
    if unknown:
        error(f'Unsupported fields: {", ".join(sorted(unknown))}')
    if table == 'invoices':
        if 'status' in body and body['status'] not in ('Draft', 'Issued', 'Paid'):
            error('Invalid invoice status')
    data = {}
    for key, value in body.items():
        if isinstance(value, str):
            value = value.strip()
        if value == '' and key not in ('name', 'code', 'registration'):
            value = None
        if key in INTEGER_IDS:
            if value is not None:
                try:
                    value = int(value)
                    if value <= 0: raise ValueError()
                except (ValueError, TypeError):
                    error(f'{key} must be a valid record')
        elif key in NUMERIC:
            if value is not None:
                try:
                    value = float(value)
                    if value < 0 or value > 1_000_000_000: raise ValueError()
                except (ValueError, TypeError):
                    error(f'{key} must be a nonnegative number')
        elif value is not None and not isinstance(value, str):
            error(f'{key} must be text')
        if isinstance(value, str) and len(value) > (4000 if key == 'notes' else 500):
            error(f'{key} is too long')
        data[key] = value
    if not partial:
        for required in REQUIRED.get(table, []):
            if data.get(required) is None or data.get(required) == '':
                error(f'Missing required field: {required.replace("_", " ")}')
    status_allowed = {
        'customers': {'Active', 'Inactive'}, 'trucks': {'Available', 'In service', 'Out of service'},
        'drivers': {'Available', 'On leave', 'Off duty'},
        'maintenance': {'Completed', 'Scheduled', 'In progress'},
    }
    if table in status_allowed and 'status' in data and data['status'] is not None and data['status'] not in status_allowed[table]:
        error('Invalid status value')
    if table == 'loads':
        if not partial or 'planned_start' in data or 'planned_end' in data:
            pass  # Fully checked with prior values in validate_record().
        if 'unit' in data and data['unit'] not in ('tons', 'cubic yards', 'loads'):
            error('Load unit must be tons, cubic yards or loads')
    if table == 'inspections':
        for field in ('brakes', 'tyres', 'lights', 'hydraulics'):
            if field in data and data[field] not in ('Pass', 'Fail'):
                error(f'{field} must be Pass or Fail')
    for field in ['planned_start', 'planned_end']:
        if field in data and data[field]:
            try: datetime.fromisoformat(data[field])
            except ValueError: error('Invalid schedule date/time')
    if table == 'daily_logs':
        for field in ('start_time','end_time'):
            if field in data and data[field] and len(data[field]) not in (5, 8):
                error('Time must be in HH:MM format')
    return data


def validate_record(conn, table, data, existing_id=None):
    if table == 'loads':
        if data['planned_end'] <= data['planned_start']:
            error('Scheduled end must be after the start time')
        if data['qty'] <= 0:
            error('Load quantity must be greater than zero')
        truck = row(conn, 'trucks', data['truck_id'])
        driver = row(conn, 'drivers', data['driver_id'])
        customer = row(conn, 'customers', data['customer_id'])
        if not truck or not driver or not customer: error('Select a valid customer, truck and driver')
        if truck['status'] != 'Available': error('That truck is not marked Available')
        if driver['status'] != 'Available': error('That driver is not marked Available')
        if customer['status'] != 'Active': error('That customer is inactive')
        planned_date = data['planned_start'][:10]
        if driver.get('licence_expiry') and driver['licence_expiry'] < planned_date:
            error(f"{driver['name']}'s licence expires before this dispatch")
        if truck.get('insurance_due') and truck['insurance_due'] < planned_date:
            error(f"{truck['code']}'s insurance expires before this dispatch")
        if data['unit'] == 'tons' and truck.get('capacity') and data['qty'] > truck['capacity']:
            error(f"Quantity exceeds {truck['code']}'s {truck['capacity']:g}-ton rated capacity")
        conflict = conn.execute('''SELECT reference FROM loads WHERE id != ? AND status IN ('Scheduled','In progress')
           AND (truck_id=? OR driver_id=?) AND planned_start < ? AND planned_end > ? LIMIT 1''',
           (existing_id or 0, data['truck_id'], data['driver_id'], data['planned_end'], data['planned_start'])).fetchone()
        if conflict:
            error(f'Scheduling conflict with {conflict[0]}. Choose another truck, driver or time.')
    if table == 'daily_logs':
        if data['odo_end'] < data['odo_start']: error('End odometer cannot be lower than start odometer')
        if data['end_time'] <= data['start_time']: error('End time must be after start time')
        if data.get('load_id'):
            linked = row(conn,'loads',data['load_id'])
            if not linked: error('The linked load does not exist')
            if linked['truck_id'] != data['truck_id'] or linked['driver_id'] != data['driver_id']:
                error('The daily log truck and driver must match the linked load')
            if data.get('customer_id') and linked['customer_id'] != data['customer_id']:
                error('The daily log customer must match the linked load')
    if table == 'fuel' and (data['litres'] <= 0 or data['unit_price'] <= 0):
        error('Fuel litres and unit price must be greater than zero')


def initialise():
    c = dbconnect()
    c.executescript('''
    PRAGMA journal_mode=WAL;
    CREATE TABLE IF NOT EXISTS sequences (key TEXT PRIMARY KEY, last_number INTEGER NOT NULL);
    CREATE TABLE IF NOT EXISTS customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE, contact TEXT, phone TEXT, email TEXT, address TEXT, payment_terms TEXT DEFAULT '30 days', status TEXT DEFAULT 'Active', notes TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL);
    CREATE TABLE IF NOT EXISTS trucks (id INTEGER PRIMARY KEY, code TEXT NOT NULL UNIQUE, registration TEXT NOT NULL UNIQUE, make_model TEXT, capacity REAL DEFAULT 20, odometer REAL DEFAULT 0, status TEXT DEFAULT 'Available', insurance_due TEXT, service_due_date TEXT, service_due_km REAL, notes TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL);
    CREATE TABLE IF NOT EXISTS drivers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, phone TEXT, licence TEXT, licence_expiry TEXT, status TEXT DEFAULT 'Available', notes TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL);
    CREATE TABLE IF NOT EXISTS loads (id INTEGER PRIMARY KEY, reference TEXT NOT NULL UNIQUE, customer_id INTEGER NOT NULL REFERENCES customers(id), truck_id INTEGER NOT NULL REFERENCES trucks(id), driver_id INTEGER NOT NULL REFERENCES drivers(id), pickup TEXT NOT NULL, destination TEXT NOT NULL, material TEXT NOT NULL, planned_start TEXT NOT NULL, planned_end TEXT NOT NULL, qty REAL NOT NULL, unit TEXT NOT NULL DEFAULT 'tons', rate REAL NOT NULL DEFAULT 0, status TEXT NOT NULL DEFAULT 'Scheduled', actual_qty REAL, received_by TEXT, ticket_number TEXT, delivered_at TEXT, invoice_id INTEGER, notes TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL);
    CREATE TABLE IF NOT EXISTS daily_logs (id INTEGER PRIMARY KEY, driver_id INTEGER NOT NULL REFERENCES drivers(id), truck_id INTEGER NOT NULL REFERENCES trucks(id), log_date TEXT NOT NULL, start_time TEXT NOT NULL, end_time TEXT NOT NULL, odo_start REAL NOT NULL, odo_end REAL NOT NULL, customer_id INTEGER REFERENCES customers(id), load_id INTEGER REFERENCES loads(id), destination TEXT, material TEXT, quantity REAL DEFAULT 0, fuel_uplift REAL DEFAULT 0, notes TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL);
    CREATE TABLE IF NOT EXISTS fuel (id INTEGER PRIMARY KEY, truck_id INTEGER NOT NULL REFERENCES trucks(id), driver_id INTEGER REFERENCES drivers(id), purchase_date TEXT NOT NULL, vendor TEXT NOT NULL, litres REAL NOT NULL, unit_price REAL NOT NULL, amount REAL NOT NULL, odometer REAL, notes TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL);
    CREATE TABLE IF NOT EXISTS maintenance (id INTEGER PRIMARY KEY, truck_id INTEGER NOT NULL REFERENCES trucks(id), service_date TEXT NOT NULL, service_type TEXT NOT NULL, supplier TEXT, amount REAL NOT NULL DEFAULT 0, next_due_date TEXT, next_due_km REAL, status TEXT NOT NULL DEFAULT 'Completed', notes TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL);
    CREATE TABLE IF NOT EXISTS expenses (id INTEGER PRIMARY KEY, truck_id INTEGER NOT NULL REFERENCES trucks(id), expense_date TEXT NOT NULL, category TEXT NOT NULL, supplier TEXT, amount REAL NOT NULL, notes TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL);
    CREATE TABLE IF NOT EXISTS inspections (id INTEGER PRIMARY KEY, truck_id INTEGER NOT NULL REFERENCES trucks(id), driver_id INTEGER NOT NULL REFERENCES drivers(id), inspection_date TEXT NOT NULL, odometer REAL, brakes TEXT NOT NULL, tyres TEXT NOT NULL, lights TEXT NOT NULL, hydraulics TEXT NOT NULL, overall TEXT NOT NULL, notes TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL);
    CREATE TABLE IF NOT EXISTS invoices (id INTEGER PRIMARY KEY, number TEXT UNIQUE NOT NULL, load_id INTEGER NOT NULL UNIQUE REFERENCES loads(id), customer_id INTEGER NOT NULL REFERENCES customers(id), amount REAL NOT NULL, status TEXT NOT NULL DEFAULT 'Draft', issued_at TEXT, due_at TEXT, paid_at TEXT, notes TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL);
    CREATE TABLE IF NOT EXISTS attachments (id INTEGER PRIMARY KEY, entity TEXT NOT NULL, entity_id INTEGER NOT NULL, original_name TEXT, stored_name TEXT NOT NULL, mime TEXT NOT NULL, sha256 TEXT NOT NULL, created_by TEXT NOT NULL, created_at TEXT NOT NULL);
    CREATE TABLE IF NOT EXISTS audit (id INTEGER PRIMARY KEY, created_at TEXT NOT NULL, actor TEXT NOT NULL, action TEXT NOT NULL, entity TEXT NOT NULL, entity_id INTEGER NOT NULL, reference TEXT, before_json TEXT, after_json TEXT);
    CREATE INDEX IF NOT EXISTS idx_loads_schedule ON loads (planned_start,planned_end);
    CREATE INDEX IF NOT EXISTS idx_loads_ref ON loads(reference);
    CREATE INDEX IF NOT EXISTS idx_audit_entity ON audit(entity,entity_id);
    CREATE INDEX IF NOT EXISTS idx_photos_entity ON attachments(entity,entity_id);
    ''')
    c.commit()
    if MODE == 'training' and c.execute('SELECT COUNT(*) FROM customers').fetchone()[0] == 0:
        seed_demo(c)
        c.commit()
    c.close()


def seed_demo(c):
    now = now_iso()
    d = local_date()
    entities = [
        ('customers', ('name','contact','phone','email','address','payment_terms','status'), [
            ('SAMPLE: Grand Bahama Builders','Project Office','242-555-0111','sample1@example.invalid','Freeport, Grand Bahama','30 days','Active'),
            ('SAMPLE: Island Aggregate Supply','Dispatch Desk','242-555-0112','sample2@example.invalid','Hawksbill, Grand Bahama','15 days','Active'),
            ('SAMPLE: Bahamia Civil Works','Site Supervisor','242-555-0113','sample3@example.invalid','South Bahamia','30 days','Active')]),
        ('trucks', ('code','registration','make_model','capacity','odometer','status','service_due_date','insurance_due'), [
            ('HT-01','SAMPLE-01','Mack tandem dump truck',20,58120,'Available',local_date(24),local_date(60)),
            ('HT-02','SAMPLE-02','Kenworth dump truck',20,74205,'Available',local_date(8),local_date(45))]),
        ('drivers', ('name','phone','licence','licence_expiry','status'), [
            ('Sample Driver A','242-555-0141','DEMO-DL-001',local_date(180),'Available'),
            ('Sample Driver B','242-555-0142','DEMO-DL-002',local_date(210),'Available')])]
    for table, cols, items in entities:
        keys = ','.join(cols)
        q = ','.join(['?']*(len(cols)+2))
        for vals in items:
            c.execute(f'INSERT INTO {table} ({keys},created_at,updated_at) VALUES ({q})', (*vals,now,now))
    # A completed sample demonstrates customer > load > proof > invoice links.
    ref = seq(c,'HW')
    c.execute('''INSERT INTO loads (reference,customer_id,truck_id,driver_id,pickup,destination,material,planned_start,planned_end,qty,unit,rate,status,actual_qty,received_by,ticket_number,delivered_at,notes,created_at,updated_at) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)''',
        (ref,1,1,1,'Freeport Quarry','South Bahamia Site','Crushed limestone',f'{local_date(-1)}T09:00',f'{local_date(-1)}T11:00',16,'tons',46,'Delivered',16,'Sample Site Representative','SAMPLE-TKT-001',f'{local_date(-1)}T10:45','Sample record with fictional delivery ticket photograph',now,now))
    load_id = c.execute('SELECT last_insert_rowid()').fetchone()[0]
    inv_no = seq(c,'INV')
    c.execute('INSERT INTO invoices (number,load_id,customer_id,amount,status,issued_at,due_at,notes,created_at,updated_at) VALUES (?,?,?,?,?,?,?,?,?,?)',
              (inv_no,load_id,1,736,'Issued',local_date(-1),local_date(29),'SAMPLE invoice',now,now))
    invoice_id=c.execute('SELECT last_insert_rowid()').fetchone()[0]
    c.execute('UPDATE loads SET invoice_id=? WHERE id=?',(invoice_id,load_id))
    # Fictional proof helps trainees see the full record > photo > invoice chain.
    ticket_image = Image.new('RGB',(1100,680),'white')
    draw = ImageDraw.Draw(ticket_image)
    draw.rectangle((20,20,1080,660),outline='#0d3c70',width=8)
    draw.rectangle((25,25,1075,130),fill='#0d3c70')
    draw.text((65,61),'HAULWRIGHT  /  SAMPLE DELIVERY TICKET',fill='white',stroke_width=1)
    draw.text((66,175),'TRAINING SAMPLE - NOT A REAL RECEIPT',fill='#ac2434',stroke_width=1)
    for idx,line in enumerate([
        f'Load: {ref}     Ticket: SAMPLE-TKT-001',
        'Customer: SAMPLE: Grand Bahama Builders',
        'Truck: HT-01     Driver: Sample Driver A',
        'Destination: South Bahamia Site',
        'Delivered: 16 tons of crushed limestone',
        'Received by: Sample Site Representative',
    ]):
        draw.text((66,255+idx*47),line,fill='#16314a')
    image_output=io.BytesIO();ticket_image.save(image_output,'JPEG',quality=88)
    stored=uuid.uuid4().hex+'.jpg';ticket_data=image_output.getvalue()
    (UPLOADS/stored).write_bytes(ticket_data)
    c.execute('''INSERT INTO attachments(entity,entity_id,original_name,stored_name,mime,sha256,created_by,created_at)
        VALUES(?,?,?,?,?,?,?,?)''',('loads',load_id,'SAMPLE_delivery_ticket.jpg',stored,'image/jpeg',hashlib.sha256(ticket_data).hexdigest(),'System demo seed',now))
    scheduled = [(2,2,2,'Island Aggregate Yard','Freeport Industrial Park','Construction sand',12,38,'09:00','10:30'),
                 (3,1,1,'Port Logistics Yard','Hawksbill','Clean fill',14,42,'13:00','15:00')]
    for customer_id,truck_id,driver_id,orig,dest,mat,qty,rate,start,end in scheduled:
        c.execute('''INSERT INTO loads(reference,customer_id,truck_id,driver_id,pickup,destination,material,planned_start,planned_end,qty,unit,rate,status,notes,created_at,updated_at) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)''',
                  (seq(c,'HW'),customer_id,truck_id,driver_id,orig,dest,mat,f'{d}T{start}',f'{d}T{end}',qty,'tons',rate,'Scheduled','Sample scheduled load',now,now))
    c.execute('INSERT INTO fuel (truck_id,driver_id,purchase_date,vendor,litres,unit_price,amount,odometer,notes,created_at,updated_at) VALUES (?,?,?,?,?,?,?,?,?,?,?)',
              (1,1,local_date(-2),'SAMPLE: Freeport Fuel Stop',105,6.10,640.5,58020,'Sample diesel purchase in BSD',now,now))
    c.execute('INSERT INTO fuel (truck_id,driver_id,purchase_date,vendor,litres,unit_price,amount,odometer,notes,created_at,updated_at) VALUES (?,?,?,?,?,?,?,?,?,?,?)',
              (2,2,local_date(-3),'SAMPLE: Port Fuel Depot',112,6.15,688.8,74010,'Sample diesel purchase in BSD',now,now))
    c.execute('INSERT INTO maintenance (truck_id,service_date,service_type,supplier,amount,next_due_date,next_due_km,status,notes,created_at,updated_at) VALUES (?,?,?,?,?,?,?,?,?,?,?)',
              (1,local_date(-10),'Oil and filter service','SAMPLE: Service Contractor',325,local_date(24),60100,'Completed','Sample maintenance record',now,now))
    c.execute('INSERT INTO expenses (truck_id,expense_date,category,supplier,amount,notes,created_at,updated_at) VALUES (?,?,?,?,?,?,?,?)',
              (2,local_date(-6),'Tyres','SAMPLE: Tyre Supplier',245,'Sample tyre expense',now,now))
    c.execute('INSERT INTO daily_logs (driver_id,truck_id,log_date,start_time,end_time,odo_start,odo_end,customer_id,load_id,destination,material,quantity,fuel_uplift,notes,created_at,updated_at) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)',
              (1,1,local_date(-1),'08:00','16:00',58058,58120,1,load_id,'South Bahamia Site','Crushed limestone',16,0,'Sample daily driver log',now,now))
    audit(c,'System demo seed','CREATE SAMPLE DATA','system',0,'SAMPLE',None,{'mode':'training','description':'Example records only'})


initialise()


@app.middleware('http')
async def security_headers(request: Request, call_next):
    res = await call_next(request)
    res.headers['X-Content-Type-Options'] = 'nosniff'
    res.headers['Referrer-Policy'] = 'same-origin'
    res.headers['X-Frame-Options'] = 'DENY'
    res.headers['Cache-Control'] = 'no-store' if request.url.path.startswith('/api') else 'public, max-age=1800'
    return res


@app.get('/api/health')
def health():
    return {'ok':True,'mode':MODE,'database':DB.exists()}


@app.get('/api/bootstrap')
def bootstrap():
    c=dbconnect()
    try:
        result={}
        for t in TABLES + ['attachments','audit']:
            if t=='audit':
                items = c.execute('SELECT * FROM audit ORDER BY id DESC LIMIT 500').fetchall()
            else:
                items = c.execute(f'SELECT * FROM {t} ORDER BY id DESC').fetchall()
            result[t] = [dict(r) for r in items]
        result['mode'] = MODE
        result['currency'] = 'BSD'
        result['server_time'] = now_iso()
        result['demo_notice'] = 'Demonstration records are identified as SAMPLE' if MODE == 'training' else ''
        return result
    finally:
        c.close()


@app.post('/api/records/{table}')
async def add_record(table: str, request: Request):
    valid_entity(table)
    if table == 'invoices': error('Invoices are automatically issued when loads are completed')
    raw = await request.json()
    body=clean(table,raw)
    if table in ('customers','trucks','drivers'):
        body.setdefault('status','Active' if table=='customers' else 'Available')
    if table == 'loads': body.setdefault('unit','tons')
    if table == 'maintenance': body.setdefault('status','Completed')
    if table == 'fuel': body['amount'] = round(body['litres']*body['unit_price'],2)
    if table == 'inspections': body['overall'] = 'Fail' if any(body[k]=='Fail' for k in ('brakes','tyres','lights','hydraulics')) else 'Pass'
    body['created_at']=now_iso(); body['updated_at']=now_iso()
    try:
        with transaction() as c:
            validate_record(c,table,body)
            if table=='loads': body['reference']=seq(c,'HW'); body['status']='Scheduled'
            columns=list(body)
            fields=','.join(columns); placeholders=','.join(['?']*len(columns))
            cursor=c.execute(f'INSERT INTO {table} ({fields}) VALUES ({placeholders})',[body[k] for k in columns])
            created=row(c,table,cursor.lastrowid)
            audit(c,actor_from(request),'CREATE',table,created['id'],created.get('reference') or created.get('code') or '',None,created)
            if table=='inspections' and body['overall']=='Fail':
                previous=row(c,'trucks',body['truck_id'])
                c.execute('UPDATE trucks SET status=?,updated_at=? WHERE id=?',('Out of service',now_iso(),body['truck_id']))
                audit(c,actor_from(request),'SAFETY HOLD','trucks',body['truck_id'],previous['code'],previous,row(c,'trucks',body['truck_id']))
            if table in ('daily_logs','fuel','inspections'):
                maybe_update_odometer(c,table,created,actor_from(request))
            if table == 'maintenance':
                maybe_update_service_due(c,created,actor_from(request))
            return created
    except sqlite3.IntegrityError as exc:
        error('Record could not be saved. Check unique identifiers and related records.')


def maybe_update_odometer(c,table,rec,actor):
    km = rec.get('odo_end') if table=='daily_logs' else rec.get('odometer')
    if km is None: return
    prior=row(c,'trucks',rec['truck_id'])
    if prior and km > (prior['odometer'] or 0):
        c.execute('UPDATE trucks SET odometer=?,updated_at=? WHERE id=?',(km,now_iso(),rec['truck_id']))
        audit(c,actor,'ODOMETER UPDATE','trucks',rec['truck_id'],prior['code'],prior,row(c,'trucks',rec['truck_id']))


def maybe_update_service_due(c,maintenance,actor):
    if maintenance.get('status') != 'Completed':
        return
    changes={}
    if maintenance.get('next_due_date'):
        changes['service_due_date']=maintenance['next_due_date']
    if maintenance.get('next_due_km') is not None:
        changes['service_due_km']=maintenance['next_due_km']
    prior=row(c,'trucks',maintenance['truck_id'])
    if not prior or not changes or all(prior.get(k)==v for k,v in changes.items()):
        return
    changes['updated_at']=now_iso()
    c.execute(f"UPDATE trucks SET {','.join(f'{k}=?' for k in changes)} WHERE id=?",list(changes.values())+[prior['id']])
    audit(c,actor,'UPDATE SERVICE SCHEDULE','trucks',prior['id'],prior['code'],prior,row(c,'trucks',prior['id']))


@app.patch('/api/records/{table}/{identifier}')
async def update_record(table:str, identifier:int, request:Request):
    valid_entity(table)
    raw=await request.json()
    patch=clean(table,raw,partial=True)
    if not patch:error('Nothing to update')
    try:
        with transaction() as c:
            before=row(c,table,identifier)
            if not before:error('Record not found',404)
            if table=='loads' and before['status']!='Scheduled':error('Only scheduled loads can be edited. Completed records are retained for audit.')
            if table=='invoices':
                if 'status' in patch:
                    old=before['status']; new=patch['status']
                    permitted={'Draft':{'Draft','Issued','Paid'},'Issued':{'Issued','Paid'},'Paid':{'Paid'}}
                    if new not in permitted[old]:error('Paid invoices cannot be reopened, and issued invoices cannot become draft')
                    if new in ('Issued','Paid'):
                        issue = before['issued_at'] or local_date()
                        if not before['issued_at']: patch['issued_at']=issue
                        if not before['due_at']:
                            cust = row(c,'customers',before['customer_id'])
                            patch['due_at']=invoice_due_date(issue,cust.get('payment_terms') if cust else None)
                    if new=='Paid' and old!='Paid':patch['paid_at']=local_date()
            if table=='fuel':
                merged={**before, **patch}
                patch['amount']=round((merged['litres'] or 0)*(merged['unit_price'] or 0),2)
            if table=='inspections':error('Safety inspections cannot be edited. Record a new inspection instead.')
            if table=='daily_logs':
                merged={**before,**patch};validate_record(c,table,merged,identifier)
            if table=='loads':
                merged={**before,**patch};validate_record(c,table,merged,identifier)
            fields=list(patch); patch['updated_at']=now_iso();fields.append('updated_at')
            sql=','.join(f'{f}=?' for f in fields)
            c.execute(f'UPDATE {table} SET {sql} WHERE id=?',[patch[f] for f in fields]+[identifier])
            after=row(c,table,identifier)
            audit(c,actor_from(request),'UPDATE',table,identifier,after.get('reference') or after.get('number') or after.get('code') or '',before,after)
            if table in ('daily_logs','fuel'):
                maybe_update_odometer(c,table,after,actor_from(request))
            if table == 'maintenance':
                maybe_update_service_due(c,after,actor_from(request))
            return after
    except sqlite3.IntegrityError:
        error('Update would duplicate a unique field or invalidate a linked record')


@app.post('/api/loads/{identifier}/action')
async def load_action(identifier:int, request:Request):
    body=await request.json(); action=body.get('action','')
    if action not in ('start','cancel'):error('Invalid load action')
    with transaction() as c:
        before=row(c,'loads',identifier)
        if not before:error('Load not found',404)
        if before['status']!='Scheduled' and not (action=='cancel' and before['status']=='In progress'):
            error('This load can no longer be changed to that status')
        status='In progress' if action=='start' else 'Cancelled'
        if action=='start':
            merged=dict(before);validate_record(c,'loads',merged,identifier)
        c.execute('UPDATE loads SET status=?,updated_at=? WHERE id=?',(status,now_iso(),identifier))
        after=row(c,'loads',identifier)
        audit(c,actor_from(request),action.upper(),'loads',identifier,after['reference'],before,after)
        return after


@app.post('/api/loads/{identifier}/deliver')
async def deliver_load(identifier:int, request:Request):
    body=await request.json()
    received=(body.get('received_by') or '').strip()
    ticket=(body.get('ticket_number') or '').strip()
    try: qty=float(body.get('actual_qty',''))
    except (TypeError,ValueError):error('Delivered quantity must be a valid number')
    if qty<=0 or qty>1_000_000:error('Delivered quantity must be greater than zero')
    if not received or not ticket:error('Delivery recipient and ticket number are required')
    with transaction() as c:
        before=row(c,'loads',identifier)
        if not before:error('Load not found',404)
        if before['status'] not in ('Scheduled','In progress'):error('Load already delivered or cancelled')
        photo=c.execute("SELECT COUNT(*) FROM attachments WHERE entity='loads' AND entity_id=?",(identifier,)).fetchone()[0]
        if not photo:error('Attach a delivery ticket photograph before completing this load')
        truck=row(c,'trucks',before['truck_id'])
        if before['unit']=='tons' and truck['capacity'] and qty>truck['capacity']:
            error('Delivered quantity exceeds truck rated tonnage')
        delivered_at=body.get('delivered_at') or datetime.now(LOCAL_TZ).replace(tzinfo=None).isoformat(timespec='minutes')
        try: datetime.fromisoformat(delivered_at)
        except ValueError:error('Invalid delivery time')
        amount=round(qty*before['rate'],2)
        inv=seq(c,'INV')
        c.execute('''INSERT INTO invoices(number,load_id,customer_id,amount,status,notes,created_at,updated_at)
                    VALUES (?,?,?,?,?,?,?,?)''',(inv,identifier,before['customer_id'],amount,'Draft',f'Generated from {before["reference"]}',now_iso(),now_iso()))
        invoice_id=c.execute('SELECT last_insert_rowid()').fetchone()[0]
        c.execute('''UPDATE loads SET status='Delivered',actual_qty=?,received_by=?,ticket_number=?,delivered_at=?,invoice_id=?,updated_at=? WHERE id=?''',
                  (qty,received[:250],ticket[:120],delivered_at,invoice_id,now_iso(),identifier))
        after=row(c,'loads',identifier)
        audit(c,actor_from(request),'DELIVER','loads',identifier,after['reference'],before,after)
        audit(c,actor_from(request),'ISSUE DRAFT','invoices',invoice_id,inv,None,row(c,'invoices',invoice_id))
        return {'load':after,'invoice':row(c,'invoices',invoice_id)}


@app.post('/api/attachments')
async def upload_attachment(request:Request):
    body=await request.json()
    entity=body.get('entity')
    identifier=body.get('entity_id')
    if entity not in ('loads','fuel','daily_logs','maintenance','expenses','inspections'):
        error('This record type cannot accept photographs')
    try: identifier=int(identifier)
    except (ValueError,TypeError):error('Invalid photo record ID')
    encoded=body.get('data') or ''
    if not isinstance(encoded,str) or len(encoded)>15_000_000:error('Photo is missing or too large (maximum 8 MB original)')
    if encoded.startswith('data:'):
        encoded=encoded.split(',',1)[-1]
    try: data=base64.b64decode(encoded,validate=True)
    except Exception:error('Invalid image upload')
    if len(data)>8_000_000:error('Please choose a photograph smaller than 8 MB')
    try:
        with Image.open(io.BytesIO(data)) as source:
            if source.format not in ('JPEG','PNG','WEBP'):error('Only JPEG, PNG and WebP photographs are supported')
            image=ImageOps.exif_transpose(source)
            image.thumbnail((2400,2400),Image.Resampling.LANCZOS)
            if image.mode=='RGBA':
                bg=Image.new('RGB',image.size,'white'); bg.paste(image,mask=image.getchannel('A')); image=bg
            else:image=image.convert('RGB')
            out=io.BytesIO();image.save(out,'JPEG',quality=84,optimize=True)
            result_bytes=out.getvalue()
    except (UnidentifiedImageError, OSError, ValueError):
        error('The image could not be read. Try a JPEG or PNG photograph')
    stored=uuid.uuid4().hex+'.jpg'
    dest=UPLOADS/stored
    try:
        with transaction() as c:
            before=row(c,entity,identifier)
            if not before:error('Record not found',404)
            if entity=='loads' and before['status'] in ('Cancelled',):error('Cannot attach to a cancelled load')
            dest.write_bytes(result_bytes)
            cur=c.execute('''INSERT INTO attachments(entity,entity_id,original_name,stored_name,mime,sha256,created_by,created_at)
                         VALUES(?,?,?,?,?,?,?,?)''',(entity,identifier,str(body.get('filename') or 'photo.jpg')[:180],stored,'image/jpeg',hashlib.sha256(result_bytes).hexdigest(),actor_from(request),now_iso()))
            uploaded=row(c,'attachments',cur.lastrowid)
            audit(c,actor_from(request),'ATTACH PHOTO',entity,identifier,before.get('reference') or before.get('code') or '',None,{'attachment_id':cur.lastrowid,'sha256':uploaded['sha256'],'original_name':uploaded['original_name']})
            return uploaded
    except Exception:
        dest.unlink(missing_ok=True)
        raise


@app.get('/api/attachments/{identifier}/image')
def attachment(identifier:int):
    c=dbconnect()
    try: result=row(c,'attachments',identifier)
    finally:c.close()
    if not result:error('Photograph not found',404)
    path=UPLOADS/result['stored_name']
    if not path.is_file():error('Photo file is unavailable',404)
    return FileResponse(path, media_type='image/jpeg',headers={'Cache-Control':'private, no-store'})


@app.get('/api/export/{table}')
def csv_export(table:str):
    if table not in TABLES+['audit','attachments']:error('Invalid export type',404)
    c=dbconnect()
    try:
        records=[dict(x) for x in c.execute(f'SELECT * FROM {table} ORDER BY id DESC').fetchall()]
        fields=[str(x[1]) for x in c.execute(f'PRAGMA table_info({table})').fetchall()]
    finally:c.close()
    text=io.StringIO();w=csv.DictWriter(text,fieldnames=fields,extrasaction='ignore');w.writeheader()
    for item in records:
        # Prevent spreadsheet applications treating untrusted text as a formula.
        safe={k:('\''+v if isinstance(v,str) and v.startswith(('=','+','-','@')) else v) for k,v in item.items()}
        w.writerow(safe)
    return Response(content='\ufeff'+text.getvalue(),media_type='text/csv; charset=utf-8',headers={'Content-Disposition':f'attachment; filename="haulwright_{table}_{local_date()}.csv"'})


# Serve only the website directory; uploaded photos are served by an explicit API route.
app.mount('/', StaticFiles(directory=str(ROOT/'web'), html=True), name='site')

if __name__=='__main__':
    import uvicorn
    port=int(os.environ.get('PORT','8080'))
    uvicorn.run(app,host=os.environ.get('HOST','127.0.0.1'),port=port)