from fastapi import APIRouter, Depends, HTTPException, UploadFile, File, Form, Query
from sqlalchemy.orm import Session
from sqlalchemy.exc import IntegrityError
from sqlalchemy import or_, func, and_
from typing import List, Optional
from decimal import Decimal
from datetime import datetime
from io import BytesIO

from app.core.database import get_db
from app.core.security import require_any, require_permission_or_roles, enforce_branch_scope, require_branch_match
from app.core.dependencies import get_current_company_id
from app.models.inventory import Product, UnitOfMeasure, InventoryAdjustment, InventoryTransaction
from app.models.branch import Branch
from app.models.user import User
from app.schemas.inventory import ProductResponse, ProductCreate, ProductUpdate, InventoryAdjustmentCreate, InventoryAdjustmentResponse
from app.services.file_service import FileService
from app.services.inventory_service import InventoryService
from fastapi.responses import StreamingResponse
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Alignment, Font
from app.utils.product_stock import (
    is_inventory_tracked,
    apply_non_inventory_defaults,
    normalize_product_type,
    NON_INVENTORY_PRODUCT_TYPES,
)
from app.utils.logger import get_logger, log_exception, log_error_with_context

logger = get_logger(__name__)

# Inventory module: manager owns branch-level inventory management.
# Universal roles (super_admin, admin, accountant) pass implicitly via security.ALLOWED_EVERYTHING.
# POS users (cashier/pos_user) get read-only access to product listings & images; they cannot create/update/delete.
# Legacy 'staff' role removed from access matrix -> no longer granted implicit inventory access.
# Relaxed: allow cashier/pos_user/manager plus universal roles (handled in require_any)
router = APIRouter()  # Dependencies removed for development


@router.get("/units-of-measure")
async def get_units_of_measure(
    db: Session = Depends(get_db),
    company_id: Optional[str] = Depends(get_current_company_id),
):
    """Get all units of measure with conversion and base-unit metadata."""
    try:
        query = db.query(UnitOfMeasure)
        if company_id:
            branch_ids_sq = db.query(Branch.id).filter(Branch.company_id == company_id)
            query = query.filter(
                or_(UnitOfMeasure.branch_id.in_(branch_ids_sq), UnitOfMeasure.branch_id.is_(None))
            )
        units = query.all()
        result = []
        # Preload base units map for convenience
        by_id = {u.id: u for u in units}
        for unit in units:
            base = by_id.get(unit.base_unit_id) if unit.base_unit_id else None
            result.append({
                "id": unit.id,
                "name": unit.name,
                "abbreviation": unit.abbreviation,
                "description": unit.description,
                "is_base_unit": bool(unit.is_base_unit),
                "conversion_factor": float(unit.conversion_factor or 1.0),
                "base_unit_id": unit.base_unit_id,
                "base_unit": ({
                    "id": base.id,
                    "name": base.name,
                    "abbreviation": base.abbreviation,
                } if base else None)
            })
        return result
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Error fetching units of measure: {str(e)}")


@router.get("/products", response_model=List[ProductResponse])
async def get_products(
    db: Session = Depends(get_db),
    include_images: bool = Query(True, description="Include image URLs in response"),
    category: Optional[str] = Query(None, description="Filter by category"),
    active_only: bool = Query(True, description="Show only active products"),
    branch_id: Optional[str] = Query(None, description="Filter by branch assignment"),
    branch_inventory_only: bool = Query(False, description="Show only products with branch inventory (requires branch_id)"),
    search: Optional[str] = Query(None, description="Search name, SKU, or barcode"),
    product_type: Optional[str] = Query(None, description="Filter by product type"),
    stock_items_only: bool = Query(False, description="Exclude non-inventory service items"),
    limit: int = Query(500, ge=1, le=500, description="Maximum products to return"),
    company_id: Optional[str] = Depends(get_current_company_id),
):
    """Get products filtered by branch scope for non-global roles."""
    try:
        from app.models.inventory_allocation import BranchInventoryAllocation

        # If branch_inventory_only is requested, use branch inventory allocation
        if branch_inventory_only and branch_id:
            query = db.query(
                Product,
                BranchInventoryAllocation
            ).join(
                BranchInventoryAllocation,
                and_(
                    BranchInventoryAllocation.product_id == Product.id,
                    BranchInventoryAllocation.branch_id == branch_id,
                    BranchInventoryAllocation.allocation_status == 'received'
                )
            )

            if active_only:
                query = query.filter(Product.active == True)
            if category:
                query = query.filter(Product.category == category)
            if product_type:
                query = query.filter(func.lower(Product.product_type) == normalize_product_type(product_type))
            if stock_items_only:
                query = query.filter(
                    or_(
                        Product.product_type.is_(None),
                        ~func.lower(Product.product_type).in_(list(NON_INVENTORY_PRODUCT_TYPES)),
                    )
                )

            results = query.all()
            response_products = []

            for product, allocation in results:
                available_qty = allocation.received_quantity - (allocation.sold_quantity or 0) - (allocation.reserved_quantity or 0)

                # Only include products with available quantity
                if available_qty > 0:
                    product_data = {
                        "id": product.id,
                        "name": product.name,
                        "sku": product.sku,
                        "description": product.description,
                        "quantity": available_qty,  # Branch available quantity
                        "barcode": product.barcode,
                        "cost_price": product.cost_price or 0,
                        "selling_price": product.selling_price or 0,
                        "is_serialized": product.is_serialized or False,
                        "is_perishable": product.is_perishable,
                        "category": product.category,
                        "brand": product.brand,
                        "model": product.model,
                        "weight": product.weight,
                        "dimensions": product.dimensions,
                        "minimum_stock_level": product.minimum_stock_level or 0,
                        "maximum_stock_level": product.maximum_stock_level,
                        "reorder_point": product.reorder_point or 0,
                        "active": product.active or True,
                        "notes": product.notes,
                        "expiry_date": product.expiry_date,
                        "batch_number": product.batch_number,
                        "warranty_period_months": product.warranty_period_months,
                        "warranty_period_years": product.warranty_period_years,
                        "branch_id": product.branch_id,
                        "supplier_id": product.supplier_id,
                        "accounting_code_id": product.accounting_code_id,
                        "unit_of_measure_id": product.unit_of_measure_id,
                        "image_url": product.image_url if include_images else None,
                        "is_taxable": product.is_taxable or True,
                        "product_type": product.product_type,
                        "tracks_inventory": is_inventory_tracked(product),
                        "is_recurring_income": product.is_recurring_income or False,
                        "recurring_income_type": product.recurring_income_type,
                        "recurring_amount": product.recurring_amount,
                        "recurring_interval": product.recurring_interval,
                        "recurring_start_date": product.recurring_start_date,
                        "recurring_end_date": product.recurring_end_date,
                        "recurring_description": product.recurring_description,
                        "created_at": product.created_at,
                        "updated_at": product.updated_at,
                        "cost_center_id": product.cost_center_id,
                        "project_id": product.project_id
                    }
                    response_products.append(product_data)

            return response_products

        # Original logic for non-branch-inventory queries
        query = db.query(Product)
        if active_only:
            query = query.filter(Product.active == True)
        if category:
            query = query.filter(Product.category == category)
        # branch_id param adjusts displayed qty via allocations — do not hide products
        # whose master record is tied to another branch (stock may still exist via allocation).
        if company_id:
            branch_ids_sq = db.query(Branch.id).filter(Branch.company_id == company_id)
            query = query.filter(
                or_(
                    Product.branch_id.in_(branch_ids_sq),
                    Product.branch_id.is_(None),
                )
            )

        if search:
            pattern = f"%{search.strip()}%"
            query = query.filter(
                or_(
                    Product.name.ilike(pattern),
                    Product.sku.ilike(pattern),
                    Product.barcode.ilike(pattern),
                )
            )
        if product_type:
            query = query.filter(func.lower(Product.product_type) == normalize_product_type(product_type))
        if stock_items_only:
            query = query.filter(
                or_(
                    Product.product_type.is_(None),
                    ~func.lower(Product.product_type).in_(list(NON_INVENTORY_PRODUCT_TYPES)),
                )
            )

        products = query.order_by(Product.name).limit(limit).all()

        branch_qty_map: dict = {}
        if branch_id:
            from app.models.inventory_allocation import BranchInventoryAllocation
            alloc_rows = (
                db.query(
                    BranchInventoryAllocation.product_id,
                    func.coalesce(func.sum(BranchInventoryAllocation.available_quantity), 0),
                )
                .filter(
                    BranchInventoryAllocation.branch_id == branch_id,
                    BranchInventoryAllocation.allocation_status == "received",
                )
                .group_by(BranchInventoryAllocation.product_id)
                .all()
            )
            branch_qty_map = {str(pid): int(qty or 0) for pid, qty in alloc_rows}

        # Convert to response format to avoid serialization issues
        response_products = []
        for product in products:
            pid = str(product.id)
            branch_available = branch_qty_map.get(pid, 0) if branch_id else None
            global_qty = int(product.quantity or 0)
            display_qty = global_qty
            if branch_id:
                # Branch view: show allocation when set; else global qty for products tied to this branch
                if branch_available and branch_available > 0:
                    display_qty = branch_available
                elif product.branch_id and str(product.branch_id) == str(branch_id):
                    display_qty = global_qty

            product_data = {
                "id": product.id,
                "name": product.name,
                "sku": product.sku,
                "description": product.description,
                "quantity": display_qty,
                "global_quantity": global_qty,
                "branch_available_quantity": branch_available if branch_id else None,
                "barcode": product.barcode,
                "cost_price": product.cost_price or 0,
                "selling_price": product.selling_price or 0,
                "is_serialized": product.is_serialized or False,
                "is_perishable": product.is_perishable,
                "category": product.category,
                "brand": product.brand,
                "model": product.model,
                "weight": product.weight,
                "dimensions": product.dimensions,
                "minimum_stock_level": product.minimum_stock_level or 0,
                "maximum_stock_level": product.maximum_stock_level,
                "reorder_point": product.reorder_point or 0,
                "active": product.active or True,
                "notes": product.notes,
                "expiry_date": product.expiry_date,
                "batch_number": product.batch_number,
                "warranty_period_months": product.warranty_period_months,
                "warranty_period_years": product.warranty_period_years,
                "branch_id": product.branch_id,
                "supplier_id": product.supplier_id,
                "accounting_code_id": product.accounting_code_id,
                "unit_of_measure_id": product.unit_of_measure_id,
                "image_url": product.image_url if include_images else None,
                "is_taxable": product.is_taxable or True,
                "product_type": product.product_type,
                "tracks_inventory": is_inventory_tracked(product),
                "is_recurring_income": product.is_recurring_income or False,
                "recurring_income_type": product.recurring_income_type,
                "recurring_amount": product.recurring_amount,
                "recurring_interval": product.recurring_interval,
                "recurring_start_date": product.recurring_start_date,
                "recurring_end_date": product.recurring_end_date,
                "recurring_description": product.recurring_description,
                "created_at": product.created_at,
                "updated_at": product.updated_at,
                # Dimensional assignments
                "cost_center_id": product.cost_center_id,
                "project_id": product.project_id
            }
            response_products.append(product_data)

        return response_products
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Error fetching products: {str(e)}")


@router.post("/products/sync-branch-stock")
async def sync_branch_stock_from_product_quantities(
    db: Session = Depends(get_db),
    company_id: Optional[str] = Depends(get_current_company_id),
):
    """
  Backfill branch received allocations from product.quantity where missing.
  Use after fixing purchase receipt logic so branch/POS views match HQ stock.
    """
    from app.models.inventory_allocation import BranchInventoryAllocation
    from app.services.branch_inventory_guard import add_received_branch_stock

    q = db.query(Product).filter(Product.active == True, Product.quantity > 0)
    non_inv_types = list(NON_INVENTORY_PRODUCT_TYPES)
    q = q.filter(
        or_(
            Product.product_type.is_(None),
            ~func.lower(Product.product_type).in_(non_inv_types),
        )
    )
    if company_id:
        branch_ids_sq = db.query(Branch.id).filter(Branch.company_id == company_id)
        q = q.filter(Product.branch_id.in_(branch_ids_sq))

    updated = 0
    for product in q.all():
        if not product.branch_id:
            continue
        pid = str(product.id)
        bid = str(product.branch_id)
        qty = int(product.quantity or 0)
        if qty <= 0:
            continue
        existing = (
            db.query(BranchInventoryAllocation)
            .filter(
                BranchInventoryAllocation.product_id == pid,
                BranchInventoryAllocation.branch_id == bid,
                BranchInventoryAllocation.allocation_status == "received",
            )
            .first()
        )
        if existing and int(existing.available_quantity or 0) >= qty:
            continue
        if existing:
            existing.available_quantity = qty
            existing.received_quantity = max(int(existing.received_quantity or 0), qty)
            updated += 1
        else:
            add_received_branch_stock(
                db, pid, bid, qty, cost_per_unit=float(product.cost_price or 0)
            )
            updated += 1
    db.commit()
    return {"success": True, "updated": updated}


@router.get("/products/template")
async def download_product_import_template(db: Session = Depends(get_db)):
    """Generate a blank Excel template for bulk product import."""
    try:
        workbook = Workbook()
        sheet = workbook.active
        sheet.title = "Products"

        headers = [
            "SKU",
            "Name",
            "Description",
            "Category",
            "Product Type",
            "Cost Price",
            "Selling Price",
            "Quantity",
            "Reorder Point",
            "Unit of Measure ID",
            "Is Taxable (TRUE/FALSE)",
            "Barcode",
            "Serialized (TRUE/FALSE)",
            "Perishable (TRUE/FALSE)",
            "Notes",
            "Serial Numbers",
        ]

        header_font = Font(bold=True)
        header_alignment = Alignment(horizontal="center", vertical="center")

        sheet.append(headers)
        for idx, _ in enumerate(headers, start=1):
            cell = sheet.cell(row=1, column=idx)
            cell.font = header_font
            cell.alignment = header_alignment
            column_letter = sheet.cell(row=1, column=idx).column_letter
            sheet.column_dimensions[column_letter].width = 20

        # Provide hints in row 2 to guide import formatting
        sheet.append([
            "Required. Unique stock keeping unit",
            "Required. Product display name",
            "Optional description",
            "Optional category label",
            "inventory_item | service | assembly",
            "Numeric. Purchase cost",
            "Numeric. Selling price",
            "Numeric. Starting quantity",
            "Numeric. Minimum stock threshold",
            "Match an existing Unit of Measure ID",
            "TRUE for taxable, FALSE otherwise",
            "Optional barcode",
            "TRUE if item is serialized",
            "TRUE if perishable",
            "Optional notes",
            "Comma-separated serial numbers e.g. SN001,SN002,SN003",
        ])

        sheet.freeze_panes = "A2"

        # Add instructions sheet with additional guidance
        instructions = workbook.create_sheet("Instructions")
        instructions["A1"] = "Product Import Template"
        instructions["A1"].font = Font(bold=True, size=14)
        instructions["A3"] = "1. Fill in product details on the 'Products' sheet without removing the header row."
        instructions["A4"] = "2. Required columns: SKU, Name, Product Type, Cost Price, Selling Price, Unit of Measure ID."
        instructions["A5"] = "3. Use TRUE/FALSE for all boolean columns. Leave optional fields blank if not applicable."
        instructions["A6"] = "4. Save the file as .xlsx and upload it using the stock import tool."
        instructions["A7"] = "5. Retrieve valid Unit of Measure IDs from the units endpoint or products page before populating."

        for row in range(1, 8):
            instructions.row_dimensions[row].height = 18
        instructions.column_dimensions["A"].width = 110

        output = BytesIO()
        workbook.save(output)
        output.seek(0)

        filename = f"product_import_template_{datetime.now().strftime('%Y%m%d')}.xlsx"
        return StreamingResponse(
            output,
            media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            headers={
                "Content-Disposition": f"attachment; filename={filename}"
            },
        )
    except Exception as exc:
        raise HTTPException(status_code=500, detail=f"Error creating template: {exc}")


@router.post("/products/import-preview")
async def preview_import_file(file: UploadFile = File(...)):
    """Read an Excel or CSV file and return sheet names + preview rows + ALL rows for summary."""
    filename_lower = file.filename.lower()
    try:
        content = await file.read()
        if filename_lower.endswith('.csv'):
            import csv as _csv
            import io as _io
            text = content.decode('utf-8-sig', errors='replace')
            all_rows = [[str(v).strip() for v in row] for row in _csv.reader(_io.StringIO(text))]
            sheet_name = 'Sheet1'
            return {
                "sheets": [sheet_name],
                "rows": {sheet_name: all_rows[:10]},        # first 10 for header detection + display
                "all_rows": {sheet_name: all_rows},         # full data for summary counts
                "format": "csv",
            }
        elif filename_lower.endswith(('.xlsx', '.xls')):
            wb = load_workbook(BytesIO(content), data_only=True)
            rows_by_sheet: dict = {}
            all_rows_by_sheet: dict = {}
            for sheet_name in wb.sheetnames:
                ws = wb[sheet_name]
                all_rows = [[str(v).strip() if v is not None else '' for v in row]
                            for row in ws.iter_rows(values_only=True)]
                rows_by_sheet[sheet_name] = all_rows[:10]
                all_rows_by_sheet[sheet_name] = all_rows
            return {
                "sheets": list(wb.sheetnames),
                "rows": rows_by_sheet,
                "all_rows": all_rows_by_sheet,
                "format": "xlsx",
            }
        else:
            raise HTTPException(status_code=400, detail="Only .xlsx, .xls, and .csv files are supported")
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(status_code=400, detail=f"Could not read file: {str(e)}")


@router.post("/products/import")
async def import_products_from_excel(
    file: UploadFile = File(...),
    column_map: Optional[str] = Form(None),
    db: Session = Depends(get_db),
    company_id: Optional[str] = Depends(get_current_company_id),
):
    """Import products from Excel. Accepts custom column mapping via column_map JSON,
    or falls back to the standard 'Products' sheet template format."""
    if not file.filename.lower().endswith(('.xlsx', '.xls')):
        raise HTTPException(status_code=400, detail="Only .xlsx and .xls files are supported")

    try:
        import json as _json
        content = await file.read()
        wb = load_workbook(BytesIO(content), data_only=True)

        def _s(v, default=None):
            if v is None: return default
            s = str(v).strip()
            return s if s else default

        def _b(v, default=False):
            if v is None: return default
            if isinstance(v, bool): return v
            return str(v).upper() in ('TRUE', '1', 'YES')

        def _d(v, default=Decimal('0.00')):
            if v is None: return default
            try: return Decimal(str(v))
            except: return default

        def _i(v, default=0):
            if v is None: return default
            try: return int(float(str(v)))
            except: return default

        if column_map:
            try:
                cm = _json.loads(column_map)
            except Exception:
                raise HTTPException(status_code=400, detail="Invalid column_map JSON")
            sheet_name = cm.get('sheet', wb.sheetnames[0] if wb.sheetnames else '')
            if sheet_name not in wb.sheetnames:
                raise HTTPException(status_code=400, detail=f"Sheet '{sheet_name}' not found in file")
            all_rows = list(wb[sheet_name].iter_rows(values_only=True))
            header_row_idx = int(cm.get('header_row', 0))
            data_start_row_idx = int(cm.get('data_start_row', 1))
            if header_row_idx >= len(all_rows):
                raise HTTPException(status_code=400, detail="Header row index out of range")
            headers = [str(v).strip() if v is not None else '' for v in all_rows[header_row_idx]]
            col_idx = {h: i for i, h in enumerate(headers) if h}
            data_rows = all_rows[data_start_row_idx:]
            row_start_num = data_start_row_idx + 1
            field_mappings = cm.get('mappings', {})

            def extract_row(row):
                def gv(key):
                    col_name = field_mappings.get(key)
                    if not col_name: return None
                    idx = col_idx.get(col_name)
                    if idx is None: return None
                    return row[idx] if idx < len(row) else None
                return {
                    'sku':                 _s(gv('sku')),
                    'name':                _s(gv('name')),
                    'description':         _s(gv('description')),
                    'category':            _s(gv('category')),
                    'brand':               _s(gv('brand')),
                    'condition':           _s(gv('condition')),
                    'location':            _s(gv('location')),
                    'product_type':        _s(gv('product_type'), 'inventory_item'),
                    'cost_price':          _d(gv('cost_price')),
                    'selling_price':       _d(gv('selling_price')),
                    'quantity':            _i(gv('quantity'), 0),
                    'reorder_point':       _i(gv('reorder_point')),
                    'minimum_stock_level': _i(gv('reorder_point')),
                    'unit_of_measure_id':  _s(gv('unit_of_measure_id')),
                    'is_taxable':          _b(gv('is_taxable'), True),
                    'barcode':             _s(gv('barcode')),
                    'is_serialized':       _b(gv('is_serialized'), False),
                    'is_perishable':       _b(gv('is_perishable'), False),
                    'notes':               _s(gv('notes')),
                    'serial_numbers':      _s(gv('serial_numbers'), ''),
                }
        else:
            if "Products" not in wb.sheetnames:
                raise HTTPException(status_code=400, detail="Excel file must have a 'Products' sheet. Download the import template first, or use 'Upload Product Import File' which supports custom column mapping.")
            all_rows = list(wb["Products"].iter_rows(values_only=True))
            if len(all_rows) < 3:
                raise HTTPException(status_code=400, detail="No data rows found. Row 1 = headers, Row 2 = hints, data starts at Row 3.")
            data_rows = all_rows[2:]
            row_start_num = 3

            def extract_row(row):
                r = lambda i: (row[i] if i < len(row) else None) if row else None
                return {
                    'sku':                 _s(r(0)),
                    'name':                _s(r(1)),
                    'description':         _s(r(2)),
                    'category':            _s(r(3)),
                    'product_type':        _s(r(4), 'inventory_item'),
                    'cost_price':          _d(r(5)),
                    'selling_price':       _d(r(6)),
                    'quantity':            _i(r(7), 0),
                    'reorder_point':       _i(r(8)),
                    'minimum_stock_level': _i(r(8)),
                    'unit_of_measure_id':  _s(r(9)),
                    'is_taxable':          _b(r(10), True),
                    'barcode':             _s(r(11)),
                    'is_serialized':       _b(r(12), False),
                    'is_perishable':       _b(r(13), False),
                    'notes':               _s(r(14)),
                    'serial_numbers':      _s(r(15), ''),
                }

        # Pre-build UoM lookup so raw values like 'UNIT', 'pcs', 'Piece' resolve to a real UUID
        _uom_q = db.query(UnitOfMeasure)
        if company_id:
            _uom_branch_ids = db.query(Branch.id).filter(Branch.company_id == company_id)
            _uom_q = _uom_q.filter(or_(UnitOfMeasure.branch_id.in_(_uom_branch_ids), UnitOfMeasure.branch_id.is_(None)))
        _all_uoms = _uom_q.all()
        _uom_by_id   = {u.id: u.id for u in _all_uoms}
        _uom_by_abbr = {(u.abbreviation or '').strip().upper(): u.id for u in _all_uoms}
        _uom_by_name = {(u.name or '').strip().upper(): u.id for u in _all_uoms}

        def _resolve_uom(val):
            if not val:
                return None
            s = str(val).strip()
            if s in _uom_by_id:          # exact UUID match
                return s
            key = s.upper()
            return _uom_by_abbr.get(key) or _uom_by_name.get(key)  # None if not found

        # Phase 1: validate all rows and collect what to create/update (no writes yet)
        phase1 = []
        errors = []
        for row_idx, row in enumerate(data_rows, start=row_start_num):
            if all(v is None or str(v).strip() == '' for v in row):
                continue
            d = extract_row(row)
            d['unit_of_measure_id'] = _resolve_uom(d.get('unit_of_measure_id'))
            sku = d['sku']
            name = d['name']
            if not sku:
                errors.append({"row": row_idx, "sku": "", "error": "SKU is required"})
                continue
            existing = db.query(Product).filter(Product.sku == sku).first()
            fields = {k: v for k, v in d.items() if k not in ('quantity', 'serial_numbers') and v is not None}
            # Fall back to SKU as name for new products where name column is empty/unmapped
            if not existing and not fields.get('name'):
                fields['name'] = sku
            phase1.append({'existing': existing, 'fields': fields,
                           'quantity': d['quantity'], 'serials_raw': d['serial_numbers'],
                           'row_idx': row_idx, 'sku': sku})

        # Phase 2: apply changes
        from app.models.inventory import SerialNumber
        created = 0
        updated = 0
        serials_added = 0
        _batch_sku_ids = {}  # tracks products created in this import batch: sku -> product_id
        for item in phase1:
            sp = db.begin_nested()  # savepoint — keeps outer tx alive on per-row failure
            try:
                existing = item['existing']
                fields = item['fields']
                quantity = item['quantity']
                serials_raw = item['serials_raw']
                sku = item['sku']

                if existing:
                    # Product already in DB — update fields
                    for k, v in fields.items():
                        if k != 'sku':
                            setattr(existing, k, v)
                    db.flush()
                    product_id = existing.id
                    _batch_sku_ids[sku] = product_id
                    updated += 1
                elif sku in _batch_sku_ids:
                    # Duplicate row in this Excel file — product already created above;
                    # just accumulate serial numbers, don't try to re-insert
                    product_id = _batch_sku_ids[sku]
                else:
                    new_product = Product(**fields)
                    db.add(new_product)
                    db.flush()
                    product_id = new_product.id
                    _batch_sku_ids[sku] = product_id
                    if quantity > 0:
                        db.query(Product).filter(Product.id == product_id).update({'quantity': quantity})
                        tx = InventoryTransaction(
                            product_id=product_id,
                            transaction_type='opening_stock',
                            quantity=quantity,
                            date=datetime.utcnow().date(),
                            reference='Excel import',
                            note='Opening stock from Excel import',
                        )
                        db.add(tx)
                    created += 1
                # Register serial numbers
                if serials_raw:
                    for serial in (s.strip() for s in serials_raw.split(',') if s.strip()):
                        exists = db.query(SerialNumber).filter(
                            SerialNumber.serial == serial,
                            SerialNumber.product_id == product_id
                        ).first()
                        if not exists:
                            db.add(SerialNumber(serial=serial, product_id=product_id, status='in_stock'))
                            serials_added += 1
                sp.commit()
            except Exception as row_err:
                sp.rollback()  # rollback to savepoint only — outer transaction stays alive
                errors.append({"row": item['row_idx'], "sku": item['sku'], "error": str(row_err)})

        db.commit()
        rows_skipped = len(errors)
        msg_parts = []
        if created:     msg_parts.append(f"{created} products created")
        if updated:     msg_parts.append(f"{updated} updated")
        if serials_added: msg_parts.append(f"{serials_added} serial numbers registered")
        if rows_skipped: msg_parts.append(f"{rows_skipped} rows skipped")
        return {
            "success": True,
            "created": created,
            "updated": updated,
            "serials_added": serials_added,
            "rows_skipped": rows_skipped,
            "errors": errors,
            "message": "Import complete: " + ", ".join(msg_parts) if msg_parts else "Import complete (no changes)",
        }
    except HTTPException:
        raise
    except Exception as e:
        db.rollback()
        raise HTTPException(status_code=500, detail=f"Error processing import file: {str(e)}")


@router.get("/products/purchase-template")
async def download_purchase_import_template():
    """Generate an Excel template for bulk stock purchase import."""
    wb = Workbook()

    # Sheet 1: Purchase Info
    info = wb.active
    info.title = "Purchase Info"
    bold = Font(bold=True)
    italic = Font(italic=True)

    pi_headers = ['Supplier Name or ID *', 'Purchase Date *', 'Reference / PO Number',
                  'Supplier Invoice Number', 'Notes']
    pi_hints = ['Enter supplier name (partial match) or exact supplier ID',
                'Format: YYYY-MM-DD e.g. 2025-03-02',
                'Optional. Your internal PO reference',
                'Optional. Supplier invoice number',
                'Optional. General notes for this purchase']
    pi_example = ['ABC Suppliers Ltd', datetime.now().strftime('%Y-%m-%d'),
                  'PO-2025-001', 'INV-12345', 'Monthly stock replenishment']

    for col_idx, h in enumerate(pi_headers, start=1):
        cell = info.cell(row=1, column=col_idx, value=h)
        cell.font = bold
        info.column_dimensions[cell.column_letter].width = 34
    for col_idx, h in enumerate(pi_hints, start=1):
        info.cell(row=2, column=col_idx, value=h)
    for col_idx, v in enumerate(pi_example, start=1):
        info.cell(row=3, column=col_idx, value=v).font = italic

    # Sheet 2: Items
    items_ws = wb.create_sheet("Items")
    i_headers = ['SKU *', 'Description', 'Quantity *', 'Cost Price (Ex VAT) *',
                 'VAT Rate % (e.g. 14)', 'Serial Numbers (comma-sep)',
                 'Batch Number', 'Expiry Date (YYYY-MM-DD)', 'Notes']
    i_hints   = ['Required. Must match existing product SKU',
                 'Optional. Defaults to product name',
                 'Required. Quantity being purchased',
                 'Required. Unit cost excluding VAT',
                 'Enter 0 for no VAT, or e.g. 14 for 14%',
                 'For serialized products: SN001,SN002,SN003',
                 'Optional. Batch or lot number',
                 'Optional. e.g. 2025-12-31',
                 'Optional. Notes for this line item']
    i_example = ['PROD-001', 'Widget A', 10, 25.50, 14, 'SN001,SN002', 'BATCH-001', '2025-12-31', '']

    for col_idx, h in enumerate(i_headers, start=1):
        cell = items_ws.cell(row=1, column=col_idx, value=h)
        cell.font = bold
        cell.alignment = Alignment(horizontal='center', vertical='center')
        items_ws.column_dimensions[cell.column_letter].width = 26
    for col_idx, h in enumerate(i_hints, start=1):
        items_ws.cell(row=2, column=col_idx, value=h)
    for col_idx, v in enumerate(i_example, start=1):
        items_ws.cell(row=3, column=col_idx, value=v).font = italic
    items_ws.freeze_panes = 'A3'

    # Sheet 3: Instructions
    instr = wb.create_sheet("Instructions")
    lines = [
        ('Stock Purchase Import Instructions', True, 14),
        ('', False, 11),
        ('Purchase Info sheet (Sheet 1):', True, 11),
        ('  Fill in Row 3 with your purchase header. Row 1 = headers, Row 2 = hints, Row 3 = your data.', False, 11),
        ('  Supplier: Enter a partial name matching your supplier record, or the exact supplier ID.', False, 11),
        ('  Purchase Date: Must be YYYY-MM-DD format.', False, 11),
        ('', False, 11),
        ('Items sheet (Sheet 2):', True, 11),
        ('  Add one product per row starting from Row 3 (Rows 1-2 are headers/hints).', False, 11),
        ('  SKU: Must exactly match an existing product SKU in the system.', False, 11),
        ('  Serial Numbers: Comma-separated list. For serialized products the count should match Quantity.', False, 11),
        ('  VAT Rate: Enter percentage only (e.g., 14 for 14%, 0 for zero-rated).', False, 11),
        ('', False, 11),
        ('What happens on import:', True, 11),
        ('  1. A Purchase record is created in the system.', False, 11),
        ('  2. Journal entries posted automatically: Dr. Inventory Account, Cr. Accounts Payable.', False, 11),
        ('  3. Inventory quantities are increased for each product line.', False, 11),
        ('  4. Serial numbers are registered as in-stock if provided.', False, 11),
        ('  5. The purchase appears in the Purchases module for further action (payment, receiving, etc.).', False, 11),
    ]
    for row_idx, (text, is_bold, size) in enumerate(lines, start=1):
        cell = instr.cell(row=row_idx, column=1, value=text)
        cell.font = Font(bold=is_bold, size=size)
    instr.column_dimensions['A'].width = 100

    output = BytesIO()
    wb.save(output)
    output.seek(0)
    filename = f"stock_purchase_template_{datetime.now().strftime('%Y%m%d')}.xlsx"
    return StreamingResponse(
        output,
        media_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        headers={"Content-Disposition": f"attachment; filename={filename}"},
    )


@router.post("/products/serialized-inventory-import")
async def import_serialized_inventory_from_excel(
    file: UploadFile = File(...),
    import_type: str = Form("opening_stock"),   # opening_stock | adjustment_in | adjustment_out
    reason: Optional[str] = Form(None),
    effective_date_str: Optional[str] = Form(None),
    branch_id: Optional[str] = Form(None),
    db: Session = Depends(get_db),
    company_id: Optional[str] = Depends(get_current_company_id),
):
    """
    Import serialized hardware stock directly into inventory (no Purchase record).
    Use this for initial setup (opening_stock) or periodic stock adjustments.

    import_type values:
      opening_stock   – sets opening quantity; skips products that already have stock
      adjustment_in   – positive stock adjustment (adds to existing qty)
      adjustment_out  – negative stock adjustment (removes from existing qty)

    Expected sheet: 'Serialized Stock'
    Columns: same as serialized-stock-import (Category, Brand, Model, Specification,
    Serial Number, Barcode EAN-13, Condition, Quantity, Cost Price, Selling Price, Location)
    """
    from app.models.inventory import SerialNumber, InventoryTransaction
    from app.models.branch import Branch
    from app.models.user import User
    from decimal import InvalidOperation
    from sqlalchemy import text as _text

    VALID_TYPES = ('opening_stock', 'adjustment_in', 'adjustment_out')
    if import_type not in VALID_TYPES:
        raise HTTPException(status_code=400, detail=f"import_type must be one of {VALID_TYPES}")

    if not file.filename.lower().endswith(('.xlsx', '.xls')):
        raise HTTPException(status_code=400, detail="Only .xlsx / .xls files are supported")

    def _dec(v, default=Decimal('0')):
        if v is None:
            return default
        try:
            return Decimal(str(v))
        except InvalidOperation:
            return default

    def _parse_date(v):
        if not v:
            return datetime.utcnow()
        if hasattr(v, 'year'):
            return datetime(v.year, v.month, v.day)
        for fmt in ('%Y-%m-%d', '%d/%m/%Y', '%m/%d/%Y', '%d-%m-%Y'):
            try:
                return datetime.strptime(str(v).strip(), fmt)
            except ValueError:
                pass
        return datetime.utcnow()

    try:
        content = await file.read()
        wb = load_workbook(BytesIO(content), data_only=True)
        sheet_name = 'Serialized Stock' if 'Serialized Stock' in wb.sheetnames else wb.sheetnames[0]
        ws = wb[sheet_name]

        # ── Resolve branch ────────────────────────────────────────────────────
        if branch_id:
            db_branch = db.query(Branch).filter(Branch.id == branch_id, Branch.active == True).first()
            if not db_branch:
                raise HTTPException(status_code=400, detail=f"Branch '{branch_id}' not found")
        else:
            _branch_q = db.query(Branch).filter(Branch.active == True)
            if company_id:
                _branch_q = _branch_q.filter(Branch.company_id == company_id)
            db_branch = _branch_q.first()
        resolved_branch_id = db_branch.id if db_branch else None

        system_user = db.query(User).filter(User.is_active == True).first()
        created_by = system_user.id if system_user else None
        effective_date = _parse_date(effective_date_str)
        ref_label = reason or f"Serialized {import_type.replace('_', ' ')} import {effective_date.strftime('%Y-%m-%d')}"

        # ── Read & group rows by model ────────────────────────────────────────
        from collections import defaultdict

        class GroupData:
            __slots__ = ('barcode', 'condition', 'cost', 'sell', 'serials')
            def __init__(self):
                self.barcode = None
                self.condition = 'New'
                self.cost = Decimal('0')
                self.sell = Decimal('0')
                self.serials = []  # (serial, location)

        groups: dict = {}
        row_errors = []
        total_rows = 0

        for row_idx, row in enumerate(ws.iter_rows(min_row=2, values_only=True), start=2):
            if row[0] is None:
                continue
            total_rows += 1
            try:
                cat   = str(row[0]).strip()
                brand = str(row[1]).strip()
                model = str(row[2]).strip()
                spec  = str(row[3]).strip() if row[3] else ''
                serial   = str(row[4]).strip() if row[4] else ''
                barcode  = str(row[5]).strip() if row[5] else ''
                condition = str(row[6]).strip() if row[6] else 'New'
                cost  = _dec(row[8])
                sell  = _dec(row[9])
                location = str(row[10]).strip() if len(row) > 10 and row[10] else ''

                if not serial:
                    row_errors.append({"row": row_idx, "error": "Empty serial number — skipped"})
                    continue

                key = (cat, brand, model, spec)
                if key not in groups:
                    g = GroupData()
                    g.barcode = barcode
                    g.condition = condition
                    g.cost = cost
                    g.sell = sell
                    groups[key] = g
                groups[key].serials.append((serial, location))
            except Exception as e:
                row_errors.append({"row": row_idx, "error": str(e)})

        if not groups:
            raise HTTPException(status_code=400, detail="No valid rows found in the file.")

        # ── Find or create Products ───────────────────────────────────────────
        products_created = 0
        products_found = 0
        product_map: dict = {}

        for key, gdata in groups.items():
            cat, brand, model, spec = key
            prod_name = f"{brand} {model}"
            barcode = gdata.barcode

            prod = None
            if barcode:
                prod = db.query(Product).filter(
                    (Product.barcode == barcode) | (Product.sku == barcode)
                ).first()
            if not prod:
                prod = db.query(Product).filter(
                    Product.name == prod_name,
                    Product.category == cat,
                ).first()

            if prod:
                products_found += 1
                if not prod.is_serialized:
                    prod.is_serialized = True
            else:
                prod = Product(
                    name=prod_name,
                    sku=barcode or f"{cat[:3].upper()}-{brand[:3].upper()}-{model[:6].upper()}",
                    barcode=barcode,
                    description=spec,
                    category=cat,
                    brand=brand,
                    cost_price=gdata.cost,
                    selling_price=gdata.sell,
                    quantity=0,
                    is_serialized=True,
                    condition=gdata.condition,
                    branch_id=resolved_branch_id,
                    product_type='inventory_item',
                )
                db.add(prod)
                products_created += 1

            product_map[key] = prod

        db.flush()

        # ── Inventory quantity updates ────────────────────────────────────────
        txn_type_map = {
            'opening_stock': 'opening_stock',
            'adjustment_in': 'adjustment_in',
            'adjustment_out': 'adjustment_out',
        }
        txn_type = txn_type_map[import_type]

        qty_deltas = {}
        for key, gdata in groups.items():
            prod = product_map[key]
            qty = len(gdata.serials)
            if import_type == 'opening_stock' and (prod.quantity or 0) > 0:
                # Skip qty update for opening stock if product already has stock
                continue
            sign = -1 if import_type == 'adjustment_out' else 1
            qty_deltas[prod.id] = qty_deltas.get(prod.id, 0) + sign * qty

        if qty_deltas:
            db.execute(
                _text("UPDATE products SET quantity = GREATEST(quantity + :delta, 0) WHERE id = :pid"),
                [{"delta": delta, "pid": pid} for pid, delta in qty_deltas.items()],
            )

        # Inventory transactions
        txns = []
        for key, gdata in groups.items():
            prod = product_map[key]
            qty = len(gdata.serials)
            sign = -1 if import_type == 'adjustment_out' else 1
            txns.append(InventoryTransaction(
                product_id=prod.id,
                transaction_type=txn_type,
                quantity=sign * qty,
                unit_cost=gdata.cost,
                date=effective_date.date() if hasattr(effective_date, 'date') else effective_date,
                reference=ref_label,
                note=ref_label,
                branch_id=resolved_branch_id,
                created_by=created_by,
            ))
        if txns:
            db.add_all(txns)

        # ── Serial Numbers ────────────────────────────────────────────────────
        all_serials = [s for gdata in groups.values() for s, _ in gdata.serials]
        existing_serials = {
            row[0]
            for row in db.execute(
                _text("SELECT serial FROM serial_numbers WHERE serial = ANY(:serials)"),
                {"serials": all_serials},
            )
        } if all_serials else set()

        sn_rows = []
        serials_skipped = []
        seen_in_file = set()

        for key, gdata in groups.items():
            prod = product_map[key]
            serial_status = 'available' if import_type != 'adjustment_out' else 'returned'
            for serial, location in gdata.serials:
                if serial in existing_serials or serial in seen_in_file:
                    serials_skipped.append(serial)
                    continue
                seen_in_file.add(serial)
                sn_rows.append(SerialNumber(
                    serial=serial,
                    product_id=prod.id,
                    status=serial_status,
                    branch_id=resolved_branch_id,
                    location=location or None,
                ))

        if sn_rows:
            db.add_all(sn_rows)

        db.commit()

        type_labels = {
            'opening_stock': 'Opening stock',
            'adjustment_in': 'Stock adjustment (in)',
            'adjustment_out': 'Stock adjustment (out)',
        }

        return {
            "success": True,
            "import_type": import_type,
            "products_found": products_found,
            "products_created": products_created,
            "serials_imported": len(sn_rows),
            "serials_skipped_duplicates": len(serials_skipped),
            "total_rows_read": total_rows,
            "row_errors": row_errors,
            "message": (
                f"{type_labels[import_type]}: {products_created} new product(s), "
                f"{products_found} existing. {len(sn_rows)} serials imported"
                + (f", {len(serials_skipped)} duplicate(s) skipped" if serials_skipped else "")
                + "."
            ),
        }

    except HTTPException:
        raise
    except Exception as e:
        db.rollback()
        raise HTTPException(status_code=500, detail=f"Import failed: {str(e)}")


@router.post("/products/serialized-stock-import")
async def import_serialized_stock_from_excel(
    file: UploadFile = File(...),
    supplier_id: Optional[str] = Form(...),
    purchase_date_str: Optional[str] = Form(None),
    reference: Optional[str] = Form(None),
    branch_id: Optional[str] = Form(None),
    column_map: Optional[str] = Form(None),
    db: Session = Depends(get_db),
    company_id: Optional[str] = Depends(get_current_company_id),
):
    """
    Import serialized hardware stock from Excel.

    Two modes:
    - Fixed mode (no column_map): expects sheet 'Serialized Stock', fixed column positions,
      one row per serial number.
    - Flexible mode (column_map JSON provided): any sheet, any column order, supports
      comma-separated serial numbers in a single cell (consolidated format).

    One Purchase record is created for the whole file in both modes.
    """
    from app.models.purchases import Purchase, PurchaseItem, Supplier
    from app.models.inventory import SerialNumber, InventoryTransaction
    from app.services.ifrs_accounting_service import IFRSAccountingService
    from app.models.branch import Branch
    from app.models.user import User
    from decimal import InvalidOperation
    from sqlalchemy import text as _text
    from collections import defaultdict

    if not file.filename.lower().endswith(('.xlsx', '.xls')):
        raise HTTPException(status_code=400, detail="Only .xlsx / .xls files are supported")

    def _dec(v, default=Decimal('0')):
        if v is None:
            return default
        try:
            return Decimal(str(v))
        except InvalidOperation:
            return default

    def _parse_date(v):
        if not v:
            return datetime.utcnow()
        if hasattr(v, 'year'):
            return datetime(v.year, v.month, v.day)
        for fmt in ('%Y-%m-%d', '%d/%m/%Y', '%m/%d/%Y', '%d-%m-%Y'):
            try:
                return datetime.strptime(str(v).strip(), fmt)
            except ValueError:
                pass
        return datetime.utcnow()

    try:
        import json as _json
        content = await file.read()
        wb = load_workbook(BytesIO(content), data_only=True)

        # ── Flexible mode: header-based column mapping, comma-sep serials ─────
        if column_map:
            try:
                cm = _json.loads(column_map)
            except Exception:
                raise HTTPException(status_code=400, detail="Invalid column_map JSON")

            sheet_name = cm.get('sheet', wb.sheetnames[0])
            if sheet_name not in wb.sheetnames:
                sheet_name = wb.sheetnames[0]
            header_row_idx = max(0, int(cm.get('header_row', 1)) - 1)   # 1-based from UI → 0-based index
            data_start_idx = max(1, int(cm.get('data_start_row', 2)) - 1)  # 1-based → 0-based index
            mappings = cm.get('mappings', {})

            all_rows = [[str(v).strip() if v is not None else '' for v in r]
                        for r in wb[sheet_name].iter_rows(values_only=True)]
            if header_row_idx >= len(all_rows):
                raise HTTPException(status_code=400, detail="Header row index out of range")

            headers = [str(v).strip() for v in all_rows[header_row_idx]]
            col_idx_map = {h: i for i, h in enumerate(headers) if h}

            def _gv(row, field):
                col = mappings.get(field)
                if not col:
                    return ''
                idx = col_idx_map.get(col)
                return row[idx].strip() if idx is not None and idx < len(row) else ''

            groups = {}
            row_errors = []
            total_rows = 0

            for row_idx, row in enumerate(all_rows[data_start_idx:], start=data_start_idx + 1):
                if not any(v for v in row if str(v).strip()):
                    continue
                total_rows += 1
                try:
                    cat      = _gv(row, 'category') or 'General'
                    brand    = _gv(row, 'brand') or _gv(row, 'name') or 'Unknown'
                    model    = _gv(row, 'model') or ''
                    spec     = _gv(row, 'description') or ''
                    barcode  = _gv(row, 'sku') or _gv(row, 'barcode') or ''
                    condition = _gv(row, 'condition') or 'New'
                    cost     = _dec(_gv(row, 'cost') or None)
                    sell     = _dec(_gv(row, 'selling_price') or None)
                    location = _gv(row, 'location') or ''
                    serials_raw = _gv(row, 'serial_numbers') or ''

                    serials_list = [s.strip() for s in serials_raw.split(',') if s.strip()]
                    if not serials_list:
                        row_errors.append({"row": row_idx, "error": "No serial numbers — skipped"})
                        continue

                    key = (cat, brand, model, spec, barcode)
                    if key not in groups:
                        class _GD:
                            __slots__ = ('barcode', 'condition', 'cost', 'sell', 'serials')
                            def __init__(self):
                                self.barcode   = None
                                self.condition = None
                                self.cost      = Decimal('0')
                                self.sell      = Decimal('0')
                                self.serials   = []
                        g = _GD()
                        g.barcode    = barcode
                        g.condition  = condition
                        g.cost       = cost
                        g.sell       = sell
                        groups[key]  = g
                    for serial in serials_list:
                        groups[key].serials.append((serial, location))

                except Exception as e:
                    row_errors.append({"row": row_idx, "error": str(e)})

            if not groups:
                raise HTTPException(status_code=400, detail="No valid rows found in file.")

        # ── Fixed mode: original column-positional format ─────────────────────
        else:
            sheet_name = 'Serialized Stock'
            if sheet_name not in wb.sheetnames:
                sheet_name = wb.sheetnames[0]

            ws = wb[sheet_name]

            class GroupData:
                __slots__ = ('barcode', 'condition', 'cost', 'sell', 'serials')
                def __init__(self):
                    self.barcode = None
                    self.condition = None
                    self.cost = Decimal('0')
                    self.sell = Decimal('0')
                    self.serials = []  # list of (serial_str, location_str)

            groups = {}
            row_errors = []
            total_rows = 0

            for row_idx, row in enumerate(ws.iter_rows(min_row=2, values_only=True), start=2):
                if row[0] is None:
                    continue
                total_rows += 1
                try:
                    cat   = str(row[0]).strip()
                    brand = str(row[1]).strip()
                    model = str(row[2]).strip()
                    spec  = str(row[3]).strip() if row[3] else ''
                    serial   = str(row[4]).strip() if row[4] else ''
                    barcode  = str(row[5]).strip() if row[5] else ''
                    condition = str(row[6]).strip() if row[6] else 'New'
                    cost  = _dec(row[8])
                    sell  = _dec(row[9])
                    location = str(row[10]).strip() if row[10] else ''

                    if not serial:
                        row_errors.append({"row": row_idx, "error": "Empty serial number — skipped"})
                        continue

                    key = (cat, brand, model, spec)
                    if key not in groups:
                        g = GroupData()
                        g.barcode = barcode
                        g.condition = condition
                        g.cost = cost
                        g.sell = sell
                        groups[key] = g
                    groups[key].serials.append((serial, location))

                except Exception as e:
                    row_errors.append({"row": row_idx, "error": str(e)})

            if not groups:
                raise HTTPException(status_code=400, detail="No valid rows found in the file.")

        # ── Common: validate supplier, branch, user ───────────────────────────
        _supplier_q = db.query(Supplier).filter(Supplier.id == supplier_id, Supplier.active == True)
        if company_id:
            _sup_branch_ids = db.query(Branch.id).filter(Branch.company_id == company_id)
            _supplier_q = _supplier_q.filter(Supplier.branch_id.in_(_sup_branch_ids))
        supplier = _supplier_q.first()
        if not supplier:
            raise HTTPException(status_code=400, detail=f"Supplier '{supplier_id}' not found")

        # ── Resolve branch ────────────────────────────────────────────────────
        if branch_id:
            db_branch = db.query(Branch).filter(Branch.id == branch_id, Branch.active == True).first()
            if not db_branch:
                raise HTTPException(status_code=400, detail=f"Branch '{branch_id}' not found")
        else:
            _branch_q = db.query(Branch).filter(Branch.active == True)
            if company_id:
                _branch_q = _branch_q.filter(Branch.company_id == company_id)
            db_branch = _branch_q.first()
        resolved_branch_id = db_branch.id if db_branch else None

        system_user = db.query(User).filter(User.is_active == True).first()
        created_by = system_user.id if system_user else None
        purchase_date = _parse_date(purchase_date_str)

        # ── Find or create Products ───────────────────────────────────────────
        products_created = 0
        products_found = 0
        product_map = {}

        for key, gdata in groups.items():
            # key is (cat, brand, model, spec) in fixed mode or (cat, brand, model, spec, barcode) in flexible mode
            cat, brand, model, spec = key[0], key[1], key[2], key[3]
            prod_name = f"{brand} {model}".strip()
            barcode = gdata.barcode

            # Look up by barcode (used as SKU) first, then by name
            prod = None
            if barcode:
                prod = db.query(Product).filter(
                    (Product.barcode == barcode) | (Product.sku == barcode)
                ).first()
            if not prod:
                prod = db.query(Product).filter(
                    Product.name == prod_name,
                    Product.category == cat,
                ).first()

            if prod:
                products_found += 1
                # Ensure is_serialized is set
                if not prod.is_serialized:
                    prod.is_serialized = True
            else:
                prod = Product(
                    name=prod_name,
                    sku=barcode or f"{cat[:3].upper()}-{brand[:3].upper()}-{model[:6].upper()}",
                    barcode=barcode,
                    description=spec,
                    category=cat,
                    brand=brand,
                    cost_price=gdata.cost,
                    selling_price=gdata.sell,
                    quantity=0,
                    is_serialized=True,
                    condition=gdata.condition,
                    supplier_id=supplier.id,
                    branch_id=resolved_branch_id,
                    product_type='inventory_item',
                )
                db.add(prod)
                products_created += 1

            product_map[key] = prod

        db.flush()  # Assign IDs to new products

        # ── Create Purchase record ────────────────────────────────────────────
        total_cost = sum(
            gdata.cost * len(gdata.serials)
            for gdata in groups.values()
        )

        db_purchase = Purchase(
            supplier_id=supplier.id,
            purchase_date=purchase_date,
            total_amount=total_cost,
            total_vat_amount=Decimal('0'),
            total_amount_ex_vat=total_cost,
            status='received',
            reference=reference or f"STOCK-IMPORT-{datetime.utcnow().strftime('%Y%m%d%H%M')}",
            notes=f"Serialized stock import from {file.filename} on {datetime.utcnow().strftime('%Y-%m-%d')}",
            branch_id=resolved_branch_id,
            created_by=created_by,
        )
        db.add(db_purchase)
        db.flush()

        # ── Create PurchaseItems ──────────────────────────────────────────────
        purchase_items_map: dict[GroupKey, PurchaseItem] = {}
        for key, gdata in groups.items():
            prod = product_map[key]
            qty = len(gdata.serials)
            line_total = gdata.cost * qty
            db_item = PurchaseItem(
                purchase_id=db_purchase.id,
                product_id=prod.id,
                description=prod.name,
                category=prod.category,
                brand=prod.brand,
                condition=gdata.condition,
                quantity=Decimal(qty),
                cost=gdata.cost,
                total_cost=line_total,
                vat_rate=Decimal('0'),
                vat_amount=Decimal('0'),
                is_inventory=True,
            )
            db.add(db_item)
            purchase_items_map[key] = db_item

        db.flush()

        # ── Journal entries ───────────────────────────────────────────────────
        je_status = 'posted'
        try:
            ifrs_service = IFRSAccountingService(db)
            ifrs_service.create_purchase_journal_entries(db_purchase, created_by_user_id=created_by)
            db_purchase.posting_status = 'posted'
            db_purchase.posted_by = created_by
            db.commit()
        except Exception as je_err:
            je_status = f"warning: {je_err}"
            db.rollback()
            # Re-add everything after rollback wipes the session
            # (JE failure is non-fatal — stock still gets imported)
            db.add(db_purchase)

        # ── Inventory qty updates ─────────────────────────────────────────────
        qty_deltas = {
            product_map[key].id: len(gdata.serials)
            for key, gdata in groups.items()
        }
        db.execute(
            _text("UPDATE products SET quantity = GREATEST(quantity + :delta, 0) WHERE id = :pid"),
            [{"delta": delta, "pid": pid} for pid, delta in qty_deltas.items()],
        )

        # Inventory transactions
        txns = [
            InventoryTransaction(
                product_id=product_map[key].id,
                transaction_type='goods_receipt',
                quantity=len(gdata.serials),
                unit_cost=gdata.cost,
                date=purchase_date.date() if hasattr(purchase_date, 'date') else purchase_date,
                reference=db_purchase.reference,
                branch_id=resolved_branch_id,
                related_purchase_id=db_purchase.id,
            )
            for key, gdata in groups.items()
        ]
        db.add_all(txns)

        # ── Serial Numbers (bulk insert, skip duplicates) ─────────────────────
        existing_serials = {
            row[0]
            for row in db.execute(
                _text("SELECT serial FROM serial_numbers WHERE serial = ANY(:serials)"),
                {"serials": [s for gdata in groups.values() for s, _ in gdata.serials]},
            )
        }

        sn_rows = []
        serials_skipped = []
        for key, gdata in groups.items():
            prod = product_map[key]
            for serial, location in gdata.serials:
                if serial in existing_serials:
                    serials_skipped.append(serial)
                    continue
                existing_serials.add(serial)  # prevent within-file dupes
                sn_rows.append(SerialNumber(
                    serial=serial,
                    product_id=prod.id,
                    status='available',
                    purchase_date=purchase_date.date() if hasattr(purchase_date, 'date') else purchase_date,
                    purchase_id=db_purchase.id,
                    supplier_id=supplier.id,
                    branch_id=resolved_branch_id,
                    location=location or None,
                ))

        if sn_rows:
            db.add_all(sn_rows)

        db.commit()

        return {
            "success": True,
            "purchase_id": db_purchase.id,
            "reference": db_purchase.reference,
            "products_found": products_found,
            "products_created": products_created,
            "serials_imported": len(sn_rows),
            "serials_skipped_duplicates": len(serials_skipped),
            "total_rows_read": total_rows,
            "row_errors": row_errors,
            "journal_entries": je_status,
            "message": (
                f"Import complete: {products_created} new product(s), {products_found} existing. "
                f"{len(sn_rows)} serials imported"
                + (f", {len(serials_skipped)} duplicate(s) skipped" if serials_skipped else "")
                + f". Purchase ref: {db_purchase.reference}."
            ),
        }

    except HTTPException:
        raise
    except Exception as e:
        db.rollback()
        raise HTTPException(status_code=500, detail=f"Import failed: {str(e)}")


@router.post("/products/purchase-import")
async def import_stock_purchase_from_excel(
    file: UploadFile = File(...),
    # Optional form fields — when provided, bypass the fixed template format
    supplier_id: Optional[str] = Form(None),
    purchase_date_str: Optional[str] = Form(None),
    reference: Optional[str] = Form(None),
    invoice_number: Optional[str] = Form(None),
    notes_hdr_form: Optional[str] = Form(None),
    column_map: Optional[str] = Form(None),
    db: Session = Depends(get_db),
    company_id: Optional[str] = Depends(get_current_company_id),
):
    """Import a stock purchase from Excel or CSV. Creates a purchase record with journal entries
    (Dr. Inventory, Cr. Accounts Payable), updates inventory quantities, and registers
    serial numbers. The purchase will appear in the Purchases module.

    Two modes:
    - Template mode (no column_map): requires 'Purchase Info' + 'Items' sheets in the Excel file.
    - Flexible mode (column_map provided): any Excel/CSV file; supplier_id and purchase_date
      are passed as form fields; column_map JSON maps file columns to item fields.
    """
    from app.models.purchases import Purchase, PurchaseItem, Supplier
    from app.models.inventory import SerialNumber
    from app.services.ifrs_accounting_service import IFRSAccountingService
    from app.services.inventory_service import InventoryService
    from app.models.branch import Branch
    from app.models.user import User
    from decimal import InvalidOperation
    import json as _json

    filename_lower = file.filename.lower()
    if not filename_lower.endswith(('.xlsx', '.xls', '.csv')):
        raise HTTPException(status_code=400, detail="Only .xlsx, .xls, and .csv files are supported")

    try:
        content = await file.read()

        def _s(v): return str(v).strip() if v is not None else ''
        def _parse_date(v):
            if v is None: return datetime.utcnow()
            if hasattr(v, 'year'): return datetime(v.year, v.month, v.day)
            for fmt in ('%Y-%m-%d', '%d/%m/%Y', '%m/%d/%Y', '%d-%m-%Y'):
                try: return datetime.strptime(str(v).strip(), fmt)
                except: pass
            return datetime.utcnow()
        def _dec(v, default=Decimal('0')):
            if v is None: return default
            try: return Decimal(str(v))
            except InvalidOperation: return default

        # ── Flexible mode (column_map provided) ──────────────────────────────
        if column_map:
            try:
                cm = _json.loads(column_map)
            except Exception:
                raise HTTPException(status_code=400, detail="Invalid column_map JSON")

            # Resolve supplier from form field
            if not supplier_id:
                raise HTTPException(status_code=400, detail="supplier_id is required in flexible import mode")
            supplier = db.query(Supplier).filter(Supplier.id == supplier_id, Supplier.active == True).first()
            if not supplier:
                raise HTTPException(status_code=400, detail=f"Supplier '{supplier_id}' not found")

            purchase_date = _parse_date(purchase_date_str)
            notes_hdr = notes_hdr_form or f'Imported from {file.filename} on {datetime.utcnow().strftime("%Y-%m-%d")}'

            # Read the file (Excel or CSV)
            sheet_name = cm.get('sheet', '')
            header_row_idx = int(cm.get('header_row', 0))
            data_start_idx = int(cm.get('data_start_row', 1))
            mappings = cm.get('mappings', {})

            if filename_lower.endswith('.csv'):
                import csv as _csv, io as _io
                text = content.decode('utf-8-sig', errors='replace')
                all_rows = [row for row in _csv.reader(_io.StringIO(text))]
            else:
                wb = load_workbook(BytesIO(content), data_only=True)
                if sheet_name not in wb.sheetnames:
                    sheet_name = wb.sheetnames[0]
                all_rows = [[str(v).strip() if v is not None else '' for v in r]
                            for r in wb[sheet_name].iter_rows(values_only=True)]

            if header_row_idx >= len(all_rows):
                raise HTTPException(status_code=400, detail="Header row index out of range")

            headers = [str(v).strip() for v in all_rows[header_row_idx]]
            col_idx_map = {h: i for i, h in enumerate(headers) if h}

            def gv(row, field):
                col = mappings.get(field)
                if not col: return None
                idx = col_idx_map.get(col)
                return row[idx].strip() if idx is not None and idx < len(row) else None

            data_rows_flex = all_rows[data_start_idx:]
            line_items = []
            item_errors = []
            total_ex_vat = Decimal('0')
            total_vat = Decimal('0')

            # Pre-load all products into lookup dicts — avoids 1500 individual queries
            _all_products = db.query(Product).all()
            _sku_map = {p.sku: p for p in _all_products if p.sku}
            _name_map = {p.name.lower(): p for p in _all_products if p.name}

            for row_idx, row in enumerate(data_rows_flex, start=data_start_idx + 1):
                if not any(v for v in row if str(v).strip()):
                    continue
                try:
                    sku = gv(row, 'sku') or gv(row, 'barcode') or ''
                    description_raw = gv(row, 'description') or ''

                    product = None
                    if sku:
                        product = _sku_map.get(sku)
                        if not product:
                            # try barcode column separately if not already tried
                            barcode_val = gv(row, 'barcode') or ''
                            if barcode_val and barcode_val != sku:
                                product = _sku_map.get(barcode_val)
                        if not product:
                            # try case-insensitive name match
                            sku_lower = sku.lower()
                            product = next((p for k, p in _name_map.items() if sku_lower in k or k in sku_lower), None)

                    qty_raw = gv(row, 'quantity') or '0'
                    cost_raw = gv(row, 'cost') or '0'
                    quantity = _dec(qty_raw, Decimal('0'))
                    cost = _dec(cost_raw, product.cost_price if product else Decimal('0'))

                    # Use SKU or a row placeholder as description fallback so no row is silently dropped
                    if not description_raw:
                        if product:
                            description_raw = product.name
                        elif sku:
                            description_raw = sku
                        else:
                            description_raw = f'Item row {row_idx}'

                    serials_raw = gv(row, 'serial_numbers') or ''
                    vat_rate = _dec(gv(row, 'vat_rate'), Decimal('0'))
                    category_val = gv(row, 'category') or (product.category if product else None)
                    brand_val = gv(row, 'brand') or (product.brand if product else None)
                    condition_val = gv(row, 'condition') or (product.condition if product else None)
                    location_val = gv(row, 'location') or (product.location if product else None)

                    line_total = quantity * cost
                    line_vat = line_total * (vat_rate / Decimal('100'))
                    total_ex_vat += line_total
                    total_vat += line_vat

                    serials = [s.strip() for s in serials_raw.split(',') if s.strip()]
                    line_items.append({
                        'product_id': product.id if product else None,
                        'description': description_raw,
                        'category': category_val,
                        'brand': brand_val,
                        'condition': condition_val,
                        'location': location_val,
                        'quantity': quantity,
                        'cost': cost,
                        'total_cost': line_total,
                        'vat_rate': vat_rate,
                        'vat_amount': line_vat,
                        'notes': None,
                        'serials': serials,
                    })
                except Exception as re:
                    item_errors.append({"row": row_idx, "sku": sku if 'sku' in dir() else '', "error": str(re)})

            if not line_items:
                detail = "No valid item rows found."
                if item_errors:
                    detail += f" Errors: {'; '.join(e['error'] for e in item_errors[:3])}"
                raise HTTPException(status_code=400, detail=detail)

        # ── Template mode (no column_map) ────────────────────────────────────
        else:
            if filename_lower.endswith('.csv'):
                raise HTTPException(status_code=400, detail="CSV is only supported in flexible mode (use the column mapping import wizard).")
            wb = load_workbook(BytesIO(content), data_only=True)
            if not {'Purchase Info', 'Items'}.issubset(set(wb.sheetnames)):
                raise HTTPException(
                    status_code=400,
                    detail="File must have 'Purchase Info' and 'Items' sheets. Download the purchase import template, "
                           "or use 'Import Purchases from File' which supports any Excel/CSV with column mapping."
                )

            # --- Parse Purchase Info (row 3) ---
            pi_rows = list(wb["Purchase Info"].iter_rows(values_only=True))
            if len(pi_rows) < 3 or all(v is None or str(v).strip() == '' for v in pi_rows[2]):
                raise HTTPException(status_code=400, detail="Purchase Info sheet: fill in row 3 with your purchase details.")

            info = pi_rows[2]
            supplier_ref   = _s(info[0]) if len(info) > 0 else ''
            purchase_date  = _parse_date(info[1] if len(info) > 1 else None)
            reference      = _s(info[2]) if len(info) > 2 else ''
            invoice_number = _s(info[3]) if len(info) > 3 else ''
            notes_hdr      = _s(info[4]) if len(info) > 4 else ''

            if not supplier_ref:
                raise HTTPException(status_code=400, detail="Supplier Name or ID is required in Purchase Info sheet, row 3.")

            supplier = (
                db.query(Supplier).filter(Supplier.id == supplier_ref, Supplier.active == True).first()
                or db.query(Supplier).filter(Supplier.name.ilike(f'%{supplier_ref}%'), Supplier.active == True).first()
            )
            if not supplier:
                raise HTTPException(status_code=400, detail=f"Supplier '{supplier_ref}' not found. Check the name matches a supplier in the system.")

            # --- Parse Items (rows 3+) ---
            item_rows = list(wb["Items"].iter_rows(values_only=True))
            if len(item_rows) < 3:
                raise HTTPException(status_code=400, detail="Items sheet has no data rows (data starts at row 3).")

            line_items = []
            item_errors = []
            total_ex_vat = Decimal('0')
            total_vat    = Decimal('0')

            # Pre-load all products by SKU to avoid N+1 queries
            _all_products_tmpl = db.query(Product).all()
            _sku_map_tmpl = {p.sku: p for p in _all_products_tmpl if p.sku}

            for row_idx, row in enumerate(item_rows[2:], start=3):
                if all(v is None or str(v).strip() == '' for v in row):
                    continue
                try:
                    sku = _s(row[0]) if len(row) > 0 else ''
                    if not sku:
                        continue
                    product = _sku_map_tmpl.get(sku)
                    if not product:
                        item_errors.append({"row": row_idx, "sku": sku, "error": f"Product SKU '{sku}' not found"})
                        continue
                    description = _s(row[1]) if len(row) > 1 and row[1] else product.name
                    quantity    = _dec(row[2] if len(row) > 2 else None, Decimal('1'))
                    cost        = _dec(row[3] if len(row) > 3 else None, product.cost_price or Decimal('0'))
                    vat_rate    = _dec(row[4] if len(row) > 4 else None, Decimal('0'))
                    serials_raw = _s(row[5]) if len(row) > 5 and row[5] else ''
                    batch       = _s(row[6]) if len(row) > 6 and row[6] else ''
                    expiry      = _s(row[7]) if len(row) > 7 and row[7] else ''
                    item_notes  = _s(row[8]) if len(row) > 8 and row[8] else ''

                    line_total  = quantity * cost
                    line_vat    = line_total * (vat_rate / Decimal('100'))
                    total_ex_vat += line_total
                    total_vat    += line_vat

                    serials = [s.strip() for s in serials_raw.split(',') if s.strip()] if serials_raw else []
                    extra_note = ' | '.join(filter(None, [f"Batch:{batch}" if batch else '', f"Expiry:{expiry}" if expiry else '']))
                    line_items.append({
                        'product_id':  product.id,
                        'description': description,
                        'quantity':    quantity,
                        'cost':        cost,
                        'total_cost':  line_total,
                        'vat_rate':    vat_rate,
                        'vat_amount':  line_vat,
                        'notes':       f"{item_notes} {extra_note}".strip() or None,
                        'serials':     serials,
                        'supplier_id': supplier.id,
                        'purchase_date': purchase_date,
                    })
                except Exception as re:
                    item_errors.append({"row": row_idx, "sku": _s(row[0]) if row else '', "error": str(re)})

            if not line_items:
                detail = "No valid items found in the Items sheet."
                if item_errors:
                    detail += f" Errors: {item_errors}"
                raise HTTPException(status_code=400, detail=detail)

            notes_hdr = notes_hdr

        # ── Common: validate items found ─────────────────────────────────────
        if not line_items:
            detail = "No valid item rows found."
            if item_errors:
                detail += f" Errors: {'; '.join(e['error'] for e in item_errors[:3])}"
            raise HTTPException(status_code=400, detail=detail)

        grand_total = total_ex_vat + total_vat

        # System defaults
        _def_branch_q = db.query(Branch).filter(Branch.active == True)
        if company_id:
            _def_branch_q = _def_branch_q.filter(Branch.company_id == company_id)
        default_branch = _def_branch_q.first()
        branch_id = default_branch.id if default_branch else None
        system_user = db.query(User).filter(User.is_active == True).first()
        created_by = system_user.id if system_user else None

        # --- Create Purchase ---
        db_purchase = Purchase(
            supplier_id=supplier.id,
            purchase_date=purchase_date,
            total_amount=grand_total,
            total_vat_amount=total_vat,
            total_amount_ex_vat=total_ex_vat,
            status='pending',
            reference=reference or None,
            supplier_invoice_number=invoice_number or None,
            notes=notes_hdr or f'Imported from Excel on {datetime.utcnow().strftime("%Y-%m-%d")}',
            branch_id=branch_id,
            created_by=created_by,
        )
        db.add(db_purchase)
        db.flush()

        # --- Create Purchase Items (bulk add, single flush) ---
        created_item_serials = []   # list of (db_item, serials_list)
        db_items_pending = []
        for item in line_items:
            serials = item.pop('serials')
            item.pop('supplier_id', None)
            item.pop('purchase_date', None)
            db_item = PurchaseItem(
                purchase_id=db_purchase.id,
                product_id=item['product_id'],
                description=item['description'],
                category=item.get('category'),
                brand=item.get('brand'),
                condition=item.get('condition'),
                location=item.get('location'),
                quantity=item['quantity'],
                cost=item['cost'],
                total_cost=item['total_cost'],
                vat_rate=item['vat_rate'],
                vat_amount=item['vat_amount'],
                notes=item['notes'],
                is_inventory=True,
            )
            db.add(db_item)
            db_items_pending.append((db_item, serials))

        # Single flush assigns all IDs at once instead of 1500 round-trips
        db.flush()
        created_item_serials = db_items_pending

        db.commit()
        db.refresh(db_purchase)

        # --- Journal Entries ---
        je_status = "posted"
        try:
            ifrs_service = IFRSAccountingService(db)
            ifrs_service.create_purchase_journal_entries(db_purchase, created_by_user_id=created_by)
            db_purchase.posting_status = 'posted'
            db_purchase.posted_by = created_by
            db.commit()
        except Exception as je_err:
            je_status = f"warning: {je_err}"
            print(f"[WARN] Journal entries failed for purchase import {db_purchase.id}: {je_err}")

        # --- Inventory Updates & Serial Numbers ---
        # --- Inventory Updates (bulk) ---
        # Accumulate quantity deltas per product, then apply in one SQL UPDATE per product
        # and one bulk insert for all InventoryTransaction rows. This replaces 1500 individual
        # commits with a single commit at the end.
        from app.models.inventory import InventoryTransaction
        from sqlalchemy import text as _text

        qty_delta: dict = {}   # product_id -> total quantity change
        for db_item, _ in created_item_serials:
            if db_item.product_id:
                pid = db_item.product_id
                qty_delta[pid] = qty_delta.get(pid, 0) + int(db_item.quantity)

        if qty_delta:
            # Bulk-update product quantities in one statement per product (still fast — ~1-10 products in most cases, or many but each is a single indexed UPDATE)
            db.execute(
                _text("""
                    UPDATE products
                    SET quantity = GREATEST(quantity + :delta, 0)
                    WHERE id = :pid
                """),
                [{"delta": delta, "pid": pid} for pid, delta in qty_delta.items()]
            )

            # Bulk insert InventoryTransaction rows — one add_all, one flush
            ref_label = f"Purchase import {db_purchase.reference or db_purchase.id}"
            txn_rows = [
                InventoryTransaction(
                    product_id=db_item.product_id,
                    transaction_type='goods_receipt',
                    quantity=abs(int(db_item.quantity)),
                    unit_cost=db_item.cost,
                    date=datetime.utcnow().date(),
                    reference=ref_label,
                    branch_id=branch_id,
                    related_purchase_id=db_purchase.id,
                )
                for db_item, _ in created_item_serials
                if db_item.product_id
            ]
            db.add_all(txn_rows)

        # Serial numbers (usually none for bulk stock imports)
        sn_serials = set()
        sn_rows = []
        for db_item, serials in created_item_serials:
            for serial in serials:
                if serial and serial not in sn_serials:
                    sn_serials.add(serial)
                    sn_rows.append(SerialNumber(
                        serial=serial,
                        product_id=db_item.product_id,
                        status='available',
                        purchase_date=purchase_date.date() if hasattr(purchase_date, 'date') else purchase_date,
                        purchase_id=db_purchase.id,
                        supplier_id=supplier.id,
                        branch_id=branch_id,
                    ))
        if sn_rows:
            db.add_all(sn_rows)

        db.commit()

        return {
            "success": True,
            "purchase_id": db_purchase.id,
            "reference": db_purchase.reference,
            "items_count": len(created_item_serials),
            "total_amount": float(grand_total),
            "journal_entries": je_status,
            "item_errors": item_errors,
            "message": (
                f"Purchase imported: {len(created_item_serials)} items, "
                f"total {float(grand_total):.2f}. "
                f"Journal entries: {je_status}."
                + (f" {len(item_errors)} item(s) skipped." if item_errors else "")
            ),
        }
    except HTTPException:
        raise
    except Exception as e:
        db.rollback()
        raise HTTPException(status_code=500, detail=f"Error processing purchase import: {str(e)}")


@router.get("/products/{product_id}", response_model=ProductResponse)
async def get_product(
    product_id: str,
    db: Session = Depends(get_db),
    include_image_info: bool = Query(False, description="Include detailed image information")
):
    """Get product by ID with optional image details"""
    try:
        product = db.query(Product).filter(Product.id == product_id).first()
        if not product:
            raise HTTPException(status_code=404, detail="Product not found")

        response_data = product.__dict__.copy()

        # Add image information if requested
        if include_image_info and product.image_url:
            file_service = FileService()
            image_info = file_service.get_image_info(product.image_url)
            if image_info:
                response_data["image_info"] = image_info

        return response_data
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Error fetching product: {str(e)}")


@router.post("/products", response_model=ProductResponse)
async def create_product(
    product: ProductCreate,
    db: Session = Depends(get_db),
    # current_user parameter removed for development)
):
    """Create a new product"""
    try:
        # Check if SKU already exists
        existing_product = db.query(Product).filter(Product.sku == product.sku).first()
        if existing_product:
            raise HTTPException(status_code=400, detail="Product with this SKU already exists")

        product_data = apply_non_inventory_defaults(product.model_dump())
        db_product = Product(**product_data)
        db.add(db_product)
        db.commit()
        db.refresh(db_product)
        return db_product
    except HTTPException:
        raise
    except IntegrityError as e:
        db.rollback()
        # Parse the constraint error to provide user-friendly message
        error_msg = str(e.orig)
        if 'products_sku_key' in error_msg or 'duplicate key' in error_msg.lower():
            raise HTTPException(
                status_code=400,
                detail=f"A product with SKU '{product.sku}' already exists. Please use a unique SKU."
            )
        elif 'unique constraint' in error_msg.lower():
            raise HTTPException(status_code=400, detail="A product with this information already exists")
        else:
            raise HTTPException(status_code=400, detail=f"Database constraint violation: {error_msg}")
    except Exception as e:
        db.rollback()
        raise HTTPException(status_code=500, detail=f"Error creating product: {str(e)}")


@router.put("/products/{product_id}", response_model=ProductResponse)
async def update_product(
    product_id: str,
    product: ProductUpdate,
    db: Session = Depends(get_db),
    # current_user parameter removed for development)
):
    """Update a product.

    WARNING: If updating quantity, this will change it directly without creating
    inventory transactions or journal entries. Use the /adjustments endpoint instead
    for proper inventory adjustments with accounting entries.
    """
    try:
        db_product = db.query(Product).filter(Product.id == product_id).first()
        if not db_product:
            raise HTTPException(status_code=404, detail="Product not found")
        # Branch scope
        # require_branch_match(..., current_user)  # Removed for development

        update_data = product.dict(exclude_unset=True)
        effective_type = update_data.get("product_type", db_product.product_type)
        if not is_inventory_tracked({"product_type": effective_type}):
            merged = {**update_data, "product_type": normalize_product_type(effective_type)}
            update_data = {k: v for k, v in apply_non_inventory_defaults(merged).items() if k in update_data or k in {
                "product_type", "quantity", "minimum_stock_level", "reorder_point",
                "maximum_stock_level", "is_serialized", "is_weight_based", "is_perishable",
            }}
        elif "product_type" in update_data:
            update_data["product_type"] = normalize_product_type(update_data["product_type"])

        if 'quantity' in update_data and update_data['quantity'] != db_product.quantity:
            # Log warning about direct quantity change
            print(f"️  WARNING: Direct quantity change for product {db_product.name}")
            print(f"   Old: {db_product.quantity}, New: {update_data['quantity']}")
            print(f"   Consider using /adjustments endpoint for proper accounting")

        # Update fields
        for field, value in update_data.items():
            setattr(db_product, field, value)

        db.commit()
        db.refresh(db_product)
        return db_product
    except HTTPException:
        raise
    except IntegrityError as e:
        db.rollback()
        # Parse the constraint error to provide user-friendly message
        error_msg = str(e.orig)
        if 'products_sku_key' in error_msg or 'duplicate key' in error_msg.lower():
            # Extract the SKU from update_data if it exists
            sku = update_data.get('sku', db_product.sku)
            raise HTTPException(
                status_code=400,
                detail=f"A product with SKU '{sku}' already exists. Please use a unique SKU."
            )
        elif 'unique constraint' in error_msg.lower():
            raise HTTPException(status_code=400, detail="A product with this information already exists")
        else:
            raise HTTPException(status_code=400, detail=f"Database constraint violation: {error_msg}")
    except Exception as e:
        db.rollback()
        raise HTTPException(status_code=500, detail=f"Error updating product: {str(e)}")


@router.delete("/products/{product_id}")
async def delete_product(product_id: str, db: Session = Depends(get_db)):  # current_user parameter removed for development
    """Delete a product and its associated image"""
    try:
        product = db.query(Product).filter(Product.id == product_id).first()
        if not product:
            raise HTTPException(status_code=404, detail="Product not found")
        # require_branch_match(..., current_user)  # Removed for development

        # Delete associated image if it exists
        if product.image_url:
            file_service = FileService()
            file_service.delete_product_image(product.image_url)

        db.delete(product)
        db.commit()
        return {"message": "Product deleted successfully"}
    except HTTPException:
        raise
    except Exception as e:
        db.rollback()
        raise HTTPException(status_code=500, detail=f"Error deleting product: {str(e)}")


@router.post("/products/{product_id}/image")
async def upload_product_image(
    product_id: str,
    file: UploadFile = File(...),
    create_thumbnail: bool = Query(True, description="Create thumbnail version"),
    resize_image: bool = Query(True, description="Resize image to standard dimensions"),
    db: Session = Depends(get_db),
    # current_user parameter removed for development)
):
    """Upload an image for a product with optional processing"""
    try:
        # Check if product exists
        product = db.query(Product).filter(Product.id == product_id).first()
        if not product:
            raise HTTPException(status_code=404, detail="Product not found")
        # require_branch_match(..., current_user)  # Removed for development

        # Initialize file service
        file_service = FileService()

        # Upload image
        image_url = await file_service.upload_product_image(file, product_id)

        # Process image if requested
        thumbnail_url = None
        if resize_image:
            file_service.resize_image(image_url, max_width=800, max_height=600)

        if create_thumbnail:
            thumbnail_url = file_service.create_thumbnail(image_url, size=(150, 150))
            # Store thumbnail URL in a separate field or metadata if needed

        # Update product with image URL
        product.image_url = image_url
        db.commit()

        return {
            "success": True,
            "image_url": image_url,
            "thumbnail_url": thumbnail_url,
            "message": "Product image uploaded successfully"
        }

    except HTTPException:
        raise
    except Exception as e:
        db.rollback()
        raise HTTPException(status_code=500, detail=f"Error uploading product image: {str(e)}")


@router.delete("/products/{product_id}/image")
async def delete_product_image(
    product_id: str,
    db: Session = Depends(get_db),
    # current_user parameter removed for development)
):
    """Delete a product image"""
    try:
        # Check if product exists
        product = db.query(Product).filter(Product.id == product_id).first()
        if not product:
            raise HTTPException(status_code=404, detail="Product not found")
        # require_branch_match(..., current_user)  # Removed for development

        if not product.image_url:
            raise HTTPException(status_code=404, detail="Product has no image")

        # Initialize file service
        file_service = FileService()

        # Delete image file
        if file_service.delete_product_image(product.image_url):
            # Clear image URL from product
            product.image_url = None
            db.commit()

            return {
                "success": True,
                "message": "Product image deleted successfully"
            }
        else:
            raise HTTPException(status_code=500, detail="Failed to delete image file")

    except HTTPException:
        raise
    except Exception as e:
        db.rollback()
        raise HTTPException(status_code=500, detail=f"Error deleting product image: {str(e)}")


@router.get("/products/{product_id}/image")
async def get_product_image_info(
    product_id: str,
    db: Session = Depends(get_db)
):
    """Get information about a product's image"""
    try:
        # Check if product exists
        product = db.query(Product).filter(Product.id == product_id).first()
        if not product:
            raise HTTPException(status_code=404, detail="Product not found")

        if not product.image_url:
            raise HTTPException(status_code=404, detail="Product has no image")

        # Initialize file service
        file_service = FileService()

        # Get image information
        image_info = file_service.get_image_info(product.image_url)

        if image_info:
            return {
                "success": True,
                "image_url": product.image_url,
                "image_info": image_info
            }
        else:
            raise HTTPException(status_code=404, detail="Image file not found")

    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Error getting image info: {str(e)}")


@router.post("/products/{product_id}/image/resize")
async def resize_product_image(
    product_id: str,
    max_width: int = Query(800, description="Maximum width"),
    max_height: int = Query(600, description="Maximum height"),
    db: Session = Depends(get_db),
    # current_user parameter removed for development)
):
    """Resize a product image"""
    try:
        # Check if product exists
        product = db.query(Product).filter(Product.id == product_id).first()
        if not product:
            raise HTTPException(status_code=404, detail="Product not found")

        if not product.image_url:
            raise HTTPException(status_code=404, detail="Product has no image")

        # Initialize file service
        file_service = FileService()

        # Resize image
        success = file_service.resize_image(product.image_url, max_width, max_height)

        if success:
            return {
                "success": True,
                "message": "Image resized successfully",
                "new_dimensions": {"max_width": max_width, "max_height": max_height}
            }
        else:
            raise HTTPException(status_code=500, detail="Failed to resize image")

    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Error resizing image: {str(e)}")


@router.post("/products/{product_id}/image/thumbnail")
async def create_product_thumbnail(
    product_id: str,
    width: int = Query(150, description="Thumbnail width"),
    height: int = Query(150, description="Thumbnail height"),
    db: Session = Depends(get_db),
    # current_user parameter removed for development)
):
    """Create a thumbnail for a product image"""
    try:
        # Check if product exists
        product = db.query(Product).filter(Product.id == product_id).first()
        if not product:
            raise HTTPException(status_code=404, detail="Product not found")

        if not product.image_url:
            raise HTTPException(status_code=404, detail="Product has no image")

        # Initialize file service
        file_service = FileService()

        # Create thumbnail
        thumbnail_url = file_service.create_thumbnail(product.image_url, (width, height))

        if thumbnail_url:
            return {
                "success": True,
                "thumbnail_url": thumbnail_url,
                "dimensions": {"width": width, "height": height}
            }
        else:
            raise HTTPException(status_code=500, detail="Failed to create thumbnail")

    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Error creating thumbnail: {str(e)}")


@router.post("/products/{product_id}/image/optimize")
async def optimize_product_image(
    product_id: str,
    quality: int = Query(85, ge=1, le=100, description="JPEG quality (1-100)"),
    db: Session = Depends(get_db),
    # current_user parameter removed for development)
):
    """Optimize a product image for web use"""
    try:
        # Check if product exists
        product = db.query(Product).filter(Product.id == product_id).first()
        if not product:
            raise HTTPException(status_code=404, detail="Product not found")

        if not product.image_url:
            raise HTTPException(status_code=404, detail="Product has no image")

        # Initialize file service
        file_service = FileService()

        # Optimize image
        success = file_service.optimize_image(product.image_url, quality)

        if success:
            return {
                "success": True,
                "message": "Image optimized successfully",
                "quality": quality
            }
        else:
            raise HTTPException(status_code=500, detail="Failed to optimize image")

    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Error optimizing image: {str(e)}")


@router.post("/products/{product_id}/image/watermark")
async def add_watermark_to_image(
    product_id: str,
    watermark_text: str = Query("CNPERP", description="Watermark text"),
    db: Session = Depends(get_db),
    # current_user parameter removed for development)
):
    """Add a watermark to a product image"""
    try:
        # Check if product exists
        product = db.query(Product).filter(Product.id == product_id).first()
        if not product:
            raise HTTPException(status_code=404, detail="Product not found")

        if not product.image_url:
            raise HTTPException(status_code=404, detail="Product has no image")

        # Initialize file service
        file_service = FileService()

        # Add watermark
        success = file_service.add_watermark(product.image_url, watermark_text)

        if success:
            return {
                "success": True,
                "message": "Watermark added successfully",
                "watermark_text": watermark_text
            }
        else:
            raise HTTPException(status_code=500, detail="Failed to add watermark")

    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Error adding watermark: {str(e)}")


@router.get("/images/statistics")
async def get_image_statistics():
    """Get statistics about uploaded product images"""
    try:
        file_service = FileService()
        stats = file_service.get_image_statistics()

        return {
            "success": True,
            "statistics": stats
        }

    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Error getting image statistics: {str(e)}")


@router.get("/products/categories")
async def get_product_categories(
    db: Session = Depends(get_db),
    company_id: Optional[str] = Depends(get_current_company_id),
):
    """Get all product categories"""
    try:
        query = db.query(Product.category).filter(
            Product.category.isnot(None),
            Product.active == True
        )
        if company_id:
            branch_ids_sq = db.query(Branch.id).filter(Branch.company_id == company_id)
            query = query.filter(Product.branch_id.in_(branch_ids_sq))
        categories = query.distinct().all()

        return [{"category": cat[0]} for cat in categories if cat[0]]
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Error fetching categories: {str(e)}")


@router.get("/transactions")
async def get_inventory_transactions():
    """Get inventory transactions"""
    return {"message": "Inventory transactions endpoint - to be implemented"}


@router.get("/adjustments", response_model=List[InventoryAdjustmentResponse])
async def get_inventory_adjustments(
    product_id: Optional[str] = None,
    branch_id: Optional[str] = None,
    limit: int = 100,
    db: Session = Depends(get_db),
    company_id: Optional[str] = Depends(get_current_company_id),
):
    """Get inventory adjustments with optional filters"""
    try:
        query = db.query(InventoryAdjustment)

        if product_id:
            query = query.filter(InventoryAdjustment.product_id == product_id)

        if branch_id:
            query = query.filter(InventoryAdjustment.branch_id == branch_id)
        elif company_id:
            branch_ids_sq = db.query(Branch.id).filter(Branch.company_id == company_id)
            query = query.filter(InventoryAdjustment.branch_id.in_(branch_ids_sq))

        adjustments = query.order_by(InventoryAdjustment.adjustment_date.desc()).limit(limit).all()
        return adjustments

    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Error fetching adjustments: {str(e)}")


@router.post("/adjustments", response_model=InventoryAdjustmentResponse)
async def create_inventory_adjustment(
    adjustment: InventoryAdjustmentCreate,
    db: Session = Depends(get_db)
):
    """Create an inventory adjustment with proper journal entries"""
    try:
        from app.models.accounting import AccountingEntry, JournalEntry, AccountingCode
        from datetime import date

        # Get the product
        product = db.query(Product).filter(Product.id == adjustment.product_id).first()
        if not product:
            raise HTTPException(status_code=404, detail="Product not found")

        # Store previous quantity
        previous_quantity = product.quantity

        # Determine transaction type and validate
        if adjustment.quantity_change > 0:
            transaction_type = 'opening_stock' if adjustment.adjustment_type == 'opening_stock' else 'return'
        else:
            if adjustment.adjustment_type == 'damage':
                transaction_type = 'damage'
            elif adjustment.adjustment_type == 'theft':
                transaction_type = 'theft'
            else:
                transaction_type = 'adjustment'

        # Use inventory service to update quantity and create transaction
        inventory_service = InventoryService(db)
        success, message = inventory_service.update_product_quantity(
            product_id=adjustment.product_id,
            quantity_change=abs(adjustment.quantity_change),
            transaction_type=transaction_type,
            reference=f"ADJ-{adjustment.adjustment_type.upper()}",
            branch_id=adjustment.branch_id or product.branch_id,
            note=f"{adjustment.reason}. {adjustment.notes or ''}"
        )

        if not success:
            raise HTTPException(status_code=400, detail=message)

        # Refresh product to get new quantity
        db.refresh(product)
        new_quantity = product.quantity

        # Create accounting entry for the adjustment
        adjustment_date = adjustment.adjustment_date or date.today()
        total_amount = abs(adjustment.quantity_change) * (product.cost_price or 0)

        # Use product's branch or find first available branch
        branch_id_to_use = adjustment.branch_id or product.branch_id
        if not branch_id_to_use:
            from app.models.branch import Branch
            first_branch = db.query(Branch).first()
            if first_branch:
                branch_id_to_use = first_branch.id

        accounting_entry = AccountingEntry(
            date_prepared=adjustment_date,
            date_posted=adjustment_date,
            particulars=f"Inventory Adjustment - {adjustment.adjustment_type}: {product.name}",
            book=f"INV-ADJ-{adjustment.adjustment_type.upper()}",
            status='posted',
            branch_id=branch_id_to_use
        )
        db.add(accounting_entry)
        db.flush()

        # Get inventory account
        inventory_account = product.accounting_code
        if not inventory_account:
            # Try to find a default inventory account
            inventory_account = db.query(AccountingCode).filter(
                AccountingCode.account_type == 'Asset',
                AccountingCode.name.ilike('%inventory%')
            ).first()

        if inventory_account and total_amount > 0:
            # Create journal entries based on adjustment type
            if adjustment.quantity_change > 0:
                # Increase inventory: Dr. Inventory, Cr. Inventory Gain/Opening Stock
                journal_entry1 = JournalEntry(
                    accounting_entry_id=accounting_entry.id,
                    accounting_code_id=inventory_account.id,
                    entry_type='debit',
                    debit_amount=total_amount,
                    description=f"Inventory increase - {adjustment.reason}",
                    date=adjustment_date,
                    date_posted=adjustment_date,
                    branch_id=accounting_entry.branch_id
                )
                db.add(journal_entry1)

                # Credit side - find or use a gain account
                gain_account = db.query(AccountingCode).filter(
                    AccountingCode.account_type == 'Income',
                    AccountingCode.name.ilike('%inventory gain%')
                ).first()

                if not gain_account:
                    # Use other income account
                    gain_account = db.query(AccountingCode).filter(
                        AccountingCode.account_type == 'Income',
                        AccountingCode.name.ilike('%other income%')
                    ).first()

                if gain_account:
                    journal_entry2 = JournalEntry(
                        accounting_entry_id=accounting_entry.id,
                        accounting_code_id=gain_account.id,
                        entry_type='credit',
                        credit_amount=total_amount,
                        description=f"Inventory gain - {adjustment.reason}",
                        date=adjustment_date,
                        date_posted=adjustment_date,
                        branch_id=accounting_entry.branch_id
                    )
                    db.add(journal_entry2)
            else:
                # Decrease inventory: Dr. Loss/Expense, Cr. Inventory
                loss_account = None

                if adjustment.adjustment_type == 'damage':
                    loss_account = db.query(AccountingCode).filter(
                        AccountingCode.account_type == 'Expense',
                        AccountingCode.name.ilike('%damage%')
                    ).first()
                elif adjustment.adjustment_type == 'theft':
                    loss_account = db.query(AccountingCode).filter(
                        AccountingCode.account_type == 'Expense',
                        AccountingCode.name.ilike('%theft%')
                    ).first()

                if not loss_account:
                    # Use inventory loss or other expense
                    loss_account = db.query(AccountingCode).filter(
                        AccountingCode.account_type == 'Expense',
                        AccountingCode.name.ilike('%inventory loss%')
                    ).first()

                if not loss_account:
                    loss_account = db.query(AccountingCode).filter(
                        AccountingCode.account_type == 'Expense',
                        AccountingCode.name.ilike('%other expense%')
                    ).first()

                if loss_account:
                    journal_entry1 = JournalEntry(
                        accounting_entry_id=accounting_entry.id,
                        accounting_code_id=loss_account.id,
                        entry_type='debit',
                        debit_amount=total_amount,
                        description=f"Inventory loss - {adjustment.adjustment_type} - {adjustment.reason}",
                        date=adjustment_date,
                        date_posted=adjustment_date,
                        branch_id=accounting_entry.branch_id
                    )
                    db.add(journal_entry1)

                journal_entry2 = JournalEntry(
                    accounting_entry_id=accounting_entry.id,
                    accounting_code_id=inventory_account.id,
                    entry_type='credit',
                    credit_amount=total_amount,
                    description=f"Inventory reduction - {adjustment.reason}",
                    date=adjustment_date,
                    date_posted=adjustment_date,
                    branch_id=accounting_entry.branch_id
                )
                db.add(journal_entry2)

        # Create inventory adjustment record
        db_adjustment = InventoryAdjustment(
            product_id=adjustment.product_id,
            adjustment_date=adjustment_date,
            quantity=abs(adjustment.quantity_change),
            reason=adjustment.reason,
            total_amount=total_amount,
            accounting_entry_type='inventory_adjustment',
            accounting_entry_id=accounting_entry.id,
            branch_id=branch_id_to_use,
            adjustment_type=adjustment.adjustment_type,
            previous_quantity=previous_quantity,
            new_quantity=new_quantity,
            unit_cost=product.cost_price,
            notes=adjustment.notes
        )
        db.add(db_adjustment)

        db.commit()
        db.refresh(db_adjustment)

        return db_adjustment

    except HTTPException:
        raise
    except Exception as e:
        db.rollback()
        raise HTTPException(status_code=500, detail=f"Error creating adjustment: {str(e)}")


# ─────────────────────────────────────────────────────────────────────────────
#  PRODUCT TRACEABILITY
#  Lookup a product's full purchase chain by serial number, SKU/barcode,
#  batch number, or purchase date range.
# ─────────────────────────────────────────────────────────────────────────────

@router.get("/products/traceability")
async def trace_product(
    q: Optional[str] = Query(None, description="Serial number, SKU, or batch number to look up"),
    lookup_type: Optional[str] = Query(None, description="serial | sku | batch | date"),
    date_from: Optional[str] = Query(None),
    date_to: Optional[str]   = Query(None),
    db: Session = Depends(get_db)
):
    """
    Trace a product back to its supplier and purchase.
    - By serial number: returns which supplier it came from, which purchase, cost, location.
    - By SKU / barcode: returns all purchase history for that product across all suppliers.
    - By batch number: returns all serials in that batch and their purchase origins.
    - By date range: returns all purchases received in that window.
    """
    from app.models.inventory import SerialNumber
    from app.models.purchases import Purchase, PurchaseItem, Supplier
    from datetime import date as _date

    results = []

    try:
        # ── Serial number lookup ─────────────────────────────────────────────
        if (not lookup_type or lookup_type == 'serial') and q:
            query = (
                db.query(SerialNumber)
                .filter(SerialNumber.serial.ilike(f"%{q}%"))
                .order_by(SerialNumber.purchase_date.desc())
                .limit(50)
            )
            for sn in query.all():
                results.append(_serial_to_trace_dict(sn, db))

        # ── SKU / barcode lookup ─────────────────────────────────────────────
        if (not lookup_type or lookup_type == 'sku') and q and not results:
            prod = db.query(Product).filter(
                (Product.sku == q) | (Product.barcode == q) | (Product.sku.ilike(f"%{q}%"))
            ).first()
            if prod:
                sns = (
                    db.query(SerialNumber)
                    .filter(SerialNumber.product_id == prod.id)
                    .order_by(SerialNumber.purchase_date.desc())
                    .limit(200)
                    .all()
                )
                results = [_serial_to_trace_dict(sn, db) for sn in sns]

                # Also include purchase-item rows without serials
                pi_rows = (
                    db.query(PurchaseItem)
                    .filter(PurchaseItem.product_id == prod.id)
                    .order_by(PurchaseItem.id.desc())
                    .limit(50)
                    .all()
                )
                for pi in pi_rows:
                    results.append(_purchase_item_to_trace_dict(pi, db))

        # ── Auto: try batch if serial/sku found nothing ───────────────────────
        if (not lookup_type or lookup_type == 'auto') and q and not results:
            sns = (
                db.query(SerialNumber)
                .filter(SerialNumber.batch_number.ilike(f"%{q}%"))
                .order_by(SerialNumber.purchase_date.desc())
                .limit(200)
                .all()
            )
            results = [_serial_to_trace_dict(sn, db) for sn in sns]

        # ── Batch number lookup ──────────────────────────────────────────────
        if (lookup_type == 'batch') and q:
            sns = (
                db.query(SerialNumber)
                .filter(SerialNumber.batch_number.ilike(f"%{q}%"))
                .order_by(SerialNumber.purchase_date.desc())
                .limit(200)
                .all()
            )
            results = [_serial_to_trace_dict(sn, db) for sn in sns]

        # ── Date range lookup ────────────────────────────────────────────────
        if lookup_type == 'date':
            try:
                df = _date.fromisoformat(date_from) if date_from else None
                dt = _date.fromisoformat(date_to)   if date_to   else None
            except ValueError:
                raise HTTPException(status_code=400, detail="Invalid date format — use YYYY-MM-DD")
            pq = db.query(Purchase)
            if df:
                pq = pq.filter(Purchase.purchase_date >= df)
            if dt:
                pq = pq.filter(Purchase.purchase_date <= dt)
            purchases = pq.order_by(Purchase.purchase_date.desc()).limit(100).all()
            for pur in purchases:
                for pi in pur.purchase_items:
                    results.append(_purchase_item_to_trace_dict(pi, db))

        return {"results": results, "total": len(results)}

    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Traceability lookup error: {str(e)}")


def _serial_to_trace_dict(sn, db):
    """Convert a SerialNumber row into a traceability dict."""
    from app.models.purchases import Purchase, Supplier
    prod = sn.product
    supplier = sn.supplier
    purchase = sn.purchase
    if not supplier and purchase:
        supplier = purchase.supplier
    return {
        "type": "serial",
        "serial": sn.serial,
        "status": sn.status,
        "batch_number": sn.batch_number,
        "location": sn.location,
        "purchase_date": str(sn.purchase_date) if sn.purchase_date else (
            str(purchase.purchase_date) if purchase else None
        ),
        "product": {
            "id": str(prod.id) if prod else None,
            "name": prod.name if prod else None,
            "sku": prod.sku if prod else None,
            "barcode": prod.barcode if prod else None,
            "model": prod.model if prod else None,
            "brand": prod.brand if prod else None,
            "category": prod.category if prod else None,
        } if prod else None,
        "supplier": {
            "id": str(supplier.id) if supplier else None,
            "name": supplier.name if supplier else None,
            "email": supplier.email if supplier else None,
            "telephone": supplier.telephone if supplier else None,
        } if supplier else None,
        "purchase": {
            "id": str(purchase.id) if purchase else None,
            "reference": purchase.reference if purchase else None,
            "purchase_date": str(purchase.purchase_date) if purchase else None,
            "status": purchase.status if purchase else None,
            "total_amount": str(purchase.total_amount) if purchase else None,
        } if purchase else None,
        "branch": sn.branch.name if sn.branch else None,
    }


def _purchase_item_to_trace_dict(pi, db):
    """Convert a PurchaseItem row into a traceability dict."""
    prod = pi.product
    purchase = pi.purchase
    supplier = purchase.supplier if purchase else None
    return {
        "type": "purchase_item",
        "serial": None,
        "status": "received" if purchase and purchase.status else None,
        "batch_number": None,
        "location": pi.location if hasattr(pi, 'location') else None,
        "purchase_date": str(purchase.purchase_date) if purchase else None,
        "product": {
            "id": str(prod.id) if prod else None,
            "name": prod.name if prod else None,
            "sku": prod.sku if prod else None,
            "barcode": prod.barcode if prod else None,
            "model": prod.model if prod else None,
            "brand": prod.brand if prod else None,
            "category": prod.category if prod else None,
        } if prod else None,
        "supplier": {
            "id": str(supplier.id) if supplier else None,
            "name": supplier.name if supplier else None,
            "email": supplier.email if supplier else None,
            "telephone": supplier.telephone if supplier else None,
        } if supplier else None,
        "purchase": {
            "id": str(purchase.id) if purchase else None,
            "reference": purchase.reference if purchase else None,
            "purchase_date": str(purchase.purchase_date) if purchase else None,
            "status": purchase.status if purchase else None,
            "total_amount": str(purchase.total_amount) if purchase else None,
            "quantity": str(pi.quantity) if pi.quantity else None,
            "unit_cost": str(pi.cost) if pi.cost else None,
        } if purchase else None,
        "branch": purchase.branch.name if (purchase and purchase.branch) else None,
    }


@router.get("/products/{product_id}/supplier-history")
async def get_product_supplier_history(
    product_id: str,
    db: Session = Depends(get_db)
):
    """
    Return every supplier who has ever supplied this product,
    with purchase history per supplier.
    """
    from app.models.purchases import Purchase, PurchaseItem, Supplier
    from app.models.inventory import SerialNumber

    product = db.query(Product).filter(Product.id == product_id).first()
    if not product:
        raise HTTPException(status_code=404, detail="Product not found")

    # Aggregate purchase items for this product
    pi_rows = (
        db.query(PurchaseItem)
        .filter(PurchaseItem.product_id == product_id)
        .all()
    )

    # Build per-supplier summary
    supplier_map: dict = {}
    for pi in pi_rows:
        purchase = pi.purchase
        if not purchase:
            continue
        supplier = purchase.supplier
        if not supplier:
            continue
        sid = str(supplier.id)
        if sid not in supplier_map:
            supplier_map[sid] = {
                "supplier": {"id": sid, "name": supplier.name, "email": supplier.email},
                "purchases": [],
                "total_qty": 0,
                "total_cost": 0.0,
                "first_purchase": None,
                "last_purchase": None,
            }
        supplier_map[sid]["purchases"].append({
            "purchase_id": str(purchase.id),
            "reference": purchase.reference,
            "purchase_date": str(purchase.purchase_date) if purchase.purchase_date else None,
            "quantity": float(pi.quantity) if pi.quantity else 0,
            "unit_cost": float(pi.cost) if pi.cost else 0.0,
            "total_cost": float(pi.total_cost) if pi.total_cost else 0.0,
        })
        supplier_map[sid]["total_qty"] += float(pi.quantity or 0)
        supplier_map[sid]["total_cost"] += float(pi.total_cost or 0)
        pd_str = str(purchase.purchase_date) if purchase.purchase_date else None
        if pd_str:
            if not supplier_map[sid]["first_purchase"] or pd_str < supplier_map[sid]["first_purchase"]:
                supplier_map[sid]["first_purchase"] = pd_str
            if not supplier_map[sid]["last_purchase"] or pd_str > supplier_map[sid]["last_purchase"]:
                supplier_map[sid]["last_purchase"] = pd_str

    # Also include catalog entries (pre-tagged suppliers without purchase history yet)
    from app.models.inventory import ProductSupplierCatalog
    catalog_rows = (
        db.query(ProductSupplierCatalog)
        .filter(ProductSupplierCatalog.product_id == product_id, ProductSupplierCatalog.active == True)
        .all()
    )
    catalog_by_supplier = {str(c.supplier_id): c for c in catalog_rows}

    results = list(supplier_map.values())
    # Merge catalog info
    for r in results:
        c = catalog_by_supplier.get(r["supplier"]["id"])
        if c:
            r["catalog"] = {
                "supplier_sku": c.supplier_sku,
                "supplier_product_name": c.supplier_product_name,
                "unit_cost": float(c.unit_cost) if c.unit_cost else None,
                "lead_time_days": c.lead_time_days,
                "min_order_qty": float(c.minimum_order_qty) if c.minimum_order_qty else 1,
                "is_preferred": c.is_preferred,
                "notes": c.notes,
            }

    # Add catalog-only entries (supplier tagged but no purchase yet)
    purchased_supplier_ids = {r["supplier"]["id"] for r in results}
    for c in catalog_rows:
        if str(c.supplier_id) not in purchased_supplier_ids and c.supplier:
            results.append({
                "supplier": {"id": str(c.supplier.id), "name": c.supplier.name, "email": c.supplier.email},
                "purchases": [],
                "total_qty": 0,
                "total_cost": 0.0,
                "first_purchase": None,
                "last_purchase": None,
                "catalog": {
                    "supplier_sku": c.supplier_sku,
                    "supplier_product_name": c.supplier_product_name,
                    "unit_cost": float(c.unit_cost) if c.unit_cost else None,
                    "lead_time_days": c.lead_time_days,
                    "min_order_qty": float(c.minimum_order_qty) if c.minimum_order_qty else 1,
                    "is_preferred": c.is_preferred,
                    "notes": c.notes,
                },
            })

    return {
        "product": {"id": product.id, "name": product.name, "sku": product.sku, "barcode": product.barcode},
        "suppliers": results,
        "total_suppliers": len(results),
    }


# ─────────────────────────────────────────────────────────────────────────────
#  PRODUCT-SUPPLIER CATALOG CRUD
#  Tag a product to one or more suppliers with pricing and reference data.
# ─────────────────────────────────────────────────────────────────────────────

@router.get("/products/{product_id}/suppliers")
async def list_product_suppliers(product_id: str, db: Session = Depends(get_db)):
    """List all suppliers tagged to a product in the catalog."""
    from app.models.inventory import ProductSupplierCatalog
    rows = (
        db.query(ProductSupplierCatalog)
        .filter(ProductSupplierCatalog.product_id == product_id)
        .all()
    )
    return [_catalog_row_to_dict(r) for r in rows]


@router.post("/products/{product_id}/suppliers")
async def add_product_supplier(product_id: str, payload: dict, db: Session = Depends(get_db)):
    """Tag a supplier to a product (create catalog entry)."""
    from app.models.inventory import ProductSupplierCatalog
    from app.models.purchases import Supplier

    if not db.query(Product).filter(Product.id == product_id).first():
        raise HTTPException(status_code=404, detail="Product not found")
    supplier_id = payload.get("supplier_id")
    if not supplier_id or not db.query(Supplier).filter(Supplier.id == supplier_id).first():
        raise HTTPException(status_code=400, detail="Valid supplier_id required")

    # If this is marked preferred, remove preferred flag from others
    if payload.get("is_preferred"):
        db.query(ProductSupplierCatalog).filter(
            ProductSupplierCatalog.product_id == product_id
        ).update({"is_preferred": False})

    row = ProductSupplierCatalog(
        product_id=product_id,
        supplier_id=supplier_id,
        supplier_sku=payload.get("supplier_sku"),
        supplier_product_name=payload.get("supplier_product_name"),
        unit_cost=payload.get("unit_cost"),
        currency=payload.get("currency", "BWP"),
        lead_time_days=int(payload.get("lead_time_days", 0)),
        minimum_order_qty=payload.get("minimum_order_qty", 1),
        is_preferred=bool(payload.get("is_preferred", False)),
        active=True,
        notes=payload.get("notes"),
    )
    db.add(row)
    db.commit()
    db.refresh(row)
    return _catalog_row_to_dict(row)


@router.put("/products/{product_id}/suppliers/{catalog_id}")
async def update_product_supplier(
    product_id: str, catalog_id: str, payload: dict, db: Session = Depends(get_db)
):
    """Update a catalog entry (pricing, lead time, preferred flag, etc.)."""
    from app.models.inventory import ProductSupplierCatalog
    row = db.query(ProductSupplierCatalog).filter(
        ProductSupplierCatalog.id == catalog_id,
        ProductSupplierCatalog.product_id == product_id,
    ).first()
    if not row:
        raise HTTPException(status_code=404, detail="Catalog entry not found")

    if payload.get("is_preferred") and not row.is_preferred:
        db.query(ProductSupplierCatalog).filter(
            ProductSupplierCatalog.product_id == product_id,
            ProductSupplierCatalog.id != catalog_id,
        ).update({"is_preferred": False})

    for field in ("supplier_sku", "supplier_product_name", "unit_cost", "currency",
                  "lead_time_days", "minimum_order_qty", "is_preferred", "active", "notes"):
        if field in payload:
            setattr(row, field, payload[field])
    db.commit()
    db.refresh(row)
    return _catalog_row_to_dict(row)


@router.delete("/products/{product_id}/suppliers/{catalog_id}")
async def remove_product_supplier(product_id: str, catalog_id: str, db: Session = Depends(get_db)):
    """Remove a supplier tag from a product."""
    from app.models.inventory import ProductSupplierCatalog
    row = db.query(ProductSupplierCatalog).filter(
        ProductSupplierCatalog.id == catalog_id,
        ProductSupplierCatalog.product_id == product_id,
    ).first()
    if not row:
        raise HTTPException(status_code=404, detail="Catalog entry not found")
    db.delete(row)
    db.commit()
    return {"detail": "Removed"}


def _catalog_row_to_dict(row):
    supplier = row.supplier
    return {
        "id": row.id,
        "product_id": row.product_id,
        "supplier": {
            "id": str(supplier.id) if supplier else None,
            "name": supplier.name if supplier else None,
            "email": supplier.email if supplier else None,
        } if supplier else None,
        "supplier_sku": row.supplier_sku,
        "supplier_product_name": row.supplier_product_name,
        "unit_cost": float(row.unit_cost) if row.unit_cost else None,
        "currency": row.currency,
        "lead_time_days": row.lead_time_days,
        "minimum_order_qty": float(row.minimum_order_qty) if row.minimum_order_qty else 1,
        "is_preferred": row.is_preferred,
        "active": row.active,
        "notes": row.notes,
    }

