radionica3d/backend/routers/orders.py

import db
import schemas
import auth_utils
import notifications
import config
from typing import List, Optional
import json
import os
import uuid
import shutil
import hashlib
import preview_utils
import slicer_utils
from fastapi import APIRouter, Request, Form, Depends, HTTPException, BackgroundTasks, UploadFile, File
from services import pricing
from services import order_processing
from services import event_hooks
from services.audit_service import audit_service
from services.rate_limit_service import rate_limit_service
from dependencies import get_current_user, require_admin, get_current_user_optional
from pydantic import BaseModel
from datetime import datetime
import locales
from services.global_manager import global_manager

router = APIRouter(prefix="/orders", tags=["orders"])



@router.post("")
async def create_order(
    request: Request,
    background_tasks: BackgroundTasks,
    first_name: str = Form(...),
    last_name: str = Form(...),
    phone: str = Form(...),
    email: str = Form(...),
    shipping_address: str = Form(...),
    model_link: Optional[str] = Form(None),
    allow_portfolio: bool = Form(False),
    notes: Optional[str] = Form(None),
    material_id: int = Form(...),
    file_ids: str = Form("[]"),
    file_quantities: str = Form("[]"),
    quantity: int = Form(1),
    color_name: Optional[str] = Form(None),
    is_company: bool = Form(False),
    company_name: Optional[str] = Form(None),
    company_pib: Optional[str] = Form(None),
    company_address: Optional[str] = Form(None),
    user: Optional[dict] = Depends(get_current_user_optional)
):
    ip = request.client.host if request.client else "unknown"
    email_addr = email.lower()
    lang = request.query_params.get("lang", "en")

    is_admin = user.get("role") == "admin" if user else False
    if not is_admin and rate_limit_service.is_order_flooding(email_addr, ip):
        raise HTTPException(
            status_code=429, 
            detail=locales.translate_error("flood_control", lang)
        )

    user_id = user.get("id") if user else None

    parsed_ids = []
    parsed_quantities = []
    if file_ids:
        try:
            parsed_ids = json.loads(file_ids)
            parsed_quantities = json.loads(file_quantities)
        except:
            pass

    name_col = f"name_{lang}" if lang in ["en", "ru", "me"] else "name_en"
    # Fetch material info including technology-specific hourly price if available
    mat_query = """
        SELECT m.name_en, m.name_ru, m.name_ua, m.name_me, m.price_per_cm3, 
               COALESCE(NULLIF(m.price_per_hour, 0), s.price_per_hour, %s) as price_per_hour,
               m.speed_coeff
        FROM materials m
        LEFT JOIN services s ON m.service_id = s.id
        WHERE m.id = %s
    """
    mat_info = db.execute_query(mat_query, (config.DEFAULT_PRINTER_HOUR_PRICE, material_id))
    mat_name = mat_info[0][name_col] if mat_info else "Unknown"
    mat_price = mat_info[0]['price_per_cm3'] if mat_info else 0.0
    hour_price = mat_info[0]['price_per_hour'] if mat_info else config.DEFAULT_PRINTER_HOUR_PRICE
    speed_coeff = mat_info[0]['speed_coeff'] if mat_info else 1.0

    file_sizes = []
    if parsed_ids:
        format_strings = ','.join(['%s'] * len(parsed_ids))
        file_rows = db.execute_query(f"SELECT file_size FROM order_files WHERE id IN ({format_strings})", tuple(parsed_ids))
        file_sizes = [r['file_size'] for r in file_rows]

    estimated_price, item_prices, pricing_metadata = pricing.calculate_estimated_price(material_id, file_sizes, parsed_quantities if parsed_quantities else None, return_details=True)

    # Snapshoting initial parameters
    original_params = json.dumps({
        "material_name": mat_name,
        "material_price": float(mat_price) if mat_price is not None else 0.0,
        "printer_hour_price": float(pricing_metadata['price_per_hour']),
        "speed_coeff": float(pricing_metadata['speed_coeff']),
        "base_fee": float(pricing_metadata['base_fee']),
        "discount_percent": float(pricing_metadata['discount_percent']),
        "estimated_price": float(estimated_price) if estimated_price is not None else 0.0,
        "quantity": quantity,
        "color_name": color_name,
        "notes": notes,
        "first_name": first_name,
        "last_name": last_name,
        "phone": phone,
        "email": email,
        "shipping_address": shipping_address,
        "model_link": model_link,
        "is_company": is_company,
        "company_name": company_name,
        "company_pib": company_pib,
        "company_address": company_address
    })

    order_query = """
    INSERT INTO orders (user_id, material_id, first_name, last_name, phone, email, shipping_address, model_link, status, is_company, company_name, company_pib, company_address, allow_portfolio, estimated_price, material_name, material_price, printer_hour_price, speed_coeff, discount_percent, color_name, quantity, notes, original_params)
    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, 'pending', %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
    """
    order_params = (user_id, material_id, first_name, last_name, phone, email, shipping_address, model_link, is_company, company_name, company_pib, company_address, allow_portfolio, estimated_price, mat_name, mat_price, hour_price, speed_coeff, pricing_metadata['discount_percent'], color_name, quantity, notes, original_params)
    
    try:
        order_insert_id = db.execute_commit(order_query, order_params)
        if parsed_ids:
            for idx, f_id in enumerate(parsed_ids):
                qty = parsed_quantities[idx] if idx < len(parsed_quantities) else 1
                unit_p = item_prices[idx] if idx < len(item_prices) else 0.0
                db.execute_commit("UPDATE order_files SET order_id = %s, quantity = %s, unit_price = %s WHERE id = %s", (order_insert_id, qty, unit_p, f_id))
                
                # Create corresponding fixed order item
                file_row = db.execute_query("SELECT filename FROM order_files WHERE id = %s", (f_id,))
                fname = file_row[0]['filename'] if file_row else f"File #{f_id}"
                db.execute_commit(
                    "INSERT INTO order_items (order_id, description, quantity, unit_price, total_price) VALUES (%s, %s, %s, %s, %s)",
                    (order_insert_id, f"3D Print: {fname}", qty, unit_p, round(qty * unit_p, 2))
                )
        background_tasks.add_task(order_processing.process_order_slicing, order_insert_id)
        background_tasks.add_task(event_hooks.on_order_created, order_insert_id)
        
        # LOG ACTION
        await audit_service.log(
            user_id=user_id,
            action="order_created",
            target_type="order",
            target_id=order_insert_id,
            details={
                "email": email_addr,
                "mat_id": material_id,
                "price": float(estimated_price) if estimated_price is not None else 0.0,
                "quantity": quantity
            }
        )
        
        # Record placement for rate limiting
        rate_limit_service.record_order_placement(email_addr, ip)
        
        return {"status": "success", "order_id": order_insert_id, "message": "Order submitted successfully"}
    except Exception as e:
        print(f"Error creating order: {e}")
        raise HTTPException(status_code=500, detail="Internal server error occurred while processing order")

@router.get("/my")
async def get_my_orders(
    page: int = 1,
    size: int = 10,
    user: dict = Depends(get_current_user)
):
    user_id = user.get("id")
    offset = (page - 1) * size

    # Get total count
    count_query = "SELECT COUNT(*) as total FROM orders WHERE user_id = %s"
    count_res = db.execute_query(count_query, (user_id,))
    total = count_res[0]['total'] if count_res else 0

    query = """
    SELECT o.*, 
           (SELECT count(*) FROM order_messages om WHERE om.order_id = o.id AND om.is_from_admin = TRUE AND om.is_read = FALSE) as unread_count,
           GROUP_CONCAT(IF(f.id IS NOT NULL, JSON_OBJECT('id', f.id, 'filename', f.filename, 'file_path', f.file_path, 'quantity', f.quantity, 'preview_path', f.preview_path, 'print_time', f.print_time, 'filament_g', f.filament_g, 'dimensions', f.dimensions), NULL)) as files
    FROM orders o
    LEFT JOIN order_files f ON o.id = f.order_id
    WHERE o.user_id = %s
    GROUP BY o.id
    ORDER BY 
      CASE 
        WHEN status IN ('pending', 'processing', 'shipped') THEN 0
        ELSE 1
      END ASC,
      o.created_at DESC
    LIMIT %s OFFSET %s
    """
    results = db.execute_query(query, (user_id, size, offset))
    for row in results:
        if row['files']:
            try: row['files'] = json.loads(f"[{row['files']}]")
            except: row['files'] = []
        else: row['files'] = []
        row['items'] = db.execute_query("SELECT * FROM order_items WHERE order_id = %s", (row['id'],))
    return {"orders": results, "total": total}

@router.post("/{order_id}/review")
async def post_order_review(order_id: int, review: schemas.OrderReview, user: dict = Depends(get_current_user)):
    # Check if order belongs to user and is in appropriate status
    order = db.execute_query("SELECT id, status FROM orders WHERE id = %s AND user_id = %s", (order_id, user['id']))
    if not order:
        raise HTTPException(status_code=404, detail="Order not found or access denied")
    
    if order[0]['status'] not in ['shipped', 'completed']:
        raise HTTPException(status_code=400, detail="Reviews can only be posted for shipped or completed orders")
    
    db.execute_commit(
        "UPDATE orders SET review_text = %s, rating = %s, review_approved = FALSE WHERE id = %s",
        (review.review_text, review.rating, order_id)
    )
    
    # Create audit log
    await audit_service.log(user['id'], "ORDER_REVIEW", f"Posted review for order {order_id}", order_id)
    
    return {"message": "Review submitted successfully and is awaiting moderation"}

@router.patch("/{order_id}/review/approve")
async def approve_order_review(order_id: int, admin: dict = Depends(require_admin)):
    db.execute_commit("UPDATE orders SET review_approved = TRUE WHERE id = %s", (order_id,))
    await audit_service.log(admin['id'], "ORDER_REVIEW_APPROVE", f"Approved review for order {order_id}", order_id)
    return {"message": "Review approved successfully"}

@router.get("/reviews/public", response_model=List[schemas.PublicReview])
async def get_public_reviews():
    # Only return approved reviews, anonymized (strictly only the first word of the first name)
    query = "SELECT SUBSTRING_INDEX(first_name, ' ', 1) as first_name, rating, review_text FROM orders WHERE review_approved = TRUE ORDER BY created_at DESC LIMIT 10"
    return db.execute_query(query)


@router.post("/estimate")
async def get_price_estimate(data: schemas.EstimateRequest):
    estimated_total, file_prices, metadata = pricing.calculate_estimated_price(
        data.material_id, 
        data.file_sizes, 
        data.file_quantities, 
        return_details=True
    )
    
    qts = data.file_quantities if data.file_quantities else [1]*len(data.file_sizes)
    return {
        "file_prices": file_prices, 
        "total_estimate": estimated_total,
        "metadata": metadata
    }

# --- ADMIN ORDER ENDPOINTS ---

@router.get("/admin/list") # Using /admin/list to avoid conflict with /my
async def get_admin_orders(
    search: Optional[str] = None,
    status: Optional[str] = None,
    date_from: Optional[str] = None,
    date_to: Optional[str] = None,
    admin: dict = Depends(require_admin)
):
    
    where_clauses = []
    params = []

    if search:
        search_term = f"%{search}%"
        where_clauses.append("(o.id LIKE %s OR o.email LIKE %s OR o.first_name LIKE %s OR o.last_name LIKE %s OR o.company_name LIKE %s OR o.phone LIKE %s OR o.shipping_address LIKE %s)")
        params.extend([search_term] * 7)
    
    if status and status != 'all':
        where_clauses.append("o.status = %s")
        params.append(status)
    
    if date_from:
        where_clauses.append("o.created_at >= %s")
        params.append(date_from)
    
    if date_to:
        where_clauses.append("o.created_at <= %s")
        params.append(date_to)

    where_sql = f"WHERE {' AND '.join(where_clauses)}" if where_clauses else ""

    query = f"""
    SELECT o.*, u.can_chat, COALESCE(o.total_price, o.estimated_price) AS invoice_amount,
           (SELECT count(*) FROM order_messages om WHERE om.order_id = o.id AND om.is_from_admin = FALSE AND om.is_read = FALSE) as unread_count,
           GROUP_CONCAT(IF(f.id IS NOT NULL, JSON_OBJECT('id', f.id, 'filename', f.filename, 'file_path', f.file_path, 'file_size', f.file_size, 'quantity', f.quantity, 'preview_path', f.preview_path, 'print_time', f.print_time, 'filament_g', f.filament_g, 'dimensions', f.dimensions), NULL)) as files
    FROM orders o
    LEFT JOIN users u ON o.user_id = u.id
    LEFT JOIN order_files f ON o.id = f.order_id
    {where_sql}
    GROUP BY o.id
    ORDER BY o.created_at DESC
    """
    results = db.execute_query(query, tuple(params))
    import session_utils
    for row in results:
        row['is_online'] = session_utils.is_user_online(row['user_id']) if row.get('user_id') else False
        if row['files']:
            try: row['files'] = json.loads(f"[{row['files']}]")
            except: row['files'] = []
        else: row['files'] = []
        row['items'] = db.execute_query("SELECT * FROM order_items WHERE order_id = %s", (row['id'],))
        photos = db.execute_query("SELECT id, file_path, is_public FROM order_photos WHERE order_id = %s", (row['id'],))
        row['photos'] = photos
    return results

@router.patch("/{order_id}")
async def update_order(
    order_id: int,
    data: schemas.AdminOrderUpdate,
    background_tasks: BackgroundTasks,
    admin: dict = Depends(require_admin)
):
    print(f"DEBUG: update_order {order_id} - payload: {data.model_dump(exclude_unset=True)}")
    order_info = db.execute_query("SELECT * FROM orders WHERE id = %s", (order_id,))
    if not order_info: raise HTTPException(status_code=404, detail="Order not found")

    update_fields = []
    params = []
    if data.status:
        if order_info:
            background_tasks.add_task(
                event_hooks.on_order_status_changed, 
                order_id, 
                data.status, 
                order_info[0], 
                data.send_notification
            )
            
            # Generate Payment Documents based on status transitions
            from services.invoice_service import InvoiceService
            o = order_info[0]
            price_val = data.total_price if data.total_price is not None else (o.get('total_price') or o.get('estimated_price') or 0)
            price = float(price_val)
            
            # 1. Proforma / Payment Slip (on 'processing' or any initial active state)
            if data.status in ['processing', 'shipped']:
                try:
                    if o.get('is_company'):
                        pdf_path = InvoiceService.generate_document(o, doc_type="predracun", override_price=price)
                    else:
                        payer_name = f"{o['first_name']} {o.get('last_name', '')}".strip()
                        pdf_path = InvoiceService.generate_uplatnica(order_id, payer_name, o.get('shipping_address', ''), price)
                    
                    update_fields.append("proforma_path = %s")
                    params.append(pdf_path)
                except Exception as e:
                    print(f"Failed to generate proforma: {e}")

            # 2. Final Invoice (only on 'shipped')
            if data.status == 'shipped':
                try:
                    pdf_path = InvoiceService.generate_document(o, doc_type="faktura", override_price=price)
                    update_fields.append("invoice_path = %s")
                    params.append(pdf_path)
                except Exception as e:
                    print(f"Failed to generate final invoice: {e}")

    if data.status:
        update_fields.append("status = %s")
        params.append(data.status)
    if data.total_price is not None:
        update_fields.append("total_price = %s")
        params.append(data.total_price)
        # Sync estimated_price as well to ensure display consistency in all views
        update_fields.append("estimated_price = %s")
        params.append(data.total_price)
    
    if data.material_id is not None:
        update_fields.append("material_id = %s")
        params.append(data.material_id)
        # Also update snapshot names and prices from handbook
        mat_info = db.execute_query("SELECT name_en, price_per_cm3, price_per_hour FROM materials WHERE id = %s", (data.material_id,))
        if mat_info:
            update_fields.append("material_name = %s")
            params.append(mat_info[0]['name_en'])
            update_fields.append("material_price = %s")
            params.append(mat_info[0]['price_per_cm3'])
            update_fields.append("printer_hour_price = %s")
            params.append(mat_info[0]['price_per_hour'] if mat_info[0]['price_per_hour'] is not None else config.DEFAULT_PRINTER_HOUR_PRICE)
    elif data.material_name is not None:
        update_fields.append("material_name = %s")
        params.append(data.material_name)
    
    if data.color_name is not None:
        update_fields.append("color_name = %s")
        params.append(data.color_name)
    
    if data.quantity is not None:
        update_fields.append("quantity = %s")
        params.append(data.quantity)
    
    if data.fiscal_qr_url is not None:
        update_fields.append("fiscal_qr_url = %s")
        params.append(data.fiscal_qr_url)
        # Auto-set fiscalized_at if adding URL for the first time
        if order_info[0].get('fiscalized_at') is None:
            update_fields.append("fiscalized_at = %s")
            params.append(datetime.now())

    if data.ikof is not None:
        update_fields.append("ikof = %s")
        params.append(data.ikof)
    if data.jikr is not None:
        update_fields.append("jikr = %s")
        params.append(data.jikr)
        
    if data.first_name is not None:
        update_fields.append("first_name = %s")
        params.append(data.first_name)
    if data.last_name is not None:
        update_fields.append("last_name = %s")
        params.append(data.last_name)
    if data.email is not None:
        update_fields.append("email = %s")
        params.append(data.email)
    if data.phone is not None:
        update_fields.append("phone = %s")
        params.append(data.phone)
    if data.shipping_address is not None:
        update_fields.append("shipping_address = %s")
        params.append(data.shipping_address)
    if data.notes is not None:
        update_fields.append("notes = %s")
        params.append(data.notes)

    if data.review_text is not None:
        update_fields.append("review_text = %s")
        params.append(data.review_text)
    if data.rating is not None:
        update_fields.append("rating = %s")
        params.append(data.rating)
    if data.review_approved is not None:
        update_fields.append("review_approved = %s")
        params.append(data.review_approved)
    
    if update_fields:
        query = f"UPDATE orders SET {', '.join(update_fields)} WHERE id = %s"
        params.append(order_id)
        db.execute_commit(query, tuple(params))
        
        # LOG ACTION
        await audit_service.log(
            user_id=admin.get("id"),
            action="update_order",
            target_type="order",
            target_id=order_id,
            details={"updated_fields": {k.split(" = ")[0]: v for k, v in zip(update_fields, params)}}
        )
        
        # NOTIFY USER VIA WEBSOCKET
        await global_manager.notify_order_update(order_info[0]['user_id'], order_id)

    return {"id": order_id, "status": "updated"}

@router.post("/{order_id}/attach-file")
async def admin_attach_file(
    order_id: int,
    background_tasks: BackgroundTasks,
    file: UploadFile = File(...),
    admin: dict = Depends(require_admin)
):
        
    unique_filename = f"{uuid.uuid4()}{os.path.splitext(file.filename)[1]}"
    file_path = os.path.join(config.UPLOAD_DIR, unique_filename)
    db_file_path = f"uploads/{unique_filename}"
    
    sha256_hash = hashlib.sha256()
    with open(file_path, "wb") as buffer:
        while chunk := file.file.read(8192):
            sha256_hash.update(chunk)
            buffer.write(chunk)
    
    query = "INSERT INTO order_files (order_id, filename, file_path, file_size, quantity, file_hash, print_time, filament_g, preview_path) VALUES (%s, %s, %s, %s, 1, %s, NULL, NULL, NULL)"
    f_id = db.execute_commit(query, (order_id, file.filename, db_file_path, file.size, sha256_hash.hexdigest()))
    
    # Run heavy processing in background to avoid worker timeouts
    background_tasks.add_task(order_processing.process_single_file_async, f_id)
    
    # NOTIFY USER VIA WEBSOCKET
    order_info = db.execute_query("SELECT user_id FROM orders WHERE id = %s", (order_id,))
    if order_info:
        await global_manager.notify_order_update(order_info[0]['user_id'], order_id)

    return {"file_id": f_id, "filename": file.filename, "status": "processing"}

@router.delete("/{order_id}/files/{file_id}")
async def admin_delete_file(
    order_id: int,
    file_id: int,
    admin: dict = Depends(require_admin)
):
        
    file_record = db.execute_query("SELECT file_path, preview_path FROM order_files WHERE id = %s AND order_id = %s", (file_id, order_id))
    if not file_record:
        raise HTTPException(status_code=404, detail="File not found")
        
    base_dir = config.BASE_DIR
    try:
        if file_record[0]['file_path']:
            os.remove(os.path.join(base_dir, file_record[0]['file_path']))
        if file_record[0]['preview_path']:
            os.remove(os.path.join(base_dir, file_record[0]['preview_path']))
    except Exception as e:
        print(f"Error removing file from disk: {e}")
        
    db.execute_commit("DELETE FROM order_files WHERE id = %s AND order_id = %s", (file_id, order_id))
    
    # LOG ACTION
    await audit_service.log(
        user_id=admin.get("id"),
        action="delete_order_file",
        target_type="order",
        target_id=order_id,
        details={"file_id": file_id}
    )

    # NOTIFY USER VIA WEBSOCKET
    order_info = db.execute_query("SELECT user_id FROM orders WHERE id = %s", (order_id,))
    if order_info:
        await global_manager.notify_order_update(order_info[0]['user_id'], order_id)

    return {"status": "success"}

@router.patch("/{order_id}/files/{file_id}")
async def admin_update_file(
    order_id: int,
    file_id: int,
    data: dict,
    admin: dict = Depends(require_admin)
):
    if "quantity" in data:
        db.execute_commit("UPDATE order_files SET quantity = %s WHERE id = %s AND order_id = %s", (data['quantity'], file_id, order_id))
    
    # Notify user/admin via WS
    order_info = db.execute_query("SELECT user_id FROM orders WHERE id = %s", (order_id,))
    if order_info:
        await global_manager.notify_order_update(order_info[0]['user_id'], order_id)
        
    return {"status": "success"}

    
class OrderItemSchema(BaseModel):
    description: str
    quantity: int
    unit_price: float

@router.get("/{order_id}/items")
async def get_order_items(order_id: int):
    items = db.execute_query("SELECT * FROM order_items WHERE order_id = %s", (order_id,))
    return items

@router.put("/{order_id}/items")
async def update_order_items(order_id: int, items: List[OrderItemSchema], admin: dict = Depends(require_admin)):
    db.execute_commit("DELETE FROM order_items WHERE order_id = %s", (order_id,))
    
    total_order_price = 0
    items_summary = []
    for item in items:
        tot_p = round(item.quantity * item.unit_price, 2)
        total_order_price += tot_p
        db.execute_commit(
            "INSERT INTO order_items (order_id, description, quantity, unit_price, total_price) VALUES (%s, %s, %s, %s, %s)",
            (order_id, item.description, item.quantity, item.unit_price, tot_p)
        )
        items_summary.append(item.dict())
    
    # Sync main order total_price
    db.execute_commit("UPDATE orders SET total_price = %s WHERE id = %s", (total_order_price, order_id))
    
    # LOG ACTION
    await audit_service.log(
        user_id=admin.get("id"),
        action="update_order_items",
        target_type="order",
        target_id=order_id,
        details={"total_price": total_order_price, "items": items_summary}
    )

    # NOTIFY USER VIA WEBSOCKET
    order_info = db.execute_query("SELECT user_id FROM orders WHERE id = %s", (order_id,))
    if order_info:
        await global_manager.notify_order_update(order_info[0]['user_id'], order_id)

    return {"status": "success", "total_price": total_order_price}

@router.delete("/{order_id}/admin")
async def delete_order_admin(
    order_id: int,
    admin: dict = Depends(require_admin)
):
    # Fetch user_id before deletion to notify later
    order_info = db.execute_query("SELECT user_id FROM orders WHERE id = %s", (order_id,))
    if not order_info: raise HTTPException(status_code=404, detail="Order not found")
    customer_id = order_info[0]['user_id']
        
    # 1. Find all related files to delete from disk
    files = db.execute_query("SELECT file_path, preview_path FROM order_files WHERE order_id = %s", (order_id,))
    photos = db.execute_query("SELECT file_path FROM order_photos WHERE order_id = %s", (order_id,))
    
    base_dir = config.BASE_DIR
    
    # Delete order_files from disk
    for f in files:
        try:
            if f.get('file_path'):
                fpath = os.path.join(base_dir, f['file_path'])
                if os.path.exists(fpath): os.remove(fpath)
            if f.get('preview_path'):
                ppath = os.path.join(base_dir, f['preview_path'])
                if os.path.exists(ppath): os.remove(ppath)
        except Exception as e:
            print(f"Error deleting order file {f.get('file_path')}: {e}")

    # Delete photos from disk
    for p in photos:
        try:
            if p.get('file_path'):
                fpath = os.path.join(base_dir, p['file_path'])
                if os.path.exists(fpath): os.remove(fpath)
        except Exception as e:
            print(f"Error deleting order photo {p.get('file_path')}: {e}")
            
    # 2. Delete from DB tables (due to possible lack of CASCADE or to be explicit)
    try:
        db.execute_commit("DELETE FROM order_messages WHERE order_id = %s", (order_id,))
        db.execute_commit("DELETE FROM order_items WHERE order_id = %s", (order_id,))
        db.execute_commit("DELETE FROM order_files WHERE order_id = %s", (order_id,))
        db.execute_commit("DELETE FROM order_photos WHERE order_id = %s", (order_id,))
        db.execute_commit("DELETE FROM orders WHERE id = %s", (order_id,))
        
        # LOG ACTION
        await audit_service.log(
            user_id=admin.get("id"),
            action="delete_order_entirely",
            target_type="order",
            target_id=order_id,
            details={"order_id": order_id}
        )

        # NOTIFY USER VIA WEBSOCKET
        await global_manager.notify_order_update(customer_id, order_id)

        return {"status": "success", "message": f"Order {order_id} deleted entirely"}
    except Exception as e:
        print(f"Failed to delete order {order_id}: {e}")
        raise HTTPException(status_code=500, detail=str(e))