ns = params_kw.copy()
ns['page'] = ns.get('page') or 1
ns['sort'] = ns.get('sort') or 'sale_date'

date_start = ns.get('date_start', '')
date_end = ns.get('date_end', '')
supplier_id = ns.get('supplier_id', '')

userorgid = await get_userorgid()
sc_dbname = get_module_dbname('supplychain')
acc_dbname = get_module_dbname('accounting')

# 我方: supplychain_accounting 按产品汇总
sc_where = "WHERE 1=1"
sc_params = {}
if date_start:
	sc_where += " AND sa.sale_date >= ${date_start}$"
	sc_params['date_start'] = date_start
if date_end:
	sc_where += " AND sa.sale_date <= ${date_end}$"
	sc_params['date_end'] = date_end
if supplier_id:
	sc_where += " AND sa.supplier_id = ${supplier_id}$"
	sc_params['supplier_id'] = supplier_id

sc_sql = f"""
SELECT 
	sa.productid,
	p.name as productid_text,
	COUNT(*) as tx_count,
	SUM(sa.quantity) as total_qty,
	SUM(sa.supply_amount) as our_supply_amount,
	SUM(sa.dist_amount) as dist_total_amount,
	SUM(sa.profit_amount) as profit_total_amount
FROM supplychain_accounting sa
LEFT JOIN product p ON sa.productid = p.id COLLATE utf8mb4_unicode_ci
{sc_where}
GROUP BY sa.productid, p.name
ORDER BY p.name
"""

# 我方账务: bill_detail中供应商相关的记账
acc_sql = """
SELECT 
	bd.subjectname,
	bd.subjectname as subjectname_text,
	COUNT(*) as acc_tx_count,
	SUM(CASE WHEN bd.accounting_dir = 'D' THEN bd.amount ELSE 0 END) as acc_debit,
	SUM(CASE WHEN bd.accounting_dir = 'C' THEN bd.amount ELSE 0 END) as acc_credit
FROM bill_detail bd
INNER JOIN bill b ON bd.billid = b.id COLLATE utf8mb4_unicode_ci
WHERE bd.participanttype = 'supplier'
"""
acc_params = {}
if date_start:
	acc_sql += " AND b.bill_date >= ${date_start}$"
	acc_params['date_start'] = date_start
if date_end:
	acc_sql += " AND b.bill_date <= ${date_end}$"
	acc_params['date_end'] = date_end
if supplier_id:
	acc_sql += " AND bd.participantid = ${supplier_id}$"
	acc_params['supplier_id'] = supplier_id
acc_sql += " GROUP BY bd.subjectname ORDER BY bd.subjectname"

# 已结算: sales_ledger中settlement_status='1'且与供应商相关的
settled_sql = f"""
SELECT 
	COALESCE(SUM(supply_amount), 0) as settled_supply_amount,
	COUNT(*) as settled_count
FROM sales_ledger
WHERE settlement_status = '1'
"""
if date_start:
	settled_sql += " AND sale_date >= ${date_start}$"
if date_end:
	settled_sql += " AND sale_date <= ${date_end}$"
if supplier_id:
	settled_sql += " AND supplier_id = ${supplier_id}$"

db = DBPools()
rows = []

async with db.sqlorContext(sc_dbname) as sc_sor:
	sc_recs = await sc_sor.sqlExe(sc_sql, sc_params)
	settled_recs = await sc_sor.sqlExe(settled_sql, sc_params)

async with db.sqlorContext(acc_dbname) as acc_sor:
	acc_recs = await acc_sor.sqlExe(acc_sql, acc_params)

# 我方交易明细行(按产品汇总)
for r in (sc_recs or []):
	row = {
		'id': r.productid or '',
		'productid': r.productid or '',
		'productid_text': r.productid_text or '',
		'category': '我方交易',
		'tx_count': int(r.tx_count or 0),
		'total_qty': float(r.total_qty or 0),
		'our_amount': float(r.our_supply_amount or 0),
		'their_amount': 0,
		'diff_amount': float(r.our_supply_amount or 0),
		'match_status': '1'
	}
	rows.append(row)

# 我方账务汇总行
for r in (acc_recs or []):
	row = {
		'id': r.subjectname or '',
		'productid': '',
		'productid_text': r.subjectname or '',
		'category': '我方账务',
		'tx_count': int(r.acc_tx_count or 0),
		'total_qty': 0,
		'our_amount': float(r.acc_debit or 0) - float(r.acc_credit or 0),
		'their_amount': 0,
		'diff_amount': float(r.acc_debit or 0) - float(r.acc_credit or 0),
		'match_status': '1'
	}
	rows.append(row)

total = len(rows)
our_supply_total = sum(r['our_amount'] for r in rows if r['category'] == '我方交易')
settled_supply = float(settled_recs[0].settled_supply_amount) if settled_recs else 0
unsettled_supply = our_supply_total - settled_supply

debug(f'get_provider_reconcile: rows={total}, our_supply={our_supply_total}, settled={settled_supply}')

return json.dumps({
	'success': True,
	'total': total,
	'rows': rows,
	'summary': {
		'our_total': our_supply_total,
		'settled': settled_supply,
		'unsettled': unsettled_supply,
		'tx_count': total
	}
}, ensure_ascii=False, default=str)