<?php
declare(strict_types=1);
require __DIR__.'/app/bootstrap.php';require_permission('treasury.bank_reconcile');require __DIR__.'/app/layout.php';
$pdo=db();$companyId=company_id();$userId=(int)user()['id'];
if($_SERVER['REQUEST_METHOD']==='POST'){verify_csrf();try{$action=(string)($_POST['action']??'');
 if($action==='statement_import'){$bankId=(int)($_POST['bank_account_id']??0);$bank=(int)scalar('SELECT COUNT(*) FROM bank_accounts WHERE id=? AND company_id=?',[$bankId,$companyId]);if(!$bank)throw new RuntimeException('الحساب البنكي غير صالح.');$raw=trim((string)($_POST['statement_lines']??''));$fileName=null;if(isset($_FILES['csv_file'])&&($_FILES['csv_file']['error']??UPLOAD_ERR_NO_FILE)===UPLOAD_ERR_OK){if((int)$_FILES['csv_file']['size']>2097152)throw new RuntimeException('ملف CSV أكبر من 2MB.');$raw=(string)file_get_contents((string)$_FILES['csv_file']['tmp_name']);$fileName=basename((string)$_FILES['csv_file']['name']);}if($raw==='')throw new RuntimeException('ارفع ملف CSV أو ألصق حركات الكشف.');$parsed=[];$rows=preg_split('/\R/u',$raw)?:[];foreach($rows as $row){$row=trim($row);if($row==='')continue;$cols=str_getcsv($row);if(count($cols)<5)$cols=str_getcsv($row,';');$date=trim((string)($cols[0]??''));if(!preg_match('/^\d{4}-\d{2}-\d{2}$/',$date))continue;$debit=round((float)str_replace(',','',(string)($cols[3]??0)),2);$credit=round((float)str_replace(',','',(string)($cols[4]??0)),2);$balance=isset($cols[5])&&trim((string)$cols[5])!==''?round((float)str_replace(',','',(string)$cols[5]),2):null;if($debit<0||$credit<0||($debit<=0&&$credit<=0))continue;$parsed[]=[$date,trim((string)($cols[1]??''))?:null,trim((string)($cols[2]??''))?:null,$debit,$credit,$balance];}if(!$parsed)throw new RuntimeException('لم يتم العثور على حركات صحيحة. التنسيق: التاريخ، المرجع، البيان، مدين، دائن، الرصيد.');$from=(string)($_POST['statement_from']??'');$to=(string)($_POST['statement_to']??'');if(!$from||!$to||$from>$to)throw new RuntimeException('فترة الكشف غير صحيحة.');$opening=round((float)($_POST['opening_balance']??0),2);$closing=round((float)($_POST['closing_balance']??0),2);$pdo->beginTransaction();$hash=hash('sha256',$raw);if((int)scalar('SELECT COUNT(*) FROM bank_statement_imports WHERE bank_account_id=? AND file_hash=?',[$bankId,$hash]))throw new RuntimeException('سبق استيراد هذا الكشف.');$s=$pdo->prepare("INSERT INTO bank_statement_imports(bank_account_id,statement_from,statement_to,opening_balance,closing_balance,file_name,file_hash,imported_by,imported_at,status) VALUES (?,?,?,?,?,?,?,?,NOW(),'imported')");$s->execute([$bankId,$from,$to,$opening,$closing,$fileName,$hash,$userId]);$importId=(int)$pdo->lastInsertId();$s=$pdo->prepare("INSERT INTO bank_statement_lines(bank_statement_import_id,line_no,transaction_date,bank_reference,description,debit_amount,credit_amount,running_balance,reconciliation_status) VALUES (?,?,?,?,?,?,?,?,'unmatched')");foreach($parsed as $i=>$line)$s->execute([$importId,$i+1,...$line]);$pdo->commit();audit('bank_statement.import','bank_statement_import',(string)$importId,['lines'=>count($parsed)]);flash('success','تم استيراد كشف البنك بعدد '.count($parsed).' حركة.');
 }elseif($action==='reconcile'){$importId=(int)($_POST['import_id']??0);$q=$pdo->prepare('SELECT bi.*,ba.company_id,ba.opening_balance account_opening,ba.account_id FROM bank_statement_imports bi JOIN bank_accounts ba ON ba.id=bi.bank_account_id WHERE bi.id=? AND ba.company_id=?');$q->execute([$importId,$companyId]);$import=$q->fetch();if(!$import)throw new RuntimeException('كشف البنك غير متاح.');if((int)scalar('SELECT COUNT(*) FROM bank_reconciliations WHERE bank_statement_import_id=?',[$importId]))throw new RuntimeException('تم إنشاء تسوية لهذا الكشف مسبقًا.');$movement=(float)scalar("SELECT COALESCE(SUM(jl.debit_amount-jl.credit_amount),0) FROM journal_lines jl JOIN journal_entries je ON je.id=jl.journal_entry_id WHERE jl.account_id=? AND je.company_id=? AND je.status='posted' AND je.entry_date<=?",[$import['account_id'],$companyId,$import['statement_to']]);$book=round((float)$import['account_opening']+$movement,2);$difference=round((float)$import['closing_balance']-$book,2);$pdo->beginTransaction();$status=abs($difference)<0.01?'reviewed':'draft';$pdo->prepare('INSERT INTO bank_reconciliations(bank_account_id,bank_statement_import_id,reconciliation_date,book_balance,statement_balance,difference_amount,status,prepared_by,created_at) VALUES (?,?,?,?,?,?,?,?,NOW())')->execute([$import['bank_account_id'],$importId,$import['statement_to'],$book,$import['closing_balance'],$difference,$status,$userId]);$reconId=(int)$pdo->lastInsertId();$lines=$pdo->prepare("SELECT * FROM bank_statement_lines WHERE bank_statement_import_id=? AND reconciliation_status='unmatched' ORDER BY line_no");$lines->execute([$importId]);$matched=0;foreach($lines->fetchAll() as $line){$voucherId=0;$kind='';$amount=(float)$line['credit_amount']>0?(float)$line['credit_amount']:(float)$line['debit_amount'];if((float)$line['credit_amount']>0){$sql="SELECT rv.id FROM receipt_vouchers rv WHERE rv.company_id=? AND rv.bank_account_id=? AND rv.status='posted' AND ABS(rv.amount-?)<0.01 AND rv.receipt_date BETWEEN DATE_SUB(?,INTERVAL 3 DAY) AND DATE_ADD(?,INTERVAL 3 DAY) AND NOT EXISTS(SELECT 1 FROM bank_reconciliation_lines brl WHERE brl.receipt_voucher_id=rv.id) ORDER BY ABS(DATEDIFF(rv.receipt_date,?)),rv.id LIMIT 1";$v=$pdo->prepare($sql);$v->execute([$companyId,$import['bank_account_id'],$amount,$line['transaction_date'],$line['transaction_date'],$line['transaction_date']]);$voucherId=(int)$v->fetchColumn();$kind='receipt';}else{$sql="SELECT pv.id FROM payment_vouchers pv WHERE pv.company_id=? AND pv.bank_account_id=? AND pv.status='posted' AND ABS(pv.amount-?)<0.01 AND pv.payment_date BETWEEN DATE_SUB(?,INTERVAL 3 DAY) AND DATE_ADD(?,INTERVAL 3 DAY) AND NOT EXISTS(SELECT 1 FROM bank_reconciliation_lines brl WHERE brl.payment_voucher_id=pv.id) ORDER BY ABS(DATEDIFF(pv.payment_date,?)),pv.id LIMIT 1";$v=$pdo->prepare($sql);$v->execute([$companyId,$import['bank_account_id'],$amount,$line['transaction_date'],$line['transaction_date'],$line['transaction_date']]);$voucherId=(int)$v->fetchColumn();$kind='payment';}if($voucherId){$pdo->prepare('INSERT INTO bank_reconciliation_lines(bank_reconciliation_id,bank_statement_line_id,receipt_voucher_id,payment_voucher_id,matched_amount,match_type,matched_by,matched_at) VALUES (?,?,?,?,?,\'exact\',?,NOW())')->execute([$reconId,$line['id'],$kind==='receipt'?$voucherId:null,$kind==='payment'?$voucherId:null,$amount,$userId]);$pdo->prepare("UPDATE bank_statement_lines SET reconciliation_status='matched' WHERE id=?")->execute([$line['id']]);$matched++;}}$unmatched=(int)scalar("SELECT COUNT(*) FROM bank_statement_lines WHERE bank_statement_import_id=? AND reconciliation_status='unmatched'",[$importId]);$pdo->prepare('UPDATE bank_statement_imports SET status=? WHERE id=?')->execute([$unmatched?'partially_reconciled':'reconciled',$importId]);$pdo->commit();audit('bank_reconciliation.create','bank_reconciliation',(string)$reconId,['matched'=>$matched,'difference'=>$difference]);flash(abs($difference)<0.01?'success':'error','تمت المطابقة الآلية لـ '.$matched.' حركة. فرق التسوية: '.number_format($difference,2));
 }elseif($action==='reconciliation_approve'){$id=(int)($_POST['id']??0);$s=$pdo->prepare("UPDATE bank_reconciliations br JOIN bank_accounts ba ON ba.id=br.bank_account_id SET br.status='approved',br.approved_by=?,br.approved_at=NOW() WHERE br.id=? AND ba.company_id=? AND br.status='reviewed' AND ABS(br.difference_amount)<0.01");$s->execute([$userId,$id,$companyId]);if(!$s->rowCount())throw new RuntimeException('لا يمكن اعتماد تسوية غير متوازنة.');audit('bank_reconciliation.approve','bank_reconciliation',(string)$id);flash('success','تم اعتماد التسوية البنكية.');
 }elseif($action==='cheque_create'){$bankId=(int)($_POST['bank_account_id']??0);$partnerId=(int)($_POST['partner_id']??0);$amount=round((float)($_POST['amount']??0),2);if(!$bankId||!$partnerId||$amount<=0)throw new RuntimeException('البنك والطرف والمبلغ مطلوبة.');if(!(int)scalar('SELECT COUNT(*) FROM bank_accounts WHERE id=? AND company_id=?',[$bankId,$companyId]))throw new RuntimeException('الحساب البنكي غير صالح.');$type=(string)($_POST['cheque_type']??'issued');if(!in_array($type,['issued','received'],true))throw new RuntimeException('نوع الشيك غير صالح.');$initial=$type==='issued'?'issued':'received';$s=$pdo->prepare('INSERT INTO cheques(company_id,bank_account_id,cheque_no,cheque_type,partner_id,amount,issue_date,due_date,status,created_at) VALUES (?,?,?,?,?,?,?,?,?,NOW())');$s->execute([$companyId,$bankId,trim((string)$_POST['cheque_no']),$type,$partnerId,$amount,$_POST['issue_date'],$_POST['due_date'],$initial]);audit('cheque.create','cheque',(string)$pdo->lastInsertId());flash('success','تم تسجيل الشيك.');
 }elseif($action==='cheque_status'){$id=(int)($_POST['id']??0);$status=(string)($_POST['status']??'');$allowed=['deposited','cleared','returned','cancelled','stale'];if(!in_array($status,$allowed,true))throw new RuntimeException('حالة الشيك غير صالحة.');$s=$pdo->prepare('UPDATE cheques SET status=?,cleared_on=CASE WHEN ?=\'cleared\' THEN CURDATE() ELSE cleared_on END WHERE id=? AND company_id=? AND status NOT IN (\'cleared\',\'cancelled\')');$s->execute([$status,$status,$id,$companyId]);if(!$s->rowCount())throw new RuntimeException('تعذر تحديث حالة الشيك.');audit('cheque.status','cheque',(string)$id,['status'=>$status]);flash('success','تم تحديث حالة الشيك.');
 }}catch(Throwable $e){if($pdo->inTransaction())$pdo->rollBack();flash('error',$e instanceof PDOException?'تعذر الحفظ؛ تحقق من الأرقام وعدم تكرار السجل.':$e->getMessage());}redirect('banking.php');}
$banks=$pdo->prepare('SELECT * FROM bank_accounts WHERE company_id=? AND is_active=1 ORDER BY code');$banks->execute([$companyId]);$banks=$banks->fetchAll();$partners=$pdo->prepare("SELECT id,partner_code,legal_name_ar FROM business_partners WHERE company_id=? AND status='active' AND deleted_at IS NULL ORDER BY legal_name_ar");$partners->execute([$companyId]);$partners=$partners->fetchAll();$imports=$pdo->prepare("SELECT bi.*,ba.code,ba.bank_name,(SELECT COUNT(*) FROM bank_statement_lines bl WHERE bl.bank_statement_import_id=bi.id) lines_count,(SELECT COUNT(*) FROM bank_statement_lines bl WHERE bl.bank_statement_import_id=bi.id AND bl.reconciliation_status='matched') matched_count FROM bank_statement_imports bi JOIN bank_accounts ba ON ba.id=bi.bank_account_id WHERE ba.company_id=? ORDER BY bi.id DESC");$imports->execute([$companyId]);$imports=$imports->fetchAll();$recons=$pdo->prepare('SELECT br.*,ba.code,ba.bank_name FROM bank_reconciliations br JOIN bank_accounts ba ON ba.id=br.bank_account_id WHERE ba.company_id=? ORDER BY br.id DESC');$recons->execute([$companyId]);$recons=$recons->fetchAll();$cheques=$pdo->prepare("SELECT c.*,ba.bank_name,bp.legal_name_ar partner_name FROM cheques c JOIN bank_accounts ba ON ba.id=c.bank_account_id LEFT JOIN business_partners bp ON bp.id=c.partner_id WHERE c.company_id=? ORDER BY c.due_date,c.id DESC");$cheques->execute([$companyId]);$cheques=$cheques->fetchAll();
render_header('banking','التسويات البنكية والشيكات','استيراد كشوف البنك والمطابقة الآلية ومتابعة الشيكات.');?>
<details class="panel quick-add" open><summary>＋ استيراد كشف بنكي</summary><form method="post" enctype="multipart/form-data"><?= csrf_field() ?><input type="hidden" name="action" value="statement_import"><div class="form-grid"><label>الحساب البنكي<select name="bank_account_id" required><?php foreach($banks as $b):?><option value="<?= (int)$b['id'] ?>"><?= e($b['code'].' — '.$b['bank_name']) ?></option><?php endforeach;?></select></label><label>من<input type="date" name="statement_from" required></label><label>إلى<input type="date" name="statement_to" required></label><label>الرصيد الافتتاحي<input type="number" step="0.01" name="opening_balance" value="0"></label><label>الرصيد الختامي<input type="number" step="0.01" name="closing_balance" required></label><label>ملف CSV<input type="file" name="csv_file" accept=".csv,text/csv"></label><label class="wide">أو ألصق الحركات<textarea name="statement_lines" rows="5" placeholder="2026-01-01,REF001,تحويل عميل,0,1000,1000"></textarea><small>التنسيق: التاريخ، المرجع، البيان، مدين، دائن، الرصيد.</small></label></div><button class="primary-btn">استيراد الكشف</button></form></details>
<section class="panel data-card"><header><h2>كشوف البنك المستوردة</h2></header><div class="responsive-table"><table><thead><tr><th>البنك</th><th>الفترة</th><th>الختامي</th><th>المطابق</th><th>الحالة</th><th></th></tr></thead><tbody><?php foreach($imports as $r):?><tr><td><?= e($r['code'].' — '.$r['bank_name']) ?></td><td><?= e($r['statement_from'].' إلى '.$r['statement_to']) ?></td><td><?= number_format((float)$r['closing_balance'],2) ?></td><td><?= (int)$r['matched_count'] ?> / <?= (int)$r['lines_count'] ?></td><td><span class="chip"><?= e($r['status']) ?></span></td><td><?php if(!(int)scalar('SELECT COUNT(*) FROM bank_reconciliations WHERE bank_statement_import_id=?',[$r['id']])):?><form method="post"><?= csrf_field() ?><input type="hidden" name="action" value="reconcile"><input type="hidden" name="import_id" value="<?= (int)$r['id'] ?>"><button>مطابقة آلية</button></form><?php endif;?></td></tr><?php endforeach;?></tbody></table></div></section>
<section class="panel data-card"><header><h2>نتائج التسويات</h2></header><div class="responsive-table"><table><thead><tr><th>البنك</th><th>التاريخ</th><th>رصيد الدفاتر</th><th>رصيد البنك</th><th>الفرق</th><th>الحالة</th><th></th></tr></thead><tbody><?php foreach($recons as $r):?><tr><td><?= e($r['code'].' — '.$r['bank_name']) ?></td><td><?= e($r['reconciliation_date']) ?></td><td><?= number_format((float)$r['book_balance'],2) ?></td><td><?= number_format((float)$r['statement_balance'],2) ?></td><td><?= number_format((float)$r['difference_amount'],2) ?></td><td><span class="chip"><?= e($r['status']) ?></span></td><td><?php if($r['status']==='reviewed'):?><form method="post"><?= csrf_field() ?><input type="hidden" name="action" value="reconciliation_approve"><input type="hidden" name="id" value="<?= (int)$r['id'] ?>"><button>اعتماد</button></form><?php endif;?></td></tr><?php endforeach;?></tbody></table></div></section>
<details class="panel quick-add"><summary>＋ تسجيل شيك</summary><form method="post"><?= csrf_field() ?><input type="hidden" name="action" value="cheque_create"><div class="form-grid"><label>الحساب البنكي<select name="bank_account_id"><?php foreach($banks as $b):?><option value="<?= (int)$b['id'] ?>"><?= e($b['code'].' — '.$b['bank_name']) ?></option><?php endforeach;?></select></label><label>نوع الشيك<select name="cheque_type"><option value="issued">صادر</option><option value="received">وارد</option></select></label><label>رقم الشيك<input name="cheque_no" required></label><label>الطرف<select name="partner_id"><?php foreach($partners as $p):?><option value="<?= (int)$p['id'] ?>"><?= e($p['partner_code'].' — '.$p['legal_name_ar']) ?></option><?php endforeach;?></select></label><label>المبلغ<input type="number" step="0.01" min="0.01" name="amount" required></label><label>تاريخ الإصدار<input type="date" name="issue_date" value="<?= date('Y-m-d') ?>" required></label><label>تاريخ الاستحقاق<input type="date" name="due_date" required></label></div><button class="primary-btn">حفظ الشيك</button></form></details>
<section class="panel data-card"><header><h2>سجل الشيكات</h2></header><div class="responsive-table"><table><thead><tr><th>الشيك</th><th>النوع</th><th>الطرف</th><th>المبلغ</th><th>الاستحقاق</th><th>الحالة</th><th>تحديث</th></tr></thead><tbody><?php foreach($cheques as $c):?><tr><td><?= e($c['cheque_no'].' — '.$c['bank_name']) ?></td><td><?= $c['cheque_type']==='issued'?'صادر':'وارد' ?></td><td><?= e($c['partner_name']?:'—') ?></td><td><?= number_format((float)$c['amount'],2) ?></td><td><?= e($c['due_date']) ?></td><td><span class="chip"><?= e($c['status']) ?></span></td><td><?php if(!in_array($c['status'],['cleared','cancelled'],true)):?><form method="post"><?= csrf_field() ?><input type="hidden" name="action" value="cheque_status"><input type="hidden" name="id" value="<?= (int)$c['id'] ?>"><select name="status"><option value="deposited">مودع</option><option value="cleared">مصروف</option><option value="returned">مرتجع</option><option value="stale">متقادم</option><option value="cancelled">ملغي</option></select><button>تحديث</button></form><?php endif;?></td></tr><?php endforeach;?></tbody></table></div></section>
<?php render_footer();
