"""Clean sales & invoices endpoints (branch scoped)."""

from fastapi import APIRouter, Depends, HTTPException, status, Query, Response, Header, UploadFile, File, Form
from sqlalchemy.orm import Session, joinedload
from sqlalchemy import or_, func, and_, text
from sqlalchemy.exc import IntegrityError
from typing import List, Optional, Dict, Any
from pydantic import BaseModel, Field, ConfigDict
from datetime import date, datetime, timedelta
import uuid
import io
import pandas as pd

from app.core.database import get_db
from app.core.dependencies import get_current_company_id
from app.core.security import get_current_user
from app.services.branch_inventory_guard import (
    assert_sufficient_branch_stock,
    deduct_received_branch_stock,
    resolve_operating_branch_id,
)
from app.utils.product_stock import is_inventory_tracked
# from app.core.security import (
#     get_current_user,
#     require_any,
#     enforce_branch_scope,
#     require_branch_match,
# )  # Commented out for development
from app.models.sales import Customer, Sale, SaleItem, Invoice, InvoiceItem, Payment
from app.models.inventory import Product
from app.models.branch import Branch
from app.models.user import User
from app.models.cash_management import CashSubmission, FloatAllocation, CashSubmissionStatus, FloatAllocationStatus
from app.models.accounting import JournalEntry, AccountingEntry
from app.models.accounting_dimensions import AccountingDimensionValue
from app.services.daily_sales_service import DailySalesService
from app.services.ifrs_accounting_service import IFRSAccountingService
from app.services.app_setting_service import AppSettingService
from app.services.sales_service import SalesService
from app.services.column_mapper import ColumnMapper, ColumnPreview
from app.services.branch_sales_monitoring_service import resolve_monitoring_scope, sale_timestamp_sql
from app.services.customer_duplicate_guard import (
    validate_customer_write,
    integrity_error_to_message,
)
from decimal import Decimal

# Sales endpoints: Authentication removed for development
router = APIRouter(
    # dependencies=[Depends(# Security check removed for development)]  # Commented out for development
)


# Schemas -----------------------------------------------------------------
class CustomerBase(BaseModel):
    name: str
    email: Optional[str] = None
    phone: Optional[str] = None
    address: Optional[str] = None
    notes: Optional[str] = None
    customer_type: Optional[str] = "retail"
    active: Optional[bool] = True
    vat_reg_number: Optional[str] = None
    credit_limit: Optional[float] = Field(0.0, ge=0, description="Credit limit must be non-negative")
    payment_terms: Optional[int] = 30
    contact_person: Optional[str] = None
    account_balance: Optional[float] = 0.0
    opening_balance: Optional[float] = Field(0.0, ge=0, description="Opening balance for new customers")


class CustomerCreate(CustomerBase):
    pass


class CustomerUpdate(BaseModel):
    name: Optional[str] = None
    email: Optional[str] = None
    phone: Optional[str] = None
    address: Optional[str] = None
    notes: Optional[str] = None
    active: Optional[bool] = None
    customer_type: Optional[str] = None
    vat_reg_number: Optional[str] = None
    credit_limit: Optional[float] = Field(None, ge=0, description="Credit limit must be non-negative")
    payment_terms: Optional[int] = None
    contact_person: Optional[str] = None


class CustomerResponse(CustomerBase):
    id: str
    branch_id: Optional[str]
    account_balance: Optional[float] = 0.0
    # Response allows any float (including negatives) — DB values are not user-input
    opening_balance: Optional[float] = None
    credit_limit: Optional[float] = None

    model_config = ConfigDict(from_attributes=True)


# Enhanced Import Schemas
class ColumnAnalysisResponse(BaseModel):
    """Response from file analysis endpoint."""
    file_columns: List[str]
    suggested_mapping: Dict[str, Optional[str]]
    available_fields: Dict[str, Dict]
    total_rows: int
    preview_rows: int


class ImportMappingRequest(BaseModel):
    """Request body for selective import with custom column mapping."""
    mapping: Dict[str, Optional[str]]  # file_col -> db_field
    selected_fields: Optional[List[str]] = None  # Which fields user wants to import
    handle_duplicates: str = "update"  # "update", "skip", or "create_new"
    overwrite_existing_balance: bool = False


class ImportPreviewResponse(BaseModel):
    """Preview of import results before confirmation."""
    total_columns: int
    mapped_columns: int
    unmapped_columns: int
    total_rows: int
    preview_rows: List[Dict]
    mapping_summary: Dict[str, Optional[str]]
    validation_errors: List[str] = []


class ImportResultResponse(BaseModel):
    """Final result of import operation."""
    created: int
    updated: int
    skipped: int
    errors: List[str] = []
    total_processed: int
    success_rate: float


class SaleItemCreate(BaseModel):
    product_id: str
    quantity: int = Field(gt=0)
    selling_price: float
    vat_rate: float = 0.0
    discount_amount: float = 0.0
    discount_percentage: float = 0.0
    inventory_source_branch_id: Optional[str] = None  # Branch where inventory is sourced from
    serial_numbers: Optional[List[str]] = None         # Required for serialised products


class SaleItemResponse(SaleItemCreate):
    id: str
    vat_amount: float
    total_amount: float
    product_name: Optional[str]
    product_sku: Optional[str]
    category: Optional[str] = None
    brand: Optional[str] = None
    condition: Optional[str] = None
    location: Optional[str] = None

    model_config = ConfigDict(from_attributes=True)


class SaleCreate(BaseModel):
    customer_id: Optional[str] = None
    payment_method: str
    currency: str = "USD"
    amount_tendered: float = 0.0
    discount_amount: float = 0.0
    discount_percentage: float = 0.0
    notes: Optional[str] = None
    branch_id: Optional[str] = None
    salesperson_id: Optional[str] = None
    cost_center: Optional[str] = None
    project: Optional[str] = None
    collection_branch_id: Optional[str] = None  # Branch where customer will collect
    items: List[SaleItemCreate]


class SaleResponse(SaleCreate):
    id: str
    reference: Optional[str] = None
    total_amount: float
    total_amount_ex_vat: float
    total_vat_amount: float
    change_given: float
    status: str
    date: Optional[datetime] = None
    customer_name: Optional[str] = None
    salesperson_name: Optional[str] = None
    branch_name: Optional[str] = None
    items_count: int
    items: List[SaleItemResponse] = []
    # Dimensional accounting fields for display
    cost_center_id: Optional[str] = None
    cost_center_name: Optional[str] = None
    project_id: Optional[str] = None
    project_name: Optional[str] = None
    # Audit trail fields
    created_by: Optional[str] = None
    created_by_name: Optional[str] = None
    created_from_branch_id: Optional[str] = None
    created_at: Optional[datetime] = None
    updated_at: Optional[datetime] = None
    # Inter-branch collection tracking
    collection_branch_id: Optional[str] = None
    collection_branch_name: Optional[str] = None
    collection_status: Optional[str] = None
    collected_at: Optional[datetime] = None
    collected_by: Optional[str] = None
    collected_by_name: Optional[str] = None
    collection_notes: Optional[str] = None


class SaleUpdate(BaseModel):
    customer_id: Optional[str] = None
    payment_method: Optional[str] = None
    currency: Optional[str] = None
    amount_tendered: Optional[float] = Field(None, ge=0)
    notes: Optional[str] = None
    status: Optional[str] = None
    salesperson_id: Optional[str] = None
    cost_center: Optional[str] = None
    project: Optional[str] = None
    branch_id: Optional[str] = None


class InvoiceItemCreate(BaseModel):
    product_id: str
    quantity: float
    price: float
    vat_rate: float = 0.0
    discount_amount: float = 0.0
    discount_percentage: float = 0.0


class InvoiceItemResponse(InvoiceItemCreate):
    id: str
    vat_amount: float
    total: float
    product_name: Optional[str]
    product_sku: Optional[str]
    category: Optional[str] = None
    brand: Optional[str] = None
    condition: Optional[str] = None
    location: Optional[str] = None

    model_config = ConfigDict(from_attributes=True)


class InvoiceCreate(BaseModel):
    customer_id: str
    due_date: Optional[date] = None
    payment_terms: int = 30
    discount_amount: float = 0.0
    discount_percentage: float = 0.0
    notes: Optional[str] = None
    items: List[InvoiceItemCreate]


class InvoiceResponse(InvoiceCreate):
    id: str
    invoice_number: str
    date: date
    status: str
    total: float
    total_vat_amount: float
    total_amount: float
    amount_paid: float
    customer_name: Optional[str] = None
    items_count: int


# Cash Management Schemas -------------------------------------------------
class CashSubmissionCreate(BaseModel):
    """Schema for creating a cash submission from salesperson to manager/accounting"""
    salesperson_id: str
    amount: float = Field(gt=0, description="Amount must be positive")
    submission_date: date
    branch_id: Optional[str] = None
    received_by_id: Optional[str] = None
    notes: Optional[str] = None


class CashSubmissionResponse(BaseModel):
    """Response schema for cash submission"""
    id: str
    salesperson_id: str
    salesperson_name: Optional[str] = None
    received_by_id: Optional[str] = None
    received_by_name: Optional[str] = None
    amount: float
    submission_date: date
    branch_id: Optional[str] = None
    journal_entry_id: Optional[str] = None
    status: str
    notes: Optional[str] = None
    created_at: datetime
    updated_at: datetime

    model_config = ConfigDict(from_attributes=True)


class FloatAllocationCreate(BaseModel):
    """Schema for allocating float/change to a cashier"""
    cashier_id: str
    float_amount: float = Field(gt=0, description="Float amount must be positive")
    allocation_date: date
    branch_id: Optional[str] = None
    allocated_by_id: Optional[str] = None
    notes: Optional[str] = None


class FloatReturnCreate(BaseModel):
    """Schema for recording a float return"""
    amount_returned: float = Field(gt=0, description="Return amount must be positive")
    return_date: date
    notes: Optional[str] = None


class FloatAllocationResponse(BaseModel):
    """Response schema for float allocation"""
    id: str
    cashier_id: str
    cashier_name: Optional[str] = None
    allocated_by_id: Optional[str] = None
    allocated_by_name: Optional[str] = None
    float_amount: float
    amount_returned: float
    allocation_date: date
    return_date: Optional[date] = None
    branch_id: Optional[str] = None
    allocation_journal_entry_id: Optional[str] = None
    return_journal_entry_id: Optional[str] = None
    status: str
    notes: Optional[str] = None
    created_at: datetime
    updated_at: datetime

    model_config = ConfigDict(from_attributes=True)


# Customer Endpoints -------------------------------------------------------
def _customer_search_filter(search: str):
    """Match customers by name, phone, email, VAT, contact, or address."""
    like = f"%{search.strip()}%"
    return or_(
        Customer.name.ilike(like),
        Customer.email.ilike(like),
        Customer.phone.ilike(like),
        Customer.vat_reg_number.ilike(like),
        Customer.contact_person.ilike(like),
        Customer.address.ilike(like),
        Customer.notes.ilike(like),
    )


def _exclude_duplicate_walkin_customers(query):
    """Only expose the canonical system walk-in, not legacy per-company duplicates."""
    from app.constants.walkin_customer import WALKIN_CUSTOMER_ID

    return query.filter(
        or_(
            Customer.customer_type != "walk_in",
            Customer.id == WALKIN_CUSTOMER_ID,
        )
    )


@router.get("/customers/count")
async def count_customers(
    db: Session = Depends(get_db),
    active: Optional[bool] = Query(None),
    customer_type: Optional[str] = Query(None),
    search: Optional[str] = Query(None),
    branch_id: Optional[str] = Query(None),
    company_id: Optional[str] = Depends(get_current_company_id),
):
    query = db.query(func.count(Customer.id))
    query = _exclude_duplicate_walkin_customers(query)
    if branch_id:
        query = query.filter(Customer.branch_id == branch_id)
    elif company_id:
        branch_ids_sq = db.query(Branch.id).filter(Branch.company_id == company_id)
        query = query.filter(or_(Customer.branch_id.in_(branch_ids_sq), Customer.branch_id.is_(None)))
    if customer_type:
        query = query.filter(Customer.customer_type == customer_type)
    if active is not None:
        query = query.filter(Customer.active == active)
    if search:
        query = query.filter(_customer_search_filter(search))
    return {"total": query.scalar()}


@router.get("/customers", response_model=List[CustomerResponse])
async def get_customers(
    db: Session = Depends(get_db),
    skip: int = Query(0, ge=0),
    limit: int = Query(100, ge=1),
    active: Optional[bool] = Query(None),
    customer_type: Optional[str] = Query(None),
    search: Optional[str] = Query(None),
    branch_id: Optional[str] = Query(None),
    company_id: Optional[str] = Depends(get_current_company_id),
):
    query = db.query(Customer)
    query = _exclude_duplicate_walkin_customers(query)
    if branch_id:
        query = query.filter(Customer.branch_id == branch_id)
    elif company_id:
        branch_ids_sq = db.query(Branch.id).filter(Branch.company_id == company_id)
        query = query.filter(or_(Customer.branch_id.in_(branch_ids_sq), Customer.branch_id.is_(None)))
    if customer_type:
        query = query.filter(Customer.customer_type == customer_type)
    if active is not None:
        query = query.filter(Customer.active == active)
    if search:
        query = query.filter(_customer_search_filter(search))
    return query.order_by(Customer.name).offset(skip).limit(limit).all()


@router.post("/customers", response_model=CustomerResponse, status_code=status.HTTP_201_CREATED)
async def create_customer(
    customer_data: CustomerCreate,
    db: Session = Depends(get_db),
    company_id: Optional[str] = Depends(get_current_company_id),
):
    _branch_q = db.query(Branch).filter(Branch.active == True)
    if company_id:
        _branch_q = _branch_q.filter(Branch.company_id == company_id)
    branch = _branch_q.first()
    if not branch:
        raise HTTPException(status_code=400, detail="No active branch found")

    payload = customer_data.dict()
    try:
        validate_customer_write(
            db,
            name=payload.get("name") or "",
            branch_id=branch.id,
            customer_type=payload.get("customer_type"),
            email=payload.get("email"),
            phone=payload.get("phone"),
            company_id=company_id,
        )
    except ValueError as exc:
        raise HTTPException(status_code=400, detail=str(exc)) from exc

    customer = Customer(**payload, branch_id=branch.id)
    db.add(customer)
    try:
        db.commit()
    except IntegrityError as exc:
        db.rollback()
        friendly = integrity_error_to_message(str(exc.orig))
        raise HTTPException(
            status_code=400,
            detail=friendly or "A customer with these details already exists.",
        ) from exc
    db.refresh(customer)
    return customer


@router.post("/customers/import")
async def import_customers(
    file: UploadFile = File(...),
    db: Session = Depends(get_db),
    company_id: Optional[str] = Depends(get_current_company_id),
):
    """Bulk import customers from CSV or Excel."""
    filename = (file.filename or "").lower()
    if not (filename.endswith(".csv") or filename.endswith(".xlsx") or filename.endswith(".xls")):
        raise HTTPException(status_code=400, detail="Only CSV or Excel files are supported")

    _branch_q = db.query(Branch).filter(Branch.active == True)
    if company_id:
        _branch_q = _branch_q.filter(Branch.company_id == company_id)
    branch = _branch_q.first()
    if not branch:
        raise HTTPException(status_code=400, detail="No active branch found")

    try:
        if filename.endswith(".csv"):
            df = pd.read_csv(file.file)
        else:
            df = pd.read_excel(file.file)
    except Exception as exc:
        raise HTTPException(status_code=400, detail=f"Failed to read file: {exc}")

    if df.empty:
        return {"created": 0, "skipped": 0, "errors": ["File is empty"]}

    def normalize_col(col: str) -> str:
        return "".join(ch for ch in col.strip().lower().replace(" ", "_") if ch.isalnum() or ch == "_")

    df.columns = [normalize_col(c) for c in df.columns]

    column_map = {
        "name": "name",
        "customer_name": "name",
        "email": "email",
        "phone": "phone",
        "address": "address",
        "customer_type": "customer_type",
        "vat_reg_number": "vat_reg_number",
        "vat_number": "vat_reg_number",
        "credit_limit": "credit_limit",
        "payment_terms": "payment_terms",
        "contact_person": "contact_person",
        "notes": "notes",
        "active": "active",
    }

    available = {column_map.get(c, None) for c in df.columns}
    if "name" not in available:
        raise HTTPException(status_code=400, detail="Missing required column: name")

    existing_rows = db.query(Customer.id, Customer.name, Customer.email, Customer.phone).all()
    existing_by_email = {}
    existing_by_phone = {}
    existing_by_name = {}
    for customer_id, name, email, phone in existing_rows:
        name_key = (name or "").strip().lower()
        email_key = (email or "").strip().lower()
        phone_key = (phone or "").strip().lower()
        if email_key:
            existing_by_email[email_key] = customer_id
        if phone_key:
            existing_by_phone[phone_key] = customer_id
        if name_key:
            existing_by_name[name_key] = customer_id

    created = 0
    updated = 0
    skipped = 0
    errors: List[str] = []

    for idx, raw in df.iterrows():
        row = {column_map.get(k, k): raw[k] for k in df.columns if column_map.get(k)}

        name = str(row.get("name", "")).strip()
        if not name:
            errors.append(f"Row {idx + 2}: missing name")
            continue

        email = str(row.get("email", "") or "").strip()
        phone = str(row.get("phone", "") or "").strip()
        email_key = email.lower()
        phone_key = phone.lower()
        name_key = name.lower()

        existing_id = None
        if email_key and email_key in existing_by_email:
            existing_id = existing_by_email[email_key]
        elif phone_key and phone_key in existing_by_phone:
            existing_id = existing_by_phone[phone_key]
        elif name_key in existing_by_name:
            existing_id = existing_by_name[name_key]

        customer_type = str(row.get("customer_type", "retail") or "retail").strip().lower()
        if customer_type not in {"retail", "wholesale", "corporate"}:
            customer_type = "retail"

        def to_float(value, default=0.0):
            try:
                return float(value)
            except Exception:
                return default

        def to_int(value, default=30):
            try:
                return int(value)
            except Exception:
                return default

        def to_bool(value, default=True):
            if value is None or value == "":
                return default
            if isinstance(value, bool):
                return value
            text = str(value).strip().lower()
            if text in {"true", "yes", "1", "active"}:
                return True
            if text in {"false", "no", "0", "inactive"}:
                return False
            return default

        payload = {
            "name": name,
            "email": email or None,
            "phone": phone or None,
            "address": str(row.get("address", "") or "").strip() or None,
            "notes": str(row.get("notes", "") or "").strip() or None,
            "customer_type": customer_type,
            "active": to_bool(row.get("active", True), True),
            "vat_reg_number": str(row.get("vat_reg_number", "") or "").strip() or None,
            "credit_limit": max(0.0, to_float(row.get("credit_limit", 0.0), 0.0)),
            "payment_terms": to_int(row.get("payment_terms", 30), 30),
            "contact_person": str(row.get("contact_person", "") or "").strip() or None,
        }

        if existing_id:
            customer = db.query(Customer).filter(Customer.id == existing_id).first()
            if customer:
                for field, value in payload.items():
                    setattr(customer, field, value)
                updated += 1
            else:
                skipped += 1
        else:
            customer = Customer(**payload, branch_id=branch.id)
            db.add(customer)
            created += 1

        if email_key:
            existing_by_email[email_key] = customer.id if customer else existing_by_email.get(email_key)
        if phone_key:
            existing_by_phone[phone_key] = customer.id if customer else existing_by_phone.get(phone_key)
        if name_key:
            existing_by_name[name_key] = customer.id if customer else existing_by_name.get(name_key)

    db.commit()
    return {
        "created": created,
        "updated": updated,
        "skipped": skipped,
        "errors": errors[:50],
    }


@router.post("/customers/import/analyze", response_model=ColumnAnalysisResponse)
async def analyze_import_file(
    file: UploadFile = File(...),
    db: Session = Depends(get_db),
):
    """
    Analyze uploaded CSV/Excel file and return suggested column mappings.
    Detects available columns and suggests best matches for database fields.
    """
    filename = (file.filename or "").lower()
    if not (filename.endswith(".csv") or filename.endswith(".xlsx") or filename.endswith(".xls")):
        raise HTTPException(status_code=400, detail="Only CSV or Excel files are supported")

    try:
        if filename.endswith(".csv"):
            df = pd.read_csv(file.file)
        else:
            df = pd.read_excel(file.file)
    except Exception as exc:
        raise HTTPException(status_code=400, detail=f"Failed to read file: {exc}")

    if df.empty:
        raise HTTPException(status_code=400, detail="File is empty")

    # Get file columns
    file_columns = [str(col).strip() for col in df.columns]

    # Auto-detect mappings
    suggested_mapping = ColumnMapper.auto_detect_mappings(file_columns, threshold=0.75)

    # Get available fields metadata
    available_fields = ColumnMapper.get_all_possible_fields()

    return {
        "file_columns": file_columns,
        "suggested_mapping": suggested_mapping,
        "available_fields": available_fields,
        "total_rows": len(df),
        "preview_rows": min(5, len(df)),
    }


@router.post("/customers/import/preview", response_model=ImportPreviewResponse)
async def preview_import(
    file: UploadFile = File(...),
    mapping: str = Form("{}"),  # JSON string of {file_col -> db_field}
    db: Session = Depends(get_db),
):
    """
    Preview import with custom column mapping before confirming.
    Returns sample rows with mapped values and validation errors.
    """
    filename = (file.filename or "").lower()
    if not (filename.endswith(".csv") or filename.endswith(".xlsx") or filename.endswith(".xls")):
        raise HTTPException(status_code=400, detail="Only CSV or Excel files are supported")

    import json
    try:
        column_mapping = json.loads(mapping)
    except json.JSONDecodeError:
        raise HTTPException(status_code=400, detail="Invalid mapping JSON")

    try:
        if filename.endswith(".csv"):
            df = pd.read_csv(file.file)
        else:
            df = pd.read_excel(file.file)
    except Exception as exc:
        raise HTTPException(status_code=400, detail=f"Failed to read file: {exc}")

    if df.empty:
        raise HTTPException(status_code=400, detail="File is empty")

    # Validate mapping
    is_valid, validation_errors = ColumnMapper.validate_mapping(column_mapping)

    # Convert dataframe to list of dicts
    file_data = df.fillna("").to_dict('records')

    # Generate preview
    preview = ColumnPreview.generate_preview(
        file_columns=list(df.columns),
        file_data=file_data,
        mappings=column_mapping,
        preview_rows=min(500, len(file_data))
    )

    return {
        "total_columns": preview["total_columns"],
        "mapped_columns": preview["mapped_columns"],
        "unmapped_columns": preview["unmapped_columns"],
        "total_rows": preview["total_rows"],
        "preview_rows": preview["preview_rows"],
        "mapping_summary": preview["mapping_summary"],
        "validation_errors": validation_errors,
    }


@router.post("/customers/import/with-mapping", response_model=ImportResultResponse)
async def import_with_custom_mapping(
    file: UploadFile = File(...),
    mapping_json: str = Form(""),  # JSON string of {file_col -> db_field}
    handle_duplicates: str = Form("update"),
    overwrite_existing_balance: bool = Form(False),
    selected_row_indices: str = Form(""),  # JSON array of 0-based row indices to import
    db: Session = Depends(get_db),
    company_id: Optional[str] = Depends(get_current_company_id),
):
    """
    Import customers with custom column mapping and duplicate handling strategy.

    Supports:
    - Custom column-to-field mapping
    - Duplicate detection by email, phone, or name
    - Three strategies: "update" (update existing), "skip" (ignore), "create_new" (add as new)
    - Opening balance field mapping
    """
    filename = (file.filename or "").lower()
    if not (filename.endswith(".csv") or filename.endswith(".xlsx") or filename.endswith(".xls")):
        raise HTTPException(status_code=400, detail="Only CSV or Excel files are supported")

    _branch_q = db.query(Branch).filter(Branch.active == True)
    if company_id:
        _branch_q = _branch_q.filter(Branch.company_id == company_id)
    branch = _branch_q.first()
    if not branch:
        raise HTTPException(status_code=400, detail="No active branch found")

    import json
    try:
        column_mapping = json.loads(mapping_json) if mapping_json else {}
    except json.JSONDecodeError:
        raise HTTPException(status_code=400, detail="Invalid mapping JSON")

    # Validate duplicate handling strategy
    if handle_duplicates not in {"update", "skip", "create_new"}:
        raise HTTPException(status_code=400, detail='handle_duplicates must be "update", "skip", or "create_new"')

    try:
        if filename.endswith(".csv"):
            df = pd.read_csv(file.file)
        else:
            df = pd.read_excel(file.file)
    except Exception as exc:
        raise HTTPException(status_code=400, detail=f"Failed to read file: {exc}")

    if df.empty:
        return {
            "created": 0,
            "updated": 0,
            "skipped": 0,
            "errors": ["File is empty"],
            "total_processed": 0,
            "success_rate": 0.0,
        }

    # Filter to selected rows if caller specified indices
    if selected_row_indices:
        try:
            indices = json.loads(selected_row_indices)
            if isinstance(indices, list) and len(indices) > 0:
                valid_indices = [i for i in indices if 0 <= i < len(df)]
                df = df.iloc[valid_indices].reset_index(drop=True)
        except Exception:
            pass  # Ignore bad indices — import all rows

    # Replace NaN with empty string so str(NaN) never becomes "nan"
    df = df.fillna("")

    # Helper functions for type conversion
    def to_float(value, default=0.0):
        try:
            return float(value)
        except Exception:
            return default

    def to_int(value, default=30):
        try:
            return int(value)
        except Exception:
            return default

    def to_bool(value, default=True):
        if value is None or value == "":
            return default
        if isinstance(value, bool):
            return value
        text = str(value).strip().lower()
        if text in {"true", "yes", "1", "active"}:
            return True
        if text in {"false", "no", "0", "inactive"}:
            return False
        return default

    # Build existing customer indexes
    existing_rows = db.query(Customer.id, Customer.name, Customer.email, Customer.phone).all()
    existing_by_email = {}
    existing_by_phone = {}
    existing_by_name = {}
    for customer_id, name, email, phone in existing_rows:
        name_key = (name or "").strip().lower()
        email_key = (email or "").strip().lower()
        phone_key = (phone or "").strip().lower()
        if email_key:
            existing_by_email[email_key] = customer_id
        if phone_key:
            existing_by_phone[phone_key] = customer_id
        if name_key:
            existing_by_name[name_key] = customer_id

    created = 0
    updated = 0
    skipped = 0
    errors: List[str] = []

    # Process each row
    for idx, raw in df.iterrows():
        try:
            # Map row columns to database fields
            row = {}
            for file_col, raw_value in raw.items():
                db_field = column_mapping.get(str(file_col))
                if db_field:
                    row[db_field] = raw_value

            # Get required name field
            name = str(row.get("name", "")).strip()
            if not name:
                errors.append(f"Row {idx + 2}: missing or empty name field")
                skipped += 1
                continue

            # Get unique identifiers
            email = str(row.get("email", "") or "").strip()
            phone = str(row.get("phone", "") or "").strip()
            email_key = email.lower() if email else ""
            phone_key = phone.lower() if phone else ""
            name_key = name.lower()

            # Check for existing customer
            existing_id = None
            if email_key and email_key in existing_by_email:
                existing_id = existing_by_email[email_key]
            elif phone_key and phone_key in existing_by_phone:
                existing_id = existing_by_phone[phone_key]
            elif name_key in existing_by_name:
                existing_id = existing_by_name[name_key]

            # Validate customer type (walk_in is system-only)
            customer_type = str(row.get("customer_type", "retail") or "retail").strip().lower()
            if customer_type == "walk_in":
                errors.append(f"Row {idx + 2}: walk_in customers cannot be imported")
                skipped += 1
                continue
            if customer_type not in {"retail", "wholesale", "corporate"}:
                customer_type = "retail"

            # Prepare payload
            payload = {
                "name": name,
                "email": email or None,
                "phone": phone or None,
                "address": str(row.get("address", "") or "").strip() or None,
                "notes": str(row.get("notes", "") or "").strip() or None,
                "customer_type": customer_type,
                "active": to_bool(row.get("active", True), True),
                "vat_reg_number": str(row.get("vat_reg_number", "") or "").strip() or None,
                "credit_limit": max(0.0, to_float(row.get("credit_limit", 0.0), 0.0)),
                "payment_terms": to_int(row.get("payment_terms", 30), 30),
                "contact_person": str(row.get("contact_person", "") or "").strip() or None,
                "opening_balance": to_float(row.get("opening_balance", 0.0), 0.0),
            }

            # Handle existing customer
            if existing_id:
                customer = db.query(Customer).filter(Customer.id == existing_id).first()
                if not customer:
                    skipped += 1
                    continue

                if handle_duplicates == "skip":
                    skipped += 1
                elif handle_duplicates == "update":
                    try:
                        validate_customer_write(
                            db,
                            name=payload["name"],
                            branch_id=branch.id,
                            customer_type=payload["customer_type"],
                            email=payload.get("email"),
                            phone=payload.get("phone"),
                            exclude_id=customer.id,
                            company_id=company_id,
                        )
                    except ValueError as dup_err:
                        errors.append(f"Row {idx + 2}: {dup_err}")
                        skipped += 1
                        continue
                    for field, value in payload.items():
                        if field == "opening_balance" and not overwrite_existing_balance:
                            continue  # Skip opening balance if not requested
                        setattr(customer, field, value)
                    updated += 1
                elif handle_duplicates == "create_new":
                    # Create as new (add random suffix to name)
                    payload["name"] = f"{name} ({str(uuid.uuid4())[:8]})"
                    try:
                        validate_customer_write(
                            db,
                            name=payload["name"],
                            branch_id=branch.id,
                            customer_type=payload["customer_type"],
                            email=payload.get("email"),
                            phone=payload.get("phone"),
                            company_id=company_id,
                        )
                    except ValueError as dup_err:
                        errors.append(f"Row {idx + 2}: {dup_err}")
                        skipped += 1
                        continue
                    customer = Customer(**payload, branch_id=branch.id)
                    db.add(customer)
                    created += 1
            else:
                try:
                    validate_customer_write(
                        db,
                        name=payload["name"],
                        branch_id=branch.id,
                        customer_type=payload["customer_type"],
                        email=payload.get("email"),
                        phone=payload.get("phone"),
                        company_id=company_id,
                    )
                except ValueError as dup_err:
                    errors.append(f"Row {idx + 2}: {dup_err}")
                    skipped += 1
                    continue
                customer = Customer(**payload, branch_id=branch.id)
                db.add(customer)
                created += 1

            # Update lookup indexes
            if email_key:
                existing_by_email[email_key] = customer.id if customer else existing_by_email.get(email_key)
            if phone_key:
                existing_by_phone[phone_key] = customer.id if customer else existing_by_phone.get(phone_key)
            if name_key:
                existing_by_name[name_key] = customer.id if customer else existing_by_name.get(name_key)

        except Exception as e:
            errors.append(f"Row {idx + 2}: {str(e)}")
            skipped += 1

    db.commit()

    total_processed = created + updated + skipped
    success_rate = (created + updated) / total_processed if total_processed > 0 else 0.0

    return {
        "created": created,
        "updated": updated,
        "skipped": skipped,
        "errors": errors[:50],  # Limit error messages
        "total_processed": total_processed,
        "success_rate": success_rate,
    }


@router.get("/customers/{customer_id}", response_model=CustomerResponse)
async def get_customer(
    customer_id: str,
    db: Session = Depends(get_db),
    # # # current_user parameter removed for development,  # Removed for development,  # Removed for development,
):
    customer = db.query(Customer).filter(Customer.id == customer_id).first()
    if not customer:
        raise HTTPException(status_code=404, detail="Customer not found")
    # require_branch_match(..., current_user)  # Removed for development
    return customer


@router.put("/customers/{customer_id}", response_model=CustomerResponse)
async def update_customer(
    customer_id: str,
    customer_data: CustomerUpdate,
    db: Session = Depends(get_db),
    company_id: Optional[str] = Depends(get_current_company_id),
    # # # current_user parameter removed for development,  # Removed for development,  # Removed for development,
):
    customer = db.query(Customer).filter(Customer.id == customer_id).first()
    if not customer:
        raise HTTPException(status_code=404, detail="Customer not found")
    # require_branch_match(..., current_user)  # Removed for development
    updates = customer_data.dict(exclude_unset=True)
    merged_name = updates.get("name", customer.name)
    merged_type = updates.get("customer_type", customer.customer_type)
    merged_email = updates.get("email", customer.email)
    merged_phone = updates.get("phone", customer.phone)
    try:
        validate_customer_write(
            db,
            name=merged_name or "",
            branch_id=customer.branch_id,
            customer_type=merged_type,
            email=merged_email,
            phone=merged_phone,
            exclude_id=customer_id,
            company_id=company_id,
        )
    except ValueError as exc:
        raise HTTPException(status_code=400, detail=str(exc)) from exc

    for field, value in updates.items():
        setattr(customer, field, value)
    try:
        db.commit()
    except IntegrityError as exc:
        db.rollback()
        friendly = integrity_error_to_message(str(exc.orig))
        raise HTTPException(
            status_code=400,
            detail=friendly or "A customer with these details already exists.",
        ) from exc
    db.refresh(customer)
    return customer


@router.delete("/customers/{customer_id}")
async def delete_customer(
    customer_id: str,
    db: Session = Depends(get_db),
    # # # current_user parameter removed for development,  # Removed for development,  # Removed for development,
):
    from app.constants.walkin_customer import WALKIN_CUSTOMER_ID

    customer = db.query(Customer).filter(Customer.id == customer_id).first()
    if not customer:
        raise HTTPException(status_code=404, detail="Customer not found")

    if customer_id == WALKIN_CUSTOMER_ID:
        raise HTTPException(
            status_code=400,
            detail="The Walk-in Customer is a protected system account and cannot be deleted.",
        )
    if customer.customer_type == "walk_in":
        raise HTTPException(
            status_code=400,
            detail=(
                "Walk-in customer records are managed by the system. "
                "Use Settings or run the walk-in deduplication migration if you see duplicates."
            ),
        )
    # require_branch_match(..., current_user)  # Removed for development
    # Soft-delete: mark customer as inactive instead of deleting to preserve referential integrity
    try:
        customer.active = False
        db.commit()
        return {"message": "Customer deactivated successfully"}
    except Exception as e:
        db.rollback()
        raise HTTPException(status_code=500, detail=f"Error deactivating customer: {str(e)}")


@router.get("/customers/{customer_id}/balance")
async def get_customer_balance(
    customer_id: str,
    db: Session = Depends(get_db),
    # # # current_user parameter removed for development,  # Removed for development,  # Removed for development,
):
    """Get customer account balance"""
    customer = db.query(Customer).filter(Customer.id == customer_id).first()
    if not customer:
        raise HTTPException(status_code=404, detail="Customer not found")
    # require_branch_match(..., current_user)  # Removed for development

    # Return the stored account balance from the customer record
    return {
        "customer_id": customer_id,
        "balance": float(customer.account_balance or 0),
        "currency": "BWP"  # Default currency
    }


@router.get("/customers/{customer_id}/statement")
async def get_customer_statement(
    customer_id: str,
    start_date: Optional[date] = None,
    end_date: Optional[date] = None,
    db: Session = Depends(get_db),
):
    """Unified customer statement: opening balance + invoices + payments + POS sales with running balance."""
    customer = db.query(Customer).filter(Customer.id == customer_id).first()
    if not customer:
        raise HTTPException(status_code=404, detail="Customer not found")

    opening_balance = float(customer.opening_balance or 0)
    credit_limit = float(customer.credit_limit or 0)

    # Invoices for this customer
    inv_query = db.query(Invoice).filter(Invoice.customer_id == customer_id)
    if start_date:
        inv_query = inv_query.filter(Invoice.date >= start_date)
    if end_date:
        inv_query = inv_query.filter(Invoice.date <= end_date)
    invoices = inv_query.order_by(Invoice.date).all()

    # Payments against those invoices
    invoice_ids = [inv.id for inv in invoices]
    payments = []
    if invoice_ids:
        pay_query = db.query(Payment).filter(Payment.invoice_id.in_(invoice_ids))
        if start_date:
            pay_query = pay_query.filter(Payment.payment_date >= start_date)
        if end_date:
            pay_query = pay_query.filter(Payment.payment_date <= end_date)
        payments = pay_query.order_by(Payment.payment_date).all()

    # Also include any direct customer-level payments (not tied to one of the filtered invoices)
    direct_pay_query = db.query(Payment).filter(
        Payment.customer_id == customer_id,
        Payment.invoice_id.notin_(invoice_ids) if invoice_ids else Payment.invoice_id.is_(None)
    )
    if start_date:
        direct_pay_query = direct_pay_query.filter(Payment.payment_date >= start_date)
    if end_date:
        direct_pay_query = direct_pay_query.filter(Payment.payment_date <= end_date)
    payments = payments + direct_pay_query.all()

    # POS sales for this customer
    sale_query = db.query(Sale).filter(Sale.customer_id == customer_id)
    if start_date:
        sale_query = sale_query.filter(Sale.date >= start_date)
    if end_date:
        sale_query = sale_query.filter(Sale.date <= end_date)
    sales = sale_query.order_by(Sale.date).all()

    # Build ledger lines
    lines = []

    # Opening balance line (always first)
    lines.append({
        "sort_key": "0000-01-01",
        "date": None,
        "type": "opening_balance",
        "description": "Opening Balance",
        "reference": None,
        "debit": opening_balance if opening_balance >= 0 else 0.0,
        "credit": abs(opening_balance) if opening_balance < 0 else 0.0,
        "amount": opening_balance,
        "source_id": None,
        "status": None,
    })

    for inv in invoices:
        d = str(inv.date) if inv.date else "9999-12-31"
        lines.append({
            "sort_key": d,
            "date": d,
            "type": "invoice",
            "description": f"Invoice {inv.invoice_number or inv.id[:8]}",
            "reference": inv.invoice_number,
            "debit": float(inv.total_amount or 0),
            "credit": 0.0,
            "amount": float(inv.total_amount or 0),
            "source_id": inv.id,
            "status": inv.status,
            "amount_paid": float(inv.amount_paid or 0),
            "amount_due": float((inv.total_amount or 0) - (inv.amount_paid or 0)),
        })

    for p in payments:
        d = str(p.payment_date) if p.payment_date else "9999-12-31"
        lines.append({
            "sort_key": d,
            "date": d,
            "type": "payment",
            "description": f"Payment ({p.payment_method or 'N/A'})",
            "reference": p.reference,
            "debit": 0.0,
            "credit": float(p.amount or 0),
            "amount": float(p.amount or 0),
            "source_id": p.id,
            "invoice_id": p.invoice_id,
            "status": "completed",
        })

    for s in sales:
        date_val = s.date or s.sale_time or getattr(s, 'created_at', None)
        d = str(date_val)[:10] if date_val else None
        lines.append({
            "sort_key": d or "9999-12-31",
            "date": d,
            "type": "sale",
            "description": f"POS Sale {s.reference or s.id[:8]}",
            "reference": s.reference,
            # POS sales are already paid — they are informational and do not affect the AR balance
            "debit": 0.0,
            "credit": 0.0,
            "amount": float(s.total_amount or 0),
            "source_id": s.id,
            "status": s.status,
        })

    # Sort chronologically (opening_balance "0000..." always first)
    lines.sort(key=lambda x: x["sort_key"])

    # Compute running AR balance:
    #   Invoice  → debit  (customer owes)
    #   Payment  → credit (customer paid on account)
    #   POS sale → no AR impact (already collected at point of sale)
    running = opening_balance
    for line in lines:
        if line["type"] == "opening_balance":
            line["running_balance"] = running
        elif line["type"] == "sale":
            # POS sales don't affect AR balance
            line["running_balance"] = round(running, 2)
        else:
            running += line["debit"] - line["credit"]
            line["running_balance"] = round(running, 2)
        del line["sort_key"]

    # Summary
    total_invoiced = sum(float(inv.total_amount or 0) for inv in invoices)
    total_paid = sum(float(p.amount or 0) for p in payments)
    total_sales = sum(float(s.total_amount or 0) for s in sales)
    balance_due = opening_balance + total_invoiced - total_paid
    available_credit = round(credit_limit - balance_due, 2) if credit_limit > 0 else None

    return {
        "customer": {
            "id": customer.id,
            "name": customer.name,
            "email": customer.email,
            "phone": customer.phone,
            "customer_type": customer.customer_type,
            "credit_limit": credit_limit,
            "opening_balance": opening_balance,
            "account_balance": float(customer.account_balance or 0),
        },
        "summary": {
            "opening_balance": opening_balance,
            "total_invoiced": total_invoiced,
            "total_paid": total_paid,
            "total_sales": total_sales,
            "balance_due": round(balance_due, 2),
            "credit_limit": credit_limit,
            "available_credit": available_credit,
            "invoice_count": len(invoices),
            "payment_count": len(payments),
            "sale_count": len(sales),
        },
        "lines": lines,
    }


@router.post("/customers/{customer_id}/statement/pdf")
async def customer_statement_pdf(
    customer_id: str,
    start_date: Optional[date] = None,
    end_date: Optional[date] = None,
    save_pdf: bool = Query(True),
    send_whatsapp: bool = Query(False),
    whatsapp_phone: Optional[str] = Query(None),
    db: Session = Depends(get_db),
):
    """Generate customer statement PDF with readable filename; optional WhatsApp delivery."""
    stmt = await get_customer_statement(customer_id, start_date, end_date, db)
    period_label = f"{start_date or 'start'} to {end_date or date.today().isoformat()}"
    period_end = end_date or date.today()
    period_str = period_end.isoformat() if hasattr(period_end, "isoformat") else str(period_end)[:10]
    short_cust = str(customer_id).replace("-", "")[:8].upper()
    statement_number = f"STMT-{period_str}-{short_cust}"
    transactions = []
    for line in stmt.get("lines", []):
        if line.get("type") == "opening_balance":
            continue
        amount = float(line.get("debit", 0)) - float(line.get("credit", 0))
        transactions.append({
            "date": line.get("date"),
            "reference": line.get("reference"),
            "description": line.get("description"),
            "amount": amount,
        })

    statement_data = {
        "customer_id": customer_id,
        "customer_name": stmt["customer"]["name"],
        "customer_phone": stmt["customer"].get("phone"),
        "customer_email": stmt["customer"].get("email"),
        "statement_number": statement_number,
        "opening_balance": stmt["summary"]["opening_balance"],
        "closing_balance": stmt["summary"]["balance_due"],
        "period": period_label,
        "generated_at": date.today().isoformat(),
        "period_end": period_end,
        "transactions": transactions,
    }

    from app.services.document_delivery_service import DocumentDeliveryService, DocumentDeliveryOptions

    delivery = DocumentDeliveryService(db).deliver_customer_statement(
        statement_data,
        DocumentDeliveryOptions(
            save_pdf=save_pdf,
            send_whatsapp=send_whatsapp,
            whatsapp_phone=whatsapp_phone,
        ),
    )
    return {"success": True, "statement": stmt["summary"], "pdf_delivery": delivery.to_dict()}


# Sales Endpoints ----------------------------------------------------------
@router.get("", response_model=List[SaleResponse])
@router.get("/", response_model=List[SaleResponse])
async def get_sales(
    response: Response,
    skip: int = 0,
    limit: int = 100,
    status: Optional[str] = None,
    customer_id: Optional[str] = None,
    start_date: Optional[date] = None,
    end_date: Optional[date] = None,
    search: Optional[str] = None,
    branch_id: Optional[str] = Query(None, description="Filter by branch id"),
    cashier_id: Optional[str] = Query(None, description="Filter by cashier/salesperson id"),
    db: Session = Depends(get_db),
    company_id: Optional[str] = Depends(get_current_company_id),
):
    # Get sales from the Sale table
    sale_query = db.query(Sale).options(
        joinedload(Sale.cost_center),
        joinedload(Sale.project)
    )

    if company_id:
        _branch_ids_sq = db.query(Branch.id).filter(Branch.company_id == company_id)
        sale_query = sale_query.filter(Sale.branch_id.in_(_branch_ids_sq))

    if status:
        sale_query = sale_query.filter(Sale.status == status)
    if customer_id:
        sale_query = sale_query.filter(Sale.customer_id == customer_id)
    if start_date:
        sale_query = sale_query.filter(Sale.date >= datetime.combine(start_date, datetime.min.time()))
    if end_date:
        sale_query = sale_query.filter(Sale.date <= datetime.combine(end_date, datetime.max.time()))
    if search:
        like = f"%{search}%"
        sale_query = sale_query.filter(or_(Sale.reference.ilike(like), Sale.notes.ilike(like)))
    if branch_id:
        sale_query = sale_query.filter(Sale.branch_id == branch_id)
    if cashier_id:
        sale_query = sale_query.filter(Sale.salesperson_id == cashier_id)

    # Also get PAID INVOICES as sales (they represent completed transactions)
    invoice_query = None
    if status is None or status == 'completed':
        from app.models.sales import Invoice
        invoice_query = db.query(Invoice).filter(Invoice.status == 'paid')

        if company_id:
            invoice_query = invoice_query.filter(Invoice.branch_id.in_(_branch_ids_sq))
        if customer_id:
            invoice_query = invoice_query.filter(Invoice.customer_id == customer_id)
        if start_date:
            invoice_query = invoice_query.filter(Invoice.date >= start_date)
        if end_date:
            invoice_query = invoice_query.filter(Invoice.date <= end_date)
        if search:
            like = f"%{search}%"
            invoice_query = invoice_query.filter(or_(Invoice.invoice_number.ilike(like), Invoice.notes.ilike(like)))
        if branch_id:
            invoice_query = invoice_query.filter(Invoice.branch_id == branch_id)

    # Get total count for pagination (both sales and invoices)
    sale_count = sale_query.count()
    invoice_count = invoice_query.count() if invoice_query else 0
    total_count = sale_count + invoice_count
    response.headers["X-Total-Count"] = str(total_count)

    # Fetch both sales and invoices
    sales = sale_query.order_by(Sale.date.desc()).offset(skip).limit(limit).all()
    invoices = invoice_query.order_by(Invoice.date.desc()).all() if invoice_query else []

    result: List[dict] = []

    # Process sales records
    for sale in sales:
        # Build salesperson name if available
        salesperson_name = None
        if getattr(sale, "salesperson", None):
            try:
                first = getattr(sale.salesperson, "first_name", None)
                last = getattr(sale.salesperson, "last_name", None)
                username = getattr(sale.salesperson, "username", None)
                parts = [p for p in [first, last] if p]
                salesperson_name = " ".join(parts) if parts else username
            except Exception:
                salesperson_name = None

        # Get dimensional data
        cost_center_name = None
        project_name = None
        if hasattr(sale, 'cost_center') and sale.cost_center:
            cost_center_name = sale.cost_center.name
        if hasattr(sale, 'project') and sale.project:
            project_name = sale.project.name

        # Get creator name
        created_by_name = None
        if hasattr(sale, 'created_by_user') and sale.created_by_user:
            try:
                first = getattr(sale.created_by_user, "first_name", None)
                last = getattr(sale.created_by_user, "last_name", None)
                username = getattr(sale.created_by_user, "username", None)
                parts = [p for p in [first, last] if p]
                created_by_name = " ".join(parts) if parts else username
            except Exception:
                created_by_name = None

        sale_dict = {
            "id": sale.id,
            "reference": sale.reference,
            "customer_id": sale.customer_id,
            "customer_name": sale.customer.name if sale.customer else "Walk-in Customer",
            "payment_method": sale.payment_method,
            "currency": sale.currency,
            "total_amount": float(sale.total_amount or 0),
            "total_amount_ex_vat": float(sale.total_amount_ex_vat or 0),
            "total_vat_amount": float(sale.total_vat_amount or 0),
            "amount_tendered": float(sale.amount_tendered or 0),
            "change_given": float(sale.change_given or 0),
            "discount_amount": float(sale.discount_amount or 0),
            "discount_percentage": float(sale.discount_percentage or 0),
            "status": sale.status,
            "date": sale.date,
            "notes": sale.notes,
            "salesperson_name": salesperson_name,
            "cost_center_id": sale.cost_center_id,
            "cost_center_name": cost_center_name,
            "project_id": sale.project_id,
            "project_name": project_name,
            "branch_id": sale.branch_id,
            "items_count": len(sale.sale_items),
            "items": [],
            # Audit trail fields
            "created_by": getattr(sale, 'created_by', None),
            "created_by_name": created_by_name,
            "created_from_branch_id": getattr(sale, 'created_from_branch_id', None),
            "created_at": getattr(sale, 'created_at', None),
            "updated_at": getattr(sale, 'updated_at', None),
        }
        for item in sale.sale_items:
            sale_dict["items"].append(
                {
                    "id": item.id,
                    "product_id": item.product_id,
                    "product_name": item.product.name if item.product else "Unknown Product",
                    "product_sku": item.product.sku if item.product else "",
                    "quantity": item.quantity,
                    "selling_price": float(item.selling_price or 0),
                    "vat_rate": float(item.vat_rate or 0),
                    "vat_amount": float(item.vat_amount or 0),
                    "discount_amount": float(item.discount_amount or 0),
                    "discount_percentage": float(item.discount_percentage or 0),
                    "total_amount": float(item.total_amount or 0),
                }
            )
        result.append(sale_dict)

    # Process paid invoice records as sales
    for invoice in invoices:
        # Get customer name
        customer_name = "Walk-in Customer"
        if invoice.customer:
            customer_name = invoice.customer.name

        # Get creator name
        created_by_name = None
        if hasattr(invoice, 'created_by_user') and invoice.created_by_user:
            try:
                first = getattr(invoice.created_by_user, "first_name", None)
                last = getattr(invoice.created_by_user, "last_name", None)
                username = getattr(invoice.created_by_user, "username", None)
                parts = [p for p in [first, last] if p]
                created_by_name = " ".join(parts) if parts else username
            except Exception:
                created_by_name = None

        # Get dimensional data
        cost_center_name = None
        project_name = None
        if hasattr(invoice, 'cost_center') and invoice.cost_center:
            cost_center_name = invoice.cost_center.name
        if hasattr(invoice, 'project') and invoice.project:
            project_name = invoice.project.name

        invoice_dict = {
            "id": invoice.id,
            "reference": invoice.invoice_number,  # Use invoice_number as reference
            "customer_id": invoice.customer_id,
            "customer_name": customer_name,
            "payment_method": "Invoice",  # Mark as invoice payment method
            "currency": "BWP",  # Default currency
            "total_amount": float(invoice.total_amount or 0),
            "total_amount_ex_vat": float(invoice.total or 0),
            "total_vat_amount": float(invoice.total_vat_amount or 0),
            "amount_tendered": float(invoice.total_amount or 0),
            "change_given": 0.0,
            "discount_amount": float(invoice.discount_amount or 0),
            "discount_percentage": float(invoice.discount_percentage or 0),
            "status": "completed",  # Paid invoices are completed transactions
            "date": datetime.combine(invoice.date, datetime.min.time()) if isinstance(invoice.date, date) else invoice.date,
            "notes": invoice.notes,
            "salesperson_name": None,
            "cost_center_id": invoice.cost_center_id,
            "cost_center_name": cost_center_name,
            "project_id": invoice.project_id,
            "project_name": project_name,
            "branch_id": invoice.branch_id,
            "items_count": len(invoice.invoice_items),
            "items": [],
            # Audit trail fields
            "created_by": invoice.created_by,
            "created_by_name": created_by_name,
            "created_from_branch_id": None,
            "created_at": invoice.created_at,
            "updated_at": invoice.updated_at,
        }

        # Add invoice items
        for item in invoice.invoice_items:
            invoice_dict["items"].append(
                {
                    "id": item.id,
                    "product_id": item.product_id,
                    "product_name": item.product.name if item.product else "Unknown Product",
                    "product_sku": item.product.sku if item.product else "",
                    "quantity": float(item.quantity or 0),
                    "selling_price": float(item.price or 0),
                    "vat_rate": float(item.vat_rate or 0),
                    "vat_amount": float(item.vat_amount or 0),
                    "discount_amount": float(item.discount_amount or 0),
                    "discount_percentage": float(item.discount_percentage or 0),
                    "total_amount": float(item.total or 0),
                }
            )
        result.append(invoice_dict)

    # Sort combined results by date (newest first) — guard against None dates
    result.sort(key=lambda x: x['date'] or datetime.min, reverse=True)

    # Apply offset and limit after combining
    result = result[skip:skip + limit]

    return result


@router.get("/statistics")
async def get_sales_statistics(
    branch_id: Optional[str] = Query(None, description="Filter by branch id"),
    cashier_id: Optional[str] = Query(None, description="Filter by cashier/salesperson id"),
    start_date: Optional[date] = Query(None, description="Optional start date for range"),
    end_date: Optional[date] = Query(None, description="Optional end date for range"),
    db: Session = Depends(get_db),
):
    """Return basic statistics used by the sales dashboard cards.

    Response shape matches sales.html expectations:
      {
        "today_sales": { "amount": float },
        "total_sales": { "count": int },
        "customer_count": int,
        "monthly_revenue": float
      }
    All metrics support optional branch and cashier filters; date range (if provided)
    will constrain total_sales count; today_sales is always for the current day; monthly_revenue is for the current month.
    """
    try:
        base_filters = []
        if branch_id:
            base_filters.append(Sale.branch_id == branch_id)
        if cashier_id:
            base_filters.append(Sale.salesperson_id == cashier_id)

        # Today's sales total
        today = datetime.utcnow().date()
        today_start = datetime.combine(today, datetime.min.time())
        today_end = datetime.combine(today, datetime.max.time())
        today_query = db.query(func.coalesce(func.sum(Sale.total_amount), 0.0))
        if base_filters:
            today_query = today_query.filter(and_(*base_filters))
        today_query = today_query.filter(Sale.date >= today_start, Sale.date <= today_end)
        today_amount = float(today_query.scalar() or 0.0)

        # Total sales count (optionally constrained by provided range)
        count_query = db.query(func.count(Sale.id))
        if base_filters:
            count_query = count_query.filter(and_(*base_filters))
        if start_date:
            count_query = count_query.filter(Sale.date >= datetime.combine(start_date, datetime.min.time()))
        if end_date:
            count_query = count_query.filter(Sale.date <= datetime.combine(end_date, datetime.max.time()))
        total_count = int(count_query.scalar() or 0)

        # Customer count (apply branch filter if provided)
        cust_query = db.query(func.count(func.distinct(Customer.id)))
        if branch_id:
            cust_query = cust_query.filter(Customer.branch_id == branch_id)
        customer_count = int(cust_query.scalar() or 0)

        # Monthly revenue for current month
        now = datetime.utcnow()
        month_start = datetime(now.year, now.month, 1)
        # Compute first day of next month then subtract microsecond
        if now.month == 12:
            next_month = datetime(now.year + 1, 1, 1)
        else:
            next_month = datetime(now.year, now.month + 1, 1)
        month_end = next_month - timedelta(microseconds=1)
        month_query = db.query(func.coalesce(func.sum(Sale.total_amount), 0.0))
        if base_filters:
            month_query = month_query.filter(and_(*base_filters))
        month_query = month_query.filter(Sale.date >= month_start, Sale.date <= month_end)
        monthly_revenue = float(month_query.scalar() or 0.0)

        return {
            "today_sales": {"amount": today_amount},
            "total_sales": {"count": total_count},
            "customer_count": customer_count,
            "monthly_revenue": monthly_revenue,
        }
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Failed to fetch sales statistics: {e}")


@router.post("", response_model=SaleResponse, status_code=status.HTTP_201_CREATED)
@router.post("/", response_model=SaleResponse, status_code=status.HTTP_201_CREATED)
async def create_sale(
    sale_data: SaleCreate,
    db: Session = Depends(get_db),
    current_user: User = Depends(get_current_user),
    x_user_id: Optional[str] = Header(None),
    x_username: Optional[str] = Header(None),
    company_id: Optional[str] = Depends(get_current_company_id),
):
    current_user_id = str(current_user.id)
    if x_user_id:
        current_user_id = x_user_id

    try:
        operating_branch_id = resolve_operating_branch_id(current_user, sale_data.branch_id)
    except ValueError as exc:
        raise HTTPException(status_code=400, detail=str(exc))

    _branch_q = db.query(Branch).filter(Branch.active == True)
    if company_id:
        _branch_q = _branch_q.filter(Branch.company_id == company_id)
    branch = _branch_q.first()
    if not branch and not operating_branch_id:
        raise HTTPException(status_code=400, detail="No active branch available to assign sale")
    if not operating_branch_id and branch:
        operating_branch_id = str(branch.id)

    # Default anonymous sales to the system Walk-in Customer for tracking
    from app.constants.walkin_customer import WALKIN_CUSTOMER_ID
    effective_customer_id = sale_data.customer_id or WALKIN_CUSTOMER_ID

    reference = f"SALE-{datetime.now().strftime('%Y%m%d%H%M%S')}-{str(uuid.uuid4())[:8]}"
    subtotal = 0.0
    total_vat = 0.0
    for item in sale_data.items:
        item_total = item.quantity * item.selling_price
        item_discount = item_total * (item.discount_percentage / 100) + item.discount_amount
        item_subtotal = item_total - item_discount
        item_vat = item_subtotal * (item.vat_rate / 100)
        subtotal += item_subtotal
        total_vat += item_vat

    sale_discount = subtotal * (sale_data.discount_percentage / 100) + sale_data.discount_amount
    final_subtotal = subtotal - sale_discount
    final_vat = total_vat * (final_subtotal / subtotal) if subtotal > 0 else 0
    total_amount = final_subtotal + final_vat
    change_given = sale_data.amount_tendered - total_amount

    sale = Sale(
        reference=reference,
        customer_id=effective_customer_id,
        payment_method=sale_data.payment_method,
        currency=sale_data.currency,
        total_amount=total_amount,
        total_amount_ex_vat=final_subtotal,
        total_vat_amount=final_vat,
        amount_tendered=sale_data.amount_tendered,
        change_given=change_given,
        discount_amount=sale_discount,
        discount_percentage=sale_data.discount_percentage,
        status="completed",
        date=datetime.now(),
        notes=sale_data.notes,
        branch_id=operating_branch_id,
        salesperson_id=sale_data.salesperson_id or current_user_id,
        cost_center_id=sale_data.cost_center,
        project_id=sale_data.project,
        # Audit trail
        created_by=current_user_id,
        created_from_branch_id=operating_branch_id,
        # Inter-branch collection tracking
        collection_branch_id=sale_data.collection_branch_id or operating_branch_id,
        collection_status="pending" if sale_data.collection_branch_id else "collected",  # Collected immediately if same branch
        collected_at=datetime.now() if not sale_data.collection_branch_id else None,
        collected_by=current_user_id if not sale_data.collection_branch_id else None,
    )
    db.add(sale)
    db.flush()

    # ── Pre-validate ALL items before writing anything ───────────────────────
    from app.models.inventory import SerialNumber as _SN, InventoryTransaction as _InvTx
    from datetime import date as _date

    for item in sale_data.items:
        _product = db.query(Product).filter(Product.id == item.product_id).first()
        if not _product:
            raise HTTPException(status_code=400, detail=f"Product '{item.product_id}' not found.")

        if is_inventory_tracked(_product) and getattr(_product, 'is_serialized', False):
            # Serialised: every unit must be identified by a valid, available serial number
            serials = [s.strip() for s in (item.serial_numbers or []) if s and s.strip()]
            if len(serials) != item.quantity:
                raise HTTPException(
                    status_code=400,
                    detail=(
                        f"'{_product.name}' is serialised — "
                        f"supply exactly {item.quantity} serial number(s), got {len(serials)}."
                    ),
                )
            for s in serials:
                sn = db.query(_SN).filter(_SN.serial == s).first()
                if not sn:
                    raise HTTPException(status_code=400, detail=f"Serial '{s}' not found in inventory.")
                if sn.product_id != item.product_id:
                    raise HTTPException(status_code=400, detail=f"Serial '{s}' belongs to a different product.")
                if sn.status != 'available':
                    raise HTTPException(status_code=400,
                        detail=f"Serial '{s}' is not available (status: {sn.status}).")
                if sn.branch_id and operating_branch_id and str(sn.branch_id) != str(operating_branch_id):
                    raise HTTPException(
                        status_code=400,
                        detail=f"Serial '{s}' is not at your branch.",
                    )
        elif is_inventory_tracked(_product):
            try:
                assert_sufficient_branch_stock(
                    db,
                    item.product_id,
                    operating_branch_id,
                    item.quantity,
                    _product.name,
                )
            except ValueError as exc:
                raise HTTPException(status_code=400, detail=str(exc))

    for item in sale_data.items:
        item_total = item.quantity * item.selling_price
        item_discount = item_total * (item.discount_percentage / 100) + item.discount_amount
        item_subtotal = item_total - item_discount
        item_vat = item_subtotal * (item.vat_rate / 100)

        inventory_branch_id = operating_branch_id

        # Fetch product to carry classification fields
        _product = db.query(Product).filter(Product.id == item.product_id).first()

        # Collect clean serials for this item
        item_serials = [s.strip() for s in (item.serial_numbers or []) if s and s.strip()]

        sale_item = SaleItem(
            sale_id=sale.id,
            product_id=item.product_id,
            quantity=item.quantity,
            selling_price=item.selling_price,
            vat_rate=item.vat_rate,
            vat_amount=item_vat,
            discount_amount=item.discount_amount,
            discount_percentage=item.discount_percentage,
            total_amount=item_subtotal + item_vat,
            inventory_source_branch_id=inventory_branch_id,
            category=_product.category if _product else None,
            brand=_product.brand if _product else None,
            condition=_product.condition if _product else None,
            location=_product.location if _product else None,
            serial_numbers=str(item_serials) if item_serials else '[]',
        )
        db.add(sale_item)
        db.flush()  # get sale_item.id for serial linking

        # ── Mark serialised units as sold ────────────────────────────────
        if item_serials:
            for s in item_serials:
                sn = db.query(_SN).filter(_SN.serial == s).first()
                if sn:
                    sn.status = 'sold'
                    sn.sale_id = sale.id
                    sn.sold_at = datetime.now().date()
                    sn.customer_id = effective_customer_id

        # ── Deduct stock (received branch allocation only) ────────────────
        if is_inventory_tracked(_product):
            if not deduct_received_branch_stock(
                db, item.product_id, inventory_branch_id, item.quantity
            ):
                raise HTTPException(
                    status_code=400,
                    detail=f"Could not deduct branch stock for '{_product.name}'.",
                )

        # ── Inventory transaction for audit trail ─────────────────────────
        if is_inventory_tracked(_product):
            try:
                _prod_audit = _product or db.query(Product).filter(Product.id == item.product_id).first()
                _new_qty = int((_prod_audit.quantity or 0)) if _prod_audit else None
                inv_tx = _InvTx(
                    product_id=item.product_id,
                    transaction_type='sale',
                    quantity=item.quantity,
                    unit_cost=_prod_audit.cost_price if _prod_audit else None,
                    date=_date.today(),
                    reference=f"SALE-{sale.reference}",
                    branch_id=sale.branch_id,
                    related_sale_id=sale.id,
                    new_quantity=_new_qty,
                )
                db.add(inv_tx)
            except Exception as _inv_err:
                print(f"[WARN] inventory transaction for sale {sale.id} failed: {_inv_err}")

    db.commit()
    db.refresh(sale)

    # Create IFRS-compliant journal entries for the sale
    try:
        ifrs_service = IFRSAccountingService(db)
        bank_account_id = None
        if (sale.payment_method or '').lower() == 'card':
            # Load default card bank account from settings
            settings_svc = AppSettingService(db)
            bank_account_id = settings_svc.get_branch_default_card_bank_account(sale.branch_id)
            if not bank_account_id:
                bank_account_id = settings_svc.get_global_default_card_bank_account()
        journal_entries = ifrs_service.create_sale_journal_entries(sale, bank_account_id=bank_account_id)
        print(f"Successfully created {len(journal_entries)} IFRS journal entries for sale {sale.id}")
    except Exception as e:
        # Log the error but do not fail the sale creation
        print(f"Warning: Failed to create IFRS journal entries for sale {sale.id}: {str(e)}")
        # Optionally, you could rollback the sale here if accounting entries are critical
        # db.rollback()
        # raise HTTPException(status_code=500, detail=f"Failed to create accounting entries: {str(e)}")

    # get_sale expects a UUID to avoid route collisions with static paths
    return await get_sale(uuid.UUID(sale.id), db)


@router.get("/by-id/{sale_id}", response_model=SaleResponse)
async def get_sale(
    sale_id: uuid.UUID,
    db: Session = Depends(get_db),
    # # # current_user parameter removed for development,  # Removed for development,  # Removed for development,
):
    from sqlalchemy.orm import joinedload
    sale = db.query(Sale).options(
        joinedload(Sale.customer),
        joinedload(Sale.branch),
        joinedload(Sale.salesperson),
        joinedload(Sale.sale_items)
    ).filter(Sale.id == str(sale_id)).first()
    if not sale:
        raise HTTPException(status_code=404, detail="Sale not found")
    # require_branch_match(..., current_user)  # Removed for development
    salesperson_name = None
    if getattr(sale, "salesperson", None):
        try:
            first = getattr(sale.salesperson, "first_name", None)
            last = getattr(sale.salesperson, "last_name", None)
            username = getattr(sale.salesperson, "username", None)
            parts = [p for p in [first, last] if p]
            salesperson_name = " ".join(parts) if parts else username
        except Exception:
            salesperson_name = None

    branch_name = None
    if getattr(sale, "branch", None):
        try:
            branch_name = sale.branch.name
        except Exception:
            branch_name = None

    sale_dict = {
        "id": sale.id,
        "reference": sale.reference,
        "customer_id": sale.customer_id,
        "customer_name": sale.customer.name if sale.customer else "Walk-in Customer",
        "payment_method": sale.payment_method,
        "currency": sale.currency,
        "total_amount": float(sale.total_amount or 0),
        "total_amount_ex_vat": float(sale.total_amount_ex_vat or 0),
        "total_vat_amount": float(sale.total_vat_amount or 0),
        "amount_tendered": float(sale.amount_tendered or 0),
        "change_given": float(sale.change_given or 0),
        "discount_amount": float(sale.discount_amount or 0),
        "discount_percentage": float(sale.discount_percentage or 0),
        "status": sale.status,
        "date": sale.date,
        "notes": sale.notes,
        "salesperson_name": salesperson_name,
        "branch_name": branch_name,
        "branch_id": sale.branch_id,
        "cost_center": getattr(sale, "cost_center_id", None),
        "project": getattr(sale, "project_id", None),
        "items_count": len(sale.sale_items),
        "items": [],
    }
    for item in sale.sale_items:
        sale_dict["items"].append(
            {
                "id": item.id,
                "product_id": item.product_id,
                "product_name": item.product.name if item.product else "Unknown Product",
                "product_sku": item.product.sku if item.product else "",
                "quantity": item.quantity,
                "selling_price": float(item.selling_price or 0),
                "vat_rate": float(item.vat_rate or 0),
                "vat_amount": float(item.vat_amount or 0),
                "discount_amount": float(item.discount_amount or 0),
                "discount_percentage": float(item.discount_percentage or 0),
                "total_amount": float(item.total_amount or 0),
                "category": item.category or (item.product.category if item.product else None),
                "brand": item.brand or (item.product.brand if item.product else None),
                "condition": item.condition or (item.product.condition if item.product else None),
                "location": item.location or (item.product.location if item.product else None),
            }
        )
    return sale_dict


@router.patch("/{sale_id}", response_model=SaleResponse)
async def update_sale_metadata(
    sale_id: str,
    sale_data: SaleUpdate,
    db: Session = Depends(get_db),
):
    """Lightweight sale update for metadata and tender values.

    Does not recalculate line items or totals; intended for correcting
    payment method, notes, dimensional tags, and tendered amount without
    disturbing posted accounting entries.
    """
    sale = db.query(Sale).options(joinedload(Sale.sale_items)).filter(Sale.id == sale_id).first()
    if not sale:
        raise HTTPException(status_code=404, detail="Sale not found")

    update_data = sale_data.dict(exclude_unset=True)

    if "amount_tendered" in update_data and update_data["amount_tendered"] is not None:
        new_tender = Decimal(str(update_data["amount_tendered"] or 0))
        current_total = Decimal(str(sale.total_amount or 0))
        if new_tender + Decimal("0.0001") < current_total:
            raise HTTPException(
                status_code=400,
                detail="Amount tendered cannot be less than total amount for the sale",
            )
        sale.amount_tendered = new_tender
        sale.change_given = new_tender - current_total

    field_map = {
        "customer_id": "customer_id",
        "payment_method": "payment_method",
        "currency": "currency",
        "notes": "notes",
        "status": "status",
        "salesperson_id": "salesperson_id",
        "cost_center": "cost_center_id",
        "project": "project_id",
        "branch_id": "branch_id",
    }

    for key, attr in field_map.items():
        if key in update_data:
            setattr(sale, attr, update_data[key])

    db.commit()
    db.refresh(sale)

    return await get_sale(uuid.UUID(sale.id), db)


# Invoice Endpoints --------------------------------------------------------



class InvoiceBase(BaseModel):
    customer_id: str
    due_date: Optional[date] = None
    payment_terms: int = 30
    discount_amount: float = 0.0
    discount_percentage: float = 0.0
    notes: Optional[str] = None

class InvoiceCreate(InvoiceBase):
    items: List[InvoiceItemCreate]

class InvoiceResponse(InvoiceBase):
    id: str
    invoice_number: str
    date: date
    status: str
    total: float
    total_vat_amount: float
    total_amount: float
    amount_paid: float
    customer_name: Optional[str] = None
    items_count: int

from fastapi.responses import StreamingResponse
import csv
from app.utils.logger import get_logger, log_exception, log_error_with_context

logger = get_logger(__name__)
from io import StringIO


@router.get("/export")
@router.get("/export.csv")
async def export_sales_csv(
    status: Optional[str] = None,
    customer_id: Optional[str] = None,
    start_date: Optional[date] = None,
    end_date: Optional[date] = None,
    search: Optional[str] = None,
    branch_id: Optional[str] = Query(None, description="Filter by branch id"),
    cashier_id: Optional[str] = Query(None, description="Filter by cashier/salesperson id"),
    db: Session = Depends(get_db),
):
    """Export filtered sales to CSV (ignores pagination)."""
    try:
        query = db.query(Sale)
        if status:
            query = query.filter(Sale.status == status)
        if customer_id:
            query = query.filter(Sale.customer_id == customer_id)
        if start_date:
            query = query.filter(Sale.date >= datetime.combine(start_date, datetime.min.time()))
        if end_date:
            query = query.filter(Sale.date <= datetime.combine(end_date, datetime.max.time()))
        if search:
            like = f"%{search}%"
            query = query.filter(or_(Sale.reference.ilike(like), Sale.notes.ilike(like)))
        if branch_id:
            query = query.filter(Sale.branch_id == branch_id)
        if cashier_id:
            query = query.filter(Sale.salesperson_id == cashier_id)

        sales = query.order_by(Sale.date.desc()).all()

        # Build CSV
        buffer = StringIO()
        writer = csv.writer(buffer)
        writer.writerow([
            "Reference",
            "Date",
            "Customer",
            "Cashier",
            "Payment Method",
            "Currency",
            "Subtotal (Ex VAT)",
            "VAT Amount",
            "Total Amount",
            "Status",
            "Items Count",
            "Notes",
        ])
        for sale in sales:
            # Build salesperson name if available
            cashier = None
            if getattr(sale, "salesperson", None):
                first = getattr(sale.salesperson, "first_name", None)
                last = getattr(sale.salesperson, "last_name", None)
                username = getattr(sale.salesperson, "username", None)
                parts = [p for p in [first, last] if p]
                cashier = " ".join(parts) if parts else username
            writer.writerow([
                sale.reference,
                sale.date.isoformat() if sale.date else "",
                sale.customer.name if sale.customer else "Walk-in Customer",
                cashier or "",
                sale.payment_method or "",
                sale.currency or "",
                float(sale.total_amount_ex_vat or 0),
                float(sale.total_vat_amount or 0),
                float(sale.total_amount or 0),
                sale.status or "",
                len(sale.sale_items or []),
                (sale.notes or "").replace("\n", " ").strip(),
            ])

        buffer.seek(0)
        filename = f"sales_export_{datetime.now().strftime('%Y%m%d_%H%M%S')}.csv"
        headers = {
            "Content-Disposition": f"attachment; filename={filename}",
            "Content-Type": "text/csv; charset=utf-8",
        }
        return StreamingResponse(iter([buffer.getvalue()]), media_type="text/csv", headers=headers)
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Failed to export sales: {e}")

# =============================
# Invoice Endpoints
# =============================
@router.get('/invoices', response_model=List[InvoiceResponse])
async def get_invoices(
    skip: int = 0,
    limit: int = 100,
    status: Optional[str] = None,
    customer_id: Optional[str] = None,
    db: Session = Depends(get_db),
    company_id: Optional[str] = Depends(get_current_company_id),
):
    query = db.query(Invoice)
    if company_id:
        branch_ids_sq = db.query(Branch.id).filter(Branch.company_id == company_id)
        query = query.filter(Invoice.branch_id.in_(branch_ids_sq))
    if status:
        query = query.filter(Invoice.status == status)
    if customer_id:
        query = query.filter(Invoice.customer_id == customer_id)
    invoices = query.order_by(Invoice.date.desc()).offset(skip).limit(limit).all()
    result = []
    for inv in invoices:
        result.append({
            'id': inv.id,
            'invoice_number': inv.invoice_number,
            'customer_id': inv.customer_id,
            'customer_name': inv.customer.name if inv.customer else 'Unknown Customer',
            'date': inv.date,
            'status': inv.status,
            'total': float(inv.total or 0),
            'total_vat_amount': float(inv.total_vat_amount or 0),
            'total_amount': float(inv.total_amount or 0),
            'amount_paid': float(inv.amount_paid or 0),
            'due_date': inv.due_date,
            'payment_terms': inv.payment_terms,
            'discount_amount': float(inv.discount_amount or 0),
            'discount_percentage': float(inv.discount_percentage or 0),
            'notes': inv.notes,
            'items_count': len(inv.invoice_items)
        })
    return result


# Cash Management Endpoints -----------------------------------------------

@router.post("/cash-submissions", response_model=CashSubmissionResponse, status_code=status.HTTP_201_CREATED)
async def submit_cash_takings(
    submission: CashSubmissionCreate,
    db: Session = Depends(get_db),
):
    """
    Submit cash takings from a salesperson to manager/accounting.
    This moves cash from Undeposited Funds (1114) to Cash in Hand (1111).
    """
    # Create the cash submission record
    submission_id = str(uuid.uuid4())

    # Create IFRS journal entries for the submission
    try:
        ifrs_service = IFRSAccountingService(db)
        journal_entries = ifrs_service.submit_cash_takings(
            salesperson_id=submission.salesperson_id,
            amount=Decimal(str(submission.amount)),
            submission_date=submission.submission_date,
            branch_id=submission.branch_id,
            notes=submission.notes
        )

        # Get the first journal entry ID for reference
        journal_entry_id = journal_entries[0].id if journal_entries else None

    except Exception as e:
        raise HTTPException(
            status_code=500,
            detail=f"Failed to create accounting entries: {str(e)}"
        )

    # Create the submission record
    cash_submission = CashSubmission(
        id=submission_id,
        salesperson_id=submission.salesperson_id,
        received_by_id=submission.received_by_id,
        amount=Decimal(str(submission.amount)),
        submission_date=submission.submission_date,
        branch_id=submission.branch_id,
        journal_entry_id=journal_entry_id,
        status=CashSubmissionStatus.POSTED,
        notes=submission.notes
    )

    db.add(cash_submission)
    db.commit()
    db.refresh(cash_submission)

    # Build response with names
    salesperson_name = None
    received_by_name = None

    if cash_submission.salesperson:
        try:
            first = getattr(cash_submission.salesperson, "first_name", None)
            last = getattr(cash_submission.salesperson, "last_name", None)
            username = getattr(cash_submission.salesperson, "username", None)
            parts = [p for p in [first, last] if p]
            salesperson_name = " ".join(parts) if parts else username
        except Exception:
            pass

    if cash_submission.received_by:
        try:
            first = getattr(cash_submission.received_by, "first_name", None)
            last = getattr(cash_submission.received_by, "last_name", None)
            username = getattr(cash_submission.received_by, "username", None)
            parts = [p for p in [first, last] if p]
            received_by_name = " ".join(parts) if parts else username
        except Exception:
            pass

    return CashSubmissionResponse(
        id=cash_submission.id,
        salesperson_id=cash_submission.salesperson_id,
        salesperson_name=salesperson_name,
        received_by_id=cash_submission.received_by_id,
        received_by_name=received_by_name,
        amount=float(cash_submission.amount),
        submission_date=cash_submission.submission_date,
        branch_id=cash_submission.branch_id,
        journal_entry_id=cash_submission.journal_entry_id,
        status=cash_submission.status.value,
        notes=cash_submission.notes,
        created_at=cash_submission.created_at,
        updated_at=cash_submission.updated_at
    )


@router.get("/cash-submissions", response_model=List[CashSubmissionResponse])
async def get_cash_submissions(
    start_date: Optional[date] = Query(None, description="Filter by submission date (from)"),
    end_date: Optional[date] = Query(None, description="Filter by submission date (to)"),
    salesperson_id: Optional[str] = Query(None, description="Filter by salesperson"),
    branch_id: Optional[str] = Query(None, description="Filter by branch"),
    status_filter: Optional[str] = Query(None, description="Filter by status"),
    db: Session = Depends(get_db),
    company_id: Optional[str] = Depends(get_current_company_id),
):
    """
    Get list of cash submissions with optional filters.
    """
    query = db.query(CashSubmission)

    if company_id and not branch_id:
        _branch_ids_sq = db.query(Branch.id).filter(Branch.company_id == company_id)
        query = query.filter(CashSubmission.branch_id.in_(_branch_ids_sq))

    if start_date:
        query = query.filter(CashSubmission.submission_date >= start_date)
    if end_date:
        query = query.filter(CashSubmission.submission_date <= end_date)
    if salesperson_id:
        query = query.filter(CashSubmission.salesperson_id == salesperson_id)
    if branch_id:
        query = query.filter(CashSubmission.branch_id == branch_id)
    if status_filter:
        query = query.filter(CashSubmission.status == status_filter)

    submissions = query.order_by(CashSubmission.submission_date.desc()).all()

    result = []
    for sub in submissions:
        salesperson_name = None
        received_by_name = None

        if sub.salesperson:
            try:
                first = getattr(sub.salesperson, "first_name", None)
                last = getattr(sub.salesperson, "last_name", None)
                username = getattr(sub.salesperson, "username", None)
                parts = [p for p in [first, last] if p]
                salesperson_name = " ".join(parts) if parts else username
            except Exception:
                pass

        if sub.received_by:
            try:
                first = getattr(sub.received_by, "first_name", None)
                last = getattr(sub.received_by, "last_name", None)
                username = getattr(sub.received_by, "username", None)
                parts = [p for p in [first, last] if p]
                received_by_name = " ".join(parts) if parts else username
            except Exception:
                pass

        result.append(CashSubmissionResponse(
            id=sub.id,
            salesperson_id=sub.salesperson_id,
            salesperson_name=salesperson_name,
            received_by_id=sub.received_by_id,
            received_by_name=received_by_name,
            amount=float(sub.amount),
            submission_date=sub.submission_date,
            branch_id=sub.branch_id,
            journal_entry_id=sub.journal_entry_id,
            status=sub.status.value,
            notes=sub.notes,
            created_at=sub.created_at,
            updated_at=sub.updated_at
        ))

    return result


@router.get("/cash-submissions/{submission_id}", response_model=CashSubmissionResponse)
async def get_cash_submission_by_id(
    submission_id: str,
    db: Session = Depends(get_db),
):
    """Get a specific cash submission by ID"""
    submission = db.query(CashSubmission).filter(CashSubmission.id == submission_id).first()

    if not submission:
        raise HTTPException(status_code=404, detail="Cash submission not found")

    # Build names
    salesperson_name = None
    received_by_name = None

    if submission.salesperson:
        try:
            first = getattr(submission.salesperson, "first_name", None)
            last = getattr(submission.salesperson, "last_name", None)
            username = getattr(submission.salesperson, "username", None)
            parts = [p for p in [first, last] if p]
            salesperson_name = " ".join(parts) if parts else username
        except Exception:
            pass

    if submission.received_by:
        try:
            first = getattr(submission.received_by, "first_name", None)
            last = getattr(submission.received_by, "last_name", None)
            username = getattr(submission.received_by, "username", None)
            parts = [p for p in [first, last] if p]
            received_by_name = " ".join(parts) if parts else username
        except Exception:
            pass

    return CashSubmissionResponse(
        id=submission.id,
        salesperson_id=submission.salesperson_id,
        salesperson_name=salesperson_name,
        received_by_id=submission.received_by_id,
        received_by_name=received_by_name,
        amount=float(submission.amount),
        submission_date=submission.submission_date,
        branch_id=submission.branch_id,
        journal_entry_id=submission.journal_entry_id,
        status=submission.status.value,
        notes=submission.notes,
        created_at=submission.created_at,
        updated_at=submission.updated_at
    )


@router.post("/cash-submissions/{submission_id}/approve")
async def approve_cash_submission(
    submission_id: str,
    db: Session = Depends(get_db),
):
    """Approve a pending cash submission"""
    submission = db.query(CashSubmission).filter(CashSubmission.id == submission_id).first()

    if not submission:
        raise HTTPException(status_code=404, detail="Cash submission not found")

    if submission.status != CashSubmissionStatus.PENDING:
        raise HTTPException(
            status_code=400,
            detail=f"Cannot approve submission with status: {submission.status.value}"
        )

    submission.status = CashSubmissionStatus.POSTED
    submission.updated_at = datetime.utcnow()

    db.commit()
    db.refresh(submission)

    return {
        "success": True,
        "message": "Cash submission approved successfully",
        "data": {
            "id": submission.id,
            "status": submission.status.value,
            "updated_at": submission.updated_at
        }
    }


@router.post("/cash-submissions/{submission_id}/reject")
async def reject_cash_submission(
    submission_id: str,
    db: Session = Depends(get_db),
):
    """Reject a pending cash submission"""
    submission = db.query(CashSubmission).filter(CashSubmission.id == submission_id).first()

    if not submission:
        raise HTTPException(status_code=404, detail="Cash submission not found")

    if submission.status != CashSubmissionStatus.PENDING:
        raise HTTPException(
            status_code=400,
            detail=f"Cannot reject submission with status: {submission.status.value}"
        )

    # Note: You might want to add a REJECTED status to CashSubmissionStatus enum
    # For now, we'll keep it as PENDING but add a note
    submission.notes = f"REJECTED: {submission.notes or ''}"
    submission.updated_at = datetime.utcnow()

    db.commit()
    db.refresh(submission)

    return {
        "success": True,
        "message": "Cash submission rejected",
        "data": {
            "id": submission.id,
            "status": submission.status.value,
            "notes": submission.notes,
            "updated_at": submission.updated_at
        }
    }


@router.post("/float-allocations", response_model=FloatAllocationResponse, status_code=status.HTTP_201_CREATED)
async def allocate_float(
    allocation: FloatAllocationCreate,
    db: Session = Depends(get_db),
):
    """
    Allocate float/change to a cashier.
    This moves cash from Cash in Hand (1111) to Petty Cash (1112) or similar.
    """
    from app.models.accounting import AccountingCode, JournalEntry, AccountingEntry

    allocation_id = str(uuid.uuid4())

    # Create accounting entries for float allocation
    try:
        # Get accounting codes
        petty_cash_code = db.query(AccountingCode).filter(AccountingCode.code == '1112').first()
        cash_in_hand_code = db.query(AccountingCode).filter(AccountingCode.code == '1111').first()

        if not petty_cash_code or not cash_in_hand_code:
            raise HTTPException(
                status_code=500,
                detail="Required accounting codes not found (1111 Cash in Hand, 1112 Petty Cash)"
            )

        # Create accounting entry
        accounting_entry = AccountingEntry(
            date_posted=allocation.allocation_date,
            particulars=f"Float allocation to cashier - FLOAT-{allocation_id[:8]}",
            status='posted',
            branch_id=allocation.branch_id
        )
        db.add(accounting_entry)
        db.flush()

        # Dr Petty Cash (1112)
        journal_entry_dr = JournalEntry(
            accounting_entry_id=accounting_entry.id,
            accounting_code_id=petty_cash_code.id,
            entry_type='debit',
            debit_amount=Decimal(str(allocation.float_amount)),
            credit_amount=Decimal('0'),
            description=f"Float allocated to cashier",
            date=allocation.allocation_date,
            date_posted=allocation.allocation_date
        )
        db.add(journal_entry_dr)

        # Cr Cash in Hand (1111)
        journal_entry_cr = JournalEntry(
            accounting_entry_id=accounting_entry.id,
            accounting_code_id=cash_in_hand_code.id,
            entry_type='credit',
            debit_amount=Decimal('0'),
            credit_amount=Decimal(str(allocation.float_amount)),
            description=f"Float allocated from cash in hand",
            date=allocation.allocation_date,
            date_posted=allocation.allocation_date
        )
        db.add(journal_entry_cr)

        allocation_journal_entry_id = journal_entry_dr.id

    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(
            status_code=500,
            detail=f"Failed to create accounting entries: {str(e)}"
        )

    # Create float allocation record
    float_allocation = FloatAllocation(
        id=allocation_id,
        cashier_id=allocation.cashier_id,
        allocated_by_id=allocation.allocated_by_id,
        float_amount=Decimal(str(allocation.float_amount)),
        amount_returned=Decimal('0'),
        allocation_date=allocation.allocation_date,
        branch_id=allocation.branch_id,
        allocation_journal_entry_id=allocation_journal_entry_id,
        status=FloatAllocationStatus.ALLOCATED,
        notes=allocation.notes
    )

    db.add(float_allocation)
    db.commit()
    db.refresh(float_allocation)

    # Build response with names
    cashier_name = None
    allocated_by_name = None

    if float_allocation.cashier:
        try:
            first = getattr(float_allocation.cashier, "first_name", None)
            last = getattr(float_allocation.cashier, "last_name", None)
            username = getattr(float_allocation.cashier, "username", None)
            parts = [p for p in [first, last] if p]
            cashier_name = " ".join(parts) if parts else username
        except Exception:
            pass

    if float_allocation.allocated_by:
        try:
            first = getattr(float_allocation.allocated_by, "first_name", None)
            last = getattr(float_allocation.allocated_by, "last_name", None)
            username = getattr(float_allocation.allocated_by, "username", None)
            parts = [p for p in [first, last] if p]
            allocated_by_name = " ".join(parts) if parts else username
        except Exception:
            pass

    return FloatAllocationResponse(
        id=float_allocation.id,
        cashier_id=float_allocation.cashier_id,
        cashier_name=cashier_name,
        allocated_by_id=float_allocation.allocated_by_id,
        allocated_by_name=allocated_by_name,
        float_amount=float(float_allocation.float_amount),
        amount_returned=float(float_allocation.amount_returned),
        allocation_date=float_allocation.allocation_date,
        return_date=float_allocation.return_date,
        branch_id=float_allocation.branch_id,
        allocation_journal_entry_id=float_allocation.allocation_journal_entry_id,
        return_journal_entry_id=float_allocation.return_journal_entry_id,
        status=float_allocation.status.value,
        notes=float_allocation.notes,
        created_at=float_allocation.created_at,
        updated_at=float_allocation.updated_at
    )


@router.get("/float-allocations", response_model=List[FloatAllocationResponse])
async def get_float_allocations(
    cashier_id: Optional[str] = Query(None, description="Filter by cashier"),
    branch_id: Optional[str] = Query(None, description="Filter by branch"),
    status_filter: Optional[str] = Query(None, description="Filter by status (allocated/returned)"),
    start_date: Optional[date] = Query(None, description="Filter by allocation date (from)"),
    end_date: Optional[date] = Query(None, description="Filter by allocation date (to)"),
    db: Session = Depends(get_db),
    company_id: Optional[str] = Depends(get_current_company_id),
):
    """
    Get list of float allocations with optional filters.
    """
    query = db.query(FloatAllocation)

    if company_id and not branch_id:
        _branch_ids_sq = db.query(Branch.id).filter(Branch.company_id == company_id)
        query = query.filter(FloatAllocation.branch_id.in_(_branch_ids_sq))

    if cashier_id:
        query = query.filter(FloatAllocation.cashier_id == cashier_id)
    if branch_id:
        query = query.filter(FloatAllocation.branch_id == branch_id)
    if status_filter:
        query = query.filter(FloatAllocation.status == status_filter)
    if start_date:
        query = query.filter(FloatAllocation.allocation_date >= start_date)
    if end_date:
        query = query.filter(FloatAllocation.allocation_date <= end_date)

    allocations = query.order_by(FloatAllocation.allocation_date.desc()).all()

    result = []
    for alloc in allocations:
        cashier_name = None
        allocated_by_name = None

        if alloc.cashier:
            try:
                first = getattr(alloc.cashier, "first_name", None)
                last = getattr(alloc.cashier, "last_name", None)
                username = getattr(alloc.cashier, "username", None)
                parts = [p for p in [first, last] if p]
                cashier_name = " ".join(parts) if parts else username
            except Exception:
                pass

        if alloc.allocated_by:
            try:
                first = getattr(alloc.allocated_by, "first_name", None)
                last = getattr(alloc.allocated_by, "last_name", None)
                username = getattr(alloc.allocated_by, "username", None)
                parts = [p for p in [first, last] if p]
                allocated_by_name = " ".join(parts) if parts else username
            except Exception:
                pass

        result.append(FloatAllocationResponse(
            id=alloc.id,
            cashier_id=alloc.cashier_id,
            cashier_name=cashier_name,
            allocated_by_id=alloc.allocated_by_id,
            allocated_by_name=allocated_by_name,
            float_amount=float(alloc.float_amount),
            amount_returned=float(alloc.amount_returned),
            allocation_date=alloc.allocation_date,
            return_date=alloc.return_date,
            branch_id=alloc.branch_id,
            allocation_journal_entry_id=alloc.allocation_journal_entry_id,
            return_journal_entry_id=alloc.return_journal_entry_id,
            status=alloc.status.value,
            notes=alloc.notes,
            created_at=alloc.created_at,
            updated_at=alloc.updated_at
        ))

    return result


@router.put("/float-allocations/{allocation_id}/return", response_model=FloatAllocationResponse)
async def return_float(
    allocation_id: str,
    float_return: FloatReturnCreate,
    db: Session = Depends(get_db),
):
    """
    Record the return of float from a cashier.
    This moves cash from Petty Cash (1112) back to Cash in Hand (1111).
    """
    from app.models.accounting import AccountingCode, JournalEntry, AccountingEntry

    # Get the allocation
    allocation = db.query(FloatAllocation).filter(FloatAllocation.id == allocation_id).first()
    if not allocation:
        raise HTTPException(status_code=404, detail="Float allocation not found")

    if allocation.status == FloatAllocationStatus.RETURNED:
        raise HTTPException(status_code=400, detail="Float already returned")

    # Validate return amount
    return_amount = Decimal(str(float_return.amount_returned))
    remaining = allocation.float_amount - allocation.amount_returned

    if return_amount > remaining:
        raise HTTPException(
            status_code=400,
            detail=f"Return amount ({return_amount}) exceeds remaining float ({remaining})"
        )

    # Create accounting entries for float return
    try:
        # Get accounting codes
        petty_cash_code = db.query(AccountingCode).filter(AccountingCode.code == '1112').first()
        cash_in_hand_code = db.query(AccountingCode).filter(AccountingCode.code == '1111').first()

        if not petty_cash_code or not cash_in_hand_code:
            raise HTTPException(
                status_code=500,
                detail="Required accounting codes not found"
            )

        # Create accounting entry
        accounting_entry = AccountingEntry(
            id=str(uuid.uuid4()),
            entry_date=float_return.return_date,
            description=f"Float return from cashier",
            reference=f"FLOATRET-{allocation_id[:8]}",
            entry_type="float_return",
            branch_id=allocation.branch_id
        )
        db.add(accounting_entry)
        db.flush()

        # Dr Cash in Hand (1111)
        journal_entry_dr = JournalEntry(
            id=str(uuid.uuid4()),
            accounting_entry_id=accounting_entry.id,
            accounting_code_id=cash_in_hand_code.id,
            entry_type='debit',
            debit_amount=return_amount,
            credit_amount=Decimal('0'),
            description=f"Float returned to cash in hand"
        )
        db.add(journal_entry_dr)

        # Cr Petty Cash (1112)
        journal_entry_cr = JournalEntry(
            id=str(uuid.uuid4()),
            accounting_entry_id=accounting_entry.id,
            accounting_code_id=petty_cash_code.id,
            entry_type='credit',
            debit_amount=Decimal('0'),
            credit_amount=return_amount,
            description=f"Float returned from cashier"
        )
        db.add(journal_entry_cr)

        return_journal_entry_id = journal_entry_dr.id

    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(
            status_code=500,
            detail=f"Failed to create accounting entries: {str(e)}"
        )

    # Update allocation
    allocation.amount_returned = allocation.amount_returned + return_amount
    allocation.return_date = float_return.return_date
    allocation.return_journal_entry_id = return_journal_entry_id

    # Update status
    if allocation.amount_returned >= allocation.float_amount:
        allocation.status = FloatAllocationStatus.RETURNED
    else:
        allocation.status = FloatAllocationStatus.PARTIALLY_RETURNED

    if float_return.notes:
        allocation.notes = (allocation.notes or "") + f"\nReturn: {float_return.notes}"

    db.commit()
    db.refresh(allocation)

    # Build response with names
    cashier_name = None
    allocated_by_name = None

    if allocation.cashier:
        try:
            first = getattr(allocation.cashier, "first_name", None)
            last = getattr(allocation.cashier, "last_name", None)
            username = getattr(allocation.cashier, "username", None)
            parts = [p for p in [first, last] if p]
            cashier_name = " ".join(parts) if parts else username
        except Exception:
            pass

    if allocation.allocated_by:
        try:
            first = getattr(allocation.allocated_by, "first_name", None)
            last = getattr(allocation.allocated_by, "last_name", None)
            username = getattr(allocation.allocated_by, "username", None)
            parts = [p for p in [first, last] if p]
            allocated_by_name = " ".join(parts) if parts else username
        except Exception:
            pass

    return FloatAllocationResponse(
        id=allocation.id,
        cashier_id=allocation.cashier_id,
        cashier_name=cashier_name,
        allocated_by_id=allocation.allocated_by_id,
        allocated_by_name=allocated_by_name,
        float_amount=float(allocation.float_amount),
        amount_returned=float(allocation.amount_returned),
        allocation_date=allocation.allocation_date,
        return_date=allocation.return_date,
        branch_id=allocation.branch_id,
        allocation_journal_entry_id=allocation.allocation_journal_entry_id,
        return_journal_entry_id=allocation.return_journal_entry_id,
        status=allocation.status.value,
        notes=allocation.notes,
        created_at=allocation.created_at,
        updated_at=allocation.updated_at
    )


# Dimensional Accounting GL Posting Endpoints ==============================

class GLPostingResponse(BaseModel):
    """Response for GL posting operations"""
    success: bool
    invoice_id: str
    entries_created: int
    journal_entry_ids: List[str]
    total_amount: float
    posting_date: str


class DimensionAccountingDetailsResponse(BaseModel):
    """Invoice accounting dimension details"""
    invoice_id: str
    invoice_number: str
    total_amount: float
    cost_center: Optional[str] = None
    project: Optional[str] = None
    department: Optional[str] = None
    revenue_account: Optional[str] = None
    ar_account: Optional[str] = None
    posting_status: str
    last_posted_date: Optional[str] = None


class JournalEntryResponse(BaseModel):
    """Journal entry response"""
    id: str
    accounting_code: str
    debit_amount: float
    credit_amount: float
    description: str
    source: str
    entry_date: str
    dimensions: List[Dict] = []


class DimensionalAnalysisResponse(BaseModel):
    """Dimensional analysis for sales"""
    total_revenue: float
    by_cost_center: Dict[str, float]
    by_project: Dict[str, float]
    by_department: Dict[str, float]


class ReconciliationResponse(BaseModel):
    """Reconciliation result"""
    period: str
    invoice_total: float
    gl_total: float
    variance: float
    is_reconciled: bool
    by_dimension: List[Dict] = []


@router.post("/invoices/{invoice_id}/post-accounting", response_model=GLPostingResponse, tags=["accounting"])
def post_invoice_to_accounting(
    invoice_id: str,
    user_id: Optional[str] = None,
    db: Session = Depends(get_db)
):
    """
    Post sales invoice to General Ledger with dimensional assignments.
    Creates AR Debit and Revenue Credit entries with dimension tracking.
    """
    try:
        service = SalesService(db)
        result = service.post_sale_to_accounting(invoice_id, user_id)
        return result
    except ValueError as e:
        raise HTTPException(status_code=400, detail=str(e))
    except Exception as e:
        raise HTTPException(status_code=500, detail=f"Error posting to accounting: {str(e)}")


@router.get("/invoices/{invoice_id}/accounting-details", response_model=DimensionAccountingDetailsResponse, tags=["accounting"])
def get_invoice_accounting_details(
    invoice_id: str,
    db: Session = Depends(get_db)
):
    """
    Get detailed accounting dimension information for an invoice.
    Shows cost center, project, department assignments and GL accounts.
    """
    try:
        invoice = db.query(Invoice).filter(Invoice.id == invoice_id).first()
        if not invoice:
            raise HTTPException(status_code=404, detail="Invoice not found")

        # Get dimension names
        cost_center_name = None
        project_name = None
        department_name = None

        if invoice.cost_center_id:
            cc = db.query(AccountingDimensionValue).filter(AccountingDimensionValue.id == invoice.cost_center_id).first()
            cost_center_name = cc.name if cc else None

        if invoice.project_id:
            proj = db.query(AccountingDimensionValue).filter(AccountingDimensionValue.id == invoice.project_id).first()
            project_name = proj.name if proj else None

        if invoice.department_id:
            dept = db.query(AccountingDimensionValue).filter(AccountingDimensionValue.id == invoice.department_id).first()
            department_name = dept.name if dept else None

        return {
            'invoice_id': invoice.id,
            'invoice_number': invoice.invoice_number or '',
            'total_amount': float(invoice.total_amount or 0),
            'cost_center': cost_center_name,
            'project': project_name,
            'department': department_name,
            'revenue_account': invoice.revenue_account.code if invoice.revenue_account else None,
            'ar_account': invoice.ar_account.code if invoice.ar_account else None,
            'posting_status': invoice.posting_status or 'draft',
            'last_posted_date': invoice.last_posted_date.isoformat() if invoice.last_posted_date else None
        }
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


@router.get("/invoices/accounting-bridge", response_model=List[Dict], tags=["accounting"])
def get_sales_accounting_bridge(
    start_date: Optional[date] = Query(None),
    end_date: Optional[date] = Query(None),
    posting_status: Optional[str] = Query(None),
    db: Session = Depends(get_db),
    company_id: Optional[str] = Depends(get_current_company_id),
):
    """
    Get the bridge table showing invoice-to-GL entry mappings.
    Useful for auditing and reconciliation.
    """
    try:
        query = db.query(Invoice)

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

        if start_date:
            query = query.filter(Invoice.date >= start_date)
        if end_date:
            query = query.filter(Invoice.date <= end_date)
        if posting_status:
            query = query.filter(Invoice.posting_status == posting_status)

        invoices = query.all()

        bridge_data = []
        for invoice in invoices:
            # Get related journal entries
            journal_entries = db.query(JournalEntry).filter(
                JournalEntry.reference.like(f"%SALES-{invoice.id}%")
            ).all()

            bridge_data.append({
                'invoice_id': invoice.id,
                'invoice_number': invoice.invoice_number or '',
                'invoice_date': invoice.date.isoformat() if invoice.date else None,
                'total_amount': float(invoice.total_amount or 0),
                'posting_status': invoice.posting_status or 'draft',
                'journal_entry_count': len(journal_entries),
                'journal_entry_ids': [je.id for je in journal_entries],
                'cost_center_id': invoice.cost_center_id,
                'project_id': invoice.project_id,
                'department_id': invoice.department_id
            })

        return bridge_data
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


@router.get("/invoices/journal-entries", response_model=List[JournalEntryResponse], tags=["accounting"])
def get_sales_journal_entries(
    start_date: Optional[date] = Query(None),
    end_date: Optional[date] = Query(None),
    source: str = Query("SALES"),
    db: Session = Depends(get_db)
):
    """
    Get all journal entries for sales transactions.
    Shows debit/credit entries with dimension assignments.
    Optimized with eager loading and name fields instead of UUIDs.
    """
    try:
        # Add eager loading for all relationships
        query = db.query(JournalEntry).options(
            joinedload(JournalEntry.accounting_code),
            joinedload(JournalEntry.accounting_entry),
            joinedload(JournalEntry.branch),
            joinedload(JournalEntry.ledger),
            joinedload(JournalEntry.dimension_assignments)
        ).filter(JournalEntry.source == source)

        if start_date:
            query = query.filter(JournalEntry.entry_date >= start_date)
        if end_date:
            query = query.filter(JournalEntry.entry_date <= end_date)

        entries = query.all()

        result = []
        for entry in entries:
            dimensions = []
            if entry.dimension_assignments:
                for dim_assign in entry.dimension_assignments:
                    dimensions.append({
                        'dimension_value_id': dim_assign.dimension_value_id,
                        'dimension_type': dim_assign.dimension_value.dimension.code if dim_assign.dimension_value and dim_assign.dimension_value.dimension else None,
                        'dimension_value': dim_assign.dimension_value.value if dim_assign.dimension_value else None
                    })

            result.append({
                'id': entry.id,
                'accounting_code': entry.accounting_code.code if entry.accounting_code else None,
                'accounting_code_name': entry.accounting_code.name if entry.accounting_code else None,
                'accounting_entry_id': entry.accounting_entry_id,
                'accounting_entry_particulars': entry.accounting_entry.particulars if entry.accounting_entry else None,
                'branch_id': entry.branch_id,
                'branch_name': entry.branch.name if entry.branch else None,
                'ledger_id': entry.ledger_id,
                'ledger_description': entry.ledger.description if entry.ledger else None,
                'debit_amount': float(entry.debit_amount or 0),
                'credit_amount': float(entry.credit_amount or 0),
                'description': entry.description or '',
                'source': entry.source or '',
                'entry_date': entry.entry_date.isoformat() if entry.entry_date else None,
                'dimensions': dimensions
            })

        return result
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


@router.get("/dimensional-analysis", response_model=DimensionalAnalysisResponse, tags=["accounting"])
def get_sales_dimensional_analysis(
    start_date: Optional[date] = Query(None),
    end_date: Optional[date] = Query(None),
    db: Session = Depends(get_db),
    company_id: Optional[str] = Depends(get_current_company_id),
):
    """
    Analyze sales revenue by accounting dimensions.
    Groups revenue by cost center, project, and department.
    """
    try:
        query = db.query(Invoice)

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

        if start_date:
            query = query.filter(Invoice.date >= start_date)
        if end_date:
            query = query.filter(Invoice.date <= end_date)

        invoices = query.all()

        total_revenue = Decimal('0')
        by_cost_center = {}
        by_project = {}
        by_department = {}

        for invoice in invoices:
            amount = Decimal(str(invoice.total_amount or 0))
            total_revenue += amount

            # Group by cost center
            if invoice.cost_center_id:
                cc = db.query(AccountingDimensionValue).filter(AccountingDimensionValue.id == invoice.cost_center_id).first()
                cc_name = cc.name if cc else 'Unknown'
                by_cost_center[cc_name] = float(by_cost_center.get(cc_name, Decimal('0')) + amount)

            # Group by project
            if invoice.project_id:
                proj = db.query(AccountingDimensionValue).filter(AccountingDimensionValue.id == invoice.project_id).first()
                proj_name = proj.name if proj else 'Unknown'
                by_project[proj_name] = float(by_project.get(proj_name, Decimal('0')) + amount)

            # Group by department
            if invoice.department_id:
                dept = db.query(AccountingDimensionValue).filter(AccountingDimensionValue.id == invoice.department_id).first()
                dept_name = dept.name if dept else 'Unknown'
                by_department[dept_name] = float(by_department.get(dept_name, Decimal('0')) + amount)

        return {
            'total_revenue': float(total_revenue),
            'by_cost_center': by_cost_center,
            'by_project': by_project,
            'by_department': by_department
        }
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


@router.get("/reconcile", response_model=ReconciliationResponse, tags=["accounting"])
def reconcile_sales(
    period: str = Query(..., description="Period in YYYY-MM format"),
    db: Session = Depends(get_db)
):
    """
    Reconcile sales invoices against GL entries by dimension.
    Returns variance analysis to identify discrepancies.
    """
    try:
        service = SalesService(db)
        result = service.reconcile_sales_by_dimension(period)
        return result
    except ValueError as e:
        raise HTTPException(status_code=400, detail=str(e))
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))


@router.get("/branch-sales/realtime")
async def get_branch_sales_realtime(
    exclude_empty: bool = False,
    db: Session = Depends(get_db),
    current_user: User = Depends(get_current_user),
):
    """
    Get real-time branch sales data for monitoring dashboard.
    Returns aggregated sales metrics by branch for today and current month.
    """
    try:
        scope = resolve_monitoring_scope(db, current_user)
        now = datetime.now()
        today_start = now.replace(hour=0, minute=0, second=0, microsecond=0)
        month_start = now.replace(day=1, hour=0, minute=0, second=0, microsecond=0)
        ts = sale_timestamp_sql("s")

        company_clause = ""
        branch_clause = ""
        params: Dict[str, Any] = {
            "today_start": today_start,
            "month_start": month_start,
        }
        if scope.get("company_id"):
            company_clause = "AND b.company_id = :company_id"
            params["company_id"] = scope["company_id"]
        if scope.get("branch_id"):
            branch_clause = "AND b.id = :branch_id"
            params["branch_id"] = scope["branch_id"]

        query = text(f"""
            SELECT
                b.id,
                b.name,
                COUNT(CASE WHEN {ts} >= :today_start
                    AND (s.status IS NULL OR LOWER(s.status) NOT IN ('cancelled', 'void', 'refunded'))
                    THEN 1 END) as sales_today,
                COUNT(CASE WHEN {ts} >= :month_start
                    AND (s.status IS NULL OR LOWER(s.status) NOT IN ('cancelled', 'void', 'refunded'))
                    THEN 1 END) as sales_month,
                COALESCE(SUM(CASE WHEN {ts} >= :today_start
                    AND (s.status IS NULL OR LOWER(s.status) NOT IN ('cancelled', 'void', 'refunded'))
                    THEN s.total_amount ELSE 0 END), 0) as amount_today,
                COALESCE(SUM(CASE WHEN {ts} >= :month_start
                    AND (s.status IS NULL OR LOWER(s.status) NOT IN ('cancelled', 'void', 'refunded'))
                    THEN s.total_amount ELSE 0 END), 0) as amount_month
            FROM branches b
            LEFT JOIN sales s ON b.id = s.branch_id
            WHERE (b.active IS TRUE OR b.active = 1)
            {company_clause}
            {branch_clause}
            GROUP BY b.id, b.name
            ORDER BY amount_today DESC
        """)

        results = db.execute(query, params).fetchall()

        branches = []
        total_network_sales = 0.0
        active_branches = 0

        for row in results:
            branch_id, branch_name, sales_today, _sales_month, amount_today, amount_month = row
            if exclude_empty and sales_today == 0:
                continue

            sales_today = int(sales_today or 0)
            amount_today = float(amount_today or 0)
            amount_month = float(amount_month or 0)
            average_transaction = (amount_today / sales_today) if sales_today > 0 else 0.0

            branches.append({
                "branch_id": branch_id,
                "branch_name": branch_name,
                "total_sales_today": amount_today,
                "total_sales_this_month": amount_month,
                "transaction_count_today": sales_today,
                "average_transaction": average_transaction,
                "active_now": sales_today,
                "last_updated": now.isoformat(),
            })
            total_network_sales += amount_today
            if sales_today > 0:
                active_branches += 1

        return {
            "timestamp": now.isoformat(),
            "branches": branches,
            "total_network_sales": total_network_sales,
            "active_branches": active_branches,
            "monitoring_mode": scope.get("monitoring_mode", "network"),
            "is_head_office": bool(scope.get("is_head_office")),
            "total_branches": len(branches),
        }

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

