from __future__ import annotations

from datetime import date, datetime, timezone
from decimal import Decimal
from typing import Any, Mapping
from uuid import UUID

from psycopg import sql
from psycopg.types.json import Jsonb

from .db import connect
from .models import (
    PartyIn, ProductIn, SourceFileIn, PurchaseOrderIn, StockMovementIn,
    ShipmentSignoffIn, ValidationIssueIn, ReconciliationRunIn,
)


def _now_code(prefix: str) -> str:
    return f"{prefix}-{datetime.now(timezone.utc).strftime('%Y%m%d%H%M%S%f')}"


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


def _insert(cur, table: str, data: Mapping[str, Any]) -> str:
    clean = {k: v for k, v in data.items() if v is not None}
    if not clean:
        raise ValueError('empty insert payload')
    cols = [sql.Identifier(k) for k in clean]
    vals = []
    for v in clean.values():
        if isinstance(v, dict) or isinstance(v, list):
            vals.append(Jsonb(v))
        else:
            vals.append(v)
    q = sql.SQL('INSERT INTO {table} ({cols}) VALUES ({placeholders}) RETURNING id').format(
        table=sql.Identifier(table),
        cols=sql.SQL(', ').join(cols),
        placeholders=sql.SQL(', ').join(sql.Placeholder() for _ in vals),
    )
    cur.execute(q, vals)
    return str(cur.fetchone()['id'])


def _audit(cur, *, actor_profile: str, tool_name: str, operation: str, status: str, target_table: str | None = None,
           target_id: str | None = None, payload: dict[str, Any] | None = None, validation: dict[str, Any] | None = None,
           error_message: str | None = None, idempotency_key: str | None = None) -> str:
    req_data = {
        'request_code': _now_code('WR'),
        'actor_profile': actor_profile,
        'tool_name': tool_name,
        'operation': operation,
        'idempotency_key': idempotency_key,
        'request_payload': Jsonb(_to_jsonable(payload or {})),
        'validation_result': Jsonb(_to_jsonable(validation or {})),
        'status': status,
        'target_table': target_table,
        'target_id': target_id,
        'error_message': error_message,
        'applied_at': datetime.now(timezone.utc) if status == 'applied' else None,
    }
    clean = {k: v for k, v in req_data.items() if v is not None}
    q = sql.SQL('INSERT INTO api_write_requests ({cols}) VALUES ({vals}) RETURNING id').format(
        cols=sql.SQL(', ').join(sql.Identifier(k) for k in clean),
        vals=sql.SQL(', ').join(sql.Placeholder() for _ in clean),
    )
    cur.execute(q, list(clean.values()))
    req_id = str(cur.fetchone()['id'])
    cur.execute(
        """
        INSERT INTO agent_write_audit(agent_profile, tool_name, operation, target_table, target_id, request_payload, result_status, result_message)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
        """,
        (actor_profile, tool_name, operation if operation in {'create','update','import','compute','delete_request'} else 'create',
         target_table, target_id, Jsonb(_to_jsonable(payload or {})), 'success' if status == 'applied' else 'needs_review', error_message or status),
    )
    return req_id


def _check_idempotency(cur, key: str | None) -> dict[str, Any] | None:
    if not key:
        return None
    cur.execute('SELECT id, status, target_table, target_id, error_message FROM api_write_requests WHERE idempotency_key=%s', (key,))
    row = cur.fetchone()
    return dict(row) if row else None


def create_party(payload: PartyIn, *, actor_profile: str, idempotency_key: str | None = None) -> dict[str, Any]:
    with connect() as conn:
        with conn.cursor() as cur:
            existing = _check_idempotency(cur, idempotency_key)
            if existing:
                return {'idempotent': True, **existing}
            data = payload.model_dump(exclude_none=True, mode='python')
            data.setdefault('party_code', _now_code('PT'))
            target_id = _insert(cur, 'parties', data)
            _audit(cur, actor_profile=actor_profile, tool_name='create_party', operation='create', status='applied', target_table='parties', target_id=target_id, payload=data, idempotency_key=idempotency_key)
            conn.commit()
            return {'id': target_id}


def create_product(payload: ProductIn, *, actor_profile: str, idempotency_key: str | None = None) -> dict[str, Any]:
    with connect() as conn:
        with conn.cursor() as cur:
            existing = _check_idempotency(cur, idempotency_key)
            if existing:
                return {'idempotent': True, **existing}
            data = payload.model_dump(exclude_none=True, mode='python')
            data.setdefault('sku_code', _now_code('SKU'))
            target_id = _insert(cur, 'products', data)
            if data.get('raw_name_default') and data['raw_name_default'] != data['standard_name']:
                _insert(cur, 'product_aliases', {
                    'product_id': target_id,
                    'alias_name': data['raw_name_default'],
                    'alias_type': 'raw_name',
                    'confidence': 0.8,
                    'verification_status': 'pending',
                })
            _audit(cur, actor_profile=actor_profile, tool_name='create_product', operation='create', status='applied', target_table='products', target_id=target_id, payload=data, idempotency_key=idempotency_key)
            conn.commit()
            return {'id': target_id}


def register_source_file(payload: SourceFileIn, *, actor_profile: str, idempotency_key: str | None = None) -> dict[str, Any]:
    with connect() as conn:
        with conn.cursor() as cur:
            existing = _check_idempotency(cur, idempotency_key)
            if existing:
                return {'idempotent': True, **existing}
            data = payload.model_dump(exclude_none=True, mode='python')
            data.setdefault('extraction_status', 'pending')
            target_id = _insert(cur, 'source_files', data)
            _insert(cur, 'file_receipts', {
                'source_file_id': target_id,
                'receipt_code': _now_code('FR'),
                'source_platform': data.get('source_platform'),
                'sender_name': data.get('uploader'),
                'received_at': data.get('received_at'),
                'source_cache_path': data.get('source_cache_path'),
                'original_source_path': data.get('original_source_path'),
                'receive_status': data.get('received_status'),
                'file_exists': data.get('file_exists'),
                'notes': data.get('notes'),
            })
            _audit(cur, actor_profile=actor_profile, tool_name='register_source_file', operation='create', status='applied', target_table='source_files', target_id=target_id, payload=data, idempotency_key=idempotency_key)
            conn.commit()
            return {'id': target_id}


def create_purchase_order(payload: PurchaseOrderIn, *, actor_profile: str, idempotency_key: str | None = None) -> dict[str, Any]:
    if not payload.lines:
        raise ValueError('purchase order requires at least one line')
    with connect() as conn:
        with conn.cursor() as cur:
            existing = _check_idempotency(cur, idempotency_key)
            if existing:
                return {'idempotent': True, **existing}
            order_data = payload.model_dump(exclude={'lines'}, exclude_none=True, mode='python')
            order_data['order_code'] = _now_code('PO')
            if order_data.get('expected_arrival_text') is None:
                if order_data.get('expected_arrival_start') and order_data.get('expected_arrival_end'):
                    order_data['expected_arrival_text'] = f"{order_data['expected_arrival_start']}~{order_data['expected_arrival_end']}"
            order_id = _insert(cur, 'purchase_orders', order_data)
            line_ids: list[str] = []
            allocation_ids: list[str] = []
            for idx, line in enumerate(payload.lines, start=1):
                line_data = line.model_dump(exclude={'channel_allocations'}, exclude_none=True, mode='python')
                line_data.update({'order_id': order_id, 'line_no': idx, 'verification_status': 'pending'})
                if line_data.get('delivery_quantity') is None:
                    line_data['delivery_quantity'] = line_data['quantity']
                line_id = _insert(cur, 'order_lines', line_data)
                line_ids.append(line_id)
                for alloc in line.channel_allocations:
                    alloc_data = alloc.model_dump(exclude_none=True, mode='python')
                    alloc_data['order_line_id'] = line_id
                    if alloc_data.get('planned_quantity') is None:
                        alloc_data['planned_quantity'] = alloc_data.get('original_quantity', 0)
                    if alloc_data.get('confirmed_quantity') is None:
                        alloc_data['confirmed_quantity'] = float(alloc_data.get('original_quantity', 0)) + float(alloc_data.get('adjustment_quantity', 0))
                    allocation_ids.append(_insert(cur, 'channel_allocations', alloc_data))
            _audit(cur, actor_profile=actor_profile, tool_name='create_purchase_order', operation='create', status='applied', target_table='purchase_orders', target_id=order_id, payload=payload.model_dump(mode='json'), validation={'line_count': len(line_ids), 'allocation_count': len(allocation_ids)}, idempotency_key=idempotency_key)
            conn.commit()
            return {'id': order_id, 'line_ids': line_ids, 'allocation_ids': allocation_ids}


def add_stock_movement(payload: StockMovementIn, *, actor_profile: str, idempotency_key: str | None = None) -> dict[str, Any]:
    data = payload.model_dump(exclude_none=True, mode='python')
    data['movement_code'] = _now_code('MV')
    data['movement_no'] = data['movement_code']
    with connect() as conn:
        with conn.cursor() as cur:
            existing = _check_idempotency(cur, idempotency_key)
            if existing:
                return {'idempotent': True, **existing}
            if data['direction'] == 'out':
                cur.execute('SELECT current_quantity FROM v_current_stock WHERE stock_item_id=%s', (data['stock_item_id'],))
                row = cur.fetchone()
                current = row['current_quantity'] if row else 0
                if current is not None and current < data['quantity']:
                    issue_id = _insert(cur, 'validation_issues', {
                        'issue_type': 'negative_stock', 'severity': 'error', 'related_table': 'stock_items',
                        'related_id': data['stock_item_id'], 'field_name': 'quantity',
                        'message': f"库存不足：当前 {current}，申请出库 {data['quantity']}", 'status': 'open'
                    })
                    _audit(cur, actor_profile=actor_profile, tool_name='add_stock_movement', operation='create', status='rejected', target_table='validation_issues', target_id=issue_id, payload=data, validation={'current_quantity': str(current)}, error_message='库存不足', idempotency_key=idempotency_key)
                    conn.commit()
                    return {'rejected': True, 'validation_issue_id': issue_id}
            target_id = _insert(cur, 'stock_movements', data)
            _audit(cur, actor_profile=actor_profile, tool_name='add_stock_movement', operation='create', status='applied', target_table='stock_movements', target_id=target_id, payload=data, idempotency_key=idempotency_key)
            conn.commit()
            return {'id': target_id}


def create_shipment_signoff(payload: ShipmentSignoffIn, *, actor_profile: str, idempotency_key: str | None = None) -> dict[str, Any]:
    with connect() as conn:
        with conn.cursor() as cur:
            existing = _check_idempotency(cur, idempotency_key)
            if existing:
                return {'idempotent': True, **existing}
            data = payload.model_dump(exclude={'lines'}, exclude_none=True, mode='python')
            data['signoff_code'] = _now_code('SO')
            signoff_id = _insert(cur, 'shipment_signoffs', data)
            line_ids: list[str] = []
            for idx, line in enumerate(payload.lines, start=1):
                line_data = line.model_dump(exclude_none=True, mode='python')
                line_data.update({'signoff_id': signoff_id, 'line_no': idx})
                if line_data.get('shipped_quantity') is None:
                    line_data['shipped_quantity'] = line_data['quantity']
                if line_data.get('signed_quantity') is None:
                    line_data['signed_quantity'] = line_data['quantity'] if line_data.get('sign_status') == 'signed' else 0
                line_ids.append(_insert(cur, 'shipment_signoff_lines', line_data))
            _audit(cur, actor_profile=actor_profile, tool_name='create_shipment_signoff', operation='create', status='applied', target_table='shipment_signoffs', target_id=signoff_id, payload=payload.model_dump(mode='json'), validation={'line_count': len(line_ids)}, idempotency_key=idempotency_key)
            conn.commit()
            return {'id': signoff_id, 'line_ids': line_ids}


def create_validation_issue(payload: ValidationIssueIn, *, actor_profile: str, idempotency_key: str | None = None) -> dict[str, Any]:
    with connect() as conn:
        with conn.cursor() as cur:
            existing = _check_idempotency(cur, idempotency_key)
            if existing:
                return {'idempotent': True, **existing}
            data = payload.model_dump(exclude_none=True, mode='python')
            data.setdefault('status', 'open')
            target_id = _insert(cur, 'validation_issues', data)
            _audit(cur, actor_profile=actor_profile, tool_name='create_validation_issue', operation='create', status='applied', target_table='validation_issues', target_id=target_id, payload=data, idempotency_key=idempotency_key)
            conn.commit()
            return {'id': target_id}


def run_reconciliation(payload: ReconciliationRunIn, *, actor_profile: str, idempotency_key: str | None = None) -> dict[str, Any]:
    """Create an auditable reconciliation snapshot from the live reconciliation view."""
    data = payload.model_dump(exclude_none=True, mode='python')
    project_id = data.get('project_id')
    with connect() as conn:
        with conn.cursor() as cur:
            existing = _check_idempotency(cur, idempotency_key)
            if existing:
                return {'idempotent': True, **existing}
            run_data = {
                'run_code': _now_code('RC'),
                'project_id': project_id,
                'reconciliation_type': 'supplier_vs_upstream_demand',
                'policy_version': 'v1-live-shortage',
                'input_scope': {'project_id': str(project_id) if project_id else None},
                'run_status': 'completed',
                'notes': data.get('notes'),
            }
            run_id = _insert(cur, 'reconciliation_runs', run_data)
            params = {'run_id': run_id, 'project_id': project_id}
            project_filter = 'where (%(project_id)s::uuid is null or project_id = %(project_id)s::uuid)'
            cur.execute(f'''
                INSERT INTO reconciliation_lines(
                    run_id, product_id, product_name_standard_draft,
                    supplier_purchase_quantity_total, upstream_demand_quantity_total,
                    formal_order_quantity_total, supplement_demand_quantity_total,
                    difference_supplier_minus_demand, self_stock_candidate_qty, shortage_qty,
                    difference_status, raw_payload
                )
                SELECT
                    %(run_id)s::uuid, product_id, product_name,
                    supplier_purchase_quantity_total, upstream_demand_quantity_total,
                    formal_order_quantity_total, supplement_demand_quantity_total,
                    difference_supplier_minus_demand, self_stock_candidate_qty, shortage_qty,
                    CASE
                        WHEN shortage_qty > 0 THEN 'shortage'
                        WHEN difference_supplier_minus_demand > 0 THEN 'surplus'
                        ELSE 'balanced'
                    END,
                    jsonb_build_object(
                        'project_id', project_id,
                        'product_name', product_name,
                        'generated_from', 'v_supplier_vs_upstream_reconciliation_live'
                    )
                FROM v_supplier_vs_upstream_reconciliation_live
                {project_filter}
            ''', params)
            line_count = cur.rowcount
            _audit(
                cur, actor_profile=actor_profile, tool_name='run_reconciliation', operation='compute',
                status='applied', target_table='reconciliation_runs', target_id=run_id,
                payload=data, validation={'line_count': line_count}, idempotency_key=idempotency_key,
            )
            conn.commit()
            return {'id': run_id, 'line_count': line_count}