from datetime import date, datetime
from decimal import Decimal
from uuid import UUID

from mcp.server.fastmcp import FastMCP

from .db import fetch_all, fetch_one
from .models import PartyIn, ProductIn, SourceFileIn, PurchaseOrderIn, StockMovementIn, ShipmentSignoffIn, ValidationIssueIn, ReconciliationRunIn
from .write_service import (
    create_party, create_product, register_source_file, create_purchase_order,
    add_stock_movement, create_shipment_signoff, create_validation_issue, run_reconciliation,
)

mcp = FastMCP('goods-ledger-write-layer')


def _jsonable(value):
    if isinstance(value, (datetime, date)):
        return value.isoformat()
    if isinstance(value, Decimal):
        return str(value)
    if isinstance(value, UUID):
        return str(value)
    if isinstance(value, dict):
        return {k: _jsonable(v) for k, v in value.items()}
    if isinstance(value, list):
        return [_jsonable(v) for v in value]
    return value


def _limit(value: int, default: int = 20, maximum: int = 100) -> int:
    try:
        n = int(value)
    except Exception:
        n = default
    return max(1, min(n, maximum))


@mcp.tool()
def ledger_health() -> dict:
    """Read-only health check for the goods_ledger database connection."""
    row = fetch_one("select current_database() as database, now() as server_time")
    return {'ok': True, **_jsonable(row or {})}


@mcp.tool()
def ledger_summary() -> dict:
    """Read-only summary counts for the goods_ledger system."""
    rows = fetch_all(
        """
        select 'products' as key, count(*)::int as value from products
        union all select 'source_files', count(*)::int from source_files
        union all select 'purchase_orders', count(*)::int from purchase_orders
        union all select 'order_lines', count(*)::int from order_lines
        union all select 'channel_allocations', count(*)::int from channel_allocations
        union all select 'stock_movements', count(*)::int from stock_movements
        union all select 'shipment_signoffs', count(*)::int from shipment_signoffs
        union all select 'validation_issues_open', count(*)::int from validation_issues where status = 'open'
        """
    )
    return {r['key']: r['value'] for r in rows}


@mcp.tool()
def ledger_current_stock(limit: int = 50, keyword: str = '') -> dict:
    """Read-only current stock rows. Optional keyword matches stock item display name/code."""
    n = _limit(limit, default=50, maximum=100)
    if keyword:
        rows = fetch_all(
            """
            select * from v_current_stock
            where coalesce(display_name,'') ilike %(kw)s
               or coalesce(stock_item_code,'') ilike %(kw)s
            order by display_name nulls last
            limit %(limit)s
            """,
            {'kw': f'%{keyword}%', 'limit': n},
        )
    else:
        rows = fetch_all("select * from v_current_stock order by display_name nulls last limit %(limit)s", {'limit': n})
    return {'rows': _jsonable(rows), 'count': len(rows)}


@mcp.tool()
def ledger_recent_stock_movements(limit: int = 30, product_keyword: str = '') -> dict:
    """Read-only recent inventory movements."""
    n = _limit(limit, default=30, maximum=100)
    where = ""
    params = {'limit': n}
    if product_keyword:
        where = "where coalesce(si.display_name,'') ilike %(kw)s or coalesce(si.stock_item_code,'') ilike %(kw)s or coalesce(p.standard_name,'') ilike %(kw)s or coalesce(p.barcode_69,'') ilike %(kw)s"
        params['kw'] = f'%{product_keyword}%'
    rows = fetch_all(
        f"""
        select sm.id, sm.movement_code, sm.movement_date, sm.direction, sm.movement_type,
               sm.quantity, sm.unit, sm.purpose, sm.returnable, sm.signoff_status,
               sm.logistics_company, sm.tracking_no, sm.receiver_name,
               si.stock_item_code, si.display_name, p.standard_name, p.spec, p.barcode_69,
               handler.display_name as handler_name, counterparty.display_name as counterparty_name
        from stock_movements sm
        left join stock_items si on si.id = sm.stock_item_id
        left join products p on p.id = si.product_id
        left join parties handler on handler.id = sm.handler_party_id
        left join parties counterparty on counterparty.id = sm.counterparty_id
        {where}
        order by sm.movement_date desc, sm.created_at desc
        limit %(limit)s
        """,
        params,
    )
    return {'rows': _jsonable(rows), 'count': len(rows)}


@mcp.tool()
def ledger_open_validation_issues(limit: int = 50) -> dict:
    """Read-only open validation issues needing review."""
    n = _limit(limit, default=50, maximum=100)
    rows = fetch_all(
        """
        select id, issue_type, severity, related_table, related_id, field_name,
               message, status, created_at
        from validation_issues
        where status = 'open'
        order by created_at desc
        limit %(limit)s
        """,
        {'limit': n},
    )
    return {'rows': _jsonable(rows), 'count': len(rows)}


@mcp.tool()
def ledger_dashboard_overview() -> dict:
    """Read-only overview counts, recent source files, and recent validation issues."""
    counts = {r['metric']: r['value'] for r in fetch_all('select metric, value from v_api_summary order by metric')}
    extras = fetch_one('''
        select
            (select count(*) from validation_issues where status='open')::int as open_issue_count,
            (select count(*) from stock_movements where direction='in')::int as stock_in_count,
            (select count(*) from stock_movements where direction='out')::int as stock_out_count,
            (select count(*) from reconciliation_runs)::int as reconciliation_run_count,
            (select count(*) from api_write_requests)::int as api_write_request_count
    ''') or {}
    return _jsonable({'counts': counts, 'extras': extras})


@mcp.tool()
def ledger_search(keyword: str, scope: str = 'all', limit: int = 10) -> dict:
    """Unified search across products, orders, files, stock movements, shipments, and issues."""
    n = _limit(limit, default=10, maximum=50)
    kw = f'%{keyword}%'
    scopes = {s.strip() for s in scope.split(',') if s.strip()} or {'all'}
    def enabled(name: str) -> bool:
        return 'all' in scopes or name in scopes
    groups = {}
    if enabled('products'):
        groups['products'] = fetch_all('''
            select id, sku_code, standard_name, raw_name_default, category, spec, barcode_69, guobo_code, status
            from products
            where coalesce(standard_name,'') ilike %(kw)s or coalesce(raw_name_default,'') ilike %(kw)s
               or coalesce(sku_code,'') ilike %(kw)s or coalesce(barcode_69,'') ilike %(kw)s or coalesce(guobo_code,'') ilike %(kw)s
            order by standard_name limit %(limit)s
        ''', {'kw': kw, 'limit': n})
    if enabled('orders'):
        groups['orders'] = fetch_all('''
            select po.id, po.order_code, po.order_side, po.order_type, po.order_no, po.external_contract_no,
                   bp.display_name as buyer_name, sp.display_name as seller_name
            from purchase_orders po
            left join parties bp on bp.id = po.buyer_party_id
            left join parties sp on sp.id = po.seller_party_id
            where coalesce(po.order_code,'') ilike %(kw)s or coalesce(po.order_no,'') ilike %(kw)s
               or coalesce(po.external_contract_no,'') ilike %(kw)s or coalesce(po.tracking_no,'') ilike %(kw)s
               or coalesce(bp.display_name,'') ilike %(kw)s or coalesce(sp.display_name,'') ilike %(kw)s
            order by po.created_at desc limit %(limit)s
        ''', {'kw': kw, 'limit': n})
    if enabled('files'):
        groups['files'] = fetch_all('''
            select id, original_filename, stored_path, material_type, source_platform, uploader, extraction_status, received_at
            from source_files
            where coalesce(original_filename,'') ilike %(kw)s or coalesce(stored_path,'') ilike %(kw)s
               or coalesce(material_type,'') ilike %(kw)s or coalesce(source_platform,'') ilike %(kw)s or coalesce(uploader,'') ilike %(kw)s
            order by created_at desc limit %(limit)s
        ''', {'kw': kw, 'limit': n})
    if enabled('stock'):
        groups['stock_movements'] = fetch_all('''
            select sm.id, sm.movement_code, sm.movement_date, sm.direction, sm.movement_type, sm.quantity, sm.unit,
                   sm.tracking_no, sm.receiver_name, si.display_name as stock_item_name
            from stock_movements sm left join stock_items si on si.id = sm.stock_item_id
            where coalesce(sm.movement_code,'') ilike %(kw)s or coalesce(sm.tracking_no,'') ilike %(kw)s
               or coalesce(sm.receiver_name,'') ilike %(kw)s or coalesce(si.display_name,'') ilike %(kw)s
               or coalesce(si.stock_item_code,'') ilike %(kw)s
            order by sm.movement_date desc, sm.created_at desc limit %(limit)s
        ''', {'kw': kw, 'limit': n})
    if enabled('shipments'):
        groups['shipments'] = fetch_all('''
            select id, signoff_code, ship_date, status, logistics_company, tracking_no, receiver_name, signer_name, signed_at
            from shipment_signoffs
            where coalesce(signoff_code,'') ilike %(kw)s or coalesce(logistics_company,'') ilike %(kw)s
               or coalesce(tracking_no,'') ilike %(kw)s or coalesce(receiver_name,'') ilike %(kw)s or coalesce(signer_name,'') ilike %(kw)s
            order by created_at desc limit %(limit)s
        ''', {'kw': kw, 'limit': n})
    if enabled('issues'):
        groups['issues'] = fetch_all('''
            select id, issue_type, severity, related_table, field_name, message, status, created_at
            from validation_issues
            where coalesce(issue_type,'') ilike %(kw)s or coalesce(related_table,'') ilike %(kw)s
               or coalesce(field_name,'') ilike %(kw)s or coalesce(message,'') ilike %(kw)s
            order by created_at desc limit %(limit)s
        ''', {'kw': kw, 'limit': n})
    return {'query': keyword, 'scope': scope, 'groups': _jsonable(groups)}


@mcp.tool()
def ledger_reconciliation_dashboard(limit: int = 30) -> dict:
    """Read-only live supplier-vs-upstream reconciliation dashboard."""
    n = _limit(limit, default=30, maximum=100)
    summary = fetch_one('''
        select count(*)::int as product_count,
               count(*) filter (where shortage_qty > 0)::int as shortage_count,
               coalesce(sum(shortage_qty),0) as shortage_quantity_total,
               coalesce(sum(self_stock_candidate_qty),0) as self_stock_candidate_total
        from v_supplier_vs_upstream_reconciliation_live
    ''') or {}
    rows = fetch_all('''
        select * from v_supplier_vs_upstream_reconciliation_live
        order by shortage_qty desc nulls last, product_name limit %(limit)s
    ''', {'limit': n})
    return {'summary': _jsonable(summary), 'rows': _jsonable(rows), 'count': len(rows)}


@mcp.tool()
def ledger_run_reconciliation(project_id: str = '', notes: str = '', actor_profile: str = 'mcp', idempotency_key: str = '') -> dict:
    """Create an auditable reconciliation snapshot from the live reconciliation view."""
    payload = {'notes': notes or None}
    if project_id:
        payload['project_id'] = project_id
    return run_reconciliation(ReconciliationRunIn(**payload), actor_profile=actor_profile, idempotency_key=idempotency_key or None)


@mcp.tool()
def ledger_create_party(payload: dict, actor_profile: str = 'mcp', idempotency_key: str = '') -> dict:
    """Create a party/customer/supplier/channel/warehouse/person. Business-level write, audited."""
    return create_party(PartyIn(**payload), actor_profile=actor_profile, idempotency_key=idempotency_key or None)


@mcp.tool()
def ledger_create_product(payload: dict, actor_profile: str = 'mcp', idempotency_key: str = '') -> dict:
    """Create a product/SKU with optional barcode, 国博 code, spec, price, and alias fields. Audited."""
    return create_product(ProductIn(**payload), actor_profile=actor_profile, idempotency_key=idempotency_key or None)


@mcp.tool()
def ledger_register_source_file(payload: dict, actor_profile: str = 'mcp', idempotency_key: str = '') -> dict:
    """Register a source/evidence file and its receipt metadata. Audited."""
    return register_source_file(SourceFileIn(**payload), actor_profile=actor_profile, idempotency_key=idempotency_key or None)


@mcp.tool()
def ledger_create_purchase_order(payload: dict, actor_profile: str = 'mcp', idempotency_key: str = '') -> dict:
    """Create upstream/downstream/supplemental purchase order with lines and channel allocations. Audited."""
    return create_purchase_order(PurchaseOrderIn(**payload), actor_profile=actor_profile, idempotency_key=idempotency_key or None)


@mcp.tool()
def ledger_add_stock_movement(payload: dict, actor_profile: str = 'mcp', idempotency_key: str = '') -> dict:
    """Append an inventory movement. Outbound movements validate available stock and write issues on rejection."""
    return add_stock_movement(StockMovementIn(**payload), actor_profile=actor_profile, idempotency_key=idempotency_key or None)


@mcp.tool()
def ledger_create_shipment_signoff(payload: dict, actor_profile: str = 'mcp', idempotency_key: str = '') -> dict:
    """Create shipment/signoff header and lines with logistics/signing fields. Audited."""
    return create_shipment_signoff(ShipmentSignoffIn(**payload), actor_profile=actor_profile, idempotency_key=idempotency_key or None)


@mcp.tool()
def ledger_create_validation_issue(payload: dict, actor_profile: str = 'mcp', idempotency_key: str = '') -> dict:
    """Record a validation issue for later human review. Audited."""
    return create_validation_issue(ValidationIssueIn(**payload), actor_profile=actor_profile, idempotency_key=idempotency_key or None)


if __name__ == '__main__':
    mcp.run()